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 def sql_rollback(self):
1305 self.conn.rollback()
1307 def rollback(self):
1308 """ Reverse all actions from the current transaction.
1310 Undo all the changes made since the database was opened or the last
1311 commit() or rollback() was performed.
1312 """
1313 logging.getLogger('roundup.hyperdb').info('rollback')
1315 self.sql_rollback()
1317 # roll back "other" transaction stuff
1318 for method, args in self.transactions:
1319 # delete temporary files
1320 if method == self.doStoreFile:
1321 self.rollbackStoreFile(*args)
1322 self.transactions = []
1324 # clear the cache
1325 self.clearCache()
1327 def sql_close(self):
1328 logging.getLogger('roundup.hyperdb').info('close')
1329 self.conn.close()
1331 def close(self):
1332 """ Close off the connection.
1333 """
1334 self.indexer.close()
1335 self.sql_close()
1337 #
1338 # The base Class class
1339 #
1340 class Class(hyperdb.Class):
1341 """ The handle to a particular class of nodes in a hyperdatabase.
1343 All methods except __repr__ and getnode must be implemented by a
1344 concrete backend Class.
1345 """
1347 def schema(self):
1348 """ A dumpable version of the schema that we can store in the
1349 database
1350 """
1351 return (self.key, [(x, repr(y)) for x,y in self.properties.iteritems()])
1353 def enableJournalling(self):
1354 """Turn journalling on for this class
1355 """
1356 self.do_journal = 1
1358 def disableJournalling(self):
1359 """Turn journalling off for this class
1360 """
1361 self.do_journal = 0
1363 # Editing nodes:
1364 def create(self, **propvalues):
1365 """ Create a new node of this class and return its id.
1367 The keyword arguments in 'propvalues' map property names to values.
1369 The values of arguments must be acceptable for the types of their
1370 corresponding properties or a TypeError is raised.
1372 If this class has a key property, it must be present and its value
1373 must not collide with other key strings or a ValueError is raised.
1375 Any other properties on this class that are missing from the
1376 'propvalues' dictionary are set to None.
1378 If an id in a link or multilink property does not refer to a valid
1379 node, an IndexError is raised.
1380 """
1381 self.fireAuditors('create', None, propvalues)
1382 newid = self.create_inner(**propvalues)
1383 self.fireReactors('create', newid, None)
1384 return newid
1386 def create_inner(self, **propvalues):
1387 """ Called by create, in-between the audit and react calls.
1388 """
1389 if 'id' in propvalues:
1390 raise KeyError('"id" is reserved')
1392 if self.db.journaltag is None:
1393 raise DatabaseError(_('Database open read-only'))
1395 if ('creator' in propvalues or 'actor' in propvalues or
1396 'creation' in propvalues or 'activity' in propvalues):
1397 raise KeyError('"creator", "actor", "creation" and '
1398 '"activity" are reserved')
1400 # new node's id
1401 newid = self.db.newid(self.classname)
1403 # validate propvalues
1404 num_re = re.compile('^\d+$')
1405 for key, value in propvalues.iteritems():
1406 if key == self.key:
1407 try:
1408 self.lookup(value)
1409 except KeyError:
1410 pass
1411 else:
1412 raise ValueError('node with key "%s" exists'%value)
1414 # try to handle this property
1415 try:
1416 prop = self.properties[key]
1417 except KeyError:
1418 raise KeyError('"%s" has no property "%s"'%(self.classname,
1419 key))
1421 if value is not None and isinstance(prop, Link):
1422 if type(value) != type(''):
1423 raise ValueError('link value must be String')
1424 link_class = self.properties[key].classname
1425 # if it isn't a number, it's a key
1426 if not num_re.match(value):
1427 try:
1428 value = self.db.classes[link_class].lookup(value)
1429 except (TypeError, KeyError):
1430 raise IndexError('new property "%s": %s not a %s'%(
1431 key, value, link_class))
1432 elif not self.db.getclass(link_class).hasnode(value):
1433 raise IndexError('%s has no node %s'%(link_class,
1434 value))
1436 # save off the value
1437 propvalues[key] = value
1439 # register the link with the newly linked node
1440 if self.do_journal and self.properties[key].do_journal:
1441 self.db.addjournal(link_class, value, 'link',
1442 (self.classname, newid, key))
1444 elif isinstance(prop, Multilink):
1445 if value is None:
1446 value = []
1447 if not hasattr(value, '__iter__'):
1448 raise TypeError('new property "%s" not an iterable of ids'%key)
1449 # clean up and validate the list of links
1450 link_class = self.properties[key].classname
1451 l = []
1452 for entry in value:
1453 if type(entry) != type(''):
1454 raise ValueError('"%s" multilink value (%r) '
1455 'must contain Strings'%(key, value))
1456 # if it isn't a number, it's a key
1457 if not num_re.match(entry):
1458 try:
1459 entry = self.db.classes[link_class].lookup(entry)
1460 except (TypeError, KeyError):
1461 raise IndexError('new property "%s": %s not a %s'%(
1462 key, entry, self.properties[key].classname))
1463 l.append(entry)
1464 value = l
1465 propvalues[key] = value
1467 # handle additions
1468 for nodeid in value:
1469 if not self.db.getclass(link_class).hasnode(nodeid):
1470 raise IndexError('%s has no node %s'%(link_class,
1471 nodeid))
1472 # register the link with the newly linked node
1473 if self.do_journal and self.properties[key].do_journal:
1474 self.db.addjournal(link_class, nodeid, 'link',
1475 (self.classname, newid, key))
1477 elif isinstance(prop, String):
1478 if type(value) != type('') and type(value) != type(u''):
1479 raise TypeError('new property "%s" not a string'%key)
1480 if prop.indexme:
1481 self.db.indexer.add_text((self.classname, newid, key),
1482 value)
1484 elif isinstance(prop, Password):
1485 if not isinstance(value, password.Password):
1486 raise TypeError('new property "%s" not a Password'%key)
1488 elif isinstance(prop, Date):
1489 if value is not None and not isinstance(value, date.Date):
1490 raise TypeError('new property "%s" not a Date'%key)
1492 elif isinstance(prop, Interval):
1493 if value is not None and not isinstance(value, date.Interval):
1494 raise TypeError('new property "%s" not an Interval'%key)
1496 elif value is not None and isinstance(prop, Number):
1497 try:
1498 float(value)
1499 except ValueError:
1500 raise TypeError('new property "%s" not numeric'%key)
1502 elif value is not None and isinstance(prop, Boolean):
1503 try:
1504 int(value)
1505 except ValueError:
1506 raise TypeError('new property "%s" not boolean'%key)
1508 # make sure there's data where there needs to be
1509 for key, prop in self.properties.iteritems():
1510 if key in propvalues:
1511 continue
1512 if key == self.key:
1513 raise ValueError('key property "%s" is required'%key)
1514 if isinstance(prop, Multilink):
1515 propvalues[key] = []
1516 else:
1517 propvalues[key] = None
1519 # done
1520 self.db.addnode(self.classname, newid, propvalues)
1521 if self.do_journal:
1522 self.db.addjournal(self.classname, newid, ''"create", {})
1524 # XXX numeric ids
1525 return str(newid)
1527 def get(self, nodeid, propname, default=_marker, cache=1):
1528 """Get the value of a property on an existing node of this class.
1530 'nodeid' must be the id of an existing node of this class or an
1531 IndexError is raised. 'propname' must be the name of a property
1532 of this class or a KeyError is raised.
1534 'cache' exists for backwards compatibility, and is not used.
1535 """
1536 if propname == 'id':
1537 return nodeid
1539 # get the node's dict
1540 d = self.db.getnode(self.classname, nodeid)
1542 if propname == 'creation':
1543 if 'creation' in d:
1544 return d['creation']
1545 else:
1546 return date.Date()
1547 if propname == 'activity':
1548 if 'activity' in d:
1549 return d['activity']
1550 else:
1551 return date.Date()
1552 if propname == 'creator':
1553 if 'creator' in d:
1554 return d['creator']
1555 else:
1556 return self.db.getuid()
1557 if propname == 'actor':
1558 if 'actor' in d:
1559 return d['actor']
1560 else:
1561 return self.db.getuid()
1563 # get the property (raises KeyErorr if invalid)
1564 prop = self.properties[propname]
1566 # handle there being no value in the table for the property
1567 if propname not in d or d[propname] is None:
1568 if default is _marker:
1569 if isinstance(prop, Multilink):
1570 return []
1571 else:
1572 return None
1573 else:
1574 return default
1576 # don't pass our list to other code
1577 if isinstance(prop, Multilink):
1578 return d[propname][:]
1580 return d[propname]
1582 def set(self, nodeid, **propvalues):
1583 """Modify a property on an existing node of this class.
1585 'nodeid' must be the id of an existing node of this class or an
1586 IndexError is raised.
1588 Each key in 'propvalues' must be the name of a property of this
1589 class or a KeyError is raised.
1591 All values in 'propvalues' must be acceptable types for their
1592 corresponding properties or a TypeError is raised.
1594 If the value of the key property is set, it must not collide with
1595 other key strings or a ValueError is raised.
1597 If the value of a Link or Multilink property contains an invalid
1598 node id, a ValueError is raised.
1599 """
1600 self.fireAuditors('set', nodeid, propvalues)
1601 oldvalues = copy.deepcopy(self.db.getnode(self.classname, nodeid))
1602 propvalues = self.set_inner(nodeid, **propvalues)
1603 self.fireReactors('set', nodeid, oldvalues)
1604 return propvalues
1606 def set_inner(self, nodeid, **propvalues):
1607 """ Called by set, in-between the audit and react calls.
1608 """
1609 if not propvalues:
1610 return propvalues
1612 if ('creator' in propvalues or 'actor' in propvalues or
1613 'creation' in propvalues or 'activity' in propvalues):
1614 raise KeyError('"creator", "actor", "creation" and '
1615 '"activity" are reserved')
1617 if 'id' in propvalues:
1618 raise KeyError('"id" is reserved')
1620 if self.db.journaltag is None:
1621 raise DatabaseError(_('Database open read-only'))
1623 node = self.db.getnode(self.classname, nodeid)
1624 if self.is_retired(nodeid):
1625 raise IndexError('Requested item is retired')
1626 num_re = re.compile('^\d+$')
1628 # make a copy of the values dictionary - we'll modify the contents
1629 propvalues = propvalues.copy()
1631 # if the journal value is to be different, store it in here
1632 journalvalues = {}
1634 # remember the add/remove stuff for multilinks, making it easier
1635 # for the Database layer to do its stuff
1636 multilink_changes = {}
1638 for propname, value in list(propvalues.items()):
1639 # check to make sure we're not duplicating an existing key
1640 if propname == self.key and node[propname] != value:
1641 try:
1642 self.lookup(value)
1643 except KeyError:
1644 pass
1645 else:
1646 raise ValueError('node with key "%s" exists'%value)
1648 # this will raise the KeyError if the property isn't valid
1649 # ... we don't use getprops() here because we only care about
1650 # the writeable properties.
1651 try:
1652 prop = self.properties[propname]
1653 except KeyError:
1654 raise KeyError('"%s" has no property named "%s"'%(
1655 self.classname, propname))
1657 # if the value's the same as the existing value, no sense in
1658 # doing anything
1659 current = node.get(propname, None)
1660 if value == current:
1661 del propvalues[propname]
1662 continue
1663 journalvalues[propname] = current
1665 # do stuff based on the prop type
1666 if isinstance(prop, Link):
1667 link_class = prop.classname
1668 # if it isn't a number, it's a key
1669 if value is not None and not isinstance(value, type('')):
1670 raise ValueError('property "%s" link value be a string'%(
1671 propname))
1672 if isinstance(value, type('')) and not num_re.match(value):
1673 try:
1674 value = self.db.classes[link_class].lookup(value)
1675 except (TypeError, KeyError):
1676 raise IndexError('new property "%s": %s not a %s'%(
1677 propname, value, prop.classname))
1679 if (value is not None and
1680 not self.db.getclass(link_class).hasnode(value)):
1681 raise IndexError('%s has no node %s'%(link_class,
1682 value))
1684 if self.do_journal and prop.do_journal:
1685 # register the unlink with the old linked node
1686 if node[propname] is not None:
1687 self.db.addjournal(link_class, node[propname],
1688 ''"unlink", (self.classname, nodeid, propname))
1690 # register the link with the newly linked node
1691 if value is not None:
1692 self.db.addjournal(link_class, value, ''"link",
1693 (self.classname, nodeid, propname))
1695 elif isinstance(prop, Multilink):
1696 if value is None:
1697 value = []
1698 if not hasattr(value, '__iter__'):
1699 raise TypeError('new property "%s" not an iterable of'
1700 ' ids'%propname)
1701 link_class = self.properties[propname].classname
1702 l = []
1703 for entry in value:
1704 # if it isn't a number, it's a key
1705 if type(entry) != type(''):
1706 raise ValueError('new property "%s" link value '
1707 'must be a string'%propname)
1708 if not num_re.match(entry):
1709 try:
1710 entry = self.db.classes[link_class].lookup(entry)
1711 except (TypeError, KeyError):
1712 raise IndexError('new property "%s": %s not a %s'%(
1713 propname, entry,
1714 self.properties[propname].classname))
1715 l.append(entry)
1716 value = l
1717 propvalues[propname] = value
1719 # figure the journal entry for this property
1720 add = []
1721 remove = []
1723 # handle removals
1724 if propname in node:
1725 l = node[propname]
1726 else:
1727 l = []
1728 for id in l[:]:
1729 if id in value:
1730 continue
1731 # register the unlink with the old linked node
1732 if self.do_journal and self.properties[propname].do_journal:
1733 self.db.addjournal(link_class, id, 'unlink',
1734 (self.classname, nodeid, propname))
1735 l.remove(id)
1736 remove.append(id)
1738 # handle additions
1739 for id in value:
1740 if id in l:
1741 continue
1742 # We can safely check this condition after
1743 # checking that this is an addition to the
1744 # multilink since the condition was checked for
1745 # existing entries at the point they were added to
1746 # the multilink. Since the hasnode call will
1747 # result in a SQL query, it is more efficient to
1748 # avoid the check if possible.
1749 if not self.db.getclass(link_class).hasnode(id):
1750 raise IndexError('%s has no node %s'%(link_class,
1751 id))
1752 # register the link with the newly linked node
1753 if self.do_journal and self.properties[propname].do_journal:
1754 self.db.addjournal(link_class, id, 'link',
1755 (self.classname, nodeid, propname))
1756 l.append(id)
1757 add.append(id)
1759 # figure the journal entry
1760 l = []
1761 if add:
1762 l.append(('+', add))
1763 if remove:
1764 l.append(('-', remove))
1765 multilink_changes[propname] = (add, remove)
1766 if l:
1767 journalvalues[propname] = tuple(l)
1769 elif isinstance(prop, String):
1770 if value is not None and type(value) != type('') and type(value) != type(u''):
1771 raise TypeError('new property "%s" not a string'%propname)
1772 if prop.indexme:
1773 if value is None: value = ''
1774 self.db.indexer.add_text((self.classname, nodeid, propname),
1775 value)
1777 elif isinstance(prop, Password):
1778 if not isinstance(value, password.Password):
1779 raise TypeError('new property "%s" not a Password'%propname)
1780 propvalues[propname] = value
1782 elif value is not None and isinstance(prop, Date):
1783 if not isinstance(value, date.Date):
1784 raise TypeError('new property "%s" not a Date'% propname)
1785 propvalues[propname] = value
1787 elif value is not None and isinstance(prop, Interval):
1788 if not isinstance(value, date.Interval):
1789 raise TypeError('new property "%s" not an '
1790 'Interval'%propname)
1791 propvalues[propname] = value
1793 elif value is not None and isinstance(prop, Number):
1794 try:
1795 float(value)
1796 except ValueError:
1797 raise TypeError('new property "%s" not numeric'%propname)
1799 elif value is not None and isinstance(prop, Boolean):
1800 try:
1801 int(value)
1802 except ValueError:
1803 raise TypeError('new property "%s" not boolean'%propname)
1805 # nothing to do?
1806 if not propvalues:
1807 return propvalues
1809 # update the activity time
1810 propvalues['activity'] = date.Date()
1811 propvalues['actor'] = self.db.getuid()
1813 # do the set
1814 self.db.setnode(self.classname, nodeid, propvalues, multilink_changes)
1816 # remove the activity props now they're handled
1817 del propvalues['activity']
1818 del propvalues['actor']
1820 # journal the set
1821 if self.do_journal:
1822 self.db.addjournal(self.classname, nodeid, ''"set", journalvalues)
1824 return propvalues
1826 def retire(self, nodeid):
1827 """Retire a node.
1829 The properties on the node remain available from the get() method,
1830 and the node's id is never reused.
1832 Retired nodes are not returned by the find(), list(), or lookup()
1833 methods, and other nodes may reuse the values of their key properties.
1834 """
1835 if self.db.journaltag is None:
1836 raise DatabaseError(_('Database open read-only'))
1838 self.fireAuditors('retire', nodeid, None)
1840 # use the arg for __retired__ to cope with any odd database type
1841 # conversion (hello, sqlite)
1842 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
1843 self.db.arg, self.db.arg)
1844 self.db.sql(sql, (nodeid, nodeid))
1845 if self.do_journal:
1846 self.db.addjournal(self.classname, nodeid, ''"retired", None)
1848 self.fireReactors('retire', nodeid, None)
1850 def restore(self, nodeid):
1851 """Restore a retired node.
1853 Make node available for all operations like it was before retirement.
1854 """
1855 if self.db.journaltag is None:
1856 raise DatabaseError(_('Database open read-only'))
1858 node = self.db.getnode(self.classname, nodeid)
1859 # check if key property was overrided
1860 key = self.getkey()
1861 try:
1862 id = self.lookup(node[key])
1863 except KeyError:
1864 pass
1865 else:
1866 raise KeyError("Key property (%s) of retired node clashes "
1867 "with existing one (%s)" % (key, node[key]))
1869 self.fireAuditors('restore', nodeid, None)
1870 # use the arg for __retired__ to cope with any odd database type
1871 # conversion (hello, sqlite)
1872 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
1873 self.db.arg, self.db.arg)
1874 self.db.sql(sql, (0, nodeid))
1875 if self.do_journal:
1876 self.db.addjournal(self.classname, nodeid, ''"restored", None)
1878 self.fireReactors('restore', nodeid, None)
1880 def is_retired(self, nodeid):
1881 """Return true if the node is rerired
1882 """
1883 sql = 'select __retired__ from _%s where id=%s'%(self.classname,
1884 self.db.arg)
1885 self.db.sql(sql, (nodeid,))
1886 return int(self.db.sql_fetchone()[0]) > 0
1888 def destroy(self, nodeid):
1889 """Destroy a node.
1891 WARNING: this method should never be used except in extremely rare
1892 situations where there could never be links to the node being
1893 deleted
1895 WARNING: use retire() instead
1897 WARNING: the properties of this node will not be available ever again
1899 WARNING: really, use retire() instead
1901 Well, I think that's enough warnings. This method exists mostly to
1902 support the session storage of the cgi interface.
1904 The node is completely removed from the hyperdb, including all journal
1905 entries. It will no longer be available, and will generally break code
1906 if there are any references to the node.
1907 """
1908 if self.db.journaltag is None:
1909 raise DatabaseError(_('Database open read-only'))
1910 self.db.destroynode(self.classname, nodeid)
1912 def history(self, nodeid):
1913 """Retrieve the journal of edits on a particular node.
1915 'nodeid' must be the id of an existing node of this class or an
1916 IndexError is raised.
1918 The returned list contains tuples of the form
1920 (nodeid, date, tag, action, params)
1922 'date' is a Timestamp object specifying the time of the change and
1923 'tag' is the journaltag specified when the database was opened.
1924 """
1925 if not self.do_journal:
1926 raise ValueError('Journalling is disabled for this class')
1927 return self.db.getjournal(self.classname, nodeid)
1929 # Locating nodes:
1930 def hasnode(self, nodeid):
1931 """Determine if the given nodeid actually exists
1932 """
1933 return self.db.hasnode(self.classname, nodeid)
1935 def setkey(self, propname):
1936 """Select a String property of this class to be the key property.
1938 'propname' must be the name of a String property of this class or
1939 None, or a TypeError is raised. The values of the key property on
1940 all existing nodes must be unique or a ValueError is raised.
1941 """
1942 prop = self.getprops()[propname]
1943 if not isinstance(prop, String):
1944 raise TypeError('key properties must be String')
1945 self.key = propname
1947 def getkey(self):
1948 """Return the name of the key property for this class or None."""
1949 return self.key
1951 def lookup(self, keyvalue):
1952 """Locate a particular node by its key property and return its id.
1954 If this class has no key property, a TypeError is raised. If the
1955 'keyvalue' matches one of the values for the key property among
1956 the nodes in this class, the matching node's id is returned;
1957 otherwise a KeyError is raised.
1958 """
1959 if not self.key:
1960 raise TypeError('No key property set for class %s'%self.classname)
1962 # use the arg to handle any odd database type conversion (hello,
1963 # sqlite)
1964 sql = "select id from _%s where _%s=%s and __retired__=%s"%(
1965 self.classname, self.key, self.db.arg, self.db.arg)
1966 self.db.sql(sql, (str(keyvalue), 0))
1968 # see if there was a result that's not retired
1969 row = self.db.sql_fetchone()
1970 if not row:
1971 raise KeyError('No key (%s) value "%s" for "%s"'%(self.key,
1972 keyvalue, self.classname))
1974 # return the id
1975 # XXX numeric ids
1976 return str(row[0])
1978 def find(self, **propspec):
1979 """Get the ids of nodes in this class which link to the given nodes.
1981 'propspec' consists of keyword args propname=nodeid or
1982 propname={nodeid:1, }
1983 'propname' must be the name of a property in this class, or a
1984 KeyError is raised. That property must be a Link or
1985 Multilink property, or a TypeError is raised.
1987 Any node in this class whose 'propname' property links to any of
1988 the nodeids will be returned. Examples::
1990 db.issue.find(messages='1')
1991 db.issue.find(messages={'1':1,'3':1}, files={'7':1})
1992 """
1993 # shortcut
1994 if not propspec:
1995 return []
1997 # validate the args
1998 props = self.getprops()
1999 for propname, nodeids in propspec.iteritems():
2000 # check the prop is OK
2001 prop = props[propname]
2002 if not isinstance(prop, Link) and not isinstance(prop, Multilink):
2003 raise TypeError("'%s' not a Link/Multilink property"%propname)
2005 # first, links
2006 a = self.db.arg
2007 allvalues = ()
2008 sql = []
2009 where = []
2010 for prop, values in propspec.iteritems():
2011 if not isinstance(props[prop], hyperdb.Link):
2012 continue
2013 if type(values) is type({}) and len(values) == 1:
2014 values = list(values)[0]
2015 if type(values) is type(''):
2016 allvalues += (values,)
2017 where.append('_%s = %s'%(prop, a))
2018 elif values is None:
2019 where.append('_%s is NULL'%prop)
2020 else:
2021 values = list(values)
2022 s = ''
2023 if None in values:
2024 values.remove(None)
2025 s = '_%s is NULL or '%prop
2026 allvalues += tuple(values)
2027 s += '_%s in (%s)'%(prop, ','.join([a]*len(values)))
2028 where.append('(' + s +')')
2029 if where:
2030 allvalues = (0, ) + allvalues
2031 sql.append("""select id from _%s where __retired__=%s
2032 and %s"""%(self.classname, a, ' and '.join(where)))
2034 # now multilinks
2035 for prop, values in propspec.iteritems():
2036 if not isinstance(props[prop], hyperdb.Multilink):
2037 continue
2038 if not values:
2039 continue
2040 allvalues += (0, )
2041 if type(values) is type(''):
2042 allvalues += (values,)
2043 s = a
2044 else:
2045 allvalues += tuple(values)
2046 s = ','.join([a]*len(values))
2047 tn = '%s_%s'%(self.classname, prop)
2048 sql.append("""select id from _%s, %s where __retired__=%s
2049 and id = %s.nodeid and %s.linkid in (%s)"""%(self.classname,
2050 tn, a, tn, tn, s))
2052 if not sql:
2053 return []
2054 sql = ' union '.join(sql)
2055 self.db.sql(sql, allvalues)
2056 # XXX numeric ids
2057 l = [str(x[0]) for x in self.db.sql_fetchall()]
2058 return l
2060 def stringFind(self, **requirements):
2061 """Locate a particular node by matching a set of its String
2062 properties in a caseless search.
2064 If the property is not a String property, a TypeError is raised.
2066 The return is a list of the id of all nodes that match.
2067 """
2068 where = []
2069 args = []
2070 for propname in requirements:
2071 prop = self.properties[propname]
2072 if not isinstance(prop, String):
2073 raise TypeError("'%s' not a String property"%propname)
2074 where.append(propname)
2075 args.append(requirements[propname].lower())
2077 # generate the where clause
2078 s = ' and '.join(['lower(_%s)=%s'%(col, self.db.arg) for col in where])
2079 sql = 'select id from _%s where %s and __retired__=%s'%(
2080 self.classname, s, self.db.arg)
2081 args.append(0)
2082 self.db.sql(sql, tuple(args))
2083 # XXX numeric ids
2084 l = [str(x[0]) for x in self.db.sql_fetchall()]
2085 return l
2087 def list(self):
2088 """ Return a list of the ids of the active nodes in this class.
2089 """
2090 return self.getnodeids(retired=0)
2092 def getnodeids(self, retired=None):
2093 """ Retrieve all the ids of the nodes for a particular Class.
2095 Set retired=None to get all nodes. Otherwise it'll get all the
2096 retired or non-retired nodes, depending on the flag.
2097 """
2098 # flip the sense of the 'retired' flag if we don't want all of them
2099 if retired is not None:
2100 args = (0, )
2101 if retired:
2102 compare = '>'
2103 else:
2104 compare = '='
2105 sql = 'select id from _%s where __retired__%s%s'%(self.classname,
2106 compare, self.db.arg)
2107 else:
2108 args = ()
2109 sql = 'select id from _%s'%self.classname
2110 self.db.sql(sql, args)
2111 # XXX numeric ids
2112 ids = [str(x[0]) for x in self.db.cursor.fetchall()]
2113 return ids
2115 def _subselect(self, classname, multilink_table):
2116 """Create a subselect. This is factored out because some
2117 databases (hmm only one, so far) doesn't support subselects
2118 look for "I can't believe it's not a toy RDBMS" in the mysql
2119 backend.
2120 """
2121 return '_%s.id not in (select nodeid from %s)'%(classname,
2122 multilink_table)
2124 # Some DBs order NULL values last. Set this variable in the backend
2125 # for prepending an order by clause for each attribute that causes
2126 # correct sort order for NULLs. Examples:
2127 # order_by_null_values = '(%s is not NULL)'
2128 # order_by_null_values = 'notnull(%s)'
2129 # The format parameter is replaced with the attribute.
2130 order_by_null_values = None
2132 def filter(self, search_matches, filterspec, sort=[], group=[]):
2133 """Return a list of the ids of the active nodes in this class that
2134 match the 'filter' spec, sorted by the group spec and then the
2135 sort spec
2137 "filterspec" is {propname: value(s)}
2139 "sort" and "group" are [(dir, prop), ...] where dir is '+', '-'
2140 or None and prop is a prop name or None. Note that for
2141 backward-compatibility reasons a single (dir, prop) tuple is
2142 also allowed.
2144 "search_matches" is a container type or None
2146 The filter must match all properties specificed. If the property
2147 value to match is a list:
2149 1. String properties must match all elements in the list, and
2150 2. Other properties must match any of the elements in the list.
2151 """
2152 # we can't match anything if search_matches is empty
2153 if not search_matches and search_matches is not None:
2154 return []
2156 if __debug__:
2157 start_t = time.time()
2159 icn = self.classname
2161 # vars to hold the components of the SQL statement
2162 frum = [] # FROM clauses
2163 loj = [] # LEFT OUTER JOIN clauses
2164 where = [] # WHERE clauses
2165 args = [] # *any* positional arguments
2166 a = self.db.arg
2168 # figure the WHERE clause from the filterspec
2169 mlfilt = 0 # are we joining with Multilink tables?
2170 sortattr = self._sortattr (group = group, sort = sort)
2171 proptree = self._proptree(filterspec, sortattr)
2172 mlseen = 0
2173 for pt in reversed(proptree.sortattr):
2174 p = pt
2175 while p.parent:
2176 if isinstance (p.propclass, Multilink):
2177 mlseen = True
2178 if mlseen:
2179 p.sort_ids_needed = True
2180 p.tree_sort_done = False
2181 p = p.parent
2182 if not mlseen:
2183 pt.attr_sort_done = pt.tree_sort_done = True
2184 proptree.compute_sort_done()
2186 ordercols = []
2187 auxcols = {}
2188 mlsort = []
2189 rhsnum = 0
2190 for p in proptree:
2191 oc = None
2192 cn = p.classname
2193 ln = p.uniqname
2194 pln = p.parent.uniqname
2195 pcn = p.parent.classname
2196 k = p.name
2197 v = p.val
2198 propclass = p.propclass
2199 if p.sort_type > 0:
2200 oc = ac = '_%s._%s'%(pln, k)
2201 if isinstance(propclass, Multilink):
2202 if p.sort_type < 2:
2203 mlfilt = 1
2204 tn = '%s_%s'%(pcn, k)
2205 if v in ('-1', ['-1'], []):
2206 # only match rows that have count(linkid)=0 in the
2207 # corresponding multilink table)
2208 where.append(self._subselect(pcn, tn))
2209 else:
2210 frum.append(tn)
2211 where.append('_%s.id=%s.nodeid'%(pln,tn))
2212 if p.children:
2213 frum.append('_%s as _%s' % (cn, ln))
2214 where.append('%s.linkid=_%s.id'%(tn, ln))
2215 if p.has_values:
2216 if isinstance(v, type([])):
2217 s = ','.join([a for x in v])
2218 where.append('%s.linkid in (%s)'%(tn, s))
2219 args = args + v
2220 else:
2221 where.append('%s.linkid=%s'%(tn, a))
2222 args.append(v)
2223 if p.sort_type > 0:
2224 assert not p.attr_sort_done and not p.sort_ids_needed
2225 elif k == 'id':
2226 if p.sort_type < 2:
2227 if isinstance(v, type([])):
2228 s = ','.join([a for x in v])
2229 where.append('_%s.%s in (%s)'%(pln, k, s))
2230 args = args + v
2231 else:
2232 where.append('_%s.%s=%s'%(pln, k, a))
2233 args.append(v)
2234 if p.sort_type > 0:
2235 oc = ac = '_%s.id'%pln
2236 elif isinstance(propclass, String):
2237 if p.sort_type < 2:
2238 if not isinstance(v, type([])):
2239 v = [v]
2241 # Quote the bits in the string that need it and then embed
2242 # in a "substring" search. Note - need to quote the '%' so
2243 # they make it through the python layer happily
2244 v = ['%%'+self.db.sql_stringquote(s)+'%%' for s in v]
2246 # now add to the where clause
2247 where.append('('
2248 +' and '.join(["_%s._%s LIKE '%s'"%(pln, k, s) for s in v])
2249 +')')
2250 # note: args are embedded in the query string now
2251 if p.sort_type > 0:
2252 oc = ac = 'lower(_%s._%s)'%(pln, k)
2253 elif isinstance(propclass, Link):
2254 if p.sort_type < 2:
2255 if p.children:
2256 if p.sort_type == 0:
2257 frum.append('_%s as _%s' % (cn, ln))
2258 where.append('_%s._%s=_%s.id'%(pln, k, ln))
2259 if p.has_values:
2260 if isinstance(v, type([])):
2261 d = {}
2262 for entry in v:
2263 if entry == '-1':
2264 entry = None
2265 d[entry] = entry
2266 l = []
2267 if None in d or not d:
2268 if None in d: del d[None]
2269 l.append('_%s._%s is NULL'%(pln, k))
2270 if d:
2271 v = list(d)
2272 s = ','.join([a for x in v])
2273 l.append('(_%s._%s in (%s))'%(pln, k, s))
2274 args = args + v
2275 if l:
2276 where.append('(' + ' or '.join(l) +')')
2277 else:
2278 if v in ('-1', None):
2279 v = None
2280 where.append('_%s._%s is NULL'%(pln, k))
2281 else:
2282 where.append('_%s._%s=%s'%(pln, k, a))
2283 args.append(v)
2284 if p.sort_type > 0:
2285 lp = p.cls.labelprop()
2286 oc = ac = '_%s._%s'%(pln, k)
2287 if lp != 'id':
2288 if p.tree_sort_done and p.sort_type > 0:
2289 loj.append(
2290 'LEFT OUTER JOIN _%s as _%s on _%s._%s=_%s.id'%(
2291 cn, ln, pln, k, ln))
2292 oc = '_%s._%s'%(ln, lp)
2293 elif isinstance(propclass, Date) and p.sort_type < 2:
2294 dc = self.db.to_sql_value(hyperdb.Date)
2295 if isinstance(v, type([])):
2296 s = ','.join([a for x in v])
2297 where.append('_%s._%s in (%s)'%(pln, k, s))
2298 args = args + [dc(date.Date(x)) for x in v]
2299 else:
2300 try:
2301 # Try to filter on range of dates
2302 date_rng = propclass.range_from_raw(v, self.db)
2303 if date_rng.from_value:
2304 where.append('_%s._%s >= %s'%(pln, k, a))
2305 args.append(dc(date_rng.from_value))
2306 if date_rng.to_value:
2307 where.append('_%s._%s <= %s'%(pln, k, a))
2308 args.append(dc(date_rng.to_value))
2309 except ValueError:
2310 # If range creation fails - ignore that search parameter
2311 pass
2312 elif isinstance(propclass, Interval):
2313 # filter/sort using the __<prop>_int__ column
2314 if p.sort_type < 2:
2315 if isinstance(v, type([])):
2316 s = ','.join([a for x in v])
2317 where.append('_%s.__%s_int__ in (%s)'%(pln, k, s))
2318 args = args + [date.Interval(x).as_seconds() for x in v]
2319 else:
2320 try:
2321 # Try to filter on range of intervals
2322 date_rng = Range(v, date.Interval)
2323 if date_rng.from_value:
2324 where.append('_%s.__%s_int__ >= %s'%(pln, k, a))
2325 args.append(date_rng.from_value.as_seconds())
2326 if date_rng.to_value:
2327 where.append('_%s.__%s_int__ <= %s'%(pln, k, a))
2328 args.append(date_rng.to_value.as_seconds())
2329 except ValueError:
2330 # If range creation fails - ignore search parameter
2331 pass
2332 if p.sort_type > 0:
2333 oc = ac = '_%s.__%s_int__'%(pln,k)
2334 elif isinstance(propclass, Boolean) and p.sort_type < 2:
2335 if type(v) == type(""):
2336 v = v.split(',')
2337 if type(v) != type([]):
2338 v = [v]
2339 bv = []
2340 for val in v:
2341 if type(val) is type(''):
2342 bv.append(propclass.from_raw (val))
2343 else:
2344 bv.append(bool(val))
2345 if len(bv) == 1:
2346 where.append('_%s._%s=%s'%(pln, k, a))
2347 args = args + bv
2348 else:
2349 s = ','.join([a for x in v])
2350 where.append('_%s._%s in (%s)'%(pln, k, s))
2351 args = args + bv
2352 elif p.sort_type < 2:
2353 if isinstance(v, type([])):
2354 s = ','.join([a for x in v])
2355 where.append('_%s._%s in (%s)'%(pln, k, s))
2356 args = args + v
2357 else:
2358 where.append('_%s._%s=%s'%(pln, k, a))
2359 args.append(v)
2360 if oc:
2361 if p.sort_ids_needed:
2362 auxcols[ac] = p
2363 if p.tree_sort_done and p.sort_direction:
2364 # Don't select top-level id twice
2365 if p.name != 'id' or p.parent != proptree:
2366 ordercols.append(oc)
2367 desc = ['', ' desc'][p.sort_direction == '-']
2368 # Some SQL dbs sort NULL values last -- we want them first.
2369 if (self.order_by_null_values and p.name != 'id'):
2370 nv = self.order_by_null_values % oc
2371 ordercols.append(nv)
2372 p.orderby.append(nv + desc)
2373 p.orderby.append(oc + desc)
2375 props = self.getprops()
2377 # don't match retired nodes
2378 where.append('_%s.__retired__=0'%icn)
2380 # add results of full text search
2381 if search_matches is not None:
2382 s = ','.join([a for x in search_matches])
2383 where.append('_%s.id in (%s)'%(icn, s))
2384 args = args + [x for x in search_matches]
2386 # construct the SQL
2387 frum.append('_'+icn)
2388 frum = ','.join(frum)
2389 if where:
2390 where = ' where ' + (' and '.join(where))
2391 else:
2392 where = ''
2393 if mlfilt:
2394 # we're joining tables on the id, so we will get dupes if we
2395 # don't distinct()
2396 cols = ['distinct(_%s.id)'%icn]
2397 else:
2398 cols = ['_%s.id'%icn]
2399 if ordercols:
2400 cols = cols + ordercols
2401 order = []
2402 # keep correct sequence of order attributes.
2403 for sa in proptree.sortattr:
2404 if not sa.attr_sort_done:
2405 continue
2406 order.extend(sa.orderby)
2407 if order:
2408 order = ' order by %s'%(','.join(order))
2409 else:
2410 order = ''
2411 for o, p in auxcols.iteritems ():
2412 cols.append (o)
2413 p.auxcol = len (cols) - 1
2415 cols = ','.join(cols)
2416 loj = ' '.join(loj)
2417 sql = 'select %s from %s %s %s%s'%(cols, frum, loj, where, order)
2418 args = tuple(args)
2419 __traceback_info__ = (sql, args)
2420 self.db.sql(sql, args)
2421 l = self.db.sql_fetchall()
2423 # Compute values needed for sorting in proptree.sort
2424 for p in auxcols.itervalues():
2425 p.sort_ids = p.sort_result = [row[p.auxcol] for row in l]
2426 # return the IDs (the first column)
2427 # XXX numeric ids
2428 l = [str(row[0]) for row in l]
2429 l = proptree.sort (l)
2431 if __debug__:
2432 self.db.stats['filtering'] += (time.time() - start_t)
2433 return l
2435 def filter_sql(self, sql):
2436 """Return a list of the ids of the items in this class that match
2437 the SQL provided. The SQL is a complete "select" statement.
2439 The SQL select must include the item id as the first column.
2441 This function DOES NOT filter out retired items, add on a where
2442 clause "__retired__=0" if you don't want retired nodes.
2443 """
2444 if __debug__:
2445 start_t = time.time()
2447 self.db.sql(sql)
2448 l = self.db.sql_fetchall()
2450 if __debug__:
2451 self.db.stats['filtering'] += (time.time() - start_t)
2452 return l
2454 def count(self):
2455 """Get the number of nodes in this class.
2457 If the returned integer is 'numnodes', the ids of all the nodes
2458 in this class run from 1 to numnodes, and numnodes+1 will be the
2459 id of the next node to be created in this class.
2460 """
2461 return self.db.countnodes(self.classname)
2463 # Manipulating properties:
2464 def getprops(self, protected=1):
2465 """Return a dictionary mapping property names to property objects.
2466 If the "protected" flag is true, we include protected properties -
2467 those which may not be modified.
2468 """
2469 d = self.properties.copy()
2470 if protected:
2471 d['id'] = String()
2472 d['creation'] = hyperdb.Date()
2473 d['activity'] = hyperdb.Date()
2474 d['creator'] = hyperdb.Link('user')
2475 d['actor'] = hyperdb.Link('user')
2476 return d
2478 def addprop(self, **properties):
2479 """Add properties to this class.
2481 The keyword arguments in 'properties' must map names to property
2482 objects, or a TypeError is raised. None of the keys in 'properties'
2483 may collide with the names of existing properties, or a ValueError
2484 is raised before any properties have been added.
2485 """
2486 for key in properties:
2487 if key in self.properties:
2488 raise ValueError(key)
2489 self.properties.update(properties)
2491 def index(self, nodeid):
2492 """Add (or refresh) the node to search indexes
2493 """
2494 # find all the String properties that have indexme
2495 for prop, propclass in self.getprops().iteritems():
2496 if isinstance(propclass, String) and propclass.indexme:
2497 self.db.indexer.add_text((self.classname, nodeid, prop),
2498 str(self.get(nodeid, prop)))
2500 #
2501 # import / export support
2502 #
2503 def export_list(self, propnames, nodeid):
2504 """ Export a node - generate a list of CSV-able data in the order
2505 specified by propnames for the given node.
2506 """
2507 properties = self.getprops()
2508 l = []
2509 for prop in propnames:
2510 proptype = properties[prop]
2511 value = self.get(nodeid, prop)
2512 # "marshal" data where needed
2513 if value is None:
2514 pass
2515 elif isinstance(proptype, hyperdb.Date):
2516 value = value.get_tuple()
2517 elif isinstance(proptype, hyperdb.Interval):
2518 value = value.get_tuple()
2519 elif isinstance(proptype, hyperdb.Password):
2520 value = str(value)
2521 l.append(repr(value))
2522 l.append(repr(self.is_retired(nodeid)))
2523 return l
2525 def import_list(self, propnames, proplist):
2526 """ Import a node - all information including "id" is present and
2527 should not be sanity checked. Triggers are not triggered. The
2528 journal should be initialised using the "creator" and "created"
2529 information.
2531 Return the nodeid of the node imported.
2532 """
2533 if self.db.journaltag is None:
2534 raise DatabaseError(_('Database open read-only'))
2535 properties = self.getprops()
2537 # make the new node's property map
2538 d = {}
2539 retire = 0
2540 if not "id" in propnames:
2541 newid = self.db.newid(self.classname)
2542 else:
2543 newid = eval(proplist[propnames.index("id")])
2544 for i in range(len(propnames)):
2545 # Use eval to reverse the repr() used to output the CSV
2546 value = eval(proplist[i])
2548 # Figure the property for this column
2549 propname = propnames[i]
2551 # "unmarshal" where necessary
2552 if propname == 'id':
2553 continue
2554 elif propname == 'is retired':
2555 # is the item retired?
2556 if int(value):
2557 retire = 1
2558 continue
2559 elif value is None:
2560 d[propname] = None
2561 continue
2563 prop = properties[propname]
2564 if value is None:
2565 # don't set Nones
2566 continue
2567 elif isinstance(prop, hyperdb.Date):
2568 value = date.Date(value)
2569 elif isinstance(prop, hyperdb.Interval):
2570 value = date.Interval(value)
2571 elif isinstance(prop, hyperdb.Password):
2572 pwd = password.Password()
2573 pwd.unpack(value)
2574 value = pwd
2575 elif isinstance(prop, String):
2576 if isinstance(value, unicode):
2577 value = value.encode('utf8')
2578 if not isinstance(value, str):
2579 raise TypeError('new property "%(propname)s" not a '
2580 'string: %(value)r'%locals())
2581 if prop.indexme:
2582 self.db.indexer.add_text((self.classname, newid, propname),
2583 value)
2584 d[propname] = value
2586 # get a new id if necessary
2587 if newid is None:
2588 newid = self.db.newid(self.classname)
2590 # insert new node or update existing?
2591 if not self.hasnode(newid):
2592 self.db.addnode(self.classname, newid, d) # insert
2593 else:
2594 self.db.setnode(self.classname, newid, d) # update
2596 # retire?
2597 if retire:
2598 # use the arg for __retired__ to cope with any odd database type
2599 # conversion (hello, sqlite)
2600 sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
2601 self.db.arg, self.db.arg)
2602 self.db.sql(sql, (newid, newid))
2603 return newid
2605 def export_journals(self):
2606 """Export a class's journal - generate a list of lists of
2607 CSV-able data:
2609 nodeid, date, user, action, params
2611 No heading here - the columns are fixed.
2612 """
2613 properties = self.getprops()
2614 r = []
2615 for nodeid in self.getnodeids():
2616 for nodeid, date, user, action, params in self.history(nodeid):
2617 date = date.get_tuple()
2618 if action == 'set':
2619 export_data = {}
2620 for propname, value in params.iteritems():
2621 if propname not in properties:
2622 # property no longer in the schema
2623 continue
2625 prop = properties[propname]
2626 # make sure the params are eval()'able
2627 if value is None:
2628 pass
2629 elif isinstance(prop, Date):
2630 value = value.get_tuple()
2631 elif isinstance(prop, Interval):
2632 value = value.get_tuple()
2633 elif isinstance(prop, Password):
2634 value = str(value)
2635 export_data[propname] = value
2636 params = export_data
2637 elif action == 'create' and params:
2638 # old tracker with data stored in the create!
2639 params = {}
2640 l = [nodeid, date, user, action, params]
2641 r.append(list(map(repr, l)))
2642 return r
2644 def import_journals(self, entries):
2645 """Import a class's journal.
2647 Uses setjournal() to set the journal for each item."""
2648 properties = self.getprops()
2649 d = {}
2650 for l in entries:
2651 nodeid, jdate, user, action, params = map(eval, l)
2652 r = d.setdefault(nodeid, [])
2653 if action == 'set':
2654 for propname, value in params.iteritems():
2655 prop = properties[propname]
2656 if value is None:
2657 pass
2658 elif isinstance(prop, Date):
2659 value = date.Date(value)
2660 elif isinstance(prop, Interval):
2661 value = date.Interval(value)
2662 elif isinstance(prop, Password):
2663 pwd = password.Password()
2664 pwd.unpack(value)
2665 value = pwd
2666 params[propname] = value
2667 elif action == 'create' and params:
2668 # old tracker with data stored in the create!
2669 params = {}
2670 r.append((nodeid, date.Date(jdate), user, action, params))
2672 for nodeid, l in d.iteritems():
2673 self.db.setjournal(self.classname, nodeid, l)
2675 class FileClass(hyperdb.FileClass, Class):
2676 """This class defines a large chunk of data. To support this, it has a
2677 mandatory String property "content" which is typically saved off
2678 externally to the hyperdb.
2680 The default MIME type of this data is defined by the
2681 "default_mime_type" class attribute, which may be overridden by each
2682 node if the class defines a "type" String property.
2683 """
2684 def __init__(self, db, classname, **properties):
2685 """The newly-created class automatically includes the "content"
2686 and "type" properties.
2687 """
2688 if 'content' not in properties:
2689 properties['content'] = hyperdb.String(indexme='yes')
2690 if 'type' not in properties:
2691 properties['type'] = hyperdb.String()
2692 Class.__init__(self, db, classname, **properties)
2694 def create(self, **propvalues):
2695 """ snaffle the file propvalue and store in a file
2696 """
2697 # we need to fire the auditors now, or the content property won't
2698 # be in propvalues for the auditors to play with
2699 self.fireAuditors('create', None, propvalues)
2701 # now remove the content property so it's not stored in the db
2702 content = propvalues['content']
2703 del propvalues['content']
2705 # do the database create
2706 newid = self.create_inner(**propvalues)
2708 # figure the mime type
2709 mime_type = propvalues.get('type', self.default_mime_type)
2711 # and index!
2712 if self.properties['content'].indexme:
2713 self.db.indexer.add_text((self.classname, newid, 'content'),
2714 content, mime_type)
2716 # store off the content as a file
2717 self.db.storefile(self.classname, newid, None, content)
2719 # fire reactors
2720 self.fireReactors('create', newid, None)
2722 return newid
2724 def get(self, nodeid, propname, default=_marker, cache=1):
2725 """ Trap the content propname and get it from the file
2727 'cache' exists for backwards compatibility, and is not used.
2728 """
2729 poss_msg = 'Possibly a access right configuration problem.'
2730 if propname == 'content':
2731 try:
2732 return self.db.getfile(self.classname, nodeid, None)
2733 except IOError, strerror:
2734 # BUG: by catching this we donot see an error in the log.
2735 return 'ERROR reading file: %s%s\n%s\n%s'%(
2736 self.classname, nodeid, poss_msg, strerror)
2737 if default is not _marker:
2738 return Class.get(self, nodeid, propname, default)
2739 else:
2740 return Class.get(self, nodeid, propname)
2742 def set(self, itemid, **propvalues):
2743 """ Snarf the "content" propvalue and update it in a file
2744 """
2745 self.fireAuditors('set', itemid, propvalues)
2746 oldvalues = copy.deepcopy(self.db.getnode(self.classname, itemid))
2748 # now remove the content property so it's not stored in the db
2749 content = None
2750 if 'content' in propvalues:
2751 content = propvalues['content']
2752 del propvalues['content']
2754 # do the database create
2755 propvalues = self.set_inner(itemid, **propvalues)
2757 # do content?
2758 if content:
2759 # store and possibly index
2760 self.db.storefile(self.classname, itemid, None, content)
2761 if self.properties['content'].indexme:
2762 mime_type = self.get(itemid, 'type', self.default_mime_type)
2763 self.db.indexer.add_text((self.classname, itemid, 'content'),
2764 content, mime_type)
2765 propvalues['content'] = content
2767 # fire reactors
2768 self.fireReactors('set', itemid, oldvalues)
2769 return propvalues
2771 def index(self, nodeid):
2772 """ Add (or refresh) the node to search indexes.
2774 Use the content-type property for the content property.
2775 """
2776 # find all the String properties that have indexme
2777 for prop, propclass in self.getprops().iteritems():
2778 if prop == 'content' and propclass.indexme:
2779 mime_type = self.get(nodeid, 'type', self.default_mime_type)
2780 self.db.indexer.add_text((self.classname, nodeid, 'content'),
2781 str(self.get(nodeid, 'content')), mime_type)
2782 elif isinstance(propclass, hyperdb.String) and propclass.indexme:
2783 # index them under (classname, nodeid, property)
2784 try:
2785 value = str(self.get(nodeid, prop))
2786 except IndexError:
2787 # node has been destroyed
2788 continue
2789 self.db.indexer.add_text((self.classname, nodeid, prop), value)
2791 # XXX deviation from spec - was called ItemClass
2792 class IssueClass(Class, roundupdb.IssueClass):
2793 # Overridden methods:
2794 def __init__(self, db, classname, **properties):
2795 """The newly-created class automatically includes the "messages",
2796 "files", "nosy", and "superseder" properties. If the 'properties'
2797 dictionary attempts to specify any of these properties or a
2798 "creation", "creator", "activity" or "actor" property, a ValueError
2799 is raised.
2800 """
2801 if 'title' not in properties:
2802 properties['title'] = hyperdb.String(indexme='yes')
2803 if 'messages' not in properties:
2804 properties['messages'] = hyperdb.Multilink("msg")
2805 if 'files' not in properties:
2806 properties['files'] = hyperdb.Multilink("file")
2807 if 'nosy' not in properties:
2808 # note: journalling is turned off as it really just wastes
2809 # space. this behaviour may be overridden in an instance
2810 properties['nosy'] = hyperdb.Multilink("user", do_journal="no")
2811 if 'superseder' not in properties:
2812 properties['superseder'] = hyperdb.Multilink(classname)
2813 Class.__init__(self, db, classname, **properties)
2815 # vim: set et sts=4 sw=4 :