111 lines
4.2 KiB
Python
111 lines
4.2 KiB
Python
from datetime import datetime
|
|
import sqlite3
|
|
import json
|
|
|
|
from utils import asset
|
|
from vntypes import *
|
|
|
|
class VNDB:
|
|
|
|
def __init__(self, db_name=asset('vn_database.db')):
|
|
self.__db_name = db_name
|
|
with sqlite3.connect(self.__db_name) as connection:
|
|
cursor = connection.cursor()
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS marks (
|
|
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
type TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
UNIQUE(id, value)
|
|
);
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS authors (
|
|
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
thumbnail TEXT NOT NULL,
|
|
UNIQUE(id, title, description, thumbnail)
|
|
);
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS novels (
|
|
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
author INTEGER NOT NULL,
|
|
UNIQUE(id, title, author)
|
|
);
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS posts_log (
|
|
novel INTEGER NOT NULL,
|
|
channel INTEGER NOT NULL,
|
|
marks JSON NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
author INTEGER NOT NULL,
|
|
files_on_disk JSON NOT NULL,
|
|
post_info JSON,
|
|
post_at INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
''')
|
|
|
|
connection.commit()
|
|
|
|
def __execute(self, sql: str, params: tuple = ()):
|
|
with sqlite3.connect(self.__db_name) as connection:
|
|
connection.cursor().execute(sql, params)
|
|
connection.commit()
|
|
|
|
def __fetch(self, sql: str, params: tuple = ()):
|
|
with sqlite3.connect(self.__db_name) as connection:
|
|
cursor = connection.cursor()
|
|
cursor.execute(sql, params)
|
|
return cursor.fetchall()
|
|
|
|
def search_author(self, title: str):
|
|
return self.__fetch("SELECT * FROM authors "
|
|
"WHERE title LIKE ?", (title,))
|
|
|
|
def search_mark(self, mark: Mark):
|
|
return self.__fetch("SELECT * FROM marks "
|
|
"WHERE value LIKE ? "
|
|
"AND type = ?", ('%'+mark.value+'%',mark.type))
|
|
|
|
def search_novel(self, title: str):
|
|
return self.__fetch("SELECT * FROM novels "
|
|
"WHERE title LIKE ? ", (title,))
|
|
|
|
def insert_author(self, title: str, desc: str, thumb: str):
|
|
self.__execute("INSERT INTO authors (title, description, thumbnail) "
|
|
"VALUES (?, ?, ?)", (title, desc, thumb))
|
|
|
|
def insert_mark(self, type: str, value: str):
|
|
self.__execute("INSERT INTO marks (type, value) "
|
|
"VALUES (?, ?)", (type, value))
|
|
|
|
def insert_novel(self, novel: Novel):
|
|
self.__execute("INSERT INTO novels (title, author) "
|
|
f"SELECT {novel.title}, {novel.author_id} "
|
|
f"WHERE NOT EXISTS(SELECT 1 FROM novels WHERE title = {novel.title} AND author = {novel.author_id});")
|
|
|
|
# FIXME: SQL Types
|
|
def insert_post(self, full_novel: FullNovel):
|
|
self.insert_novel(full_novel.data)
|
|
self.__execute("INSERT INTO posts_log (novel, channel, marks, title, description, author, files_on_disk, post_at, created_at) "
|
|
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
|
|
(self.search_novel(full_novel.data.title)[0],
|
|
full_novel.data.tg_channel,
|
|
json.dumps(full_novel.data.marks),
|
|
full_novel.data.title,
|
|
full_novel.data.description,
|
|
full_novel.data.author_id,
|
|
json.dumps(full_novel.files),
|
|
full_novel.data.post_at,
|
|
datetime.now()))
|
|
|