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 _drop(self, cursor, table):
70 cursor.execute("DROP TABLE IF EXISTS " + table)
72 def _update(self, cursor, table, values, kvfilter):
73 UPDATE = "UPDATE %(table)s SET %(setval)s %(where)s"
81 setval += "%s%s=:%s" % (sep, k, mk)
84 where = self._build_where(kvfilter, kv)
86 update = UPDATE % {'table': table, 'setval': setval, 'where': where}
87 cursor.execute(update, kv)
89 def _insert(self, cursor, table, values):
90 INSERT = "INSERT INTO %(table)s VALUES(%(values)s)"
96 insert = INSERT % {'table': table, 'values': vals}
97 cursor.execute(insert, values)
99 def _delete(self, cursor, table, kvfilter):
100 DELETE = "DELETE FROM %(table)s %(where)s"
102 where = self._build_where(kvfilter, kv)
103 delete = DELETE % {'table': table, 'where': where}
104 cursor.execute(delete, kv)
106 def _row_to_dict_tree(self, data, row):
112 self._row_to_dict_tree(d2, row[1:])
116 if data[name] is list:
117 data[name].append(value)
120 data[name] = [v, value]
124 def _rows_to_dict_tree(self, rows):
127 self._row_to_dict_tree(data, r)
130 def _load_data(self, table, columns, kvfilter=None):
134 con = sqlite3.connect(self._dbname)
136 self._create(cur, table, columns)
137 rows = self._select(cur, table, kvfilter)
139 except sqlite3.Error, e:
142 self.error("Failed to load data for table %s: [%s]" % (table, e))
147 return self._rows_to_dict_tree(rows)
149 def load_config(self):
151 columns = ['name', 'value']
152 return self._load_data(table, columns)
154 def load_options(self, table, name=None):
157 kvfilter['name'] = name
158 options = self._load_data(table, OPTIONS_COLUMNS, kvfilter)
159 if name and name in options:
163 def save_options(self, table, name, options):
167 con = sqlite3.connect(self._dbname)
169 self._create(cur, table, OPTIONS_COLUMNS)
170 rows = self._select(cur, table, {'name': name},
173 curvals[row[0]] = row[1]
177 self._update(cur, table,
178 {'value': options[opt]},
179 {'name': name, 'option': opt})
181 self._insert(cur, table, (name, opt, options[opt]))
184 except sqlite3.Error, e:
187 self.error("Failed to store config: [%s]" % e)
193 def delete_options(self, table, name, options=None):
194 kvfilter = {'name': name}
196 con = sqlite3.connect(self._dbname)
198 self._create(cur, table, OPTIONS_COLUMNS)
200 self._delete(cur, table, kvfilter)
203 kvfilter['option'] = opt
204 self._delete(cur, table, kvfilter)
206 except sqlite3.Error, e:
209 self.error("Failed to delete from %s: [%s]" % (table, e))
215 def new_unique_data(self, table, data):
218 con = sqlite3.connect(self._dbname)
220 self._create(cur, table, UNIQUE_DATA_COLUMNS)
221 newid = str(uuid.uuid4())
223 self._insert(cur, table, (newid, name, data[name]))
225 except sqlite3.Error, e:
228 cherrypy.log.error("Failed to store %s data: [%s]" % (table, e))
235 def get_unique_data(self, table, uuidval=None, name=None, value=None):
238 kvfilter['uuid'] = uuidval
240 kvfilter['name'] = name
242 kvfilter['value'] = value
243 return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
245 def save_unique_data(self, table, data):
249 con = sqlite3.connect(self._dbname)
251 self._create(cur, table, UNIQUE_DATA_COLUMNS)
254 rows = self._select(cur, table, {'uuid': uid},
262 self._update(cur, table,
263 {'value': datum[name]},
264 {'uuid': uid, 'name': name})
266 self._insert(cur, table, (uid, name, datum[name]))
269 except sqlite3.Error, e:
272 self.error("Failed to store data in %s: [%s]" % (table, e))
278 def del_unique_data(self, table, uuidval):
279 kvfilter = {'uuid': uuidval}
282 con = sqlite3.connect(self._dbname)
284 self._delete(cur, table, kvfilter)
285 except sqlite3.Error, e:
286 self.error("Failed to delete data from %s: [%s]" % (table, e))
291 def reset_data(self, table):
293 con = sqlite3.connect(self._dbname)
295 self._drop(cur, table)
296 self._create(cur, table, UNIQUE_DATA_COLUMNS)
298 except sqlite3.Error, e:
301 self.error("Failed to erase all data from %s: [%s]" % (table, e))
307 class AdminStore(Store):
310 super(AdminStore, self).__init__('admin.config.db')
312 def get_data(self, plugin, idval=None, name=None, value=None):
313 return self.get_unique_data(plugin+"_data", idval, name, value)
315 def save_data(self, plugin, data):
316 return self.save_unique_data(plugin+"_data", data)
318 def new_datum(self, plugin, datum):
319 table = plugin+"_data"
320 return self.new_unique_data(table, datum)
322 def del_datum(self, plugin, idval):
323 table = plugin+"_data"
324 return self.del_unique_data(table, idval)
326 def wipe_data(self, plugin):
327 table = plugin+"_data"
328 self.reset_data(table)
331 class UserStore(Store):
333 def __init__(self, path=None):
334 super(UserStore, self).__init__('user.prefs.db')
336 def save_user_preferences(self, user, options):
337 return self.save_options('users', user, options)
340 class TranStore(Store):
342 def __init__(self, path=None):
343 super(TranStore, self).__init__('transactions.db')