diff options
3 files changed, 163 insertions, 162 deletions
diff --git a/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py b/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py index 4362c2d..10c42d0 100644 --- a/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py +++ b/fietsboek/updater/scripts/upd_20230103_lu8w3rwlz4ddcpms.py @@ -29,42 +29,42 @@ alembic_revision = 'c939800af428' class Up(UpdateScript): def pre_alembic(self, config): engine = create_engine(config["sqlalchemy.url"]) - connection = engine.connect() data_dir = Path(config["fietsboek.data_dir"]) - sql = ( - "SELECT tracks.id, tracks.title, tracks.description, tracks.date_raw, " - "tracks.date_tz, users.name " - "FROM tracks, users " - "WHERE tracks.owner_id = users.id;" - ) - for row in connection.execute(text(sql)): - track_id, title, description, date_raw, date_tz, author_name = row - if isinstance(date_raw, str): - date_raw = datetime.datetime.strptime(date_raw, "%Y-%m-%d %H:%M:%S.%f") - if date_tz is None: - timezone = datetime.timezone.utc - else: - timezone = datetime.timezone(datetime.timedelta(minutes=date_tz)) - date = date_raw.replace(tzinfo=timezone) - - self.tell(f"Embedding metadata for track {track_id}") - track_dir = data_dir / "tracks" / str(track_id) - gpx_path = track_dir / "track.gpx.br" - - raw_gpx = brotli.decompress(gpx_path.read_bytes()) - gpx = gpxpy.parse(raw_gpx) - - for track in gpx.tracks: - track.name = None - track.description = None - - gpx.author_name = author_name - gpx.name = title - gpx.description = description - gpx.time = date - - gpx_path.write_bytes(brotli.compress(gpx.to_xml().encode("utf-8"), quality=4)) + with engine.connect() as connection: + sql = ( + "SELECT tracks.id, tracks.title, tracks.description, tracks.date_raw, " + "tracks.date_tz, users.name " + "FROM tracks, users " + "WHERE tracks.owner_id = users.id;" + ) + for row in connection.execute(text(sql)): + track_id, title, description, date_raw, date_tz, author_name = row + if isinstance(date_raw, str): + date_raw = datetime.datetime.strptime(date_raw, "%Y-%m-%d %H:%M:%S.%f") + if date_tz is None: + timezone = datetime.timezone.utc + else: + timezone = datetime.timezone(datetime.timedelta(minutes=date_tz)) + date = date_raw.replace(tzinfo=timezone) + + self.tell(f"Embedding metadata for track {track_id}") + track_dir = data_dir / "tracks" / str(track_id) + gpx_path = track_dir / "track.gpx.br" + + raw_gpx = brotli.decompress(gpx_path.read_bytes()) + gpx = gpxpy.parse(raw_gpx) + + for track in gpx.tracks: + track.name = None + track.description = None + + gpx.author_name = author_name + gpx.name = title + gpx.description = description + gpx.time = date + + gpx_path.write_bytes(brotli.compress(gpx.to_xml().encode("utf-8"), quality=4)) def post_alembic(self, config): pass diff --git a/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py b/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py index e3e5e47..ae6c29a 100644 --- a/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py +++ b/fietsboek/updater/scripts/upd_20251109_nm561argcq1s8w27.py @@ -36,127 +36,128 @@ class Up(UpdateScript): connection = engine.connect() data_dir = Path(config["fietsboek.data_dir"]) - # This can happen in a fresh instance - if not (data_dir / "tracks").exists(): - return - - for track_dir in (data_dir / "tracks").iterdir(): - track_id = int(track_dir.name) - self.tell(f"Loading track {track_id}") - - gpx_path = track_dir / "track.gpx.br" - - # We're careful here, in case a previous update was interrupted - if not gpx_path.exists(): - continue - - gpx_bytes = brotli.decompress(gpx_path.read_bytes()) - - track = convert.smart_convert(gpx_bytes) - with connection.begin(): - connection.execute( - text("DELETE FROM track_points WHERE track_id = :id;"), - {"id": track_id}, - ) - connection.execute( - text("DELETE FROM waypoints WHERE track_id = :id;"), - {"id": track_id}, - ) - for index, point in enumerate(track.path().points): + with engine.connect() as connection: + # This can happen in a fresh instance + if not (data_dir / "tracks").exists(): + return + + for track_dir in (data_dir / "tracks").iterdir(): + track_id = int(track_dir.name) + self.tell(f"Loading track {track_id}") + + gpx_path = track_dir / "track.gpx.br" + + # We're careful here, in case a previous update was interrupted + if not gpx_path.exists(): + continue + + gpx_bytes = brotli.decompress(gpx_path.read_bytes()) + + track = convert.smart_convert(gpx_bytes) + with connection.begin(): connection.execute( - text("""INSERT INTO track_points ( - track_id, "index", longitude, latitude, elevation, time_offset - ) VALUES ( - :track_id, :index, :longitude, :latitude, :elevation, :time_offset - );"""), - { - "track_id": track_id, - "index": index, - "longitude": point.longitude, - "latitude": point.latitude, - "elevation": point.elevation, - "time_offset": point.time_offset, - }, + text("DELETE FROM track_points WHERE track_id = :id;"), + {"id": track_id}, ) - for waypoint in track.waypoints: connection.execute( - text("""INSERT INTO waypoints ( - track_id, longitude, latitude, elevation, name, description - ) VALUES ( - :track_id, :longitude, :latitude, :elevation, :name, :description - );"""), - { - "track_id": track_id, - "longitude": waypoint.longitude, - "latitude": waypoint.latitude, - "elevation": waypoint.elevation, - "name": waypoint.name, - "description": waypoint.description, - }, + text("DELETE FROM waypoints WHERE track_id = :id;"), + {"id": track_id}, + ) + for index, point in enumerate(track.path().points): + connection.execute( + text("""INSERT INTO track_points ( + track_id, "index", longitude, latitude, elevation, time_offset + ) VALUES ( + :track_id, :index, :longitude, :latitude, :elevation, :time_offset + );"""), + { + "track_id": track_id, + "index": index, + "longitude": point.longitude, + "latitude": point.latitude, + "elevation": point.elevation, + "time_offset": point.time_offset, + }, + ) + for waypoint in track.waypoints: + connection.execute( + text("""INSERT INTO waypoints ( + track_id, longitude, latitude, elevation, name, description + ) VALUES ( + :track_id, :longitude, :latitude, :elevation, :name, :description + );"""), + { + "track_id": track_id, + "longitude": waypoint.longitude, + "latitude": waypoint.latitude, + "elevation": waypoint.elevation, + "name": waypoint.name, + "description": waypoint.description, + }, + ) + + gpx_path.unlink() + shutil.move( + track_dir / "track.bck.gpx.br", + track_dir / "track.bck.br", ) - - gpx_path.unlink() - shutil.move( - track_dir / "track.bck.gpx.br", - track_dir / "track.bck.br", - ) class Down(UpdateScript): def pre_alembic(self, config): engine = create_engine(config["sqlalchemy.url"]) - connection = engine.connect() data_dir = Path(config["fietsboek.data_dir"]) query = text("SELECT id, title, description, date_raw FROM tracks;") - for row in connection.execute(query): - gpx = gpxpy.gpx.GPX() - gpx.description = row.description - gpx.name = row.title - - start_date = row.date_raw - if isinstance(start_date, str): - start_date = datetime.datetime.fromisoformat(start_date) - - segment = gpxpy.gpx.GPXTrackSegment() - points_query = text(""" - SELECT longitude, latitude, elevation, time_offset - FROM track_points WHERE track_id = :track_id ORDER BY "index"; - """) - for point in connection.execute(points_query, {"track_id": row.id}): - segment.points.append( - gpxpy.gpx.GPXTrackPoint( - latitude=point.latitude, - longitude=point.longitude, - elevation=point.elevation, - time=start_date + datetime.timedelta(seconds=point.time_offset), + with engine.connect() as connection: + for row in connection.execute(query): + gpx = gpxpy.gpx.GPX() + gpx.description = row.description + gpx.name = row.title + + start_date = row.date_raw + if isinstance(start_date, str): + start_date = datetime.datetime.fromisoformat(start_date) + + segment = gpxpy.gpx.GPXTrackSegment() + points_query = text(""" + SELECT longitude, latitude, elevation, time_offset + FROM track_points WHERE track_id = :track_id ORDER BY "index"; + """) + for point in connection.execute(points_query, {"track_id": row.id}): + segment.points.append( + gpxpy.gpx.GPXTrackPoint( + latitude=point.latitude, + longitude=point.longitude, + elevation=point.elevation, + time=start_date + datetime.timedelta(seconds=point.time_offset), + ) ) - ) - track = gpxpy.gpx.GPXTrack() - track.segments.append(segment) - gpx.tracks.append(track) - - waypoints_query = text(""" - SELECT longitude, latitude, elevation, name, description - FROM waypoints WHERE track_id = :track_id; - """) - for wpt in connection.execute(waypoints_query, {"track_id": row.id}): - gpx.waypoints.append( - gpxpy.gpx.GPXWaypoint( - longitude=wpt.longitude, - latitude=wpt.latitude, - elevation=wpt.elevation, - name=wpt.name, - comment=wpt.description, - description=wpt.description, + track = gpxpy.gpx.GPXTrack() + track.segments.append(segment) + gpx.tracks.append(track) + + waypoints_query = text(""" + SELECT longitude, latitude, elevation, name, description + FROM waypoints WHERE track_id = :track_id; + """) + for wpt in connection.execute(waypoints_query, {"track_id": row.id}): + gpx.waypoints.append( + gpxpy.gpx.GPXWaypoint( + longitude=wpt.longitude, + latitude=wpt.latitude, + elevation=wpt.elevation, + name=wpt.name, + comment=wpt.description, + description=wpt.description, + ) ) - ) - xml_data = gpx.to_xml(prettyprint=False).encode("utf-8") - track_dir = data_dir / "tracks" / str(row.id) - (track_dir / "track.gpx.br").write_bytes(brotli.compress(xml_data)) - shutil.move(track_dir / "track.bck.br", track_dir / "track.bck.gpx.br") + xml_data = gpx.to_xml(prettyprint=False).encode("utf-8") + track_dir = data_dir / "tracks" / str(row.id) + (track_dir / "track.gpx.br").write_bytes(brotli.compress(xml_data)) + shutil.move(track_dir / "track.bck.br", track_dir / "track.bck.gpx.br") def post_alembic(self, config): pass diff --git a/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py b/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py index e900c7a..cdc09f6 100644 --- a/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py +++ b/fietsboek/updater/scripts/upd_30ppwg8zi4ujb46f.py @@ -25,18 +25,18 @@ alembic_revision = 'c939800af428' class Up(UpdateScript): def pre_alembic(self, config): engine = create_engine(config["sqlalchemy.url"]) - connection = engine.connect() - data_dir = Path(config["fietsboek.data_dir"]) + with engine.connect() as connection: + data_dir = Path(config["fietsboek.data_dir"]) - for row in connection.execute(text("SELECT id, gpx FROM tracks;")): - self.tell(f"Moving GPX data for track {row.id} from database to disk") - track_dir = data_dir / "tracks" / str(row.id) - track_dir.mkdir(parents=True, exist_ok=True) + for row in connection.execute(text("SELECT id, gpx FROM tracks;")): + self.tell(f"Moving GPX data for track {row.id} from database to disk") + track_dir = data_dir / "tracks" / str(row.id) + track_dir.mkdir(parents=True, exist_ok=True) - raw_gpx = gzip.decompress(row.gpx) - gpx_path = track_dir / "track.gpx.br" - gpx_path.write_bytes(brotli.compress(raw_gpx, quality=5)) - shutil.copy(gpx_path, track_dir / "track.bck.gpx.br") + raw_gpx = gzip.decompress(row.gpx) + gpx_path = track_dir / "track.gpx.br" + gpx_path.write_bytes(brotli.compress(raw_gpx, quality=5)) + shutil.copy(gpx_path, track_dir / "track.bck.gpx.br") def post_alembic(self, config): pass @@ -48,18 +48,18 @@ class Down(UpdateScript): def post_alembic(self, config): engine = create_engine(config["sqlalchemy.url"]) - connection = engine.connect() - data_dir = Path(config["fietsboek.data_dir"]) + with engine.connect() as connection: + data_dir = Path(config["fietsboek.data_dir"]) - for track_path in (data_dir / "tracks").iterdir(): - track_id = int(track_path.name) - self.tell(f"Moving GPX data for track {track_id} from disk to database") - brotli_data = (track_path / "track.gpx.br").read_bytes() - gzip_data = gzip.compress(brotli.decompress(brotli_data)) - connection.execute( - text("UPDATE tracks SET gpx = :gpx WHERE id = :id;"), - gpx=gzip_data, id=track_id - ) + for track_path in (data_dir / "tracks").iterdir(): + track_id = int(track_path.name) + self.tell(f"Moving GPX data for track {track_id} from disk to database") + brotli_data = (track_path / "track.gpx.br").read_bytes() + gzip_data = gzip.compress(brotli.decompress(brotli_data)) + connection.execute( + text("UPDATE tracks SET gpx = :gpx WHERE id = :id;"), + gpx=gzip_data, id=track_id + ) - (track_path / "track.gpx.br").unlink() - (track_path / "track.bck.gpx.br").unlink(missing_ok=True) + (track_path / "track.gpx.br").unlink() + (track_path / "track.bck.gpx.br").unlink(missing_ok=True) |