aboutsummaryrefslogtreecommitdiffstats
path: root/lib
diff options
context:
space:
mode:
Diffstat (limited to 'lib')
-rw-r--r--lib/af83/schema_cloner.rb147
-rw-r--r--lib/af83/stored_procedures.rb44
-rw-r--r--lib/sql/clone_schema.sql114
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;
generated by cgit v1.2.3 (git 2.25.1) at 2026-02-25 14:24:41 +0000