1 # $Id: back_sqlite.py,v 1.11 2003-11-11 11:19:18 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)')
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 sql_index_exists(self, table_name, index_name):
110 self.cursor.execute('pragma index_list(%s)'%table_name)
111 for entry in self.cursor.fetchall():
112 if entry[1] == index_name:
113 return 1
114 return 0
116 def save_dbschema(self, schema):
117 ''' Save the schema definition that the database currently implements
118 '''
119 s = repr(self.database_schema)
120 self.sql('insert into schema values (%s)', (s,))
122 def load_dbschema(self):
123 ''' Load the schema definition that the database currently implements
124 '''
125 self.cursor.execute('select schema from schema')
126 return eval(self.cursor.fetchone()[0])
128 def save_journal(self, classname, cols, nodeid, journaldate,
129 journaltag, action, params):
130 ''' Save the journal entry to the database
131 '''
132 # make the params db-friendly
133 params = repr(params)
134 entry = (nodeid, journaldate, journaltag, action, params)
136 # do the insert
137 a = self.arg
138 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
139 cols, a, a, a, a, a)
140 if __debug__:
141 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
142 self.cursor.execute(sql, entry)
144 def load_journal(self, classname, cols, nodeid):
145 ''' Load the journal from the database
146 '''
147 # now get the journal entries
148 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
149 self.arg)
150 if __debug__:
151 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
152 self.cursor.execute(sql, (nodeid,))
153 res = []
154 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
155 params = eval(params)
156 res.append((nodeid, date.Date(date_stamp), user, action, params))
157 return res
159 def unserialise(self, classname, node):
160 ''' Decode the marshalled node data
162 SQLite stringifies _everything_... so we need to re-numberificate
163 Booleans and Numbers.
164 '''
165 if __debug__:
166 print >>hyperdb.DEBUG, 'unserialise', classname, node
167 properties = self.getclass(classname).getprops()
168 d = {}
169 for k, v in node.items():
170 # if the property doesn't exist, or is the "retired" flag then
171 # it won't be in the properties dict
172 if not properties.has_key(k):
173 d[k] = v
174 continue
176 # get the property spec
177 prop = properties[k]
179 if isinstance(prop, Date) and v is not None:
180 d[k] = date.Date(v)
181 elif isinstance(prop, Interval) and v is not None:
182 d[k] = date.Interval(v)
183 elif isinstance(prop, Password) and v is not None:
184 p = password.Password()
185 p.unpack(v)
186 d[k] = p
187 elif isinstance(prop, Boolean) and v is not None:
188 d[k] = int(v)
189 elif isinstance(prop, Number) and v is not None:
190 # try int first, then assume it's a float
191 try:
192 d[k] = int(v)
193 except ValueError:
194 d[k] = float(v)
195 else:
196 d[k] = v
197 return d