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),

)



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

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
    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
215
216
217
 
# File 'lib/umbrellio_utils/migrations.rb', line 202

def create_distributed_table!(table_name, sharding_key, db_name: UmbrellioUtils::ClickHouse.db_name)
  cluster = UmbrellioUtils.config.clickhouse_cluster
  on_cluster = UmbrellioUtils::ClickHouse.on_cluster

  UmbrellioUtils::ClickHouse.execute(<<~SQL.squish)
    DROP TABLE IF EXISTS #{db_name}.#{table_name}_distributed
    #{on_cluster}
  SQL

  UmbrellioUtils::ClickHouse.execute(<<~SQL.squish)
    CREATE TABLE #{db_name}.#{table_name}_distributed
    #{on_cluster}
    AS #{db_name}.#{table_name}
    ENGINE = Distributed(#{cluster}, #{db_name}, #{table_name}, #{sharding_key})
  SQL
end

#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 = <<~SQL.squish
      DO $$
      BEGIN
        IF NOT EXISTS (
          SELECT 1
          FROM pg_constraint
          WHERE conname = '#{constraint_name}'
        ) THEN
          ALTER TABLE #{assoc_table} ADD CONSTRAINT #{constraint_name}
            FOREIGN KEY (#{assoc_name}) REFERENCES #{table_name}(id) NOT VALID;
        END IF;
      END$$;
    SQL
    DB.run(fkey_query)
  end
end

#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(<<~SQL.squish)
    LOCK TABLE #{table_name} IN ACCESS EXCLUSIVE MODE;

    CREATE OR REPLACE FUNCTION #{column_name}_bigint_trigger()
    RETURNS trigger
    AS
    $BODY$
    DECLARE
    BEGIN
        NEW.#{column_name}_bigint := NEW.#{column_name};
        RETURN NEW;
    END;
    $BODY$ LANGUAGE plpgsql;

    ALTER TABLE #{table_name} ADD #{column_name}_bigint BIGINT;

    CREATE TRIGGER #{table_name}_#{column_name}_bigint
      BEFORE INSERT OR UPDATE
      ON #{table_name}
      FOR EACH ROW
      EXECUTE FUNCTION #{column_name}_bigint_trigger();
  SQL
end

#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(<<~SQL.squish)
    LOCK TABLE #{table_name} IN ACCESS EXCLUSIVE MODE;

    CREATE OR REPLACE FUNCTION id_trigger()
    RETURNS trigger
    AS
    $BODY$
    DECLARE
    BEGIN
        NEW.id_bigint := NEW.id;
        RETURN NEW;
    END;
    $BODY$ LANGUAGE plpgsql;

    ALTER TABLE #{table_name} ADD id_bigint BIGINT;

    CREATE TRIGGER #{table_name}_bigint
      BEFORE INSERT OR UPDATE
      ON #{table_name}
      FOR EACH ROW
      EXECUTE FUNCTION id_trigger();
  SQL
end

#drop_columns_from_view(view_name, *columns) ⇒ Object

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



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

def drop_columns_from_view(view_name, *columns)
  DB.transaction do
    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 = <<~SQL.squish
      ALTER TABLE #{assoc_table} DROP CONSTRAINT IF EXISTS #{constraint_name};
    SQL
    DB.run(fkey_query)
  end
end

#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 = <<~SQL.squish
    LOCK TABLE #{table_name} IN ACCESS EXCLUSIVE MODE;
    DROP TRIGGER #{table_name}_#{column_name}_bigint ON #{table_name};
    ALTER TABLE #{table_name} RENAME #{column_name} TO #{column_name}_integer;
    ALTER TABLE #{table_name} RENAME #{column_name}_bigint TO #{column_name};
  SQL

  fkey_query = ""
  unless skip_constraint
    constraint_name = "#{table_name}_#{column_name}_fkey"
    ref_table_name = column_name.to_s.delete_suffix("_id").pluralize
    fkey_query = <<~SQL.squish
      ALTER TABLE #{table_name}
      DROP CONSTRAINT IF EXISTS #{constraint_name},
      ADD CONSTRAINT #{constraint_name}
      FOREIGN KEY (#{column_name}) REFERENCES #{ref_table_name}(id) NOT VALID;
    SQL
  end

  drop_query = <<~SQL.squish
    ALTER TABLE #{table_name} DROP #{column_name}_integer;
  SQL

  constr_query = ""
  if uniq_constr
    constr_query = <<~SQL.squish
      ALTER TABLE #{table_name}
      ADD CONSTRAINT #{table_name}_#{column_name}_key UNIQUE (#{column_name});
    SQL
  end

  pkey_query = ""
  if primary_key.present?
    pkey_query = <<~SQL.squish
      ALTER TABLE #{table_name} ADD CONSTRAINT #{table_name}_pkey PRIMARY KEY
        USING INDEX #{table_name}_#{primary_key.join("_")}_index;
    SQL
  end

  query = query_start + fkey_query + drop_query + constr_query + pkey_query
  DB.run(query)
end

#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 = <<~SQL.squish
    LOCK TABLE #{table_name} IN ACCESS EXCLUSIVE MODE;
    DROP TRIGGER #{table_name}_bigint ON #{table_name};
    ALTER TABLE #{table_name} RENAME id TO id_integer;
    ALTER TABLE #{table_name} RENAME id_bigint TO id;

    CREATE SEQUENCE IF NOT EXISTS new_#{table_name}_id_seq
      START WITH 1
      INCREMENT BY 1
      NO MINVALUE
      NO MAXVALUE
      CACHE 1;

    SELECT setval(
      'new_#{table_name}_id_seq',
      COALESCE((SELECT MAX(id) + 1 FROM #{table_name}), 1),
      false
    );
    ALTER TABLE #{table_name}
      ALTER COLUMN id SET DEFAULT nextval('new_#{table_name}_id_seq');
  SQL

  fkey_query = ""
  associations.map do |assoc_table, assoc_name|
    constraint_name = "#{assoc_table}_#{assoc_name}_fkey"

    fkey_query += <<~SQL.squish
      ALTER TABLE #{assoc_table}
      DROP CONSTRAINT IF EXISTS #{constraint_name}
    SQL
    if skip_fk_create
      fkey_query += ";"
      next
    end

    fkey_query += <<~SQL.squish
      , ADD CONSTRAINT #{constraint_name}
      FOREIGN KEY (#{assoc_name}) REFERENCES #{table_name}(id) NOT VALID;
    SQL
  end

  query_end = <<~SQL.squish
    ALTER TABLE #{table_name} DROP id_integer;
    ALTER TABLE #{table_name} ADD CONSTRAINT #{table_name}_pkey PRIMARY KEY
      USING INDEX #{table_name}_id_bigint_index;
  SQL

  query = query_start + fkey_query + query_end
  DB.run(query)
end