refs 3604 @12h all tests pass

4 files changed, 50 insertions, 185 deletions

diff --git a/lib/af83/schema_cloner.rb b/lib/af83/schema_cloner.rb
index 299e63a82..40c04f400 100644
--- a/lib/af83/schema_cloner.rb
+++ b/lib/af83/schema_cloner.rb
@@ -1,7 +1,7 @@
 module AF83
   class SchemaCloner
 
-    attr_reader :source_schema, :target_schema, :include_records
+    attr_reader :source_schema, :target_schema
 
     def clone_schema
       assure_schema_preconditons
@@ -10,10 +10,26 @@ module AF83
 
     private
 
+    def adjust_default table_name, column_name, default_val
+      changed_default = default_val.sub(%r{\Anextval\('#{source_schema}}, "nextval('#{target_schema}")
+      execute "ALTER TABLE #{target_schema}.#{table_name} ALTER COLUMN #{column_name} SET DEFAULT #{changed_default}"
+    end
+
+    def adjust_defaults table_name
+      pairs = execute <<-EOSQL
+        SELECT column_name, column_default
+          FROM information_schema.columns
+          WHERE table_schema = '#{target_schema}' AND table_name = '#{table_name}' AND
+                column_default LIKE 'nextval(''#{source_schema}%::regclass)'
+      EOSQL
+      pairs.each do | pair |
+        adjust_default table_name, pair['column_name'], pair['column_default']
+      end
+    end
+
     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}
@@ -22,12 +38,11 @@ module AF83
           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}');
+      #{cycle_on_off} CYCLE;
 
-     EOSQL
+        -- TODO: What is this good for?
+        SELECT setval('#{target_schema}.#{sequence_name}', #{seq_props.last_value}, '#{seq_props.is_called}');
+      EOSQL
     end
 
     def assure_schema_preconditons
@@ -37,8 +52,15 @@ module AF83
       raise RuntimeError, "Source Schema #{source_schema} does not exist" unless source
     end
 
+    def clone_foreign_key fk_desc
+      relname, conname, constraint_def = fk_desc.values_at(*%w[relname conname constraint_def])
+      constraint_def = constraint_def.sub(" REFERENCES #{source_schema}.", " REFERENCES #{target_schema}.")
+      execute <<-EOSQL
+      ALTER TABLE #{target_schema}.#{relname} ADD CONSTRAINT #{conname} #{constraint_def}
+      EOSQL
+    end
     def clone_foreign_keys
-      
+      get_foreign_keys.each(&method(:clone_foreign_key))
     end
 
     def clone_sequence sequence_name
@@ -51,8 +73,8 @@ module AF83
 
     def clone_table table_name
       create_table table_name
-
     end
+
     def clone_tables
       table_names.each(&method(:clone_table))
     end
@@ -62,8 +84,8 @@ module AF83
     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}" 
+      execute "INSERT INTO #{target_schema}.#{table_name} SELECT * FROM #{source_schema}.#{table_name}"
+      adjust_defaults table_name
     end
     def create_target_schema
       execute("CREATE SCHEMA #{target_schema}")
@@ -84,10 +106,19 @@ module AF83
       execute(str).flat_map(&:values)
     end
 
-    def initialize(source_schema, target_schema, include_records: true)
+    def get_foreign_keys
+      execute <<-EOS
+        SELECT rn.relname, ct.conname, pg_get_constraintdef(ct.oid) AS constraint_def
+          FROM pg_constraint ct JOIN pg_class rn ON rn.oid = ct.conrelid
+          WHERE connamespace = #{source['oid']} AND rn.relkind = 'r' AND ct.contype = 'f'
+      EOS
+    end
+    def get_columns(table_name)
+    end
+
+    def initialize(source_schema, target_schema)
       @source_schema = source_schema
       @target_schema = target_schema
-      @include_records = include_records
     end
 
     #
@@ -98,19 +129,19 @@ module AF83
     end
 
     def source
-       @__source__ ||= execute("SELECT oid FROM pg_namespace WHERE nspname = '#{source_schema}' LIMIT 1").first;
+      @__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}'"
+      @__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'"
+      @__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/lib/af83/stored_procedures.rb b/lib/af83/stored_procedures.rb
deleted file mode 100644
index b13941a32..000000000
--- a/lib/af83/stored_procedures.rb
+++ /dev/null
@@ -1,44 +0,0 @@
-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
-      "'t'"
-    when FalseClass
-      "'f'"
-    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
deleted file mode 100644
index 9fb88466b..000000000
--- a/lib/sql/clone_schema.sql
+++ /dev/null
@@ -1,114 +0,0 @@
-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/lib/sql/message.sql b/lib/sql/message.sql
deleted file mode 100644
index 94d7a3496..000000000
--- a/lib/sql/message.sql
+++ /dev/null
@@ -1,8 +0,0 @@
-CREATE OR REPLACE FUNCTION message( content text) RETURNS void AS
-$BODY$
-  BEGIN
-    INSERT INTO xxx VALUES (nextval('xxx_id_seq'), 'message from stored procedure: ' || content, current_timestamp);
-    RAISE EXCEPTION 'Oh no';
-    RETURN;
-  END;
-$BODY$ LANGUAGE plpgsql VOLATILE COST 100;