Module: UmbrellioUtils::Migrations

Extended by:
Migrations
Included in:
Migrations
Defined in:
lib/umbrellio_utils/migrations.rb

Instance Method Summary collapse

Instance Method Details

#add_columns_to_view(view_name, *sequel_columns) ⇒ Object

add_columns_to_view(

"orders_clickhouse_view",
Sequel[:orders][:data].pg_jsonb.get_text("some_data_column").as(:some_column),
Sequel[:orders][:column].as(:some_other_column),

)



222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/umbrellio_utils/migrations.rb', line 222

def add_columns_to_view(view_name, *sequel_columns)
  sequel_columns.each do |column|
    unless column.is_a?(Sequel::SQL::AliasedExpression)
      raise ArgumentError.new("not Sequel::SQL::AliasedExpression")
    end
  end

  DB.transaction do
    DB.run("LOCK TABLE #{view_name}")
    definition = view_definition(view_name)
    sql = sequel_columns.map { |x| DB.literal(x) }.join(", ")
    new_definition = definition.sub("FROM", ", #{sql} FROM")
    DB.run("CREATE OR REPLACE VIEW #{view_name} AS #{new_definition}")
  end
end

#check_associations(model, method, reverse_method) ⇒ Object

rubocop:enable Metrics/MethodLength



194
195
196
197
198
199
200
 
# File 'lib/umbrellio_utils/migrations.rb', line 194

def check_associations(model, method, reverse_method)
  model.dataset.limit(10).all.each do |record|
    res = record.public_send(method).public_send(reverse_method)
    raise StandardError if res.blank?
  end
  true
end

#check_id_consistency(table_name, col_name = "id") ⇒ Object 



139
140
141
142
143
144
145
 
# File 'lib/umbrellio_utils/migrations.rb', line 139

def check_id_consistency(table_name, col_name = "id")
  res = DB[table_name].where(
    Sequel[col_name.to_sym] !~ SQL.coalesce(Sequel[:"#{col_name}_bigint"], 0),
  ).count
  raise "Inconsistent ids in #{table_name}: #{res} records" if res.positive?
  true
end

#create_distributed_table!(table_name, sharding_key, db_name: UmbrellioUtils::ClickHouse.db_name) ⇒ Object 



202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/umbrellio_utils/migrations.rb', line 202

def create_distributed_table!(table_name, sharding_key, db_name: UmbrellioUtils::ClickHouse.db_name)
  UmbrellioUtils::ClickHouse.execute("    DROP TABLE IF EXISTS \#{db_name}.\#{table_name}_distributed\n    ON CLUSTER click_cluster\n  SQL\n\n  UmbrellioUtils::ClickHouse.execute(<<~SQL.squish)\n    CREATE TABLE \#{db_name}.\#{table_name}_distributed\n    ON CLUSTER click_cluster\n    AS \#{db_name}.\#{table_name}\n    ENGINE = Distributed(click_cluster, \#{db_name}, \#{table_name}, \#{sharding_key})\n  SQL\nend\n".squish)

#create_foreign_keys(table_name, associations) ⇒ Object 



94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/umbrellio_utils/migrations.rb', line 94

def create_foreign_keys(table_name, associations)
  associations.map do |assoc_table, assoc_name|
    constraint_name = "#{assoc_table}_#{assoc_name}_fkey"
    fkey_query = "      DO $$\n      BEGIN\n        IF NOT EXISTS (\n          SELECT 1\n          FROM pg_constraint\n          WHERE conname = '\#{constraint_name}'\n        ) THEN\n          ALTER TABLE \#{assoc_table} ADD CONSTRAINT \#{constraint_name}\n            FOREIGN KEY (\#{assoc_name}) REFERENCES \#{table_name}(id) NOT VALID;\n        END IF;\n      END$$;\n    SQL\n    DB.run(fkey_query)\n  end\nend\n".squish

#create_new_foreign_key_column(table_name, column_name) ⇒ Object 



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/umbrellio_utils/migrations.rb', line 114

def create_new_foreign_key_column(table_name, column_name)
  DB.run("    LOCK TABLE \#{table_name} IN ACCESS EXCLUSIVE MODE;\n\n    CREATE OR REPLACE FUNCTION \#{column_name}_bigint_trigger()\n    RETURNS trigger\n    AS\n    $BODY$\n    DECLARE\n    BEGIN\n        NEW.\#{column_name}_bigint := NEW.\#{column_name};\n        RETURN NEW;\n    END;\n    $BODY$ LANGUAGE plpgsql;\n\n    ALTER TABLE \#{table_name} ADD \#{column_name}_bigint BIGINT;\n\n    CREATE TRIGGER \#{table_name}_\#{column_name}_bigint\n      BEFORE INSERT OR UPDATE\n      ON \#{table_name}\n      FOR EACH ROW\n      EXECUTE FUNCTION \#{column_name}_bigint_trigger();\n  SQL\nend\n".squish)

#create_new_id_bigint_column(table_name) ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# File 'lib/umbrellio_utils/migrations.rb', line 7

def create_new_id_bigint_column(table_name)
  DB.run("    LOCK TABLE \#{table_name} IN ACCESS EXCLUSIVE MODE;\n\n    CREATE OR REPLACE FUNCTION id_trigger()\n    RETURNS trigger\n    AS\n    $BODY$\n    DECLARE\n    BEGIN\n        NEW.id_bigint := NEW.id;\n        RETURN NEW;\n    END;\n    $BODY$ LANGUAGE plpgsql;\n\n    ALTER TABLE \#{table_name} ADD id_bigint BIGINT;\n\n    CREATE TRIGGER \#{table_name}_bigint\n      BEFORE INSERT OR UPDATE\n      ON \#{table_name}\n      FOR EACH ROW\n      EXECUTE FUNCTION id_trigger();\n  SQL\nend\n".squish)

#drop_columns_from_view(view_name, *columns) ⇒ Object

drop_columns_from_view(“orders_clickhouse_view”, “id”, “guid”)



240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/umbrellio_utils/migrations.rb', line 240

def drop_columns_from_view(view_name, *columns)
  DB.transaction do
    DB.run("LOCK TABLE #{view_name}")
    definition = view_definition(view_name)
    parsed_columns = parse_columns(definition)
    parsed_columns.reject! { |name, _| name.in?(columns) }
    sql = parsed_columns.map { |_, sql| sql }.join(", ")
    new_definition = definition.sub(/SELECT(.*?)FROM/i, "SELECT #{sql} FROM")
    DB.run("DROP VIEW #{view_name}")
    DB.run("CREATE VIEW #{view_name} AS #{new_definition}")
  end
end

#drop_foreign_keys(_table_name, associations) ⇒ Object 



84
85
86
87
88
89
90
91
92
 
# File 'lib/umbrellio_utils/migrations.rb', line 84

def drop_foreign_keys(_table_name, associations)
  associations.map do |assoc_table, assoc_name|
    constraint_name = "#{assoc_table}_#{assoc_name}_fkey"
    fkey_query = "      ALTER TABLE \#{assoc_table} DROP CONSTRAINT IF EXISTS \#{constraint_name};\n    SQL\n    DB.run(fkey_query)\n  end\nend\n".squish

#drop_old_foreign_key_column(table_name, column_name, skip_constraint: false, primary_key: [], uniq_constr: false) ⇒ Object

rubocop:disable Metrics/MethodLength



148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'lib/umbrellio_utils/migrations.rb', line 148

def drop_old_foreign_key_column(table_name, column_name, skip_constraint: false,
                                primary_key: [], uniq_constr: false)
  query_start = "    LOCK TABLE \#{table_name} IN ACCESS EXCLUSIVE MODE;\n    DROP TRIGGER \#{table_name}_\#{column_name}_bigint ON \#{table_name};\n    ALTER TABLE \#{table_name} RENAME \#{column_name} TO \#{column_name}_integer;\n    ALTER TABLE \#{table_name} RENAME \#{column_name}_bigint TO \#{column_name};\n  SQL\n\n  fkey_query = \"\"\n  unless skip_constraint\n    constraint_name = \"\#{table_name}_\#{column_name}_fkey\"\n    ref_table_name = column_name.to_s.delete_suffix(\"_id\").pluralize\n    fkey_query = <<~SQL.squish\n      ALTER TABLE \#{table_name}\n      DROP CONSTRAINT IF EXISTS \#{constraint_name},\n      ADD CONSTRAINT \#{constraint_name}\n      FOREIGN KEY (\#{column_name}) REFERENCES \#{ref_table_name}(id) NOT VALID;\n    SQL\n  end\n\n  drop_query = <<~SQL.squish\n    ALTER TABLE \#{table_name} DROP \#{column_name}_integer;\n  SQL\n\n  constr_query = \"\"\n  if uniq_constr\n    constr_query = <<~SQL.squish\n      ALTER TABLE \#{table_name}\n      ADD CONSTRAINT \#{table_name}_\#{column_name}_key UNIQUE (\#{column_name});\n    SQL\n  end\n\n  pkey_query = \"\"\n  if primary_key.present?\n    pkey_query = <<~SQL.squish\n      ALTER TABLE \#{table_name} ADD CONSTRAINT \#{table_name}_pkey PRIMARY KEY\n        USING INDEX \#{table_name}_\#{primary_key.join(\"_\")}_index;\n    SQL\n  end\n\n  query = query_start + fkey_query + drop_query + constr_query + pkey_query\n  DB.run(query)\nend\n".squish

#drop_old_id_column(table_name, associations = {}, skip_fk_create: false) ⇒ Object

rubocop:disable Metrics/MethodLength



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/umbrellio_utils/migrations.rb', line 32

def drop_old_id_column(table_name, associations = {}, skip_fk_create: false) # rubocop:disable Metrics/MethodLength
  query_start = "    LOCK TABLE \#{table_name} IN ACCESS EXCLUSIVE MODE;\n    DROP TRIGGER \#{table_name}_bigint ON \#{table_name};\n    ALTER TABLE \#{table_name} RENAME id TO id_integer;\n    ALTER TABLE \#{table_name} RENAME id_bigint TO id;\n\n    CREATE SEQUENCE IF NOT EXISTS new_\#{table_name}_id_seq\n      START WITH 1\n      INCREMENT BY 1\n      NO MINVALUE\n      NO MAXVALUE\n      CACHE 1;\n\n    SELECT setval(\n      'new_\#{table_name}_id_seq',\n      COALESCE((SELECT MAX(id) + 1 FROM \#{table_name}), 1),\n      false\n    );\n    ALTER TABLE \#{table_name}\n      ALTER COLUMN id SET DEFAULT nextval('new_\#{table_name}_id_seq');\n  SQL\n\n  fkey_query = \"\"\n  associations.map do |assoc_table, assoc_name|\n    constraint_name = \"\#{assoc_table}_\#{assoc_name}_fkey\"\n\n    fkey_query += <<~SQL.squish\n      ALTER TABLE \#{assoc_table}\n      DROP CONSTRAINT IF EXISTS \#{constraint_name}\n    SQL\n    if skip_fk_create\n      fkey_query += \";\"\n      next\n    end\n\n    fkey_query += <<~SQL.squish\n      , ADD CONSTRAINT \#{constraint_name}\n      FOREIGN KEY (\#{assoc_name}) REFERENCES \#{table_name}(id) NOT VALID;\n    SQL\n  end\n\n  query_end = <<~SQL.squish\n    ALTER TABLE \#{table_name} DROP id_integer;\n    ALTER TABLE \#{table_name} ADD CONSTRAINT \#{table_name}_pkey PRIMARY KEY\n      USING INDEX \#{table_name}_id_bigint_index;\n  SQL\n\n  query = query_start + fkey_query + query_end\n  DB.run(query)\nend\n".squish