-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreatedb.py
executable file
·117 lines (94 loc) · 5.51 KB
/
createdb.py
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
#!/usr/bin/env python
__author__ = "Telmo Menezes (telmo@telmomenezes.com)"
__date__ = "Mar 2011"
import sys
import sqlite3
def safe_execute(cur, query):
try:
cur.execute(query)
print('Executed query: %s' % query)
except sqlite3.OperationalError:
pass
def create_db(dbpath):
conn = sqlite3.connect(dbpath)
cur = conn.cursor()
# create journals table
safe_execute(cur, "CREATE TABLE publications (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE publications ADD COLUMN title TEXT")
safe_execute(cur, "ALTER TABLE publications ADD COLUMN iso_title TEXT")
safe_execute(cur, "ALTER TABLE publications ADD COLUMN type TEXT")
safe_execute(cur, "ALTER TABLE publications ADD COLUMN ISSN TEXT")
# create issues table
safe_execute(cur, "CREATE TABLE issues (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN wos_id TEXT")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN pub_id INTEGER")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN year INTEGER")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN date TEXT")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN volume INTEGER")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN issue INTEGER")
safe_execute(cur, "ALTER TABLE issues ADD COLUMN timestamp REAL")
# create articles table
safe_execute(cur, "CREATE TABLE articles (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN wos_id TEXT")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN title TEXT")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN abstract TEXT")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN issue_id INTEGER")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN type TEXT")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN beginning_page INTEGER")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN end_page INTEGER")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN page_count INTEGER")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN language TEXT")
safe_execute(cur, "ALTER TABLE articles ADD COLUMN timestamp REAL")
# create authors table
safe_execute(cur, "CREATE TABLE authors (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE authors ADD COLUMN name TEXT")
# create article_author table
safe_execute(cur, "CREATE TABLE article_author (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE article_author ADD COLUMN article_id INTEGER")
safe_execute(cur, "ALTER TABLE article_author ADD COLUMN author_id INTEGER")
# create keywords table
safe_execute(cur, "CREATE TABLE keywords (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE keywords ADD COLUMN keyword TEXT")
# create article_keyword table
safe_execute(cur, "CREATE TABLE article_keyword (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE article_keyword ADD COLUMN article_id INTEGER")
safe_execute(cur, "ALTER TABLE article_keyword ADD COLUMN keyword_id INTEGER")
# create citations table
safe_execute(cur, "CREATE TABLE citations (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE citations ADD COLUMN orig_id INTEGER")
safe_execute(cur, "ALTER TABLE citations ADD COLUMN targ_id INTEGER")
safe_execute(cur, "ALTER TABLE citations ADD COLUMN orig_wosid TEXT")
safe_execute(cur, "ALTER TABLE citations ADD COLUMN targ_wosid TEXT")
# create organizations table
safe_execute(cur, "CREATE TABLE organizations (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE organizations ADD COLUMN name TEXT")
safe_execute(cur, "ALTER TABLE organizations ADD COLUMN city TEXT")
safe_execute(cur, "ALTER TABLE organizations ADD COLUMN province_state TEXT")
safe_execute(cur, "ALTER TABLE organizations ADD COLUMN country TEXT")
safe_execute(cur, "ALTER TABLE organizations ADD COLUMN postal_code TEXT")
# create article_organization table
safe_execute(cur, "CREATE TABLE article_organization (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE article_organization ADD COLUMN article_id INTEGER")
safe_execute(cur, "ALTER TABLE article_organization ADD COLUMN organization_id INTEGER")
#create author_citations table
safe_execute(cur, "CREATE TABLE author_citations (id INTEGER PRIMARY KEY)")
safe_execute(cur, "ALTER TABLE author_citations ADD COLUMN orig_id INTEGER")
safe_execute(cur, "ALTER TABLE author_citations ADD COLUMN targ_id INTEGER")
safe_execute(cur, "ALTER TABLE author_citations ADD COLUMN timestamp REAL")
# indexes
safe_execute(cur, "CREATE INDEX articles_id ON articles (id)")
safe_execute(cur, "CREATE INDEX articles_wos_id ON articles (wos_id)")
safe_execute(cur, "CREATE INDEX issues_id ON issues (id)")
safe_execute(cur, "CREATE INDEX issues_wos_id ON issues (wos_id)")
safe_execute(cur, "CREATE INDEX publications_ISSN ON publications (ISSN)")
safe_execute(cur, "CREATE INDEX authors_name ON authors (name)")
safe_execute(cur, "CREATE INDEX keywords_keyword ON keywords (keyword)")
safe_execute(cur, "CREATE INDEX organizations_name ON organizations (name)")
safe_execute(cur, "CREATE INDEX author_citations_id ON author_citations (id)")
safe_execute(cur, "CREATE INDEX author_citations_orig_targ ON author_citations (orig_id, targ_id)")
safe_execute(cur, "CREATE INDEX article_author_article_id ON article_author (article_id)")
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
create_db(sys.argv[1])