Class: Csvql::TableHandler

Inherits:
Object
  • Object
show all
Defined in:
lib/csvql/csvql.rb

Instance Method Summary collapse

Constructor Details

#initialize(path, console) ⇒ TableHandler

Returns a new instance of TableHandler.



9
10
11
12
13
14
15
16
17
18
 
# File 'lib/csvql/csvql.rb', line 9

def initialize(path, console)
  @db_file = if path && path.strip.size > 0
               path
             elsif console
               @tmp_file = Tempfile.new("csvql").path
             else
               ":memory:"
             end
  @db = SQLite3::Database.new(@db_file)
end

Instance Method Details

#create_alias(table, view = "tbl") ⇒ Object 



27
28
29
30
31
 
# File 'lib/csvql/csvql.rb', line 27

def create_alias(table, view="tbl")
  return if table == view
  exec "DROP VIEW IF EXISTS #{view}"
  exec "CREATE VIEW #{view} AS SELECT * FROM #{table}"
end

#create_table(schema, table_name = "tbl") ⇒ Object 



20
21
22
23
24
25
 
# File 'lib/csvql/csvql.rb', line 20

def create_table(schema, table_name="tbl")
  @col_name = schema.split(",").map {|c| c.split.first.strip }
  @col_size = @col_name.size
  @table_name = table_name
  exec "CREATE TABLE IF NOT EXISTS #{@table_name} (#{schema})"
end

#drop_table(table_name = "tbl") ⇒ Object 



33
34
35
 
# File 'lib/csvql/csvql.rb', line 33

def drop_table(table_name="tbl")
  exec "DROP TABLE IF EXISTS #{table_name}"
end

#exec(sql) ⇒ Object 



51
52
53
 
# File 'lib/csvql/csvql.rb', line 51

def exec(sql)
  @db.execute(sql)
end

#insert(cols, line) ⇒ Object 



42
43
44
45
46
47
48
49
 
# File 'lib/csvql/csvql.rb', line 42

def insert(cols, line)
  if cols.size != @col_size
    puts "line #{line}: wrong number of fields in line (skipping)"
    return
  end
  @pre ||= prepare(cols)
  @pre.execute(cols)
end

#open_console ⇒ Object 



55
56
57
58
 
# File 'lib/csvql/csvql.rb', line 55

def open_console
  system("sqlite3", @db_file)
  File.delete(@tmp_file) if @tmp_file
end

#prepare(cols) ⇒ Object 



37
38
39
40
 
# File 'lib/csvql/csvql.rb', line 37

def prepare(cols)
  sql = "INSERT INTO #{@table_name} (#{@col_name.join(",")}) VALUES (#{cols.map{"?"}.join(",")});"
  @pre = @db.prepare(sql)
end