![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
index e7d141c64c516dbfb1b49cb5c05b374140bdbf57..2894cc63e35a8374b17ed070bd0fd0f6955353a9 100644 (file)
-# $Id: rdbms_common.py,v 1.75 2004-02-11 23:55:09 richard Exp $
+# $Id: rdbms_common.py,v 1.81 2004-03-18 01:58:45 richard Exp $
''' Relational database (SQL) backend common code.
Basics:
probably a bit of work to be done if a database is used that actually
honors column typing, since the initial databases don't (sqlite stores
everything as a string.)
+
+The schema of the hyperdb being mapped to the database is stored in the
+database itself as a repr()'ed dictionary of information about each Class
+that maps to a table. If that information differs from the hyperdb schema,
+then we update it. We also store in the schema dict a version which
+allows us to upgrade the database schema when necessary. See upgrade_db().
'''
__docformat__ = 'restructuredtext'
# support
from blobfiles import FileStorage
from roundup.indexer import Indexer
-from sessions import Sessions, OneTimeKeys
+from sessions_rdbms import Sessions, OneTimeKeys
from roundup.date import Range
# number of rows to keep in memory
self.dir = config.DATABASE
self.classes = {}
self.indexer = Indexer(self.dir)
- self.sessions = Sessions(self.config)
- self.otks = OneTimeKeys(self.config)
self.security = security.Security(self)
# additional transaction support for external files and the like
self.lockfile = None
# open a connection to the database, creating the "conn" attribute
- self.sql_open_connection()
+ self.open_connection()
def clearCache(self):
self.cache = {}
self.cache_lru = []
- def sql_open_connection(self):
- ''' Open a connection to the database, creating it if necessary
+ def getSessionManager(self):
+ return Sessions(self)
+
+ def getOTKManager(self):
+ return OneTimeKeys(self)
+
+ def open_connection(self):
+ ''' Open a connection to the database, creating it if necessary.
+
+ Must call self.load_dbschema()
'''
raise NotImplemented
'''
return re.sub("'", "''", str(value))
- def save_dbschema(self, schema):
- ''' Save the schema definition that the database currently implements
- '''
- s = repr(self.database_schema)
- self.sql('insert into schema values (%s)', (s,))
+ def init_dbschema(self):
+ self.database_schema = {
+ 'version': self.current_db_version,
+ 'tables': {}
+ }
def load_dbschema(self):
''' Load the schema definition that the database currently implements
'''
self.cursor.execute('select schema from schema')
- return eval(self.cursor.fetchone()[0])
+ schema = self.cursor.fetchone()
+ if schema:
+ self.database_schema = eval(schema[0])
+ else:
+ self.database_schema = {}
+
+ def save_dbschema(self, schema):
+ ''' Save the schema definition that the database currently implements
+ '''
+ s = repr(self.database_schema)
+ self.sql('insert into schema values (%s)', (s,))
def post_init(self):
''' Called once the schema initialisation has finished.
We should now confirm that the schema defined by our "classes"
attribute actually matches the schema in the database.
'''
+ save = self.upgrade_db()
+
# now detect changes in the schema
- save = 0
+ tables = self.database_schema['tables']
for classname, spec in self.classes.items():
- if self.database_schema.has_key(classname):
- dbspec = self.database_schema[classname]
+ if tables.has_key(classname):
+ dbspec = tables[classname]
if self.update_class(spec, dbspec):
- self.database_schema[classname] = spec.schema()
+ tables[classname] = spec.schema()
save = 1
else:
self.create_class(spec)
- self.database_schema[classname] = spec.schema()
+ tables[classname] = spec.schema()
save = 1
- for classname, spec in self.database_schema.items():
+ for classname, spec in tables.items():
if not self.classes.has_key(classname):
- self.drop_class(classname, spec)
- del self.database_schema[classname]
+ self.drop_class(classname, tables[classname])
+ del tables[classname]
save = 1
# update the database version of the schema
# commit
self.conn.commit()
+ # update this number when we need to make changes to the SQL structure
+ # of the backen database
+ current_db_version = 2
+ def upgrade_db(self):
+ ''' Update the SQL database to reflect changes in the backend code.
+
+ Return boolean whether we need to save the schema.
+ '''
+ version = self.database_schema.get('version', 1)
+ if version == self.current_db_version:
+ # nothing to do
+ return 0
+
+ if version == 1:
+ # version 1 doesn't have the OTK, session and indexing in the
+ # database
+ self.create_version_2_tables()
+ # version 1 also didn't have the actor column
+ self.add_actor_column()
+
+ self.database_schema['version'] = self.current_db_version
+ return 1
+
+
def refresh_database(self):
self.post_init()
"properties" is a list of (name, prop) where prop may be an
instance of a hyperdb "type" _or_ a string repr of that type.
'''
- cols = ['_activity', '_creator', '_creation']
+ cols = ['_actor', '_activity', '_creator', '_creation']
mls = []
# add the multilinks separately
for col, prop in properties:
if __debug__:
print >>hyperdb.DEBUG, 'update_class FIRING'
+ # detect key prop change for potential index change
+ keyprop_changes = 0
+ if new_spec[0] != old_spec[0]:
+ keyprop_changes = {'remove': old_spec[0], 'add': new_spec[0]}
+
# detect multilinks that have been removed, and drop their table
old_has = {}
- for name,prop in old_spec[1]:
+ for name, prop in old_spec[1]:
old_has[name] = 1
- if new_has(name) or not isinstance(prop, Multilink):
+ if new_has(name):
continue
- # it's a multilink, and it's been removed - drop the old
- # table. First drop indexes.
- self.drop_multilink_table_indexes(spec.classname, ml)
- sql = 'drop table %s_%s'%(spec.classname, prop)
+
+ if isinstance(prop, Multilink):
+ # first drop indexes.
+ self.drop_multilink_table_indexes(spec.classname, ml)
+
+ # now the multilink table itself
+ sql = 'drop table %s_%s'%(spec.classname, prop)
+ else:
+ # if this is the key prop, drop the index first
+ if old_spec[0] == prop:
+ self.drop_class_table_key_index(spec.classname, prop)
+ del keyprop_changes['remove']
+
+ # drop the column
+ sql = 'alter table _%s drop column _%s'%(spec.classname, prop)
+
if __debug__:
print >>hyperdb.DEBUG, 'update_class', (self, sql)
self.cursor.execute(sql)
old_has = old_has.has_key
- # now figure how we populate the new table
- fetch = ['_activity', '_creation', '_creator']
- properties = spec.getprops()
- for propname,x in new_spec[1]:
- prop = properties[propname]
- if isinstance(prop, Multilink):
- if force or not old_has(propname):
- # we need to create the new table
- self.create_multilink_table(spec, propname)
- elif old_has(propname):
- # we copy this col over from the old table
- fetch.append('_'+propname)
-
- # select the data out of the old table
- fetch.append('id')
- fetch.append('__retired__')
- fetchcols = ','.join(fetch)
- cn = spec.classname
- sql = 'select %s from _%s'%(fetchcols, cn)
- if __debug__:
- print >>hyperdb.DEBUG, 'update_class', (self, sql)
- self.cursor.execute(sql)
- olddata = self.cursor.fetchall()
-
- # TODO: update all the other index dropping code
- self.drop_class_table_indexes(cn, old_spec[0])
+ # if we didn't remove the key prop just then, but the key prop has
+ # changed, we still need to remove the old index
+ if keyprop_changes.has_key('remove'):
+ self.drop_class_table_key_index(spec.classname,
+ keyprop_changes['remove'])
- # drop the old table
- self.cursor.execute('drop table _%s'%cn)
+ # add new columns
+ for propname, x in new_spec[1]:
+ if old_has(propname):
+ continue
+ sql = 'alter table _%s add column _%s varchar(255)'%(
+ spec.classname, propname)
+ if __debug__:
+ print >>hyperdb.DEBUG, 'update_class', (self, sql)
+ self.cursor.execute(sql)
- # create the new table
- self.create_class_table(spec)
+ # if the new column is a key prop, we need an index!
+ if new_spec[0] == propname:
+ self.create_class_table_key_index(spec.classname, propname)
+ del keyprop_changes['add']
- if olddata:
- # do the insert
- args = ','.join([self.arg for x in fetch])
- sql = 'insert into _%s (%s) values (%s)'%(cn, fetchcols, args)
- if __debug__:
- print >>hyperdb.DEBUG, 'update_class', (self, sql, olddata[0])
- for entry in olddata:
- self.cursor.execute(sql, tuple(entry))
+ # if we didn't add the key prop just then, but the key prop has
+ # changed, we still need to add the new index
+ if keyprop_changes.has_key('add'):
+ self.create_class_table_key_index(spec.classname,
+ keyprop_changes['add'])
return 1
# drop the old table indexes first
l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn]
if key:
- # key prop too?
l.append('_%s_%s_idx'%(cn, key))
- # TODO: update all the other index dropping code
table_name = '_%s'%cn
for index_name in l:
if not self.sql_index_exists(table_name, index_name):
print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
self.cursor.execute(index_sql)
+ def create_class_table_key_index(self, cn, key):
+ ''' create the class table for the given spec
+ '''
+ if __debug__:
+ print >>hyperdb.DEBUG, 'update_class setting keyprop %r'% \
+ key
+ index_sql3 = 'create index _%s_%s_idx on _%s(_%s)'%(cn, key,
+ cn, key)
+ if __debug__:
+ print >>hyperdb.DEBUG, 'create_index', (self, index_sql3)
+ self.cursor.execute(index_sql3)
+
+ def drop_class_table_key_index(self, cn, key):
+ table_name = '_%s'%cn
+ index_name = '_%s_%s_idx'%(cn, key)
+ if not self.sql_index_exists(table_name, index_name):
+ return
+ sql = 'drop index '+index_name
+ if __debug__:
+ print >>hyperdb.DEBUG, 'drop_index', (self, sql)
+ self.cursor.execute(sql)
+
def create_journal_table(self, spec):
''' create the journal table for a class given the spec and
already-determined cols
def create_journal_table_indexes(self, spec):
# index on nodeid
- index_sql = 'create index %s_journ_idx on %s__journal(nodeid)'%(
+ sql = 'create index %s_journ_idx on %s__journal(nodeid)'%(
spec.classname, spec.classname)
if __debug__:
- print >>hyperdb.DEBUG, 'create_index', (self, index_sql)
- self.cursor.execute(index_sql)
+ print >>hyperdb.DEBUG, 'create_index', (self, sql)
+ self.cursor.execute(sql)
def drop_journal_table_indexes(self, classname):
index_name = '%s_journ_idx'%classname
raise ValueError, cn
self.classes[cn] = cl
+ # add default Edit and View permissions
+ self.security.addPermission(name="Edit", klass=cn,
+ description="User is allowed to edit "+cn)
+ self.security.addPermission(name="View", klass=cn,
+ description="User is allowed to access "+cn)
+
def getclasses(self):
''' Return a list of the names of all existing classes.
'''
# calling code's node assumptions)
node = node.copy()
node['creation'] = node['activity'] = date.Date()
- node['creator'] = self.getuid()
+ node['actor'] = node['creator'] = self.getuid()
# default the non-multilink columns
for col, prop in cl.properties.items():
# add the special props
values = values.copy()
values['activity'] = date.Date()
+ values['actor'] = self.getuid()
# make db-friendly
values = self.serialise(classname, values)
def sql_commit(self):
''' Actually commit to the database.
'''
+ if __debug__:
+ print >>hyperdb.DEBUG, '+++ commit database connection +++'
self.conn.commit()
def commit(self):
return (classname, nodeid)
def sql_close(self):
+ if __debug__:
+ print >>hyperdb.DEBUG, '+++ close database connection +++'
self.conn.close()
def close(self):
''' Close off the connection.
'''
self.sql_close()
- if self.lockfile is not None:
- locking.release_lock(self.lockfile)
- if self.lockfile is not None:
- self.lockfile.close()
- self.lockfile = None
#
# The base Class class
or a ValueError is raised. The keyword arguments in 'properties'
must map names to property objects, or a TypeError is raised.
'''
- if (properties.has_key('creation') or properties.has_key('activity')
- or properties.has_key('creator')):
- raise ValueError, '"creation", "activity" and "creator" are '\
- 'reserved'
+ for name in 'creation activity creator actor'.split():
+ if properties.has_key(name):
+ raise ValueError, '"creation", "activity", "creator" and '\
+ '"actor" are reserved'
self.classname = classname
self.properties = properties
if self.db.journaltag is None:
raise DatabaseError, 'Database open read-only'
- if propvalues.has_key('creation') or propvalues.has_key('activity'):
- raise KeyError, '"creation" and "activity" are reserved'
+ if propvalues.has_key('creator') or propvalues.has_key('actor') or \
+ propvalues.has_key('creation') or propvalues.has_key('activity'):
+ raise KeyError, '"creator", "actor", "creation" and '\
+ '"activity" are reserved'
# new node's id
newid = self.db.newid(self.classname)
creation = None
if d.has_key('activity'):
del d['activity']
+ if d.has_key('actor'):
+ del d['actor']
self.db.addjournal(self.classname, newid, 'create', {}, creator,
creation)
return newid
return d['creator']
else:
return self.db.getuid()
+ if propname == 'actor':
+ if d.has_key('actor'):
+ return d['actor']
+ else:
+ return self.db.getuid()
# get the property (raises KeyErorr if invalid)
prop = self.properties[propname]
if not propvalues:
return propvalues
- if propvalues.has_key('creation') or propvalues.has_key('activity'):
- raise KeyError, '"creation" and "activity" are reserved'
+ if propvalues.has_key('creation') or propvalues.has_key('creator') or \
+ propvalues.has_key('actor') or propvalues.has_key('activity'):
+ raise KeyError, '"creation", "creator", "actor" and '\
+ '"activity" are reserved'
if propvalues.has_key('id'):
raise KeyError, '"id" is reserved'
d['creation'] = hyperdb.Date()
d['activity'] = hyperdb.Date()
d['creator'] = hyperdb.Link('user')
+ d['actor'] = hyperdb.Link('user')
return d
def addprop(self, **properties):
'''The newly-created class automatically includes the "messages",
"files", "nosy", and "superseder" properties. If the 'properties'
dictionary attempts to specify any of these properties or a
- "creation" or "activity" property, a ValueError is raised.
+ "creation", "creator", "activity" or "actor" property, a ValueError
+ is raised.
'''
if not properties.has_key('title'):
properties['title'] = hyperdb.String(indexme='yes')