Class: Icebox::Icebox

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

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_or_options = {}) ⇒ Icebox

Returns a new instance of Icebox.



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/icebox.rb', line 12

def initialize(db_or_options = {})
  # Icebox is configurable using a YAML file in /etc/icebox.conf
  # The root-level key :connections should be an array of option sets to be
  # passed to the Sequel#mysql method. Right now, only the first is used.

  if db_or_options.class == Hash
    begin
      defaults = YAML.load(File.open("/etc/icebox.conf"))[:connections][0]
    rescue 
      defaults = {database: 'test', socket: '/tmp/mysql-ib.sock'}
    end
    @db = Sequel.mysql(defaults.merge(db_or_options))
  else
    @db = db_or_options
  end
  raise "Initialization of Icebox object failed" unless @db
end

Instance Attribute Details

#db ⇒ Object

Returns the value of attribute db.



10
11
12
 
# File 'lib/icebox.rb', line 10

def db
  @db
end

Instance Method Details

#create_table_as(table, sql) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'lib/icebox.rb', line 77

def create_table_as(table, sql)
  view = "icebox_view_for_#{table}"

  # Create view from SQL, extract layout, and make new table from it:
  @db.create_or_replace_view view, sql
  fields_definition = @db.schema(view).map do |col|
    "#{col[0]} #{col[1][:db_type]}"
  end.join(',')
  @db << "CREATE TABLE #{table} (#{fields_definition})"
  insert_into table, "SELECT * FROM #{view}"
  @db.drop_view view
end

#insert_into(table, sql) ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/icebox.rb', line 57

def insert_into(table, sql)
  pipe = "/tmp/icebox_pipe_for_#{table}"
  sql = sql.sub /\s*;\s*/, ''  # Chop off semicolon if present

  # Simultaneously export and re-import CSV through FIFO pipe:
  @db.disconnect  # Needed for fork; Reconnects automatically
  pid1 = fork do
    @db << <<-SQL
      SET @bh_pipemode = 'client';
      #{sql} INTO OUTFILE '#{pipe}'
      FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\\\'
    SQL
  end
  pid2 = fork do
    load_data_infile table, pipe, fifo: true
  end
  Process.waitpid(pid1)
  Process.waitpid(pid2)
end

#load_data_infile(table, path = nil, opts = {}) ⇒ Object 



30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/icebox.rb', line 30

def load_data_infile(table, path=nil, opts={})
  # If a handle object is given, start writing it out into a new FIFO pipe
  # from within a forked subprocess before we start loading:
  if opts[:handle]
    pipe = "/tmp/icebox_pipe_for_#{table}_at_#{Time.now.to_i}"
    system "mkfifo #{pipe}"
    opts[:fifo] = pipe
    pid = fork do
      File.open(pipe, 'a') do |p|
        p.write opts[:handle].read
      end
    end
  end

  path = path || opts[:path] || opts[:fifo]
  raise "load_data_infile called with no input source" unless path

  load_sql = <<-SQL
    #{"SET @bh_pipemode = 'server';" if opts[:fifo]}
    LOAD DATA INFILE '#{path}' INTO TABLE #{table}
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\\\';
  SQL
  # Force value retrieval to delay function return until query finishes:
  @db[load_sql].all
  Process.waitpid(pid) if pid # Wait for writer too if it exists
end