annotate src/tools/sqlite.py @ 419:6c167a2e04b8

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