Mercurial > p > roundup > code

comparison roundup/backends/rdbms_common.py @ 2217:98d3bf8ffb19

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 85954067e496
children 1d1362c54c94
comparison
equal deleted inserted replaced
2216:759ed26e24dd 2217:98d3bf8ffb19
1 # $Id: rdbms_common.py,v 1.90 2004-04-08 00:40:20 richard Exp $ 1 # $Id: rdbms_common.py,v 1.91 2004-04-18 05:31:02 richard Exp $
2 ''' Relational database (SQL) backend common code. 2 ''' Relational database (SQL) backend common code.
3 3
4 Basics: 4 Basics:
5 5
6 - map roundup classes to relational tables 6 - map roundup classes to relational tables
203 # change the schema structure 203 # change the schema structure
204 self.database_schema = {'tables': self.database_schema} 204 self.database_schema = {'tables': self.database_schema}
205 205
206 # version 1 didn't have the actor column (note that in 206 # version 1 didn't have the actor column (note that in
207 # MySQL this will also transition the tables to typed columns) 207 # MySQL this will also transition the tables to typed columns)
208 self.add_actor_column() 208 self.add_new_columns_v2()
209 209
210 # version 1 doesn't have the OTK, session and indexing in the 210 # version 1 doesn't have the OTK, session and indexing in the
211 # database 211 # database
212 self.create_version_2_tables() 212 self.create_version_2_tables()
213 213
221 def reindex(self): 221 def reindex(self):
222 for klass in self.classes.values(): 222 for klass in self.classes.values():
223 for nodeid in klass.list(): 223 for nodeid in klass.list():
224 klass.index(nodeid) 224 klass.index(nodeid)
225 self.indexer.save_index() 225 self.indexer.save_index()
226
227 226
228 hyperdb_to_sql_datatypes = { 227 hyperdb_to_sql_datatypes = {
229 hyperdb.String : 'VARCHAR(255)', 228 hyperdb.String : 'VARCHAR(255)',
230 hyperdb.Date : 'TIMESTAMP', 229 hyperdb.Date : 'TIMESTAMP',
231 hyperdb.Link : 'INTEGER', 230 hyperdb.Link : 'INTEGER',
258 #and prop.find('Multilink') != -1: 257 #and prop.find('Multilink') != -1:
259 #mls.append(col) 258 #mls.append(col)
260 259
261 datatype = self.hyperdb_to_sql_datatypes[prop.__class__] 260 datatype = self.hyperdb_to_sql_datatypes[prop.__class__]
262 cols.append(('_'+col, datatype)) 261 cols.append(('_'+col, datatype))
262
263 # Intervals stored as two columns
264 if isinstance(prop, Interval):
265 cols.append(('__'+col+'_int__', 'BIGINT'))
263 266
264 cols.sort() 267 cols.sort()
265 return cols, mls 268 return cols, mls
266 269
267 def update_class(self, spec, old_spec, force=0): 270 def update_class(self, spec, old_spec, force=0):
611 614
612 hyperdb_to_sql_value = { 615 hyperdb_to_sql_value = {
613 hyperdb.String : str, 616 hyperdb.String : str,
614 hyperdb.Date : lambda x: x.formal(sep=' ', sec='%.3f'), 617 hyperdb.Date : lambda x: x.formal(sep=' ', sec='%.3f'),
615 hyperdb.Link : int, 618 hyperdb.Link : int,
616 hyperdb.Interval : lambda x: x.serialise(), 619 hyperdb.Interval : str,
617 hyperdb.Password : str, 620 hyperdb.Password : str,
618 hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE', 621 hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE',
619 hyperdb.Number : lambda x: x, 622 hyperdb.Number : lambda x: x,
620 } 623 }
621 def addnode(self, classname, nodeid, node): 624 def addnode(self, classname, nodeid, node):
655 self.cache_lru.remove(key) 658 self.cache_lru.remove(key)
656 659
657 # figure the values to insert 660 # figure the values to insert
658 vals = [] 661 vals = []
659 for col,dt in cols: 662 for col,dt in cols:
663 # this is somewhat dodgy....
664 if col.endswith('_int__'):
665 # XXX eugh, this test suxxors
666 value = values[col[2:-6]]
667 # this is an Interval special "int" column
668 if value is not None:
669 vals.append(value.as_seconds())
670 else:
671 vals.append(value)
672 continue
673
660 prop = props[col[1:]] 674 prop = props[col[1:]]
661 value = values[col[1:]] 675 value = values[col[1:]]
662 if value: 676 if value:
663 value = self.hyperdb_to_sql_value[prop.__class__](value) 677 value = self.hyperdb_to_sql_value[prop.__class__](value)
664 vals.append(value) 678 vals.append(value)
708 # add the multilinks separately 722 # add the multilinks separately
709 for col in values.keys(): 723 for col in values.keys():
710 prop = props[col] 724 prop = props[col]
711 if isinstance(prop, Multilink): 725 if isinstance(prop, Multilink):
712 mls.append(col) 726 mls.append(col)
727 elif isinstance(prop, Interval):
728 # Intervals store the seconds value too
729 cols.append(col)
730 # extra leading '_' added by code below
731 cols.append('_' +col + '_int__')
713 else: 732 else:
714 cols.append(col) 733 cols.append(col)
715 cols.sort() 734 cols.sort()
716 735
717 # figure the values to insert 736 # figure the values to insert
718 vals = [] 737 vals = []
719 for col in cols: 738 for col in cols:
720 prop = props[col] 739 if col.endswith('_int__'):
721 value = values[col] 740 # XXX eugh, this test suxxors
722 if value is not None: 741 # Intervals store the seconds value too
723 value = self.hyperdb_to_sql_value[prop.__class__](value) 742 col = col[1:-6]
724 vals.append(value) 743 prop = props[col]
744 value = values[col]
745 if value is None:
746 vals.append(None)
747 else:
748 vals.append(value.as_seconds())
749 else:
750 prop = props[col]
751 value = values[col]
752 if value is None:
753 e = None
754 else:
755 e = self.hyperdb_to_sql_value[prop.__class__](value)
756 vals.append(e)
757
725 vals.append(int(nodeid)) 758 vals.append(int(nodeid))
726 vals = tuple(vals) 759 vals = tuple(vals)
727 760
728 # if there's any updates to regular columns, do them 761 # if there's any updates to regular columns, do them
729 if cols: 762 if cols:
814 # make up the node 847 # make up the node
815 node = {} 848 node = {}
816 props = cl.getprops(protected=1) 849 props = cl.getprops(protected=1)
817 for col in range(len(cols)): 850 for col in range(len(cols)):
818 name = cols[col][0][1:] 851 name = cols[col][0][1:]
852 if name.endswith('_int__'):
853 # XXX eugh, this test suxxors
854 # ignore the special Interval-as-seconds column
855 continue
819 value = values[col] 856 value = values[col]
820 if value is not None: 857 if value is not None:
821 value = self.sql_to_hyperdb_value[props[name].__class__](value) 858 value = self.sql_to_hyperdb_value[props[name].__class__](value)
822 node[name] = value 859 node[name] = value
823 860
1838 where.append(propname) 1875 where.append(propname)
1839 args.append(requirements[propname].lower()) 1876 args.append(requirements[propname].lower())
1840 1877
1841 # generate the where clause 1878 # generate the where clause
1842 s = ' and '.join(['lower(_%s)=%s'%(col, self.db.arg) for col in where]) 1879 s = ' and '.join(['lower(_%s)=%s'%(col, self.db.arg) for col in where])
1843 sql = 'select id from _%s where %s and __retired__=%s'%(self.classname, 1880 sql = 'select id from _%s where %s and __retired__<>%s'%(
1844 s, self.db.arg) 1881 self.classname, s, self.db.arg)
1845 args.append(0) 1882 args.append(1)
1846 self.db.sql(sql, tuple(args)) 1883 self.db.sql(sql, tuple(args))
1847 # XXX numeric ids 1884 # XXX numeric ids
1848 l = [str(x[0]) for x in self.db.sql_fetchall()] 1885 l = [str(x[0]) for x in self.db.sql_fetchall()]
1849 if __debug__: 1886 if __debug__:
1850 print >>hyperdb.DEBUG, 'find ... ', l 1887 print >>hyperdb.DEBUG, 'find ... ', l
1987 args.append(dc(date_rng.to_value)) 2024 args.append(dc(date_rng.to_value))
1988 except ValueError: 2025 except ValueError:
1989 # If range creation fails - ignore that search parameter 2026 # If range creation fails - ignore that search parameter
1990 pass 2027 pass
1991 elif isinstance(propclass, Interval): 2028 elif isinstance(propclass, Interval):
2029 # filter using the __<prop>_int__ column
1992 if isinstance(v, type([])): 2030 if isinstance(v, type([])):
1993 s = ','.join([a for x in v]) 2031 s = ','.join([a for x in v])
1994 where.append('_%s in (%s)'%(k, s)) 2032 where.append('__%s_int__ in (%s)'%(k, s))
1995 args = args + [date.Interval(x).serialise() for x in v] 2033 args = args + [date.Interval(x).as_seconds() for x in v]
1996 else: 2034 else:
1997 try: 2035 try:
1998 # Try to filter on range of intervals 2036 # Try to filter on range of intervals
1999 date_rng = Range(v, date.Interval) 2037 date_rng = Range(v, date.Interval)
2000 if date_rng.from_value: 2038 if date_rng.from_value:
2001 where.append('_%s >= %s'%(k, a)) 2039 where.append('__%s_int__ >= %s'%(k, a))
2002 args.append(date_rng.from_value.serialise()) 2040 args.append(date_rng.from_value.as_seconds())
2003 if date_rng.to_value: 2041 if date_rng.to_value:
2004 where.append('_%s <= %s'%(k, a)) 2042 where.append('__%s_int__ <= %s'%(k, a))
2005 args.append(date_rng.to_value.serialise()) 2043 args.append(date_rng.to_value.as_seconds())
2006 except ValueError: 2044 except ValueError:
2007 # If range creation fails - ignore that search parameter 2045 # If range creation fails - ignore that search parameter
2008 pass 2046 pass
2009 #where.append('_%s=%s'%(k, a))
2010 #args.append(date.Interval(v).serialise())
2011 else: 2047 else:
2012 if isinstance(v, type([])): 2048 if isinstance(v, type([])):
2013 s = ','.join([a for x in v]) 2049 s = ','.join([a for x in v])
2014 where.append('_%s in (%s)'%(k, s)) 2050 where.append('_%s in (%s)'%(k, s))
2015 args = args + v 2051 args = args + v
2035 sdir, prop = sortby 2071 sdir, prop = sortby
2036 if sdir and prop: 2072 if sdir and prop:
2037 if isinstance(props[prop], Multilink): 2073 if isinstance(props[prop], Multilink):
2038 mlsort.append(sortby) 2074 mlsort.append(sortby)
2039 continue 2075 continue
2076 elif isinstance(props[prop], Interval):
2077 # use the int column for sorting
2078 o = '__'+prop+'_int__'
2079 ordercols.append(o)
2040 elif prop == 'id': 2080 elif prop == 'id':
2041 o = 'id' 2081 o = 'id'
2042 else: 2082 else:
2043 o = '_'+prop 2083 o = '_'+prop
2044 ordercols.append(o) 2084 ordercols.append(o)
2070 self.db.cursor.execute(sql) 2110 self.db.cursor.execute(sql)
2071 l = self.db.sql_fetchall() 2111 l = self.db.sql_fetchall()
2072 2112
2073 # return the IDs (the first column) 2113 # return the IDs (the first column)
2074 # XXX numeric ids 2114 # XXX numeric ids
2075 l = [str(row[0]) for row in l] 2115 l = [str(row[0]) for row in l]
2076 2116
2077 if not mlsort: 2117 if not mlsort:
2078 return l 2118 return l
2079 2119
2080 # ergh. someone wants to sort by a multilink. 2120 # ergh. someone wants to sort by a multilink.
2245 pwd.unpack(value) 2285 pwd.unpack(value)
2246 value = pwd 2286 value = pwd
2247 d[propname] = value 2287 d[propname] = value
2248 2288
2249 # get a new id if necessary 2289 # get a new id if necessary
2250 if newid is None or not self.hasnode(newid): 2290 if newid is None:
2251 newid = self.db.newid(self.classname) 2291 newid = self.db.newid(self.classname)
2252 self.db.addnode(self.classname, newid, d) 2292
2293 # insert new node or update existing?
2294 if not self.hasnode(newid):
2295 self.db.addnode(self.classname, newid, d) # insert
2253 else: 2296 else:
2254 # update 2297 self.db.setnode(self.classname, newid, d) # update
2255 self.db.setnode(self.classname, newid, d)
2256 2298
2257 # retire? 2299 # retire?
2258 if retire: 2300 if retire:
2259 # use the arg for __retired__ to cope with any odd database type 2301 # use the arg for __retired__ to cope with any odd database type
2260 # conversion (hello, sqlite) 2302 # conversion (hello, sqlite)
Roundup Issue Tracker: http://roundup-tracker.org/