36 lines
1.7 KiB
Python
36 lines
1.7 KiB
Python
import sqlite3
|
|
|
|
longterm = sqlite3.connect("/data/archive/lthist.sqlite3")
|
|
longterm.executescript(f"""
|
|
CREATE TABLE IF NOT EXISTS places (
|
|
guid TEXT PRIMARY KEY,
|
|
url TEXT,
|
|
title TEXT,
|
|
visit_count INTEGER DEFAULT 0,
|
|
last_visit_date INTEGER,
|
|
description TEXT,
|
|
preview_image_url TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS bookmarks (
|
|
guid TEXT PRIMARY KEY,
|
|
bookmark TEXT NOT NULL REFERENCES places(guid),
|
|
title TEXT,
|
|
dateAdded INTEGER,
|
|
lastModified INTEGER
|
|
);
|
|
CREATE TABLE IF NOT EXISTS historyvisits (
|
|
id TEXT PRIMARY KEY,
|
|
place TEXT NOT NULL REFERENCES places(guid),
|
|
date INTEGER NOT NULL,
|
|
type INTEGER NOT NULL
|
|
);
|
|
""")
|
|
longterm.execute("ATTACH DATABASE '/tmp/places.sqlite' AS transient;")
|
|
longterm.execute("""INSERT INTO places SELECT guid, url, title, visit_count, last_visit_date, description, preview_image_url FROM moz_places WHERE true
|
|
ON CONFLICT DO UPDATE SET visit_count = excluded.visit_count, last_visit_date = excluded.last_visit_date, title = excluded.title, description = excluded.description, preview_image_url = excluded.preview_image_url;""")
|
|
longterm.execute("""INSERT INTO bookmarks SELECT moz_bookmarks.guid, moz_places.guid, moz_bookmarks.title, dateAdded, lastModified FROM moz_bookmarks JOIN moz_places ON moz_places.id = moz_bookmarks.fk WHERE true
|
|
ON CONFLICT DO UPDATE SET lastModified = excluded.lastModified, title = excluded.title;""")
|
|
# TODO: possibly wrong with new profile, might need to increment historyvisits or something
|
|
longterm.execute("INSERT INTO historyvisits SELECT (moz_historyvisits.id || '/' || visit_date), moz_places.guid, visit_date, visit_type FROM moz_historyvisits JOIN moz_places ON moz_places.id = moz_historyvisits.place_id ON CONFLICT DO NOTHING;")
|
|
|
|
longterm.commit() |