Mercurial > libervia-backend

comparison src/tools/sqlite.py @ 412:62b17854254e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
database integration: first draft - using SQLite as backend /!\ Not finished yet, break execution. SàT CAN'T LAUNCH PROPERLY IN THE CURRENT STATE
author Goffi <goffi@goffi.org>
date Sun, 30 Oct 2011 23:13:40 +0100
parents
children dd4caab17008
comparison
equal deleted inserted replaced
411:b109a79ac72f 412:62b17854254e
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