"""MariaDB/MySQL storage retaining the local Database's public interface.""" import hashlib import json import re 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_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): 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 = [] 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 COLUMNS FROM jobs LIKE 'resume_pending'").fetchone(): conn.execute('ALTER TABLE jobs ADD COLUMN resume_pending TINYINT DEFAULT 0') 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, charset='utf8mb4', autocommit=False, connect_timeout=10, read_timeout=120, write_timeout=120) try: yield Connection(raw) raw.commit() except BaseException: raw.rollback() raise finally: raw.close() def upsert_catalog(self, 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:]) with self.connection() as conn: 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, 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: with self.connection() as conn: 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]}