-
Notifications
You must be signed in to change notification settings - Fork 0
/
.billet.rb
90 lines (82 loc) · 28.9 KB
/
.billet.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
83
84
85
86
87
88
89
90
items = ask(<<-QUESTION).split(?,).map(&:strip).map(&:to_i).each do |i|
Available migratios to generate are:
1. Timestamps for created_at/updated_at fields.
2. Add column with default value from nested table where is_default = true
3. Add field which contains generated uid
4. Add counter cache column.
5. Add foreign key to calculate it through join table.
6. Add cache value of column in another table.
7. Add cache column with sum by a column of nested records.
8. Add human readable slug generation
Choose migration to generate (use comma to run several ones): (1-8):
QUESTION
case ({ 1 => :timestamps, 2 => :default_column, 3 => :uid, 4 => :counter_cache, 5 => :belongs_directly, 6 => :column_cache, 7 => :sum_cache, 8 => :readable_slug })[i]
when :timestamps
begin
tablename = ask("Which table should be updated with timestamps?").strip.presence || say("Table name can't be blank.") || break
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_timestamps_for_#{tablename}.rb", "class TimestampsFor#{tablename.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n create_or_replace_touch_functions_and_triggers_on(:insert)\n create_or_replace_touch_functions_and_triggers_on(:update)\n create_or_replace_touch_functions_and_triggers_on(:delete)\n end\n\n private\n\n def table_names\n %w[#{tablename}].sort\n end\n\n def create_or_replace_touch_functions_and_triggers_on(action)\n table_names.each do |table_name|\n options = touch_options_for(table_name)\n \# next if options.none?\n\n execute %{\n CREATE OR REPLACE FUNCTION touch_for_\#{table_name}_on_\#{action}() RETURNS TRIGGER AS $$\n BEGIN\#{dynamic_touch_query(options)}\n \#{\"SELECT CURRENT_TIMESTAMP INTO NEW.updated_at;\n \" if action == :update}RETURN \#{action == :delete ? \'OLD\' : \'NEW\'};\n END;\n $$ LANGUAGE plpgsql;\n }\n\n create_touch_trigger_for(table_name, action)\n end\n end\n\n def create_touch_trigger_for(table_name, action)\n execute %{\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_touch_deps_on_\#{action} BEFORE \#{action.upcase} ON \#{table_name}\n FOR EACH ROW EXECUTE PROCEDURE touch_for_\#{table_name}_on_\#{action}();\n }\n end\n\n def touch_options_for(table_name)\n execute %(\n SELECT ccu.table_name AS foreign_table_name, kcu.column_name AS column_name\n FROM information_schema.table_constraints AS tc\n JOIN information_schema.key_column_usage AS kcu\n ON tc.constraint_name = kcu.constraint_name\n JOIN information_schema.constraint_column_usage AS ccu\n ON ccu.constraint_name = tc.constraint_name\n WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_name = \'\#{table_name}\'\n ORDER BY ccu.table_name\n )\n end\n\n def dynamic_touch_query(options)\n options.inject(\'\') do |query, opts|\n query << %(\n UPDATE \#{opts[\'foreign_table_name\']} SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.\#{opts[\'column_name\']};)\n end\n end\nend\n"
end while false
when :default_column
begin
parent_table = ask("Set parent table which should contains count column: ").strip.presence || say("Table name can't be blank.") || break
child_table = ask("Set a table with foreign key which one count of records should be calculated: ").strip.presence || say("Table name can't be blank.") || break
foreign_column = ask("Set foreign column (by default is '#{parent_table.singularize}_id'): ").strip.presence || "#{parent_table.singularize}_id"
child_column = ask("Set child column where value will be fetched: ").strip.presence || say("Child column can't be empty or generated from other fields") || break
parent_column = ask("Set parent column where value will be cached ('#{child_column}' by default): ").strip.presence || "#{child_column}"
child_default_column = ask("Set child boolean column where TRUE means that this is default value: ('is_default' by deafult)").strip.presence || "is_default"
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_add_default_column_#{parent_table.underscore}_#{parent_column.underscore}.rb", "class AddDefaultColumn#{parent_table.camelcase}#{parent_column.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n add_column \'#{parent_table}\', \'#{parent_column}\', :string, null: false\n\n add_column \'#{child_table}\', \'#{child_column}\', :string, null: false\n add_column \'#{child_table}\', \'#{child_default_column}\', :boolean, null: false, default: false, comment: \'Can be TRUE only once per #{child_table}.#{foreign_column}\'\n\n add_default_column_triggers(\n parent_table_name: \'#{parent_table}\',\n foreign_column: \'#{foreign_column}\',\n table_name: \'#{child_table}\',\n parent_column_name: \'#{parent_column}\',\n column_name: \'#{child_column}\',\n boolean_column_name: \'#{child_default_column}\'\n )\n end\n\n private\n\n def add_default_column_triggers(parent_table_name:, foreign_column:, table_name:, parent_column_name:, column_name:, boolean_column_name:)\n function_name1 = \"sync_\#{column_name[0..15]}_afr_\#{parent_table_name[0..15]}\"\n function_name2 = \"sync_\#{parent_table_name[0..15]}1_\#{parent_column_name[0..15]}\"\n function_name3 = \"sync_\#{parent_table_name[0..15]}_\#{table_name[0..15]}\"\n\n execute <<~SQL\n CREATE FUNCTION sync_\#{function_name1}() RETURNS trigger LANGUAGE plpgsql AS $$\n DECLARE existing_record \#{table_name}%ROWTYPE;\n BEGIN\n IF NEW.\#{parent_column_name} IS NULL THEN RETURN NEW; END IF;\n\n IF OLD IS NOT NULL AND OLD.\#{parent_column_name} = NEW.\#{parent_column_name} THEN RETURN NEW; END IF;\n\n SELECT id, \#{foreign_column}, \#{boolean_column_name} INTO existing_record FROM \#{table_name} WHERE \#{column_name} = NEW.\#{parent_column_name} LIMIT 1;\n\n IF existing_record.\#{foreign_column} IS NULL THEN\n INSERT INTO \#{table_name}(\#{foreign_column}, \#{column_name}, \#{boolean_column_name}) VALUES (NEW.id, NEW.\#{parent_column_name}, TRUE);\n ELSEIF existing_record.\#{foreign_column} <> NEW.id THEN\n RAISE EXCEPTION \'\"%\" value is already used by \#{parent_table_name} ID %\', NEW.\#{parent_column_name}, existing_record.\#{foreign_column};\n ELSEIF existing_record.\#{boolean_column_name} = FALSE THEN\n UPDATE \#{table_name} SET \#{boolean_column_name} = TRUE WHERE id = existing_record.id;\n END IF;\n\n RETURN NEW;\n END;\n $$;\n\n CREATE FUNCTION \#{function_name2}() RETURNS trigger LANGUAGE plpgsql AS $$\n BEGIN\n IF OLD IS NOT NULL AND OLD.\#{boolean_column_name} = TRUE AND NEW.\#{foreign_column} <> OLD.\#{foreign_column} THEN\n IF NOT EXISTS(SELECT id FROM \#{table_name} WHERE \#{foreign_column} = OLD.\#{foreign_column} AND \#{boolean_column_name} = true AND id <> OLD.id LIMIT 1) THEN\n RAISE EXCEPTION \'Set another primary phone for previous owner before changing \#{column_name} \#{foreign_column}\';\n END IF;\n END IF;\n IF NOT EXISTS(SELECT id FROM \#{table_name} WHERE \#{foreign_column} = NEW.\#{foreign_column} LIMIT 1) THEN\n NEW.\#{boolean_column_name} = TRUE;\n ELSIF NEW.\#{boolean_column_name} = TRUE AND (OLD IS NULL OR OLD.\#{boolean_column_name} = FALSE) THEN\n UPDATE \#{table_name} SET \#{boolean_column_name} = FALSE WHERE \#{foreign_column} = NEW.\#{foreign_column} AND \#{boolean_column_name} = TRUE;\n END IF;\n RETURN NEW;\n END;\n $$;\n\n CREATE FUNCTION \#{function_name3}() RETURNS trigger LANGUAGE plpgsql AS $$\n DECLARE table_owner \#{parent_table_name}%ROWTYPE;\n BEGIN\n IF NEW.\#{boolean_column_name} = TRUE THEN\n SELECT id, \#{parent_column_name} INTO table_owner FROM \#{parent_table_name} WHERE id = NEW.\#{foreign_column} LIMIT 1;\n IF table_owner.\#{parent_column_name} IS NULL OR table_owner.\#{parent_column_name} <> NEW.\#{column_name} THEN\n UPDATE \#{parent_table_name} SET \#{parent_column_name} = NEW.\#{column_name} WHERE id = NEW.\#{foreign_column};\n END IF;\n END IF;\n RETURN NULL;\n END;\n $$;\n\n CREATE TRIGGER _#{(rand(10) + 30).to_s.rjust(3, ?0)}_sync_\#{column_name[0..15]}_afr_\#{parent_table_name[0..15]}_i AFTER INSERT ON \#{parent_table_name} FOR EACH ROW EXECUTE PROCEDURE \#{function_name1}();\n CREATE TRIGGER _#{(rand(10) + 30).to_s.rjust(3, ?0)}_sync_\#{column_name[0..15]}_afr_\#{parent_table_name[0..15]}_u AFTER UPDATE ON \#{parent_table_name} FOR EACH ROW EXECUTE PROCEDURE \#{function_name1}();\n\n CREATE TRIGGER _#{(rand(10) + 30).to_s.rjust(3, ?0)}_sync_\#{parent_column_name}_bfr_i BEFORE INSERT ON \#{table_name} FOR EACH ROW EXECUTE PROCEDURE \#{function_name2}();\n CREATE TRIGGER _#{(rand(10) + 30).to_s.rjust(3, ?0)}_sync_\#{parent_column_name}_bfr_u BEFORE UPDATE ON \#{table_name} FOR EACH ROW EXECUTE PROCEDURE \#{function_name2}();\n\n CREATE TRIGGER _#{(rand(10) + 50).to_s.rjust(3, ?0)}_sync_\#{parent_table_name}_\#{parent_column_name}_bfr_i AFTER INSERT ON \#{table_name} FOR EACH ROW EXECUTE PROCEDURE \#{function_name3}();\n CREATE TRIGGER _#{(rand(10) + 50).to_s.rjust(3, ?0)}_sync_\#{parent_table_name}_\#{parent_column_name}_bfr_u AFTER UPDATE ON \#{table_name} FOR EACH ROW EXECUTE PROCEDURE \#{function_name3}();\n SQL\n end\nend\n"
end while false
when :uid
begin
is_append = ["n", "no"].include?(ask("Is this first time whene you add uid to the project? Y/n").strip.presence.downcase)
tablename = ask("Which table should have uid field?").strip.presence || say("Table name can't be empty.") || break
field_name = ask("Field name:").strip.presence || say("Field name can't be empty.") || break
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_add_field_#{field_name.underscore.gsub(/[^a-zA-Z]/, "_")}_to_#{tablename}.rb", (is_append ? "class AddHumanReadableSlugGenerationTo#{tablename.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n add_column :#{tablename}, :#{field_name}, :string, default: -> { \'generate_uid(36)\' }\n end\nend\n" : "class AddHumanReadableSlugGenerationTo#{tablename.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n execute \'create extension if not exists pgcrypto;\'\n execute <<-SQL.squish\n CREATE OR REPLACE FUNCTION generate_uid(i integer) RETURNS text AS $$\n BEGIN\n RETURN\n substring(lower(concat(\n encode(digest(concat(cast(now() AS text), gen_random_bytes(50)::text), \'sha512\'), \'hex\'),\n encode(digest(concat(cast(now() AS text), gen_random_bytes(50)::text), \'sha512\'), \'hex\')\n )) from 0 for i);\n END;\n $$ LANGUAGE plpgsql;\n SQL\n\n add_column :#{tablename}, :#{field_name}, :string, default: -> { \'generate_uid(36)\' }\n end\n\n def down\n execute %{\n DROP FUNCTION IF EXISTS generate_uid(integer);\n DROP EXTENSION IF EXISTS pgcrypto;\n }\n end\nend\n")
end while false
when :counter_cache
begin
parent_table = ask("Set parent table which should contains count column: ").strip.presence || say("Table name can't be blank.") || break
child_table = ask("Set a table with foreign key which one count of records should be calculated: ").strip.presence || say("Table name can't be blank.") || break
counter_column = ask("Set counter column (by default is '#{child_table}_count'): ").strip.presence || "#{child_table}_count"
foreign_column = ask("Set foreign column (by default is '#{parent_table.singularize}_id'): ").strip.presence || "#{child_table.singularize}_id"
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_add_counter_cache_to_#{parent_table}_for_#{child_table}.rb", "class AddCounterСacheTo#{parent_table.camelcase}For#{child_table.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n counter_trigger(parent_table: :#{parent_table}, child_table: :#{child_table}, counter_column: :#{counter_column}, foreign_column: :#{foreign_column})\n end\n\n private\n\n def counter_trigger(parent_table:, child_table:, counter_column:, foreign_column:)\n function_name = \"update_\#{parent_table}_\#{child_table}_counter\"\n execute %{\n CREATE OR REPLACE FUNCTION \#{function_name}_on_insert() RETURNS TRIGGER AS $$\n BEGIN\n UPDATE \#{parent_table} SET \#{counter_column} = COALESCE((SELECT COUNT(id) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = NEW.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = NEW.\#{foreign_column});\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION \#{function_name}_on_delete() RETURNS TRIGGER AS $$\n BEGIN\n UPDATE \#{parent_table} SET \#{counter_column} = COALESCE((SELECT COUNT(id) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = OLD.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = OLD.\#{foreign_column});\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION \#{function_name}_on_update() RETURNS TRIGGER AS $$\n BEGIN\n IF NEW.\#{foreign_column} <> OLD.\#{foreign_column} THEN\n UPDATE \#{parent_table} SET \#{counter_column} = COALESCE((SELECT COUNT(id) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = NEW.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = NEW.\#{foreign_column});\n UPDATE \#{parent_table} SET \#{counter_column} = COALESCE((SELECT COUNT(id) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = OLD.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = OLD.\#{foreign_column});\n END IF;\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_insert AFTER INSERT ON \#{child_table} FOR EACH ROW EXECUTE PROCEDURE \#{function_name}_on_insert();\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_update AFTER UPDATE ON \#{child_table} FOR EACH ROW EXECUTE PROCEDURE \#{function_name}_on_update();\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_delete AFTER DELETE ON \#{child_table} FOR EACH ROW EXECUTE PROCEDURE \#{function_name}_on_delete();\n }\n end\nend\n"
end while false
when :belongs_directly
begin
child_table = ask("Current table name with new foreign key: ").strip.presence || say("Table name can't be blank.") || break
parent_table = ask("Set parent table which should contains foreign key: ").strip.presence || say("Table name can't be blank.") || break
grandpa_table = ask("Set grandpa table name: ").strip.presence || say("Table name can't be blank.") || break
parent_foreign_column = ask("Column name to join parent table (by default is '#{parent_table.singularize}_id'): ").strip.presence || "#{parent_table.singularize}_id"
new_foreign_column = ask("Set new foreign column name (by default is '#{grandpa_table.singularize}_id'): ").strip.presence || "#{grandpa_table.singularize}_id"
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_belongs_direcly_to_#{grandpa_table}_for_#{child_table}.rb", "class BelongsDirectlyTo#{grandpa_table.camelcase}For#{child_table.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n add_reference :#{child_table}, :#{grandpa_table.singularize}, index: true\n execute <<-SQL\n CREATE FUNCTION #{child_table}_belongs_directly_to_#{grandpa_table}() RETURNS trigger\n LANGUAGE plpgsql\n AS $$\n BEGIN\n SELECT #{new_foreign_column} FROM #{parent_table} WHERE #{parent_table}.id = NEW.#{parent_foreign_column} INTO NEW.#{new_foreign_column};\n\n RETURN NEW;\n END;\n $$;\n\n CREATE TRIGGER _#{(rand(50) + 50).to_s.rjust(3, ?0)}_bdt_#{grandpa_table.singularize}_i BEFORE INSERT ON #{child_table} FOR EACH ROW EXECUTE PROCEDURE #{child_table}_belongs_directly_to_#{grandpa_table}();\n CREATE TRIGGER _#{(rand(50) + 50).to_s.rjust(3, ?0)}_bdt_#{grandpa_table.singularize}_u BEFORE UPDATE OF #{parent_foreign_column} ON #{child_table} FOR EACH ROW EXECUTE PROCEDURE #{child_table}_belongs_directly_to_#{grandpa_table}();\n SQL\n end\nend\n"
end while false
when :column_cache
begin
parent_table = ask("Set parent table which should contains count column: ").strip.presence || say("Table name can't be blank.") || break
child_table = ask("Set a table with foreign key which one count of records should be calculated: ").strip.presence || say("Table name can't be blank.") || break
foreign_column = ask("Set foreign column (by default is '#{parent_table.singularize}_id'): ").strip.presence || "#{parent_table.singularize}_id"
child_column = ask("Set child column where value will be cached: ").strip.presence || break
parent_column = ask("Set parent column where value will be fetched: ").strip.presence || break
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_cache_column_#{parent_column}_from_#{parent_table}_to_#{child_table}.rb", "class CacheColumn#{parent_column.camelcase}From#{parent_table.camelcase}To#{child_table.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n cache_column_trigger(parent_table: \'#{parent_table}\', child_table: \'#{child_table}\', columns_map: { \'#{parent_column}\' => \'#{child_column}\' }, foreign_column: \'#{foreign_column}\')\n end\n\n private\n\n def cache_column_trigger(parent_table:, child_table:, columns_map: {}, foreign_column:, primary_column: :id, function_name: nil)\n parent_columns = columns_map.to_a.map(&:first)\n child_columns = columns_map.to_a.map(&:second)\n\n function_name = \"cache_\#{child_table[0..10]}_\#{parent_table[0..10]}_cols\" if function_name.nil?\n\n execute %{\n CREATE OR REPLACE FUNCTION \#{function_name}_child() RETURNS TRIGGER AS $$\n BEGIN\n SELECT \#{parent_columns.join(\', \')} INTO \#{child_columns.map { |i| \"NEW.\#{i}\" }.join(\', \')} FROM \#{parent_table} WHERE \#{parent_table}.\#{primary_column} = NEW.\#{foreign_column} LIMIT 1;\n\n RETURN NEW;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION \#{function_name}_parent() RETURNS TRIGGER AS $$\n BEGIN\n UPDATE \#{child_table} SET \#{columns_map.map { |i,j| \"\#{i} = NEW.\#{j}\" }.join(\', \')} WHERE \#{child_table}.\#{foreign_column} = NEW.\#{primary_column};\n\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_i BEFORE INSERT ON \#{child_table}\n FOR EACH ROW\n EXECUTE PROCEDURE \#{function_name}_child();\n\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_u BEFORE UPDATE ON \#{child_table}\n FOR EACH ROW WHEN (\#{child_columns.map { |i| \"(NEW.\#{i} <> OLD.\#{i})\" }.join(\' AND \')} AND (NEW.\#{foreign_column} IS NOT NULL))\n EXECUTE PROCEDURE \#{function_name}_child();\n\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_u BEFORE UPDATE ON \#{parent_table}\n FOR EACH ROW WHEN (\#{parent_columns.map { |i| \"(NEW.\#{i} <> OLD.\#{i})\" }.join(\' AND \')})\n EXECUTE PROCEDURE \#{function_name}_parent();\n }\n end\nend\n"
end while false
when :sum_cache
begin
parent_table = ask("Set parent table which should contains count column: ").strip.presence || say("Table name can't be blank.") || break
child_table = ask("Set a table with foreign key which one count of records should be calculated: ").strip.presence || say("Table name can't be blank.") || break
sum_column = ask("Set column which should contains sum (by default is '#{child_table.underscore}_sum'): ").strip.presence || "#{child_table.underscore}_sum"
child_column = ask("Set column of nested table which should be sum up: ").strip.presence || break
foreign_column = ask("Set foreign column (by default is '#{parent_table.singularize.underscore}_id'): ").strip.presence || "#{parent_table.singularize.underscore}_id"
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_add_sum_cache_to_#{parent_table.underscore}_for_#{child_table.underscore}_#{sum_column.underscore}.rb", "class AddSumСacheTo#{parent_table.camelcase}For#{child_table.camelcase}#{sum_column.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n sum_trigger(parent_table: :#{parent_table}, child_table: :#{child_table}, counter_column: :#{counter_column}, foreign_column: :#{foreign_column})\n sum_trigger(parent_table: :#{parent_table}, child_table: :#{child_table}, sum_column: :#{sum_column}, foreign_column: :#{foreign_column}, child_column:)\n end\n\n private\n\n def sum_trigger(parent_table:, child_table:, sum_column:, foreign_column:, child_column:)\n function_name = \"upd_\#{parent_table[0..15]}_\#{child_table[0..15]}_sum\"\n execute %{\n CREATE OR REPLACE FUNCTION \#{function_name}_on_insert() RETURNS TRIGGER AS $$\n BEGIN\n UPDATE \#{parent_table} SET \#{sum_column} = COALESCE((SELECT SUM(\#{child_column}) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = NEW.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = NEW.\#{foreign_column});\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION \#{function_name}_on_delete() RETURNS TRIGGER AS $$\n BEGIN\n UPDATE \#{parent_table} SET \#{sum_column} = COALESCE((SELECT SUM(\#{child_column}) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = OLD.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = OLD.\#{foreign_column});\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION \#{function_name}_on_update() RETURNS TRIGGER AS $$\n BEGIN\n IF NEW.\#{foreign_column} <> OLD.\#{foreign_column} THEN\n UPDATE \#{parent_table} SET \#{sum_column} = COALESCE((SELECT SUM(\#{child_column}) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = NEW.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = NEW.\#{foreign_column});\n UPDATE \#{parent_table} SET \#{sum_column} = COALESCE((SELECT SUM(\#{child_column}) FROM \#{child_table} GROUP BY \#{foreign_column} HAVING \#{foreign_column} = OLD.\#{foreign_column}), 0) WHERE (\#{parent_table}.id = OLD.\#{foreign_column});\n END IF;\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_insert AFTER INSERT ON \#{child_table} FOR EACH ROW EXECUTE PROCEDURE \#{function_name}_on_i();\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_update AFTER UPDATE ON \#{child_table} FOR EACH ROW EXECUTE PROCEDURE \#{function_name}_on_u();\n CREATE TRIGGER _#{(rand(10) + 10).to_s.rjust(3, ?0)}_\#{function_name}_on_delete AFTER DELETE ON \#{child_table} FOR EACH ROW EXECUTE PROCEDURE \#{function_name}_on_d();\n }\n end\nend\n"
end while false
when :readable_slug
begin
is_append = ["n", "no"].include?(ask("Is this first time whene you add slug to this project? Y/n").strip.presence.downcase)
tablename = ask("Which table should have readable slug?").strip.presence || say("Table name can't be blank.") || break
slug_prefix = ask("Choose slug prefix (a couple of letters length is preferred):") || say("Slug prefix can't be blank") || break
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_create_slug_system.rb", "class CreateSlugSystem < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n VOWELS = %w[A E I O U Y].freeze\n CONSONANTS = %w[B C D F G H J K L M N P Q R S T V W X Z].freeze\n SEED = #{38.times.map { |i| rand(10) }.join}\n\n def up\n add_tables_and_columns\n create_static_sql_functions\n generate_prefixes\n end\n\n private\n\n def add_tables_and_columns\n create_table :prefix_letters do |t|\n t.string :key, null: false, unique: true\n t.column :table_name, :regclass, null: false, unique: true\n end\n\n create_table :prefix_sequence_list do |t|\n t.date :key, null: false, unique: true\n t.string :sequence, null: false, unique: true\n end\n \# add column if table already exists\n \# add_column :prefix_sequence_list, :clients_sequence_reseted, :boolean, null: false, default: false\n\n add_index :prefix_sequence_list, :key, unique: true\n end\n\n def generate_prefixes\n codes = VOWELS.each_with_object([]) do |a, obj|\n CONSONANTS.each { |b| CONSONANTS.each { |c| obj.push(\"\#{b}\#{a}\#{c}\") } }\n end\n\n start_date = Date.yesterday\n\n values = codes.shuffle(random: Random.new(SEED)).map do |code|\n start_date = start_date.tomorrow\n %((\'\#{start_date}\', \'\#{code}\'))\n end\n\n execute <<~SQL\n INSERT INTO prefix_sequence_list (\"key\", \"sequence\") VALUES \#{values.join(\', \')};\n SQL\n end\n\n def create_static_sql_functions\n execute(\n <<~SQL\n CREATE OR REPLACE FUNCTION generate_slug(table_name regclass, seq_name TEXT) RETURNS TEXT AS $$\n DECLARE\n daily_code TEXT;\n prefix TEXT;\n BEGIN\n SELECT fetch_daily_code(table_name, seq_name) INTO daily_code;\n SELECT fetch_table_prefix(table_name) INTO prefix;\n\n RETURN CONCAT_WS(\'-\', prefix, daily_code, nextval(seq_name));\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION fetch_daily_code(table_name regclass, seq_name TEXT) RETURNS TEXT AS $$\n DECLARE\n prefix_sequence_list_id prefix_sequence_list.id%TYPE;\n daily_code TEXT;\n used BOOLEAN;\n BEGIN\n EXECUTE format(\'SELECT sl.id, sl.sequence, sl.%s_sequence_reseted FROM prefix_sequence_list AS sl WHERE sl.key = now()::date LIMIT 1;\', table_name) INTO prefix_sequence_list_id, daily_code, used;\n\n IF NOT used THEN\n EXECUTE format(\'UPDATE prefix_sequence_list AS sl SET %s_sequence_reseted = TRUE WHERE sl.id = %s;\', table_name, prefix_sequence_list_id);\n EXECUTE format(\'ALTER SEQUENCE %s RESTART WITH 1;\', seq_name);\n END IF;\n\n RETURN daily_code;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION fetch_table_prefix(tname regclass) RETURNS TEXT AS $$\n DECLARE\n prefix TEXT;\n BEGIN\n SELECT key INTO prefix FROM prefix_letters WHERE \"table_name\" = tname LIMIT 1;\n\n RETURN prefix;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE OR REPLACE FUNCTION restrict_slug_update() RETURNS TRIGGER AS $$\n BEGIN\n NEW.slug = OLD.slug;\n RETURN NEW;\n END;\n $$ LANGUAGE plpgsql;\n SQL\n )\n end\nend\n" if is_append
create_file "db/migrate/#{Time.now.utc.strftime("%Y%m%d%H%M%S")}_add_human_readable_slug_generation_to_#{tablename}.rb", "class AddHumanReadableSlugGenerationTo#{tablename.camelcase} < ActiveRecord::Migration[#{Rails::VERSION::MAJOR}.#{Rails::VERSION::MINOR}]\n def up\n add_column :#{tablename}, :slug, :string\n update_existing_records(:#{tablename}, \'#{slug_prefix}\')\n\n generate_slug(:#{tablename}, \'#{slug_prefix}\')\n\n change_column_null :#{tablename}, :slug, false\n add_index :#{tablename}, [:slug], unique: true, name: \'slug_index\'\n end\n\n private\n\n def update_existing_records(table_name, prefix)\n execute <<~SQL\n UPDATE \#{table_name} SET slug = CONCAT(\'\#{prefix}-AAA-\', id);\n SQL\n end\n\n def generate_slug(table_name, prefix)\n execute <<~SQL\n INSERT INTO prefix_letters(\"table_name\", \"key\") VALUES(\'\#{table_name}\', \'\#{prefix}\');\n SQL\n\n add_column :prefix_sequence_list, \"\#{table_name}_sequence_reseted\", :boolean, default: false\n\n execute <<~SQL\n CREATE SEQUENCE \#{table_name}_daily_seq OWNED BY prefix_sequence_list.key;\n\n CREATE OR REPLACE FUNCTION human_slug_for_\#{table_name}() RETURNS TRIGGER AS $$\n BEGIN\n SELECT generate_slug(\'\#{table_name}\'::regclass, \'\#{table_name}_daily_seq\') INTO NEW.slug;\n RETURN NEW;\n END;\n $$ LANGUAGE plpgsql;\n\n CREATE TRIGGER _#{(rand(10) + 20).to_s.rjust(3, ?0)}_gen_slug_for_\#{table_name}_on_i BEFORE INSERT ON \#{table_name}\n FOR EACH ROW EXECUTE PROCEDURE human_slug_for_\#{table_name}();\n\n CREATE TRIGGER _#{(rand(50) + 200).to_s.rjust(3, ?0)}_restrict_slug BEFORE UPDATE OF slug ON \#{table_name}\n FOR EACH ROW WHEN (NEW.slug <> OLD.slug) EXECUTE PROCEDURE restrict_slug_update();\n SQL\n end\nend\n"
end while false
else
puts "Nothing to generate"
end
end