Refs: #3604 @2h checking sequences as defaults

5 files changed, 152 insertions, 358 deletions

diff --git a/lib/af83/schema_cloner.rb b/lib/af83/schema_cloner.rb
index 933fffc3d..299e63a82 100644
--- a/lib/af83/schema_cloner.rb
+++ b/lib/af83/schema_cloner.rb
@@ -2,15 +2,34 @@ module AF83
   class SchemaCloner
 
     attr_reader :source_schema, :target_schema, :include_records
-    def clone_schema(source_schema, target_schema, include_records: true)
-      @source_schema = source_schema
-      @target_schema = target_schema
-      @include_records = include_records
 
-      clone_schema_
+    def clone_schema
+      assure_schema_preconditons
+      create_target_schema
     end
 
     private
+
+    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}
+          MINVALUE #{seq_props.min_value}
+          MAXVALUE #{seq_props.max_value}
+          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}');
+
+     EOSQL
+    end
+
     def assure_schema_preconditons
       raise RuntimeError, "Target Schema #{target_schema} does already exist" unless
       execute("SELECT oid FROM pg_namespace WHERE nspname = '#{target_schema}' LIMIT 1").empty?
@@ -18,21 +37,80 @@ module AF83
       raise RuntimeError, "Source Schema #{source_schema} does not exist" unless source
     end
 
-    def clone_schema_
-      assure_schema_preconditons
+    def clone_foreign_keys
+      
     end
-    def connection
-      @__connection__ ||= ActiveRecord::Base.connection
+
+    def clone_sequence sequence_name
+      create_sequence sequence_name
+      alter_sequence sequence_name
+    end
+    def clone_sequences
+      source_sequence_names.each(&method(:clone_sequence))
+    end
+
+    def clone_table table_name
+      create_table table_name
+
+    end
+    def clone_tables
+      table_names.each(&method(:clone_table))
+    end
+
+    def create_sequence sequence_name
+      execute "CREATE SEQUENCE #{target_schema}.#{sequence_name}"
+    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}" 
+    end
+    def create_target_schema
+      execute("CREATE SCHEMA #{target_schema}")
+      clone_sequences
+      clone_tables
+      clone_foreign_keys
     end
     def execute(str)
       connection.execute(str).to_a
     end
+    def execute_get_first(str)
+      execute(str).first
+    end
+    def execute_get_ostruct(str)
+      OpenStruct.new(execute_get_first(str))
+    end
+    def execute_get_values(str)
+      execute(str).flat_map(&:values)
+    end
+
+    def initialize(source_schema, target_schema, include_records: true)
+      @source_schema = source_schema
+      @target_schema = target_schema
+      @include_records = include_records
+    end
+
+    #
+    #  Memvars
+    #  -------
+    def connection
+      @__connection__ ||= ActiveRecord::Base.connection
+    end
 
     def source
        @__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}'"
+    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'"
+    end
   end
 end
diff --git a/spec/lib/af83/cloning/clone_schema_spec.rb b/spec/lib/af83/cloning/clone_schema_spec.rb
index aa74bb372..5e441cc8e 100644
--- a/spec/lib/af83/cloning/clone_schema_spec.rb
+++ b/spec/lib/af83/cloning/clone_schema_spec.rb
@@ -1,135 +1,69 @@
-include Support::PGCatalog
-
-RSpec.describe AF83::SchemaCloner do
+RSpec.describe AF83::SchemaCloner, type: :pg_catalog do
   let( :source_schema ){ "source_schema" }
   let( :target_schema ){ "target_schema" }
   let( :child_table ){ "children" }
   let( :parent_table ){ "parents" }
 
-  subject { described_class.new }
-
-  before do
-    create_schema_with_tables
-  end
+  subject { described_class.new source_schema, target_schema }
 
-  context "before cloning" do
-    it "target schema does not exist" do
-      expect( get_schema_oid(target_schema) ).to be_nil
+  context "after cloning" do
+    before do
+      create_schema_with_tables
+      subject.clone_schema
     end
-  end
-
-  shared_examples_for "after cloning schema" do
 
-    let( :expected_target_parent_count ){ include_recs ? 1 : 0 }
-    let( :expected_target_child_count ){ include_recs ? 1 : 0 }
-
-    it "table information is correctly read" do
+    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",
-                 "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}])
+      "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_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}])
+      "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",
-               "last_value"=>"1",
-               "start_value"=>"1",
-               "increment_by"=>"1",
-               "max_value"=>"9223372036854775807",
-               "min_value"=>"1",
-               "cache_value"=>"1",
-               "log_cnt"=>"0",
-               "is_cycled"=>"f",
-               "is_called"=>"f"}])
 
-      expect(get_sequences(target_schema, parent_table))
-      .to eq([{"sequence_name"=>"#{parent_table}_id_seq",
-               "last_value"=>"1",
-               "start_value"=>"1",
-               "increment_by"=>"1",
-               "max_value"=>"9223372036854775807",
-               "min_value"=>"1",
-               "cache_value"=>"1",
-               "log_cnt"=>"0",
-               "is_cycled"=>"f",
-               "is_called"=>"f"}])
-    end
 
-    it "has the correct foreign keys" do
+    xit "has the correct foreign keys" do
       expect( get_foreign_keys(target_schema, child_table) )
-      .to eq([{
+        .to eq([{
         "constraint_name" => "children_parents",
         "constraint_def"  => "FOREIGN KEY (parents_id) REFERENCES target_schema.parents(id)"}])
     end
 
-    it "the data has been copied or not" do
-      source_pt_count = count_records(source_schema, parent_table)
-      source_ch_count = count_records(source_schema, child_table)
-      target_pt_count = count_records(target_schema, parent_table)
-      target_ch_count = count_records(target_schema, child_table)
-
-      expect( source_pt_count ).to eq( 1 )
-      expect( source_ch_count ).to eq( 1 )
-      expect( target_pt_count ).to eq( expected_target_parent_count )
-      expect( target_ch_count ).to eq( expected_target_child_count )
+    xit "the data has been copied" do
     end
-  end
 
-  context "step by step", :wip do
-    # before do
-    #   subject.clone_schema(source_schema, target_schema)
-    # end
-    it "assure target schema nonexistance" do
-      expect{ subject.clone_schema(source_schema, source_schema) }.to raise_error(RuntimeError)
-    end
-    it "assure source schema's existance" do
-      expect{ subject.clone_schema(target_schema, target_schema) }.to raise_error(RuntimeError)
-    end
-
-  end
-
-  context "after cloning" do
-    before do
-      subject.clone_schema(source_schema, target_schema, include_recs: include_recs)
+    xit "it has the correct unique keys"
+      
     end
 
-    context "without including records" do
-      let( :include_recs ){ false }
-      it_behaves_like 'after cloning schema'
+    xit "inserts are independent" do
     end
 
-    context "with including records" do
-      let( :include_recs ){ true }
-      it_behaves_like 'after cloning schema'
-    end
   end
 
-end
-
-def create_schema_with_tables
-  execute <<-EOSQL
+  def create_schema_with_tables
+    execute <<-EOSQL
     DROP SCHEMA IF EXISTS #{source_schema} CASCADE;
     CREATE SCHEMA #{source_schema};
 
@@ -138,13 +72,18 @@ def create_schema_with_tables
     );
     CREATE TABLE #{source_schema}.#{child_table} (
       id bigserial PRIMARY KEY,
-    #{parent_table}_id bigint
+    #{parent_table}_id bigint,
+     some_key bigint NOT NULL,
+      is_orphan boolean DEFAULT false
     );
+
+    CREATE UNIQUE INDEX #{source_schema}.#{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);
-  EOSQL
+    EOSQL
+  end
 end
-
diff --git a/spec/lib/af83/stored_procedure_spec.rb b/spec/lib/af83/stored_procedure_spec.rb
deleted file mode 100644
index 2530d7fc1..000000000
--- a/spec/lib/af83/stored_procedure_spec.rb
+++ /dev/null
@@ -1,20 +0,0 @@
-require 'rails_helper'
-
-RSpec.describe StoredProcedures do
-
-
-  before do
-    described_class.create_stored_procedure(:clone_schema)
-  end
-
-  let( :source_schema_name ){ "parissudest201604" }
-  let( :dest_schema_name ){ "#{source_schema_name}_v1"}
-
-  context "Error cases"  do
-    it "raises an error if stored procedure does not exist" do
-      expect{ described_class.invoke_stored_procedure(:idonotexist) }
-      .to raise_error(ArgumentError, %r{no such stored procedure "idonotexist"})
-    end
-  end
-
-end
diff --git a/spec/lib/af83/stored_procedures/clone_schema_spec.rb b/spec/lib/af83/stored_procedures/clone_schema_spec.rb
deleted file mode 100644
index bdc3bd6cc..000000000
--- a/spec/lib/af83/stored_procedures/clone_schema_spec.rb
+++ /dev/null
@@ -1,193 +0,0 @@
-require 'spec_helper'
-
-include Support::PGCatalog
-
-
-RSpec.describe StoredProcedures do
-  let( :source_schema ){ "source_schema" }
-  let( :target_schema ){ "target_schema" }
-  let( :child_table ){ "children" }
-  let( :parent_table ){ "parents" }
-
-  before do
-    create_schema_with_tables
-    StoredProcedures.create_stored_procedure :clone_schema
-  end
-
-  # :meta specs are not run, as the describe the testing methd and not the application
-  context "meta specs describe source schema's introspection", :meta do
-    it "table information is correctly read" do
-      expect(get_table_information(source_schema, child_table))
-        .to eq([{"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" do
-      expect(get_sequences(source_schema, child_table))
-      .to eq([{"sequence_name"=>"#{child_table}_id_seq",
-               "last_value"=>"1",
-               "start_value"=>"1",
-               "increment_by"=>"1",
-               "max_value"=>"9223372036854775807",
-               "min_value"=>"1",
-               "cache_value"=>"1",
-               "log_cnt"=>"0",
-               "is_cycled"=>"f",
-               "is_called"=>"f"}])
-
-      expect(get_sequences(source_schema, parent_table))
-      .to eq([{"sequence_name"=>"#{parent_table}_id_seq",
-               "last_value"=>"1",
-               "start_value"=>"1",
-               "increment_by"=>"1",
-               "max_value"=>"9223372036854775807",
-               "min_value"=>"1",
-               "cache_value"=>"1",
-               "log_cnt"=>"0",
-               "is_cycled"=>"f",
-               "is_called"=>"f"}])
-    end
-
-    it "shows foreign key constraints are correctly read" do
-      expect( get_foreign_keys(source_schema, child_table) )
-      .to eq([{
-        "constraint_name" => "children_parents",
-        "constraint_def"  => "FOREIGN KEY (parents_id) REFERENCES source_schema.parents(id)"}])
-    end
-  end
-
-  context "before cloning" do
-    it "target schema does not exist" do
-      expect( get_schema_oid(target_schema) ).to be_nil
-    end
-  end
-
-  shared_examples_for "after cloning schema" do
-
-    let( :expected_target_parent_count ){ include_recs ? 1 : 0 }
-    let( :expected_target_child_count ){ include_recs ? 1 : 0 }
-
-    it "table information is correctly read" do
-      expect(get_table_information(source_schema, child_table))
-        .to eq([{"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_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",
-               "last_value"=>"1",
-               "start_value"=>"1",
-               "increment_by"=>"1",
-               "max_value"=>"9223372036854775807",
-               "min_value"=>"1",
-               "cache_value"=>"1",
-               "log_cnt"=>"0",
-               "is_cycled"=>"f",
-               "is_called"=>"f"}])
-
-      expect(get_sequences(target_schema, parent_table))
-      .to eq([{"sequence_name"=>"#{parent_table}_id_seq",
-               "last_value"=>"1",
-               "start_value"=>"1",
-               "increment_by"=>"1",
-               "max_value"=>"9223372036854775807",
-               "min_value"=>"1",
-               "cache_value"=>"1",
-               "log_cnt"=>"0",
-               "is_cycled"=>"f",
-               "is_called"=>"f"}])
-    end
-
-    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
-
-    it "the data has been copied or not" do
-      source_pt_count = count_records(source_schema, parent_table)
-      source_ch_count = count_records(source_schema, child_table)
-      target_pt_count = count_records(target_schema, parent_table)
-      target_ch_count = count_records(target_schema, child_table)
-
-      expect( source_pt_count ).to eq( 1 )
-      expect( source_ch_count ).to eq( 1 )
-      expect( target_pt_count ).to eq( expected_target_parent_count )
-      expect( target_ch_count ).to eq( expected_target_child_count )
-    end
-  end
-
-  context "after cloning" do
-    before do
-      described_class.invoke_stored_procedure(:clone_schema, source_schema, target_schema, include_recs)
-    end
-
-    context "without including records" do
-      let( :include_recs ){ false }
-      it_behaves_like 'after cloning schema'
-    end
-
-    context "with including records" do
-      let( :include_recs ){ true }
-      it_behaves_like 'after cloning schema'
-    end
-
-  end
-
-end
-
-def create_schema_with_tables
-  execute <<-EOSQL
-    DROP SCHEMA IF EXISTS #{source_schema} CASCADE;
-    CREATE SCHEMA #{source_schema};
-
-    CREATE TABLE #{source_schema}.#{parent_table} (
-      id bigserial PRIMARY KEY
-    );
-    CREATE TABLE #{source_schema}.#{child_table} (
-      id bigserial PRIMARY KEY,
-    #{parent_table}_id bigint
-    );
-    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);
-  EOSQL
-end
-
diff --git a/spec/support/pg_catalog.rb b/spec/support/pg_catalog.rb
index 7f86ee582..b902acf82 100644
--- a/spec/support/pg_catalog.rb
+++ b/spec/support/pg_catalog.rb
@@ -3,9 +3,10 @@ module Support
     # TODO: Check what of the follwowing can be done with ActiveRecord. E.g.
     # @connection.foreign_keys(table)...
 
-    def count_records(schema_name, table_name)
-      result = execute("SELECT COUNT(*) AS count FROM #{schema_name}.#{table_name}")
-      return result.to_a.first["count"].to_i
+    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
 
     def get_columns(schema_name, table_name)
@@ -25,11 +26,8 @@ module Support
         .first
     end
 
-    def get_sequences(schema_name, table_name)
-      sequences = execute <<-EOSQL
-        SELECT sequence_name FROM information_schema.sequences
-          WHERE sequence_schema = '#{schema_name}' AND sequence_name LIKE '#{table_name}%'
-      EOSQL
+    def get_sequences(schema_name, sequence_name)
+      sequences = execute(sequence_query(schema_name, sequence_name))
       sequences.values.flatten.map do | sequence |
         execute "SELECT * from #{schema_name}.#{sequence}"
       end.flat_map(&:to_a)
@@ -52,30 +50,18 @@ module Support
     end
 
     def foreign_key_query(schema_oid, table_name)
-      key = [:foreign_key_query, schema_oid, table_name]
-      get_or_create_query(key){ <<-EOQ
-      SELECT ct.conname AS constraint_name, pg_get_constraintdef(ct.oid) AS constraint_def
-      FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid
-      WHERE connamespace = #{schema_oid} AND rn.relname = '#{table_name}' AND rn.relkind = 'r' AND ct.contype = 'f'
-        EOQ
-      }
-    end
-
-    def sequence_properties_query(schema_name, sequence_name)
-      key = [:sequence_properies_query, schema_name, sequence_name]
-      get_or_create_query(key){ <<-EOQ
-                                Coming Soon
-        EOQ
-      }
-      
-    end
-
-    def get_or_create_query(query_key, &query_value)
-      queries.fetch(query_key){ queries[query_key] = query_value.() }
+      <<-EOQ
+        SELECT ct.conname AS constraint_name, pg_get_constraintdef(ct.oid) AS constraint_def
+          FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid
+          WHERE connamespace = #{schema_oid} AND rn.relname = '#{table_name}' AND rn.relkind = 'r' AND ct.contype = 'f'
+      EOQ
     end
 
-    def queries
-       @__queries__ ||= {}
+    def sequence_query(schema_name, sequence_name)
+      <<-EOQ
+        SELECT sequence_name FROM information_schema.sequences
+          WHERE sequence_schema = '#{schema_name}' AND sequence_name = '#{sequence_name}'
+      EOQ
     end
 
     def without_keys(*keys)
@@ -87,3 +73,7 @@ module Support
     end
   end
 end
+
+RSpec.configure do | conf |
+  conf.include Support::PGCatalog, type: :pg_catalog
+end