Mercurial > p > roundup > code
changeset 2217:98d3bf8ffb19
store Intervals as two columns (and other fixes
| author | Richard Jones <richard@users.sourceforge.net> |
|---|---|
| date | Sun, 18 Apr 2004 05:31:03 +0000 |
| parents | 759ed26e24dd |
| children | 5673b24ceb0d |
| files | CHANGES.txt TODO.txt roundup/backends/back_mysql.py roundup/backends/back_sqlite.py roundup/backends/rdbms_common.py test/db_test_base.py test/test_dates.py |
| diffstat | 7 files changed, 176 insertions(+), 90 deletions(-) [+] |
line wrap: on
line diff
--- a/CHANGES.txt Sat Apr 17 01:50:00 2004 +0000 +++ b/CHANGES.txt Sun Apr 18 05:31:03 2004 +0000 @@ -31,6 +31,7 @@ - sqlite backend had stopped using the global lock - better check for anonymous viewing of user items (sf bug 933510) - stop Interval from displaying an empty string (sf bug 934022) +- fixed storage of some datatypes in some RDBMS backends 2004-03-27 0.7.0b2
--- a/TODO.txt Sat Apr 17 01:50:00 2004 +0000 +++ b/TODO.txt Sun Apr 18 05:31:03 2004 +0000 @@ -1,8 +1,5 @@ This file contains items that need doing before the next release: -. make Intervals store timestamps, not strings - - Optionally: - have rdbms backends look up the journal for actor if it's not set - migrate to numeric ID values (fixes bug 817217)
--- a/roundup/backends/back_mysql.py Sat Apr 17 01:50:00 2004 +0000 +++ b/roundup/backends/back_mysql.py Sun Apr 18 05:31:03 2004 +0000 @@ -126,7 +126,7 @@ # no fractional seconds for MySQL hyperdb.Date : lambda x: x.formal(sep=' '), hyperdb.Link : int, - hyperdb.Interval : lambda x: x.serialise(), + hyperdb.Interval : str, hyperdb.Password : str, hyperdb.Boolean : int, hyperdb.Number : lambda x: x, @@ -190,7 +190,7 @@ sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg) self.cursor.execute(sql, ('__textids', 1)) - def add_actor_column(self): + def add_new_columns_v2(self): '''While we're adding the actor column, we need to update the tables to have the correct datatypes.''' for klass in self.classes.values(): @@ -274,8 +274,15 @@ # convert to new MySQL data type prop = properties[name] if v is not None: - v = self.hyperdb_to_sql_value[prop.__class__](v) - l.append(v) + e = self.hyperdb_to_sql_value[prop.__class__](v) + l.append(e) + + # Intervals store the seconds value too + if isinstance(prop, Interval): + if v is not None: + l.append(v.as_seconds()) + else: + l.append(e) self.drop_class_table_indexes(cn, old_spec[0]) @@ -595,25 +602,24 @@ # If range creation fails - ignore that search parameter pass elif isinstance(propclass, Interval): + # filter using the __<prop>_int__ column if isinstance(v, type([])): s = ','.join([a for x in v]) - where.append('_%s in (%s)'%(k, s)) - args = args + [date.Interval(x).serialise() for x in v] + where.append('__%s_int__ in (%s)'%(k, s)) + args = args + [date.Interval(x).as_seconds() for x in v] else: try: # Try to filter on range of intervals date_rng = Range(v, date.Interval) - if (date_rng.from_value): - where.append('_%s >= %s'%(k, a)) - args.append(date_rng.from_value.serialise()) - if (date_rng.to_value): - where.append('_%s <= %s'%(k, a)) - args.append(date_rng.to_value.serialise()) + if date_rng.from_value: + where.append('__%s_int__ >= %s'%(k, a)) + args.append(date_rng.from_value.as_seconds()) + if date_rng.to_value: + where.append('__%s_int__ <= %s'%(k, a)) + args.append(date_rng.to_value.as_seconds()) except ValueError: # If range creation fails - ignore that search parameter pass - #where.append('_%s=%s'%(k, a)) - #args.append(date.Interval(v).serialise()) else: if isinstance(v, type([])): s = ','.join([a for x in v]) @@ -634,34 +640,27 @@ args = args + v # "grouping" is just the first-order sorting in the SQL fetch - # can modify it...) orderby = [] ordercols = [] - if group[0] is not None and group[1] is not None: - if group[0] != '-': - orderby.append('_'+group[1]) - ordercols.append('_'+group[1]) - else: - orderby.append('_'+group[1]+' desc') - ordercols.append('_'+group[1]) - - # now add in the sorting - group = '' - if sort[0] is not None and sort[1] is not None: - direction, colname = sort - if direction != '-': - if colname == 'id': - orderby.append(colname) + mlsort = [] + for sortby in group, sort: + sdir, prop = sortby + if sdir and prop: + if isinstance(props[prop], Multilink): + mlsort.append(sortby) + continue + elif isinstance(props[prop], Interval): + # use the int column for sorting + o = '__'+prop+'_int__' + ordercols.append(o) + elif prop == 'id': + o = 'id' else: - orderby.append('_'+colname) - ordercols.append('_'+colname) - else: - if colname == 'id': - orderby.append(colname+' desc') - ordercols.append(colname) - else: - orderby.append('_'+colname+' desc') - ordercols.append('_'+colname) + o = '_'+prop + ordercols.append(o) + if sdir == '-': + o += ' desc' + orderby.append(o) # construct the SQL frum = ','.join(frum) @@ -669,14 +668,14 @@ where = ' where ' + (' and '.join(where)) else: where = '' - cols = ['id'] + cols = ['distinct(id)'] if orderby: cols = cols + ordercols order = ' order by %s'%(','.join(orderby)) else: order = '' cols = ','.join(cols) - sql = 'select %s from %s %s%s%s'%(cols, frum, where, group, order) + sql = 'select %s from %s %s%s'%(cols, frum, where, order) args = tuple(args) if __debug__: print >>hyperdb.DEBUG, 'filter', (self, sql, args) @@ -685,7 +684,28 @@ # return the IDs (the first column) # XXX numeric ids - return [str(row[0]) for row in l] + l = [str(row[0]) for row in l] + + if not mlsort: + return l + + # ergh. someone wants to sort by a multilink. + r = [] + for id in l: + m = [] + for ml in mlsort: + m.append(self.get(id, ml[1])) + r.append((id, m)) + i = 0 + for sortby in mlsort: + def sortfun(a, b, dir=sortby[i]): + if dir == '-': + return cmp(b[1][i], a[1][i]) + else: + return cmp(a[1][i], b[1][i]) + r.sort(sortfun) + i += 1 + return [i[0] for i in r] class Class(MysqlClass, rdbms_common.Class): pass
--- a/roundup/backends/back_sqlite.py Sat Apr 17 01:50:00 2004 +0000 +++ b/roundup/backends/back_sqlite.py Sun Apr 18 05:31:03 2004 +0000 @@ -1,4 +1,4 @@ -# $Id: back_sqlite.py,v 1.24 2004-04-07 01:12:26 richard Exp $ +# $Id: back_sqlite.py,v 1.25 2004-04-18 05:31:02 richard Exp $ '''Implements a backend for SQLite. See https://pysqlite.sourceforge.net/ for pysqlite info @@ -32,7 +32,7 @@ hyperdb.String : str, hyperdb.Date : lambda x: x.serialise(), hyperdb.Link : int, - hyperdb.Interval : lambda x: x.serialise(), + hyperdb.Interval : str, hyperdb.Password : str, hyperdb.Boolean : int, hyperdb.Number : lambda x: x, @@ -40,8 +40,7 @@ sql_to_hyperdb_value = { hyperdb.String : str, hyperdb.Date : lambda x: date.Date(str(x)), -# hyperdb.Link : int, # XXX numeric ids - hyperdb.Link : str, + hyperdb.Link : str, # XXX numeric ids hyperdb.Interval : date.Interval, hyperdb.Password : lambda x: password.Password(encrypted=x), hyperdb.Boolean : int, @@ -95,17 +94,17 @@ sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg) self.cursor.execute(sql, ('__textids', 1)) - def add_actor_column(self): + def add_new_columns_v2(self): # update existing tables to have the new actor column tables = self.database_schema['tables'] for classname, spec in self.classes.items(): if tables.has_key(classname): dbspec = tables[classname] - self.update_class(spec, dbspec, force=1, adding_actor=1) + self.update_class(spec, dbspec, force=1, adding_v2=1) # we've updated - don't try again tables[classname] = spec.schema() - def update_class(self, spec, old_spec, force=0, adding_actor=0): + def update_class(self, spec, old_spec, force=0, adding_v2=0): ''' Determine the differences between the current spec and the database version of the spec, and update where necessary. @@ -141,7 +140,7 @@ old_has = old_has.has_key # now figure how we populate the new table - if adding_actor: + if adding_v2: fetch = ['_activity', '_creation', '_creator'] else: fetch = ['_actor', '_activity', '_creation', '_creator'] @@ -201,14 +200,41 @@ self.create_class_table(spec) if olddata: + inscols = [] + for propname,x in new_spec[1]: + prop = properties[propname] + if isinstance(prop, hyperdb.Multilink): + continue + elif isinstance(prop, hyperdb.Interval): + inscols.append('_'+propname) + inscols.append('__'+propname+'_int__') + elif old_has(propname): + # we copy this col over from the old table + inscols.append('_'+propname) + # do the insert of the old data - the new columns will have # NULL values - args = ','.join([self.arg for x in fetch]) - sql = 'insert into _%s (%s) values (%s)'%(cn, fetchcols, args) + args = ','.join([self.arg for x in inscols]) + cols = ','.join(inscols) + sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args) if __debug__: print >>hyperdb.DEBUG, 'update_class', (self, sql, olddata[0]) for entry in olddata: - self.cursor.execute(sql, tuple(entry)) + d = [] + for name in inscols: + # generate the new value for the Interval int column + if name.endswith('_int__'): + name = name[2:-6] + if entry.has_key(name): + v = hyperdb.Interval(entry[name]).as_seconds() + else: + v = None + elif entry.has_key(name): + v = entry[name] + else: + v = None + d.append(v) + self.cursor.execute(sql, tuple(d)) return 1
--- a/roundup/backends/rdbms_common.py Sat Apr 17 01:50:00 2004 +0000 +++ b/roundup/backends/rdbms_common.py Sun Apr 18 05:31:03 2004 +0000 @@ -1,4 +1,4 @@ -# $Id: rdbms_common.py,v 1.90 2004-04-08 00:40:20 richard Exp $ +# $Id: rdbms_common.py,v 1.91 2004-04-18 05:31:02 richard Exp $ ''' Relational database (SQL) backend common code. Basics: @@ -205,7 +205,7 @@ # version 1 didn't have the actor column (note that in # MySQL this will also transition the tables to typed columns) - self.add_actor_column() + self.add_new_columns_v2() # version 1 doesn't have the OTK, session and indexing in the # database @@ -224,7 +224,6 @@ klass.index(nodeid) self.indexer.save_index() - hyperdb_to_sql_datatypes = { hyperdb.String : 'VARCHAR(255)', hyperdb.Date : 'TIMESTAMP', @@ -261,6 +260,10 @@ datatype = self.hyperdb_to_sql_datatypes[prop.__class__] cols.append(('_'+col, datatype)) + # Intervals stored as two columns + if isinstance(prop, Interval): + cols.append(('__'+col+'_int__', 'BIGINT')) + cols.sort() return cols, mls @@ -613,7 +616,7 @@ hyperdb.String : str, hyperdb.Date : lambda x: x.formal(sep=' ', sec='%.3f'), hyperdb.Link : int, - hyperdb.Interval : lambda x: x.serialise(), + hyperdb.Interval : str, hyperdb.Password : str, hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE', hyperdb.Number : lambda x: x, @@ -657,6 +660,17 @@ # figure the values to insert vals = [] for col,dt in cols: + # this is somewhat dodgy.... + if col.endswith('_int__'): + # XXX eugh, this test suxxors + value = values[col[2:-6]] + # this is an Interval special "int" column + if value is not None: + vals.append(value.as_seconds()) + else: + vals.append(value) + continue + prop = props[col[1:]] value = values[col[1:]] if value: @@ -710,6 +724,11 @@ prop = props[col] if isinstance(prop, Multilink): mls.append(col) + elif isinstance(prop, Interval): + # Intervals store the seconds value too + cols.append(col) + # extra leading '_' added by code below + cols.append('_' +col + '_int__') else: cols.append(col) cols.sort() @@ -717,11 +736,25 @@ # figure the values to insert vals = [] for col in cols: - prop = props[col] - value = values[col] - if value is not None: - value = self.hyperdb_to_sql_value[prop.__class__](value) - vals.append(value) + if col.endswith('_int__'): + # XXX eugh, this test suxxors + # Intervals store the seconds value too + col = col[1:-6] + prop = props[col] + value = values[col] + if value is None: + vals.append(None) + else: + vals.append(value.as_seconds()) + else: + prop = props[col] + value = values[col] + if value is None: + e = None + else: + e = self.hyperdb_to_sql_value[prop.__class__](value) + vals.append(e) + vals.append(int(nodeid)) vals = tuple(vals) @@ -816,6 +849,10 @@ props = cl.getprops(protected=1) for col in range(len(cols)): name = cols[col][0][1:] + if name.endswith('_int__'): + # XXX eugh, this test suxxors + # ignore the special Interval-as-seconds column + continue value = values[col] if value is not None: value = self.sql_to_hyperdb_value[props[name].__class__](value) @@ -1840,9 +1877,9 @@ # generate the where clause s = ' and '.join(['lower(_%s)=%s'%(col, self.db.arg) for col in where]) - sql = 'select id from _%s where %s and __retired__=%s'%(self.classname, - s, self.db.arg) - args.append(0) + sql = 'select id from _%s where %s and __retired__<>%s'%( + self.classname, s, self.db.arg) + args.append(1) self.db.sql(sql, tuple(args)) # XXX numeric ids l = [str(x[0]) for x in self.db.sql_fetchall()] @@ -1989,25 +2026,24 @@ # If range creation fails - ignore that search parameter pass elif isinstance(propclass, Interval): + # filter using the __<prop>_int__ column if isinstance(v, type([])): s = ','.join([a for x in v]) - where.append('_%s in (%s)'%(k, s)) - args = args + [date.Interval(x).serialise() for x in v] + where.append('__%s_int__ in (%s)'%(k, s)) + args = args + [date.Interval(x).as_seconds() for x in v] else: try: # Try to filter on range of intervals date_rng = Range(v, date.Interval) if date_rng.from_value: - where.append('_%s >= %s'%(k, a)) - args.append(date_rng.from_value.serialise()) + where.append('__%s_int__ >= %s'%(k, a)) + args.append(date_rng.from_value.as_seconds()) if date_rng.to_value: - where.append('_%s <= %s'%(k, a)) - args.append(date_rng.to_value.serialise()) + where.append('__%s_int__ <= %s'%(k, a)) + args.append(date_rng.to_value.as_seconds()) except ValueError: # If range creation fails - ignore that search parameter pass - #where.append('_%s=%s'%(k, a)) - #args.append(date.Interval(v).serialise()) else: if isinstance(v, type([])): s = ','.join([a for x in v]) @@ -2037,6 +2073,10 @@ if isinstance(props[prop], Multilink): mlsort.append(sortby) continue + elif isinstance(props[prop], Interval): + # use the int column for sorting + o = '__'+prop+'_int__' + ordercols.append(o) elif prop == 'id': o = 'id' else: @@ -2072,7 +2112,7 @@ # return the IDs (the first column) # XXX numeric ids - l = [str(row[0]) for row in l] + l = [str(row[0]) for row in l] if not mlsort: return l @@ -2247,12 +2287,14 @@ d[propname] = value # get a new id if necessary - if newid is None or not self.hasnode(newid): + if newid is None: newid = self.db.newid(self.classname) - self.db.addnode(self.classname, newid, d) + + # insert new node or update existing? + if not self.hasnode(newid): + self.db.addnode(self.classname, newid, d) # insert else: - # update - self.db.setnode(self.classname, newid, d) + self.db.setnode(self.classname, newid, d) # update # retire? if retire:
--- a/test/db_test_base.py Sat Apr 17 01:50:00 2004 +0000 +++ b/test/db_test_base.py Sun Apr 18 05:31:03 2004 +0000 @@ -15,7 +15,7 @@ # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # -# $Id: db_test_base.py,v 1.24 2004-04-08 00:43:23 richard Exp $ +# $Id: db_test_base.py,v 1.25 2004-04-18 05:31:02 richard Exp $ import unittest, os, shutil, errno, imp, sys, time, pprint @@ -948,7 +948,7 @@ # ensure retired items are retired :) l = items.keys(); l.sort() m = klass.list(); m.sort() - ae(l, m) + ae(l, m, '%s id list wrong %r vs. %r'%(cn, l, m)) for id, props in items.items(): for name, value in props.items(): l = klass.get(id, name)
--- a/test/test_dates.py Sat Apr 17 01:50:00 2004 +0000 +++ b/test/test_dates.py Sun Apr 18 05:31:03 2004 +0000 @@ -15,7 +15,7 @@ # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # -# $Id: test_dates.py,v 1.31 2003-12-04 23:06:53 richard Exp $ +# $Id: test_dates.py,v 1.32 2004-04-18 05:31:03 richard Exp $ from __future__ import nested_scopes import unittest, time @@ -265,14 +265,14 @@ ae(str(Interval('1y') + Interval('1y')), '+ 2y') ae(str(Interval('1y') + Interval('1m')), '+ 1y 1m') ae(str(Interval('1y') + Interval('2:40')), '+ 1y 2:40') - ae(str(Interval('1y') + Interval('- 1y')), '') - ae(str(Interval('- 1y') + Interval('1y')), '') + ae(str(Interval('1y') + Interval('- 1y')), '00:00') + ae(str(Interval('- 1y') + Interval('1y')), '00:00') ae(str(Interval('- 1y') + Interval('- 1y')), '- 2y') ae(str(Interval('1y') + Interval('- 1m')), '+ 11m') ae(str(Interval('1:00') + Interval('1:00')), '+ 2:00') ae(str(Interval('0:50') + Interval('0:50')), '+ 1:40') - ae(str(Interval('1:50') + Interval('- 1:50')), '') - ae(str(Interval('- 1:50') + Interval('1:50')), '') + ae(str(Interval('1:50') + Interval('- 1:50')), '00:00') + ae(str(Interval('- 1:50') + Interval('1:50')), '00:00') ae(str(Interval('- 1:50') + Interval('- 1:50')), '- 3:40') ae(str(Interval('1:59:59') + Interval('00:00:01')), '+ 2:00') ae(str(Interval('2:00') + Interval('- 00:00:01')), '+ 1:59:59') @@ -282,11 +282,11 @@ ae(str(Interval('1y') - Interval('- 1y')), '+ 2y') ae(str(Interval('1y') - Interval('- 1m')), '+ 1y 1m') ae(str(Interval('1y') - Interval('- 2:40')), '+ 1y 2:40') - ae(str(Interval('1y') - Interval('1y')), '') + ae(str(Interval('1y') - Interval('1y')), '00:00') ae(str(Interval('1y') - Interval('1m')), '+ 11m') ae(str(Interval('1:00') - Interval('- 1:00')), '+ 2:00') ae(str(Interval('0:50') - Interval('- 0:50')), '+ 1:40') - ae(str(Interval('1:50') - Interval('1:50')), '') + ae(str(Interval('1:50') - Interval('1:50')), '00:00') ae(str(Interval('1:59:59') - Interval('- 00:00:01')), '+ 2:00') ae(str(Interval('2:00') - Interval('00:00:01')), '+ 1:59:59')