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.



81
82
83
84
85
86
87
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 81

def initialize(swdatatblname, db)
  @swdatatblname = swdatatblname
  @swdatakeys = [ ]
  @swdatatypes = [  ]
  @sqdatatemplate = "" 
  @db = db
end

Instance Method Details

#addnewcolumn(k, vt) ⇒ Object 



137
138
139
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 137

def addnewcolumn(k, vt)
  @db.execute(format("alter table main.`%s` add column `%s` %s", @swdatatblname, k, vt))
end

#buildinitialtable(data) ⇒ Object 



105
106
107
108
109
110
111
112
113
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 105

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(",")
      # used to just add date_scraped in, but without it can't create an empty table
  @db.execute(format("create table main.`%s` (%s)", @swdatatblname, scoldef))
end

#findclosestindex(unique_keys) ⇒ Object 



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 141

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 



201
202
203
204
205
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 201

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.



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 168

def makenewindex(idxname, unique_keys)
  istart = 0
  if idxname
    mnum = re.search("(\d+)$", idxname)
    if mnum
      istart = int(mnum.group(1))
    end
  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 



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 115

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 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/scraperwiki/sqlite_save_info.rb', line 89

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