3030import sqlalchemy as sa
3131from alembic import op
3232from sqlalchemy_utils import UUIDType
33- from uuid6 import uuid7
3433
3534from airflow .migrations .db_types import TIMESTAMP , StringID
3635from airflow .models .base import naming_convention
37- from airflow .models .dagcode import DagCode
3836from airflow .utils import timezone
3937
4038# revision identifiers, used by Alembic.
4543airflow_version = "3.0.0"
4644
4745
48- def _get_rows (sql , conn ):
49- stmt = sa .text (sql )
50- rows = conn .execute (stmt )
51- if rows :
52- rows = rows .fetchall ()
53- else :
54- rows = []
55- return rows
56-
57-
58- def _airflow_2_fileloc_hash (fileloc ):
59- import hashlib
60- import struct
61-
62- # Only 7 bytes because MySQL BigInteger can hold only 8 bytes (signed).
63- return struct .unpack (">Q" , hashlib .sha1 (fileloc .encode ("utf-8" )).digest ()[- 8 :])[0 ] >> 8
64-
65-
6646def upgrade ():
6747 """Apply add dag versioning."""
68- conn = op .get_bind ()
69-
70- op .execute ("DELETE FROM dag_code WHERE fileloc_hash NOT IN (SELECT fileloc_hash FROM serialized_dag)" )
48+ op .execute ("delete from dag_code;" )
49+ op .execute ("delete from serialized_dag;" )
7150
7251 op .create_table (
7352 "dag_version" ,
@@ -84,165 +63,15 @@ def upgrade():
8463 sa .PrimaryKeyConstraint ("id" , name = op .f ("dag_version_pkey" )),
8564 sa .UniqueConstraint ("dag_id" , "version_number" , name = "dag_id_v_name_v_number_unique_constraint" ),
8665 )
87- with op .batch_alter_table (
88- "dag_code" ,
89- ) as batch_op :
90- batch_op .drop_constraint ("dag_code_pkey" , type_ = "primary" )
91- batch_op .add_column (sa .Column ("id" , UUIDType (binary = False )))
92- batch_op .add_column (sa .Column ("dag_version_id" , UUIDType (binary = False )))
93- batch_op .add_column (sa .Column ("source_code_hash" , sa .String (length = 32 )))
94- batch_op .add_column (sa .Column ("dag_id" , StringID ()))
95- batch_op .add_column (sa .Column ("created_at" , TIMESTAMP (), default = timezone .utcnow ))
96-
97- with op .batch_alter_table (
98- "serialized_dag" ,
99- ) as batch_op :
100- batch_op .add_column (sa .Column ("id" , UUIDType (binary = False )))
101- batch_op .drop_index ("idx_fileloc_hash" )
102- batch_op .add_column (sa .Column ("dag_version_id" , UUIDType (binary = False )))
103- batch_op .add_column (sa .Column ("created_at" , TIMESTAMP (), default = timezone .utcnow ))
104-
105- # Data migration
106- rows = _get_rows ("SELECT dag_id FROM serialized_dag" , conn )
107-
108- stmt = sa .text ("""
109- UPDATE serialized_dag
110- SET id = :_id
111- WHERE dag_id = :dag_id AND id IS NULL
112- """ )
113-
114- for row in rows :
115- id = uuid7 ()
116- if conn .dialect .name != "postgresql" :
117- id = id .hex
118- else :
119- id = str (id )
120-
121- conn .execute (stmt .bindparams (_id = id , dag_id = row .dag_id ))
122- id2 = uuid7 ()
123- if conn .dialect .name != "postgresql" :
124- id2 = id2 .hex
125- else :
126- id2 = str (id2 )
127- # Update dagversion table
128- conn .execute (
129- sa .text ("""
130- INSERT INTO dag_version (id, version_number, dag_id, created_at, last_updated)
131- VALUES (:id, 1, :dag_id, :created_at, :last_updated)
132- """ ).bindparams (
133- id = id2 , dag_id = row .dag_id , created_at = timezone .utcnow (), last_updated = timezone .utcnow ()
134- )
135- )
136-
137- # Update serialized_dag table with dag_version_id where dag_id matches
138- if conn .dialect .name == "mysql" :
139- conn .execute (
140- sa .text ("""
141- UPDATE serialized_dag sd
142- JOIN dag_version dv ON sd.dag_id = dv.dag_id
143- SET sd.dag_version_id = dv.id,
144- sd.created_at = dv.created_at
145- """ )
146- )
147- else :
148- conn .execute (
149- sa .text ("""
150- UPDATE serialized_dag
151- SET dag_version_id = dag_version.id,
152- created_at = dag_version.created_at
153- FROM dag_version
154- WHERE serialized_dag.dag_id = dag_version.dag_id
155- """ )
156- )
157- # Update dag_code table where fileloc_hash of serialized_dag matches
158- if conn .dialect .name == "mysql" :
159- conn .execute (
160- sa .text ("""
161- UPDATE dag_code dc
162- JOIN serialized_dag sd ON dc.fileloc_hash = sd.fileloc_hash
163- SET dc.dag_version_id = sd.dag_version_id,
164- dc.created_at = sd.created_at,
165- dc.dag_id = sd.dag_id
166- """ )
167- )
168- else :
169- conn .execute (
170- sa .text ("""
171- UPDATE dag_code
172- SET dag_version_id = dag_version.id,
173- created_at = serialized_dag.created_at,
174- dag_id = serialized_dag.dag_id
175- FROM serialized_dag, dag_version
176- WHERE dag_code.fileloc_hash = serialized_dag.fileloc_hash
177- AND serialized_dag.dag_version_id = dag_version.id
178- """ )
179- )
180-
181- # select all rows in serialized_dag where the dag_id is not in dag_code
182-
183- stmt = """
184- SELECT dag_id, fileloc, fileloc_hash, dag_version_id
185- FROM serialized_dag
186- WHERE dag_id NOT IN (SELECT dag_id FROM dag_code)
187- AND dag_id in (SELECT dag_id FROM dag)
188- """
189- rows = _get_rows (stmt , conn )
190- # Insert the missing rows from serialized_dag to dag_code
191- stmt = sa .text ("""
192- INSERT INTO dag_code (dag_version_id, dag_id, fileloc, fileloc_hash, source_code, last_updated, created_at)
193- VALUES (:dag_version_id, :dag_id, :fileloc, :fileloc_hash, :source_code, :last_updated, :created_at)
194- """ )
195- for row in rows :
196- try :
197- source_code = DagCode .get_code_from_file (row .fileloc )
198- except FileNotFoundError :
199- source_code = "source_code"
200- conn .execute (
201- stmt .bindparams (
202- dag_version_id = row .dag_version_id ,
203- dag_id = row .dag_id ,
204- fileloc = row .fileloc ,
205- fileloc_hash = row .fileloc_hash ,
206- source_code = source_code ,
207- last_updated = timezone .utcnow (),
208- created_at = timezone .utcnow (),
209- )
210- )
211-
212- stmt = "SELECT dag_id, fileloc FROM dag_code"
213- rows = _get_rows (stmt , conn )
214- stmt = sa .text ("""
215- UPDATE dag_code
216- SET id = :_id,
217- dag_id = :dag_id,
218- source_code = :source_code,
219- source_code_hash = :source_code_hash
220- WHERE dag_id = :dag_id AND id IS NULL
221- """ )
222- for row in rows :
223- id = uuid7 ()
224- if conn .dialect .name != "postgresql" :
225- id = id .hex
226- else :
227- id = str (id )
228- try :
229- source_code = DagCode .get_code_from_file (row .fileloc )
230- except FileNotFoundError :
231- source_code = "source_code"
232- conn .execute (
233- stmt .bindparams (
234- _id = id ,
235- source_code_hash = DagCode .dag_source_hash (source_code ),
236- source_code = source_code ,
237- dag_id = row .dag_id ,
238- )
239- )
240-
24166 with op .batch_alter_table ("dag_code" ) as batch_op :
242- batch_op .alter_column ("dag_id" , existing_type = StringID (), nullable = False )
243- batch_op .alter_column ("id" , existing_type = UUIDType (binary = False ), nullable = False )
67+ batch_op .drop_constraint ("dag_code_pkey" , type_ = "primary" )
68+ batch_op .drop_column ("fileloc_hash" )
69+ batch_op .add_column (sa .Column ("id" , UUIDType (binary = False ), nullable = False ))
24470 batch_op .create_primary_key ("dag_code_pkey" , ["id" ])
245- batch_op .alter_column ("dag_version_id" , existing_type = UUIDType (binary = False ), nullable = False )
71+ batch_op .add_column (sa .Column ("dag_version_id" , UUIDType (binary = False ), nullable = False ))
72+ batch_op .add_column (sa .Column ("source_code_hash" , sa .String (length = 32 ), nullable = False ))
73+ batch_op .add_column (sa .Column ("dag_id" , StringID (), nullable = False ))
74+ batch_op .add_column (sa .Column ("created_at" , TIMESTAMP (), default = timezone .utcnow , nullable = False ))
24675 batch_op .create_foreign_key (
24776 batch_op .f ("dag_code_dag_version_id_fkey" ),
24877 "dag_version" ,
@@ -251,16 +80,15 @@ def upgrade():
25180 ondelete = "CASCADE" ,
25281 )
25382 batch_op .create_unique_constraint ("dag_code_dag_version_id_uq" , ["dag_version_id" ])
254- batch_op .drop_column ("fileloc_hash" )
255- batch_op .alter_column ("source_code_hash" , existing_type = sa .String (length = 32 ), nullable = False )
256- batch_op .alter_column ("created_at" , existing_type = TIMESTAMP (), nullable = False )
25783
25884 with op .batch_alter_table ("serialized_dag" ) as batch_op :
25985 batch_op .drop_constraint ("serialized_dag_pkey" , type_ = "primary" )
260- batch_op .alter_column ("id" , existing_type = UUIDType (binary = False ), nullable = False )
261- batch_op .alter_column ("dag_version_id" , existing_type = UUIDType (binary = False ), nullable = False )
86+ batch_op .drop_index ("idx_fileloc_hash" )
26287 batch_op .drop_column ("fileloc_hash" )
26388 batch_op .drop_column ("fileloc" )
89+ batch_op .add_column (sa .Column ("id" , UUIDType (binary = False ), nullable = False ))
90+ batch_op .add_column (sa .Column ("dag_version_id" , UUIDType (binary = False ), nullable = False ))
91+ batch_op .add_column (sa .Column ("created_at" , TIMESTAMP (), default = timezone .utcnow , nullable = False ))
26492 batch_op .create_primary_key ("serialized_dag_pkey" , ["id" ])
26593 batch_op .create_foreign_key (
26694 batch_op .f ("serialized_dag_dag_version_id_fkey" ),
@@ -270,7 +98,6 @@ def upgrade():
27098 ondelete = "CASCADE" ,
27199 )
272100 batch_op .create_unique_constraint ("serialized_dag_dag_version_id_uq" , ["dag_version_id" ])
273- batch_op .alter_column ("created_at" , existing_type = TIMESTAMP (), nullable = False )
274101
275102 with op .batch_alter_table ("task_instance" , schema = None ) as batch_op :
276103 batch_op .add_column (sa .Column ("dag_version_id" , UUIDType (binary = False )))
@@ -299,113 +126,40 @@ def upgrade():
299126
300127def downgrade ():
301128 """Unapply add dag versioning."""
302- conn = op .get_bind ()
129+ with op .batch_alter_table ("task_instance" , schema = None ) as batch_op :
130+ batch_op .drop_constraint (batch_op .f ("task_instance_dag_version_id_fkey" ), type_ = "foreignkey" )
131+ batch_op .drop_column ("dag_version_id" )
303132
304133 with op .batch_alter_table ("task_instance_history" , schema = None ) as batch_op :
305134 batch_op .drop_column ("dag_version_id" )
306135
307- with op .batch_alter_table ("task_instance" , schema = None ) as batch_op :
308- batch_op .drop_constraint (batch_op .f ("task_instance_dag_version_id_fkey" ), type_ = "foreignkey" )
309- batch_op .drop_column ("dag_version_id" )
136+ with op .batch_alter_table ("dag_run" , schema = None ) as batch_op :
137+ batch_op .add_column (sa .Column ("dag_hash" , sa .String (length = 32 ), autoincrement = False , nullable = True ))
138+ batch_op .drop_constraint ("created_dag_version_id_fkey" , type_ = "foreignkey" )
139+ batch_op .drop_column ("created_dag_version_id" )
140+
141+ op .execute ("delete from dag_code;" )
142+ op .execute ("delete from serialized_dag;" )
310143
311144 with op .batch_alter_table ("dag_code" , schema = None ) as batch_op :
145+ batch_op .drop_constraint ("dag_code_pkey" , type_ = "primary" )
312146 batch_op .drop_constraint (batch_op .f ("dag_code_dag_version_id_fkey" ), type_ = "foreignkey" )
313147 batch_op .add_column (sa .Column ("fileloc_hash" , sa .BigInteger , nullable = True ))
148+ batch_op .create_primary_key ("dag_code_pkey" , ["fileloc_hash" ])
314149 batch_op .drop_column ("source_code_hash" )
315150 batch_op .drop_column ("created_at" )
316-
317- # Update the added fileloc_hash with the hash of fileloc
318- stmt = "SELECT fileloc FROM dag_code"
319- rows = _get_rows (stmt , conn )
320- stmt = sa .text ("""
321- UPDATE dag_code
322- SET fileloc_hash = :_hash
323- where fileloc = :fileloc and fileloc_hash is null
324- """ )
325- for row in rows :
326- hash = _airflow_2_fileloc_hash (row .fileloc )
327- conn .execute (stmt .bindparams (_hash = hash , fileloc = row .fileloc ))
151+ batch_op .drop_column ("id" )
152+ batch_op .drop_column ("dag_version_id" )
153+ batch_op .drop_column ("dag_id" )
328154
329155 with op .batch_alter_table ("serialized_dag" , schema = None , naming_convention = naming_convention ) as batch_op :
330156 batch_op .drop_column ("id" )
331- batch_op .add_column (sa .Column ("fileloc" , sa .String (length = 2000 ), nullable = True ))
332- batch_op .add_column (sa .Column ("fileloc_hash" , sa .BIGINT (), nullable = True ))
333-
334157 batch_op .drop_constraint (batch_op .f ("serialized_dag_dag_version_id_fkey" ), type_ = "foreignkey" )
335158 batch_op .drop_column ("created_at" )
336-
337- # Update the serialized fileloc with fileloc from dag_code where dag_version_id matches
338- if conn .dialect .name == "mysql" :
339- conn .execute (
340- sa .text ("""
341- UPDATE serialized_dag sd
342- JOIN dag_code dc ON sd.dag_version_id = dc.dag_version_id
343- SET sd.fileloc = dc.fileloc,
344- sd.fileloc_hash = dc.fileloc_hash
345- """ )
346- )
347- else :
348- conn .execute (
349- sa .text ("""
350- UPDATE serialized_dag
351- SET fileloc = dag_code.fileloc,
352- fileloc_hash = dag_code.fileloc_hash
353- FROM dag_code
354- WHERE serialized_dag.dag_version_id = dag_code.dag_version_id
355- """ )
356- )
357- # Deduplicate the rows in dag_code with the same fileloc_hash so we can make fileloc_hash the primary key
358- stmt = sa .text ("""
359- WITH ranked_rows AS (
360- SELECT
361- fileloc_hash,
362- ROW_NUMBER() OVER (PARTITION BY fileloc_hash ORDER BY id) as row_num
363- FROM dag_code
364- )
365- DELETE FROM dag_code
366- WHERE EXISTS (
367- SELECT 1
368- FROM ranked_rows
369- WHERE ranked_rows.fileloc_hash = dag_code.fileloc_hash
370- AND ranked_rows.row_num > 1
371- );
372- """ )
373- conn .execute (stmt )
374- with op .batch_alter_table ("serialized_dag" ) as batch_op :
375159 batch_op .drop_column ("dag_version_id" )
160+ batch_op .add_column (sa .Column ("fileloc" , sa .String (length = 2000 ), nullable = False ))
161+ batch_op .add_column (sa .Column ("fileloc_hash" , sa .BIGINT (), nullable = False ))
376162 batch_op .create_index ("idx_fileloc_hash" , ["fileloc_hash" ], unique = False )
377163 batch_op .create_primary_key ("serialized_dag_pkey" , ["dag_id" ])
378- batch_op .alter_column ("fileloc" , existing_type = sa .String (length = 2000 ), nullable = False )
379- batch_op .alter_column ("fileloc_hash" , existing_type = sa .BIGINT (), nullable = False )
380-
381- with op .batch_alter_table ("dag_code" ) as batch_op :
382- batch_op .drop_column ("id" )
383- batch_op .create_primary_key ("dag_code_pkey" , ["fileloc_hash" ])
384- batch_op .drop_column ("dag_version_id" )
385- batch_op .drop_column ("dag_id" )
386-
387- with op .batch_alter_table ("dag_run" , schema = None ) as batch_op :
388- batch_op .add_column (sa .Column ("dag_hash" , sa .String (length = 32 ), autoincrement = False , nullable = True ))
389- batch_op .drop_constraint ("created_dag_version_id_fkey" , type_ = "foreignkey" )
390- batch_op .drop_column ("created_dag_version_id" )
391-
392- # Update dag_run dag_hash with dag_hash from serialized_dag where dag_id matches
393- if conn .dialect .name == "mysql" :
394- conn .execute (
395- sa .text ("""
396- UPDATE dag_run dr
397- JOIN serialized_dag sd ON dr.dag_id = sd.dag_id
398- SET dr.dag_hash = sd.dag_hash
399- """ )
400- )
401- else :
402- conn .execute (
403- sa .text ("""
404- UPDATE dag_run
405- SET dag_hash = serialized_dag.dag_hash
406- FROM serialized_dag
407- WHERE dag_run.dag_id = serialized_dag.dag_id
408- """ )
409- )
410164
411165 op .drop_table ("dag_version" )
0 commit comments