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