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