userdata.sql (14624B)
1 -- 101 2 3 -- Copyright (c) 2009 Center for History and New Media 4 -- George Mason University, Fairfax, Virginia, USA 5 -- http://zotero.org 6 -- 7 -- This file is part of Zotero. 8 -- 9 -- Zotero is free software: you can redistribute it and/or modify 10 -- it under the terms of the GNU Affero General Public License as published by 11 -- the Free Software Foundation, either version 3 of the License, or 12 -- (at your option) any later version. 13 -- 14 -- Zotero is distributed in the hope that it will be useful, 15 -- but WITHOUT ANY WARRANTY; without even the implied warranty of 16 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 17 -- GNU Affero General Public License for more details. 18 -- 19 -- You should have received a copy of the GNU Affero General Public License 20 -- along with Zotero. If not, see <http://www.gnu.org/licenses/>. 21 22 23 -- This file creates tables containing user-specific data for new users -- 24 -- any changes made here must be mirrored in transition steps in schema.js::_migrateSchema() 25 26 27 CREATE TABLE version ( 28 schema TEXT PRIMARY KEY, 29 version INT NOT NULL 30 ); 31 CREATE INDEX schema ON version(schema); 32 33 -- Settings that have to be tied to the local database rather than the profile directory 34 CREATE TABLE settings ( 35 setting TEXT, 36 key TEXT, 37 value, 38 PRIMARY KEY (setting, key) 39 ); 40 41 -- Settings that get synced between Zotero installations 42 CREATE TABLE syncedSettings ( 43 setting TEXT NOT NULL, 44 libraryID INT NOT NULL, 45 value NOT NULL, 46 version INT NOT NULL DEFAULT 0, 47 synced INT NOT NULL DEFAULT 0, 48 PRIMARY KEY (setting, libraryID), 49 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 50 ); 51 52 -- Primary data applicable to all items 53 CREATE TABLE items ( 54 itemID INTEGER PRIMARY KEY, 55 itemTypeID INT NOT NULL, 56 dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 57 dateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 58 clientDateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 59 libraryID INT NOT NULL, 60 key TEXT NOT NULL, 61 version INT NOT NULL DEFAULT 0, 62 synced INT NOT NULL DEFAULT 0, 63 UNIQUE (libraryID, key), 64 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 65 ); 66 CREATE INDEX items_synced ON items(synced); 67 68 CREATE TABLE itemDataValues ( 69 valueID INTEGER PRIMARY KEY, 70 value UNIQUE 71 ); 72 73 -- Type-specific data for individual items 74 CREATE TABLE itemData ( 75 itemID INT, 76 fieldID INT, 77 valueID, 78 PRIMARY KEY (itemID, fieldID), 79 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 80 FOREIGN KEY (fieldID) REFERENCES fieldsCombined(fieldID), 81 FOREIGN KEY (valueID) REFERENCES itemDataValues(valueID) 82 ); 83 CREATE INDEX itemData_fieldID ON itemData(fieldID); 84 85 -- Note data for note and attachment items 86 CREATE TABLE itemNotes ( 87 itemID INTEGER PRIMARY KEY, 88 parentItemID INT, 89 note TEXT, 90 title TEXT, 91 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 92 FOREIGN KEY (parentItemID) REFERENCES items(itemID) ON DELETE CASCADE 93 ); 94 CREATE INDEX itemNotes_parentItemID ON itemNotes(parentItemID); 95 96 -- Metadata for attachment items 97 CREATE TABLE itemAttachments ( 98 itemID INTEGER PRIMARY KEY, 99 parentItemID INT, 100 linkMode INT, 101 contentType TEXT, 102 charsetID INT, 103 path TEXT, 104 syncState INT DEFAULT 0, 105 storageModTime INT, 106 storageHash TEXT, 107 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 108 FOREIGN KEY (parentItemID) REFERENCES items(itemID) ON DELETE CASCADE, 109 FOREIGN KEY (charsetID) REFERENCES charsets(charsetID) ON DELETE SET NULL 110 ); 111 CREATE INDEX itemAttachments_parentItemID ON itemAttachments(parentItemID); 112 CREATE INDEX itemAttachments_charsetID ON itemAttachments(charsetID); 113 CREATE INDEX itemAttachments_contentType ON itemAttachments(contentType); 114 CREATE INDEX itemAttachments_syncState ON itemAttachments(syncState); 115 116 CREATE TABLE tags ( 117 tagID INTEGER PRIMARY KEY, 118 name TEXT NOT NULL UNIQUE 119 ); 120 121 CREATE TABLE itemRelations ( 122 itemID INT NOT NULL, 123 predicateID INT NOT NULL, 124 object TEXT NOT NULL, 125 PRIMARY KEY (itemID, predicateID, object), 126 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 127 FOREIGN KEY (predicateID) REFERENCES relationPredicates(predicateID) ON DELETE CASCADE 128 ); 129 CREATE INDEX itemRelations_predicateID ON itemRelations(predicateID); 130 CREATE INDEX itemRelations_object ON itemRelations(object); 131 132 CREATE TABLE itemTags ( 133 itemID INT NOT NULL, 134 tagID INT NOT NULL, 135 type INT NOT NULL, 136 PRIMARY KEY (itemID, tagID), 137 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 138 FOREIGN KEY (tagID) REFERENCES tags(tagID) ON DELETE CASCADE 139 ); 140 CREATE INDEX itemTags_tagID ON itemTags(tagID); 141 142 CREATE TABLE creators ( 143 creatorID INTEGER PRIMARY KEY, 144 firstName TEXT, 145 lastName TEXT, 146 fieldMode INT, 147 UNIQUE (lastName, firstName, fieldMode) 148 ); 149 150 CREATE TABLE itemCreators ( 151 itemID INT NOT NULL, 152 creatorID INT NOT NULL, 153 creatorTypeID INT NOT NULL DEFAULT 1, 154 orderIndex INT NOT NULL DEFAULT 0, 155 PRIMARY KEY (itemID, creatorID, creatorTypeID, orderIndex), 156 UNIQUE (itemID, orderIndex), 157 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 158 FOREIGN KEY (creatorID) REFERENCES creators(creatorID) ON DELETE CASCADE, 159 FOREIGN KEY (creatorTypeID) REFERENCES creatorTypes(creatorTypeID) 160 ); 161 CREATE INDEX itemCreators_creatorTypeID ON itemCreators(creatorTypeID); 162 163 CREATE TABLE collections ( 164 collectionID INTEGER PRIMARY KEY, 165 collectionName TEXT NOT NULL, 166 parentCollectionID INT DEFAULT NULL, 167 clientDateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 168 libraryID INT NOT NULL, 169 key TEXT NOT NULL, 170 version INT NOT NULL DEFAULT 0, 171 synced INT NOT NULL DEFAULT 0, 172 UNIQUE (libraryID, key), 173 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE, 174 FOREIGN KEY (parentCollectionID) REFERENCES collections(collectionID) ON DELETE CASCADE 175 ); 176 CREATE INDEX collections_synced ON collections(synced); 177 178 CREATE TABLE collectionItems ( 179 collectionID INT NOT NULL, 180 itemID INT NOT NULL, 181 orderIndex INT NOT NULL DEFAULT 0, 182 PRIMARY KEY (collectionID, itemID), 183 FOREIGN KEY (collectionID) REFERENCES collections(collectionID) ON DELETE CASCADE, 184 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE 185 ); 186 CREATE INDEX collectionItems_itemID ON collectionItems(itemID); 187 188 CREATE TABLE collectionRelations ( 189 collectionID INT NOT NULL, 190 predicateID INT NOT NULL, 191 object TEXT NOT NULL, 192 PRIMARY KEY (collectionID, predicateID, object), 193 FOREIGN KEY (collectionID) REFERENCES collections(collectionID) ON DELETE CASCADE, 194 FOREIGN KEY (predicateID) REFERENCES relationPredicates(predicateID) ON DELETE CASCADE 195 ); 196 CREATE INDEX collectionRelations_predicateID ON collectionRelations(predicateID); 197 CREATE INDEX collectionRelations_object ON collectionRelations(object); 198 199 CREATE TABLE feeds ( 200 libraryID INTEGER PRIMARY KEY, 201 name TEXT NOT NULL, 202 url TEXT NOT NULL UNIQUE, 203 lastUpdate TIMESTAMP, 204 lastCheck TIMESTAMP, 205 lastCheckError TEXT, 206 cleanupReadAfter INT, 207 cleanupUnreadAfter INT, 208 refreshInterval INT, 209 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 210 ); 211 212 CREATE TABLE feedItems ( 213 itemID INTEGER PRIMARY KEY, 214 guid TEXT NOT NULL UNIQUE, 215 readTime TIMESTAMP, 216 translatedTime TIMESTAMP, 217 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE 218 ); 219 220 CREATE TABLE savedSearches ( 221 savedSearchID INTEGER PRIMARY KEY, 222 savedSearchName TEXT NOT NULL, 223 clientDateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 224 libraryID INT NOT NULL, 225 key TEXT NOT NULL, 226 version INT NOT NULL DEFAULT 0, 227 synced INT NOT NULL DEFAULT 0, 228 UNIQUE (libraryID, key), 229 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 230 ); 231 CREATE INDEX savedSearches_synced ON savedSearches(synced); 232 233 CREATE TABLE savedSearchConditions ( 234 savedSearchID INT NOT NULL, 235 searchConditionID INT NOT NULL, 236 condition TEXT NOT NULL, 237 operator TEXT, 238 value TEXT, 239 required NONE, 240 PRIMARY KEY (savedSearchID, searchConditionID), 241 FOREIGN KEY (savedSearchID) REFERENCES savedSearches(savedSearchID) ON DELETE CASCADE 242 ); 243 244 CREATE TABLE deletedItems ( 245 itemID INTEGER PRIMARY KEY, 246 dateDeleted DEFAULT CURRENT_TIMESTAMP NOT NULL, 247 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE 248 ); 249 CREATE INDEX deletedItems_dateDeleted ON deletedItems(dateDeleted); 250 251 CREATE TABLE libraries ( 252 libraryID INTEGER PRIMARY KEY, 253 type TEXT NOT NULL, 254 editable INT NOT NULL, 255 filesEditable INT NOT NULL, 256 version INT NOT NULL DEFAULT 0, 257 storageVersion INT NOT NULL DEFAULT 0, 258 lastSync INT NOT NULL DEFAULT 0, 259 archived INT NOT NULL DEFAULT 0 260 ); 261 262 CREATE TABLE users ( 263 userID INTEGER PRIMARY KEY, 264 username TEXT NOT NULL 265 ); 266 267 CREATE TABLE groups ( 268 groupID INTEGER PRIMARY KEY, 269 libraryID INT NOT NULL UNIQUE, 270 name TEXT NOT NULL, 271 description TEXT NOT NULL, 272 version INT NOT NULL, 273 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 274 ); 275 276 CREATE TABLE groupItems ( 277 itemID INTEGER PRIMARY KEY, 278 createdByUserID INT, 279 lastModifiedByUserID INT, 280 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE, 281 FOREIGN KEY (createdByUserID) REFERENCES users(userID) ON DELETE SET NULL, 282 FOREIGN KEY (lastModifiedByUserID) REFERENCES users(userID) ON DELETE SET NULL 283 ); 284 285 CREATE TABLE publicationsItems ( 286 itemID INTEGER PRIMARY KEY 287 ); 288 289 CREATE TABLE fulltextItems ( 290 itemID INTEGER PRIMARY KEY, 291 indexedPages INT, 292 totalPages INT, 293 indexedChars INT, 294 totalChars INT, 295 version INT NOT NULL DEFAULT 0, 296 synced INT NOT NULL DEFAULT 0, 297 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE 298 ); 299 CREATE INDEX fulltextItems_synced ON fulltextItems(synced); 300 CREATE INDEX fulltextItems_version ON fulltextItems(version); 301 302 CREATE TABLE fulltextWords ( 303 wordID INTEGER PRIMARY KEY, 304 word TEXT UNIQUE 305 ); 306 307 CREATE TABLE fulltextItemWords ( 308 wordID INT, 309 itemID INT, 310 PRIMARY KEY (wordID, itemID), 311 FOREIGN KEY (wordID) REFERENCES fulltextWords(wordID), 312 FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE 313 ); 314 CREATE INDEX fulltextItemWords_itemID ON fulltextItemWords(itemID); 315 316 CREATE TABLE syncCache ( 317 libraryID INT NOT NULL, 318 key TEXT NOT NULL, 319 syncObjectTypeID INT NOT NULL, 320 version INT NOT NULL, 321 data TEXT, 322 PRIMARY KEY (libraryID, key, syncObjectTypeID, version), 323 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE, 324 FOREIGN KEY (syncObjectTypeID) REFERENCES syncObjectTypes(syncObjectTypeID) 325 ); 326 327 CREATE TABLE syncDeleteLog ( 328 syncObjectTypeID INT NOT NULL, 329 libraryID INT NOT NULL, 330 key TEXT NOT NULL, 331 dateDeleted TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, 332 UNIQUE (syncObjectTypeID, libraryID, key), 333 FOREIGN KEY (syncObjectTypeID) REFERENCES syncObjectTypes(syncObjectTypeID), 334 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 335 ); 336 337 CREATE TABLE syncQueue ( 338 libraryID INT NOT NULL, 339 key TEXT NOT NULL, 340 syncObjectTypeID INT NOT NULL, 341 lastCheck TIMESTAMP, 342 tries INT, 343 PRIMARY KEY (libraryID, key, syncObjectTypeID), 344 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE, 345 FOREIGN KEY (syncObjectTypeID) REFERENCES syncObjectTypes(syncObjectTypeID) ON DELETE CASCADE 346 ); 347 348 CREATE TABLE storageDeleteLog ( 349 libraryID INT NOT NULL, 350 key TEXT NOT NULL, 351 dateDeleted TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, 352 PRIMARY KEY (libraryID, key), 353 FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE 354 ); 355 356 CREATE TABLE annotations ( 357 annotationID INTEGER PRIMARY KEY, 358 itemID INT NOT NULL, 359 parent TEXT, 360 textNode INT, 361 offset INT, 362 x INT, 363 y INT, 364 cols INT, 365 rows INT, 366 text TEXT, 367 collapsed BOOL, 368 dateModified DATE, 369 FOREIGN KEY (itemID) REFERENCES itemAttachments(itemID) ON DELETE CASCADE 370 ); 371 CREATE INDEX annotations_itemID ON annotations(itemID); 372 373 CREATE TABLE highlights ( 374 highlightID INTEGER PRIMARY KEY, 375 itemID INT NOT NULL, 376 startParent TEXT, 377 startTextNode INT, 378 startOffset INT, 379 endParent TEXT, 380 endTextNode INT, 381 endOffset INT, 382 dateModified DATE, 383 FOREIGN KEY (itemID) REFERENCES itemAttachments(itemID) ON DELETE CASCADE 384 ); 385 CREATE INDEX highlights_itemID ON highlights(itemID); 386 387 CREATE TABLE proxies ( 388 proxyID INTEGER PRIMARY KEY, 389 multiHost INT, 390 autoAssociate INT, 391 scheme TEXT 392 ); 393 394 CREATE TABLE proxyHosts ( 395 hostID INTEGER PRIMARY KEY, 396 proxyID INTEGER, 397 hostname TEXT, 398 FOREIGN KEY (proxyID) REFERENCES proxies(proxyID) 399 ); 400 CREATE INDEX proxyHosts_proxyID ON proxyHosts(proxyID); 401 402 CREATE TABLE relationPredicates ( 403 predicateID INTEGER PRIMARY KEY, 404 predicate TEXT UNIQUE 405 ); 406 407 -- These shouldn't be used yet 408 CREATE TABLE customItemTypes ( 409 customItemTypeID INTEGER PRIMARY KEY, 410 typeName TEXT, 411 label TEXT, 412 display INT DEFAULT 1, -- 0 == hide, 1 == display, 2 == primary 413 icon TEXT 414 ); 415 416 CREATE TABLE customFields ( 417 customFieldID INTEGER PRIMARY KEY, 418 fieldName TEXT, 419 label TEXT 420 ); 421 422 CREATE TABLE customItemTypeFields ( 423 customItemTypeID INT NOT NULL, 424 fieldID INT, 425 customFieldID INT, 426 hide INT NOT NULL, 427 orderIndex INT NOT NULL, 428 PRIMARY KEY (customItemTypeID, orderIndex), 429 FOREIGN KEY (customItemTypeID) REFERENCES customItemTypes(customItemTypeID), 430 FOREIGN KEY (fieldID) REFERENCES fields(fieldID), 431 FOREIGN KEY (customFieldID) REFERENCES customFields(customFieldID) 432 ); 433 CREATE INDEX customItemTypeFields_fieldID ON customItemTypeFields(fieldID); 434 CREATE INDEX customItemTypeFields_customFieldID ON customItemTypeFields(customFieldID); 435 436 CREATE TABLE customBaseFieldMappings ( 437 customItemTypeID INT, 438 baseFieldID INT, 439 customFieldID INT, 440 PRIMARY KEY (customItemTypeID, baseFieldID, customFieldID), 441 FOREIGN KEY (customItemTypeID) REFERENCES customItemTypes(customItemTypeID), 442 FOREIGN KEY (baseFieldID) REFERENCES fields(fieldID), 443 FOREIGN KEY (customFieldID) REFERENCES customFields(customFieldID) 444 ); 445 CREATE INDEX customBaseFieldMappings_baseFieldID ON customBaseFieldMappings(baseFieldID); 446 CREATE INDEX customBaseFieldMappings_customFieldID ON customBaseFieldMappings(customFieldID); 447 448 CREATE TABLE translatorCache ( 449 fileName TEXT PRIMARY KEY, 450 metadataJSON TEXT, 451 lastModifiedTime INT 452 );