clone_schema fixed -> lib/sql/clone_schema.sql; lib/af83/stored_procedures.rb for sql stored procedure management; Refs #2864

12 files changed, 392 insertions, 14 deletions

diff --git a/INSTALL.md b/INSTALL.md
index c8dbf97ba..8586803dd 100644
--- a/INSTALL.md
+++ b/INSTALL.md
@@ -55,7 +55,10 @@ When promted for the password enter the highly secure string `chouette`.
 
 #### Create database
 
+      bundle exec rake db:create
       bundle exec rake db:migrate
+
+      RAILS_ENV=test bundle exec rake db:create
       RAILS_ENV=test bundle exec rake db:migrate
 
 #### Install node.js packages
diff --git a/app/workers/referential_cloning_worker.rb b/app/workers/referential_cloning_worker.rb
index dda569d7c..cfd0dca2d 100644
--- a/app/workers/referential_cloning_worker.rb
+++ b/app/workers/referential_cloning_worker.rb
@@ -1,17 +1,133 @@
 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 || ' ;' ;
+
+      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;
+
+    -- 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;
+
+    -- 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;
+    EOSQL
+  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_func    = "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 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; 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) ; 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); 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 ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';' 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 ' || quote_ident(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; FOR object IN SELECT TABLE_NAME::text FROM information_schema.tables WHERE table_schema = quote_ident(source_schema) AND table_type = 'BASE TABLE' LOOP 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; 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; 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; 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;"
     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 sql_func
+      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
diff --git a/config/initializers/active_record.rb b/config/initializers/active_record.rb
index cb4bf65b4..bdf9e0b4b 100644
--- a/config/initializers/active_record.rb
+++ b/config/initializers/active_record.rb
@@ -1,2 +1,5 @@
+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/db/seeds.rb b/db/seeds.rb
index 815327300..cf902e6aa 100644
--- a/db/seeds.rb
+++ b/db/seeds.rb
@@ -55,7 +55,7 @@ workbench = Workbench.find_by(name: "Gestion de l'offre")
 
 [["parissudest201604", "Paris Sud-Est Avril 2016"],
  ["parissudest201605", "Paris Sud-Est Mai 2016"]].each do |slug, name|
-  operator.referentials.find_or_create_by(slug: slug) do |referential|
+  operator.referentials.find_or_create_by!(slug: slug) do |referential|
     referential.name      = name
     referential.prefix    = slug
     referential.workbench = workbench
@@ -63,3 +63,6 @@ workbench = Workbench.find_by(name: "Gestion de l'offre")
     referential.stop_area_referential = stop_area_referential
   end
 end
+
+# Clone last referential
+# Referential.new_from(Referential.last)
diff --git a/lib/af83/stored_procedures.rb b/lib/af83/stored_procedures.rb
new file mode 100644
index 000000000..698f3861d
--- /dev/null
+++ b/lib/af83/stored_procedures.rb
@@ -0,0 +1,44 @@
+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
+      "TRUE"
+    when FalseClass
+      "FALSE"
+    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
new file mode 100644
index 000000000..9fb88466b
--- /dev/null
+++ b/lib/sql/clone_schema.sql
@@ -0,0 +1,114 @@
+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/spec/factories/referential_clonings.rb b/spec/factories/referential_clonings.rb
deleted file mode 100644
index e968d44f1..000000000
--- a/spec/factories/referential_clonings.rb
+++ /dev/null
@@ -1,6 +0,0 @@
-FactoryGirl.define do
-  factory :referential_cloning do
-    association :source_referential, :factory => :referential
-    association :target_referential, :factory => :referential
-  end
-end
diff --git a/spec/lib/af83/stored_procedure_spec.rb b/spec/lib/af83/stored_procedure_spec.rb
new file mode 100644
index 000000000..2530d7fc1
--- /dev/null
+++ b/spec/lib/af83/stored_procedure_spec.rb
@@ -0,0 +1,20 @@
+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
new file mode 100644
index 000000000..646e97d9f
--- /dev/null
+++ b/spec/lib/af83/stored_procedures/clone_schema_spec.rb
@@ -0,0 +1,19 @@
+require 'rails_helper'
+
+RSpec.describe StoredProcedures do
+
+  include Support::PGCatalog
+  context "clone_schema creates correct table in dest schema" do
+    before do
+      drop_schema!(dest_schema_name)
+    end
+
+    it "creates the schema" do
+      expect( get_fks!(dest_schema_name, "access_links") ).to be_empty
+      described_class.invoke_stored_procedure(:clone_schema, source_schema_name, dest_schema_name, true)
+      expect( get_fks!(dest_schema_name, "access_links") )
+      .to eq( [{"constraint_name"=>"aclk_acpt_fkey",
+                "constraint_def"=>"FOREIGN KEY (access_point_id) REFERENCES parissudest201604_v1.access_points(id)"}])
+    end
+  end
+end
diff --git a/spec/spec_helper.rb b/spec/spec_helper.rb
index f32d8a0fc..f66d721c9 100644
--- a/spec/spec_helper.rb
+++ b/spec/spec_helper.rb
@@ -51,7 +51,8 @@ RSpec.configure do |config|
 
   #Capybara.exact = true
   Capybara.javascript_driver = :poltergeist
-  config.filter_run_excluding :js => true
+  config.filter_run_excluding :js  => true
+  config.filter_run           :wip => true      
   config.run_all_when_everything_filtered = true
   config.include TokenInputHelper, :type => :feature
 
diff --git a/spec/support/pg_catalog.rb b/spec/support/pg_catalog.rb
new file mode 100644
index 000000000..629bee9ae
--- /dev/null
+++ b/spec/support/pg_catalog.rb
@@ -0,0 +1,65 @@
+module Support
+  module PGCatalog
+
+    def drop_schema!(schema_name)
+      execute("DROP SCHEMA #{schema_name} CASCADE")
+    rescue
+      nil
+    end
+
+    def get_foreign_keys!(schema_oid, table_name)
+      schema_oid = get_schema_oid!(schema_oid) unless Integer === schema_oid
+      return [] unless schema_oid
+      execute(foreign_key_query(schema_oid, table_name))
+        .to_a
+    end
+    def get_schema_oid!(schema_name)
+      execute("SELECT oid FROM pg_namespace WHERE nspname = '#{schema_name}'")
+        .values
+        .flatten
+        .first
+    end
+
+
+    def table_from_schema(schema_name, table_name)
+      execute
+    end
+
+
+    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 }
+    end
+
+    def queries
+       @__queries__ ||= {}
+    end
+  end
+end
diff --git a/spec/workers/referential_cloning_worker_spec.rb b/spec/workers/referential_cloning_worker_spec.rb
deleted file mode 100644
index dd7b33f23..000000000
--- a/spec/workers/referential_cloning_worker_spec.rb
+++ /dev/null
@@ -1,4 +0,0 @@
-require 'rails_helper'
-RSpec.describe ReferentialCloningWorker, type: :worker do
-    pending "add some examples to (or delete) #{__FILE__}"
-end