instrumentation/next-share/BaseLib/schema_sdb_v5.sql

   1 -- Tribler SQLite Database
   2 -- Version: 5
   3 --
   4 -- History:
   5 --   v1: Published as part of Tribler 4.5
   6 --   v2: Published as part of Tribler 5.0
   7 --   v3: Published as part of Next-Share M16
   8 --   v4: Published as part of Tribler 5.2
   9 --   v5: Published as part of Next-Share M30 for subtitles integration
  10
  11 --
  12 -- See Tribler/Core/CacheDB/sqlitecachedb.py updateDB() for exact version diffs.
  13 --
  14 -- v4: ChannelCast is an extension of the concept of ModerationCast, with
  15 --     an additional integrity measure. 'torrenthash' field is used to protect
  16 --     the integrity of the torrent file created by the publisher, from fake-
  17 --     tracker attack, by including sha1 hash of the dictionary corresponding
  18 --     to the entire torrent.
  19 --
  20 --     'InvertedIndex' table is used for precise keyword matching than
  21 --     substring search that was used previously.
  22
  23 BEGIN TRANSACTION create_table;
  24
  25 ----------------------------------------
  26
  27 CREATE TABLE BarterCast (
  28   peer_id_from  integer,
  29   peer_id_to    integer,
  30   downloaded    numeric,
  31   uploaded      numeric,
  32   last_seen     numeric,
  33   value         numeric
  34 );
  35
  36 CREATE UNIQUE INDEX bartercast_idx
  37   ON BarterCast
  38   (peer_id_from, peer_id_to);
  39
  40 ----------------------------------------
  41
  42 CREATE TABLE Category (
  43   category_id    integer PRIMARY KEY NOT NULL,
  44   name           text NOT NULL,
  45   description    text
  46 );
  47
  48 ----------------------------------------
  49
  50 CREATE TABLE MyInfo (
  51   entry  PRIMARY KEY,
  52   value  text
  53 );
  54
  55 ----------------------------------------
  56
  57 CREATE TABLE MyPreference (
  58   torrent_id     integer PRIMARY KEY NOT NULL,
  59   destination_path text NOT NULL,
  60   progress       numeric,
  61   creation_time  integer NOT NULL,
  62   -- V2: Patch for BuddyCast 4
  63   click_position INTEGER DEFAULT -1,
  64   reranking_strategy INTEGER DEFAULT -1
  65 );
  66
  67 ----------------------------------------
  68
  69 CREATE TABLE Peer (
  70   peer_id              integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  71   permid               text NOT NULL,
  72   name                 text,
  73   ip                   text,
  74   port                 integer,
  75   thumbnail            text,
  76   oversion             integer,
  77   similarity           numeric DEFAULT 0,
  78   friend               integer DEFAULT 0,
  79   superpeer            integer DEFAULT 0,
  80   last_seen            numeric DEFAULT 0,
  81   last_connected       numeric,
  82   last_buddycast       numeric,
  83   connected_times      integer DEFAULT 0,
  84   buddycast_times      integer DEFAULT 0,
  85   num_peers            integer,
  86   num_torrents         integer,
  87   num_prefs            integer,
  88   num_queries          integer,
  89   -- V3: Addition for local peer discovery
  90   is_local             integer DEFAULT 0
  91 );
  92
  93 CREATE UNIQUE INDEX permid_idx
  94   ON Peer
  95   (permid);
  96
  97 CREATE INDEX Peer_name_idx
  98   ON Peer
  99   (name);
 100
 101 CREATE INDEX Peer_ip_idx
 102   ON Peer
 103   (ip);
 104
 105 CREATE INDEX Peer_similarity_idx
 106   ON Peer
 107   (similarity);
 108
 109 CREATE INDEX Peer_last_seen_idx
 110   ON Peer
 111   (last_seen);
 112
 113 CREATE INDEX Peer_last_connected_idx
 114   ON Peer
 115   (last_connected);
 116
 117 CREATE INDEX Peer_num_peers_idx
 118   ON Peer
 119   (num_peers);
 120
 121 CREATE INDEX Peer_num_torrents_idx
 122   ON Peer
 123   (num_torrents);
 124
 125 ----------------------------------------
 126
 127 CREATE TABLE Preference (
 128   peer_id     integer NOT NULL,
 129   torrent_id  integer NOT NULL,
 130   -- V2: Patch for BuddyCast 4
 131   click_position INTEGER DEFAULT -1,
 132   reranking_strategy INTEGER DEFAULT -1
 133 );
 134
 135 CREATE INDEX Preference_peer_id_idx
 136   ON Preference
 137   (peer_id);
 138
 139 CREATE INDEX Preference_torrent_id_idx
 140   ON Preference
 141   (torrent_id);
 142
 143 CREATE UNIQUE INDEX pref_idx
 144   ON Preference
 145   (peer_id, torrent_id);
 146
 147 ----------------------------------------
 148
 149 CREATE TABLE Torrent (
 150   torrent_id       integer PRIMARY KEY AUTOINCREMENT NOT NULL,
 151   infohash                 text NOT NULL,
 152   name             text,
 153   torrent_file_name text,
 154   length           integer,
 155   creation_date    integer,
 156   num_files        integer,
 157   thumbnail        integer,
 158   insert_time      numeric,
 159   secret           integer,
 160   relevance        numeric DEFAULT 0,
 161   source_id        integer,
 162   category_id      integer,
 163   status_id        integer,
 164   num_seeders      integer,
 165   num_leechers     integer,
 166   comment          text
 167 );
 168
 169 CREATE UNIQUE INDEX infohash_idx
 170   ON Torrent
 171   (infohash);
 172
 173 CREATE INDEX Torrent_length_idx
 174   ON Torrent
 175   (length);
 176
 177 CREATE INDEX Torrent_creation_date_idx
 178   ON Torrent
 179   (creation_date);
 180
 181 CREATE INDEX Torrent_relevance_idx
 182   ON Torrent
 183   (relevance);
 184
 185 CREATE INDEX Torrent_num_seeders_idx
 186   ON Torrent
 187   (num_seeders);
 188
 189 CREATE INDEX Torrent_num_leechers_idx
 190   ON Torrent
 191   (num_leechers);
 192
 193 CREATE INDEX Torrent_name_idx
 194   ON Torrent
 195   (name);
 196
 197 ----------------------------------------
 198
 199 CREATE TABLE TorrentSource (
 200   source_id    integer PRIMARY KEY NOT NULL,
 201   name         text NOT NULL,
 202   description  text
 203 );
 204
 205 CREATE UNIQUE INDEX torrent_source_idx
 206   ON TorrentSource
 207   (name);
 208
 209 ----------------------------------------
 210
 211 CREATE TABLE TorrentStatus (
 212   status_id    integer PRIMARY KEY NOT NULL,
 213   name         text NOT NULL,
 214   description  text
 215 );
 216
 217 ----------------------------------------
 218
 219 CREATE TABLE TorrentTracker (
 220   torrent_id   integer NOT NULL,
 221   tracker      text NOT NULL,
 222   announce_tier    integer,
 223   ignored_times    integer,
 224   retried_times    integer,
 225   last_check       numeric
 226 );
 227
 228 CREATE UNIQUE INDEX torrent_tracker_idx
 229   ON TorrentTracker
 230   (torrent_id, tracker);
 231
 232 ----------------------------------------
 233
 234 CREATE VIEW SuperPeer AS SELECT * FROM Peer WHERE superpeer=1;
 235
 236 CREATE VIEW Friend AS SELECT * FROM Peer WHERE friend=1;
 237
 238 CREATE VIEW CollectedTorrent AS SELECT * FROM Torrent WHERE torrent_file_name IS NOT NULL;
 239
 240
 241 -- V2: Patch for VoteCast
 242
 243 CREATE TABLE VoteCast (
 244 mod_id text,
 245 voter_id text,
 246 vote integer,
 247 time_stamp integer
 248 );
 249
 250 CREATE INDEX mod_id_idx
 251 on VoteCast
 252 (mod_id);
 253
 254 CREATE INDEX voter_id_idx
 255 on VoteCast
 256 (voter_id);
 257
 258 CREATE UNIQUE INDEX votecast_idx
 259 ON VoteCast
 260 (mod_id, voter_id);
 261
 262
 263 -- V2: Patch for BuddyCast 4
 264
 265 CREATE TABLE ClicklogSearch (
 266                      peer_id INTEGER DEFAULT 0,
 267                      torrent_id INTEGER DEFAULT 0,
 268                      term_id INTEGER DEFAULT 0,
 269                      term_order INTEGER DEFAULT 0
 270                      );
 271 CREATE INDEX idx_search_term ON ClicklogSearch (term_id);
 272 CREATE INDEX idx_search_torrent ON ClicklogSearch (torrent_id);
 273
 274 CREATE TABLE ClicklogTerm (
 275                     term_id INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 0,
 276                     term VARCHAR(255) NOT NULL,
 277                     times_seen INTEGER DEFAULT 0 NOT NULL
 278                     );
 279 CREATE INDEX idx_terms_term ON ClicklogTerm(term);
 280
 281
 282
 283
 284
 285 --v4: Path for BuddyCast 5. Adding Popularity table
 286
 287 CREATE TABLE Popularity (
 288                          torrent_id INTEGER,
 289                          peer_id INTEGER,
 290                          msg_receive_time NUMERIC,
 291                          size_calc_age NUMERIC,
 292                          num_seeders INTEGER DEFAULT 0,
 293                          num_leechers INTEGER DEFAULT 0,
 294                          num_of_sources INTEGER DEFAULT 0
 295                      );
 296
 297 CREATE INDEX Message_receive_time_idx
 298   ON Popularity
 299    (msg_receive_time);
 300
 301 CREATE INDEX Size_calc_age_idx
 302   ON Popularity
 303    (size_calc_age);
 304
 305 CREATE INDEX Number_of_seeders_idx
 306   ON Popularity
 307    (num_seeders);
 308
 309 CREATE INDEX Number_of_leechers_idx
 310   ON Popularity
 311    (num_leechers);
 312
 313 CREATE UNIQUE INDEX Popularity_idx
 314   ON Popularity
 315    (torrent_id, peer_id, msg_receive_time);
 316
 317
 318
 319 -- v4: Patch for ChannelCast, Search
 320
 321 CREATE TABLE ChannelCast (
 322 publisher_id text,
 323 publisher_name text,
 324 infohash text,
 325 torrenthash text,
 326 torrentname text,
 327 time_stamp integer,
 328 signature text
 329 );
 330
 331 CREATE INDEX pub_id_idx
 332 on ChannelCast
 333 (publisher_id);
 334
 335 CREATE INDEX pub_name_idx
 336 on ChannelCast
 337 (publisher_name);
 338
 339 CREATE INDEX infohash_ch_idx
 340 on ChannelCast
 341 (infohash);
 342
 343 ----------------------------------------
 344
 345 CREATE TABLE InvertedIndex (
 346 word               text NOT NULL,
 347 torrent_id         integer
 348 );
 349
 350 CREATE INDEX word_idx
 351 on InvertedIndex
 352 (word);
 353
 354 CREATE UNIQUE INDEX invertedindex_idx
 355 on InvertedIndex
 356 (word,torrent_id);
 357 --------------------------------------
 358
 359 -- v5 Subtitles DB
 360 CREATE TABLE Metadata (
 361   metadata_id integer PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
 362   publisher_id text NOT NULL,
 363   infohash text NOT NULL,
 364   description text,
 365   timestamp integer NOT NULL,
 366   signature text NOT NULL,
 367   UNIQUE (publisher_id, infohash),
 368   FOREIGN KEY (publisher_id, infohash)
 369     REFERENCES ChannelCast(publisher_id, infohash)
 370     ON DELETE CASCADE -- the fk constraint is not enforced by sqlite
 371 );
 372
 373 CREATE INDEX infohash_md_idx
 374 on Metadata(infohash);
 375
 376 CREATE INDEX pub_md_idx
 377 on Metadata(publisher_id);
 378
 379
 380 CREATE TABLE Subtitles (
 381   metadata_id_fk integer,
 382   subtitle_lang text NOT NULL,
 383   subtitle_location text,
 384   checksum text NOT NULL,
 385   UNIQUE (metadata_id_fk,subtitle_lang),
 386   FOREIGN KEY (metadata_id_fk)
 387     REFERENCES Metadata(metadata_id)
 388     ON DELETE CASCADE, -- the fk constraint is not enforced by sqlite
 389
 390   -- ISO639-2 uses 3 characters for lang codes
 391   CONSTRAINT lang_code_length
 392     CHECK ( length(subtitle_lang) == 3 )
 393 );
 394
 395
 396 CREATE INDEX metadata_sub_idx
 397 on Subtitles(metadata_id_fk);
 398
 399 -- Stores the subtitles that peers have as an integer bitmask
 400  CREATE TABLE SubtitlesHave (
 401     metadata_id_fk integer,
 402     peer_id text NOT NULL,
 403     have_mask integer NOT NULL,
 404     received_ts integer NOT NULL, --timestamp indicating when the mask was received
 405     UNIQUE (metadata_id_fk, peer_id),
 406     FOREIGN KEY (metadata_id_fk)
 407       REFERENCES Metadata(metadata_id)
 408       ON DELETE CASCADE, -- the fk constraint is not enforced by sqlite
 409
 410     -- 32 bit unsigned integer
 411     CONSTRAINT have_mask_length
 412       CHECK (have_mask >= 0 AND have_mask < 4294967296)
 413 );
 414
 415 CREATE INDEX subtitles_have_idx
 416 on SubtitlesHave(metadata_id_fk);
 417
 418 -- this index can boost queries
 419 -- ordered by timestamp on the SubtitlesHave DB
 420 CREATE INDEX subtitles_have_ts
 421 on SubtitlesHave(received_ts);
 422
 423 -------------------------------------
 424
 425 COMMIT TRANSACTION create_table;
 426
 427 ----------------------------------------
 428
 429 BEGIN TRANSACTION init_values;
 430
 431 INSERT INTO Category VALUES (1, 'Video', 'Video Files');
 432 INSERT INTO Category VALUES (2, 'VideoClips', 'Video Clips');
 433 INSERT INTO Category VALUES (3, 'Audio', 'Audio');
 434 INSERT INTO Category VALUES (4, 'Compressed', 'Compressed');
 435 INSERT INTO Category VALUES (5, 'Document', 'Documents');
 436 INSERT INTO Category VALUES (6, 'Picture', 'Pictures');
 437 INSERT INTO Category VALUES (7, 'xxx', 'XXX');
 438 INSERT INTO Category VALUES (8, 'other', 'Other');
 439
 440 INSERT INTO TorrentStatus VALUES (0, 'unknown', NULL);
 441 INSERT INTO TorrentStatus VALUES (1, 'good', NULL);
 442 INSERT INTO TorrentStatus VALUES (2, 'dead', NULL);
 443
 444 INSERT INTO TorrentSource VALUES (0, '', 'Unknown');
 445 INSERT INTO TorrentSource VALUES (1, 'BC', 'Received from other user');
 446
 447 INSERT INTO MyInfo VALUES ('version', 5);
 448
 449 COMMIT TRANSACTION init_values;
 450