Coverage for ivatar/ivataraccount/migrations/0021_add_performance_indexes.py: 54%

1# Generated manually for performance optimization 

2 

3from typing import Any, List, Tuple, Optional 

4from django.db import migrations, connection 

5 

6 

7def create_indexes(apps: Any, schema_editor: Any) -> None: 

8 """ 

9 Create performance indexes for both PostgreSQL and MySQL compatibility. 

10 Uses CONCURRENTLY for PostgreSQL production, regular CREATE INDEX for tests/transactions. 

11 """ 

12 db_engine = connection.vendor 

13 

14 indexes: List[Tuple[str, str, str, Optional[str]]] = [ 

15 # ConfirmedEmail indexes 

16 ("idx_cemail_digest", "ivataraccount_confirmedemail", "digest", None), 

17 ( 

18 "idx_cemail_digest_sha256", 

19 "ivataraccount_confirmedemail", 

20 "digest_sha256", 

21 None, 

22 ), 

23 ( 

24 "idx_cemail_access_count", 

25 "ivataraccount_confirmedemail", 

26 "access_count", 

27 None, 

28 ), 

29 ( 

30 "idx_cemail_bluesky_handle", 

31 "ivataraccount_confirmedemail", 

32 "bluesky_handle", 

33 "WHERE bluesky_handle IS NOT NULL", 

34 ), 

35 # Photo indexes 

36 ("idx_photo_format", "ivataraccount_photo", "format", None), 

37 ("idx_photo_access_count", "ivataraccount_photo", "access_count", None), 

38 # Composite indexes 

39 ( 

40 "idx_cemail_user_access", 

41 "ivataraccount_confirmedemail", 

42 "user_id, access_count", 

43 None, 

44 ), 

45 ( 

46 "idx_cemail_photo_access", 

47 "ivataraccount_confirmedemail", 

48 "photo_id, access_count", 

49 None, 

50 ), 

51 ("idx_photo_user_format", "ivataraccount_photo", "user_id, format", None), 

52 ] 

53 

54 with connection.cursor() as cursor: 

55 # Check if we're in a transaction (test environment) 

56 try: 

57 cursor.execute("SELECT 1") 

58 in_transaction = connection.in_atomic_block 

59 except Exception: 

60 in_transaction = True 

61 

62 for index_name, table_name, columns, where_clause in indexes: 

63 try: 

64 if db_engine == "postgresql": 

65 # Use CONCURRENTLY only if not in a transaction (production) 

66 # Use regular CREATE INDEX if in a transaction (tests) 

67 if in_transaction: 

68 # In transaction (test environment) - use regular CREATE INDEX 

69 if where_clause: 

70 sql = f"CREATE INDEX IF NOT EXISTS {index_name} ON {table_name}({columns}) {where_clause};" 

71 else: 

72 sql = f"CREATE INDEX IF NOT EXISTS {index_name} ON {table_name}({columns});" 

73 else: 

74 # Not in transaction (production) - use CONCURRENTLY 

75 if where_clause: 

76 sql = f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {index_name} ON {table_name}({columns}) {where_clause};" 

77 else: 

78 sql = f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {index_name} ON {table_name}({columns});" 

79 else: 

80 # MySQL and other databases - skip partial indexes 

81 if where_clause: 

82 print( 

83 f"Skipping partial index {index_name} for {db_engine} (not supported)" 

84 ) 

85 continue 

86 sql = f"CREATE INDEX IF NOT EXISTS {index_name} ON {table_name}({columns});" 

87 

88 cursor.execute(sql) 

89 print(f"Created index: {index_name}") 

90 

91 except Exception as e: 

92 # Index might already exist or other error - log and continue 

93 print(f"Index {index_name} creation skipped: {e}") 

94 

95 

96def drop_indexes(apps: Any, schema_editor: Any) -> None: 

97 """ 

98 Drop the performance indexes. 

99 """ 

100 indexes: List[str] = [ 

101 "idx_cemail_digest", 

102 "idx_cemail_digest_sha256", 

103 "idx_cemail_access_count", 

104 "idx_cemail_bluesky_handle", 

105 "idx_photo_format", 

106 "idx_photo_access_count", 

107 "idx_cemail_user_access", 

108 "idx_cemail_photo_access", 

109 "idx_photo_user_format", 

110 ] 

111 

112 with connection.cursor() as cursor: 

113 for index_name in indexes: 

114 try: 

115 cursor.execute(f"DROP INDEX IF EXISTS {index_name};") 

116 print(f"Dropped index: {index_name}") 

117 except Exception as e: 

118 print(f"Index {index_name} drop skipped: {e}") 

119 

120 

121class Migration(migrations.Migration): 

122 

123 dependencies = [ 

124 ("ivataraccount", "0020_confirmedopenid_bluesky_handle"), 

125 ] 

126 

127 operations = [ 

128 migrations.RunPython(create_indexes, drop_indexes), 

129 ]