|
412
|
1 #!/usr/bin/python |
|
|
2 # -*- coding: utf-8 -*- |
|
|
3 |
|
|
4 """ |
|
|
5 SAT: a jabber client |
|
|
6 Copyright (C) 2009, 2010, 2011 Jérôme Poisson (goffi@goffi.org) |
|
|
7 |
|
|
8 This program is free software: you can redistribute it and/or modify |
|
|
9 it under the terms of the GNU General Public License as published by |
|
|
10 the Free Software Foundation, either version 3 of the License, or |
|
|
11 (at your option) any later version. |
|
|
12 |
|
|
13 This program is distributed in the hope that it will be useful, |
|
|
14 but WITHOUT ANY WARRANTY; without even the implied warranty of |
|
|
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
|
|
16 GNU General Public License for more details. |
|
|
17 |
|
|
18 You should have received a copy of the GNU General Public License |
|
|
19 along with this program. If not, see <http://www.gnu.org/licenses/>. |
|
|
20 """ |
|
|
21 |
|
|
22 |
|
|
23 from logging import debug, info, warning, error |
|
|
24 from twisted.enterprise import adbapi |
|
|
25 from twisted.internet import defer |
|
|
26 import os.path |
|
|
27 |
|
|
28 class SqliteStorage(): |
|
|
29 """This class manage storage with Sqlite database""" |
|
|
30 |
|
|
31 |
|
|
32 def __init__(self, db_filename): |
|
|
33 """Connect to the given database |
|
|
34 @param db_filename: full path to the Sqlite database""" |
|
|
35 self.initialized = defer.Deferred() #triggered when memory is fully initialised and ready |
|
|
36 init_defers = [] #list of deferred we have to wait to before initialisation is complete |
|
|
37 self.profiles={} #we keep cache for the profiles (key: profile name, value: profile id) |
|
|
38 |
|
|
39 info(_("Connecting database")) |
|
|
40 new_base = not os.path.exists(db_filename) #do we have to create the database ? |
|
|
41 self.dbpool = adbapi.ConnectionPool("sqlite3", db_filename, check_same_thread=False) |
|
|
42 init_defers.append(self.dbpool.runOperation("PRAGMA foreign_keys = ON").addErrback(lambda x: error(_("Can't activate foreign keys")))) |
|
|
43 if new_base: |
|
|
44 info(_("The database is new, creating the tables")) |
|
|
45 database_creation = [ |
|
|
46 "CREATE TABLE profiles (id INTEGER PRIMARY KEY ASC, name TEXT, UNIQUE (name))", |
|
|
47 "CREATE TABLE historic (id INTEGER PRIMARY KEY ASC, profile_id INTEGER, source TEXT, dest TEXT, source_res TEXT, dest_res TEXT, timestamp DATETIME, message TEXT, FOREIGN KEY(profile_id) REFERENCES profiles(id))", |
|
|
48 "CREATE TABLE param_gen (category TEXT, name TEXT, value TEXT, PRIMARY KEY (category,name))", |
|
|
49 "CREATE TABLE param_ind (category TEXT, name TEXT, profile_id INTEGER, value TEXT, PRIMARY KEY (category,name,profile_id), FOREIGN KEY(profile_id) REFERENCES profiles(id))"] |
|
|
50 for op in database_creation: |
|
|
51 d = self.dbpool.runOperation(op) |
|
|
52 d.addErrback(lambda x: error(_("Error while creating tables in database [QUERY: %s]") % op )) |
|
|
53 init_defers.append(d) |
|
|
54 |
|
|
55 def fillProfileCache(ignore): |
|
|
56 d = self.dbpool.runQuery("SELECT name,id FROM profiles").addCallback(self._profilesCache) |
|
|
57 d.chainDeferred(self.initialized) |
|
|
58 |
|
|
59 defer.DeferredList(init_defers).addCallback(fillProfileCache) |
|
|
60 |
|
|
61 #Profiles |
|
|
62 def _profilesCache(self, profiles_result): |
|
|
63 """Fill the profiles cache |
|
|
64 @param profiles_result: result of the sql profiles query""" |
|
|
65 for profile in profiles_result: |
|
|
66 name, id = profile |
|
|
67 self.profiles[name] = id |
|
|
68 |
|
|
69 def getProfilesList(self): |
|
|
70 """"Return list of all registered profiles""" |
|
|
71 return self.profiles.keys() |
|
|
72 |
|
|
73 def hasProfile(self, profile_name): |
|
|
74 """return True if profile_name exists |
|
|
75 @param profile_name: name of the profile to check""" |
|
|
76 return self.profiles.has_key(profile_name) |
|
|
77 |
|
|
78 def createProfile(self, name): |
|
|
79 """Create a new profile |
|
|
80 @param name: name of the profile |
|
|
81 @return: deferred triggered once profile is actually created""" |
|
|
82 def getProfileId(ignore): |
|
|
83 return self.dbpool.runQuery("SELECT (id) FROM profiles WHERE name = ?", (name,)) |
|
|
84 |
|
|
85 def profile_created(profile_id): |
|
|
86 _id = profile_id[0][0] |
|
|
87 self.profiles[name] = _id #we synchronise the cache |
|
|
88 |
|
|
89 d = self.dbpool.runQuery("INSERT INTO profiles(name) VALUES (?)", (name,)) |
|
|
90 d.addCallback(getProfileId) |
|
|
91 d.addCallback(profile_created) |
|
|
92 d.addErrback(lambda ignore: error(_("Can't create profile %(name)s" % {"name":name}))) |
|
|
93 return d |
|
|
94 |
|
|
95 #Params |
|
|
96 def loadGenParams(self, params_gen): |
|
|
97 """Load general parameters |
|
|
98 @param params_gen: dictionary to fill |
|
|
99 @return: deferred""" |
|
|
100 def fillParams(result): |
|
|
101 params_gen[(category, name)] = value |
|
|
102 debug(_("loading general parameters from database")) |
|
|
103 return self.dbpool.runQuery("SELECT category,name,value FROM param_gen").addCallback(fillParams) |
|
|
104 |
|
|
105 def loadIndParams(self, params_ind, profile): |
|
|
106 """Load general parameters |
|
|
107 @param params_ind: dictionary to fill |
|
|
108 @param profile: a profile which *must* exist |
|
|
109 @return: deferred""" |
|
|
110 def fillParams(result): |
|
|
111 params_ind[profile][(category, name)] = value |
|
|
112 debug(_("loading individual parameters from database")) |
|
|
113 d = self.dbpool.runQuery("SELECT category,name,value FROM param_gen WHERE profile_id=?", self.profiles[profile]) |
|
|
114 d.addCallback(fillParams) |
|
|
115 return d |
|
|
116 |
|
|
117 def setGenParam(self, category, name, value): |
|
|
118 """Save the general parameters in database |
|
|
119 @param category: category of the parameter |
|
|
120 @param name: name of the parameter |
|
|
121 @param value: value to set |
|
|
122 @return: deferred""" |
|
|
123 d = self.dbpool.runQuery("REPLACE INTO param_gen(category,name,value) VALUES (?,?,?)", (category, name, value)) |
|
|
124 d.addErrback(lambda ignore: error(_("Can't set general parameter (%(category)s/%(name)s) in database" % {"category":category, "name":name}))) |
|
|
125 return d |
|
|
126 |
|
|
127 def setIndParam(self, category, name, value, profile): |
|
|
128 """Save the general parameters in database |
|
|
129 @param category: category of the parameter |
|
|
130 @param name: name of the parameter |
|
|
131 @param value: value to set |
|
|
132 @param profile: a profile which *must* exist |
|
|
133 @return: deferred""" |
|
|
134 d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value)) |
|
|
135 d.addErrback(lambda ignore: error(_("Can't set individual parameter (%(category)s/%(name)s) for [%(profile)s] in database" % {"category":category, "name":name, "profile":profile}))) |
|
|
136 return d |
