04100d09316daac2229e5979670148f0cbdc4b30
1 # $Id: back_sqlite.py,v 1.16 2004-03-15 05:50:20 richard Exp $
2 '''Implements a backend for SQLite.
4 See https://pysqlite.sourceforge.net/ for pysqlite info
5 '''
6 __docformat__ = 'restructuredtext'
8 import os, base64, marshal
10 from roundup import hyperdb
11 from roundup.backends import rdbms_common
12 from roundup.backends import locking
13 import sqlite
15 class Database(rdbms_common.Database):
16 # char to use for positional arguments
17 arg = '%s'
19 def sql_open_connection(self):
20 # ensure files are group readable and writable
21 os.umask(0002)
22 db = os.path.join(self.config.DATABASE, 'db')
24 # lock it
25 lockfilenm = db[:-3] + 'lck'
26 self.lockfile = locking.acquire_lock(lockfilenm)
27 self.lockfile.write(str(os.getpid()))
28 self.lockfile.flush()
30 self.conn = sqlite.connect(db=db)
31 self.cursor = self.conn.cursor()
32 try:
33 self.load_dbschema()
34 except sqlite.DatabaseError, error:
35 if str(error) != 'no such table: schema':
36 raise
37 self.init_dbschema()
38 self.cursor.execute('create table schema (schema varchar)')
39 self.cursor.execute('create table ids (name varchar, num integer)')
40 self.cursor.execute('create index ids_name_idx on ids(name)')
41 self.create_version_2_tables()
43 def create_version_2_tables(self):
44 self.cursor.execute('create table otks (otk_key varchar, '
45 'otk_value varchar, otk_time varchar)')
46 self.cursor.execute('create index otks_key_idx on otks(otk_key)')
47 self.cursor.execute('create table sessions (s_key varchar, '
48 's_last_use varchar, s_user varchar)')
49 self.cursor.execute('create index sessions_key_idx on sessions(s_key)')
51 def add_actor_column(self):
52 # update existing tables to have the new actor column
53 tables = self.database_schema['tables']
54 for classname, spec in self.classes.items():
55 if tables.has_key(classname):
56 dbspec = tables[classname]
57 self.update_class(spec, dbspec, force=1, adding_actor=1)
59 def update_class(self, spec, old_spec, force=0, adding_actor=0):
60 ''' Determine the differences between the current spec and the
61 database version of the spec, and update where necessary.
63 If 'force' is true, update the database anyway.
65 SQLite doesn't have ALTER TABLE, so we have to copy and
66 regenerate the tables with the new schema.
67 '''
68 new_has = spec.properties.has_key
69 new_spec = spec.schema()
70 new_spec[1].sort()
71 old_spec[1].sort()
72 if not force and new_spec == old_spec:
73 # no changes
74 return 0
76 if __debug__:
77 print >>hyperdb.DEBUG, 'update_class FIRING'
79 # detect multilinks that have been removed, and drop their table
80 old_has = {}
81 for name,prop in old_spec[1]:
82 old_has[name] = 1
83 if new_has(name) or not isinstance(prop, hyperdb.Multilink):
84 continue
85 # it's a multilink, and it's been removed - drop the old
86 # table. First drop indexes.
87 self.drop_multilink_table_indexes(spec.classname, ml)
88 sql = 'drop table %s_%s'%(spec.classname, prop)
89 if __debug__:
90 print >>hyperdb.DEBUG, 'update_class', (self, sql)
91 self.cursor.execute(sql)
92 old_has = old_has.has_key
94 # now figure how we populate the new table
95 if adding_actor:
96 fetch = ['_activity', '_creation', '_creator']
97 else:
98 fetch = ['_actor', '_activity', '_creation', '_creator']
99 properties = spec.getprops()
100 for propname,x in new_spec[1]:
101 prop = properties[propname]
102 if isinstance(prop, hyperdb.Multilink):
103 if force or not old_has(propname):
104 # we need to create the new table
105 self.create_multilink_table(spec, propname)
106 elif old_has(propname):
107 # we copy this col over from the old table
108 fetch.append('_'+propname)
110 # select the data out of the old table
111 fetch.append('id')
112 fetch.append('__retired__')
113 fetchcols = ','.join(fetch)
114 cn = spec.classname
115 sql = 'select %s from _%s'%(fetchcols, cn)
116 if __debug__:
117 print >>hyperdb.DEBUG, 'update_class', (self, sql)
118 self.cursor.execute(sql)
119 olddata = self.cursor.fetchall()
121 # TODO: update all the other index dropping code
122 self.drop_class_table_indexes(cn, old_spec[0])
124 # drop the old table
125 self.cursor.execute('drop table _%s'%cn)
127 # create the new table
128 self.create_class_table(spec)
130 if olddata:
131 # do the insert of the old data - the new columns will have
132 # NULL values
133 args = ','.join([self.arg for x in fetch])
134 sql = 'insert into _%s (%s) values (%s)'%(cn, fetchcols, args)
135 if __debug__:
136 print >>hyperdb.DEBUG, 'update_class', (self, sql, olddata[0])
137 for entry in olddata:
138 self.cursor.execute(sql, tuple(entry))
140 return 1
142 def sql_close(self):
143 ''' Squash any error caused by us already having closed the
144 connection.
145 '''
146 try:
147 self.conn.close()
148 except sqlite.ProgrammingError, value:
149 if str(value) != 'close failed - Connection is closed.':
150 raise
152 def sql_rollback(self):
153 ''' Squash any error caused by us having closed the connection (and
154 therefore not having anything to roll back)
155 '''
156 try:
157 self.conn.rollback()
158 except sqlite.ProgrammingError, value:
159 if str(value) != 'rollback failed - Connection is closed.':
160 raise
162 def __repr__(self):
163 return '<roundlite 0x%x>'%id(self)
165 def sql_commit(self):
166 ''' Actually commit to the database.
168 Ignore errors if there's nothing to commit.
169 '''
170 try:
171 self.conn.commit()
172 except sqlite.DatabaseError, error:
173 if str(error) != 'cannot commit - no transaction is active':
174 raise
176 def sql_index_exists(self, table_name, index_name):
177 self.cursor.execute('pragma index_list(%s)'%table_name)
178 for entry in self.cursor.fetchall():
179 if entry[1] == index_name:
180 return 1
181 return 0
183 class sqliteClass:
184 def filter(self, search_matches, filterspec, sort=(None,None),
185 group=(None,None)):
186 ''' If there's NO matches to a fetch, sqlite returns NULL
187 instead of nothing
188 '''
189 return filter(None, rdbms_common.Class.filter(self, search_matches,
190 filterspec, sort=sort, group=group))
192 class Class(sqliteClass, rdbms_common.Class):
193 pass
195 class IssueClass(sqliteClass, rdbms_common.IssueClass):
196 pass
198 class FileClass(sqliteClass, rdbms_common.FileClass):
199 pass