Class: SqliteSqlWrapper

Inherits:
SqlWrapper show all
Defined in:
lib/coopy/sqlite_sql_wrapper.rb

Direct Known Subclasses

ScraperwikiSqlWrapper

Instance Method Summary collapse

Methods inherited from SqlWrapper

#except_primary_key

Constructor Details

#initialize(db) ⇒ SqliteSqlWrapper

Returns a new instance of SqliteSqlWrapper.



4
5
6
7
8
9
10
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 4

def initialize(db)
  @db = db
  @t = nil
  @qt = nil
  @pk = nil
  @info = {}
end

Instance Method Details

#column_names(tbl) ⇒ Object 



98
99
100
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 98

def column_names(tbl)
  columns(tbl).map{|c| part(c,1,"name")}
end

#columns(tbl) ⇒ Object 



92
93
94
95
96
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 92

def columns(tbl)
  tbl = complete_table(tbl)
  @info[tbl] = pragma(tbl,"table_info") unless @info.has_key? tbl
  @info[tbl]
end

#complete_table(tbl) ⇒ Object 



24
25
26
27
28
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 24

def complete_table(tbl)
  @t = tbl unless tbl.nil?
  @t = get_table_names[0] if @t.nil?
  @t
end

#copy_table_structure(rdb, tbl) ⇒ Object

copy the structure of an attached table, along with any indexes



130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 130

def copy_table_structure(rdb,tbl)
  template = "SELECT sql, type from X.sqlite_master WHERE tbl_name = ? ORDER BY type DESC"
  lsql = template.gsub('X',"main")
  rsql = template.gsub('X',quote_with_dots(rdb))
  args = [quote_with_dots(tbl)]
  lschema = sqlite_execute(lsql,args)
  rschema = sqlite_execute(rsql,args)
  if lschema.length>0
    return false
  end
  rschema.each{ |row| sqlite_execute(row[0],[]) }
  true
end

#delete(tbl, cols, vals) ⇒ Object 



53
54
55
56
57
58
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 53

def delete(tbl,cols,vals)
  tbl = quote_table(tbl)
  template = cols.map{|c| quote_column(c) + ' IS ?'}.join(" AND ")
  template = "DELETE FROM #{tbl} WHERE #{template}"
  sqlite_execute(template,vals)
end

#fetch(sql, names) ⇒ Object 



102
103
104
105
106
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 102

def fetch(sql,names)
  sqlite_execute(sql,[]).each do |row|
    yield row
  end
end

#get_table_names ⇒ Object 



20
21
22
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 20

def get_table_names
  sqlite_execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",[]).flatten
end

#insert(tbl, cols, vals) ⇒ Object 



46
47
48
49
50
51
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 46

def insert(tbl,cols,vals)
  tbl = quote_table(tbl)
  template = cols.map{|x| '?'}.join(",")
  template = "INSERT INTO #{tbl} VALUES(#{template})"
  sqlite_execute(template,vals)
end

#part(row, n, name) ⇒ Object 



88
89
90
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 88

def part(row,n,name)
  row[n]
end

#pk_from_unique_index(tbl) ⇒ Object 



119
120
121
122
123
124
125
126
127
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 119

def pk_from_unique_index(tbl)
  pragma(tbl,"index_list").each do |row|
    if part(row,2,"unique").to_s == "1"
      idx = part(row,1,"name")
      return pragma(idx,"index_info").map{|r| part(r,2,"name")}
    end
  end
  nil
end

#pragma(tbl, info) ⇒ Object 



74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 74

def pragma(tbl,info)
  if tbl.include? '.'
    dbname, tbname, *ignore = tbl.split('.')
    dbname = quote_with_dots(dbname)
    tbname = quote_with_dots(tbname)
    query = "PRAGMA #{dbname}.#{info}(#{tbname})"
  else
    tbl = quote_with_dots(tbl)
    query = "PRAGMA #{info}(#{tbl})"
  end
  result = sqlite_execute(query,[])
  result
end

#primary_key(tbl) ⇒ Object 



108
109
110
111
112
113
114
115
116
117
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 108

def primary_key(tbl)
  return @pk unless @pk.nil?
  cols = columns(tbl)
  cols = cols.select{|c| part(c,5,"pk").to_s=="1"}.map{|c| part(c,1,"name")}
  if cols.length == 0
    cols = pk_from_unique_index(tbl)
  end
  @pk = cols if cols.length>0
  cols
end

#quote_column(col) ⇒ Object 



41
42
43
44
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 41

def quote_column(col)
  return col if col.match(/^[a-zA-Z0-9_]+$/)
  quote_with_dots(col)
end

#quote_table(tbl) ⇒ Object 



35
36
37
38
39
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 35

def quote_table(tbl)
  complete_table(tbl)
  return @t if @t.match(/^[a-zA-Z0-9_]+$/)
  quote_with_dots(@t)
end

#quote_with_dots(x) ⇒ Object 



30
31
32
33
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 30

def quote_with_dots(x)
  return x if x.match(/^[a-zA-Z0-9_]+$/)
  x.split('.').map{|p| "`#{p}`"}.join('.')
end

#set_primary_key(lst) ⇒ Object 



12
13
14
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 12

def set_primary_key(lst)
  @pk = lst
end

#sqlite_execute(template, vals) ⇒ Object 



16
17
18
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 16

def sqlite_execute(template,vals)
  return @db.execute(template,*vals)
end

#transaction(&block) ⇒ Object 



69
70
71
72
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 69

def transaction(&block)
  # not yet mapped, not yet used
  block.call
end

#update(tbl, set_cols, set_vals, cond_cols, cond_vals) ⇒ Object 



60
61
62
63
64
65
66
67
 
# File 'lib/coopy/sqlite_sql_wrapper.rb', line 60

def update(tbl,set_cols,set_vals,cond_cols,cond_vals)
  tbl = quote_table(tbl)
  conds = cond_cols.map{|c| quote_column(c) + ' IS ?'}.join(" AND ")
  sets = set_cols.map{|c| quote_column(c) + ' = ?'}.join(", ")
  template = "UPDATE #{tbl} SET #{sets} WHERE #{conds}"
  v = set_vals + cond_vals
  sqlite_execute(template,v)
end