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 # dummy value meaning "argument not passed"
75 _marker = []
77 def _num_cvt(num):
78 num = str(num)
79 try:
80 return int(num)
81 except:
82 return float(num)
84 def _bool_cvt(value):
85 if value in ('TRUE', 'FALSE'):
86 return {'TRUE': 1, 'FALSE': 0}[value]
87 # assume it's a number returned from the db API
88 return int(value)
90 def connection_dict(config, dbnamestr=None):
91 """ Used by Postgresql and MySQL to detemine the keyword args for
92 opening the database connection."""
93 d = { }
94 if dbnamestr:
95 d[dbnamestr] = config.RDBMS_NAME
96 for name in ('host', 'port', 'password', 'user', 'read_default_group',
97 'read_default_file'):
98 cvar = 'RDBMS_'+name.upper()
99 if config[cvar] is not None:
100 d[name] = config[cvar]
101 return d
103 class Database(FileStorage, hyperdb.Database, roundupdb.Database):
104 """ Wrapper around an SQL database that presents a hyperdb interface.
106 - some functionality is specific to the actual SQL database, hence
107 the sql_* methods that are NotImplemented
108 - we keep a cache of the latest N row fetches (where N is configurable).
109 """
110 def __init__(self, config, journaltag=None):
111 """ Open the database and load the schema from it.
112 """
113 FileStorage.__init__(self, config.UMASK)
114 self.config, self.journaltag = config, journaltag
115 self.dir = config.DATABASE
116 self.classes = {}
117 self.indexer = Indexer(self)
118 self.security = security.Security(self)
120 # additional transaction support for external files and the like
121 self.transactions = []
123 # keep a cache of the N most recently retrieved rows of any kind
124 # (classname, nodeid) = row
125 self.cache_size = config.RDBMS_CACHE_SIZE
126 self.cache = {}
127 self.cache_lru = []
128 self.stats = {'cache_hits': 0, 'cache_misses': 0, 'get_items': 0,
129 'filtering': 0}
131 # database lock
132 self.lockfile = None
134 # open a connection to the database, creating the "conn" attribute
135 self.open_connection()
137 def clearCache(self):
138 self.cache = {}
139 self.cache_lru = []
141 def getSessionManager(self):
142 return Sessions(self)
144 def getOTKManager(self):
145 return OneTimeKeys(self)
147 def open_connection(self):
148 """ Open a connection to the database, creating it if necessary.
150 Must call self.load_dbschema()
151 """
152 raise NotImplemented
154 def sql(self, sql, args=None):
155 """ Execute the sql with the optional args.
156 """
157 self.log_debug('SQL %r %r'%(sql, args))
158 if args:
159 self.cursor.execute(sql, args)
160 else:
161 self.cursor.execute(sql)
163 def sql_fetchone(self):
164 """ Fetch a single row. If there's nothing to fetch, return None.
165 """
166 return self.cursor.fetchone()
168 def sql_fetchall(self):
169 """ Fetch all rows. If there's nothing to fetch, return [].
170 """
171 return self.cursor.fetchall()
173 def sql_stringquote(self, value):
174 """ Quote the string so it's safe to put in the 'sql quotes'
175 """
176 return re.sub("'", "''", str(value))
178 def init_dbschema(self):
179 self.database_schema = {
180 'version': self.current_db_version,
181 'tables': {}
182 }
184 def load_dbschema(self):
185 """ Load the schema definition that the database currently implements
186 """
187 self.cursor.execute('select schema from schema')
188 schema = self.cursor.fetchone()
189 if schema:
190 self.database_schema = eval(schema[0])
191 else:
192 self.database_schema = {}
194 def save_dbschema(self):
195 """ Save the schema definition that the database currently implements
196 """
197 s = repr(self.database_schema)
198 self.sql('delete from schema')
199 self.sql('insert into schema values (%s)'%self.arg, (s,))
201 def post_init(self):
202 """ Called once the schema initialisation has finished.
204 We should now confirm that the schema defined by our "classes"
205 attribute actually matches the schema in the database.
206 """
207 save = 0
209 # handle changes in the schema
210 tables = self.database_schema['tables']
211 for classname, spec in self.classes.iteritems():
212 if classname in tables:
213 dbspec = tables[classname]
214 if self.update_class(spec, dbspec):
215 tables[classname] = spec.schema()
216 save = 1
217 else:
218 self.create_class(spec)
219 tables[classname] = spec.schema()
220 save = 1
222 for classname, spec in list(tables.items()):
223 if classname not in self.classes:
224 self.drop_class(classname, tables[classname])
225 del tables[classname]
226 save = 1
228 # now upgrade the database for column type changes, new internal
229 # tables, etc.
230 save = save | self.upgrade_db()
232 # update the database version of the schema
233 if save:
234 self.save_dbschema()
236 # reindex the db if necessary
237 if self.indexer.should_reindex():
238 self.reindex()
240 # commit
241 self.sql_commit()
243 # update this number when we need to make changes to the SQL structure
244 # of the backen database
245 current_db_version = 5
246 db_version_updated = False
247 def upgrade_db(self):
248 """ Update the SQL database to reflect changes in the backend code.
250 Return boolean whether we need to save the schema.
251 """
252 version = self.database_schema.get('version', 1)
253 if version > self.current_db_version:
254 raise DatabaseError('attempting to run rev %d DATABASE with rev '
255 '%d CODE!'%(version, self.current_db_version))
256 if version == self.current_db_version:
257 # nothing to do
258 return 0
260 if version < 2:
261 self.log_info('upgrade to version 2')
262 # change the schema structure
263 self.database_schema = {'tables': self.database_schema}
265 # version 1 didn't have the actor column (note that in
266 # MySQL this will also transition the tables to typed columns)
267 self.add_new_columns_v2()
269 # version 1 doesn't have the OTK, session and indexing in the
270 # database
271 self.create_version_2_tables()
273 if version < 3:
274 self.log_info('upgrade to version 3')
275 self.fix_version_2_tables()
277 if version < 4:
278 self.fix_version_3_tables()
280 if version < 5:
281 self.fix_version_4_tables()
283 self.database_schema['version'] = self.current_db_version
284 self.db_version_updated = True
285 return 1
287 def fix_version_3_tables(self):
288 # drop the shorter VARCHAR OTK column and add a new TEXT one
289 for name in ('otk', 'session'):
290 self.sql('DELETE FROM %ss'%name)
291 self.sql('ALTER TABLE %ss DROP %s_value'%(name, name))
292 self.sql('ALTER TABLE %ss ADD %s_value TEXT'%(name, name))
294 def fix_version_2_tables(self):
295 # Default (used by sqlite): NOOP
296 pass
298 def fix_version_4_tables(self):
299 # note this is an explicit call now
300 c = self.cursor
301 for cn, klass in self.classes.iteritems():
302 c.execute('select id from _%s where __retired__<>0'%(cn,))
303 for (id,) in c.fetchall():
304 c.execute('update _%s set __retired__=%s where id=%s'%(cn,
305 self.arg, self.arg), (id, id))
307 if klass.key:
308 self.add_class_key_required_unique_constraint(cn, klass.key)
310 def _convert_journal_tables(self):
311 """Get current journal table contents, drop the table and re-create"""
312 c = self.cursor
313 cols = ','.join('nodeid date tag action params'.split())
314 for klass in self.classes.itervalues():
315 # slurp and drop
316 sql = 'select %s from %s__journal order by date'%(cols,
317 klass.classname)
318 c.execute(sql)
319 contents = c.fetchall()
320 self.drop_journal_table_indexes(klass.classname)
321 c.execute('drop table %s__journal'%klass.classname)
323 # re-create and re-populate
324 self.create_journal_table(klass)
325 a = self.arg
326 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(
327 klass.classname, cols, a, a, a, a, a)
328 for row in contents:
329 # no data conversion needed
330 self.cursor.execute(sql, row)
332 def _convert_string_properties(self):
333 """Get current Class tables that contain String properties, and
334 convert the VARCHAR columns to TEXT"""
335 c = self.cursor
336 for klass in self.classes.itervalues():
337 # slurp and drop
338 cols, mls = self.determine_columns(list(klass.properties.iteritems()))
339 scols = ','.join([i[0] for i in cols])
340 sql = 'select id,%s from _%s'%(scols, klass.classname)
341 c.execute(sql)
342 contents = c.fetchall()
343 self.drop_class_table_indexes(klass.classname, klass.getkey())
344 c.execute('drop table _%s'%klass.classname)
346 # re-create and re-populate
347 self.create_class_table(klass, create_sequence=0)
348 a = ','.join([self.arg for i in range(len(cols)+1)])
349 sql = 'insert into _%s (id,%s) values (%s)'%(klass.classname,
350 scols, a)
351 for row in contents:
352 l = []
353 for entry in row:
354 # mysql will already be a string - psql needs "help"
355 if entry is not None and not isinstance(entry, type('')):
356 entry = str(entry)
357 l.append(entry)
358 self.cursor.execute(sql, l)
360 def refresh_database(self):
361 self.post_init()
364 def reindex(self, classname=None, show_progress=False):
365 if classname:
366 classes = [self.getclass(classname)]
367 else:
368 classes = list(self.classes.itervalues())
369 for klass in classes:
370 if show_progress:
371 for nodeid in support.Progress('Reindex %s'%klass.classname,
372 klass.list()):
373 klass.index(nodeid)
374 else:
375 for nodeid in klass.list():
376 klass.index(nodeid)
377 self.indexer.save_index()
379 hyperdb_to_sql_datatypes = {
380 hyperdb.String : 'TEXT',
381 hyperdb.Date : 'TIMESTAMP',
382 hyperdb.Link : 'INTEGER',
383 hyperdb.Interval : 'VARCHAR(255)',
384 hyperdb.Password : 'VARCHAR(255)',
385 hyperdb.Boolean : 'BOOLEAN',
386 hyperdb.Number : 'REAL',
387 }
389 def hyperdb_to_sql_datatype(self, propclass):
391 datatype = self.hyperdb_to_sql_datatypes.get(propclass)
392 if datatype:
393 return datatype
395 for k, v in self.hyperdb_to_sql_datatypes.iteritems():
396 if issubclass(propclass, k):
397 return v
399 raise ValueError('%r is not a hyperdb property class' % propclass)
401 def determine_columns(self, properties):
402 """ Figure the column names and multilink properties from the spec
404 "properties" is a list of (name, prop) where prop may be an
405 instance of a hyperdb "type" _or_ a string repr of that type.
406 """
407 cols = [
408 ('_actor', self.hyperdb_to_sql_datatype(hyperdb.Link)),
409 ('_activity', self.hyperdb_to_sql_datatype(hyperdb.Date)),
410 ('_creator', self.hyperdb_to_sql_datatype(hyperdb.Link)),
411 ('_creation', self.hyperdb_to_sql_datatype(hyperdb.Date)),
412 ]
413 mls = []
414 # add the multilinks separately
415 for col, prop in properties:
416 if isinstance(prop, Multilink):
417 mls.append(col)
418 continue
420 if isinstance(prop, type('')):
421 raise ValueError("string property spec!")
422 #and prop.find('Multilink') != -1:
423 #mls.append(col)
425 datatype = self.hyperdb_to_sql_datatype(prop.__class__)
426 cols.append(('_'+col, datatype))
428 # Intervals stored as two columns
429 if isinstance(prop, Interval):
430 cols.append(('__'+col+'_int__', 'BIGINT'))
432 cols.sort()
433 return cols, mls
435 def update_class(self, spec, old_spec, force=0):
436 """ Determine the differences between the current spec and the
437 database version of the spec, and update where necessary.
439 If 'force' is true, update the database anyway.
440 """
441 new_spec = spec.schema()
442 new_spec[1].sort()
443 old_spec[1].sort()
444 if not force and new_spec == old_spec:
445 # no changes
446 return 0
448 logger = logging.getLogger('roundup.hyperdb')
449 logger.info('update_class %s'%spec.classname)
451 logger.debug('old_spec %r'%(old_spec,))
452 logger.debug('new_spec %r'%(new_spec,))
454 # detect key prop change for potential index change
455 keyprop_changes = {}
456 if new_spec[0] != old_spec[0]:
457 if old_spec[0]:
458 keyprop_changes['remove'] = old_spec[0]
459 if new_spec[0]:
460 keyprop_changes['add'] = new_spec[0]
462 # detect multilinks that have been removed, and drop their table
463 old_has = {}
464 for name, prop in old_spec[1]:
465 old_has[name] = 1
466 if name in spec.properties:
467 continue
469 if prop.find('Multilink to') != -1:
470 # first drop indexes.
471 self.drop_multilink_table_indexes(spec.classname, name)
473 # now the multilink table itself
474 sql = 'drop table %s_%s'%(spec.classname, name)
475 else:
476 # if this is the key prop, drop the index first
477 if old_spec[0] == prop:
478 self.drop_class_table_key_index(spec.classname, name)
479 del keyprop_changes['remove']
481 # drop the column
482 sql = 'alter table _%s drop column _%s'%(spec.classname, name)
484 self.sql(sql)
486 # if we didn't remove the key prop just then, but the key prop has
487 # changed, we still need to remove the old index
488 if 'remove' in keyprop_changes:
489 self.drop_class_table_key_index(spec.classname,
490 keyprop_changes['remove'])
492 # add new columns
493 for propname, prop in new_spec[1]:
494 if propname in old_has:
495 continue
496 prop = spec.properties[propname]
497 if isinstance(prop, Multilink):
498 self.create_multilink_table(spec, propname)
499 else:
500 # add the column
501 coltype = self.hyperdb_to_sql_datatype(prop.__class__)
502 sql = 'alter table _%s add column _%s %s'%(
503 spec.classname, propname, coltype)
504 self.sql(sql)
506 # extra Interval column
507 if isinstance(prop, Interval):
508 sql = 'alter table _%s add column __%s_int__ BIGINT'%(
509 spec.classname, propname)
510 self.sql(sql)
512 # if the new column is a key prop, we need an index!
513 if new_spec[0] == propname:
514 self.create_class_table_key_index(spec.classname, propname)
515 del keyprop_changes['add']
517 # if we didn't add the key prop just then, but the key prop has
518 # changed, we still need to add the new index
519 if 'add' in keyprop_changes:
520 self.create_class_table_key_index(spec.classname,
521 keyprop_changes['add'])
523 return 1
525 def determine_all_columns(self, spec):
526 """Figure out the columns from the spec and also add internal columns
528 """
529 cols, mls = self.determine_columns(list(spec.properties.iteritems()))
531 # add on our special columns
532 cols.append(('id', 'INTEGER PRIMARY KEY'))
533 cols.append(('__retired__', 'INTEGER DEFAULT 0'))
534 return cols, mls
536 def create_class_table(self, spec):
537 """Create the class table for the given Class "spec". Creates the
538 indexes too."""
539 cols, mls = self.determine_all_columns(spec)
541 # create the base table
542 scols = ','.join(['%s %s'%x for x in cols])
543 sql = 'create table _%s (%s)'%(spec.classname, scols)
544 self.sql(sql)
546 self.create_class_table_indexes(spec)
548 return cols, mls
550 def create_class_table_indexes(self, spec):
551 """ create the class table for the given spec
552 """
553 # create __retired__ index
554 index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)'%(
555 spec.classname, spec.classname)
556 self.sql(index_sql2)
558 # create index for key property
559 if spec.key:
560 index_sql3 = 'create index _%s_%s_idx on _%s(_%s)'%(
561 spec.classname, spec.key,
562 spec.classname, spec.key)
563 self.sql(index_sql3)
565 # and the unique index for key / retired(id)
566 self.add_class_key_required_unique_constraint(spec.classname,
567 spec.key)
569 # TODO: create indexes on (selected?) Link property columns, as
570 # they're more likely to be used for lookup
572 def add_class_key_required_unique_constraint(self, cn, key):
573 sql = '''create unique index _%s_key_retired_idx
574 on _%s(__retired__, _%s)'''%(cn, cn, key)
575 self.sql(sql)
577 def drop_class_table_indexes(self, cn, key):
578 # drop the old table indexes first
579 l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn]
580 if key:
581 l.append('_%s_%s_idx'%(cn, key))
583 table_name = '_%s'%cn
584 for index_name in l:
585 if not self.sql_index_exists(table_name, index_name):
586 continue
587 index_sql = 'drop index '+index_name
588 self.sql(index_sql)
590 def create_class_table_key_index(self, cn, key):
591 """ create the class table for the given spec
592 """
593 sql = 'create index _%s_%s_idx on _%s(_%s)'%(cn, key, cn, key)
594 self.sql(sql)
596 def drop_class_table_key_index(self, cn, key):
597 table_name = '_%s'%cn
598 index_name = '_%s_%s_idx'%(cn, key)
599 if self.sql_index_exists(table_name, index_name):
600 sql = 'drop index '+index_name
601 self.sql(sql)
603 # and now the retired unique index too
604 index_name = '_%s_key_retired_idx'%cn
605 if self.sql_index_exists(table_name, index_name):
606 sql = 'drop index '+index_name
607 self.sql(sql)
609 def create_journal_table(self, spec):
610 """ create the journal table for a class given the spec and
611 already-determined cols
612 """
613 # journal table
614 cols = ','.join(['%s varchar'%x
615 for x in 'nodeid date tag action params'.split()])
616 sql = """create table %s__journal (
617 nodeid integer, date %s, tag varchar(255),
618 action varchar(255), params text)""" % (spec.classname,
619 self.hyperdb_to_sql_datatype(hyperdb.Date))
620 self.sql(sql)
621 self.create_journal_table_indexes(spec)
623 def create_journal_table_indexes(self, spec):
624 # index on nodeid
625 sql = 'create index %s_journ_idx on %s__journal(nodeid)'%(
626 spec.classname, spec.classname)
627 self.sql(sql)
629 def drop_journal_table_indexes(self, classname):
630 index_name = '%s_journ_idx'%classname
631 if not self.sql_index_exists('%s__journal'%classname, index_name):
632 return
633 index_sql = 'drop index '+index_name
634 self.sql(index_sql)
636 def create_multilink_table(self, spec, ml):
637 """ Create a multilink table for the "ml" property of the class
638 given by the spec
639 """
640 # create the table
641 sql = 'create table %s_%s (linkid INTEGER, nodeid INTEGER)'%(
642 spec.classname, ml)
643 self.sql(sql)
644 self.create_multilink_table_indexes(spec, ml)
646 def create_multilink_table_indexes(self, spec, ml):
647 # create index on linkid
648 index_sql = 'create index %s_%s_l_idx on %s_%s(linkid)'%(
649 spec.classname, ml, spec.classname, ml)
650 self.sql(index_sql)
652 # create index on nodeid
653 index_sql = 'create index %s_%s_n_idx on %s_%s(nodeid)'%(
654 spec.classname, ml, spec.classname, ml)
655 self.sql(index_sql)
657 def drop_multilink_table_indexes(self, classname, ml):
658 l = [
659 '%s_%s_l_idx'%(classname, ml),
660 '%s_%s_n_idx'%(classname, ml)
661 ]
662 table_name = '%s_%s'%(classname, ml)
663 for index_name in l:
664 if not self.sql_index_exists(table_name, index_name):
665 continue
666 index_sql = 'drop index %s'%index_name
667 self.sql(index_sql)
669 def create_class(self, spec):
670 """ Create a database table according to the given spec.
671 """
672 cols, mls = self.create_class_table(spec)
673 self.create_journal_table(spec)
675 # now create the multilink tables
676 for ml in mls:
677 self.create_multilink_table(spec, ml)
679 def drop_class(self, cn, spec):
680 """ Drop the given table from the database.
682 Drop the journal and multilink tables too.
683 """
684 properties = spec[1]
685 # figure the multilinks
686 mls = []
687 for propname, prop in properties:
688 if isinstance(prop, Multilink):
689 mls.append(propname)
691 # drop class table and indexes
692 self.drop_class_table_indexes(cn, spec[0])
694 self.drop_class_table(cn)
696 # drop journal table and indexes
697 self.drop_journal_table_indexes(cn)
698 sql = 'drop table %s__journal'%cn
699 self.sql(sql)
701 for ml in mls:
702 # drop multilink table and indexes
703 self.drop_multilink_table_indexes(cn, ml)
704 sql = 'drop table %s_%s'%(spec.classname, ml)
705 self.sql(sql)
707 def drop_class_table(self, cn):
708 sql = 'drop table _%s'%cn
709 self.sql(sql)
711 #
712 # Classes
713 #
714 def __getattr__(self, classname):
715 """ A convenient way of calling self.getclass(classname).
716 """
717 if classname in self.classes:
718 return self.classes[classname]
719 raise AttributeError(classname)
721 def addclass(self, cl):
722 """ Add a Class to the hyperdatabase.
723 """
724 cn = cl.classname
725 if cn in self.classes:
726 raise ValueError(cn)
727 self.classes[cn] = cl
729 # add default Edit and View permissions
730 self.security.addPermission(name="Create", klass=cn,
731 description="User is allowed to create "+cn)
732 self.security.addPermission(name="Edit", klass=cn,
733 description="User is allowed to edit "+cn)
734 self.security.addPermission(name="View", klass=cn,
735 description="User is allowed to access "+cn)
737 def getclasses(self):
738 """ Return a list of the names of all existing classes.
739 """
740 return sorted(self.classes)
742 def getclass(self, classname):
743 """Get the Class object representing a particular class.
745 If 'classname' is not a valid class name, a KeyError is raised.
746 """
747 try:
748 return self.classes[classname]
749 except KeyError:
750 raise KeyError('There is no class called "%s"'%classname)
752 def clear(self):
753 """Delete all database contents.
755 Note: I don't commit here, which is different behaviour to the
756 "nuke from orbit" behaviour in the dbs.
757 """
758 logging.getLogger('roundup.hyperdb').info('clear')
759 for cn in self.classes:
760 sql = 'delete from _%s'%cn
761 self.sql(sql)
763 #
764 # Nodes
765 #
767 hyperdb_to_sql_value = {
768 hyperdb.String : str,
769 # fractional seconds by default
770 hyperdb.Date : lambda x: x.formal(sep=' ', sec='%06.3f'),
771 hyperdb.Link : int,
772 hyperdb.Interval : str,
773 hyperdb.Password : str,
774 hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE',
775 hyperdb.Number : lambda x: x,
776 hyperdb.Multilink : lambda x: x, # used in journal marshalling
777 }
779 def to_sql_value(self, propklass):
781 fn = self.hyperdb_to_sql_value.get(propklass)
782 if fn:
783 return fn
785 for k, v in self.hyperdb_to_sql_value.iteritems():
786 if issubclass(propklass, k):
787 return v
789 raise ValueError('%r is not a hyperdb property class' % propklass)
791 def addnode(self, classname, nodeid, node):
792 """ Add the specified node to its class's db.
793 """
794 self.log_debug('addnode %s%s %r'%(classname,
795 nodeid, node))
797 # determine the column definitions and multilink tables
798 cl = self.classes[classname]
799 cols, mls = self.determine_columns(list(cl.properties.iteritems()))
801 # we'll be supplied these props if we're doing an import
802 values = node.copy()
803 if 'creator' not in values:
804 # add in the "calculated" properties (dupe so we don't affect
805 # calling code's node assumptions)
806 values['creation'] = values['activity'] = date.Date()
807 values['actor'] = values['creator'] = self.getuid()
809 cl = self.classes[classname]
810 props = cl.getprops(protected=1)
811 del props['id']
813 # default the non-multilink columns
814 for col, prop in props.iteritems():
815 if col not in values:
816 if isinstance(prop, Multilink):
817 values[col] = []
818 else:
819 values[col] = None
821 # clear this node out of the cache if it's in there
822 key = (classname, nodeid)
823 if key in self.cache:
824 del self.cache[key]
825 self.cache_lru.remove(key)
827 # figure the values to insert
828 vals = []
829 for col,dt in cols:
830 # this is somewhat dodgy....
831 if col.endswith('_int__'):
832 # XXX eugh, this test suxxors
833 value = values[col[2:-6]]
834 # this is an Interval special "int" column
835 if value is not None:
836 vals.append(value.as_seconds())
837 else:
838 vals.append(value)
839 continue
841 prop = props[col[1:]]
842 value = values[col[1:]]
843 if value is not None:
844 value = self.to_sql_value(prop.__class__)(value)
845 vals.append(value)
846 vals.append(nodeid)
847 vals = tuple(vals)
849 # make sure the ordering is correct for column name -> column value
850 s = ','.join([self.arg for x in cols]) + ',%s'%self.arg
851 cols = ','.join([col for col,dt in cols]) + ',id'
853 # perform the inserts
854 sql = 'insert into _%s (%s) values (%s)'%(classname, cols, s)
855 self.sql(sql, vals)
857 # insert the multilink rows
858 for col in mls:
859 t = '%s_%s'%(classname, col)
860 for entry in node[col]:
861 sql = 'insert into %s (linkid, nodeid) values (%s,%s)'%(t,
862 self.arg, self.arg)
863 self.sql(sql, (entry, nodeid))
865 def setnode(self, classname, nodeid, values, multilink_changes={}):
866 """ Change the specified node.
867 """
868 self.log_debug('setnode %s%s %r'
869 % (classname, nodeid, values))
871 # clear this node out of the cache if it's in there
872 key = (classname, nodeid)
873 if key in self.cache:
874 del self.cache[key]
875 self.cache_lru.remove(key)
877 cl = self.classes[classname]
878 props = cl.getprops()
880 cols = []
881 mls = []
882 # add the multilinks separately
883 for col in values:
884 prop = props[col]
885 if isinstance(prop, Multilink):
886 mls.append(col)
887 elif isinstance(prop, Interval):
888 # Intervals store the seconds value too
889 cols.append(col)
890 # extra leading '_' added by code below
891 cols.append('_' +col + '_int__')
892 else:
893 cols.append(col)
894 cols.sort()
896 # figure the values to insert
897 vals = []
898 for col in cols:
899 if col.endswith('_int__'):
900 # XXX eugh, this test suxxors
901 # Intervals store the seconds value too
902 col = col[1:-6]
903 prop = props[col]
904 value = values[col]
905 if value is None:
906 vals.append(None)
907 else:
908 vals.append(value.as_seconds())
909 else:
910 prop = props[col]
911 value = values[col]
912 if value is None:
913 e = None
914 else:
915 e = self.to_sql_value(prop.__class__)(value)
916 vals.append(e)
918 vals.append(int(nodeid))
919 vals = tuple(vals)
921 # if there's any updates to regular columns, do them
922 if cols:
923 # make sure the ordering is correct for column name -> column value
924 s = ','.join(['_%s=%s'%(x, self.arg) for x in cols])
925 cols = ','.join(cols)
927 # perform the update
928 sql = 'update _%s set %s where id=%s'%(classname, s, self.arg)
929 self.sql(sql, vals)
931 # we're probably coming from an import, not a change
932 if not multilink_changes:
933 for name in mls:
934 prop = props[name]
935 value = values[name]
937 t = '%s_%s'%(classname, name)
939 # clear out previous values for this node
940 # XXX numeric ids
941 self.sql('delete from %s where nodeid=%s'%(t, self.arg),
942 (nodeid,))
944 # insert the values for this node
945 for entry in values[name]:
946 sql = 'insert into %s (linkid, nodeid) values (%s,%s)'%(t,
947 self.arg, self.arg)
948 # XXX numeric ids
949 self.sql(sql, (entry, nodeid))
951 # we have multilink changes to apply
952 for col, (add, remove) in multilink_changes.iteritems():
953 tn = '%s_%s'%(classname, col)
954 if add:
955 sql = 'insert into %s (nodeid, linkid) values (%s,%s)'%(tn,
956 self.arg, self.arg)
957 for addid in add:
958 # XXX numeric ids
959 self.sql(sql, (int(nodeid), int(addid)))
960 if remove:
961 s = ','.join([self.arg]*len(remove))
962 sql = 'delete from %s where nodeid=%s and linkid in (%s)'%(tn,
963 self.arg, s)
964 # XXX numeric ids
965 self.sql(sql, [int(nodeid)] + remove)
967 sql_to_hyperdb_value = {
968 hyperdb.String : str,
969 hyperdb.Date : lambda x:date.Date(str(x).replace(' ', '.')),
970 # hyperdb.Link : int, # XXX numeric ids
971 hyperdb.Link : str,
972 hyperdb.Interval : date.Interval,
973 hyperdb.Password : lambda x: password.Password(encrypted=x),
974 hyperdb.Boolean : _bool_cvt,
975 hyperdb.Number : _num_cvt,
976 hyperdb.Multilink : lambda x: x, # used in journal marshalling
977 }
979 def to_hyperdb_value(self, propklass):
981 fn = self.sql_to_hyperdb_value.get(propklass)
982 if fn:
983 return fn
985 for k, v in self.sql_to_hyperdb_value.iteritems():
986 if issubclass(propklass, k):
987 return v
989 raise ValueError('%r is not a hyperdb property class' % propklass)
991 def getnode(self, classname, nodeid):
992 """ Get a node from the database.
993 """
994 # see if we have this node cached
995 key = (classname, nodeid)
996 if key in self.cache:
997 # push us back to the top of the LRU
998 self.cache_lru.remove(key)
999 self.cache_lru.insert(0, key)
1000 if __debug__:
1001 self.stats['cache_hits'] += 1
1002 # return the cached information
1003 return self.cache[key]
1005 if __debug__:
1006 self.stats['cache_misses'] += 1
1007 start_t = time.time()
1009 # figure the columns we're fetching
1010 cl = self.classes[classname]
1011 cols, mls = self.determine_columns(list(cl.properties.iteritems()))
1012 scols = ','.join([col for col,dt in cols])
1014 # perform the basic property fetch
1015 sql = 'select %s from _%s where id=%s'%(scols, classname, self.arg)
1016 self.sql(sql, (nodeid,))
1018 values = self.sql_fetchone()
1019 if values is None:
1020 raise IndexError('no such %s node %s'%(classname, nodeid))
1022 # make up the node
1023 node = {}
1024 props = cl.getprops(protected=1)
1025 for col in range(len(cols)):
1026 name = cols[col][0][1:]
1027 if name.endswith('_int__'):
1028 # XXX eugh, this test suxxors
1029 # ignore the special Interval-as-seconds column
1030 continue
1031 value = values[col]
1032 if value is not None:
1033 value = self.to_hyperdb_value(props[name].__class__)(value)
1034 node[name] = value
1037 # now the multilinks
1038 for col in mls:
1039 # get the link ids
1040 sql = 'select linkid from %s_%s where nodeid=%s'%(classname, col,
1041 self.arg)
1042 self.sql(sql, (nodeid,))
1043 # extract the first column from the result
1044 # XXX numeric ids
1045 items = [int(x[0]) for x in self.cursor.fetchall()]
1046 items.sort ()
1047 node[col] = [str(x) for x in items]
1049 # save off in the cache
1050 key = (classname, nodeid)
1051 self.cache[key] = node
1052 # update the LRU
1053 self.cache_lru.insert(0, key)
1054 if len(self.cache_lru) > self.cache_size:
1055 del self.cache[self.cache_lru.pop()]
1057 if __debug__:
1058 self.stats['get_items'] += (time.time() - start_t)
1060 return node
1062 def destroynode(self, classname, nodeid):
1063 """Remove a node from the database. Called exclusively by the
1064 destroy() method on Class.
1065 """
1066 logging.getLogger('roundup.hyperdb').info('destroynode %s%s'%(
1067 classname, nodeid))
1069 # make sure the node exists
1070 if not self.hasnode(classname, nodeid):
1071 raise IndexError('%s has no node %s'%(classname, nodeid))
1073 # see if we have this node cached
1074 if (classname, nodeid) in self.cache:
1075 del self.cache[(classname, nodeid)]
1077 # see if there's any obvious commit actions that we should get rid of
1078 for entry in self.transactions[:]:
1079 if entry[1][:2] == (classname, nodeid):
1080 self.transactions.remove(entry)
1082 # now do the SQL
1083 sql = 'delete from _%s where id=%s'%(classname, self.arg)
1084 self.sql(sql, (nodeid,))
1086 # remove from multilnks
1087 cl = self.getclass(classname)
1088 x, mls = self.determine_columns(list(cl.properties.iteritems()))
1089 for col in mls:
1090 # get the link ids
1091 sql = 'delete from %s_%s where nodeid=%s'%(classname, col, self.arg)
1092 self.sql(sql, (nodeid,))
1094 # remove journal entries
1095 sql = 'delete from %s__journal where nodeid=%s'%(classname, self.arg)
1096 self.sql(sql, (nodeid,))
1098 # cleanup any blob filestorage when we commit
1099 self.transactions.append((FileStorage.destroy, (self, classname, nodeid)))
1101 def hasnode(self, classname, nodeid):
1102 """ Determine if the database has a given node.
1103 """
1104 # If this node is in the cache, then we do not need to go to
1105 # the database. (We don't consider this an LRU hit, though.)
1106 if (classname, nodeid) in self.cache:
1107 # Return 1, not True, to match the type of the result of
1108 # the SQL operation below.
1109 return 1
1110 sql = 'select count(*) from _%s where id=%s'%(classname, self.arg)
1111 self.sql(sql, (nodeid,))
1112 return int(self.cursor.fetchone()[0])
1114 def countnodes(self, classname):
1115 """ Count the number of nodes that exist for a particular Class.
1116 """
1117 sql = 'select count(*) from _%s'%classname
1118 self.sql(sql)
1119 return self.cursor.fetchone()[0]
1121 def addjournal(self, classname, nodeid, action, params, creator=None,
1122 creation=None):
1123 """ Journal the Action
1124 'action' may be:
1126 'create' or 'set' -- 'params' is a dictionary of property values
1127 'link' or 'unlink' -- 'params' is (classname, nodeid, propname)
1128 'retire' -- 'params' is None
1129 """
1130 # handle supply of the special journalling parameters (usually
1131 # supplied on importing an existing database)
1132 if creator:
1133 journaltag = creator
1134 else:
1135 journaltag = self.getuid()
1136 if creation:
1137 journaldate = creation
1138 else:
1139 journaldate = date.Date()
1141 # create the journal entry
1142 cols = 'nodeid,date,tag,action,params'
1144 self.log_debug('addjournal %s%s %r %s %s %r'%(classname,
1145 nodeid, journaldate, journaltag, action, params))
1147 # make the journalled data marshallable
1148 if isinstance(params, type({})):
1149 self._journal_marshal(params, classname)
1151 params = repr(params)
1153 dc = self.to_sql_value(hyperdb.Date)
1154 journaldate = dc(journaldate)
1156 self.save_journal(classname, cols, nodeid, journaldate,
1157 journaltag, action, params)
1159 def setjournal(self, classname, nodeid, journal):
1160 """Set the journal to the "journal" list."""
1161 # clear out any existing entries
1162 self.sql('delete from %s__journal where nodeid=%s'%(classname,
1163 self.arg), (nodeid,))
1165 # create the journal entry
1166 cols = 'nodeid,date,tag,action,params'
1168 dc = self.to_sql_value(hyperdb.Date)
1169 for nodeid, journaldate, journaltag, action, params in journal:
1170 self.log_debug('addjournal %s%s %r %s %s %r'%(
1171 classname, nodeid, journaldate, journaltag, action,
1172 params))
1174 # make the journalled data marshallable
1175 if isinstance(params, type({})):
1176 self._journal_marshal(params, classname)
1177 params = repr(params)
1179 self.save_journal(classname, cols, nodeid, dc(journaldate),
1180 journaltag, action, params)
1182 def _journal_marshal(self, params, classname):
1183 """Convert the journal params values into safely repr'able and
1184 eval'able values."""
1185 properties = self.getclass(classname).getprops()
1186 for param, value in params.iteritems():
1187 if not value:
1188 continue
1189 property = properties[param]
1190 cvt = self.to_sql_value(property.__class__)
1191 if isinstance(property, Password):
1192 params[param] = cvt(value)
1193 elif isinstance(property, Date):
1194 params[param] = cvt(value)
1195 elif isinstance(property, Interval):
1196 params[param] = cvt(value)
1197 elif isinstance(property, Boolean):
1198 params[param] = cvt(value)
1200 def getjournal(self, classname, nodeid):
1201 """ get the journal for id
1202 """
1203 # make sure the node exists
1204 if not self.hasnode(classname, nodeid):
1205 raise IndexError('%s has no node %s'%(classname, nodeid))
1207 cols = ','.join('nodeid date tag action params'.split())
1208 journal = self.load_journal(classname, cols, nodeid)
1210 # now unmarshal the data
1211 dc = self.to_hyperdb_value(hyperdb.Date)
1212 res = []
1213 properties = self.getclass(classname).getprops()
1214 for nodeid, date_stamp, user, action, params in journal:
1215 params = eval(params)
1216 if isinstance(params, type({})):
1217 for param, value in params.iteritems():
1218 if not value:
1219 continue
1220 property = properties.get(param, None)
1221 if property is None:
1222 # deleted property
1223 continue
1224 cvt = self.to_hyperdb_value(property.__class__)
1225 if isinstance(property, Password):
1226 params[param] = cvt(value)
1227 elif isinstance(property, Date):
1228 params[param] = cvt(value)
1229 elif isinstance(property, Interval):
1230 params[param] = cvt(value)
1231 elif isinstance(property, Boolean):
1232 params[param] = cvt(value)
1233 # XXX numeric ids
1234 res.append((str(nodeid), dc(date_stamp), user, action, params))
1235 return res
1237 def save_journal(self, classname, cols, nodeid, journaldate,
1238 journaltag, action, params):
1239 """ Save the journal entry to the database
1240 """
1241 entry = (nodeid, journaldate, journaltag, action, params)
1243 # do the insert
1244 a = self.arg
1245 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(
1246 classname, cols, a, a, a, a, a)
1247 self.sql(sql, entry)
1249 def load_journal(self, classname, cols, nodeid):
1250 """ Load the journal from the database
1251 """
1252 # now get the journal entries
1253 sql = 'select %s from %s__journal where nodeid=%s order by date'%(
1254 cols, classname, self.arg)
1255 self.sql(sql, (nodeid,))
1256 return self.cursor.fetchall()
1258 def pack(self, pack_before):
1259 """ Delete all journal entries except "create" before 'pack_before'.
1260 """
1261 date_stamp = self.to_sql_value(Date)(pack_before)
1263 # do the delete
1264 for classname in self.classes:
1265 sql = "delete from %s__journal where date<%s and "\
1266 "action<>'create'"%(classname, self.arg)
1267 self.sql(sql, (date_stamp,))
1269 def sql_commit(self, fail_ok=False):
1270 """ Actually commit to the database.
1271 """
1272 logging.getLogger('roundup.hyperdb').info('commit')
1274 self.conn.commit()
1276 # open a new cursor for subsequent work
1277 self.cursor = self.conn.cursor()
1279 def commit(self, fail_ok=False):
1280 """ Commit the current transactions.
1282 Save all data changed since the database was opened or since the
1283 last commit() or rollback().
1285 fail_ok indicates that the commit is allowed to fail. This is used
1286 in the web interface when committing cleaning of the session
1287 database. We don't care if there's a concurrency issue there.
1289 The only backend this seems to affect is postgres.
1290 """
1291 # commit the database
1292 self.sql_commit(fail_ok)
1294 # now, do all the other transaction stuff
1295 for method, args in self.transactions:
1296 method(*args)
1298 # save the indexer
1299 self.indexer.save_index()
1301 # clear out the transactions
1302 self.transactions = []
1304 # clear the cache: Don't carry over cached values from one
1305 # transaction to the next (there may be other changes from other
1306 # transactions)
1307 self.clearCache()
1309 def sql_rollback(self):
1310 self.conn.rollback()
1312 def rollback(self):
1313 """ Reverse all actions from the current transaction.
1315 Undo all the changes made since the database was opened or the last
1316 commit() or rollback() was performed.
1317 """
1318 logging.getLogger('roundup.hyperdb').info('rollback')
1320 self.sql_rollback()
1322 # roll back "other" transaction stuff
1323 for method, args in self.transactions:
1324 # delete temporary files
1325 if method == self.doStoreFile:
1326 self.rollbackStoreFile(*args)
1327 self.transactions = []
1329 # clear the cache
1330 self.clearCache()
1332 def sql_close(self):
1333 logging.getLogger('roundup.hyperdb').info('close')
1334 self.conn.close()
1336 def close(self):
1337 """ Close off the connection.
1338 """
1339 self.indexer.close()
1340 self.sql_close()
1342 #
1343 # The base Class class
1344 #
1345 class Class(hyperdb.Class):
1346 """ The handle to a particular class of nodes in a hyperdatabase.
1348 All methods except __repr__ and getnode must be implemented by a
1349 concrete backend Class.
1350 """
1352 def schema(self):
1353 """ A dumpable version of the schema that we can store in the
1354 database
1355 """
1356 return (self.key, [(x, repr(y)) for x,y in self.properties.iteritems()])
1358 def enableJournalling(self):
1359 """Turn journalling on for this class
1360 """
1361 self.do_journal = 1
1363 def disableJournalling(self):
1364 """Turn journalling off for this class
1365 """
1366 self.do_journal = 0
1368 # Editing nodes:
1369 def create(self, **propvalues):
1370 """ Create a new node of this class and return its id.
1372 The keyword arguments in 'propvalues' map property names to values.
1374 The values of arguments must be acceptable for the types of their
1375 corresponding properties or a TypeError is raised.
1377 If this class has a key property, it must be present and its value
1378 must not collide with other key strings or a ValueError is raised.
1380 Any other properties on this class that are missing from the
1381 'propvalues' dictionary are set to None.
1383 If an id in a link or multilink property does not refer to a valid
1384 node, an IndexError is raised.
1385 """
1386 self.fireAuditors('create', None, propvalues)
1387 newid = self.create_inner(**propvalues)
1388 self.fireReactors('create', newid, None)
1389 return newid
1391 def create_inner(self, **propvalues):
1392 """ Called by create, in-between the audit and react calls.
1393 """
1394 if 'id' in propvalues:
1395 raise KeyError('"id" is reserved')
1397 if self.db.journaltag is None:
1398 raise DatabaseError(_('Database open read-only'))
1400 if ('creator' in propvalues or 'actor' in propvalues or
1401 'creation' in propvalues or 'activity' in propvalues):
1402 raise KeyError('"creator", "actor", "creation" and '
1403 '"activity" are reserved')
1405 # new node's id
1406 newid = self.db.newid(self.classname)
1408 # validate propvalues
1409 num_re = re.compile('^\d+$')
1410 for key, value in propvalues.iteritems():
1411 if key == self.key:
1412 try:
1413 self.lookup(value)
1414 except KeyError:
1415 pass
1416 else:
1417 raise ValueError('node with key "%s" exists'%value)
1419 # try to handle this property
1420 try:
1421 prop = self.properties[key]
1422 except KeyError:
1423 raise KeyError('"%s" has no property "%s"'%(self.classname,
1424 key))
1426 if value is not None and isinstance(prop, Link):
1427 if type(value) != type(''):
1428 raise ValueError('link value must be String')
1429 link_class = self.properties[key].classname
1430 # if it isn't a number, it's a key
1431 if not num_re.match(value):
1432 try:
1433 value = self.db.classes[link_class].lookup(value)
1434 except (TypeError, KeyError):
1435 raise IndexError('new property "%s": %s not a %s'%(
1436 key, value, link_class))
1437 elif not self.db.getclass(link_class).hasnode(value):
1438 raise IndexError('%s has no node %s'%(link_class,
1439 value))
1441 # save off the value
1442 propvalues[key] = value
1444 # register the link with the newly linked node
1445 if self.do_journal and self.properties[key].do_journal:
1446 self.db.addjournal(link_class, value, 'link',
1447 (self.classname, newid, key))
1449 elif isinstance(prop, Multilink):
1450 if value is None:
1451 value = []
1452 if not hasattr(value, '__iter__'):
1453 raise TypeError('new property "%s" not an iterable of ids'%key)
1454 # clean up and validate the list of links
1455 link_class = self.properties[key].classname
1456 l = []
1457 for entry in value:
1458 if type(entry) != type(''):
1459 raise ValueError('"%s" multilink value (%r) '
1460 'must contain Strings'%(key, value))
1461 # if it isn't a number, it's a key
1462 if not num_re.match(entry):
1463 try:
1464 entry = self.db.classes[link_class].lookup(entry)
1465 except (TypeError, KeyError):
1466 raise IndexError('new property "%s": %s not a %s'%(
1467 key, entry, self.properties[key].classname))
1468 l.append(entry)
1469 value = l
1470 propvalues[key] = value
1472 # handle additions
1473 for nodeid in value:
1474 if not self.db.getclass(link_class).hasnode(nodeid):
1475 raise IndexError('%s has no node %s'%(link_class,
1476 nodeid))
1477 # register the link with the newly linked node
1478 if self.do_journal and self.properties[key].do_journal:
1479 self.db.addjournal(link_class, nodeid, 'link',
1480 (self.classname, newid, key))
1482 elif isinstance(prop, String):
1483 if type(value) != type('') and type(value) != type(u''):
1484 raise TypeError('new property "%s" not a string'%key)
1485 if prop.indexme:
1486 self.db.indexer.add_text((self.classname, newid, key),
1487 value)
1489 elif isinstance(prop, Password):
1490 if not isinstance(value, password.Password):
1491 raise TypeError('new property "%s" not a Password'%key)
1493 elif isinstance(prop, Date):
1494 if value is not None and not isinstance(value, date.Date):
1495 raise TypeError('new property "%s" not a Date'%key)
1497 elif isinstance(prop, Interval):
1498 if value is not None and not isinstance(value, date.Interval):
1499 raise TypeError('new property "%s" not an Interval'%key)
1501 elif value is not None and isinstance(prop, Number):
1502 try:
1503 float(value)
1504 except ValueError:
1505 raise TypeError('new property "%s" not numeric'%key)
1507 elif value is not None and isinstance(prop, Boolean):
1508 try:
1509 int(value)
1510 except ValueError:
1511 raise TypeError('new property "%s" not boolean'%key)
1513 # make sure there's data where there needs to be
1514 for key, prop in self.properties.iteritems():
1515 if key in propvalues:
1516 continue
1517 if key == self.key:
1518 raise ValueError('key property "%s" is required'%key)
1519 if isinstance(prop, Multilink):
1520 propvalues[key] = []
1521 else:
1522 propvalues[key] = None
1524 # done
1525 self.db.addnode(self.classname, newid, propvalues)
1526 if self.do_journal:
1527 self.db.addjournal(self.classname, newid, ''"create", {})
1529 # XXX numeric ids
1530 return str(newid)
1532 def get(self, nodeid, propname, default=_marker, cache=1):
1533 """Get the value of a property on an existing node of this class.
1535 'nodeid' must be the id of an existing node of this class or an
1536 IndexError is raised. 'propname' must be the name of a property
1537 of this class or a KeyError is raised.
1539 'cache' exists for backwards compatibility, and is not used.
1540 """
1541 if propname == 'id':
1542 return nodeid
1544 # get the node's dict
1545 d = self.db.getnode(self.classname, nodeid)
1547 if propname == 'creation':
1548 if 'creation' in d:
1549 return d['creation']
1550 else:
1551 return date.Date()
1552 if propname == 'activity':
1553 if 'activity' in d:
1554 return d['activity']
1555 else:
1556 return date.Date()
1557 if propname == 'creator':
1558 if 'creator' in d:
1559 return d['creator']
1560 else:
1561 return self.db.getuid()
1562 if propname == 'actor':
1563 if 'actor' in d:
1564 return d['actor']
1565 else:
1566 return self.db.getuid()
1568 # get the property (raises KeyErorr if invalid)
1569 prop = self.properties[propname]
1571 # handle there being no value in the table for the property
1572 if propname not in d or d[propname] is None:
1573 if default is _marker:
1574 if isinstance(prop, Multilink):
1575 return []
1576 else:
1577 return None
1578 else:
1579 return default
1581 # don't pass our list to other code
1582 if isinstance(prop, Multilink):
1583 return d[propname][:]
1585 return d[propname]
1587 def set(self, nodeid, **propvalues):
1588 """Modify a property on an existing node of this class.
1590 'nodeid' must be the id of an existing node of this class or an
1591 IndexError is raised.
1593 Each key in 'propvalues' must be the name of a property of this
1594 class or a KeyError is raised.
1596 All values in 'propvalues' must be acceptable types for their
1597 corresponding properties or a TypeError is raised.
1599 If the value of the key property is set, it must not collide with
1600 other key strings or a ValueError is raised.
1602 If the value of a Link or Multilink property contains an invalid
1603 node id, a ValueError is raised.
1604 """
1605 self.fireAuditors('set', nodeid, propvalues)
1606 oldvalues = copy.deepcopy(self.db.getnode(self.classname, nodeid))
1607 propvalues = self.set_inner(nodeid, **propvalues)
1608 self.fireReactors('set', nodeid, oldvalues)
1609 return propvalues
1611 def set_inner(self, nodeid, **propvalues):
1612 """ Called by set, in-between the audit and react calls.
1613 """
1614 if not propvalues:
1615 return propvalues
1617 if ('creator' in propvalues or 'actor' in propvalues or
1618 'creation' in propvalues or 'activity' in propvalues):
1619 raise KeyError('"creator", "actor", "creation" and '
1620 '"activity" are reserved')
1622 if 'id' in propvalues:
1623 raise KeyError('"id" is reserved')
1625 if self.db.journaltag is None:
1626 raise DatabaseError(_('Database open read-only'))
1628 node = self.db.getnode(self.classname, nodeid)
1629 if self.is_retired(nodeid):
1630 raise IndexError('Requested item is retired')
1631 num_re = re.compile('^\d+$')
1633 # make a copy of the values dictionary - we'll modify the contents
1634 propvalues = propvalues.copy()
1636 # if the journal value is to be different, store it in here
1637 journalvalues = {}
1639 # remember the add/remove stuff for multilinks, making it easier
1640 # for the Database layer to do its stuff
1641 multilink_changes = {}
1643 for propname, value in list(propvalues.items()):
1644 # check to make sure we're not duplicating an existing key
1645 if propname == self.key and node[propname] != value:
1646 try:
1647 self.lookup(value)
1648 except KeyError:
1649 pass
1650 else:
1651 raise ValueError('node with key "%s" exists'%value)
1653 # this will raise the KeyError if the property isn't valid
1654 # ... we don't use getprops() here because we only care about
1655 # the writeable properties.
1656 try:
1657 prop = self.properties[propname]
1658 except KeyError:
1659 raise KeyError('"%s" has no property named "%s"'%(
1660 self.classname, propname))
1662 # if the value's the same as the existing value, no sense in
1663 # doing anything
1664 current = node.get(propname, None)
1665 if value == current:
1666 del propvalues[propname]
1667 continue
1668 journalvalues[propname] = current
1670 # do stuff based on the prop type
1671 if isinstance(prop, Link):
1672 link_class = prop.classname
1673 # if it isn't a number, it's a key
1674 if value is not None and not isinstance(value, type('')):
1675 raise ValueError('property "%s" link value be a string'%(
1676 propname))
1677 if isinstance(value, type('')) and not num_re.match(value):
1678 try:
1679 value = self.db.classes[link_class].lookup(value)
1680 except (TypeError, KeyError):
1681 raise IndexError('new property "%s": %s not a %s'%(
1682 propname, value, prop.classname))
1684 if (value is not None and
1685 not self.db.getclass(link_class).hasnode(value)):
1686 raise IndexError('%s has no node %s'%(link_class,
1687 value))
1689 if self.do_journal and prop.do_journal:
1690 # register the unlink with the old linked node
1691 if node[propname] is not None:
1692 self.db.addjournal(link_class, node[propname],
1693 ''"unlink", (self.classname, nodeid, propname))
1695 # register the link with the newly linked node
1696 if value is not None:
1697 self.db.addjournal(link_class, value, ''"link",
1698 (self.classname, nodeid, propname))
1700 elif isinstance(prop, Multilink):
1701 if value is None:
1702 value = []
1703 if not hasattr(value, '__iter__'):
1704 raise TypeError('new property "%s" not an iterable of'
1705 ' ids'%propname)
1706 link_class = self.properties[propname].classname
1707 l = []
1708 for entry in value:
1709 # if it isn't a number, it's a key
1710 if type(entry) != type(''):
1711 raise ValueError('new property "%s" link value '
1712 'must be a string'%propname)
1713 if not num_re.match(entry):
1714 try:
1715 entry = self.db.classes[link_class].lookup(entry)
1716 except (TypeError, KeyError):
1717 raise IndexError('new property "%s": %s not a %s'%(
1718 propname, entry,
1719 self.properties[propname].classname))
1720 l.append(entry)
1721 value = l
1722 propvalues[propname] = value
1724 # figure the journal entry for this property
1725 add = []
1726 remove = []
1728 # handle removals
1729 if propname in node:
1730 l = node[propname]
1731 else:
1732 l = []
1733 for id in l[:]:
1734 if id in value:
1735 continue
1736 # register the unlink with the old linked node
1737 if self.do_journal and self.properties[propname].do_journal:
1738 self.db.addjournal(link_class, id, 'unlink',
1739 (self.classname, nodeid, propname))
1740 l.remove(id)
1741 remove.append(id)
1743 # handle additions
1744 for id in value:
1745 if id in l:
1746 continue
1747 # We can safely check this condition after
1748 # checking that this is an addition to the
1749 # multilink since the condition was checked for
1750 # existing entries at the point they were added to
1751 # the multilink. Since the hasnode call will
1752 # result in a SQL query, it is more efficient to
1753 # avoid the check if possible.
1754 if not self.db.getclass(link_class).hasnode(id):
1755 raise IndexError('%s has no node %s'%(link_class,
1756 id))
1757 # register the link with the newly linked node
1758 if self.do_journal and self.properties[propname].do_journal:
1759 self.db.addjournal(link_class, id, 'link',
1760 (self.classname, nodeid, propname))
1761 l.append(id)
1762 add.append(id)
1764 # figure the journal entry
1765 l = []
1766 if add:
1767 l.append(('+', add))
1768 if remove:
1769 l.append(('-', remove))
1770 multilink_changes[propname] = (add, remove)
1771 if l:
1772 journalvalues[propname] = tuple(l)
1774 elif isinstance(prop, String):
1775 if value is not None and type(value) != type('') and type(value) != type(u''):
1776 raise TypeError('new property "%s" not a string'%propname)
1777 if prop.indexme:
1778 if value is None: value = ''
1779 self.db.indexer.add_text((self.classname, nodeid, propname),
1780 value)
1782 elif isinstance(prop, Password):
1783 if not isinstance(value, password.Password):
1784 raise TypeError('new property "%s" not a Password'%propname)
1785 propvalues[propname] = value
1787 elif value is not None and isinstance(prop, Date):
1788 if not isinstance(value, date.Date):
1789 raise TypeError('new property "%s" not a Date'% propname)
1790 propvalues[propname] = value
1792 elif value is not None and isinstance(prop, Interval):
1793 if not isinstance(value, date.Interval):
1794 raise TypeError('new property "%s" not an '
1795 'Interval'%propname)
1796 propvalues[propname] = value
1798 elif value is not None and isinstance(prop, Number):
1799 try:
1800 float(value)
1801 except ValueError:
1802 raise TypeError('new property "%s" not numeric'%propname)
1804 elif value is not None and isinstance(prop, Boolean):
1805 try:
1806 int(value)
1807 except ValueError:
1808 raise TypeError('new property "%s" not boolean'%propname)
1810 # nothing to do?
1811 if not propvalues:
1812 return propvalues
1814 # update the activity time
1815 propvalues['activity'] = date.Date()
1816 propvalues['actor'] = self.db.getuid()
1818 # do the set
1819 self.db.setnode(self.classname, nodeid, propvalues, multilink_changes)
1821 # remove the activity props now they're handled
1822 del propvalues['activity']
1823 del propvalues['actor']
1825 # journal the set
1826 if self.do_journal:
1827 self.db.addjournal(self.classname, nodeid, ''"set", journalvalues)
1829 return propvalues
1831 def retire(self, nodeid):
1832 """Retire a node.
1834 The properties on the node remain available from the get() method,
1835 and the node's id is never reused.
1837 Retired nodes are not returned by the find(), list(), or lookup()
1838 methods, and other nodes may reuse the values of their key properties.
1839 """
1840 if self.db.journaltag is None:
1841 raise DatabaseError(_('Database open read-only'))
1843 self.fireAuditors('retire', nodeid, None)
1845 # use the arg for __retired__ to cope with any odd database type
1846 # conversion (hello, sqlite)
1847 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
1848 self.db.arg, self.db.arg)
1849 self.db.sql(sql, (nodeid, nodeid))
1850 if self.do_journal:
1851 self.db.addjournal(self.classname, nodeid, ''"retired", None)
1853 self.fireReactors('retire', nodeid, None)
1855 def restore(self, nodeid):
1856 """Restore a retired node.
1858 Make node available for all operations like it was before retirement.
1859 """
1860 if self.db.journaltag is None:
1861 raise DatabaseError(_('Database open read-only'))
1863 node = self.db.getnode(self.classname, nodeid)
1864 # check if key property was overrided
1865 key = self.getkey()
1866 try:
1867 id = self.lookup(node[key])
1868 except KeyError:
1869 pass
1870 else:
1871 raise KeyError("Key property (%s) of retired node clashes "
1872 "with existing one (%s)" % (key, node[key]))
1874 self.fireAuditors('restore', nodeid, None)
1875 # use the arg for __retired__ to cope with any odd database type
1876 # conversion (hello, sqlite)
1877 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
1878 self.db.arg, self.db.arg)
1879 self.db.sql(sql, (0, nodeid))
1880 if self.do_journal:
1881 self.db.addjournal(self.classname, nodeid, ''"restored", None)
1883 self.fireReactors('restore', nodeid, None)
1885 def is_retired(self, nodeid):
1886 """Return true if the node is rerired
1887 """
1888 sql = 'select __retired__ from _%s where id=%s'%(self.classname,
1889 self.db.arg)
1890 self.db.sql(sql, (nodeid,))
1891 return int(self.db.sql_fetchone()[0]) > 0
1893 def destroy(self, nodeid):
1894 """Destroy a node.
1896 WARNING: this method should never be used except in extremely rare
1897 situations where there could never be links to the node being
1898 deleted
1900 WARNING: use retire() instead
1902 WARNING: the properties of this node will not be available ever again
1904 WARNING: really, use retire() instead
1906 Well, I think that's enough warnings. This method exists mostly to
1907 support the session storage of the cgi interface.
1909 The node is completely removed from the hyperdb, including all journal
1910 entries. It will no longer be available, and will generally break code
1911 if there are any references to the node.
1912 """
1913 if self.db.journaltag is None:
1914 raise DatabaseError(_('Database open read-only'))
1915 self.db.destroynode(self.classname, nodeid)
1917 def history(self, nodeid):
1918 """Retrieve the journal of edits on a particular node.
1920 'nodeid' must be the id of an existing node of this class or an
1921 IndexError is raised.
1923 The returned list contains tuples of the form
1925 (nodeid, date, tag, action, params)
1927 'date' is a Timestamp object specifying the time of the change and
1928 'tag' is the journaltag specified when the database was opened.
1929 """
1930 if not self.do_journal:
1931 raise ValueError('Journalling is disabled for this class')
1932 return self.db.getjournal(self.classname, nodeid)
1934 # Locating nodes:
1935 def hasnode(self, nodeid):
1936 """Determine if the given nodeid actually exists
1937 """
1938 return self.db.hasnode(self.classname, nodeid)
1940 def setkey(self, propname):
1941 """Select a String property of this class to be the key property.
1943 'propname' must be the name of a String property of this class or
1944 None, or a TypeError is raised. The values of the key property on
1945 all existing nodes must be unique or a ValueError is raised.
1946 """
1947 prop = self.getprops()[propname]
1948 if not isinstance(prop, String):
1949 raise TypeError('key properties must be String')
1950 self.key = propname
1952 def getkey(self):
1953 """Return the name of the key property for this class or None."""
1954 return self.key
1956 def lookup(self, keyvalue):
1957 """Locate a particular node by its key property and return its id.
1959 If this class has no key property, a TypeError is raised. If the
1960 'keyvalue' matches one of the values for the key property among
1961 the nodes in this class, the matching node's id is returned;
1962 otherwise a KeyError is raised.
1963 """
1964 if not self.key:
1965 raise TypeError('No key property set for class %s'%self.classname)
1967 # use the arg to handle any odd database type conversion (hello,
1968 # sqlite)
1969 sql = "select id from _%s where _%s=%s and __retired__=%s"%(
1970 self.classname, self.key, self.db.arg, self.db.arg)
1971 self.db.sql(sql, (str(keyvalue), 0))
1973 # see if there was a result that's not retired
1974 row = self.db.sql_fetchone()
1975 if not row:
1976 raise KeyError('No key (%s) value "%s" for "%s"'%(self.key,
1977 keyvalue, self.classname))
1979 # return the id
1980 # XXX numeric ids
1981 return str(row[0])
1983 def find(self, **propspec):
1984 """Get the ids of nodes in this class which link to the given nodes.
1986 'propspec' consists of keyword args propname=nodeid or
1987 propname={nodeid:1, }
1988 'propname' must be the name of a property in this class, or a
1989 KeyError is raised. That property must be a Link or
1990 Multilink property, or a TypeError is raised.
1992 Any node in this class whose 'propname' property links to any of
1993 the nodeids will be returned. Examples::
1995 db.issue.find(messages='1')
1996 db.issue.find(messages={'1':1,'3':1}, files={'7':1})
1997 """
1998 # shortcut
1999 if not propspec:
2000 return []
2002 # validate the args
2003 props = self.getprops()
2004 for propname, nodeids in propspec.iteritems():
2005 # check the prop is OK
2006 prop = props[propname]
2007 if not isinstance(prop, Link) and not isinstance(prop, Multilink):
2008 raise TypeError("'%s' not a Link/Multilink property"%propname)
2010 # first, links
2011 a = self.db.arg
2012 allvalues = ()
2013 sql = []
2014 where = []
2015 for prop, values in propspec.iteritems():
2016 if not isinstance(props[prop], hyperdb.Link):
2017 continue
2018 if type(values) is type({}) and len(values) == 1:
2019 values = list(values)[0]
2020 if type(values) is type(''):
2021 allvalues += (values,)
2022 where.append('_%s = %s'%(prop, a))
2023 elif values is None:
2024 where.append('_%s is NULL'%prop)
2025 else:
2026 values = list(values)
2027 s = ''
2028 if None in values:
2029 values.remove(None)
2030 s = '_%s is NULL or '%prop
2031 allvalues += tuple(values)
2032 s += '_%s in (%s)'%(prop, ','.join([a]*len(values)))
2033 where.append('(' + s +')')
2034 if where:
2035 allvalues = (0, ) + allvalues
2036 sql.append("""select id from _%s where __retired__=%s
2037 and %s"""%(self.classname, a, ' and '.join(where)))
2039 # now multilinks
2040 for prop, values in propspec.iteritems():
2041 if not isinstance(props[prop], hyperdb.Multilink):
2042 continue
2043 if not values:
2044 continue
2045 allvalues += (0, )
2046 if type(values) is type(''):
2047 allvalues += (values,)
2048 s = a
2049 else:
2050 allvalues += tuple(values)
2051 s = ','.join([a]*len(values))
2052 tn = '%s_%s'%(self.classname, prop)
2053 sql.append("""select id from _%s, %s where __retired__=%s
2054 and id = %s.nodeid and %s.linkid in (%s)"""%(self.classname,
2055 tn, a, tn, tn, s))
2057 if not sql:
2058 return []
2059 sql = ' union '.join(sql)
2060 self.db.sql(sql, allvalues)
2061 # XXX numeric ids
2062 l = [str(x[0]) for x in self.db.sql_fetchall()]
2063 return l
2065 def stringFind(self, **requirements):
2066 """Locate a particular node by matching a set of its String
2067 properties in a caseless search.
2069 If the property is not a String property, a TypeError is raised.
2071 The return is a list of the id of all nodes that match.
2072 """
2073 where = []
2074 args = []
2075 for propname in requirements:
2076 prop = self.properties[propname]
2077 if not isinstance(prop, String):
2078 raise TypeError("'%s' not a String property"%propname)
2079 where.append(propname)
2080 args.append(requirements[propname].lower())
2082 # generate the where clause
2083 s = ' and '.join(['lower(_%s)=%s'%(col, self.db.arg) for col in where])
2084 sql = 'select id from _%s where %s and __retired__=%s'%(
2085 self.classname, s, self.db.arg)
2086 args.append(0)
2087 self.db.sql(sql, tuple(args))
2088 # XXX numeric ids
2089 l = [str(x[0]) for x in self.db.sql_fetchall()]
2090 return l
2092 def list(self):
2093 """ Return a list of the ids of the active nodes in this class.
2094 """
2095 return self.getnodeids(retired=0)
2097 def getnodeids(self, retired=None):
2098 """ Retrieve all the ids of the nodes for a particular Class.
2100 Set retired=None to get all nodes. Otherwise it'll get all the
2101 retired or non-retired nodes, depending on the flag.
2102 """
2103 # flip the sense of the 'retired' flag if we don't want all of them
2104 if retired is not None:
2105 args = (0, )
2106 if retired:
2107 compare = '>'
2108 else:
2109 compare = '='
2110 sql = 'select id from _%s where __retired__%s%s'%(self.classname,
2111 compare, self.db.arg)
2112 else:
2113 args = ()
2114 sql = 'select id from _%s'%self.classname
2115 self.db.sql(sql, args)
2116 # XXX numeric ids
2117 ids = [str(x[0]) for x in self.db.cursor.fetchall()]
2118 return ids
2120 def _subselect(self, classname, multilink_table):
2121 """Create a subselect. This is factored out because some
2122 databases (hmm only one, so far) doesn't support subselects
2123 look for "I can't believe it's not a toy RDBMS" in the mysql
2124 backend.
2125 """
2126 return '_%s.id not in (select nodeid from %s)'%(classname,
2127 multilink_table)
2129 # Some DBs order NULL values last. Set this variable in the backend
2130 # for prepending an order by clause for each attribute that causes
2131 # correct sort order for NULLs. Examples:
2132 # order_by_null_values = '(%s is not NULL)'
2133 # order_by_null_values = 'notnull(%s)'
2134 # The format parameter is replaced with the attribute.
2135 order_by_null_values = None
2137 def filter(self, search_matches, filterspec, sort=[], group=[]):
2138 """Return a list of the ids of the active nodes in this class that
2139 match the 'filter' spec, sorted by the group spec and then the
2140 sort spec
2142 "filterspec" is {propname: value(s)}
2144 "sort" and "group" are [(dir, prop), ...] where dir is '+', '-'
2145 or None and prop is a prop name or None. Note that for
2146 backward-compatibility reasons a single (dir, prop) tuple is
2147 also allowed.
2149 "search_matches" is a container type or None
2151 The filter must match all properties specificed. If the property
2152 value to match is a list:
2154 1. String properties must match all elements in the list, and
2155 2. Other properties must match any of the elements in the list.
2156 """
2157 # we can't match anything if search_matches is empty
2158 if not search_matches and search_matches is not None:
2159 return []
2161 if __debug__:
2162 start_t = time.time()
2164 icn = self.classname
2166 # vars to hold the components of the SQL statement
2167 frum = [] # FROM clauses
2168 loj = [] # LEFT OUTER JOIN clauses
2169 where = [] # WHERE clauses
2170 args = [] # *any* positional arguments
2171 a = self.db.arg
2173 # figure the WHERE clause from the filterspec
2174 mlfilt = 0 # are we joining with Multilink tables?
2175 sortattr = self._sortattr (group = group, sort = sort)
2176 proptree = self._proptree(filterspec, sortattr)
2177 mlseen = 0
2178 for pt in reversed(proptree.sortattr):
2179 p = pt
2180 while p.parent:
2181 if isinstance (p.propclass, Multilink):
2182 mlseen = True
2183 if mlseen:
2184 p.sort_ids_needed = True
2185 p.tree_sort_done = False
2186 p = p.parent
2187 if not mlseen:
2188 pt.attr_sort_done = pt.tree_sort_done = True
2189 proptree.compute_sort_done()
2191 ordercols = []
2192 auxcols = {}
2193 mlsort = []
2194 rhsnum = 0
2195 for p in proptree:
2196 oc = None
2197 cn = p.classname
2198 ln = p.uniqname
2199 pln = p.parent.uniqname
2200 pcn = p.parent.classname
2201 k = p.name
2202 v = p.val
2203 propclass = p.propclass
2204 if p.sort_type > 0:
2205 oc = ac = '_%s._%s'%(pln, k)
2206 if isinstance(propclass, Multilink):
2207 if p.sort_type < 2:
2208 mlfilt = 1
2209 tn = '%s_%s'%(pcn, k)
2210 if v in ('-1', ['-1'], []):
2211 # only match rows that have count(linkid)=0 in the
2212 # corresponding multilink table)
2213 where.append(self._subselect(pcn, tn))
2214 else:
2215 frum.append(tn)
2216 where.append('_%s.id=%s.nodeid'%(pln,tn))
2217 if p.children:
2218 frum.append('_%s as _%s' % (cn, ln))
2219 where.append('%s.linkid=_%s.id'%(tn, ln))
2220 if p.has_values:
2221 if isinstance(v, type([])):
2222 s = ','.join([a for x in v])
2223 where.append('%s.linkid in (%s)'%(tn, s))
2224 args = args + v
2225 else:
2226 where.append('%s.linkid=%s'%(tn, a))
2227 args.append(v)
2228 if p.sort_type > 0:
2229 assert not p.attr_sort_done and not p.sort_ids_needed
2230 elif k == 'id':
2231 if p.sort_type < 2:
2232 if isinstance(v, type([])):
2233 s = ','.join([a for x in v])
2234 where.append('_%s.%s in (%s)'%(pln, k, s))
2235 args = args + v
2236 else:
2237 where.append('_%s.%s=%s'%(pln, k, a))
2238 args.append(v)
2239 if p.sort_type > 0:
2240 oc = ac = '_%s.id'%pln
2241 elif isinstance(propclass, String):
2242 if p.sort_type < 2:
2243 if not isinstance(v, type([])):
2244 v = [v]
2246 # Quote the bits in the string that need it and then embed
2247 # in a "substring" search. Note - need to quote the '%' so
2248 # they make it through the python layer happily
2249 v = ['%%'+self.db.sql_stringquote(s)+'%%' for s in v]
2251 # now add to the where clause
2252 where.append('('
2253 +' and '.join(["_%s._%s LIKE '%s'"%(pln, k, s) for s in v])
2254 +')')
2255 # note: args are embedded in the query string now
2256 if p.sort_type > 0:
2257 oc = ac = 'lower(_%s._%s)'%(pln, k)
2258 elif isinstance(propclass, Link):
2259 if p.sort_type < 2:
2260 if p.children:
2261 if p.sort_type == 0:
2262 frum.append('_%s as _%s' % (cn, ln))
2263 where.append('_%s._%s=_%s.id'%(pln, k, ln))
2264 if p.has_values:
2265 if isinstance(v, type([])):
2266 d = {}
2267 for entry in v:
2268 if entry == '-1':
2269 entry = None
2270 d[entry] = entry
2271 l = []
2272 if None in d or not d:
2273 if None in d: del d[None]
2274 l.append('_%s._%s is NULL'%(pln, k))
2275 if d:
2276 v = list(d)
2277 s = ','.join([a for x in v])
2278 l.append('(_%s._%s in (%s))'%(pln, k, s))
2279 args = args + v
2280 if l:
2281 where.append('(' + ' or '.join(l) +')')
2282 else:
2283 if v in ('-1', None):
2284 v = None
2285 where.append('_%s._%s is NULL'%(pln, k))
2286 else:
2287 where.append('_%s._%s=%s'%(pln, k, a))
2288 args.append(v)
2289 if p.sort_type > 0:
2290 lp = p.cls.labelprop()
2291 oc = ac = '_%s._%s'%(pln, k)
2292 if lp != 'id':
2293 if p.tree_sort_done and p.sort_type > 0:
2294 loj.append(
2295 'LEFT OUTER JOIN _%s as _%s on _%s._%s=_%s.id'%(
2296 cn, ln, pln, k, ln))
2297 oc = '_%s._%s'%(ln, lp)
2298 elif isinstance(propclass, Date) and p.sort_type < 2:
2299 dc = self.db.to_sql_value(hyperdb.Date)
2300 if isinstance(v, type([])):
2301 s = ','.join([a for x in v])
2302 where.append('_%s._%s in (%s)'%(pln, k, s))
2303 args = args + [dc(date.Date(x)) for x in v]
2304 else:
2305 try:
2306 # Try to filter on range of dates
2307 date_rng = propclass.range_from_raw(v, self.db)
2308 if date_rng.from_value:
2309 where.append('_%s._%s >= %s'%(pln, k, a))
2310 args.append(dc(date_rng.from_value))
2311 if date_rng.to_value:
2312 where.append('_%s._%s <= %s'%(pln, k, a))
2313 args.append(dc(date_rng.to_value))
2314 except ValueError:
2315 # If range creation fails - ignore that search parameter
2316 pass
2317 elif isinstance(propclass, Interval):
2318 # filter/sort using the __<prop>_int__ column
2319 if p.sort_type < 2:
2320 if isinstance(v, type([])):
2321 s = ','.join([a for x in v])
2322 where.append('_%s.__%s_int__ in (%s)'%(pln, k, s))
2323 args = args + [date.Interval(x).as_seconds() for x in v]
2324 else:
2325 try:
2326 # Try to filter on range of intervals
2327 date_rng = Range(v, date.Interval)
2328 if date_rng.from_value:
2329 where.append('_%s.__%s_int__ >= %s'%(pln, k, a))
2330 args.append(date_rng.from_value.as_seconds())
2331 if date_rng.to_value:
2332 where.append('_%s.__%s_int__ <= %s'%(pln, k, a))
2333 args.append(date_rng.to_value.as_seconds())
2334 except ValueError:
2335 # If range creation fails - ignore search parameter
2336 pass
2337 if p.sort_type > 0:
2338 oc = ac = '_%s.__%s_int__'%(pln,k)
2339 elif isinstance(propclass, Boolean) and p.sort_type < 2:
2340 if type(v) == type(""):
2341 v = v.split(',')
2342 if type(v) != type([]):
2343 v = [v]
2344 bv = []
2345 for val in v:
2346 if type(val) is type(''):
2347 bv.append(propclass.from_raw (val))
2348 else:
2349 bv.append(bool(val))
2350 if len(bv) == 1:
2351 where.append('_%s._%s=%s'%(pln, k, a))
2352 args = args + bv
2353 else:
2354 s = ','.join([a for x in v])
2355 where.append('_%s._%s in (%s)'%(pln, k, s))
2356 args = args + bv
2357 elif p.sort_type < 2:
2358 if isinstance(v, type([])):
2359 s = ','.join([a for x in v])
2360 where.append('_%s._%s in (%s)'%(pln, k, s))
2361 args = args + v
2362 else:
2363 where.append('_%s._%s=%s'%(pln, k, a))
2364 args.append(v)
2365 if oc:
2366 if p.sort_ids_needed:
2367 auxcols[ac] = p
2368 if p.tree_sort_done and p.sort_direction:
2369 # Don't select top-level id twice
2370 if p.name != 'id' or p.parent != proptree:
2371 ordercols.append(oc)
2372 desc = ['', ' desc'][p.sort_direction == '-']
2373 # Some SQL dbs sort NULL values last -- we want them first.
2374 if (self.order_by_null_values and p.name != 'id'):
2375 nv = self.order_by_null_values % oc
2376 ordercols.append(nv)
2377 p.orderby.append(nv + desc)
2378 p.orderby.append(oc + desc)
2380 props = self.getprops()
2382 # don't match retired nodes
2383 where.append('_%s.__retired__=0'%icn)
2385 # add results of full text search
2386 if search_matches is not None:
2387 s = ','.join([a for x in search_matches])
2388 where.append('_%s.id in (%s)'%(icn, s))
2389 args = args + [x for x in search_matches]
2391 # construct the SQL
2392 frum.append('_'+icn)
2393 frum = ','.join(frum)
2394 if where:
2395 where = ' where ' + (' and '.join(where))
2396 else:
2397 where = ''
2398 if mlfilt:
2399 # we're joining tables on the id, so we will get dupes if we
2400 # don't distinct()
2401 cols = ['distinct(_%s.id)'%icn]
2402 else:
2403 cols = ['_%s.id'%icn]
2404 if ordercols:
2405 cols = cols + ordercols
2406 order = []
2407 # keep correct sequence of order attributes.
2408 for sa in proptree.sortattr:
2409 if not sa.attr_sort_done:
2410 continue
2411 order.extend(sa.orderby)
2412 if order:
2413 order = ' order by %s'%(','.join(order))
2414 else:
2415 order = ''
2416 for o, p in auxcols.iteritems ():
2417 cols.append (o)
2418 p.auxcol = len (cols) - 1
2420 cols = ','.join(cols)
2421 loj = ' '.join(loj)
2422 sql = 'select %s from %s %s %s%s'%(cols, frum, loj, where, order)
2423 args = tuple(args)
2424 __traceback_info__ = (sql, args)
2425 self.db.sql(sql, args)
2426 l = self.db.sql_fetchall()
2428 # Compute values needed for sorting in proptree.sort
2429 for p in auxcols.itervalues():
2430 p.sort_ids = p.sort_result = [row[p.auxcol] for row in l]
2431 # return the IDs (the first column)
2432 # XXX numeric ids
2433 l = [str(row[0]) for row in l]
2434 l = proptree.sort (l)
2436 if __debug__:
2437 self.db.stats['filtering'] += (time.time() - start_t)
2438 return l
2440 def filter_sql(self, sql):
2441 """Return a list of the ids of the items in this class that match
2442 the SQL provided. The SQL is a complete "select" statement.
2444 The SQL select must include the item id as the first column.
2446 This function DOES NOT filter out retired items, add on a where
2447 clause "__retired__=0" if you don't want retired nodes.
2448 """
2449 if __debug__:
2450 start_t = time.time()
2452 self.db.sql(sql)
2453 l = self.db.sql_fetchall()
2455 if __debug__:
2456 self.db.stats['filtering'] += (time.time() - start_t)
2457 return l
2459 def count(self):
2460 """Get the number of nodes in this class.
2462 If the returned integer is 'numnodes', the ids of all the nodes
2463 in this class run from 1 to numnodes, and numnodes+1 will be the
2464 id of the next node to be created in this class.
2465 """
2466 return self.db.countnodes(self.classname)
2468 # Manipulating properties:
2469 def getprops(self, protected=1):
2470 """Return a dictionary mapping property names to property objects.
2471 If the "protected" flag is true, we include protected properties -
2472 those which may not be modified.
2473 """
2474 d = self.properties.copy()
2475 if protected:
2476 d['id'] = String()
2477 d['creation'] = hyperdb.Date()
2478 d['activity'] = hyperdb.Date()
2479 d['creator'] = hyperdb.Link('user')
2480 d['actor'] = hyperdb.Link('user')
2481 return d
2483 def addprop(self, **properties):
2484 """Add properties to this class.
2486 The keyword arguments in 'properties' must map names to property
2487 objects, or a TypeError is raised. None of the keys in 'properties'
2488 may collide with the names of existing properties, or a ValueError
2489 is raised before any properties have been added.
2490 """
2491 for key in properties:
2492 if key in self.properties:
2493 raise ValueError(key)
2494 self.properties.update(properties)
2496 def index(self, nodeid):
2497 """Add (or refresh) the node to search indexes
2498 """
2499 # find all the String properties that have indexme
2500 for prop, propclass in self.getprops().iteritems():
2501 if isinstance(propclass, String) and propclass.indexme:
2502 self.db.indexer.add_text((self.classname, nodeid, prop),
2503 str(self.get(nodeid, prop)))
2505 #
2506 # import / export support
2507 #
2508 def export_list(self, propnames, nodeid):
2509 """ Export a node - generate a list of CSV-able data in the order
2510 specified by propnames for the given node.
2511 """
2512 properties = self.getprops()
2513 l = []
2514 for prop in propnames:
2515 proptype = properties[prop]
2516 value = self.get(nodeid, prop)
2517 # "marshal" data where needed
2518 if value is None:
2519 pass
2520 elif isinstance(proptype, hyperdb.Date):
2521 value = value.get_tuple()
2522 elif isinstance(proptype, hyperdb.Interval):
2523 value = value.get_tuple()
2524 elif isinstance(proptype, hyperdb.Password):
2525 value = str(value)
2526 l.append(repr(value))
2527 l.append(repr(self.is_retired(nodeid)))
2528 return l
2530 def import_list(self, propnames, proplist):
2531 """ Import a node - all information including "id" is present and
2532 should not be sanity checked. Triggers are not triggered. The
2533 journal should be initialised using the "creator" and "created"
2534 information.
2536 Return the nodeid of the node imported.
2537 """
2538 if self.db.journaltag is None:
2539 raise DatabaseError(_('Database open read-only'))
2540 properties = self.getprops()
2542 # make the new node's property map
2543 d = {}
2544 retire = 0
2545 if not "id" in propnames:
2546 newid = self.db.newid(self.classname)
2547 else:
2548 newid = eval(proplist[propnames.index("id")])
2549 for i in range(len(propnames)):
2550 # Use eval to reverse the repr() used to output the CSV
2551 value = eval(proplist[i])
2553 # Figure the property for this column
2554 propname = propnames[i]
2556 # "unmarshal" where necessary
2557 if propname == 'id':
2558 continue
2559 elif propname == 'is retired':
2560 # is the item retired?
2561 if int(value):
2562 retire = 1
2563 continue
2564 elif value is None:
2565 d[propname] = None
2566 continue
2568 prop = properties[propname]
2569 if value is None:
2570 # don't set Nones
2571 continue
2572 elif isinstance(prop, hyperdb.Date):
2573 value = date.Date(value)
2574 elif isinstance(prop, hyperdb.Interval):
2575 value = date.Interval(value)
2576 elif isinstance(prop, hyperdb.Password):
2577 pwd = password.Password()
2578 pwd.unpack(value)
2579 value = pwd
2580 elif isinstance(prop, String):
2581 if isinstance(value, unicode):
2582 value = value.encode('utf8')
2583 if not isinstance(value, str):
2584 raise TypeError('new property "%(propname)s" not a '
2585 'string: %(value)r'%locals())
2586 if prop.indexme:
2587 self.db.indexer.add_text((self.classname, newid, propname),
2588 value)
2589 d[propname] = value
2591 # get a new id if necessary
2592 if newid is None:
2593 newid = self.db.newid(self.classname)
2595 # insert new node or update existing?
2596 if not self.hasnode(newid):
2597 self.db.addnode(self.classname, newid, d) # insert
2598 else:
2599 self.db.setnode(self.classname, newid, d) # update
2601 # retire?
2602 if retire:
2603 # use the arg for __retired__ to cope with any odd database type
2604 # conversion (hello, sqlite)
2605 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
2606 self.db.arg, self.db.arg)
2607 self.db.sql(sql, (newid, newid))
2608 return newid
2610 def export_journals(self):
2611 """Export a class's journal - generate a list of lists of
2612 CSV-able data:
2614 nodeid, date, user, action, params
2616 No heading here - the columns are fixed.
2617 """
2618 properties = self.getprops()
2619 r = []
2620 for nodeid in self.getnodeids():
2621 for nodeid, date, user, action, params in self.history(nodeid):
2622 date = date.get_tuple()
2623 if action == 'set':
2624 export_data = {}
2625 for propname, value in params.iteritems():
2626 if propname not in properties:
2627 # property no longer in the schema
2628 continue
2630 prop = properties[propname]
2631 # make sure the params are eval()'able
2632 if value is None:
2633 pass
2634 elif isinstance(prop, Date):
2635 value = value.get_tuple()
2636 elif isinstance(prop, Interval):
2637 value = value.get_tuple()
2638 elif isinstance(prop, Password):
2639 value = str(value)
2640 export_data[propname] = value
2641 params = export_data
2642 elif action == 'create' and params:
2643 # old tracker with data stored in the create!
2644 params = {}
2645 l = [nodeid, date, user, action, params]
2646 r.append(list(map(repr, l)))
2647 return r
2649 class FileClass(hyperdb.FileClass, Class):
2650 """This class defines a large chunk of data. To support this, it has a
2651 mandatory String property "content" which is typically saved off
2652 externally to the hyperdb.
2654 The default MIME type of this data is defined by the
2655 "default_mime_type" class attribute, which may be overridden by each
2656 node if the class defines a "type" String property.
2657 """
2658 def __init__(self, db, classname, **properties):
2659 """The newly-created class automatically includes the "content"
2660 and "type" properties.
2661 """
2662 if 'content' not in properties:
2663 properties['content'] = hyperdb.String(indexme='yes')
2664 if 'type' not in properties:
2665 properties['type'] = hyperdb.String()
2666 Class.__init__(self, db, classname, **properties)
2668 def create(self, **propvalues):
2669 """ snaffle the file propvalue and store in a file
2670 """
2671 # we need to fire the auditors now, or the content property won't
2672 # be in propvalues for the auditors to play with
2673 self.fireAuditors('create', None, propvalues)
2675 # now remove the content property so it's not stored in the db
2676 content = propvalues['content']
2677 del propvalues['content']
2679 # do the database create
2680 newid = self.create_inner(**propvalues)
2682 # figure the mime type
2683 mime_type = propvalues.get('type', self.default_mime_type)
2685 # and index!
2686 if self.properties['content'].indexme:
2687 self.db.indexer.add_text((self.classname, newid, 'content'),
2688 content, mime_type)
2690 # store off the content as a file
2691 self.db.storefile(self.classname, newid, None, content)
2693 # fire reactors
2694 self.fireReactors('create', newid, None)
2696 return newid
2698 def get(self, nodeid, propname, default=_marker, cache=1):
2699 """ Trap the content propname and get it from the file
2701 'cache' exists for backwards compatibility, and is not used.
2702 """
2703 poss_msg = 'Possibly a access right configuration problem.'
2704 if propname == 'content':
2705 try:
2706 return self.db.getfile(self.classname, nodeid, None)
2707 except IOError, strerror:
2708 # BUG: by catching this we donot see an error in the log.
2709 return 'ERROR reading file: %s%s\n%s\n%s'%(
2710 self.classname, nodeid, poss_msg, strerror)
2711 if default is not _marker:
2712 return Class.get(self, nodeid, propname, default)
2713 else:
2714 return Class.get(self, nodeid, propname)
2716 def set(self, itemid, **propvalues):
2717 """ Snarf the "content" propvalue and update it in a file
2718 """
2719 self.fireAuditors('set', itemid, propvalues)
2720 oldvalues = copy.deepcopy(self.db.getnode(self.classname, itemid))
2722 # now remove the content property so it's not stored in the db
2723 content = None
2724 if 'content' in propvalues:
2725 content = propvalues['content']
2726 del propvalues['content']
2728 # do the database create
2729 propvalues = self.set_inner(itemid, **propvalues)
2731 # do content?
2732 if content:
2733 # store and possibly index
2734 self.db.storefile(self.classname, itemid, None, content)
2735 if self.properties['content'].indexme:
2736 mime_type = self.get(itemid, 'type', self.default_mime_type)
2737 self.db.indexer.add_text((self.classname, itemid, 'content'),
2738 content, mime_type)
2739 propvalues['content'] = content
2741 # fire reactors
2742 self.fireReactors('set', itemid, oldvalues)
2743 return propvalues
2745 def index(self, nodeid):
2746 """ Add (or refresh) the node to search indexes.
2748 Use the content-type property for the content property.
2749 """
2750 # find all the String properties that have indexme
2751 for prop, propclass in self.getprops().iteritems():
2752 if prop == 'content' and propclass.indexme:
2753 mime_type = self.get(nodeid, 'type', self.default_mime_type)
2754 self.db.indexer.add_text((self.classname, nodeid, 'content'),
2755 str(self.get(nodeid, 'content')), mime_type)
2756 elif isinstance(propclass, hyperdb.String) and propclass.indexme:
2757 # index them under (classname, nodeid, property)
2758 try:
2759 value = str(self.get(nodeid, prop))
2760 except IndexError:
2761 # node has been destroyed
2762 continue
2763 self.db.indexer.add_text((self.classname, nodeid, prop), value)
2765 # XXX deviation from spec - was called ItemClass
2766 class IssueClass(Class, roundupdb.IssueClass):
2767 # Overridden methods:
2768 def __init__(self, db, classname, **properties):
2769 """The newly-created class automatically includes the "messages",
2770 "files", "nosy", and "superseder" properties. If the 'properties'
2771 dictionary attempts to specify any of these properties or a
2772 "creation", "creator", "activity" or "actor" property, a ValueError
2773 is raised.
2774 """
2775 if 'title' not in properties:
2776 properties['title'] = hyperdb.String(indexme='yes')
2777 if 'messages' not in properties:
2778 properties['messages'] = hyperdb.Multilink("msg")
2779 if 'files' not in properties:
2780 properties['files'] = hyperdb.Multilink("file")
2781 if 'nosy' not in properties:
2782 # note: journalling is turned off as it really just wastes
2783 # space. this behaviour may be overridden in an instance
2784 properties['nosy'] = hyperdb.Multilink("user", do_journal="no")
2785 if 'superseder' not in properties:
2786 properties['superseder'] = hyperdb.Multilink(classname)
2787 Class.__init__(self, db, classname, **properties)
2789 # vim: set et sts=4 sw=4 :