"""SQL utilities functions and classes.:organization: Logilab:copyright: 2001-2009 LOGILAB S.A. (Paris, FRANCE), license is LGPL v2.:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr:license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses"""__docformat__="restructuredtext en"fromwarningsimportwarnfromdatetimeimportdatetime,date,timedeltaimportlogilab.commonaslgcfromlogilab.common.shellutilsimportProgressBarfromlogilab.commonimportdbfromlogilab.common.adbhimportget_adv_func_helperfromlogilab.common.sqlgenimportSQLGeneratorfromindexerimportget_indexerfromcubicwebimportBinary,ConfigurationErrorfromcubicweb.utilsimporttodate,todatetimefromcubicweb.common.uilibimportremove_html_tagsfromcubicweb.serverimportSQL_CONNECT_HOOKSfromcubicweb.server.utilsimportcrypt_passwordlgc.USE_MX_DATETIME=FalseSQL_PREFIX='cw_'defsqlexec(sqlstmts,cursor_or_execute,withpb=True,delimiter=';'):"""execute sql statements ignoring DROP/ CREATE GROUP or USER statements error. If a cnx is given, commit at each statement """ifhasattr(cursor_or_execute,'execute'):execute=cursor_or_execute.executeelse:execute=cursor_or_executesqlstmts=sqlstmts.split(delimiter)ifwithpb:pb=ProgressBar(len(sqlstmts))forsqlinsqlstmts:sql=sql.strip()ifwithpb:pb.update()ifnotsql:continue# some dbapi modules doesn't accept unicode for sql stringexecute(str(sql))ifwithpb:printdefsqlgrants(schema,driver,user,text_index=True,set_owner=True,skip_relations=(),skip_entities=()):"""return sql to give all access privileges to the given user on the system schema """fromyams.schema2sqlimportgrant_schemafromcubicweb.server.sourcesimportnativeoutput=[]w=output.appendw(native.grant_schema(user,set_owner))w('')iftext_index:indexer=get_indexer(driver)w(indexer.sql_grant_user(user))w('')w(grant_schema(schema,user,set_owner,skip_entities=skip_entities,prefix=SQL_PREFIX))return'\n'.join(output)defsqlschema(schema,driver,text_index=True,user=None,set_owner=False,skip_relations=('has_text','identity'),skip_entities=()):"""return the system sql schema, according to the given parameters"""fromyams.schema2sqlimportschema2sqlfromcubicweb.server.sourcesimportnativeifset_owner:assertuser,'user is argument required when set_owner is true'output=[]w=output.appendw(native.sql_schema(driver))w('')iftext_index:indexer=get_indexer(driver)w(indexer.sql_init_fti())w('')dbhelper=get_adv_func_helper(driver)w(schema2sql(dbhelper,schema,prefix=SQL_PREFIX,skip_entities=skip_entities,skip_relations=skip_relations))ifdbhelper.users_supportanduser:w('')w(sqlgrants(schema,driver,user,text_index,set_owner,skip_relations,skip_entities))return'\n'.join(output)defsqldropschema(schema,driver,text_index=True,skip_relations=('has_text','identity'),skip_entities=()):"""return the sql to drop the schema, according to the given parameters"""fromyams.schema2sqlimportdropschema2sqlfromcubicweb.server.sourcesimportnativeoutput=[]w=output.appendw(native.sql_drop_schema(driver))w('')iftext_index:indexer=get_indexer(driver)w(indexer.sql_drop_fti())w('')w(dropschema2sql(schema,prefix=SQL_PREFIX,skip_entities=skip_entities,skip_relations=skip_relations))return'\n'.join(output)try:frommx.DateTimeimportDateTimeType,DateTimeDeltaTypeexceptImportError:DateTimeType=DateTimeDeltaType=NoneclassSQLAdapterMixIn(object):"""Mixin for SQL data sources, getting a connection from a configuration dictionary and handling connection locking """def__init__(self,source_config):try:self.dbdriver=source_config['db-driver'].lower()self.dbname=source_config['db-name']exceptKeyError:raiseConfigurationError('missing some expected entries in sources file')self.dbhost=source_config.get('db-host')port=source_config.get('db-port')self.dbport=portandint(port)orNoneself.dbuser=source_config.get('db-user')self.dbpasswd=source_config.get('db-password')self.encoding=source_config.get('db-encoding','UTF-8')self.dbapi_module=db.get_dbapi_compliant_module(self.dbdriver)self.binary=self.dbapi_module.Binaryself.dbhelper=self.dbapi_module.adv_func_helperself.sqlgen=SQLGenerator()defget_connection(self,user=None,password=None):"""open and return a connection to the database"""ifuserorself.dbuser:self.info('connecting to %s@%s for user %s',self.dbname,self.dbhostor'localhost',userorself.dbuser)else:self.info('connecting to %s@%s',self.dbname,self.dbhostor'localhost')cnx=self.dbapi_module.connect(self.dbhost,self.dbname,userorself.dbuser,passwordorself.dbpasswd,port=self.dbport)init_cnx(self.dbdriver,cnx)#self.dbapi_module.type_code_test(cnx.cursor())returncnxdefmerge_args(self,args,query_args):ifargsisnotNone:args=dict(args)forkey,valinargs.items():# convert cubicweb binary into db binaryifisinstance(val,Binary):val=self.binary(val.getvalue())# XXX <3.2 bw compateliftype(val)isDateTimeType:warn('found mx date time instance, please update to use datetime',DeprecationWarning)val=datetime(val.year,val.month,val.day,val.hour,val.minute,int(val.second))eliftype(val)isDateTimeDeltaType:warn('found mx date time instance, please update to use datetime',DeprecationWarning)val=timedelta(0,int(val.seconds),0)args[key]=val# should not collideargs.update(query_args)returnargsreturnquery_argsdefprocess_result(self,cursor):"""return a list of CubicWeb compliant values from data in the given cursor """descr=cursor.descriptionencoding=self.encodingprocess_value=self.dbapi_module.process_valuebinary=Binaryresults=cursor.fetchall()fori,lineinenumerate(results):result=[]forcol,valueinenumerate(line):ifvalueisNone:result.append(value)continueresult.append(process_value(value,descr[col],encoding,binary))results[i]=resultreturnresultsdefpreprocess_entity(self,entity):"""return a dictionary to use as extra argument to cursor.execute to insert/update an entity into a SQL database """attrs={}eschema=entity.e_schemaforattr,valueinentity.items():rschema=eschema.subject_relation(attr)ifrschema.is_final():atype=str(entity.e_schema.destination(attr))ifatype=='Boolean':value=self.dbhelper.boolean_value(value)elifatype=='Password':# if value is a Binary instance, this mean we got it# from a query result and so it is already encryptedifisinstance(value,Binary):value=value.getvalue()else:value=crypt_password(value)# XXX needed for sqlite but I don't think it is for other backendselifatype=='Datetime'andisinstance(value,date):value=todatetime(value)elifatype=='Date'andisinstance(value,datetime):value=todate(value)elifisinstance(value,Binary):value=self.binary(value.getvalue())# XXX <3.2 bw compateliftype(value)isDateTimeType:warn('found mx date time instance, please update to use datetime',DeprecationWarning)value=datetime(value.year,value.month,value.day,value.hour,value.minute,int(value.second))eliftype(value)isDateTimeDeltaType:warn('found mx date time instance, please update to use datetime',DeprecationWarning)value=timedelta(0,int(value.seconds),0)attrs[SQL_PREFIX+str(attr)]=valuereturnattrsfromloggingimportgetLoggerfromcubicwebimportset_log_methodsset_log_methods(SQLAdapterMixIn,getLogger('cubicweb.sqladapter'))definit_sqlite_connexion(cnx):# XXX should not be publicly exposed#def comma_join(strings):# return ', '.join(strings)#cnx.create_function("COMMA_JOIN", 1, comma_join)classconcat_strings(object):def__init__(self):self.values=[]defstep(self,value):ifvalueisnotNone:self.values.append(value)deffinalize(self):return', '.join(self.values)# renamed to GROUP_CONCAT in cubicweb 2.45, keep old name for bw compat for# some timecnx.create_aggregate("CONCAT_STRINGS",1,concat_strings)cnx.create_aggregate("GROUP_CONCAT",1,concat_strings)def_limit_size(text,maxsize,format='text/plain'):iflen(text)<maxsize:returntextifformatin('text/html','text/xhtml','text/xml'):text=remove_html_tags(text)iflen(text)>maxsize:text=text[:maxsize]+'...'returntextdeflimit_size3(text,format,maxsize):return_limit_size(text,maxsize,format)cnx.create_function("LIMIT_SIZE",3,limit_size3)deflimit_size2(text,maxsize):return_limit_size(text,maxsize)cnx.create_function("TEXT_LIMIT_SIZE",2,limit_size2)importyams.constraintsifhasattr(yams.constraints,'patch_sqlite_decimal'):yams.constraints.patch_sqlite_decimal()sqlite_hooks=SQL_CONNECT_HOOKS.setdefault('sqlite',[])sqlite_hooks.append(init_sqlite_connexion)definit_cnx(driver,cnx):forhookinSQL_CONNECT_HOOKS.get(driver,()):hook(cnx)