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

3 files changed, 147 insertions, 158 deletions

diff --git a/lib/af83/schema_cloner.rb b/lib/af83/schema_cloner.rb
new file mode 100644
index 000000000..40c04f400
--- /dev/null
+++ b/lib/af83/schema_cloner.rb
@@ -0,0 +1,147 @@
+module AF83
+  class SchemaCloner
+
+    attr_reader :source_schema, :target_schema
+
+    def clone_schema
+      assure_schema_preconditons
+      create_target_schema
+    end
+
+    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'
+      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;
+
+        -- 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
+      raise RuntimeError, "Target Schema #{target_schema} does already exist" unless
+      execute("SELECT oid FROM pg_namespace WHERE nspname = '#{target_schema}' LIMIT 1").empty?
+
+      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
+      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)"
+      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}")
+      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 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
+    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/lib/af83/stored_procedures.rb b/lib/af83/stored_procedures.rb
deleted file mode 100644
index 698f3861d..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
-      "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
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;