class Audrey::Engine::SQLite3
¶ ↑
Audrey::Engine::SQLite3
Constants
- RUBY_SCALAR_TO_SQLITE
Attributes
dbh[R]
partition[RW]
path[R]
preps[R]
transactions[R]
Public Class Methods
new(p_path, p_mode, opts={})
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 19 def initialize(p_path, p_mode, opts={}) # default options opts = {'immediate_commit'=>true}.merge(opts) # set some properties @path = p_path @immediate_commit = opts['immediate_commit'] ? true : false @preps = {} @mode = p_mode # KLUDGE: For now, default to partition 'm' @partition = 'm' # lock file set_lock() # if file exists existed = File.exist?(@path) # open database handle @dbh = get_dbh() # custom functions custom_functions() # create and structure database if the file doesn't exist if not existed require 'audrey/engine/sqlite3/init' Audrey::Engine::SQLite3::Init.build @dbh end # transactions @transactions = [] # initial transaction if @immediate_commit @transactions.push Audrey::Engine::SQLite3::Transaction::AutoCommit.new(self) else @transactions.push Audrey::Engine::SQLite3::Transaction::RC.new(self) end # create stuff just for this session self.class.session_tables @dbh end
Private Class Methods
session_tables(dbh)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 1121 def self.session_tables(dbh) # create temp tables # KLUDGE: query_rows references objects, but apparently you can't reference # from a temp table to a main table. For now just not enforcing referential # integrity. sql = <<~"SQL" create temp table connection(key varchar(5) primary key, val varchar(64)); insert into connection(key, val) values('partition', null); create temp table queries (pk varchar(5) primary key); create temp table subqueries (pk varchar(5) primary key, query not null references queries(pk)); create temp table query_rows ( subquery varchar(5) not null references subqueries(pk) on delete cascade, parent varchar(36) not null, -- references objects(pk), unique(subquery, parent) ); SQL # execute dbh.execute_batch sql end
set_row_scalar(row)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 1104 def self.set_row_scalar(row) # fix scalar if necessary if converter = Audrey::AUDREY_SCALAR_TO_RUBY[row['aclass']] row['scalar'] = converter.call(row['scalar']) end # return return row end
Public Instance Methods
add_relationship(parent_pk, child_pk, opts={})
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 505 def add_relationship(parent_pk, child_pk, opts={}) # ensure prepared statement if not @preps['add_relationship'] sql = <<~SQL insert into relationships(pk, parent, child, ord, hkey) values( :pk, :parent, :child, case when :ord is null then coalesce((select max(ord) from relationships where parent=parent), 0) + 1 else :ord end, :hkey) SQL @preps['add_relationship'] = @dbh.prepare(sql) end # uuid for new relationship record pk = Audrey::Util.uuid # execute begin @preps['add_relationship'].execute( sql, 'pk'=>pk, 'parent'=>parent_pk, 'child'=>child_pk, 'ord'=>opts['ord'], 'hkey'=>opts['hkey'] ); ensure @preps['add_relationship'].reset! end # return return pk end
add_xt(obj_pk, xt_pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 451 def add_xt(obj_pk, xt_pk) # $tm.hrm # prepare insert statement if necessary if not @preps['insert_xt'] sql = <<~SQL insert into objects(pk, partition, hsa, aclass) values(?, (select partition from objects where pk=?), 'h', 'xt') SQL @preps['insert_xt'] ||= @dbh.prepare(sql) end # create xt simple_exec @preps['insert_xt'], xt_pk, obj_pk # associate record @preps['add_xt'] ||= @dbh.prepare('update objects set xt=:xtpk where pk=:pk and partition=:partition') simple_exec @preps['add_xt'], 'xtpk'=>xt_pk, 'pk'=>obj_pk, 'partition'=>@partition end
ancestors(child_pk) { |row| ... }
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 949 def ancestors(child_pk) # sql to add parents if not @preps['ancestors'] sql = <<~'SQL' select * from objects where pk in ( with recursive ancestors(c) as ( values(:child_pk) union select parent from relationships, ancestors where relationships.child=ancestors.c ) select parent from relationships where relationships.child in ancestors ); SQL @preps['ancestors'] = @dbh.prepare(sql) end # loop through results @preps['ancestors'].execute('child_pk'=>child_pk).each do |row| yield row end ensure @preps['ancestors'] and @preps['ancestors'].reset! end
array_each(parent_pk) { |row| ... }
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 889 def array_each(parent_pk) # $tm.hrm # ensure statement handle if not @preps['array_each'] sql = <<~SQL select r.ord as ord, o.* from relationships r, objects o where r.parent=:parent and o.pk=r.child order by r.ord SQL @preps['array_each'] = @dbh.prepare(sql) end # loop through records @preps['array_each'].execute('parent'=>parent_pk).each_hash do |row| yield row end ensure @preps['array_each'] and @preps['array_each'].reset! end
changed_get(pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 784 def changed_get(pk) # $tm.hrm # prepare @preps['changed_get'] ||= @dbh.prepare('select changed from objects where pk=:pk') # execute @preps['changed_get'].execute('pk'=>pk).each_hash do |row| return row['changed'] end ensure @preps['changed_get'] and @preps['changed_get'].reset! end
changed_objects() { |row| ... }
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 997 def changed_objects # $tm.hrm # sql to add parents if not @preps['changed_objects'] sql = <<~'SQL' select * from objects where changed and custom_class(aclass) order by changed SQL @preps['changed_objects'] = @dbh.prepare(sql) end # loop through results @preps['changed_objects'].execute().each do |row| yield row end ensure @preps['changed_objects'] and @preps['changed_objects'].reset! end
changed_set(pk, bool)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 805 def changed_set(pk, bool) # $tm.hrm # set to changed if bool @preps['changed_set_true'] ||= @dbh.prepare('update base_objects set changed=current_timestamp where pk=:pk') simple_exec @preps['changed_set_true'], 'pk'=>pk else @preps['changed_set_false'] ||= @dbh.prepare('update base_objects set changed=null where pk=:pk') simple_exec @preps['changed_set_false'], 'pk'=>pk end end
clear_collection(parent_pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 767 def clear_collection(parent_pk) # $tm.hrm # prepare @preps['clear_collection'] ||= @dbh.prepare('delete from relationships where parent=:parent') # execute simple_exec @preps['clear_collection'], 'parent'=>parent_pk end
close(opts={})
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 190 def close(opts={}) # $tm.hrm # close prepared statements @preps.keys.each do |key| stmt = @preps[key] if stmt and (not stmt.closed?) stmt.close end @preps.delete key end # purge if opts['purge'] purge() end # close database handle if not @dbh.closed? @dbh.close end # unlock file if @lock @lock.flock File::LOCK_UN @lock.close end end
collection_child_pks(parent_pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 825 def collection_child_pks(parent_pk) # $tm.hrm # prepare if not @preps['collection_child_pks'] sql = <<~'SQL' select child from relationships where parent=:parent SQL @preps['collection_child_pks'] ||= @dbh.prepare(sql) end # init rv = [] # get count @preps['collection_child_pks'].execute(parent_pk).each_hash do |row| rv.push row['child'] end # return return rv ensure @preps['collection_child_pks'] and @preps['collection_child_pks'].reset! end
collection_length(parent_pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 860 def collection_length(parent_pk) # $tm.hrm # prepare if not @preps['collection_length'] sql = <<~'SQL' select count(*) as count from relationships where parent=:parent SQL @preps['collection_length'] ||= @dbh.prepare(sql) end # get count @preps['collection_length'].execute(parent_pk).each_hash do |row| return row['count'] end ensure @preps['collection_length'] and @preps['collection_length'].reset! end
commit()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 92 def commit return @transactions[0].commit end
custom_functions()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 228 def custom_functions() #----------------------------------------------------------------------- # partition # @dbh.create_function('current_partition', 0) do |func| func.result = @partition end # # partition #----------------------------------------------------------------------- #----------------------------------------------------------------------- # insert_object_journal # @dbh.create_function('insert_object_journal', 4 ) do |func, partition, root, aclass, scalar, xt| # $tm.hrm # translate scalars to objects for storage in JSON if converter = Audrey::AUDREY_SCALAR_TO_RUBY[aclass] scalar = converter.call(scalar) end # initialize rv = {} rv['fc'] = aclass rv['pt'] = partition # scalar if rv['s'].is_a?(String) rv['s'] = scalar.force_encoding('UTF-8') end # xt if xt rv['xt'] = xt end # root if root == 1 rv['root'] = true end # set return result func.result = JSON.generate(rv) end # # insert_object_journal #----------------------------------------------------------------------- #----------------------------------------------------------------------- # aclass_fco, aclass_isolate # @dbh.create_function('aclass_fco', 1) do |func, aclass| func.result = Audrey::Util.aclass_fco(aclass) ? 1 : 0 end @dbh.create_function('aclass_isolate', 1) do |func, aclass| func.result = Audrey::Util.aclass_isolate(aclass) ? 1 : 0 end # # aclass_fco, aclass_isolate #----------------------------------------------------------------------- #----------------------------------------------------------------------- # graph_field # @dbh.create_function('graph_field', 2) do |func, aclass, hkey| func.result = Audrey::Util.graph_field?(aclass, hkey) ? 1 : 0 end # # graph_field #----------------------------------------------------------------------- #----------------------------------------------------------------------- # custom_class # @dbh.create_function('custom_class', 1) do |func, aclass| func.result = Audrey::Util.custom_class?(aclass) ? 1 : 0 end # # custom_class #----------------------------------------------------------------------- #----------------------------------------------------------------------- # debug # @dbh.create_function('debug', 1) do |func, msg| puts msg func.result = 1 end # # debug #----------------------------------------------------------------------- end
delete_object(pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 427 def delete_object(pk) # $tm.hrm # ensure statement handle if not @preps['delete_object'] sql = <<~'SQL' delete from objects where pk=:pk and partition=:partition SQL @preps['delete_object'] = @dbh.prepare(sql) end # execute simple_exec @preps['delete_object'], 'pk' => pk, 'partition' => @partition end
get_dbh()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 71 def get_dbh # get database handle rv = ::SQLite3::Database.new(@path) # enforce referential integrity rv.execute 'pragma foreign_keys=on' # results as hash rv.results_as_hash = true # return return rv end
get_xt(obj_pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 479 def get_xt(obj_pk) # prepare statement if necessary @preps['existing_xt'] ||= @dbh.prepare('select xt from objects where pk=:pk and partition=:partition') # search @preps['existing_xt'].execute('pk'=>obj_pk, 'partition'=>@partition).each do |row| return row['xt'] end # create xt_pk = Audrey::Util.uuid add_xt obj_pk, xt_pk # return return xt_pk ensure @preps['existing_xt'] and @preps['existing_xt'].reset! end
hash_each(parent_pk) { |key, row_hash_by_key(parent_pk, key)| ... }
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 715 def hash_each(parent_pk) # $tm.hrm rv = nil # loop through keys hash_keys(parent_pk).each do |key| if block_given? yield key, row_hash_by_key(parent_pk, key) else rv ||= [] rv.push [key, row_hash_by_key(parent_pk, key)] end end # return return rv end
hash_element_delete(parent_pk, key)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 624 def hash_element_delete(parent_pk, key) # $tm.hrm # get record record = row_hash_by_key(parent_pk, key) # execute if record if not @preps['delete_hash_key'] sql = <<~'SQL' delete from relationships where parent=:parent and hkey=:hkey SQL @preps['delete_hash_key'] = @dbh.prepare(sql) end simple_exec @preps['delete_hash_key'], 'parent'=>parent_pk, 'hkey'=>key end # return return record end
hash_has_key?(parent_pk, key)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 554 def hash_has_key?(parent_pk, key) # $tm.hrm # puts "parent_pk: #{parent_pk}" # puts "key: #{key}" # prepare if not @preps['hash_has_key'] sql = <<~'SQL' select count(*) as count from relationships where parent=:parent and hkey=:hkey SQL @preps['hash_has_key'] ||= @dbh.prepare(sql) end # get count @preps['hash_has_key'].execute('parent'=>parent_pk, 'hkey'=>key).each_hash do |row| return row['count'] > 0 end ensure @preps['hash_has_key'] and @preps['hash_has_key'].reset! end
hash_keys(parent_pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 741 def hash_keys(parent_pk) # $tm.hrm rv = [] # prepare @preps['hash_keys'] ||= @dbh.prepare('select hkey from relationships where parent=:parent order by ord') # loop through records @preps['hash_keys'].execute('parent'=>parent_pk).each_hash do |row| rv.push row['hkey'] end # return return rv ensure @preps['hash_keys'] and @preps['hash_keys'].reset! end
in_transaction?()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 107 def in_transaction? # loop through transactions @transactions.each do |tr| tr.is_a?(Audrey::Engine::SQLite3::Transaction::RC) and return true end # else not in transaction return false end
insert_object(object, opts={})
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 363 def insert_object(object, opts={}) # $tm.hrm # pk pk = opts['pk'] || Audrey::Util.uuid # if scalar if dfn = Audrey::Engine::SQLite3::RUBY_SCALAR_TO_SQLITE[object.class] if not @preps['insert_scalar'] sql = <<~'SQL' insert into objects(pk, partition, hsa, aclass, scalar) values(:pk, :partition, 's', :aclass, :scalar) SQL @preps['insert_scalar'] = @dbh.prepare(sql) end # insert record simple_exec( @preps['insert_scalar'], 'pk' => pk, 'partition' => @partition, 'aclass' => dfn['aclass'].to_s, 'scalar' => dfn['to_db'].call(object) ); # if hash or array elsif dfn = Audrey::RUBY_OBJECT_TO_AUDREY[object.class] and dfn['nclass'] simple_exec( prepare_insert_collection(), 'pk' => pk, 'partition' => @partition, 'hsa' => dfn['aclass'].hsa, 'aclass' => dfn['aclass'].to_s ); # if custom object elsif object.is_a?(Audrey::Object::Custom) oclass = object.class simple_exec( prepare_insert_collection(), 'pk' => pk, 'partition' => @partition, 'hsa' => oclass.hsa, 'aclass' => oclass.to_s, ); # else unknown else raise 'unknown-object-class: ' + object.class.to_s end # return pk return pk end
object_exists?(pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 919 def object_exists?(pk) # $tm.hrm # ensure statement handle if not @preps['object_exists'] sql = <<~'SQL' select count(*) as count from objects where pk=:pk and partition=:partition SQL @preps['object_exists'] = @dbh.prepare(sql) end @preps['object_exists'].execute('pk'=>pk, 'partition'=>@partition).each do |row| return row['count'] > 0 end ensure @preps['object_exists'] and @preps['object_exists'].reset! end
purge()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 335 def purge # $tm.hrm if @mode.write sql = 'delete from objects where pk in (select pk from fco_not_traced)' @dbh.execute sql end end
q0(p_fquery)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 985 def q0(p_fquery) require 'audrey/engine/sqlite3/query/q0' return Audrey::Engine::SQLite3::Query::Q0.new(self, p_fquery) end
reset_savepoints(ftr)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 169 def reset_savepoints(ftr) found = false @transactions.each do |tr| if tr == ftr found = true end if found tr.start end end end
rollback()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 96 def rollback return @transactions[0].rollback end
root_pk()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 351 def root_pk sql = 'select pk from objects where root and partition=:partition' return @dbh.get_first_value(sql, 'partition'=>@partition) end
row_by_array_index(parent_pk, idx)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 680 def row_by_array_index(parent_pk, idx) # $tm.hrm # ensure statement handle if not @preps['row_by_array_index'] sql = <<~SQL select r.ord as ord, o.* from relationships r, objects o where r.parent=:parent and o.pk=r.child order by r.ord limit 1 offset :offset SQL @preps['row_by_array_index'] = @dbh.prepare(sql) end # get record @preps['row_by_array_index'].execute('parent'=>parent_pk, 'offset'=>idx).each_hash do |row| return self.class.set_row_scalar(row.to_h) end # else return nil return nil ensure @preps['row_by_array_index'] and @preps['row_by_array_index'].reset! end
row_by_pk(pk)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 655 def row_by_pk(pk) # $tm.hrm # ensure prepared statement @preps['row_by_pk'] ||= @dbh.prepare('select * from objects where pk=:pk and partition=:partition') # get record @preps['row_by_pk'].execute('pk'=>pk, 'partition'=>@partition).each_hash do |row| return self.class.set_row_scalar(row.to_h) end # didn't find the object, so return nil return nil ensure @preps['row_by_pk'] and @preps['row_by_pk'].reset! end
row_hash_by_key(parent_pk, hkey)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 586 def row_hash_by_key(parent_pk, hkey) # $tm.hrm # ensure statement handle if not @preps['row_hash_by_key'] sql = <<~SQL select * from objects where pk = (select child from relationships where parent=:parent and hkey=:hkey) and partition=:partition SQL @preps['row_hash_by_key'] = @dbh.prepare(sql) end # params params = {} params['parent'] = parent_pk params['hkey'] = hkey params['partition'] = @partition # get record @preps['row_hash_by_key'].execute(params).each_hash do |row| return self.class.set_row_scalar(row.to_h) end ensure @preps['row_hash_by_key'] and @preps['row_hash_by_key'].reset! end
transaction(opts={}) { |tr| ... }
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 124 def transaction(opts={}) # $tm.hrm tr = Audrey::Engine::SQLite3::Transaction::RC.new(self) @transactions.push tr # block if block_given? begin # yield yield tr # autocommit if necessary if opts['autocommit'] tr.commit end # exit transaction rescue Audrey::Exception::TransactionExit => e unless e.tr == tr raise e end # return out of this method return nil ensure tr.terminate @transactions.pop end # return nil return nil # non-block else return tr end end
Private Instance Methods
prepare_insert_collection()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 1084 def prepare_insert_collection if not @preps['insert_collection'] sql = <<~'SQL' insert into objects(pk, partition, hsa, aclass) values(:pk, :partition, :hsa, :aclass) SQL @preps['insert_collection'] = @dbh.prepare(sql) end return @preps['insert_collection'] end
set_lock()
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 1151 def set_lock() # $tm.hrm # open lock file @lock = File.open(@path + '.lock', File::RDWR|File::CREAT) # exclusive if @mode.write @lock.flock File::LOCK_EX # shared else @lock.flock File::LOCK_SH end end
simple_exec(stmt, *opts)
click to toggle source
# File lib/audrey/engine/sqlite3.rb, line 1034 def simple_exec(stmt, *opts) begin stmt.execute(*opts) ensure stmt.reset! end end