#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Lexis 词库一键建表 + 导入脚本 用法(默认 SQLite,无需任何服务): python import_lexis.py --recreate python import_lexis.py --sqlite-path D:/data/lexis.db --recreate PostgreSQL: pip install psycopg2-binary python import_lexis.py --db postgres --dsn "postgresql://user:pass@host:5432/lexis" --recreate MySQL: pip install pymysql python import_lexis.py --db mysql --dsn "mysql://user:pass@host:3306/lexis" --recreate 只导入解密数据,自动跳过 wordbooks/original 与 zpk/original。 """ from __future__ import annotations import argparse import csv import glob import json import os import re import sqlite3 import sys import time from collections import defaultdict from urllib.parse import urlparse, unquote # --------------------------------------------------------------------------- # 表定义 # --------------------------------------------------------------------------- TABLES = [ "exam", "book", "book_phrase_book", "word_entry", "book_entry", "word_spelling_variant", "word_sense", "sense_object", "sense_exam", "sense_relation", "entry_exam_meaning_order", "phrase", "phrase_exam", "phrase_exam_order", "example_group", "example_group_exam", "example_usage", "example_sentence", "entry_exam_sentence", "confusable_word", "morpheme", "card", "card_exam", "word_family", "word_family_exam", "word_family_exam_order", "word_family_variant", "word_family_variant_member", "word_family_mini_group", "word_family_mini_group_member", "card_family_link", "synonym_word", "synonym_sense", "synonym_group", "synonym_group_member", "synonym_antonym", ] COLUMNS = { "exam": ("exam_id", "exam_code", "exam_name_zh", "is_grade_usable", "is_pseudo"), "book": ("book_id", "book_code", "book_name", "description", "exam_id", "declared_entry_count", "cover_url", "package_url", "content_version", "schema_version", "sort_by", "sort_at", "is_for_sale", "is_purchased", "is_opt", "source_code", "catalog_type", "labels", "publish_log", "pkg_page", "external_link", "has_wordbook_file"), "book_phrase_book": ("book_id", "phrase_book_id", "position"), "word_entry": ("entry_id", "headword", "index_word", "definition_text", "us_pronunciation", "uk_pronunciation", "data_version", "book_exam_id", "occurrence_count", "has_listening_examples", "source_zpk_key", "source_variant_code", "source_timestamp"), "book_entry": ("book_entry_id", "book_id", "entry_id", "display_word", "headword_text", "seq_book", "seq_az", "seq_freq", "seq_rand", "source_zpk_key"), "word_spelling_variant": ("entry_id", "position", "variant"), "word_sense": ("sense_id", "entry_id", "sense_no", "part_of_speech", "gloss_zh", "secondary_exam_codes", "flag_h"), "sense_object": ("meaning_id", "entry_id", "source_meaning_id", "sense_id"), "sense_exam": ("sense_id", "exam_id"), "sense_relation": ("sense_id", "relation_type", "position", "target_meaning_id"), "entry_exam_meaning_order": ("entry_id", "exam_id", "scope", "position", "meaning_id"), "phrase": ("phrase_id", "entry_id", "source_phrase_id", "meaning_id", "group_type", "text_en", "text_zh", "audio_ref", "extra_code"), "phrase_exam": ("phrase_id", "exam_id"), "phrase_exam_order": ("entry_id", "exam_id", "group_type", "position", "phrase_id"), "example_group": ("example_group_id", "entry_id", "meaning_id", "group_kind", "part_of_speech", "entry_kind_code", "head_form", "definition_en", "definition_zh", "youdao_code_1", "youdao_code_2", "source_exam_codes"), "example_group_exam": ("example_group_id", "exam_id"), "example_usage": ("usage_id", "example_group_id", "pattern_id", "pattern_text", "position"), "example_sentence": ("sentence_id", "source_sentence_key", "source_sentence_ext_key", "example_group_id", "usage_id", "position", "text_en", "text_zh", "audio_ref", "audio_url", "image_path", "source_title", "is_listening_corpus"), "entry_exam_sentence": ("entry_id", "exam_id", "position", "text_en", "text_zh", "source_desc"), "confusable_word": ("confusable_id", "entry_id", "position", "word_text", "gloss_zh", "pos_code", "spelling_flag"), "morpheme": ("morpheme_id", "morpheme_type", "source_morpheme_id", "form", "display_text", "meaning_en", "meaning_zh", "note"), "card": ("card_id", "word", "highlighted_word", "memory_note", "example_text", "prefix_morpheme_id", "root_morpheme_id", "suffix_morpheme_id", "default_family_ref", "is_hidden"), "card_exam": ("card_id", "exam_id"), "word_family": ("family_entry_id", "top_family_id", "word", "highlighted_word", "interpretation", "prefix_morpheme_id", "suffix_morpheme_id", "group_level_1", "group_level_2", "group_level_3"), "word_family_exam": ("family_entry_id", "exam_id"), "word_family_exam_order": ("family_entry_id", "exam_id", "position"), "word_family_variant": ("variant_id", "variant_word", "family_entry_id", "top_family_id", "highlighted_word", "interpretation", "prefix_morpheme_id", "suffix_morpheme_id"), "word_family_variant_member": ("variant_id", "exam_id", "position", "member_word", "is_placeholder"), "word_family_mini_group": ("mini_group_id", "size"), "word_family_mini_group_member": ("mini_group_id", "family_entry_id", "position"), "card_family_link": ("link_id", "card_id", "group_no", "family_ref", "is_hidden"), "synonym_word": ("word_id", "word"), "synonym_sense": ("sense_id", "word_id", "part_of_speech", "gloss_zh"), "synonym_group": ("sense_id", "exam_id", "canonical_word_id", "member_count"), "synonym_group_member": ("sense_id", "exam_id", "position", "member_word_id"), "synonym_antonym": ("sense_id", "exam_id", "position", "antonym_word_id"), } # 字段注释:DDL 生成时强制校验,缺一不可 COLUMN_COMMENTS = { # exam ("exam", "exam_id"): "主键;0=def 默认视图,1-13 真实考试,14=XX 未分类,15=OTHER 其他", ("exam", "exam_code"): "考试业务编码,仅作唯一键,不参与外键", ("exam", "exam_name_zh"): "考试中文名称", ("exam", "is_grade_usable"): "是否可用于年级/考试编码(仅 1-10 为真)", ("exam", "is_pseudo"): "是否为伪考试视图(def/XX/OTHER)", # book ("book", "book_id"): "词书主键,对应目录 id", ("book", "book_code"): "词书业务唯一编码,取自文件名后缀", ("book", "book_name"): "词书名称", ("book", "description"): "词书描述", ("book", "exam_id"): "关联考试 id,空表示非考试类", ("book", "declared_entry_count"): "源端声明的词条数", ("book", "cover_url"): "封面图片地址", ("book", "package_url"): "词书数据包地址", ("book", "content_version"): "内容版本", ("book", "schema_version"): "目录结构版本", ("book", "sort_by"): "排序方式", ("book", "sort_at"): "排序时间,Unix 秒", ("book", "is_for_sale"): "是否售卖", ("book", "is_purchased"): "是否已购买", ("book", "is_opt"): "是否可选项", ("book", "source_code"): "来源编码", ("book", "catalog_type"): "目录类型", ("book", "labels"): "标签文本", ("book", "publish_log"): "发布日志", ("book", "pkg_page"): "包页面配置,JSON", ("book", "external_link"): "外部链接配置,JSON", ("book", "has_wordbook_file"): "是否存在词书清单文件", # book_phrase_book ("book_phrase_book", "book_id"): "词书 id", ("book_phrase_book", "phrase_book_id"): "内嵌短语书的词书 id", ("book_phrase_book", "position"): "内嵌顺序", # word_entry ("word_entry", "entry_id"): "词条主键,取自 zpk 文件名第 2 段", ("word_entry", "headword"): "词条原形", ("word_entry", "index_word"): "归一化索引词", ("word_entry", "definition_text"): "原始释义文本(词性 + 中文)", ("word_entry", "us_pronunciation"): "美式音标", ("word_entry", "uk_pronunciation"): "英式音标", ("word_entry", "data_version"): "源数据版本", ("word_entry", "book_exam_id"): "所属考试 id", ("word_entry", "occurrence_count"): "源端声明的收录词书数", ("word_entry", "has_listening_examples"): "是否包含听力语料例句", ("word_entry", "source_zpk_key"): "源 zpk 文件键,仅溯源", ("word_entry", "source_variant_code"): "源文件名中的变体编码", ("word_entry", "source_timestamp"): "源文件名中的时间戳,Unix 秒", # book_entry ("book_entry", "book_entry_id"): "词书词条关联主键", ("book_entry", "book_id"): "词书 id", ("book_entry", "entry_id"): "词条 id", ("book_entry", "display_word"): "词书中的展示词形", ("book_entry", "headword_text"): "主词/词根提示,弱文本引用", ("book_entry", "seq_book"): "词书内顺序", ("book_entry", "seq_az"): "字母序", ("book_entry", "seq_freq"): "词频排名,0 表示未知", ("book_entry", "seq_rand"): "随机顺序", ("book_entry", "source_zpk_key"): "源 zpk 文件键,仅溯源", # word_spelling_variant ("word_spelling_variant", "entry_id"): "词条 id", ("word_spelling_variant", "position"): "变体顺序", ("word_spelling_variant", "variant"): "拼写变体文本", # word_sense ("word_sense", "sense_id"): "义项主键(代理)", ("word_sense", "entry_id"): "词条 id", ("word_sense", "sense_no"): "词条内义项序号,源值不连续", ("word_sense", "part_of_speech"): "词性", ("word_sense", "gloss_zh"): "中文释义", ("word_sense", "secondary_exam_codes"): "源字段 s,次要考试编码列表,逗号分隔", ("word_sense", "flag_h"): "源字段 h,数值标记", # sense_object ("sense_object", "meaning_id"): "释义对象主键(代理)", ("sense_object", "entry_id"): "词条 id", ("sense_object", "source_meaning_id"): "源释义对象 id(oid),仅词条内唯一", ("sense_object", "sense_id"): "所属义项 id", # sense_exam ("sense_exam", "sense_id"): "义项 id", ("sense_exam", "exam_id"): "考试 id", # sense_relation ("sense_relation", "sense_id"): "义项 id", ("sense_relation", "relation_type"): "关系类型:synonym/antonym/reference", ("sense_relation", "position"): "关系内顺序", ("sense_relation", "target_meaning_id"): "目标释义对象 id", # entry_exam_meaning_order ("entry_exam_meaning_order", "entry_id"): "词条 id", ("entry_exam_meaning_order", "exam_id"): "考试 id", ("entry_exam_meaning_order", "scope"): "释义范围:core 核心 / ext 扩展", ("entry_exam_meaning_order", "position"): "范围内顺序", ("entry_exam_meaning_order", "meaning_id"): "释义对象 id", # phrase ("phrase", "phrase_id"): "短语主键(代理)", ("phrase", "entry_id"): "词条 id", ("phrase", "source_phrase_id"): "源短语 id(pid),仅词条内唯一", ("phrase", "meaning_id"): "所属释义对象 id", ("phrase", "group_type"): "短语组类型,源值 1/2", ("phrase", "text_en"): "英文短语", ("phrase", "text_zh"): "中文释义", ("phrase", "audio_ref"): "发音资源标识,非外键", ("phrase", "extra_code"): "源附加编码 bd", # phrase_exam ("phrase_exam", "phrase_id"): "短语 id", ("phrase_exam", "exam_id"): "考试 id", # phrase_exam_order ("phrase_exam_order", "entry_id"): "词条 id", ("phrase_exam_order", "exam_id"): "考试 id", ("phrase_exam_order", "group_type"): "短语组类型", ("phrase_exam_order", "position"): "分考试顺序", ("phrase_exam_order", "phrase_id"): "短语 id", # example_group ("example_group", "example_group_id"): "例句块主键(代理)", ("example_group", "entry_id"): "词条 id", ("example_group", "meaning_id"): "所属释义对象 id,type 99 为空", ("example_group", "group_kind"): "例句块类型:1 核心,2 扩展,3 短语动词,4 习语,99 听力语料", ("example_group", "part_of_speech"): "词性", ("example_group", "entry_kind_code"): "词条类型码:NORMAL/PHRV/IDM/PHR", ("example_group", "head_form"): "词头形式", ("example_group", "definition_en"): "英文释义", ("example_group", "definition_zh"): "中文释义", ("example_group", "youdao_code_1"): "源附加编码 yu", ("example_group", "youdao_code_2"): "源附加编码 yo", ("example_group", "source_exam_codes"): "源考试编码快照,规范值见 example_group_exam", # example_group_exam ("example_group_exam", "example_group_id"): "例句块 id", ("example_group_exam", "exam_id"): "考试 id", # example_usage ("example_usage", "usage_id"): "用法模式主键(代理)", ("example_usage", "example_group_id"): "例句块 id", ("example_usage", "pattern_id"): "关联短语 id(源 uid 解析)", ("example_usage", "pattern_text"): "用法模式文本", ("example_usage", "position"): "组内顺序", # example_sentence ("example_sentence", "sentence_id"): "句子主键(代理)", ("example_sentence", "source_sentence_key"): "源句子标识 sid,不唯一", ("example_sentence", "source_sentence_ext_key"): "源句子扩展标识 eid,不唯一", ("example_sentence", "example_group_id"): "例句块 id", ("example_sentence", "usage_id"): "用法模式 id,type 99 为空", ("example_sentence", "position"): "组内顺序", ("example_sentence", "text_en"): "英文句子,含高亮标签", ("example_sentence", "text_zh"): "中文翻译", ("example_sentence", "audio_ref"): "发音资源标识,非外键", ("example_sentence", "audio_url"): "音频地址", ("example_sentence", "image_path"): "图片路径", ("example_sentence", "source_title"): "来源书名或剧名", ("example_sentence", "is_listening_corpus"): "是否听力语料例句", # entry_exam_sentence ("entry_exam_sentence", "entry_id"): "词条 id", ("entry_exam_sentence", "exam_id"): "考试 id", ("entry_exam_sentence", "position"): "顺序", ("entry_exam_sentence", "text_en"): "英文例句", ("entry_exam_sentence", "text_zh"): "中文翻译", ("entry_exam_sentence", "source_desc"): "来源说明", # confusable_word ("confusable_word", "confusable_id"): "易混词主键(代理)", ("confusable_word", "entry_id"): "词条 id", ("confusable_word", "position"): "顺序", ("confusable_word", "word_text"): "易混词文本", ("confusable_word", "gloss_zh"): "中文释义", ("confusable_word", "pos_code"): "源词性编码", ("confusable_word", "spelling_flag"): "拼写标记", # morpheme ("morpheme", "morpheme_id"): "词素主键(统一代理)", ("morpheme", "morpheme_type"): "词素类型:prefix/root/suffix", ("morpheme", "source_morpheme_id"): "源表内 id", ("morpheme", "form"): "词素形式", ("morpheme", "display_text"): "原始展示文本", ("morpheme", "meaning_en"): "英文释义", ("morpheme", "meaning_zh"): "中文释义", ("morpheme", "note"): "备注", # card ("card", "card_id"): "卡片主键", ("card", "word"): "单词", ("card", "highlighted_word"): "高亮 HTML 快照", ("card", "memory_note"): "记忆提示", ("card", "example_text"): "例句文本", ("card", "prefix_morpheme_id"): "前缀词素 id", ("card", "root_morpheme_id"): "词根词素 id", ("card", "suffix_morpheme_id"): "后缀词素 id", ("card", "default_family_ref"): "旧版词族引用,不可解析,无外键", ("card", "is_hidden"): "是否隐藏", # card_exam ("card_exam", "card_id"): "卡片 id", ("card_exam", "exam_id"): "考试 id", # word_family ("word_family", "family_entry_id"): "词族词条主键", ("word_family", "top_family_id"): "顶层词族 id,自引用", ("word_family", "word"): "单词", ("word_family", "highlighted_word"): "高亮 HTML 快照", ("word_family", "interpretation"): "词性 + 中文释义", ("word_family", "prefix_morpheme_id"): "前缀词素 id", ("word_family", "suffix_morpheme_id"): "后缀词素 id", ("word_family", "group_level_1"): "分组层级 1", ("word_family", "group_level_2"): "分组层级 2", ("word_family", "group_level_3"): "分组层级 3", # word_family_exam ("word_family_exam", "family_entry_id"): "词族词条 id", ("word_family_exam", "exam_id"): "考试 id", # word_family_exam_order ("word_family_exam_order", "family_entry_id"): "词族词条 id", ("word_family_exam_order", "exam_id"): "考试 id", ("word_family_exam_order", "position"): "分考试顺序", # word_family_variant ("word_family_variant", "variant_id"): "变体主键", ("word_family_variant", "variant_word"): "变体词", ("word_family_variant", "family_entry_id"): "所属词族词条 id", ("word_family_variant", "top_family_id"): "顶层词族 id", ("word_family_variant", "highlighted_word"): "高亮 HTML 快照", ("word_family_variant", "interpretation"): "词性 + 中文释义", ("word_family_variant", "prefix_morpheme_id"): "前缀词素 id", ("word_family_variant", "suffix_morpheme_id"): "后缀词素 id", # word_family_variant_member ("word_family_variant_member", "variant_id"): "变体 id", ("word_family_variant_member", "exam_id"): "考试 id", ("word_family_variant_member", "position"): "顺序", ("word_family_variant_member", "member_word"): "成员词", ("word_family_variant_member", "is_placeholder"): "是否为 * 占位符", # word_family_mini_group ("word_family_mini_group", "mini_group_id"): "小组主键", ("word_family_mini_group", "size"): "小组成员数", # word_family_mini_group_member ("word_family_mini_group_member", "mini_group_id"): "小组 id", ("word_family_mini_group_member", "family_entry_id"): "词族词条 id", ("word_family_mini_group_member", "position"): "组内顺序", # card_family_link ("card_family_link", "link_id"): "关联主键", ("card_family_link", "card_id"): "卡片 id", ("card_family_link", "group_no"): "分组编号(1-50)", ("card_family_link", "family_ref"): "旧版词族引用,不可解析,无外键", ("card_family_link", "is_hidden"): "是否隐藏", # synonym_word ("synonym_word", "word_id"): "近义词词头主键", ("synonym_word", "word"): "单词", # synonym_sense ("synonym_sense", "sense_id"): "近义词义项主键", ("synonym_sense", "word_id"): "词头 id", ("synonym_sense", "part_of_speech"): "词性", ("synonym_sense", "gloss_zh"): "中文释义", # synonym_group ("synonym_group", "sense_id"): "近义词义项 id", ("synonym_group", "exam_id"): "考试 id", ("synonym_group", "canonical_word_id"): "规范词 id(源 free),可为空", ("synonym_group", "member_count"): "近义词成员数", # synonym_group_member ("synonym_group_member", "sense_id"): "近义词义项 id", ("synonym_group_member", "exam_id"): "考试 id", ("synonym_group_member", "position"): "组内顺序", ("synonym_group_member", "member_word_id"): "成员词 id", # synonym_antonym ("synonym_antonym", "sense_id"): "近义词义项 id", ("synonym_antonym", "exam_id"): "考试 id", ("synonym_antonym", "position"): "组内顺序", ("synonym_antonym", "antonym_word_id"): "反义词 id", } # PostgreSQL DDL;SQLite 通过类型替换复用 SCHEMA_PG = """ CREATE TABLE exam ( exam_id bigint PRIMARY KEY, exam_code varchar(16) NOT NULL UNIQUE, exam_name_zh varchar(32) NOT NULL, is_grade_usable boolean NOT NULL DEFAULT FALSE, is_pseudo boolean NOT NULL DEFAULT FALSE ); CREATE TABLE book ( book_id bigint PRIMARY KEY, book_code varchar(32) NOT NULL UNIQUE, book_name varchar(128) NOT NULL, description text, exam_id bigint REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, declared_entry_count integer, cover_url text, package_url text, content_version integer, schema_version integer, sort_by varchar(16), sort_at bigint, is_for_sale smallint, is_purchased smallint, is_opt smallint, source_code smallint, catalog_type smallint, labels text, publish_log text, pkg_page jsonb, external_link jsonb, has_wordbook_file boolean NOT NULL DEFAULT TRUE ); CREATE TABLE book_phrase_book ( book_id bigint NOT NULL REFERENCES book(book_id) DEFERRABLE INITIALLY DEFERRED, phrase_book_id bigint NOT NULL REFERENCES book(book_id) DEFERRABLE INITIALLY DEFERRED, position smallint NOT NULL, PRIMARY KEY (book_id, phrase_book_id) ); CREATE TABLE word_entry ( entry_id bigint PRIMARY KEY, headword varchar(256) NOT NULL, index_word varchar(256) NOT NULL, definition_text text, us_pronunciation varchar(128), uk_pronunciation varchar(128), data_version varchar(8), book_exam_id bigint REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, occurrence_count integer, has_listening_examples boolean NOT NULL DEFAULT FALSE, source_zpk_key varchar(64) UNIQUE, source_variant_code integer, source_timestamp bigint ); CREATE TABLE book_entry ( book_entry_id bigint PRIMARY KEY, book_id bigint NOT NULL REFERENCES book(book_id) DEFERRABLE INITIALLY DEFERRED, entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, display_word varchar(256) NOT NULL, headword_text varchar(256), seq_book integer, seq_az integer, seq_freq integer, seq_rand integer, source_zpk_key varchar(64), UNIQUE (book_id, entry_id) ); CREATE TABLE word_spelling_variant ( entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, position smallint NOT NULL, variant varchar(256) NOT NULL, PRIMARY KEY (entry_id, position) ); CREATE TABLE word_sense ( sense_id bigint PRIMARY KEY, entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, sense_no integer NOT NULL, part_of_speech varchar(32), gloss_zh text, secondary_exam_codes text, flag_h smallint ); CREATE TABLE sense_object ( meaning_id bigint PRIMARY KEY, entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, source_meaning_id bigint NOT NULL, sense_id bigint REFERENCES word_sense(sense_id) DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE sense_exam ( sense_id bigint NOT NULL REFERENCES word_sense(sense_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (sense_id, exam_id) ); CREATE TABLE sense_relation ( sense_id bigint NOT NULL REFERENCES word_sense(sense_id) DEFERRABLE INITIALLY DEFERRED, relation_type varchar(16) NOT NULL, position smallint NOT NULL, target_meaning_id bigint NOT NULL REFERENCES sense_object(meaning_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (sense_id, relation_type, position) ); CREATE TABLE entry_exam_meaning_order ( entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, scope varchar(8) NOT NULL CHECK (scope IN ('core','ext')), position smallint NOT NULL, meaning_id bigint NOT NULL REFERENCES sense_object(meaning_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (entry_id, exam_id, scope, position) ); CREATE TABLE phrase ( phrase_id bigint PRIMARY KEY, entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, source_phrase_id bigint, meaning_id bigint REFERENCES sense_object(meaning_id) DEFERRABLE INITIALLY DEFERRED, group_type smallint NOT NULL, text_en text NOT NULL, text_zh text, audio_ref varchar(64), extra_code varchar(16), UNIQUE (entry_id, source_phrase_id) ); CREATE TABLE phrase_exam ( phrase_id bigint NOT NULL REFERENCES phrase(phrase_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (phrase_id, exam_id) ); CREATE TABLE phrase_exam_order ( entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, group_type smallint NOT NULL, position smallint NOT NULL, phrase_id bigint NOT NULL REFERENCES phrase(phrase_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (entry_id, exam_id, group_type, position) ); CREATE TABLE example_group ( example_group_id bigint PRIMARY KEY, entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, meaning_id bigint REFERENCES sense_object(meaning_id) DEFERRABLE INITIALLY DEFERRED, group_kind smallint NOT NULL, part_of_speech varchar(32), entry_kind_code varchar(16), head_form varchar(256), definition_en text, definition_zh text, youdao_code_1 varchar(64), youdao_code_2 varchar(64), source_exam_codes text ); CREATE TABLE example_group_exam ( example_group_id bigint NOT NULL REFERENCES example_group(example_group_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (example_group_id, exam_id) ); CREATE TABLE example_usage ( usage_id bigint PRIMARY KEY, example_group_id bigint NOT NULL REFERENCES example_group(example_group_id) DEFERRABLE INITIALLY DEFERRED, pattern_id bigint REFERENCES phrase(phrase_id) DEFERRABLE INITIALLY DEFERRED, pattern_text text, position smallint NOT NULL ); CREATE TABLE example_sentence ( sentence_id bigint PRIMARY KEY, source_sentence_key varchar(64), source_sentence_ext_key varchar(64), example_group_id bigint NOT NULL REFERENCES example_group(example_group_id) DEFERRABLE INITIALLY DEFERRED, usage_id bigint REFERENCES example_usage(usage_id) DEFERRABLE INITIALLY DEFERRED, position smallint, text_en text NOT NULL, text_zh text, audio_ref varchar(64), audio_url text, image_path text, source_title varchar(256), is_listening_corpus boolean NOT NULL DEFAULT FALSE ); CREATE TABLE entry_exam_sentence ( entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, position smallint NOT NULL, text_en text NOT NULL, text_zh text, source_desc text, PRIMARY KEY (entry_id, exam_id, position) ); CREATE TABLE confusable_word ( confusable_id bigint PRIMARY KEY, entry_id bigint NOT NULL REFERENCES word_entry(entry_id) DEFERRABLE INITIALLY DEFERRED, position smallint NOT NULL, word_text varchar(256) NOT NULL, gloss_zh text, pos_code smallint, spelling_flag varchar(8), UNIQUE (entry_id, word_text) ); CREATE TABLE morpheme ( morpheme_id bigint PRIMARY KEY, morpheme_type varchar(8) NOT NULL CHECK (morpheme_type IN ('prefix','root','suffix')), source_morpheme_id bigint NOT NULL, form varchar(64), display_text varchar(256) NOT NULL, meaning_en varchar(256), meaning_zh varchar(256), note varchar(256), UNIQUE (morpheme_type, source_morpheme_id) ); CREATE TABLE card ( card_id bigint PRIMARY KEY, word varchar(256) NOT NULL, highlighted_word text, memory_note text, example_text text, prefix_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED, root_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED, suffix_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED, default_family_ref bigint, is_hidden boolean NOT NULL DEFAULT FALSE ); CREATE TABLE card_exam ( card_id bigint NOT NULL REFERENCES card(card_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (card_id, exam_id) ); CREATE TABLE word_family ( family_entry_id bigint PRIMARY KEY, top_family_id bigint NOT NULL REFERENCES word_family(family_entry_id) DEFERRABLE INITIALLY DEFERRED, word varchar(256) NOT NULL, highlighted_word text, interpretation text, prefix_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED, suffix_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED, group_level_1 smallint, group_level_2 smallint, group_level_3 smallint ); CREATE TABLE word_family_exam ( family_entry_id bigint NOT NULL REFERENCES word_family(family_entry_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (family_entry_id, exam_id) ); CREATE TABLE word_family_exam_order ( family_entry_id bigint NOT NULL REFERENCES word_family(family_entry_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, position integer NOT NULL, PRIMARY KEY (family_entry_id, exam_id) ); CREATE TABLE word_family_variant ( variant_id bigint PRIMARY KEY, variant_word varchar(256) NOT NULL, family_entry_id bigint NOT NULL REFERENCES word_family(family_entry_id) DEFERRABLE INITIALLY DEFERRED, top_family_id bigint NOT NULL REFERENCES word_family(family_entry_id) DEFERRABLE INITIALLY DEFERRED, highlighted_word text, interpretation text, prefix_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED, suffix_morpheme_id bigint REFERENCES morpheme(morpheme_id) DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE word_family_variant_member ( variant_id bigint NOT NULL REFERENCES word_family_variant(variant_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, position smallint NOT NULL, member_word varchar(256) NOT NULL, is_placeholder boolean NOT NULL DEFAULT FALSE, PRIMARY KEY (variant_id, exam_id, position) ); CREATE TABLE word_family_mini_group ( mini_group_id bigint PRIMARY KEY, size smallint NOT NULL ); CREATE TABLE word_family_mini_group_member ( mini_group_id bigint NOT NULL REFERENCES word_family_mini_group(mini_group_id) DEFERRABLE INITIALLY DEFERRED, family_entry_id bigint NOT NULL REFERENCES word_family(family_entry_id) DEFERRABLE INITIALLY DEFERRED, position smallint NOT NULL, PRIMARY KEY (mini_group_id, family_entry_id) ); CREATE TABLE card_family_link ( link_id bigint PRIMARY KEY, card_id bigint NOT NULL REFERENCES card(card_id) DEFERRABLE INITIALLY DEFERRED, group_no smallint NOT NULL, family_ref bigint, is_hidden boolean NOT NULL DEFAULT FALSE ); CREATE TABLE synonym_word ( word_id bigint PRIMARY KEY, word varchar(256) NOT NULL ); CREATE TABLE synonym_sense ( sense_id bigint PRIMARY KEY, word_id bigint NOT NULL REFERENCES synonym_word(word_id) DEFERRABLE INITIALLY DEFERRED, part_of_speech varchar(32), gloss_zh text ); CREATE TABLE synonym_group ( sense_id bigint NOT NULL REFERENCES synonym_sense(sense_id) DEFERRABLE INITIALLY DEFERRED, exam_id bigint NOT NULL REFERENCES exam(exam_id) DEFERRABLE INITIALLY DEFERRED, canonical_word_id bigint REFERENCES synonym_word(word_id) DEFERRABLE INITIALLY DEFERRED, member_count smallint NOT NULL, PRIMARY KEY (sense_id, exam_id) ); CREATE TABLE synonym_group_member ( sense_id bigint NOT NULL, exam_id bigint NOT NULL, position smallint NOT NULL, member_word_id bigint NOT NULL REFERENCES synonym_word(word_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (sense_id, exam_id, position), FOREIGN KEY (sense_id, exam_id) REFERENCES synonym_group(sense_id, exam_id) DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE synonym_antonym ( sense_id bigint NOT NULL, exam_id bigint NOT NULL, position smallint NOT NULL, antonym_word_id bigint NOT NULL REFERENCES synonym_word(word_id) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (sense_id, exam_id, position), FOREIGN KEY (sense_id, exam_id) REFERENCES synonym_group(sense_id, exam_id) DEFERRABLE INITIALLY DEFERRED ); """ INDEXES = """ CREATE INDEX idx_book_entry_book_seq ON book_entry (book_id, seq_book); CREATE INDEX idx_book_entry_entry ON book_entry (entry_id); CREATE INDEX idx_word_entry_index_word ON word_entry (index_word); CREATE INDEX idx_word_sense_entry ON word_sense (entry_id, sense_no); CREATE INDEX idx_sense_object_entry ON sense_object (entry_id, source_meaning_id); CREATE INDEX idx_phrase_entry ON phrase (entry_id); CREATE INDEX idx_phrase_meaning ON phrase (meaning_id); CREATE INDEX idx_example_group_entry ON example_group (entry_id, group_kind); CREATE INDEX idx_example_group_meaning ON example_group (meaning_id); CREATE INDEX idx_example_sentence_group ON example_sentence (example_group_id); CREATE INDEX idx_example_sentence_usage ON example_sentence (usage_id); CREATE INDEX idx_example_sentence_src ON example_sentence (source_sentence_key); CREATE INDEX idx_entry_exam_sentence_entry ON entry_exam_sentence (entry_id); CREATE INDEX idx_confusable_entry ON confusable_word (entry_id); CREATE INDEX idx_morpheme_form ON morpheme (morpheme_type, form); CREATE INDEX idx_word_family_word ON word_family (word); CREATE INDEX idx_word_family_top ON word_family (top_family_id); CREATE INDEX idx_mini_member_family ON word_family_mini_group_member (family_entry_id); CREATE INDEX idx_card_word ON card (word); CREATE INDEX idx_card_family_link_card ON card_family_link (card_id); CREATE INDEX idx_synonym_word_text ON synonym_word (word); CREATE INDEX idx_syn_group_member_word ON synonym_group_member (member_word_id); CREATE INDEX idx_syn_antonym_word ON synonym_antonym (antonym_word_id); """ EXAM_CODES = { "def": (0, "默认视图", True), "ZK": (1, "中考", False), "GK": (2, "高考", False), "CET4": (3, "四级", False), "CET6": (4, "六级", False), "KY": (5, "考研", False), "IELTS": (6, "雅思", False), "TOEFL": (7, "托福", False), "PRO4": (8, "专四", False), "PRO8": (9, "专八", False), "ZSB": (10, "专升本", False), "KB": (11, "考博", False), "SAT": (12, "SAT", False), "GRE": (13, "GRE", False), "XX": (14, "未分类", True), "OTHER": (15, "其他", True), } GRADE_USABLE = set(range(1, 11)) SYNONYM_EXAM_COLUMNS = { "zk": 1, "gk": 2, "cet4": 3, "cet6": 4, "ky": 5, "ielts": 6, "toefl": 7, "zsb": 10, "other": 15, } # --------------------------------------------------------------------------- # 工具函数 # --------------------------------------------------------------------------- def _to_int(value, default=None): if value is None: return default if isinstance(value, bool): return int(value) if isinstance(value, int): return value s = str(value).strip() if not s: return default try: return int(s) except ValueError: try: return int(float(s)) except ValueError: return default def _to_bool(value): return 1 if str(value).strip().lower() in ("1", "true", "yes", "y") else 0 def _text(value): if value is None: return None s = str(value) return s if s != "" else None def read_csv(path): with open(path, encoding="utf-8-sig", newline="") as f: yield from csv.DictReader(f) def exam_id_of(code): if code is None: return None s = str(code).strip() if not s: return None if s in EXAM_CODES: return EXAM_CODES[s][0] up = s.upper() if up in EXAM_CODES: return EXAM_CODES[up][0] return None def parse_affix(display): """'duct = to lead 引导' -> (form, meaning_en, meaning_zh, note)""" text = (display or "").strip() if "=" in text: left, right = text.split("=", 1) form = left.strip() right = right.strip() else: form, right = text, "" # 以第一个 CJK 字符为界 m = re.search(r"[\u3400-\u9fff]", right) if m: en = right[:m.start()].strip() or None zh = right[m.start():].strip() or None note = None if ":" in zh: zh, note = zh.split(":", 1) zh, note = zh.strip(), note.strip() or None elif ":" in zh: zh, note = zh.split(":", 1) zh, note = zh.strip(), note.strip() or None return form, en, zh, note return form, right or None, None, None # --------------------------------------------------------------------------- # 数据库适配 # --------------------------------------------------------------------------- class SQLiteAdapter: placeholder = "?" def __init__(self, path): self.conn = sqlite3.connect(path) self.conn.execute("PRAGMA journal_mode=OFF") self.conn.execute("PRAGMA synchronous=OFF") self.conn.execute("PRAGMA temp_store=MEMORY") self.conn.execute("PRAGMA cache_size=-200000") self.conn.execute("PRAGMA foreign_keys=OFF") self.cur = self.conn.cursor() def begin(self): self.conn.execute("BEGIN") def execute(self, sql, params=()): self.cur.execute(sql, params) def execute_script(self, script): self.cur.executescript(script) def insert_many(self, table, columns, rows): sql = "INSERT INTO {} ({}) VALUES ({})".format( table, ",".join(columns), ",".join("?" * len(columns))) self.cur.executemany(sql, rows) def commit(self): self.conn.commit() def rollback(self): self.conn.rollback() def close(self): self.conn.close() def table_names(self): return {r[0] for r in self.cur.execute( "SELECT name FROM sqlite_master WHERE type='table'")} class PGAdapter: placeholder = "%s" def __init__(self, dsn): try: import psycopg2 from psycopg2.extras import execute_values except ImportError: try: import psycopg as psycopg2 from psycopg.extras import execute_values except ImportError as exc: raise SystemExit( "需要 psycopg2:pip install psycopg2-binary") from exc self.conn = psycopg2.connect(dsn) self.conn.autocommit = False self.cur = self.conn.cursor() self._execute_values = execute_values def begin(self): pass def execute(self, sql, params=()): self.cur.execute(sql, params) def execute_script(self, script): for stmt in script.split(";"): if stmt.strip(): self.cur.execute(stmt) def insert_many(self, table, columns, rows): base = "INSERT INTO {} ({}) VALUES %s".format( table, ",".join(columns)) self._execute_values(self.cur, base, rows, page_size=1000) def commit(self): self.conn.commit() def rollback(self): self.conn.rollback() def close(self): self.conn.close() def table_names(self): self.cur.execute( "SELECT table_name FROM information_schema.tables " "WHERE table_schema='public'") return {r[0] for r in self.cur.fetchall()} class MySQLAdapter: placeholder = "%s" def __init__(self, dsn): try: import pymysql except ImportError: try: import mysql.connector as pymysql except ImportError as exc: raise SystemExit( "需要 MySQL 驱动:pip install pymysql") from exc parsed = urlparse(dsn) if parsed.scheme not in ("mysql", "mysql+pymysql", ""): raise SystemExit("MySQL DSN 应形如 mysql://user:pass@host:3306/db") self.conn = pymysql.connect( host=parsed.hostname or "127.0.0.1", port=parsed.port or 3306, user=unquote(parsed.username or "root"), password=unquote(parsed.password or ""), database=(parsed.path or "/").lstrip("/"), charset="utf8mb4", autocommit=False, ) self.cur = self.conn.cursor() def begin(self): pass def execute(self, sql, params=()): self.cur.execute(sql, params) def execute_script(self, script): for stmt in script.split(";"): if stmt.strip(): self.cur.execute(stmt) def insert_many(self, table, columns, rows): sql = "INSERT INTO {} ({}) VALUES ({})".format( table, ",".join(columns), ",".join(["%s"] * len(columns))) self.cur.executemany(sql, rows) def commit(self): self.conn.commit() def rollback(self): self.conn.rollback() def close(self): self.conn.close() def table_names(self): self.cur.execute( "SELECT table_name FROM information_schema.tables " "WHERE table_schema = DATABASE()") return {r[0] for r in self.cur.fetchall()} NON_COLUMN_KEYWORDS = { "PRIMARY", "FOREIGN", "UNIQUE", "CHECK", "CONSTRAINT", "KEY", "INDEX", } def annotate_ddl(ddl, dialect): """为每个字段追加注释;MySQL 同时把行内 REFERENCES 提升为表级外键。""" out = [] table = None fks = [] col_re = re.compile(r"^(\s+)([a-z_][a-z0-9_]*)\s+(.+?)(,?)\s*$") for line in ddl.splitlines(): m = re.match(r"\s*CREATE TABLE\s+(\w+)\s*\(", line, re.I) if m: table = m.group(1) fks = [] elif table and line.strip().startswith(")"): if fks: if out and not out[-1].rstrip().endswith(","): out[-1] = out[-1].rstrip() + "," out.append(" " + ",\n ".join(fks)) table = None mc = col_re.match(line) if table else None if mc: col, body, comma = mc.group(2), mc.group(3).rstrip(), mc.group(4) if col.upper() not in NON_COLUMN_KEYWORDS: comment = COLUMN_COMMENTS.get((table, col)) if comment is None: raise ValueError(f"缺少字段注释: {table}.{col}") esc = comment.replace("'", "''") if dialect == "mysql": ref = re.search( r"\s+REFERENCES\s+(\w+)\s*\(([^)]+)\)\s*$", body, re.I) if ref: body = body[:ref.start()].rstrip() fks.append("FOREIGN KEY ({}) REFERENCES {}({})".format( col, ref.group(1), ref.group(2))) chk = re.search(r"\s+CHECK\s*\((.+)\)\s*$", body, re.I) if chk: body = body[:chk.start()].rstrip() fks.append("CHECK ({})".format(chk.group(1))) line = "{}{} {} COMMENT '{}'{}".format( mc.group(1), col, body, esc, comma) else: line = line.rstrip() if line.endswith(","): line = line[:-1].rstrip() + f", -- {comment}" else: line = line + f" -- {comment}" out.append(line) return "\n".join(out) def pg_comment_statements(): stmts = [] for table in TABLES: for col in COLUMNS[table]: comment = COLUMN_COMMENTS.get((table, col)) if comment is None: raise ValueError(f"缺少字段注释: {table}.{col}") esc = comment.replace("'", "''") stmts.append(f"COMMENT ON COLUMN {table}.{col} IS '{esc}'") return stmts def sqlite_ddl(): ddl = SCHEMA_PG ddl = re.sub(r"\bDEFERRABLE INITIALLY DEFERRED\b", "", ddl) ddl = re.sub(r"\bjsonb\b", "TEXT", ddl) ddl = re.sub(r"\bboolean\b", "INTEGER", ddl) ddl = re.sub(r"\bbigint\b", "INTEGER", ddl) ddl = re.sub(r"\bsmallint\b", "INTEGER", ddl) ddl = re.sub(r"\bDEFAULT FALSE\b", "DEFAULT 0", ddl, flags=re.I) ddl = re.sub(r"\bDEFAULT TRUE\b", "DEFAULT 1", ddl, flags=re.I) return annotate_ddl(ddl, "sqlite") def mysql_ddl(): ddl = SCHEMA_PG ddl = re.sub(r"\bDEFERRABLE INITIALLY DEFERRED\b", "", ddl) ddl = re.sub(r"\bjsonb\b", "JSON", ddl) ddl = re.sub(r"\bboolean\b", "TINYINT(1)", ddl) ddl = re.sub(r"\bDEFAULT FALSE\b", "DEFAULT 0", ddl, flags=re.I) ddl = re.sub(r"\bDEFAULT TRUE\b", "DEFAULT 1", ddl, flags=re.I) parts = [] for stmt in ddl.split(";"): if not stmt.strip(): continue stmt = stmt.rstrip() if stmt.lstrip().upper().startswith("CREATE TABLE"): # 必须用 utf8mb4_bin:源数据存在仅大小写不同的唯一键组合 # (如 confusable_word 同一 entry 下 italic / Italic), # _ci 排序规则会把二者视为重复,与 SQLite/PostgreSQL 语义不一致。 stmt += " ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin" parts.append(stmt) ddl = ";\n".join(parts) + ";" return annotate_ddl(ddl, "mysql") # --------------------------------------------------------------------------- # 批量写入 # --------------------------------------------------------------------------- class Writer: def __init__(self, adapter, batch_size=10000): self.adapter = adapter self.batch_size = batch_size self.buffers = defaultdict(list) self.counts = defaultdict(int) def add(self, table, row): self.buffers[table].append(row) if len(self.buffers[table]) >= self.batch_size: # 按依赖顺序统一刷盘,兼容 MySQL 的即时外键检查 self.flush_all() def flush(self, table): buf = self.buffers[table] if not buf: return self.adapter.insert_many(table, COLUMNS[table], buf) self.counts[table] += len(buf) buf.clear() def flush_all(self): for table in TABLES: self.flush(table) def nid(name): nid.value[name] += 1 return nid.value[name] nid.value = defaultdict(int) # --------------------------------------------------------------------------- # 导入:公共维表 # --------------------------------------------------------------------------- def import_exams(w, data_dir): seen = set() path = os.path.join(data_dir, "wordroot-export", "word_family_exam.csv") if os.path.exists(path): for r in read_csv(path): code = (r.get("exam") or "").strip() eid = _to_int(r.get("id")) if not code or eid is None: continue name = (r.get("tag") or "").strip() or code usable = 1 if eid in GRADE_USABLE else 0 w.add("exam", (eid, code, name, usable, 0)) seen.add(eid) for code, (eid, name, pseudo) in EXAM_CODES.items(): if eid not in seen: usable = 1 if eid in GRADE_USABLE else 0 w.add("exam", (eid, code, name, usable, 1 if pseudo else 0)) w.flush("exam") print(f" exam: {w.counts['exam']}") # --------------------------------------------------------------------------- # 导入:词书 # --------------------------------------------------------------------------- def scan_books(data_dir, limit_books=None): """返回 books(meta), canonical(entry -> (ts,key,word_hint)), phrase_book_ids""" pattern = os.path.join(data_dir, "wordbooks", "decrypted", "LangEasyLexisV3_*.csv") files = sorted(glob.glob(pattern)) if limit_books: files = files[:limit_books] books = {} canonical = {} for path in files: code = os.path.basename(path)[len("LangEasyLexisV3_"):-4] first = None with open(path, encoding="utf-8-sig", newline="") as f: for row in csv.DictReader(f): if first is None: first = row eid = _to_int(row.get("id")) key = (row.get("zpk") or "").strip() if eid is None or not key: continue ts = _to_int(key.rsplit("_", 1)[-1], 0) hint = (row.get("word") or "").strip() or None prev = canonical.get(eid) if prev is None or ts > prev[0]: canonical[eid] = (ts, key, hint) if first is not None: books[code] = first return books, canonical def import_books(w, data_dir, limit_books=None): catalog_path = os.path.join(data_dir, "book-catalog.json") catalog = [] if os.path.exists(catalog_path): with open(catalog_path, encoding="utf-8-sig") as f: catalog = json.load(f) by_code = {c.get("code"): c for c in catalog} books, canonical = scan_books(data_dir, limit_books) book_ids = set() phrase_pairs = [] for code, first in books.items(): meta = by_code.get(code, {}) book_id = _to_int(first.get("book_id")) book_ids.add(book_id) exam_id = exam_id_of(first.get("book_exam") or meta.get("exam")) name = (first.get("book_name") or meta.get("name") or code).strip() declared = _to_int(first.get("book_count"), meta.get("count")) w.add("book", ( book_id, code, name, _text(meta.get("desc")), exam_id, declared, _text(meta.get("cover")), _text(meta.get("url")), _to_int(meta.get("version")), _to_int(meta.get("bookVersion")), _text(meta.get("sortBy")), _to_int(meta.get("sortTime")), _to_int(meta.get("forSale")), _to_int(meta.get("purchased")), _to_int(meta.get("opt")), _to_int(meta.get("src")), _to_int(meta.get("type")), _text(meta.get("labels")), _text(meta.get("publishLog")), json.dumps(meta.get("pkgPage"), ensure_ascii=False) if meta.get("pkgPage") is not None else None, json.dumps(meta.get("externalLink"), ensure_ascii=False) if meta.get("externalLink") is not None else None, 1, )) for pos, pid in enumerate(meta.get("phraseBookIds") or []): pid = _to_int(pid) if pid is not None: phrase_pairs.append((book_id, pid, pos)) # 目录中存在但磁盘无清单的词书 for code, meta in by_code.items(): if code in books: continue book_id = _to_int(meta.get("id")) if book_id is None: continue book_ids.add(book_id) w.add("book", ( book_id, code, (meta.get("name") or code), _text(meta.get("desc")), exam_id_of(meta.get("exam")), _to_int(meta.get("count")), _text(meta.get("cover")), _text(meta.get("url")), _to_int(meta.get("version")), _to_int(meta.get("bookVersion")), _text(meta.get("sortBy")), _to_int(meta.get("sortTime")), _to_int(meta.get("forSale")), _to_int(meta.get("purchased")), _to_int(meta.get("opt")), _to_int(meta.get("src")), _to_int(meta.get("type")), _text(meta.get("labels")), _text(meta.get("publishLog")), json.dumps(meta.get("pkgPage"), ensure_ascii=False) if meta.get("pkgPage") is not None else None, json.dumps(meta.get("externalLink"), ensure_ascii=False) if meta.get("externalLink") is not None else None, 0, )) for pos, pid in enumerate(meta.get("phraseBookIds") or []): pid = _to_int(pid) if pid is not None: phrase_pairs.append((book_id, pid, pos)) w.flush("book") seen_pp = set() for pair in phrase_pairs: if pair[1] in book_ids and pair[:2] not in seen_pp: seen_pp.add(pair[:2]) w.add("book_phrase_book", pair) w.flush("book_phrase_book") print(f" book: {w.counts['book']} book_phrase_book: {w.counts['book_phrase_book']}") return books, canonical def import_book_entries(w, data_dir, limit_books=None): pattern = os.path.join(data_dir, "wordbooks", "decrypted", "LangEasyLexisV3_*.csv") files = sorted(glob.glob(pattern)) if limit_books: files = files[:limit_books] seq = 0 for path in files: for r in read_csv(path): seq += 1 w.add("book_entry", ( seq, _to_int(r.get("book_id")), _to_int(r.get("id")), (r.get("word") or "").strip(), _text((r.get("main_word") or "").strip()), _to_int(r.get("seq_book")), _to_int(r.get("seq_az")), _to_int(r.get("seq_freq")), _to_int(r.get("seq_rand")), _text((r.get("zpk") or "").strip()), )) w.flush("book_entry") print(f" book_entry: {w.counts['book_entry']}") # --------------------------------------------------------------------------- # 导入:zpk 词条 # --------------------------------------------------------------------------- def _load_example(d): raw = d.get("example") if isinstance(raw, dict): return raw if isinstance(raw, str) and raw.strip(): try: return json.loads(raw) except json.JSONDecodeError: return {} return {} def _as_list(value): if value is None: return [] if isinstance(value, (list, tuple)): return list(value) return [value] def import_word_entries(w, data_dir, canonical): zpk_dir = os.path.join(data_dir, "zpk", "decrypted") total = len(canonical) done = 0 t0 = time.time() for eid in sorted(canonical): ts, key, hint = canonical[eid] path = os.path.join(zpk_dir, key + ".json") stem = key.split("_") variant_code = _to_int(stem[2]) if len(stem) > 2 else None if not os.path.exists(path): # 缺失文件:写入占位词条,保证 book_entry 外键成立 w.add("word_entry", ( eid, hint or str(eid), (hint or str(eid)).lower(), None, None, None, None, None, 0, 0, key, variant_code, ts)) done += 1 continue try: with open(path, encoding="utf-8") as f: d = json.load(f) except (json.JSONDecodeError, OSError) as exc: print(f" ! 跳过损坏 JSON {key}: {exc}") w.add("word_entry", ( eid, hint or str(eid), (hint or str(eid)).lower(), None, None, None, None, None, 0, 0, key, variant_code, ts)) done += 1 continue meta = d.get("meta") or {} ex = _load_example(d) word = (d.get("word") or hint or str(eid)).strip() w.add("word_entry", ( eid, word, (d.get("index_word") or word).strip(), _text(d.get("interpret")), _text(d.get("us_pron")), _text(d.get("uk_pron")), _text(meta.get("data_version")), exam_id_of(meta.get("book_exam")), _to_int(d.get("book_occurrences"), 0), 1 if ex.get("is_type_99") else 0, key, variant_code, ts, )) for pos, sp in enumerate(d.get("spell") or []): w.add("word_spelling_variant", (eid, pos, str(sp))) # 义项 + 释义对象 meaning_map = {} relations = [] for group in d.get("interpret_v2") or []: pos_name = group.get("p") for sense in group.get("d") or []: sid = nid("sense") w.add("word_sense", ( sid, eid, _to_int(sense.get("id"), 0), pos_name, _text(sense.get("i")), _text(",".join(str(x) for x in _as_list(sense.get("s")))), _to_int(sense.get("h")), )) seen_exam = set() for code in _as_list(sense.get("t")): xid = exam_id_of(code) if xid is not None and xid not in seen_exam: seen_exam.add(xid) w.add("sense_exam", (sid, xid)) for oid in _as_list(sense.get("oids")): oid = _to_int(oid) if oid is None: continue mid = nid("meaning") w.add("sense_object", (mid, eid, oid, sid)) meaning_map.setdefault(oid, mid) for field, rel in (("synids", "synonym"), ("antonymids", "antonym"), ("refids", "reference")): for apos, target in enumerate(_as_list(sense.get(field))): target = _to_int(target) if target is not None: relations.append((sid, rel, apos, target)) for sid, rel, apos, target in relations: mid = meaning_map.get(target) if mid is not None: w.add("sense_relation", (sid, rel, apos, mid)) # 短语 phrase_map = {} for pg in d.get("phrase") or []: gtype = _to_int(pg.get("t"), 0) for x in pg.get("p") or []: pid = nid("phrase") src_pid = _to_int(x.get("pid")) if src_pid is not None: phrase_map.setdefault(src_pid, pid) oid = _to_int(x.get("oid")) mid = meaning_map.get(oid) if oid is not None else None w.add("phrase", ( pid, eid, src_pid, mid, gtype, _text(x.get("en")) or "", _text(x.get("cn")), _text(x.get("fid")), _text(x.get("bd")), )) seen_exam = set() for code in _as_list(x.get("exams")): xid = exam_id_of(code) if xid is not None and xid not in seen_exam: seen_exam.add(xid) w.add("phrase_exam", (pid, xid)) for code, pids in (pg.get("pseqs") or {}).items(): xid = exam_id_of(code) if xid is None: continue for pos, src_pid in enumerate(pids): pid = phrase_map.get(_to_int(src_pid)) if pid is not None: w.add("phrase_exam_order", (eid, xid, gtype, pos, pid)) # 例句块 for block in ex.get("data") or []: kind = _to_int(block.get("type"), 99) info = block.get("i") or {} exam_ids = [exam_id_of(c) for c in _as_list(block.get("exams"))] exam_ids = list(dict.fromkeys(x for x in exam_ids if x is not None)) source_codes = ",".join( str(c) for c in _as_list(block.get("exams")) if c) if str(kind) == "99": egid = nid("example_group") w.add("example_group", ( egid, eid, None, 99, None, None, None, None, None, None, None, source_codes)) for sent in block.get("g") or []: if not isinstance(sent, dict): continue w.add("example_sentence", _sentence_row(sent, egid, None, True)) continue oid = _to_int(block.get("oid")) mid = meaning_map.get(oid) if oid is not None else None egid = nid("example_group") w.add("example_group", ( egid, eid, mid, kind, _text(info.get("p")), _text(info.get("t")), _text(info.get("u")), _text(info.get("e")), _text(info.get("c")), _text(info.get("yu")), _text(info.get("yo")), source_codes, )) for xid in exam_ids: w.add("example_group_exam", (egid, xid)) for upos, usage in enumerate(block.get("g") or []): uid = nid("usage") src_uid = _to_int(usage.get("uid")) pattern_id = phrase_map.get(src_uid) if src_uid else None w.add("example_usage", ( uid, egid, pattern_id, _text(usage.get("u")), upos)) for sent in usage.get("s") or []: if not isinstance(sent, dict): continue w.add("example_sentence", _sentence_row(sent, egid, uid, False)) # 考试例句 for code, arr in (d.get("exam_sentences") or {}).items(): xid = exam_id_of(code) if xid is None: continue for pos, item in enumerate(arr or []): if not isinstance(item, dict): continue w.add("entry_exam_sentence", ( eid, xid, pos, _text(item.get("en")) or "", _text(item.get("cn")), _text(item.get("src")), )) # 义项顺序 for code, obj in (ex.get("oseqs") or {}).items(): xid = exam_id_of(code) if xid is None or not isinstance(obj, dict): continue for scope in ("core", "ext"): seen_meaning = set() for pos, oid in enumerate(obj.get(scope) or []): mid = meaning_map.get(_to_int(oid)) if mid is not None and mid not in seen_meaning: seen_meaning.add(mid) w.add("entry_exam_meaning_order", (eid, xid, scope, pos, mid)) # 易混词 seen_confuse = set() cv = d.get("confuse_v2") if cv: for pos, item in enumerate(cv): if not isinstance(item, dict): continue text = (item.get("w") or "").strip() if not text or text in seen_confuse: continue seen_confuse.add(text) w.add("confusable_word", ( nid("confusable"), eid, pos, text, _text(item.get("i")), _to_int(item.get("p")), _text(item.get("sp")))) else: conf = d.get("confuse") or [] exps = d.get("confuse_exps") or [] for pos, text in enumerate(conf): text = str(text).strip() if not text or text in seen_confuse: continue seen_confuse.add(text) gloss = exps[pos] if pos < len(exps) else None w.add("confusable_word", ( nid("confusable"), eid, pos, text, _text(gloss), None, None)) done += 1 if done % 2000 == 0: elapsed = time.time() - t0 print(f" zpk: {done}/{total} ({elapsed:.0f}s)") w.flush_all() print(f" word_entry: {w.counts['word_entry']} " f"word_sense: {w.counts['word_sense']} " f"phrase: {w.counts['phrase']} " f"example_sentence: {w.counts['example_sentence']}") def _sentence_row(sent, egid, uid, listening): return ( nid("sentence"), _text((sent.get("sid") or "")), _text((sent.get("eid") or "")), egid, uid, _to_int(sent.get("s")), _text(sent.get("e")) or "", _text(sent.get("c")), _text(sent.get("fid")), _text(sent.get("u")), _text(sent.get("i")), _text(sent.get("b")), 1 if listening else 0, ) # --------------------------------------------------------------------------- # 导入:词根词族 # --------------------------------------------------------------------------- def import_wordroot(w, data_dir): base = os.path.join(data_dir, "wordroot-export") # 词素 morpheme_map = {} for typ, fname in (("prefix", "prefix.csv"), ("root", "root.csv"), ("suffix", "suffix.csv")): path = os.path.join(base, fname) if not os.path.exists(path): continue for r in read_csv(path): src = _to_int(r.get("id")) if src is None: continue mid = nid("morpheme") morpheme_map[(typ, src)] = mid form, en, zh, note = parse_affix(r.get("display")) w.add("morpheme", (mid, typ, src, form, r.get("display") or "", en, zh, note)) w.flush("morpheme") def morph(typ, value): src = _to_int(value) if src is None or src <= 0: return None return morpheme_map.get((typ, src)) # 卡片 card_path = os.path.join(base, "card_info.csv") if os.path.exists(card_path): for r in read_csv(card_path): cid = _to_int(r.get("id")) if cid is None: continue w.add("card", ( cid, (r.get("word") or "").strip(), _text(r.get("hightlight")), _text(r.get("memory")), _text(r.get("example")), morph("prefix", r.get("prefix")), morph("root", r.get("root")), morph("suffix", r.get("suffix")), _to_int(r.get("default_f_id")), _to_bool(r.get("hidden")), )) seen_exam = set() for g in (r.get("grade") or "").split(","): xid = _to_int(g) if xid is not None and xid not in seen_exam: seen_exam.add(xid) w.add("card_exam", (cid, xid)) w.flush("card") # 词族 family_path = os.path.join(base, "word_family.csv") family_ids = set() family_rows = list(read_csv(family_path)) if os.path.exists(family_path) else [] for r in family_rows: fid = _to_int(r.get("id")) if fid is not None: family_ids.add(fid) # 先插入自指根行,满足自外键 ordered = [r for r in family_rows if _to_int(r.get("id")) == _to_int(r.get("top_id"))] ordered += [r for r in family_rows if _to_int(r.get("id")) != _to_int(r.get("top_id"))] for r in ordered: fid = _to_int(r.get("id")) if fid is None: continue w.add("word_family", ( fid, _to_int(r.get("top_id"), fid), (r.get("word") or "").strip(), _text(r.get("hightlight")), _text(r.get("interpret")), morph("prefix", r.get("prefix")), morph("suffix", r.get("suffix")), _to_int(r.get("group1")), _to_int(r.get("group2")), _to_int(r.get("group3")), )) seen_exam = set() for g in (r.get("grade") or "").split(","): xid = _to_int(g) if xid is not None and xid not in seen_exam: seen_exam.add(xid) w.add("word_family_exam", (fid, xid)) try: j = json.loads(r.get("json") or "{}") except json.JSONDecodeError: j = {} for key, value in j.items(): xid = _to_int(key) pos = _to_int(value) if xid is not None and pos is not None: w.add("word_family_exam_order", (fid, xid, pos)) w.flush("word_family") # 变体 var_path = os.path.join(base, "word_family_variant.csv") variant_ids = [] if os.path.exists(var_path): for r in read_csv(var_path): vid = _to_int(r.get("id")) fid = _to_int(r.get("word_family_id")) if vid is None or fid not in family_ids: continue variant_ids.append(vid) w.add("word_family_variant", ( vid, (r.get("variant") or "").strip(), fid, _to_int(r.get("top_id"), fid), _text(r.get("hightlight")), _text(r.get("interpret")), morph("prefix", r.get("prefix")), morph("suffix", r.get("suffix")), )) try: j = json.loads(r.get("json") or "{}") except json.JSONDecodeError: j = {} for key, words in j.items(): xid = _to_int(key) if xid is None or not isinstance(words, list): continue for pos, word in enumerate(words): text = str(word) w.add("word_family_variant_member", ( vid, xid, pos, text, 1 if text == "*" else 0)) w.flush("word_family_variant") # 小组 group_path = os.path.join(base, "word_family_mini_group.csv") if os.path.exists(group_path): for r in read_csv(group_path): gid = _to_int(r.get("id")) if gid is None: continue tokens = [x.strip() for x in (r.get("word_ids") or "").split(",") if x.strip()] w.add("word_family_mini_group", (gid, len(tokens))) seen = set() for pos, tok in enumerate(tokens): fid = _to_int(tok) if fid is None or fid not in family_ids or fid in seen: continue seen.add(fid) w.add("word_family_mini_group_member", (gid, fid, pos)) w.flush("word_family_mini_group") # 卡片—分组 wg_path = os.path.join(base, "word_group.csv") if os.path.exists(wg_path): for r in read_csv(wg_path): lid = _to_int(r.get("id")) cid = _to_int(r.get("card_info_id")) if lid is None or cid is None: continue w.add("card_family_link", ( lid, cid, _to_int(r.get("group_id"), 0), _to_int(r.get("f_id")), _to_bool(r.get("hidden")), )) w.flush("card_family_link") print(f" morpheme: {w.counts['morpheme']} card: {w.counts['card']} " f"word_family: {w.counts['word_family']} " f"variant: {w.counts['word_family_variant']} " f"mini_group: {w.counts['word_family_mini_group']}") # --------------------------------------------------------------------------- # 导入:近义词 # --------------------------------------------------------------------------- def import_synonyms(w, data_dir): base = os.path.join(data_dir, "wordroot-export") words_path = os.path.join(base, "synonym_words.csv") word_ids = set() if os.path.exists(words_path): for r in read_csv(words_path): wid = _to_int(r.get("id")) if wid is None: continue word_ids.add(wid) w.add("synonym_word", (wid, (r.get("word") or "").strip())) w.flush("synonym_word") data_path = os.path.join(base, "synonym_data.csv") if not os.path.exists(data_path): print(" synonym_data.csv 不存在,跳过") return for r in read_csv(data_path): sid = _to_int(r.get("id")) wid = _to_int(r.get("word_id")) if sid is None or wid is None: continue w.add("synonym_sense", ( sid, wid, _text(r.get("pos")), _text(r.get("interpret")))) for column, xid in SYNONYM_EXAM_COLUMNS.items(): raw = (r.get(column) or "").strip() if not raw: continue try: cell = json.loads(raw) except json.JSONDecodeError: continue syn = [x for x in str(cell.get("syn") or "").split(",") if x.strip()] ant = [x for x in str(cell.get("ant") or "").split(",") if x.strip()] free = _to_int(cell.get("free")) if free is not None and free not in word_ids: free = None syn_ids = [] seen = set() for x in syn: v = _to_int(x) if v in word_ids and v not in seen: seen.add(v) syn_ids.append(v) w.add("synonym_group", (sid, xid, free, len(syn_ids))) for pos, v in enumerate(syn_ids): w.add("synonym_group_member", (sid, xid, pos, v)) for pos, x in enumerate(ant): v = _to_int(x) if v in word_ids: w.add("synonym_antonym", (sid, xid, pos, v)) w.flush_all() print(f" synonym_word: {w.counts['synonym_word']} " f"synonym_sense: {w.counts['synonym_sense']} " f"synonym_group: {w.counts['synonym_group']}") # --------------------------------------------------------------------------- # 主流程 # --------------------------------------------------------------------------- def drop_schema(adapter): if isinstance(adapter, SQLiteAdapter): adapter.execute("PRAGMA foreign_keys=OFF") for table in reversed(TABLES): adapter.execute(f"DROP TABLE IF EXISTS {table}") elif isinstance(adapter, MySQLAdapter): adapter.execute("SET FOREIGN_KEY_CHECKS=0") for table in reversed(TABLES): adapter.execute(f"DROP TABLE IF EXISTS {table}") adapter.execute("SET FOREIGN_KEY_CHECKS=1") else: for table in reversed(TABLES): adapter.execute(f"DROP TABLE IF EXISTS {table} CASCADE") def create_schema(adapter): if isinstance(adapter, SQLiteAdapter): script = sqlite_ddl() elif isinstance(adapter, MySQLAdapter): script = mysql_ddl() else: script = annotate_ddl(SCHEMA_PG, "pg") adapter.execute_script(script) if isinstance(adapter, PGAdapter): for stmt in pg_comment_statements(): adapter.execute(stmt) def create_indexes(adapter): for stmt in INDEXES.split(";"): if stmt.strip(): adapter.execute(stmt) def print_counts(adapter): print("\n导入结果:") total = 0 for table in TABLES: try: adapter.execute(f"SELECT COUNT(*) FROM {table}") n = adapter.cur.fetchone()[0] except Exception: n = -1 total += max(n, 0) print(f" {table:<34} {n:>10,}") print(f" {'TOTAL':<34} {total:>10,}") def validate_sqlite(adapter): try: rows = adapter.cur.execute("PRAGMA foreign_key_check").fetchall() except Exception as exc: print(f" 外键校验失败: {exc}") return print(f"\n外键校验: {'通过' if not rows else f'发现 {len(rows)} 条违规'}") for row in rows[:10]: print(" ", row) def main(argv=None): here = os.path.dirname(os.path.abspath(__file__)) default_data = os.path.join(here, "output") if not os.path.isdir(default_data): default_data = os.path.join(os.getcwd(), "output") ap = argparse.ArgumentParser(description="Lexis 词库一键建表并导入") ap.add_argument("--db", choices=("sqlite", "postgres", "mysql"), default="sqlite") ap.add_argument("--sqlite-path", default=os.path.join(here, "lexis.db")) ap.add_argument("--dsn", help="PostgreSQL / MySQL DSN") ap.add_argument("--data-dir", default=default_data) ap.add_argument("--recreate", action="store_true", help="删除并重建所有表") ap.add_argument("--batch-size", type=int, default=10000) ap.add_argument("--limit-books", type=int, default=None, help="仅导入前 N 本词书(调试用)") args = ap.parse_args(argv) data_dir = os.path.abspath(args.data_dir) if not os.path.isdir(data_dir): raise SystemExit(f"数据目录不存在: {data_dir}") nid.value.clear() print(f"数据目录: {data_dir}") if args.db == "sqlite": adapter = SQLiteAdapter(args.sqlite_path) print(f"目标: SQLite {os.path.abspath(args.sqlite_path)}") elif args.db == "mysql": if not args.dsn: raise SystemExit("--db mysql 时必须提供 --dsn") adapter = MySQLAdapter(args.dsn) print("目标: MySQL") else: if not args.dsn: raise SystemExit("--db postgres 时必须提供 --dsn") adapter = PGAdapter(args.dsn) print("目标: PostgreSQL") existing = adapter.table_names() & set(TABLES) if existing and not args.recreate: raise SystemExit( "数据库中已存在本模型的表;如需重建请加 --recreate") if existing and args.recreate: print("删除旧表...") drop_schema(adapter) print("建表...") create_schema(adapter) if isinstance(adapter, MySQLAdapter): adapter.execute("SET FOREIGN_KEY_CHECKS=0") adapter.execute("SET UNIQUE_CHECKS=0") adapter.begin() w = Writer(adapter, batch_size=args.batch_size) t0 = time.time() try: print("导入考试维表...") import_exams(w, data_dir) print("扫描词书清单...") books, canonical = import_books(w, data_dir, args.limit_books) print(f" 词书 {len(books)} 本,词条 {len(canonical)} 个") print("导入词条词典(每个 entry 取最新 zpk 版本)...") import_word_entries(w, data_dir, canonical) print("导入词书词条...") import_book_entries(w, data_dir, args.limit_books) print("导入词根词族...") import_wordroot(w, data_dir) print("导入近反义词...") import_synonyms(w, data_dir) adapter.commit() except Exception: adapter.rollback() raise if isinstance(adapter, MySQLAdapter): adapter.execute("SET FOREIGN_KEY_CHECKS=1") adapter.execute("SET UNIQUE_CHECKS=1") print(f"\n导入耗时 {time.time() - t0:.1f}s,创建索引...") adapter.begin() create_indexes(adapter) adapter.commit() print_counts(adapter) if isinstance(adapter, SQLiteAdapter): validate_sqlite(adapter) adapter.close() print("\n完成。") if __name__ == "__main__": main()