diff options
| -rw-r--r-- | app/workers/referential_cloning_worker.rb | 145 | ||||
| -rw-r--r-- | db/migrate/20170123131243_set_user_permissions.rb | 16 | ||||
| -rwxr-xr-x | spec/lib/af83/stored_procedures/clone_schema_spec.rb | 52 | ||||
| -rw-r--r-- | spec/support/pg_catalog.rb | 8 | ||||
| -rw-r--r-- | spec/workers/referential_cloning_worker_spec.rb | 45 |
5 files changed, 120 insertions, 146 deletions
diff --git a/app/workers/referential_cloning_worker.rb b/app/workers/referential_cloning_worker.rb index cfd0dca2d..ef3acd529 100644 --- a/app/workers/referential_cloning_worker.rb +++ b/app/workers/referential_cloning_worker.rb @@ -2,139 +2,32 @@ class ReferentialCloningWorker include Sidekiq::Worker sidekiq_options queue: 'wip' - def self.sql_func - @__sql_func__ ||= <<-EOSQL -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 || ' ;' ; + # Replace default apartment created schema with clone schema from source referential + def perform(id) + ref_cloning = ReferentialCloning.find id - 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; + source_schema = ref_cloning.source_referential.slug + target_schema = "#{source_schema}_tmp" - -- 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; + clone_schema ref_cloning, source_schema, target_schema + end - -- 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; + private - -- 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) || ' ' || 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' - LOOP - EXECUTE qry; - END LOOP; + def clone_schema ref_cloning, source_schema, target_schema + ref_cloning.run! - -- 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; + StoredProcedures.invoke_stored_procedure(:clone_schema, source_schema, target_schema, true) + execute_sql "DROP SCHEMA #{source_schema} CASCADE;" + execute_sql "ALTER SCHEMA #{target_schema} RENAME TO #{source_schema};" - 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; - EOSQL + ref_cloning.successful! + rescue Exception => e + Rails.logger.error "ReferentialCloningWorker : #{e}" + ref_cloning.failed! end - def perform(id) - require 'pry' - binding.pry - # Replace default apartment created schema with clone schema from source referential - ref_cloning = ReferentialCloning.find id - sql_clone = "SELECT clone_schema('#{ref_cloning.source_referential.slug}', '#{ref_cloning.target_referential.slug}_tmp', TRUE);" - sql_drop = "DROP SCHEMA #{ref_cloning.target_referential.slug} CASCADE;" - sql_rename = "ALTER SCHEMA #{ref_cloning.target_referential.slug}_tmp RENAME TO #{ref_cloning.target_referential.slug};" - ref_cloning.run! - - begin - ActiveRecord::Base.connection.execute self.class.sql_func - ActiveRecord::Base.connection.execute sql_clone - ActiveRecord::Base.connection.execute sql_drop - ActiveRecord::Base.connection.execute sql_rename - ref_cloning.successful! - rescue Exception => e - Rails.logger.error "ReferentialCloningWorker : #{e}" - ref_cloning.failed! - end + def execute_sql sql + ActiveRecord::Base.connection.execute sql end end diff --git a/db/migrate/20170123131243_set_user_permissions.rb b/db/migrate/20170123131243_set_user_permissions.rb deleted file mode 100644 index 8b9288073..000000000 --- a/db/migrate/20170123131243_set_user_permissions.rb +++ /dev/null @@ -1,16 +0,0 @@ -class SetUserPermissions < ActiveRecord::Migration - def change - permissions = ['routes.create', 'routes.edit', - 'routes.destroy', 'journey_patterns.create', - 'journey_patterns.edit', 'journey_patterns.destroy', - 'vehicle_journeys.create', 'vehicle_journeys.edit', - 'vehicle_journeys.destroy', 'time_tables.create', - 'time_tables.edit', 'time_tables.destroy', - 'footnotes.edit', 'footnotes.create', - 'footnotes.destroy', 'routing_constraint_zones.create', - 'routing_constraint_zones.edit', 'routing_constraint_zones.destroy'] - # TODO: Make this more idiomatic - User.update_all( permissions: permissions ) - # User.update_all( permissions: "{#{permissions.join(",")}}" ) - end -end diff --git a/spec/lib/af83/stored_procedures/clone_schema_spec.rb b/spec/lib/af83/stored_procedures/clone_schema_spec.rb index 6ec0de5e7..69422e4ef 100755 --- a/spec/lib/af83/stored_procedures/clone_schema_spec.rb +++ b/spec/lib/af83/stored_procedures/clone_schema_spec.rb @@ -14,7 +14,25 @@ RSpec.describe StoredProcedures do end context "meta specs describe source schema's introspection" do - it "shows, sequences are correctly read", :meta do + it "table information is correctly read" do + expect(get_table_information(source_schema, child_table)) + .to eq([{"table_catalog"=>"chouette_test", + "table_schema"=>"source_schema", + "table_name"=>"children", + "table_type"=>"BASE TABLE", + "self_referencing_column_name"=>nil, + "reference_generation"=>nil, + "user_defined_type_catalog"=>nil, + "user_defined_type_schema"=>nil, + "user_defined_type_name"=>nil, + "is_insertable_into"=>"YES", + "is_typed"=>"NO", + "commit_action"=>nil}]) + + expect( get_table_information(target_schema, child_table) ).to be_empty + end + + it "sequences are correctly read", :meta do expect(get_sequences(source_schema, child_table)) .to eq([{"sequence_name"=>"#{child_table}_id_seq", "last_value"=>"1", @@ -63,6 +81,36 @@ RSpec.describe StoredProcedures do expect( get_schema_oid(target_schema) ).not_to be_nil end + it "table information is correctly read" do + expect(get_table_information(source_schema, child_table)) + .to eq([{"table_catalog"=>"chouette_test", + "table_schema"=>"source_schema", + "table_name"=>"children", + "table_type"=>"BASE TABLE", + "self_referencing_column_name"=>nil, + "reference_generation"=>nil, + "user_defined_type_catalog"=>nil, + "user_defined_type_schema"=>nil, + "user_defined_type_name"=>nil, + "is_insertable_into"=>"YES", + "is_typed"=>"NO", + "commit_action"=>nil}]) + + expect( get_table_information(target_schema, child_table)) + .to eq([{"table_catalog"=>"chouette_test", + "table_schema"=>"target_schema", + "table_name"=>"children", + "table_type"=>"BASE TABLE", + "self_referencing_column_name"=>nil, + "reference_generation"=>nil, + "user_defined_type_catalog"=>nil, + "user_defined_type_schema"=>nil, + "user_defined_type_name"=>nil, + "is_insertable_into"=>"YES", + "is_typed"=>"NO", + "commit_action"=>nil}]) + end + it "has the correct sequences" do expect(get_sequences(target_schema, child_table)) .to eq([{"sequence_name"=>"#{child_table}_id_seq", @@ -95,7 +143,7 @@ RSpec.describe StoredProcedures do "constraint_name" => "children_parents", "constraint_def" => "FOREIGN KEY (parents_id) REFERENCES target_schema.parents(id)"}]) end - + end end diff --git a/spec/support/pg_catalog.rb b/spec/support/pg_catalog.rb index 3cd3966c7..dd0219482 100644 --- a/spec/support/pg_catalog.rb +++ b/spec/support/pg_catalog.rb @@ -1,6 +1,9 @@ module Support module PGCatalog + def get_columns(schema_name, table_name) + execute("SELECT * from information_schema.columns WHERE table_name = '#{table_name}' AND table_schema = '#{schema_name}'") + end def get_foreign_keys(schema_oid, table_name) schema_oid = get_schema_oid(schema_oid) unless Integer === schema_oid return [] unless schema_oid @@ -25,8 +28,9 @@ module Support end.flat_map(&:to_a) end - def table_from_schema(schema_name, table_name) - execute + def get_table_information(schema_name, table_name) + execute("SELECT * FROM information_schema.tables WHERE table_name = '#{table_name}' AND table_schema = '#{schema_name}'") + .to_a end diff --git a/spec/workers/referential_cloning_worker_spec.rb b/spec/workers/referential_cloning_worker_spec.rb new file mode 100644 index 000000000..85d771742 --- /dev/null +++ b/spec/workers/referential_cloning_worker_spec.rb @@ -0,0 +1,45 @@ +require 'spec_helper' +require 'ostruct' + +RSpec.describe ReferentialCloningWorker do + + context "given a refererntial cloning" do + + let( :id ){ double } + + let( :worker ){ described_class.new } + + + let( :source_schema ){ "source_schema" } + let( :target_schema ){ "#{source_schema}_tmp" } + let( :referential_cloning ){ OpenStruct.new(source_referential: OpenStruct.new(slug: source_schema)) } + + 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( worker ).to receive(:execute_sql).with( "DROP SCHEMA #{source_schema} CASCADE;" ) + + expect( referential_cloning ).to receive(:run!) + end + + it "invokes the correct stored procedure, updates the database and the AASM" do + expect( worker ).to receive(:execute_sql).with( "ALTER SCHEMA #{target_schema} RENAME TO #{source_schema};" ) + expect( referential_cloning ).to receive(:successful!) + worker.perform(id) + end + + it "handles failure correctly" do + expect( worker ) + .to receive(:execute_sql) + .with( "ALTER SCHEMA #{target_schema} RENAME TO #{source_schema};" ) + .and_raise(RuntimeError) + + expect( referential_cloning ).to receive(:failed!) + worker.perform(id) + end + end + +end |