Class: Coopy::SqliteHelper
- Inherits:
-
Object
- Object
- Coopy::SqliteHelper
- Defined in:
- lib/lib/coopy/sqlite_helper.rb
Instance Method Summary collapse
- #_delete(db, name, conds) ⇒ Object
- #alter_columns(db, name, columns) ⇒ Object
- #attach(db, tag, resource_name) ⇒ Object
- #count_rows(db, name) ⇒ Object
- #get_row_ids(db, name) ⇒ Object
- #get_table_names(db) ⇒ Object
-
#initialize ⇒ SqliteHelper
constructor
A new instance of SqliteHelper.
- #insert(db, name, vals) ⇒ Object
- #update(db, name, conds, vals) ⇒ Object
Constructor Details
#initialize ⇒ SqliteHelper
Returns a new instance of SqliteHelper.
7 8 |
# File 'lib/lib/coopy/sqlite_helper.rb', line 7 def initialize end |
Instance Method Details
#_delete(db, name, conds) ⇒ Object
73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
# 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) ⇒ Object
248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 |
# 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 = 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) ⇒ Object
120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
# 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) ⇒ Object
21 22 23 24 25 26 27 28 29 30 |
# 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) ⇒ Object
32 33 34 35 36 37 38 39 40 41 42 |
# 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) ⇒ Object
10 11 12 13 14 15 16 17 18 19 |
# 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) ⇒ Object
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
# 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) ⇒ Object
44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
# 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 |