Coverage for datakiln/metadata/migrations/versions/v0002_objects.py: 0%
48 statements
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
1"""Tables and scripts as objects of one kind
3The tables are recorded by their full name in a table of objects, the same as
4the scripts, with the rest of what is specific to a kind in its own table.
6Revision ID: 0002
7Revises: 0001
8"""
9# pylint: disable=no-member, invalid-name, duplicate-code
10from alembic import op
11import sqlalchemy as sa
14revision = "0002"
15down_revision = "0001"
17SCHEMA = "__metadata"
20def upgrade():
21 """Create the tables of objects, and move the rows of the old ones."""
22 op.rename_table("artifact_", "artifact_old_", schema=SCHEMA)
23 op.rename_table("table_", "table_old_", schema=SCHEMA)
25 op.create_table(
26 "object_",
27 sa.Column("name_", sa.String(), nullable=False),
28 sa.Column("kind_", sa.String(), nullable=False),
29 sa.Column("checksum_", sa.String(), nullable=False),
30 sa.PrimaryKeyConstraint("name_"),
31 schema=SCHEMA,
32 )
33 op.create_table(
34 "table_",
35 sa.Column("name_", sa.String(), nullable=False),
36 sa.PrimaryKeyConstraint("name_"),
37 sa.ForeignKeyConstraint(
38 ["name_"], [f"{SCHEMA}.object_.name_"], ondelete="CASCADE"),
39 schema=SCHEMA,
40 )
41 op.create_table(
42 "script_",
43 sa.Column("name_", sa.String(), nullable=False),
44 sa.Column("drop_", sa.String(), nullable=False),
45 sa.PrimaryKeyConstraint("name_"),
46 sa.ForeignKeyConstraint(
47 ["name_"], [f"{SCHEMA}.object_.name_"], ondelete="CASCADE"),
48 schema=SCHEMA,
49 )
50 op.create_table(
51 "artifact_",
52 sa.Column("path_", sa.String(), nullable=False),
53 sa.Column("name_", sa.String(), nullable=False),
54 sa.Column("checksum_", sa.String(), nullable=False),
55 sa.PrimaryKeyConstraint("path_"),
56 sa.ForeignKeyConstraint(
57 ["name_"], [f"{SCHEMA}.table_.name_"], ondelete="CASCADE"),
58 schema=SCHEMA,
59 )
61 # Move the rows, with the names in lowercase as the database folds them
62 table = sa.table(
63 "table_old_", sa.column("schema_"), sa.column("table_"),
64 sa.column("checksum_"), schema=SCHEMA)
65 artifact = sa.table(
66 "artifact_old_", sa.column("path_"), sa.column("schema_"),
67 sa.column("table_"), sa.column("checksum_"), schema=SCHEMA)
68 objects = sa.table(
69 "object_", sa.column("name_"), sa.column("kind_"),
70 sa.column("checksum_"), schema=SCHEMA)
71 tables = sa.table("table_", sa.column("name_"), schema=SCHEMA)
72 artifacts = sa.table(
73 "artifact_", sa.column("path_"), sa.column("name_"),
74 sa.column("checksum_"), schema=SCHEMA)
76 name = sa.func.lower(table.c.schema_ + "." + table.c.table_)
77 op.execute(sa.insert(objects).from_select(
78 ["name_", "kind_", "checksum_"],
79 sa.select(name, sa.literal("table"), table.c.checksum_)))
80 op.execute(sa.insert(tables).from_select(["name_"], sa.select(name)))
82 name = sa.func.lower(artifact.c.schema_ + "." + artifact.c.table_)
83 op.execute(sa.insert(artifacts).from_select(
84 ["path_", "name_", "checksum_"],
85 sa.select(artifact.c.path_, name, artifact.c.checksum_)))
87 op.drop_table("artifact_old_", schema=SCHEMA)
88 op.drop_table("table_old_", schema=SCHEMA)
91def downgrade():
92 """Recreate the tables of the first version from the objects.
94 The names come back in lowercase, as the database folds them, and the
95 scripts are dropped, since the first version did not record them.
96 """
97 objects = sa.table(
98 "object_", sa.column("name_"), sa.column("checksum_"), schema=SCHEMA)
99 tables = sa.table("table_", sa.column("name_"), schema=SCHEMA)
100 artifacts = sa.table(
101 "artifact_", sa.column("path_"), sa.column("name_"),
102 sa.column("checksum_"), schema=SCHEMA)
104 # Keep the rows, since the tables have the same names in both versions
105 bind = op.get_bind()
106 saved_tables = bind.execute(
107 sa.select(objects.c.name_, objects.c.checksum_).select_from(
108 objects.join(tables, objects.c.name_ == tables.c.name_))
109 ).all()
110 saved_artifacts = bind.execute(sa.select(
111 artifacts.c.path_, artifacts.c.name_, artifacts.c.checksum_)).all()
113 op.drop_table("artifact_", schema=SCHEMA)
114 op.drop_table("table_", schema=SCHEMA)
115 op.drop_table("script_", schema=SCHEMA)
116 op.drop_table("object_", schema=SCHEMA)
118 op.create_table(
119 "table_",
120 sa.Column("schema_", sa.String(), nullable=False),
121 sa.Column("table_", sa.String(), nullable=False),
122 sa.Column("checksum_", sa.String(), nullable=False),
123 sa.PrimaryKeyConstraint("schema_", "table_"),
124 schema=SCHEMA,
125 )
126 op.create_table(
127 "artifact_",
128 sa.Column("path_", sa.String(), nullable=False),
129 sa.Column("schema_", sa.String(), nullable=False),
130 sa.Column("table_", sa.String(), nullable=False),
131 sa.Column("checksum_", sa.String(), nullable=False),
132 sa.PrimaryKeyConstraint("path_"),
133 sa.ForeignKeyConstraint(
134 ["table_", "schema_"],
135 [f"{SCHEMA}.table_.table_", f"{SCHEMA}.table_.schema_"],
136 ),
137 schema=SCHEMA,
138 )
140 # A full name is the schema and the table
141 old_tables = []
142 for name, checksum in saved_tables:
143 schema, _, table = name.partition(".")
144 old_tables.append(
145 {"schema_": schema, "table_": table, "checksum_": checksum})
147 old_artifacts = []
148 for path, name, checksum in saved_artifacts:
149 schema, _, table = name.partition(".")
150 old_artifacts.append({
151 "path_": path, "schema_": schema, "table_": table,
152 "checksum_": checksum
153 })
155 for name, rows in (("table_", old_tables), ("artifact_", old_artifacts)):
156 if rows:
157 op.bulk_insert(
158 sa.table(name, *map(sa.column, rows[0]), schema=SCHEMA), rows)