1 '''CMS Conditions DB Python library. 4 __author__ =
'Miguel Ojeda' 5 __copyright__ =
'Copyright 2013, CERN' 6 __credits__ = [
'Giacomo Govi',
'Miguel Ojeda',
'Andreas Pfeiffer']
7 __license__ =
'Unknown' 8 __maintainer__ =
'Giacomo Govi' 9 __email__ =
'giacomo.govi@cern.ch' 17 import sqlalchemy.ext.declarative
19 from sqlalchemy
import Enum
21 schema_name =
'cms_conditions' 22 dbuser_name =
'cms_conditions' 23 dbreader_user_name =
'cms_cond_general_r' 24 dbwriter_user_name =
'cms_cond_general_w' 25 logger = logging.getLogger(__name__)
28 authPathEnvVar =
'COND_AUTH_PATH' 29 dbkey_filename =
'db.key' 30 dbkey_folder = os.path.join(
'.cms_cond',dbkey_filename)
38 ORAPRO =
'cms_orcon_adg' 41 ORAINT =
'cms_orcoff_int' 42 ORADEV =
'cms_orcoff_prep' 43 ONLINEORAPRO =
'cms_orcon_prod' 44 ONLINEORAINT =
'cmsintr_lb' 48 if logger.level == logging.NOTSET:
49 logger.setLevel(logging.WARN)
53 return hashlib.sha1(data.encode(
'ascii')).hexdigest()
60 description_length = 4000
61 hash_length = len(
hash(
''))
63 web_experts_email =
'cms-cond-dev@cern.ch' 64 offline_db_experts_email =
'cms-offlinedb-exp@cern.ch' 65 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' 67 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)
69 The database parameter (--db) refers to the database where the tool 70 will connect to read all the data. By default, the production account 71 (through Frontier) will be used. 73 In subcommands which take a source and a destination, --db always refers to 74 the source, and --destdb to the destination. For both of them the following 77 The database parameter can be an official alias, a filename or any 80 The official aliases are the following strings (first column): 82 Alias Level Database RO/RW Notes 83 ------------ ----------- ------------- ---------- ------------------------------- 85 pro Production Frontier (ADG) read-only Default. 86 arc Archive Frontier read-only 87 int Integration Frontier read-only 88 dev Development Frontier read-only 89 boost Production Frontier read-only 90 boostprep Development Frontier read-only 92 orapro Production Oracle (ADG) read-only Password required. 93 oraarc Archive Oracle read-only Password required. 94 oraint Integration Oracle read-write Password required. 95 oradev Development Oracle read-write Password required. 97 onlineorapro Production Oracle read-write Password required. Online only. 98 onlineoraint Online Int Oracle read-write Password required. Online only. 100 Most of the time, if you are a regular user, you will want to read/copy 101 conditions from the Frontier production account. Therefore, you can omit 102 the --db parameter, unless you want to read from somewhere else, 103 e.g. from your local SQLite file. 105 In addition, the parameter may be a filename (path) pointing to a local 109 relative/path/to/file.db 110 /absolute/path/to/file.db 112 Finally, any valid SQLAlchemy URL can be used. This allows full 113 flexibility in cases where it may be needed, e.g. 115 sqlite:// In-memory, volatile SQLite DB. 116 oracle://user@devdb11 Your private Oracle DB in devdb11 [*] 118 [*] See https://account.cern.ch/ -> Services for more information 119 on personal Oracle accounts. 121 For the official aliases, the password will be asked automatically 122 interactively. The same applies for Oracle URLs where the password 123 was not provided inside it, e.g.: 125 oracle://user@devdb11 The tool will prompt you for the password. 126 oracle://user:pass@devdb11 Password inlined. [+] 128 [+] Caution: Never write passwords in command-line parameters in 129 multi-user machines (e.g. lxplus), since other users can see them 130 in the process table (e.g. ps). 132 This means that both the official aliases and the filenames are shortcuts 133 to the full SQLAlchemy URL equivalents, e.g. the following are the same: 135 relative/path/to/file.db === sqlite:///relative/path/to/file.db 136 /absolute/path/to/file.db === sqlite:////absolute/path/to/file.db 140 return 'oracle://%s/%s'%(db_service,db_schema)
144 validation =
'validation' 153 synch_list = list(x.value
for x
in list(Synchronization))
164 _Base = sqlalchemy.ext.declarative.declarative_base()
168 if schema_name
is not None:
169 name =
'%s.%s' %(schema_name, table_name)
185 fqn =
'%s.%s' %(table, column)
186 if schema
is not None:
187 fqn =
'%s.%s' %(schema,fqn)
193 dbtype_name =
'%s_%s' %(baseType.__name__,backendName)
194 members[
'__tablename__'] = baseType.__tablename__
195 members[
'__table_args__'] =
None 196 if schemaName
is not None:
197 members[
'__table_args__'] = {
'schema': schemaName }
198 for k,v
in baseType.columns.items():
202 if isinstance(v[0],DbRef):
203 refColDbt = v[0].rtype.columns[v[0].rcol][0]
204 pk = (
True if v[1]==_Col.pk
else False)
206 members[k] = sqlalchemy.Column(refColDbt,sqlalchemy.ForeignKey(
fq_col(schemaName,v[0].rtype.__tablename__,v[0].rcol)),primary_key=
True)
208 nullable = (
False if v[1] == _Col.notNull
else True)
209 members[k] = sqlalchemy.Column(refColDbt,sqlalchemy.ForeignKey(
fq_col(schemaName,v[0].rtype.__tablename__,v[0].rcol)),nullable=nullable)
210 if v[0].rtype.__name__
not in deps_reg:
211 deps_reg.add(v[0].rtype.__name__)
212 reftype_name =
'%s_%s' %(v[0].rtype.__name__,backendName)
213 members[(v[0].rtype.__name__).lower()] = sqlalchemy.orm.relationship(reftype_name)
216 members[k] = sqlalchemy.Column(v[0],primary_key=
True)
218 nullable = (
True if v[1]==_Col.nullable
else False)
219 if defColVal
is None:
220 members[k] = sqlalchemy.Column(v[0],nullable=nullable)
222 members[k] = sqlalchemy.Column(v[0],nullable=nullable, default=defColVal)
223 dbType = type(dbtype_name,(_Base,),members)
225 if backendName
not in db_models.keys():
226 db_models[backendName] = {}
227 db_models[backendName][baseType.__name__] = dbType
231 if tp.__table_args__
is not None:
232 return tp.__table_args__[
'schema']
236 __tablename__ =
'TAG' 237 columns = {
'name': (sqlalchemy.String(name_length),_Col.pk),
238 'time_type': (sqlalchemy.Enum(*
tuple(TimeType.__members__.keys())),_Col.notNull),
239 'object_type': (sqlalchemy.String(name_length),_Col.notNull),
240 'synchronization': (sqlalchemy.Enum(*
tuple(Synchronization.__members__.keys())),_Col.notNull),
241 'description': (sqlalchemy.String(description_length),_Col.notNull),
242 'last_validated_time':(sqlalchemy.BIGINT,_Col.notNull),
243 'end_of_validity':(sqlalchemy.BIGINT,_Col.notNull),
244 'insertion_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
245 'modification_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
246 'protection_code':(sqlalchemy.Integer,_Col.notNull,0) }
249 __tablename__ =
'TAG_METADATA' 250 columns = {
'tag_name': (
DbRef(Tag,
'name'),_Col.pk),
251 'min_serialization_v': (sqlalchemy.String(20),_Col.notNull),
252 'min_since': (sqlalchemy.BIGINT,_Col.notNull),
253 'modification_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
256 __tablename__ =
'TAG_AUTHORIZATION' 257 columns = {
'tag_name': (
DbRef(Tag,
'name'),_Col.pk),
258 'access_type': (sqlalchemy.Integer,_Col.notNull),
259 'credential': (sqlalchemy.String(name_length),_Col.notNull),
260 'credential_type':(sqlalchemy.Integer,_Col.notNull) }
263 __tablename__ =
'PAYLOAD' 264 columns = {
'hash': (sqlalchemy.CHAR(hash_length),_Col.pk),
265 'object_type': (sqlalchemy.String(name_length),_Col.notNull),
266 'data': (sqlalchemy.BLOB,_Col.notNull),
267 'streamer_info':(sqlalchemy.BLOB,_Col.notNull),
268 'version':(sqlalchemy.String(20),_Col.notNull),
269 'insertion_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
273 __tablename__ =
'IOV' 274 columns = {
'tag_name':(
DbRef(Tag,
'name'),_Col.pk),
275 'since':(sqlalchemy.BIGINT,_Col.pk),
276 'insertion_time':(sqlalchemy.TIMESTAMP,_Col.pk),
277 'payload_hash':(
DbRef(Payload,
'hash'),_Col.notNull) }
281 __tablename__ =
'GLOBAL_TAG' 282 columns = {
'name':(sqlalchemy.String(name_length),_Col.pk),
283 'validity': (sqlalchemy.BIGINT,_Col.notNull),
284 'description':(sqlalchemy.String(description_length),_Col.notNull),
285 'release':(sqlalchemy.String(name_length),_Col.notNull),
286 'insertion_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
287 'snapshot_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
290 __tablename__ =
'GLOBAL_TAG_MAP' 291 columns = {
'global_tag_name':(
DbRef(GlobalTag,
'name'),_Col.pk),
292 'record':(sqlalchemy.String(name_length),_Col.pk),
293 'label':(sqlalchemy.String(name_length),_Col.pk),
294 'tag_name':(
DbRef(Tag,
'name'),_Col.notNull) }
299 __tablename__ =
'TAG_LOG' 300 columns = {
'tag_name':(
DbRef(Tag,
'name'),_Col.pk),
301 'event_time':(sqlalchemy.TIMESTAMP,_Col.pk),
302 'action':(sqlalchemy.String(100),_Col.pk),
303 'user_name':(sqlalchemy.String(100),_Col.notNull),
304 'host_name':(sqlalchemy.String(100),_Col.notNull),
305 'command':(sqlalchemy.String(500),_Col.notNull),
306 'user_text':(sqlalchemy.String(4000),_Col.notNull) }
309 __tablename__ =
'RUN_INFO' 310 columns = {
'run_number':(sqlalchemy.BIGINT,_Col.pk),
311 'start_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
312 'end_time':(sqlalchemy.TIMESTAMP,_Col.notNull) }
315 __tablename__ =
'BOOST_RUN_MAP' 316 columns = {
'run_number':(sqlalchemy.BIGINT,_Col.pk),
317 'run_start_time':(sqlalchemy.TIMESTAMP,_Col.notNull),
318 'boost_version': (sqlalchemy.String(20),_Col.notNull) }
328 if url.drivername ==
'sqlite':
330 self.
engine = sqlalchemy.create_engine(url)
332 enabled_foreign_keys = self.
engine.execute(
'pragma foreign_keys').scalar()
333 supports_foreign_keys = enabled_foreign_keys
is not None 334 if not supports_foreign_keys:
335 logger.warning(
'Your SQLite database does not support foreign keys, so constraints will not be checked. Please upgrade.')
336 elif not enabled_foreign_keys:
337 self.
engine.execute(
'pragma foreign_keys = on')
340 self.
engine = sqlalchemy.create_engine(url, max_identifier_length=30)
342 self.
_session = sqlalchemy.orm.scoped_session(sqlalchemy.orm.sessionmaker(bind=self.
engine))
364 logging.debug(
'Loading db types...')
379 basename = theType.__name__
395 return _Base.metadata
418 '''Tests whether the current DB looks like a valid CMS Conditions one. 425 ret =
all([self.
engine.dialect.has_table(engine_connection, table.__tablename__,
getSchema(table))
for table
in [_Tag, _IOV, _Payload]])
426 engine_connection.close()
430 '''Initializes a database. 432 logging.info(
'Initializing database...')
434 logging.debug(
'Dropping tables...')
439 logging.debug(
'Creating tables...')
451 sessiondict[key %(session1._url.drivername,session1._url.host)] = session1
452 sessiondict[key %(session2._url.drivername,session2._url.host)] = session2
453 masterkey = key %(
'oracle',ONLINEORAPRO)
454 if masterkey
in sessiondict.keys():
455 return sessiondict[masterkey]
456 adgkey = key %(
'oracle',ORAPRO)
457 if adgkey
in sessiondict.keys():
458 return sessiondict[adgkey]
459 frontierkey = key %(
'frontier',PRO)
460 if frontierkey
in sessiondict.keys():
461 return sessiondict[frontierkey]
464 session = conn.session()
472 return subprocess.Popen([
'cmsGetFnConnect',
'frontier://%s' % database], stdout = subprocess.PIPE).
communicate()[0].
strip()
475 if technology ==
'frontier':
478 py3k = sys.version_info >= (3, 0)
483 elif technology ==
'oracle':
484 return '%s://%s@%s' % (technology, schema_name, service)
488 if database.startswith(
'sqlite:')
or database.startswith(
'sqlite_file:'):
489 ignore, database = database.split(
':',1)
491 if ':' in database
and '://' not in database:
492 database, schema = database.split(
':')
496 'pro' : (
'frontier',
'PromptProd', {
'R': schema_name }, ), 497 'arc' : (
'frontier',
'FrontierArc', {
'R': schema_name }, ), 498 'int' : (
'frontier',
'FrontierInt', {
'R': schema_name }, ), 499 'dev' : (
'frontier',
'FrontierPrep', {
'R': schema_name }, ), 501 'orapro': (
'oracle',
'cms_orcon_adg', {
'R': dbreader_user_name }, ), 502 'oraarc': (
'oracle',
'cmsarc_lb', {
'R': dbreader_user_name }, ), 504 'oraint': (
'oracle',
'cms_orcoff_int', {
'R': dbreader_user_name, 505 'W': dbwriter_user_name }, ),
506 'oradev': (
'oracle',
'cms_orcoff_prep', {
'R': dbreader_user_name, 507 'W': dbwriter_user_name }, ),
508 'onlineorapro': (
'oracle',
'cms_orcon_prod', {
'R': dbreader_user_name, 509 'W': dbwriter_user_name }, ),
510 'onlineoraint': (
'oracle',
'cmsintr_lb', {
'R': dbreader_user_name, 511 'W': dbwriter_user_name }, ),
514 if database
in officialdbs.keys():
515 key = (
'R' if read_only else 'W')
516 mapping = officialdbs[database]
519 schema_dict = mapping[2]
520 if key
in schema_dict.keys():
523 raise Exception(
"Read-only database %s://%s cannot be accessed in update mode." %(tech,service))
525 logging.debug(
'connection string set to "%s"' % database)
528 url = sqlalchemy.engine.url.make_url(database)
529 except sqlalchemy.exc.ArgumentError:
530 url = sqlalchemy.engine.url.make_url(
'sqlite:///%s' % database)
533 def connect(url, authPath=None, verbose=0, as_admin=False):
534 '''Returns a Connection instance to the CMS Condition DB. 536 See database_help for the description of the database parameter. 538 The verbosity level is as follows: 540 0 = No output (default). 541 1 = SQL statements issued, including their parameters. 542 2 = In addition, results of the queries (all rows and the column headers). 545 check_admin = as_admin
546 if url.drivername ==
'oracle':
547 if url.username
is None:
548 logging.error(
'Could not resolve the username for the connection %s. Please provide a connection in the format oracle://[user]:[pass]@[host]' %url )
549 raise Exception(
'Connection format error: %s' %url )
550 if url.password
is None:
552 if authPathEnvVar
in os.environ:
553 authPath = os.environ[authPathEnvVar]
554 explicit_auth =
False 555 if authPath
is not None:
556 dbkey_path = os.path.join(authPath,dbkey_folder)
557 if not os.path.exists(dbkey_path):
558 authFile = os.path.join(authPath,
'.netrc')
559 if os.path.exists(authFile):
560 entryKey = url.host.lower()+
"/"+url.username.lower()
561 logging.debug(
'Looking up credentials for %s in file %s ' %(entryKey,authFile) )
563 params = netrc.netrc( authFile ).authenticators(entryKey)
564 if params
is not None:
565 (username, account, password) = params
566 url.username = username
567 url.password = password
569 msg =
'The entry %s has not been found in the .netrc file.' %entryKey
574 import libCondDBPyBind11Interface
as auth
575 role_code = auth.reader_role
576 if url.username == dbwriter_user_name:
577 role_code = auth.writer_role
579 role_code = auth.admin_role
581 logging.debug(
'Using db key to get credentials for %s' %connection_string )
582 (dbuser,username,password) = auth.get_credentials_from_db(connection_string,role_code,authPath)
583 if username==
'' or password==
'':
584 raise Exception(
'No credentials found to connect on %s with the required access role.'%connection_string)
586 url.username = username
587 url.password = password
590 pwd = getpass.getpass(
'Password for %s: ' %
str(url))
591 if pwd
is None or pwd ==
'':
592 pwd = getpass.getpass(
'Password for %s: ' %
str(url))
593 if pwd
is None or pwd ==
'':
594 raise Exception(
'Empty password provided, bailing out...')
597 raise Exception(
'Admin access has not been granted. Please provide a valid admin db-key.')
599 raise Exception(
'Admin access is not available for technology "%s".' %url.drivername)
601 logging.getLogger(
'sqlalchemy.engine').setLevel(logging.INFO)
604 logging.getLogger(
'sqlalchemy.engine').setLevel(logging.DEBUG)
612 ret = session.query(primary_key).\
613 filter(primary_key == value).\
615 except sqlalchemy.exc.OperationalError:
621 '''To be used inside filter(). 624 if timestamp
is None:
629 return sqlalchemy.literal(
True) == sqlalchemy.literal(
True)
631 return conddb.IOV.insertion_time <= _parse_timestamp(timestamp)
635 is_tag =
_exists(session, Tag.name, name)
638 result[
'type'] =
'Tag' 639 result[
'name'] = session.query(Tag).get(name).name
640 result[
'timeType'] = session.query(Tag.time_type).\
641 filter(Tag.name == name).\
644 result[
'iovs'] = session.query(IOV.since, IOV.insertion_time, IOV.payload_hash, Payload.object_type).\
647 IOV.tag_name == name,
650 order_by(IOV.since.desc(), IOV.insertion_time.desc()).\
652 order_by(IOV.since, IOV.insertion_time).\
656 is_global_tag =
_exists(session, GlobalTag.name, name)
658 result[
'type'] =
'GlobalTag' 659 result[
'name'] = session.query(GlobalTag).get(name)
660 result[
'tags'] = session.query(GlobalTagMap.record, GlobalTagMap.label, GlobalTagMap.tag_name).\
661 filter(GlobalTagMap.global_tag_name == name).\
662 order_by(GlobalTagMap.record, GlobalTagMap.label).\
664 except sqlalchemy.exc.OperationalError:
665 sys.stderr.write(
"No table for GlobalTags found in DB.\n\n")
667 if not is_tag
and not is_global_tag:
668 raise Exception(
'There is no tag or global tag named %s in the database.' % name)
674 data, payloadType = session.query(Payload.data, Payload.object_type).
filter(Payload.hash == hash).
one()
def init(self, drop=False)
def listObject(session, name, snapshot=None)
def getPayload(session, hash)
def getSessionOnMasterDB(session1, session2)
def _getCMSFrontierConnectionString(database)
static void * communicate(void *obj)
def make_url(database='pro', read_only=True)
def make_dbtype(backendName, schemaName, baseType)
def fq_col(schema, table, column)
def fq_name(schema_name, table_name)
static std::string join(char **cmd)
def _getCMSSQLAlchemyConnectionString(technology, service, schema_name)
def get_dbtype(self, theType)
def oracle_connection_string(db_service, db_schema)
def connect(url, authPath=None, verbose=0, as_admin=False)
def _inserted_before(timestamp)
def __init__(self, refType, refColumn)
def _exists(session, primary_key, value)