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/>.
21 from ipsilon.util.log import Log
22 from sqlalchemy import create_engine
23 from sqlalchemy import MetaData, Table, Column, Text
24 from sqlalchemy.sql import select
28 OPTIONS_COLUMNS = ['name', 'option', 'value']
29 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
34 def __init__(self, name):
35 if name not in cherrypy.config:
36 raise NameError('Unknown database %s' % name)
37 engine_name = cherrypy.config[name]
38 if '://' not in engine_name:
39 engine_name = 'sqlite:///' + engine_name
40 self._dbengine = create_engine(engine_name)
46 return self._dbengine.connect()
50 def at(self, *args, **kwargs):
53 return f(self, *args, **kwargs)
59 def __init__(self, db_obj, table, columns, autotable=True, trans=True):
61 self.autotable = autotable
62 self._con = self._db.connection()
63 self._trans = self._con.begin() if trans else None
64 self._table = self._get_table(table, columns)
66 def _get_table(self, name, columns):
67 table = Table(name, MetaData(self._db.engine()))
69 table.append_column(Column(c, Text()))
72 def _where(self, kvfilter):
74 if kvfilter is not None:
76 w = self._table.columns[k] == kvfilter[k]
83 def _columns(self, columns=None):
85 if columns is not None:
88 cols.append(self._table.columns[c])
90 cols = self._table.columns
94 self._trans.rollback()
100 self._table.create(checkfirst=True)
103 self._table.drop(checkfirst=True)
106 def select(self, kvfilter=None, columns=None):
107 return self._con.execute(select(self._columns(columns),
108 self._where(kvfilter)))
111 def insert(self, values):
112 self._con.execute(self._table.insert(values))
115 def update(self, values, kvfilter):
116 self._con.execute(self._table.update(self._where(kvfilter), values))
119 def delete(self, kvfilter):
120 self._con.execute(self._table.delete(self._where(kvfilter)))
125 def __init__(self, config_name):
126 self._db = SqlStore(config_name)
127 self._query = SqlQuery
129 def new_query(self, table, columns=None, autotable=True, autocommit=True):
130 return self._query(self._db, table, columns, autotable, autocommit)
132 def _row_to_dict_tree(self, data, row):
138 self._row_to_dict_tree(d2, row[1:])
142 if data[name] is list:
143 data[name].append(value)
146 data[name] = [v, value]
150 def _rows_to_dict_tree(self, rows):
153 self._row_to_dict_tree(data, r)
156 def load_data(self, table, columns, kvfilter=None):
159 q = self._query(self._db, table, columns, trans=False)
160 rows = q.select(kvfilter)
161 except Exception, e: # pylint: disable=broad-except
162 self.error("Failed to load data for table %s: [%s]" % (table, e))
163 return self._rows_to_dict_tree(rows)
165 def load_config(self):
167 columns = ['name', 'value']
168 return self.load_data(table, columns)
170 def load_options(self, table, name=None):
173 kvfilter['name'] = name
174 options = self.load_data(table, OPTIONS_COLUMNS, kvfilter)
175 if name and name in options:
179 def save_options(self, table, name, options):
183 q = self._query(self._db, table, OPTIONS_COLUMNS)
184 rows = q.select({'name': name}, ['option', 'value'])
186 curvals[row[0]] = row[1]
190 q.update({'value': options[opt]},
191 {'name': name, 'option': opt})
193 q.insert((name, opt, options[opt]))
196 except Exception, e: # pylint: disable=broad-except
199 self.error("Failed to save options: [%s]" % e)
202 def delete_options(self, table, name, options=None):
203 kvfilter = {'name': name}
206 q = self._query(self._db, table, OPTIONS_COLUMNS)
211 kvfilter['option'] = opt
214 except Exception, e: # pylint: disable=broad-except
217 self.error("Failed to delete from %s: [%s]" % (table, e))
220 def new_unique_data(self, table, data):
221 newid = str(uuid.uuid4())
224 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
226 q.insert((newid, name, data[name]))
228 except Exception, e: # pylint: disable=broad-except
231 self.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):
248 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
251 rows = q.select({'uuid': uid}, ['name', 'value'])
258 q.update({'value': datum[name]},
259 {'uuid': uid, 'name': name})
261 q.insert((uid, name, datum[name]))
264 except Exception, e: # pylint: disable=broad-except
267 self.error("Failed to store data in %s: [%s]" % (table, e))
270 def del_unique_data(self, table, uuidval):
271 kvfilter = {'uuid': uuidval}
273 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
275 except Exception, e: # pylint: disable=broad-except
276 self.error("Failed to delete data from %s: [%s]" % (table, e))
278 def reset_data(self, table):
280 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
284 except Exception, e: # pylint: disable=broad-except
287 self.error("Failed to erase all data from %s: [%s]" % (table, e))
290 class AdminStore(Store):
293 super(AdminStore, self).__init__('admin.config.db')
295 def get_data(self, plugin, idval=None, name=None, value=None):
296 return self.get_unique_data(plugin+"_data", idval, name, value)
298 def save_data(self, plugin, data):
299 return self.save_unique_data(plugin+"_data", data)
301 def new_datum(self, plugin, datum):
302 table = plugin+"_data"
303 return self.new_unique_data(table, datum)
305 def del_datum(self, plugin, idval):
306 table = plugin+"_data"
307 return self.del_unique_data(table, idval)
309 def wipe_data(self, plugin):
310 table = plugin+"_data"
311 self.reset_data(table)
314 class UserStore(Store):
316 def __init__(self, path=None):
317 super(UserStore, self).__init__('user.prefs.db')
319 def save_user_preferences(self, user, options):
320 self.save_options('users', user, options)
322 def load_user_preferences(self, user):
323 return self.load_options('users', user)
325 def save_plugin_data(self, plugin, user, options):
326 self.save_options(plugin+"_data", user, options)
328 def load_plugin_data(self, plugin, user):
329 return self.load_options(plugin+"_data", user)
332 class TranStore(Store):
334 def __init__(self, path=None):
335 super(TranStore, self).__init__('transactions.db')