3 # Copyright (C) 2013 Simo Sorce <simo@redhat.com>
5 # see file 'COPYING' for use and warranty information
7 # This program is free software; you can redistribute it and/or modify
8 # it under the terms of the GNU General Public License as published by
9 # the Free Software Foundation, either version 3 of the License, or
10 # (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
22 from ipsilon.util.log import Log
26 OPTIONS_COLUMNS = ['name', 'option', 'value']
27 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
32 def __init__(self, config_name):
33 if config_name not in cherrypy.config:
34 raise NameError('Unknown database type %s' % config_name)
35 self._dbname = cherrypy.config[config_name]
37 def _build_where(self, kvfilter, kvout):
42 kvout[mk] = kvfilter[k]
43 where += "%s %s=:%s" % (sep, k, mk)
47 def _build_select(self, table, kvfilter=None, kvout=None, columns=None):
48 SELECT = "SELECT %(cols)s FROM %(table)s %(where)s"
51 cols = ",".join(columns)
53 if kvfilter is not None:
54 where = self._build_where(kvfilter, kvout)
55 return SELECT % {'table': table, 'cols': cols, 'where': where}
57 def _select(self, cursor, table, kvfilter=None, columns=None):
59 select = self._build_select(table, kvfilter, kv, columns)
60 cursor.execute(select, kv)
61 return cursor.fetchall()
63 def _create(self, cursor, table, columns):
64 CREATE = "CREATE TABLE IF NOT EXISTS %(table)s(%(cols)s)"
65 cols = ",".join(columns)
66 create = CREATE % {'table': table, 'cols': cols}
67 cursor.execute(create)
69 def _update(self, cursor, table, values, kvfilter):
70 UPDATE = "UPDATE %(table)s SET %(setval)s %(where)s"
78 setval += "%s%s=:%s" % (sep, k, mk)
81 where = self._build_where(kvfilter, kv)
83 update = UPDATE % {'table': table, 'setval': setval, 'where': where}
84 cursor.execute(update, kv)
86 def _insert(self, cursor, table, values):
87 INSERT = "INSERT INTO %(table)s VALUES(%(values)s)"
93 insert = INSERT % {'table': table, 'values': vals}
94 cursor.execute(insert, values)
96 def _delete(self, cursor, table, kvfilter):
97 DELETE = "DELETE FROM %(table)s %(where)s"
99 where = self._build_where(kvfilter, kv)
100 delete = DELETE % {'table': table, 'where': where}
101 cursor.execute(delete, kv)
103 def _row_to_dict_tree(self, data, row):
109 self._row_to_dict_tree(d2, row[1:])
113 if data[name] is list:
114 data[name].append(value)
117 data[name] = [v, value]
121 def _rows_to_dict_tree(self, rows):
124 self._row_to_dict_tree(data, r)
127 def _load_data(self, table, columns, kvfilter=None):
131 con = sqlite3.connect(self._dbname)
133 self._create(cur, table, columns)
134 rows = self._select(cur, table, kvfilter)
136 except sqlite3.Error, e:
139 self.error("Failed to load data for table %s: [%s]" % (table, e))
144 return self._rows_to_dict_tree(rows)
146 def load_config(self):
148 columns = ['name', 'value']
149 return self._load_data(table, columns)
151 def load_options(self, table, name=None):
154 kvfilter['name'] = name
155 options = self._load_data(table, OPTIONS_COLUMNS, kvfilter)
156 if name and name in options:
160 def save_options(self, table, name, options):
164 con = sqlite3.connect(self._dbname)
166 self._create(cur, table, OPTIONS_COLUMNS)
167 rows = self._select(cur, table, {'name': name},
170 curvals[row[0]] = row[1]
174 self._update(cur, table,
175 {'value': options[opt]},
176 {'name': name, 'option': opt})
178 self._insert(cur, table, (name, opt, options[opt]))
181 except sqlite3.Error, e:
184 self.error("Failed to store config: [%s]" % e)
190 def delete_options(self, table, name, options=None):
191 kvfilter = {'name': name}
193 con = sqlite3.connect(self._dbname)
195 self._create(cur, table, OPTIONS_COLUMNS)
197 self._delete(cur, table, kvfilter)
200 kvfilter['option'] = opt
201 self._delete(cur, table, kvfilter)
203 except sqlite3.Error, e:
206 self.error("Failed to delete from %s: [%s]" % (table, e))
212 def new_unique_data(self, table, data):
215 con = sqlite3.connect(self._dbname)
217 self._create(cur, table, UNIQUE_DATA_COLUMNS)
218 newid = str(uuid.uuid4())
220 self._insert(cur, table, (newid, name, data[name]))
222 except sqlite3.Error, e:
225 cherrypy.log.error("Failed to store %s data: [%s]" % (table, e))
232 def get_unique_data(self, table, uuidval=None, name=None, value=None):
235 kvfilter['uuid'] = uuidval
237 kvfilter['name'] = name
239 kvfilter['value'] = value
240 return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
242 def save_unique_data(self, table, data):
246 con = sqlite3.connect(self._dbname)
248 self._create(cur, table, UNIQUE_DATA_COLUMNS)
251 rows = self._select(cur, table, {'uuid': uid},
259 self._update(cur, table,
260 {'value': datum[name]},
261 {'uuid': uid, 'name': name})
263 self._insert(cur, table, (uid, name, datum[name]))
266 except sqlite3.Error, e:
269 self.error("Failed to store data in %s: [%s]" % (table, e))
275 def del_unique_data(self, table, uuidval):
276 kvfilter = {'uuid': uuidval}
279 con = sqlite3.connect(self._dbname)
281 self._delete(cur, table, kvfilter)
282 except sqlite3.Error, e:
283 self.error("Failed to delete data from %s: [%s]" % (table, e))
289 class AdminStore(Store):
292 super(AdminStore, self).__init__('admin.config.db')
294 def get_data(self, plugin, idval=None, name=None, value=None):
295 return self.get_unique_data(plugin+"_data", idval, name, value)
297 def save_data(self, plugin, data):
298 return self.save_unique_data(plugin+"_data", data)
300 def new_datum(self, plugin, datum):
301 table = plugin+"_data"
302 return self.new_unique_data(table, datum)
304 def del_datum(self, plugin, idval):
305 table = plugin+"_data"
306 return self.del_unique_data(table, idval)
308 def wipe_data(self, plugin):
309 table = plugin+"_data"
310 # Try to backup old data first, just in case
312 con = sqlite3.connect(self._dbname)
314 cur.execute("DROP TABLE IF EXISTS " + table)
315 self._create(cur, table, UNIQUE_DATA_COLUMNS)
317 except sqlite3.Error, e:
320 cherrypy.log.error("Failed to wipe %s data: [%s]" % (plugin, e))
327 class UserStore(Store):
329 def __init__(self, path=None):
330 super(UserStore, self).__init__('user.prefs.db')
332 def save_user_preferences(self, user, options):
333 return self.save_options('users', user, options)
336 class TranStore(Store):
338 def __init__(self, path=None):
339 super(TranStore, self).__init__('transactions.db')