Class: SQLiteMagic::SqliteSaveInfo
- Inherits:
-
Object
- Object
- SQLiteMagic::SqliteSaveInfo
- Defined in:
- lib/scraperwiki/sqlite_save_info.rb
Instance Method Summary collapse
- #addnewcolumn(k, vt) ⇒ Object
- #buildinitialtable(data) ⇒ Object
- #findclosestindex(unique_keys) ⇒ Object
-
#initialize(swdatatblname, db) ⇒ SqliteSaveInfo
constructor
A new instance of SqliteSaveInfo.
- #insertdata(data) ⇒ Object
-
#makenewindex(idxname, unique_keys) ⇒ Object
increment to next index number every time there is a change, and add the new index before dropping the old one.
- #newcolumns(data) ⇒ Object
- #rebuildinfo ⇒ Object
Constructor Details
#initialize(swdatatblname, db) ⇒ SqliteSaveInfo
Returns a new instance of SqliteSaveInfo.
91 92 93 94 95 96 97 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 91 def initialize(swdatatblname, db) @swdatatblname = swdatatblname @swdatakeys = [ ] @swdatatypes = [ ] @sqdatatemplate = "" @db = db end |
Instance Method Details
#addnewcolumn(k, vt) ⇒ Object
146 147 148 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 146 def addnewcolumn(k, vt) @db.execute(format("alter table main.`%s` add column `%s` %s", @swdatatblname, k, vt)) end |
#buildinitialtable(data) ⇒ Object
115 116 117 118 119 120 121 122 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 115 def buildinitialtable(data) raise "buildinitialtable: no swdatakeys" unless @swdatakeys.length == 0 coldef = self.newcolumns(data) raise "buildinitialtable: no coldef" unless coldef.length > 0 # coldef = coldef[:1] # just put one column in; the rest could be altered -- to prove it's good scoldef = coldef.map { |col| format("`%s` %s", col[0], col[1]) }.join(",") @db.execute(format("create table main.`%s` (%s)", @swdatatblname, scoldef)) end |
#findclosestindex(unique_keys) ⇒ Object
150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 150 def findclosestindex(unique_keys) idxlist = @db.execute(format("PRAGMA main.index_list(`%s`)", @swdatatblname)) # [seq,name,unique] # puts "findclosestindex: idxlist is "+ idxlist.to_s if idxlist.include?('error') return [nil, nil] end uniqueindexes = [ ] for idxel in idxlist if idxel[2] idxname = idxel[1] idxinfo = @db.execute(format("PRAGMA main.index_info(`%s`)", idxname)) # [seqno,cid,name] idxset = idxinfo.map { |a| a[2] }.to_set idxoverlap = idxset.intersection(unique_keys).length uniqueindexes.push([idxoverlap, idxname, idxset]) end end if uniqueindexes.length == 0 return [nil, nil] end uniqueindexes.sort() # puts "uniqueindexes=" + uniqueindexes.to_s return [uniqueindexes[-1][1], uniqueindexes[-1][2]] end |
#insertdata(data) ⇒ Object
211 212 213 214 215 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 211 def insertdata(data) values = @swdatakeys.map { |k| data[k] } res = @db.query(@sqdatatemplate, values) res.close end |
#makenewindex(idxname, unique_keys) ⇒ Object
increment to next index number every time there is a change, and add the new index before dropping the old one.
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 177 def makenewindex(idxname, unique_keys) istart = 0 if idxname #mnum = re.search("(\d+)$", idxname) #if mnum # istart = int(mnum.group(1)) #end istart = idxname.match("(\d+)$").first.to_i rescue 0 end for i in 0..10000 newidxname = format("%s_index%d", @swdatatblname, istart+i) does_exist = @db.get_first_value("select count(*) from main.sqlite_master where name=?", newidxname) if does_exist == 0 break end end res = { "newindex" => newidxname } lres = @db.execute(format("create unique index `%s` on `%s` (%s)", newidxname, @swdatatblname, unique_keys.map { |k| format("`%s`", k) }.join(","))) if lres.include?('error') return lres end if idxname lres = @db.execute(format("drop index main.`%s`", idxname)) if lres.include?('error') if lres['error'] != 'sqlite3.Error: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped' return lres end end res["droppedindex"] = idxname end return res end |
#newcolumns(data) ⇒ Object
124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 124 def newcolumns(data) newcols = [ ] for k, v in data if !@swdatakeys.include?(k) if v != nil #if k[-5:] == "_blob" # vt = "blob" # coerced into affinity none if v.class == Fixnum vt = "integer" elsif v.class == Float vt = "real" else vt = "text" end newcols.push([k, vt]) end end end # puts "newcols=" + newcols.to_s return newcols end |
#rebuildinfo ⇒ Object
99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
# File 'lib/scraperwiki/sqlite_save_info.rb', line 99 def rebuildinfo() does_exist = @db.get_first_value("select count(*) from main.sqlite_master where name=?", @swdatatblname) if does_exist == 0 return false end tblinfo = @db.execute("PRAGMA main.table_info(`%s`)" % @swdatatblname) # puts "tblinfo="+ tblinfo.to_s @swdatakeys = tblinfo.map { |a| a[1] } @swdatatypes = tblinfo.map { |a| a[2] } @sqdatatemplate = format("insert or replace into main.`%s` values (%s)", @swdatatblname, (["?"]*@swdatakeys.length).join(",")) return true end |