diff options
| -rw-r--r-- | app/models/referential_cloning.rb | 4 | ||||
| -rw-r--r-- | app/workers/referential_cloning_worker.rb | 4 | ||||
| -rw-r--r-- | config/initializers/active_record.rb | 4 | ||||
| -rw-r--r-- | lib/af83/schema_cloner.rb | 69 | ||||
| -rw-r--r-- | lib/af83/stored_procedures.rb | 44 | ||||
| -rw-r--r-- | lib/sql/clone_schema.sql | 114 | ||||
| -rw-r--r-- | lib/sql/message.sql | 8 | ||||
| -rw-r--r-- | spec/lib/af83/cloning/clone_schema_spec.rb | 46 | ||||
| -rw-r--r-- | spec/support/bare_sql.rb | 58 | ||||
| -rw-r--r-- | spec/support/hash.rb | 6 | ||||
| -rw-r--r-- | spec/support/pg_catalog.rb | 19 | ||||
| -rw-r--r-- | spec/workers/referential_cloning_worker_spec.rb | 7 |
12 files changed, 161 insertions, 222 deletions
diff --git a/app/models/referential_cloning.rb b/app/models/referential_cloning.rb index 9006b2ac5..5bf283814 100644 --- a/app/models/referential_cloning.rb +++ b/app/models/referential_cloning.rb @@ -6,8 +6,8 @@ class ReferentialCloning < ActiveRecord::Base private def perform_clone - # ReferentialCloningWorker.perform_async(id) - ReferentialCloningWorker.new.perform(id) + ReferentialCloningWorker.perform_async(id) + # ReferentialCloningWorker.new.perform(id) end aasm column: :status do diff --git a/app/workers/referential_cloning_worker.rb b/app/workers/referential_cloning_worker.rb index c74566966..6592160ec 100644 --- a/app/workers/referential_cloning_worker.rb +++ b/app/workers/referential_cloning_worker.rb @@ -16,7 +16,9 @@ class ReferentialCloningWorker def clone_schema ref_cloning, source_schema, target_schema ref_cloning.run! - StoredProcedures.invoke_stored_procedure(:clone_schema, source_schema, target_schema, true) + AF83::SchemaCloner + .new(source_schema, target_schema) + .clone_schema ref_cloning.successful! rescue Exception => e diff --git a/config/initializers/active_record.rb b/config/initializers/active_record.rb index bdf9e0b4b..0a7378532 100644 --- a/config/initializers/active_record.rb +++ b/config/initializers/active_record.rb @@ -1,5 +1 @@ -require_relative '../../lib/af83/stored_procedures' - ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:primary_key] = "bigserial primary key" - -StoredProcedures.create_stored_procedure(:clone_schema) diff --git a/lib/af83/schema_cloner.rb b/lib/af83/schema_cloner.rb index 299e63a82..40c04f400 100644 --- a/lib/af83/schema_cloner.rb +++ b/lib/af83/schema_cloner.rb @@ -1,7 +1,7 @@ module AF83 class SchemaCloner - attr_reader :source_schema, :target_schema, :include_records + attr_reader :source_schema, :target_schema def clone_schema assure_schema_preconditons @@ -10,10 +10,26 @@ module AF83 private + def adjust_default table_name, column_name, default_val + changed_default = default_val.sub(%r{\Anextval\('#{source_schema}}, "nextval('#{target_schema}") + execute "ALTER TABLE #{target_schema}.#{table_name} ALTER COLUMN #{column_name} SET DEFAULT #{changed_default}" + end + + def adjust_defaults table_name + pairs = execute <<-EOSQL + SELECT column_name, column_default + FROM information_schema.columns + WHERE table_schema = '#{target_schema}' AND table_name = '#{table_name}' AND + column_default LIKE 'nextval(''#{source_schema}%::regclass)' + EOSQL + pairs.each do | pair | + adjust_default table_name, pair['column_name'], pair['column_default'] + end + end + def alter_sequence sequence_name seq_props = execute_get_ostruct( "SELECT * FROM #{source_schema}.#{sequence_name}" ) cycle_on_off = seq_props.is_cycled == 't' ? '' : 'NO' - seq_value = include_records ? seq_props.last_value : seq_props.start_value execute <<-EOSQL ALTER SEQUENCE #{target_schema}.#{sequence_name} INCREMENT BY #{seq_props.increment_by} @@ -22,12 +38,11 @@ module AF83 START WITH #{seq_props.start_value} RESTART WITH #{seq_props.last_value} CACHE #{seq_props.cache_value} - #{cycle_on_off} CYCLE; - - - SELECT setval('#{target_schema}.#{sequence_name}', #{seq_value}, '#{seq_props.is_called}'); + #{cycle_on_off} CYCLE; - EOSQL + -- TODO: What is this good for? + SELECT setval('#{target_schema}.#{sequence_name}', #{seq_props.last_value}, '#{seq_props.is_called}'); + EOSQL end def assure_schema_preconditons @@ -37,8 +52,15 @@ module AF83 raise RuntimeError, "Source Schema #{source_schema} does not exist" unless source end + def clone_foreign_key fk_desc + relname, conname, constraint_def = fk_desc.values_at(*%w[relname conname constraint_def]) + constraint_def = constraint_def.sub(" REFERENCES #{source_schema}.", " REFERENCES #{target_schema}.") + execute <<-EOSQL + ALTER TABLE #{target_schema}.#{relname} ADD CONSTRAINT #{conname} #{constraint_def} + EOSQL + end def clone_foreign_keys - + get_foreign_keys.each(&method(:clone_foreign_key)) end def clone_sequence sequence_name @@ -51,8 +73,8 @@ module AF83 def clone_table table_name create_table table_name - end + def clone_tables table_names.each(&method(:clone_table)) end @@ -62,8 +84,8 @@ module AF83 end def create_table table_name execute "CREATE TABLE #{target_schema}.#{table_name} (LIKE #{source_schema}.#{table_name} INCLUDING ALL)" - return unless include_records - execute "INSERT INTO #{target_schema}.#{table_name} SELECT * FROM #{source_schema}.#{table_name}" + execute "INSERT INTO #{target_schema}.#{table_name} SELECT * FROM #{source_schema}.#{table_name}" + adjust_defaults table_name end def create_target_schema execute("CREATE SCHEMA #{target_schema}") @@ -84,10 +106,19 @@ module AF83 execute(str).flat_map(&:values) end - def initialize(source_schema, target_schema, include_records: true) + def get_foreign_keys + execute <<-EOS + SELECT rn.relname, ct.conname, pg_get_constraintdef(ct.oid) AS constraint_def + FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid + WHERE connamespace = #{source['oid']} AND rn.relkind = 'r' AND ct.contype = 'f' + EOS + end + def get_columns(table_name) + end + + def initialize(source_schema, target_schema) @source_schema = source_schema @target_schema = target_schema - @include_records = include_records end # @@ -98,19 +129,19 @@ module AF83 end def source - @__source__ ||= execute("SELECT oid FROM pg_namespace WHERE nspname = '#{source_schema}' LIMIT 1").first; + @__source__ ||= execute("SELECT oid FROM pg_namespace WHERE nspname = '#{source_schema}' LIMIT 1").first; end def source_sequence_names - @__source_sequence_names__ ||= - execute_get_values \ - "SELECT sequence_name::text FROM information_schema.sequences WHERE sequence_schema = '#{source_schema}'" + @__source_sequence_names__ ||= + execute_get_values \ + "SELECT sequence_name::text FROM information_schema.sequences WHERE sequence_schema = '#{source_schema}'" end def source_oid @__source_oid__ ||= source["oid"].to_i; end def table_names - @__table_names__ ||= execute_get_values \ - "SELECT TABLE_NAME::text FROM information_schema.tables WHERE table_schema = '#{ source_schema }' AND table_type = 'BASE TABLE'" + @__table_names__ ||= execute_get_values \ + "SELECT TABLE_NAME::text FROM information_schema.tables WHERE table_schema = '#{ source_schema }' AND table_type = 'BASE TABLE'" end end end diff --git a/lib/af83/stored_procedures.rb b/lib/af83/stored_procedures.rb deleted file mode 100644 index b13941a32..000000000 --- a/lib/af83/stored_procedures.rb +++ /dev/null @@ -1,44 +0,0 @@ -module StoredProcedures extend self - - def invoke_stored_procedure(name, *params) - name = name.to_s - raise ArgumentError, "no such stored procedure #{name.inspect}" unless stored_procedures[name] - invocation = "#{name}(#{quote_params(params)})" - ActiveRecord::Base.connection.execute "SELECT #{invocation}" - end - - def create_stored_procedure(name) - name = name.to_s - sql_file = File.expand_path("../../sql/#{name}.sql", __FILE__) - raise ArgumentError, "missing sql file #{sql_file.inspect}" unless File.readable? sql_file - - # We could store the file's content for reload without application restart if desired. - stored_procedures[name] = true - - ActiveRecord::Base.connection.execute File.read(sql_file) - end - - private - def quote_params(params) - params - .map(&method(:quote_param)) - .join(", ") - end - - def quote_param(param) - case param - when String - "'#{param}'" - when TrueClass - "'t'" - when FalseClass - "'f'" - else - param - end - end - - def stored_procedures - @__stored_procedures__ ||= {} - end -end diff --git a/lib/sql/clone_schema.sql b/lib/sql/clone_schema.sql deleted file mode 100644 index 9fb88466b..000000000 --- a/lib/sql/clone_schema.sql +++ /dev/null @@ -1,114 +0,0 @@ -CREATE OR REPLACE FUNCTION clone_schema( source_schema text, dest_schema text, include_recs boolean) RETURNS void AS -$BODY$ - DECLARE - src_oid oid; - tbl_oid oid; - func_oid oid; - object text; - buffer text; - srctbl text; - default_ text; - column_ text; - qry text; - dest_qry text; - v_def text; - seqval bigint; - sq_last_value bigint; - sq_max_value bigint; - sq_start_value bigint; - sq_increment_by bigint; - sq_min_value bigint; - sq_cache_value bigint; - sq_log_cnt bigint; - sq_is_called boolean; - sq_is_cycled boolean; - sq_cycled char(10); - BEGIN - -- Assure that source_schema exists - SELECT oid INTO src_oid FROM pg_namespace WHERE nspname = quote_ident(source_schema); - IF NOT FOUND THEN - RAISE NOTICE 'source schema % does not exist!', source_schema; - RETURN; - END IF; - - -- Refute that dest_schema exists and then create it - PERFORM nspname FROM pg_namespace WHERE nspname = quote_ident(dest_schema); - IF FOUND THEN - RAISE NOTICE 'dest schema % already exists!', dest_schema; - RETURN; - END IF; - EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ; - - -- loop over sequences, creating the same one in the destination namespace, ... - FOR object IN - SELECT sequence_name::text FROM information_schema.sequences WHERE sequence_schema = quote_ident(source_schema) - LOOP - EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object); - - -- ...storing the attributes of the sequence from the source namespace in local variables, ... - srctbl := quote_ident(source_schema) || '.' || quote_ident(object); - EXECUTE 'SELECT last_value, max_value, start_value, increment_by, min_value, cache_value, log_cnt, is_cycled, is_called FROM ' || srctbl || ';' INTO sq_last_value, sq_max_value, sq_start_value, sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, sq_is_called; - IF sq_is_cycled THEN - sq_cycled := 'CYCLE'; - ELSE - sq_cycled := 'NO CYCLE'; - END IF; - EXECUTE 'ALTER SEQUENCE ' || (dest_schema) || '.' || quote_ident(object) || ' INCREMENT BY ' || sq_increment_by || ' MINVALUE ' || sq_min_value || ' MAXVALUE ' || sq_max_value || ' START WITH ' || sq_start_value || ' RESTART ' || sq_min_value || ' CACHE ' || sq_cache_value || sq_cycled || ' ;' ; - - buffer := quote_ident(dest_schema) || '.' || quote_ident(object); - IF include_recs THEN - EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ; - ELSE - EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ; - END IF; - END LOOP; - - -- loop over tables in source schema,... - FOR object IN - SELECT TABLE_NAME::text FROM information_schema.tables WHERE table_schema = quote_ident(source_schema) AND table_type = 'BASE TABLE' - LOOP - -- ...creating the table in the destination schema, potentially including the records - buffer := dest_schema || '.' || quote_ident(object); - EXECUTE 'CREATE TABLE ' || buffer || '(LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) || ' INCLUDING ALL)'; - IF include_recs THEN - EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'; - END IF; - - -- alter table, assuring the destination schema's table has: - -- * the same defaults - FOR column_, default_ - IN SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) - FROM information_schema.COLUMNS - WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)' - LOOP - EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_; - END LOOP; - END LOOP; - - /* SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || pg_get_constraintdef(ct.oid) || ';' FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid WHERE connamespace = src_oid AND rn.relkind = 'r' AND ct.contype = 'f' LIMIT 2; */ - -- apply all constraints on tables in destination schema - FOR qry IN - SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || REPLACE(pg_get_constraintdef(ct.oid), source_schema || '.', dest_schema || '.') || ';' - FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid WHERE connamespace = src_oid AND rn.relkind = 'r' AND ct.contype = 'f' - LOOP - EXECUTE qry; - END LOOP; - - -- create views from source schema in destination schema - FOR object IN - SELECT table_name::text, view_definition FROM information_schema.views WHERE table_schema = quote_ident(source_schema) - LOOP - buffer := dest_schema || '.' || quote_ident(object); - SELECT view_definition INTO v_def FROM information_schema.views WHERE table_schema = quote_ident(source_schema) AND table_name = quote_ident(object); - EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ; - END LOOP; - - FOR func_oid IN SELECT oid FROM pg_proc WHERE pronamespace = src_oid - LOOP - SELECT pg_get_functiondef(func_oid) INTO qry; - SELECT replace(qry, source_schema, dest_schema) INTO dest_qry; - EXECUTE dest_qry; - END LOOP; - RETURN; - END; -$BODY$ LANGUAGE plpgsql VOLATILE COST 100; diff --git a/lib/sql/message.sql b/lib/sql/message.sql deleted file mode 100644 index 94d7a3496..000000000 --- a/lib/sql/message.sql +++ /dev/null @@ -1,8 +0,0 @@ -CREATE OR REPLACE FUNCTION message( content text) RETURNS void AS -$BODY$ - BEGIN - INSERT INTO xxx VALUES (nextval('xxx_id_seq'), 'message from stored procedure: ' || content, current_timestamp); - RAISE EXCEPTION 'Oh no'; - RETURN; - END; -$BODY$ LANGUAGE plpgsql VOLATILE COST 100; diff --git a/spec/lib/af83/cloning/clone_schema_spec.rb b/spec/lib/af83/cloning/clone_schema_spec.rb index 5e441cc8e..3d541f3e9 100644 --- a/spec/lib/af83/cloning/clone_schema_spec.rb +++ b/spec/lib/af83/cloning/clone_schema_spec.rb @@ -13,8 +13,6 @@ RSpec.describe AF83::SchemaCloner, type: :pg_catalog do end it "table information is correctly duplicated" do - expect_same_sequence_params("#{child_table}_id_seq") - expect_same_sequence_params("#{parent_table}_id_seq") expect(get_table_information(source_schema, child_table)) .to eq([{"table_schema"=>"source_schema", "table_name"=>"children", @@ -42,22 +40,44 @@ RSpec.describe AF83::SchemaCloner, type: :pg_catalog do "commit_action"=>nil}]) end + it "table content is the same and sequences are synchronized" do + expect_same_content(parent_table) + expect_same_content(child_table) + + expect_same_sequence_params("#{parent_table}_id_seq") + expect_same_sequence_params("#{child_table}_id_seq") + end + + it "has correctly updated default values" do + child_table_pk_default = get_columns(target_schema, child_table) + .find{ |col| col["column_name"] == "id" }["column_default"] + expect( child_table_pk_default ).to eq("nextval('#{target_schema}.children_id_seq'::regclass)") + end - xit "has the correct foreign keys" do + it "has the correct foreign keys" do expect( get_foreign_keys(target_schema, child_table) ) .to eq([{ "constraint_name" => "children_parents", "constraint_def" => "FOREIGN KEY (parents_id) REFERENCES target_schema.parents(id)"}]) end - xit "the data has been copied" do - end + xit "it has the correct unique keys UNTESTABLE SO FAR" do + insert source_schema, child_table, "#{parent_table}_id" => 1, some_key: 400 + insert target_schema, child_table, "#{parent_table}_id" => 1, some_key: 400 + reinsert_sql = "INSERT INTO #{source_schema}.#{child_table} (#{parent_table}_id, some_key) VALUES (1, 400)" + expect{ execute(reinsert_sql) rescue nil}.not_to change{ execute("SELECT COUNT(*) FROM #{source_schema}.#{child_table}") } - xit "it has the correct unique keys" - + # expect{ insert(target_schema, child_table, "#{parent_table}_id" => 1, some_key: 400) }.to raise_error(ActiveRecord::RecordNotUnique) end - xit "inserts are independent" do + it "inserts are independent" do + insert source_schema, child_table, "#{parent_table}_id" => 1, some_key: 400 + insert target_schema, child_table, "#{parent_table}_id" => 1, some_key: 400 + last_source = get_content(source_schema, child_table).last + last_target = get_content(target_schema, child_table).last + + expect( last_source ).to eq("id"=>"3", "parents_id"=>"1", "some_key"=>"400", "is_orphan"=>"f") + expect( last_target ).to eq("id"=>"3", "parents_id"=>"1", "some_key"=>"400", "is_orphan"=>"f") end end @@ -77,13 +97,17 @@ RSpec.describe AF83::SchemaCloner, type: :pg_catalog do is_orphan boolean DEFAULT false ); - CREATE UNIQUE INDEX #{source_schema}.#{child_table}_some_key_idx ON #{source_schema}.#{child_table} (some_key); + CREATE UNIQUE INDEX #{child_table}_some_key_idx ON #{source_schema}.#{child_table} (some_key); ALTER TABLE #{source_schema}.#{child_table} ADD CONSTRAINT #{child_table}_#{parent_table} FOREIGN KEY( #{parent_table}_id ) REFERENCES #{source_schema}.#{parent_table}(id); - INSERT INTO #{source_schema}.#{parent_table} VALUES (100); - INSERT INTO #{source_schema}.#{child_table} VALUES (1, 100); + + INSERT INTO #{source_schema}.#{parent_table} VALUES (DEFAULT); + INSERT INTO #{source_schema}.#{parent_table} VALUES (DEFAULT); EOSQL + insert source_schema, child_table, "#{parent_table}_id" => 1, some_key: 200 + insert source_schema, child_table, "#{parent_table}_id" => 2, some_key: 300, is_orphan: true end + end diff --git a/spec/support/bare_sql.rb b/spec/support/bare_sql.rb new file mode 100644 index 000000000..03a50ef77 --- /dev/null +++ b/spec/support/bare_sql.rb @@ -0,0 +1,58 @@ +module Support + module BareSQL + + def insert(schema, table, values) + execute "INSERT INTO #{schema}.#{table} (#{_keys(values)}) VALUES (#{_values values})" + end + + def execute(sql) + base_connection.execute(sql) + end + + def expect_same_content(table_name) + expected_content = get_content(source_schema, table_name) + actual_content = get_content(target_schema, table_name) + expect( actual_content ).to eq(expected_content) + end + + def expect_same_sequence_params(sequence_name) + expected_seq = Hash.without(get_sequences(source_schema, sequence_name).first, 'log_cnt') + actual_seq = Hash.without(get_sequences(target_schema, sequence_name).first, 'log_cnt') + expect( actual_seq ).to eq(expected_seq) + end + + def get_content(schema_name, table_name) + execute("SELECT * FROM #{schema_name}.#{table_name}").to_a + end + + private + + def base_connection + ActiveRecord::Base.connection + end + + def _keys(values) + values.keys.map(&:to_s).join(", ") + end + + def _values(values) + values + .values + .map(&method(:_format)) + .join(', ') + end + + def _format(val) + case val + when String + "'#{val}'" + when TrueClass + "'t'" + when FalseClass + "'f'" + else + val.to_s + end + end + end +end diff --git a/spec/support/hash.rb b/spec/support/hash.rb new file mode 100644 index 000000000..ec9a2f895 --- /dev/null +++ b/spec/support/hash.rb @@ -0,0 +1,6 @@ +class << Hash + def without(hash, *keys) + nk = hash.keys - keys + Hash[*nk.zip(hash.values_at(*nk)).flatten] + end +end diff --git a/spec/support/pg_catalog.rb b/spec/support/pg_catalog.rb index b902acf82..38c75aabe 100644 --- a/spec/support/pg_catalog.rb +++ b/spec/support/pg_catalog.rb @@ -1,17 +1,11 @@ module Support module PGCatalog - # TODO: Check what of the follwowing can be done with ActiveRecord. E.g. - # @connection.foreign_keys(table)... - - def expect_same_sequence_params(sequence_name) - expected_seq = get_sequences(source_schema, sequence_name) - actual_seq = get_sequences(target_schema, sequence_name) - expect( actual_seq ).to eq(expected_seq) - end + include BareSQL def get_columns(schema_name, table_name) - execute("SELECT * from information_schema.columns WHERE table_name = '#{table_name}' AND table_schema = '#{schema_name}'") + execute("SELECT column_name, column_default FROM information_schema.columns WHERE table_name = '#{table_name}' AND table_schema = '#{schema_name}'").to_a end + def get_foreign_keys(schema_oid, table_name) schema_oid = get_schema_oid(schema_oid) unless Integer === schema_oid return [] unless schema_oid @@ -41,13 +35,6 @@ module Support private - def base_connection - ActiveRecord::Base.connection - end - - def execute(sql) - base_connection.execute(sql) - end def foreign_key_query(schema_oid, table_name) <<-EOQ diff --git a/spec/workers/referential_cloning_worker_spec.rb b/spec/workers/referential_cloning_worker_spec.rb index c531e02a9..52ed8913b 100644 --- a/spec/workers/referential_cloning_worker_spec.rb +++ b/spec/workers/referential_cloning_worker_spec.rb @@ -17,12 +17,13 @@ RSpec.describe ReferentialCloningWorker do let( :target_schema ){ "target_schema" } let( :referential_cloning ){ OpenStruct.new(source_referential: make_referential(source_schema), target_referential: make_referential(target_schema)) } + let( :cloner ){ 'cloner' } + before do expect( ReferentialCloning ).to receive(:find).with(id).and_return(referential_cloning) - expect( StoredProcedures ) - .to receive(:invoke_stored_procedure) - .with(:clone_schema, source_schema, target_schema, true) + expect( AF83::SchemaCloner ).to receive(:new).with( source_schema, target_schema ).and_return(cloner) + expect( cloner ).to receive(:clone_schema) expect( referential_cloning ).to receive(:run!) end |