1 # $Id: back_sqlite.py,v 1.22 2004-03-26 06:38:10 richard Exp $
2 '''Implements a backend for SQLite.
4 See https://pysqlite.sourceforge.net/ for pysqlite info
7 NOTE: we use the rdbms_common table creation methods which define datatypes
8 for the columns, but sqlite IGNORES these specifications.
9 '''
10 __docformat__ = 'restructuredtext'
12 import os, base64, marshal
14 from roundup import hyperdb, date, password
15 from roundup.backends import rdbms_common
16 import sqlite
18 class Database(rdbms_common.Database):
19 # char to use for positional arguments
20 arg = '%s'
22 hyperdb_to_sql_value = {
23 hyperdb.String : str,
24 hyperdb.Date : lambda x: x.serialise(),
25 hyperdb.Link : int,
26 hyperdb.Interval : lambda x: x.serialise(),
27 hyperdb.Password : str,
28 hyperdb.Boolean : int,
29 hyperdb.Number : lambda x: x,
30 }
31 sql_to_hyperdb_value = {
32 hyperdb.String : str,
33 hyperdb.Date : lambda x: date.Date(str(x)),
34 # hyperdb.Link : int, # XXX numeric ids
35 hyperdb.Link : str,
36 hyperdb.Interval : date.Interval,
37 hyperdb.Password : lambda x: password.Password(encrypted=x),
38 hyperdb.Boolean : int,
39 hyperdb.Number : rdbms_common._num_cvt,
40 }
42 def sql_open_connection(self):
43 db = os.path.join(self.config.DATABASE, 'db')
44 conn = sqlite.connect(db=db)
45 cursor = conn.cursor()
46 return (conn, cursor)
48 def open_connection(self):
49 # ensure files are group readable and writable
50 os.umask(0002)
52 (self.conn, self.cursor) = self.sql_open_connection()
54 try:
55 self.load_dbschema()
56 except sqlite.DatabaseError, error:
57 if str(error) != 'no such table: schema':
58 raise
59 self.init_dbschema()
60 self.cursor.execute('create table schema (schema varchar)')
61 self.cursor.execute('create table ids (name varchar, num integer)')
62 self.cursor.execute('create index ids_name_idx on ids(name)')
63 self.create_version_2_tables()
65 def create_version_2_tables(self):
66 self.cursor.execute('create table otks (otk_key varchar, '
67 'otk_value varchar, otk_time integer)')
68 self.cursor.execute('create index otks_key_idx on otks(otk_key)')
69 self.cursor.execute('create table sessions (session_key varchar, '
70 'session_time integer, session_value varchar)')
71 self.cursor.execute('create index sessions_key_idx on '
72 'sessions(session_key)')
74 # full-text indexing store
75 self.cursor.execute('CREATE TABLE __textids (_class varchar, '
76 '_itemid varchar, _prop varchar, _textid integer primary key) ')
77 self.cursor.execute('CREATE TABLE __words (_word varchar, '
78 '_textid integer)')
79 self.cursor.execute('CREATE INDEX words_word_ids ON __words(_word)')
80 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
81 self.cursor.execute(sql, ('__textids', 1))
83 def add_actor_column(self):
84 # update existing tables to have the new actor column
85 tables = self.database_schema['tables']
86 for classname, spec in self.classes.items():
87 if tables.has_key(classname):
88 dbspec = tables[classname]
89 self.update_class(spec, dbspec, force=1, adding_actor=1)
90 # we've updated - don't try again
91 tables[classname] = spec.schema()
93 def update_class(self, spec, old_spec, force=0, adding_actor=0):
94 ''' Determine the differences between the current spec and the
95 database version of the spec, and update where necessary.
97 If 'force' is true, update the database anyway.
99 SQLite doesn't have ALTER TABLE, so we have to copy and
100 regenerate the tables with the new schema.
101 '''
102 new_has = spec.properties.has_key
103 new_spec = spec.schema()
104 new_spec[1].sort()
105 old_spec[1].sort()
106 if not force and new_spec == old_spec:
107 # no changes
108 return 0
110 if __debug__:
111 print >>hyperdb.DEBUG, 'update_class FIRING for', spec.classname
113 # detect multilinks that have been removed, and drop their table
114 old_has = {}
115 for name, prop in old_spec[1]:
116 old_has[name] = 1
117 if new_has(name) or not isinstance(prop, hyperdb.Multilink):
118 continue
119 # it's a multilink, and it's been removed - drop the old
120 # table. First drop indexes.
121 self.drop_multilink_table_indexes(spec.classname, ml)
122 sql = 'drop table %s_%s'%(spec.classname, prop)
123 if __debug__:
124 print >>hyperdb.DEBUG, 'update_class', (self, sql)
125 self.cursor.execute(sql)
126 old_has = old_has.has_key
128 # now figure how we populate the new table
129 if adding_actor:
130 fetch = ['_activity', '_creation', '_creator']
131 else:
132 fetch = ['_actor', '_activity', '_creation', '_creator']
133 properties = spec.getprops()
134 for propname,x in new_spec[1]:
135 prop = properties[propname]
136 if isinstance(prop, hyperdb.Multilink):
137 if not old_has(propname):
138 # we need to create the new table
139 self.create_multilink_table(spec, propname)
140 elif force:
141 tn = '%s_%s'%(spec.classname, propname)
142 # grabe the current values
143 sql = 'select linkid, nodeid from %s'%tn
144 if __debug__:
145 print >>hyperdb.DEBUG, 'update_class', (self, sql)
146 self.cursor.execute(sql)
147 rows = self.cursor.fetchall()
149 # drop the old table
150 self.drop_multilink_table_indexes(spec.classname, propname)
151 sql = 'drop table %s'%tn
152 if __debug__:
153 print >>hyperdb.DEBUG, 'migration', (self, sql)
154 self.cursor.execute(sql)
156 # re-create and populate the new table
157 self.create_multilink_table(spec, propname)
158 sql = '''insert into %s (linkid, nodeid) values
159 (%s, %s)'''%(tn, self.arg, self.arg)
160 for linkid, nodeid in rows:
161 self.cursor.execute(sql, (int(linkid), int(nodeid)))
162 elif old_has(propname):
163 # we copy this col over from the old table
164 fetch.append('_'+propname)
166 # select the data out of the old table
167 fetch.append('id')
168 fetch.append('__retired__')
169 fetchcols = ','.join(fetch)
170 cn = spec.classname
171 sql = 'select %s from _%s'%(fetchcols, cn)
172 if __debug__:
173 print >>hyperdb.DEBUG, 'update_class', (self, sql)
174 self.cursor.execute(sql)
175 olddata = self.cursor.fetchall()
177 # TODO: update all the other index dropping code
178 self.drop_class_table_indexes(cn, old_spec[0])
180 # drop the old table
181 if __debug__:
182 print >>hyperdb.DEBUG, 'update_class "drop table _%s"'%cn
183 self.cursor.execute('drop table _%s'%cn)
185 # create the new table
186 self.create_class_table(spec)
188 if olddata:
189 # do the insert of the old data - the new columns will have
190 # NULL values
191 args = ','.join([self.arg for x in fetch])
192 sql = 'insert into _%s (%s) values (%s)'%(cn, fetchcols, args)
193 if __debug__:
194 print >>hyperdb.DEBUG, 'update_class', (self, sql, olddata[0])
195 for entry in olddata:
196 self.cursor.execute(sql, tuple(entry))
198 return 1
200 def sql_close(self):
201 ''' Squash any error caused by us already having closed the
202 connection.
203 '''
204 try:
205 self.conn.close()
206 except sqlite.ProgrammingError, value:
207 if str(value) != 'close failed - Connection is closed.':
208 raise
210 def sql_rollback(self):
211 ''' Squash any error caused by us having closed the connection (and
212 therefore not having anything to roll back)
213 '''
214 try:
215 self.conn.rollback()
216 except sqlite.ProgrammingError, value:
217 if str(value) != 'rollback failed - Connection is closed.':
218 raise
220 def __repr__(self):
221 return '<roundlite 0x%x>'%id(self)
223 def sql_commit(self):
224 ''' Actually commit to the database.
226 Ignore errors if there's nothing to commit.
227 '''
228 try:
229 self.conn.commit()
230 except sqlite.DatabaseError, error:
231 if str(error) != 'cannot commit - no transaction is active':
232 raise
234 def sql_index_exists(self, table_name, index_name):
235 self.cursor.execute('pragma index_list(%s)'%table_name)
236 for entry in self.cursor.fetchall():
237 if entry[1] == index_name:
238 return 1
239 return 0
241 # old-skool id generation
242 def newid(self, classname):
243 ''' Generate a new id for the given class
244 '''
245 # get the next ID
246 sql = 'select num from ids where name=%s'%self.arg
247 if __debug__:
248 print >>hyperdb.DEBUG, 'newid', (self, sql, classname)
249 self.cursor.execute(sql, (classname, ))
250 newid = int(self.cursor.fetchone()[0])
252 # update the counter
253 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
254 vals = (int(newid)+1, classname)
255 if __debug__:
256 print >>hyperdb.DEBUG, 'newid', (self, sql, vals)
257 self.cursor.execute(sql, vals)
259 # return as string
260 return str(newid)
262 def setid(self, classname, setid):
263 ''' Set the id counter: used during import of database
265 We add one to make it behave like the seqeunces in postgres.
266 '''
267 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
268 vals = (int(setid)+1, classname)
269 if __debug__:
270 print >>hyperdb.DEBUG, 'setid', (self, sql, vals)
271 self.cursor.execute(sql, vals)
273 def create_class(self, spec):
274 rdbms_common.Database.create_class(self, spec)
275 sql = 'insert into ids (name, num) values (%s, %s)'
276 vals = (spec.classname, 1)
277 if __debug__:
278 print >>hyperdb.DEBUG, 'create_class', (self, sql, vals)
279 self.cursor.execute(sql, vals)
281 class sqliteClass:
282 def filter(self, search_matches, filterspec, sort=(None,None),
283 group=(None,None)):
284 ''' If there's NO matches to a fetch, sqlite returns NULL
285 instead of nothing
286 '''
287 return filter(None, rdbms_common.Class.filter(self, search_matches,
288 filterspec, sort=sort, group=group))
290 class Class(sqliteClass, rdbms_common.Class):
291 pass
293 class IssueClass(sqliteClass, rdbms_common.IssueClass):
294 pass
296 class FileClass(sqliteClass, rdbms_common.FileClass):
297 pass