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

colmaps[R]

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

new(oldtbls=[]) click to toggle source

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

column_registered?(oldtbl, oldcol) click to toggle source

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
empty?() click to toggle source

Whether empty

# File lib/db_suit_rails/sql_skelton/col_index.rb, line 77
def empty?()
  @colmaps.empty?
end
fkey?(oldtbl, oldcol) click to toggle source

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
fkeyval(oldtbl, oldcol) click to toggle source

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
mixcolnames(oldtbl, exclude: nil, compact: true) click to toggle source

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
newcolnames(oldtbl, exclude: nil, compact: true) click to toggle source

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
newcolval(oldtbl, oldcol) click to toggle source

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
oldcolnames(oldtbl, exclude: nil, compact: true) click to toggle source

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
paircolnames(oldtbl) click to toggle source

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(oldtbl, oldcol=nil, newcol=nil, fkey: nil) click to toggle source

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
set_fkeyval_with(oldtbl, oldcol, fkey) click to toggle source

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
set_newcolval_with(oldtbl, oldcol, value) click to toggle source

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
table_exist?(oldtbl) click to toggle source

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(oldtbl, oldcol, fkey: nil, force: false) click to toggle source

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
update!(oldtbl, oldcol, fkey: nil) click to toggle source

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
updated_col!(oldtbl, oldcol, fkey: nil) click to toggle source

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

mk_newcolname(oldtbl, oldcol, useprefix: false) click to toggle source

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
old_or_newcolnames(old_or_new, oldtbl, exclude: nil, compact: true) click to toggle source

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
unique_colname(oldtbl, oldcol, col_root=nil, useprefix: false) click to toggle source

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