dogsheep/swarm-to-sqlite

Create a SQLite database containing your checkin history from Foursquare Swarm

swarm-to-sqlite's Issues

Implement code to pull checkins from the Foursquare API

The tool currently only works with a pre-prepared JSON file of checkins.

When called without options, it should prompt the user to paste in a Foursquare OAuth token.

The --token= option should work too, and should be backed up by an optional environment variable.

ambiguous column name: createdAt - on checkin_details view

It looks like Swarm changed their schema and now both venues and checkins have createdAt fields.

Which breaks this view:

swarm-to-sqlite/swarm_to_sqlite/utils.py

Lines 171 to 188 in 719b6e9

	select
	checkins.id,
	created,
	venues.id as venue_id,
	venues.name as venue_name,
	venues.latitude,
	venues.longitude,
	group_concat(categories.name) as venue_categories,
	shout,
	createdBy,
	events.name as event_name
	from checkins
	join venues on checkins.venue = venues.id
	left join events on checkins.event = events.id
	join categories_venues on venues.id = categories_venues.venues_id
	join categories on categories.id = categories_venues.categories_id
	group by checkins.id
	order by createdAt desc

fails before generating views. ERR: table sqlite_master may not be modified

generates checkins.db but seems to fail before generating views

note: it worked on an Ubuntu WSL but fails on macOS 12.5.1

later edit: I suspect this is a problem with my local set-up, dogsheep-beta index also throws the same error

full error:

Importing 2591 checkins  [###################################-]   98%  00:00:00
Traceback (most recent call last):
  File "/Users/pax/devbox/envAll/bin/swarm-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/swarm_to_sqlite/cli.py", line 77, in cli
    ensure_foreign_keys(db)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/swarm_to_sqlite/utils.py", line 145, in ensure_foreign_keys
    db[fk.table].add_foreign_key(fk.column, fk.other_table, fk.other_column)
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/sqlite_utils/db.py", line 2123, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/Users/pax/devbox/envAll/lib/python3.8/site-packages/sqlite_utils/db.py", line 1086, in add_foreign_keys
    cursor.execute(
sqlite3.OperationalError: table sqlite_master may not be modified

--save option to dump checkins to a JSON file on disk

This is a complement to the --load option - mainly useful for development purposes.

(I'll rename --file to --load as part of this issue).

Error thrown: table photos has no column named hasSticker

While running swarm-to-sqlite it throws an error:

harper@:~/dogsheep/swarm$ swarm-to-sqlite checkins.db --save=checkins.json
Please provide your Foursquare OAuth token:
Importing 8127 checkins  [#################-------------------]   49%  00:01:52
Traceback (most recent call last):
File "/home/harper/.local/bin/swarm-to-sqlite", line 11, in <module>
    sys.exit(cli())
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
File "/home/harper/.local/lib/python3.6/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
File "/home/harper/.local/lib/python3.6/site-packages/swarm_to_sqlite/cli.py", line 73, in cli
    save_checkin(checkin, db)
File "/home/harper/.local/lib/python3.6/site-packages/swarm_to_sqlite/utils.py", line 94, in save_checkin
    photos_table.insert(photo, replace=True)
File "/home/harper/.local/lib/python3.6/site-packages/sqlite_utils/db.py", line 963, in insert
    alter = self.value_or_default("alter", alter)
File "/home/harper/.local/lib/python3.6/site-packages/sqlite_utils/db.py", line 1142, in insert_all
    def upsert_all(
sqlite3.OperationalError: table photos has no column named hasSticker

Where should i dig in?

403 when getting token

I tried to use https://your-foursquare-oauth-token.glitch.me/ to get my Swarm auth token and got a 403 after I clicked the Allow button:

I'm not sure if this is the right repo to report this in

Online tool for getting a Foursquare OAuth token

I will link to this from the documentation. See also this conversation on Twitter: https://twitter.com/simonw/status/1166822603023011840

I've decided to go with "copy and paste in a token" rather than hooking up a local web server that can have tokens passed to it.

Treat Foursquare timestamps as UTC

Current test failure is due to timezone differences between my laptop and Circle CI:

https://circleci.com/gh/dogsheep/swarm-to-sqlite/3

E         Full diff:
E         - [{'created': '2018-07-01T04:48:19',
E         ?                           ^
E         + [{'created': '2018-07-01T02:48:19',
E         ?                           ^
E         'createdAt': 1530413299,

The timestamps I store in created should always be UTC.

Option to fetch only checkins more recent than the current max checkin

The Foursquare checkins API supports "return every checkin occurring after this point" - I can pass it the maximum createdAt date currently stored in the database. This will allow for quick incremental fetches via a cron.

Error thrown: sqlite3.OperationalError: table users has no column named lastName

Just installed swarm-to-sqlite-0.3.2 and tried according to the docs:

Traceback (most recent call last):
  File "/usr/local/bin/swarm-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/swarm_to_sqlite/cli.py", line 73, in cli
    save_checkin(checkin, db)
  File "/usr/local/lib/python3.9/site-packages/swarm_to_sqlite/utils.py", line 82, in save_checkin
    checkins_table.m2m("users", user, m2m_table="likes", pk="id")
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1914, in m2m
    id = other_table.insert(record, pk=pk, replace=True).last_pk
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1647, in insert
    return self.insert_all(
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1765, in insert_all
    self.insert_chunk(
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 1575, in insert_chunk
    result = self.db.execute(query, params)
  File "/usr/local/lib/python3.9/site-packages/sqlite_utils/db.py", line 200, in execute
    return self.conn.execute(sql, parameters)
sqlite3.OperationalError: table users has no column named lastName

Upgrade to sqlite-utils 2.x

Define a view that displays photos correctly

The photos table stores data like this:

id	createdAt	source	prefix	suffix	width	height	visibility	created ▲	user
5e12c9708506bc000840262a	January 06, 2020 - 05:45:20 UTC	Swarm for iOS 1	https://fastly.4sqi.net/img/general/	/15889193_AXxGk4I1nbzUZuyYqObgbXdJNyEHiwj6AUDq0tPZWtw.jpg	1920	1440	public	2020-01-06T05:45:20	15889193

The photo URL can be derived from those pieces - define a SQL view which does that (using datasette-json-html to display the pictures)