Files
kaoyan/studio/mysql_database.py

176 lines
10 KiB
Python

"""MariaDB/MySQL storage retaining the local Database's public interface."""
import hashlib
import json
import re
import ssl
from contextlib import contextmanager
import pymysql
from studio.database import Database, encode
from studio.domain import normalized_name, is_special_score
SCHEMA = [
"""CREATE TABLE IF NOT EXISTS catalog(
id BIGINT PRIMARY KEY AUTO_INCREMENT,record_key CHAR(64) NOT NULL UNIQUE,year INT NOT NULL,
school_code VARCHAR(32),school_name VARCHAR(255),province VARCHAR(64),major_code VARCHAR(32),major_name VARCHAR(255),
department_code VARCHAR(64),department_name VARCHAR(255),study_mode VARCHAR(32),degree_type VARCHAR(64),level VARCHAR(128),
subjects TEXT,quota INT,source VARCHAR(128),favorite TINYINT NOT NULL DEFAULT 0,data LONGTEXT NOT NULL,
department_norm VARCHAR(255) NOT NULL DEFAULT '',INDEX catalog_filter(year,province,major_code,school_code))""",
"""CREATE TABLE IF NOT EXISTS scores(
id BIGINT PRIMARY KEY AUTO_INCREMENT,school_code VARCHAR(32),major_code VARCHAR(32),year INT,
department_id VARCHAR(64),department_name VARCHAR(255),scope VARCHAR(32),total INT,source VARCHAR(128),data LONGTEXT NOT NULL,
note TEXT NOT NULL,raw_code VARCHAR(64) NOT NULL,score_key CHAR(64) NOT NULL UNIQUE,
department_norm VARCHAR(255) NOT NULL DEFAULT '',special_line TINYINT NOT NULL DEFAULT 0,
INDEX scores_lookup(school_code,major_code,year))""",
"CREATE TABLE IF NOT EXISTS schools(school_code VARCHAR(32) PRIMARY KEY,school_name VARCHAR(255),province VARCHAR(64),level VARCHAR(128),data LONGTEXT)",
"CREATE TABLE IF NOT EXISTS jobs(id VARCHAR(64) PRIMARY KEY,params LONGTEXT,status VARCHAR(32),created_at VARCHAR(40),updated_at VARCHAR(40),message TEXT,errors INT DEFAULT 0,records INT DEFAULT 0,resume_pending TINYINT DEFAULT 0)",
"CREATE TABLE IF NOT EXISTS job_units(job_id VARCHAR(64),unit VARCHAR(255),PRIMARY KEY(job_id,unit))",
"CREATE TABLE IF NOT EXISTS job_catalog_pages(job_id VARCHAR(64),page_key VARCHAR(64),data LONGTEXT NOT NULL,PRIMARY KEY(job_id,page_key))",
"CREATE TABLE IF NOT EXISTS job_logs(id BIGINT PRIMARY KEY AUTO_INCREMENT,job_id VARCHAR(64),created_at VARCHAR(40),level VARCHAR(32),message TEXT,INDEX job_logs_lookup(job_id,id))",
]
def score_key(row):
identity = [row.get(field) or '' for field in ('school_code', 'major_code', 'year', 'department_id', 'department_name', 'scope', 'source', 'note', 'raw_code')]
return hashlib.sha256(encode(identity).encode()).hexdigest()
class Row(dict):
def __getitem__(self, key):
return tuple(self.values())[key] if isinstance(key, int) else super().__getitem__(key)
class Result:
def __init__(self, cursor):
self.rowcount = cursor.rowcount
self.columns = [item[0] for item in cursor.description] if cursor.description else []
self.rows = cursor.fetchall() if cursor.description else ()
self.offset = 0
cursor.close()
def fetchone(self):
if self.offset >= len(self.rows):
return None
result = Row(zip(self.columns, self.rows[self.offset]))
self.offset += 1
return result
def fetchall(self):
return list(self)
def __iter__(self):
while self.offset < len(self.rows):
yield self.fetchone()
def mysql_sql(sql):
if sql.startswith('SELECT count(*) FROM (') and sql.endswith(')'):
sql += ' AS studio_count'
sql = sql.replace('INSERT OR IGNORE', 'INSERT IGNORE')
sql = re.sub(r'ON CONFLICT\([^)]*\) DO UPDATE SET', 'ON DUPLICATE KEY UPDATE', sql)
sql = re.sub(r'excluded\.(\w+)', r'VALUES(\1)', sql)
sql = sql.replace("is_special_score(json_extract(x.data,'$.note'))=0", 'x.special_line=0')
sql = sql.replace('norm_name(x.department_name)', 'x.department_norm').replace('norm_name(c.department_name)', 'c.department_norm')
sql = re.sub(r"json_extract\(([^,]+),('[^']+')\)", r'JSON_UNQUOTE(JSON_EXTRACT(\1,\2))', sql)
return sql.replace('?', '%s')
class Connection:
def __init__(self, raw):
self.raw = raw
def execute(self, sql, params=None):
cursor = self.raw.cursor()
try:
cursor.execute(mysql_sql(sql), params)
return Result(cursor)
except BaseException:
cursor.close()
raise
def executemany(self, sql, values):
cursor = self.raw.cursor()
try:
cursor.executemany(mysql_sql(sql), values)
return Result(cursor)
except BaseException:
cursor.close()
raise
class MySQLDatabase(Database):
def __init__(self, config, recover_jobs=True):
self.config = {key: value for key, value in config.items() if key in ('host', 'port', 'user', 'password', 'database', 'unix_socket')}
if not self.config.get('database'):
raise ValueError('数据库配置缺少 database')
self.recovered_job_ids = []
self._ssl_context = None
with self.connection() as conn:
for statement in SCHEMA:
conn.execute(statement + ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin')
if not conn.execute("SHOW INDEX FROM catalog WHERE Key_name='catalog_export'").fetchone():
conn.execute('CREATE INDEX catalog_export ON catalog(year DESC,school_name,id)')
if not conn.execute("SHOW INDEX FROM catalog WHERE Key_name='catalog_targets'").fetchone():
conn.execute('CREATE INDEX catalog_targets ON catalog(year,school_code,major_code)')
if not conn.execute("SHOW COLUMNS FROM jobs LIKE 'resume_pending'").fetchone():
conn.execute('ALTER TABLE jobs ADD COLUMN resume_pending TINYINT DEFAULT 0')
if recover_jobs:
recovery = "status IN ('running','queued') OR (status='paused' AND resume_pending=1)"
self.recovered_job_ids = [row['id'] for row in conn.execute('SELECT id FROM jobs WHERE ' + recovery)]
conn.execute("UPDATE jobs SET status='paused',resume_pending=0,message='服务重启,任务可从断点恢复' WHERE " + recovery)
@contextmanager
def connection(self):
raw = pymysql.connect(**self.config, ssl=self._ssl_context, charset='utf8mb4', autocommit=False, connect_timeout=10, read_timeout=120, write_timeout=120)
try:
# Retain the driver's negotiated context rather than reload its CA
# store for every short-lived query. Plain servers retain default behavior.
if self._ssl_context is None and isinstance(getattr(raw, '_sock', None), ssl.SSLSocket):
self._ssl_context = raw.ctx
yield Connection(raw)
raw.commit()
except BaseException:
raw.rollback()
raise
finally:
raw.close()
def _upsert_catalog(self, conn, records):
fields = ['record_key', 'year', 'school_code', 'school_name', 'province', 'major_code', 'major_name', 'department_code', 'department_name', 'study_mode', 'degree_type', 'level', 'subjects', 'quota', 'source', 'data', 'department_norm']
values = []
for record in records:
row = {**record, 'subjects': ' | '.join(record.get('subjects', [])), 'data': encode(record), 'department_norm': normalized_name(record.get('department_name'))}
values.append([row.get(field) for field in fields])
if not values:
return
update = ','.join(f'{field}=VALUES({field})' for field in fields[1:])
conn.executemany(f"INSERT INTO catalog({','.join(fields)}) VALUES({','.join('?' for _ in fields)}) ON DUPLICATE KEY UPDATE {update}", values)
for record in records:
self._school(conn, record)
def _upsert_scores(self, conn, records):
fields = ['school_code', 'major_code', 'year', 'department_id', 'department_name', 'scope', 'total', 'source', 'data', 'note', 'raw_code', 'score_key', 'department_norm', 'special_line']
values = []
for record in records:
row = {**record, 'department_id': record.get('department_id') or '', 'department_name': record.get('department_name') or '', 'source': record.get('source', '掌上考研'), 'note': record.get('note') or '', 'raw_code': record.get('raw_code') or ''}
row.update(data=encode(record), score_key=score_key(row), department_norm=normalized_name(row['department_name']), special_line=int(is_special_score(row['note'])))
values.append([row.get(field) for field in fields])
if values:
conn.executemany(f"INSERT INTO scores({','.join(fields)}) VALUES({','.join('?' for _ in fields)}) ON DUPLICATE KEY UPDATE total=VALUES(total),data=VALUES(data),department_norm=VALUES(department_norm),special_line=VALUES(special_line)", values)
def favorite(self, rid, enabled):
# MariaDB reports changed rows; an already-selected favorite is still success.
with self.connection() as conn:
conn.execute('UPDATE catalog SET favorite=? WHERE id=?', (int(enabled), rid))
return conn.execute('SELECT 1 FROM catalog WHERE id=?', (rid,)).fetchone() is not None
def history(self, rid):
record = self.record(rid)
if record is None:
return None
with self.connection() as conn:
scores = conn.execute('SELECT data FROM scores WHERE school_code=? AND major_code=? ORDER BY year,scope,department_name', (record['school_code'], record['major_code'])).fetchall()
plans = conn.execute('SELECT data FROM catalog WHERE school_code=? AND major_code=? AND department_norm=? ORDER BY year,id', (record['school_code'], record['major_code'], normalized_name(record['department_name']))).fetchall()
def matches(row):
return not row.get('department_name') and not row.get('department_id') or row.get('department_id') and row['department_id'] == record['department_code'] or row.get('department_name') and normalized_name(row['department_name']) == normalized_name(record['department_name'])
return {'record': record, 'scores': [row for entry in scores if matches(row := json.loads(entry['data']))], 'plans': [json.loads(row['data']) for row in plans]}