class CsvPack::Tab

Constants

DATA_TYPES
 mappings for data types
 from tabular data package to ActiveRecord migrations

see http://dataprotocols.org/json-table-schema/   (section Field Types and Formats)

for now supports these types

Public Class Methods

new( h, text ) click to toggle source
# File lib/csvpack/pack.rb, line 103
def initialize( h, text )
  @h = h

  ## todo parse csv
  ##  note: use header options (first row MUST include headers)
  @data = CSV.parse( text, headers: true )

  pp @data[0]
end

Public Instance Methods

ar_clazz() click to toggle source
# File lib/csvpack/pack.rb, line 259
def ar_clazz
  @ar_clazz ||= begin
    clazz = Class.new( ActiveRecord::Base ) do
      ## nothing here for now
    end
    puts "set table_name to #{sanitize_name( name )}"
    clazz.table_name = sanitize_name( name )
    clazz
  end
  @ar_clazz
end
dump_schema() click to toggle source
# File lib/csvpack/pack.rb, line 234
def dump_schema
  ## try to dump schema (fields)
  puts "*** dump schema:"

  @h['schema']['fields'].each do |f|
    puts "   #{f['name']} ( #{sanitize_name(f['name'])} ) : #{f['type']}} ( #{DATA_TYPES[f['type']]} )"
  end

end
import!() click to toggle source
# File lib/csvpack/pack.rb, line 139
def import!
  connect!
  con = ActiveRecord::Base.connection

  column_names = []
  column_types = []
  column_placeholders = []
  @h['schema']['fields'].each do |f|
    column_names << sanitize_name(f['name'])
    column_types << DATA_TYPES[f['type']]
    column_placeholders << '?'
  end

  sql_insert_into = "INSERT INTO #{sanitize_name(name)} (#{column_names.join(',')}) VALUES "
  puts sql_insert_into

  i=0
  @data.each do |row|
    i+=1
    ## next if i > 3   ## for testing; only insert a couple of recs

    ## todo: check if all string is ok; or number/date/etc. conversion needed/required?
    values = []
    row.fields.each_with_index do |value,index|   # get array of values
      type = column_types[index]
      ## todo add boolean ??
      if value.blank?
        values << 'NULL'
      elsif [:number,:float,:integer].include?( type )
        values << value           ## do NOT wrap in quotes (numeric)
      else
        esc_value = value.gsub( "'", "''" )  ## escape quotes e.g. ' becomse \'\', that is, double quotes
        values << "'#{esc_value}'"    ## wrap in quotes
      end
    end
    pp values

    sql = "#{sql_insert_into} (#{values.join(',')})"
    puts sql
    con.execute( sql )
  end
end
import_v1!() click to toggle source
# File lib/csvpack/pack.rb, line 183
def import_v1!
   ### note: import via sql for (do NOT use ActiveRecord record class for now)
  con = ActiveRecord::Base.connection

  column_names = []
  column_types = []
  column_placeholders = []
  @h['schema']['fields'].each do |f|
    column_names << sanitize_name(f['name'])
    column_types << DATA_TYPES[f['type']]
    column_placeholders << '?'
  end

  sql = "INSERT INTO #{sanitize_name(name)} (#{column_names.join(',')}) VALUES (#{column_placeholders.join(',')})"
  puts sql

  i=0
  @data.each do |row|
    i+=1
    next if i > 3   ## for testing; only insert a couple of recs

    ## todo: check if all string is ok; or number/date/etc. conversion needed/required?
    params = row.fields   # get array of values
    pp params
    con.exec_insert( sql, 'SQL', params )  # todo/check: 2nd param name used for logging only??
  end
end
name() click to toggle source
# File lib/csvpack/pack.rb, line 113
def name()  @h['name']; end
pretty_print( printer ) click to toggle source
# File lib/csvpack/pack.rb, line 116
def pretty_print( printer )
  printer.text "Tab<#{object_id} @data.name=#{name}, @data.size=#{@data.size}>"
end
sanitize_name( ident ) click to toggle source
# File lib/csvpack/pack.rb, line 245
def sanitize_name( ident )
  ##
  ## if identifier starts w/ number add leading underscore (_)
  ##  e.g. 52 Week Price  => becomes  _52_week_price

  ident = ident.strip.downcase
  ident = ident.gsub( /[\.\-\/]/, '_' )  ## convert some special chars to underscore (e.g. dash -)
  ident = ident.gsub( ' ', '_' )
  ident = ident.gsub( /[^a-z0-9_]/, '' )
  ident = "_#{ident}"  if ident =~ /^[0-9]/
  ident
end
up!() click to toggle source
# File lib/csvpack/pack.rb, line 121
def up!
  # run Migration#up to create table
  connect!
  con = ActiveRecord::Base.connection

  con.create_table sanitize_name( name ) do |t|
    @h['schema']['fields'].each do |f|
      column_name = sanitize_name(f['name'])
      column_type = DATA_TYPES[f['type']]

      puts "  #{column_type} :#{column_name}  =>  #{f['type']} - #{f['name']}"

      t.send( column_type.to_sym, column_name.to_sym )   ## todo/check: to_sym needed?
    end
    t.string  :name
  end
end

Private Instance Methods

connect!() click to toggle source

helper to get connection; if not connection established use defaults

# File lib/csvpack/pack.rb, line 274
def connect!
  ## todo: cache returned con - why, why not ??
  unless ActiveRecord::Base.connected?
    puts "note: no database connection established; using defaults (e.g. in-memory SQLite database)"
    ActiveRecord::Base.establish_connection( adapter:  'sqlite3', database: ':memory:' )
    ActiveRecord::Base.logger = Logger.new( STDOUT )
  end
  ActiveRecord::Base.connection
end