class SqlSkelton::ColIndex
class SqlSkelton::ColIndex
¶ ↑
Summary¶ ↑
Class for mapping between the original and modified SQL tables/columns
Description¶ ↑
This holds the information of old and new names for each column and whether it is referenced or not, and if it is, from which old table/column.
When you specify something, it is always the old table or column name.
Example¶ ↑
ci = SqlSkelton::ColIndex.new(tables) # Columns are not set. ci.update!(tables[0], column1) ci.update!(tables[0], 'tax_id') ci.update!(tables[0], 'job_id') # Foreign key (you could have done it in the sentnce above). fk_tax = SqlSkelton::Fkey.new(tables[0], 'tax_id', tables[1], 'eid') ci.update!(tables[0], 'tax_id', fkey: fk_tax) # Manually sets ci.set_newcolval_with(oldtbl, oldcol, new_value) new_col_name = newcolval(oldtbl, oldcol) fk_job = SqlSkelton::Fkey.new(tables[0], 'job_id', tables[2], 'fid') ci.set_fkeyval_with(tables[0], 'job_id', fk_job) ci.fkey?( tables[0], 'job_id') # => true fk_job_refer = fkeyval(tables[0], 'job_id') ci.update!(tables[0], 'job_id') # fkey option is unnecessary.
Constants
- ReservedColumnNames
List of the reserved Field names. Note it is also a bad idea to use a name identical with a method name of Method (check with ActiveRecord.singleton_methods) @see www.rubymagic.org/posts/ruby-and-rails-reserved-words
Attributes
Hash of Hash of Hash:
{ Old-Table-Name => { :order => [colname1, colname2, ...], # Holding the order of columns Old-Column-Name => { :name => New_colname, :fkey => Fkey|nil } } }
Public Class Methods
Constructor
Table list is NOT mandatory to specify.
@param oldtbls [Array,String] Old table name(s)
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 67 def initialize(oldtbls=[]) @colmaps = {} [oldtbls].flatten.map{|i| remove_sqlprefix(i)}.uniq.each do |ec| push(ec) end end
Public Instance Methods
Whether a old-name column is registered?
@param oldtbl [String] Old table name @param oldcol [String] Old column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 92 def column_registered?(oldtbl, oldcol) oldtbl = remove_sqlprefix(oldtbl) table_exist?(oldtbl) && @colmaps[oldtbl].has_key?(oldcol) end
Whether empty
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 77 def empty?() @colmaps.empty? end
Whether a old-name column is a foreign key?
@param oldtbl [String] Old table name @param oldcol [String] Old column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 144 def fkey?(oldtbl, oldcol) oldtbl = remove_sqlprefix(oldtbl) !!(fkeyval(oldtbl, oldcol)) end
Returns a foreign key (SqlSkelton::Fkey
object) or nil
@param oldtbl [String] Old table name @param oldcol [String] Old column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 124 def fkeyval(oldtbl, oldcol) oldtbl = remove_sqlprefix(oldtbl) column_registered?(oldtbl, oldcol) || (return nil) @colmaps[oldtbl][oldcol][:fkey] end
Returns an Array of mixed column names == (new OR old)
@param (see oldcolnames
)
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 356 def mixcolnames(oldtbl, exclude: nil, compact: true) oldtbl = remove_sqlprefix(oldtbl) newcol = newcolnames(oldtbl, exclude: exclude, compact: compact) oldcol = oldcolnames(oldtbl, exclude: exclude, compact: compact) mixcol = [] newcol.each_with_index do |newc, i| mixcol[i] = (newc || oldcol[i]) end mixcol end
Returns an Array of new column names.
@param (see oldcolnames
)
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 347 def newcolnames(oldtbl, exclude: nil, compact: true) oldtbl = remove_sqlprefix(oldtbl) old_or_newcolnames(:new, oldtbl, exclude: exclude, compact: compact) end
Gets a stored newcol name
@param oldtbl [String] Old table name @param oldcol [String] Candidate new column name @return [String] Stored new column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 103 def newcolval(oldtbl, oldcol) oldtbl = remove_sqlprefix(oldtbl) @colmaps[oldtbl][oldcol][:name] end
Returns an Array of old column names.
@param oldtbl [String] Old table name @param exclude [String] Old column name, the corresponding value of which either becomes nil in, or if compact option is true, is excluded from, the result. @param compact [Boolean] Read only when exclude option is non-nil. If true (Def), the result is compacted; else the excluded value becomes nil. @return [Array]
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 338 def oldcolnames(oldtbl, exclude: nil, compact: true) oldtbl = remove_sqlprefix(oldtbl) old_or_newcolnames(:old, oldtbl, exclude: exclude, compact: compact) end
Returns an Array of Hash of column names { :old => old, :new => new }
Returned array is sorted.
@param oldtbl [String] Old table name @return [Array]
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 316 def paircolnames(oldtbl) oldtbl = remove_sqlprefix(oldtbl) table_exist?(oldtbl) || (raise DbSuitRailsError, "ERROR: Old-table (#{oldtbl}) does not exist.") arret = [] @colmaps[oldtbl].select{ |k, v| defined? k.gsub }.each_pair do |k, v| # (:order => [Array]) is excluded by hash#select arret.push( { :old => k, :new => v[:name] } ) end arret.sort { |a,b| @colmaps[oldtbl][:order].find_index(a[:old]) <=> @colmaps[oldtbl][:order].find_index(b[:old]) } end
Push a new column (and/or table)
Make sure to use this method to add a record in @colmaps . Or, alternatively, you can use {#update!}, which calls this method, and sets newcol automatically.
If the record already exists, this raises an error.
@param oldtbl [String] Old table name @param oldcol [String] Old column name @param newcol [String] New column name @param fkey [SqlSkelton::Fkey, NilClass] Foreign key or (Default) nil @return [self]
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 163 def push(oldtbl, oldcol=nil, newcol=nil, fkey: nil) oldtbl = remove_sqlprefix(oldtbl) if table_exist?(oldtbl) oldcol || (raise DbSuitRailsError, "ERROR: Failed to push old-table (#{oldtbl}) (with no column specified) as it already exists.") else @colmaps[oldtbl] = { :order => [] } # (raise "ERROR: Failed to push column (#{oldcol}) as old-table (#{oldtbl}) does not exist.") end !oldcol && (return self) column_registered?(oldtbl, oldcol) && (raise DbSuitRailsError, "ERROR: Failed to push old-column (#{oldcol}) in old-table (#{oldtbl}), as it exists.") @colmaps[oldtbl][oldcol] = { :name => newcol, :fkey => fkey } @colmaps[oldtbl][:order].push(oldcol) self end
Returns a foreign key (SqlSkelton::Fkey
object) or nil
@param oldtbl [String] Old table name @param oldcol [String] Old column name @param fkey [SqlSkelton::Fkey, NilClass] Foreign key
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 135 def set_fkeyval_with(oldtbl, oldcol, fkey) oldtbl = remove_sqlprefix(oldtbl) @colmaps[oldtbl][oldcol][:fkey] = fkey end
Sets a newcol name
@param oldtbl [String] Old table name @param oldcol [String] Candidate new column name @param value [String] new column name to set @return [String] Stored new column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 114 def set_newcolval_with(oldtbl, oldcol, value) oldtbl = remove_sqlprefix(oldtbl) @colmaps[oldtbl][oldcol][:name] = value end
Whether a old-name table is registered?
@param oldtbl [String] Old table name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 84 def table_exist?(oldtbl) @colmaps.has_key?(remove_sqlprefix(oldtbl)) end
Update a column (or all the columns)
If oldcol is :all, all the column are updated, in which case fkey option is ignored.
This updates a newcol name only if fkey is set or given or if the existing one is empty or nil.
If force: option is true, regardless of fkey and current newcol name, newcol is updated.
@param oldtbl [String] Old table name @param oldcol [String, Symbol] Old column name, or :all @param fkey [SqlSkelton::Fkey, NilClass] Foreign key or (Default) nil @param force forcibly updates. @return [self]
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 197 def update(oldtbl, oldcol, fkey: nil, force: false) oldtbl = remove_sqlprefix(oldtbl) if :all == oldcol oldcolnames(oldtbl).each do |ec_col| update(oldtbl, ec_col, force: force) end return self end column_registered?(oldtbl, oldcol) || (raise DbSuitRailsError, "ERROR: Failed to update column (#{oldcol}) in old-table (#{oldtbl}), which may not exist.") cur_fkey = (fkey || fkeyval(oldtbl, oldcol)) newcol = newcolval(oldtbl, oldcol) if !newcol || newcol.empty? set_newcolval_with(oldtbl, oldcol, mk_newcolname(oldtbl, oldcol)) ## Foreign key has not been taken into account. Therefore it is now. cur_fkey && (return update(oldtbl, oldcol, fkey: fkey)) return self end ## If force, attempts to update newcol name regardless the other conditions. force && set_newcolval_with(oldtbl, oldcol, mk_newcolname(oldtbl, oldcol)) cur_fkey || (return self) set_fkeyval_with(oldtbl, oldcol, cur_fkey) ## Maybe unchanged. case oldcol when /^id$/i newn = unique_colname(oldtbl, oldcol, 'id' + SuffixId + '_id', useprefix: true) set_newcolval_with(oldtbl, oldcol, newn) return self when /_id$/i # New column name must end in _id if a foreign key. # And in fact the name-root has to be the singular form of the table name # which references, AND its colum name must be 'id' (as in 2 characters). return self if /_id$/i =~ newcol # Old column name ends in _id, but the new one does not. # Because foreign key is defined, it should. # In other words, newcol must be identical to oldcol, as long as available set_newcolval_with(oldtbl, oldcol, unique_colname(oldtbl, oldcol)) return self else # Does nothing. return self end end
Updates a column or pushes it if it is not registered, yet.
Basically, this method is a wrapper for {#update} and {#push}
This updates a newcol name regardless the conditions (force option is set).
If oldcol is :all, all the column are updated, in which case fkey option is ignored.
@param oldtbl [String] Old table name @param oldcol [String, Symbol] Old column name or :all . Must not be nil. @param fkey [SqlSkelton::Fkey, NilClass] Foreign key or (Default) nil @return [self]
@see SqlSkelton::ColIndex.update
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 263 def update!(oldtbl, oldcol, fkey: nil) raise DbSuitRailsError, "ERROR: oldtbl (#{oldtbl.inspect}) must be String." if ! oldtbl oldtbl = remove_sqlprefix(oldtbl) if !oldcol table_exist?(oldtbl) || push(oldtbl, oldcol, fkey: fkey) warn "WARNING: non-nil fkey is ignored when the specified column is nil in ColIndex#update!." if fkey return self end ## Note: ## Somehow, the following sentence followed by simply update(..., force: true) does not work. ## and so the rescue sentence seems essential. # # column_registered?(oldtbl, oldcol) || push(oldtbl, oldcol, fkey: fkey) begin return update(oldtbl, oldcol, fkey: fkey, force: true) rescue DbSuitRailsError end push(oldtbl, oldcol, fkey: fkey) update(oldtbl, oldcol, force: true) end
Wrapper of {#update!}
It returns the new column-name. Also, it does NOT update the record, if the new file is already registered.
@param oldtbl [String] Old table name @param oldcol [String, Symbol] Old column name or :all @param fkey [SqlSkelton::Fkey, NilClass] Foreign key or (Default) nil @return [String]
@see SqlSkelton::ColIndex.update
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 299 def updated_col!(oldtbl, oldcol, fkey: nil) oldtbl = remove_sqlprefix(oldtbl) if ! column_registered?(oldtbl, oldcol) push(oldtbl, oldcol, fkey: fkey) update(oldtbl, oldcol, force: true) end newcolval(oldtbl, oldcol) end
Private Instance Methods
Gets a non-existing new column name
This is a low-level method, and does not take into account {#fkey?}. In practice, use {#update!} etc.
@param oldtbl [String] Old table name @param oldcol [String] Old column name (Template for the new column name) @param useprefix [Boolean] If true, add a prefix as opposed to suffix. @return [String] Unique new column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 408 def mk_newcolname(oldtbl, oldcol, useprefix: false) oldtbl = remove_sqlprefix(oldtbl) case oldcol when /^id$/i return unique_colname(oldtbl, oldcol, 'id' + SuffixId, useprefix: useprefix) when /_id$/i newcol_cand = (fkey?(oldtbl, oldcol) ? oldcol : (oldcol + SuffixId)) return unique_colname(oldtbl, oldcol, newcol_cand, useprefix: useprefix) else return oldcol end end
Core routine for {#oldcolnames} and {#newcolnames}
@param old_or_new [Symbol] :old or :new. @param (see oldcolnames
)
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 425 def old_or_newcolnames(old_or_new, oldtbl, exclude: nil, compact: true) oldtbl = remove_sqlprefix(oldtbl) pairs = paircolnames(oldtbl) arret = pairs.map{|i| (exclude == i[:old]) ? nil : i[old_or_new]} return arret if !(exclude && compact) arret.delete_at( pairs.find_index{ |val| exclude == val[:old] } ) arret end
Gets a unique new column name.
If the CANDIDATE fails, it searches for CANDIDATE1, CANDIDATE2, … etc. Or, n1_CANDIDATE, n2_CANDIDATE1, … etc, if useprefix option is true.
@param oldtbl [String] Old table name @param oldcol [String] Old column name @param col_root [String, NilClass] Candidate new column name. If unspecified, copied from oldcol @param useprefix [Boolean] If true, add a prefix as opposed to suffix. @return [String] Unique new column name
# File lib/db_suit_rails/sql_skelton/col_index.rb, line 383 def unique_colname(oldtbl, oldcol, col_root=nil, useprefix: false) oldtbl = remove_sqlprefix(oldtbl) col_root ||= oldcol case col_root when oldtbl+'count', *ReservedColumnNames col_root += SuffixId end existing_cols = mixcolnames(oldtbl, exclude: oldcol) # unique_name_cand defined in Utils module. newcol = unique_name_cand(col_root, useprefix: useprefix){ |cand| ! existing_cols.include?(cand) } newcol && (return(newcol)) raise "ERROR: Failed to find a new column name for candidate column-name(#{col_root} in (old) Table(#{oldtbl})." end