1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
|
/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*-
* vim: sw=2 ts=2 et lcs=trail\:.,tab\:>~ :
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
* file, You can obtain one at http://mozilla.org/MPL/2.0/. */
#ifndef __nsPlacesTables_h__
#define __nsPlacesTables_h__
#define CREATE_MOZ_PLACES \
nsLiteralCString( \
"CREATE TABLE moz_places ( " \
" id INTEGER PRIMARY KEY" \
", url LONGVARCHAR" \
", title LONGVARCHAR" \
", rev_host LONGVARCHAR" \
", visit_count INTEGER DEFAULT 0" \
", hidden INTEGER DEFAULT 0 NOT NULL" \
", typed INTEGER DEFAULT 0 NOT NULL" \
", frecency INTEGER DEFAULT -1 NOT NULL" \
", last_visit_date INTEGER " \
", guid TEXT" \
", foreign_count INTEGER DEFAULT 0 NOT NULL" \
", url_hash INTEGER DEFAULT 0 NOT NULL " \
", description TEXT" \
", preview_image_url TEXT" \
", site_name TEXT" \
", origin_id INTEGER REFERENCES moz_origins(id)" \
", recalc_frecency INTEGER NOT NULL DEFAULT 0" \
", alt_frecency INTEGER" \
", recalc_alt_frecency INTEGER NOT NULL DEFAULT 0" \
")")
#define CREATE_MOZ_HISTORYVISITS \
nsLiteralCString( \
"CREATE TABLE moz_historyvisits (" \
" id INTEGER PRIMARY KEY" \
", from_visit INTEGER" \
", place_id INTEGER" \
", visit_date INTEGER" \
", visit_type INTEGER" \
", session INTEGER" \
", source INTEGER DEFAULT 0 NOT NULL" \
", triggeringPlaceId INTEGER" \
")")
// These two tables were designed to store data with json in mind
// ideally one column per "consumer" (sync, annotations, etc) to keep
// concerns separate. Using an UPSERT is the suggested way to update
// this table vs INSERT OR REPLACE to avoid clearing out any existing properties
// see PlacesSyncUtils.sys.mjs for an example of how sync does this
#define CREATE_MOZ_PLACES_EXTRA \
nsLiteralCString( \
"CREATE TABLE moz_places_extra (" \
" place_id INTEGER PRIMARY KEY NOT NULL" \
", sync_json TEXT" \
", FOREIGN KEY (place_id) REFERENCES moz_places(id) ON DELETE CASCADE " \
")")
#define CREATE_MOZ_HISTORYVISITS_EXTRA \
nsLiteralCString( \
"CREATE TABLE moz_historyvisits_extra (" \
" visit_id INTEGER PRIMARY KEY NOT NULL" \
", sync_json TEXT" \
", FOREIGN KEY (visit_id) REFERENCES moz_historyvisits(id) ON " \
" DELETE CASCADE" \
")")
#define CREATE_MOZ_INPUTHISTORY \
nsLiteralCString( \
"CREATE TABLE moz_inputhistory (" \
" place_id INTEGER NOT NULL" \
", input LONGVARCHAR NOT NULL" \
", use_count INTEGER" \
", PRIMARY KEY (place_id, input)" \
")")
// Note: flags, expiration, type, dateAdded and lastModified should be
// considered deprecated but are kept to ease backwards compatibility.
#define CREATE_MOZ_ANNOS \
nsLiteralCString( \
"CREATE TABLE moz_annos (" \
" id INTEGER PRIMARY KEY" \
", place_id INTEGER NOT NULL" \
", anno_attribute_id INTEGER" \
", content LONGVARCHAR" \
", flags INTEGER DEFAULT 0" \
", expiration INTEGER DEFAULT 0" \
", type INTEGER DEFAULT 0" \
", dateAdded INTEGER DEFAULT 0" \
", lastModified INTEGER DEFAULT 0" \
")")
#define CREATE_MOZ_ANNO_ATTRIBUTES \
nsLiteralCString( \
"CREATE TABLE moz_anno_attributes (" \
" id INTEGER PRIMARY KEY" \
", name VARCHAR(32) UNIQUE NOT NULL" \
")")
#define CREATE_MOZ_ITEMS_ANNOS \
nsLiteralCString( \
"CREATE TABLE moz_items_annos (" \
" id INTEGER PRIMARY KEY" \
", item_id INTEGER NOT NULL" \
", anno_attribute_id INTEGER" \
", content LONGVARCHAR" \
", flags INTEGER DEFAULT 0" \
", expiration INTEGER DEFAULT 0" \
", type INTEGER DEFAULT 0" \
", dateAdded INTEGER DEFAULT 0" \
", lastModified INTEGER DEFAULT 0" \
")")
#define CREATE_MOZ_BOOKMARKS \
nsLiteralCString( \
"CREATE TABLE moz_bookmarks (" \
" id INTEGER PRIMARY KEY" \
", type INTEGER" \
", fk INTEGER DEFAULT NULL" /* place_id */ \
", parent INTEGER" \
", position INTEGER" \
", title LONGVARCHAR" \
", keyword_id INTEGER" \
", folder_type TEXT" \
", dateAdded INTEGER" \
", lastModified INTEGER" \
", guid TEXT" /* The sync status is determined from the change source. \
We set this to SYNC_STATUS_NEW = 1 for new local \
bookmarks, and SYNC_STATUS_NORMAL = 2 for bookmarks \
from other devices. Uploading a local bookmark for the \
first time changes its status to SYNC_STATUS_NORMAL. \
For bookmarks restored from a backup, we set \
SYNC_STATUS_UNKNOWN = 0, indicating that Sync should \
reconcile them with bookmarks on the server. If Sync is \
disconnected or never set up, all bookmarks will stay \
in SYNC_STATUS_NEW. \
*/ \
", syncStatus INTEGER NOT NULL DEFAULT 0" /* This field is incremented \
for every bookmark change \
that should trigger a sync. \
It's a counter instead of a \
Boolean so that we can \
track changes made during a \
sync, and queue them for \
the next sync. Changes made \
by Sync don't bump the \
counter, to avoid sync \
loops. If Sync is \
disconnected, we'll reset \
the counter to 1 for all \
bookmarks. \
*/ \
", syncChangeCounter INTEGER NOT NULL DEFAULT 1" \
")")
// This table stores tombstones for bookmarks with SYNC_STATUS_NORMAL. We
// upload tombstones during a sync, and delete them from this table on success.
// If Sync is disconnected, we'll delete all stored tombstones. If Sync is
// never set up, we'll never write new tombstones, since all bookmarks will stay
// in SYNC_STATUS_NEW.
#define CREATE_MOZ_BOOKMARKS_DELETED \
nsLiteralCString( \
"CREATE TABLE moz_bookmarks_deleted (" \
" guid TEXT PRIMARY KEY" \
", dateRemoved INTEGER NOT NULL DEFAULT 0" \
")")
#define CREATE_MOZ_KEYWORDS \
nsLiteralCString( \
"CREATE TABLE moz_keywords (" \
" id INTEGER PRIMARY KEY AUTOINCREMENT" \
", keyword TEXT UNIQUE" \
", place_id INTEGER" \
", post_data TEXT" \
")")
#define CREATE_MOZ_ORIGINS \
nsLiteralCString( \
"CREATE TABLE moz_origins ( " \
"id INTEGER PRIMARY KEY, " \
"prefix TEXT NOT NULL, " \
"host TEXT NOT NULL, " \
"frecency INTEGER NOT NULL, " \
"recalc_frecency INTEGER NOT NULL DEFAULT 0, " \
"alt_frecency INTEGER, " \
"recalc_alt_frecency INTEGER NOT NULL DEFAULT 0, " \
"UNIQUE (prefix, host) " \
")")
// Note: this should be kept up-to-date with the definition in
// nsPlacesAutoComplete.js.
#define CREATE_MOZ_OPENPAGES_TEMP \
nsLiteralCString( \
"CREATE TEMP TABLE moz_openpages_temp (" \
" url TEXT" \
", userContextId INTEGER" \
", open_count INTEGER" \
", PRIMARY KEY (url, userContextId)" \
")")
// This table is used to remove orphan origins after pages are removed from
// moz_places. Insertions are made by moz_places_afterdelete_trigger.
// This allows for more performant handling of batch removals, since we'll look
// for orphan origins only once, instead of doing it for each page removal.
// The downside of this approach is that after the removal is complete the
// consumer must remember to also delete from this table, and a trigger will
// take care of orphans.
#define CREATE_UPDATEORIGINSDELETE_TEMP \
nsLiteralCString( \
"CREATE TEMP TABLE moz_updateoriginsdelete_temp ( " \
" prefix TEXT NOT NULL, " \
" host TEXT NOT NULL, " \
" PRIMARY KEY (prefix, host) " \
") WITHOUT ROWID")
// This table would not be strictly needed for functionality since it's just
// mimicking moz_places, though it's great for database portability.
// With this we don't have to take care into account a bunch of database
// mismatch cases, where places.sqlite could be mixed up with a favicons.sqlite
// created with a different places.sqlite (not just in case of a user messing
// up with the profile, but also in case of corruption).
#define CREATE_MOZ_PAGES_W_ICONS \
nsLiteralCString( \
"CREATE TABLE moz_pages_w_icons ( " \
"id INTEGER PRIMARY KEY, " \
"page_url TEXT NOT NULL, " \
"page_url_hash INTEGER NOT NULL " \
") ")
// This table retains the icons data. The hashes url is "fixed" (thus the scheme
// and www are trimmed in most cases) so we can quickly query for root icon urls
// like "domain/favicon.ico".
// We are considering squared icons for simplicity, so storing only one size.
// For svg payloads, width will be set to 65535 (UINT16_MAX).
#define CREATE_MOZ_ICONS \
nsLiteralCString( \
"CREATE TABLE moz_icons ( " \
"id INTEGER PRIMARY KEY, " \
"icon_url TEXT NOT NULL, " \
"fixed_icon_url_hash INTEGER NOT NULL, " \
"width INTEGER NOT NULL DEFAULT 0, " \
"root INTEGER NOT NULL DEFAULT 0, " \
"color INTEGER, " \
"expire_ms INTEGER NOT NULL DEFAULT 0, " \
"data BLOB " \
") ")
// This table maintains relations between icons and pages.
// Each page can have multiple icons, and each icon can be used by multiple
// pages.
#define CREATE_MOZ_ICONS_TO_PAGES \
nsLiteralCString( \
"CREATE TABLE moz_icons_to_pages ( " \
"page_id INTEGER NOT NULL, " \
"icon_id INTEGER NOT NULL, " \
"expire_ms INTEGER NOT NULL DEFAULT 0, " \
"PRIMARY KEY (page_id, icon_id), " \
"FOREIGN KEY (page_id) REFERENCES moz_pages_w_icons ON DELETE CASCADE, " \
"FOREIGN KEY (icon_id) REFERENCES moz_icons ON DELETE CASCADE " \
") WITHOUT ROWID ")
// This table holds key-value metadata for Places and its consumers. Sync stores
// the sync IDs for the bookmarks and history collections in this table, and the
// last sync time for history.
#define CREATE_MOZ_META \
nsLiteralCString( \
"CREATE TABLE moz_meta (" \
"key TEXT PRIMARY KEY, " \
"value NOT NULL" \
") WITHOUT ROWID ")
// This table holds history interactions that will be used to achieve improved
// history recalls.
#define CREATE_MOZ_PLACES_METADATA \
nsLiteralCString( \
"CREATE TABLE moz_places_metadata (" \
"id INTEGER PRIMARY KEY, " \
"place_id INTEGER NOT NULL, " \
"referrer_place_id INTEGER, " \
"created_at INTEGER NOT NULL DEFAULT 0, " \
"updated_at INTEGER NOT NULL DEFAULT 0, " \
"total_view_time INTEGER NOT NULL DEFAULT 0, " \
"typing_time INTEGER NOT NULL DEFAULT 0, " \
"key_presses INTEGER NOT NULL DEFAULT 0, " \
"scrolling_time INTEGER NOT NULL DEFAULT 0, " \
"scrolling_distance INTEGER NOT NULL DEFAULT 0, " \
"document_type INTEGER NOT NULL DEFAULT 0, " \
"search_query_id INTEGER, " \
"FOREIGN KEY (place_id) REFERENCES moz_places(id) ON DELETE CASCADE, " \
"FOREIGN KEY (referrer_place_id) REFERENCES moz_places(id) ON DELETE " \
"CASCADE, " \
"FOREIGN KEY(search_query_id) REFERENCES " \
"moz_places_metadata_search_queries(id) ON DELETE CASCADE " \
"CHECK(place_id != referrer_place_id) " \
")")
#define CREATE_MOZ_PLACES_METADATA_SEARCH_QUERIES \
nsLiteralCString( \
"CREATE TABLE IF NOT EXISTS moz_places_metadata_search_queries ( " \
"id INTEGER PRIMARY KEY, " \
"terms TEXT NOT NULL UNIQUE " \
")")
#define CREATE_MOZ_PREVIEWS_TOMBSTONES \
nsLiteralCString( \
"CREATE TABLE IF NOT EXISTS moz_previews_tombstones ( " \
" hash TEXT PRIMARY KEY " \
") WITHOUT ROWID")
#endif // __nsPlacesTables_h__
|
