Class: SQLite3::Database

Inherits:

Object

• Object
• SQLite3::Database

Defined in:

lib/carray-io-sqlite3/core.rb

Instance Method Summary

• #create_table(table, data, names = nil) ⇒ Object
• #import_table(dbfile, src_table, dst_table = nil) ⇒ Object

  insert.

• #insert(table, data) ⇒ Object
• #schema(table = nil) ⇒ Object

Instance Method Details

#create_table(table, data, names = nil) ⇒ Object

# File 'lib/carray-io-sqlite3/core.rb', line 129

def create_table (table, data, names = nil)
  ncols = data.rank == 1 ? 1 : data.dim1
  if names
    varlist = names.map{|s| s.to_s }
  elsif data.respond_to?(:names)
    varlist = data.names
  else
    varlist = []
    ncols.times do |i|
      varlist << "col#{i}"
    end
  end
  execute %{create table #{table} (#{varlist.join(",")})}
  insert(table, data)
end

#import_table(dbfile, src_table, dst_table = nil) ⇒ Object

insert

# File 'lib/carray-io-sqlite3/core.rb', line 177

def import_table (dbfile, src_table, dst_table = nil)
  dst_table ||= src_table
  db = "db#{Thread.current.object_id}"
  execute %{
    attach database "#{dbfile}" as #{db};
  }
  sql = get_first_value %{
    select sql from #{db}.sqlite_master where type = 'table' and name = "#{src_table}"
  }
  sql.sub!(/create\s+table\s(.+?)\s/i, "create table if not exists #{dst_table}")
  execute sql
  execute %{
    insert into #{dst_table} select * from #{db}.#{src_table};
  }
  indeces = execute %{
    select sql from #{db}.sqlite_master where type = 'index' and tbl_name = "#{src_table}"        
  } 
  indeces.each do |row|
    sql = row[0]
    unless sql
      break
    end
    sql.sub!(/create\s+index/i, "create index if not exists")
    sql.sub!(/on\s+#{src_table}\(/i, "on #{dst_table}(")
    execute sql
  end
  execute %{
    detach database #{db};
  }
end

#insert(table, data) ⇒ Object

# File 'lib/carray-io-sqlite3/core.rb', line 145

def insert (table, data)
  table_info = execute %{
    pragma table_info(#{table}) 
  }  
  if table_info.empty?
    raise "#{table} is empty table"
  end
  qstns = (["?"]*table_info.size).join(",")
  self.transaction {
    stmt = prepare %{ 
      insert or replace into #{table} values (#{qstns}) 
    }
    data.to_a.each do |row|
      stmt.execute *row
    end
    stmt.close      
  }
end

#schema(table = nil) ⇒ Object

# File 'lib/carray-io-sqlite3/core.rb', line 164

def schema (table = nil)
  if table
    return get_first_value %{
      select sql from sqlite_master where type in ('table', 'view', 'index') and name = "#{table}"
    }
  else
    return execute(%{
      select sql from sqlite_master where type in ('table', 'view', 'index')
    }).map{|v| v[0]}
  end
end