Coverage for /usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/versions/sqlite/2023_10_30_103720_22ef3915ccd8_index_and_backfill_block_type_name.py: 86%

19 statements  

« prev     ^ index     » next       coverage.py v7.10.6, created at 2025-12-05 11:21 +0000

1"""Index and backfill block_type_name 

2 

3Revision ID: 22ef3915ccd8 

4Revises: cef24af2ec34 

5Create Date: 2023-10-30 10:37:20.922002 

6 

7""" 

8 

9import sqlalchemy as sa 1a

10from alembic import op 1a

11 

12# revision identifiers, used by Alembic. 

13revision = "22ef3915ccd8" 1a

14down_revision = "cef24af2ec34" 1a

15branch_labels = None 1a

16depends_on = None 1a

17 

18 

19def upgrade(): 1a

20 with op.batch_alter_table("block_document", schema=None) as batch_op: 1a

21 batch_op.create_index( 1a

22 batch_op.f("ix_block_document__block_type_name_name"), 

23 ["block_type_name", "name"], 

24 unique=False, 

25 ) 

26 

27 backfill_query = """ 1a

28 UPDATE block_document 

29 SET block_type_name = (SELECT name from block_type where block_type.id = block_document.block_type_id) 

30 WHERE block_document.id in (SELECT id from block_document where block_type_name is null limit 500); 

31 """ 

32 

33 with op.get_context().autocommit_block(): 1a

34 conn = op.get_bind() 1a

35 while True: 1a

36 # Execute until we've backfilled all block_type_names 

37 result = conn.execute(sa.text(backfill_query)) 1a

38 if result.rowcount <= 0: 38 ↛ 35line 38 didn't jump to line 35 because the condition on line 38 was always true1a

39 break 1a

40 

41 

42def downgrade(): 1a

43 with op.get_context().autocommit_block(): 

44 op.execute( 

45 """ 

46 DROP INDEX 

47 ix_block_document__block_type_name_name 

48 """ 

49 )