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

col_index[R]

ColIndex instance for mapping database column names

csrt_index[R]

Constraint-Index instance for listing the constraint name

mappingcsv[R]

Output Mapping CSV file

mappingsql[R]

Output Mapping SQL information (Hash-keys: :filename, :tblname, :sqlname)

outfile[R]

Output SQL file

tbl_index[R]

TblIndex instance for mapping database table names

Public Class Methods

colname4id(tblname) click to toggle source

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
new(infile, outfile=nil, mappingcsv: DefMappingCsvname, mappingsql: {}) click to toggle source

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(oldtbl, newcol='id', newtype='bigint') click to toggle source

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
convert_multi_cols(oldtbl, oldcolsstr, retall: false) click to toggle source

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
get_end_comment(instr) click to toggle source

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
get_foreign_keys(strin, oldtbl, oldcol=nil) click to toggle source

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.

  1. 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}.

  2. 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
get_hsflag_in_read(instr='', command='', **hskwd) click to toggle source

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
push_csrt_index(oldtbl, constraint, kind_in=:unknown) click to toggle source

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
read(stage: :refactoring, instr: nil, setstr: nil, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
read_alter(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
read_child(id_str, *rest, **kwd) click to toggle source

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_col(oldtbl, eline, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
read_copy(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
read_create(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
read_name(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
read_select(ma_last, indices={}, stage: :refactoring, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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(dryrun: false, iow: @outfile, outmapping: @mappingcsv, outmappingsql: @mappingsql, delete_primary: true, delete_sequence: true, delete_trigger: true) click to toggle source

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
split_onoff_comments(instr, ar_beg: []) click to toggle source

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(outfile: @mappingcsv, tbl_index: @tbl_index, col_index: @col_index) click to toggle source

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_mappingsql(csvfile: @mappingcsv, mappingsql: @mappingsql) click to toggle source

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(iow: @outfile, instr: @strall) click to toggle source

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

mk_tmpl_mappingsql() click to toggle source

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