class SqlSkelton
class SqlSkelton
¶ ↑
Summary¶ ↑
Class for mapping between the original and modified SQL tables/columns
Description¶ ↑
Note¶ ↑
“constraint names” are preserved, as they are guaranteed to be unique. Index made by CREATE INDEX is not taken into account; it may take a form like `TABLE_COLUMN_index`.
-- Name: password_resets_email_index; Type: INDEX; Schema: public; Owner: seller CREATE INDEX password_resets_email_index ON password_resets USING btree (email);
Author: M. Sakano (Wise Babel Ltd)
Constants
- DefMappingCsvname
Default mapping CSV file between the client table/column names and their couterparts in the server.
- DefMappingSql
Default parameters for SQL script for mapping between the client table names and their couterparts (Table and column names)
This defines with the contents
1. the table name (key :tblname4tbl => "db_tbl_mappings" (Def)) that contains the mapping information for the table names for old and new data, as well as the column names in it, and 2. the table name (key :tblname4col => "db_col_mappings" (Def)) that contains the mapping information for the column names, where the former table is referred to with a foreign-key column (Def: "db_tbl_mapping_id").
The prefixes for the column names (:colprefix4tbl => “tbl_” and :colprefix4col => “col_”) and the suffixes for them (:colsuffix4from and :colsuffix4to) are also defined.
Default parameters for SQL script for mapping between the client table names and their couterparts (Table and column names)
- MatIndices
- MaxColnameBytes
Maximum number of bytes allowed for a column name in PostgreSQL
- RexSqls
Attributes
ColIndex
instance for mapping database column names
Constraint-Index instance for listing the constraint name
Output Mapping CSV file
Output Mapping SQL information (Hash-keys: :filename, :tblname, :sqlname)
Output SQL file
TblIndex
instance for mapping database table names
Public Class Methods
Returns the column name to referencing as a foreign key the primary key of the given table.
Following the Rails convention. Requires ActiveSupport of Rails.
@param tblname [String] @return [String]
# File lib/db_suit_rails/sql_skelton.rb, line 1102 def self.colname4id(tblname) tblname.singularize + '_id' end
Set up the basic parameters.
For mappingsql Hash, the following is the specification:
{ :filename => String(Filename to output), :tblname => { :tbl => String(Table-name for table-name mapping), # the Foreign-key points to this. :col => String(Table-name for column-name mapping) }, :colname => { :tbl => { :from => String(Column-name for the table-names of from-database), :to => String(Column-name for the table-names of to-database), }, :col => { :from => String(Column-name for the column-names of from-database), :to => String(Column-name for the column-names of to-database), }, } }
@param infile [String, IO] Original SQL @param outfile [String, IO] Output filename (Def: INFILE_ROOT_ror.sql / out.sql) @param mappingcsv [String] Output mapping-CSV file (Def: mapping.csv) @param mappingsql [Hash] Output mapping-SQL file info (keys: :filename, :tblname[:tbl, :col], :colname[:tbl|:col => :from|:to])
# File lib/db_suit_rails/sql_skelton.rb, line 128 def initialize(infile, outfile=nil, mappingcsv: DefMappingCsvname, mappingsql: {}) @outfile = outfile if defined?(infile.read) @strall = infile.read @outfile ||= '_rails_db.sql' else @strall = File.read(infile) @outfile ||= File.basename(infile, '.sql') + '_rails_db.sql' end @mappingcsv = mappingcsv @mappingsql = mk_tmpl_mappingsql().merge(mappingsql) @tbl_index = TblIndex.new @col_index = ColIndex.new @csrt_index = {} # { 'PRIMARY' => {'old_table' => []}, 'UNIQUE' => {} } end
Public Instance Methods
Add primary key
Returns a String to setup a new primary key 'id'
@param oldtbl [String] old table name @param newcol [String] new column name to add @param newtype [String] type for newcol @return [Array<SqlSkelton::Fkey, String, NilClass>] [Array|NilClass, updated_string]
# File lib/db_suit_rails/sql_skelton.rb, line 302 def add_primary_key(oldtbl, newcol='id', newtype='bigint') retstr = "ALTER TABLE #{oldtbl} ADD COLUMN #{newcol} #{newtype};\n" # retstr << "ALTER TABLE #{oldtbl} ADD PRIMARY KEY #{newcol};\n" retstr << "ALTER TABLE ONLY #{oldtbl} ADD CONSTRAINT #{@csrt_index['PRIMARY'][oldtbl]} PRIMARY KEY (#{newcol});\n" return retstr end
Handle a table and many columns, returns the updated string.
@param oldtbl [String] old table name @param oldcolsstr [String] comma-separated old column names @param retall [Boolean] if true, returns an [String, ArrayNew, ArrayOld] (Def: F, and String only) @return [String] revised string
# File lib/db_suit_rails/sql_skelton.rb, line 437 def convert_multi_cols(oldtbl, oldcolsstr, retall: false) oldcolsstr = oldcolsstr.sub(/^(\s*)/, '') str_pre = $1 oldcolsstr.sub!(/(\s*)$/, '') str_post = $1 aroldcol = oldcolsstr.split(/[\s,]+/) arnewcol = aroldcol.map{ |i| @col_index.updated_col!(oldtbl, i) } retstr = str_pre + arnewcol.join(', ') + str_post retall ? (return [retstr, arnewcol, aroldcol]) : (return retstr) end
Search for the end of a comment in a given string
Returns a two-element Array. If the-end-of-comment is not found, the 2nd element is nil, and 1st element is equal to instr.
@param instr [String] String to examine @return [Array<String>] [Comment, Rest|nil]
# File lib/db_suit_rails/sql_skelton.rb, line 488 def get_end_comment(instr) if /(\*\/)/ !~ instr return [instr, nil] end return [$`+$1, $'] end
Read column names
Returns a 2-component array. The 1st element can be nil if no REFERENCES is found, and the 2nd element is the updated (or not-updated) text.
-
To check out “FOREIGN KEY” statement, the second parameter of oldtbl (for the table name of the current table) must be given. In that case, the 1st element of the returned array is an array of {SqlSkelton::Fkey}.
-
To check out inline “REFERENCES”, the third parameter is mandatory.
@param strin [String] String to evaluate @param oldtbl [String] old table name @param oldcol [String] old column name @return [Array<SqlSkelton::Fkey, String, NilClass>] [Array|NilClass, updated_string]
# File lib/db_suit_rails/sql_skelton.rb, line 325 def get_foreign_keys(strin, oldtbl, oldcol=nil) case strin when /(FOREIGN\s+KEY\s*\(\s*)([\w\s,]+)(\)\s*REFERENCES\s+)([\w.]+)(\s*\(\s*)([\w\s,]+)(\))/i ## FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) oldtbl || (raise "ERROR: oldtbl must be given for checking FOREIGN KEY: strin= #{strin}") oldtbl_prt = $4 retstr = $` + $1 strnewcol, _, aroldcol = convert_multi_cols(oldtbl, $2, retall: true) retstr << strnewcol << $3 strnewcol_prt, _, aroldcol_prt = convert_multi_cols(oldtbl_prt, $6, retall: true) # "_prt" for "Parent" fkeys = [] aroldcol.each_with_index do |e_oldcol, i| fkeys.push((self.class)::Fkey.new(oldtbl, e_oldcol, oldtbl_prt, aroldcol_prt[i])) @col_index.update!(oldtbl, e_oldcol, fkey: fkeys[-1]) end retstr << @tbl_index.updated_tbl!(oldtbl_prt) << $5 << strnewcol_prt << $7 << $' return [fkeys, retstr] when /\b(REFERENCES\s+)([\w.]+)((\s*\(\s*)(\w+)(\s*\)))?(\s*,\s*)?(--.*)?$/i ## REFERENCES products (product_no), oldtbl_prt = $2 newtbl_prt = @tbl_index.updated_tbl!(oldtbl_prt) retstr = $` + $1 + newtbl_prt if $3 fkey = (self.class)::Fkey.new(oldtbl, oldcol, oldtbl_prt, $5) @col_index.update!(oldtbl, oldcol, fkey: fkey) @col_index.update!(oldtbl_prt, $5) newcol_prt = @col_index.updated_col!(oldtbl_prt, $5) retstr << $4 << newcol_prt << $6 << ($7 || '') << ($8 || '') else retstr << $7 << $8 end return [[fkey], retstr] else return [nil, strin] end end
Gets hsflag for read() to reset it.
If the later part of the line ends in an open-ended comment, this routine handles it.
@param instr [String] String to examine @param command [String] Current command, e.g., ALTER @param hskwd [Object] Any keyword you want to preset. @return [Hash] hsflag
# File lib/db_suit_rails/sql_skelton.rb, line 505 def get_hsflag_in_read(instr='', command='', **hskwd) hsflag = { :in_create => nil, # Inside CREATE :in_comment => false, # multiple-line comment :in_sentence => false, # multiple line :from_stdin => false, # During COPY statement :tbl_cur => nil, # Current Table to process } hsflag.merge!(hskwd) if hsflag[:in_comment] ## Inside a comment return hsflag end if hsflag[:in_sentence] hsflag[:in_sentence] = false if /(?<!\\)(?:\\\\)*;/ =~ instr ## '\;' is ignored. end # ar_onoff_comment = split_onoff_comments(instr) # if ar_onoff_comment.size.even? # ## It is in an open-ended comment. # hsflag[:in_comment] = true # ar_onoff_comment.pop # end # if /;\s*(--.*)?$/ !~ ar_onoff_comment[-1] # ## The sentence is open-ended # hsflag[:in_sentence] = command # end hsflag end
Add a new constraint index
@param oldtbl [String] old table name @param constraint [String] unique name for the constraint @param kind_in [String] String, including spaces. eg, “PRIMARY KEY ” @return [Array] [Index(eg. UNIQUE, PRIMARY), Index-Number-in-Array]
# File lib/db_suit_rails/sql_skelton.rb, line 546 def push_csrt_index(oldtbl, constraint, kind_in=:unknown) kind = (defined?(kind_in.strip) ? kind_in.strip.upcase.split[0] : kind_in) # UNIQUE, PRIMARY (single word only) @csrt_index.has_key?(kind) || (@csrt_index[kind] = {}) @csrt_index[kind].has_key?(oldtbl) || (@csrt_index[kind][oldtbl] = []) ind = @csrt_index[kind][oldtbl].find_index(constraint) if ind return [kind, ind] else @csrt_index[kind][oldtbl].push(constraint) return [kind, @csrt_index[kind][oldtbl].size-1] end end
Main routine to read the input file and modifies it internally.
Returns modified @strall (and set if specified) and sets @tbl_index and @col_index
This assumes several formats, such as:
/^CREATE TABLE migrations \(
*[\w\s]+,
*[\w\s]+,
*[\w\s]+
\);/
In short, this script does not process a sentence straddling over multiple lines as one in most cases.
Three stages are available:
1. :refactoring for refactroing the input string, where split lines of ALTER are connected, 2. :indexing to take into account the foreign keys, if there are any, 3. :final for producing the final output string.
You should run this method 3 times each with a different stage parameter specified in this order.
Note at the moment in :final all the variables are still attempted to be updated, which is a bit of waste.
@param stage [Symbol] (:refactoring|:indexing|:final) @param instr [String, NilClass] String to examine (Default: @strall) @param setstr [Boolean, NilClass] if True (Default unless instr is specified), the result is written to the instance variable. @param delete_primary [Boolean] Delete primary key definitions and add a new one. @param delete_sequence [Boolean] Delete all the Sequences. @param delete_trigger [Boolean] Delete all the triggers. @return [String]
# File lib/db_suit_rails/sql_skelton.rb, line 995 def read(stage: :refactoring, instr: nil, setstr: nil, delete_primary: true, delete_sequence: true, delete_trigger: true) setstr = true if (!instr && setstr.nil?) # @see get_hsflag_in_read() hsflag = get_hsflag_in_read() strret = '' (instr || @strall).each_line do |eline| ret_readsql = catch(:readsql){ if hsflag[:in_comment] str_comment, eline = get_end_comment(eline) strret += str_comment eline ? redo : next end if hsflag[:from_stdin] strret += eline hsflag[:from_stdin] = false if /^\\\./ =~ eline next end if hsflag[:in_create] ## Inside CREATE TABLE hsflag, str_revised, comma = read_col(hsflag[:in_create], eline, stage: stage, delete_primary: delete_primary, delete_sequence: delete_sequence, delete_trigger: delete_trigger) case stage when :refactoring, :indexing strret += eline next when :final if comma == ',' ## After PRIMARY KEY is deleted, the comman in the previous line should now be deleted. strret.sub!(/,\s*\Z/, '') end strret += str_revised next else raise "ERROR: stage=(#{stage.inspect})" end end if hsflag[:in_sentence] && /ALTER/i =~ hsflag[:in_sentence] case stage when :refactoring strret.chomp! # To connect this sentence to the previous one. strret += eline hsflag2 = get_hsflag_in_read(eline, in_sentence: hsflag[:in_sentence]) if ! hsflag2[:in_sentence] hsflag[:in_sentence] = hsflag2[:in_sentence] end next # if /.*;/ =~ eline # strret += $& # eline = $' # hsflag[:in_sentence] = nil # hsflag.has_key?(:tbl_cur) && (hsflag[:tbl_cur] = nil) # redo # else # strret += eline # next # end else raise DbSuitRailsError, "ERROR: stage must be specified as :refactoring first while setstr option is set as true (or refactoring of an ALTER line somehow has failed...): #{eline.chomp}" end end ## In_sentence like inside ALTER or CREATE SEQUENCE if hsflag[:in_sentence] strret += eline hsflag = get_hsflag_in_read(eline, in_sentence: hsflag[:in_sentence]) next end ## Main routine RexSqls.each_pair do |ea_key, ea_rex| matched = ea_rex.match(eline) if matched hsflag, to_add, remaining = read_child(ea_key, matched, MatIndices[ea_key], stage: stage, delete_primary: delete_primary, delete_sequence: delete_sequence, delete_trigger: delete_trigger) strret += to_add throw :readsql, remaining end end strret += eline next } # ret_readsql = catch(:readsql){ eline = (ret_readsql || next) # == Variable remaining (in the loop) redo end # (instr || @strall).each_line do |eline| @strall = strret if setstr return strret end
Sub-routine to read 'ALTER' statement.
The third parameter in the returned array is, if nil, next should be invoked in the caller, else the loop parameter should be replaced and redo be invoked.
@param ma_last [MatchData] Last match @param indices [Hash] Hash of indices (key: :oldtbl, :object etc) to indicate what object is indicated by which index in ma_last. @param stage [Symbol] (:refactoring|:indexing|:final) @param delete_primary [Boolean] Delete primary key definitions and add a new one. @param delete_sequence [Boolean] Delete all the Sequences. @param delete_trigger [Boolean] Delete all the triggers. @return [Array] [Hash(hsflag), String(to_add), String|NilClass]
# File lib/db_suit_rails/sql_skelton.rb, line 598 def read_alter(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) oldtbl = ma_last[indices[:oldtbl]] eline = ma_last.post_match case stage when :refactoring to_add = ma_last[0] + eline if /.*;/ =~ eline hsflag = get_hsflag_in_read(eline) else hsflag = get_hsflag_in_read(eline, 'ALTER', in_sentence: 'ALTER', tbl_cur: oldtbl) ###### NOTE: Check get_hsflag_in_read() ######## to_add.chomp! end return [hsflag, to_add, nil] end newtbl = @tbl_index.updated_tbl!(oldtbl) case stage when :indexing to_add = ma_last[0] when :final to_add = ma_last[1] + newtbl else raise end case eline when /^(\s+ADD\s+CONSTRAINT\s+)([\w.]+)([\w\s]+)(\()([\w\s,]+)(\))/i # $1 $2 $3 $4 $5 $6 ## ADD CONSTRAINT shop_b01unique UNIQUE (office_id, file_path, file_name, file_row); ## ADD CONSTRAINT shop_client_pkey PRIMARY KEY (office_id, client_id); com_option = $1 # ADD CONSTRAINT constraint_id = $2 # shop_client_pkey constraint_kind = $3 # PRIMARY KEY spacer = $4 # ( constraint_key = $5 # office_id, client_id tail_part = $6 + $' # ); push_csrt_index(oldtbl, constraint_id, constraint_kind) # Add in @csrt_index to_add << com_option << constraint_id case stage when :indexing to_add << constraint_kind << spacer << constraint_key when :final if delete_primary && /\bPRIMARY\s+KEY\b/i =~ constraint_kind if constraint_key.strip.split(',').size <= 1 to_add = '-- ' + to_add else constraint_kind.sub!(/\bPRIMARY\s+KEY\b/i, 'UNIQUE') end end strnewcol = convert_multi_cols(oldtbl, constraint_key, retall: false) to_add << constraint_kind << spacer << strnewcol else raise end to_add << tail_part return [get_hsflag_in_read($'), to_add, nil] when /^(\s+ALTER\s+COLUMN\s+)([\w.]+)([\w\s]+\bnextval\(')(#{Regexp.quote(oldtbl)}_id_seq)(')/i # $1 $2 $3 $4 $5 ## ALTER TABLE ONLY shop_b05 ALTER COLUMN id SET DEFAULT nextval('shop_b05_id_seq'::regclass); newcol = @col_index.updated_col!(oldtbl, $2) newseq = @tbl_index.updated_tbl!($4) case stage when :indexing to_add << $& when :final to_add << $1 << newcol << $3 << newseq << $5 else raise end to_add << $' hsflag = get_hsflag_in_read($', in_sentence: 'ALTER', tbl_cur: oldtbl) return [hsflag, to_add, nil] else to_add << eline hsflag = get_hsflag_in_read(eline, in_sentence: 'ALTER', tbl_cur: oldtbl) return [hsflag, to_add, nil] end # case eline end
Meta routines to run a child method
@param id_str [String] String identifier (CREATE|ALTER) @param (see read_alter
) @return [Array] [Hash(hsflag), String(to_add), String|NilClass]
# File lib/db_suit_rails/sql_skelton.rb, line 567 def read_child(id_str, *rest, **kwd) #def read_child(id_str, *rest, stage: :refactoring) case id_str when 'ALTER' return read_alter( *rest, **kwd) when 'COPY' return read_copy( *rest, **kwd) when 'CREATE' return read_create(*rest, **kwd) when 'NAME' return read_name( *rest, **kwd) when 'SELECT' return read_select(*rest, **kwd) else raise end end
Read column names
@param oldtbl [String] old table name @param eline [String] A line input @param stage [Symbol] (:refactoring|:indexing|:final) @param delete_primary [Boolean] Delete primary key definitions and add a new one. @param delete_sequence [Boolean] Delete all the Sequences. @param delete_trigger [Boolean] Delete all the triggers. @return [Array] [hsflag, revised_line, (',')] A comma is returned if the comman at the previous line should be deleted.
# File lib/db_suit_rails/sql_skelton.rb, line 379 def read_col(oldtbl, eline, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) case eline when /^\s*\)\s*;/ ## End of CREATE TABLE return [get_hsflag_in_read($'), eline] when /^(\s*)(--.*)?$/ ## Comment line return [get_hsflag_in_read($', in_create: oldtbl), eline] when /^(\s*)(\w+)/ word_pre, word1st, str2nd = $1, $2, $' case stage when :refactoring return [get_hsflag_in_read(str2nd, in_create: oldtbl), eline] end ## id integer NOT NULL REFERENCES products (product_no), # spaces = $1 # post_match = $' case word1st.upcase when 'FOREIGN' ## FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) _, retstr = get_foreign_keys(eline, oldtbl) return [get_hsflag_in_read(str2nd, in_create: oldtbl), retstr] when 'CONSTRAINT', 'CHECK', 'PRIMARY', 'UNIQUE', 'EXCLUDE', 'DEFERRABLE', 'INITIALLY' comma = nil if delete_primary && :final == stage eline2 = eline.sub(/\bPRIMARY KEY\s*\([^)]*\) *(,?)/i, ' ') comma = ',' if (eline != eline2) && ($1.empty?) eline = eline2 end hsflag = get_hsflag_in_read(str2nd, in_create: oldtbl) return [hsflag, eline, comma] else ## Column is found. newcol = @col_index.updated_col!(oldtbl, word1st) str1st = word_pre + newcol if delete_primary && :final == stage str2nd.sub!(/\bSERIAL(\s+PRIMARY\s+KEY)\b/i, 'int\1') str2nd.sub!(/\s*\bPRIMARY KEY\b\s*/i, ' ') end ## Checks out the foreign key constraint _, retstr = get_foreign_keys(str2nd, oldtbl, word1st) return [get_hsflag_in_read(retstr, in_create: oldtbl), str1st + retstr] end else raise "ERROR: Unsupported format of the line: #{eline}" end end
Sub-routine to read 'COPY' statement.
@param (see read_alter
) @return (see read_alter
)
# File lib/db_suit_rails/sql_skelton.rb, line 700 def read_copy(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) oldtbl = ma_last[indices[:oldtbl]] strcols= ma_last[indices[:oldcols]] eline = ma_last.post_match hsflag = get_hsflag_in_read(from_stdin: true) case stage when :refactoring return [hsflag, ma_last[0] + eline, nil] end ## Note: ## ## Everything that may need to be modified (from old table/column names to new ones) ## for the returned string is included in the argument ma_last. newtbl = @tbl_index.updated_tbl!(oldtbl) strnewcol = convert_multi_cols(oldtbl, strcols, retall: false) case stage when :indexing return [hsflag, ma_last[0] + eline, nil] when :final to_add = ma_last[1] + newtbl + ma_last[indices[:oldcols]-1] + strnewcol + ma_last[indices[:tail]] + eline return [hsflag, to_add, nil] else raise end end
Sub-routine to read 'CREATE' statement.
@param (see read_alter
) @return (see read_alter
)
# File lib/db_suit_rails/sql_skelton.rb, line 737 def read_create(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) oldtbl = ma_last[indices[:oldtbl]] # May not be a table name (see below (case-when-else clause)) object = ma_last[indices[:object]].upcase eline = ma_last.post_match case object when 'SEQUENCE' hsflag = get_hsflag_in_read(eline) when 'TABLE' if /(\s+\(\s*(--.*)?$)?/i !~ eline raise DbSuitRailsError, "ERROR: Unsupported format: #{(ma_last[0]+eline).chomp}" end hsflag = get_hsflag_in_read($', in_create: oldtbl) # , tbl_cur: oldtbl) else # oldtbl is NOT a table name but a trigger name etc. oldtbl = nil end case stage when :refactoring to_add = ma_last[0] + eline hsflag ||= get_hsflag_in_read(eline) return [hsflag, to_add, nil] end ## Only if SEQUENCE or TABLE (namely if hsflag is defined already), oldtbl is a genuine table name. ## Note this is needed here to do indexing, if necessary. to_add = (oldtbl ? (ma_last[1] + @tbl_index.updated_tbl!(oldtbl)) : ma_last[0]) case stage when :indexing to_add = ma_last[0] # resets. when :final if delete_primary && (object == 'TABLE') ## Adds a new PRIMARY KEY named "id" ## SERIAL from 1 to 2147483647 (cf. BIGSERIAL: 1 to 9223372036854775807) eline += " id SERIAL PRIMARY KEY,\n" end else raise end if hsflag # Either SEQUENCE or TABLE to_add << eline return [hsflag, to_add, nil] end case object when 'TRIGGER' ## CREATE TRIGGER tg01 BEFORE INSERT OR UPDATE ON shop_office_c FOR EACH ROW EXECUTE PROCEDURE tg_ins_upd_trriger(); if /^([\w+\s]+\bON\s+)([\w.]+)(\s+FOR\s+)/i !~ eline raise DbSuitRailsError, "ERROR: Unsupported format: #{(ma_last[0]+eline).chomp}" end newtbl = @tbl_index.updated_tbl!($2) case stage when :indexing to_add << $& when :final to_add << $1 << newtbl << $3 if delete_trigger to_add = '-- ' + to_add end else raise end to_add << $' return [get_hsflag_in_read($'), to_add, nil] when 'INDEX' ## CREATE INDEX password_resets_email_index ON password_resets USING btree (email); if /^(\s+ON\s+)([\w.]+)(\s+USING\b[\w\s]+\(\s*)(\w+)(\s*\)\s*;)/i !~ eline # $1 $2 $3 $4 $5 raise DbSuitRailsError, "ERROR: Unsupported format: #{(ma_last[0]+eline).chomp}" end newtbl = @tbl_index.updated_tbl!($2) newcol = @col_index.updated_col!($2, $4) case stage when :indexing to_add << $& when :final to_add << $1 << newtbl << $3 << newcol << $5 else raise end to_add << $' return [get_hsflag_in_read($'), to_add, nil] else raise DbSuitRailsError, "ERROR: Unsupported format: #{(ma_last[0]+eline).chomp}" end end
Sub-routine to read 'NAME' (comment-line).
@param (see read_alter
) @return (see read_alter
)
# File lib/db_suit_rails/sql_skelton.rb, line 844 def read_name(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) oldtbl = ma_last[indices[:oldtbl]] # May not be a table name, depending on the type. oldcol = ma_last[indices[:second]] # May be nil eline = ma_last.post_match hsflag = get_hsflag_in_read() # Because it is in a comment line anyway. case stage when :refactoring to_add = ma_last[0] + eline return [hsflag, to_add, nil] end ## Note: ## ## Everything that may need to be modified (from old table/column names to new ones) ## for the returned string is included in the argument ma_last. ## They depend on the type, which is examined below. if /^(\s*;\s*Type:\s+)(TABLE|SEQUENCE|DEFAULT|CONSTRAINT|TRIGGER)(\b(?:[\w\s.]*);)/i !~ eline # $1 $2 $3 ## -- Name: shop_b01; Type: TABLE; Schema: public; Owner: seller ## -- Data for Name: migrations; Type: TABLE DATA; Schema: public; Owner: seller ## -- Name: migrations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: seller ## -- Name: migrations migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: seller ## -- Name: shop_b01 shop_b01unique; Type: CONSTRAINT; Schema: public; Owner: seller ## -- Name: shop_b01 id; Type: DEFAULT; Schema: public; Owner: seller ## -- Name: shop_office_c tg01; Type: TRIGGER; Schema: public; Owner: seller #### The following is skipped, deliberately. ## -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: ## -- Name: tg_ins_upd_trriger(); Type: FUNCTION; Schema: public; Owner: seller return [hsflag, ma_last[0] + eline, nil] end case $2.upcase when 'TABLE', 'SEQUENCE', 'DEFAULT' newtbl = @tbl_index.updated_tbl!(oldtbl) newcol = @col_index.updated_col!(oldtbl, oldcol) if oldcol when 'CONSTRAINT', 'TRIGGER' newtbl = @tbl_index.updated_tbl!(oldtbl) newcol = oldcol ## Constraint/Trigger identifier etc. else return [get_hsflag_in_read(), ma_last[0] + eline, nil] end case stage when :indexing to_add = ma_last[0] when :final tmpstr = (newcol ? (ma_last[indices[:second]-1] + newcol) : '') to_add = ma_last[1] + newtbl + tmpstr else raise "ERROR: stage=(#{stage.inspect})" end to_add << eline return [hsflag, to_add, nil] end
Sub-routine to read 'SELECT' statement.
@param (see read_alter
) @return (see read_alter
)
# File lib/db_suit_rails/sql_skelton.rb, line 910 def read_select(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) oldtbl = ma_last[indices[:oldtbl]] eline = ma_last.post_match hsflag = get_hsflag_in_read(eline) case stage when :refactoring return [hsflag, ma_last[0] + eline, nil] end ## Note: ## ## Everything that may need to be modified (from old table/column names to new ones) ## for the returned string is included in the argument ma_last. newtbl = @tbl_index.updated_tbl!(oldtbl) case stage when :indexing return [hsflag, ma_last[0] + eline, nil] when :final to_add = ma_last[1] + newtbl + ma_last[indices[:tail]] + eline return [hsflag, to_add, nil] else raise end end
Run
@param dryrun [Boolean] Dryrun if True @param iow [IO,String] Output IO/Filename. In default, the instance variable is used. @param outmapping [String] Output CSV file. @param outmappingsql [Hash] Output SQL file info. See {#initialize} for specification. @param delete_primary [Boolean] Delete primary key definitions and add a new one. @param delete_sequence [Boolean] Delete all the Sequences. @param delete_trigger [Boolean] Delete all the triggers. @return [Array<String>] [output.sql, mapping.csv, nil or mapping.sql]
# File lib/db_suit_rails/sql_skelton.rb, line 156 def run(dryrun: false, iow: @outfile, outmapping: @mappingcsv, outmappingsql: @mappingsql, delete_primary: true, delete_sequence: true, delete_trigger: true) # @return [Array<String>] [output.sql, mapping.sql] # is_dryrun = dryrun read(stage: :refactoring, delete_primary: delete_primary, delete_sequence: delete_sequence, delete_trigger: delete_trigger) read(stage: :indexing, delete_primary: delete_primary, delete_sequence: delete_sequence, delete_trigger: delete_trigger) read(stage: :final, delete_primary: delete_primary, delete_sequence: delete_sequence, delete_trigger: delete_trigger) if !dryrun outsql = write_sql(iow: iow) outmap = write_mapping(outfile: outmapping) outmapsql = write_mappingsql(csvfile: outmap, mappingsql: outmappingsql) end return [outsql, outmap, outmapsql] end
Returns an array of non-comments and comments
If the returned array has an even number of elements, the next line must be inside a comment.
@param instr [String] String to examine @param ar_beg [Array] The previous array (for recursive uses). Even number of elements. @return [Array] [Non-Comment1, Comment1, Non-Comment2, …]
# File lib/db_suit_rails/sql_skelton.rb, line 461 def split_onoff_comments(instr, ar_beg: []) if /\/\*/ !~ instr return ar_beg+[instr] # odd number of elements end ar_beg.push($`) # odd number of elements rest = $' if /\*\// !~ rest ## It is inside an open-ended comment. return ar_beg+['/*'+rest] # even number of elements end ## It contains a Comment, but the comment is closed. ## Inspects further, recursively. ar_beg.push('/*'+$'+'*/') # even number of elements return not_in_comment($', ar_beg: ar_beg) # Either even or odd end
Write mapping CSV file
The format is as follows:
Old_Table,New_Table,Old_Column,New_Column
Note this table is obviously not normalised.
@param outfile [String] Output file. @param tbl_index
[TblIndex] Table mapping source @param col_index
[ColIndex] Column mapping source @return [String] mapping.csv
# File lib/db_suit_rails/sql_skelton.rb, line 207 def write_mapping(outfile: @mappingcsv, tbl_index: @tbl_index, col_index: @col_index) CSV.open(outfile, "w") do |csv| col_index.colmaps.each_pair do |ea_tbl, ea_hscol| ea_hscol[:order].each do |ea_col| csv << [ea_tbl, tbl_index.newtblval(ea_tbl), ea_col, ea_hscol[ea_col][:name]] end end end return outfile end
Write mapping SQL file
Note the tables are normalised.
@param csvfile [String] Mapping CSV filename. @param mappingsql [Hash] Output SQL file info. See {#initialize} for specification. @return [String, NilClass] mapping.sql
# File lib/db_suit_rails/sql_skelton.rb, line 228 def write_mappingsql(csvfile: @mappingcsv, mappingsql: @mappingsql) return nil if ! mappingsql[:filename] tmptbl = 'cloud_db_tbl_col_mappings' tbl_ref_id_name = self.class.colname4id(mappingsql[:tblname][:tbl]) # @param mappingsql [Hash] Output mapping-SQL file info (keys: :filename, :tblname[:tbl, :col], :colname[:tbl|:col => :from|:to]) open(mappingsql[:filename], "w"){ |iow| iow.print <<EOD -- Creates a pair of normalised mapping tables for table and column names. CREATE TABLE #{tmptbl} ( id SERIAL PRIMARY KEY, #{mappingsql[:colname][:tbl][:from]} varchar(#{MaxColnameBytes}), #{mappingsql[:colname][:tbl][:to]} varchar(#{MaxColnameBytes}), #{mappingsql[:colname][:col][:from]} varchar(#{MaxColnameBytes}), #{mappingsql[:colname][:col][:to]} varchar(#{MaxColnameBytes}), UNIQUE (#{mappingsql[:colname][:tbl][:from]}, #{mappingsql[:colname][:col][:from]}), UNIQUE (#{mappingsql[:colname][:tbl][:to]}, #{mappingsql[:colname][:col][:to]}) ); COPY cloud_db_tbl_col_mappings (#{mappingsql[:colname][:tbl][:from]}, #{mappingsql[:colname][:tbl][:to]}, #{mappingsql[:colname][:col][:from]}, #{mappingsql[:colname][:col][:to]}) FROM STDIN WITH (FORMAT 'csv', DELIMITER ','); EOD iow.print File.read(csvfile) iow.print <<EOD \\. -- Normalising the tables. CREATE TABLE IF NOT EXISTS #{mappingsql[:tblname][:tbl]} ( id SERIAL PRIMARY KEY, #{mappingsql[:colname][:tbl][:from]} varchar(#{MaxColnameBytes}) UNIQUE NOT NULL, #{mappingsql[:colname][:tbl][:to]} varchar(#{MaxColnameBytes}) UNIQUE NOT NULL ); CREATE TABLE IF NOT EXISTS #{mappingsql[:tblname][:col]} ( id SERIAL PRIMARY KEY, #{tbl_ref_id_name} integer REFERENCES #{mappingsql[:tblname][:tbl]} ON DELETE RESTRICT, #{mappingsql[:colname][:col][:from]} varchar(#{MaxColnameBytes}) NOT NULL, #{mappingsql[:colname][:col][:to]} varchar(#{MaxColnameBytes}) NOT NULL, UNIQUE (#{tbl_ref_id_name}, #{mappingsql[:colname][:col][:from]}, #{mappingsql[:colname][:col][:to]}) ); INSERT INTO #{mappingsql[:tblname][:tbl]} (#{mappingsql[:colname][:tbl][:from]}, #{mappingsql[:colname][:tbl][:to]}) SELECT DISTINCT #{mappingsql[:colname][:tbl][:from]}, #{mappingsql[:colname][:tbl][:to]} FROM #{tmptbl} ORDER BY #{mappingsql[:colname][:tbl][:from]}; INSERT INTO #{mappingsql[:tblname][:col]} (#{tbl_ref_id_name}, #{mappingsql[:colname][:col][:from]}, #{mappingsql[:colname][:col][:to]}) SELECT t.id, tc.#{mappingsql[:colname][:col][:from]}, tc.#{mappingsql[:colname][:col][:to]} FROM #{tmptbl} tc INNER JOIN #{mappingsql[:tblname][:tbl]} t ON tc.#{mappingsql[:colname][:tbl][:from]} = t.#{mappingsql[:colname][:tbl][:from]} ORDER BY t.id, tc.id; -- Drop the un-normalized table. DROP TABLE #{tmptbl}; EOD } return mappingsql[:filename] end
Write SQL
@param iow [IO,String] Output IO/Filename. In default, the instance variable is used. @param instr [String, NilClass] String to examine (Default: @strall) @return [String, IO] Output.sql
# File lib/db_suit_rails/sql_skelton.rb, line 176 def write_sql(iow: @outfile, instr: @strall) instr ||= @strall close_iow = false if defined? iow.sync iow_out = iow else iow_out = open((iow || @outfile), 'w') close_iow = true end begin iow_out.print instr ensure iow_out.close if close_iow end return [@outfile, @mappingcsv] end
Private Instance Methods
Makes template mappingsql
@return [Hash]
# File lib/db_suit_rails/sql_skelton.rb, line 80 def mk_tmpl_mappingsql hs = { :filename => DefMappingCsvname.sub(/\.csv$/, ''), :tblname => { :tbl => DefMappingSql[:tblname4tbl], :col => DefMappingSql[:tblname4col], }, :colname => { :tbl => { :from => DefMappingSql[:colprefix4tbl] + DefMappingSql[:colsuffix4from], :to => DefMappingSql[:colprefix4tbl] + DefMappingSql[:colsuffix4to], }, :col => { :from => DefMappingSql[:colprefix4col] + DefMappingSql[:colsuffix4from], :to => DefMappingSql[:colprefix4col] + DefMappingSql[:colsuffix4to], }, } } end