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
|
package postgres
import "fmt"
// table name for torrents
const tableTorrents = "torrents"
// table name for users
const tableUsers = "users"
// table for new comments
const tableComments = "comments"
// table for old comments
const tableOldComments = "comments_old"
// table for torrent reports
const tableTorrentReports = "torrent_reports"
// table for user follows
const tableUserFollows = "user_follows"
// table for old user uploads
const tableOldUserUploads = "user_uploads_old"
// all tables that we have in current database schema in the order they are created
var tables = []createTable{
// users table
createTable{
name: tableUsers,
columns: tableColumns{
"user_id SERIAL PRIMARY KEY",
"username TEXT NOT NULL",
"password TEXT NOT NULL",
"email TEXT",
"status INTEGER NOT NULL",
"created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL",
"updated_at TIMESTAMP WITHOUT TIME ZONE",
"last_login_at TIMESTAMP WITHOUT TIME ZONE",
"last_login_ip TEXT",
"api_token TEXT",
"api_token_expiry TIMESTAMP WITHOUT TIME ZONE NOT NULL",
"language TEXT",
"MD5 TEXT",
},
postCreate: []sqlQuery{
createIndex(tableUsers, "username"),
},
},
// torrents table
createTable{
name: tableTorrents,
columns: tableColumns{
"torrent_id SERIAL PRIMARY KEY",
"torrent_name TEXT",
"torrent_hash TEXT NOT NULL",
"category INTEGER NOT NULL",
"sub_category INTEGER NOT NULL",
"status INTEGER NOT NULL",
"date TIMESTAMP WITHOUT TIME ZONE",
fmt.Sprintf("uploader INTEGER NOT NULL REFERENCES %s (user_id)", tableUsers),
"downloads INTEGER",
"stardom INTEGER NOT NULL",
"filesize BIGINT",
"description TEXT NOT NULL",
"website_link TEXT",
"deleted_at TIMESTAMP WITHOUT TIME ZONE",
"seeders INTEGER",
"leechers INTEGER",
"completed INTEGER",
"last_scrape TIMESTAMP WITHOUT TIME ZONE",
},
postCreate: []sqlQuery{
createIndex(tableTorrents, "torrent_id"),
createIndex(tableTorrents, "deleted_at"),
createIndex(tableTorrents, "uploader"),
createTrigraph(tableTorrents, "category", "torrent_name"),
createTrigraph(tableTorrents, "sub_category", "torrent_name"),
createTrigraph(tableTorrents, "status", "torrent_name"),
createTrigraph(tableTorrents, "torrent_name"),
},
},
// new comments table
createTable{
name: tableComments,
columns: tableColumns{
"comment_id SERIAL PRIMARY KEY",
fmt.Sprintf("torrent_id INTEGER REFERENCES %s (torrent_id)", tableTorrents),
fmt.Sprintf("user_id INTEGER REFERENCES %s (user_id)", tableUsers),
"content TEXT NOT NULL",
"created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL",
"updated_at TIMESTAMP WITHOUT TIME ZONE",
"deleted_at TIMESTAMP WITH TIME ZONE",
},
postCreate: []sqlQuery{
createIndex(tableComments, "torrent_id"),
},
},
// old comments table
createTable{
name: tableOldComments,
columns: tableColumns{
fmt.Sprintf("torrent_id INTEGER NOT NULL REFERENCES %s (torrent_id)", tableTorrents),
"username TEXT NOT NULL",
"content TEXT NOT NULL",
"date TIMESTAMP WITHOUT TIME ZONE NOT NULL",
},
},
// torrent reports table
createTable{
name: tableTorrentReports,
columns: tableColumns{
"torrent_report_id SERIAL PRIMARY KEY",
"type TEXT",
fmt.Sprintf("torrent_id INTEGER REFERENCES %s (torrent_id)", tableTorrents),
"user_id INTEGER",
"created_at TIMESTAMP WITH TIME ZONE",
},
postCreate: []sqlQuery{
createIndex(tableTorrentReports, "torrent_report_id"),
},
},
// user follows table
createTable{
name: tableUserFollows,
columns: tableColumns{
"user_id INTEGER NOT NULL",
"following INTEGER NOT NULL",
"PRIMARY KEY(user_id, following)",
},
},
// old uploads table
createTable{
name: tableOldUserUploads,
columns: tableColumns{
"username TEXT IS NOT NULL",
fmt.Sprintf("torrent_id INTEGER IS NOT NULL REFERENCES %s (torrent_id)", tableTorrents),
"PRIMARY KEY (torrent_id)",
},
},
}
|
