class Coopy::SqliteHelper

Public Class Methods

new() click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 7
def initialize
end

Public Instance Methods

_delete(db,name,conds) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 73
def _delete(db,name,conds)
  q = "DELETE FROM " + _hx_str(db.get_quoted_table_name(name)) + " WHERE "
  lst = Array.new
  _it = ::Rb::RubyIterator.new(conds.keys)
  while(_it.has_next) do
    k = _it._next
    q += " and " if lst.length > 0
    q += db.get_quoted_column_name(k)
    q += " = ?"
    lst.push(conds[k])
  end
  if !db._begin(q,lst,[]) 
    puts "Problem with database delete"
    return false
  end
  db._end
  true
end
alter_columns(db,name,columns) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 248
def alter_columns(db,name,columns)
  not_blank = lambda {|x|
    return false if x == nil || x == "" || x == "null"
    true
  }
  sql = self.fetch_schema(db,name)
  schema = self.split_schema(db,name,sql)
  parts = schema[:parts]
  nparts = Array.new
  new_column_list = Array.new
  ins_column_list = Array.new
  sel_column_list = Array.new
  meta = schema[:columns]
  begin
    _g1 = 0
    _g = columns.length
    while(_g1 < _g) 
      i = _g1
      _g1+=1
      c = columns[i]
      if c.name != nil 
        if c.prev_name != nil 
          sel_column_list.push(c.prev_name)
          ins_column_list.push(c.name)
        end
        orig_type = ""
        orig_primary = false
        if schema[:name2column].include?(c.name) 
          m = schema[:name2column][c.name]
          orig_type = m.type_value
          orig_primary = m.primary
        end
        next_type = orig_type
        next_primary = orig_primary
        if c.props != nil 
          _g2 = 0
          _g3 = c.props
          while(_g2 < _g3.length) 
            p = _g3[_g2]
            _g2+=1
            next_type = p.val if p.name == "type"
            next_primary = "" + _hx_str(p.val.to_s) == "primary" if p.name == "key"
          end
        end
        part = "" + _hx_str(c.name)
        part += " " + _hx_str(next_type) if not_blank.call(next_type)
        part += " PRIMARY KEY" if next_primary
        nparts.push(part)
        new_column_list.push(c.name)
      end
    end
  end
  return false if !self.exec(db,"BEGIN TRANSACTION")
  c1 = self.column_list_sql(ins_column_list)
  tname = db.get_quoted_table_name(name)
  return false if !self.exec(db,"CREATE TEMPORARY TABLE __coopy_backup(" + _hx_str(c1) + ")")
  return false if !self.exec(db,"INSERT INTO __coopy_backup (" + _hx_str(c1) + ") SELECT " + _hx_str(c1) + " FROM " + _hx_str(tname))
  return false if !self.exec(db,"DROP TABLE " + _hx_str(tname))
  return false if !self.exec(db,_hx_str(schema[:preamble]) + "(" + _hx_str(nparts.join(", ")) + ")")
  return false if !self.exec(db,"INSERT INTO " + _hx_str(tname) + " (" + _hx_str(c1) + ") SELECT " + _hx_str(c1) + " FROM __coopy_backup")
  return false if !self.exec(db,"DROP TABLE __coopy_backup")
  return false if !self.exec(db,"COMMIT")
  true
end
attach(db,tag,resource_name) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 120
def attach(db,tag,resource_name)
  tag_present = false
  tag_correct = false
  result = Array.new
  q = "PRAGMA database_list"
  return false if !db._begin(q,nil,["seq","name","file"])
  while(db.read) 
    name = db.get(1)
    if name == tag 
      tag_present = true
      file = db.get(2)
      tag_correct = true if file == resource_name
    end
  end
  db._end
  if tag_present 
    return true if tag_correct
    if !db._begin("DETACH `" + _hx_str(tag) + "`",nil,[]) 
      puts "Failed to detach " + _hx_str(tag)
      return false
    end
    db._end
  end
  if !db._begin("ATTACH ? AS `" + _hx_str(tag) + "`",[resource_name],[]) 
    puts "Failed to attach " + _hx_str(resource_name) + " as " + _hx_str(tag)
    return false
  end
  db._end
  true
end
count_rows(db,name) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 21
def count_rows(db,name)
  q = "SELECT COUNT(*) AS ct FROM " + _hx_str(db.get_quoted_table_name(name))
  return -1 if !db._begin(q,nil,["ct"])
  ct = -1
  while(db.read) 
    ct = db.get(0)
  end
  db._end
  ct
end
get_row_ids(db,name) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 32
def get_row_ids(db,name)
  result = Array.new
  q = "SELECT ROWID AS r FROM " + _hx_str(db.get_quoted_table_name(name)) + " ORDER BY ROWID"
  return nil if !db._begin(q,nil,["r"])
  while(db.read) 
    c = db.get(0)
    result.push(c)
  end
  db._end
  result
end
get_table_names(db) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 10
def get_table_names(db)
  q = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
  return nil if !db._begin(q,nil,["name"])
  names = Array.new
  while(db.read) 
    names.push(db.get(0))
  end
  db._end
  names
end
insert(db,name,vals) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 92
def insert(db,name,vals)
  q = "INSERT INTO " + _hx_str(db.get_quoted_table_name(name)) + " ("
  lst = Array.new
  _it = ::Rb::RubyIterator.new(vals.keys)
  while(_it.has_next) do
    k = _it._next
    q += "," if lst.length > 0
    q += db.get_quoted_column_name(k)
    lst.push(vals[k])
  end
  q += ") VALUES("
  need_comma = false
  _it2 = ::Rb::RubyIterator.new(vals.keys)
  while(_it2.has_next) do
    k1 = _it2._next
    q += "," if need_comma
    q += "?"
    need_comma = true
  end
  q += ")"
  if !db._begin(q,lst,[]) 
    puts "Problem with database insert"
    return false
  end
  db._end
  true
end
update(db,name,conds,vals) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 44
def update(db,name,conds,vals)
  q = "UPDATE " + _hx_str(db.get_quoted_table_name(name)) + " SET "
  lst = Array.new
  _it = ::Rb::RubyIterator.new(vals.keys)
  while(_it.has_next) do
    k = _it._next
    q += ", " if lst.length > 0
    q += db.get_quoted_column_name(k)
    q += " = ?"
    lst.push(vals[k])
  end
  val_len = lst.length
  q += " WHERE "
  _it2 = ::Rb::RubyIterator.new(conds.keys)
  while(_it2.has_next) do
    k1 = _it2._next
    q += " and " if lst.length > val_len
    q += db.get_quoted_column_name(k1)
    q += " IS ?"
    lst.push(conds[k1])
  end
  if !db._begin(q,lst,[]) 
    puts "Problem with database update"
    return false
  end
  db._end
  true
end

Protected Instance Methods

column_list_sql(x) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 153
def column_list_sql(x)
  x.join(",")
end
exec(db,query) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 237
def exec(db,query)
  if !db._begin(query) 
    puts "database problem"
    return false
  end
  db._end
  true
end
fetch_schema(db,name) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 157
def fetch_schema(db,name)
  tname = db.get_quoted_table_name(name)
  query = "select sql from sqlite_master where name = '" + _hx_str(tname) + "'"
  if !db._begin(query,nil,["sql"]) 
    puts "Cannot find schema for table " + _hx_str(tname)
    return nil
  end
  sql = ""
  sql = db.get(0) if db.read
  db._end
  sql
end
split_schema(db,name,sql) click to toggle source
# File lib/lib/coopy/sqlite_helper.rb, line 170
def split_schema(db,name,sql)
  preamble = ""
  parts = Array.new
  double_quote = false
  single_quote = false
  token = ""
  nesting = 0
  begin
    _g1 = 0
    _g = sql.length
    while(_g1 < _g) 
      i = _g1
      _g1+=1
      ch = sql[i]
      if double_quote || single_quote 
        if double_quote 
          double_quote = false if ch == "\""
        end
        if single_quote 
          single_quote = false if ch == "'"
        end
        token += ch
        next
      end
      brk = false
      if ch == "(" 
        nesting+=1
        brk = true if nesting == 1
      elsif ch == ")" 
        nesting-=1
        brk = true if nesting == 0
      end
      if ch == "," 
        brk = true
        if nesting == 1 
        end
      end
      if brk 
        token = token[1,token.length] if token[0] == " "
        if preamble == "" 
          preamble = token
        else 
          parts.push(token)
        end
        token = ""
      else 
        token += ch
      end
    end
  end
  cols = db.get_columns(name)
  name2part = {}
  name2col = {}
  begin
    _g11 = 0
    _g2 = cols.length
    while(_g11 < _g2) 
      i1 = _g11
      _g11+=1
      col = cols[i1]
      name2part[col.name] = parts[i1]
      name2col[col.name] = cols[i1]
    end
  end
  { preamble: preamble, parts: parts, name2part: name2part, columns: cols, name2column: name2col}
end