Mercurial > p > roundup > code

comparison roundup/backends/back_sqlite.py @ 2514:091711fb2f8c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial logging integration: replace all debug prints with logging calls... ...clean up and replace some with info() logs.
author Richard Jones <richard@users.sourceforge.net>
date Fri, 02 Jul 2004 05:22:09 +0000
parents 31cb1014300c
children a9e1fff1e793
comparison
equal deleted inserted replaced
2512:f5542d92307a 2514:091711fb2f8c
1 # $Id: back_sqlite.py,v 1.29 2004-05-28 01:09:11 richard Exp $ 1 # $Id: back_sqlite.py,v 1.30 2004-07-02 05:22:09 richard Exp $
2 '''Implements a backend for SQLite. 2 '''Implements a backend for SQLite.
3 3
4 See https://pysqlite.sourceforge.net/ for pysqlite info 4 See https://pysqlite.sourceforge.net/ for pysqlite info
5 5
6 6
53 '''Open a standard, non-autocommitting connection. 53 '''Open a standard, non-autocommitting connection.
54 54
55 pysqlite will automatically BEGIN TRANSACTION for us. 55 pysqlite will automatically BEGIN TRANSACTION for us.
56 ''' 56 '''
57 db = os.path.join(self.config.DATABASE, 'db') 57 db = os.path.join(self.config.DATABASE, 'db')
58 self.config.logging.getLogger('hyperdb').info('open database %r'%db)
58 conn = sqlite.connect(db=db) 59 conn = sqlite.connect(db=db)
59 # set a 30 second timeout (extraordinarily generous) for handling 60 # set a 30 second timeout (extraordinarily generous) for handling
60 # locked database 61 # locked database
61 conn.db.sqlite_busy_timeout(30 * 1000) 62 conn.db.sqlite_busy_timeout(30 * 1000)
62 cursor = conn.cursor() 63 cursor = conn.cursor()
72 self.load_dbschema() 73 self.load_dbschema()
73 except sqlite.DatabaseError, error: 74 except sqlite.DatabaseError, error:
74 if str(error) != 'no such table: schema': 75 if str(error) != 'no such table: schema':
75 raise 76 raise
76 self.init_dbschema() 77 self.init_dbschema()
77 self.cursor.execute('create table schema (schema varchar)') 78 self.sql('create table schema (schema varchar)')
78 self.cursor.execute('create table ids (name varchar, num integer)') 79 self.sql('create table ids (name varchar, num integer)')
79 self.cursor.execute('create index ids_name_idx on ids(name)') 80 self.sql('create index ids_name_idx on ids(name)')
80 self.create_version_2_tables() 81 self.create_version_2_tables()
81 82
82 def create_version_2_tables(self): 83 def create_version_2_tables(self):
83 self.cursor.execute('create table otks (otk_key varchar, ' 84 self.sql('create table otks (otk_key varchar, '
84 'otk_value varchar, otk_time integer)') 85 'otk_value varchar, otk_time integer)')
85 self.cursor.execute('create index otks_key_idx on otks(otk_key)') 86 self.sql('create index otks_key_idx on otks(otk_key)')
86 self.cursor.execute('create table sessions (session_key varchar, ' 87 self.sql('create table sessions (session_key varchar, '
87 'session_time integer, session_value varchar)') 88 'session_time integer, session_value varchar)')
88 self.cursor.execute('create index sessions_key_idx on ' 89 self.sql('create index sessions_key_idx on '
89 'sessions(session_key)') 90 'sessions(session_key)')
90 91
91 # full-text indexing store 92 # full-text indexing store
92 self.cursor.execute('CREATE TABLE __textids (_class varchar, ' 93 self.sql('CREATE TABLE __textids (_class varchar, '
93 '_itemid varchar, _prop varchar, _textid integer primary key) ') 94 '_itemid varchar, _prop varchar, _textid integer primary key) ')
94 self.cursor.execute('CREATE TABLE __words (_word varchar, ' 95 self.sql('CREATE TABLE __words (_word varchar, '
95 '_textid integer)') 96 '_textid integer)')
96 self.cursor.execute('CREATE INDEX words_word_ids ON __words(_word)') 97 self.sql('CREATE INDEX words_word_ids ON __words(_word)')
97 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg) 98 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
98 self.cursor.execute(sql, ('__textids', 1)) 99 self.sql(sql, ('__textids', 1))
99 100
100 def add_new_columns_v2(self): 101 def add_new_columns_v2(self):
101 # update existing tables to have the new actor column 102 # update existing tables to have the new actor column
102 tables = self.database_schema['tables'] 103 tables = self.database_schema['tables']
103 for classname, spec in self.classes.items(): 104 for classname, spec in self.classes.items():
122 old_spec[1].sort() 123 old_spec[1].sort()
123 if not force and new_spec == old_spec: 124 if not force and new_spec == old_spec:
124 # no changes 125 # no changes
125 return 0 126 return 0
126 127
127 if __debug__: 128 self.config.logging.getLogger('hyperdb').info('update_class %s'%spec.classname)
128 print >>hyperdb.DEBUG, 'update_class FIRING for', spec.classname
129 129
130 # detect multilinks that have been removed, and drop their table 130 # detect multilinks that have been removed, and drop their table
131 old_has = {} 131 old_has = {}
132 for name, prop in old_spec[1]: 132 for name, prop in old_spec[1]:
133 old_has[name] = 1 133 old_has[name] = 1
135 continue 135 continue
136 # it's a multilink, and it's been removed - drop the old 136 # it's a multilink, and it's been removed - drop the old
137 # table. First drop indexes. 137 # table. First drop indexes.
138 self.drop_multilink_table_indexes(spec.classname, ml) 138 self.drop_multilink_table_indexes(spec.classname, ml)
139 sql = 'drop table %s_%s'%(spec.classname, prop) 139 sql = 'drop table %s_%s'%(spec.classname, prop)
140 if __debug__: 140 self.sql(sql)
141 print >>hyperdb.DEBUG, 'update_class', (self, sql)
142 self.cursor.execute(sql)
143 old_has = old_has.has_key 141 old_has = old_has.has_key
144 142
145 # now figure how we populate the new table 143 # now figure how we populate the new table
146 if adding_v2: 144 if adding_v2:
147 fetch = ['_activity', '_creation', '_creator'] 145 fetch = ['_activity', '_creation', '_creator']
156 self.create_multilink_table(spec, propname) 154 self.create_multilink_table(spec, propname)
157 elif force: 155 elif force:
158 tn = '%s_%s'%(spec.classname, propname) 156 tn = '%s_%s'%(spec.classname, propname)
159 # grabe the current values 157 # grabe the current values
160 sql = 'select linkid, nodeid from %s'%tn 158 sql = 'select linkid, nodeid from %s'%tn
161 if __debug__: 159 self.sql(sql)
162 print >>hyperdb.DEBUG, 'update_class', (self, sql)
163 self.cursor.execute(sql)
164 rows = self.cursor.fetchall() 160 rows = self.cursor.fetchall()
165 161
166 # drop the old table 162 # drop the old table
167 self.drop_multilink_table_indexes(spec.classname, propname) 163 self.drop_multilink_table_indexes(spec.classname, propname)
168 sql = 'drop table %s'%tn 164 sql = 'drop table %s'%tn
169 if __debug__: 165 self.sql(sql)
170 print >>hyperdb.DEBUG, 'migration', (self, sql)
171 self.cursor.execute(sql)
172 166
173 # re-create and populate the new table 167 # re-create and populate the new table
174 self.create_multilink_table(spec, propname) 168 self.create_multilink_table(spec, propname)
175 sql = '''insert into %s (linkid, nodeid) values 169 sql = '''insert into %s (linkid, nodeid) values
176 (%s, %s)'''%(tn, self.arg, self.arg) 170 (%s, %s)'''%(tn, self.arg, self.arg)
177 for linkid, nodeid in rows: 171 for linkid, nodeid in rows:
178 self.cursor.execute(sql, (int(linkid), int(nodeid))) 172 self.sql(sql, (int(linkid), int(nodeid)))
179 elif old_has(propname): 173 elif old_has(propname):
180 # we copy this col over from the old table 174 # we copy this col over from the old table
181 fetch.append('_'+propname) 175 fetch.append('_'+propname)
182 176
183 # select the data out of the old table 177 # select the data out of the old table
184 fetch.append('id') 178 fetch.append('id')
185 fetch.append('__retired__') 179 fetch.append('__retired__')
186 fetchcols = ','.join(fetch) 180 fetchcols = ','.join(fetch)
187 cn = spec.classname 181 cn = spec.classname
188 sql = 'select %s from _%s'%(fetchcols, cn) 182 sql = 'select %s from _%s'%(fetchcols, cn)
189 if __debug__: 183 self.sql(sql)
190 print >>hyperdb.DEBUG, 'update_class', (self, sql)
191 self.cursor.execute(sql)
192 olddata = self.cursor.fetchall() 184 olddata = self.cursor.fetchall()
193 185
194 # TODO: update all the other index dropping code 186 # TODO: update all the other index dropping code
195 self.drop_class_table_indexes(cn, old_spec[0]) 187 self.drop_class_table_indexes(cn, old_spec[0])
196 188
197 # drop the old table 189 # drop the old table
198 if __debug__: 190 self.sql('drop table _%s'%cn)
199 print >>hyperdb.DEBUG, 'update_class "drop table _%s"'%cn
200 self.cursor.execute('drop table _%s'%cn)
201 191
202 # create the new table 192 # create the new table
203 self.create_class_table(spec) 193 self.create_class_table(spec)
204 194
205 if olddata: 195 if olddata:
218 # do the insert of the old data - the new columns will have 208 # do the insert of the old data - the new columns will have
219 # NULL values 209 # NULL values
220 args = ','.join([self.arg for x in inscols]) 210 args = ','.join([self.arg for x in inscols])
221 cols = ','.join(inscols) 211 cols = ','.join(inscols)
222 sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args) 212 sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args)
223 if __debug__:
224 print >>hyperdb.DEBUG, 'update_class', (self, sql, olddata[0])
225 for entry in olddata: 213 for entry in olddata:
226 d = [] 214 d = []
227 for name in inscols: 215 for name in inscols:
228 # generate the new value for the Interval int column 216 # generate the new value for the Interval int column
229 if name.endswith('_int__'): 217 if name.endswith('_int__'):
235 elif entry.has_key(name): 223 elif entry.has_key(name):
236 v = entry[name] 224 v = entry[name]
237 else: 225 else:
238 v = None 226 v = None
239 d.append(v) 227 d.append(v)
240 self.cursor.execute(sql, tuple(d)) 228 self.sql(sql, tuple(d))
241 229
242 return 1 230 return 1
243 231
244 def sql_close(self): 232 def sql_close(self):
245 ''' Squash any error caused by us already having closed the 233 ''' Squash any error caused by us already having closed the
276 raise 264 raise
277 # open a new cursor for subsequent work 265 # open a new cursor for subsequent work
278 self.cursor = self.conn.cursor() 266 self.cursor = self.conn.cursor()
279 267
280 def sql_index_exists(self, table_name, index_name): 268 def sql_index_exists(self, table_name, index_name):
281 self.cursor.execute('pragma index_list(%s)'%table_name) 269 self.sql('pragma index_list(%s)'%table_name)
282 for entry in self.cursor.fetchall(): 270 for entry in self.cursor.fetchall():
283 if entry[1] == index_name: 271 if entry[1] == index_name:
284 return 1 272 return 1
285 return 0 273 return 0
286 274
288 def newid(self, classname): 276 def newid(self, classname):
289 ''' Generate a new id for the given class 277 ''' Generate a new id for the given class
290 ''' 278 '''
291 # get the next ID 279 # get the next ID
292 sql = 'select num from ids where name=%s'%self.arg 280 sql = 'select num from ids where name=%s'%self.arg
293 if __debug__: 281 self.sql(sql, (classname, ))
294 print >>hyperdb.DEBUG, 'newid', (self, sql, classname)
295 self.cursor.execute(sql, (classname, ))
296 newid = int(self.cursor.fetchone()[0]) 282 newid = int(self.cursor.fetchone()[0])
297 283
298 # update the counter 284 # update the counter
299 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg) 285 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
300 vals = (int(newid)+1, classname) 286 vals = (int(newid)+1, classname)
301 if __debug__: 287 self.sql(sql, vals)
302 print >>hyperdb.DEBUG, 'newid', (self, sql, vals)
303 self.cursor.execute(sql, vals)
304 288
305 # return as string 289 # return as string
306 return str(newid) 290 return str(newid)
307 291
308 def setid(self, classname, setid): 292 def setid(self, classname, setid):
310 294
311 We add one to make it behave like the seqeunces in postgres. 295 We add one to make it behave like the seqeunces in postgres.
312 ''' 296 '''
313 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg) 297 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
314 vals = (int(setid)+1, classname) 298 vals = (int(setid)+1, classname)
315 if __debug__: 299 self.sql(sql, vals)
316 print >>hyperdb.DEBUG, 'setid', (self, sql, vals)
317 self.cursor.execute(sql, vals)
318 300
319 def create_class(self, spec): 301 def create_class(self, spec):
320 rdbms_common.Database.create_class(self, spec) 302 rdbms_common.Database.create_class(self, spec)
321 sql = 'insert into ids (name, num) values (%s, %s)' 303 sql = 'insert into ids (name, num) values (%s, %s)'
322 vals = (spec.classname, 1) 304 vals = (spec.classname, 1)
323 if __debug__: 305 self.sql(sql, vals)
324 print >>hyperdb.DEBUG, 'create_class', (self, sql, vals)
325 self.cursor.execute(sql, vals)
326 306
327 class sqliteClass: 307 class sqliteClass:
328 def filter(self, search_matches, filterspec, sort=(None,None), 308 def filter(self, search_matches, filterspec, sort=(None,None),
329 group=(None,None)): 309 group=(None,None)):
330 ''' If there's NO matches to a fetch, sqlite returns NULL 310 ''' If there's NO matches to a fetch, sqlite returns NULL
Roundup Issue Tracker: http://roundup-tracker.org/