Module: SchemaMoves::Helpers
- Defined in:
- lib/schema_moves.rb
Instance Method Summary collapse
- #appname ⇒ Object
- #ask(*args, &block) ⇒ Object
- #create_new_triggers(new_schema) ⇒ Object
- #create_schema(schema) ⇒ Object
- #create_schemas ⇒ Object
- #drop_old_schemas ⇒ Object
- #drop_old_triggers(old_schema, new_schema) ⇒ Object
- #drop_schema(schema) ⇒ Object
- #get_schema_names(new = true) ⇒ Object
- #migrate_objects ⇒ Object
- #move_objects(from_schema, to_schema, relkind, object_type) ⇒ Object
- #want_to_drop_old_schemas? ⇒ Boolean
Instance Method Details
#appname ⇒ Object
204 205 206 |
# File 'lib/schema_moves.rb', line 204 def appname Rails.application.class.parent_name.underscore end |
#ask(*args, &block) ⇒ Object
155 156 157 |
# File 'lib/schema_moves.rb', line 155 def ask(*args, &block) HighLine.new.ask(*args, &block) end |
#create_new_triggers(new_schema) ⇒ Object
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 |
# File 'lib/schema_moves.rb', line 47 def create_new_triggers(new_schema) connection = ActiveRecord::Base.connection sql = %{ CREATE FUNCTION #{new_schema}.tg_disallow() RETURNS TRIGGER AS $TRIGGER$ BEGIN IF TG_LEVEL <> 'STATEMENT' THEN RAISE EXCEPTION $$You should use a statement-level trigger (trigger %, table %)$$, TG_NAME, TG_RELID::regclass; END IF; RAISE EXCEPTION $$%s are not allowed on table %$$, TG_OP, TG_RELNAME; RETURN NULL; END $TRIGGER$ LANGUAGE plpgsql; CREATE FUNCTION #{new_schema}.template_revision_ordinal() RETURNS TRIGGER AS $TRIGGER$ BEGIN IF NEW.ordinal IS NOT NULL THEN RAISE EXCEPTION $$Must not supply ordinal value manually.$$; END IF; NEW.ordinal = (SELECT COALESCE(MAX(ordinal)+1,1) FROM #{new_schema}.template_revisions WHERE template_id = NEW.template_id); RETURN NEW; END $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER #{new_schema}_template_revisions__bfr_insert BEFORE INSERT ON #{new_schema}.template_revisions FOR EACH ROW EXECUTE PROCEDURE #{new_schema}.template_revision_ordinal(); CREATE TRIGGER #{new_schema}_template_revisions__no_delete BEFORE DELETE ON #{new_schema}.template_revisions FOR EACH STATEMENT EXECUTE PROCEDURE #{new_schema}.tg_disallow(); CREATE TRIGGER #{new_schema}_template_revisions__no_update BEFORE UPDATE OF notes, is_minor, template_id, author_id, created_at, ordinal ON #{new_schema}.template_revisions FOR EACH STATEMENT EXECUTE PROCEDURE #{new_schema}.tg_disallow(); CREATE FUNCTION #{new_schema}.pages_revision_ordinal() RETURNS TRIGGER AS $TRIGGER$ BEGIN IF NEW.ordinal IS NOT NULL THEN RAISE EXCEPTION $$Must not supply ordinal value manually.$$; END IF; NEW.ordinal = (SELECT COALESCE(MAX(ordinal)+1,1) FROM #{new_schema}.page_revisions WHERE page_id = NEW.page_id); RETURN NEW; END $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER #{new_schema}_page_revisions__bfr_insert BEFORE INSERT ON #{new_schema}.page_revisions FOR EACH ROW EXECUTE PROCEDURE #{new_schema}.pages_revision_ordinal(); CREATE TRIGGER #{new_schema}_page_revisions__no_delete BEFORE DELETE ON #{new_schema}.page_revisions FOR EACH STATEMENT EXECUTE PROCEDURE #{new_schema}.tg_disallow(); CREATE TRIGGER #{new_schema}_page_revisions__no_update BEFORE UPDATE OF notes, is_minor, page_id, author_id, created_at, ordinal ON #{new_schema}.page_revisions FOR EACH STATEMENT EXECUTE PROCEDURE #{new_schema}.tg_disallow(); } puts "Creating new triggers..." connection.execute sql end |
#create_schema(schema) ⇒ Object
3 4 5 6 7 8 9 10 11 |
# File 'lib/schema_moves.rb', line 3 def create_schema(schema) connection = ActiveRecord::Base.connection sql = %{ CREATE SCHEMA #{schema}; } puts "Creating #{schema} schema" connection.execute sql end |
#create_schemas ⇒ Object
171 172 173 174 |
# File 'lib/schema_moves.rb', line 171 def create_schemas create_schema @new_landable create_schema @new_traffic end |
#drop_old_schemas ⇒ Object
191 192 193 194 |
# File 'lib/schema_moves.rb', line 191 def drop_old_schemas drop_schema @old_landable drop_schema @old_traffic end |
#drop_old_triggers(old_schema, new_schema) ⇒ Object
136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 |
# File 'lib/schema_moves.rb', line 136 def drop_old_triggers(old_schema, new_schema) connection = ActiveRecord::Base.connection sql = %{ DROP TRIGGER IF EXISTS #{old_schema}_page_revisions__bfr_insert ON #{new_schema}.page_revisions; DROP TRIGGER IF EXISTS #{old_schema}_page_revisions__no_delete ON #{new_schema}.page_revisions; DROP TRIGGER IF EXISTS #{old_schema}_page_revisions__no_update ON #{new_schema}.page_revisions; DROP TRIGGER IF EXISTS #{old_schema}_template_revisions__bfr_insert ON #{new_schema}.template_revisions; DROP TRIGGER IF EXISTS #{old_schema}_template_revisions__no_delete ON #{new_schema}.template_revisions; DROP TRIGGER IF EXISTS #{old_schema}_template_revisions__no_update ON #{new_schema}.template_revisions; DROP FUNCTION IF EXISTS #{old_schema}.pages_revision_ordinal(); DROP FUNCTION IF EXISTS #{old_schema}.template_revision_ordinal(); DROP FUNCTION IF EXISTS #{old_schema}.tg_disallow(); } puts "Dropping old triggers..." connection.execute sql end |
#drop_schema(schema) ⇒ Object
13 14 15 16 17 18 19 20 21 |
# File 'lib/schema_moves.rb', line 13 def drop_schema(schema) connection = ActiveRecord::Base.connection sql = %{ DROP SCHEMA #{schema}; } puts "Dropping #{schema} schema" connection.execute sql end |
#get_schema_names(new = true) ⇒ Object
159 160 161 162 163 164 165 166 167 168 169 |
# File 'lib/schema_moves.rb', line 159 def get_schema_names(new = true) # Always get old schemas @old_landable = ask("Enter the OLD main landable schema: ") { |q| q.default = 'landable' } @old_traffic = ask("Enter the OLD traffic schema: ") { |q| q.default = 'landable_traffic' } # Only ask for new names if new == true if new @new_landable = ask("Enter the NEW main landable schema: ") { |q| q.default = "#{appname}_landable" } @new_traffic = ask("Enter the NEW traffic schema: ") { |q| q.default = "#{appname}_landable_traffic" } end end |
#migrate_objects ⇒ Object
176 177 178 179 180 181 182 183 184 185 186 187 188 189 |
# File 'lib/schema_moves.rb', line 176 def migrate_objects # move_tables move_objects(@old_landable, @new_landable, 'r', 'TABLE') move_objects(@old_traffic, @new_traffic, 'r', 'TABLE') # move_sequences move_objects(@old_landable, @new_landable, 's', 'SEQUENCE') move_objects(@old_traffic, @new_traffic, 's', 'SEQUENCE') # move_views move_objects(@old_landable, @new_landable, 'v', 'TABLE') move_objects(@old_traffic, @new_traffic, 'v', 'TABLE') # move_triggers create_new_triggers(@new_landable) drop_old_triggers(@old_landable, @new_landable) end |
#move_objects(from_schema, to_schema, relkind, object_type) ⇒ Object
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
# File 'lib/schema_moves.rb', line 23 def move_objects(from_schema, to_schema, relkind, object_type) connection = ActiveRecord::Base.connection # move objects from public to new schema objects = connection.select_all(" SELECT o.relname FROM pg_class o JOIN pg_namespace n ON n.oid=o.relnamespace AND n.nspname = '#{from_schema}' AND o.relkind = '#{relkind}' ORDER BY o.relname ") objects.each do |object| sql = %{ ALTER #{object_type} #{from_schema}.#{object['relname']} SET SCHEMA #{to_schema} } puts "Moving #{from_schema}.#{object['relname']} TO #{to_schema}" connection.execute sql end end |
#want_to_drop_old_schemas? ⇒ Boolean
196 197 198 199 200 201 202 |
# File 'lib/schema_moves.rb', line 196 def want_to_drop_old_schemas? drop = nil until ['yes', 'no'].include?(drop.to_s.downcase) drop = ask("Would you like to drop the old schemas? (Yes or No)") { |q| q.default = 'no' } end drop.to_s.downcase == 'yes' end |