Class: SQLiteLayer

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

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(filename, options = {}) ⇒ SQLiteLayer

Returns a new instance of SQLiteLayer.



30
31
32
33
34
35
36
 
# File 'lib/sapis/sqlite_layer.rb', line 30

def initialize(filename, options={})
  absolute_db_filename = options[:relative] ? File.expand_path(filename, '~') : filename

  @db = SQLite3::Database.new(absolute_db_filename)

  @db.execute('PRAGMA foreign_keys = ON')
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



28
29
30
 
# File 'lib/sapis/sqlite_layer.rb', line 28

def db
  @db
end

Instance Method Details

#close ⇒ Object 



162
163
164
 
# File 'lib/sapis/sqlite_layer.rb', line 162

def close
  @db.close
end

#execute(sql, *params) ⇒ Object 



104
105
106
 
# File 'lib/sapis/sqlite_layer.rb', line 104

def execute(sql, *params)
  @db.execute(sql, params)
end

#insert_values(table, values, options = {}) ⇒ Object

values: Can be either an array of values, or a hash field=>value.

In order to insert BLOBs, pass a value enclosed in an array, e.g. ['mydata']

Doesn’t try to be clever.

options:

:straight_insert        hash of values (position => value) to insert straight, without placeholders.
                        values are not automatically escaped or quoted.
                        this makes sense for some edge cases.



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
# File 'lib/sapis/sqlite_layer.rb', line 48

def insert_values(table, values, options={})
  straight_insert = options[:straight_insert] || {}

  sql_fields       = []
  sql_placeholders = []
  sql_values       = []

  case values
  when Hash
    values.each do |field, value|
      sql_fields       << field.to_s
      sql_placeholders << '?'
      sql_values       <<  value
    end
  when Array
    values.each do |value|
      sql_placeholders << '?'
      sql_values       <<  value
    end
  else
    raise "Invalid values class: #{values.class}"
  end

  straight_insert.each do |field, value|
    sql_fields       << field.to_s
    sql_placeholders << value
  end

  sql_values.each_with_index do |value, i|
    sql_values[i] = SQLite3::Blob.new(value.first) if value.is_a?(Array)
  end

  sql = "INSERT INTO #{table}"
  sql << "( #{sql_fields.join(', ')} )" if sql_fields.size > 0
  sql << " VALUES( #{sql_placeholders.join(', ')} )"

  @db.execute(sql, sql_values)

  @db.last_insert_row_id
end

#select(sql, *params) ⇒ Object 



108
109
110
111
 
# File 'lib/sapis/sqlite_layer.rb', line 108

def select(sql, *params)
  options = params.last.is_a?(Hash) ? params.pop : {}
  execute(sql, *params)
end

#select_all(sql, *params) ⇒ Object 



131
132
133
134
135
136
137
 
# File 'lib/sapis/sqlite_layer.rb', line 131

def select_all(sql, *params)
  column_names, *raw_data = select_with_headers(sql, *params)

  raw_data.map do |row|
    Hash[column_names.zip(row)]
  end
end

#select_value(sql, *params) ⇒ Object

params: the last can be :options options:

:force:  force finding a value



117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/sapis/sqlite_layer.rb', line 117

def select_value(sql, *params)
  options = params.last.is_a?(Hash) ? params.pop : {}

  row = execute(sql, *params).first

  value = row && row.first

  if value
    value
  elsif options[:force]
    raise "Value not found!"
  end
end

#select_with_headers(sql, *params) ⇒ Object

This is a :select_row with headers as first row.



141
142
143
 
# File 'lib/sapis/sqlite_layer.rb', line 141

def select_with_headers(sql, *params)
  @db.execute2(sql, params)
end

#transaction(commit = true, &block) ⇒ Object

Can be nested - only the outer call with start a transaction.



147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/sapis/sqlite_layer.rb', line 147

def transaction(commit=true, &block)
  if @db.transaction_active?
    yield
  else
    begin
      @db.transaction do
        yield
        raise Rollback.new if !commit
      end
    rescue Rollback
      # do nothing
    end
  end
end

#update_values(table, values) ⇒ Object

values: the :where key is the where condition



91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/sapis/sqlite_layer.rb', line 91

def update_values(table, values)
  where_sql = values.delete(:where) || 'TRUE'

  set_sql, set_values = values.inject(["", []]) do |(current_set_sql, current_set_values), (column, value)|
    current_set_sql << ', ' if current_set_sql != ''
    current_set_sql << "#{column} = ?"
    current_set_values << value
    [current_set_sql, current_set_values]
  end

  @db.execute("UPDATE #{table} SET #{set_sql} WHERE #{where_sql}", set_values)
end