72d0c69937a3feb1e445c03563dc614fc0bdaabf
1 #
2 # Copyright (c) 2001 Bizar Software Pty Ltd (http://www.bizarsoftware.com.au/)
3 # This module is free software, and you may redistribute it and/or modify
4 # under the same terms as Python, so long as this copyright message and
5 # disclaimer are retained in their original form.
6 #
7 # IN NO EVENT SHALL BIZAR SOFTWARE PTY LTD BE LIABLE TO ANY PARTY FOR
8 # DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING
9 # OUT OF THE USE OF THIS CODE, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE
10 # POSSIBILITY OF SUCH DAMAGE.
11 #
12 # BIZAR SOFTWARE PTY LTD SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING,
13 # BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
14 # FOR A PARTICULAR PURPOSE. THE CODE PROVIDED HEREUNDER IS ON AN "AS IS"
15 # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE,
16 # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
17 #
18 """ Relational database (SQL) backend common code.
20 Basics:
22 - map roundup classes to relational tables
23 - automatically detect schema changes and modify the table schemas
24 appropriately (we store the "database version" of the schema in the
25 database itself as the only row of the "schema" table)
26 - multilinks (which represent a many-to-many relationship) are handled through
27 intermediate tables
28 - journals are stored adjunct to the per-class tables
29 - table names and columns have "_" prepended so the names can't clash with
30 restricted names (like "order")
31 - retirement is determined by the __retired__ column being > 0
33 Database-specific changes may generally be pushed out to the overridable
34 sql_* methods, since everything else should be fairly generic. There's
35 probably a bit of work to be done if a database is used that actually
36 honors column typing, since the initial databases don't (sqlite stores
37 everything as a string.)
39 The schema of the hyperdb being mapped to the database is stored in the
40 database itself as a repr()'ed dictionary of information about each Class
41 that maps to a table. If that information differs from the hyperdb schema,
42 then we update it. We also store in the schema dict a version which
43 allows us to upgrade the database schema when necessary. See upgrade_db().
45 To force a unqiueness constraint on the key properties we put the item
46 id into the __retired__ column duing retirement (so it's 0 for "active"
47 items) and place a unqiueness constraint on key + __retired__. This is
48 particularly important for the users class where multiple users may
49 try to have the same username, with potentially many retired users with
50 the same name.
51 """
52 __docformat__ = 'restructuredtext'
54 # standard python modules
55 import sys, os, time, re, errno, weakref, copy, logging
57 # roundup modules
58 from roundup import hyperdb, date, password, roundupdb, security, support
59 from roundup.hyperdb import String, Password, Date, Interval, Link, \
60 Multilink, DatabaseError, Boolean, Number, Node
61 from roundup.backends import locking
62 from roundup.support import reversed
63 from roundup.i18n import _
65 # support
66 from roundup.backends.blobfiles import FileStorage
67 try:
68 from roundup.backends.indexer_xapian import Indexer
69 except ImportError:
70 from roundup.backends.indexer_rdbms import Indexer
71 from roundup.backends.sessions_rdbms import Sessions, OneTimeKeys
72 from roundup.date import Range
74 from roundup.backends.back_anydbm import compile_expression
77 # dummy value meaning "argument not passed"
78 _marker = []
80 def _num_cvt(num):
81 num = str(num)
82 try:
83 return int(num)
84 except:
85 return float(num)
87 def _bool_cvt(value):
88 if value in ('TRUE', 'FALSE'):
89 return {'TRUE': 1, 'FALSE': 0}[value]
90 # assume it's a number returned from the db API
91 return int(value)
93 def connection_dict(config, dbnamestr=None):
94 """ Used by Postgresql and MySQL to detemine the keyword args for
95 opening the database connection."""
96 d = { }
97 if dbnamestr:
98 d[dbnamestr] = config.RDBMS_NAME
99 for name in ('host', 'port', 'password', 'user', 'read_default_group',
100 'read_default_file'):
101 cvar = 'RDBMS_'+name.upper()
102 if config[cvar] is not None:
103 d[name] = config[cvar]
104 return d
107 class IdListOptimizer:
108 """ To prevent flooding the SQL parser of the underlaying
109 db engine with "x IN (1, 2, 3, ..., <large number>)" collapses
110 these cases to "x BETWEEN 1 AND <large number>".
111 """
113 def __init__(self):
114 self.ranges = []
115 self.singles = []
117 def append(self, nid):
118 """ Invariant: nid are ordered ascending """
119 if self.ranges:
120 last = self.ranges[-1]
121 if last[1] == nid-1:
122 last[1] = nid
123 return
124 if self.singles:
125 last = self.singles[-1]
126 if last == nid-1:
127 self.singles.pop()
128 self.ranges.append([last, nid])
129 return
130 self.singles.append(nid)
132 def where(self, field, placeholder):
133 ranges = self.ranges
134 singles = self.singles
136 if not singles and not ranges: return "(1=0)", []
138 if ranges:
139 between = '%s BETWEEN %s AND %s' % (
140 field, placeholder, placeholder)
141 stmnt = [between] * len(ranges)
142 else:
143 stmnt = []
144 if singles:
145 stmnt.append('%s in (%s)' % (
146 field, ','.join([placeholder]*len(singles))))
148 return '(%s)' % ' OR '.join(stmnt), sum(ranges, []) + singles
150 def __str__(self):
151 return "ranges: %r / singles: %r" % (self.ranges, self.singles)
154 class Database(FileStorage, hyperdb.Database, roundupdb.Database):
155 """ Wrapper around an SQL database that presents a hyperdb interface.
157 - some functionality is specific to the actual SQL database, hence
158 the sql_* methods that are NotImplemented
159 - we keep a cache of the latest N row fetches (where N is configurable).
160 """
161 def __init__(self, config, journaltag=None):
162 """ Open the database and load the schema from it.
163 """
164 FileStorage.__init__(self, config.UMASK)
165 self.config, self.journaltag = config, journaltag
166 self.dir = config.DATABASE
167 self.classes = {}
168 self.indexer = Indexer(self)
169 self.security = security.Security(self)
171 # additional transaction support for external files and the like
172 self.transactions = []
174 # keep a cache of the N most recently retrieved rows of any kind
175 # (classname, nodeid) = row
176 self.cache_size = config.RDBMS_CACHE_SIZE
177 self.cache = {}
178 self.cache_lru = []
179 self.stats = {'cache_hits': 0, 'cache_misses': 0, 'get_items': 0,
180 'filtering': 0}
182 # database lock
183 self.lockfile = None
185 # open a connection to the database, creating the "conn" attribute
186 self.open_connection()
188 def clearCache(self):
189 self.cache = {}
190 self.cache_lru = []
192 def getSessionManager(self):
193 return Sessions(self)
195 def getOTKManager(self):
196 return OneTimeKeys(self)
198 def open_connection(self):
199 """ Open a connection to the database, creating it if necessary.
201 Must call self.load_dbschema()
202 """
203 raise NotImplemented
205 def sql(self, sql, args=None):
206 """ Execute the sql with the optional args.
207 """
208 self.log_debug('SQL %r %r'%(sql, args))
209 if args:
210 self.cursor.execute(sql, args)
211 else:
212 self.cursor.execute(sql)
214 def sql_fetchone(self):
215 """ Fetch a single row. If there's nothing to fetch, return None.
216 """
217 return self.cursor.fetchone()
219 def sql_fetchall(self):
220 """ Fetch all rows. If there's nothing to fetch, return [].
221 """
222 return self.cursor.fetchall()
224 def sql_fetchiter(self):
225 """ Fetch all row as a generator
226 """
227 while True:
228 row = self.cursor.fetchone()
229 if not row: break
230 yield row
232 def sql_stringquote(self, value):
233 """ Quote the string so it's safe to put in the 'sql quotes'
234 """
235 return re.sub("'", "''", str(value))
237 def init_dbschema(self):
238 self.database_schema = {
239 'version': self.current_db_version,
240 'tables': {}
241 }
243 def load_dbschema(self):
244 """ Load the schema definition that the database currently implements
245 """
246 self.cursor.execute('select schema from schema')
247 schema = self.cursor.fetchone()
248 if schema:
249 self.database_schema = eval(schema[0])
250 else:
251 self.database_schema = {}
253 def save_dbschema(self):
254 """ Save the schema definition that the database currently implements
255 """
256 s = repr(self.database_schema)
257 self.sql('delete from schema')
258 self.sql('insert into schema values (%s)'%self.arg, (s,))
260 def post_init(self):
261 """ Called once the schema initialisation has finished.
263 We should now confirm that the schema defined by our "classes"
264 attribute actually matches the schema in the database.
265 """
266 save = 0
268 # handle changes in the schema
269 tables = self.database_schema['tables']
270 for classname, spec in self.classes.iteritems():
271 if classname in tables:
272 dbspec = tables[classname]
273 if self.update_class(spec, dbspec):
274 tables[classname] = spec.schema()
275 save = 1
276 else:
277 self.create_class(spec)
278 tables[classname] = spec.schema()
279 save = 1
281 for classname, spec in list(tables.items()):
282 if classname not in self.classes:
283 self.drop_class(classname, tables[classname])
284 del tables[classname]
285 save = 1
287 # now upgrade the database for column type changes, new internal
288 # tables, etc.
289 save = save | self.upgrade_db()
291 # update the database version of the schema
292 if save:
293 self.save_dbschema()
295 # reindex the db if necessary
296 if self.indexer.should_reindex():
297 self.reindex()
299 # commit
300 self.sql_commit()
302 # update this number when we need to make changes to the SQL structure
303 # of the backen database
304 current_db_version = 5
305 db_version_updated = False
306 def upgrade_db(self):
307 """ Update the SQL database to reflect changes in the backend code.
309 Return boolean whether we need to save the schema.
310 """
311 version = self.database_schema.get('version', 1)
312 if version > self.current_db_version:
313 raise DatabaseError('attempting to run rev %d DATABASE with rev '
314 '%d CODE!'%(version, self.current_db_version))
315 if version == self.current_db_version:
316 # nothing to do
317 return 0
319 if version < 2:
320 self.log_info('upgrade to version 2')
321 # change the schema structure
322 self.database_schema = {'tables': self.database_schema}
324 # version 1 didn't have the actor column (note that in
325 # MySQL this will also transition the tables to typed columns)
326 self.add_new_columns_v2()
328 # version 1 doesn't have the OTK, session and indexing in the
329 # database
330 self.create_version_2_tables()
332 if version < 3:
333 self.log_info('upgrade to version 3')
334 self.fix_version_2_tables()
336 if version < 4:
337 self.fix_version_3_tables()
339 if version < 5:
340 self.fix_version_4_tables()
342 self.database_schema['version'] = self.current_db_version
343 self.db_version_updated = True
344 return 1
346 def fix_version_3_tables(self):
347 # drop the shorter VARCHAR OTK column and add a new TEXT one
348 for name in ('otk', 'session'):
349 self.sql('DELETE FROM %ss'%name)
350 self.sql('ALTER TABLE %ss DROP %s_value'%(name, name))
351 self.sql('ALTER TABLE %ss ADD %s_value TEXT'%(name, name))
353 def fix_version_2_tables(self):
354 # Default (used by sqlite): NOOP
355 pass
357 def fix_version_4_tables(self):
358 # note this is an explicit call now
359 c = self.cursor
360 for cn, klass in self.classes.iteritems():
361 c.execute('select id from _%s where __retired__<>0'%(cn,))
362 for (id,) in c.fetchall():
363 c.execute('update _%s set __retired__=%s where id=%s'%(cn,
364 self.arg, self.arg), (id, id))
366 if klass.key:
367 self.add_class_key_required_unique_constraint(cn, klass.key)
369 def _convert_journal_tables(self):
370 """Get current journal table contents, drop the table and re-create"""
371 c = self.cursor
372 cols = ','.join('nodeid date tag action params'.split())
373 for klass in self.classes.itervalues():
374 # slurp and drop
375 sql = 'select %s from %s__journal order by date'%(cols,
376 klass.classname)
377 c.execute(sql)
378 contents = c.fetchall()
379 self.drop_journal_table_indexes(klass.classname)
380 c.execute('drop table %s__journal'%klass.classname)
382 # re-create and re-populate
383 self.create_journal_table(klass)
384 a = self.arg
385 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(
386 klass.classname, cols, a, a, a, a, a)
387 for row in contents:
388 # no data conversion needed
389 self.cursor.execute(sql, row)
391 def _convert_string_properties(self):
392 """Get current Class tables that contain String properties, and
393 convert the VARCHAR columns to TEXT"""
394 c = self.cursor
395 for klass in self.classes.itervalues():
396 # slurp and drop
397 cols, mls = self.determine_columns(list(klass.properties.iteritems()))
398 scols = ','.join([i[0] for i in cols])
399 sql = 'select id,%s from _%s'%(scols, klass.classname)
400 c.execute(sql)
401 contents = c.fetchall()
402 self.drop_class_table_indexes(klass.classname, klass.getkey())
403 c.execute('drop table _%s'%klass.classname)
405 # re-create and re-populate
406 self.create_class_table(klass, create_sequence=0)
407 a = ','.join([self.arg for i in range(len(cols)+1)])
408 sql = 'insert into _%s (id,%s) values (%s)'%(klass.classname,
409 scols, a)
410 for row in contents:
411 l = []
412 for entry in row:
413 # mysql will already be a string - psql needs "help"
414 if entry is not None and not isinstance(entry, type('')):
415 entry = str(entry)
416 l.append(entry)
417 self.cursor.execute(sql, l)
419 def refresh_database(self):
420 self.post_init()
423 def reindex(self, classname=None, show_progress=False):
424 if classname:
425 classes = [self.getclass(classname)]
426 else:
427 classes = list(self.classes.itervalues())
428 for klass in classes:
429 if show_progress:
430 for nodeid in support.Progress('Reindex %s'%klass.classname,
431 klass.list()):
432 klass.index(nodeid)
433 else:
434 for nodeid in klass.list():
435 klass.index(nodeid)
436 self.indexer.save_index()
438 hyperdb_to_sql_datatypes = {
439 hyperdb.String : 'TEXT',
440 hyperdb.Date : 'TIMESTAMP',
441 hyperdb.Link : 'INTEGER',
442 hyperdb.Interval : 'VARCHAR(255)',
443 hyperdb.Password : 'VARCHAR(255)',
444 hyperdb.Boolean : 'BOOLEAN',
445 hyperdb.Number : 'REAL',
446 }
448 def hyperdb_to_sql_datatype(self, propclass):
450 datatype = self.hyperdb_to_sql_datatypes.get(propclass)
451 if datatype:
452 return datatype
454 for k, v in self.hyperdb_to_sql_datatypes.iteritems():
455 if issubclass(propclass, k):
456 return v
458 raise ValueError('%r is not a hyperdb property class' % propclass)
460 def determine_columns(self, properties):
461 """ Figure the column names and multilink properties from the spec
463 "properties" is a list of (name, prop) where prop may be an
464 instance of a hyperdb "type" _or_ a string repr of that type.
465 """
466 cols = [
467 ('_actor', self.hyperdb_to_sql_datatype(hyperdb.Link)),
468 ('_activity', self.hyperdb_to_sql_datatype(hyperdb.Date)),
469 ('_creator', self.hyperdb_to_sql_datatype(hyperdb.Link)),
470 ('_creation', self.hyperdb_to_sql_datatype(hyperdb.Date)),
471 ]
472 mls = []
473 # add the multilinks separately
474 for col, prop in properties:
475 if isinstance(prop, Multilink):
476 mls.append(col)
477 continue
479 if isinstance(prop, type('')):
480 raise ValueError("string property spec!")
481 #and prop.find('Multilink') != -1:
482 #mls.append(col)
484 datatype = self.hyperdb_to_sql_datatype(prop.__class__)
485 cols.append(('_'+col, datatype))
487 # Intervals stored as two columns
488 if isinstance(prop, Interval):
489 cols.append(('__'+col+'_int__', 'BIGINT'))
491 cols.sort()
492 return cols, mls
494 def update_class(self, spec, old_spec, force=0):
495 """ Determine the differences between the current spec and the
496 database version of the spec, and update where necessary.
498 If 'force' is true, update the database anyway.
499 """
500 new_spec = spec.schema()
501 new_spec[1].sort()
502 old_spec[1].sort()
503 if not force and new_spec == old_spec:
504 # no changes
505 return 0
507 logger = logging.getLogger('roundup.hyperdb')
508 logger.info('update_class %s'%spec.classname)
510 logger.debug('old_spec %r'%(old_spec,))
511 logger.debug('new_spec %r'%(new_spec,))
513 # detect key prop change for potential index change
514 keyprop_changes = {}
515 if new_spec[0] != old_spec[0]:
516 if old_spec[0]:
517 keyprop_changes['remove'] = old_spec[0]
518 if new_spec[0]:
519 keyprop_changes['add'] = new_spec[0]
521 # detect multilinks that have been removed, and drop their table
522 old_has = {}
523 for name, prop in old_spec[1]:
524 old_has[name] = 1
525 if name in spec.properties:
526 continue
528 if prop.find('Multilink to') != -1:
529 # first drop indexes.
530 self.drop_multilink_table_indexes(spec.classname, name)
532 # now the multilink table itself
533 sql = 'drop table %s_%s'%(spec.classname, name)
534 else:
535 # if this is the key prop, drop the index first
536 if old_spec[0] == prop:
537 self.drop_class_table_key_index(spec.classname, name)
538 del keyprop_changes['remove']
540 # drop the column
541 sql = 'alter table _%s drop column _%s'%(spec.classname, name)
543 self.sql(sql)
545 # if we didn't remove the key prop just then, but the key prop has
546 # changed, we still need to remove the old index
547 if 'remove' in keyprop_changes:
548 self.drop_class_table_key_index(spec.classname,
549 keyprop_changes['remove'])
551 # add new columns
552 for propname, prop in new_spec[1]:
553 if propname in old_has:
554 continue
555 prop = spec.properties[propname]
556 if isinstance(prop, Multilink):
557 self.create_multilink_table(spec, propname)
558 else:
559 # add the column
560 coltype = self.hyperdb_to_sql_datatype(prop.__class__)
561 sql = 'alter table _%s add column _%s %s'%(
562 spec.classname, propname, coltype)
563 self.sql(sql)
565 # extra Interval column
566 if isinstance(prop, Interval):
567 sql = 'alter table _%s add column __%s_int__ BIGINT'%(
568 spec.classname, propname)
569 self.sql(sql)
571 # if the new column is a key prop, we need an index!
572 if new_spec[0] == propname:
573 self.create_class_table_key_index(spec.classname, propname)
574 del keyprop_changes['add']
576 # if we didn't add the key prop just then, but the key prop has
577 # changed, we still need to add the new index
578 if 'add' in keyprop_changes:
579 self.create_class_table_key_index(spec.classname,
580 keyprop_changes['add'])
582 return 1
584 def determine_all_columns(self, spec):
585 """Figure out the columns from the spec and also add internal columns
587 """
588 cols, mls = self.determine_columns(list(spec.properties.iteritems()))
590 # add on our special columns
591 cols.append(('id', 'INTEGER PRIMARY KEY'))
592 cols.append(('__retired__', 'INTEGER DEFAULT 0'))
593 return cols, mls
595 def create_class_table(self, spec):
596 """Create the class table for the given Class "spec". Creates the
597 indexes too."""
598 cols, mls = self.determine_all_columns(spec)
600 # create the base table
601 scols = ','.join(['%s %s'%x for x in cols])
602 sql = 'create table _%s (%s)'%(spec.classname, scols)
603 self.sql(sql)
605 self.create_class_table_indexes(spec)
607 return cols, mls
609 def create_class_table_indexes(self, spec):
610 """ create the class table for the given spec
611 """
612 # create __retired__ index
613 index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)'%(
614 spec.classname, spec.classname)
615 self.sql(index_sql2)
617 # create index for key property
618 if spec.key:
619 index_sql3 = 'create index _%s_%s_idx on _%s(_%s)'%(
620 spec.classname, spec.key,
621 spec.classname, spec.key)
622 self.sql(index_sql3)
624 # and the unique index for key / retired(id)
625 self.add_class_key_required_unique_constraint(spec.classname,
626 spec.key)
628 # TODO: create indexes on (selected?) Link property columns, as
629 # they're more likely to be used for lookup
631 def add_class_key_required_unique_constraint(self, cn, key):
632 sql = '''create unique index _%s_key_retired_idx
633 on _%s(__retired__, _%s)'''%(cn, cn, key)
634 self.sql(sql)
636 def drop_class_table_indexes(self, cn, key):
637 # drop the old table indexes first
638 l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn]
639 if key:
640 l.append('_%s_%s_idx'%(cn, key))
642 table_name = '_%s'%cn
643 for index_name in l:
644 if not self.sql_index_exists(table_name, index_name):
645 continue
646 index_sql = 'drop index '+index_name
647 self.sql(index_sql)
649 def create_class_table_key_index(self, cn, key):
650 """ create the class table for the given spec
651 """
652 sql = 'create index _%s_%s_idx on _%s(_%s)'%(cn, key, cn, key)
653 self.sql(sql)
655 def drop_class_table_key_index(self, cn, key):
656 table_name = '_%s'%cn
657 index_name = '_%s_%s_idx'%(cn, key)
658 if self.sql_index_exists(table_name, index_name):
659 sql = 'drop index '+index_name
660 self.sql(sql)
662 # and now the retired unique index too
663 index_name = '_%s_key_retired_idx'%cn
664 if self.sql_index_exists(table_name, index_name):
665 sql = 'drop index '+index_name
666 self.sql(sql)
668 def create_journal_table(self, spec):
669 """ create the journal table for a class given the spec and
670 already-determined cols
671 """
672 # journal table
673 cols = ','.join(['%s varchar'%x
674 for x in 'nodeid date tag action params'.split()])
675 sql = """create table %s__journal (
676 nodeid integer, date %s, tag varchar(255),
677 action varchar(255), params text)""" % (spec.classname,
678 self.hyperdb_to_sql_datatype(hyperdb.Date))
679 self.sql(sql)
680 self.create_journal_table_indexes(spec)
682 def create_journal_table_indexes(self, spec):
683 # index on nodeid
684 sql = 'create index %s_journ_idx on %s__journal(nodeid)'%(
685 spec.classname, spec.classname)
686 self.sql(sql)
688 def drop_journal_table_indexes(self, classname):
689 index_name = '%s_journ_idx'%classname
690 if not self.sql_index_exists('%s__journal'%classname, index_name):
691 return
692 index_sql = 'drop index '+index_name
693 self.sql(index_sql)
695 def create_multilink_table(self, spec, ml):
696 """ Create a multilink table for the "ml" property of the class
697 given by the spec
698 """
699 # create the table
700 sql = 'create table %s_%s (linkid INTEGER, nodeid INTEGER)'%(
701 spec.classname, ml)
702 self.sql(sql)
703 self.create_multilink_table_indexes(spec, ml)
705 def create_multilink_table_indexes(self, spec, ml):
706 # create index on linkid
707 index_sql = 'create index %s_%s_l_idx on %s_%s(linkid)'%(
708 spec.classname, ml, spec.classname, ml)
709 self.sql(index_sql)
711 # create index on nodeid
712 index_sql = 'create index %s_%s_n_idx on %s_%s(nodeid)'%(
713 spec.classname, ml, spec.classname, ml)
714 self.sql(index_sql)
716 def drop_multilink_table_indexes(self, classname, ml):
717 l = [
718 '%s_%s_l_idx'%(classname, ml),
719 '%s_%s_n_idx'%(classname, ml)
720 ]
721 table_name = '%s_%s'%(classname, ml)
722 for index_name in l:
723 if not self.sql_index_exists(table_name, index_name):
724 continue
725 index_sql = 'drop index %s'%index_name
726 self.sql(index_sql)
728 def create_class(self, spec):
729 """ Create a database table according to the given spec.
730 """
731 cols, mls = self.create_class_table(spec)
732 self.create_journal_table(spec)
734 # now create the multilink tables
735 for ml in mls:
736 self.create_multilink_table(spec, ml)
738 def drop_class(self, cn, spec):
739 """ Drop the given table from the database.
741 Drop the journal and multilink tables too.
742 """
743 properties = spec[1]
744 # figure the multilinks
745 mls = []
746 for propname, prop in properties:
747 if isinstance(prop, Multilink):
748 mls.append(propname)
750 # drop class table and indexes
751 self.drop_class_table_indexes(cn, spec[0])
753 self.drop_class_table(cn)
755 # drop journal table and indexes
756 self.drop_journal_table_indexes(cn)
757 sql = 'drop table %s__journal'%cn
758 self.sql(sql)
760 for ml in mls:
761 # drop multilink table and indexes
762 self.drop_multilink_table_indexes(cn, ml)
763 sql = 'drop table %s_%s'%(spec.classname, ml)
764 self.sql(sql)
766 def drop_class_table(self, cn):
767 sql = 'drop table _%s'%cn
768 self.sql(sql)
770 #
771 # Classes
772 #
773 def __getattr__(self, classname):
774 """ A convenient way of calling self.getclass(classname).
775 """
776 if classname in self.classes:
777 return self.classes[classname]
778 raise AttributeError(classname)
780 def addclass(self, cl):
781 """ Add a Class to the hyperdatabase.
782 """
783 cn = cl.classname
784 if cn in self.classes:
785 raise ValueError(cn)
786 self.classes[cn] = cl
788 # add default Edit and View permissions
789 self.security.addPermission(name="Create", klass=cn,
790 description="User is allowed to create "+cn)
791 self.security.addPermission(name="Edit", klass=cn,
792 description="User is allowed to edit "+cn)
793 self.security.addPermission(name="View", klass=cn,
794 description="User is allowed to access "+cn)
796 def getclasses(self):
797 """ Return a list of the names of all existing classes.
798 """
799 return sorted(self.classes)
801 def getclass(self, classname):
802 """Get the Class object representing a particular class.
804 If 'classname' is not a valid class name, a KeyError is raised.
805 """
806 try:
807 return self.classes[classname]
808 except KeyError:
809 raise KeyError('There is no class called "%s"'%classname)
811 def clear(self):
812 """Delete all database contents.
814 Note: I don't commit here, which is different behaviour to the
815 "nuke from orbit" behaviour in the dbs.
816 """
817 logging.getLogger('roundup.hyperdb').info('clear')
818 for cn in self.classes:
819 sql = 'delete from _%s'%cn
820 self.sql(sql)
822 #
823 # Nodes
824 #
826 hyperdb_to_sql_value = {
827 hyperdb.String : str,
828 # fractional seconds by default
829 hyperdb.Date : lambda x: x.formal(sep=' ', sec='%06.3f'),
830 hyperdb.Link : int,
831 hyperdb.Interval : str,
832 hyperdb.Password : str,
833 hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE',
834 hyperdb.Number : lambda x: x,
835 hyperdb.Multilink : lambda x: x, # used in journal marshalling
836 }
838 def to_sql_value(self, propklass):
840 fn = self.hyperdb_to_sql_value.get(propklass)
841 if fn:
842 return fn
844 for k, v in self.hyperdb_to_sql_value.iteritems():
845 if issubclass(propklass, k):
846 return v
848 raise ValueError('%r is not a hyperdb property class' % propklass)
850 def addnode(self, classname, nodeid, node):
851 """ Add the specified node to its class's db.
852 """
853 self.log_debug('addnode %s%s %r'%(classname,
854 nodeid, node))
856 # determine the column definitions and multilink tables
857 cl = self.classes[classname]
858 cols, mls = self.determine_columns(list(cl.properties.iteritems()))
860 # we'll be supplied these props if we're doing an import
861 values = node.copy()
862 if 'creator' not in values:
863 # add in the "calculated" properties (dupe so we don't affect
864 # calling code's node assumptions)
865 values['creation'] = values['activity'] = date.Date()
866 values['actor'] = values['creator'] = self.getuid()
868 cl = self.classes[classname]
869 props = cl.getprops(protected=1)
870 del props['id']
872 # default the non-multilink columns
873 for col, prop in props.iteritems():
874 if col not in values:
875 if isinstance(prop, Multilink):
876 values[col] = []
877 else:
878 values[col] = None
880 # clear this node out of the cache if it's in there
881 key = (classname, nodeid)
882 if key in self.cache:
883 del self.cache[key]
884 self.cache_lru.remove(key)
886 # figure the values to insert
887 vals = []
888 for col,dt in cols:
889 # this is somewhat dodgy....
890 if col.endswith('_int__'):
891 # XXX eugh, this test suxxors
892 value = values[col[2:-6]]
893 # this is an Interval special "int" column
894 if value is not None:
895 vals.append(value.as_seconds())
896 else:
897 vals.append(value)
898 continue
900 prop = props[col[1:]]
901 value = values[col[1:]]
902 if value is not None:
903 value = self.to_sql_value(prop.__class__)(value)
904 vals.append(value)
905 vals.append(nodeid)
906 vals = tuple(vals)
908 # make sure the ordering is correct for column name -> column value
909 s = ','.join([self.arg for x in cols]) + ',%s'%self.arg
910 cols = ','.join([col for col,dt in cols]) + ',id'
912 # perform the inserts
913 sql = 'insert into _%s (%s) values (%s)'%(classname, cols, s)
914 self.sql(sql, vals)
916 # insert the multilink rows
917 for col in mls:
918 t = '%s_%s'%(classname, col)
919 for entry in node[col]:
920 sql = 'insert into %s (linkid, nodeid) values (%s,%s)'%(t,
921 self.arg, self.arg)
922 self.sql(sql, (entry, nodeid))
924 def setnode(self, classname, nodeid, values, multilink_changes={}):
925 """ Change the specified node.
926 """
927 self.log_debug('setnode %s%s %r'
928 % (classname, nodeid, values))
930 # clear this node out of the cache if it's in there
931 key = (classname, nodeid)
932 if key in self.cache:
933 del self.cache[key]
934 self.cache_lru.remove(key)
936 cl = self.classes[classname]
937 props = cl.getprops()
939 cols = []
940 mls = []
941 # add the multilinks separately
942 for col in values:
943 prop = props[col]
944 if isinstance(prop, Multilink):
945 mls.append(col)
946 elif isinstance(prop, Interval):
947 # Intervals store the seconds value too
948 cols.append(col)
949 # extra leading '_' added by code below
950 cols.append('_' +col + '_int__')
951 else:
952 cols.append(col)
953 cols.sort()
955 # figure the values to insert
956 vals = []
957 for col in cols:
958 if col.endswith('_int__'):
959 # XXX eugh, this test suxxors
960 # Intervals store the seconds value too
961 col = col[1:-6]
962 prop = props[col]
963 value = values[col]
964 if value is None:
965 vals.append(None)
966 else:
967 vals.append(value.as_seconds())
968 else:
969 prop = props[col]
970 value = values[col]
971 if value is None:
972 e = None
973 else:
974 e = self.to_sql_value(prop.__class__)(value)
975 vals.append(e)
977 vals.append(int(nodeid))
978 vals = tuple(vals)
980 # if there's any updates to regular columns, do them
981 if cols:
982 # make sure the ordering is correct for column name -> column value
983 s = ','.join(['_%s=%s'%(x, self.arg) for x in cols])
984 cols = ','.join(cols)
986 # perform the update
987 sql = 'update _%s set %s where id=%s'%(classname, s, self.arg)
988 self.sql(sql, vals)
990 # we're probably coming from an import, not a change
991 if not multilink_changes:
992 for name in mls:
993 prop = props[name]
994 value = values[name]
996 t = '%s_%s'%(classname, name)
998 # clear out previous values for this node
999 # XXX numeric ids
1000 self.sql('delete from %s where nodeid=%s'%(t, self.arg),
1001 (nodeid,))
1003 # insert the values for this node
1004 for entry in values[name]:
1005 sql = 'insert into %s (linkid, nodeid) values (%s,%s)'%(t,
1006 self.arg, self.arg)
1007 # XXX numeric ids
1008 self.sql(sql, (entry, nodeid))
1010 # we have multilink changes to apply
1011 for col, (add, remove) in multilink_changes.iteritems():
1012 tn = '%s_%s'%(classname, col)
1013 if add:
1014 sql = 'insert into %s (nodeid, linkid) values (%s,%s)'%(tn,
1015 self.arg, self.arg)
1016 for addid in add:
1017 # XXX numeric ids
1018 self.sql(sql, (int(nodeid), int(addid)))
1019 if remove:
1020 s = ','.join([self.arg]*len(remove))
1021 sql = 'delete from %s where nodeid=%s and linkid in (%s)'%(tn,
1022 self.arg, s)
1023 # XXX numeric ids
1024 self.sql(sql, [int(nodeid)] + remove)
1026 sql_to_hyperdb_value = {
1027 hyperdb.String : str,
1028 hyperdb.Date : lambda x:date.Date(str(x).replace(' ', '.')),
1029 # hyperdb.Link : int, # XXX numeric ids
1030 hyperdb.Link : str,
1031 hyperdb.Interval : date.Interval,
1032 hyperdb.Password : lambda x: password.Password(encrypted=x),
1033 hyperdb.Boolean : _bool_cvt,
1034 hyperdb.Number : _num_cvt,
1035 hyperdb.Multilink : lambda x: x, # used in journal marshalling
1036 }
1038 def to_hyperdb_value(self, propklass):
1040 fn = self.sql_to_hyperdb_value.get(propklass)
1041 if fn:
1042 return fn
1044 for k, v in self.sql_to_hyperdb_value.iteritems():
1045 if issubclass(propklass, k):
1046 return v
1048 raise ValueError('%r is not a hyperdb property class' % propklass)
1050 def getnode(self, classname, nodeid):
1051 """ Get a node from the database.
1052 """
1053 # see if we have this node cached
1054 key = (classname, nodeid)
1055 if key in self.cache:
1056 # push us back to the top of the LRU
1057 self.cache_lru.remove(key)
1058 self.cache_lru.insert(0, key)
1059 if __debug__:
1060 self.stats['cache_hits'] += 1
1061 # return the cached information
1062 return self.cache[key]
1064 if __debug__:
1065 self.stats['cache_misses'] += 1
1066 start_t = time.time()
1068 # figure the columns we're fetching
1069 cl = self.classes[classname]
1070 cols, mls = self.determine_columns(list(cl.properties.iteritems()))
1071 scols = ','.join([col for col,dt in cols])
1073 # perform the basic property fetch
1074 sql = 'select %s from _%s where id=%s'%(scols, classname, self.arg)
1075 self.sql(sql, (nodeid,))
1077 values = self.sql_fetchone()
1078 if values is None:
1079 raise IndexError('no such %s node %s'%(classname, nodeid))
1081 # make up the node
1082 node = {}
1083 props = cl.getprops(protected=1)
1084 for col in range(len(cols)):
1085 name = cols[col][0][1:]
1086 if name.endswith('_int__'):
1087 # XXX eugh, this test suxxors
1088 # ignore the special Interval-as-seconds column
1089 continue
1090 value = values[col]
1091 if value is not None:
1092 value = self.to_hyperdb_value(props[name].__class__)(value)
1093 node[name] = value
1096 # now the multilinks
1097 for col in mls:
1098 # get the link ids
1099 sql = 'select linkid from %s_%s where nodeid=%s'%(classname, col,
1100 self.arg)
1101 self.sql(sql, (nodeid,))
1102 # extract the first column from the result
1103 # XXX numeric ids
1104 items = [int(x[0]) for x in self.cursor.fetchall()]
1105 items.sort ()
1106 node[col] = [str(x) for x in items]
1108 # save off in the cache
1109 key = (classname, nodeid)
1110 self.cache[key] = node
1111 # update the LRU
1112 self.cache_lru.insert(0, key)
1113 if len(self.cache_lru) > self.cache_size:
1114 del self.cache[self.cache_lru.pop()]
1116 if __debug__:
1117 self.stats['get_items'] += (time.time() - start_t)
1119 return node
1121 def destroynode(self, classname, nodeid):
1122 """Remove a node from the database. Called exclusively by the
1123 destroy() method on Class.
1124 """
1125 logging.getLogger('roundup.hyperdb').info('destroynode %s%s'%(
1126 classname, nodeid))
1128 # make sure the node exists
1129 if not self.hasnode(classname, nodeid):
1130 raise IndexError('%s has no node %s'%(classname, nodeid))
1132 # see if we have this node cached
1133 if (classname, nodeid) in self.cache:
1134 del self.cache[(classname, nodeid)]
1136 # see if there's any obvious commit actions that we should get rid of
1137 for entry in self.transactions[:]:
1138 if entry[1][:2] == (classname, nodeid):
1139 self.transactions.remove(entry)
1141 # now do the SQL
1142 sql = 'delete from _%s where id=%s'%(classname, self.arg)
1143 self.sql(sql, (nodeid,))
1145 # remove from multilnks
1146 cl = self.getclass(classname)
1147 x, mls = self.determine_columns(list(cl.properties.iteritems()))
1148 for col in mls:
1149 # get the link ids
1150 sql = 'delete from %s_%s where nodeid=%s'%(classname, col, self.arg)
1151 self.sql(sql, (nodeid,))
1153 # remove journal entries
1154 sql = 'delete from %s__journal where nodeid=%s'%(classname, self.arg)
1155 self.sql(sql, (nodeid,))
1157 # cleanup any blob filestorage when we commit
1158 self.transactions.append((FileStorage.destroy, (self, classname, nodeid)))
1160 def hasnode(self, classname, nodeid):
1161 """ Determine if the database has a given node.
1162 """
1163 # If this node is in the cache, then we do not need to go to
1164 # the database. (We don't consider this an LRU hit, though.)
1165 if (classname, nodeid) in self.cache:
1166 # Return 1, not True, to match the type of the result of
1167 # the SQL operation below.
1168 return 1
1169 sql = 'select count(*) from _%s where id=%s'%(classname, self.arg)
1170 self.sql(sql, (nodeid,))
1171 return int(self.cursor.fetchone()[0])
1173 def countnodes(self, classname):
1174 """ Count the number of nodes that exist for a particular Class.
1175 """
1176 sql = 'select count(*) from _%s'%classname
1177 self.sql(sql)
1178 return self.cursor.fetchone()[0]
1180 def addjournal(self, classname, nodeid, action, params, creator=None,
1181 creation=None):
1182 """ Journal the Action
1183 'action' may be:
1185 'create' or 'set' -- 'params' is a dictionary of property values
1186 'link' or 'unlink' -- 'params' is (classname, nodeid, propname)
1187 'retire' -- 'params' is None
1188 """
1189 # handle supply of the special journalling parameters (usually
1190 # supplied on importing an existing database)
1191 if creator:
1192 journaltag = creator
1193 else:
1194 journaltag = self.getuid()
1195 if creation:
1196 journaldate = creation
1197 else:
1198 journaldate = date.Date()
1200 # create the journal entry
1201 cols = 'nodeid,date,tag,action,params'
1203 self.log_debug('addjournal %s%s %r %s %s %r'%(classname,
1204 nodeid, journaldate, journaltag, action, params))
1206 # make the journalled data marshallable
1207 if isinstance(params, type({})):
1208 self._journal_marshal(params, classname)
1210 params = repr(params)
1212 dc = self.to_sql_value(hyperdb.Date)
1213 journaldate = dc(journaldate)
1215 self.save_journal(classname, cols, nodeid, journaldate,
1216 journaltag, action, params)
1218 def setjournal(self, classname, nodeid, journal):
1219 """Set the journal to the "journal" list."""
1220 # clear out any existing entries
1221 self.sql('delete from %s__journal where nodeid=%s'%(classname,
1222 self.arg), (nodeid,))
1224 # create the journal entry
1225 cols = 'nodeid,date,tag,action,params'
1227 dc = self.to_sql_value(hyperdb.Date)
1228 for nodeid, journaldate, journaltag, action, params in journal:
1229 self.log_debug('addjournal %s%s %r %s %s %r'%(
1230 classname, nodeid, journaldate, journaltag, action,
1231 params))
1233 # make the journalled data marshallable
1234 if isinstance(params, type({})):
1235 self._journal_marshal(params, classname)
1236 params = repr(params)
1238 self.save_journal(classname, cols, nodeid, dc(journaldate),
1239 journaltag, action, params)
1241 def _journal_marshal(self, params, classname):
1242 """Convert the journal params values into safely repr'able and
1243 eval'able values."""
1244 properties = self.getclass(classname).getprops()
1245 for param, value in params.iteritems():
1246 if not value:
1247 continue
1248 property = properties[param]
1249 cvt = self.to_sql_value(property.__class__)
1250 if isinstance(property, Password):
1251 params[param] = cvt(value)
1252 elif isinstance(property, Date):
1253 params[param] = cvt(value)
1254 elif isinstance(property, Interval):
1255 params[param] = cvt(value)
1256 elif isinstance(property, Boolean):
1257 params[param] = cvt(value)
1259 def getjournal(self, classname, nodeid):
1260 """ get the journal for id
1261 """
1262 # make sure the node exists
1263 if not self.hasnode(classname, nodeid):
1264 raise IndexError('%s has no node %s'%(classname, nodeid))
1266 cols = ','.join('nodeid date tag action params'.split())
1267 journal = self.load_journal(classname, cols, nodeid)
1269 # now unmarshal the data
1270 dc = self.to_hyperdb_value(hyperdb.Date)
1271 res = []
1272 properties = self.getclass(classname).getprops()
1273 for nodeid, date_stamp, user, action, params in journal:
1274 params = eval(params)
1275 if isinstance(params, type({})):
1276 for param, value in params.iteritems():
1277 if not value:
1278 continue
1279 property = properties.get(param, None)
1280 if property is None:
1281 # deleted property
1282 continue
1283 cvt = self.to_hyperdb_value(property.__class__)
1284 if isinstance(property, Password):
1285 params[param] = cvt(value)
1286 elif isinstance(property, Date):
1287 params[param] = cvt(value)
1288 elif isinstance(property, Interval):
1289 params[param] = cvt(value)
1290 elif isinstance(property, Boolean):
1291 params[param] = cvt(value)
1292 # XXX numeric ids
1293 res.append((str(nodeid), dc(date_stamp), user, action, params))
1294 return res
1296 def save_journal(self, classname, cols, nodeid, journaldate,
1297 journaltag, action, params):
1298 """ Save the journal entry to the database
1299 """
1300 entry = (nodeid, journaldate, journaltag, action, params)
1302 # do the insert
1303 a = self.arg
1304 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(
1305 classname, cols, a, a, a, a, a)
1306 self.sql(sql, entry)
1308 def load_journal(self, classname, cols, nodeid):
1309 """ Load the journal from the database
1310 """
1311 # now get the journal entries
1312 sql = 'select %s from %s__journal where nodeid=%s order by date'%(
1313 cols, classname, self.arg)
1314 self.sql(sql, (nodeid,))
1315 return self.cursor.fetchall()
1317 def pack(self, pack_before):
1318 """ Delete all journal entries except "create" before 'pack_before'.
1319 """
1320 date_stamp = self.to_sql_value(Date)(pack_before)
1322 # do the delete
1323 for classname in self.classes:
1324 sql = "delete from %s__journal where date<%s and "\
1325 "action<>'create'"%(classname, self.arg)
1326 self.sql(sql, (date_stamp,))
1328 def sql_commit(self, fail_ok=False):
1329 """ Actually commit to the database.
1330 """
1331 logging.getLogger('roundup.hyperdb').info('commit')
1333 self.conn.commit()
1335 # open a new cursor for subsequent work
1336 self.cursor = self.conn.cursor()
1338 def commit(self, fail_ok=False):
1339 """ Commit the current transactions.
1341 Save all data changed since the database was opened or since the
1342 last commit() or rollback().
1344 fail_ok indicates that the commit is allowed to fail. This is used
1345 in the web interface when committing cleaning of the session
1346 database. We don't care if there's a concurrency issue there.
1348 The only backend this seems to affect is postgres.
1349 """
1350 # commit the database
1351 self.sql_commit(fail_ok)
1353 # now, do all the other transaction stuff
1354 for method, args in self.transactions:
1355 method(*args)
1357 # save the indexer
1358 self.indexer.save_index()
1360 # clear out the transactions
1361 self.transactions = []
1363 # clear the cache: Don't carry over cached values from one
1364 # transaction to the next (there may be other changes from other
1365 # transactions)
1366 self.clearCache()
1368 def sql_rollback(self):
1369 self.conn.rollback()
1371 def rollback(self):
1372 """ Reverse all actions from the current transaction.
1374 Undo all the changes made since the database was opened or the last
1375 commit() or rollback() was performed.
1376 """
1377 logging.getLogger('roundup.hyperdb').info('rollback')
1379 self.sql_rollback()
1381 # roll back "other" transaction stuff
1382 for method, args in self.transactions:
1383 # delete temporary files
1384 if method == self.doStoreFile:
1385 self.rollbackStoreFile(*args)
1386 self.transactions = []
1388 # clear the cache
1389 self.clearCache()
1391 def sql_close(self):
1392 logging.getLogger('roundup.hyperdb').info('close')
1393 self.conn.close()
1395 def close(self):
1396 """ Close off the connection.
1397 """
1398 self.indexer.close()
1399 self.sql_close()
1401 #
1402 # The base Class class
1403 #
1404 class Class(hyperdb.Class):
1405 """ The handle to a particular class of nodes in a hyperdatabase.
1407 All methods except __repr__ and getnode must be implemented by a
1408 concrete backend Class.
1409 """
1411 def schema(self):
1412 """ A dumpable version of the schema that we can store in the
1413 database
1414 """
1415 return (self.key, [(x, repr(y)) for x,y in self.properties.iteritems()])
1417 def enableJournalling(self):
1418 """Turn journalling on for this class
1419 """
1420 self.do_journal = 1
1422 def disableJournalling(self):
1423 """Turn journalling off for this class
1424 """
1425 self.do_journal = 0
1427 # Editing nodes:
1428 def create(self, **propvalues):
1429 """ Create a new node of this class and return its id.
1431 The keyword arguments in 'propvalues' map property names to values.
1433 The values of arguments must be acceptable for the types of their
1434 corresponding properties or a TypeError is raised.
1436 If this class has a key property, it must be present and its value
1437 must not collide with other key strings or a ValueError is raised.
1439 Any other properties on this class that are missing from the
1440 'propvalues' dictionary are set to None.
1442 If an id in a link or multilink property does not refer to a valid
1443 node, an IndexError is raised.
1444 """
1445 self.fireAuditors('create', None, propvalues)
1446 newid = self.create_inner(**propvalues)
1447 self.fireReactors('create', newid, None)
1448 return newid
1450 def create_inner(self, **propvalues):
1451 """ Called by create, in-between the audit and react calls.
1452 """
1453 if 'id' in propvalues:
1454 raise KeyError('"id" is reserved')
1456 if self.db.journaltag is None:
1457 raise DatabaseError(_('Database open read-only'))
1459 if ('creator' in propvalues or 'actor' in propvalues or
1460 'creation' in propvalues or 'activity' in propvalues):
1461 raise KeyError('"creator", "actor", "creation" and '
1462 '"activity" are reserved')
1464 # new node's id
1465 newid = self.db.newid(self.classname)
1467 # validate propvalues
1468 num_re = re.compile('^\d+$')
1469 for key, value in propvalues.iteritems():
1470 if key == self.key:
1471 try:
1472 self.lookup(value)
1473 except KeyError:
1474 pass
1475 else:
1476 raise ValueError('node with key "%s" exists'%value)
1478 # try to handle this property
1479 try:
1480 prop = self.properties[key]
1481 except KeyError:
1482 raise KeyError('"%s" has no property "%s"'%(self.classname,
1483 key))
1485 if value is not None and isinstance(prop, Link):
1486 if type(value) != type(''):
1487 raise ValueError('link value must be String')
1488 link_class = self.properties[key].classname
1489 # if it isn't a number, it's a key
1490 if not num_re.match(value):
1491 try:
1492 value = self.db.classes[link_class].lookup(value)
1493 except (TypeError, KeyError):
1494 raise IndexError('new property "%s": %s not a %s'%(
1495 key, value, link_class))
1496 elif not self.db.getclass(link_class).hasnode(value):
1497 raise IndexError('%s has no node %s'%(link_class,
1498 value))
1500 # save off the value
1501 propvalues[key] = value
1503 # register the link with the newly linked node
1504 if self.do_journal and self.properties[key].do_journal:
1505 self.db.addjournal(link_class, value, 'link',
1506 (self.classname, newid, key))
1508 elif isinstance(prop, Multilink):
1509 if value is None:
1510 value = []
1511 if not hasattr(value, '__iter__'):
1512 raise TypeError('new property "%s" not an iterable of ids'%key)
1513 # clean up and validate the list of links
1514 link_class = self.properties[key].classname
1515 l = []
1516 for entry in value:
1517 if type(entry) != type(''):
1518 raise ValueError('"%s" multilink value (%r) '
1519 'must contain Strings'%(key, value))
1520 # if it isn't a number, it's a key
1521 if not num_re.match(entry):
1522 try:
1523 entry = self.db.classes[link_class].lookup(entry)
1524 except (TypeError, KeyError):
1525 raise IndexError('new property "%s": %s not a %s'%(
1526 key, entry, self.properties[key].classname))
1527 l.append(entry)
1528 value = l
1529 propvalues[key] = value
1531 # handle additions
1532 for nodeid in value:
1533 if not self.db.getclass(link_class).hasnode(nodeid):
1534 raise IndexError('%s has no node %s'%(link_class,
1535 nodeid))
1536 # register the link with the newly linked node
1537 if self.do_journal and self.properties[key].do_journal:
1538 self.db.addjournal(link_class, nodeid, 'link',
1539 (self.classname, newid, key))
1541 elif isinstance(prop, String):
1542 if type(value) != type('') and type(value) != type(u''):
1543 raise TypeError('new property "%s" not a string'%key)
1544 if prop.indexme:
1545 self.db.indexer.add_text((self.classname, newid, key),
1546 value)
1548 elif isinstance(prop, Password):
1549 if not isinstance(value, password.Password):
1550 raise TypeError('new property "%s" not a Password'%key)
1552 elif isinstance(prop, Date):
1553 if value is not None and not isinstance(value, date.Date):
1554 raise TypeError('new property "%s" not a Date'%key)
1556 elif isinstance(prop, Interval):
1557 if value is not None and not isinstance(value, date.Interval):
1558 raise TypeError('new property "%s" not an Interval'%key)
1560 elif value is not None and isinstance(prop, Number):
1561 try:
1562 float(value)
1563 except ValueError:
1564 raise TypeError('new property "%s" not numeric'%key)
1566 elif value is not None and isinstance(prop, Boolean):
1567 try:
1568 int(value)
1569 except ValueError:
1570 raise TypeError('new property "%s" not boolean'%key)
1572 # make sure there's data where there needs to be
1573 for key, prop in self.properties.iteritems():
1574 if key in propvalues:
1575 continue
1576 if key == self.key:
1577 raise ValueError('key property "%s" is required'%key)
1578 if isinstance(prop, Multilink):
1579 propvalues[key] = []
1580 else:
1581 propvalues[key] = None
1583 # done
1584 self.db.addnode(self.classname, newid, propvalues)
1585 if self.do_journal:
1586 self.db.addjournal(self.classname, newid, ''"create", {})
1588 # XXX numeric ids
1589 return str(newid)
1591 def get(self, nodeid, propname, default=_marker, cache=1):
1592 """Get the value of a property on an existing node of this class.
1594 'nodeid' must be the id of an existing node of this class or an
1595 IndexError is raised. 'propname' must be the name of a property
1596 of this class or a KeyError is raised.
1598 'cache' exists for backwards compatibility, and is not used.
1599 """
1600 if propname == 'id':
1601 return nodeid
1603 # get the node's dict
1604 d = self.db.getnode(self.classname, nodeid)
1606 if propname == 'creation':
1607 if 'creation' in d:
1608 return d['creation']
1609 else:
1610 return date.Date()
1611 if propname == 'activity':
1612 if 'activity' in d:
1613 return d['activity']
1614 else:
1615 return date.Date()
1616 if propname == 'creator':
1617 if 'creator' in d:
1618 return d['creator']
1619 else:
1620 return self.db.getuid()
1621 if propname == 'actor':
1622 if 'actor' in d:
1623 return d['actor']
1624 else:
1625 return self.db.getuid()
1627 # get the property (raises KeyErorr if invalid)
1628 prop = self.properties[propname]
1630 # handle there being no value in the table for the property
1631 if propname not in d or d[propname] is None:
1632 if default is _marker:
1633 if isinstance(prop, Multilink):
1634 return []
1635 else:
1636 return None
1637 else:
1638 return default
1640 # don't pass our list to other code
1641 if isinstance(prop, Multilink):
1642 return d[propname][:]
1644 return d[propname]
1646 def set(self, nodeid, **propvalues):
1647 """Modify a property on an existing node of this class.
1649 'nodeid' must be the id of an existing node of this class or an
1650 IndexError is raised.
1652 Each key in 'propvalues' must be the name of a property of this
1653 class or a KeyError is raised.
1655 All values in 'propvalues' must be acceptable types for their
1656 corresponding properties or a TypeError is raised.
1658 If the value of the key property is set, it must not collide with
1659 other key strings or a ValueError is raised.
1661 If the value of a Link or Multilink property contains an invalid
1662 node id, a ValueError is raised.
1663 """
1664 self.fireAuditors('set', nodeid, propvalues)
1665 oldvalues = copy.deepcopy(self.db.getnode(self.classname, nodeid))
1666 propvalues = self.set_inner(nodeid, **propvalues)
1667 self.fireReactors('set', nodeid, oldvalues)
1668 return propvalues
1670 def set_inner(self, nodeid, **propvalues):
1671 """ Called by set, in-between the audit and react calls.
1672 """
1673 if not propvalues:
1674 return propvalues
1676 if ('creator' in propvalues or 'actor' in propvalues or
1677 'creation' in propvalues or 'activity' in propvalues):
1678 raise KeyError('"creator", "actor", "creation" and '
1679 '"activity" are reserved')
1681 if 'id' in propvalues:
1682 raise KeyError('"id" is reserved')
1684 if self.db.journaltag is None:
1685 raise DatabaseError(_('Database open read-only'))
1687 node = self.db.getnode(self.classname, nodeid)
1688 if self.is_retired(nodeid):
1689 raise IndexError('Requested item is retired')
1690 num_re = re.compile('^\d+$')
1692 # make a copy of the values dictionary - we'll modify the contents
1693 propvalues = propvalues.copy()
1695 # if the journal value is to be different, store it in here
1696 journalvalues = {}
1698 # remember the add/remove stuff for multilinks, making it easier
1699 # for the Database layer to do its stuff
1700 multilink_changes = {}
1702 for propname, value in list(propvalues.items()):
1703 # check to make sure we're not duplicating an existing key
1704 if propname == self.key and node[propname] != value:
1705 try:
1706 self.lookup(value)
1707 except KeyError:
1708 pass
1709 else:
1710 raise ValueError('node with key "%s" exists'%value)
1712 # this will raise the KeyError if the property isn't valid
1713 # ... we don't use getprops() here because we only care about
1714 # the writeable properties.
1715 try:
1716 prop = self.properties[propname]
1717 except KeyError:
1718 raise KeyError('"%s" has no property named "%s"'%(
1719 self.classname, propname))
1721 # if the value's the same as the existing value, no sense in
1722 # doing anything
1723 current = node.get(propname, None)
1724 if value == current:
1725 del propvalues[propname]
1726 continue
1727 journalvalues[propname] = current
1729 # do stuff based on the prop type
1730 if isinstance(prop, Link):
1731 link_class = prop.classname
1732 # if it isn't a number, it's a key
1733 if value is not None and not isinstance(value, type('')):
1734 raise ValueError('property "%s" link value be a string'%(
1735 propname))
1736 if isinstance(value, type('')) and not num_re.match(value):
1737 try:
1738 value = self.db.classes[link_class].lookup(value)
1739 except (TypeError, KeyError):
1740 raise IndexError('new property "%s": %s not a %s'%(
1741 propname, value, prop.classname))
1743 if (value is not None and
1744 not self.db.getclass(link_class).hasnode(value)):
1745 raise IndexError('%s has no node %s'%(link_class,
1746 value))
1748 if self.do_journal and prop.do_journal:
1749 # register the unlink with the old linked node
1750 if node[propname] is not None:
1751 self.db.addjournal(link_class, node[propname],
1752 ''"unlink", (self.classname, nodeid, propname))
1754 # register the link with the newly linked node
1755 if value is not None:
1756 self.db.addjournal(link_class, value, ''"link",
1757 (self.classname, nodeid, propname))
1759 elif isinstance(prop, Multilink):
1760 if value is None:
1761 value = []
1762 if not hasattr(value, '__iter__'):
1763 raise TypeError('new property "%s" not an iterable of'
1764 ' ids'%propname)
1765 link_class = self.properties[propname].classname
1766 l = []
1767 for entry in value:
1768 # if it isn't a number, it's a key
1769 if type(entry) != type(''):
1770 raise ValueError('new property "%s" link value '
1771 'must be a string'%propname)
1772 if not num_re.match(entry):
1773 try:
1774 entry = self.db.classes[link_class].lookup(entry)
1775 except (TypeError, KeyError):
1776 raise IndexError('new property "%s": %s not a %s'%(
1777 propname, entry,
1778 self.properties[propname].classname))
1779 l.append(entry)
1780 value = l
1781 propvalues[propname] = value
1783 # figure the journal entry for this property
1784 add = []
1785 remove = []
1787 # handle removals
1788 if propname in node:
1789 l = node[propname]
1790 else:
1791 l = []
1792 for id in l[:]:
1793 if id in value:
1794 continue
1795 # register the unlink with the old linked node
1796 if self.do_journal and self.properties[propname].do_journal:
1797 self.db.addjournal(link_class, id, 'unlink',
1798 (self.classname, nodeid, propname))
1799 l.remove(id)
1800 remove.append(id)
1802 # handle additions
1803 for id in value:
1804 if id in l:
1805 continue
1806 # We can safely check this condition after
1807 # checking that this is an addition to the
1808 # multilink since the condition was checked for
1809 # existing entries at the point they were added to
1810 # the multilink. Since the hasnode call will
1811 # result in a SQL query, it is more efficient to
1812 # avoid the check if possible.
1813 if not self.db.getclass(link_class).hasnode(id):
1814 raise IndexError('%s has no node %s'%(link_class,
1815 id))
1816 # register the link with the newly linked node
1817 if self.do_journal and self.properties[propname].do_journal:
1818 self.db.addjournal(link_class, id, 'link',
1819 (self.classname, nodeid, propname))
1820 l.append(id)
1821 add.append(id)
1823 # figure the journal entry
1824 l = []
1825 if add:
1826 l.append(('+', add))
1827 if remove:
1828 l.append(('-', remove))
1829 multilink_changes[propname] = (add, remove)
1830 if l:
1831 journalvalues[propname] = tuple(l)
1833 elif isinstance(prop, String):
1834 if value is not None and type(value) != type('') and type(value) != type(u''):
1835 raise TypeError('new property "%s" not a string'%propname)
1836 if prop.indexme:
1837 if value is None: value = ''
1838 self.db.indexer.add_text((self.classname, nodeid, propname),
1839 value)
1841 elif isinstance(prop, Password):
1842 if not isinstance(value, password.Password):
1843 raise TypeError('new property "%s" not a Password'%propname)
1844 propvalues[propname] = value
1846 elif value is not None and isinstance(prop, Date):
1847 if not isinstance(value, date.Date):
1848 raise TypeError('new property "%s" not a Date'% propname)
1849 propvalues[propname] = value
1851 elif value is not None and isinstance(prop, Interval):
1852 if not isinstance(value, date.Interval):
1853 raise TypeError('new property "%s" not an '
1854 'Interval'%propname)
1855 propvalues[propname] = value
1857 elif value is not None and isinstance(prop, Number):
1858 try:
1859 float(value)
1860 except ValueError:
1861 raise TypeError('new property "%s" not numeric'%propname)
1863 elif value is not None and isinstance(prop, Boolean):
1864 try:
1865 int(value)
1866 except ValueError:
1867 raise TypeError('new property "%s" not boolean'%propname)
1869 # nothing to do?
1870 if not propvalues:
1871 return propvalues
1873 # update the activity time
1874 propvalues['activity'] = date.Date()
1875 propvalues['actor'] = self.db.getuid()
1877 # do the set
1878 self.db.setnode(self.classname, nodeid, propvalues, multilink_changes)
1880 # remove the activity props now they're handled
1881 del propvalues['activity']
1882 del propvalues['actor']
1884 # journal the set
1885 if self.do_journal:
1886 self.db.addjournal(self.classname, nodeid, ''"set", journalvalues)
1888 return propvalues
1890 def retire(self, nodeid):
1891 """Retire a node.
1893 The properties on the node remain available from the get() method,
1894 and the node's id is never reused.
1896 Retired nodes are not returned by the find(), list(), or lookup()
1897 methods, and other nodes may reuse the values of their key properties.
1898 """
1899 if self.db.journaltag is None:
1900 raise DatabaseError(_('Database open read-only'))
1902 self.fireAuditors('retire', nodeid, None)
1904 # use the arg for __retired__ to cope with any odd database type
1905 # conversion (hello, sqlite)
1906 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
1907 self.db.arg, self.db.arg)
1908 self.db.sql(sql, (nodeid, nodeid))
1909 if self.do_journal:
1910 self.db.addjournal(self.classname, nodeid, ''"retired", None)
1912 self.fireReactors('retire', nodeid, None)
1914 def restore(self, nodeid):
1915 """Restore a retired node.
1917 Make node available for all operations like it was before retirement.
1918 """
1919 if self.db.journaltag is None:
1920 raise DatabaseError(_('Database open read-only'))
1922 node = self.db.getnode(self.classname, nodeid)
1923 # check if key property was overrided
1924 key = self.getkey()
1925 try:
1926 id = self.lookup(node[key])
1927 except KeyError:
1928 pass
1929 else:
1930 raise KeyError("Key property (%s) of retired node clashes "
1931 "with existing one (%s)" % (key, node[key]))
1933 self.fireAuditors('restore', nodeid, None)
1934 # use the arg for __retired__ to cope with any odd database type
1935 # conversion (hello, sqlite)
1936 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
1937 self.db.arg, self.db.arg)
1938 self.db.sql(sql, (0, nodeid))
1939 if self.do_journal:
1940 self.db.addjournal(self.classname, nodeid, ''"restored", None)
1942 self.fireReactors('restore', nodeid, None)
1944 def is_retired(self, nodeid):
1945 """Return true if the node is rerired
1946 """
1947 sql = 'select __retired__ from _%s where id=%s'%(self.classname,
1948 self.db.arg)
1949 self.db.sql(sql, (nodeid,))
1950 return int(self.db.sql_fetchone()[0]) > 0
1952 def destroy(self, nodeid):
1953 """Destroy a node.
1955 WARNING: this method should never be used except in extremely rare
1956 situations where there could never be links to the node being
1957 deleted
1959 WARNING: use retire() instead
1961 WARNING: the properties of this node will not be available ever again
1963 WARNING: really, use retire() instead
1965 Well, I think that's enough warnings. This method exists mostly to
1966 support the session storage of the cgi interface.
1968 The node is completely removed from the hyperdb, including all journal
1969 entries. It will no longer be available, and will generally break code
1970 if there are any references to the node.
1971 """
1972 if self.db.journaltag is None:
1973 raise DatabaseError(_('Database open read-only'))
1974 self.db.destroynode(self.classname, nodeid)
1976 def history(self, nodeid):
1977 """Retrieve the journal of edits on a particular node.
1979 'nodeid' must be the id of an existing node of this class or an
1980 IndexError is raised.
1982 The returned list contains tuples of the form
1984 (nodeid, date, tag, action, params)
1986 'date' is a Timestamp object specifying the time of the change and
1987 'tag' is the journaltag specified when the database was opened.
1988 """
1989 if not self.do_journal:
1990 raise ValueError('Journalling is disabled for this class')
1991 return self.db.getjournal(self.classname, nodeid)
1993 # Locating nodes:
1994 def hasnode(self, nodeid):
1995 """Determine if the given nodeid actually exists
1996 """
1997 return self.db.hasnode(self.classname, nodeid)
1999 def setkey(self, propname):
2000 """Select a String property of this class to be the key property.
2002 'propname' must be the name of a String property of this class or
2003 None, or a TypeError is raised. The values of the key property on
2004 all existing nodes must be unique or a ValueError is raised.
2005 """
2006 prop = self.getprops()[propname]
2007 if not isinstance(prop, String):
2008 raise TypeError('key properties must be String')
2009 self.key = propname
2011 def getkey(self):
2012 """Return the name of the key property for this class or None."""
2013 return self.key
2015 def lookup(self, keyvalue):
2016 """Locate a particular node by its key property and return its id.
2018 If this class has no key property, a TypeError is raised. If the
2019 'keyvalue' matches one of the values for the key property among
2020 the nodes in this class, the matching node's id is returned;
2021 otherwise a KeyError is raised.
2022 """
2023 if not self.key:
2024 raise TypeError('No key property set for class %s'%self.classname)
2026 # use the arg to handle any odd database type conversion (hello,
2027 # sqlite)
2028 sql = "select id from _%s where _%s=%s and __retired__=%s"%(
2029 self.classname, self.key, self.db.arg, self.db.arg)
2030 self.db.sql(sql, (str(keyvalue), 0))
2032 # see if there was a result that's not retired
2033 row = self.db.sql_fetchone()
2034 if not row:
2035 raise KeyError('No key (%s) value "%s" for "%s"'%(self.key,
2036 keyvalue, self.classname))
2038 # return the id
2039 # XXX numeric ids
2040 return str(row[0])
2042 def find(self, **propspec):
2043 """Get the ids of nodes in this class which link to the given nodes.
2045 'propspec' consists of keyword args propname=nodeid or
2046 propname={nodeid:1, }
2047 'propname' must be the name of a property in this class, or a
2048 KeyError is raised. That property must be a Link or
2049 Multilink property, or a TypeError is raised.
2051 Any node in this class whose 'propname' property links to any of
2052 the nodeids will be returned. Examples::
2054 db.issue.find(messages='1')
2055 db.issue.find(messages={'1':1,'3':1}, files={'7':1})
2056 """
2057 # shortcut
2058 if not propspec:
2059 return []
2061 # validate the args
2062 props = self.getprops()
2063 for propname, nodeids in propspec.iteritems():
2064 # check the prop is OK
2065 prop = props[propname]
2066 if not isinstance(prop, Link) and not isinstance(prop, Multilink):
2067 raise TypeError("'%s' not a Link/Multilink property"%propname)
2069 # first, links
2070 a = self.db.arg
2071 allvalues = ()
2072 sql = []
2073 where = []
2074 for prop, values in propspec.iteritems():
2075 if not isinstance(props[prop], hyperdb.Link):
2076 continue
2077 if type(values) is type({}) and len(values) == 1:
2078 values = list(values)[0]
2079 if type(values) is type(''):
2080 allvalues += (values,)
2081 where.append('_%s = %s'%(prop, a))
2082 elif values is None:
2083 where.append('_%s is NULL'%prop)
2084 else:
2085 values = list(values)
2086 s = ''
2087 if None in values:
2088 values.remove(None)
2089 s = '_%s is NULL or '%prop
2090 allvalues += tuple(values)
2091 s += '_%s in (%s)'%(prop, ','.join([a]*len(values)))
2092 where.append('(' + s +')')
2093 if where:
2094 allvalues = (0, ) + allvalues
2095 sql.append("""select id from _%s where __retired__=%s
2096 and %s"""%(self.classname, a, ' and '.join(where)))
2098 # now multilinks
2099 for prop, values in propspec.iteritems():
2100 if not isinstance(props[prop], hyperdb.Multilink):
2101 continue
2102 if not values:
2103 continue
2104 allvalues += (0, )
2105 if type(values) is type(''):
2106 allvalues += (values,)
2107 s = a
2108 else:
2109 allvalues += tuple(values)
2110 s = ','.join([a]*len(values))
2111 tn = '%s_%s'%(self.classname, prop)
2112 sql.append("""select id from _%s, %s where __retired__=%s
2113 and id = %s.nodeid and %s.linkid in (%s)"""%(self.classname,
2114 tn, a, tn, tn, s))
2116 if not sql:
2117 return []
2118 sql = ' union '.join(sql)
2119 self.db.sql(sql, allvalues)
2120 # XXX numeric ids
2121 l = [str(x[0]) for x in self.db.sql_fetchall()]
2122 return l
2124 def stringFind(self, **requirements):
2125 """Locate a particular node by matching a set of its String
2126 properties in a caseless search.
2128 If the property is not a String property, a TypeError is raised.
2130 The return is a list of the id of all nodes that match.
2131 """
2132 where = []
2133 args = []
2134 for propname in requirements:
2135 prop = self.properties[propname]
2136 if not isinstance(prop, String):
2137 raise TypeError("'%s' not a String property"%propname)
2138 where.append(propname)
2139 args.append(requirements[propname].lower())
2141 # generate the where clause
2142 s = ' and '.join(['lower(_%s)=%s'%(col, self.db.arg) for col in where])
2143 sql = 'select id from _%s where %s and __retired__=%s'%(
2144 self.classname, s, self.db.arg)
2145 args.append(0)
2146 self.db.sql(sql, tuple(args))
2147 # XXX numeric ids
2148 l = [str(x[0]) for x in self.db.sql_fetchall()]
2149 return l
2151 def list(self):
2152 """ Return a list of the ids of the active nodes in this class.
2153 """
2154 return self.getnodeids(retired=0)
2156 def getnodeids(self, retired=None):
2157 """ Retrieve all the ids of the nodes for a particular Class.
2159 Set retired=None to get all nodes. Otherwise it'll get all the
2160 retired or non-retired nodes, depending on the flag.
2161 """
2162 # flip the sense of the 'retired' flag if we don't want all of them
2163 if retired is not None:
2164 args = (0, )
2165 if retired:
2166 compare = '>'
2167 else:
2168 compare = '='
2169 sql = 'select id from _%s where __retired__%s%s'%(self.classname,
2170 compare, self.db.arg)
2171 else:
2172 args = ()
2173 sql = 'select id from _%s'%self.classname
2174 self.db.sql(sql, args)
2175 # XXX numeric ids
2176 ids = [str(x[0]) for x in self.db.cursor.fetchall()]
2177 return ids
2179 def _subselect(self, classname, multilink_table):
2180 """Create a subselect. This is factored out because some
2181 databases (hmm only one, so far) doesn't support subselects
2182 look for "I can't believe it's not a toy RDBMS" in the mysql
2183 backend.
2184 """
2185 return '_%s.id not in (select nodeid from %s)'%(classname,
2186 multilink_table)
2188 # Some DBs order NULL values last. Set this variable in the backend
2189 # for prepending an order by clause for each attribute that causes
2190 # correct sort order for NULLs. Examples:
2191 # order_by_null_values = '(%s is not NULL)'
2192 # order_by_null_values = 'notnull(%s)'
2193 # The format parameter is replaced with the attribute.
2194 order_by_null_values = None
2196 def supports_subselects(self):
2197 '''Assuming DBs can do subselects, overwrite if they cannot.
2198 '''
2199 return True
2201 def _filter_multilink_expression_fallback(
2202 self, classname, multilink_table, expr):
2203 '''This is a fallback for database that do not support
2204 subselects.'''
2206 is_valid = expr.evaluate
2208 last_id, kws = None, []
2210 ids = IdListOptimizer()
2211 append = ids.append
2213 # This join and the evaluation in program space
2214 # can be expensive for larger databases!
2215 # TODO: Find a faster way to collect the data needed
2216 # to evalute the expression.
2217 # Moving the expression evaluation into the database
2218 # would be nice but this tricky: Think about the cases
2219 # where the multilink table does not have join values
2220 # needed in evaluation.
2222 stmnt = "SELECT c.id, m.linkid FROM _%s c " \
2223 "LEFT OUTER JOIN %s m " \
2224 "ON c.id = m.nodeid ORDER BY c.id" % (
2225 classname, multilink_table)
2226 self.db.sql(stmnt)
2228 # collect all multilink items for a class item
2229 for nid, kw in self.db.sql_fetchiter():
2230 if nid != last_id:
2231 if last_id is None:
2232 last_id = nid
2233 else:
2234 # we have all multilink items -> evaluate!
2235 if is_valid(kws): append(last_id)
2236 last_id, kws = nid, []
2237 if kw is not None:
2238 kws.append(kw)
2240 if last_id is not None and is_valid(kws):
2241 append(last_id)
2243 # we have ids of the classname table
2244 return ids.where("_%s.id" % classname, self.db.arg)
2246 def _filter_multilink_expression(self, classname, multilink_table, v):
2247 """ Filters out elements of the classname table that do not
2248 match the given expression.
2249 Returns tuple of 'WHERE' introns for the overall filter.
2250 """
2251 try:
2252 opcodes = [int(x) for x in v]
2253 if min(opcodes) >= -1: raise ValueError()
2255 expr = compile_expression(opcodes)
2257 if not self.supports_subselects():
2258 # We heavily rely on subselects. If there is
2259 # no decent support fall back to slower variant.
2260 return self._filter_multilink_expression_fallback(
2261 classname, multilink_table, expr)
2263 atom = \
2264 "%s IN(SELECT linkid FROM %s WHERE nodeid=a.id)" % (
2265 self.db.arg,
2266 multilink_table)
2268 intron = \
2269 "_%(classname)s.id in (SELECT id " \
2270 "FROM _%(classname)s AS a WHERE %(condition)s) " % {
2271 'classname' : classname,
2272 'condition' : expr.generate(lambda n: atom) }
2274 values = []
2275 def collect_values(n): values.append(n.x)
2276 expr.visit(collect_values)
2278 return intron, values
2279 except:
2280 # original behavior
2281 where = "%s.linkid in (%s)" % (
2282 multilink_table, ','.join([self.db.arg] * len(v)))
2283 return where, v, True # True to indicate original
2285 def filter(self, search_matches, filterspec, sort=[], group=[]):
2286 """Return a list of the ids of the active nodes in this class that
2287 match the 'filter' spec, sorted by the group spec and then the
2288 sort spec
2290 "filterspec" is {propname: value(s)}
2292 "sort" and "group" are [(dir, prop), ...] where dir is '+', '-'
2293 or None and prop is a prop name or None. Note that for
2294 backward-compatibility reasons a single (dir, prop) tuple is
2295 also allowed.
2297 "search_matches" is a container type or None
2299 The filter must match all properties specificed. If the property
2300 value to match is a list:
2302 1. String properties must match all elements in the list, and
2303 2. Other properties must match any of the elements in the list.
2304 """
2305 # we can't match anything if search_matches is empty
2306 if not search_matches and search_matches is not None:
2307 return []
2309 if __debug__:
2310 start_t = time.time()
2312 icn = self.classname
2314 # vars to hold the components of the SQL statement
2315 frum = [] # FROM clauses
2316 loj = [] # LEFT OUTER JOIN clauses
2317 where = [] # WHERE clauses
2318 args = [] # *any* positional arguments
2319 a = self.db.arg
2321 # figure the WHERE clause from the filterspec
2322 mlfilt = 0 # are we joining with Multilink tables?
2323 sortattr = self._sortattr (group = group, sort = sort)
2324 proptree = self._proptree(filterspec, sortattr)
2325 mlseen = 0
2326 for pt in reversed(proptree.sortattr):
2327 p = pt
2328 while p.parent:
2329 if isinstance (p.propclass, Multilink):
2330 mlseen = True
2331 if mlseen:
2332 p.sort_ids_needed = True
2333 p.tree_sort_done = False
2334 p = p.parent
2335 if not mlseen:
2336 pt.attr_sort_done = pt.tree_sort_done = True
2337 proptree.compute_sort_done()
2339 ordercols = []
2340 auxcols = {}
2341 mlsort = []
2342 rhsnum = 0
2343 for p in proptree:
2344 oc = None
2345 cn = p.classname
2346 ln = p.uniqname
2347 pln = p.parent.uniqname
2348 pcn = p.parent.classname
2349 k = p.name
2350 v = p.val
2351 propclass = p.propclass
2352 if p.sort_type > 0:
2353 oc = ac = '_%s._%s'%(pln, k)
2354 if isinstance(propclass, Multilink):
2355 if p.sort_type < 2:
2356 mlfilt = 1
2357 tn = '%s_%s'%(pcn, k)
2358 if v in ('-1', ['-1'], []):
2359 # only match rows that have count(linkid)=0 in the
2360 # corresponding multilink table)
2361 where.append(self._subselect(pcn, tn))
2362 else:
2363 frum.append(tn)
2364 gen_join = True
2366 if p.has_values and isinstance(v, type([])):
2367 result = self._filter_multilink_expression(pln, tn, v)
2368 # XXX: We dont need an id join if we used the filter
2369 gen_join = len(result) == 3
2371 if gen_join:
2372 where.append('_%s.id=%s.nodeid'%(pln,tn))
2374 if p.children:
2375 frum.append('_%s as _%s' % (cn, ln))
2376 where.append('%s.linkid=_%s.id'%(tn, ln))
2378 if p.has_values:
2379 if isinstance(v, type([])):
2380 where.append(result[0])
2381 args += result[1]
2382 else:
2383 where.append('%s.linkid=%s'%(tn, a))
2384 args.append(v)
2385 if p.sort_type > 0:
2386 assert not p.attr_sort_done and not p.sort_ids_needed
2387 elif k == 'id':
2388 if p.sort_type < 2:
2389 if isinstance(v, type([])):
2390 # If there are no permitted values, then the
2391 # where clause will always be false, and we
2392 # can optimize the query away.
2393 if not v:
2394 return []
2395 s = ','.join([a for x in v])
2396 where.append('_%s.%s in (%s)'%(pln, k, s))
2397 args = args + v
2398 else:
2399 where.append('_%s.%s=%s'%(pln, k, a))
2400 args.append(v)
2401 if p.sort_type > 0:
2402 oc = ac = '_%s.id'%pln
2403 elif isinstance(propclass, String):
2404 if p.sort_type < 2:
2405 if not isinstance(v, type([])):
2406 v = [v]
2408 # Quote the bits in the string that need it and then embed
2409 # in a "substring" search. Note - need to quote the '%' so
2410 # they make it through the python layer happily
2411 v = ['%%'+self.db.sql_stringquote(s)+'%%' for s in v]
2413 # now add to the where clause
2414 where.append('('
2415 +' and '.join(["_%s._%s LIKE '%s'"%(pln, k, s) for s in v])
2416 +')')
2417 # note: args are embedded in the query string now
2418 if p.sort_type > 0:
2419 oc = ac = 'lower(_%s._%s)'%(pln, k)
2420 elif isinstance(propclass, Link):
2421 if p.sort_type < 2:
2422 if p.children:
2423 if p.sort_type == 0:
2424 frum.append('_%s as _%s' % (cn, ln))
2425 where.append('_%s._%s=_%s.id'%(pln, k, ln))
2426 if p.has_values:
2427 if isinstance(v, type([])):
2428 d = {}
2429 for entry in v:
2430 if entry == '-1':
2431 entry = None
2432 d[entry] = entry
2433 l = []
2434 if None in d or not d:
2435 if None in d: del d[None]
2436 l.append('_%s._%s is NULL'%(pln, k))
2437 if d:
2438 v = list(d)
2439 s = ','.join([a for x in v])
2440 l.append('(_%s._%s in (%s))'%(pln, k, s))
2441 args = args + v
2442 if l:
2443 where.append('(' + ' or '.join(l) +')')
2444 else:
2445 if v in ('-1', None):
2446 v = None
2447 where.append('_%s._%s is NULL'%(pln, k))
2448 else:
2449 where.append('_%s._%s=%s'%(pln, k, a))
2450 args.append(v)
2451 if p.sort_type > 0:
2452 lp = p.cls.labelprop()
2453 oc = ac = '_%s._%s'%(pln, k)
2454 if lp != 'id':
2455 if p.tree_sort_done and p.sort_type > 0:
2456 loj.append(
2457 'LEFT OUTER JOIN _%s as _%s on _%s._%s=_%s.id'%(
2458 cn, ln, pln, k, ln))
2459 oc = '_%s._%s'%(ln, lp)
2460 elif isinstance(propclass, Date) and p.sort_type < 2:
2461 dc = self.db.to_sql_value(hyperdb.Date)
2462 if isinstance(v, type([])):
2463 s = ','.join([a for x in v])
2464 where.append('_%s._%s in (%s)'%(pln, k, s))
2465 args = args + [dc(date.Date(x)) for x in v]
2466 else:
2467 try:
2468 # Try to filter on range of dates
2469 date_rng = propclass.range_from_raw(v, self.db)
2470 if date_rng.from_value:
2471 where.append('_%s._%s >= %s'%(pln, k, a))
2472 args.append(dc(date_rng.from_value))
2473 if date_rng.to_value:
2474 where.append('_%s._%s <= %s'%(pln, k, a))
2475 args.append(dc(date_rng.to_value))
2476 except ValueError:
2477 # If range creation fails - ignore that search parameter
2478 pass
2479 elif isinstance(propclass, Interval):
2480 # filter/sort using the __<prop>_int__ column
2481 if p.sort_type < 2:
2482 if isinstance(v, type([])):
2483 s = ','.join([a for x in v])
2484 where.append('_%s.__%s_int__ in (%s)'%(pln, k, s))
2485 args = args + [date.Interval(x).as_seconds() for x in v]
2486 else:
2487 try:
2488 # Try to filter on range of intervals
2489 date_rng = Range(v, date.Interval)
2490 if date_rng.from_value:
2491 where.append('_%s.__%s_int__ >= %s'%(pln, k, a))
2492 args.append(date_rng.from_value.as_seconds())
2493 if date_rng.to_value:
2494 where.append('_%s.__%s_int__ <= %s'%(pln, k, a))
2495 args.append(date_rng.to_value.as_seconds())
2496 except ValueError:
2497 # If range creation fails - ignore search parameter
2498 pass
2499 if p.sort_type > 0:
2500 oc = ac = '_%s.__%s_int__'%(pln,k)
2501 elif isinstance(propclass, Boolean) and p.sort_type < 2:
2502 if type(v) == type(""):
2503 v = v.split(',')
2504 if type(v) != type([]):
2505 v = [v]
2506 bv = []
2507 for val in v:
2508 if type(val) is type(''):
2509 bv.append(propclass.from_raw (val))
2510 else:
2511 bv.append(bool(val))
2512 if len(bv) == 1:
2513 where.append('_%s._%s=%s'%(pln, k, a))
2514 args = args + bv
2515 else:
2516 s = ','.join([a for x in v])
2517 where.append('_%s._%s in (%s)'%(pln, k, s))
2518 args = args + bv
2519 elif p.sort_type < 2:
2520 if isinstance(v, type([])):
2521 s = ','.join([a for x in v])
2522 where.append('_%s._%s in (%s)'%(pln, k, s))
2523 args = args + v
2524 else:
2525 where.append('_%s._%s=%s'%(pln, k, a))
2526 args.append(v)
2527 if oc:
2528 if p.sort_ids_needed:
2529 auxcols[ac] = p
2530 if p.tree_sort_done and p.sort_direction:
2531 # Don't select top-level id twice
2532 if p.name != 'id' or p.parent != proptree:
2533 ordercols.append(oc)
2534 desc = ['', ' desc'][p.sort_direction == '-']
2535 # Some SQL dbs sort NULL values last -- we want them first.
2536 if (self.order_by_null_values and p.name != 'id'):
2537 nv = self.order_by_null_values % oc
2538 ordercols.append(nv)
2539 p.orderby.append(nv + desc)
2540 p.orderby.append(oc + desc)
2542 props = self.getprops()
2544 # don't match retired nodes
2545 where.append('_%s.__retired__=0'%icn)
2547 # add results of full text search
2548 if search_matches is not None:
2549 s = ','.join([a for x in search_matches])
2550 where.append('_%s.id in (%s)'%(icn, s))
2551 args = args + [x for x in search_matches]
2553 # construct the SQL
2554 frum.append('_'+icn)
2555 frum = ','.join(frum)
2556 if where:
2557 where = ' where ' + (' and '.join(where))
2558 else:
2559 where = ''
2560 if mlfilt:
2561 # we're joining tables on the id, so we will get dupes if we
2562 # don't distinct()
2563 cols = ['distinct(_%s.id)'%icn]
2564 else:
2565 cols = ['_%s.id'%icn]
2566 if ordercols:
2567 cols = cols + ordercols
2568 order = []
2569 # keep correct sequence of order attributes.
2570 for sa in proptree.sortattr:
2571 if not sa.attr_sort_done:
2572 continue
2573 order.extend(sa.orderby)
2574 if order:
2575 order = ' order by %s'%(','.join(order))
2576 else:
2577 order = ''
2578 for o, p in auxcols.iteritems ():
2579 cols.append (o)
2580 p.auxcol = len (cols) - 1
2582 cols = ','.join(cols)
2583 loj = ' '.join(loj)
2584 sql = 'select %s from %s %s %s%s'%(cols, frum, loj, where, order)
2585 args = tuple(args)
2586 __traceback_info__ = (sql, args)
2587 self.db.sql(sql, args)
2588 l = self.db.sql_fetchall()
2590 # Compute values needed for sorting in proptree.sort
2591 for p in auxcols.itervalues():
2592 p.sort_ids = p.sort_result = [row[p.auxcol] for row in l]
2593 # return the IDs (the first column)
2594 # XXX numeric ids
2595 l = [str(row[0]) for row in l]
2596 l = proptree.sort (l)
2598 if __debug__:
2599 self.db.stats['filtering'] += (time.time() - start_t)
2600 return l
2602 def filter_sql(self, sql):
2603 """Return a list of the ids of the items in this class that match
2604 the SQL provided. The SQL is a complete "select" statement.
2606 The SQL select must include the item id as the first column.
2608 This function DOES NOT filter out retired items, add on a where
2609 clause "__retired__=0" if you don't want retired nodes.
2610 """
2611 if __debug__:
2612 start_t = time.time()
2614 self.db.sql(sql)
2615 l = self.db.sql_fetchall()
2617 if __debug__:
2618 self.db.stats['filtering'] += (time.time() - start_t)
2619 return l
2621 def count(self):
2622 """Get the number of nodes in this class.
2624 If the returned integer is 'numnodes', the ids of all the nodes
2625 in this class run from 1 to numnodes, and numnodes+1 will be the
2626 id of the next node to be created in this class.
2627 """
2628 return self.db.countnodes(self.classname)
2630 # Manipulating properties:
2631 def getprops(self, protected=1):
2632 """Return a dictionary mapping property names to property objects.
2633 If the "protected" flag is true, we include protected properties -
2634 those which may not be modified.
2635 """
2636 d = self.properties.copy()
2637 if protected:
2638 d['id'] = String()
2639 d['creation'] = hyperdb.Date()
2640 d['activity'] = hyperdb.Date()
2641 d['creator'] = hyperdb.Link('user')
2642 d['actor'] = hyperdb.Link('user')
2643 return d
2645 def addprop(self, **properties):
2646 """Add properties to this class.
2648 The keyword arguments in 'properties' must map names to property
2649 objects, or a TypeError is raised. None of the keys in 'properties'
2650 may collide with the names of existing properties, or a ValueError
2651 is raised before any properties have been added.
2652 """
2653 for key in properties:
2654 if key in self.properties:
2655 raise ValueError(key)
2656 self.properties.update(properties)
2658 def index(self, nodeid):
2659 """Add (or refresh) the node to search indexes
2660 """
2661 # find all the String properties that have indexme
2662 for prop, propclass in self.getprops().iteritems():
2663 if isinstance(propclass, String) and propclass.indexme:
2664 self.db.indexer.add_text((self.classname, nodeid, prop),
2665 str(self.get(nodeid, prop)))
2667 #
2668 # import / export support
2669 #
2670 def export_list(self, propnames, nodeid):
2671 """ Export a node - generate a list of CSV-able data in the order
2672 specified by propnames for the given node.
2673 """
2674 properties = self.getprops()
2675 l = []
2676 for prop in propnames:
2677 proptype = properties[prop]
2678 value = self.get(nodeid, prop)
2679 # "marshal" data where needed
2680 if value is None:
2681 pass
2682 elif isinstance(proptype, hyperdb.Date):
2683 value = value.get_tuple()
2684 elif isinstance(proptype, hyperdb.Interval):
2685 value = value.get_tuple()
2686 elif isinstance(proptype, hyperdb.Password):
2687 value = str(value)
2688 l.append(repr(value))
2689 l.append(repr(self.is_retired(nodeid)))
2690 return l
2692 def import_list(self, propnames, proplist):
2693 """ Import a node - all information including "id" is present and
2694 should not be sanity checked. Triggers are not triggered. The
2695 journal should be initialised using the "creator" and "created"
2696 information.
2698 Return the nodeid of the node imported.
2699 """
2700 if self.db.journaltag is None:
2701 raise DatabaseError(_('Database open read-only'))
2702 properties = self.getprops()
2704 # make the new node's property map
2705 d = {}
2706 retire = 0
2707 if not "id" in propnames:
2708 newid = self.db.newid(self.classname)
2709 else:
2710 newid = eval(proplist[propnames.index("id")])
2711 for i in range(len(propnames)):
2712 # Use eval to reverse the repr() used to output the CSV
2713 value = eval(proplist[i])
2715 # Figure the property for this column
2716 propname = propnames[i]
2718 # "unmarshal" where necessary
2719 if propname == 'id':
2720 continue
2721 elif propname == 'is retired':
2722 # is the item retired?
2723 if int(value):
2724 retire = 1
2725 continue
2726 elif value is None:
2727 d[propname] = None
2728 continue
2730 prop = properties[propname]
2731 if value is None:
2732 # don't set Nones
2733 continue
2734 elif isinstance(prop, hyperdb.Date):
2735 value = date.Date(value)
2736 elif isinstance(prop, hyperdb.Interval):
2737 value = date.Interval(value)
2738 elif isinstance(prop, hyperdb.Password):
2739 pwd = password.Password()
2740 pwd.unpack(value)
2741 value = pwd
2742 elif isinstance(prop, String):
2743 if isinstance(value, unicode):
2744 value = value.encode('utf8')
2745 if not isinstance(value, str):
2746 raise TypeError('new property "%(propname)s" not a '
2747 'string: %(value)r'%locals())
2748 if prop.indexme:
2749 self.db.indexer.add_text((self.classname, newid, propname),
2750 value)
2751 d[propname] = value
2753 # get a new id if necessary
2754 if newid is None:
2755 newid = self.db.newid(self.classname)
2757 # insert new node or update existing?
2758 if not self.hasnode(newid):
2759 self.db.addnode(self.classname, newid, d) # insert
2760 else:
2761 self.db.setnode(self.classname, newid, d) # update
2763 # retire?
2764 if retire:
2765 # use the arg for __retired__ to cope with any odd database type
2766 # conversion (hello, sqlite)
2767 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
2768 self.db.arg, self.db.arg)
2769 self.db.sql(sql, (newid, newid))
2770 return newid
2772 def export_journals(self):
2773 """Export a class's journal - generate a list of lists of
2774 CSV-able data:
2776 nodeid, date, user, action, params
2778 No heading here - the columns are fixed.
2779 """
2780 properties = self.getprops()
2781 r = []
2782 for nodeid in self.getnodeids():
2783 for nodeid, date, user, action, params in self.history(nodeid):
2784 date = date.get_tuple()
2785 if action == 'set':
2786 export_data = {}
2787 for propname, value in params.iteritems():
2788 if propname not in properties:
2789 # property no longer in the schema
2790 continue
2792 prop = properties[propname]
2793 # make sure the params are eval()'able
2794 if value is None:
2795 pass
2796 elif isinstance(prop, Date):
2797 value = value.get_tuple()
2798 elif isinstance(prop, Interval):
2799 value = value.get_tuple()
2800 elif isinstance(prop, Password):
2801 value = str(value)
2802 export_data[propname] = value
2803 params = export_data
2804 elif action == 'create' and params:
2805 # old tracker with data stored in the create!
2806 params = {}
2807 l = [nodeid, date, user, action, params]
2808 r.append(list(map(repr, l)))
2809 return r
2811 class FileClass(hyperdb.FileClass, Class):
2812 """This class defines a large chunk of data. To support this, it has a
2813 mandatory String property "content" which is typically saved off
2814 externally to the hyperdb.
2816 The default MIME type of this data is defined by the
2817 "default_mime_type" class attribute, which may be overridden by each
2818 node if the class defines a "type" String property.
2819 """
2820 def __init__(self, db, classname, **properties):
2821 """The newly-created class automatically includes the "content"
2822 and "type" properties.
2823 """
2824 if 'content' not in properties:
2825 properties['content'] = hyperdb.String(indexme='yes')
2826 if 'type' not in properties:
2827 properties['type'] = hyperdb.String()
2828 Class.__init__(self, db, classname, **properties)
2830 def create(self, **propvalues):
2831 """ snaffle the file propvalue and store in a file
2832 """
2833 # we need to fire the auditors now, or the content property won't
2834 # be in propvalues for the auditors to play with
2835 self.fireAuditors('create', None, propvalues)
2837 # now remove the content property so it's not stored in the db
2838 content = propvalues['content']
2839 del propvalues['content']
2841 # do the database create
2842 newid = self.create_inner(**propvalues)
2844 # figure the mime type
2845 mime_type = propvalues.get('type', self.default_mime_type)
2847 # and index!
2848 if self.properties['content'].indexme:
2849 self.db.indexer.add_text((self.classname, newid, 'content'),
2850 content, mime_type)
2852 # store off the content as a file
2853 self.db.storefile(self.classname, newid, None, content)
2855 # fire reactors
2856 self.fireReactors('create', newid, None)
2858 return newid
2860 def get(self, nodeid, propname, default=_marker, cache=1):
2861 """ Trap the content propname and get it from the file
2863 'cache' exists for backwards compatibility, and is not used.
2864 """
2865 poss_msg = 'Possibly a access right configuration problem.'
2866 if propname == 'content':
2867 try:
2868 return self.db.getfile(self.classname, nodeid, None)
2869 except IOError, strerror:
2870 # BUG: by catching this we donot see an error in the log.
2871 return 'ERROR reading file: %s%s\n%s\n%s'%(
2872 self.classname, nodeid, poss_msg, strerror)
2873 if default is not _marker:
2874 return Class.get(self, nodeid, propname, default)
2875 else:
2876 return Class.get(self, nodeid, propname)
2878 def set(self, itemid, **propvalues):
2879 """ Snarf the "content" propvalue and update it in a file
2880 """
2881 self.fireAuditors('set', itemid, propvalues)
2882 oldvalues = copy.deepcopy(self.db.getnode(self.classname, itemid))
2884 # now remove the content property so it's not stored in the db
2885 content = None
2886 if 'content' in propvalues:
2887 content = propvalues['content']
2888 del propvalues['content']
2890 # do the database create
2891 propvalues = self.set_inner(itemid, **propvalues)
2893 # do content?
2894 if content:
2895 # store and possibly index
2896 self.db.storefile(self.classname, itemid, None, content)
2897 if self.properties['content'].indexme:
2898 mime_type = self.get(itemid, 'type', self.default_mime_type)
2899 self.db.indexer.add_text((self.classname, itemid, 'content'),
2900 content, mime_type)
2901 propvalues['content'] = content
2903 # fire reactors
2904 self.fireReactors('set', itemid, oldvalues)
2905 return propvalues
2907 def index(self, nodeid):
2908 """ Add (or refresh) the node to search indexes.
2910 Use the content-type property for the content property.
2911 """
2912 # find all the String properties that have indexme
2913 for prop, propclass in self.getprops().iteritems():
2914 if prop == 'content' and propclass.indexme:
2915 mime_type = self.get(nodeid, 'type', self.default_mime_type)
2916 self.db.indexer.add_text((self.classname, nodeid, 'content'),
2917 str(self.get(nodeid, 'content')), mime_type)
2918 elif isinstance(propclass, hyperdb.String) and propclass.indexme:
2919 # index them under (classname, nodeid, property)
2920 try:
2921 value = str(self.get(nodeid, prop))
2922 except IndexError:
2923 # node has been destroyed
2924 continue
2925 self.db.indexer.add_text((self.classname, nodeid, prop), value)
2927 # XXX deviation from spec - was called ItemClass
2928 class IssueClass(Class, roundupdb.IssueClass):
2929 # Overridden methods:
2930 def __init__(self, db, classname, **properties):
2931 """The newly-created class automatically includes the "messages",
2932 "files", "nosy", and "superseder" properties. If the 'properties'
2933 dictionary attempts to specify any of these properties or a
2934 "creation", "creator", "activity" or "actor" property, a ValueError
2935 is raised.
2936 """
2937 if 'title' not in properties:
2938 properties['title'] = hyperdb.String(indexme='yes')
2939 if 'messages' not in properties:
2940 properties['messages'] = hyperdb.Multilink("msg")
2941 if 'files' not in properties:
2942 properties['files'] = hyperdb.Multilink("file")
2943 if 'nosy' not in properties:
2944 # note: journalling is turned off as it really just wastes
2945 # space. this behaviour may be overridden in an instance
2946 properties['nosy'] = hyperdb.Multilink("user", do_journal="no")
2947 if 'superseder' not in properties:
2948 properties['superseder'] = hyperdb.Multilink(classname)
2949 Class.__init__(self, db, classname, **properties)
2951 # vim: set et sts=4 sw=4 :