pythia-table

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Tables, Columns, Comments

表、列与注释

Announce at start: "Using pythia-table — reading the table and its neighbours before designing DDL."

Phase: Learn → Ask → Do — the table as it is, the decisions that are the developer's, then one ordered script

A table outlives every program that reads it. Its columns are a contract with every report, interface and procedure in the schema, and structural DDL has almost no undo:

DROP COLUMN

is permanent, a renamed column breaks every caller at once. Five minutes of design is the cheapest step there is.

开始时宣告：「正在使用 pythia-table——先读取目标表及其相关对象，再设计 DDL。」

阶段： 学习 → 询问 → 执行 —— 先了解表当前的状态，再明确应由开发者决定的事项，最后生成一个有序脚本

表的生命周期长于读取它的每一个程序。它的列是 schema 中每份报表、接口和存储过程共同遵守的契约，而结构性 DDL 几乎没有撤销手段：

DROP COLUMN

是永久性的，重命名列则会同时破坏所有调用方。投入五分钟做设计，是成本最低的一步。

The Iron Law

铁律

NO TABLE OR COLUMN WITHOUT ITS COMMENT, ITS CONSTRAINTS AND ITS IMPACT KNOWN

NO TABLE OR COLUMN WITHOUT ITS COMMENT, ITS CONSTRAINTS AND ITS IMPACT KNOWN

The Workflow

工作流

Learn the table and its neighbours.
- ```
pythia cols T
```
  ·
```
pythia comments T
```
  ·
```
pythia stats T
```
  — what exists, what is documented, which foreign keys lack an index.
- ```
pythia impact T
```
  before any ALTER — every program that recompiles.
- ```
pythia similar T_<NAME>
```
  and
```
pythia ddl TABLE <NEIGHBOUR>
```
  — how this house already builds tables: PK style, audit columns, tablespace, naming. Read
```
.pythia/conventions.md
```
  and
```
pythia conventions
```
  .
Ask what is the developer's (through
```
pythia-spec
```
): what is kept or deleted, nullability of existing rows and the backfill value, retention, who writes the table (an API package, a trigger, an interface), identity column vs sequence when the house has no rule. Never invent a business default for a NOT NULL column.
Write one ordered script —
```
reference/ddl-standards.md
```
has the template and the datatype rules:
- name every constraint (
```
PK_
```
  ,
```
FK_
```
  ,
```
UK_
```
  ,
```
CK_
```
  unless the house says otherwise); a primary key on every table; an index on every FK column;
- text as
```
VARCHAR2(n CHAR)
```
  — a Vietnamese character can take 3 bytes; money as
```
NUMBER(p,s)
```
  , never FLOAT;
```
DATE
```
  or
```
TIMESTAMP
```
  by need;
- ```
COMMENT ON TABLE
```
  and
```
COMMENT ON COLUMN
```
  for every new table and column: meaning, unit, allowed values, source. Vietnamese is fine here —
```
COMMENT ... IS
```
  takes a literal only (no
```
unistr
```
  ), and pythia sends UTF-8 exactly;
```
pythia doctor --db
```
  warns if the character set cannot hold it.
Lint it:
```
pythia lint script.sql comments.sql
```
(one run, so the comments file counts) — missing comments, unnamed constraints, unindexed FKs,
```
ADD ... NOT NULL
```
without a default, byte-semantics text, missing house columns. Errors are fixed, not argued.
Land it with
```
pythia-deploy
```
(
```
pythia deploy script.sql
```
): every statement previewed, one approval, run in order, stopped at the first failure. A single statement can go through
```
pythia apply
```
as usual.
```
structural
```
is
```
deny
```
by default — relay the refusal; the developer decides (
```
pythia policy set structural confirm
```
, or an autopilot lease naming
```
structural
```
).

Verify (

pythia-verify

pythia cols T

pythia comments T

(0 missing),

pythia invalid

against the baseline.

了解目标表及其相关对象。
- ```
pythia cols T
```
  ·
```
pythia comments T
```
  ·
```
pythia stats T
```
  —— 了解现有对象、已有文档，以及哪些外键缺少索引。
- 任何
```
ALTER
```
  之前执行
```
pythia impact T
```
  ——确认所有会重新编译的程序。
- ```
pythia similar T_<NAME>
```
  和
```
pythia ddl TABLE <NEIGHBOUR>
```
  —— 了解内部规范中现有的建表方式：PK 样式、审计列、表空间和命名规则。读取
```
.pythia/conventions.md
```
  并运行
```
pythia conventions
```
  。
通过
pythia-spec
询问应由开发者决定的事项：保留还是删除什么、现有行是否允许为
```
NULL
```
以及回填值、保留期限、由谁写入该表（API package、trigger 或 interface），以及当内部规范没有相关规则时使用 identity column 还是 sequence。绝不能擅自为
```
NOT NULL
```
列设定业务默认值。
编写一个有序脚本——
```
reference/ddl-standards.md
```
提供了模板和数据类型规则：
- 为每个约束命名（
```
PK_
```
  、
```
FK_
```
  、
```
UK_
```
  、
```
CK_
```
  ，除非内部规范另有规定）；每张表都要有主键；每个 FK 列都要有索引；
- 文本使用
```
VARCHAR2(n CHAR)
```
  ——一个越南语字符可能占用 3 字节；金额使用
```
NUMBER(p,s)
```
  ，绝不使用
```
FLOAT
```
  ；根据需要使用
```
DATE
```
  或
```
TIMESTAMP
```
  ；
- 为每一张新表和每一个新列编写
```
COMMENT ON TABLE
```
  和
```
COMMENT ON COLUMN
```
  ：含义、单位、允许值和来源。这里可以使用越南语——
```
COMMENT ... IS
```
  只接受字面量（不能使用
```
unistr
```
  ），并且 pythia 会原样发送 UTF-8；
```
pythia doctor --db
```
  会在字符集无法容纳这些内容时发出警告。
执行 lint：
```
pythia lint script.sql comments.sql
```
（只运行一次，这样 comments 文件也会被计入）——检查缺失的注释、未命名的约束、未建立索引的 FK、没有默认值的
```
ADD ... NOT NULL
```
、使用字节语义的文本，以及缺少内部规范要求的列。发现错误应修复，而不是争辩。
使用
```
pythia-deploy
```
落地执行（
```
pythia deploy script.sql
```
）：预览每一条语句，只需一次批准，按顺序执行，并在首次失败时停止。单条语句也可以照常通过
```
pythia apply
```
执行。
```
structural
```
默认设为
```
deny
```
——应如实转达拒绝结果；由开发者决定（
```
pythia policy set structural confirm
```
，或使用指定了
```
structural
```
的 autopilot lease）。
验证（
```
pythia-verify
```
）：运行
```
pythia cols T
```
、
```
pythia comments T
```
（缺失数为 0），并对照基线运行
```
pythia invalid
```
。

Changing a live table — expand, then contract

修改在线表——先扩展，再收缩

Change	Safe shape
New mandatory column	`ADD (c ... DEFAULT <agreed> NOT NULL)` — one step, metadata-only on 12c+. No agreed default → add nullable, backfill ( `data_dml` ), then `MODIFY c NOT NULL` .
Rename	Never in place on a shared schema: add the new column, copy, move the callers, drop the old one later — a separate, destructive approval.
Type change	Widening ( `VARCHAR2(50)` → `(100)` ) is in place; anything narrowing or converting: new column, copy, swap.
New FK on existing data	`ENABLE NOVALIDATE` first, clean the rows, then `VALIDATE` — and the index before the constraint.
Drop	Destructive: its own preview, its own approval, never inside an autopilot lease unless the card said so. Check the Recycle Bin is on and never add `PURGE` without being asked.

Big tables (millions of rows):

ALTER TABLE ... ADD

with a default is metadata-only, but

MODIFY

, index builds and backfills are not — ask about a window, and

ONLINE

index builds need Enterprise Edition. Partitioning and compression are separately licensed options: name that before proposing them.

变更	安全做法
新增必填列	`ADD (c ... DEFAULT <agreed> NOT NULL)` ——一步完成，在 12c+ 上仅修改元数据。如果尚未商定默认值：先添加可空列，执行回填（ `data_dml` ），再执行 `MODIFY c NOT NULL` 。
重命名	在共享 schema 中绝不原地执行：添加新列、复制数据、迁移调用方，之后再删除旧列——作为一项独立的破坏性操作审批。
类型变更	扩宽（ `VARCHAR2(50)` → `(100)` ）可原地执行；任何缩窄或转换操作：新建列、复制数据、再交换。
在现有数据上添加 FK	先执行 `ENABLE NOVALIDATE` ，清理数据行，然后执行 `VALIDATE` ——并在添加约束之前创建索引。
删除	破坏性操作：单独预览、单独审批，除非任务卡中明确说明，否则绝不能放在 autopilot lease 内执行。确认 Recycle Bin 已开启，未经要求绝不能添加 `PURGE` 。

对于数百万行的大表：带默认值的

ALTER TABLE ... ADD

仅修改元数据，但

MODIFY

、索引构建和回填并非如此——应询问可操作的维护窗口，而

ONLINE

索引构建需要 Enterprise Edition。分区和压缩是单独授权的选项：提出这些方案前应先说明这一点。

Is rollback real?

回滚真的有效吗？

Statement	Undo
`COMMENT ON`	Yes — the previous comment is snapshotted; `pythia journal restore <entry>` .
`CREATE TABLE / INDEX / SEQUENCE`	A `DROP` — structural, written by hand, approved like any write.
`ALTER TABLE ADD`	`ALTER TABLE ... DROP COLUMN` — the data written since is lost.
`DROP` , `TRUNCATE` , `SET UNUSED`	No. Flashback is a DBA's job, if retention allows it.

语句	撤销方式
`COMMENT ON`	是——旧注释已保存快照； `pythia journal restore <entry>` 。
`CREATE TABLE / INDEX / SEQUENCE`	执行 `DROP` ——属于结构性操作，需手工编写，并像任何写操作一样审批。
`ALTER TABLE ADD`	执行 `ALTER TABLE ... DROP COLUMN` ——此后写入的数据会丢失。
`DROP, TRUNCATE, SET UNUSED`	不能。如果保留期允许，Flashback 由 DBA 负责处理。

Red Flags — STOP if you catch yourself thinking

危险信号——发现自己在有以下想法时立即停止

Thought	Reality
"Comments later, once it works"	Later never comes; the dictionary is the only documentation that survives. Same script.
"The FK index is an optimisation"	Without it, deleting a parent locks the whole child table. It is correctness under load.
"VARCHAR2(100) is plenty"	100 bytes is ~33 Vietnamese characters in AL32UTF8. Say `CHAR` .
"I'll default the NOT NULL column to 0"	That is a business decision made silently. Ask.
"Rename is one statement"	And one broken caller per reference. Expand, then contract.
"The table is new, impact is zero"	Say so with `pythia impact` as evidence — then it is a fact, not a guess.

想法	现实情况
「能运行之后再补注释」	「以后」永远不会到来；数据字典是唯一能够长期留存的文档。必须在同一个脚本中完成。
「FK 索引只是一项优化」	没有它，删除父表记录会锁住整个子表。这在负载下属于正确性问题，而不只是性能优化。
「 `VARCHAR2(100)` 足够了」	在 AL32UTF8 中，100 字节约等于 33 个越南语字符。应使用 `CHAR` 语义。
「我会把 `NOT NULL` 列的默认值设为 0」	这是未经说明的业务决策。必须询问。
「重命名只需一条语句」	每处引用都会因此产生一个损坏的调用方。先扩展，再收缩。
「这是新表，影响范围为零」	运行 `pythia impact` 并以结果为证——这样它才是事实，而不是猜测。

When NOT to use this skill

哪些情况下不要使用此 skill

Changing code that reads the table →
```
pythia-write
```
/
```
pythia-package
```
.
A slow query on an existing table →
```
pythia-tune
```
(an index is still designed here once tuning asks for it).
Data fixes (
```
UPDATE
```
/
```
DELETE
```
) →
```
pythia-apply
```
; they are
```
data_dml
```
.

修改读取该表的代码 →
```
pythia-write
```
/
```
pythia-package
```
。
现有表上的慢查询 →
```
pythia-tune
```
（但当调优提出要求时，索引仍应在此处设计）。
数据修复（
```
UPDATE
```
/
```
DELETE
```
）→
```
pythia-apply
```
；它们属于
```
data_dml
```
。