class Audrey::Engine::SQLite3::Query::Q0
¶ ↑
Audrey::Engine::SQLite3::Query::Q0
Public Class Methods
new(p_engine, p_fquery)
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 8 def initialize(p_engine, p_fquery) @engine = p_engine @dbh = @engine.dbh @fquery = p_fquery set_query_pk() end
Public Instance Methods
aclasses(wheres, params)
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 231 def aclasses(wheres, params) fcs = @fquery.aclasses fcs.any? or return where = [] # loop through aclasses fcs.each do |fc| rs = Audrey::Util.randstr(40) params[rs] = fc where.push ':' + rs end # add to wheres wheres.push 'aclass in (' + where.join(', ') + ')' end
count()
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 207 def count # $tm.hrm subqueries = prepare_subqueries() # sql sql = <<~'SQL' select count(*) as count from objects o, query_rows qr where o.pk = qr.parent and qr.subquery = :sqpk SQL # return count return @dbh.get_first_value(sql, 'sqpk'=>subqueries['objects']) end
each(opts={}) { |row| ... }
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 91 def each(opts={}) itr = iterator(opts) itr.each do |row| yield row['pk'] end ensure itr and itr.close end
hash_pairs(wheres, params)
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 254 def hash_pairs(wheres, params) # $tm.hrm # loop through fields @fquery.fields.each do |k, vals| # key krs = Audrey::Util.randstr(40) params[krs] = k # if we're looking for a null value is_null = false # initialize clause clause = '(hkey=:' + krs + ')' # if specific values are wanted unless [*vals].include?(Audrey::Query::Q0::Defined) send_vals = [] # force to array # Note: just using [*vals] doesn't work because if vals is nil # then [*vals] is an empty array if not vals.is_a?(Array) vals = [vals] end vals.each do |val| if val.nil? is_null = true else vrs = Audrey::Util.randstr(40) params[vrs] = val send_vals.push ':' + vrs end end # ors ors = [] # send_vals if send_vals.any? ors.push 'scalar in (' + send_vals.join(', ') + ')' end # if null if is_null ors.push 'scalar is null' end # add scalar conditions to clause clause += ' and (' + ors.join(') or (') + ')' end # if looking for a null value, add condition in which the parent # element is selected if no such key/value pair exists if is_null null_clause = "((select count(*) from hash_pairs hp where parent=hv.parent and hkey=:#{krs} ) = 0)" clause = "(#{clause}) or #{null_clause}" end # add where clause wheres.push clause end end
iterator(opts={})
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 50 def iterator(opts={}) # $tm.hrm subqueries = prepare_subqueries() exec_opts = {} # sql sql = <<~'SQL' select o.pk from objects o, query_rows qr where o.pk = qr.parent and qr.subquery = :sqpk SQL # options exec_opts['sqpk'] = subqueries['objects'] # limit if @fquery.limit exec_opts['limit'] = @fquery.limit sql += "limit :limit\n" end # offset if @fquery.offset exec_opts['offset'] = @fquery.offset sql += "offset :offset\n" end # stmt and resultset stmt = @dbh.prepare(sql) return stmt.execute(exec_opts) end
select_hash_elements(subqueries)
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 170 def select_hash_elements(subqueries) # $tm.hrm # early exit @fquery.fields.any? or return # build sql and params sql = "select distinct :sqpk as sqpk, hv.parent\nfrom hash_pairs hv\nwhere" wheres = [] params = {} # sub_query_pk subqueries['hash'] = params['sqpk'] = sub_query_pk() # hash values hash_pairs wheres, params # add wheres sql += "\n(" + wheres.join(") and\n(") + ')' # wrap in insert sql = <<~"SQL" insert into query_rows #{sql} SQL # execute @engine.dbh.execute sql, params end
select_objects(subqueries)
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 108 def select_objects(subqueries) # $tm.hrm # query params params = {} subqueries['objects'] = params['sqpk'] = sub_query_pk() # initial sql sql = <<~'SQL' select :sqpk as sqpk, pk from objects where (partition = :partition) SQL # partition pk params['partition'] = @engine.partition # aclasses if (aclasses = @fquery.aclasses) and aclasses.any? wheres = [] aclasses.each do |aclass| rs = Audrey::Util.randstr(40) wheres.push ":#{rs}" params[rs] = aclass end # add wheres sql += " and\n" + ' (aclass in (' + wheres.join(', ') + '))' end # limit to hashes if subqueries['hash'] sql = <<~"SQL" #{sql} and (pk in (select parent from query_rows where subquery=:hashsqpk)) SQL params['hashsqpk'] = subqueries['hash'] end # prepend insert into sql = <<~"SQL" insert into query_rows(subquery, parent) #{sql} SQL # execute @dbh.execute sql, params end
set_query_pk()
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 22 def set_query_pk @qpk = Audrey::Util.randstr(40) sql = 'insert into queries(pk) values(:qpk)' @dbh.execute sql, 'qpk'=>@qpk end
sub_query_pk()
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 35 def sub_query_pk() # $tm.hrm rv = Audrey::Util.randstr(40) sql = 'insert into subqueries(pk, query) values(:sqpk, :qpk)' @dbh.execute sql, 'sqpk'=>rv, 'qpk'=>@qpk return rv end
Private Instance Methods
prepare_subqueries()
click to toggle source
# File lib/audrey/engine/sqlite3/query/q0.rb, line 330 def prepare_subqueries subqueries = {} select_hash_elements subqueries select_objects subqueries return subqueries end