Utilities/python/conddblib.py

0001 '''CMS Conditions DB Python library.
0002 '''
0003
0004 __author__ = 'Miguel Ojeda'
0005 __copyright__ = 'Copyright 2013, CERN'
0006 __credits__ = ['Giacomo Govi', 'Miguel Ojeda', 'Andreas Pfeiffer']
0007 __license__ = 'Unknown'
0008 __maintainer__ = 'Giacomo Govi'
0009 __email__ = 'giacomo.govi@cern.ch'
0010
0011
0012 import os
0013 import hashlib
0014 import logging
0015
0016 import sqlalchemy
0017 import sqlalchemy.ext.declarative
0018 import enum
0019 from sqlalchemy import Enum
0020
0021 schema_name = 'cms_conditions'
0022 dbuser_name = 'cms_conditions'
0023 dbreader_user_name = 'cms_cond_general_r'
0024 dbwriter_user_name = 'cms_cond_general_w'
0025 logger = logging.getLogger(__name__)
0026
0027 #authentication/authorization params
0028 authPathEnvVar = 'COND_AUTH_PATH'
0029 dbkey_filename = 'db.key'
0030 dbkey_folder = os.path.join('.cms_cond',dbkey_filename)
0031
0032 # frontier services
0033 PRO ='PromptProd'
0034 ARC ='FrontierArc'
0035 INT ='FrontierInt'
0036 DEV ='FrontierPrep'
0037 # oracle read only services
0038 ORAPRO = 'cms_orcon_adg'
0039 ORAARC = 'cmsarc_lb'
0040 # oracle masters
0041 ORAINT = 'cms_orcoff_int'
0042 ORADEV = 'cms_orcoff_prep'
0043 ONLINEORAPRO = 'cms_orcon_prod'
0044 ONLINEORAINT = 'cmsintr_lb'
0045
0046 # Set initial level to WARN.  This so that log statements don't occur in
0047 # the absense of explicit logging being enabled.
0048 if logger.level == logging.NOTSET:
0049     logger.setLevel(logging.WARN)
0050
0051 # Utility functions
0052 def hash(data):
0053     return hashlib.sha1(data.encode('ascii')).hexdigest()
0054
0055
0056 # Constants
0057 empty_label = '-'
0058
0059 name_length = 100
0060 description_length = 4000
0061 hash_length = len(hash(''))
0062
0063 web_experts_email = 'cms-cond-dev@cern.ch'
0064 offline_db_experts_email = 'cms-offlinedb-exp@cern.ch'
0065 offline_db_experts_phone = '+41 22 76 70817, or 70817 from CERN; check https://twiki.cern.ch/twiki/bin/viewauth/CMS/DBShifterHelpPage if it does not work; availability depends on the state of the LHC'
0066
0067 contact_help = 'If you need assistance, please write an email to %s and %s. If you need immediate/urgent assistance, you can call the Offline DB expert on call (%s).' % (offline_db_experts_email, web_experts_email, offline_db_experts_phone)
0068 database_help = '''
0069     The database parameter (--db) refers to the database where the tool
0070     will connect to read all the data. By default, the production account
0071     (through Frontier) will be used.
0072
0073     In subcommands which take a source and a destination, --db always refers to
0074     the source, and --destdb to the destination. For both of them the following
0075     rules apply.
0076
0077     The database parameter can be an official alias, a filename or any
0078     valid SQLAlchemy URL.
0079
0080     The official aliases are the following strings (first column):
0081
0082       Alias         Level        Database       RO/RW       Notes
0083       ------------  -----------  -------------  ----------  -------------------------------
0084
0085       pro           Production   Frontier (ADG) read-only   Default.
0086       arc           Archive      Frontier       read-only
0087       int           Integration  Frontier       read-only
0088       dev           Development  Frontier       read-only
0089       boost         Production   Frontier       read-only
0090       boostprep     Development  Frontier       read-only
0091
0092       orapro        Production   Oracle (ADG)   read-only   Password required.
0093       oraarc        Archive      Oracle         read-only   Password required.
0094       oraint        Integration  Oracle         read-write  Password required.
0095       oradev        Development  Oracle         read-write  Password required.
0096
0097       onlineorapro  Production   Oracle         read-write  Password required. Online only.
0098       onlineoraint  Online Int   Oracle         read-write  Password required. Online only.
0099
0100     Most of the time, if you are a regular user, you will want to read/copy
0101     conditions from the Frontier production account. Therefore, you can omit
0102     the --db parameter, unless you want to read from somewhere else,
0103     e.g. from your local SQLite file.
0104
0105     In addition, the parameter may be a filename (path) pointing to a local
0106     SQLite file, e.g.
0107
0108       file.db
0109       relative/path/to/file.db
0110       /absolute/path/to/file.db
0111
0112     Finally, any valid SQLAlchemy URL can be used. This allows full
0113     flexibility in cases where it may be needed, e.g.
0114
0115       sqlite://              In-memory, volatile SQLite DB.
0116       oracle://user@devdb11  Your private Oracle DB in devdb11 [*]
0117
0118         [*] See https://account.cern.ch/ -> Services for more information
0119             on personal Oracle accounts.
0120
0121     For the official aliases, the password will be asked automatically
0122     interactively. The same applies for Oracle URLs where the password
0123     was not provided inside it, e.g.:
0124
0125       oracle://user@devdb11       The tool will prompt you for the password.
0126       oracle://user:pass@devdb11  Password inlined. [+]
0127
0128         [+] Caution: Never write passwords in command-line parameters in
0129             multi-user machines (e.g. lxplus), since other users can see them
0130             in the process table (e.g. ps).
0131
0132     This means that both the official aliases and the filenames are shortcuts
0133     to the full SQLAlchemy URL equivalents, e.g. the following are the same:
0134
0135        relative/path/to/file.db  ===  sqlite:///relative/path/to/file.db
0136       /absolute/path/to/file.db  ===  sqlite:////absolute/path/to/file.db
0137 '''
0138
0139 def oracle_connection_string(db_service, db_schema ):
0140     return 'oracle://%s/%s'%(db_service,db_schema)
0141
0142 class Synchronization(enum.Enum):
0143     any        = 'any'
0144     validation = 'validation'
0145     mc         = 'mc'
0146     runmc      = 'runmc'
0147     hlt        = 'hlt'
0148     express    = 'express'
0149     prompt     = 'prompt'
0150     pcl        = 'pcl'
0151     offline    = 'offline'
0152
0153 synch_list = list(x.value for x in list(Synchronization))
0154
0155 class TimeType(enum.Enum):
0156     Run  = 'Run'
0157     Time = 'Time'
0158     Lumi = 'Lumi'
0159     Hash = 'Hash'
0160     User = 'User'
0161
0162
0163 # Schema definition
0164 _Base = sqlalchemy.ext.declarative.declarative_base()
0165
0166 def fq_name( schema_name, table_name ):
0167     name = table_name
0168     if schema_name is not None:
0169         name = '%s.%s' %(schema_name, table_name)
0170     return name
0171
0172 db_models = {}
0173
0174 class _Col(Enum):
0175     nullable = 0
0176     notNull = 1
0177     pk = 2
0178
0179 class DbRef:
0180     def __init__(self,refType, refColumn):
0181         self.rtype = refType
0182         self.rcol = refColumn
0183
0184 def fq_col( schema, table, column ):
0185     fqn = '%s.%s' %(table, column)
0186     if schema is not None:
0187         fqn = '%s.%s' %(schema,fqn)
0188     return fqn
0189
0190 def make_dbtype( backendName, schemaName, baseType ):
0191     members = {}
0192     deps_reg = set()
0193     dbtype_name = '%s_%s' %(baseType.__name__,backendName)
0194     members['__tablename__'] = baseType.__tablename__
0195     members['__table_args__'] = None
0196     if schemaName is not None:
0197         members['__table_args__'] = {'schema': schemaName }
0198     for k,v in baseType.columns.items():
0199         defColVal = None
0200         if len(v)==3:
0201             defColVal = v[2]
0202         if isinstance(v[0],DbRef):
0203             refColDbt = v[0].rtype.columns[v[0].rcol][0]
0204             pk = (True if v[1]==_Col.pk else False)
0205             if v[1]==_Col.pk:
0206                 members[k] = sqlalchemy.Column(refColDbt,sqlalchemy.ForeignKey(fq_col(schemaName,v[0].rtype.__tablename__,v[0].rcol)),primary_key=True)
0207             else:
0208                 nullable = (False if v[1] == _Col.notNull else True)
0209                 members[k] = sqlalchemy.Column(refColDbt,sqlalchemy.ForeignKey(fq_col(schemaName,v[0].rtype.__tablename__,v[0].rcol)),nullable=nullable)
0210             if v[0].rtype.__name__ not in deps_reg:
0211                 deps_reg.add(v[0].rtype.__name__)
0212                 reftype_name = '%s_%s' %(v[0].rtype.__name__,backendName)
0213                 members[(v[0].rtype.__name__).lower()] = sqlalchemy.orm.relationship(reftype_name)
0214         else:
0215             if v[1]==_Col.pk:
0216                 members[k] = sqlalchemy.Column(v[0],primary_key=True)
0217             else:
0218                 nullable = (True if v[1]==_Col.nullable else False)
0219                 if defColVal is None:
0220                     members[k] = sqlalchemy.Column(v[0],nullable=nullable)
0221                 else:
0222                     members[k] = sqlalchemy.Column(v[0],nullable=nullable, default=defColVal)
0223     dbType = type(dbtype_name,(_Base,),members)
0224
0225     if backendName not in db_models.keys():
0226         db_models[backendName] = {}
0227     db_models[backendName][baseType.__name__] = dbType
0228     return dbType
0229
0230 def getSchema(tp):
0231     if tp.__table_args__ is not None:
0232         return tp.__table_args__['schema']
0233     return None
0234
0235 class Tag:
0236     __tablename__       = 'TAG'
0237     columns             = { 'name': (sqlalchemy.String(name_length),_Col.pk),
0238                             'time_type': (sqlalchemy.Enum(*tuple(TimeType.__members__.keys())),_Col.notNull),
0239                             'object_type': (sqlalchemy.String(name_length),_Col.notNull),
0240                             'synchronization': (sqlalchemy.Enum(*tuple(Synchronization.__members__.keys())),_Col.notNull),
0241                             'description': (sqlalchemy.String(description_length),_Col.notNull),
0242                             'last_validated_time':(sqlalchemy.BIGINT,_Col.notNull),
0243                             'end_of_validity':(sqlalchemy.BIGINT,_Col.notNull),
0244                             'insertion_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
0245                             'modification_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
0246                             'protection_code':(sqlalchemy.Integer,_Col.notNull,0)   }
0247
0248 class TagMetadata:
0249     __tablename__       = 'TAG_METADATA'
0250     columns             = { 'tag_name': (DbRef(Tag,'name'),_Col.pk),
0251                             'min_serialization_v': (sqlalchemy.String(20),_Col.notNull),
0252                             'min_since': (sqlalchemy.BIGINT,_Col.notNull),
0253                             'modification_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
0254
0255 class TagAuthorization:
0256     __tablename__       = 'TAG_AUTHORIZATION'
0257     columns             = { 'tag_name': (DbRef(Tag,'name'),_Col.pk),
0258                             'access_type': (sqlalchemy.Integer,_Col.notNull),
0259                             'credential': (sqlalchemy.String(name_length),_Col.notNull),
0260                             'credential_type':(sqlalchemy.Integer,_Col.notNull) }
0261
0262 class Payload:
0263     __tablename__       = 'PAYLOAD'
0264     columns             = { 'hash': (sqlalchemy.CHAR(hash_length),_Col.pk),
0265                             'object_type': (sqlalchemy.String(name_length),_Col.notNull),
0266                             'data': (sqlalchemy.BLOB,_Col.notNull),
0267                             'streamer_info':(sqlalchemy.BLOB,_Col.notNull),
0268                             'version':(sqlalchemy.String(20),_Col.notNull),
0269                             'insertion_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
0270
0271
0272 class IOV:
0273     __tablename__       = 'IOV'
0274     columns             = { 'tag_name':(DbRef(Tag,'name'),_Col.pk),
0275                             'since':(sqlalchemy.BIGINT,_Col.pk),
0276                             'insertion_time':(sqlalchemy.TIMESTAMP,_Col.pk),
0277                             'payload_hash':(DbRef(Payload,'hash'),_Col.notNull) }
0278
0279
0280 class GlobalTag:
0281     __tablename__       = 'GLOBAL_TAG'
0282     columns             = { 'name':(sqlalchemy.String(name_length),_Col.pk),
0283                             'validity': (sqlalchemy.BIGINT,_Col.notNull),
0284                             'description':(sqlalchemy.String(description_length),_Col.notNull),
0285                             'release':(sqlalchemy.String(name_length),_Col.notNull),
0286                             'insertion_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
0287                             'snapshot_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
0288
0289 class GlobalTagMap:
0290     __tablename__       = 'GLOBAL_TAG_MAP'
0291     columns             = { 'global_tag_name':(DbRef(GlobalTag,'name'),_Col.pk),
0292                             'record':(sqlalchemy.String(name_length),_Col.pk),
0293                             'label':(sqlalchemy.String(name_length),_Col.pk),
0294                             'tag_name':(DbRef(Tag,'name'),_Col.notNull) }
0295
0296
0297
0298 class TagLog:
0299     __tablename__       = 'TAG_LOG'
0300     columns             = { 'tag_name':(DbRef(Tag,'name'),_Col.pk),
0301                             'event_time':(sqlalchemy.TIMESTAMP,_Col.pk),
0302                             'action':(sqlalchemy.String(100),_Col.pk),
0303                             'user_name':(sqlalchemy.String(100),_Col.notNull),
0304                             'host_name':(sqlalchemy.String(100),_Col.notNull),
0305                             'command':(sqlalchemy.String(500),_Col.notNull),
0306                             'user_text':(sqlalchemy.String(4000),_Col.notNull) }
0307
0308 class RunInfo:
0309     __tablename__       = 'RUN_INFO'
0310     columns             = { 'run_number':(sqlalchemy.BIGINT,_Col.pk),
0311                             'start_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
0312                             'end_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
0313
0314 class BoostRunMap:
0315     __tablename__       = 'BOOST_RUN_MAP'
0316     columns             = { 'run_number':(sqlalchemy.BIGINT,_Col.pk),
0317                             'run_start_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
0318                             'boost_version': (sqlalchemy.String(20),_Col.notNull) }
0319
0320 # CondDB object
0321 class Connection(object):
0322
0323     def __init__(self, url):
0324         # Workaround to avoid creating files if not present.
0325         # Python's sqlite3 module does not use sqlite3_open_v2(),
0326         # and therefore we cannot disable SQLITE_OPEN_CREATE.
0327         # Only in the case of creating a new database we skip the check.
0328         if url.drivername == 'sqlite':
0329
0330             self.engine = sqlalchemy.create_engine(url)
0331
0332             enabled_foreign_keys = self.engine.execute('pragma foreign_keys').scalar()
0333             supports_foreign_keys = enabled_foreign_keys is not None
0334             if not supports_foreign_keys:
0335                 logger.warning('Your SQLite database does not support foreign keys, so constraints will not be checked. Please upgrade.')
0336             elif not enabled_foreign_keys:
0337                 self.engine.execute('pragma foreign_keys = on')
0338
0339         else:
0340             self.engine = sqlalchemy.create_engine(url, max_identifier_length=30)
0341
0342         self._session = sqlalchemy.orm.scoped_session(sqlalchemy.orm.sessionmaker(bind=self.engine))
0343
0344         self._is_frontier = url.drivername == 'oracle+frontier'
0345         self._is_oracle = url.drivername == 'oracle'
0346         self._is_sqlite = url.drivername == 'sqlite'
0347
0348         self._is_read_only = self._is_frontier or url.host in {
0349             'cms_orcon_adg',
0350             'cmsarc_lb',
0351         }
0352
0353         self._is_official = self._is_frontier or url.host in {
0354             'cms_orcon_adg',
0355             'cmsarc_lb',
0356             'cms_orcoff_int',
0357             'cms_orcoff_prep',
0358             'cms_orcon_prod',
0359             'cmsintr_lb',
0360         }
0361         self._url = url
0362         self._backendName = ('sqlite' if self._is_sqlite else 'oracle' )
0363         self._schemaName = ( None if self._is_sqlite else schema_name )
0364         logging.debug('Loading db types...')
0365         self.get_dbtype(Tag).__name__
0366         self.get_dbtype(Payload)
0367         self.get_dbtype(IOV)
0368         self.get_dbtype(TagLog)
0369         self.get_dbtype(GlobalTag)
0370         self.get_dbtype(GlobalTagMap)
0371         self.get_dbtype(RunInfo)
0372         if not self._is_sqlite:
0373             self.get_dbtype(TagMetadata)
0374             self.get_dbtype(TagAuthorization)
0375             self.get_dbtype(BoostRunMap)
0376         self._is_valid = self.is_valid()
0377
0378     def get_dbtype(self,theType):
0379         basename = theType.__name__
0380         if self._backendName not in db_models.keys() or basename not in db_models[self._backendName].keys():
0381             return make_dbtype( self._backendName, self._schemaName, theType )
0382         else:
0383             return db_models[self._backendName][basename]
0384
0385     def session(self):
0386         s = self._session()
0387         s.get_dbtype = self.get_dbtype
0388         s._is_sqlite = self._is_sqlite
0389         s.is_oracle = self.is_oracle
0390         s._url = self._url
0391         return s
0392
0393     @property
0394     def metadata(self):
0395         return _Base.metadata
0396
0397     @property
0398     def is_frontier(self):
0399         return self._is_frontier
0400
0401     @property
0402     def is_oracle(self):
0403         return self._is_oracle
0404
0405     @property
0406     def is_sqlite(self):
0407         return self._is_sqlite
0408
0409     @property
0410     def is_read_only(self):
0411         return self._is_read_only
0412
0413     @property
0414     def is_official(self):
0415         return self._is_official
0416
0417     def is_valid(self):
0418         '''Tests whether the current DB looks like a valid CMS Conditions one.
0419         '''
0420         engine_connection = self.engine.connect()
0421         # temporarely avoid the check on the GT tables - there are releases in use where C++ does not create these tables.
0422         _Tag = self.get_dbtype(Tag)
0423         _IOV = self.get_dbtype(IOV)
0424         _Payload = self.get_dbtype(Payload)
0425         ret = all([self.engine.dialect.has_table(engine_connection, table.__tablename__,getSchema(table)) for table in [_Tag, _IOV, _Payload]])
0426         engine_connection.close()
0427         return ret
0428
0429     def init(self, drop=False):
0430         '''Initializes a database.
0431         '''
0432         logging.info('Initializing database...')
0433         if drop:
0434             logging.debug('Dropping tables...')
0435             self.metadata.drop_all(self.engine)
0436             self._is_valid = False
0437         else:
0438             if not self._is_valid:
0439                 logging.debug('Creating tables...')
0440                 self.get_dbtype(Tag).__table__.create(bind = self.engine)
0441                 self.get_dbtype(Payload).__table__.create(bind = self.engine)
0442                 self.get_dbtype(IOV).__table__.create(bind = self.engine)
0443                 self.get_dbtype(TagLog).__table__.create(bind = self.engine)
0444                 self.get_dbtype(GlobalTag).__table__.create(bind = self.engine)
0445                 self.get_dbtype(GlobalTagMap).__table__.create(bind = self.engine)
0446                 self._is_valid = True
0447
0448 def getSessionOnMasterDB( session1, session2 ):
0449     key = '%s/%s'
0450     sessiondict = { }
0451     sessiondict[key %(session1._url.drivername,session1._url.host)] = session1
0452     sessiondict[key %(session2._url.drivername,session2._url.host)] = session2
0453     masterkey = key %('oracle',ONLINEORAPRO)
0454     if masterkey in sessiondict.keys():
0455         return sessiondict[masterkey]
0456     adgkey = key %('oracle',ORAPRO)
0457     if adgkey in sessiondict.keys():
0458         return sessiondict[adgkey]
0459     frontierkey = key %('frontier',PRO)
0460     if frontierkey in sessiondict.keys():
0461         return sessiondict[frontierkey]
0462     # default case: frontier on pro
0463     conn = Connection(make_url())
0464     session = conn.session()
0465     # is it required?
0466     session._conn = conn
0467     return session
0468
0469 # Connection helpers
0470 def _getCMSFrontierConnectionString(database):
0471     import subprocess
0472     return subprocess.Popen(['cmsGetFnConnect', 'frontier://%s' % database], stdout = subprocess.PIPE).communicate()[0].strip()
0473
0474 def _getCMSSQLAlchemyConnectionString(technology,service,schema_name):
0475     if technology == 'frontier':
0476         import urllib
0477         import sys
0478         py3k = sys.version_info >= (3, 0)
0479         if py3k:
0480             return '%s://@%s/%s' % ('oracle+frontier', urllib.parse.quote_plus(_getCMSFrontierConnectionString(service)), schema_name )
0481         else:
0482             return '%s://@%s/%s' % ('oracle+frontier', urllib.quote_plus(_getCMSFrontierConnectionString(service)), schema_name )
0483     elif technology == 'oracle':
0484         return '%s://%s@%s' % (technology, schema_name, service)
0485
0486 # Entry point
0487 def make_url(database='pro',read_only = True):
0488     if database.startswith('sqlite:') or database.startswith('sqlite_file:'):
0489         ignore, database = database.split(':',1)
0490
0491     if ':' in database and '://' not in database: # check if we really got a shortcut like "pro:<schema>" (and not a url like proto://...), if so, disentangle
0492         database, schema = database.split(':')
0493
0494     officialdbs = {
0495         # frontier
0496         'pro' :         ('frontier','PromptProd',             { 'R': schema_name }, ),
0497         'arc' :         ('frontier','FrontierArc',            { 'R': schema_name }, ),
0498         'int' :         ('frontier','FrontierInt',            { 'R': schema_name }, ),
0499         'dev' :         ('frontier','FrontierPrep',           { 'R': schema_name }, ),
0500         # oracle adg
0501         'orapro':       ('oracle',         'cms_orcon_adg',   { 'R': dbreader_user_name }, ),
0502         'oraarc':       ('oracle',         'cmsarc_lb',       { 'R': dbreader_user_name }, ),
0503         # oracle masters
0504         'oraint':       ('oracle',         'cms_orcoff_int',  { 'R': dbreader_user_name,
0505                                                                 'W': dbwriter_user_name }, ),
0506         'oradev':       ('oracle',         'cms_orcoff_prep', { 'R': dbreader_user_name,
0507                                                                 'W': dbwriter_user_name }, ),
0508         'onlineorapro': ('oracle',         'cms_orcon_prod',  { 'R': dbreader_user_name,
0509                                                                 'W': dbwriter_user_name }, ),
0510         'onlineoraint': ('oracle',         'cmsintr_lb',      { 'R': dbreader_user_name,
0511                                                                 'W': dbwriter_user_name }, ),
0512     }
0513
0514     if database in officialdbs.keys():
0515         key = ('R' if read_only else 'W')
0516         mapping = officialdbs[database]
0517         tech = mapping[0]
0518         service = mapping[1]
0519         schema_dict = mapping[2]
0520         if key in schema_dict.keys():
0521             database = _getCMSSQLAlchemyConnectionString(tech,service,schema_dict[key])
0522         else:
0523             raise Exception("Read-only database %s://%s cannot be accessed in update mode." %(tech,service))
0524
0525     logging.debug('connection string set to "%s"' % database)
0526
0527     try:
0528         url = sqlalchemy.engine.url.make_url(database)
0529     except sqlalchemy.exc.ArgumentError:
0530         url = sqlalchemy.engine.url.make_url('sqlite:///%s' % database)
0531     return url
0532
0533 def connect(url, authPath=None, verbose=0, as_admin=False):
0534     '''Returns a Connection instance to the CMS Condition DB.
0535
0536     See database_help for the description of the database parameter.
0537
0538     The verbosity level is as follows:
0539
0540         0 = No output (default).
0541         1 = SQL statements issued, including their parameters.
0542         2 = In addition, results of the queries (all rows and the column headers).
0543     '''
0544
0545     check_admin = as_admin
0546     if url.drivername == 'oracle':
0547         if url.username is None:
0548             logging.error('Could not resolve the username for the connection %s. Please provide a connection in the format oracle://[user]:[pass]@[host]' %url )
0549             raise Exception('Connection format error: %s' %url )
0550         if url.password is None:
0551             if authPath is None:
0552                 if authPathEnvVar in os.environ:
0553                     authPath = os.environ[authPathEnvVar]
0554             explicit_auth = False
0555             if authPath is not None:
0556                 dbkey_path = os.path.join(authPath,dbkey_folder)
0557                 if not os.path.exists(dbkey_path):
0558                     authFile = os.path.join(authPath,'.netrc')
0559                     if os.path.exists(authFile):
0560                         entryKey = url.host.lower()+"/"+url.username.lower()
0561                         logging.debug('Looking up credentials for %s in file %s ' %(entryKey,authFile) )
0562                         import netrc
0563                         params = netrc.netrc( authFile ).authenticators(entryKey)
0564                         if params is not None:
0565                             (username, account, password) = params
0566                             url.username = username
0567                             url.password = password
0568                         else:
0569                             msg = 'The entry %s has not been found in the .netrc file.' %entryKey
0570                             raise TypeError(msg)
0571                     else:
0572                         explicit_auth =True
0573                 else:
0574                     import libCondDBPyBind11Interface as auth
0575                     role_code = auth.reader_role
0576                     if url.username == dbwriter_user_name:
0577                         role_code = auth.writer_role
0578                     if check_admin:
0579                         role_code = auth.admin_role
0580                     connection_string = oracle_connection_string(url.host.lower(),schema_name)
0581                     logging.debug('Using db key to get credentials for %s' %connection_string )
0582                     (dbuser,username,password) = auth.get_credentials_from_db(connection_string,role_code,authPath)
0583                     if username=='' or password=='':
0584                         raise Exception('No credentials found to connect on %s with the required access role.'%connection_string)
0585                     check_admin = False
0586                     url.username = username
0587                     url.password = password
0588             else:
0589                 import getpass
0590                 pwd = getpass.getpass('Password for %s: ' % str(url))
0591                 if pwd is None or pwd == '':
0592                     pwd = getpass.getpass('Password for %s: ' % str(url))
0593                     if pwd is None or pwd == '':
0594                         raise Exception('Empty password provided, bailing out...')
0595                 url.password = pwd
0596         if check_admin:
0597             raise Exception('Admin access has not been granted. Please provide a valid admin db-key.')
0598     if check_admin:
0599        raise Exception('Admin access is not available for technology "%s".' %url.drivername)
0600     if verbose >= 1:
0601         logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
0602
0603     if verbose >= 2:
0604         logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
0605
0606     return Connection(url)
0607
0608
0609 def _exists(session, primary_key, value):
0610     ret = None
0611     try:
0612         ret = session.query(primary_key).\
0613             filter(primary_key == value).\
0614             count() != 0
0615     except sqlalchemy.exc.OperationalError:
0616         pass
0617
0618     return ret
0619
0620 def _inserted_before(timestamp):
0621     '''To be used inside filter().
0622     '''
0623
0624     if timestamp is None:
0625         # XXX: Returning None does not get optimized (skipped) by SQLAlchemy,
0626         #      and returning True does not work in Oracle (generates "and 1"
0627         #      which breaks Oracle but not SQLite). For the moment just use
0628         #      this dummy condition.
0629         return sqlalchemy.literal(True) == sqlalchemy.literal(True)
0630
0631     return conddb.IOV.insertion_time <= _parse_timestamp(timestamp)
0632
0633 def listObject(session, name, snapshot=None):
0634
0635     is_tag = _exists(session, Tag.name, name)
0636     result = {}
0637     if is_tag:
0638         result['type'] = 'Tag'
0639         result['name'] = session.query(Tag).get(name).name
0640         result['timeType'] = session.query(Tag.time_type).\
0641                                      filter(Tag.name == name).\
0642                                      scalar()
0643
0644         result['iovs'] = session.query(IOV.since, IOV.insertion_time, IOV.payload_hash, Payload.object_type).\
0645                 join(IOV.payload).\
0646                 filter(
0647                     IOV.tag_name == name,
0648                     _inserted_before(snapshot),
0649                 ).\
0650                 order_by(IOV.since.desc(), IOV.insertion_time.desc()).\
0651                 from_self().\
0652                 order_by(IOV.since, IOV.insertion_time).\
0653                 all()
0654
0655     try:
0656         is_global_tag = _exists(session, GlobalTag.name, name)
0657         if is_global_tag:
0658             result['type'] = 'GlobalTag'
0659             result['name'] = session.query(GlobalTag).get(name)
0660             result['tags'] = session.query(GlobalTagMap.record, GlobalTagMap.label, GlobalTagMap.tag_name).\
0661                                      filter(GlobalTagMap.global_tag_name == name).\
0662                                      order_by(GlobalTagMap.record, GlobalTagMap.label).\
0663                                      all()
0664     except sqlalchemy.exc.OperationalError:
0665         sys.stderr.write("No table for GlobalTags found in DB.\n\n")
0666
0667     if not is_tag and not is_global_tag:
0668         raise Exception('There is no tag or global tag named %s in the database.' % name)
0669
0670     return result
0671
0672 def getPayload(session, hash):
0673     # get payload from DB:
0674     data, payloadType = session.query(Payload.data, Payload.object_type).filter(Payload.hash == hash).one()
0675     return data