Class: SQLiteMagic::SqliteSaveInfo

Inherits:
Object
  • Object
show all
Defined in:
lib/scraperwiki/sqlite_save_info.rb

Instance Method Summary collapse

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