From 55c7ee618e5d59ac5eb691febb6dd911f3f75d2a Mon Sep 17 00:00:00 2001 From: RaindropsSys Date: Sun, 16 Jun 2024 12:02:05 +0200 Subject: Fix SQL stuff + remove C++ code --- updater/sql/main.py | 206 ---------------------------------------------------- 1 file changed, 206 deletions(-) delete mode 100755 updater/sql/main.py (limited to 'updater/sql/main.py') diff --git a/updater/sql/main.py b/updater/sql/main.py deleted file mode 100755 index f4e3a01..0000000 --- a/updater/sql/main.py +++ /dev/null @@ -1,206 +0,0 @@ -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!") -- cgit