How it Works#

Google Sheet#

我维护着一个 Google Sheet, 它是这个项目的数据源. 这个 Google Sheet 是由我手动维护的.

Generate Code#

当我觉得有必要发布新版本时, 我需要用 Google Sheet 中的数据重新生成代码. 我先将 Google Sheet 另存为 Xlsx (Excel) 文件. 然后运行 debug_code_gen.py 脚本. 这个脚本中我会指定要将哪些 Tab 提取成 Dataset.

debug/debug_code_gen.py
 1# -*- coding: utf-8 -*-
 2
 3from pathlib_mate import Path
 4from acore_df.code_gen.api import Dataset, generate_code
 5
 6path_xlsx = Path("/Users/sanhehu/Downloads/azerothcore-dataframe.xlsx")
 7dataset_list = [
 8    Dataset(tab="item_template_class"),
 9    Dataset(tab="item_template_subclass"),
10    Dataset(tab="item_template_quality"),
11    Dataset(tab="item_template_bonding"),
12    Dataset(tab="item_template_allowable_class"),
13    Dataset(tab="item_template_stat_type", mapping={"type_id": "id"}),
14    Dataset(tab="item_template_damage_type"),
15    Dataset(tab="factions", id_col="faction_id"),
16]
17generate_code(path_xlsx, dataset_list)

这个脚本最终会生成一个 acore_df/model.py, 里面参考 Google Sheet 中的数据, 生成了 SqlAlchemy 的类和 DataClass 类. 然后提供了一个 Lookup 类, 用于查询这些数据.

acore_df/model.py
  1# -*- coding: utf-8 -*-
  2
  3import typing as T
  4import dataclasses
  5from pathlib import Path
  6
  7import sqlalchemy as sa
  8import sqlalchemy.orm as orm
  9
 10from .paths import path_sqlite
 11from .dataset import BaseDataset, download_sqlite
 12
 13Base = orm.declarative_base()
 14
 15
 16
 17class ItemTemplateClass(Base):
 18    __tablename__ = "item_template_class"
 19
 20    id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
 21    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 22    name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 23
 24
 25@dataclasses.dataclass
 26class ItemTemplateClassData:
 27    id: int = dataclasses.field()
 28    name: T.Optional[str] = dataclasses.field(default=None)
 29    name_cn: T.Optional[str] = dataclasses.field(default=None)
 30
 31
 32class ItemTemplateSubclass(Base):
 33    __tablename__ = "item_template_subclass"
 34
 35    id: orm.Mapped[str] = sa.Column(sa.String, primary_key=True)
 36    class_id: orm.Mapped[T.Optional[int]] = sa.Column(sa.Integer, nullable=True)
 37    class_name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 38    class_name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 39    subclass_id: orm.Mapped[T.Optional[int]] = sa.Column(sa.Integer, nullable=True)
 40    subclass_name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 41    subclass_name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 42    comments: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 43
 44
 45@dataclasses.dataclass
 46class ItemTemplateSubclassData:
 47    id: str = dataclasses.field()
 48    class_id: T.Optional[int] = dataclasses.field(default=None)
 49    class_name: T.Optional[str] = dataclasses.field(default=None)
 50    class_name_cn: T.Optional[str] = dataclasses.field(default=None)
 51    subclass_id: T.Optional[int] = dataclasses.field(default=None)
 52    subclass_name: T.Optional[str] = dataclasses.field(default=None)
 53    subclass_name_cn: T.Optional[str] = dataclasses.field(default=None)
 54    comments: T.Optional[str] = dataclasses.field(default=None)
 55
 56
 57class ItemTemplateQuality(Base):
 58    __tablename__ = "item_template_quality"
 59
 60    id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
 61    color: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 62    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 63    name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 64
 65
 66@dataclasses.dataclass
 67class ItemTemplateQualityData:
 68    id: int = dataclasses.field()
 69    color: T.Optional[str] = dataclasses.field(default=None)
 70    name: T.Optional[str] = dataclasses.field(default=None)
 71    name_cn: T.Optional[str] = dataclasses.field(default=None)
 72
 73
 74class ItemTemplateBonding(Base):
 75    __tablename__ = "item_template_bonding"
 76
 77    id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
 78    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 79    name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 80    abbr: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 81
 82
 83@dataclasses.dataclass
 84class ItemTemplateBondingData:
 85    id: int = dataclasses.field()
 86    name: T.Optional[str] = dataclasses.field(default=None)
 87    name_cn: T.Optional[str] = dataclasses.field(default=None)
 88    abbr: T.Optional[str] = dataclasses.field(default=None)
 89
 90
 91class ItemTemplateAllowableClass(Base):
 92    __tablename__ = "item_template_allowable_class"
 93
 94    id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
 95    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 96    name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
 97
 98
 99@dataclasses.dataclass
100class ItemTemplateAllowableClassData:
101    id: int = dataclasses.field()
102    name: T.Optional[str] = dataclasses.field(default=None)
103    name_cn: T.Optional[str] = dataclasses.field(default=None)
104
105
106class ItemTemplateStatType(Base):
107    __tablename__ = "item_template_stat_type"
108
109    id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
110    type_name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
111    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
112    name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
113    name_abbr: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
114    name_abbr_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
115    description: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
116
117
118@dataclasses.dataclass
119class ItemTemplateStatTypeData:
120    id: int = dataclasses.field()
121    type_name: T.Optional[str] = dataclasses.field(default=None)
122    name: T.Optional[str] = dataclasses.field(default=None)
123    name_cn: T.Optional[str] = dataclasses.field(default=None)
124    name_abbr: T.Optional[str] = dataclasses.field(default=None)
125    name_abbr_cn: T.Optional[str] = dataclasses.field(default=None)
126    description: T.Optional[str] = dataclasses.field(default=None)
127
128
129class ItemTemplateDamageType(Base):
130    __tablename__ = "item_template_damage_type"
131
132    id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
133    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
134    name_cn: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
135
136
137@dataclasses.dataclass
138class ItemTemplateDamageTypeData:
139    id: int = dataclasses.field()
140    name: T.Optional[str] = dataclasses.field(default=None)
141    name_cn: T.Optional[str] = dataclasses.field(default=None)
142
143
144class Factions(Base):
145    __tablename__ = "factions"
146
147    faction_id: orm.Mapped[int] = sa.Column(sa.Integer, primary_key=True)
148    sort_key: orm.Mapped[T.Optional[int]] = sa.Column(sa.Integer, nullable=True)
149    expansion: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
150    type: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
151    name: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
152    description: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
153    reward: orm.Mapped[T.Optional[str]] = sa.Column(sa.String, nullable=True)
154
155
156@dataclasses.dataclass
157class FactionsData:
158    faction_id: int = dataclasses.field()
159    sort_key: T.Optional[int] = dataclasses.field(default=None)
160    expansion: T.Optional[str] = dataclasses.field(default=None)
161    type: T.Optional[str] = dataclasses.field(default=None)
162    name: T.Optional[str] = dataclasses.field(default=None)
163    description: T.Optional[str] = dataclasses.field(default=None)
164    reward: T.Optional[str] = dataclasses.field(default=None)
165
166
167
168dataset_mapping = {
169    "item_template_class": {
170        "orm_class": ItemTemplateClass,
171        "data_class": ItemTemplateClassData,
172        "id_col": "id",
173    },
174    "item_template_subclass": {
175        "orm_class": ItemTemplateSubclass,
176        "data_class": ItemTemplateSubclassData,
177        "id_col": "id",
178    },
179    "item_template_quality": {
180        "orm_class": ItemTemplateQuality,
181        "data_class": ItemTemplateQualityData,
182        "id_col": "id",
183    },
184    "item_template_bonding": {
185        "orm_class": ItemTemplateBonding,
186        "data_class": ItemTemplateBondingData,
187        "id_col": "id",
188    },
189    "item_template_allowable_class": {
190        "orm_class": ItemTemplateAllowableClass,
191        "data_class": ItemTemplateAllowableClassData,
192        "id_col": "id",
193    },
194    "item_template_stat_type": {
195        "orm_class": ItemTemplateStatType,
196        "data_class": ItemTemplateStatTypeData,
197        "id_col": "id",
198    },
199    "item_template_damage_type": {
200        "orm_class": ItemTemplateDamageType,
201        "data_class": ItemTemplateDamageTypeData,
202        "id_col": "id",
203    },
204    "factions": {
205        "orm_class": Factions,
206        "data_class": FactionsData,
207        "id_col": "faction_id",
208    },
209}
210
211
212@dataclasses.dataclass
213class ItemTemplateClassDataset(
214    BaseDataset[ItemTemplateClass, ItemTemplateClassData]
215):
216    pass
217
218@dataclasses.dataclass
219class ItemTemplateSubclassDataset(
220    BaseDataset[ItemTemplateSubclass, ItemTemplateSubclassData]
221):
222    pass
223
224@dataclasses.dataclass
225class ItemTemplateQualityDataset(
226    BaseDataset[ItemTemplateQuality, ItemTemplateQualityData]
227):
228    pass
229
230@dataclasses.dataclass
231class ItemTemplateBondingDataset(
232    BaseDataset[ItemTemplateBonding, ItemTemplateBondingData]
233):
234    pass
235
236@dataclasses.dataclass
237class ItemTemplateAllowableClassDataset(
238    BaseDataset[ItemTemplateAllowableClass, ItemTemplateAllowableClassData]
239):
240    pass
241
242@dataclasses.dataclass
243class ItemTemplateStatTypeDataset(
244    BaseDataset[ItemTemplateStatType, ItemTemplateStatTypeData]
245):
246    pass
247
248@dataclasses.dataclass
249class ItemTemplateDamageTypeDataset(
250    BaseDataset[ItemTemplateDamageType, ItemTemplateDamageTypeData]
251):
252    pass
253
254@dataclasses.dataclass
255class FactionsDataset(
256    BaseDataset[Factions, FactionsData]
257):
258    pass
259
260
261
262@dataclasses.dataclass
263class Lookup:
264    """
265    The main API to access the acore dataframe data. Useful methods are:
266
267    - :meth:`Lookup.${dataset_name}.get <acore_df.dataset.BaseDataset.get>`
268    - :meth:`Lookup.${dataset_name}.get_by_kvs <acore_df.dataset.BaseDataset.get_by_kvs>`
269    - :meth:`Lookup.${dataset_name}.df <acore_df.dataset.BaseDataset.df>`
270    - :meth:`Lookup.${dataset_name}.row_map <acore_df.dataset.BaseDataset.row_map>`
271    - :meth:`Lookup.${dataset_name}.name <acore_df.dataset.BaseDataset>`
272    - :meth:`Lookup.${dataset_name}.id_col <acore_df.dataset.BaseDataset>`
273    - :meth:`Lookup.${dataset_name}.orm_model <acore_df.dataset.BaseDataset>`
274    - :meth:`Lookup.${dataset_name}.orm_table <acore_df.dataset.BaseDataset>`
275    - :meth:`Lookup.${dataset_name}.data_class <acore_df.dataset.BaseDataset>`
276    - :meth:`Lookup.${dataset_name}.engine <acore_df.dataset.BaseDataset>`
277    """
278    engine: sa.Engine = dataclasses.field()
279    item_template_class: ItemTemplateClassDataset = dataclasses.field()
280    item_template_subclass: ItemTemplateSubclassDataset = dataclasses.field()
281    item_template_quality: ItemTemplateQualityDataset = dataclasses.field()
282    item_template_bonding: ItemTemplateBondingDataset = dataclasses.field()
283    item_template_allowable_class: ItemTemplateAllowableClassDataset = dataclasses.field()
284    item_template_stat_type: ItemTemplateStatTypeDataset = dataclasses.field()
285    item_template_damage_type: ItemTemplateDamageTypeDataset = dataclasses.field()
286    factions: FactionsDataset = dataclasses.field()
287
288    @classmethod
289    def new(cls, path_sqlite: Path = path_sqlite):
290        if path_sqlite.exists() is False:
291            download_sqlite(path_sqlite=path_sqlite)
292
293        engine = sa.create_engine(f"sqlite:///{path_sqlite}")
294        return cls(
295            engine=engine,
296            item_template_class=ItemTemplateClassDataset(
297                name="item_template_class",
298                id_col="id",
299                orm_model=ItemTemplateClass,
300                orm_table=ItemTemplateClass.__table__,
301                data_class=ItemTemplateClassData,
302                engine=engine,
303            ),
304            item_template_subclass=ItemTemplateSubclassDataset(
305                name="item_template_subclass",
306                id_col="id",
307                orm_model=ItemTemplateSubclass,
308                orm_table=ItemTemplateSubclass.__table__,
309                data_class=ItemTemplateSubclassData,
310                engine=engine,
311            ),
312            item_template_quality=ItemTemplateQualityDataset(
313                name="item_template_quality",
314                id_col="id",
315                orm_model=ItemTemplateQuality,
316                orm_table=ItemTemplateQuality.__table__,
317                data_class=ItemTemplateQualityData,
318                engine=engine,
319            ),
320            item_template_bonding=ItemTemplateBondingDataset(
321                name="item_template_bonding",
322                id_col="id",
323                orm_model=ItemTemplateBonding,
324                orm_table=ItemTemplateBonding.__table__,
325                data_class=ItemTemplateBondingData,
326                engine=engine,
327            ),
328            item_template_allowable_class=ItemTemplateAllowableClassDataset(
329                name="item_template_allowable_class",
330                id_col="id",
331                orm_model=ItemTemplateAllowableClass,
332                orm_table=ItemTemplateAllowableClass.__table__,
333                data_class=ItemTemplateAllowableClassData,
334                engine=engine,
335            ),
336            item_template_stat_type=ItemTemplateStatTypeDataset(
337                name="item_template_stat_type",
338                id_col="id",
339                orm_model=ItemTemplateStatType,
340                orm_table=ItemTemplateStatType.__table__,
341                data_class=ItemTemplateStatTypeData,
342                engine=engine,
343            ),
344            item_template_damage_type=ItemTemplateDamageTypeDataset(
345                name="item_template_damage_type",
346                id_col="id",
347                orm_model=ItemTemplateDamageType,
348                orm_table=ItemTemplateDamageType.__table__,
349                data_class=ItemTemplateDamageTypeData,
350                engine=engine,
351            ),
352            factions=FactionsDataset(
353                name="factions",
354                id_col="faction_id",
355                orm_model=Factions,
356                orm_table=Factions.__table__,
357                data_class=FactionsData,
358                engine=engine,
359            ),
360        )

生成了 Python 模块后就可以运行 tests/test_lookup.py 单元测试确保我们新生成的代码能正常工作. 如果我们对 dataframe 造成了不可逆的修改, 则一定要在 release-history.rst 中记录下来.

tests/test_lookup.py
 1# -*- coding: utf-8 -*-
 2
 3import random
 4import dataclasses
 5import sqlalchemy as sa
 6from acore_df.model import dataset_mapping, Lookup
 7
 8
 9def test():
10    lookup = Lookup.new()
11
12    for dataset_name, dct in dataset_mapping.items():
13        id_col = dct["id_col"]
14        orm_class = dct["orm_class"]
15        data_class = dct["data_class"]
16        # print(dataset_name)
17        dataset = getattr(lookup, dataset_name)
18
19        random_id = random.choice(list(dataset.row_map.keys()))
20        random_row = random.choice(list(dataset.row_map.values()))
21        row_keys = list(dataclasses.asdict(random_row).keys())
22        random_key = random.choice(row_keys)
23        random_value = getattr(random_row, random_key)
24
25        assert getattr(dataset.get(random_id), id_col) == random_id
26
27        results = dataset.get_by_kvs(kvs={random_key: random_value})
28        value_set = set([getattr(row, random_key) for row in results])
29        assert random_value in value_set
30
31        with lookup.engine.connect() as conn:
32            stmt = sa.select(dataset.orm_table).limit(10)
33            _ = list(conn.execute(stmt))
34
35
36if __name__ == "__main__":
37    from acore_df.tests import run_cov_test
38
39    run_cov_test(__file__, "acore_df.model", preview=False)

Release#

当我更新完数据, 重新生成代码, 并完成单元测试后, 就可以发布了. 每次发布新版本时, 需要将位于 ${HOME}/acore_df.sqlite (默认位置) 上传到 GitHub release. 这样在用户的机器上就能自动下载对应的数据了.

由于这是一个数据优先的项目, 任何对数据造成无法向后兼容的修改都需要发布新的大版本.