EQDZLDXu

1create_Title = ("CREATE TABLE IF NOT EXISTS Title ("
2                 "tconst TEXT PRIMARY KEY, "
3                 "title_type TEXT, "
4                 "primary_title TEXT, "
5                 "original_title TEXT, "
6                 "is_adult INTEGER, "
7                 "start_year INTEGER, "
8                 "end_year INTEGER, "
9                 "runtime_minutes INTEGER, "
10                 "genres TEXT);")
11
12create_Name = ("CREATE TABLE IF NOT EXISTS Name ("
13               "nconst TEXT PRIMARY KEY, "
14               "primary_name TEXT, "
15               "birth_year INTEGER, "
16               "death_year TEXT, "
17               "primary_profession TEXT, "
18               "known_for_titles TEXT);")
19
20create_Crew = ("CREATE TABLE IF NOT EXISTS Crew ("
21               "tconst TEXT PRIMARY KEY, "
22               "directors TEXT, "
23               "writers TEXT, "
24               "FOREIGN KEY(tconst) REFERENCES Title(tconst));")
25
26create_Episode = ("CREATE TABLE IF NOT EXISTS Episode ("
27                  "tconst TEXT, "
28                  "parent_tconst TEXT, "
29                  "season_number INTEGER, "
30                  "episode_number INTEGER, "
31                  "FOREIGN KEY(tconst) REFERENCES Title(tconst), "
32                  "FOREIGN KEY(parent_tconst) REFERENCES Title(tconst));")
33
34create_Principals = ("CREATE TABLE IF NOT EXISTS Principals ("
35                     "tconst TEXT, "
36                     "principal_cast TEXT, "
37                     "FOREIGN KEY(tconst) REFERENCES Title(tconst), "
38                     "FOREIGN KEY(principal_cast) REFERENCES Name(nconst));")
39
40create_Ratings = ("CREATE TABLE IF NOT EXISTS Ratings ("
41                  "tconst TEXT PRIMARY KEY, "
42                  "average_rating REAL, "
43                  "num_votes INTEGER, "
44                  "FOREIGN KEY(tconst) REFERENCES Title(tconst));")
45
46create_Plot = ("CREATE TABLE IF NOT EXISTS Plot ("
47               "tconst TEXT PRIMARY KEY, "
48               "plot TEXT, "
49               "FOREIGN KEY(tconst) REFERENCES Title(tconst));")
50
51create_table_statements = (create_Title,
52                           create_Name,
53                           create_Crew,
54                           create_Episode,
55                           create_Principals,
56                           create_Ratings,
57                           create_Plot)
58
59
60#Add columns one at a time since SQLite does not loop on this command.
61add_column_statements = ("ALTER TABLE Title ADD last_access TEXT;",
62                         "ALTER TABLE Title ADD release_date TEXT;",
63                         #TEXT affinity because there are different currency symbols.
64                         "ALTER TABLE Title ADD budget TEXT;",
65                         "ALTER TABLE Ratings ADD mpaa_rating TEXT;",
66                         "ALTER TABLE Principals ADD character TEXT;")
67
68create_index_statements = ("CREATE UNIQUE INDEX episodeidx_tconst ON Episode(tconst);",
69                           "CREATE UNIQUE INDEX titleidx_tconst ON Title(tconst);",
70                           "CREATE UNIQUE INDEX crewidx_tconst ON Crew(tconst);",
71                           "CREATE UNIQUE INDEX nameidx_nconst ON Name(nconst);",
72                           "CREATE UNIQUE INDEX plotidx_tconst ON Plot(tconst);",
73                           "CREATE INDEX principalsidx_tconst ON Principals(tconst);")