Revision control

SQLite format 3€@ 1*".á↩ø<9É~È{™~™~jzŽ{hxÏzatxrÖm²r™lŠgUlKf)eGeæcëe bsag`™_`^]þ^Î]
[d\ÍZ+[Y’XöXoXËV_X.UÅT¶U–SÏS"RyQÌQ/P¦P"M¢KÔG6B?Í=S<w;<9ƒ)<[[…-viewview_awesomebar_bookmarks_with_faviconsview_awesomebar_bookmarks_with_faviconsCREATE VIEW view_awesomebar_bookmarks_with_favicons AS
SELECT b.guid AS guid, b.url AS url, b.title AS title, b.description AS description, b.visitDate AS visitDate, f.id AS iconID, f.url AS iconURL, f.date AS iconDate, f.type AS iconType, f.width AS iconWidth
FROM view_awesomebar_bookmarks b LEFT JOIN favicons f ON f.id = b.faviconID‚8;??„viewview_awesomebar_bookmarksview_awesomebar_bookmarksCREATE VIEW view_awesomebar_bookmarks AS
SELECT b.guid AS guid, b.url AS url, b.title AS title, b.description AS description, b.faviconID AS faviconID, h.visitDate AS visitDate
FROM view_all_bookmarks b LEFT JOIN view_history_visits h ON b.url = h.urlY:33‚]viewview_history_visitsview_history_visitsCREATE VIEW view_history_visits AS
SELECT h.url AS url, max(v.date) AS visitDate, h.domain_id AS domain_id
FROM history h JOIN visits v ON v.siteID = h.id
GROUP BY h.id„w911‰viewview_all_bookmarksview_all_bookmarksCREATE VIEW view_all_bookmarks AS
SELECT guid, bmkUri AS url, title, description, faviconID
FROM bookmarksMirror
WHERE
type = 1 AND
is_overridden IS 0 AND
is_deleted IS 0
UNION ALL
SELECT guid, bmkUri AS url, title, description, faviconID
FROM bookmarksLocal
WHERE
type = 1 AND
is_deleted IS 0
UNION ALL
SELECT guid, bmkUri AS url, title, description, -1 AS faviconID
FROM bookmarksBuffer bb
WHERE
bb.type = 1 AND
bb.is_deleted IS 0 AND
-- Exclude pending bookmark deletions.
NOT EXISTS (SELECT 1 FROM pending_deletions AS pd WHERE pd.id = bb.guid)„>8[[‡Wviewview_bookmarksBufferStructure_on_mirrorview_bookmarksBufferStructure_on_mirrorCREATE VIEW view_bookmarksBufferStructure_on_mirror AS
-- We don't need to exclude deleted parents, because we drop those from the structure
-- table when we see them.
SELECT parent, child, idx, 1 AS is_overridden FROM bookmarksBufferStructure
UNION ALL
-- Exclude anything from the mirror that's present in the buffer (dynamic is_overridden).
SELECT parent, child, idx, 0 AS is_overridden
FROM bookmarksMirrorStructure LEFT JOIN bookmarksBuffer ON
parent = guid
WHERE guid IS NULLŠ%7gg“↩viewview_bookmarksBuffer_with_deletions_on_mirrorview_bookmarksBuffer_with_deletions_on_mirrorCREATE VIEW view_bookmarksBuffer_with_deletions_on_mirror AS
SELECT
-1 AS id,
mirror.guid AS guid,
mirror.type AS type,
mirror.date_added AS date_added,
mirror.is_deleted AS is_deleted,
mirror.parentid AS parentid,
mirror.parentName AS parentName,
mirror.feedUri AS feedUri,
mirror.siteUri AS siteUri,
mirror.pos AS pos,
mirror.title AS title,
mirror.description AS description,
mirror.bmkUri AS bmkUri,
mirror.keyword AS keyword,
mirror.folderName AS folderName,
NULL AS faviconID,
0 AS is_overridden
-- LEFT EXCLUDING JOIN to get mirror records that aren't in the buffer.
-- We don't have an is_overridden flag to help us here.
FROM bookmarksMirror mirror LEFT JOIN bookmarksBuffer buffer ON
mirror.guid = buffer.guid
WHERE buffer.guid IS NULL
UNION ALL
SELECT
-1 AS id,
guid,
type,
date_added,
is_deleted,
parentid,
parentName,
feedUri,
siteUri,
pos,
title,
description,
bmkUri,
keyword,
folderName,
NULL AS faviconID,
1 AS is_overridden
FROM bookmarksBuffer
WHERE
is_deleted IS 0 AND
NOT EXISTS (SELECT 1 FROM pending_deletions deletions WHERE deletions.id = guid)‰6II‘5viewview_bookmarksBuffer_on_mirrorview_bookmarksBuffer_on_mirrorCREATE VIEW view_bookmarksBuffer_on_mirror AS
SELECT
-1 AS id,
mirror.guid AS guid,
mirror.type AS type,
mirror.date_added AS date_added,
mirror.is_deleted AS is_deleted,
mirror.parentid AS parentid,
mirror.parentName AS parentName,
mirror.feedUri AS feedUri,
mirror.siteUri AS siteUri,
mirror.pos AS pos,
mirror.title AS title,
mirror.description AS description,
mirror.bmkUri AS bmkUri,
mirror.keyword AS keyword,
mirror.folderName AS folderName,
NULL AS faviconID,
0 AS is_overridden
-- LEFT EXCLUDING JOIN to get mirror records that aren't in the buffer.
-- We don't have an is_overridden flag to help us here.
FROM bookmarksMirror mirror LEFT JOIN bookmarksBuffer buffer ON
mirror.guid = buffer.guid
WHERE buffer.guid IS NULL
UNION ALL
SELECT
-1 AS id,
guid,
type,
date_added,
is_deleted,
parentid,
parentName,
feedUri,
siteUri,
pos,
title,
description,
bmkUri,
keyword,
folderName,
NULL AS faviconID,
1 AS is_overridden
FROM bookmarksBuffer
WHERE is_deleted IS 0ƒK5YY…uviewview_bookmarksLocalStructure_on_mirrorview_bookmarksLocalStructure_on_mirrorCREATE VIEW view_bookmarksLocalStructure_on_mirror AS
SELECT parent, child, idx, 1 AS is_overridden
FROM bookmarksLocalStructure
WHERE ((SELECT is_deleted FROM bookmarksLocal WHERE guid = parent) IS NOT 1)
UNION ALL
SELECT parent, child, idx, 0 AS is_overridden
FROM bookmarksMirrorStructure
WHERE ((SELECT is_overridden FROM bookmarksMirror WHERE guid = parent) IS NOT 1)„}4GGˆ}viewview_bookmarksLocal_on_mirrorview_bookmarksLocal_on_mirrorCREATE VIEW view_bookmarksLocal_on_mirror AS
SELECT -1 AS id, guid, type, date_added, is_deleted, parentid, parentName, feedUri, siteUri, pos, title, description, bmkUri, folderName, faviconID, NULL AS local_modified, server_modified, 0 AS is_overridden
FROM bookmarksMirror WHERE is_overridden IS NOT 1
UNION ALL
SELECT -1 AS id, guid, type, date_added, is_deleted, parentid, parentName, feedUri, siteUri, pos, title, description, bmkUri, folderName, faviconID, local_modified, NULL AS server_modified, 1 AS is_overridden
FROM bookmarksLocal WHERE is_deleted IS NOT 13K+indexidx_visits_siteID_is_local_datevisits1CREATE INDEX idx_visits_siteID_is_local_date
ON visits (siteID, is_local, date)2??indexidx_history_should_uploadhistory0CREATE INDEX idx_history_should_upload
ON history (should_upload) WHERE should_upload = 11Q=3indexidx_bookmarksMirrorStructure_childbookmarksMirrorStructure/CREATE INDEX idx_bookmarksMirrorStructure_child
ON bookmarksMirrorStructure (child)*0[=Iindexidx_bookmarksMirrorStructure_parent_idxbookmarksMirrorStructure.CREATE INDEX idx_bookmarksMirrorStructure_parent_idx
ON bookmarksMirrorStructure (parent, idx)&/Y;Eindexidx_bookmarksLocalStructure_parent_idxbookmarksLocalStructure-CREATE INDEX idx_bookmarksLocalStructure_parent_idx
ON bookmarksLocalStructure (parent, idx)*.[=Iindexidx_bookmarksBufferStructure_parent_idxbookmarksBufferStructure,CREATE INDEX idx_bookmarksBufferStructure_parent_idx
ON bookmarksBufferStructure (parent, idx)d-ƒ+tabletabstabs+CREATE TABLE tabs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
client_guid TEXT REFERENCES clients(guid) ON DELETE CASCADE,
url TEXT NOT NULL,
title TEXT,
history TEXT,
last_used INTEGER
)]+ƒtableclientsclients)CREATE TABLE clients (
guid TEXT PRIMARY KEY,
name TEXT NOT NULL,
modified INTEGER NOT NULL,
type TEXT,
formfactor TEXT,
os TEXT,
version TEXT,
fxaDeviceId TEXT
)-,Aindexsqlite_autoindex_clients_1clients**‚tablecommandscommands(CREATE TABLE commands (
id INTEGER PRIMARY KEY AUTOINCREMENT,
client_guid TEXT NOT NULL,
value TEXT NOT NULL
)ƒL(--†Ktablecached_top_sitescached_top_sites&CREATE TABLE cached_top_sites (
historyID INTEGER,
url TEXT NOT NULL,
title TEXT NOT NULL,
guid TEXT NOT NULL UNIQUE,
domain_id INTEGER,
domain TEXT NO NULL,
localVisitDate REAL,
remoteVisitDate REAL,
localVisitCount INTEGER,
remoteVisitCount INTEGER,
iconID INTEGER,
iconURL TEXT,
iconDate REAL,
iconType INTEGER,
iconWidth INTEGER,
frecencies REAL
)?)S-indexsqlite_autoindex_cached_top_sites_1cached_top_sites'Z&tablequeuequeue$CREATE TABLE queue (
url TEXT NOT NULL UNIQUE,
title TEXT
))'=indexsqlite_autoindex_queue_1queue%%['=indexidx_page_metadata_cache_key_uniqueindexpage_metadata#CREATE UNIQUE INDEX idx_page_metadata_cache_key_uniqueindex ON page_metadata (cache_key)$Y'9indexidx_page_metadata_site_url_uniqueindexpage_metadata"CREATE UNIQUE INDEX idx_page_metadata_site_url_uniqueindex ON page_metadata (site_url)c"??‚Utableactivity_stream_blocklistactivity_stream_blocklist CREATE TABLE activity_stream_blocklist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT NOT NULL UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)Q#e?indexsqlite_autoindex_activity_stream_blocklist_1activity_stream_blocklist!‚f ))…tableremote_devicesremote_devicesCREATE TABLE remote_devices (
id INTEGER PRIMARY KEY AUTOINCREMENT,
guid TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
type TEXT NOT NULL,
is_current_device INTEGER NOT NULL,
-- Timestamps in ms.
date_created INTEGER NOT NULL,
date_modified INTEGER NOT NULL,
last_access_time INTEGER
);!O)indexsqlite_autoindex_remote_devices_1remote_devicesq!!ƒ-tablehighlightshighlightsCREATE TABLE highlights (
historyID INTEGER PRIMARY KEY,
cache_key LONGVARCHAR,
url TEXT,
title TEXT,
guid TEXT,
visitCount INTEGER,
visitDate DATETIME,
is_bookmarked INTEGER
)M--‚Mtablepinned_top_sitespinned_top_sitesCREATE TABLE pinned_top_sites (
historyID INTEGER,
url TEXT NOT NULL UNIQUE,
title TEXT,
guid TEXT,
pinDate REAL,
domain TEXT NOT NULL
)?S-indexsqlite_autoindex_pinned_top_sites_1pinned_top_sites‚L''„Wtablepage_metadatapage_metadataCREATE TABLE page_metadata (
id INTEGER PRIMARY KEY,
cache_key LONGVARCHAR UNIQUE,
site_url TEXT,
media_url LONGVARCHAR,
title TEXT,
type VARCHAR(32),
description TEXT,
provider_name TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
expired_at LONG
)9M'indexsqlite_autoindex_page_metadata_1page_metadataK//‚Iviewview_icon_for_urlview_icon_for_urlCREATE VIEW view_icon_for_url AS
SELECT history.url AS url, icons.iconID AS iconID
FROM history, view_favicons_widest AS icons
WHERE history.id = icons.siteID‚ ;;ƒ-viewview_history_id_faviconview_history_id_faviconCREATE VIEW view_history_id_favicon AS
SELECT history.id AS id, iconID, iconURL, iconDate, iconType, iconWidth
FROM history LEFT OUTER JOIN view_favicons_widest ON
history.id = view_favicons_widest.siteID‚u55…viewview_favicons_widestview_favicons_widestCREATE VIEW view_favicons_widest AS
SELECT
favicon_sites.siteID AS siteID,
favicons.id AS iconID,
favicons.url AS iconURL,
favicons.date AS iconDate,
favicons.type AS iconType,
max(favicons.width) AS iconWidth
FROM favicon_sites, favicons
WHERE favicon_sites.faviconID = favicons.id
GROUP BY siteID‚''ƒ{tablefavicon_sitesfavicon_sitesCREATE TABLE favicon_sites (
id INTEGER PRIMARY KEY AUTOINCREMENT,
siteID INTEGER NOT NULL REFERENCES history(id) ON DELETE CASCADE,
faviconID INTEGER NOT NULL REFERENCES favicons(id) ON DELETE CASCADE,
UNIQUE (siteID, faviconID)
)9M'indexsqlite_autoindex_favicon_sites_1favicon_sites//gtablepending_deletionspending_deletionsCREATE TABLE pending_deletions (
id TEXT PRIMARY KEY REFERENCES bookmarksBuffer(guid) ON DELETE CASCADE
)AU/indexsqlite_autoindex_pending_deletions_1pending_deletions‚)==ƒetablebookmarksMirrorStructurebookmarksMirrorStructureCREATE TABLE bookmarksMirrorStructure (
parent TEXT NOT NULL REFERENCES bookmarksMirror(guid) ON DELETE CASCADE,
-- Should be the GUID of a child.
child TEXT NOT NULL,
-- Should advance from 0.
idx INTEGER NOT NULL
)‰s++“tablebookmarksMirrorbookmarksMirrorCREATE TABLE bookmarksMirror (
-- Shared fields.
id INTEGER PRIMARY KEY AUTOINCREMENT
, guid TEXT NOT NULL UNIQUE
-- Type enum.
, type TINYINT NOT NULL
, date_added INTEGER
-- Record/envelope metadata that'll allow us to do merges.
-- Boolean
, is_deleted TINYINT NOT NULL DEFAULT 0
-- GUID
, parentid TEXT
, parentName TEXT
-- Type-specific fields. These should be NOT NULL in many cases, but we're going
-- for a sparse schema, so this'll do for now. Enforce these in the application code.
-- LIVEMARKS
, feedUri TEXT, siteUri TEXT
-- SEPARATORS
, pos INT
-- FOLDERS, BOOKMARKS, QUERIES
, title TEXT, description TEXT
-- BOOKMARKS, QUERIES
, bmkUri TEXT, tags TEXT, keyword TEXT
-- QUERIES
, folderName TEXT, queryId TEXT
-- Milliseconds.
, server_modified INTEGER NOT NULL
-- Boolean, 0 (false) if deleted.
, hasDupe TINYINT NOT NULL DEFAULT 0, is_overridden TINYINT NOT NULL DEFAULT 0, faviconID INTEGER REFERENCES favicons(id) ON DELETE SET NULL
, CONSTRAINT parentidOrDeleted CHECK (parentid IS NOT NULL OR is_deleted = 1)
, CONSTRAINT parentNameOrDeleted CHECK (parentName IS NOT NULL OR is_deleted = 1)
)=Q+indexsqlite_autoindex_bookmarksMirror_1bookmarksMirror‚%;;ƒatablebookmarksLocalStructurebookmarksLocalStructureCREATE TABLE bookmarksLocalStructure (
parent TEXT NOT NULL REFERENCES bookmarksLocal(guid) ON DELETE CASCADE,
-- Should be the GUID of a child.
child TEXT NOT NULL,
-- Should advance from 0.
idx INTEGER NOT NULL
)‰d))“tablebookmarksLocalbookmarksLocalCREATE TABLE bookmarksLocal (
-- Shared fields.
id INTEGER PRIMARY KEY AUTOINCREMENT
, guid TEXT NOT NULL UNIQUE
-- Type enum.
, type TINYINT NOT NULL
, date_added INTEGER
-- Record/envelope metadata that'll allow us to do merges.
-- Boolean
, is_deleted TINYINT NOT NULL DEFAULT 0
-- GUID
, parentid TEXT
, parentName TEXT
-- Type-specific fields. These should be NOT NULL in many cases, but we're going
-- for a sparse schema, so this'll do for now. Enforce these in the application code.
-- LIVEMARKS
, feedUri TEXT, siteUri TEXT
-- SEPARATORS
, pos INT
-- FOLDERS, BOOKMARKS, QUERIES
, title TEXT, description TEXT
-- BOOKMARKS, QUERIES
, bmkUri TEXT, tags TEXT, keyword TEXT
-- QUERIES
, folderName TEXT, queryId TEXT
-- Can be null. Client clock. In extremis only.
, local_modified INTEGER
-- SyncStatus enum. Set when changed or created.
, sync_status TINYINT NOT NULL, faviconID INTEGER REFERENCES favicons(id) ON DELETE SET NULL
, CONSTRAINT parentidOrDeleted CHECK (parentid IS NOT NULL OR is_deleted = 1)
, CONSTRAINT parentNameOrDeleted CHECK (parentName IS NOT NULL OR is_deleted = 1)
);O)indexsqlite_autoindex_bookmarksLocal_1bookmarksLocal‚)↩==ƒetablebookmarksBufferStructurebookmarksBufferStructureCREATE TABLE bookmarksBufferStructure (
parent TEXT NOT NULL REFERENCES bookmarksBuffer(guid) ON DELETE CASCADE,
-- Should be the GUID of a child.
child TEXT NOT NULL,
-- Should advance from 0.
idx INTEGER NOT NULL
)‰ ++‘MtablebookmarksBufferbookmarksBuffer CREATE TABLE bookmarksBuffer (
-- Shared fields.
id INTEGER PRIMARY KEY AUTOINCREMENT
, guid TEXT NOT NULL UNIQUE
-- Type enum.
, type TINYINT NOT NULL
, date_added INTEGER
-- Record/envelope metadata that'll allow us to do merges.
-- Boolean
, is_deleted TINYINT NOT NULL DEFAULT 0
-- GUID
, parentid TEXT
, parentName TEXT
-- Type-specific fields. These should be NOT NULL in many cases, but we're going
-- for a sparse schema, so this'll do for now. Enforce these in the application code.
-- LIVEMARKS
, feedUri TEXT, siteUri TEXT
-- SEPARATORS
, pos INT
-- FOLDERS, BOOKMARKS, QUERIES
, title TEXT, description TEXT
-- BOOKMARKS, QUERIES
, bmkUri TEXT, tags TEXT, keyword TEXT
-- QUERIES
, folderName TEXT, queryId TEXT
-- Milliseconds.
, server_modified INTEGER NOT NULL
-- Boolean, 0 (false) if deleted.
, hasDupe TINYINT NOT NULL DEFAULT 0
, CONSTRAINT parentidOrDeleted CHECK (parentid IS NOT NULL OR is_deleted = 1)
, CONSTRAINT parentNameOrDeleted CHECK (parentName IS NOT NULL OR is_deleted = 1)
)= Q+indexsqlite_autoindex_bookmarksBuffer_1bookmarksBuffer↩ƒ …ytablevisitsvisits
CREATE TABLE visits (
id INTEGER PRIMARY KEY AUTOINCREMENT,
siteID INTEGER NOT NULL REFERENCES history(id) ON DELETE CASCADE,
-- Microseconds since epoch.
date REAL NOT NULL,
type INTEGER NOT NULL,
-- Some visits are local. Some are remote ('mirrored'). This boolean flag is the split.
is_local TINYINT NOT NULL,
UNIQUE (siteID, date, type)
)+
?indexsqlite_autoindex_visits_1visits WƒtablefaviconsfaviconsCREATE TABLE favicons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT NOT NULL UNIQUE,
width INTEGER,
height INTEGER,
type INTEGER NOT NULL,
date REAL NOT NULL
)/Cindexsqlite_autoindex_favicons_1favicons …NŠstablehistoryhistoryCREATE TABLE history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
-- Not null, but the value might be replaced by the server's.
guid TEXT NOT NULL UNIQUE,
-- May only be null for deleted records.
url TEXT UNIQUE,
title TEXT NOT NULL,
-- Can be null. Integer milliseconds.
server_modified INTEGER,
-- Can be null. Client clock. In extremis only.
local_modified INTEGER,
-- Boolean. Locally deleted.
is_deleted TINYINT NOT NULL,
-- Boolean. Set when changed or visits added.
should_upload TINYINT NOT NULL,
domain_id INTEGER REFERENCES domains(id) ON DELETE CASCADE,
CONSTRAINT urlOrDeleted CHECK (url IS NOT NULL OR is_deleted = 1)
)-Aindexsqlite_autoindex_history_2history-Aindexsqlite_autoindex_history_1historyP++Ytablesqlite_sequencesqlite_sequenceCREATE TABLE sqlite_sequence(name,seq),‚/tabledomainsdomainsCREATE TABLE domains (
id INTEGER PRIMARY KEY AUTOINCREMENT,
domain TEXT NOT NULL UNIQUE,
showOnTopSites TINYINT NOT NULL DEFAULT 1
)-Aindexsqlite_autoindex_domains_1domains↩ïï# example.com
ññ# example.com↩É”ìàÔ´À§”'favicon_sites  favicons
visits tabs visits
 history
 domains)bookmarksLocal↩½½A
ðð% wW49949FFbu4
åå; http://www.example.com/↩ÑÑ-Ihttp://example.com/favicon.icoAÖ͌{å¤è
ÞÞ!I http://example.com/favicon.ico↩ßðß  odznu  odzm?ÿ
âòâ odznu↩  odzm?ÿ↩€
€↩€↩~^ÆI ~Ï~^o% %-); dUqCGL6-YAMzdK³%šmobile______Mobile BookmarksExample Domainhttp://www.example.com/dK³%š;%%↩↩unfiled_____dK²èbroot________dK²èb;%%↩↩toolbar_____dK²èbroot________dK²èb;%%↩↩menu________dK²èbroot________dK²èb;%%↩↩mobile______dK²èbroot________dK³%š;%%↩↩root________dK²èbroot________dK²èb
œœÏàð¾­%dUqCGL6-YAMz%unfiled_____%toolbar_____%menu________% mobile______%root________↩hâÄ¥†h%%mobile______dUqCGL6-YAMz%%root________mobile______%%root________unfiled_____%% root________toolbar_____%%root________menu________↩€
€↩€↩€
€↩úú
ûû ↩——g ;;)3http://www.example.com/http://www.example.com/Example Domainexample2018-06-29 13:19:43d[&"
ää;http://www.example.com/↩€
€↩€