removed obsolete migration 20170123131243_set_user_permissions.rb; Refs #3105

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