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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
import base64
import json
import sqlite3
import os
import psycopg2
registered = [
"RaindropsSys",
]
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
print("Initializing database...")
conn = psycopg2.connect(database="derpibooru")
db = conn.cursor()
print("Creating taggings index...")
db.execute("""
CREATE INDEX IF NOT EXISTS taggings_index ON image_taggings (image_id) INCLUDE (tag_id)
""")
print("Creating tags index...")
db.execute("""
CREATE INDEX IF NOT EXISTS tags_index ON tags (id) INCLUDE (name)
""")
print("Opening tags database...")
os.system("rm -rf /app/prisbeam")
os.system("rm -rf /prisbeam")
os.system("mkdir -p /prisbeam")
os.system("mkdir -p /prisbeam/common")
os.system("mkdir -p /prisbeam/users")
if os.path.exists(f"/prisbeam/common/tags.db"):
os.remove(f"/prisbeam/common/tags.db")
db2 = sqlite3.connect(f"/prisbeam/common/tags.db")
db2.execute("CREATE TABLE tags (json LONGTEXT)")
db2.execute("CREATE TABLE aliases (json LONGTEXT)")
db2.execute("CREATE TABLE implications (json LONGTEXT)")
print("Building list of tags... Step 1/3")
db.execute("""
SELECT *
FROM tags
""")
data = list(map(lambda x: dict_factory(db, x), db.fetchall()))
for tag in data:
tag['id'] = int('10' + str(tag['id']))
db2.execute("INSERT INTO tags VALUES ('" + base64.b64encode(bytes(json.dumps(tag), 'utf-8')).decode('utf-8') + "')")
print("Building list of tags... Step 2/3")
db.execute("""
SELECT *
FROM tag_aliases
""")
data = list(map(lambda x: dict_factory(db, x), db.fetchall()))
for tag in data:
tag['tag_id'] = int('10' + str(tag['tag_id']))
tag['target_tag_id'] = int('10' + str(tag['target_tag_id']))
db2.execute("INSERT INTO aliases VALUES ('" + base64.b64encode(bytes(json.dumps(tag), 'utf-8')).decode('utf-8') + "')")
print("Building list of tags... Step 3/3")
db.execute("""
SELECT *
FROM tag_implications
""")
data = list(map(lambda x: dict_factory(db, x), db.fetchall()))
for tag in data:
tag['tag_id'] = int('10' + str(tag['tag_id']))
tag['target_tag_id'] = int('10' + str(tag['target_tag_id']))
db2.execute("INSERT INTO implications VALUES ('" + base64.b64encode(bytes(json.dumps(tag), 'utf-8')).decode('utf-8') + "')")
print("Saving...")
db2.commit()
db2.close()
print("Gathering user list...")
db.execute("""
SELECT *
FROM public.users
""")
users = list(filter(lambda x: x['name'] in registered, map(lambda x: dict_factory(db, x), db.fetchall())))
for user in users:
print(f"{user['name']}: Initialising database...");
if os.path.exists(f"/prisbeam/users/{user['name']}.db"):
os.remove(f"/prisbeam/users/{user['name']}.db")
db2 = sqlite3.connect(f"/prisbeam/users/{user['name']}.db")
db2.execute("CREATE TABLE images (json LONGTEXT)")
print(f"{user['name']}: Fetching data...")
db.execute(f"""
SELECT *
FROM image_faves
JOIN image_intensities ON image_faves.image_id = image_intensities.image_id
JOIN images ON image_faves.image_id = images.id
JOIN users ON images.user_id = users.id
WHERE image_faves.user_id = {user['id']}
""")
data = list(map(lambda x: dict_factory(db, x), db.fetchall()))
i = 0
l = len(data)
for image in data:
print(f"{user['name']}: Processing image #{image['image_id']} ({round((i / l) * 100)}%)")
db.execute(f"""
SELECT tags.id, tags.name
FROM image_taggings
JOIN tags ON image_taggings.tag_id = tags.id
WHERE image_taggings.image_id = {image['image_id']}
""")
tags = list(map(lambda x: dict_factory(db, x), db.fetchall()))
db.execute(f"""
SELECT source
FROM image_sources
WHERE image_sources.image_id = {image['image_id']}
""")
sources = list(map(lambda x: dict_factory(db, x), db.fetchall()))
dic = {
'wilson_score': 0,
'spoilered': False,
'representations': {
'full': f"{image['version_path']}full.{image['image_format']}",
'large': f"{image['version_path']}large.{image['image_format']}",
'medium': f"{image['version_path']}medium.{image['image_format']}",
'small': f"{image['version_path']}small.{image['image_format']}",
'tall': f"{image['version_path']}tall.{image['image_format']}",
'thumb': f"{image['version_path']}thumb.{image['image_format']}",
'thumb_small': f"{image['version_path']}thumb_small.{image['image_format']}",
'thumb_tiny': f"{image['version_path']}thumb_tiny.{image['image_format']}",
},
'faves': 0,
'aspect_ratio': image['image_aspect_ratio'],
'duration': 0,
'thumbnails_generated': True,
'tags': list(map(lambda x: x['name'], tags)),
'created_at': image['created_at'].isoformat(),
'tag_count': 0,
'downvotes': image['downvotes'],
'id': int('10' + str(image['image_id'])),
'source_id': image['image_id'],
'source': 'https://derpibooru.org/images/%s',
'source_name': 'Derpibooru',
'name': image['image_name'],
'width': image['image_width'],
'intensities': {
'ne': image['ne_intensity'],
'nw': image['nw_intensity'],
'se': image['se_intensity'],
'sw': image['sw_intensity']
},
'orig_sha512_hash': image['image_orig_sha512_hash'],
'deletion_reason': None,
'processed': True,
'animated': None,
'height': image['image_height'],
'description': '',
'sha512_hash': image['image_sha512_hash'],
'source_urls': list(map(lambda x: x['source'], sources)) if len(list(map(lambda x: x['source'], sources))) else [],
'upvotes': image['upvotes'],
'source_url': list(map(lambda x: x['source'], sources))[0] if len(list(map(lambda x: x['source'], sources))) else '',
'uploader_id': image['user_id'],
'score': image['score'],
'uploader': image['name'],
'first_seen_at': image['created_at'].isoformat(),
'mime_type': image['image_mime_type'],
'duplicate_of': None,
'size': image['image_size'],
'comment_count': image['comment_count'],
'view_url': f"{image['version_path'][:-1].replace('/img/', '/img/view/')}.{image['image_format']}",
'hidden_from_users': False,
'updated_at': image['updated_at'].isoformat(),
'tag_ids': list(map(lambda x: int('10' + str(x['id'])), tags)),
'format': image['image_format'],
}
db2.execute("INSERT INTO images VALUES ('" + base64.b64encode(bytes(json.dumps(dic), 'utf-8')).decode('utf-8') + "')")
i += 1
print(f"{user['name']}: Saving...")
db2.commit()
db2.close()
print(f"{user['name']}: Finished.")
db.close()
print("Moving...")
os.system("cp -rv /prisbeam/* /app/prisbeam")
os.system("rm -rf /prisbeam")
print("Done!")
|
