Class: Rho::Database

Inherits:
Object show all
Defined in:
lib/commonAPI/coreapi/RhoDatabaseApi.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(dbfile, partition) ⇒ Database

maintains a single database connection



36
37
38
39
40
41
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 36

def initialize(dbfile, partition)
  unless @database
    @dbpath = dbfile
    @database = SQLite3.new(dbfile,partition)
  end
end

Class Method Details

.get_value_for_sql_stmt(value, convert_value_to_string = true) ⇒ Object

generates a value for sql statement



243
244
245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 243

def get_value_for_sql_stmt(value, convert_value_to_string=true)
  if value.nil? or value == 'NULL'
    "NULL"
  elsif value.is_a?(String)
    safe_str_escape(value)
  else
    if convert_value_to_string
      safe_str_escape(value)
    else
      "#{value}"
    end
  end
end

.make_where_params(condition, op) ⇒ Object

Raises:

  • (ArgumentError) 


257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 257

def make_where_params(condition,op)
  raise ArgumentError if !condition || !op || op.length == 0
  quests = ""
  vals = []

  condition.each do |key,val|
      if quests.length > 0
          quests << ' ' << op << ' '
      end

      if val.nil?
          quests << "\"#{key}\" IS NULL"
      else
          quests << "\"#{key}\"=?"
				vals << val
      end

  end

  return quests,vals
end

.safe_str_escape(value) ⇒ Object 



187
188
189
190
191
192
193
194
195
196
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 187

def safe_str_escape(value)
  str = str.kind_of?(String) ? value.strip : value.to_s
  # escape string if it not starts and ends with single or double quotes
  is_esacped = 
    (str.start_with?('\'') && str.end_with?('\'')) ||
    (str.start_with?('\"') && str.end_with?('\"')) 
  str = (is_esacped ? str : "'#{str}'")
  # replace quotes in the middle of string
  str.gsub(/(?<!^)'(?!$)/,"''")
end

.select_str(select_arr) ⇒ Object 



209
210
211
212
213
214
215
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 209

def select_str(select_arr)
  select_str = ""
  select_arr.each do |attrib|
    select_str << safe_str_escape(attrib) + ","
  end
  select_str.length > 2 ? select_str[0..select_str.length-2] : select_str
end

.string_from_key_vals(values, delim) ⇒ Object

generates key/value list



233
234
235
236
237
238
239
240
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 233

def string_from_key_vals(values, delim)
  vals = ""
  values.each do |key,value|
    op = value.nil? ? 'is ' : '= '
    vals << " \"#{key}\" #{op} #{get_value_for_sql_stmt(value)} #{delim}"
  end
  vals
end

.string_from_key_vals_set(values, delim) ⇒ Object 



223
224
225
226
227
228
229
230
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 223

def string_from_key_vals_set(values, delim)
  vals = ""
  values.each do |key,value|
    op = '= '
    vals << " \"#{key}\" #{op} #{get_value_for_sql_stmt(value)} #{delim}"
  end
  vals
end

.vals_str(values) ⇒ Object

generates value clause based on hash



218
219
220
221
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 218

def vals_str(values)
  vals = string_from_key_vals_set(values, ",")
  vals[0..vals.length - 2]
end

.where_str(condition) ⇒ Object

generates where clause based on hash



199
200
201
202
203
204
205
206
207
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 199

def where_str(condition)
  where_str = ""
  if condition
    where_str += string_from_key_vals(condition,"and")
    where_str = where_str[0..where_str.length - 5]
  end

  where_str
end

Instance Method Details

#_execute_sql(sql, is_batch, args) ⇒ Object

internal API



169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 169

def _execute_sql(sql, is_batch, args)
  result = []
  if sql
    #puts "RhomDbAdapter: Executing query - #{sql}; #{args}"
    begin
      result = @database.execute( sql, is_batch, args )
      #puts "result : #{result}"
    rescue Exception => e
      puts "exception when running query: #{e}"
      puts "query: #{sql}"
      raise
    end
  end
  result
end

#close ⇒ Object

closes the database if and only if it is open



44
45
46
47
48
49
50
51
52
53
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 44

def close
  if @database
    @database.close
    @database = nil
    @dbpath = nil
  else
    return false
  end
  return true
end

#commitTransaction ⇒ Object Also known as: commit 



71
72
73
74
75
76
77
78
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 71

def commitTransaction
    begin
      @database.commitTransaction
    rescue Exception => e
      puts "exception when commit transaction : #{e}"
      raise
    end
end

#delete_all_from_table(table) ⇒ Object

deletes all rows from a given table



360
361
362
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 360

def delete_all_from_table(table)
  execute_sql "DELETE FROM \"#{table}\""
end

#delete_from_table(table, condition) ⇒ Object

deletes rows from a table which satisfy condition (hash) example usage is the following: delete_from_table(‘object_values’,“object”=>“some-object”) this would execute the following sql: delete from object_values where object=“some-object”

Raises:

  • (ArgumentError) 


352
353
354
355
356
357
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 352

def delete_from_table(table,condition)
  raise ArgumentError if !table
  quests,vals = Database.make_where_params(condition,'AND')
  query = "DELETE FROM \"#{table}\" WHERE #{quests}"
  execute_sql query, vals
end

#delete_table(table) ⇒ Object 



364
365
366
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 364

def delete_table(table)
  execute_sql "DROP TABLE IF EXISTS \"#{table}\""
end

#destroyTable(name) ⇒ Object Also known as: destroy_table

destroy one table



155
156
157
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 155

def destroyTable(name)
  destroyTables(:include => [name])
end

#destroyTables(*args) ⇒ Object Also known as: destroy_tables

deletes all rows from all tables, except list of given tables by recreating db-file and save all other tables arguments - :include, :exclude



162
163
164
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 162

def destroyTables(*args)
    @database.destroyTables args.first[:include], args.first[:exclude]
end

#executeBatchSql(sql, *args) ⇒ Object Also known as: execute_batch_sql 



135
136
137
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 135

def executeBatchSql(sql, *args)
  _execute_sql(sql, true, args)
end

#executeSql(sql, *args) ⇒ Object Also known as: execute_sql

execute a sql statement optionally, disable the factory processing which returns the result array directly



132
133
134
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 132

def executeSql(sql, *args)
  _execute_sql(sql, false, args)
end

#export ⇒ Object 



111
112
113
114
115
116
117
118
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 111

def export
 begin
@database.export
 rescue Exception => e
puts "exception when export database: #{e}"
raise
    end
end

#import(zipName) ⇒ Object 



120
121
122
123
124
125
126
127
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 120

def import(zipName)
 begin
@database.import(zipName)
 rescue Exception => e
puts "exception when import database: #{e}"
raise
 end
end

#insert_into_table(table = nil, values = nil, excludes = nil) ⇒ Object

inserts a single row into the database takes the table name and values (hash) as arguments exmaple usage is the following: insert_into_table(‘object_values, “source_id”=>1,“object”=>“some-object”,“update_type”=>’delete’) this would execute the following sql: insert into object_values (source_id,object,update_type) values (1,‘some-object’,‘delete’);

Raises:

  • (ArgumentError) 


318
319
320
321
322
323
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 318

def insert_into_table(table=nil,values=nil, excludes=nil)
  raise ArgumentError if !table
  cols,quests,vals = make_insert_params(values, excludes)
  query = "INSERT INTO \"#{table}\" (#{cols}) VALUES (#{quests})"
  execute_sql query, vals
end

#isTableExist(table_name) ⇒ Object Also known as: table_exist? 



149
150
151
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 149

def isTableExist(table_name)
  @database.isTableExist(table_name)
end

#isUiWaitForDb ⇒ Object Also known as: is_ui_waitfordb 



56
57
58
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 56

def isUiWaitForDb
    @database.isUiWaitForDb
end

#lockDb ⇒ Object Also known as: lock_db 



91
92
93
94
95
96
97
98
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 91

def lockDb
    begin
      @database.lockDb
    rescue Exception => e
      puts "exception when lockDb: #{e}"
      raise
    end
end

#make_insert_params(values, excludes) ⇒ Object

Raises:

  • (ArgumentError) 


325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 325

def make_insert_params(values, excludes)
  raise ArgumentError if !values

  cols = ""
  quests = ""
  vals = []

  values.each do |key,val|
      next if excludes && excludes[key]
      if cols.length > 0
          cols << ','
          quests << ','
      end

      cols << "\"#{key}\""
      quests << '?'
      vals << val
  end

  return cols,quests,vals
end

#make_set_params(values) ⇒ Object

Raises:

  • (ArgumentError) 


390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 390

def make_set_params(values)
  raise ArgumentError if !values

  quests = ""
  vals = []

  values.each do |key,val|
      if quests.length > 0
          quests << ','
      end

      quests << "\"#{key}\"=?"
      vals << val
  end

  return quests,vals
end

#rollbackTransaction ⇒ Object Also known as: rollback 



81
82
83
84
85
86
87
88
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 81

def rollbackTransaction
    begin
      @database.rollbackTransaction
    rescue Exception => e
      puts "exception when rollback transaction : #{e}"
      raise
    end
end

#select_from_table(table = nil, columns = nil, condition = nil, params = nil) ⇒ Object

support for select statements this function takes table name, columns (as a comma-separated list), condition (as a hash), and params (as a hash) example usage is the following: select_from_table(‘object_values’, ‘*’, “source_id”=>2,“update_type”=>‘query’,

{"order by"=>'object'})

this would return all columns where source_id = 2 and update_type = ‘query’ ordered by the “object” column

Raises:

  • (ArgumentError) 


290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 290

def select_from_table(table=nil,columns=nil,condition=nil,params=nil)

  raise ArgumentError if !table || !columns
  query = nil
  vals = []

  if condition
      quests,vals = Database.make_where_params(condition,'AND')
      if params and params['distinct']
          query = "SELECT DISTINCT #{columns} FROM \"#{table}\" WHERE #{quests}"
      elsif params and params['order by']
          query = "SELECT #{columns} FROM \"#{table}\" WHERE #{quests} ORDER BY #{params['order by']}"
      else
          query = "SELECT #{columns} FROM \"#{table}\" WHERE #{quests}"
      end
  else
      query = "SELECT #{columns} FROM \"#{table}\""
  end

  execute_sql query, vals
end

#setDoNotBackupAttribute(attr = true) ⇒ Object Also known as: set_do_not_bakup_attribute 



141
142
143
144
145
146
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 141

def setDoNotBackupAttribute( attr = true )
  if Rho::System.platform == Rho::System.PLATFORM_IOS
      Rho::System.setDoNotBackupAttribute(@dbpath, attr)
      Rho::System.setDoNotBackupAttribute(@dbpath+'.version', attr)
  end
end

#startTransaction ⇒ Object Also known as: start_transaction 



61
62
63
64
65
66
67
68
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 61

def startTransaction
    begin
      @database.startTransaction
    rescue Exception => e
      puts "exception when startTransaction: #{e}"
      raise
    end
end

#unlockDb ⇒ Object Also known as: unlock_db 



101
102
103
104
105
106
107
108
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 101

def unlockDb
    begin
      @database.unlockDb
    rescue Exception => e
      puts "exception when unlockDb: #{e}"
      raise
    end
end

#update_into_table(table = nil, values = nil, condition = nil) ⇒ Object

updates values (hash) in a given table which satisfy condition (hash) example usage is the following: update_into_table(‘object_values’,“value”=>“Electronics”,“attrib”=>“industry”) this executes the following sql: update table object_values set value=‘Electronics’ where object=‘some-object’ and attrib=‘industry’;

Raises:

  • (ArgumentError) 


373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
 
# File 'lib/commonAPI/coreapi/RhoDatabaseApi.rb', line 373

def update_into_table(table=nil,values=nil,condition=nil)
  raise ArgumentError if !table || !values
  query = nil
  vals = []
  if condition
      quests_set, vals_set = make_set_params(values)
      quests_where,vals_where = Database.make_where_params(condition,'AND')
      query = "UPDATE \"#{table}\" SET #{quests_set} WHERE #{quests_where}"
      vals = vals_set + vals_where
  else
      quests, vals = make_set_params(values)
      query = "UPDATE \"#{table}\" SET #{quests}"
  end

  execute_sql query, vals
end