1 # $Id: back_sqlite.py,v 1.8 2002-12-12 09:31:04 richard 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)')
36 def close(self):
37 ''' Close off the connection.
39 Squash any error caused by us already having closed the
40 connection.
41 '''
42 try:
43 self.conn.close()
44 except sqlite.ProgrammingError, value:
45 if str(value) != 'close failed - Connection is closed.':
46 raise
48 # release the lock too
49 if self.lockfile is not None:
50 locking.release_lock(self.lockfile)
51 if self.lockfile is not None:
52 self.lockfile.close()
53 self.lockfile = None
55 def rollback(self):
56 ''' Reverse all actions from the current transaction.
58 Undo all the changes made since the database was opened or the
59 last commit() or rollback() was performed.
61 Squash any error caused by us having closed the connection (and
62 therefore not having anything to roll back)
63 '''
64 if __debug__:
65 print >>hyperdb.DEBUG, 'rollback', (self,)
67 # roll back
68 try:
69 self.conn.rollback()
70 except sqlite.ProgrammingError, value:
71 if str(value) != 'rollback failed - Connection is closed.':
72 raise
74 # roll back "other" transaction stuff
75 for method, args in self.transactions:
76 # delete temporary files
77 if method == self.doStoreFile:
78 self.rollbackStoreFile(*args)
79 self.transactions = []
81 def __repr__(self):
82 return '<roundlite 0x%x>'%id(self)
84 def sql_fetchone(self):
85 ''' Fetch a single row. If there's nothing to fetch, return None.
86 '''
87 return self.cursor.fetchone()
89 def sql_fetchall(self):
90 ''' Fetch a single row. If there's nothing to fetch, return [].
91 '''
92 return self.cursor.fetchall()
94 def sql_commit(self):
95 ''' Actually commit to the database.
97 Ignore errors if there's nothing to commit.
98 '''
99 try:
100 self.conn.commit()
101 except sqlite.DatabaseError, error:
102 if str(error) != 'cannot commit - no transaction is active':
103 raise
105 def save_dbschema(self, schema):
106 ''' Save the schema definition that the database currently implements
107 '''
108 s = repr(self.database_schema)
109 self.sql('insert into schema values (%s)', (s,))
111 def load_dbschema(self):
112 ''' Load the schema definition that the database currently implements
113 '''
114 self.cursor.execute('select schema from schema')
115 return eval(self.cursor.fetchone()[0])
117 def save_journal(self, classname, cols, nodeid, journaldate,
118 journaltag, action, params):
119 ''' Save the journal entry to the database
120 '''
121 # make the params db-friendly
122 params = repr(params)
123 entry = (nodeid, journaldate, journaltag, action, params)
125 # do the insert
126 a = self.arg
127 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
128 cols, a, a, a, a, a)
129 if __debug__:
130 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
131 self.cursor.execute(sql, entry)
133 def load_journal(self, classname, cols, nodeid):
134 ''' Load the journal from the database
135 '''
136 # now get the journal entries
137 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
138 self.arg)
139 if __debug__:
140 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
141 self.cursor.execute(sql, (nodeid,))
142 res = []
143 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
144 params = eval(params)
145 res.append((nodeid, date.Date(date_stamp), user, action, params))
146 return res
148 def unserialise(self, classname, node):
149 ''' Decode the marshalled node data
151 SQLite stringifies _everything_... so we need to re-numberificate
152 Booleans and Numbers.
153 '''
154 if __debug__:
155 print >>hyperdb.DEBUG, 'unserialise', classname, node
156 properties = self.getclass(classname).getprops()
157 d = {}
158 for k, v in node.items():
159 # if the property doesn't exist, or is the "retired" flag then
160 # it won't be in the properties dict
161 if not properties.has_key(k):
162 d[k] = v
163 continue
165 # get the property spec
166 prop = properties[k]
168 if isinstance(prop, Date) and v is not None:
169 d[k] = date.Date(v)
170 elif isinstance(prop, Interval) and v is not None:
171 d[k] = date.Interval(v)
172 elif isinstance(prop, Password) and v is not None:
173 p = password.Password()
174 p.unpack(v)
175 d[k] = p
176 elif isinstance(prop, Boolean) and v is not None:
177 d[k] = int(v)
178 elif isinstance(prop, Number) and v is not None:
179 # try int first, then assume it's a float
180 try:
181 d[k] = int(v)
182 except ValueError:
183 d[k] = float(v)
184 else:
185 d[k] = v
186 return d