Class: LWAC::SQLite3DatabaseConnection

Inherits:
DatabaseConnection show all
Defined in:
lib/lwac/server/db_conn.rb

Overview

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config = {}) ⇒ SQLite3DatabaseConnection

Create a new connection to a database at dbpath.



219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/lwac/server/db_conn.rb', line 219

def initialize(config = {})

  begin
    require 'sqlite3'
  rescue LoadError
    $log.fatal "Your current configuration is trying to use the 'sqlite3' gem, but it is not installed."
    $log.fatal "To install, run 'gem install sqlite3 --version \"~> 1.3\"'"
    raise "Gem not found."
  end


  raise "SQLite3 database not found" if not File.exist?( config[:filename].to_s )

  @transaction        = false
  @transaction_limit  = config[:transaction_limit] || 0
  @transaction_count  = 0
  connect( config[:filename] )
  configure( config[:pragma] || {} )
end

Class Method Details

.create_database(config) ⇒ Object

Create database



316
317
318
319
320
321
322
323
324
325
326
327
328
 
# File 'lib/lwac/server/db_conn.rb', line 316

def self.create_database( config )

  begin
    require 'sqlite3'
  rescue LoadError
    $log.fatal "Your current configuration is trying to use the 'sqlite3' gem, but it is not installed."
    $log.fatal "To install, run 'gem install sqlite3 --version \"~> 1.3\"'"
    raise "Gem not found."
  end

  SQLite3::Database.new(config[:filename]) do |db|
  end
end

.database_exists?(config) ⇒ Boolean

Check database exists

Returns:

  • (Boolean) 


331
332
333
334
 
# File 'lib/lwac/server/db_conn.rb', line 331

def self.database_exists?( config )
  # TODO: check it's a database, not just some random file :-)
  File.exist?(config[:filename]) and not File.directory?(config[:filename])
end

Instance Method Details

#close ⇒ Object

Disconnect from the database.



240
241
242
243
 
# File 'lib/lwac/server/db_conn.rb', line 240

def close
  end_transaction if @transaction
  @db.close
end

#delete(table_name, where_conditions = "") ⇒ Object

Delete all items from a table



285
286
287
288
 
# File 'lib/lwac/server/db_conn.rb', line 285

def delete(table_name, where_conditions = "")
  where_conditions = "where #{where_conditions}" if where_conditions.length > 0
  return execute("delete from `#{table_name}` #{where_conditions};")
end

#execute(sql, trans = true) ⇒ Object

Execute a raw SQL statement Set trans = false to force and disable transactions



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
 
# File 'lib/lwac/server/db_conn.rb', line 293

def execute(sql, trans=true)
  start_transaction if trans
  end_transaction if @transaction and not trans 

  # Return if no sql given
  return unless sql

  $log.debug "SQLite3: #{sql}"


  # run the query
  #puts "<#{sql.split()[0]}, #{trans}, #{@transaction}>"
  res = @db.execute(sql)
  @transaction_count += 1 if @transaction

  # end the transaction if we have called enough statements
  end_transaction if @transaction_count > @transaction_limit

  return res
end

#insert(table_name, value_hash) ⇒ Object

Run an SQL insert call on a given table, with a hash of data.



246
247
248
249
250
251
252
253
 
# File 'lib/lwac/server/db_conn.rb', line 246

def insert(table_name, value_hash)
  raise "Attempt to insert 0 values into table #{table_name}" if value_hash.length == 0

  escaped_values = [] 
  value_hash.each{|k, v| escaped_values << escape(v) }

  return execute("insert into `#{table_name}` (#{value_hash.keys.join(",")}) values (#{escaped_values.join(",")});")
end

#select(table_name, fields_list, where_conditions = "") ⇒ Object

Select certain fields from a database, with certain where field == value.

Returns a record set (SQlite3)

table_name is the name of the table from which to select. fields_list is an array of fields to return in the record set where_conditions is a string of where conditions. Careful to escape!!



278
279
280
281
 
# File 'lib/lwac/server/db_conn.rb', line 278

def select(table_name, fields_list, where_conditions = "")
  where_conditions = "where #{where_conditions}" if where_conditions.length > 0
  return execute("select #{fields_list.join(",")} from `#{table_name}` #{where_conditions};")
end

#update(table_name, value_hash, where_conditions = "") ⇒ Object

Run an SQL insert call on a given table, with a hash of data.



257
258
259
260
261
262
263
264
265
266
267
268
 
# File 'lib/lwac/server/db_conn.rb', line 257

def update(table_name, value_hash, where_conditions = "")
  # Compute the WHERE clause.
  where_conditions = "where #{where_conditions}" if where_conditions.length > 0

  # Work out the SET clause
  escaped_values = []
  value_hash.each{|k, v| 
    escaped_values << "#{k}='#{escape(v)}'" 
  }

  return execute("update `#{table_name}` set #{escaped_values.join(", ")} #{where_conditions};")
end