#$Id: back_mssqlserver.py,v 1 2009-08-20 Zuriel Correa $ # # Copyright (c) 2009 # # 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. # '''MS Sql Server backend via odbc for Roundup.''' #Note that this file is a modified copy of back_postgresql.py __docformat__ = 'restructuredtext' import os, shutil, popen2, time import datetime import re import dbi, odbc import logging from indexer_rdbms import Indexer from roundup import hyperdb, date, password from roundup.hyperdb import String, Password, Date, Interval, Link, \ Multilink, DatabaseError, Boolean, Number, Node from roundup.backends import rdbms_common from roundup.backends import sessions_rdbms def connection_dict(config, dbnamestr=None): ''' read_default_group is MySQL-specific, ignore it ''' d = rdbms_common.connection_dict(config, dbnamestr) #logging.getLogger('hyperdb').info('Connection dictionary [importingdata] = %r'%(config.items(),)) if d.has_key('read_default_group'): del d['read_default_group'] if d.has_key('read_default_file'): del d['read_default_file'] return d def db_create(config): """Clear all database contents and drop database itself""" command = "CREATE DATABASE %s"%config.RDBMS_NAME logging.getLogger('hyperdb').info(command) db_command(config, command) def db_nuke(config, fail_ok=0): """Clear all database contents and drop database itself""" command = 'DROP DATABASE %s'% config.RDBMS_NAME logging.getLogger('hyperdb').info(command) db_command(config, command) if os.path.exists(config.DATABASE): shutil.rmtree(config.DATABASE) def db_command(config, command): '''Perform some sort of database-level command. Retry 10 times if we fail by conflicting with another user. ''' template1 = connection_dict(config) template1['database'] = 'template1' logging.getLogger('hyperdb').info('Running db_command= %r'%command) cursor = open_connection(config) mssql_command(cursor, command, 10) def mssql_command(cursor, command, retrytimes): '''Execute the sql 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. ''' logging.getLogger('hyperdb').info('Running pg_command = %r'%command) try: cursor.execute(command) except RuntimeError, err: response = str(err).split('\n')[0] if response.find('FATAL') != -1: raise RuntimeError, response else: #I don't know if this applies to sql server -.- msgs = [ 'is being accessed by other users', 'could not serialize access due to concurrent update', ] can_retry = 0 for msg in msgs: if response.find(msg) == -1: can_retry = 1 if can_retry and repeated > 0: time.sleep(1) mssql_command(cursor, command, retrytimes - 1) return 1 raise RuntimeError, response def open_connection(config): db = rdbms_common.connection_dict(config, 'database') #The next line exposes username and password in the log if uncommented, but is useful when debugging the config #logging.getLogger('hyperdb').info('Opening db settings = %r'%db) try: conn = odbc.odbc('Driver={SQL Native Client};Server=%s;Database=%s;Uid=%s;Pwd=%s;'%(db['host'], 'master', db['user'], db['password'],) ) except RuntimeError, message: raise hyperdb.DatabaseError, message cursor = conn.cursor() return cursor def db_exists(config): """Check if the database already exists""" db = connection_dict(config, 'database') try: #The connection string doesn't consider the port number. conn = odbc.odbc('Driver={SQL Native Client};Server=%s;Database=%s;Uid=%s;Pwd=%s;'%(db['host'], db['database'], db['user'], db['password'],) ) conn.close() return 1 except: return 0 class BasicDatabase_SqlServer: ''' Provide a nice encapsulation of an RDBMS table. Keys are id strings, values are automatically marshalled data. ''' def __init__(self, db): #logging.getLogger('hyperdb').info('Zuriel: BasicDatabase.init: self = %s, db = %s '% (self, db,) ) self.db = db self.cursor = self.db.cursor def clear(self): #Zuriel: Changed for mssql compliance self.cursor.execute('delete from [%ss]'%self.name) def exists(self, infoid): n = self.name #Zuriel: Changed for mssql compliance #self.cursor.execute('select count(*) from %ss where %s_key=%s'%(n, n, self.db.arg), (infoid,)) self.cursor.execute('select count(*) from [%ss] where %s_key=\'%s\''%(n, n, infoid.replace("'","\'\'"),)) return int(self.cursor.fetchone()[0]) _marker = [] def get(self, infoid, value, default=_marker): n = self.name #Zuriel: Changed for mssql compliance #self.cursor.execute('select %s_value from %ss where %s_key=%s'%(n, n, n, self.db.arg), (infoid,)) self.cursor.execute('select %s_value from [%ss] where %s_key=\'%s\''%(n, n, n, infoid.replace("'","\'\'"),)) res = self.cursor.fetchone() if not res: if default != self._marker: return default raise KeyError, 'No such %s "%s"'%(self.name, infoid) values = eval(res[0]) return values.get(value, None) def getall(self, infoid): n = self.name """logging.getLogger('hyperdb').info('Z: Get All:: n = %r'%n)""" #Zuriel: Changed for mssql compliance self.cursor.execute('select %s_value from [%ss] where %s_key=\'%s\''%(n, n, n, infoid.replace("'","\'\'"),)) res = self.cursor.fetchone() if not res: raise KeyError, 'No such %s "%s"'%(self.name, infoid) return eval(res[0]) def set(self, infoid, **newvalues): c = self.cursor n = self.name a = self.db.arg #Zuriel: Changed for mssql compliance #c.execute('select %s_value from %ss where %s_key=%s'%(n, n, n, a), (infoid,)) c.execute('select %s_value from [%ss] where %s_key=\'%s\''%(n, n, n, infoid.replace("'","\'\'"),)) res = c.fetchone() if res: values = eval(res[0]) else: values = {} values.update(newvalues) if res: #Zuriel: Changed for mssql compliance #sql = 'update %ss set %s_value=%s where %s_key=%s'%(n, n, a, n, a) #args = (repr(values), infoid) sql = 'update [%ss] set %s_value=\'%s\' where %s_key=\'%s\''%(n, n, repr(values).replace("'","\'\'"), n, infoid.replace("'","\'\'")) else: #Zuriel: Changed for mssql compliance #sql = 'insert into %ss (%s_key, %s_time, %s_value) values (%s, %s, %s)'%(n, n, n, n, a, a, a) #args = (infoid, time.time(), repr(values)) sql = 'insert into [%ss] (%s_key, %s_time, %s_value) values (\'%s\', \'%s\', \'%s\')'%(n, n, n, n, infoid.replace("'","\'\'"), time.time(), repr(values).replace("'","\'\'")) #Zuriel: Changed for mssql compliance #c.execute(sql, args) c.execute(sql) def destroy(self, infoid): #Zuriel: Changed for mssql compliance #self.cursor.execute('delete from %ss where %s_key=%s'%(self.name, self.name, self.db.arg), (infoid,)) self.cursor.execute('delete from [%ss] where %s_key=\'%s\''%(self.name, self.name, infoid.replace("'","\'\'"),)) def updateTimestamp(self, infoid): """ don't update every hit - once a minute should be OK """ now = time.time() #Zuriel: Changed for mssql compliance """ 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)) """ self.cursor.execute('update [%ss] set %s_time=\'%s\' where %s_key=\'%s\' and %s_time < \'%s\''%(self.name, self.name, now, self.name, infoid.replace("'","\'\'"), self.name, 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 #Zuriel: Changed for mssql compliance #self.cursor.execute('delete from %ss where %s_time < %s'%(self.name, self.name, self.db.arg), (old, )) self.cursor.execute('delete from [%ss] where %s_time < \'%s\''%(self.name, self.name, old, )) class Sessions(BasicDatabase_SqlServer): name = 'session' class OneTimeKeys(BasicDatabase_SqlServer): name = 'otk' class Database(rdbms_common.Database): arg = '%s' #Use this property to turn on identity insert which is required to import data from csv files. #The default should be 0 which means that identity insert will be off. ImportingData = 1 # used by some code to switch styles of query implements_intersect = 1 def __init__(self, config, journaltag=None): rdbms_common.Database.__init__(self, config, journaltag) self.indexer = Indexer(self) logging.getLogger('hyperdb').info("in db init and indexer=" + str(dir(self.indexer))) def getSessionManager(self): return Sessions(self) def getOTKManager(self): return OneTimeKeys(self) def sql_open_connection(self): db = connection_dict(self.config, 'database') #logging.getLogger('hyperdb').info('open database %r'%db['database']) #Close the connection if it already exists. #self.close_connection() - Not necessary try: conn = odbc.odbc('Driver={SQL Native Client};Server=%s;Database=%s;Uid=%s;Pwd=%s;'%(db['host'], db['database'], db['user'], db['password'],) ) except Exception, message: #Used to be logged, now it gives back the error raise hyperdb.DatabaseError, message cursor = conn.cursor() return (conn, cursor) def close_connection(self): try: self.cursor.close() logging.getLogger('hyperdb').info('Closing DB connection: cursor closed.') except Exception, message: logging.getLogger('hyperdb').info('Closing DB connection: cursor close failed. Probably the cursor object was not defined. This error can be ignored.') try: self.conn.close() logging.getLogger('hyperdb').info('Closing DB connection: connection closed.') except Exception, message: logging.getLogger('hyperdb').info('Closing DB connection: connection close failed. Probably the connection object was not defined. This error can be ignored.') def open_connection(self): if not db_exists(self.config): db_create(self.config) self.conn, self.cursor = self.sql_open_connection() #logging.getLogger('hyperdb').info("ext config=" + str(self.config)) self.cursor.execute("Select name from sysobjects where type='U' and lower(name)='schema'") if self.cursor.fetchone(): self.load_dbschema() else: self.rollback() self.init_dbschema() self.sql("CREATE TABLE [schema] ([schema] VARCHAR(8000))") self.sql("CREATE TABLE [dual] ([dummy] integer)") self.sql("insert into [dual] values (1)") self.create_version_2_tables() def create_version_2_tables(self): # OTK store self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), otk_value varchar(255), otk_time REAL)''') self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') # Sessions store self.sql('''CREATE TABLE sessions ( session_key VARCHAR(255), session_time REAL, session_value VARCHAR(255))''') self.sql('''CREATE INDEX sessions_key_idx ON sessions(session_key)''') # full-text indexing store self.sql('''CREATE TABLE __textids ( _textid integer identity(1,1) primary key, _class VARCHAR(255), _itemid VARCHAR(255), _prop VARCHAR(255))''') self.sql('''CREATE TABLE __words (_word VARCHAR(1000), _textid integer)''') 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 on "%ss"'%(name,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 public.__words (_word, _textid)''') def fix_version_4_tables(self): # note this is an explicit call now for cn, klass in self.classes.iteritems(): self.sql('select id from _%s where __retired__<>0'%(cn,)) ids = self.cursor.fetchall() for (id,) in ids: self.sql('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 create_journal_table(self, spec): """ create the journal table for a class given the spec and already-determined cols """ # journal table cols = ','.join(['%s varchar'%x for x in 'nodeid date tag action params'.split()]) sql = """create table %s__journal ( nodeid integer, date %s, tag varchar(255), action varchar(255), params varchar(8000))""" % (spec.classname, self.hyperdb_to_sql_datatype(hyperdb.Date)) self.sql(sql) self.create_journal_table_indexes(spec) def add_actor_column(self): # update existing tables to have the new actor column tables = self.database_schema['tables'] for name in tables.keys(): self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name) def __repr__(self): return '' % id(self) def sql_commit(self, fail_ok=False): ''' Actually commit to the database. ''' logging.getLogger('hyperdb').info('commit') try: self.conn.commit() except RuntimeError, message: # we've been instructed that this commit is allowed to fail if fail_ok and str(message).endswith('could not serialize ' 'access due to concurrent update'): logging.getLogger('hyperdb').info('commit FAILED, but fail_ok (Fail is ok in this case)') else: raise self.cursor.close() self.conn.close() # open a new cursor for subsequent work self.cursor = self.conn.cursor() def sql_stringquote(self, value): if value is None: return "" return str(value).replace('\'','\'\'') def sql_index_exists(self, table_name, index_name): sql = 'SELECT count(*) FROM sys.indexes WHERE ' sql += 'object_id = OBJECT_ID(\'%s\') AND name = \'%s\''%(table_name, index_name) self.sql(sql) return self.cursor.fetchone()[0] 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" on "%s"'%(index_name, table_name) self.sql(index_sql) def determine_all_columns(self, spec): """Figure out the columns from the spec and also add internal columns""" cols, mls = self.determine_columns(spec.properties.items()) # add on our special columns cols.append(('id', 'INTEGER PRIMARY KEY IDENTITY(1,1)')) cols.append(('__retired__', 'INTEGER DEFAULT 0')) return cols, mls def drop_class_table(self, cn): sql = 'drop table _%s'%cn self.sql(sql) def newid(self, classname): #This is not recommended for a Sql Server DB #sql = "Select (case when ident_current('_%s') = 1 then case when (select count(*) from _%s) = 0 then 1 else 2 end else ident_current('_%s')+1 end) [nextid] from dual"%(classname, classname, classname,) #self.sql(sql) #return str(self.cursor.fetchone()[0]) #This method is not meaningfull for this backend implementation but it is required to be defined. raise Exception, "The method [newid] must not be used." def setid(self, classname, setid): sql = "dbcc checkident(\'_%s\',reseed,%s)"%(classname, int(setid)) self.sql(sql) def clear(self): rdbms_common.Database.clear(self) # reset the identities for cn in self.classes.keys(): self.cursor.execute('dbcc checkident(\'_%s_ids\',reseed,1)'%cn) def sql(self, sql, args=None): """ Execute the sql with the optional args. """ logging.getLogger('hyperdb').info('Executing: SQL , args = %r %r'%(sql, args)) if args: values = [] #All this needs to be done because there are inconsistencies in the roundup code #Sometimes the arguments are parsed before calling this function, some other times they are not. for i in args: if i is not None: if type(i) == type(1):#if its numeric values.append(i); continue if str(i).startswith("'") and str(i).endswith("'"): text = str(i)[1:-1] if self.isSQLServerDate(text): values.append(str(i)) continue #If it is a number and has quotes, remove the quotes. if self.isNumeric(text): values.append(text); continue values.append('\'' + str(i).replace('\'','\'\'') + '\'') else: values.append("null") logging.getLogger('hyperdb').info('SQL command with args = %r'%(sql % tuple(values))) self.cursor.execute(sql % tuple(values)) else: self.cursor.execute(sql) def isNumeric(self, v): logging.getLogger('hyperdb').info('is numeric = %r'%(v)) if not str(v).replace(".","").replace("-","").isdigit(): return False try: float(v) return True except ValueError: return False return True def isSQLServerDate(self, v): m = re.match("([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{3})",str(v)) if m is None: return False x = "string" try: x = time.strptime(str(v)[0:-4], "%Y-%m-%d %H:%M:%S") except: return False return str(type(x)).find("struct_time") > 0 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: self.database_schema = eval(schema[0]) 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 fix_version_3_tables(self): 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] VARCHAR(8000)'%(name, name)) 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(cl.properties.items()) # we'll be supplied these props if we're doing an import values = node.copy() if not values.has_key('creator'): # 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 not values.has_key(col): 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 self.cache.has_key(key): del self.cache[key] self.cache_lru.remove(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(str(value.as_seconds())) else: vals.append(str(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(str(value)) else: vals.append("null") sqlPrefix = "" sqlSufix = "" #We will add the nodeid only if it is greater than 0 meaning we are # inserting from Class.create_inner. If less than 0, that means # that we are inserting a from CLASS.import_list. if nodeid > 0: sqlPrefix = " set identity_insert _%s on;" % (classname,) sqlSufix = " set identity_insert _%s off;" % (classname,) vals.append(nodeid) cols.append(("id","")) #vals.append(nodeid) vals = tuple(vals) # make sure the ordering is correct for column name -> column value cols = ','.join([col for col,dt in cols]) #+ ',id' #logging.getLogger('hyperdb').info("s = " + s) logging.getLogger('hyperdb').info("cols = " + cols) logging.getLogger('hyperdb').info("vals = " + str(vals) ) sql = sqlPrefix sql += "DECLARE @InsertedRows AS TABLE (Id int); DECLARE @NewId AS INT; Set NOCOUNT ON;" sql += "insert into _%s (%s) OUTPUT Inserted.id INTO @InsertedRows values (%s);" % (classname, cols, ",".join(vals)) sql += 'SELECT Id FROM @InsertedRows;' sql += sqlSufix logging.getLogger('hyperdb').info('rdbms_common.addNode: sql='+ sql) self.sql(sql) nodeid = self.cursor.fetchone()[0] # 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, entry, nodeid) self.sql(sql) return nodeid def save_journal(self, classname, cols, nodeid, journaldate, journaltag, action, params): """ Save the journal entry to the database """ entry = (classname, cols, nodeid, journaldate, journaltag, "'"+action+"'", self.to_sql_value(hyperdb.String)(params)) # do the insert sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)' % entry self.sql(sql) def setnode(self, classname, nodeid, values, multilink_changes={}): """ Change the specified node. """ 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 self.cache.has_key(key): del self.cache[key] self.cache_lru.remove(key) cl = self.classes[classname] props = cl.getprops() cols = [] mls = [] # add the multilinks separately for col in values.keys(): 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 = "null" 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) """ def filter_sql(self, sql): logging.getLogger('hyperdb').info("Im in filter_sql") rdbms_common.filter_sql(self, sql) def filter(self, search_matches, filterspec, sort=[], group=[]): logging.getLogger('hyperdb').info("Im in filter.") rdbms_common.filter(self, search_matches, filterspec, sort, group) """ def sqlDateToHyperDB_value(dateString): """ lambda x:date.Date(datetime.datetime(*time.strptime(str(x).replace("."," ").replace("'",""),"%Y-%m-%d %H:%M:%S 000")[0:6]).strftime("%Y-%m-%d.%H:%M:%S.000")), """ dtString = str(dateString).replace("."," ").replace("'","") #There could be two format types, one like "2008-12-24 00:00:00 000" , the other like "Wed May 16 15:34:34 2007". dt = None try: dt = time.strptime(dtString) except: dt = time.strptime(dtString,"%Y-%m-%d %H:%M:%S 000") d = datetime.datetime(*dt[0:6]).strftime("%Y-%m-%d.%H:%M:%S.000") return date.Date(d) #Note that File is not a type, odbc gives problems with varchar(max) because it returns a blank field sometimes. #The sql type text should not be used because it will be deprecated and because you cannot use an equality for comparisons hyperdb_to_sql_datatypes = { hyperdb.String : 'VARCHAR(8000)', hyperdb.Date : 'DATETIME', hyperdb.Link : 'INTEGER', hyperdb.Interval : 'VARCHAR(255)', hyperdb.Password : 'VARCHAR(255)', hyperdb.Boolean : 'BOOLEAN', hyperdb.Number : 'REAL', } hyperdb_to_sql_value = { hyperdb.String : lambda x: '\'' + str(x).replace('\'','\'\'') + '\'' if x is not None else "null", # fractional seconds by default hyperdb.Date : lambda x: "'" + x.formal(sep=' ', sec='%06.3f') + "'", hyperdb.Link : int, hyperdb.Interval : str, hyperdb.Password : lambda x: ('\'' + str(x).replace('\'','\'\'') + '\'') if x is not None else "null", hyperdb.Boolean : lambda x: x and 'TRUE' or 'FALSE', hyperdb.Number : lambda x: x, hyperdb.Multilink : lambda x: x, # used in journal marshalling } sql_to_hyperdb_value = { hyperdb.String : str, hyperdb.Date : sqlDateToHyperDB_value, hyperdb.Link : str, hyperdb.Interval : date.Interval, hyperdb.Password : lambda x: password.Password(encrypted=x), hyperdb.Boolean : rdbms_common._bool_cvt, hyperdb.Number : rdbms_common._num_cvt, hyperdb.Multilink : lambda x: x, # used in journal marshalling } class MsSqlServerClass: #this syntax is better because it works in multiple database systems order_by_null_values = '(case when %s is not null then 1 else 0 end)' def create_inner(self, **propvalues): """ Called by create, in-between the audit and react calls. """ if propvalues.has_key('id'): raise KeyError, '"id" is reserved' if self.db.journaltag is None: raise DatabaseError, _('Database open read-only') if propvalues.has_key('creator') or propvalues.has_key('actor') or \ propvalues.has_key('creation') or propvalues.has_key('activity'): raise KeyError, '"creator", "actor", "creation" and '\ '"activity" are reserved' # new node's id #self.db.newid(self.classname) #This number doesn't matter, it will be assigned to the real value later newid = -2 journals = [] addText = [] # validate propvalues num_re = re.compile('^\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 type(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: journals.append([link_class, value, 'link',(self.classname, newid, key)]) #This is now done at the end, once we know the real newid #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__'): 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 type(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: journals.append([link_class, nodeid, 'link', (self.classname, newid, key)]) #This is now done at the end, once we know the real newid #self.db.addjournal(link_class, nodeid, 'link', (self.classname, newid, key)) elif isinstance(prop, String): if type(value) != type('') and type(value) != type(u''): raise TypeError, 'new property "%s" not a string'%key if prop.indexme: addText.append([(self.classname, newid, key), value]) #This is now done at the end, once we know the real newid #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, 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 propvalues.has_key(key): continue if key == self.key: raise ValueError, 'key property "%s" is required'%key if isinstance(prop, Multilink): propvalues[key] = [] else: propvalues[key] = None # Obtain the real newid when adding the node newid = self.db.addnode(self.classname, newid, propvalues) for j in journals: self.db.addjournal(j[0], j[1], j[2], (j[3][0], newid, j[3][2])) for i in addText: self.db.indexer.add_text((i[0][0], newid, i[0][2]), i[1]) if self.do_journal: self.db.addjournal(self.classname, newid, ''"create", {}) # XXX numeric ids return str(newid) class Class(MsSqlServerClass, rdbms_common.Class): pass class IssueClass(MsSqlServerClass, rdbms_common.IssueClass): pass class FileClass(MsSqlServerClass, rdbms_common.FileClass): pass class Indexer(Indexer): def add_text(self, identifier, text, mime_type='text/plain'): """ "identifier" is (classname, itemid, property) """ logging.getLogger('hyperdb').info("Inside back_mssqlserver::Indexer::add_text") 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\'' % identifier self.db.cursor.execute(sql) r = self.db.cursor.fetchone() if not r: # not previously indexed #id = self.db.newid('__textids') sql = 'DECLARE @InsertedRows AS TABLE (Id int); DECLARE @NewId AS INT; Set NOCOUNT ON;' sql += 'insert into __textids (_class, _itemid, _prop) ' sql += ' OUTPUT Inserted._textid INTO @InsertedRows ' sql += ' values (\'%s\', \'%s\', \'%s\');'%identifier sql += 'SELECT Id FROM @InsertedRows;' self.db.cursor.execute(sql) id = self.db.cursor.fetchone()[0] else: id = int(r[0]) # clear out any existing indexed values sql = 'delete from __words where _textid=\'%s\'' % (str(id),) self.db.cursor.execute(sql) # ok, find all the unique words in the text text = unicode(text, "utf-8", "replace").upper() wordlist = [w.encode("utf-8") for w in re.findall(r'(?u)\b\w{2,25}\b', text)] words = set() for word in wordlist: if self.is_stopword(word): continue if len(word) > 25: continue words.add(word) # for each word, add an entry in the db #self.db.cursor.executemany(sql) #words = [(word, id) for word in words] for word in words: sql = 'insert into __words (_word, _textid) values (\'%s\', \'%s\')'%(word, id,) self.db.cursor.execute(sql) def find(self, wordlist): """look up all the words in the wordlist. If none are found return an empty dictionary * more rules here """ logging.getLogger('hyperdb').info("Inside back_mssqlserver::Indexer::find") if not wordlist: return [] l = [word.upper() for word in wordlist if 26 > len(word) > 2] if not l: return [] logging.getLogger('hyperdb').info("Inside find, l = %s" % l) # simple AND search sql = "select distinct(_textid) from __words where _word ='%s'" sql = "\nINTERSECT\n".join([sql]*len(l)) logging.getLogger('hyperdb').info("Inside find, select sql = %s" % (sql%tuple(l))) self.db.cursor.execute(sql%tuple(l)) r = self.db.cursor.fetchall() logging.getLogger('hyperdb').info("r is = %s" % len(r)) if not r: return [] a = ','.join([self.db.arg] * len(r)) sql = 'select _class, _itemid, _prop from __textids '\ 'where _textid in (%s)'%str("'"+"','".join(tuple([str(row[0]) for row in r]))+"'") logging.getLogger('hyperdb').info("sql 2 = %s" % sql) self.db.cursor.execute(sql) return self.db.cursor.fetchall()