Merge remote-tracking branch 'origin/master' into 3479-refactor-table_builder-helper

8 files changed, 288 insertions, 243 deletions

diff --git a/spec/lib/af83/cloning/clone_schema_spec.rb b/spec/lib/af83/cloning/clone_schema_spec.rb
new file mode 100644
index 000000000..3d541f3e9
--- /dev/null
+++ b/spec/lib/af83/cloning/clone_schema_spec.rb
@@ -0,0 +1,113 @@
+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 source_schema, target_schema }
+
+  context "after cloning" do
+    before do
+      create_schema_with_tables
+      subject.clone_schema
+    end
+
+    it "table information is correctly duplicated" 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 "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
+
+    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 "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}") } 
+
+      # expect{  insert(target_schema, child_table, "#{parent_table}_id" => 1, some_key: 400) }.to raise_error(ActiveRecord::RecordNotUnique)
+    end
+
+    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
+
+  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,
+     some_key bigint NOT NULL,
+      is_orphan boolean DEFAULT false
+    );
+
+    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 (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/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 c387ddc7d..000000000
--- a/spec/lib/af83/stored_procedures/clone_schema_spec.rb
+++ /dev/null
@@ -1,167 +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
-
-  context "after cloning" do
-    before do
-      described_class.invoke_stored_procedure(:clone_schema, source_schema, target_schema, false)
-    end
-
-    it "target schema does exist" 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_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
-
-  end
-
-end
-
-def create_schema_with_tables
-  execute("CREATE SCHEMA IF NOT EXISTS #{source_schema}")
-  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);
-    EOSQL
-end
-
diff --git a/spec/models/clean_up_spec.rb b/spec/models/clean_up_spec.rb
index d6aa7652c..b4cf5e1af 100644
--- a/spec/models/clean_up_spec.rb
+++ b/spec/models/clean_up_spec.rb
@@ -6,6 +6,86 @@ RSpec.describe CleanUp, :type => :model do
   it { should validate_presence_of(:date_type) }
   it { should belong_to(:referential) }
 
+  context '#exclude_dates_in_overlapping_period with :before date_type' do
+    let(:time_table) { create(:time_table) }
+    let(:period) { time_table.periods[0] }
+    let(:cleaner) { create(:clean_up, date_type: :before) }
+
+    it 'should add exclude date into period for overlapping period' do
+      days_in_period = (period.period_start..period.period_end).count
+      cleaner.begin_date = period.period_end
+
+      expect { cleaner.exclude_dates_in_overlapping_period(period) }.to change {
+        time_table.dates.where(in_out: false).count
+      }.by(days_in_period - 1)
+    end
+
+    it 'should not add exclude date if no overlapping found' do
+      cleaner.begin_date = period.period_start
+      expect { cleaner.exclude_dates_in_overlapping_period(period) }.to_not change {
+        time_table.dates.where(in_out: false).count
+      }
+    end
+  end
+
+  context '#exclude_dates_in_overlapping_period with :after date_type' do
+    let(:time_table) { create(:time_table) }
+    let(:period) { time_table.periods[0] }
+    let(:cleaner) { create(:clean_up, date_type: :after) }
+
+    it 'should add exclude date into period for overlapping period' do
+      days_in_period = (period.period_start..period.period_end).count
+      cleaner.begin_date = period.period_start + 1.day
+      expect { cleaner.exclude_dates_in_overlapping_period(period) }.to change {
+        time_table.dates.where(in_out: false).count
+      }.by(days_in_period - 2)
+    end
+
+    it 'should not add exclude date if no overlapping found' do
+      cleaner.begin_date = period.period_end
+      expect { cleaner.exclude_dates_in_overlapping_period(period) }.to_not change {
+        time_table.dates.where(in_out: false).count
+      }
+    end
+  end
+
+   context '#exclude_dates_in_overlapping_period with :between date_type' do
+    let(:time_table) { create(:time_table) }
+    let(:period) { time_table.periods[0] }
+    let(:cleaner) { create(:clean_up, date_type: :between, begin_date: period.period_start + 3.day, end_date: period.period_end) }
+
+    it 'should add exclude date into period for overlapping period' do
+      expected_day_out = (cleaner.begin_date..cleaner.end_date).count
+      expect { cleaner.exclude_dates_in_overlapping_period(period) }.to change {
+        time_table.dates.where(in_out: false).count
+      }.by(expected_day_out)
+    end
+
+    it 'should not add exclude date if no overlapping found' do
+      cleaner.begin_date = period.period_end  + 1.day
+      cleaner.end_date   = cleaner.begin_date + 1.day
+
+      expect { cleaner.exclude_dates_in_overlapping_period(period) }.to_not change {
+        time_table.dates.where(in_out: false).count
+      }
+    end
+  end
+
+  context '#overlapping_periods' do
+    let(:cleaner) { create(:clean_up, date_type: :before, end_date: nil) }
+    let(:time_table) { create(:time_table) }
+
+    it 'should detect overlapping periods' do
+      cleaner.begin_date = time_table.periods[0].period_start
+      expect(cleaner.overlapping_periods).to include(time_table.periods[0])
+    end
+
+    it 'should not return none overlapping periods' do
+      cleaner.begin_date = time_table.periods[0].period_start - 1.day
+      expect(cleaner.overlapping_periods).to_not include(time_table.periods[0])
+    end
+  end
+
   context '#clean' do
     let(:cleaner) { create(:clean_up, date_type: :before) }
 
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 bb61adba5..ca02f2550 100644
--- a/spec/support/pg_catalog.rb
+++ b/spec/support/pg_catalog.rb
@@ -1,11 +1,12 @@
+require_relative 'bare_sql'
 module Support
   module PGCatalog
-    # TODO: Check what of the follwowing can be done with ActiveRecord. E.g.
-    # @connection.foreign_keys(table)...
+    include Support::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
@@ -20,11 +21,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)
@@ -38,39 +36,20 @@ 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)
-      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)
@@ -82,3 +61,7 @@ module Support
     end
   end
 end
+
+RSpec.configure do | conf |
+  conf.include Support::PGCatalog, type: :pg_catalog
+end
diff --git a/spec/workers/referential_cloning_worker_spec.rb b/spec/workers/referential_cloning_worker_spec.rb
index 85d771742..52ed8913b 100644
--- a/spec/workers/referential_cloning_worker_spec.rb
+++ b/spec/workers/referential_cloning_worker_spec.rb
@@ -9,37 +9,29 @@ RSpec.describe ReferentialCloningWorker do
 
     let( :worker ){ described_class.new }
 
+    def make_referential(schema_name)
+      return OpenStruct.new( slug: schema_name )
+    end
 
     let( :source_schema ){ "source_schema" }
-    let( :target_schema ){ "#{source_schema}_tmp" }
-    let( :referential_cloning ){ OpenStruct.new(source_referential: OpenStruct.new(slug: source_schema)) } 
+    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( worker ).to receive(:execute_sql).with( "DROP SCHEMA #{source_schema} CASCADE;" )
+      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
 
     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