code-2a6c3eb4e059/roundup/backends/__init__.py0000644000000000000000000000564014571501054017316 0ustar 00000000000000# # Copyright (c) 2001 Bizar Software Pty Ltd (http://www.bizarsoftware.com.au/) # This module is free software, and you may redistribute it and/or modify # under the same terms as Python, so long as this copyright message and # disclaimer are retained in their original form. # # IN NO EVENT SHALL BIZAR SOFTWARE PTY LTD BE LIABLE TO ANY PARTY FOR # DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING # OUT OF THE USE OF THIS CODE, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE # POSSIBILITY OF SUCH DAMAGE. # # BIZAR SOFTWARE PTY LTD SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, # BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS # FOR A PARTICULAR PURPOSE. THE CODE PROVIDED HEREUNDER IS ON AN "AS IS" # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. '''Container for the hyperdb storage backend implementations. ''' __docformat__ = 'restructuredtext' import sys # These names are used to suppress import errors. # If get_backend raises an ImportError with appropriate # module name, have_backend quietly returns False. # Otherwise the error is reraised. _modules = { 'mysql': ('MySQLdb',), 'postgresql': ('psycopg2',), 'sqlite': ('pysqlite', 'pysqlite2', 'sqlite3', '_sqlite3', 'sqlite'), } def get_backend(name): '''Get a specific backend by name.''' vars = globals() # if requested backend has been imported yet, return current instance if name in vars: return vars[name] # import the backend module module_name = 'back_%s' % name module = __import__(module_name, vars, level=1) vars[name] = module return module def have_backend(name): '''Is backend "name" available?''' try: get_backend(name) return 1 except ImportError as e: if hasattr(e, 'name'): modname = e.name else: modname = e.args[0][16:] if e.args[0].startswith('No module named ') else None # It's always ok if memorydb is not found if modname.endswith('back_memorydb'): return 0 if modname and (modname in _modules.get(name, (name,))): return 0 raise return 0 def list_backends(): '''List all available backend names. This function has side-effect of registering backward-compatible globals for all available backends. Note: Since memorydb does not live in the backends directory, it will never be found in the default setup. It *can* be enabled by preloading test/memorydb and injecting into roundup.backends. So the normal user can never configure memorydb but it makes using the tests easier because we do not need to monkey-patch list_backends. ''' l = [] for name in 'anydbm', 'mysql', 'sqlite', 'postgresql', 'memorydb': if have_backend(name): l.append(name) return l # vim: set filetype=python sts=4 sw=4 et si : code-2a6c3eb4e059/roundup/backends/back_anydbm.py0000644000000000000000000026477014571501054020024 0ustar 00000000000000# # Copyright (c) 2001 Bizar Software Pty Ltd (http://www.bizarsoftware.com.au/) # This module is free software, and you may redistribute it and/or modify # under the same terms as Python, so long as this copyright message and # disclaimer are retained in their original form. # # IN NO EVENT SHALL BIZAR SOFTWARE PTY LTD BE LIABLE TO ANY PARTY FOR # DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING # OUT OF THE USE OF THIS CODE, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE # POSSIBILITY OF SUCH DAMAGE. # # BIZAR SOFTWARE PTY LTD SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, # BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS # FOR A PARTICULAR PURPOSE. THE CODE PROVIDED HEREUNDER IS ON AN "AS IS" # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # """This module defines a backend that saves the hyperdatabase in a database chosen by anydbm. It is guaranteed to always be available in python versions >2.1.1 (the dumbdbm fallback in 2.1.1 and earlier has several serious bugs, and is not available) """ __docformat__ = 'restructuredtext' import copy import logging import marshal import os import re import shutil import time from roundup.anypy.dbm_ import anydbm, whichdb from roundup.anypy.strings import b2s, bs2b, repr_export, eval_import, is_us from roundup import hyperdb, date, password, roundupdb, security, support from roundup.mlink_expr import Expression from roundup.backends import locking from roundup.i18n import _ from roundup.backends.blobfiles import FileStorage from roundup.backends import sessions_dbm try: from roundup.backends import sessions_redis except ImportError: sessions_redis = None from roundup.backends.indexer_common import get_indexer from hashlib import md5 def db_exists(config): # check for the user db for db in 'nodes.user nodes.user.db'.split(): if os.path.exists(os.path.join(config.DATABASE, db)): return 1 return 0 def db_nuke(config): shutil.rmtree(config.DATABASE) # python 3 doesn't have a unicode type try: unicode # noqa: F821 except NameError: unicode = str # marker used for an unspecified keyword argument _marker = [] # # Now the database # class Database(FileStorage, hyperdb.Database, roundupdb.Database): """A database for storing records containing flexible data types. Transaction stuff TODO: - check the timestamp of the class file and nuke the cache if it's modified. Do some sort of conflict checking on the dirty stuff. - perhaps detect write collisions (related to above)? attributes: dbtype: holds the value for the type of db. It is used by indexer to identify the database type so it can import the correct indexer module when using native text search mode. """ dbtype = "anydbm" # used by migrate roundup_admin command. Is a no-op for anydbm. # but needed to stop traceback in admin. db_version_updated = False def __init__(self, config, journaltag=None): """Open a hyperdatabase given a specifier to some storage. The 'storagelocator' is obtained from config.DATABASE. The meaning of 'storagelocator' depends on the particular implementation of the hyperdatabase. It could be a file name, a directory path, a socket descriptor for a connection to a database over the network, etc. The 'journaltag' is a token that will be attached to the journal entries for any edits done on the database. If 'journaltag' is None, the database is opened in read-only mode: the Class.create(), Class.set(), Class.retire(), and Class.restore() methods are disabled. """ FileStorage.__init__(self, config.UMASK) roundupdb.Database.__init__(self) self.config, self.journaltag = config, journaltag self.dir = config.DATABASE self.classes = {} self.cache = {} # cache of nodes loaded or created self.stats = {'cache_hits': 0, 'cache_misses': 0, 'get_items': 0, 'filtering': 0} self.dirtynodes = {} # keep track of the dirty nodes by class self.newnodes = {} # keep track of the new nodes by class self.destroyednodes = {} # keep track of the destroyed nodes by class self.transactions = [] self.indexer = get_indexer(config, self) self.security = security.Security(self) os.umask(config.UMASK) # make sure the database directory exists if not os.path.isdir(self.config.DATABASE): os.makedirs(self.config.DATABASE) # lock it lockfilenm = os.path.join(self.dir, 'lock') self.lockfile = locking.acquire_lock(lockfilenm) self.lockfile.write(str(os.getpid())) self.lockfile.flush() self.Session = None self.Otk = None def post_init(self): """Called once the schema initialisation has finished. """ super(Database, self).post_init() # reindex the db if necessary if self.indexer.should_reindex(): self.reindex() def refresh_database(self): """Rebuild the database """ self.reindex() def getSessionManager(self): if not self.Session: if self.config.SESSIONDB_BACKEND == "redis": if sessions_redis is None: self.Session = sessions_dbm.Sessions(self) raise ValueError("[redis] session is set, but " "redis is not found") self.Session = sessions_redis.Sessions(self) else: self.Session = sessions_dbm.Sessions(self) return self.Session def getOTKManager(self): if not self.Otk: if self.config.SESSIONDB_BACKEND == "redis": if sessions_redis is None: self.Session = sessions_dbm.OneTimeKeys(self) raise ValueError("[redis] session is set, but " "redis is not found") self.Otk = sessions_redis.OneTimeKeys(self) else: self.Otk = sessions_dbm.OneTimeKeys(self) return self.Otk def reindex(self, classname=None, show_progress=False): if classname: classes = [self.getclass(classname)] else: classes = self.classes.values() for klass in classes: if show_progress: for nodeid in support.Progress('Reindex %s' % klass.classname, klass.list()): klass.index(nodeid) else: for nodeid in klass.list(): klass.index(nodeid) self.indexer.save_index() def __repr__(self): return '' % id(self) # # Classes # def __getattr__(self, classname): """A convenient way of calling self.getclass(classname).""" if classname in self.classes: return self.classes[classname] raise AttributeError(classname) def addclass(self, cl): cn = cl.classname if cn in self.classes: raise ValueError(_('Class "%s" already defined.') % cn) self.classes[cn] = cl # add default Edit and View permissions self.security.addPermission( name="Create", klass=cn, description="User is allowed to create "+cn) 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) self.security.addPermission( name="Retire", klass=cn, description="User is allowed to retire "+cn) def getclasses(self): """Return a list of the names of all existing classes.""" return sorted(self.classes.keys()) def getclass(self, classname): """Get the Class object representing a particular class. If 'classname' is not a valid class name, a KeyError is raised. """ try: return self.classes[classname] except KeyError: raise KeyError('There is no class called "%s"' % classname) # # Class DBs # def clear(self): """Delete all database contents """ logging.getLogger('roundup.hyperdb.backend').info('clear') for cn in self.classes: for data_type in 'nodes', 'journals': path = os.path.join(self.dir, '%s.%s' % (data_type, cn)) if os.path.exists(path): os.remove(path) elif os.path.exists(path+'.db'): # dbm appends .db os.remove(path+'.db') # reset id sequences path = os.path.join(os.getcwd(), self.dir, '_ids') if os.path.exists(path): os.remove(path) elif os.path.exists(path+'.db'): # dbm appends .db os.remove(path+'.db') def getclassdb(self, classname, mode='r'): """ grab a connection to the class db that will be used for multiple actions """ return self.opendb('nodes.%s' % classname, mode) def determine_db_type(self, path): """ determine which DB wrote the class file """ db_type = '' if os.path.exists(path): db_type = whichdb(path) if not db_type: raise hyperdb.DatabaseError(_("Couldn't identify database type")) elif os.path.exists(path+'.db'): # if the path ends in '.db', it's a dbm database, whether # anydbm says it's dbhash or not! db_type = 'dbm' return db_type def opendb(self, name, mode): """Low-level database opener that gets around anydbm/dbm eccentricities. """ # figure the class db type path = os.path.join(os.getcwd(), self.dir, name) db_type = self.determine_db_type(path) # new database? let anydbm pick the best dbm # in Python 3+ the "dbm" ("anydbm" to us) module already uses the # whichdb() function to do this if not db_type or hasattr(anydbm, 'whichdb'): if __debug__: logging.getLogger('roundup.hyperdb.backend').debug( "opendb anydbm.open(%r, 'c')" % path) return anydbm.open(path, 'c') # in Python <3 it anydbm was a little dumb so manually open the # database with the correct module try: dbm = __import__(db_type) except ImportError: if db_type == 'gdbm': try: dbm = __import__('dbm.gnu') except ImportError: raise hyperdb.DatabaseError(_( "Couldn't open database - the required module '%s' " "(as dbm.gnu) is not available") % db_type) else: raise hyperdb.DatabaseError(_( "Couldn't open database - the " "required module '%s' is not available") % db_type) if __debug__: logging.getLogger('roundup.hyperdb.backend').debug( "opendb %r.open(%r, %r)" % (db_type, path, mode)) return dbm.open(path, mode) # # Node IDs # def newid(self, classname): """ Generate a new id for the given class """ # open the ids DB - create if if doesn't exist db = self.opendb('_ids', 'c') if classname in db: newid = db[classname] = str(int(db[classname]) + 1) else: # the count() bit is transitional - older dbs won't start at 1 newid = str(self.getclass(classname).count()+1) db[classname] = newid db.close() return newid def setid(self, classname, setid): """ Set the id counter: used during import of database """ # open the ids DB - create if if doesn't exist db = self.opendb('_ids', 'c') db[classname] = str(setid) db.close() # # Nodes # def addnode(self, classname, nodeid, node): """ add the specified node to its class's db """ # we'll be supplied these props if we're doing an import if 'creator' not in node: # add in the "calculated" properties (dupe so we don't affect # calling code's node assumptions) node = node.copy() node['creator'] = self.getuid() node['actor'] = self.getuid() node['creation'] = node['activity'] = date.Date() self.newnodes.setdefault(classname, {})[nodeid] = 1 self.cache.setdefault(classname, {})[nodeid] = node self.savenode(classname, nodeid, node) def setnode(self, classname, nodeid, node): """ change the specified node """ self.dirtynodes.setdefault(classname, {})[nodeid] = 1 # can't set without having already loaded the node self.cache[classname][nodeid] = node self.savenode(classname, nodeid, node) def savenode(self, classname, nodeid, node): """ perform the saving of data specified by the set/addnode """ if __debug__: logging.getLogger('roundup.hyperdb.backend').debug( 'save %s%s %r' % (classname, nodeid, node)) self.transactions.append((self.doSaveNode, (classname, nodeid, node))) def getnode(self, classname, nodeid, db=None, cache=1): """ get a node from the database Note the "cache" parameter is not used, and exists purely for backward compatibility! """ # try the cache cache_dict = self.cache.setdefault(classname, {}) if nodeid in cache_dict: if __debug__: logging.getLogger('roundup.hyperdb.backend').debug( 'get %s%s cached' % (classname, nodeid)) self.stats['cache_hits'] += 1 return cache_dict[nodeid] if __debug__: self.stats['cache_misses'] += 1 start_t = time.time() logging.getLogger('roundup.hyperdb.backend').debug( 'get %s%s' % (classname, nodeid)) # get from the database and save in the cache if db is None: db = self.getclassdb(classname) if nodeid not in db: raise IndexError("no such %s %s" % (classname, nodeid)) # check the uncommitted, destroyed nodes if (classname in self.destroyednodes and nodeid in self.destroyednodes[classname]): raise IndexError("no such %s %s" % (classname, nodeid)) # decode res = marshal.loads(db[nodeid]) # reverse the serialisation res = self.unserialise(classname, res) # store off in the cache dict if cache: cache_dict[nodeid] = res if __debug__: self.stats['get_items'] += (time.time() - start_t) return res def destroynode(self, classname, nodeid): """Remove a node from the database. Called exclusively by the destroy() method on Class. """ logging.getLogger('roundup.hyperdb.backend').info( 'destroy %s%s' % (classname, nodeid)) # remove from cache and newnodes if it's there if (classname in self.cache and nodeid in self.cache[classname]): del self.cache[classname][nodeid] if (classname in self.newnodes and nodeid in self.newnodes[classname]): del self.newnodes[classname][nodeid] # see if there's any obvious commit actions that we should get rid of for entry in self.transactions[:]: if entry[1][:2] == (classname, nodeid): self.transactions.remove(entry) # add to the destroyednodes map self.destroyednodes.setdefault(classname, {})[nodeid] = 1 # add the destroy commit action self.transactions.append((self.doDestroyNode, (classname, nodeid))) self.transactions.append((FileStorage.destroy, (self, classname, nodeid))) def serialise(self, classname, node): """Copy the node contents, converting non-marshallable data into marshallable data. """ properties = self.getclass(classname).getprops() d = {} for k, v in node.items(): if k == self.RETIRED_FLAG: d[k] = v continue # if the property doesn't exist then we really don't care if k not in properties: continue # get the property spec prop = properties[k] if isinstance(prop, hyperdb.Password) and v is not None: d[k] = str(v) elif isinstance(prop, hyperdb.Date) and v is not None: d[k] = v.serialise() elif isinstance(prop, hyperdb.Interval) and v is not None: d[k] = v.serialise() else: d[k] = v return d def unserialise(self, classname, node): """Decode the marshalled node data """ properties = self.getclass(classname).getprops() d = {} for k, v in node.items(): # if the property doesn't exist, or is the "retired" flag then # it won't be in the properties dict if k not in properties: d[k] = v continue # get the property spec prop = properties[k] if isinstance(prop, hyperdb.Date) and v is not None: d[k] = date.Date(v) elif isinstance(prop, hyperdb.Interval) and v is not None: d[k] = date.Interval(v) elif isinstance(prop, hyperdb.Password) and v is not None: d[k] = password.Password(encrypted=v, config=self.config) else: d[k] = v return d def hasnode(self, classname, nodeid, db=None): """ determine if the database has a given node """ # try the cache cache = self.cache.setdefault(classname, {}) if nodeid in cache: return 1 # not in the cache - check the database if db is None: db = self.getclassdb(classname) return nodeid in db def countnodes(self, classname, db=None): count = 0 # include the uncommitted nodes if classname in self.newnodes: count += len(self.newnodes[classname]) if classname in self.destroyednodes: count -= len(self.destroyednodes[classname]) # and count those in the DB if db is None: db = self.getclassdb(classname) return count + len(db) # # Files - special node properties # inherited from FileStorage # # Journal # def addjournal(self, classname, nodeid, action, params, creator=None, creation=None): """ Journal the Action 'action' may be: 'set' -- 'params' is a dictionary of property values 'create' -- 'params' is an empty dictionary as of Wed Nov 06 11:38:43 2002 +0000 'link' or 'unlink' -- 'params' is (classname, nodeid, propname) 'retired' or 'restored' -- 'params' is None 'creator' -- the user performing the action, which defaults to the current user. """ if __debug__: logging.getLogger('roundup.hyperdb.backend').debug( 'addjournal %s%s %s %r %s %r' % ( classname, nodeid, action, params, creator, creation)) if creator is None: creator = self.getuid() self.transactions.append((self.doSaveJournal, ( classname, nodeid, action, params, creator, creation))) def setjournal(self, classname, nodeid, journal): """Set the journal to the "journal" list.""" if __debug__: logging.getLogger('roundup.hyperdb.backend').debug( 'setjournal %s%s %r' % (classname, nodeid, journal)) self.transactions.append((self.doSetJournal, (classname, nodeid, journal))) def fix_journal(self, classname, journal): """ fix password entries to correct type """ pwprops = {} for pn, prop in self.getclass(classname).properties.items(): if isinstance(prop, hyperdb.Password): pwprops[pn] = 1 if not pwprops: return journal for j in journal: if j[3] == 'set': for k in j[4].keys(): if k in pwprops and j[4][k]: j[4][k] = password.JournalPassword(j[4][k]) return journal def getjournal(self, classname, nodeid): """ get the journal for id Raise IndexError if the node doesn't exist (as per history()'s API) """ # our journal result res = [] # add any journal entries for transactions not committed to the # database for method, args in self.transactions: if method != self.doSaveJournal: continue (cache_classname, cache_nodeid, cache_action, cache_params, cache_creator, cache_creation) = args if cache_classname == classname and cache_nodeid == nodeid: if not cache_creator: cache_creator = self.getuid() if not cache_creation: cache_creation = date.Date() res.append((cache_nodeid, cache_creation, cache_creator, cache_action, cache_params)) # attempt to open the journal - in some rare cases, the journal may # not exist try: db = self.opendb('journals.%s' % classname, 'r') except anydbm.error as error: if str(error) == "need 'c' or 'n' flag to open new db": raise IndexError('no such %s %s' % (classname, nodeid)) elif error.args[0] != 2: # this isn't a "not found" error, be alarmed! raise if res: # we have unsaved journal entries, return them return self.fix_journal(classname, res) raise IndexError('no such %s %s' % (classname, nodeid)) try: journal = marshal.loads(db[nodeid]) except KeyError: db.close() if res: # we have some unsaved journal entries, be happy! return self.fix_journal(classname, res) raise IndexError('no such %s %s' % (classname, nodeid)) db.close() # add all the saved journal entries for this node for nodeid, date_stamp, user, action, params in journal: res.append((nodeid, date.Date(date_stamp), user, action, params)) return self.fix_journal(classname, res) def pack(self, pack_before): """ Delete all journal entries except "create" before 'pack_before'. """ pack_before = pack_before.serialise() for classname in self.getclasses(): packed = 0 # get the journal db db_name = 'journals.%s' % classname path = os.path.join(os.getcwd(), self.dir, classname) db_type = self.determine_db_type(path) db = self.opendb(db_name, 'w') for key in map(b2s, db.keys()): # get the journal for this db entry journal = marshal.loads(db[key]) kept_entries = [] for entry in journal: # unpack the entry (nodeid, date_stamp, self.journaltag, action, params) = entry # if the entry is after the pack date, _or_ the initial # create entry, then it stays if date_stamp > pack_before or action == 'create': kept_entries.append(entry) else: packed += 1 db[key] = marshal.dumps(kept_entries) logging.getLogger('roundup.hyperdb.backend').info( 'packed %d %s items' % (packed, classname)) if db_type == 'gdbm': db.reorganize() db.close() # # Basic transaction support # def commit(self): """ Commit the current transactions. Save all data changed since the database was opened or since the last commit() or rollback(). """ logging.getLogger('roundup.hyperdb.backend').info( 'commit %s transactions' % (len(self.transactions))) # keep a handle to all the database files opened self.databases = {} try: # now, do all the transactions reindex = {} for method, args in self.transactions: reindex[method(*args)] = 1 finally: # make sure we close all the database files for db in self.databases.values(): db.close() del self.databases # clear the transactions list now so the blobfile implementation # doesn't think there's still pending file commits when it tries # to access the file data self.transactions = [] # reindex the nodes that request it for classname, nodeid in [k for k in reindex if k]: self.getclass(classname).index(nodeid) # save the indexer state self.indexer.save_index() self.clearCache() def clearCache(self): # all transactions committed, back to normal self.cache = {} self.dirtynodes = {} self.newnodes = {} self.destroyednodes = {} self.transactions = [] # upcall is necessary! roundupdb.Database.clearCache(self) def getCachedClassDB(self, classname): """ get the class db, looking in our cache of databases for commit """ # get the database handle db_name = 'nodes.%s' % classname if db_name not in self.databases: self.databases[db_name] = self.getclassdb(classname, 'c') return self.databases[db_name] def doSaveNode(self, classname, nodeid, node): db = self.getCachedClassDB(classname) # now save the marshalled data db[nodeid] = marshal.dumps(self.serialise(classname, node)) # return the classname, nodeid so we reindex this content return (classname, nodeid) def getCachedJournalDB(self, classname): """ get the journal db, looking in our cache of databases for commit """ # get the database handle db_name = 'journals.%s' % classname if db_name not in self.databases: self.databases[db_name] = self.opendb(db_name, 'c') return self.databases[db_name] def doSaveJournal(self, classname, nodeid, action, params, creator, creation): # serialise the parameters now if necessary if isinstance(params, type({})): if action in ('set', 'create'): params = self.serialise(classname, params) # handle supply of the special journalling parameters (usually # supplied on importing an existing database) journaltag = creator if creation: journaldate = creation.serialise() else: journaldate = date.Date().serialise() # create the journal entry entry = (nodeid, journaldate, journaltag, action, params) db = self.getCachedJournalDB(classname) # now insert the journal entry if nodeid in db: # append to existing s = db[nodeid] l = marshal.loads(s) l.append(entry) else: l = [entry] db[nodeid] = marshal.dumps(l) def doSetJournal(self, classname, nodeid, journal): l = [] for nodeid, journaldate, journaltag, action, params in journal: # serialise the parameters now if necessary if isinstance(params, type({})): if action in ('set', 'create'): params = self.serialise(classname, params) journaldate = journaldate.serialise() l.append((nodeid, journaldate, journaltag, action, params)) db = self.getCachedJournalDB(classname) db[nodeid] = marshal.dumps(l) def doDestroyNode(self, classname, nodeid): # delete from the class database db = self.getCachedClassDB(classname) if nodeid in db: del db[nodeid] # delete from the database db = self.getCachedJournalDB(classname) if nodeid in db: del db[nodeid] def rollback(self): """ Reverse all actions from the current transaction. """ logging.getLogger('roundup.hyperdb.backend').info( 'rollback %s transactions' % (len(self.transactions))) for method, args in self.transactions: # delete temporary files if method == self.doStoreFile: self.rollbackStoreFile(*args) self.cache = {} self.dirtynodes = {} self.newnodes = {} self.destroyednodes = {} self.transactions = [] def close(self): """ Nothing to do """ if self.lockfile is not None: locking.release_lock(self.lockfile) self.lockfile.close() self.lockfile = None class Class(hyperdb.Class): """The handle to a particular class of nodes in a hyperdatabase.""" def enableJournalling(self): """Turn journalling on for this class """ self.do_journal = 1 def disableJournalling(self): """Turn journalling off for this class """ self.do_journal = 0 # Editing nodes: def create(self, **propvalues): """Create a new node of this class and return its id. The keyword arguments in 'propvalues' map property names to values. The values of arguments must be acceptable for the types of their corresponding properties or a TypeError is raised. If this class has a key property, it must be present and its value must not collide with other key strings or a ValueError is raised. Any other properties on this class that are missing from the 'propvalues' dictionary are set to None. If an id in a link or multilink property does not refer to a valid node, an IndexError is raised. These operations trigger detectors and can be vetoed. Attempts to modify the "creation" or "activity" properties cause a KeyError. """ if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) self.fireAuditors('create', None, propvalues) newid = self.create_inner(**propvalues) self.fireReactors('create', newid, None) return newid def create_inner(self, **propvalues): """ Called by create, in-between the audit and react calls. """ if 'id' in propvalues: raise KeyError('"id" is reserved') if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) if ('creator' in propvalues or 'actor' in propvalues or 'creation' in propvalues or 'activity' in propvalues): raise KeyError('"creator", "actor", "creation" and ' '"activity" are reserved') for p in propvalues: prop = self.properties[p] if prop.computed: raise KeyError('"%s" is a computed property' % p) # new node's id newid = self.db.newid(self.classname) # validate propvalues num_re = re.compile(r'^\d+$') for key, value in propvalues.items(): if key == self.key: try: self.lookup(value) except KeyError: pass else: raise ValueError('node with key "%s" exists' % value) # try to handle this property try: prop = self.properties[key] except KeyError: raise KeyError('"%s" has no property "%s"' % ( self.classname, key)) if value is not None and isinstance(prop, hyperdb.Link): if not isinstance(value, str): raise ValueError('link value must be String') link_class = self.properties[key].classname # if it isn't a number, it's a key if not num_re.match(value): try: value = self.db.classes[link_class].lookup(value) except (TypeError, KeyError): raise IndexError('new property "%s": %s not a %s' % ( key, value, link_class)) elif not self.db.getclass(link_class).hasnode(value): raise IndexError('%s has no node %s' % (link_class, value)) # save off the value propvalues[key] = value # register the link with the newly linked node if self.do_journal and self.properties[key].do_journal: self.db.addjournal(link_class, value, 'link', (self.classname, newid, key)) elif isinstance(prop, hyperdb.Multilink): if value is None: value = [] if not hasattr(value, '__iter__') or isinstance(value, str): raise TypeError( 'new property "%s" not an iterable of ids' % key) # clean up and validate the list of links link_class = self.properties[key].classname l = [] for entry in value: if not isinstance(entry, str): raise ValueError('"%s" multilink value (%r) ' 'must contain Strings' % (key, value)) # if it isn't a number, it's a key if not num_re.match(entry): try: entry = self.db.classes[link_class].lookup(entry) except (TypeError, KeyError): raise IndexError( 'new property "%s": %s not a %s' % ( key, entry, self.properties[key].classname)) l.append(entry) value = l propvalues[key] = value # handle additions for nodeid in value: if not self.db.getclass(link_class).hasnode(nodeid): raise IndexError('%s has no node %s' % ( link_class, nodeid)) # register the link with the newly linked node if self.do_journal and self.properties[key].do_journal: self.db.addjournal(link_class, nodeid, 'link', (self.classname, newid, key)) elif isinstance(prop, hyperdb.String): if not isinstance(value, (str, unicode)): raise TypeError('new property "%s" not a string' % key) if prop.indexme: self.db.indexer.add_text( (self.classname, newid, key), value) elif isinstance(prop, hyperdb.Password): if not isinstance(value, password.Password): raise TypeError('new property "%s" not a Password' % key) elif isinstance(prop, hyperdb.Date): if value is not None and not isinstance(value, date.Date): raise TypeError('new property "%s" not a Date' % key) elif isinstance(prop, hyperdb.Interval): if value is not None and not isinstance(value, date.Interval): raise TypeError('new property "%s" not an Interval' % key) elif value is not None and isinstance(prop, hyperdb.Number): try: float(value) except ValueError: raise TypeError('new property "%s" not numeric' % key) elif value is not None and isinstance(prop, hyperdb.Integer): try: int(value) except ValueError: raise TypeError('new property "%s" not an integer' % key) elif value is not None and isinstance(prop, hyperdb.Boolean): try: int(value) except ValueError: raise TypeError('new property "%s" not boolean' % key) # make sure there's data where there needs to be for key, prop in self.properties.items(): if key in propvalues: continue if key == self.key: raise ValueError('key property "%s" is required' % key) if isinstance(prop, hyperdb.Multilink): propvalues[key] = [] # done self.db.addnode(self.classname, newid, propvalues) if self.do_journal: self.db.addjournal(self.classname, newid, 'create', {}) return newid def get(self, nodeid, propname, default=_marker, cache=1): """Get the value of a property on an existing node of this class. 'nodeid' must be the id of an existing node of this class or an IndexError is raised. 'propname' must be the name of a property of this class or a KeyError is raised. 'cache' exists for backward compatibility, and is not used. Attempts to get the "creation" or "activity" properties should do the right thing. """ if propname == 'id': return nodeid # get the node's dict d = self.db.getnode(self.classname, nodeid) # check for one of the special props if propname == 'creation': if 'creation' in d: return d['creation'] if not self.do_journal: raise ValueError('Journalling is disabled for this class') journal = self.db.getjournal(self.classname, nodeid) if journal: return journal[0][1] else: # on the strange chance that there's no journal return date.Date() if propname == 'activity': if 'activity' in d: return d['activity'] if not self.do_journal: raise ValueError('Journalling is disabled for this class') journal = self.db.getjournal(self.classname, nodeid) if journal: return self.db.getjournal(self.classname, nodeid)[-1][1] else: # on the strange chance that there's no journal return date.Date() if propname == 'creator': if 'creator' in d: return d['creator'] if not self.do_journal: raise ValueError('Journalling is disabled for this class') journal = self.db.getjournal(self.classname, nodeid) if journal: num_re = re.compile(r'^\d+$') value = journal[0][2] if num_re.match(value): return value else: # old-style "username" journal tag try: return self.db.user.lookup(value) except KeyError: # user's been retired, return admin return '1' else: return self.db.getuid() if propname == 'actor': if 'actor' in d: return d['actor'] if not self.do_journal: raise ValueError('Journalling is disabled for this class') journal = self.db.getjournal(self.classname, nodeid) if journal: num_re = re.compile(r'^\d+$') value = journal[-1][2] if num_re.match(value): return value else: # old-style "username" journal tag try: return self.db.user.lookup(value) except KeyError: # user's been retired, return admin return '1' else: return self.db.getuid() # get the property (raises KeyErorr if invalid) prop = self.properties[propname] if isinstance(prop, hyperdb.Multilink) and prop.computed: cls = self.db.getclass(prop.rev_classname) ids = cls.find(**{prop.rev_propname: nodeid}) return ids if propname not in d: if default is _marker: if isinstance(prop, hyperdb.Multilink): return [] else: return None else: return default # return a dupe of the list so code doesn't get confused if isinstance(prop, hyperdb.Multilink): ids = d[propname][:] ids.sort(key=lambda x: int(x)) return ids return d[propname] def set(self, nodeid, **propvalues): """Modify a property on an existing node of this class. 'nodeid' must be the id of an existing node of this class or an IndexError is raised. Each key in 'propvalues' must be the name of a property of this class or a KeyError is raised. All values in 'propvalues' must be acceptable types for their corresponding properties or a TypeError is raised. If the value of the key property is set, it must not collide with other key strings or a ValueError is raised. If the value of a Link or Multilink property contains an invalid node id, a ValueError is raised. These operations trigger detectors and can be vetoed. Attempts to modify the "creation" or "activity" properties cause a KeyError. """ if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) self.fireAuditors('set', nodeid, propvalues) oldvalues = copy.deepcopy(self.db.getnode(self.classname, nodeid)) for name, prop in self.getprops(protected=0).items(): if name in oldvalues: continue if isinstance(prop, hyperdb.Multilink): oldvalues[name] = [] else: oldvalues[name] = None propvalues = self.set_inner(nodeid, **propvalues) self.fireReactors('set', nodeid, oldvalues) return propvalues def set_inner(self, nodeid, **propvalues): """ Called by set, in-between the audit and react calls. """ if not propvalues: return propvalues if ('creator' in propvalues or 'actor' in propvalues or 'creation' in propvalues or 'activity' in propvalues): raise KeyError('"creator", "actor", "creation" and ' '"activity" are reserved') if 'id' in propvalues: raise KeyError('"id" is reserved') for p in propvalues: prop = self.properties[p] if prop.computed: raise KeyError('"%s" is a computed property' % p) if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) node = self.db.getnode(self.classname, nodeid) if self.db.RETIRED_FLAG in node: raise IndexError num_re = re.compile(r'^\d+$') # if the journal value is to be different, store it in here journalvalues = {} # omit quiet properties from history/changelog quiet_props = [] # list() propvalues 'cos it might be modified by the loop for propname, value in list(propvalues.items()): # check to make sure we're not duplicating an existing key if propname == self.key and node[propname] != value: try: self.lookup(value) except KeyError: pass else: raise ValueError('node with key "%s" exists' % value) # this will raise the KeyError if the property isn't valid # ... we don't use getprops() here because we only care about # the writeable properties. try: prop = self.properties[propname] except KeyError: raise KeyError('"%s" has no property named "%s"' % ( self.classname, propname)) # if the value's the same as the existing value, no sense in # doing anything current = node.get(propname, None) if value == current: del propvalues[propname] continue journalvalues[propname] = current # do stuff based on the prop type if isinstance(prop, hyperdb.Link): link_class = prop.classname # if it isn't a number, it's a key if value is not None and not isinstance(value, type('')): raise ValueError('property "%s" link value be a string' % ( propname)) if isinstance(value, type('')) and not num_re.match(value): try: value = self.db.classes[link_class].lookup(value) except (TypeError, KeyError): raise IndexError('new property "%s": %s not a %s' % ( propname, value, prop.classname)) if (value is not None and not self.db.getclass(link_class).hasnode(value)): raise IndexError('%s has no node %s' % (link_class, value)) if self.do_journal and prop.do_journal: # register the unlink with the old linked node if propname in node and node[propname] is not None: self.db.addjournal(link_class, node[propname], 'unlink', (self.classname, nodeid, propname)) # register the link with the newly linked node if value is not None: self.db.addjournal(link_class, value, 'link', (self.classname, nodeid, propname)) elif isinstance(prop, hyperdb.Multilink): if value is None: value = [] if not hasattr(value, '__iter__') or isinstance(value, str): raise TypeError('new property "%s" not an iterable of' ' ids' % propname) link_class = self.properties[propname].classname l = [] for entry in value: # if it isn't a number, it's a key if not isinstance(entry, str): raise ValueError('new property "%s" link value ' 'must be a string' % propname) if not num_re.match(entry): try: entry = self.db.classes[link_class].lookup(entry) except (TypeError, KeyError): raise IndexError( 'new property "%s": %s not a %s' % ( propname, entry, self.properties[propname].classname)) l.append(entry) value = l propvalues[propname] = value # figure the journal entry for this property add = [] remove = [] # handle removals if propname in node: l = node[propname] else: l = [] for id in l[:]: if id in value: continue # register the unlink with the old linked node if self.do_journal and self.properties[propname].do_journal: self.db.addjournal(link_class, id, 'unlink', (self.classname, nodeid, propname)) l.remove(id) remove.append(id) # handle additions for id in value: if not self.db.getclass(link_class).hasnode(id): raise IndexError('%s has no node %s' % ( link_class, id)) if id in l: continue # register the link with the newly linked node if self.do_journal and self.properties[propname].do_journal: self.db.addjournal(link_class, id, 'link', (self.classname, nodeid, propname)) l.append(id) add.append(id) # figure the journal entry l = [] if add: l.append(('+', add)) if remove: l.append(('-', remove)) if l: journalvalues[propname] = tuple(l) elif isinstance(prop, hyperdb.String): if value is not None and not isinstance(value, (str, unicode)): raise TypeError('new property "%s" not a ' 'string' % propname) if prop.indexme: self.db.indexer.add_text( (self.classname, nodeid, propname), value) elif isinstance(prop, hyperdb.Password): if not isinstance(value, password.Password): raise TypeError('new property "%s" not a ' 'Password' % propname) propvalues[propname] = value journalvalues[propname] = \ current and password.JournalPassword(current) elif value is not None and isinstance(prop, hyperdb.Date): if not isinstance(value, date.Date): raise TypeError('new property "%s" not a ' 'Date' % propname) propvalues[propname] = value elif value is not None and isinstance(prop, hyperdb.Interval): if not isinstance(value, date.Interval): raise TypeError('new property "%s" not an ' 'Interval' % propname) propvalues[propname] = value elif value is not None and isinstance(prop, hyperdb.Number): try: float(value) except ValueError: raise TypeError('new property "%s" not ' 'numeric' % propname) elif value is not None and isinstance(prop, hyperdb.Integer): try: int(value) except ValueError: raise TypeError('new property "%s" not ' 'numeric' % propname) elif value is not None and isinstance(prop, hyperdb.Boolean): try: int(value) except ValueError: raise TypeError('new property "%s" not ' 'boolean' % propname) node[propname] = value # record quiet properties to omit from history/changelog if prop.quiet: quiet_props.append(propname) # nothing to do? if not propvalues: return propvalues # update the activity time node['activity'] = date.Date() node['actor'] = self.db.getuid() # do the set, and journal it self.db.setnode(self.classname, nodeid, node) if self.do_journal: self.db.addjournal(self.classname, nodeid, 'set', journalvalues) # remove quiet properties from output for propname in quiet_props: if propname in propvalues: del propvalues[propname] return propvalues def retire(self, nodeid): """Retire a node. The properties on the node remain available from the get() method, and the node's id is never reused. Retired nodes are not returned by the find(), list(), or lookup() methods, and other nodes may reuse the values of their key properties. These operations trigger detectors and can be vetoed. Attempts to modify the "creation" or "activity" properties cause a KeyError. """ if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) self.fireAuditors('retire', nodeid, None) node = self.db.getnode(self.classname, nodeid) node[self.db.RETIRED_FLAG] = 1 self.db.setnode(self.classname, nodeid, node) if self.do_journal: self.db.addjournal(self.classname, nodeid, 'retired', None) self.fireReactors('retire', nodeid, None) def restore(self, nodeid): """Restore a retired node. Make node available for all operations like it was before retirement. """ if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) node = self.db.getnode(self.classname, nodeid) # check if key property was overrided key = self.getkey() try: # eval for exception side effect id = self.lookup(node[key]) # noqa: F841 except KeyError: pass else: raise KeyError("Key property (%s) of retired node clashes " "with existing one (%s)" % (key, node[key])) # Now we can safely restore node self.fireAuditors('restore', nodeid, None) del node[self.db.RETIRED_FLAG] self.db.setnode(self.classname, nodeid, node) if self.do_journal: self.db.addjournal(self.classname, nodeid, 'restored', None) self.fireReactors('restore', nodeid, None) def is_retired(self, nodeid, cldb=None): """Return true if the node is retired. """ node = self.db.getnode(self.classname, nodeid, cldb) if self.db.RETIRED_FLAG in node: return 1 return 0 def destroy(self, nodeid): """Destroy a node. WARNING: this method should never be used except in extremely rare situations where there could never be links to the node being deleted WARNING: use retire() instead WARNING: the properties of this node will not be available ever again WARNING: really, use retire() instead Well, I think that's enough warnings. This method exists mostly to support the session storage of the cgi interface. """ if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) self.db.destroynode(self.classname, nodeid) # Locating nodes: def hasnode(self, nodeid): """Determine if the given nodeid actually exists """ return self.db.hasnode(self.classname, nodeid) def setkey(self, propname): """Select a String property of this class to be the key property. 'propname' must be the name of a String property of this class or None, or a TypeError is raised. The values of the key property on all existing nodes must be unique or a ValueError is raised. If the property doesn't exist, KeyError is raised. """ prop = self.getprops()[propname] if not isinstance(prop, hyperdb.String): raise TypeError('key properties must be String') self.key = propname def getkey(self): """Return the name of the key property for this class or None.""" return self.key # TODO: set up a separate index db file for this? profile? def lookup(self, keyvalue): """Locate a particular node by its key property and return its id. If this class has no key property, a TypeError is raised. If the 'keyvalue' matches one of the values for the key property among the nodes in this class, the matching node's id is returned; otherwise a KeyError is raised. """ if not self.key: raise TypeError('No key property set for ' 'class %s' % self.classname) cldb = self.db.getclassdb(self.classname) try: for nodeid in self.getnodeids(cldb): node = self.db.getnode(self.classname, nodeid, cldb) if self.db.RETIRED_FLAG in node: continue if self.key not in node: continue if node[self.key] == keyvalue: return nodeid finally: cldb.close() raise KeyError('No key (%s) value "%s" for "%s"' % ( self.key, keyvalue, self.classname)) # change from spec - allows multiple props to match def find(self, **propspec): """Get the ids of nodes in this class which link to the given nodes. 'propspec' consists of keyword args propname=nodeid or propname={nodeid:1, } 'propname' must be the name of a property in this class, or a KeyError is raised. That property must be a Link or Multilink property, or a TypeError is raised. Any node in this class whose 'propname' property links to any of the nodeids will be returned. Examples:: db.issue.find(messages='1') db.issue.find(messages={'1':1,'3':1}, files={'7':1}) db.issue.find(messages=('1','3'), files=('7',)) db.issue.find(messages=['1','3'], files=['7']) """ # shortcut if not propspec: return [] # validate the args props = self.getprops() for propname, itemids in propspec.items(): # check the prop is OK prop = props[propname] if not isinstance(prop, hyperdb.Link) and not isinstance(prop, hyperdb.Multilink): raise TypeError("'%s' not a Link/Multilink " "property" % propname) # ok, now do the find cldb = self.db.getclassdb(self.classname) l = [] rev_multilinks = [] try: for id in self.getnodeids(db=cldb): item = self.db.getnode(self.classname, id, db=cldb) if self.db.RETIRED_FLAG in item: continue for propname, itemids in propspec.items(): if not isinstance(itemids, dict): if itemids is None or isinstance(itemids, type("")): itemids = {itemids: 1} else: itemids = dict.fromkeys(itemids) # special case if the item doesn't have this property if propname not in item: if None in itemids: l.append(id) break continue # grab the property definition and its value on this item prop = props[propname] value = item[propname] if isinstance(prop, hyperdb.Link) and value in itemids: l.append(id) break elif isinstance(prop, hyperdb.Multilink): if prop.rev_property: rev_multilinks.append((prop, itemids)) continue hit = 0 for v in value: if v in itemids: l.append(id) hit = 1 break if hit: break for prop, itemids in rev_multilinks: rprop = prop.rev_property fun = l.append if isinstance(rprop, hyperdb.Multilink): fun = l.extend for id in itemids: fun(rprop.cls.get(id, rprop.name)) if rev_multilinks: l = list(sorted(set(l))) finally: cldb.close() return l def stringFind(self, **requirements): """Locate a particular node by matching a set of its String properties in a caseless search. If the property is not a String property, a TypeError is raised. The return is a list of the id of all nodes that match. """ for propname in requirements: prop = self.properties[propname] if not isinstance(prop, hyperdb.String): raise TypeError("'%s' not a String property" % propname) requirements[propname] = requirements[propname].lower() l = [] cldb = self.db.getclassdb(self.classname) try: for nodeid in self.getnodeids(cldb): node = self.db.getnode(self.classname, nodeid, cldb) if self.db.RETIRED_FLAG in node: continue for key, value in requirements.items(): if key not in node: break if node[key] is None or node[key].lower() != value: break else: l.append(nodeid) finally: cldb.close() return l def list(self): """ Return a list of the ids of the active nodes in this class. """ l = [] cn = self.classname cldb = self.db.getclassdb(cn) try: for nodeid in self.getnodeids(cldb): node = self.db.getnode(cn, nodeid, cldb) if self.db.RETIRED_FLAG in node: continue l.append(nodeid) finally: cldb.close() l.sort() return l def getnodeids(self, db=None, retired=None): """ Return a list of ALL nodeids Set retired=None to get all nodes. Otherwise it'll get all the retired or non-retired nodes, depending on the flag. """ res = [] # start off with the new nodes if self.classname in self.db.newnodes: res.extend(self.db.newnodes[self.classname]) must_close = False if db is None: db = self.db.getclassdb(self.classname) must_close = True try: res.extend(map(b2s, db.keys())) # remove the uncommitted, destroyed nodes if self.classname in self.db.destroyednodes: for nodeid in self.db.destroyednodes[self.classname]: if nodeid in db: res.remove(nodeid) # check retired flag if retired is False or retired is True: l = [] for nodeid in res: node = self.db.getnode(self.classname, nodeid, db) is_ret = self.db.RETIRED_FLAG in node if retired == is_ret: l.append(nodeid) res = l finally: if must_close: db.close() res.sort() return res num_re = re.compile(r'^\d+$') def _filter(self, search_matches, filterspec, proptree, num_re=num_re, retired=False, exact_match_spec=_marker): """Return a list of the ids of the nodes in this class that match the 'filter' spec, sorted by the group spec and then the sort spec. "filterspec" is {propname: value(s)} same for "exact_match_spec". The latter specifies exact matching for String type while String types in "filterspec" are searched for as case insensitive substring match. "sort" and "group" are (dir, prop) where dir is '+', '-' or None and prop is a prop name or None "search_matches" is a sequence type or None "retired" specifies if we should find only non-retired nodes (default) or only retired node (value True) or all nodes. The filter must match all properties specificed. If the property value to match is a list: 1. String properties must match all elements in the list, and 2. Other properties must match any of the elements in the list. """ if __debug__: start_t = time.time() if exact_match_spec is _marker: exact_match_spec = {} cn = self.classname # optimise filterspec l = [] props = self.getprops() LINK = 'spec:link' MULTILINK = 'spec:multilink' STRING = 'spec:string' DATE = 'spec:date' INTERVAL = 'spec:interval' OTHER = 'spec:other' for exact, filtertype in enumerate((filterspec, exact_match_spec)): for k, v in filtertype.items(): propclass = props[k] if isinstance(propclass, hyperdb.Link): if not isinstance(v, list): v = [v] l.append((LINK, k, v)) elif isinstance(propclass, hyperdb.Multilink): # If it's a reverse multilink, we've already # computed the ids of our own class. if propclass.rev_property: l.append((OTHER, 'id', v)) else: # the value -1 is a special "not set" sentinel if v in ('-1', ['-1']): v = [] elif not isinstance(v, list): v = [v] l.append((MULTILINK, k, v)) elif isinstance(propclass, hyperdb.String) and k != 'id': if not isinstance(v, list): v = [v] for x in v: if exact: l.append((STRING, k, x)) else: # simple glob searching x = re.sub(r'([\|\{\}\\\.\+\[\]\(\)])', r'\\\1', x) x = x.replace('?', '.') x = x.replace('*', '.*?') l.append((STRING, k, re.compile(x, re.I))) elif isinstance(propclass, hyperdb.Date): try: ranges = [] for d in v.split(','): if d == '-': ranges.append(None) continue date_rng = propclass.range_from_raw(d, self.db) ranges.append(date_rng) l.append((DATE, k, ranges)) except ValueError: # If range creation fails - ignore that search parameter pass elif isinstance(propclass, hyperdb.Interval): try: intv_rng = date.Range(v, date.Interval) l.append((INTERVAL, k, intv_rng)) except ValueError: # If range creation fails - ignore that search parameter pass elif isinstance(propclass, hyperdb.Boolean): if isinstance(v, str): v = v.split(',') if not isinstance(v, list): v = [v] bv = [] for val in v: if isinstance(val, str): bv.append(propclass.from_raw(val)) else: bv.append(val) l.append((OTHER, k, bv)) elif k == 'id': if not isinstance(v, list): v = v.split(',') l.append((OTHER, k, [str(int(val)) for val in v])) elif isinstance(propclass, hyperdb.Number): if not isinstance(v, list): try: v = v.split(',') except AttributeError: v = [v] l.append((OTHER, k, [float(val) for val in v])) elif isinstance(propclass, hyperdb.Integer): if not isinstance(v, list): try: v = v.split(',') except AttributeError: v = [v] l.append((OTHER, k, [int(val) for val in v])) filterspec = l # now, find all the nodes that pass filtering matches = [] cldb = self.db.getclassdb(cn) t = 0 try: # TODO: only full-scan once (use items()) for nodeid in self.getnodeids(cldb, retired=retired): node = self.db.getnode(cn, nodeid, cldb) # apply filter for t, k, v in filterspec: # handle the id prop if k == 'id': if nodeid not in v: break continue # get the node value nv = node.get(k, None) match = 0 # now apply the property filter if t == LINK: # link - if this node's property doesn't appear in the # filterspec's nodeid list, skip it expr = Expression(v, is_link=True) if expr.evaluate(nv): match = 1 elif t == MULTILINK: # multilink - if any of the nodeids required by the # filterspec aren't in this node's property, then skip # it nv = node.get(k, []) # check for matching the absence of multilink values if not v: match = not nv else: # otherwise, make sure this node has each of the # required values expr = Expression(v) if expr.evaluate(nv): match = 1 elif t == STRING: if nv is None: nv = '' if is_us(v): # Exact match match = (nv == v) else: # RE search match = v.search(nv) elif t == DATE: for x in v: if x is None or nv is None: if nv is None and x is None: match = 1 break continue elif x.to_value: if x.from_value <= nv <= x.to_value: match = 1 break else: if x.from_value <= nv: match = 1 break elif t == INTERVAL: if nv is None: match = v is None else: if v.to_value: if v.from_value <= nv and v.to_value >= nv: match = 1 else: if v.from_value <= nv: match = 1 elif t == OTHER: # straight value comparison for the other types match = nv in v if not match: break else: matches.append([nodeid, node]) # filter based on full text search if search_matches is not None: k = [] for v in matches: if v[0] in search_matches: k.append(v) matches = k # add sorting information to the proptree JPROPS = {'actor': 1, 'activity': 1, 'creator': 1, 'creation': 1} children = [] if proptree: children = proptree.sortable_children() for pt in children: dir = pt.sort_direction prop = pt.name assert (dir and prop) propclass = props[prop] pt.sort_ids = [] is_pointer = isinstance(propclass, (hyperdb.Link, hyperdb.Multilink)) if not is_pointer: pt.sort_result = [] try: # cache the opened link class db, if needed. lcldb = None # cache the linked class items too lcache = {} for entry in matches: itemid = entry[-2] item = entry[-1] # handle the properties that might be "faked" # also, handle possible missing properties try: v = item[prop] except KeyError: if prop in JPROPS: # force lookup of the special journal prop v = self.get(itemid, prop) else: # the node doesn't have a value for this # property v = None if isinstance(propclass, hyperdb.Multilink): v = [] if prop == 'id': v = int(itemid) pt.sort_ids.append(v) if not is_pointer: pt.sort_result.append(v) continue # missing (None) values are always sorted first if v is None: pt.sort_ids.append(v) if not is_pointer: pt.sort_result.append(v) continue if isinstance(propclass, hyperdb.Link): lcn = propclass.classname link = self.db.classes[lcn] key = link.orderprop() child = pt.propdict[key] if key != 'id': if v not in lcache: # open the link class db if it's not already if lcldb is None: lcldb = self.db.getclassdb(lcn) lcache[v] = self.db.getnode(lcn, v, lcldb) r = lcache[v][key] child.propdict[key].sort_ids.append(r) else: child.propdict[key].sort_ids.append(v) pt.sort_ids.append(v) if not is_pointer: r = propclass.sort_repr(pt.parent.cls, v, pt.name) pt.sort_result.append(r) finally: # if we opened the link class db, close it now if lcldb is not None: lcldb.close() del lcache finally: cldb.close() # pull the id out of the individual entries matches = [entry[-2] for entry in matches] if __debug__: self.db.stats['filtering'] += (time.time() - start_t) return matches def count(self): """Get the number of nodes in this class. If the returned integer is 'numnodes', the ids of all the nodes in this class run from 1 to numnodes, and numnodes+1 will be the id of the next node to be created in this class. """ return self.db.countnodes(self.classname) # Manipulating properties: def getprops(self, protected=1): """Return a dictionary mapping property names to property objects. If the "protected" flag is true, we include protected properties - those which may not be modified. In addition to the actual properties on the node, these methods provide the "creation" and "activity" properties. If the "protected" flag is true, we include protected properties - those which may not be modified. """ d = self.properties.copy() if protected: d['id'] = hyperdb.String() d['creation'] = hyperdb.Date() d['activity'] = hyperdb.Date() d['creator'] = hyperdb.Link('user') d['actor'] = hyperdb.Link('user') return d def addprop(self, **properties): """Add properties to this class. The keyword arguments in 'properties' must map names to property objects, or a TypeError is raised. None of the keys in 'properties' may collide with the names of existing properties, or a ValueError is raised before any properties have been added. """ for key in properties: if key in self.properties: raise ValueError(key) self.properties.update(properties) def index(self, nodeid): """ Add (or refresh) the node to search indexes """ # find all the String properties that have indexme for prop, propclass in self.getprops().items(): if isinstance(propclass, hyperdb.String) and propclass.indexme: # index them under (classname, nodeid, property) self.db.indexer.add_text((self.classname, nodeid, prop), str(self.get(nodeid, prop))) # # import / export support # def export_list(self, propnames, nodeid): """ Export a node - generate a list of CSV-able data in the order specified by propnames for the given node. """ properties = self.getprops() l = [] for prop in propnames: proptype = properties[prop] value = self.get(nodeid, prop) # "marshal" data where needed if value is None: pass elif isinstance(proptype, hyperdb.Date): value = value.get_tuple() elif isinstance(proptype, hyperdb.Interval): value = value.get_tuple() elif isinstance(proptype, hyperdb.Password): value = str(value) l.append(repr_export(value)) # append retired flag l.append(repr_export(self.is_retired(nodeid))) return l def import_list(self, propnames, proplist): """ Import a node - all information including "id" is present and should not be sanity checked. Triggers are not triggered. The journal should be initialised using the "creator" and "created" information. Return the nodeid of the node imported. """ if self.db.journaltag is None: raise hyperdb.DatabaseError(_('Database open read-only')) properties = self.getprops() # make the new node's property map d = {} newid = None for i in range(len(propnames)): # Figure the property for this column propname = propnames[i] # Use eval_import to reverse the repr_export() used to # output the CSV value = eval_import(proplist[i]) # "unmarshal" where necessary if propname == 'id': newid = value continue elif propname == 'is retired': # is the item retired? if int(value): d[self.db.RETIRED_FLAG] = 1 continue elif value is None: d[propname] = None continue prop = properties[propname] if isinstance(prop, hyperdb.Date): value = date.Date(value) elif isinstance(prop, hyperdb.Interval): value = date.Interval(value) elif isinstance(prop, hyperdb.Password): value = password.Password(encrypted=value, config=self.db.config) d[propname] = value # get a new id if necessary if newid is None: newid = self.db.newid(self.classname) # add the node and journal self.db.addnode(self.classname, newid, d) return newid def export_journals(self): """Export a class's journal - generate a list of lists of CSV-able data: nodeid, date, user, action, params No heading here - the columns are fixed. """ properties = self.getprops() r = [] for nodeid in self.getnodeids(): for nodeid, date_, user, action, params in self.history( nodeid, enforceperm=False, skipquiet=False): date_ = date_.get_tuple() if action == 'set': export_data = {} for propname, value in params.items(): if propname not in properties: # property no longer in the schema continue prop = properties[propname] # make sure the params are eval()'able if value is None: pass elif isinstance(prop, hyperdb.Date): # this is a hack - some dates are stored as strings if not isinstance(value, type('')): value = value.get_tuple() elif isinstance(prop, hyperdb.Interval): # hack too - some intervals are stored as strings if not isinstance(value, type('')): value = value.get_tuple() elif isinstance(prop, hyperdb.Password): value = str(value) export_data[propname] = value params = export_data r.append([repr_export(nodeid), repr_export(date_), repr_export(user), repr_export(action), repr_export(params)]) return r class FileClass(hyperdb.FileClass, Class): """This class defines a large chunk of data. To support this, it has a mandatory String property "content" which is typically saved off externally to the hyperdb. The default MIME type of this data is defined by the "default_mime_type" class attribute, which may be overridden by each node if the class defines a "type" String property. """ def __init__(self, db, classname, **properties): """The newly-created class automatically includes the "content" and "type" properties. """ if 'content' not in properties: properties['content'] = hyperdb.String(indexme='yes') if 'type' not in properties: properties['type'] = hyperdb.String() Class.__init__(self, db, classname, **properties) def create(self, **propvalues): """ Snarf the "content" propvalue and store in a file """ # we need to fire the auditors now, or the content property won't # be in propvalues for the auditors to play with self.fireAuditors('create', None, propvalues) # now remove the content property so it's not stored in the db content = propvalues['content'] del propvalues['content'] # make sure we have a MIME type mime_type = propvalues.get('type', self.default_mime_type) # do the database create newid = self.create_inner(**propvalues) # store off the content as a file self.db.storefile(self.classname, newid, None, bs2b(content)) # fire reactors self.fireReactors('create', newid, None) return newid def get(self, nodeid, propname, default=_marker, cache=1): """ Trap the content propname and get it from the file 'cache' exists for backwards compatibility, and is not used. """ poss_msg = 'Possibly an access right configuration problem.' if propname == 'content': try: return b2s(self.db.getfile(self.classname, nodeid, None)) except IOError as strerror: # XXX by catching this we don't see an error in the log. return 'ERROR reading file: %s%s\n%s\n%s' % ( self.classname, nodeid, poss_msg, strerror) except UnicodeDecodeError as e: # if content is not text (e.g. jpeg file) we get # unicode error trying to convert to string in python 3. # trap it and supply an error message. Include md5sum # of content as this string is included in the etag # calculation of the object. return ('%s%s is not text, retrieve using ' 'binary_content property. mdsum: %s') % ( self.classname, nodeid, md5(self.db.getfile( self.classname, nodeid, None)).hexdigest()) # nosec - bandit md5 use ok elif propname == 'binary_content': return self.db.getfile(self.classname, nodeid, None) if default is not _marker: return Class.get(self, nodeid, propname, default) else: return Class.get(self, nodeid, propname) def set(self, itemid, **propvalues): """ Snarf the "content" propvalue and update it in a file """ self.fireAuditors('set', itemid, propvalues) # create the oldvalues dict - fill in any missing values oldvalues = copy.deepcopy(self.db.getnode(self.classname, itemid)) for name, prop in self.getprops(protected=0).items(): if name in oldvalues: continue if isinstance(prop, hyperdb.Multilink): oldvalues[name] = [] else: oldvalues[name] = None # now remove the content property so it's not stored in the db content = None if 'content' in propvalues: content = propvalues['content'] del propvalues['content'] # do the database update propvalues = self.set_inner(itemid, **propvalues) # do content? if content: # store and possibly index self.db.storefile(self.classname, itemid, None, bs2b(content)) if self.properties['content'].indexme: index_content = content if bytes != str and isinstance(content, bytes): index_content = content.decode('utf-8', errors='ignore') mime_type = self.get(itemid, 'type', self.default_mime_type) self.db.indexer.add_text((self.classname, itemid, 'content'), index_content, mime_type) propvalues['content'] = content # fire reactors self.fireReactors('set', itemid, oldvalues) return propvalues def index(self, nodeid): """ Add (or refresh) the node to search indexes. Use the content-type property for the content property. """ # find all the String properties that have indexme for prop, propclass in self.getprops().items(): if prop == 'content' and propclass.indexme: mime_type = self.get(nodeid, 'type', self.default_mime_type) index_content = self.get(nodeid, 'binary_content') if bytes != str and isinstance(index_content, bytes): index_content = index_content.decode('utf-8', errors='ignore') self.db.indexer.add_text((self.classname, nodeid, 'content'), index_content, mime_type) elif isinstance(propclass, hyperdb.String) and propclass.indexme: # index them under (classname, nodeid, property) try: value = str(self.get(nodeid, prop)) except IndexError: # node has been destroyed continue self.db.indexer.add_text((self.classname, nodeid, prop), value) # deviation from spec - was called ItemClass class IssueClass(Class, roundupdb.IssueClass): # Overridden methods: def __init__(self, db, classname, **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. """ if 'title' not in properties: properties['title'] = hyperdb.String(indexme='yes') if 'messages' not in properties: properties['messages'] = hyperdb.Multilink("msg") if 'files' not in properties: properties['files'] = hyperdb.Multilink("file") if 'nosy' not in properties: # note: journalling is turned off as it really just wastes # space. this behaviour may be overridden in an instance properties['nosy'] = hyperdb.Multilink("user", do_journal="no") if 'superseder' not in properties: properties['superseder'] = hyperdb.Multilink(classname) Class.__init__(self, db, classname, **properties) # vim: set et sts=4 sw=4 : code-2a6c3eb4e059/roundup/backends/back_mysql.py0000644000000000000000000006706014571501054017710 0ustar 00000000000000# # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev # # This module is free software, and you may redistribute it and/or modify # under the same terms as Python, so long as this copyright message and # disclaimer are retained in their original form. # '''This module defines a backend implementation for MySQL. How to implement AUTO_INCREMENT: mysql> create table foo (num integer auto_increment primary key, name varchar(255)) AUTO_INCREMENT=1 ENGINE=InnoDB; ql> insert into foo (name) values ('foo5'); Query OK, 1 row affected (0.00 sec) mysql> SELECT num FROM foo WHERE num IS NULL; +-----+ | num | +-----+ | 4 | +-----+ 1 row in set (0.00 sec) mysql> SELECT num FROM foo WHERE num IS NULL; Empty set (0.00 sec) NOTE: we don't need an index on the id column if it's PRIMARY KEY ''' __docformat__ = 'restructuredtext' import logging import os import shutil import sys import MySQLdb from MySQLdb.constants import ER from roundup import date, hyperdb, password from roundup.backends import rdbms_common isolation_levels = { 'read uncommitted': 'READ UNCOMMITTED', 'read committed': 'READ COMMITTED', 'repeatable read': 'REPEATABLE READ', 'serializable': 'SERIALIZABLE' } def connection_dict(config, dbnamestr=None): d = rdbms_common.connection_dict(config, dbnamestr) if 'password' in d: d['passwd'] = d['password'] del d['password'] if 'port' in d: d['port'] = int(d['port']) charset = config.RDBMS_MYSQL_CHARSET if charset != 'default': d['charset'] = charset return d def db_nuke(config): """Clear all database contents and drop database itself""" if db_exists(config): kwargs = connection_dict(config) conn = MySQLdb.connect(**kwargs) try: conn.select_db(config.RDBMS_NAME) except MySQLdb.Error: # no, it doesn't exist pass else: cursor = conn.cursor() cursor.execute("SHOW TABLES") tables = cursor.fetchall() # stupid MySQL bug requires us to drop all the tables first for table in tables: command = 'DROP TABLE `%s`' % table[0] logging.debug(command) cursor.execute(command) command = "DROP DATABASE %s" % config.RDBMS_NAME logging.info(command) cursor.execute(command) conn.commit() conn.close() if os.path.exists(config.DATABASE): shutil.rmtree(config.DATABASE) def db_create(config): """Create the database.""" kwargs = connection_dict(config) conn = MySQLdb.connect(**kwargs) cursor = conn.cursor() command = "CREATE DATABASE %s COLLATE utf8_general_ci" % config.RDBMS_NAME if sys.version_info[0] > 2: command += ' CHARACTER SET utf8' logging.info(command) cursor.execute(command) conn.commit() conn.close() def db_exists(config): """Check if database already exists.""" kwargs = connection_dict(config) conn = MySQLdb.connect(**kwargs) try: try: conn.select_db(config.RDBMS_NAME) except MySQLdb.OperationalError: return 0 finally: conn.close() return 1 class Database(rdbms_common.Database): """ Mysql DB backend implementation attributes: dbtype: holds the value for the type of db. It is used by indexer to identify the database type so it can import the correct indexer module when using native text search mode. """ arg = '%s' dbtype = "mysql" # used by some code to switch styles of query implements_intersect = 0 # Backend for MySQL to use. # InnoDB is faster, but if you're running <4.0.16 then you'll need to # use BDB to pass all unit tests. mysql_backend = 'InnoDB' # mysql_backend = 'BDB' hyperdb_to_sql_datatypes = { hyperdb.String: 'TEXT', hyperdb.Date: 'DATETIME', hyperdb.Link: 'INTEGER', hyperdb.Interval: 'VARCHAR(255)', hyperdb.Password: 'VARCHAR(255)', hyperdb.Boolean: 'BOOL', hyperdb.Number: 'REAL', hyperdb.Integer: 'INTEGER', } hyperdb_to_sql_value = { hyperdb.String: str, # no fractional seconds for MySQL hyperdb.Date: lambda x: x.formal(sep=' '), # noqa: E272 hyperdb.Link: int, hyperdb.Interval: str, hyperdb.Password: str, hyperdb.Boolean: int, hyperdb.Number: lambda x: x, # noqa: E272 hyperdb.Integer: int, hyperdb.Multilink: lambda x: x, # used in journal marshalling, noqa: E272 } def sql_open_connection(self): kwargs = connection_dict(self.config, 'db') self.log_info('open database %r' % (kwargs['db'],)) try: conn = MySQLdb.connect(**kwargs) except MySQLdb.OperationalError as message: raise hyperdb.DatabaseError(message) cursor = conn.cursor() cursor.execute("SET AUTOCOMMIT=0") lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL] cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % lvl) cursor.execute("START TRANSACTION") return (conn, cursor) def open_connection(self): # make sure the database actually exists if not db_exists(self.config): db_create(self.config) self.conn, self.cursor = self.sql_open_connection() try: self.load_dbschema() except MySQLdb.OperationalError as message: if message.args[0] != ER.NO_DB_ERROR: raise except MySQLdb.ProgrammingError as message: if message.args[0] != ER.NO_SUCH_TABLE: raise hyperdb.DatabaseError(message) self.init_dbschema() self.sql("CREATE TABLE `schema` (`schema` TEXT) ENGINE=%s" % self.mysql_backend) self.sql('''CREATE TABLE ids (name VARCHAR(255), num INTEGER) ENGINE=%s''' % self.mysql_backend) self.sql('create index ids_name_idx on ids(name)') self.create_version_2_tables() def load_dbschema(self): ''' Load the schema definition that the database currently implements ''' self.cursor.execute('select `schema` from `schema`') schema = self.cursor.fetchone() if schema: # bandit - schema is trusted self.database_schema = eval(schema[0]) # nosec else: self.database_schema = {} def save_dbschema(self): ''' Save the schema definition that the database currently implements ''' s = repr(self.database_schema) self.sql('delete from `schema`') self.sql('insert into `schema` values (%s)', (s,)) def create_version_2_tables(self): # OTK store self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), otk_value TEXT, otk_time DOUBLE) ENGINE=%s''' % self.mysql_backend) self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') # Sessions store self.sql('''CREATE TABLE sessions (session_key VARCHAR(255), session_time DOUBLE, session_value TEXT) ENGINE=%s''' % self.mysql_backend) self.sql('''CREATE INDEX sessions_key_idx ON sessions(session_key)''') # full-text indexing store self.sql('''CREATE TABLE __textids (_class VARCHAR(255), _itemid VARCHAR(255), _prop VARCHAR(255), _textid INT) ENGINE=%s''' % self.mysql_backend) self.sql('''CREATE TABLE __words (_word VARCHAR(%s), _textid INT) ENGINE=%s''' % ((self.indexer.maxlength + 5), self.mysql_backend)) self.sql('CREATE INDEX words_word_ids ON __words(_word)') self.sql('CREATE INDEX words_by_id ON __words (_textid)') self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' '__textids (_class, _itemid, _prop)') sql = 'insert into ids (name, num) values (%s,%s)' % (self.arg, self.arg) self.sql(sql, ('__textids', 1)) def add_new_columns_v2(self): '''While we're adding the actor column, we need to update the tables to have the correct datatypes.''' for klass in self.classes.values(): cn = klass.classname properties = klass.getprops() old_spec = self.database_schema['tables'][cn] # figure the non-Multilink properties to copy over propnames = ['activity', 'creation', 'creator'] # figure actions based on data type for name, s_prop in old_spec[1]: # s_prop is a repr() string of a hyperdb type object if s_prop.find('Multilink') == -1: if name in properties: propnames.append(name) continue tn = '%s_%s' % (cn, name) if name in properties: # grabe the current values sql = 'select linkid, nodeid from %s' % tn self.sql(sql) rows = self.cursor.fetchall() # drop the old table self.drop_multilink_table_indexes(cn, name) sql = 'drop table %s' % tn self.sql(sql) if name in properties: # re-create and populate the new table self.create_multilink_table(klass, name) sql = '''insert into %s (linkid, nodeid) values (%s, %s)''' % (tn, self.arg, self.arg) for linkid, nodeid in rows: self.sql(sql, (int(linkid), int(nodeid))) # figure the column names to fetch fetch = ['_%s' % name for name in propnames] # select the data out of the old table fetch.append('id') fetch.append('__retired__') fetchcols = ','.join(fetch) sql = 'select %s from _%s' % (fetchcols, cn) self.sql(sql) # unserialise the old data olddata = [] propnames = propnames + ['id', '__retired__'] cols = [] first = 1 for entry in self.cursor.fetchall(): l = [] olddata.append(l) for i in range(len(propnames)): name = propnames[i] v = entry[i] if name in ('id', '__retired__'): if first: cols.append(name) l.append(int(v)) continue if first: cols.append('_' + name) prop = properties[name] if isinstance(prop, hyperdb.Date) and v is not None: v = date.Date(v) elif isinstance(prop, hyperdb.Interval) and v is not None: v = date.Interval(v) elif isinstance(prop, hyperdb.Password) and v is not None: v = password.Password(encrypted=v, config=self.config) elif isinstance(prop, hyperdb.Integer) and v is not None: v = int(v) elif (isinstance(prop, hyperdb.Boolean) or isinstance(prop, hyperdb.Number)) and v is not None: v = float(v) # convert to new MySQL data type prop = properties[name] if v is not None: e = self.to_sql_value(prop.__class__)(v) else: e = None l.append(e) # Intervals store the seconds value too if isinstance(prop, hyperdb.Interval): if first: cols.append('__' + name + '_int__') if v is not None: l.append(v.as_seconds()) else: l.append(e) first = 0 self.drop_class_table_indexes(cn, old_spec[0]) # drop the old table self.sql('drop table _%s' % cn) # create the new table self.create_class_table(klass) # do the insert of the old data args = ','.join([self.arg for x in cols]) cols = ','.join(cols) sql = 'insert into _%s (%s) values (%s)' % (cn, cols, args) for entry in olddata: self.sql(sql, tuple(entry)) # now load up the old journal data to migrate it cols = ','.join('nodeid date tag action params'.split()) sql = 'select %s from %s__journal' % (cols, cn) self.sql(sql) # data conversions olddata = [] for nodeid, journaldate, journaltag, action, params in \ self.cursor.fetchall(): # nodeid = int(nodeid) journaldate = date.Date(journaldate) # params = eval(params) olddata.append((nodeid, journaldate, journaltag, action, params)) # drop journal table and indexes self.drop_journal_table_indexes(cn) sql = 'drop table %s__journal' % cn self.sql(sql) # re-create journal table self.create_journal_table(klass) dc = self.to_sql_value(hyperdb.Date) for nodeid, journaldate, journaltag, action, params in olddata: self.save_journal(cn, cols, nodeid, dc(journaldate), journaltag, action, params) # make sure the normal schema update code doesn't try to # change things self.database_schema['tables'][cn] = klass.schema() def fix_version_2_tables(self): # Convert journal date column to TIMESTAMP, params column to TEXT self._convert_journal_tables() # Convert all String properties to TEXT self._convert_string_properties() def fix_version_5_tables(self): # A bug caused the __key_retired_idx to be missing # unless the database was upgraded from version 4 to 5. # If it was created at version 5, the index is missing. # The user class is always present and has a key. # Check it for the index. If missing, add index to all # classes by rerunning self.fix_version_4_tables(). # if this fails abort. Probably means no user class # so we should't be doing anything. if not self.sql_index_exists("_user", "_user_key_retired_idx"): self.fix_version_4_tables() else: self.log_info('No changes needed.') def fix_version_6_tables(self): # Modify length for __words._word column. c = self.cursor sql = "alter table __words change column _word _word varchar(%s)" % ( self.arg) # Why magic number 5? It was the original offset between # column length and maxlength. c.execute(sql, (self.indexer.maxlength + 5,)) def fix_version_7_tables(self): # Modify type for session.session_time/otk.otk_time column. sql = "alter table sessions modify session_time double" self.sql(sql) sql = "alter table otks modify otk_time double" self.sql(sql) def __repr__(self): return '' % id(self) def sql_fetchone(self): return self.cursor.fetchone() def sql_fetchall(self): return self.cursor.fetchall() def sql_index_exists(self, table_name, index_name): self.sql('show index from %s' % table_name) for index in self.cursor.fetchall(): if index[2] == index_name: return 1 return 0 def create_class_table(self, spec, create_sequence=1): cols, mls = self.determine_columns(spec.properties.items()) # add on our special columns cols.append(('id', 'INTEGER PRIMARY KEY')) cols.append(('__retired__', 'INTEGER DEFAULT 0')) # create the base table scols = ','.join(['%s %s' % x for x in cols]) sql = 'create table _%s (%s) ENGINE=%s' % (spec.classname, scols, self.mysql_backend) self.sql(sql) self.create_class_table_indexes(spec) return cols, mls def create_class_table_indexes(self, spec): ''' create the class table for the given spec ''' # create __retired__ index index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)' % ( spec.classname, spec.classname) self.sql(index_sql2) # create index for key property if spec.key: if isinstance(spec.properties[spec.key], hyperdb.String): idx = spec.key + '(255)' else: idx = spec.key index_sql3 = 'create index _%s_%s_idx on _%s(_%s)' % ( spec.classname, spec.key, spec.classname, idx) self.sql(index_sql3) # and the unique index for key / retired(id) self.add_class_key_required_unique_constraint(spec.classname, spec.key) # TODO: create indexes on (selected?) Link property columns, as # they're more likely to be used for lookup def add_class_key_required_unique_constraint(self, cn, key): # mysql requires sizes on TEXT indexes prop = self.classes[cn].getprops()[key] if isinstance(prop, hyperdb.String): sql = '''create unique index _%s_key_retired_idx on _%s(__retired__, _%s(255))''' % (cn, cn, key) else: sql = '''create unique index _%s_key_retired_idx on _%s(__retired__, _%s)''' % (cn, cn, key) self.sql(sql) def create_class_table_key_index(self, cn, key): # mysql requires sizes on TEXT indexes prop = self.classes[cn].getprops()[key] if isinstance(prop, hyperdb.String): sql = 'create index _%s_%s_idx on _%s(_%s(255))' % (cn, key, cn, key) else: sql = 'create index _%s_%s_idx on _%s(_%s)' % (cn, key, cn, key) self.sql(sql) def drop_class_table_indexes(self, cn, key): # drop the old table indexes first l = ['_%s_id_idx' % cn, '_%s_retired_idx' % cn] if key: l.append('_%s_%s_idx' % (cn, key)) table_name = '_%s' % cn for index_name in l: if not self.sql_index_exists(table_name, index_name): continue index_sql = 'drop index %s on %s' % (index_name, table_name) self.sql(index_sql) def create_journal_table(self, spec): ''' create the journal table for a class given the spec and already-determined cols ''' # journal table sql = '''create table %s__journal ( nodeid integer, date datetime, tag varchar(255), action varchar(255), params text) ENGINE=%s''' % ( spec.classname, self.mysql_backend) self.sql(sql) self.create_journal_table_indexes(spec) def drop_journal_table_indexes(self, classname): index_name = '%s_journ_idx' % classname if not self.sql_index_exists('%s__journal' % classname, index_name): return index_sql = 'drop index %s on %s__journal' % (index_name, classname) self.sql(index_sql) def create_multilink_table(self, spec, ml): sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255), nodeid VARCHAR(255)) ENGINE=%s''' % (spec.classname, ml, self.mysql_backend) self.sql(sql) self.create_multilink_table_indexes(spec, ml) def drop_multilink_table_indexes(self, classname, ml): l = [ '%s_%s_l_idx' % (classname, ml), '%s_%s_n_idx' % (classname, ml) ] table_name = '%s_%s' % (classname, ml) for index_name in l: if not self.sql_index_exists(table_name, index_name): continue sql = 'drop index %s on %s' % (index_name, table_name) self.sql(sql) 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 %s on %s' % (index_name, table_name) self.sql(sql) # and now the retired unique index too index_name = '_%s_key_retired_idx' % cn if self.sql_index_exists(table_name, index_name): sql = 'drop index %s on _%s' % (index_name, cn) self.sql(sql) # old-skool id generation def newid(self, classname): ''' Generate a new id for the given class ''' # get the next ID - "FOR UPDATE" will lock the row for us sql = 'select num from ids where name=%s FOR UPDATE' % self.arg self.sql(sql, (classname, )) newid = int(self.cursor.fetchone()[0]) # update the counter sql = 'update ids set num=%s where name=%s' % (self.arg, self.arg) vals = (int(newid)+1, classname) self.sql(sql, vals) # return as string return str(newid) def setid(self, classname, setid): ''' Set the id counter: used during import of database We add one to make it behave like the seqeunces in postgres. ''' sql = 'update ids set num=%s where name=%s' % (self.arg, self.arg) vals = (int(setid)+1, classname) self.sql(sql, vals) def clear(self): rdbms_common.Database.clear(self) # set the id counters to 0 (setid adds one) so we start at 1 for cn in self.classes.keys(): self.setid(cn, 0) def create_class(self, spec): rdbms_common.Database.create_class(self, spec) sql = 'insert into ids (name, num) values (%s, %s)' vals = (spec.classname, 1) self.sql(sql, vals) def sql_commit(self): ''' Actually commit to the database. ''' self.log_info('commit') # MySQL commits don't seem to ever fail, the latest update winning. # makes you wonder why they have transactions... self.conn.commit() # open a new cursor for subsequent work self.cursor = self.conn.cursor() # make sure we're in a new transaction and not autocommitting self.sql("SET AUTOCOMMIT=0") self.sql("START TRANSACTION") def sql_close(self): self.log_info('close') try: self.conn.close() # issue2551025: with revision 1.3.14 of mysqlclient. # It looks like you can get an OperationalError 2006 # raised for closing a closed handle. except MySQLdb.OperationalError as message: if str(message) != "(2006, '')": # close connection raise except MySQLdb.ProgrammingError as message: if str(message) != 'closing a closed connection': raise class MysqlClass: case_sensitive_equal = 'COLLATE utf8_bin =' # TODO: AFAIK its version dependent for MySQL supports_subselects = False def _subselect(self, proptree): ''' "I can't believe it's not a toy RDBMS" see, even toy RDBMSes like gadfly and sqlite can do sub-selects... ''' classname = proptree.parent.classname # noqa: E221 multilink_table = proptree.propclass.table_name # noqa: E221 nodeid_name = proptree.propclass.nodeid_name # noqa: E221 linkid_name = proptree.propclass.linkid_name # noqa: E221 w = '' if proptree.need_retired: w = ' where %s.__retired__=0' % (multilink_table) if proptree.need_child_retired: tn1 = multilink_table tn2 = '_' + proptree.classname w = ', %s where %s.%s=%s.id and %s.__retired__=0' % ( tn2, tn1, linkid_name, tn2, tn2) self.db.sql('select %s from %s%s' % (nodeid_name, multilink_table, w)) s = ','.join([str(x[0]) for x in self.db.sql_fetchall()]) return '_%s.id not in (%s)' % (classname, s) def create_inner(self, **propvalues): try: return rdbms_common.Class.create_inner(self, **propvalues) except MySQLdb.IntegrityError as e: self._handle_integrity_error(e, propvalues) def set_inner(self, nodeid, **propvalues): try: return rdbms_common.Class.set_inner(self, nodeid, **propvalues) except MySQLdb.IntegrityError as e: self._handle_integrity_error(e, propvalues) def _handle_integrity_error(self, e, propvalues): ''' Handle a MySQL IntegrityError. If the error is recognized, then it may be converted into an alternative exception. Otherwise, it is raised unchanged from this function.''' # There are checks in create_inner/set_inner to see if a node # is being created with the same key as an existing node. # But, there is a race condition -- we may pass those checks, # only to find out that a parallel session has created the # node by by the time we actually issue the SQL command to # create the node. Fortunately, MySQL gives us a unique error # code for this situation, so we can detect it here and handle # it appropriately. # # The details of the race condition are as follows, where # "X" is a classname, and the term "thread" is meant to # refer generically to both threads and processes: # # Thread A Thread B # -------- -------- # read table for X # create new X object # commit # create new X object # # In Thread B, the check in create_inner does not notice that # the new X object is a duplicate of that committed in Thread # A because MySQL's default "consistent nonlocking read" # behavior means that Thread B sees a snapshot of the database # at the point at which its transaction began -- which was # before Thread A created the object. However, the attempt # to *write* to the table for X, creating a duplicate entry, # triggers an error at the point of the write. # # If both A and B's transaction begins with creating a new X # object, then this bug cannot occur because creating the # object requires getting a new ID, and newid() locks the id # table until the transaction is committed or rolledback. So, # B will block until A's commit is complete, and will not # actually get its snapshot until A's transaction completes. # But, if the transaction has begun prior to calling newid, # then the snapshot has already been established. if e[0] == ER.DUP_ENTRY: key = propvalues[self.key] raise ValueError('node with key "%s" exists' % key) # We don't know what this exception is; reraise it. raise class Class(MysqlClass, rdbms_common.Class): pass class IssueClass(MysqlClass, rdbms_common.IssueClass): pass class FileClass(MysqlClass, rdbms_common.FileClass): pass # vim: set et sts=4 sw=4 : code-2a6c3eb4e059/roundup/backends/back_postgresql.py0000644000000000000000000005324514571501054020746 0ustar 00000000000000# Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev # # This module is free software, and you may redistribute it and/or modify # under the same terms as Python, so long as this copyright message and # disclaimer are retained in their original form. # '''Postgresql backend via psycopg2 for Roundup.''' __docformat__ = 'restructuredtext' import logging import os import re import shutil import time ISOLATION_LEVEL_READ_UNCOMMITTED = None ISOLATION_LEVEL_READ_COMMITTED = None ISOLATION_LEVEL_REPEATABLE_READ = None ISOLATION_LEVEL_SERIALIZABLE = None import psycopg2 # noqa: E402 from psycopg2 import ProgrammingError # noqa: E402 from psycopg2.extensions import QuotedString # noqa: E402 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED # noqa: F401 E402 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED # noqa: E402 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ # noqa: E402 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE # noqa: E402 from psycopg2.extensions import TransactionRollbackError # noqa: F401 E402 from roundup import hyperdb # noqa: E402 from roundup.backends import rdbms_common # noqa: E402 from roundup.backends import sessions_rdbms # noqa: E402 isolation_levels = { 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED, 'read committed': ISOLATION_LEVEL_READ_COMMITTED, 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ, 'serializable': ISOLATION_LEVEL_SERIALIZABLE } def connection_dict(config, dbnamestr=None): ''' read_default_group is MySQL-specific, ignore it ''' d = rdbms_common.connection_dict(config, dbnamestr) if 'read_default_group' in d: del d['read_default_group'] if 'read_default_file' in d: del d['read_default_file'] return d def _db_schema_split(database_name): ''' Split database_name into database and schema parts''' if '.' in database_name: return database_name.split ('.') return (database_name, '') def db_create(config): """Clear all database contents and drop database itself""" db_name, schema_name = get_database_schema_names(config) if not schema_name: command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % db_name if config.RDBMS_TEMPLATE: command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE logging.getLogger('roundup.hyperdb').info(command) db_command(config, command) else: command = "CREATE SCHEMA \"%s\" AUTHORIZATION \"%s\"" % ( schema_name, get_database_user_name(config)) logging.getLogger('roundup.hyperdb').info(command) db_command(config, command, db_name) def db_nuke(config): """Drop the database (and all its contents) or the schema.""" db_name, schema_name = get_database_schema_names(config) if not schema_name: command = 'DROP DATABASE "%s"'% db_name logging.getLogger('roundup.hyperdb').info(command) db_command(config, command) else: command = 'DROP SCHEMA "%s" CASCADE' % schema_name logging.getLogger('roundup.hyperdb').info(command) db_command(config, command, db_name) if os.path.exists(config.DATABASE): shutil.rmtree(config.DATABASE) def get_database_schema_names(config): '''Get database and schema names using config.RDBMS_NAME or service defined by config.RDBMS_SERVICE. If database specifed using RDBMS_SERVICE does not exist, the error message is parsed for the database name. This database can then be created by calling code. Parsing will fail if the error message changes. The alternative is to try to find and parse the .pg_service .ini style file on unix/windows. This is less palatable. If the database specified using RDBMS_SERVICE exists, (e.g. we are doing a nuke operation), use psycopg.extensions.ConnectionInfo to get the dbname. Also parse the search_path options setting to get the schema. Only the first element of the search_path is returned. This requires psycopg2 > 2.8 from 2018. ''' if config.RDBMS_NAME: return _db_schema_split(config.RDBMS_NAME) template1 = connection_dict(config) try: conn = psycopg2.connect(**template1) except psycopg2.OperationalError as message: # extract db name from error: # 'connection to server at "127.0.0.1", port 5432 failed: \ # FATAL: database "rounduptest" does not exist\n' # ugh. # # Database name is any character sequence not including a " or # whitespace. Arguably both are allowed by: # # https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS # # with suitable quoting but ... really. search = re.search( r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist', message.args[0]) if search: dbname = search.groups()[0] # To use a schema, the db has to have been precreated. # So return '' for schema if database does not exist. return (dbname, '') raise hyperdb.DatabaseError( "Unable to determine database from service: %s" % message) dbname = psycopg2.extensions.ConnectionInfo(conn).dbname schema = '' options = psycopg2.extensions.ConnectionInfo(conn).options conn.close() # Assume schema is first word in the search_path spec. # , (for multiple items in path) and whitespace (for another option) # end the schema name. m = re.search(r'search_path=([^,\s]*)', options) if m: schema = m.group(1) if not schema: raise ValueError('Unable to get schema for service: "%s" from options: "%s"' % (template1['service'], options)) return (dbname, schema) def get_database_user_name(config): '''Get database username using config.RDBMS_USER or return user from connection created using config.RDBMS_SERVICE. If the database specified using RDBMS_SERVICE does exist, (i.e. we are doing a nuke operation), use psycopg.extensions.ConnectionInfo to get the user. This requires psycopg2 > 2.8 from 2018. ''' if config.RDBMS_USER: return config.RDBMS_USER template1 = connection_dict(config) try: conn = psycopg2.connect(**template1) except psycopg2.OperationalError as message: # extract db name from error: # 'connection to server at "127.0.0.1", port 5432 failed: \ # FATAL: database "rounduptest" does not exist\n' # ugh. # # Database name is any character sequence not including a " or # whitespace. Arguably both are allowed by: # # https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS # # with suitable quoting but ... really. search = re.search( r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist', message.args[0]) if search: dbname = search.groups()[0] # To have a user, the db has to exist already. # so return '' for user. return '' raise hyperdb.DatabaseError( "Unable to determine database from service: %s" % message) user = psycopg2.extensions.ConnectionInfo(conn).user conn.close() return user def db_command(config, command, database='postgres'): '''Perform some sort of database-level command. Retry 10 times if we fail by conflicting with another user. Since PostgreSQL version 8.1 there is a database "postgres", before "template1" seems to have been used, so we fall back to it. Compare to issue2550543. ''' template1 = connection_dict(config, 'database') template1['database'] = database try: conn = psycopg2.connect(**template1) except psycopg2.OperationalError as message: if re.search(r'database ".+" does not exist', str(message)): return db_command(config, command, database='template1') raise hyperdb.DatabaseError(message) conn.set_isolation_level(0) cursor = conn.cursor() try: for _n in range(10): if pg_command(cursor, command): return finally: conn.close() raise RuntimeError('10 attempts to create database or schema failed when running: %s' % command) def pg_command(cursor, command, args=()): '''Execute the postgresql command, which may be blocked by some other user connecting to the database, and return a true value if it succeeds. If there is a concurrent update, retry the command. ''' try: cursor.execute(command, args) except psycopg2.DatabaseError as err: response = str(err).split('\n')[0] if "FATAL" not in response: msgs = ( 'is being accessed by other users', 'could not serialize access due to concurrent update', ) for msg in msgs: if msg in response: time.sleep(0.1) return 0 raise RuntimeError(response, command, args) return 1 def db_exists(config): """Check if database or schema already exists""" db = connection_dict(config, 'database') db_name, schema_name = get_database_schema_names(config) if schema_name: db['database'] = db_name try: conn = psycopg2.connect(**db) if not schema_name: conn.close() return 1 except Exception: return 0 # will have a non-false value here; otherwise one # of the above returns would have returned. # Get a count of the number of schemas named (either 0 or 1). command = "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name = %s" cursor = conn.cursor() pg_command(cursor, command, (schema_name,)) count = cursor.fetchall()[0][0] conn.close() return count # 'count' will be 0 or 1. class Sessions(sessions_rdbms.Sessions): def set(self, *args, **kwargs): try: sessions_rdbms.Sessions.set(self, *args, **kwargs) except ProgrammingError as err: response = str(err).split('\n')[0] if -1 != response.find('ERROR') and \ -1 != response.find('could not serialize access due to concurrent update'): # another client just updated, and we're running on # serializable isolation. # see http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html self.db.rollback() class Database(rdbms_common.Database): """Postgres DB backend implementation attributes: dbtype: holds the value for the type of db. It is used by indexer to identify the database type so it can import the correct indexer module when using native text search mode. import_savepoint_count: count the number of savepoints that have been created during import. Once the limit of savepoints is reached, a commit is done and this is reset to 0. """ arg = '%s' dbtype = "postgres" import_savepoint_count = 0 # Value is set from roundup-admin using db.config["RDBMS_SAVEPOINT_LIMIT"] # or to the default of 10_000 at runtime. Use 0 here to trigger # initialization. savepoint_limit = 0 # used by some code to switch styles of query implements_intersect = 1 def sql_open_connection(self): db = connection_dict(self.config, 'database') db_name, schema_name = get_database_schema_names(self.config) if schema_name: db['database'] = db_name # database option always present: log it if not null if db['database']: logging.getLogger('roundup.hyperdb').info( 'open database %r' % db['database']) if 'service' in db: # only log if used logging.getLogger('roundup.hyperdb').info( 'open database via service %r' % db['service']) try: conn = psycopg2.connect(**db) except psycopg2.OperationalError as message: raise hyperdb.DatabaseError(message) cursor = conn.cursor() if ISOLATION_LEVEL_REPEATABLE_READ is not None: lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL] conn.set_isolation_level(lvl) if schema_name: self.sql ('SET search_path TO %s' % schema_name, cursor=cursor) # Commit is required so that a subsequent rollback # will not also rollback the search_path change. self.sql ('COMMIT', cursor=cursor) return (conn, cursor) def sql_new_cursor(self, name='default', conn=None, *args, **kw): """ Create new cursor, this may need additional parameters for performance optimization for different backends. """ use_name = self.config.RDBMS_SERVERSIDE_CURSOR kw = {} if use_name: kw['name'] = name if conn is None: conn = self.conn return conn.cursor(*args, **kw) def open_connection(self): if not db_exists(self.config): db_create(self.config) self.conn, self.cursor = self.sql_open_connection() try: self.load_dbschema() except ProgrammingError as message: if str(message).find('schema') == -1: raise self.rollback() self.init_dbschema() self.sql("CREATE TABLE schema (schema TEXT)") self.sql("CREATE TABLE dual (dummy integer)") self.sql("insert into dual values (1)") self.create_version_2_tables() self.fix_version_3_tables() # Need to commit here, otherwise otk/session will not find # the necessary tables (in a parallel connection!) self.commit() self._add_fts_table() self.commit() def checkpoint_data(self, savepoint="importing"): """Create a subtransaction savepoint. Allows recovery/retry of operation in exception handler because postgres requires a rollback in case of error generating exception. Used with restore_connection_on_error to handle uniqueness conflict in import_table(). Savepoints take memory resources. Postgres keeps all savepoints (rather than overwriting) until a commit(). Commit every ~10,000 savepoints to prevent running out of memory on import. NOTE: a commit outside of this method will not reset the import_savepoint_count. This can result in an unneeded commit on a new cursor (that has no savepoints) as there is no way to find out if there is a savepoint or how many savepoints are opened on a db connection/cursor. Because an import is a one shot deal and not part of a long running daemon (e.g. the roundup-server), I am not too worried about it. It will just slow the import down a tad. """ self.sql('SAVEPOINT %s' % savepoint) self.import_savepoint_count += 1 if not self.savepoint_limit: if "RDBMS_SAVEPOINT_LIMIT" in self.config.keys(): # note this config option is created on the fly # by admin.py::do_import. It is never listed in # config.ini. self.savepoint_limit = self.config["RDBMS_SAVEPOINT_LIMIT"] else: self.savepoint_limit = 10000 if self.import_savepoint_count > self.savepoint_limit: # track savepoints and commit every 10000 (or user value) # so we don't run postgres out of memory. An import of a # customer's tracker ran out of memory after importing # ~23000 items with: psycopg2.errors.OutOfMemory: out of # shared memory HINT: You might need to increase # max_locks_per_transaction. self.commit() self.import_savepoint_count = 0 def restore_connection_on_error(self, savepoint="importing"): """Postgres leaves a connection/cursor in an unusable state after an error. Rollback the transaction to a previous savepoint and permit a retry of the failed statement. Used with checkpoint_data to handle uniqueness conflict in import_table(). """ self.sql('ROLLBACK TO %s' % savepoint) def create_version_2_tables(self): # OTK store self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), otk_value TEXT, otk_time float)''') self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') # Sessions store self.sql('''CREATE TABLE sessions ( session_key VARCHAR(255), session_time float, session_value TEXT)''') self.sql('''CREATE INDEX sessions_key_idx ON sessions(session_key)''') # full-text indexing store self.sql('CREATE SEQUENCE ___textids_ids') self.sql('''CREATE TABLE __textids ( _textid integer primary key, _class VARCHAR(255), _itemid VARCHAR(255), _prop VARCHAR(255))''') self.sql('''CREATE TABLE __words (_word VARCHAR(%s), _textid integer)''' % (self.indexer.maxlength + 5)) self.sql('CREATE INDEX words_word_idx ON __words(_word)') self.sql('CREATE INDEX words_by_id ON __words (_textid)') self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' '__textids (_class, _itemid, _prop)') def fix_version_2_tables(self): # Convert journal date column to TIMESTAMP, params column to TEXT self._convert_journal_tables() # Convert all String properties to TEXT self._convert_string_properties() # convert session / OTK *_time columns to REAL for name in ('otk', 'session'): self.sql('drop index %ss_key_idx' % name) self.sql('drop table %ss' % name) self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255), %s_value VARCHAR(255), %s_time REAL)''' % (name, name, name, name)) self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)' % (name, name, name)) def fix_version_3_tables(self): rdbms_common.Database.fix_version_3_tables(self) self.sql('''CREATE INDEX words_both_idx ON __words USING btree (_word, _textid)''') def _add_fts_table(self): self.sql( 'CREATE TABLE __fts (_class VARCHAR(255), ' '_itemid VARCHAR(255), _prop VARCHAR(255), _tsv tsvector)' ) self.sql('CREATE INDEX __fts_idx ON __fts USING GIN (_tsv)') def fix_version_6_tables(self): # Modify length for __words._word column. c = self.cursor sql = 'alter table __words alter column _word type varchar(%s)' % ( self.arg) # Why magic number 5? It was the original offset between # column length and maxlength. c.execute(sql, (self.indexer.maxlength + 5,)) self._add_fts_table() def fix_version_7_tables(self): # Modify type for session.session_time/otk.otk_time column. # float is double precision 15 signifcant digits sql = 'alter table sessions alter column session_time type float' self.sql(sql) sql = 'alter table otks alter column otk_time type float' self.sql(sql) def add_new_columns_v2(self): # update existing tables to have the new actor column tables = self.database_schema['tables'] for name in tables: self.sql('ALTER TABLE _%s add __actor VARCHAR(255)' % name) def __repr__(self): return '' % id(self) def sql_stringquote(self, value): ''' psycopg2.QuotedString returns a "buffer" object with the single-quotes around it... ''' return str(QuotedString(str(value)))[1:-1] def sql_index_exists(self, table_name, index_name): sql = 'select count(*) from pg_indexes where ' \ 'tablename=%s and indexname=%s' % (self.arg, self.arg) self.sql(sql, (table_name, index_name)) return self.cursor.fetchone()[0] def create_class_table(self, spec, create_sequence=1): if create_sequence: sql = 'CREATE SEQUENCE _%s_ids' % spec.classname self.sql(sql) return rdbms_common.Database.create_class_table(self, spec) def drop_class_table(self, cn): sql = 'drop table _%s' % cn self.sql(sql) sql = 'drop sequence _%s_ids' % cn self.sql(sql) def newid(self, classname): sql = "select nextval('_%s_ids') from dual" % classname self.sql(sql) return str(self.cursor.fetchone()[0]) def setid(self, classname, setid): sql = "select setval('_%s_ids', %s) from dual" % (classname, int(setid)) self.sql(sql) def clear(self): rdbms_common.Database.clear(self) # reset the sequences for cn in self.classes: self.cursor.execute('DROP SEQUENCE _%s_ids' % cn) self.cursor.execute('CREATE SEQUENCE _%s_ids' % cn) class PostgresqlClass: order_by_null_values = '(%s is not NULL)' case_insensitive_like = 'ILIKE' class Class(PostgresqlClass, rdbms_common.Class): pass class IssueClass(PostgresqlClass, rdbms_common.IssueClass): pass class FileClass(PostgresqlClass, rdbms_common.FileClass): pass # vim: set et sts=4 sw=4 : code-2a6c3eb4e059/roundup/backends/back_sqlite.py0000644000000000000000000005346514571501054020050 0ustar 00000000000000"""Implements a backend for SQLite. See https://pysqlite.sourceforge.net/ for pysqlite info NOTE: we use the rdbms_common table creation methods which define datatypes for the columns, but sqlite IGNORES these specifications. """ __docformat__ = 'restructuredtext' import logging import os import shutil import time from roundup import hyperdb, date, password from roundup.backends import rdbms_common from roundup.backends import sessions_sqlite from roundup.backends import sessions_dbm try: from roundup.backends import sessions_redis except ImportError: sessions_redis = None from roundup.anypy.strings import uany2s sqlite_version = None try: import sqlite3 as sqlite sqlite_version = 3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite if sqlite.version_info < (2, 1, 0): raise ValueError('pysqlite2 minimum version is 2.1.0+ ' '- %s found' % sqlite.version) sqlite_version = 2 except ImportError: import sqlite sqlite_version = 1 def db_exists(config): return os.path.exists(os.path.join(config.DATABASE, 'db')) def db_nuke(config): shutil.rmtree(config.DATABASE) class Database(rdbms_common.Database): """Sqlite DB backend implementation attributes: dbtype: holds the value for the type of db. It is used by indexer to identify the database type so it can import the correct indexer module when using native text search mode. """ # char to use for positional arguments if sqlite_version in (2, 3): arg = '?' else: arg = '%s' dbtype = "sqlite" # used by some code to switch styles of query implements_intersect = 1 # used in generic backend to determine if db supports # 'DOUBLE PRECISION' for floating point numbers. Note that sqlite # already has double precision as its standard 'REAL' type. So this # is set to False here. implements_double_precision = False hyperdb_to_sql_datatypes = { hyperdb.String : 'VARCHAR(255)', # noqa: E203 hyperdb.Date : 'VARCHAR(30)', # noqa: E203 hyperdb.Link : 'INTEGER', # noqa: E203 hyperdb.Interval : 'VARCHAR(255)', # noqa: E203 hyperdb.Password : 'VARCHAR(255)', # noqa: E203 hyperdb.Boolean : 'BOOLEAN', # noqa: E203 hyperdb.Number : 'REAL', # noqa: E203 hyperdb.Integer : 'INTEGER', # noqa: E203 } hyperdb_to_sql_value = { hyperdb.String : str, # noqa: E203 hyperdb.Date : lambda x: x.serialise(), # noqa: E203 hyperdb.Link : int, # noqa: E203 hyperdb.Interval : str, # noqa: E203 hyperdb.Password : str, # noqa: E203 hyperdb.Boolean : int, # noqa: E203 hyperdb.Integer : int, # noqa: E203 hyperdb.Number : lambda x: x, # noqa: E203 hyperdb.Multilink : lambda x: x, # used in journal marshalling, # noqa: E203 } sql_to_hyperdb_value = { hyperdb.String : uany2s, # noqa: E203 hyperdb.Date : lambda x: date.Date(str(x)), # noqa: E203 hyperdb.Link : str, # XXX numeric ids # noqa: E203 hyperdb.Interval : date.Interval, # noqa: E203 hyperdb.Password : lambda x: password.Password(encrypted=x), # noqa: E203 hyperdb.Boolean : int, # noqa: E203 hyperdb.Integer : int, # noqa: E203 hyperdb.Number : rdbms_common._num_cvt, # noqa: E203 hyperdb.Multilink : lambda x: x, # used in journal marshalling, # noqa: E203 } # We can use DBM, redis or SQLite for managing session info and # one-time keys: # For SQL database storage of this info we have to create separate # databases for Otk and Session because SQLite doesn't support # concurrent connections to the same database. def getSessionManager(self): if not self.Session: if self.config.SESSIONDB_BACKEND == "redis": if sessions_redis is None: self.Session = sessions_sqlite.Sessions(self) raise ValueError("[redis] session is set, but " "redis module is not found") self.Session = sessions_redis.Sessions(self) elif self.config.SESSIONDB_BACKEND == "anydbm": self.Session = sessions_dbm.Sessions(self) else: self.Session = sessions_sqlite.Sessions(self) return self.Session def getOTKManager(self): if not self.Otk: if self.config.SESSIONDB_BACKEND == "redis": if sessions_redis is None: self.Session = sessions_sqlite.OneTimeKeys(self) raise ValueError("[redis] session is set, but " "redis is not found") self.Otk = sessions_redis.OneTimeKeys(self) elif self.config.SESSIONDB_BACKEND == "anydbm": self.Otk = sessions_dbm.OneTimeKeys(self) else: self.Otk = sessions_sqlite.OneTimeKeys(self) return self.Otk def sqlite_busy_handler(self, data, table, count): """invoked whenever SQLite tries to access a database that is locked""" now = time.time() if count == 1: # Timeout for handling locked database (default 30s) self._busy_handler_endtime = now + self.config.RDBMS_SQLITE_TIMEOUT elif now > self._busy_handler_endtime: # timeout expired - no more retries return 0 # sleep adaptively as retry count grows, # starting from about half a second time_to_sleep = 0.01 * (2 << min(5, count)) time.sleep(time_to_sleep) return 1 def sql_open_connection(self, dbname=None): """Open a standard, non-autocommitting connection. pysqlite will automatically BEGIN TRANSACTION for us. """ # make sure the database directory exists # database itself will be created by sqlite if needed if not os.path.isdir(self.config.DATABASE): os.makedirs(self.config.DATABASE) if dbname: db = os.path.join(self.config.DATABASE, 'db-' + dbname) else: db = os.path.join(self.config.DATABASE, 'db') logging.getLogger('roundup.hyperdb').info('open database %r' % db) # set timeout (30 second default is extraordinarily generous) # for handling locked database if sqlite_version == 1: conn = sqlite.connect(db=db) conn.db.sqlite_busy_handler(self.sqlite_busy_handler) else: conn = sqlite.connect(db, timeout=self.config.RDBMS_SQLITE_TIMEOUT) conn.row_factory = sqlite.Row # pysqlite2 / sqlite3 want us to store Unicode in the db but # that's not what's been done historically and it's definitely # not what the other backends do, so we'll stick with UTF-8 if sqlite_version in (2, 3): conn.text_factory = str cursor = conn.cursor() return (conn, cursor) def open_connection(self): # ensure files are group readable and writable os.umask(self.config.UMASK) (self.conn, self.cursor) = self.sql_open_connection() try: self.load_dbschema() except sqlite.DatabaseError as error: if str(error) != 'no such table: schema': raise self.init_dbschema() self.sql('create table schema (schema varchar)') self.sql('create table ids (name varchar, num integer)') self.sql('create index ids_name_idx on ids(name)') self.create_version_2_tables() self._add_fts5_table() # Set journal mode to WAL. self.sql_commit() # close out rollback journal/transaction self.sql('pragma journal_mode=wal') # set wal self.sql_commit() # close out rollback and commit wal change def create_version_2_tables(self): self.sql('create table otks (otk_key varchar, ' 'otk_value varchar, otk_time integer)') self.sql('create index otks_key_idx on otks(otk_key)') self.sql('create table sessions (session_key varchar, ' 'session_time integer, session_value varchar)') self.sql('create index sessions_key_idx on ' 'sessions(session_key)') # full-text indexing store self.sql('CREATE TABLE __textids (_class varchar, ' '_itemid varchar, _prop varchar, _textid' ' integer primary key) ') self.sql('CREATE TABLE __words (_word varchar, ' '_textid integer)') self.sql('CREATE INDEX words_word_ids ON __words(_word)') self.sql('CREATE INDEX words_by_id ON __words (_textid)') self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' '__textids (_class, _itemid, _prop)') sql = 'insert into ids (name, num) values (%s,%s)' % ( self.arg, self.arg) self.sql(sql, ('__textids', 1)) def add_new_columns_v2(self): # update existing tables to have the new actor column tables = self.database_schema['tables'] for classname, spec in self.classes.items(): if classname in tables: dbspec = tables[classname] self.update_class(spec, dbspec, force=1, adding_v2=1) # we've updated - don't try again tables[classname] = spec.schema() def fix_version_3_tables(self): # NOOP - no restriction on column length here pass def _add_fts5_table(self): try: self.sql('CREATE virtual TABLE __fts USING fts5(_class, ' '_itemid, _prop, _textblob)') except sqlite.OperationalError: available_options = self.cursor.execute( 'pragma compile_options;').fetchall() if 'ENABLE_FTS5' in [opt['compile_options'] for opt in available_options]: # sqlite supports FTS5 something else has gone wrong raise else: # report a useful error message raise NotImplementedError( "This version of SQLite was not built with support " "for FTS5. SQLite version: %s" % sqlite.sqlite_version) def fix_version_6_tables(self): # note sqlite has no limit on column size so v6 fixes # to __words._word length are not needed. # Add native full-text indexing table self._add_fts5_table() def update_class(self, spec, old_spec, force=0, adding_v2=0): """ Determine the differences between the current spec and the database version of the spec, and update where necessary. If 'force' is true, update the database anyway. SQLite doesn't have ALTER TABLE, so we have to copy and regenerate the tables with the new schema. """ new_spec = spec.schema() new_spec[1].sort() old_spec[1].sort() if not force and new_spec == old_spec: # no changes return 0 logging.getLogger('roundup.hyperdb').info( 'update_class %s' % spec.classname) # detect multilinks that have been removed, and drop their table old_has = {} for name, prop in old_spec[1]: old_has[name] = 1 if name in spec.properties or not \ isinstance(prop, hyperdb.Multilink): continue # it's a multilink, and it's been removed - drop the old # table. First drop indexes. self.drop_multilink_table_indexes(spec.classname, name) sql = 'drop table %s_%s' % (spec.classname, prop) self.sql(sql) # now figure how we populate the new table if adding_v2: fetch = ['_activity', '_creation', '_creator'] else: fetch = ['_actor', '_activity', '_creation', '_creator'] properties = spec.getprops() for propname, _x in new_spec[1]: prop = properties[propname] if isinstance(prop, hyperdb.Multilink): if propname not in old_has: # we need to create the new table self.create_multilink_table(spec, propname) elif force: tn = '%s_%s' % (spec.classname, propname) # grabe the current values sql = 'select linkid, nodeid from %s' % tn self.sql(sql) rows = self.cursor.fetchall() # drop the old table self.drop_multilink_table_indexes(spec.classname, propname) sql = 'drop table %s' % tn self.sql(sql) # re-create and populate the new table self.create_multilink_table(spec, propname) sql = """insert into %s (linkid, nodeid) values (%s, %s)""" % (tn, self.arg, self.arg) for linkid, nodeid in rows: self.sql(sql, (int(linkid), int(nodeid))) elif propname in old_has: # 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) self.sql(sql) olddata = self.cursor.fetchall() # TODO: update all the other index dropping code self.drop_class_table_indexes(cn, old_spec[0]) # drop the old table self.sql('drop table _%s' % cn) # create the new table self.create_class_table(spec) if olddata: inscols = ['id', '_actor', '_activity', '_creation', '_creator', '__retired__'] for propname, _x in new_spec[1]: prop = properties[propname] if isinstance(prop, hyperdb.Multilink): continue elif isinstance(prop, hyperdb.Interval): inscols.append('_'+propname) inscols.append('__'+propname+'_int__') elif propname in old_has: # we copy this col over from the old table inscols.append('_'+propname) # do the insert of the old data - the new columns will have # NULL values args = ','.join([self.arg for x in inscols]) cols = ','.join(inscols) sql = 'insert into _%s (%s) values (%s)' % (cn, cols, args) for entry in olddata: d = [] retired_id = None for name in inscols: # generate the new value for the Interval int column if name.endswith('_int__'): name = name[2:-6] if sqlite_version in (2, 3): try: v = hyperdb.Interval(entry[name]).as_seconds() except IndexError: v = None elif name in entry: v = hyperdb.Interval(entry[name]).as_seconds() else: v = None elif sqlite_version in (2, 3): try: v = entry[name] except IndexError: v = None elif (sqlite_version == 1 and name in entry): v = entry[name] else: v = None if name == 'id': retired_id = v elif (name == '__retired__' and retired_id and v not in ['0', 0]): v = retired_id d.append(v) self.sql(sql, tuple(d)) return 1 def sql_close(self): """ Squash any error caused by us already having closed the connection. """ try: self.conn.close() except sqlite.ProgrammingError as value: if str(value) != 'close failed - Connection is closed.': raise def sql_rollback(self): """ Squash any error caused by us having closed the connection (and therefore not having anything to roll back) """ try: self.conn.rollback() except sqlite.ProgrammingError as value: if str(value) != 'rollback failed - Connection is closed.': raise def __repr__(self): return '' % id(self) def sql_commit(self): """ Actually commit to the database. Ignore errors if there's nothing to commit. """ def list_dir(dir): import os files = os.listdir(self.dir) # ['db-journal', 'files', 'db'] for entry in [''] + files: path = self.dir + '/' + entry stat = os.stat(path) print("file: %s, uid: %s, gid: %s, mode: %o" % ( path, stat.st_uid, stat.st_gid, stat.st_mode)) # Getting sqlite3.OperationalError: disk I/O error # in CI. It happens intermittently. Try to get more # info about what is happening and retry the commit. # Some possibilities: # -journal file not writable # file has disappeared # # Note after exception self.conn.in_transaction is False # but was True before failed commit(). Retry succeeds, # but I am not sure it actually does anything. # for retry in range(2): try: self.conn.commit() except sqlite.OperationalError as error: if str(error) != 'disk I/O error': raise list_dir(self.dir) raise except sqlite.DatabaseError as error: if str(error) != 'cannot commit - no transaction is active': raise # else: # break # out of loop if no exception # open a new cursor for subsequent work self.cursor = self.conn.cursor() def sql_index_exists(self, table_name, index_name): self.sql('pragma index_list(%s)' % table_name) for entry in self.cursor.fetchall(): if entry[1] == index_name: return 1 return 0 # old-skool id generation def newid(self, classname): """ Generate a new id for the given class """ # Prevent other processes from reading while we increment. # Otherwise multiple processes can end up with the same # new id and hilarity results. # # Defeat pysqlite's attempts to do locking by setting # isolation_level to None. Pysqlite can commit # on it's own even if we don't want it to end the transaction. # If we rewrite to use another sqlite library like apsw we # don't have to deal with this autocommit/autotransact foolishness. self.conn.isolation_level = None # Manage the transaction locks manually. self.sql("BEGIN IMMEDIATE") # get the next ID sql = 'select num from ids where name=%s' % self.arg self.sql(sql, (classname, )) newid = int(self.cursor.fetchone()[0]) # leave the next larger number as the next newid sql = 'update ids set num=num+1 where name=%s' % self.arg vals = (classname,) self.sql(sql, vals) # reset pysqlite's auto transact stuff to default since the # rest of the code expects it. self.conn.isolation_level = '' # commit writing the data, clearing locks for other processes # and create a new cursor to the database. self.sql_commit() # return as string return str(newid) def setid(self, classname, setid): """ Set the id counter: used during import of database We add one to make it behave like the sequences in postgres. """ sql = 'update ids set num=%s where name=%s' % (self.arg, self.arg) vals = (int(setid)+1, classname) self.sql(sql, vals) def clear(self): rdbms_common.Database.clear(self) # set the id counters to 0 (setid adds one) so we start at 1 for cn in self.classes.keys(): self.setid(cn, 0) def create_class(self, spec): rdbms_common.Database.create_class(self, spec) sql = 'insert into ids (name, num) values (%s, %s)' % ( self.arg, self.arg) vals = (spec.classname, 1) self.sql(sql, vals) if sqlite_version in (2, 3): def load_journal(self, classname, cols, nodeid): """We need to turn the sqlite3.Row into a tuple so it can be unpacked""" l = rdbms_common.Database.load_journal(self, classname, cols, nodeid) cols = range(5) return [[row[col] for col in cols] for row in l] class sqliteClass: def filter(self, *args, **kw): """ If there's NO matches to a fetch, sqlite returns NULL instead of nothing """ return [f for f in rdbms_common.Class.filter(self, *args, **kw) if f] class Class(sqliteClass, rdbms_common.Class): pass class IssueClass(sqliteClass, rdbms_common.IssueClass): pass class FileClass(sqliteClass, rdbms_common.FileClass): pass # vim: set et sts=4 sw=4 : code-2a6c3eb4e059/roundup/backends/blobfiles.py0000644000000000000000000003753014571501054017523 0ustar 00000000000000# # Copyright (c) 2001 Bizar Software Pty Ltd (http://www.bizarsoftware.com.au/) # This module is free software, and you may redistribute it and/or modify # under the same terms as Python, so long as this copyright message and # disclaimer are retained in their original form. # # IN NO EVENT SHALL BIZAR SOFTWARE PTY LTD BE LIABLE TO ANY PARTY FOR # DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING # OUT OF THE USE OF THIS CODE, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE # POSSIBILITY OF SUCH DAMAGE. # # BIZAR SOFTWARE PTY LTD SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, # BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS # FOR A PARTICULAR PURPOSE. THE CODE PROVIDED HEREUNDER IS ON AN "AS IS" # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # """This module exports file storage for roundup backends. Files are stored into a directory hierarchy. """ __docformat__ = 'restructuredtext' import os def files_in_dir(dir): if not os.path.exists(dir): return 0 num_files = 0 for dir_entry in os.listdir(dir): full_filename = os.path.join(dir, dir_entry) if os.path.isfile(full_filename): num_files = num_files + 1 elif os.path.isdir(full_filename): num_files = num_files + files_in_dir(full_filename) return num_files class FileStorage(object): """Store files in some directory structure Some databases do not permit the storage of arbitrary data (i.e., file content). And, some database schema explicitly store file content in the fielsystem. In particular, if a class defines a 'filename' property, it is assumed that the data is stored in the indicated file, outside of whatever database Roundup is otherwise using. In these situations, it is difficult to maintain the transactional abstractions used elsewhere in Roundup. In particular, if a file's content is edited, but then the containing transaction is not committed, we do not want to commit the edit. Similarly, we would like to guarantee that if a transaction is committed to the database, then the edit has in fact taken place. This class provides an approximation of these transactional requirements. For classes that do not have a 'filename' property, the file name used to store the file's content is a deterministic function of the classname and nodeid for the file. The 'filename' function computes this name. The name will contain directories and subdirectories, but, suppose, for the purposes of what follows, that the filename is 'file'. Edit Procotol ------------- When a file is created or edited, the following protocol is used: 1. The new content of the file is placed in 'file.tmp'. 2. A transaction is recored in 'self.transactions' referencing the 'doStoreFile' method of this class. 3. At some subsequent point, the database 'commit' function is called. This function first performs a traditional database commit (for example, by issuing a SQL command to commit the current transaction), and, then, runs the transactions recored in 'self.transactions'. 4. The 'doStoreFile' method renames the 'file.tmp' to 'file'. If Step 3 never occurs, but, instead, the database 'rollback' method is called, then that method, after rolling back the database transaction, calls 'rollbackStoreFile', which removes 'file.tmp'. Race Condition -------------- If two Roundup instances (say, the mail gateway and a web client, or two web clients running with a multi-process server) attempt edits at the same time, both will write to 'file.tmp', and the results will be indeterminate. Crash Analysis -------------- There are several situations that may occur if a crash (whether because the machine crashes, because an unhandled Python exception is raised, or because the Python process is killed) occurs. Complexity ensues because backuping up an RDBMS is generally more complex than simply copying a file. Instead, some command is run which stores a snapshot of the database in a file. So, if you back up the database to a file, and then back up the filesystem, it is likely that further database transactions have occurred between the point of database backup and the point of filesystem backup. For the purposes, of this analysis, we assume that the filesystem backup occurred after the database backup. Furthermore, we assume that filesystem backups are atomic; i.e., the at the filesystem is not being modified during the backup. 1. Neither the 'commit' nor 'rollback' methods on the database are ever called. In this case, the '.tmp' file should be ignored as the transaction was not committed. 2. The 'commit' method is called. Subsequently, the machine crashes, and is restored from backups. The most recent filesystem backup and the most recent database backup are not in general from the same instant in time. This problem means that we can never be sure after a crash if the contents of a file are what we intend. It is always possible that an edit was made to the file that is not reflected in the filesystem. 3. A crash occurs between the point of the database commit and the call to 'doStoreFile'. If only one of 'file' and 'file.tmp' exists, then that version should be used. However, if both 'file' and 'file.tmp' exist, there is no way to know which version to use. Reading the File ---------------- When determining the content of the file, we use the following algorithm: 1. If 'self.transactions' reflects an edit of the file, then use 'file.tmp'. We know that an edit to the file is in process so 'file.tmp' is the right choice. If 'file.tmp' does not exist, raise an exception; something has removed the content of the file while we are in the process of editing it. 2. Otherwise, if 'file.tmp' exists, and 'file' does not, use 'file.tmp'. We know that the file is supposed to exist because there is a reference to it in the database. Since 'file' does not exist, we assume that Crash 3 occurred during the initial creation of the file. 3. Otherwise, use 'file'. If 'file.tmp' is not present, this is obviously the best we can do. This is always the right answer unless Crash 2 occurred, in which case the contents of 'file' may be newer than they were at the point of database backup. If 'file.tmp' is present, we know that we are not actively editing the file. The possibilities are: a. Crash 1 has occurred. In this case, using 'file' is the right answer, so we will have chosen correctly. b. Crash 3 has occurred. In this case, 'file.tmp' is the right answer, so we will have chosen incorrectly. However, 'file' was at least a previously committed value. Future Improvements ------------------- One approach would be to take advantage of databases which do allow the storage of arbitary date. For example, MySQL provides the HUGE BLOB datatype for storing up to 4GB of data. Another approach would be to store a version ('v') in the actual database and name files 'file.v'. Then, the editing protocol would become: 1. Generate a new version 'v', guaranteed to be different from all other versions ever used by the database. (The version need not be in any particular sequence; a UUID would be fine.) 2. Store the content in 'file.v'. 3. Update the database to indicate that the version of the node is 'v'. Now, if the transaction is committed, the database will refer to 'file.v', where the content exists. If the transaction is rolled back, or not committed, 'file.v' will never be referenced. In the event of a crash, under the assumptions above, there may be 'file.v' files that are not referenced by the database, but the database will be consistent, so long as unreferenced 'file.v' files are never removed until after the database has been backed up. """ tempext = '.tmp' """The suffix added to files indicating that they are uncommitted.""" def __init__(self, umask): self.umask = umask def subdirFilename(self, classname, nodeid, property=None): """Determine what the filename and subdir for nodeid + classname is.""" if property: name = '%s%s.%s' % (classname, nodeid, property) else: # roundupdb.FileClass never specified the property name, so don't # include it name = '%s%s' % (classname, nodeid) # have a separate subdir for every thousand messages subdir = str(int(nodeid) // 1000) return os.path.join(subdir, name) def _tempfile(self, filename): """Return a temporary filename. 'filename' -- The name of the eventual destination file.""" return filename + self.tempext def _editInProgress(self, classname, nodeid, property): """Return true if the file indicated is being edited. returns -- True if the current transaction includes an edit to the file indicated.""" for method, args in self.transactions: if (method == self.doStoreFile and args == (classname, nodeid, property)): return True return False def filename(self, classname, nodeid, property=None, create=0): """Determine what the filename for the given node and optionally property is. Try a variety of different filenames - the file could be in the usual place, or it could be in a temp file pre-commit *or* it could be in an old-style, backwards-compatible flat directory. """ filename = os.path.join(self.dir, 'files', classname, self.subdirFilename(classname, nodeid, property)) # If the caller is going to create the file, return the # post-commit filename. It is the callers responsibility to # add self.tempext when actually creating the file. if create: return filename tempfile = self._tempfile(filename) # If an edit to this file is in progress, then return the name # of the temporary file containing the edited content. if self._editInProgress(classname, nodeid, property): if not os.path.exists(tempfile): raise IOError('content file for %s not found' % tempfile) return tempfile if os.path.exists(filename): return filename # Otherwise, if the temporary file exists, then the probable # explanation is that a crash occurred between the point that # the database entry recording the creation of the file # occured and the point at which the file was renamed from the # temporary name to the final name. if os.path.exists(tempfile): try: # Clean up, by performing the commit now. os.rename(tempfile, filename) except OSError: pass # If two Roundup clients both try to rename the file # at the same time, only one of them will succeed. # So, tolerate such an error -- but no other. if not os.path.exists(filename): raise IOError('content file for %s not found' % filename) return filename # ok, try flat (very old-style) if property: filename = os.path.join(self.dir, 'files', '%s%s.%s' % ( classname, nodeid, property)) else: filename = os.path.join(self.dir, 'files', '%s%s' % (classname, nodeid)) if os.path.exists(filename): return filename # file just ain't there raise IOError('content file for %s not found' % filename) def filesize(self, classname, nodeid, property=None, create=0): filename = self.filename(classname, nodeid, property, create) return os.path.getsize(filename) def storefile(self, classname, nodeid, property, content): """Store the content of the file in the database. The property may be None, in which case the filename does not indicate which property is being saved. """ # determine the name of the file to write to name = self.filename(classname, nodeid, property, create=1) # make sure the file storage dir exists if not os.path.exists(os.path.dirname(name)): os.makedirs(os.path.dirname(name)) # save to a temp file name = self._tempfile(name) # make sure we don't register the rename action more than once if not self._editInProgress(classname, nodeid, property): # save off the rename action self.transactions.append((self.doStoreFile, (classname, nodeid, property))) # always set umask before writing to make sure we have the proper one # in multi-tracker (i.e. multi-umask) or modpython scenarios # the umask may have changed since last we set it. os.umask(self.umask) fd = open(name, 'wb') fd.write(content) fd.close() def getfile(self, classname, nodeid, property): """Get the content of the file in the database. """ filename = self.filename(classname, nodeid, property) f = open(filename, 'rb') try: # snarf the contents and make sure we close the file return f.read() finally: f.close() def numfiles(self): """Get number of files in storage, even across subdirectories. """ files_dir = os.path.join(self.dir, 'files') return files_in_dir(files_dir) def doStoreFile(self, classname, nodeid, property, **databases): """Store the file as part of a transaction commit. """ # determine the name of the file to write to name = self.filename(classname, nodeid, property, 1) # the file is currently ".tmp" - move it to its real name to commit if name.endswith(self.tempext): # creation dstname = os.path.splitext(name)[0] else: # edit operation dstname = name name = self._tempfile(name) # content is being updated (and some platforms, eg. win32, won't # let us rename over the top of the old file) if os.path.exists(dstname): os.remove(dstname) os.rename(name, dstname) # return the classname, nodeid so we reindex this content return (classname, nodeid) def rollbackStoreFile(self, classname, nodeid, property, **databases): """Remove the temp file as a part of a rollback """ # determine the name of the file to delete name = self.filename(classname, nodeid, property) if not name.endswith(self.tempext): name += self.tempext os.remove(name) def isStoreFile(self, classname, nodeid): """See if there is actually any FileStorage for this node. Is there a better way than using self.filename? """ try: fname = self.filename(classname, nodeid) # noqa: F841 return True except IOError: return False def destroy(self, classname, nodeid): """If there is actually FileStorage for this node remove it from the filesystem """ if self.isStoreFile(classname, nodeid): os.remove(self.filename(classname, nodeid)) # vim: set filetype=python ts=4 sw=4 et si code-2a6c3eb4e059/roundup/backends/indexer_common.py0000644000000000000000000001404514571501054020564 0ustar 00000000000000from roundup import hyperdb STOPWORDS = [ "A", "AND", "ARE", "AS", "AT", "BE", "BUT", "BY", "FOR", "IF", "IN", "INTO", "IS", "IT", "NO", "NOT", "OF", "ON", "OR", "SUCH", "THAT", "THE", "THEIR", "THEN", "THERE", "THESE", "THEY", "THIS", "TO", "WAS", "WILL", "WITH" ] def _isLink(propclass): return (isinstance(propclass, hyperdb.Link) or isinstance(propclass, hyperdb.Multilink)) class Indexer: def __init__(self, db): self.stopwords = set(STOPWORDS) for word in db.config[('main', 'indexer_stopwords')]: self.stopwords.add(word) # Do not index anything longer than maxlength characters since # that'll be gibberish (encoded text or somesuch) or shorter # than 2 characters self.minlength = 2 self.maxlength = 50 self.language = db.config[('main', 'indexer_language')] # Some indexers have a query language. If that is the case, # we don't parse the user supplied query into a wordlist. self.query_language = False def is_stopword(self, word): return word in self.stopwords def getHits(self, search_terms, klass): return self.find(search_terms) def save_index(self): pass def search(self, search_terms, klass, ignore=None): """Display search results looking for [search, terms] associated with the hyperdb Class "klass". Ignore hits on {class: property}. """ # do the index lookup hits = self.getHits(search_terms, klass) if not hits: return {} designator_propname = {} for nm, propclass in klass.getprops().items(): if _isLink(propclass): designator_propname.setdefault(propclass.classname, []).append(nm) # build a dictionary of nodes and their associated messages # and files nodeids = {} # this is the answer propspec = {} # used to do the klass.find for pn in designator_propname.values(): for propname in pn: propspec[propname] = {} # used as a set (value doesn't matter) if ignore is None: ignore = {} # don't unpack hits entries as sqlite3's Row can't be unpacked :( for entry in hits: # skip this result if we don't care about this class/property classname = entry[0] property = entry[2] if (classname, property) in ignore: continue # if it's a property on klass, it's easy # (make sure the nodeid is str() not unicode() as returned by some # backends as that can cause problems down the track) nodeid = str(entry[1]) if classname == klass.classname: if nodeid not in nodeids: nodeids[nodeid] = {} continue # make sure the class is a linked one, otherwise ignore if classname not in designator_propname: continue # it's a linked class - set up to do the klass.find for linkprop in designator_propname[classname]: propspec[linkprop][nodeid] = 1 # retain only the meaningful entries for propname, idset in list(propspec.items()): if not idset: del propspec[propname] # klass.find tells me the klass nodeids the linked nodes relate to propdefs = klass.getprops() for resid in klass.find(**propspec): resid = str(resid) if resid in nodeids: continue # we ignore duplicate resids nodeids[resid] = {} node_dict = nodeids[resid] # now figure out where it came from for linkprop in propspec: v = klass.get(resid, linkprop) # the link might be a Link so deal with a single result or None if isinstance(propdefs[linkprop], hyperdb.Link): if v is None: continue v = [v] for nodeid in v: if nodeid in propspec[linkprop]: # OK, this node[propname] has a winner if linkprop not in node_dict: node_dict[linkprop] = [nodeid] else: node_dict[linkprop].append(nodeid) return nodeids def get_indexer(config, db): indexer_name = getattr(config, "INDEXER", "") if not indexer_name: # Try everything try: from .indexer_xapian import Indexer return Indexer(db) except ImportError: pass try: from .indexer_whoosh import Indexer return Indexer(db) except ImportError: pass indexer_name = "native" # fallback to native full text search if indexer_name == "xapian": from .indexer_xapian import Indexer return Indexer(db) if indexer_name == "whoosh": from .indexer_whoosh import Indexer return Indexer(db) if indexer_name == "native-fts": if db.dbtype not in ("sqlite", "postgres"): raise AssertionError("Indexer native-fts is configured, but only " "sqlite and postgres support it. Database is: %r" % db.dbtype) if db.dbtype == "sqlite": from roundup.backends.indexer_sqlite_fts import Indexer return Indexer(db) if db.dbtype == "postgres": from roundup.backends.indexer_postgresql_fts import Indexer return Indexer(db) if indexer_name == "native": # load proper native indexing based on database type if db.dbtype == "anydbm": from roundup.backends.indexer_dbm import Indexer return Indexer(db) if db.dbtype in ("sqlite", "postgres", "mysql"): from roundup.backends.indexer_rdbms import Indexer return Indexer(db) raise AssertionError("Invalid indexer: %r" % (indexer_name)) code-2a6c3eb4e059/roundup/backends/indexer_dbm.py0000644000000000000000000002500714571501054020036 0ustar 00000000000000# # This module is derived from the module described at: # http://gnosis.cx/publish/programming/charming_python_15.txt # # Author: David Mertz (mertz@gnosis.cx) # Thanks to: Pat Knight (p.knight@ktgroup.co.uk) # Gregory Popovitch (greg@gpy.com) # # The original module was released under this license, and remains under # it: # # This file is released to the public domain. I (dqm) would # appreciate it if you choose to keep derived works under terms # that promote freedom, but obviously am giving up any rights # to compel such. # '''This module provides an indexer class, RoundupIndexer, that stores text indices in a roundup instance. This class makes searching the content of messages, string properties and text files possible. ''' __docformat__ = 'restructuredtext' import errno import marshal import os import re import shutil import zlib from roundup.backends.indexer_common import Indexer as IndexerBase class Indexer(IndexerBase): '''Indexes information from roundup's hyperdb to allow efficient searching. Three structures are created by the indexer:: files {identifier: (fileid, wordcount)} words {word: {fileid: count}} fileids {fileid: identifier} where identifier is (classname, nodeid, propertyname) ''' def __init__(self, db): IndexerBase.__init__(self, db) self.indexdb_path = os.path.join(db.config.DATABASE, 'indexes') self.indexdb = os.path.join(self.indexdb_path, 'index.db') self.reindex = 0 self.quiet = 9 self.changed = 0 # see if we need to reindex because of a change in code version = os.path.join(self.indexdb_path, 'version') if (not os.path.exists(self.indexdb_path) or not os.path.exists(version)): # for now the file itself is a flag self.force_reindex() elif os.path.exists(version): fd = open(version) version = fd.read() fd.close() # check the value and reindex if it's not the latest if version.strip() != '1': self.force_reindex() def force_reindex(self): '''Force a reindex condition ''' if os.path.exists(self.indexdb_path): shutil.rmtree(self.indexdb_path) os.makedirs(self.indexdb_path) os.chmod(self.indexdb_path, 0o775) # nosec - allow group write fd = open(os.path.join(self.indexdb_path, 'version'), 'w') fd.write('1\n') fd.close() self.reindex = 1 self.changed = 1 def should_reindex(self): '''Should we reindex? ''' return self.reindex def add_text(self, identifier, text, mime_type='text/plain'): '''Add some text associated with the (classname, nodeid, property) identifier. ''' # make sure the index is loaded self.load_index() # remove old entries for this identifier if identifier in self.files: self.purge_entry(identifier) # split into words words = self.splitter(text, mime_type) # Find new file index, and assign it to identifier # (_TOP uses trick of negative to avoid conflict with file index) self.files['_TOP'] = (self.files['_TOP'][0]-1, None) file_index = abs(self.files['_TOP'][0]) self.files[identifier] = (file_index, len(words)) self.fileids[file_index] = identifier # find the unique words filedict = {} for word in words: if self.is_stopword(word): continue if word in filedict: filedict[word] = filedict[word]+1 else: filedict[word] = 1 # now add to the totals for word in filedict: # each word has a dict of {identifier: count} if word in self.words: entry = self.words[word] else: # new word entry = {} self.words[word] = entry # make a reference to the file for this word entry[file_index] = filedict[word] # save needed self.changed = 1 def splitter(self, text, ftype): '''Split the contents of a text string into a list of 'words' ''' if ftype == 'text/plain': words = self.text_splitter(text) else: return [] return words def text_splitter(self, text): """Split text/plain string into a list of words """ if not text: return [] # case insensitive text = text.upper() # Split the raw text return re.findall(r'\b\w{%d,%d}\b' % (self.minlength, self.maxlength), text, re.UNICODE) # we override this to ignore too short and too long words # and also to fix a bug - the (fail) case. def find(self, wordlist): '''Locate files that match ALL the words in wordlist ''' if not hasattr(self, 'words'): self.load_index() self.load_index(wordlist=wordlist) entries = {} hits = None for word in wordlist: if not self.minlength <= len(word) <= self.maxlength: # word outside the bounds of what we index - ignore continue word = word.upper() if self.is_stopword(word): continue entry = self.words.get(word) # For each word, get index entries[word] = entry # of matching files if not entry: # Nothing for this one word (fail) return {} if hits is None: hits = {} for k in entry: if k not in self.fileids: raise ValueError('Index is corrupted: re-generate it') hits[k] = self.fileids[k] else: # Eliminate hits for every non-match for fileid in list(hits): if fileid not in entry: del hits[fileid] if hits is None: return {} return list(hits.values()) segments = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ#_-!" def load_index(self, reload=0, wordlist=None): # Unless reload is indicated, do not load twice if self.index_loaded() and not reload: return 0 # Ok, now let's actually load it db = {'WORDS': {}, 'FILES': {'_TOP': (0, None)}, 'FILEIDS': {}} # Identify the relevant word-dictionary segments if not wordlist: segments = self.segments else: segments = ['-', '#'] for word in wordlist: initchar = word[0].upper() if initchar not in self.segments: initchar = '_' segments.append(initchar) # Load the segments for segment in segments: try: f = open(self.indexdb + segment, 'rb') except IOError as error: # probably just nonexistent segment index file if error.errno != errno.ENOENT: raise # noqa: E701 else: pickle_str = zlib.decompress(f.read()) f.close() dbslice = marshal.loads(pickle_str) if dbslice.get('WORDS'): # if it has some words, add them for word, entry in dbslice['WORDS'].items(): db['WORDS'][word] = entry if dbslice.get('FILES'): # if it has some files, add them db['FILES'] = dbslice['FILES'] if dbslice.get('FILEIDS'): # if it has fileids, add them db['FILEIDS'] = dbslice['FILEIDS'] self.words = db['WORDS'] self.files = db['FILES'] self.fileids = db['FILEIDS'] self.changed = 0 def save_index(self): # only save if the index is loaded and changed if not self.index_loaded() or not self.changed: return # brutal space saver... delete all the small segments for segment in self.segments: try: os.remove(self.indexdb + segment) except OSError as error: # probably just nonexistent segment index file if error.errno != errno.ENOENT: raise # noqa: E701 # First write the much simpler filename/fileid dictionaries dbfil = {'WORDS': None, 'FILES': self.files, 'FILEIDS': self.fileids} marshal_fh = open(self.indexdb+'-', 'wb') marshal_fh.write(zlib.compress(marshal.dumps(dbfil))) marshal_fh.close() # The hard part is splitting the word dictionary up, of course letters = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ#_" segdicts = {} # Need batch of empty dicts for segment in letters: segdicts[segment] = {} for word, entry in self.words.items(): # Split into segment dicts initchar = word[0].upper() if initchar not in letters: # if it's a unicode character, add it to the '_' segment initchar = '_' segdicts[initchar][word] = entry # save for initchar in letters: db = {'WORDS': segdicts[initchar], 'FILES': None, 'FILEIDS': None} pickle_str = marshal.dumps(db) filename = self.indexdb + initchar pickle_fh = open(filename, 'wb') pickle_fh.write(zlib.compress(pickle_str)) pickle_fh.close() os.chmod(filename, 0o664) # save done self.changed = 0 def purge_entry(self, identifier): '''Remove a file from file index and word index ''' self.load_index() if identifier not in self.files: return file_index = self.files[identifier][0] del self.files[identifier] del self.fileids[file_index] # The much harder part, cleanup the word index for _key, occurs in self.words.items(): if file_index in occurs: del occurs[file_index] # save needed self.changed = 1 def index_loaded(self): return (hasattr(self, 'fileids') and hasattr(self, 'files') and hasattr(self, 'words')) def rollback(self): ''' load last saved index info. ''' self.load_index(reload=1) def close(self): pass # vim: set filetype=python ts=4 sw=4 et si code-2a6c3eb4e059/roundup/backends/indexer_postgresql_fts.py0000644000000000000000000001402714571501054022353 0ustar 00000000000000""" This implements the PostgreSQL full-text indexer. The table consists of (Class, propname, itemid) instances as columns along with an _tsv tsvector column. The _tsv column is searched using @@ and the instances returned. """ import re from roundup.backends.indexer_common import Indexer as IndexerBase from roundup.i18n import _ from roundup.cgi.exceptions import IndexerQueryError from psycopg2.errors import InFailedSqlTransaction, SyntaxError, \ UndefinedObject class Indexer(IndexerBase): def __init__(self, db): IndexerBase.__init__(self, db) self.db = db if db.conn.server_version < 110000: db.sql("select version()") server_descr = db.cursor.fetchone() raise ValueError("Postgres native_fts indexing requires postgres " "11.0 or newer. Server is version: %s" % server_descr) self.reindex = 0 self.query_language = True def close(self): """close the indexing database""" # just nuke the circular reference self.db = None def save_index(self): """Save the changes to the index.""" # not necessary - the RDBMS connection will handle this for us pass def force_reindex(self): """Force a reindexing of the database. This essentially empties the __fts table and sets a flag so that the databases are reindexed""" self.reindex = 1 def should_reindex(self): """returns True if the indexes need to be rebuilt""" return self.reindex def add_text(self, identifier, text, mime_type='text/plain'): """ "identifier" is (classname, itemid, property) """ if mime_type != 'text/plain': return # Ensure all elements of the identifier are strings 'cos the itemid # column is varchar even if item ids may be numbers elsewhere in the # code. ugh. identifier = tuple(map(str, identifier)) # removed pre-processing of text that incudes only words with: # self.minlength <= len(word) <= self.maxlength # Not sure if that is correct. # first, find the rowid of the (classname, itemid, property) a = self.db.arg # arg is the token for positional parameters sql = 'select ctid from __fts where _class=%s and '\ '_itemid=%s and _prop=%s' % (a, a, a) self.db.cursor.execute(sql, identifier) r = self.db.cursor.fetchone() if not r: # not previously indexed sql = 'insert into __fts (_class, _itemid, _prop, _tsv)'\ ' values (%s, %s, %s, to_tsvector(%s, %s))' % (a, a, a, a, a) try: self.db.cursor.execute(sql, identifier + (self.db.config['INDEXER_LANGUAGE'], text)) except ValueError: # if text is binary or otherwise un-indexable, # we get a ValueError. For right now ignore it. pass else: id = r[0] sql = 'update __fts set _tsv=to_tsvector(%s, %s) where ctid=%s' % \ (a, a, a) self.db.cursor.execute(sql, (self.db.config['INDEXER_LANGUAGE'], text, id)) def find(self, wordlist): """look up all the words in the wordlist. For testing wordlist is actually a list. In production, wordlist is a list of a single string that is a postgresql websearch_to_tsquery() query. https://www.postgresql.org/docs/14/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES """ if not wordlist: return [] a = self.db.arg # arg is the token for positional parameters # removed filtering of word in wordlist to include only # words with: self.minlength <= len(word) <= self.maxlength if wordlist[0].startswith("ts:"): wordlist[0] = wordlist[0][3:] sql = ('select _class, _itemid, _prop from __fts ' 'where _tsv @@ to_tsquery(%s, %s)' % (a, a)) else: if re.search(r'[<>!&|()*]', " ".join(wordlist)): # assume this is a ts query processed by websearch_to_tsquery. # since it has operator characters in it. raise IndexerQueryError(_('You have non-word/operator ' 'characters "<>!&|()*" in your query. Did you want to ' 'do a tsquery search and forgot to start it with "ts:"?')) else: sql = 'select _class, _itemid, _prop from __fts '\ 'where _tsv @@ websearch_to_tsquery(%s, %s)' % (a, a) try: # tests supply a multi element word list. Join them. self.db.cursor.execute(sql, (self.db.config['INDEXER_LANGUAGE'], " ".join(wordlist),)) except SyntaxError as e: # reset the cursor as it's invalid currently # reuse causes an InFailedSqlTransaction self.db.rollback() raise IndexerQueryError(e.args[0]) except ValueError as e: # raised when search string has a null bytes in it or # is otherwise unsuitable. raise IndexerQueryError( "Invalid search string, do you have a null in there? " + e.args[0]) except InFailedSqlTransaction: # reset the cursor as it's invalid currently self.db.rollback() raise except UndefinedObject as e: # try for a nicer user error self.db.rollback() lookfor = ('text search configuration "%s" does ' 'not exist' % self.db.config['INDEXER_LANGUAGE']) if lookfor in e.args[0]: raise ValueError(_("Check tracker config.ini for a bad " "indexer_language setting. Error is: %s") % e) else: raise return self.db.cursor.fetchall() code-2a6c3eb4e059/roundup/backends/indexer_rdbms.py0000644000000000000000000001231114571501054020375 0ustar 00000000000000""" This implements the full-text indexer over two RDBMS tables. The first is a mapping of words to occurance IDs. The second maps the IDs to (Class, propname, itemid) instances. """ import re from roundup.backends.indexer_common import Indexer as IndexerBase from roundup.anypy.strings import us2u, u2s class Indexer(IndexerBase): def __init__(self, db): IndexerBase.__init__(self, db) self.db = db self.reindex = 0 def close(self): """close the indexing database""" # just nuke the circular reference self.db = None def save_index(self): """Save the changes to the index.""" # not necessary - the RDBMS connection will handle this for us pass def force_reindex(self): """Force a reindexing of the database. This essentially empties the tables ids and index and sets a flag so that the databases are reindexed""" self.reindex = 1 def should_reindex(self): """returns True if the indexes need to be rebuilt""" return self.reindex def add_text(self, identifier, text, mime_type='text/plain'): """ "identifier" is (classname, itemid, property) """ if mime_type != 'text/plain': return # Ensure all elements of the identifier are strings 'cos the itemid # column is varchar even if item ids may be numbers elsewhere in the # code. ugh. identifier = tuple(map(str, identifier)) # first, find the id of the (classname, itemid, property) a = self.db.arg sql = 'select _textid from __textids where _class=%s and '\ '_itemid=%s and _prop=%s' % (a, a, a) self.db.cursor.execute(sql, identifier) r = self.db.cursor.fetchone() if not r: # not previously indexed id = self.db.newid('__textids') sql = 'insert into __textids (_textid, _class, _itemid, _prop)'\ ' values (%s, %s, %s, %s)' % (a, a, a, a) self.db.cursor.execute(sql, (id, ) + identifier) else: id = int(r[0]) # clear out any existing indexed values sql = 'delete from __words where _textid=%s' % a self.db.cursor.execute(sql, (id, )) # ok, find all the unique words in the text text = us2u(text, "replace") text = text.upper() wordlist = [u2s(w) for w in re.findall(r'(?u)\b\w{%d,%d}\b' % (self.minlength, self.maxlength), text)] words = set() for word in wordlist: if self.is_stopword(word): continue words.add(word) # for each word, add an entry in the db sql = 'insert into __words (_word, _textid) values (%s, %s)' % (a, a) words = [(word, id) for word in words] self.db.cursor.executemany(sql, words) def find(self, wordlist): """look up all the words in the wordlist. If none are found return an empty dictionary * more rules here """ if not wordlist: return [] cap_wl = [word.upper() for word in wordlist if self.minlength <= len(word) <= self.maxlength] clean_wl = [word for word in cap_wl if not self.is_stopword(word)] if not clean_wl: return [] if self.db.implements_intersect: # simple AND search sql = 'select distinct(_textid) from __words where _word=%s' % ( self.db.arg) sql = '\nINTERSECT\n'.join([sql]*len(clean_wl)) self.db.cursor.execute(sql, tuple(clean_wl)) r = self.db.cursor.fetchall() if not r: return [] a = ','.join([self.db.arg] * len(r)) sql = 'select _class, _itemid, _prop from __textids '\ 'where _textid in (%s)' % a self.db.cursor.execute(sql, tuple([int(row[0]) for row in r])) else: # A more complex version for MySQL since it doesn't # implement INTERSECT # Construct SQL statement to join __words table to itself # multiple times. sql = """select distinct(__words1._textid) from __words as __words1 %s where __words1._word=%s %s""" join_tmpl = ' left join __words as __words%d using (_textid) \n' match_tmpl = ' and __words%d._word=%s \n' join_list = [] match_list = [] for n in range(len(clean_wl) - 1): join_list.append(join_tmpl % (n + 2)) match_list.append(match_tmpl % (n + 2, self.db.arg)) sql = sql % (' '.join(join_list), self.db.arg, ' '.join(match_list)) self.db.cursor.execute(sql, clean_wl) r = [x[0] for x in self.db.cursor.fetchall()] if not r: return [] a = ','.join([self.db.arg] * len(r)) sql = 'select _class, _itemid, _prop from __textids '\ 'where _textid in (%s)' % a self.db.cursor.execute(sql, tuple(map(int, r))) return self.db.cursor.fetchall() code-2a6c3eb4e059/roundup/backends/indexer_sqlite_fts.py0000644000000000000000000001222714571501054021451 0ustar 00000000000000""" This implements the full-text indexer using fts5 in sqlite. The table consists of (Class, propname, itemid) instances as columns along with a textblob column. The textblob column is searched using MATCH and the instances returned. sqlite test commands to manage schema version change required by this update. -- check length before and after select length(schema) from schema; -- reset from version 7 (with fts index) to version 6 update schema set schema = (select replace(schema, '''version'': 7','''version'': 6') as new_schema from schema); -- check version. Good thing it's at the front of the schema select substr(schema,0,15) from schema; {'version': 6, """ from roundup.backends.indexer_common import Indexer as IndexerBase from roundup.i18n import _ from roundup.cgi.exceptions import IndexerQueryError try: import sqlite3 as sqlite if sqlite.sqlite_version_info < (3, 9, 0): raise ValueError('sqlite minimum version for FTS5 is 3.9.0+ ' '- %s found' % sqlite.sqlite_version) except ImportError: raise ValueError('Unable to import sqlite3 to support FTS.') class Indexer(IndexerBase): def __init__(self, db): IndexerBase.__init__(self, db) self.db = db self.reindex = 0 self.query_language = True def close(self): """close the indexing database""" # just nuke the circular reference self.db = None def save_index(self): """Save the changes to the index.""" # not necessary - the RDBMS connection will handle this for us pass def force_reindex(self): """Force a reindexing of the database. This essentially empties the __fts table and sets a flag so that the databases are reindexed""" self.reindex = 1 def should_reindex(self): """returns True if the indexes need to be rebuilt""" return self.reindex def add_text(self, identifier, text, mime_type='text/plain'): """ "identifier" is (classname, itemid, property) """ if mime_type != 'text/plain': return # Ensure all elements of the identifier are strings 'cos the itemid # column is varchar even if item ids may be numbers elsewhere in the # code. ugh. identifier = tuple(map(str, identifier)) # removed pre-processing of text that incudes only words with: # self.minlength <= len(word) <= self.maxlength # Not sure if that is correct. # first, find the rowid of the (classname, itemid, property) a = self.db.arg # arg is the token for positional parameters sql = 'select rowid from __fts where _class=%s and '\ '_itemid=%s and _prop=%s' % (a, a, a) self.db.cursor.execute(sql, identifier) r = self.db.cursor.fetchone() if not r: # not previously indexed sql = 'insert into __fts (_class, _itemid, _prop, _textblob)'\ ' values (%s, %s, %s, %s)' % (a, a, a, a) self.db.cursor.execute(sql, identifier + (text,)) else: id = int(r[0]) sql = 'update __fts set _textblob=%s where rowid=%s' % \ (a, a) self.db.cursor.execute(sql, (text, id)) def find(self, wordlist): """look up all the words in the wordlist. For testing wordlist is actually a list. In production, wordlist is a list of a single string that is a sqlite MATCH query. https://www.sqlite.org/fts5.html#full_text_query_syntax """ # Filter out stopwords. Other searches tokenize the user query # into an list of simple word tokens. For fTS, query # tokenization doesn't occur. # A user's FTS query is a wordlist with one element. The # element is a string to parse and will probably not match a # stop word. # # However the generic indexer search tests pass in a list of # word tokens. We filter the word tokens so it behaves like # other backends. This means that a search for a simple word # like 'the' (without quotes) will return no hits, as the test # expects. wordlist = [w for w in wordlist if not self.is_stopword(w.upper())] if not wordlist: return [] a = self.db.arg # arg is the token for positional parameters # removed filtering of word in wordlist to include only # words with: self.minlength <= len(word) <= self.maxlength sql = 'select _class, _itemid, _prop from __fts '\ 'where _textblob MATCH %s' % a try: # tests supply a multi element word list. Join them. self.db.cursor.execute(sql, (" ".join(wordlist),)) except sqlite.OperationalError as e: if 'no such column' in e.args[0]: raise IndexerQueryError( _("Search failed. Try quoting any terms that " "include a '-' and retry the search.")) else: raise IndexerQueryError(e.args[0].replace("fts5:", "Query error:")) return self.db.cursor.fetchall() code-2a6c3eb4e059/roundup/backends/indexer_whoosh.py0000644000000000000000000001075014571501054020602 0ustar 00000000000000''' This implements the full-text indexer using Whoosh. ''' import os from whoosh import fields, qparser, index, query, analysis from roundup.backends.indexer_common import Indexer as IndexerBase from roundup.anypy.strings import us2u class Indexer(IndexerBase): def __init__(self, db): IndexerBase.__init__(self, db) self.db_path = db.config.DATABASE self.reindex = 0 self.writer = None self.index = None self.deleted = set() def _get_index(self): if self.index is None: path = os.path.join(self.db_path, 'whoosh-index') if not os.path.exists(path): # StandardAnalyzer lowercases all words and configure it to # block stopwords and words with lengths not between # self.minlength and self.maxlength from indexer_common stopfilter = analysis.StandardAnalyzer( #stoplist=self.stopwords, minsize=self.minlength, maxsize=self.maxlength) os.mkdir(path) schema = fields.Schema(identifier=fields.ID(stored=True, unique=True), content=fields.TEXT(analyzer=stopfilter)) index.create_in(path, schema) self.index = index.open_dir(path) return self.index def save_index(self): '''Save the changes to the index.''' if not self.writer: return self.writer.commit() self.deleted = set() self.writer = None def close(self): '''close the indexing database''' pass def rollback(self): if not self.writer: return self.writer.cancel() self.deleted = set() self.writer = None def force_reindex(self): '''Force a reindexing of the database. This essentially empties the tables ids and index and sets a flag so that the databases are reindexed''' self.reindex = 1 def should_reindex(self): '''returns True if the indexes need to be rebuilt''' return self.reindex def _get_writer(self): if self.writer is None: self.writer = self._get_index().writer() return self.writer def _get_searcher(self): return self._get_index().searcher() def add_text(self, identifier, text, mime_type='text/plain'): ''' "identifier" is (classname, itemid, property) ''' if mime_type != 'text/plain': return if not text: text = u'' text = us2u(text, "replace") # We use the identifier twice: once in the actual "text" being # indexed so we can search on it, and again as the "data" being # indexed so we know what we're matching when we get results identifier = u"%s:%s:%s" % identifier # FIXME need to enhance this to handle the whoosh.store.LockError # that maybe raised if there is already another process with a lock. writer = self._get_writer() # Whoosh gets upset if a document is deleted twice in one transaction, # so we keep a list of the documents we have so far deleted to make # sure that we only delete them once. if identifier not in self.deleted: searcher = self._get_searcher() results = searcher.search(query.Term("identifier", identifier)) if len(results) > 0: writer.delete_by_term("identifier", identifier) self.deleted.add(identifier) # Note: use '.lower()' because it seems like Whoosh gets # better results that way. writer.add_document(identifier=identifier, content=text) self.save_index() def find(self, wordlist): '''look up all the words in the wordlist. If none are found return an empty dictionary * more rules here ''' wordlist = [word for word in wordlist if (self.minlength <= len(word) <= self.maxlength) and not self.is_stopword(word.upper())] if not wordlist: return {} searcher = self._get_searcher() q = query.And([query.FuzzyTerm("content", word.lower()) for word in wordlist]) results = searcher.search(q, limit=None) return [tuple(result["identifier"].split(':')) for result in results] code-2a6c3eb4e059/roundup/backends/indexer_xapian.py0000644000000000000000000001072714571501054020557 0ustar 00000000000000''' This implements the full-text indexer using the Xapian indexer. ''' import os import re import time import xapian from roundup.anypy.strings import b2s, s2b from roundup.backends.indexer_common import Indexer as IndexerBase from roundup.i18n import _ # TODO: we need to delete documents when a property is *reindexed* # Note that Xapian always uses UTF-8 encoded string, see # https://xapian.org/docs/bindings/python3/introduction.html#strings: # "Where std::string is returned, it's always mapped to bytes in # Python..." class Indexer(IndexerBase): def __init__(self, db): IndexerBase.__init__(self, db) self.db_path = db.config.DATABASE self.reindex = 0 self.transaction_active = False def _get_database(self): index = os.path.join(self.db_path, 'text-index') for n in range(10): try: # if successful return return xapian.WritableDatabase(index, xapian.DB_CREATE_OR_OPEN) except xapian.DatabaseLockError: # adaptive sleep. Get longer as count increases. time_to_sleep = 0.01 * (2 << min(5, n)) time.sleep(time_to_sleep) # we are back to the for loop # Get here only if we dropped out of the for loop. raise xapian.DatabaseLockError(_( "Unable to get lock after 10 retries on %s.") % index) def save_index(self): '''Save the changes to the index.''' if not self.transaction_active: return database = self._get_database() database.commit_transaction() self.transaction_active = False def close(self): '''close the indexing database''' pass def rollback(self): if not self.transaction_active: return database = self._get_database() database.cancel_transaction() self.transaction_active = False def force_reindex(self): '''Force a reindexing of the database. This essentially empties the tables ids and index and sets a flag so that the databases are reindexed''' self.reindex = 1 def should_reindex(self): '''returns True if the indexes need to be rebuilt''' return self.reindex def add_text(self, identifier, text, mime_type='text/plain'): ''' "identifier" is (classname, itemid, property) ''' if mime_type != 'text/plain': return if not text: text = '' # open the database and start a transaction if needed database = self._get_database() # XXX: Xapian now supports transactions, # but there is a call to save_index() missing. # if not self.transaction_active: # database.begin_transaction() # self.transaction_active = True stemmer = xapian.Stem(self.language) # We use the identifier twice: once in the actual "text" being # indexed so we can search on it, and again as the "data" being # indexed so we know what we're matching when we get results identifier = s2b('%s:%s:%s' % identifier) # create the new document doc = xapian.Document() doc.set_data(identifier) doc.add_term(identifier, 0) for match in re.finditer(r'\b\w{%d,%d}\b' % (self.minlength, self.maxlength), text.upper(), re.UNICODE): word = match.group(0) if self.is_stopword(word): continue term = stemmer(s2b(word.lower())) doc.add_posting(term, match.start(0)) database.replace_document(identifier, doc) def find(self, wordlist): '''look up all the words in the wordlist. If none are found return an empty dictionary * more rules here ''' if not wordlist: return {} database = self._get_database() enquire = xapian.Enquire(database) stemmer = xapian.Stem(self.language) terms = [] for term in [word.upper() for word in wordlist if self.minlength <= len(word) <= self.maxlength]: if not self.is_stopword(term): terms.append(stemmer(s2b(term.lower()))) query = xapian.Query(xapian.Query.OP_AND, terms) enquire.set_query(query) matches = enquire.get_mset(0, database.get_doccount()) return [tuple(b2s(m.document.get_data()).split(':')) for m in matches] code-2a6c3eb4e059/roundup/backends/locking.py0000644000000000000000000000331114571501054017176 0ustar 00000000000000#! /usr/bin/env python # Copyright (c) 2002 ekit.com Inc (http://www.ekit-inc.com/) # # Permission is hereby granted, free of charge, to any person obtaining a copy # of this software and associated documentation files (the "Software"), to deal # in the Software without restriction, including without limitation the rights # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell # copies of the Software, and to permit persons to whom the Software is # furnished to do so, subject to the following conditions: # # The above copyright notice and this permission notice shall be included in # all copies or substantial portions of the Software. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE # SOFTWARE. '''This module provides a generic interface to acquire and release exclusive access to a file. It should work on Unix and Windows. ''' __docformat__ = 'restructuredtext' from roundup.backends import portalocker def acquire_lock(path, block=1): '''Acquire a lock for the given path ''' file = open(path, 'w') if block: portalocker.lock(file, portalocker.LOCK_EX) else: portalocker.lock(file, portalocker.LOCK_EX|portalocker.LOCK_NB) return file def release_lock(file): '''Release our lock on the given path ''' portalocker.unlock(file) code-2a6c3eb4e059/roundup/backends/portalocker.py0000644000000000000000000001016714571501054020104 0ustar 00000000000000#!/usr/bin/env python # # portalocker.py - Cross-platform (posix/nt) API for flock-style file locking. # # http://code.activestate.com/recipes/65203-portalocker-cross-platform-posixnt-api-for-flock-s/ # """ Cross-platform (posix/nt) API for flock-style file locking. Synopsis:: import portalocker file = open("somefile", "r+") portalocker.lock(file, portalocker.LOCK_EX) file.seek(12) file.write("foo") file.close() If you know what you're doing, you may choose to:: portalocker.unlock(file) before closing the file, but why? Methods:: lock( file, flags ) unlock( file ) Constants:: LOCK_EX LOCK_SH LOCK_NB I learned the win32 technique for locking files from sample code provided by John Nielsen in the documentation that accompanies the win32 modules. :Author: Jonathan Feinberg Roundup Changes --------------- 2012-11-28 (anatoly techtonik) - Ported to ctypes - Dropped support for Win95, Win98 and WinME - Added return result """ from __future__ import print_function __docformat__ = 'restructuredtext' import os if os.name == 'nt': import msvcrt import ctypes from ctypes import windll from ctypes.wintypes import BOOL, DWORD, HANDLE LOCK_SH = 0 # the default LOCK_NB = 0x1 # LOCKFILE_FAIL_IMMEDIATELY LOCK_EX = 0x2 # LOCKFILE_EXCLUSIVE_LOCK # --- the code is taken from pyserial project --- # # detect size of ULONG_PTR def is_64bit(): return ctypes.sizeof(ctypes.c_ulong) != ctypes.sizeof(ctypes.c_void_p) if is_64bit(): ULONG_PTR = ctypes.c_int64 else: ULONG_PTR = ctypes.c_ulong PVOID = ctypes.c_void_p # --- Union inside Structure by stackoverflow:3480240 --- class _OFFSET(ctypes.Structure): _fields_ = [ ('Offset', DWORD), ('OffsetHigh', DWORD)] class _OFFSET_UNION(ctypes.Union): _anonymous_ = ['_offset'] _fields_ = [ ('_offset', _OFFSET), ('Pointer', PVOID)] class OVERLAPPED(ctypes.Structure): _anonymous_ = ['_offset_union'] _fields_ = [ ('Internal', ULONG_PTR), ('InternalHigh', ULONG_PTR), ('_offset_union', _OFFSET_UNION), ('hEvent', HANDLE)] LPOVERLAPPED = ctypes.POINTER(OVERLAPPED) # --- Define function prototypes for extra safety --- LockFileEx = windll.kernel32.LockFileEx LockFileEx.restype = BOOL LockFileEx.argtypes = [HANDLE, DWORD, DWORD, DWORD, DWORD, LPOVERLAPPED] UnlockFileEx = windll.kernel32.UnlockFileEx UnlockFileEx.restype = BOOL UnlockFileEx.argtypes = [HANDLE, DWORD, DWORD, DWORD, LPOVERLAPPED] elif os.name == 'posix': import fcntl LOCK_SH = fcntl.LOCK_SH # shared lock LOCK_NB = fcntl.LOCK_NB # non-blocking LOCK_EX = fcntl.LOCK_EX else: raise RuntimeError("PortaLocker only defined for nt and posix platforms") if os.name == 'nt': def lock(file, flags): """ Return True on success, False otherwise """ hfile = msvcrt.get_osfhandle(file.fileno()) overlapped = OVERLAPPED() if LockFileEx(hfile, flags, 0, 0, 0xFFFF0000, ctypes.byref(overlapped)): return True else: return False def unlock(file): hfile = msvcrt.get_osfhandle(file.fileno()) overlapped = OVERLAPPED() if UnlockFileEx(hfile, 0, 0, 0xFFFF0000, ctypes.byref(overlapped)): return True else: return False elif os.name == 'posix': def lock(file, flags): if fcntl.flock(file.fileno(), flags) == 0: return True else: return False def unlock(file): if fcntl.flock(file.fileno(), fcntl.LOCK_UN) == 0: return True else: return False if __name__ == '__main__': from time import time, strftime, localtime import sys log = open('log.txt', "a+") lock(log, LOCK_EX) timestamp = strftime("%m/%d/%Y %H:%M:%S\n", localtime(time())) log.write(timestamp) print("Wrote lines. Hit enter to release lock.") dummy = sys.stdin.readline() log.close() code-2a6c3eb4e059/roundup/backends/rdbms_common.py0000644000000000000000000041605714571501054020246 0ustar 00000000000000# # Copyright (c) 2001 Bizar Software Pty Ltd (http://www.bizarsoftware.com.au/) # This module is free software, and you may redistribute it and/or modify # under the same terms as Python, so long as this copyright message and # disclaimer are retained in their original form. # # IN NO EVENT SHALL BIZAR SOFTWARE PTY LTD BE LIABLE TO ANY PARTY FOR # DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING # OUT OF THE USE OF THIS CODE, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE # POSSIBILITY OF SUCH DAMAGE. # # BIZAR SOFTWARE PTY LTD SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, # BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS # FOR A PARTICULAR PURPOSE. THE CODE PROVIDED HEREUNDER IS ON AN "AS IS" # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # """ Relational database (SQL) backend common code. Basics: - map roundup classes to relational tables - automatically detect schema changes and modify the table schemas appropriately (we store the "database version" of the schema in the database itself as the only row of the "schema" table) - multilinks (which represent a many-to-many relationship) are handled through intermediate tables - journals are stored adjunct to the per-class tables - table names and columns have "_" prepended so the names can't clash with restricted names (like "order") - retirement is determined by the __retired__ column being > 0 Database-specific changes may generally be pushed out to the overridable sql_* methods, since everything else should be fairly generic. There's 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(). To force a unqiueness constraint on the key properties we put the item id into the __retired__ column duing retirement (so it's 0 for "active" items) and place a unqiueness constraint on key + __retired__. This is particularly important for the users class where multiple users may try to have the same username, with potentially many retired users with the same name. """ __docformat__ = 'restructuredtext' # standard python modules import copy import datetime import logging import os import re import time from hashlib import md5 # roundup modules from roundup import hyperdb, date, password, roundupdb, security, support from roundup.anypy.strings import b2s, bs2b, us2s, repr_export, eval_import from roundup.backends.blobfiles import FileStorage from roundup.backends.indexer_common import get_indexer from roundup.backends.indexer_common import Indexer as CommonIndexer from roundup.backends.sessions_rdbms import Sessions, OneTimeKeys from roundup.date import Range from roundup.hyperdb import String, Password, Date, Interval, Link, \ Multilink, DatabaseError, Boolean, Number, Integer from roundup.i18n import _ from roundup.mlink_expr import compile_expression # dummy value meaning "argument not passed" _marker = [] # python 3 doesn't have a unicode type try: unicode # noqa: F821 except NameError: unicode = str def _num_cvt(num): num = str(num) try: return int(num) except ValueError: return float(num) def _bool_cvt(value): if value in ('TRUE', 'FALSE'): return {'TRUE': 1, 'FALSE': 0}[value] # assume it's a number returned from the db API return int(value) def date_to_hyperdb_value(d): """ convert date d to a roundup date """ if isinstance(d, datetime.datetime): return date.Date(d) return date.Date(str(d).replace(' ', '.')) def connection_dict(config, dbnamestr=None): """ Used by Postgresql and MySQL to detemine the keyword args for opening the database connection.""" d = {} if dbnamestr: d[dbnamestr] = config.RDBMS_NAME for name in ('host', 'port', 'password', 'user', 'service', 'read_default_group', 'read_default_file'): cvar = 'RDBMS_'+name.upper() if config[cvar] is not None: d[name] = config[cvar] return d class IdListOptimizer: """ To prevent flooding the SQL parser of the underlaying db engine with "x IN (1, 2, 3, ..., )" collapses these cases to "x BETWEEN 1 AND ". """ def __init__(self): self.ranges = [] self.singles = [] def append(self, nid): """ Invariant: nid are ordered ascending """ if self.ranges: last = self.ranges[-1] if last[1] == nid-1: last[1] = nid return if self.singles: last = self.singles[-1] if last == nid-1: self.singles.pop() self.ranges.append([last, nid]) return self.singles.append(nid) def where(self, field, placeholder): ranges = self.ranges singles = self.singles if not singles and not ranges: return "(1=0)", [] # noqa: E701 if ranges: between = '%s BETWEEN %s AND %s' % ( field, placeholder, placeholder) stmnt = [between] * len(ranges) else: stmnt = [] if singles: stmnt.append('%s in (%s)' % ( field, ','.join([placeholder]*len(singles)))) return '(%s)' % ' OR '.join(stmnt), sum(ranges, []) + singles def __str__(self): return "ranges: %r / singles: %r" % (self.ranges, self.singles) class Database(FileStorage, hyperdb.Database, roundupdb.Database): """ Wrapper around an SQL database that presents a hyperdb interface. - some functionality is specific to the actual SQL database, hence the sql_* methods that are NotImplemented - we keep a cache of the latest N row fetches (where N is configurable). """ def __init__(self, config, journaltag=None): """ Open the database and load the schema from it. """ FileStorage.__init__(self, config.UMASK) roundupdb.Database.__init__(self) self.config, self.journaltag = config, journaltag self.dir = config.DATABASE self.classes = {} # Assign the indexer base class here. During schema # generation in open_connection, the min/max size for FTS # tokens is used when creating the database tables for # indexer=native full text search. These tables are always # created as part of the schema so that the admin can choose # indexer=native at some later date and "things will just # work" (TM). # # We would like to use get_indexer() to return the real # indexer class. However indexer=native-fts for postgres # requires a database connection (conn) to be defined when # calling get_indexer. The call to open_connection creates the # conn but also creates the schema if it is missing. if self.config['INDEXER'] != 'native-fts': self.indexer = get_indexer(config, self) else: self.indexer = CommonIndexer(self) self.security = security.Security(self) # additional transaction support for external files and the like self.transactions = [] # keep a cache of the N most recently retrieved rows of any kind # (classname, nodeid) = row self.cache_size = config.RDBMS_CACHE_SIZE self.clearCache() self.stats = {'cache_hits': 0, 'cache_misses': 0, 'get_items': 0, 'filtering': 0} # make sure the database directory exists if not os.path.isdir(self.config.DATABASE): os.makedirs(self.config.DATABASE) # database lock self.lockfile = None # Uppercase to not collide with Class names self.Session = None self.Otk = None # open a connection to the database, creating the "conn" attribute self.open_connection() # If indexer is native-fts, conn to db must be available. # so we set the real self.indexer value here, after db is open. if self.config['INDEXER'] == 'native-fts': self.indexer = get_indexer(config, self) def clearCache(self): self.cache = {} self.cache_lru = [] # upcall is necessary! roundupdb.Database.clearCache(self) def getSessionManager(self): if not self.Session: self.Session = Sessions(self) return self.Session def getOTKManager(self): if not self.Otk: self.Otk = OneTimeKeys(self) return self.Otk def open_connection(self): """ Open a connection to the database, creating it if necessary. Must call self.load_dbschema() """ raise NotImplementedError def sql(self, sql, args=None, cursor=None): """ Execute the sql with the optional args. """ self.log_debug('SQL %r %r' % (sql, args)) if not cursor: cursor = self.cursor if args: cursor.execute(sql, args) else: cursor.execute(sql) def sql_fetchone(self): """ Fetch a single row. If there's nothing to fetch, return None. """ return self.cursor.fetchone() def sql_fetchall(self): """ Fetch all rows. If there's nothing to fetch, return []. """ return self.cursor.fetchall() def sql_fetchiter(self): """ Fetch all row as a generator """ while True: row = self.cursor.fetchone() if not row: break # noqa: E701 yield row def search_stringquote(self, value): """ Quote a search string to escape magic search characters '%' and '_', also need to quote '\' (first) Then put '%' around resulting string for LIKE (or ILIKE) operator """ v = value.replace('\\', '\\\\') v = v.replace('%', '\\%') v = v.replace('_', '\\_') return '%' + v + '%' 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') schema = self.cursor.fetchone() if schema: # bandit - schema is trusted self.database_schema = eval(schema[0]) # nosec else: self.database_schema = {} def save_dbschema(self): """ Save the schema definition that the database currently implements """ s = repr(self.database_schema) self.sql('delete from schema') self.sql('insert into schema values (%s)' % self.arg, (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. """ super(Database, self).post_init() # upgrade the database for column type changes, new internal # tables, etc. save = self.upgrade_db() # handle changes in the schema tables = self.database_schema['tables'] for classname, spec in self.classes.items(): if classname in tables: dbspec = tables[classname] if self.update_class(spec, dbspec): tables[classname] = spec.schema() save = 1 else: self.create_class(spec) tables[classname] = spec.schema() save = 1 for classname in list(tables.keys()): if classname not in self.classes: self.drop_class(classname, tables[classname]) del tables[classname] save = 1 # update the database version of the schema if save: self.save_dbschema() # reindex the db if necessary if self.indexer.should_reindex(): self.reindex() # commit self.sql_commit() # update this number when we need to make changes to the SQL structure # of the backend database current_db_version = 8 db_version_updated = False 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: raise DatabaseError('attempting to run rev %d DATABASE with rev ' '%d CODE!' % (version, self.current_db_version)) if version == self.current_db_version: # nothing to do return 0 if version < 2: self.log_info('upgrade to version 2') # change the schema structure self.database_schema = {'tables': self.database_schema} # version 1 didn't have the actor column (note that in # MySQL this will also transition the tables to typed columns) self.add_new_columns_v2() # version 1 doesn't have the OTK, session and indexing in the # database self.create_version_2_tables() if version < 3: self.log_info('upgrade to version 3') self.fix_version_2_tables() if version < 4: self.log_info('upgrade to version 4') self.fix_version_3_tables() if version < 5: self.log_info('upgrade to version 5') self.fix_version_4_tables() if version < 6: self.log_info('upgrade to version 6') self.fix_version_5_tables() if version < 7: self.log_info('upgrade to version 7') self.fix_version_6_tables() if version < 8: self.log_info('upgrade to version 8') self.fix_version_7_tables() self.database_schema['version'] = self.current_db_version self.db_version_updated = True return 1 def fix_version_2_tables(self): # Default (used by sqlite): NOOP pass def fix_version_3_tables(self): # drop the shorter VARCHAR OTK column and add a new TEXT one for name in ('otk', 'session'): self.sql('DELETE FROM %ss' % name) self.sql('ALTER TABLE %ss DROP %s_value' % (name, name)) self.sql('ALTER TABLE %ss ADD %s_value TEXT' % (name, name)) def fix_version_4_tables(self): # note this is an explicit call now c = self.cursor for cn, klass in self.classes.items(): c.execute('select id from _%s where __retired__<>0' % (cn,)) for (id,) in c.fetchall(): c.execute('update _%s set __retired__=%s where id=%s' % ( cn, self.arg, self.arg), (id, id)) if klass.key: self.add_class_key_required_unique_constraint(cn, klass.key) def fix_version_5_tables(self): # Default (used by sqlite, postgres): NOOP # mysql overrides this because it is missing # __key_retired_idx index used to make # sure that the key is unique if it was created # as version 5. pass def fix_version_6_tables(self): # Default (used by nobody): NOOP # Each backend mysql, postgres, sqlite overrides this # You would think ALTER commands would be the same but nooo. pass def fix_version_7_tables(self): # Default (used by sqlite): NOOP # Each backend mysql, postgres overrides this # You would think ALTER commands would be the same but nooo. pass def _convert_journal_tables(self): """Get current journal table contents, drop the table and re-create""" c = self.cursor cols = ','.join('nodeid date tag action params'.split()) for klass in self.classes.values(): # slurp and drop sql = 'select %s from %s__journal order by date' % \ (cols, klass.classname) c.execute(sql) contents = c.fetchall() self.drop_journal_table_indexes(klass.classname) c.execute('drop table %s__journal' % klass.classname) # re-create and re-populate self.create_journal_table(klass) a = self.arg sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)' % ( klass.classname, cols, a, a, a, a, a) for row in contents: # no data conversion needed self.cursor.execute(sql, row) def _convert_string_properties(self): """Get current Class tables that contain String properties, and convert the VARCHAR columns to TEXT""" c = self.cursor for klass in self.classes.values(): # slurp and drop cols, mls = self.determine_columns(list(klass.properties.items())) scols = ','.join([i[0] for i in cols]) sql = 'select id,%s from _%s' % (scols, klass.classname) c.execute(sql) contents = c.fetchall() self.drop_class_table_indexes(klass.classname, klass.getkey()) c.execute('drop table _%s' % klass.classname) # re-create and re-populate self.create_class_table(klass, create_sequence=0) a = ','.join([self.arg for i in range(len(cols)+1)]) sql = 'insert into _%s (id,%s) values (%s)' % (klass.classname, scols, a) for row in contents: l = [] for entry in row: # mysql will already be a string - psql needs "help" if entry is not None and not isinstance(entry, type('')): entry = str(entry) l.append(entry) self.cursor.execute(sql, l) def refresh_database(self): self.post_init() def reindex(self, classname=None, show_progress=False): if classname: classes = [self.getclass(classname)] else: classes = list(self.classes.values()) for klass in classes: if show_progress: for nodeid in support.Progress('Reindex %s' % klass.classname, klass.list()): klass.index(nodeid) else: for nodeid in klass.list(): klass.index(nodeid) self.indexer.save_index() def checkpoint_data(self): """Call if you need to commit the state of the database so you can try to fix the error rather than rolling back Needed for postgres when importing data. """ pass def restore_connection_on_error(self): """on a database error/exception recover the db connection if left in an unusable state (e.g. postgres requires a rollback). """ pass # Used here in the generic backend to determine if the database # supports 'DOUBLE PRECISION' for floating point numbers. implements_double_precision = True hyperdb_to_sql_datatypes = { hyperdb.String : 'TEXT', # noqa: E203 hyperdb.Date : 'TIMESTAMP', # noqa: E203 hyperdb.Link : 'INTEGER', # noqa: E203 hyperdb.Interval : 'VARCHAR(255)', # noqa: E203 hyperdb.Password : 'VARCHAR(255)', # noqa: E203 hyperdb.Boolean : 'BOOLEAN', # noqa: E203 hyperdb.Number : 'REAL', # noqa: E203 hyperdb.Integer : 'INTEGER', # noqa: E203 } def hyperdb_to_sql_datatype(self, propclass, prop=None): datatype = self.hyperdb_to_sql_datatypes.get(propclass) if self.implements_double_precision and prop and \ isinstance(prop, Number) and prop.use_double: datatype = 'DOUBLE PRECISION' if datatype: return datatype for k, v in self.hyperdb_to_sql_datatypes.items(): if issubclass(propclass, k): return v raise ValueError('%r is not a hyperdb property class' % propclass) def determine_columns(self, properties): """ Figure the column names and multilink properties from the spec "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 = [ ('_actor', self.hyperdb_to_sql_datatype(hyperdb.Link)), ('_activity', self.hyperdb_to_sql_datatype(hyperdb.Date)), ('_creator', self.hyperdb_to_sql_datatype(hyperdb.Link)), ('_creation', self.hyperdb_to_sql_datatype(hyperdb.Date)), ] mls = [] # add the multilinks separately for col, prop in properties: # Computed props are not in the db if prop.computed: continue if isinstance(prop, Multilink): mls.append(col) continue if isinstance(prop, type('')): raise ValueError("string property spec!") # and prop.find('Multilink') != -1: # mls.append(col) datatype = self.hyperdb_to_sql_datatype(prop.__class__, prop) cols.append(('_'+col, datatype)) # Intervals stored as two columns if isinstance(prop, Interval): cols.append(('__'+col+'_int__', 'BIGINT')) cols.sort() return cols, mls def update_class(self, spec, old_spec, force=0): """ Determine the differences between the current spec and the database version of the spec, and update where necessary. If 'force' is true, update the database anyway. """ new_spec = spec.schema() new_spec[1].sort() old_spec[1].sort() if not force and new_spec == old_spec: # no changes return 0 if not self.config.RDBMS_ALLOW_ALTER: raise DatabaseError(_( 'ALTER operation disallowed: %(old)r -> %(new)r.') % { 'old': old_spec, 'new': new_spec}) logger = logging.getLogger('roundup.hyperdb.backend') logger.info('update_class %s' % spec.classname) logger.debug('old_spec %r' % (old_spec,)) logger.debug('new_spec %r' % (new_spec,)) # detect key prop change for potential index change keyprop_changes = {} if new_spec[0] != old_spec[0]: if old_spec[0]: keyprop_changes['remove'] = old_spec[0] if new_spec[0]: keyprop_changes['add'] = new_spec[0] # detect multilinks that have been removed, and drop their table old_has = {} for name, prop in old_spec[1]: old_has[name] = 1 if name in spec.properties and not spec.properties[name].computed: continue if prop.find('Multilink to') != -1: # first drop indexes. self.drop_multilink_table_indexes(spec.classname, name) # now the multilink table itself sql = 'drop table %s_%s' % (spec.classname, name) else: # if this is the key prop, drop the index first if old_spec[0] == prop: self.drop_class_table_key_index(spec.classname, name) del keyprop_changes['remove'] # drop the column sql = 'alter table _%s drop column _%s' % (spec.classname, name) self.sql(sql) # 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 'remove' in keyprop_changes: self.drop_class_table_key_index(spec.classname, keyprop_changes['remove']) # add new columns for propname, prop in new_spec[1]: if propname in old_has: continue prop = spec.properties[propname] if isinstance(prop, Multilink): self.create_multilink_table(spec, propname) else: # add the column coltype = self.hyperdb_to_sql_datatype(prop.__class__, prop) sql = 'alter table _%s add column _%s %s' % ( spec.classname, propname, coltype) self.sql(sql) # extra Interval column if isinstance(prop, Interval): sql = 'alter table _%s add column __%s_int__ BIGINT' % ( spec.classname, propname) self.sql(sql) # 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 we didn't add the key prop just then, but the key prop has # changed, we still need to add the new index if 'add' in keyprop_changes: self.create_class_table_key_index(spec.classname, keyprop_changes['add']) return 1 def determine_all_columns(self, spec): """Figure out the columns from the spec and also add internal columns """ cols, mls = self.determine_columns(list(spec.properties.items())) # add on our special columns cols.append(('id', 'INTEGER PRIMARY KEY')) cols.append(('__retired__', 'INTEGER DEFAULT 0')) return cols, mls def create_class_table(self, spec): """Create the class table for the given Class "spec". Creates the indexes too.""" cols, mls = self.determine_all_columns(spec) # create the base table scols = ','.join(['%s %s' % x for x in cols]) sql = 'create table _%s (%s)' % (spec.classname, scols) self.sql(sql) self.create_class_table_indexes(spec) return cols, mls def create_class_table_indexes(self, spec): """ create the class table for the given spec """ # create __retired__ index index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)' % ( spec.classname, spec.classname) self.sql(index_sql2) # create index for key property if spec.key: index_sql3 = 'create index _%s_%s_idx on _%s(_%s)' % ( spec.classname, spec.key, spec.classname, spec.key) self.sql(index_sql3) # and the unique index for key / retired(id) self.add_class_key_required_unique_constraint(spec.classname, spec.key) # TODO: create indexes on (selected?) Link property columns, as # they're more likely to be used for lookup def add_class_key_required_unique_constraint(self, cn, key): sql = '''create unique index _%s_key_retired_idx on _%s(__retired__, _%s)''' % (cn, cn, key) try: self.sql(sql) except Exception: # nosec # XXX catch e.g.: # _sqlite.DatabaseError: index _status_key_retired_idx # already exists pass def drop_class_table_indexes(self, cn, key): # drop the old table indexes first l = ['_%s_id_idx' % cn, '_%s_retired_idx' % cn] if key: l.append('_%s_%s_idx' % (cn, key)) table_name = '_%s' % cn for index_name in l: if not self.sql_index_exists(table_name, index_name): continue index_sql = 'drop index '+index_name self.sql(index_sql) def create_class_table_key_index(self, cn, key): """ create the class table for the given spec """ sql = 'create index _%s_%s_idx on _%s(_%s)' % (cn, key, cn, key) self.sql(sql) def drop_class_table_key_index(self, cn, key): table_name = '_%s' % cn index_name = '_%s_%s_idx' % (cn, key) if self.sql_index_exists(table_name, index_name): sql = 'drop index '+index_name self.sql(sql) # and now the retired unique index too index_name = '_%s_key_retired_idx' % cn if self.sql_index_exists(table_name, index_name): sql = 'drop index '+index_name self.sql(sql) def create_journal_table(self, spec): """ create the journal table for a class given the spec and already-determined cols """ # journal table sql = """create table %s__journal ( nodeid integer, date %s, tag varchar(255), action varchar(255), params text)""" % ( spec.classname, self.hyperdb_to_sql_datatype(hyperdb.Date)) self.sql(sql) self.create_journal_table_indexes(spec) def create_journal_table_indexes(self, spec): # index on nodeid sql = 'create index %s_journ_idx on %s__journal(nodeid)' % ( spec.classname, spec.classname) self.sql(sql) def drop_journal_table_indexes(self, classname): index_name = '%s_journ_idx' % classname if not self.sql_index_exists('%s__journal' % classname, index_name): return index_sql = 'drop index '+index_name self.sql(index_sql) def create_multilink_table(self, spec, ml): """ Create a multilink table for the "ml" property of the class given by the spec """ # create the table sql = 'create table %s_%s (linkid INTEGER, nodeid INTEGER)' % ( spec.classname, ml) self.sql(sql) self.create_multilink_table_indexes(spec, ml) def create_multilink_table_indexes(self, spec, ml): # create index on linkid index_sql = 'create index %s_%s_l_idx on %s_%s(linkid)' % ( spec.classname, ml, spec.classname, ml) self.sql(index_sql) # create index on nodeid index_sql = 'create index %s_%s_n_idx on %s_%s(nodeid)' % ( spec.classname, ml, spec.classname, ml) self.sql(index_sql) def drop_multilink_table_indexes(self, classname, ml): l = [ '%s_%s_l_idx' % (classname, ml), '%s_%s_n_idx' % (classname, ml) ] table_name = '%s_%s' % (classname, ml) for index_name in l: if not self.sql_index_exists(table_name, index_name): continue index_sql = 'drop index %s' % index_name self.sql(index_sql) def create_class(self, spec): """ Create a database table according to the given spec. """ if not self.config.RDBMS_ALLOW_CREATE: raise DatabaseError(_('CREATE operation disallowed: "%s".') % spec.classname) cols, mls = self.create_class_table(spec) self.create_journal_table(spec) # now create the multilink tables for ml in mls: self.create_multilink_table(spec, ml) def drop_class(self, cn, spec): """ Drop the given table from the database. Drop the journal and multilink tables too. """ if not self.config.RDBMS_ALLOW_DROP: raise DatabaseError(_('DROP operation disallowed: "%s".') % cn) properties = spec[1] # figure the multilinks mls = [] for propname, prop in properties: if isinstance(prop, Multilink): mls.append(propname) # drop class table and indexes self.drop_class_table_indexes(cn, spec[0]) self.drop_class_table(cn) # drop journal table and indexes self.drop_journal_table_indexes(cn) sql = 'drop table %s__journal' % cn self.sql(sql) for ml in mls: # drop multilink table and indexes self.drop_multilink_table_indexes(cn, ml) sql = 'drop table %s_%s' % (spec.classname, ml) self.sql(sql) def drop_class_table(self, cn): sql = 'drop table _%s' % cn self.sql(sql) # # Classes # def __getattr__(self, classname): """ A convenient way of calling self.getclass(classname). """ if classname in self.classes: return self.classes[classname] raise AttributeError(classname) def addclass(self, cl): """ Add a Class to the hyperdatabase. """ cn = cl.classname if cn in self.classes: raise ValueError(_('Class "%s" already defined.') % cn) self.classes[cn] = cl # add default Edit and View permissions self.security.addPermission( name="Create", klass=cn, description="User is allowed to create "+cn) 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) self.security.addPermission( name="Retire", klass=cn, description="User is allowed to retire "+cn) def getclasses(self): """ Return a list of the names of all existing classes. """ return sorted(self.classes) def getclass(self, classname): """Get the Class object representing a particular class. If 'classname' is not a valid class name, a KeyError is raised. """ try: return self.classes[classname] except KeyError: raise KeyError('There is no class called "%s"' % classname) def clear(self): """Delete all database contents. Note: I don't commit here, which is different behaviour to the "nuke from orbit" behaviour in the dbs. """ logging.getLogger('roundup.hyperdb.backend').info('clear') for cn in self.classes: sql = 'delete from _%s' % cn self.sql(sql) sql = 'delete from %s__journal' % cn self.sql(sql) # # Nodes # hyperdb_to_sql_value = { hyperdb.String : str, # noqa: E203 # fractional seconds by default hyperdb.Date : lambda x: x.formal(sep=' ', # noqa: E203 sec='%06.3f'), hyperdb.Link : int, # noqa: E203 hyperdb.Interval : str, # noqa: E203 hyperdb.Password : str, # noqa: E203 hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE', # noqa: E203 hyperdb.Number : lambda x: x, # noqa: E203 hyperdb.Integer : lambda x: x, # noqa: E203 # used in journal marshalling hyperdb.Multilink : lambda x: x, # noqa: E203 } def to_sql_value(self, propklass): fn = self.hyperdb_to_sql_value.get(propklass) if fn: return fn for k, v in self.hyperdb_to_sql_value.items(): if issubclass(propklass, k): return v raise ValueError('%r is not a hyperdb property class' % propklass) def _cache_del(self, key): del self.cache[key] self.cache_lru.remove(key) def _cache_refresh(self, key): self.cache_lru.remove(key) self.cache_lru.insert(0, key) def _cache_save(self, key, node): self.cache[key] = node # update the LRU self.cache_lru.insert(0, key) if len(self.cache_lru) > self.cache_size: del self.cache[self.cache_lru.pop()] def addnode(self, classname, nodeid, node): """ Add the specified node to its class's db. """ self.log_debug('addnode %s%s %r' % (classname, nodeid, node)) # determine the column definitions and multilink tables cl = self.classes[classname] cols, mls = self.determine_columns(list(cl.properties.items())) # we'll be supplied these props if we're doing an import values = node.copy() if 'creator' not in values: # add in the "calculated" properties (dupe so we don't affect # calling code's node assumptions) values['creation'] = values['activity'] = date.Date() values['actor'] = values['creator'] = self.getuid() cl = self.classes[classname] props = cl.getprops(protected=1) del props['id'] # default the non-multilink columns for col, prop in props.items(): if col not in values: if isinstance(prop, Multilink): values[col] = [] else: values[col] = None # clear this node out of the cache if it's in there key = (classname, nodeid) if key in self.cache: self._cache_del(key) # figure the values to insert vals = [] for col, _dt in cols: # this is somewhat dodgy.... if col.endswith('_int__'): # XXX eugh, this test suxxors value = values[col[2:-6]] # this is an Interval special "int" column if value is not None: vals.append(value.as_seconds()) else: vals.append(value) continue prop = props[col[1:]] value = values[col[1:]] if value is not None: value = self.to_sql_value(prop.__class__)(value) vals.append(value) vals.append(nodeid) vals = tuple(vals) # make sure the ordering is correct for column name -> column value s = ','.join([self.arg for x in cols]) + ',%s' % self.arg cols = ','.join([col for col, dt in cols]) + ',id' # perform the inserts sql = 'insert into _%s (%s) values (%s)' % (classname, cols, s) self.sql(sql, vals) # insert the multilink rows for col in mls: t = '%s_%s' % (classname, col) for entry in node[col]: sql = 'insert into %s (linkid, nodeid) values (%s,%s)' % ( t, self.arg, self.arg) self.sql(sql, (entry, nodeid)) def setnode(self, classname, nodeid, values, multilink_changes=None): """ Change the specified node. """ if multilink_changes is None: multilink_changes = {} # noqa: E701 self.log_debug('setnode %s%s %r' % (classname, nodeid, values)) # clear this node out of the cache if it's in there key = (classname, nodeid) if key in self.cache: self._cache_del(key) cl = self.classes[classname] props = cl.getprops() cols = [] mls = [] # add the multilinks separately for col in values: prop = props[col] if isinstance(prop, Multilink): mls.append(col) elif isinstance(prop, Interval): # Intervals store the seconds value too cols.append(col) # extra leading '_' added by code below cols.append('_' + col + '_int__') else: cols.append(col) cols.sort() # figure the values to insert vals = [] for col in cols: if col.endswith('_int__'): # XXX eugh, this test suxxors # Intervals store the seconds value too col = col[1:-6] prop = props[col] value = values[col] if value is None: vals.append(None) else: vals.append(value.as_seconds()) else: prop = props[col] value = values[col] if value is None: e = None else: e = self.to_sql_value(prop.__class__)(value) vals.append(e) vals.append(int(nodeid)) vals = tuple(vals) # if there's any updates to regular columns, do them if cols: # make sure the ordering is correct for column name -> column value s = ','.join(['_%s=%s' % (x, self.arg) for x in cols]) cols = ','.join(cols) # perform the update sql = 'update _%s set %s where id=%s' % (classname, s, self.arg) self.sql(sql, vals) # we're probably coming from an import, not a change if not multilink_changes: for name in mls: prop = props[name] value = values[name] t = '%s_%s' % (classname, name) # clear out previous values for this node # XXX numeric ids self.sql('delete from %s where nodeid=%s' % (t, self.arg), (nodeid,)) # insert the values for this node for entry in values[name]: sql = 'insert into %s (linkid, nodeid) values (%s,%s)' % ( t, self.arg, self.arg) # XXX numeric ids self.sql(sql, (entry, nodeid)) # we have multilink changes to apply for col, (add, remove) in multilink_changes.items(): tn = '%s_%s' % (classname, col) if add: sql = 'insert into %s (nodeid, linkid) values (%s,%s)' % ( tn, self.arg, self.arg) for addid in add: # XXX numeric ids self.sql(sql, (int(nodeid), int(addid))) if remove: s = ','.join([self.arg]*len(remove)) sql = 'delete from %s where nodeid=%s and linkid in (%s)' % ( tn, self.arg, s) # XXX numeric ids self.sql(sql, [int(nodeid)] + remove) sql_to_hyperdb_value = { hyperdb.String : us2s, # noqa: E203 hyperdb.Date : date_to_hyperdb_value, # noqa: E203 # hyperdb.Link : int, # XXX numeric ids hyperdb.Link : str, # noqa: E203 hyperdb.Interval : date.Interval, # noqa: E203 hyperdb.Password : lambda x: password.Password(encrypted=x), # noqa: E203 hyperdb.Boolean : _bool_cvt, # noqa: E203 hyperdb.Number : _num_cvt, # noqa: E203 hyperdb.Integer : int, # noqa: E203 # used in journal marshalling hyperdb.Multilink : lambda x: x, # noqa: E203 } def to_hyperdb_value(self, propklass): fn = self.sql_to_hyperdb_value.get(propklass) if fn: return fn for k, v in self.sql_to_hyperdb_value.items(): if issubclass(propklass, k): return v raise ValueError('%r is not a hyperdb property class' % propklass) def _materialize_multilink(self, classname, nodeid, node, propname): """ evaluation of single Multilink (lazy eval may have skipped this) """ if propname not in node: prop = self.getclass(classname).properties[propname] tn = prop.table_name lid = prop.linkid_name nid = prop.nodeid_name w = '' joi = '' if prop.computed: if isinstance(prop.rev_property, Link): w = ' and %s.__retired__=0' % tn else: tn2 = '_' + prop.classname joi = ', %s' % tn2 w = ' and %s.%s=%s.id and %s.__retired__=0' % ( tn, lid, tn2, tn2) cursor = self.sql_new_cursor(name='_materialize_multilink') sql = 'select %s from %s%s where %s=%s%s' % (lid, tn, joi, nid, self.arg, w) self.sql(sql, (nodeid,), cursor) # Reduce this to only the first row (the ID), this can save a # lot of space for large query results (not using fetchall) node[propname] = [str(x) for x in sorted(int(r[0]) for r in cursor)] cursor.close() def _materialize_multilinks(self, classname, nodeid, node, props=None): """ get all Multilinks of a node (lazy eval may have skipped this) """ cl = self.classes[classname] props = props or [pn for (pn, p) in cl.properties.items() if isinstance(p, Multilink)] for propname in props: if propname not in node: self._materialize_multilink(classname, nodeid, node, propname) def getnode(self, classname, nodeid, fetch_multilinks=True): """ Get a node from the database. For optimisation optionally we don't fetch multilinks (lazy Multilinks). But for internal database operations we need them. """ # see if we have this node cached key = (classname, nodeid) if key in self.cache: # push us back to the top of the LRU self._cache_refresh(key) if __debug__: self.stats['cache_hits'] += 1 # return the cached information if fetch_multilinks: self._materialize_multilinks(classname, nodeid, self.cache[key]) return self.cache[key] if __debug__: self.stats['cache_misses'] += 1 start_t = time.time() # figure the columns we're fetching cl = self.classes[classname] cols, mls = self.determine_columns(list(cl.properties.items())) scols = ','.join([col for col, dt in cols]) # perform the basic property fetch sql = 'select %s from _%s where id=%s' % (scols, classname, self.arg) self.sql(sql, (nodeid,)) values = self.sql_fetchone() if values is None: raise IndexError('no such %s %s' % (classname, nodeid)) # make up the node node = {} props = cl.getprops(protected=1) for col in range(len(cols)): name = cols[col][0][1:] if name.endswith('_int__'): # XXX eugh, this test suxxors # ignore the special Interval-as-seconds column continue value = values[col] if value is not None: value = self.to_hyperdb_value(props[name].__class__)(value) node[name] = value if fetch_multilinks and mls: self._materialize_multilinks(classname, nodeid, node, mls) # save off in the cache key = (classname, nodeid) self._cache_save(key, node) if __debug__: self.stats['get_items'] += (time.time() - start_t) return node def destroynode(self, classname, nodeid): """Remove a node from the database. Called exclusively by the destroy() method on Class. """ message = 'destroynode %s%s' % (classname, nodeid) logging.getLogger('roundup.hyperdb.backend').info(message) # make sure the node exists if not self.hasnode(classname, nodeid): raise IndexError('%s has no node %s' % (classname, nodeid)) # see if we have this node cached if (classname, nodeid) in self.cache: del self.cache[(classname, nodeid)] # see if there's any obvious commit actions that we should get rid of for entry in self.transactions[:]: if entry[1][:2] == (classname, nodeid): self.transactions.remove(entry) # now do the SQL sql = 'delete from _%s where id=%s' % (classname, self.arg) self.sql(sql, (nodeid,)) # remove from multilnks cl = self.getclass(classname) x, mls = self.determine_columns(list(cl.properties.items())) for col in mls: # get the link ids sql = 'delete from %s_%s where nodeid=%s' % (classname, col, self.arg) self.sql(sql, (nodeid,)) # remove journal entries sql = 'delete from %s__journal where nodeid=%s' % ( classname, self.arg) self.sql(sql, (nodeid,)) # cleanup any blob filestorage when we commit self.transactions.append((FileStorage.destroy, (self, classname, nodeid))) def hasnode(self, classname, nodeid): """ Determine if the database has a given node. """ # nodeid (aka link) is sql type INTEGER. max positive value # for INTEGER is 2^31-1 for Postgres and MySQL. The max # positive value for SqLite is 2^63 -1, so arguably this check # needs to adapt for the type of the RDBMS. For right now, # choose lowest common denominator. if int(nodeid) >= 2**31: # value out of range return false return 0 # If this node is in the cache, then we do not need to go to # the database. (We don't consider this an LRU hit, though.) if (classname, nodeid) in self.cache: # Return 1, not True, to match the type of the result of # the SQL operation below. return 1 sql = 'select count(*) from _%s where id=%s' % (classname, self.arg) self.sql(sql, (nodeid,)) return int(self.cursor.fetchone()[0]) def countnodes(self, classname): """ Count the number of nodes that exist for a particular Class. """ sql = 'select count(*) from _%s' % classname self.sql(sql) return self.cursor.fetchone()[0] def addjournal(self, classname, nodeid, action, params, creator=None, creation=None): """ Journal the Action 'action' may be: 'set' -- 'params' is a dictionary of property values 'create' -- 'params' is an empty dictionary as of Wed Nov 06 11:38:43 2002 +0000 'link' or 'unlink' -- 'params' is (classname, nodeid, propname) 'retired' or 'restored' -- 'params' is None 'creator' -- the user performing the action, which defaults to the current user. """ # handle supply of the special journalling parameters (usually # supplied on importing an existing database) if creator: journaltag = creator else: journaltag = self.getuid() if creation: journaldate = creation else: journaldate = date.Date() # create the journal entry cols = 'nodeid,date,tag,action,params' self.log_debug('addjournal %s%s %r %s %s %r' % ( classname, nodeid, journaldate, journaltag, action, params)) # make the journalled data marshallable if isinstance(params, type({})): self._journal_marshal(params, classname) params = repr_export(params) dc = self.to_sql_value(hyperdb.Date) journaldate = dc(journaldate) self.save_journal(classname, cols, nodeid, journaldate, journaltag, action, params) def setjournal(self, classname, nodeid, journal): """Set the journal to the "journal" list.""" # clear out any existing entries self.sql('delete from %s__journal where nodeid=%s' % ( classname, self.arg), (nodeid,)) # create the journal entry cols = 'nodeid,date,tag,action,params' dc = self.to_sql_value(hyperdb.Date) for nodeid, journaldate, journaltag, action, params in journal: self.log_debug('addjournal %s%s %r %s %s %r' % ( classname, nodeid, journaldate, journaltag, action, params)) # make the journalled data marshallable if isinstance(params, type({})): self._journal_marshal(params, classname) params = repr_export(params) self.save_journal(classname, cols, nodeid, dc(journaldate), journaltag, action, params) def _journal_marshal(self, params, classname): """Convert the journal params values into safely repr'able and eval'able values.""" properties = self.getclass(classname).getprops() for param, value in params.items(): if not value: continue property = properties[param] cvt = self.to_sql_value(property.__class__) if isinstance(property, Password): params[param] = cvt(value) elif isinstance(property, Date): params[param] = cvt(value) elif isinstance(property, Interval): params[param] = cvt(value) elif isinstance(property, Boolean): params[param] = cvt(value) def getjournal(self, classname, nodeid): """ get the journal for id """ # make sure the node exists if not self.hasnode(classname, nodeid): raise IndexError('%s has no node %s' % (classname, nodeid)) cols = ','.join('nodeid date tag action params'.split()) journal = self.load_journal(classname, cols, nodeid) # now unmarshal the data dc = self.to_hyperdb_value(hyperdb.Date) res = [] properties = self.getclass(classname).getprops() for nodeid, date_stamp, user, action, params in journal: params = eval_import(params) if isinstance(params, type({})): for param, value in params.items(): if not value: continue property = properties.get(param, None) if property is None: # deleted property continue cvt = self.to_hyperdb_value(property.__class__) if isinstance(property, Password): params[param] = password.JournalPassword(value) elif isinstance(property, Date): params[param] = cvt(value) elif isinstance(property, Interval): params[param] = cvt(value) elif isinstance(property, Boolean): params[param] = cvt(value) # XXX numeric ids res.append((str(nodeid), dc(date_stamp), user, action, params)) return res def save_journal(self, classname, cols, nodeid, journaldate, journaltag, action, params): """ Save the journal entry to the database """ entry = (nodeid, journaldate, journaltag, action, params) # do the insert a = self.arg sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)' % ( classname, cols, a, a, a, a, a) self.sql(sql, entry) def load_journal(self, classname, cols, nodeid): """ Load the journal from the database """ # now get the journal entries sql = 'select %s from %s__journal where nodeid=%s order by date' % ( cols, classname, self.arg) self.sql(sql, (nodeid,)) return self.cursor.fetchall() def pack(self, pack_before): """ Delete all journal entries except "create" before 'pack_before'. """ date_stamp = self.to_sql_value(Date)(pack_before) # do the delete for classname in self.classes: sql = "delete from %s__journal where date<%s and "\ "action<>'create'" % (classname, self.arg) self.sql(sql, (date_stamp,)) def sql_commit(self): """ Actually commit to the database. """ logging.getLogger('roundup.hyperdb.backend').info('commit') self.conn.commit() # open a new cursor for subsequent work self.cursor = self.conn.cursor() def sql_new_cursor(self, conn=None, *args, **kw): """ Create new cursor, this may need additional parameters for performance optimization for different backends. """ if conn is None: conn = self.conn return conn.cursor() def commit(self): """ Commit the current transactions. Save all data changed since the database was opened or since the last commit() or rollback(). """ # commit the database self.sql_commit() # session and otk are committed with the db but not the other # way round if self.Session: self.Session.commit() if self.Otk: self.Otk.commit() # now, do all the other transaction stuff for method, args in self.transactions: method(*args) # save the indexer self.indexer.save_index() # clear out the transactions self.transactions = [] # clear the cache: Don't carry over cached values from one # transaction to the next (there may be other changes from other # transactions) self.clearCache() def sql_rollback(self): self.conn.rollback() def rollback(self): """ Reverse all actions from the current transaction. Undo all the changes made since the database was opened or the last commit() or rollback() was performed. """ logging.getLogger('roundup.hyperdb.backend').info('rollback') self.sql_rollback() # roll back "other" transaction stuff for method, args in self.transactions: # delete temporary files if method == self.doStoreFile: self.rollbackStoreFile(*args) self.transactions = [] # clear the cache self.clearCache() def sql_close(self): logging.getLogger('roundup.hyperdb.backend').info('close') self.conn.close() def close(self): """ Close off the connection. """ self.indexer.close() self.sql_close() if self.Session: self.Session.close() self.Session = None if self.Otk: self.Otk.close() self.Otk = None # # The base Class class # class Class(hyperdb.Class): """ The handle to a particular class of nodes in a hyperdatabase. All methods except __repr__ and getnode must be implemented by a concrete backend Class. """ # For many databases the LIKE operator ignores case. # Postgres and Oracle have an ILIKE operator to support this. # We define the default here, can be changed in derivative class case_insensitive_like = 'LIKE' # For some databases (mysql) the = operator for strings ignores case. # We define the default here, can be changed in derivative class case_sensitive_equal = '=' # Some DBs order NULL values last. Set this variable in the backend # for prepending an order by clause for each attribute that causes # correct sort order for NULLs. Examples: # order_by_null_values = '(%s is not NULL)' # order_by_null_values = 'notnull(%s)' # The format parameter is replaced with the attribute. order_by_null_values = None # Assuming DBs can do subselects, overwrite if they cannot. supports_subselects = True def schema(self): """ A dumpable version of the schema that we can store in the database """ return (self.key, [(x, repr(y)) for x, y in self.properties.items() if not y.computed]) def enableJournalling(self): """Turn journalling on for this class """ self.do_journal = 1 def disableJournalling(self): """Turn journalling off for this class """ self.do_journal = 0 # Editing nodes: def create(self, **propvalues): """ Create a new node of this class and return its id. The keyword arguments in 'propvalues' map property names to values. The values of arguments must be acceptable for the types of their corresponding properties or a TypeError is raised. If this class has a key property, it must be present and its value must not collide with other key strings or a ValueError is raised. Any other properties on this class that are missing from the 'propvalues' dictionary are set to None. If an id in a link or multilink property does not refer to a valid node, an IndexError is raised. """ self.fireAuditors('create', None, propvalues) newid = self.create_inner(**propvalues) self.fireReactors('create', newid, None) return newid def create_inner(self, **propvalues): """ Called by create, in-between the audit and react calls. """ if 'id' in propvalues: raise KeyError('"id" is reserved') if self.db.journaltag is None: raise DatabaseError(_('Database open read-only')) if ('creator' in propvalues or 'actor' in propvalues or 'creation' in propvalues or 'activity' in propvalues): raise KeyError('"creator", "actor", "creation" and ' '"activity" are reserved') for p in propvalues: prop = self.properties[p] if prop.computed: raise KeyError('"%s" is a computed property' % p) # new node's id newid = self.db.newid(self.classname) # validate propvalues num_re = re.compile(r'^\d+$') for key, value in propvalues.items(): if key == self.key: try: self.lookup(value) except KeyError: pass else: raise ValueError('node with key "%s" exists' % value) # try to handle this property try: prop = self.properties[key] except KeyError: raise KeyError('"%s" has no property "%s"' % (self.classname, key)) if value is not None and isinstance(prop, Link): if not isinstance(value, type('')): raise ValueError('link value must be String') link_class = self.properties[key].classname # if it isn't a number, it's a key if not num_re.match(value): try: value = self.db.classes[link_class].lookup(value) except (TypeError, KeyError): raise IndexError('new property "%s": %s not a %s' % ( key, value, link_class)) elif not self.db.getclass(link_class).hasnode(value): raise IndexError('%s has no node %s' % (link_class, value)) # save off the value propvalues[key] = value # register the link with the newly linked node if self.do_journal and self.properties[key].do_journal: self.db.addjournal(link_class, value, 'link', (self.classname, newid, key)) elif isinstance(prop, Multilink): if value is None: value = [] if not hasattr(value, '__iter__') or \ isinstance(value, type('')): raise TypeError('new property "%s" not an iterable of ids' % key) # clean up and validate the list of links link_class = self.properties[key].classname l = [] for entry in value: if not isinstance(entry, type('')): raise ValueError('"%s" multilink value (%r) ' 'must contain Strings' % ( key, value)) # if it isn't a number, it's a key if not num_re.match(entry): try: entry = self.db.classes[link_class].lookup(entry) except (TypeError, KeyError): raise IndexError('new property "%s": %s not a %s' % ( key, entry, self.properties[key].classname)) l.append(entry) value = l propvalues[key] = value # handle additions for nodeid in value: if not self.db.getclass(link_class).hasnode(nodeid): raise IndexError('%s has no node %s' % (link_class, nodeid)) # register the link with the newly linked node if self.do_journal and self.properties[key].do_journal: self.db.addjournal(link_class, nodeid, 'link', (self.classname, newid, key)) elif isinstance(prop, String): if not isinstance(value, (str, unicode)): raise TypeError('new property "%s" not a string' % key) if prop.indexme: self.db.indexer.add_text((self.classname, newid, key), value) elif isinstance(prop, Password): if not isinstance(value, password.Password): raise TypeError('new property "%s" not a Password' % key) elif isinstance(prop, Date): if value is not None and not isinstance(value, date.Date): raise TypeError('new property "%s" not a Date' % key) elif isinstance(prop, Interval): if value is not None and not isinstance(value, date.Interval): raise TypeError('new property "%s" not an Interval' % key) elif value is not None and isinstance(prop, Number): try: float(value) except ValueError: raise TypeError('new property "%s" not numeric' % key) elif value is not None and isinstance(prop, Integer): try: int(value) except ValueError: raise TypeError('new property "%s" not integer' % key) elif value is not None and isinstance(prop, Boolean): try: int(value) except ValueError: raise TypeError('new property "%s" not boolean' % key) # make sure there's data where there needs to be for key, prop in self.properties.items(): if key in propvalues: continue if key == self.key: raise ValueError('key property "%s" is required' % key) if isinstance(prop, Multilink): propvalues[key] = [] else: propvalues[key] = None # done self.db.addnode(self.classname, newid, propvalues) if self.do_journal: self.db.addjournal(self.classname, newid, ''"create", {}) # XXX numeric ids return str(newid) def get(self, nodeid, propname, default=_marker, cache=1): """Get the value of a property on an existing node of this class. 'nodeid' must be the id of an existing node of this class or an IndexError is raised. 'propname' must be the name of a property of this class or a KeyError is raised. 'cache' exists for backwards compatibility, and is not used. """ if propname == 'id': return nodeid # get the node's dict d = self.db.getnode(self.classname, nodeid, fetch_multilinks=False) # handle common case -- that property is in dict -- first # if None and one of creator/creation actor/activity return None if propname in d: r = d[propname] # return copy of our list if isinstance(r, list): return r[:] if r is not None: return r elif propname in ('creation', 'activity', 'creator', 'actor'): return r # propname not in d: if propname == 'creation' or propname == 'activity': return date.Date() if propname == 'creator' or propname == 'actor': return self.db.getuid() # get the property (raises KeyError if invalid) prop = self.properties[propname] # lazy evaluation of Multilink if propname not in d and isinstance(prop, Multilink): self.db._materialize_multilink(self.classname, nodeid, d, propname) # handle there being no value in the table for the property if propname not in d or d[propname] is None: if default is _marker: if isinstance(prop, Multilink): return [] else: return None else: return default # don't pass our list to other code if isinstance(prop, Multilink): return d[propname][:] return d[propname] def set(self, nodeid, **propvalues): """Modify a property on an existing node of this class. 'nodeid' must be the id of an existing node of this class or an IndexError is raised. Each key in 'propvalues' must be the name of a property of this class or a KeyError is raised. All values in 'propvalues' must be acceptable types for their corresponding properties or a TypeError is raised. If the value of the key property is set, it must not collide with other key strings or a ValueError is raised. If the value of a Link or Multilink property contains an invalid node id, a ValueError is raised. """ self.fireAuditors('set', nodeid, propvalues) oldvalues = copy.deepcopy(self.db.getnode(self.classname, nodeid)) propvalues = self.set_inner(nodeid, **propvalues) self.fireReactors('set', nodeid, oldvalues) return propvalues def set_inner(self, nodeid, **propvalues): """ Called by set, in-between the audit and react calls. """ if not propvalues: return propvalues if ('creator' in propvalues or 'actor' in propvalues or 'creation' in propvalues or 'activity' in propvalues): raise KeyError('"creator", "actor", "creation" and ' '"activity" are reserved') if 'id' in propvalues: raise KeyError('"id" is reserved') for p in propvalues: prop = self.properties[p] if prop.computed: raise KeyError('"%s" is a computed property' % p) if self.db.journaltag is None: raise DatabaseError(_('Database open read-only')) node = self.db.getnode(self.classname, nodeid) if self.is_retired(nodeid): raise IndexError('Requested item is retired') num_re = re.compile(r'^\d+$') # make a copy of the values dictionary - we'll modify the contents propvalues = propvalues.copy() # if the journal value is to be different, store it in here journalvalues = {} # remember the add/remove stuff for multilinks, making it easier # for the Database layer to do its stuff multilink_changes = {} # omit quiet properties from history/changelog quiet_props = [] for propname, value in list(propvalues.items()): # check to make sure we're not duplicating an existing key if propname == self.key and node[propname] != value: try: self.lookup(value) except KeyError: pass else: raise ValueError('node with key "%s" exists' % value) # this will raise the KeyError if the property isn't valid # ... we don't use getprops() here because we only care about # the writeable properties. try: prop = self.properties[propname] except KeyError: raise KeyError('"%s" has no property named "%s"' % ( self.classname, propname)) # if the value's the same as the existing value, no sense in # doing anything current = node.get(propname, None) if value == current: del propvalues[propname] continue journalvalues[propname] = current # do stuff based on the prop type if isinstance(prop, Link): link_class = prop.classname # if it isn't a number, it's a key if value is not None and not isinstance(value, str): raise ValueError('property "%s" link value be a string' % ( propname)) if isinstance(value, str) and not num_re.match(value): try: value = self.db.classes[link_class].lookup(value) except (TypeError, KeyError): raise IndexError('new property "%s": %s not a %s' % ( propname, value, prop.classname)) if (value is not None and not self.db.getclass(link_class).hasnode(value)): raise IndexError('%s has no node %s' % (link_class, value)) if self.do_journal and prop.do_journal: # register the unlink with the old linked node if node[propname] is not None: self.db.addjournal( link_class, node[propname], ''"unlink", (self.classname, nodeid, propname)) # register the link with the newly linked node if value is not None: self.db.addjournal(link_class, value, ''"link", (self.classname, nodeid, propname)) elif isinstance(prop, Multilink): if value is None: value = [] if not hasattr(value, '__iter__') or isinstance(value, str): raise TypeError('new property "%s" not an iterable of' ' ids' % propname) link_class = self.properties[propname].classname l = [] for entry in value: # if it isn't a number, it's a key if not isinstance(entry, str): raise ValueError('new property "%s" link value ' 'must be a string' % propname) if not num_re.match(entry): try: entry = self.db.classes[link_class].lookup(entry) except (TypeError, KeyError): raise IndexError( 'new property "%s": %s not a %s' % ( propname, entry, self.properties[propname].classname)) l.append(entry) value = l propvalues[propname] = value # figure the journal entry for this property add = [] remove = [] # handle removals if propname in node: l = node[propname] else: l = [] for id in l[:]: if id in value: continue # register the unlink with the old linked node if self.do_journal and self.properties[propname].do_journal: self.db.addjournal(link_class, id, 'unlink', (self.classname, nodeid, propname)) l.remove(id) remove.append(id) # handle additions for id in value: if id in l: continue # We can safely check this condition after # checking that this is an addition to the # multilink since the condition was checked for # existing entries at the point they were added to # the multilink. Since the hasnode call will # result in a SQL query, it is more efficient to # avoid the check if possible. if not self.db.getclass(link_class).hasnode(id): raise IndexError('%s has no node %s' % (link_class, id)) # register the link with the newly linked node if self.do_journal and self.properties[propname].do_journal: self.db.addjournal(link_class, id, 'link', (self.classname, nodeid, propname)) l.append(id) add.append(id) # figure the journal entry l = [] if add: l.append(('+', add)) if remove: l.append(('-', remove)) multilink_changes[propname] = (add, remove) if l: journalvalues[propname] = tuple(l) elif isinstance(prop, String): if value is not None and not isinstance(value, (str, unicode)): raise TypeError( 'new property "%s" not a string' % propname) if prop.indexme: if value is None: value = '' # noqa: E701 self.db.indexer.add_text( (self.classname, nodeid, propname), value) elif isinstance(prop, Password): if not isinstance(value, password.Password): raise TypeError( 'new property "%s" not a Password' % propname) propvalues[propname] = value journalvalues[propname] = \ current and password.JournalPassword(current) elif value is not None and isinstance(prop, Date): if not isinstance(value, date.Date): raise TypeError('new property "%s" not a Date' % propname) propvalues[propname] = value elif value is not None and isinstance(prop, Interval): if not isinstance(value, date.Interval): raise TypeError('new property "%s" not an ' 'Interval' % propname) propvalues[propname] = value elif value is not None and isinstance(prop, Number): try: float(value) except ValueError: raise TypeError('new property "%s" not numeric' % propname) elif value is not None and isinstance(prop, Integer): try: int(value) except ValueError: raise TypeError('new property "%s" not integer' % propname) elif value is not None and isinstance(prop, Boolean): try: int(value) except ValueError: raise TypeError('new property "%s" not boolean' % propname) # record quiet properties to omit from history/changelog if prop.quiet: quiet_props.append(propname) # nothing to do? if not propvalues: return propvalues # update the activity time propvalues['activity'] = date.Date() propvalues['actor'] = self.db.getuid() # do the set self.db.setnode(self.classname, nodeid, propvalues, multilink_changes) # remove the activity props now they're handled del propvalues['activity'] del propvalues['actor'] # journal the set if self.do_journal: self.db.addjournal(self.classname, nodeid, ''"set", journalvalues) # remove quiet properties from output for propname in quiet_props: if propname in propvalues: del propvalues[propname] return propvalues def retire(self, nodeid): """Retire a node. The properties on the node remain available from the get() method, and the node's id is never reused. Retired nodes are not returned by the find(), list(), or lookup() methods, and other nodes may reuse the values of their key properties. """ if self.db.journaltag is None: raise DatabaseError(_('Database open read-only')) self.fireAuditors('retire', nodeid, None) # use the arg for __retired__ to cope with any odd database type # conversion (hello, sqlite) sql = 'update _%s set __retired__=%s where id=%s' % ( self.classname, self.db.arg, self.db.arg) self.db.sql(sql, (nodeid, nodeid)) if self.do_journal: self.db.addjournal(self.classname, nodeid, ''"retired", None) self.fireReactors('retire', nodeid, None) def restore(self, nodeid): """Restore a retired node. Make node available for all operations like it was before retirement. """ if self.db.journaltag is None: raise DatabaseError(_('Database open read-only')) node = self.db.getnode(self.classname, nodeid) # check if key property was overrided key = self.getkey() try: self.lookup(node[key]) except KeyError: pass else: raise KeyError("Key property (%s) of retired node clashes " "with existing one (%s)" % (key, node[key])) self.fireAuditors('restore', nodeid, None) # use the arg for __retired__ to cope with any odd database type # conversion (hello, sqlite) sql = 'update _%s set __retired__=%s where id=%s' % ( self.classname, self.db.arg, self.db.arg) self.db.sql(sql, (0, nodeid)) if self.do_journal: self.db.addjournal(self.classname, nodeid, ''"restored", None) self.fireReactors('restore', nodeid, None) def is_retired(self, nodeid): """Return true if the node is rerired """ sql = 'select __retired__ from _%s where id=%s' % (self.classname, self.db.arg) self.db.sql(sql, (nodeid,)) return int(self.db.sql_fetchone()[0]) > 0 def destroy(self, nodeid): """Destroy a node. WARNING: this method should never be used except in extremely rare situations where there could never be links to the node being deleted WARNING: use retire() instead WARNING: the properties of this node will not be available ever again WARNING: really, use retire() instead Well, I think that's enough warnings. This method exists mostly to support the session storage of the cgi interface. The node is completely removed from the hyperdb, including all journal entries. It will no longer be available, and will generally break code if there are any references to the node. """ if self.db.journaltag is None: raise DatabaseError(_('Database open read-only')) self.db.destroynode(self.classname, nodeid) # Locating nodes: def hasnode(self, nodeid): """Determine if the given nodeid actually exists """ return self.db.hasnode(self.classname, nodeid) def setkey(self, propname): """Select a String property of this class to be the key property. 'propname' must be the name of a String property of this class or None, or a TypeError is raised. The values of the key property on all existing nodes must be unique or a ValueError is raised. """ prop = self.getprops()[propname] if not isinstance(prop, String): raise TypeError('key properties must be String') self.key = propname def getkey(self): """Return the name of the key property for this class or None.""" return self.key def lookup(self, keyvalue): """Locate a particular node by its key property and return its id. If this class has no key property, a TypeError is raised. If the 'keyvalue' matches one of the values for the key property among the nodes in this class, the matching node's id is returned; otherwise a KeyError is raised. """ if not self.key: raise TypeError('No key property set for class %s' % self.classname) # use the arg to handle any odd database type conversion (hello, # sqlite) sql = "select id from _%s where _%s=%s and __retired__=%s" % ( self.classname, self.key, self.db.arg, self.db.arg) self.db.sql(sql, (str(keyvalue), 0)) # see if there was a result that's not retired row = self.db.sql_fetchone() if not row: raise KeyError('No key (%s) value "%s" for "%s"' % ( self.key, keyvalue, self.classname)) # return the id # XXX numeric ids return str(row[0]) def find(self, **propspec): """Get the ids of nodes in this class which link to the given nodes. 'propspec' consists of keyword args propname=nodeid or propname={nodeid:1, } 'propname' must be the name of a property in this class, or a KeyError is raised. That property must be a Link or Multilink property, or a TypeError is raised. Any node in this class whose 'propname' property links to any of the nodeids will be returned. Examples:: db.issue.find(messages='1') db.issue.find(messages={'1':1,'3':1}, files={'7':1}) """ # shortcut if not propspec: return [] # validate the args props = self.getprops() for propname in propspec.keys(): # check the prop is OK prop = props[propname] if not isinstance(prop, Link) and not isinstance(prop, Multilink): raise TypeError("'%s' not a Link/Multilink property" % propname) # first, links a = self.db.arg allvalues = () sql = [] where = [] for prop, values in propspec.items(): if not isinstance(props[prop], hyperdb.Link): continue if isinstance(values, dict) and len(values) == 1: values = list(values)[0] if isinstance(values, str): allvalues += (values,) where.append('_%s = %s' % (prop, a)) elif values is None: where.append('_%s is NULL' % prop) else: values = list(values) s = '' if None in values: values.remove(None) s = '_%s is NULL or ' % prop allvalues += tuple(values) s += '_%s in (%s)' % (prop, ','.join([a]*len(values))) where.append('(' + s + ')') if where: allvalues = (0, ) + allvalues sql.append("""select id from _%s where __retired__=%s and %s""" % (self.classname, a, ' and '.join(where))) # now multilinks for prop, values in propspec.items(): p = props[prop] if not isinstance(p, hyperdb.Multilink): continue if not values: continue allvalues += (0, ) tn = p.table_name ln = p.linkid_name nn = p.nodeid_name cn = '_' + self.classname ret = '' dis = '' ord = '' if p.rev_property: if isinstance(p.rev_property, Link): ret = 'and %s.__retired__=%s ' % (tn, a) allvalues += (0, ) dis = 'distinct ' ord = ' order by %s.id' % cn if isinstance(values, str): allvalues += (values,) s = a else: allvalues += tuple(values) s = ','.join([a]*len(values)) sql.append("""select %s%s.id from %s, %s where %s.__retired__=%s %sand %s.id = %s.%s and %s.%s in (%s)%s""" % ( dis, cn, cn, tn, cn, a, ret, cn, tn, nn, tn, ln, s, ord)) if not sql: return [] sql = ' union '.join(sql) self.db.sql(sql, allvalues) # XXX numeric ids l = [str(x[0]) for x in self.db.sql_fetchall()] return l def stringFind(self, **requirements): """Locate a particular node by matching a set of its String properties in a caseless search. If the property is not a String property, a TypeError is raised. The return is a list of the id of all nodes that match. """ where = [] args = [] for propname in requirements: prop = self.properties[propname] if not isinstance(prop, String): raise TypeError("'%s' not a String property" % propname) where.append(propname) args.append(requirements[propname].lower()) # generate the where clause s = ' and '.join(['lower(_%s)=%s' % (col, self.db.arg) for col in where]) sql = 'select id from _%s where %s and __retired__=%s' % ( self.classname, s, self.db.arg) args.append(0) self.db.sql(sql, tuple(args)) # XXX numeric ids l = [str(x[0]) for x in self.db.sql_fetchall()] return l def list(self): """ Return a list of the ids of the active nodes in this class. """ return self.getnodeids(retired=0) def getnodeids(self, retired=None): """ Retrieve all the ids of the nodes for a particular Class. Set retired=None to get all nodes. Otherwise it'll get all the retired or non-retired nodes, depending on the flag. """ # flip the sense of the 'retired' flag if we don't want all of them if retired is not None: args = (0, ) if retired: compare = '>' else: compare = '=' sql = 'select id from _%s where __retired__%s%s' % ( self.classname, compare, self.db.arg) else: args = () sql = 'select id from _%s' % self.classname self.db.sql(sql, args) # XXX numeric ids ids = [str(x[0]) for x in self.db.cursor.fetchall()] return ids def _subselect(self, proptree, parentname=None): """Create a subselect. This is factored out because some databases (hmm only one, so far) doesn't support subselects look for "I can't believe it's not a toy RDBMS" in the mysql backend. """ multilink_table = proptree.propclass.table_name nodeid_name = proptree.propclass.nodeid_name linkid_name = proptree.propclass.linkid_name if parentname is None: parentname = '_' + proptree.parent.classname w = '' if proptree.need_retired: w = ' where %s.__retired__=0' % (multilink_table) if proptree.need_child_retired: tn1 = multilink_table tn2 = '_' + proptree.classname w = ', %s where %s.%s=%s.id and %s.__retired__=0' % ( tn2, tn1, linkid_name, tn2, tn2) return '%s.id not in (select %s from %s%s)' % ( parentname, nodeid_name, multilink_table, w) def _filter_multilink_expression_fallback(self, proptree, expr): '''This is a fallback for database that do not support subselects.''' classname = proptree.parent.uniqname multilink_table = proptree.propclass.table_name nid = proptree.propclass.nodeid_name lid = proptree.propclass.linkid_name is_valid = expr.evaluate last_id, kws = None, [] ids = IdListOptimizer() append = ids.append # This join and the evaluation in program space # can be expensive for larger databases! # TODO: Find a faster way to collect the data needed # to evalute the expression. # Moving the expression evaluation into the database # would be nice but this tricky: Think about the cases # where the multilink table does not have join values # needed in evaluation. w = j = '' s = 'm.%s' % lid if proptree.need_retired: w = ' and m.__retired__=0' elif proptree.need_child_retired: tn2 = '_' + proptree.classname j = ' LEFT OUTER JOIN %s ON %s.id = m.%s' % (tn2, tn2, lid) w = ' and %s.__retired__=0' % (tn2) s = '%s.id' % tn2 stmnt = "SELECT c.id, %s FROM _%s as c " \ "LEFT OUTER JOIN %s as m " \ "ON c.id = m.%s%s%s ORDER BY c.id" % ( s, classname, multilink_table, nid, j, w) self.db.sql(stmnt) # collect all multilink items for a class item for nodeid, kw in self.db.sql_fetchiter(): if nodeid != last_id: if last_id is None: last_id = nodeid else: # we have all multilink items -> evaluate! if is_valid(kws): append(last_id) # noqa: E701 last_id, kws = nodeid, [] if kw is not None: kws.append(int(kw)) if last_id is not None and is_valid(kws): append(last_id) # we have ids of the classname table return ids.where("_%s.id" % classname, self.db.arg) def _filter_link_expression(self, proptree, v): """ Filter elements in the table that match the given expression """ pln = proptree.parent.uniqname prp = proptree.name try: opcodes = [int(x) for x in v] if min(opcodes) >= -1: raise ValueError() expr = compile_expression(opcodes) # NULL doesn't compare to NULL in SQL # So not (x = '1') will *not* include NULL values for x # That's why we need that and clause: atom = "_%s._%s = %s and _%s._%s is not NULL" % ( pln, prp, self.db.arg, pln, prp) atom_nil = "_%s._%s is NULL" % (pln, prp) lambda_atom = lambda n: atom if n.x >= 0 else atom_nil values = [] w = expr.generate(lambda_atom) def collect_values(n): if n.x >= 0: values.append(n.x) expr.visit(collect_values) return w, values except: pass # Fallback to original code args = [] where = None d = {} for entry in v: if entry == '-1': entry = None d[entry] = entry l = [] if None in d or not d: if None in d: del d[None] # noqa: E701 l.append('_%s._%s is NULL' % (pln, prp)) if d: v = list(d) s = ','.join([self.db.arg for x in v]) l.append('(_%s._%s in (%s))' % (pln, prp, s)) args = v if l: where = '(' + ' or '.join(l) + ')' return where, args def _filter_multilink_expression(self, proptree, v): """ Filters out elements of the classname table that do not match the given expression. Returns tuple of 'WHERE' introns for the overall filter. """ classname = proptree.parent.uniqname multilink_table = proptree.propclass.table_name nid = proptree.propclass.nodeid_name lid = proptree.propclass.linkid_name try: opcodes = [int(x) for x in v] if min(opcodes) >= -1: raise ValueError() expr = compile_expression(opcodes) if not self.supports_subselects: # We heavily rely on subselects. If there is # no decent support fall back to slower variant. return self._filter_multilink_expression_fallback( proptree, expr) w = j = '' if proptree.need_retired: w = ' and %s.__retired__=0' % (multilink_table) elif proptree.need_child_retired: tn1 = multilink_table tn2 = '_' + proptree.classname j = ', %s' % tn2 w = ' and %s.%s=%s.id and %s.__retired__=0' % ( tn1, lid, tn2, tn2) atom = \ "%s IN(SELECT %s FROM %s%s WHERE %s=a.id%s)" % ( self.db.arg, lid, multilink_table, j, nid, w) atom_nil = self._subselect(proptree, 'a') lambda_atom = lambda n: atom if n.x >= 0 else atom_nil intron = \ "_%(classname)s.id in (SELECT id " \ "FROM _%(classname)s AS a WHERE %(condition)s) " % { 'classname': classname, 'condition': expr.generate(lambda_atom)} values = [] def collect_values(n): if n.x >= 0: values.append(n.x) expr.visit(collect_values) return intron, values except: # fallback behavior when expression parsing above fails orclause = '' if '-1' in v: v = [x for x in v if int(x) > 0] orclause = self._subselect(proptree) where = [] where.append("%s.%s in (%s)" % (multilink_table, lid, ','.join([self.db.arg] * len(v)))) where.append('_%s.id=%s.%s' % (classname, multilink_table, nid)) where = ' and '.join(where) if orclause: where = '((' + ' or '.join((where + ')', orclause)) + ')' return where, v def _filter_sql(self, search_matches, filterspec, srt=[], grp=[], retr=0, retired=False, exact_match_spec={}, limit=None, offset=None): """ Compute the proptree and the SQL/ARGS for a filter. For argument description see filter below. We return a 3-tuple, the proptree, the sql and the sql-args or None if no SQL is necessary. The flag retr serves to retrieve *all* non-Multilink properties (for filling the cache during a filter_iter) """ # we can't match anything if search_matches is empty if not search_matches and search_matches is not None: return None icn = self.classname # vars to hold the components of the SQL statement frum = [] # FROM clauses loj = [] # LEFT OUTER JOIN clauses where = [] # WHERE clauses args = [] # *any* positional arguments a = self.db.arg # figure the WHERE clause from the filterspec use_distinct = False # Do we need a distinct clause? sortattr = self._sortattr(group=grp, sort=srt) proptree = self._proptree(filterspec, exact_match_spec, sortattr, retr) mlseen = 0 for pt in reversed(proptree.sortattr): p = pt while p.parent: if isinstance(p.propclass, Multilink): mlseen = True if mlseen: p.sort_ids_needed = True p.tree_sort_done = False p = p.parent if not mlseen: pt.attr_sort_done = pt.tree_sort_done = True proptree.compute_sort_done() cols = ['_%s.id' % icn] for p in proptree: rc = ac = oc = None cn = p.classname ln = p.uniqname pln = p.parent.uniqname pcn = p.parent.classname k = p.name v = p.val propclass = p.propclass if p.parent == proptree and p.name == 'id' \ and 'retrieve' in p.need_for: p.sql_idx = 0 if 'sort' in p.need_for or 'retrieve' in p.need_for: rc = oc = ac = '_%s._%s' % (pln, k) if isinstance(propclass, Multilink): if 'search' in p.need_for: # if we joining with Multilink tables we need distinct use_distinct = True tn = propclass.table_name nid = propclass.nodeid_name lid = propclass.linkid_name frum.append(tn) if p.children or p.need_child_retired: frum.append('_%s as _%s' % (cn, ln)) where.append('%s.%s=_%s.id' % (tn, lid, ln)) if p.need_child_retired: where.append('_%s.__retired__=0' % (ln)) # Note: need the where-clause if p has # children that compute additional restrictions if (not p.has_values or (not isinstance(v, type([])) and v != '-1') or p.children): where.append('_%s.id=%s.%s' % (pln, tn, nid)) if v in ('-1', ['-1'], []): # only match rows that have count(linkid)=0 in the # corresponding multilink table) where.append(self._subselect(p)) else: if p.has_values: if isinstance(v, type([])): # The where-clause above is conditionally # created in _filter_multilink_expression w, arg = self._filter_multilink_expression(p, v) where.append(w) args += arg else: where.append('%s.%s=%s' % (tn, lid, a)) args.append(v) # Don't match retired nodes if rev_multilink if p.need_retired: where.append('%s.__retired__=0' % (tn)) if 'sort' in p.need_for: assert not p.attr_sort_done and not p.sort_ids_needed elif k == 'id': if 'search' in p.need_for: if isinstance(v, type([])): # If there are no permitted values, then the # where clause will always be false, and we # can optimize the query away. if not v: return None s = ','.join([a for x in v]) where.append('_%s.%s in (%s)' % (pln, k, s)) args = args + v else: where.append('_%s.%s=%s' % (pln, k, a)) args.append(v) if 'sort' in p.need_for or 'retrieve' in p.need_for: rc = oc = ac = '_%s.id' % pln elif isinstance(propclass, String): if 'search' in p.need_for: exact = [] if not isinstance(v, type([])): v = [v] new_v = [] for x in v: if isinstance(x, hyperdb.Exact_Match): exact.append(True) new_v.append(x.value) else: exact.append(False) # Quote special search characters '%' and '_' for # correct matching with LIKE/ILIKE # Note that we now pass the elements of v as query # arguments and don't interpolate the quoted string # into the sql statement. Should be safer. new_v.append(self.db.search_stringquote(x)) v = new_v # now add to the where clause w = [] for vv, ex in zip(v, exact): if ex: w.append("_%s._%s %s %s" % ( pln, k, self.case_sensitive_equal, a)) args.append(vv) else: w.append("_%s._%s %s %s ESCAPE %s" % ( pln, k, self.case_insensitive_like, a, a)) args.extend((vv, '\\')) where.append('(' + ' and '.join(w) + ')') if 'sort' in p.need_for: oc = ac = 'lower(_%s._%s)' % (pln, k) elif isinstance(propclass, Link): if 'search' in p.need_for: if p.children: if 'sort' not in p.need_for: frum.append('_%s as _%s' % (cn, ln)) c = [x for x in p.children if 'search' in x.need_for] if c: where.append('_%s._%s=_%s.id' % (pln, k, ln)) if p.has_values: if isinstance(v, type([])): w, arg = self._filter_link_expression(p, v) if w: where.append(w) args += arg else: if v in ('-1', None): v = None where.append('_%s._%s is NULL' % (pln, k)) else: where.append('_%s._%s=%s' % (pln, k, a)) args.append(v) if 'sort' in p.need_for: lp = p.cls.labelprop() oc = ac = '_%s._%s' % (pln, k) if lp != 'id': if p.tree_sort_done: loj.append( 'LEFT OUTER JOIN _%s as _%s on _%s._%s=_%s.id' % ( cn, ln, pln, k, ln)) oc = '_%s._%s' % (ln, lp) if 'retrieve' in p.need_for: rc = '_%s._%s' % (pln, k) elif isinstance(propclass, Date) and 'search' in p.need_for: dc = self.db.to_sql_value(hyperdb.Date) if isinstance(v, type([])): s = ','.join([a for x in v]) where.append('_%s._%s in (%s)' % (pln, k, s)) args = args + [dc(date.Date(x)) for x in v] else: try: wh = [] ar = [] for d in v.split(','): w1 = [] if d == '-': wh.append('_%s._%s is NULL' % (pln, k)) continue # Try to filter on range of dates date_rng = propclass.range_from_raw(d, self.db) if date_rng.from_value: w1.append('_%s._%s >= %s' % (pln, k, a)) ar.append(dc(date_rng.from_value)) if date_rng.to_value: w1.append('_%s._%s <= %s' % (pln, k, a)) ar.append(dc(date_rng.to_value)) wh.append(' and '.join(w1)) where.append('(' + ' or '.join(wh) + ')') args.extend(ar) except ValueError: # If range creation fails - ignore that search parameter pass elif isinstance(propclass, Interval): # filter/sort using the ___int__ column if 'search' in p.need_for: if isinstance(v, type([])): s = ','.join([a for x in v]) where.append('_%s.__%s_int__ in (%s)' % (pln, k, s)) args = args + [date.Interval(x).as_seconds() for x in v] else: try: # Try to filter on range of intervals date_rng = Range(v, date.Interval) if date_rng.from_value: where.append('_%s.__%s_int__ >= %s' % ( pln, k, a)) args.append(date_rng.from_value.as_seconds()) if date_rng.to_value: where.append('_%s.__%s_int__ <= %s' % ( pln, k, a)) args.append(date_rng.to_value.as_seconds()) except ValueError: # If range creation fails - ignore search parameter pass if 'sort' in p.need_for: oc = ac = '_%s.__%s_int__' % (pln, k) if 'retrieve' in p.need_for: rc = '_%s._%s' % (pln, k) elif isinstance(propclass, Boolean) and 'search' in p.need_for: if isinstance(v, str): v = v.split(',') if not isinstance(v, list): v = [v] bv = [] for val in v: if isinstance(val, str): bv.append(propclass.from_raw(val)) else: bv.append(bool(val)) if len(bv) == 1: where.append('_%s._%s=%s' % (pln, k, a)) args = args + bv else: s = ','.join([a for x in v]) where.append('_%s._%s in (%s)' % (pln, k, s)) args = args + bv elif 'search' in p.need_for: if isinstance(v, type([])): s = ','.join([a for x in v]) where.append('_%s._%s in (%s)' % (pln, k, s)) args = args + v else: where.append('_%s._%s=%s' % (pln, k, a)) args.append(v) if oc: if p.sort_ids_needed: if rc == ac: p.sql_idx = len(cols) p.auxcol = len(cols) cols.append(ac) if p.tree_sort_done and p.sort_direction: # Don't select top-level id or multilink twice if (not p.sort_ids_needed or ac != oc) and ( p.name != 'id' or p.parent != proptree): if rc == oc: p.sql_idx = len(cols) cols.append(oc) desc = ['', ' desc'][p.sort_direction == '-'] # Some SQL dbs sort NULL values last -- we want them first. if (self.order_by_null_values and p.name != 'id'): nv = self.order_by_null_values % oc cols.append(nv) p.orderby.append(nv + desc) p.orderby.append(oc + desc) if 'retrieve' in p.need_for and p.sql_idx is None: assert(rc) p.sql_idx = len(cols) cols.append(rc) props = self.getprops() # don't match retired nodes if retired is not None: op = '=' if retired: op = '!=' where.append('_%s.__retired__%s0' % (icn, op)) # add results of full text search if search_matches is not None: s = ','.join([a for x in search_matches]) where.append('_%s.id in (%s)' % (icn, s)) args = args + [x for x in search_matches] # construct the SQL frum.append('_'+icn) frum = ','.join(frum) if where: where = ' where ' + (' and '.join(where)) else: where = '' if use_distinct: # Avoid dupes cols[0] = 'distinct(_%s.id)' % icn order = [] # keep correct sequence of order attributes. for sa in proptree.sortattr: if not sa.attr_sort_done: continue order.extend(sa.orderby) if order: order = ' order by %s' % (','.join(order)) else: order = '' if limit is not None: limit = ' LIMIT %s' % limit else: limit = '' if offset is not None: offset = ' OFFSET %s' % offset else: offset = '' cols = ','.join(cols) loj = ' '.join(loj) sql = 'select %s from %s %s %s%s%s%s' % ( cols, frum, loj, where, order, limit, offset) args = tuple(args) __traceback_info__ = (sql, args) return proptree, sql, args def filter(self, search_matches, filterspec, sort=[], group=[], retired=False, exact_match_spec={}, limit=None, offset=None): """Return a list of the ids of the active nodes in this class that match the 'filter' spec, sorted by the group spec and then the sort spec "filterspec" is {propname: value(s)} "sort" and "group" are [(dir, prop), ...] where dir is '+', '-' or None and prop is a prop name or None. Note that for backward-compatibility reasons a single (dir, prop) tuple is also allowed. "search_matches" is a container type or None The filter must match all properties specificed. If the property value to match is a list: 1. String properties must match all elements in the list, and 2. Other properties must match any of the elements in the list. """ if __debug__: start_t = time.time() sq = self._filter_sql(search_matches, filterspec, sort, group, retired=retired, exact_match_spec=exact_match_spec, limit=limit, offset=offset) # nothing to match? if sq is None: return [] proptree, sql, args = sq cursor = self.db.sql_new_cursor(name='filter') self.db.sql(sql, args, cursor) # Reduce this to only the first row (the ID), this can save a # lot of space for large query results (not using fetchall) # We cannot do this if sorting by multilink if proptree.tree_sort_done: l = [str(row[0]) for row in cursor] else: l = cursor.fetchall() cursor.close() # Multilink sorting # Compute values needed for sorting in proptree.sort if not proptree.tree_sort_done: for p in proptree: if hasattr(p, 'auxcol'): p.sort_ids = [row[p.auxcol] for row in l] p.sort_result = p._sort_repr( p.propclass.sort_repr, p.sort_ids) l = proptree.sort([str(row[0]) for row in l]) if __debug__: self.db.stats['filtering'] += (time.time() - start_t) return l def filter_iter(self, search_matches, filterspec, sort=[], group=[], retired=False, exact_match_spec={}, limit=None, offset=None): """Iterator similar to filter above with same args. Limitation: We don't sort on multilinks. This uses an optimisation: We put all nodes that are in the current row into the node cache. Then we return the node id. That way a fetch of a node won't create another sql-fetch (with a join) from the database because the nodes are already in the cache. We're using our own temporary cursor. """ sq = self._filter_sql(search_matches, filterspec, sort, group, retr=1, retired=retired, exact_match_spec=exact_match_spec, limit=limit, offset=offset) # nothing to match? if sq is None: return proptree, sql, args = sq cursor = self.db.sql_new_cursor(name='filter_iter') self.db.sql(sql, args, cursor) classes = {} for p in proptree: if 'retrieve' in p.need_for: cn = p.parent.classname ptid = p.parent.id # not the nodeid! key = (cn, ptid) if key not in classes: classes[key] = {} name = p.name assert (name) classes[key][name] = p p.to_hyperdb = self.db.to_hyperdb_value(p.propclass.__class__) while True: row = cursor.fetchone() if not row: break # noqa: E701 # populate cache with current items for (classname, _ptid), pt in classes.items(): nodeid = str(row[pt['id'].sql_idx]) key = (classname, nodeid) if key in self.db.cache: self.db._cache_refresh(key) continue node = {} for propname, p in pt.items(): value = row[p.sql_idx] if value is not None: value = p.to_hyperdb(value) node[propname] = value self.db._cache_save(key, node) yield str(row[0]) cursor.close() def filter_sql(self, sql): """Return a list of the ids of the items in this class that match the SQL provided. The SQL is a complete "select" statement. The SQL select must include the item id as the first column. This function DOES NOT filter out retired items, add on a where clause "__retired__=0" if you don't want retired nodes. """ if __debug__: start_t = time.time() self.db.sql(sql) results = self.db.sql_fetchall() if __debug__: self.db.stats['filtering'] += (time.time() - start_t) return results def count(self): """Get the number of nodes in this class. If the returned integer is 'numnodes', the ids of all the nodes in this class run from 1 to numnodes, and numnodes+1 will be the id of the next node to be created in this class. """ return self.db.countnodes(self.classname) # Manipulating properties: def getprops(self, protected=1): """Return a dictionary mapping property names to property objects. If the "protected" flag is true, we include protected properties - those which may not be modified. """ d = self.properties.copy() if protected: d['id'] = String() d['creation'] = hyperdb.Date() d['activity'] = hyperdb.Date() d['creator'] = hyperdb.Link('user') d['actor'] = hyperdb.Link('user') return d def addprop(self, **properties): """Add properties to this class. The keyword arguments in 'properties' must map names to property objects, or a TypeError is raised. None of the keys in 'properties' may collide with the names of existing properties, or a ValueError is raised before any properties have been added. """ for key in properties: if key in self.properties: raise ValueError(key) self.properties.update(properties) def index(self, nodeid): """Add (or refresh) the node to search indexes """ # find all the String properties that have indexme for prop, propclass in self.getprops().items(): if isinstance(propclass, String) and propclass.indexme: self.db.indexer.add_text((self.classname, nodeid, prop), str(self.get(nodeid, prop))) # # import / export support # def export_list(self, propnames, nodeid): """ Export a node - generate a list of CSV-able data in the order specified by propnames for the given node. """ properties = self.getprops() return_list = [] for prop in propnames: proptype = properties[prop] value = self.get(nodeid, prop) # "marshal" data where needed if value is None: pass elif isinstance(proptype, hyperdb.Date): value = value.get_tuple() elif isinstance(proptype, hyperdb.Interval): value = value.get_tuple() elif isinstance(proptype, hyperdb.Password): value = str(value) return_list.append(repr_export(value)) return_list.append(repr_export(self.is_retired(nodeid))) return return_list def import_list(self, propnames, proplist): """ Import a node - all information including "id" is present and should not be sanity checked. Triggers are not triggered. The journal should be initialised using the "creator" and "created" information. Return the nodeid of the node imported. """ logger = logging.getLogger('roundup.hyperdb.backend') if self.db.journaltag is None: raise DatabaseError(_('Database open read-only')) properties = self.getprops() # make the new node's property map d = {} retire = 0 if "id" not in propnames: newid = self.db.newid(self.classname) else: newid = eval_import(proplist[propnames.index("id")]) for i in range(len(propnames)): # Use eval_import to reverse the repr_export() used to # output the CSV value = eval_import(proplist[i]) # Figure the property for this column propname = propnames[i] # "unmarshal" where necessary if propname == 'id': continue elif propname == 'is retired': # is the item retired? if int(value): retire = 1 continue elif value is None: d[propname] = None continue prop = properties[propname] if value is None: # don't set Nones continue elif isinstance(prop, hyperdb.Date): value = date.Date(value) elif isinstance(prop, hyperdb.Interval): value = date.Interval(value) elif isinstance(prop, hyperdb.Password): value = password.Password(encrypted=value, config=self.db.config) elif isinstance(prop, String): value = us2s(value) if not isinstance(value, str): raise TypeError('new property "%(propname)s" not a ' 'string: %(value)r' % locals()) if prop.indexme: self.db.indexer.add_text((self.classname, newid, propname), value) d[propname] = value # get a new id if necessary if newid is None: newid = self.db.newid(self.classname) activeid = None has_node = False # use the arg for __retired__ to cope with any odd database type # conversion (hello, sqlite) retired_sql = 'update _%s set __retired__=%s where id=%s' % ( self.classname, self.db.arg, self.db.arg) # insert new node or update existing? # if integrity error raised try to recover try: has_node = self.hasnode(newid) if not has_node: self.db.addnode(self.classname, newid, d) # insert else: self.db.setnode(self.classname, newid, d) # update self.db.checkpoint_data() # Blech, different db's return different exceptions # so I can't list them here as some might not be defined # on a given system. So capture all exceptions from the # code above and try to correct it. If it's correctable its # some form of Uniqueness Failure/Integrity Error otherwise # undo the fixup and pass on the error. except Exception as e: # nosec logger.info('Attempting to handle import exception ' 'for id %s: %s' % (newid, e)) classdb = self.db.getclass(self.classname) keyname = classdb.getkey() if has_node or not keyname: # Not an integrity error raise self.db.restore_connection_on_error() activeid = classdb.lookup(d[keyname]) self.db.sql(retired_sql, (-1, activeid)) # clear the active node # this can only happen on an addnode, so retry try: # if this raises an error, let it propagate upward self.db.addnode(self.classname, newid, d) # insert except Exception: # undo the database change self.db.sql(retired_sql, (0, activeid)) # clear active node raise # propagate logger.info('Successfully handled import exception ' 'for id %s which conflicted with %s' % ( newid, activeid)) # retire? if retire: self.db.sql(retired_sql, (newid, newid)) if activeid: # unretire the active node self.db.sql(retired_sql, ('0', activeid)) return newid def export_journals(self): """Export a class's journal - generate a list of lists of CSV-able data: nodeid, date, user, action, params No heading here - the columns are fixed. """ properties = self.getprops() r = [] for nodeid in self.getnodeids(): for nodeid, date_, user, action, params in self.history( nodeid, enforceperm=False, skipquiet=False): date_ = date_.get_tuple() if action == 'set': export_data = {} for propname, value in params.items(): if propname not in properties: # property no longer in the schema continue prop = properties[propname] # make sure the params are eval()'able if value is None: pass elif isinstance(prop, Date): value = value.get_tuple() elif isinstance(prop, Interval): value = value.get_tuple() elif isinstance(prop, Password): value = str(value) export_data[propname] = value params = export_data elif action == 'create' and params: # old tracker with data stored in the create! params = {} l = [nodeid, date_, user, action, params] r.append(list(map(repr_export, l))) return r class FileClass(hyperdb.FileClass, Class): """This class defines a large chunk of data. To support this, it has a mandatory String property "content" which is typically saved off externally to the hyperdb. The default MIME type of this data is defined by the "default_mime_type" class attribute, which may be overridden by each node if the class defines a "type" String property. """ def __init__(self, db, classname, **properties): """The newly-created class automatically includes the "content" and "type" properties. """ if 'content' not in properties: properties['content'] = hyperdb.String(indexme='yes') if 'type' not in properties: properties['type'] = hyperdb.String() Class.__init__(self, db, classname, **properties) def create(self, **propvalues): """ snaffle the file propvalue and store in a file """ # we need to fire the auditors now, or the content property won't # be in propvalues for the auditors to play with self.fireAuditors('create', None, propvalues) # now remove the content property so it's not stored in the db content = propvalues['content'] del propvalues['content'] # do the database create newid = self.create_inner(**propvalues) # figure the mime type mime_type = propvalues.get('type', self.default_mime_type) # and index! if self.properties['content'].indexme: index_content = content if bytes != str and isinstance(content, bytes): index_content = content.decode('utf-8', errors='ignore') self.db.indexer.add_text((self.classname, newid, 'content'), index_content, mime_type) # store off the content as a file self.db.storefile(self.classname, newid, None, bs2b(content)) # fire reactors self.fireReactors('create', newid, None) return newid def get(self, nodeid, propname, default=_marker, cache=1): """ Trap the content propname and get it from the file 'cache' exists for backwards compatibility, and is not used. """ poss_msg = 'Possibly a access right configuration problem.' if propname == 'content': try: return b2s(self.db.getfile(self.classname, nodeid, None)) except IOError as strerror: # BUG: by catching this we donot see an error in the log. return 'ERROR reading file: %s%s\n%s\n%s' % ( self.classname, nodeid, poss_msg, strerror) except UnicodeDecodeError: # if content is not text (e.g. jpeg file) we get # unicode error trying to convert to string in python 3. # trap it and supply an error message. Include md5sum # of content as this string is included in the etag # calculation of the object. return ('%s%s is not text, retrieve using ' 'binary_content property. mdsum: %s') % ( self.classname, nodeid, md5(self.db.getfile( self.classname, nodeid, None)).hexdigest()) # nosec - bandit md5 use ok elif propname == 'binary_content': return self.db.getfile(self.classname, nodeid, None) if default is not _marker: return Class.get(self, nodeid, propname, default) else: return Class.get(self, nodeid, propname) def set(self, itemid, **propvalues): """ Snarf the "content" propvalue and update it in a file """ self.fireAuditors('set', itemid, propvalues) oldvalues = copy.deepcopy(self.db.getnode(self.classname, itemid)) # now remove the content property so it's not stored in the db content = None if 'content' in propvalues: content = propvalues['content'] del propvalues['content'] # do the database create propvalues = self.set_inner(itemid, **propvalues) # do content? if content: # store and possibly index self.db.storefile(self.classname, itemid, None, bs2b(content)) if self.properties['content'].indexme: mime_type = self.get(itemid, 'type', self.default_mime_type) index_content = content if bytes != str and isinstance(content, bytes): index_content = content.decode('utf-8', errors='ignore') self.db.indexer.add_text((self.classname, itemid, 'content'), index_content, mime_type) propvalues['content'] = content # fire reactors self.fireReactors('set', itemid, oldvalues) return propvalues def index(self, nodeid): """ Add (or refresh) the node to search indexes. Use the content-type property for the content property. """ # find all the String properties that have indexme for prop, propclass in self.getprops().items(): if prop == 'content' and propclass.indexme: mime_type = self.get(nodeid, 'type', self.default_mime_type) index_content = self.get(nodeid, 'binary_content') if bytes != str and isinstance(index_content, bytes): index_content = index_content.decode('utf-8', errors='ignore') self.db.indexer.add_text((self.classname, nodeid, 'content'), index_content, mime_type) elif isinstance(propclass, hyperdb.String) and propclass.indexme: # index them under (classname, nodeid, property) try: value = str(self.get(nodeid, prop)) except IndexError: # node has been destroyed continue self.db.indexer.add_text((self.classname, nodeid, prop), value) # XXX deviation from spec - was called ItemClass class IssueClass(Class, roundupdb.IssueClass): # Overridden methods: def __init__(self, db, classname, **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", "creator", "activity" or "actor" property, a ValueError is raised. """ if 'title' not in properties: properties['title'] = hyperdb.String(indexme='yes') if 'messages' not in properties: properties['messages'] = hyperdb.Multilink("msg") if 'files' not in properties: properties['files'] = hyperdb.Multilink("file") if 'nosy' not in properties: # note: journalling is turned off as it really just wastes # space. this behaviour may be overridden in an instance properties['nosy'] = hyperdb.Multilink("user", do_journal="no") if 'superseder' not in properties: properties['superseder'] = hyperdb.Multilink(classname) Class.__init__(self, db, classname, **properties) # vim: set et sts=4 sw=4 : code-2a6c3eb4e059/roundup/backends/sessions_common.py0000644000000000000000000000303514571501054020771 0ustar 00000000000000import base64, logging import roundup.anypy.random_ as random_ from roundup.anypy.strings import b2s logger = logging.getLogger('roundup.hyperdb.backend.sessions') if not random_.is_weak: logger.debug("Importing good random generator") else: logger.warning("**SystemRandom not available. Using poor random generator") class SessionCommon: def log_debug(self, msg, *args, **kwargs): """Log a message with level DEBUG.""" logger = self.get_logger() logger.debug(msg, *args, **kwargs) def log_info(self, msg, *args, **kwargs): """Log a message with level INFO.""" logger = self.get_logger() logger.info(msg, *args, **kwargs) def log_warning(self, msg, *args, **kwargs): """Log a message with level INFO.""" logger = self.get_logger() logger.warning(msg, *args, **kwargs) def get_logger(self): """Return the logger for this database.""" # Because getting a logger requires acquiring a lock, we want # to do it only once. if not hasattr(self, '__logger'): self.__logger = logging.getLogger('roundup.hyperdb.backends.%s' % self.name or "basicdb" ) return self.__logger def getUniqueKey(self, length=40): otk = b2s(base64.urlsafe_b64encode( random_.token_bytes(length))).rstrip('=') while self.exists(otk): otk = b2s(base64.urlsafe_b64encode( random_.token_bytes(length))).rstrip('=') return otk code-2a6c3eb4e059/roundup/backends/sessions_dbm.py0000644000000000000000000001502014571501054020240 0ustar 00000000000000"""This module defines a very basic store that's used by the CGI interface to store session and one-time-key information. Yes, it's called "sessions" - because originally it only defined a session class. It's now also used for One Time Key handling too. """ __docformat__ = 'restructuredtext' import marshal, os, random, time from roundup.anypy.html import html_escape as escape from roundup import hyperdb from roundup.i18n import _ from roundup.anypy.dbm_ import anydbm, whichdb from roundup.backends.sessions_common import SessionCommon class BasicDatabase(SessionCommon): ''' Provide a nice encapsulation of an anydbm store. Keys are id strings, values are automatically marshalled data. ''' _db_type = None name = None def __init__(self, db): self.config = db.config self.dir = db.config.DATABASE os.umask(db.config.UMASK) def exists(self, infoid): db = self.opendb('c') try: return infoid in db finally: db.close() def clear(self): path = os.path.join(self.dir, self.name) if os.path.exists(path): os.remove(path) elif os.path.exists(path+'.db'): # dbm appends .db os.remove(path+'.db') def cache_db_type(self, path): ''' determine which DB wrote the class file, and cache it as an attribute of __class__ (to allow for subclassed DBs to be different sorts) ''' db_type = '' if os.path.exists(path): db_type = whichdb(path) if not db_type: raise hyperdb.DatabaseError( _("Couldn't identify database type")) elif os.path.exists(path+'.db'): # if the path ends in '.db', it's a dbm database, whether # anydbm says it's dbhash or not! db_type = 'dbm' self.__class__._db_type = db_type _marker = [] def get(self, infoid, value, default=_marker): db = self.opendb('c') try: if infoid in db: values = marshal.loads(db[infoid]) else: if default != self._marker: return default raise KeyError('No such %s "%s"' % (self.name, escape(infoid))) return values.get(value, None) finally: db.close() def getall(self, infoid): db = self.opendb('c') try: try: d = marshal.loads(db[infoid]) del d['__timestamp'] return d except KeyError: raise KeyError('No such %s "%s"' % (self.name, escape(infoid))) finally: db.close() def set(self, infoid, **newvalues): db = self.opendb('c') timestamp = None try: if infoid in db: values = marshal.loads(db[infoid]) try: timestamp = values['__timestamp'] except KeyError: pass # stay at None else: values = {} if '__timestamp' in newvalues: try: float(newvalues['__timestamp']) except ValueError: # keep original timestamp if present newvalues['__timestamp'] = timestamp or time.time() else: newvalues['__timestamp'] = time.time() values.update(newvalues) db[infoid] = marshal.dumps(values) finally: db.close() def list(self): db = self.opendb('r') try: return list(db.keys()) finally: db.close() def destroy(self, infoid): db = self.opendb('c') try: if infoid in db: del db[infoid] finally: db.close() def opendb(self, mode): '''Low-level database opener that gets around anydbm/dbm eccentricities. ''' # figure the class db type path = os.path.join(os.getcwd(), self.dir, self.name) if self._db_type is None: self.cache_db_type(path) db_type = self._db_type # new database? let anydbm pick the best dbm if not db_type: return anydbm.open(path, 'c') # open the database with the correct module dbm = __import__(db_type) retries_left = 15 while True: try: handle = dbm.open(path, mode) break except OSError as e: # Primarily we want to catch and retry: # [Errno 11] Resource temporarily unavailable retry # FIXME: make this more specific if retries_left < 10: self.log_warning( 'dbm.open failed on ...%s, retry %s left: %s, %s' % (path[-15:], 15-retries_left, retries_left, e)) if retries_left < 0: # We have used up the retries. Reraise the exception # that got us here. raise else: # stagger retry to try to get around thundering herd issue. time.sleep(random.randint(0, 25)*.005) retries_left = retries_left - 1 continue # the while loop return handle def commit(self): pass def lifetime(self, key_lifetime=0): """Return the proper timestamp for a key with key_lifetime specified in seconds. Default lifetime is 0. """ now = time.time() week = 60*60*24*7 return now - week + key_lifetime def close(self): pass def updateTimestamp(self, sessid): ''' don't update every hit - once a minute should be OK ''' sess = self.get(sessid, '__timestamp', None) now = time.time() if sess is None or now > sess + 60: self.set(sessid, __timestamp=now) def clean(self): ''' Remove session records that haven't been used for a week. ''' now = time.time() week = 60*60*24*7 for sessid in self.list(): sess = self.get(sessid, '__timestamp', None) if sess is None: self.updateTimestamp(sessid) continue interval = now - sess if interval > week: self.destroy(sessid) class Sessions(BasicDatabase): name = 'sessions' class OneTimeKeys(BasicDatabase): name = 'otks' # vim: set sts ts=4 sw=4 et si : code-2a6c3eb4e059/roundup/backends/sessions_rdbms.py0000644000000000000000000001210214571501054020603 0ustar 00000000000000"""This module defines a very basic store that's used by the CGI interface to store session and one-time-key information. Yes, it's called "sessions" - because originally it only defined a session class. It's now also used for One Time Key handling too. """ __docformat__ = 'restructuredtext' import time from roundup.anypy.html import html_escape as escape from roundup.backends.sessions_common import SessionCommon class BasicDatabase(SessionCommon): ''' Provide a nice encapsulation of an RDBMS table. Keys are id strings, values are automatically marshalled data. ''' name = None def __init__(self, db): self.db = db self.conn, self.cursor = self.db.sql_open_connection() def clear(self): self.cursor.execute('delete from %ss' % self.name) def exists(self, infoid): n = self.name self.cursor.execute('select count(*) from %ss where %s_key=%s' % (n, n, self.db.arg), (infoid,)) return int(self.cursor.fetchone()[0]) _marker = [] def get(self, infoid, value, default=_marker): n = self.name self.cursor.execute('select %s_value from %ss where %s_key=%s' % (n, n, n, self.db.arg), (infoid,)) res = self.cursor.fetchone() if not res: if default != self._marker: return default raise KeyError('No such %s "%s"' % (self.name, escape(infoid))) values = eval(res[0]) return values.get(value, None) def getall(self, infoid): n = self.name self.cursor.execute('select %s_value from %ss where %s_key=%s' % (n, n, n, self.db.arg), (infoid,)) res = self.cursor.fetchone() if not res: raise KeyError('No such %s "%s"' % (self.name, escape(infoid))) return eval(res[0]) def set(self, infoid, **newvalues): """ Store all newvalues under key infoid with a timestamp in database. If newvalues['__timestamp'] exists and is representable as a floating point number (i.e. could be generated by time.time()), that value is used for the _time column in the database. """ c = self.cursor n = self.name a = self.db.arg c.execute('select %s_value from %ss where %s_key=%s' % (n, n, n, a), (infoid,)) res = c.fetchone() timestamp = time.time() if res: values = eval(res[0]) else: values = {} if '__timestamp' in newvalues: try: # __timestamp must be representable as a float. Check it. timestamp = float(newvalues['__timestamp']) except ValueError: if res: # keep the original timestamp del(newvalues['__timestamp']) else: # here timestamp is the new timestamp newvalues['__timestamp'] = timestamp values.update(newvalues) if res: sql = ('update %ss set %s_value=%s, %s_time=%s ' 'where %s_key=%s' % (n, n, a, n, a, n, a)) args = (repr(values), timestamp, infoid) else: sql = 'insert into %ss (%s_key, %s_time, %s_value) '\ 'values (%s, %s, %s)' % (n, n, n, n, a, a, a) args = (infoid, timestamp, repr(values)) c.execute(sql, args) def list(self): c = self.cursor n = self.name c.execute('select %s_key from %ss' % (n, n)) return [res[0] for res in c.fetchall()] def destroy(self, infoid): self.cursor.execute('delete from %ss where %s_key=%s' % (self.name, self.name, self.db.arg), (infoid,)) def updateTimestamp(self, infoid): """ don't update every hit - once a minute should be OK """ now = time.time() self.cursor.execute('''update %ss set %s_time=%s where %s_key=%s ''' '''and %s_time < %s''' % (self.name, self.name, self.db.arg, self.name, self.db.arg, self.name, self.db.arg), (now, infoid, now-60)) def clean(self): ''' Remove session records that haven't been used for a week. ''' now = time.time() week = 60*60*24*7 old = now - week self.cursor.execute('delete from %ss where %s_time < %s' % (self.name, self.name, self.db.arg), (old, )) def commit(self): self.log_info('commit %s' % self.name) self.conn.commit() self.cursor = self.conn.cursor() def lifetime(self, item_lifetime=0): """Return the proper timestamp for a key with key_lifetime specified in seconds. Default lifetime is 0. """ now = time.time() week = 60*60*24*7 return now - week + item_lifetime def close(self): self.conn.close() class Sessions(BasicDatabase): name = 'session' class OneTimeKeys(BasicDatabase): name = 'otk' # vim: set et sts=4 sw=4 : code-2a6c3eb4e059/roundup/backends/sessions_redis.py0000644000000000000000000002213714571501054020613 0ustar 00000000000000"""This module defines a redis based store that's used by the CGI interface to store session and one-time-key information. Yes, it's called "sessions" - because originally it only defined a session class. It's now also used for One Time Key handling too. It uses simple strings rather than redis hash structure because the hash the values are always strings. We need to be able to represent the same data types available to rdbms and dbm session stores. session_dbm uses marshal.dumps and marshal.loads. This seems 4 or 18 times faster than the repr()/eval() used by session_rdbms. So use marshal even though it is impossible to read when viewing (using redis-cli). """ __docformat__ = 'restructuredtext' import marshal import redis import time from roundup.anypy.html import html_escape as escape from roundup.i18n import _ from roundup.backends.sessions_common import SessionCommon class BasicDatabase(SessionCommon): ''' Provide a nice encapsulation of a redis store. Keys are id strings, values are automatically marshalled data. ''' name = None default_lifetime = 60*60*24*7 # 1 week # FIXME: figure out how to allow admin to change this # to repr/eval using interfaces.py or other method. # marshalled data is not readable when debugging. tostr = marshal.dumps todict = marshal.loads def __init__(self, db): self.config = db.config url = self.config.SESSIONDB_REDIS_URL # Example at default port without auth. # redis://localhost:6379/0?health_check_interval=2 # # Do not allow decode_responses=True in url, data is # marshal'ed binary data that will get broken by decoding. # Enforce this in configuration. self.redis = redis.Redis.from_url(url=url, decode_responses=False) def makekey(self, key): '''method to namespace all keys using self.name:....''' return "%s:%s" % (self.name, key) def exists(self, infoid): return self.redis.exists(self.makekey(infoid)) def clear(self): '''Delete all keys from the database.''' self.redis.flushdb() _marker = [] def get(self, infoid, value, default=_marker): '''get a specific value from the data associated with a key''' infoid = self.makekey(infoid) v = self.redis.get(infoid) if not v: if default != self._marker: return default raise KeyError(_('Key %(key)s not found in %(name)s ' 'database.') % {"name": self.name, "key": escape(infoid)}) return self.todict(v)[value] def getall(self, infoid): '''return all values associated with a key''' try: d = self.redis.get(self.makekey(infoid)) if d is not None: d = self.todict(d) else: d = {} del d['__timestamp'] return d except KeyError: # It is possible for d to be malformed missing __timestamp. # If so, we get a misleading error, but anydbm does the # same so.... raise KeyError(_('Key %(key)s not found in %(name)s ' 'database.') % {"name": self.name, "key": escape(infoid)}) ''' def set_no_tranaction(self, infoid, **newvalues): """ this is missing transaction and may be affected by a race condition on update. This will work for redis-like embedded databases that don't support watch/multi/exec """ infoid = self.makekey(infoid) timestamp=None values = self.redis.get(infoid) if values is not None: values = self.todict(values) else: values={} try: timestamp = float(values['__timestamp']) except KeyError: pass # stay at None if '__timestamp' in newvalues: try: float(newvalues['__timestamp']) except ValueError: # keep original timestamp if present newvalues['__timestamp'] = timestamp or \ (time.time() + self.default_lifetime) else: newvalues['__timestamp'] = time.time() + self.default_lifetime values.update(newvalues) self.redis.set(infoid, self.tostr(values)) self.redis.expireat(infoid, int(values['__timestamp'])) ''' def set(self, infoid, **newvalues): """ Implement set using watch/multi/exec to get some protection against a change committing between getting the data and setting new fields and saving. """ infoid = self.makekey(infoid) timestamp = None with self.redis.pipeline() as transaction: # Give up and log after three tries. # Do not loop forever. for _retry in [1, 2, 3]: # I am ignoring transaction return values. # Assuming all errors will be via exceptions. # Not clear that return values that useful. transaction.watch(infoid) values = transaction.get(infoid) if values is not None: values = self.todict(values) else: values = {} try: timestamp = float(values['__timestamp']) except KeyError: pass # stay at None if '__timestamp' in newvalues: try: float(newvalues['__timestamp']) except ValueError: # keep original timestamp if present newvalues['__timestamp'] = timestamp or \ (time.time() + self.default_lifetime) else: newvalues['__timestamp'] = time.time() + \ self.default_lifetime values.update(newvalues) transaction.multi() transaction.set(infoid, self.tostr(values)) transaction.expireat(infoid, int(values['__timestamp'])) try: # assume this works or raises an WatchError # exception indicating I need to retry. # Since this is not a real transaction, an error # in one step doesn't roll back other changes. # so I again ignore the return codes as it is not # clear that I can do the rollback myself. # Format and other errors (e.g. expireat('d', 'd')) # raise exceptions that bubble up and result in mail # to admin. transaction.execute() break except redis.exceptions.WatchError: self.log_info( _('Key %(key)s changed in %(name)s db') % {"key": escape(infoid), "name": self.name} ) else: try: username = values['user'] except KeyError: username = "Not Set" raise Exception( _("Redis set failed after %(retries)d retries for " "user %(user)s with key %(key)s") % { "key": escape(infoid), "retries": _retry, "user": username}) def list(self): return list(self.redis.keys(self.makekey('*'))) def destroy(self, infoid=None): '''use unlink rather than delete as unlink is async and doesn't wait for memory to be freed server-side ''' self.redis.unlink(self.makekey(infoid)) def commit(self): ''' no-op ''' pass def lifetime(self, key_lifetime=None): """Return the proper timestamp to expire a key with key_lifetime specified in seconds. Default lifetime is self.default_lifetime. """ return time.time() + (key_lifetime or self.default_lifetime) def updateTimestamp(self, sessid): ''' Even Redis can be overwhelmed by multiple updates, so only update if old session key is > 60 seconds old. ''' sess = self.get(sessid, '__timestamp', None) now = time.time() # unlike other session backends, __timestamp is not set to now # but now + lifetime. if sess is None or now > sess + 60 - self.default_lifetime: lifetime = self.lifetime() # note set also updates the expireat on the key in redis self.set(sessid, __timestamp=lifetime) def clean(self): ''' redis handles key expiration, so nothing to do here. ''' pass def close(self): ''' redis uses a connection pool that self manages, so nothing to do on close.''' pass class Sessions(BasicDatabase): name = 'sessions' class OneTimeKeys(BasicDatabase): name = 'otks' # vim: set sts ts=4 sw=4 et si : code-2a6c3eb4e059/roundup/backends/sessions_sqlite.py0000644000000000000000000000422114571501054021000 0ustar 00000000000000"""This module defines a very basic store that's used by the CGI interface to store session and one-time-key information. Yes, it's called "sessions" - because originally it only defined a session class. It's now also used for One Time Key handling too. We needed to split commits to session/OTK database from commits on the main db structures (user data). This required two connections to the sqlite db, which wasn't supported. This module was created so sqlite didn't have to use dbm for the session/otk data. It hopefully will provide a performance speedup. """ __docformat__ = 'restructuredtext' from roundup.backends import sessions_rdbms class BasicDatabase(sessions_rdbms.BasicDatabase): ''' Provide a nice encapsulation of an RDBMS table. Keys are id strings, values are automatically marshalled data. ''' name = None def __init__(self, db): self.db = db self.conn, self.cursor = self.db.sql_open_connection(dbname=self.name) self.sql('''SELECT name FROM sqlite_master WHERE type='table' AND ''' '''name='%ss';''' % self.name) table_exists = self.cursor.fetchone() if not table_exists: # create table/rows etc. self.sql('''CREATE TABLE %(name)ss (%(name)s_key VARCHAR(255), %(name)s_value TEXT, %(name)s_time REAL)''' % {"name": self.name}) self.sql('CREATE INDEX %(name)s_key_idx ON ' '%(name)ss(%(name)s_key)' % {"name": self.name}) # Set journal mode to WAL. self.commit() # close out rollback journal/transaction self.sql('pragma journal_mode=wal') # set wal self.commit() # close out rollback and commit wal change def sql(self, sql, args=None, cursor=None): """ Execute the sql with the optional args. """ self.log_debug('SQL %r %r' % (sql, args)) if not cursor: cursor = self.cursor if args: cursor.execute(sql, args) else: cursor.execute(sql) class Sessions(BasicDatabase): name = 'session' class OneTimeKeys(BasicDatabase): name = 'otk' # vim: set et sts=4 sw=4 :