1 # $Id: back_sqlite.py,v 1.10 2003-10-07 07:17:54 anthonybaxter Exp $
2 __doc__ = '''
3 See https://pysqlite.sourceforge.net/ for pysqlite info
4 '''
5 import base64, marshal
6 from roundup.backends.rdbms_common import *
7 from roundup.backends import locking
8 import sqlite
10 class Database(Database):
11 # char to use for positional arguments
12 arg = '%s'
14 def open_connection(self):
15 # ensure files are group readable and writable
16 os.umask(0002)
17 db = os.path.join(self.config.DATABASE, 'db')
19 # lock it
20 lockfilenm = db[:-3] + 'lck'
21 self.lockfile = locking.acquire_lock(lockfilenm)
22 self.lockfile.write(str(os.getpid()))
23 self.lockfile.flush()
25 self.conn = sqlite.connect(db=db)
26 self.cursor = self.conn.cursor()
27 try:
28 self.database_schema = self.load_dbschema()
29 except sqlite.DatabaseError, error:
30 if str(error) != 'no such table: schema':
31 raise
32 self.database_schema = {}
33 self.cursor.execute('create table schema (schema varchar)')
34 self.cursor.execute('create table ids (name varchar, num integer)')
35 self.cursor.execute('create index ids_name_idx on ids(name)')
37 def close(self):
38 ''' Close off the connection.
40 Squash any error caused by us already having closed the
41 connection.
42 '''
43 try:
44 self.conn.close()
45 except sqlite.ProgrammingError, value:
46 if str(value) != 'close failed - Connection is closed.':
47 raise
49 # release the lock too
50 if self.lockfile is not None:
51 locking.release_lock(self.lockfile)
52 if self.lockfile is not None:
53 self.lockfile.close()
54 self.lockfile = None
56 def rollback(self):
57 ''' Reverse all actions from the current transaction.
59 Undo all the changes made since the database was opened or the
60 last commit() or rollback() was performed.
62 Squash any error caused by us having closed the connection (and
63 therefore not having anything to roll back)
64 '''
65 if __debug__:
66 print >>hyperdb.DEBUG, 'rollback', (self,)
68 # roll back
69 try:
70 self.conn.rollback()
71 except sqlite.ProgrammingError, value:
72 if str(value) != 'rollback failed - Connection is closed.':
73 raise
75 # roll back "other" transaction stuff
76 for method, args in self.transactions:
77 # delete temporary files
78 if method == self.doStoreFile:
79 self.rollbackStoreFile(*args)
80 self.transactions = []
82 # clear the cache
83 self.clearCache()
85 def __repr__(self):
86 return '<roundlite 0x%x>'%id(self)
88 def sql_fetchone(self):
89 ''' Fetch a single row. If there's nothing to fetch, return None.
90 '''
91 return self.cursor.fetchone()
93 def sql_fetchall(self):
94 ''' Fetch a single row. If there's nothing to fetch, return [].
95 '''
96 return self.cursor.fetchall()
98 def sql_commit(self):
99 ''' Actually commit to the database.
101 Ignore errors if there's nothing to commit.
102 '''
103 try:
104 self.conn.commit()
105 except sqlite.DatabaseError, error:
106 if str(error) != 'cannot commit - no transaction is active':
107 raise
109 def save_dbschema(self, schema):
110 ''' Save the schema definition that the database currently implements
111 '''
112 s = repr(self.database_schema)
113 self.sql('insert into schema values (%s)', (s,))
115 def load_dbschema(self):
116 ''' Load the schema definition that the database currently implements
117 '''
118 self.cursor.execute('select schema from schema')
119 return eval(self.cursor.fetchone()[0])
121 def save_journal(self, classname, cols, nodeid, journaldate,
122 journaltag, action, params):
123 ''' Save the journal entry to the database
124 '''
125 # make the params db-friendly
126 params = repr(params)
127 entry = (nodeid, journaldate, journaltag, action, params)
129 # do the insert
130 a = self.arg
131 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
132 cols, a, a, a, a, a)
133 if __debug__:
134 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
135 self.cursor.execute(sql, entry)
137 def load_journal(self, classname, cols, nodeid):
138 ''' Load the journal from the database
139 '''
140 # now get the journal entries
141 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
142 self.arg)
143 if __debug__:
144 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
145 self.cursor.execute(sql, (nodeid,))
146 res = []
147 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
148 params = eval(params)
149 res.append((nodeid, date.Date(date_stamp), user, action, params))
150 return res
152 def unserialise(self, classname, node):
153 ''' Decode the marshalled node data
155 SQLite stringifies _everything_... so we need to re-numberificate
156 Booleans and Numbers.
157 '''
158 if __debug__:
159 print >>hyperdb.DEBUG, 'unserialise', classname, node
160 properties = self.getclass(classname).getprops()
161 d = {}
162 for k, v in node.items():
163 # if the property doesn't exist, or is the "retired" flag then
164 # it won't be in the properties dict
165 if not properties.has_key(k):
166 d[k] = v
167 continue
169 # get the property spec
170 prop = properties[k]
172 if isinstance(prop, Date) and v is not None:
173 d[k] = date.Date(v)
174 elif isinstance(prop, Interval) and v is not None:
175 d[k] = date.Interval(v)
176 elif isinstance(prop, Password) and v is not None:
177 p = password.Password()
178 p.unpack(v)
179 d[k] = p
180 elif isinstance(prop, Boolean) and v is not None:
181 d[k] = int(v)
182 elif isinstance(prop, Number) and v is not None:
183 # try int first, then assume it's a float
184 try:
185 d[k] = int(v)
186 except ValueError:
187 d[k] = float(v)
188 else:
189 d[k] = v
190 return d