def __init__(self, logger=None, dbi=None, params=None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
tablespaceTable = ""
if params:
if "tablespace_table" in params:
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
self.create["rc_threshold"] = """
CREATE TABLE rc_threshold(
site_id INTEGER NOT NULL,
sub_type_id INTEGER NOT NULL,
pending_slots INTEGER NOT NULL,
max_slots INTEGER NOT NULL) %s""" % tablespaceTable
self.constraints["rc_threshold_fk1"] = \
"""ALTER TABLE rc_threshold ADD
(CONSTRAINT rc_threshold_fk1 FOREIGN KEY (site_id) REFERENCES wmbs_location(id) ON DELETE CASCADE)"""
self.constraints["rc_threshold_fk2"] = \
"""ALTER TABLE rc_threshold ADD
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
self.create[
'i_transfers'] = "CREATE INDEX TM_TASKNAME_IDX ON FILETRANSFERSDB (TM_TASKNAME)"
self.create[
'i_workers'] = "CREATE INDEX TM_WORKER_STATE ON FILETRANSFERSDB (TM_ASO_WORKER, TM_TRANSFER_STATE) COMPRESS 2"
self.create[
'i_publishers'] = "CREATE INDEX TM_WORKER_STATE ON FILETRANSFERSDB (TM_ASO_WORKER, TM_PUBLICATION_STATE) COMPRESS 2"
# //
# // Define create statements for each table
# //
# //
# tm_id - unique ID which is also the primary key (len 60 chars)
# tm_user - username (len 30 chars is ENOUGH)
# tm_workflow - taskName (len 255 chars)
# tm_group - group which is used inside cert
# tm_role - role which is used inside cert
self.create['b_transfers'] = """
def __init__(self, logger=None, dbi=None):
"""
_init_
Call the base class's constructor and add all necessary tables for
deletion,
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
orderedTables = Create.requiredTables[:]
orderedTables.reverse()
i = 0
for tableName in orderedTables:
i += 1
prefix = string.zfill(i, 2)
if tableName.endswith("_seq"):
self.create[prefix +
tableName] = "DROP SEQUENCE %s" % tableName
elif tableName.endswith("_trg"):
self.create[prefix + tableName] = "DROP TRIGGER %s" % tableName
else:
self.create[prefix + tableName] = "DROP TABLE %s" % tableName
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the base class's constructor and add all necessary tables for
deletion,
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
orderedTables = Create.requiredTables[:]
orderedTables.reverse()
i = 0
for tableName in orderedTables:
i += 1
prefix = string.zfill(i, 2)
if tableName.endswith("_seq"):
self.create[prefix + tableName] = "DROP SEQUENCE %s" % tableName
elif tableName.endswith("_trg"):
self.create[prefix + tableName] = "DROP TRIGGER %s" % tableName
else:
self.create[prefix + tableName] = "DROP TABLE %s" % tableName
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.delete['01bl_runjob'] = "DROP TABLE bl_runjob"
self.delete['02bl_status'] = "DROP TABLE bl_status"
j = 50
for i in Create.sequence_tables:
seqname = '%s_SEQ' % i
self.create["%s%s" % (j, seqname)] = \
"DROP SEQUENCE %s" % seqname
return
def __init__(self, logger=None, dbi=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.delete['01bl_runjob'] = "DROP TABLE bl_runjob"
self.delete['02bl_status'] = "DROP TABLE bl_status"
j = 50
for i in Create.sequence_tables:
seqname = '%s_SEQ' % i
self.create["%s%s" % (j, seqname)] = \
"DROP SEQUENCE %s" % seqname
return
def __init__(self, logger = None, dbi = None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
tablespaceTable = ""
tablespaceIndex = ""
if params:
if params.has_key("tablespace_table"):
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
if params.has_key("tablespace_index"):
tablespaceIndex = "USING INDEX TABLESPACE %s" % params["tablespace_index"]
self.create["rc_threshold"] = """
CREATE TABLE rc_threshold(
site_id INTEGER NOT NULL,
sub_type_id INTEGER NOT NULL,
max_slots INTEGER NOT NULL,
priority INTEGER DEFAULT 1) %s""" % tablespaceTable
self.constraints["rc_threshold_fk1"] = \
"""ALTER TABLE rc_threshold ADD
(CONSTRAINT rc_threshold_fk1 FOREIGN KEY (site_id) REFERENCES wmbs_location(id) ON DELETE CASCADE)"""
self.constraints["rc_threshold_fk2"] = \
"""ALTER TABLE rc_threshold ADD
def __init__(self, logger=None, dbi=None, params=None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['a_transaction'] = """
SET AUTOCOMMIT = 0; """
self.create['threadpool'] = """
CREATE TABLE tp_threadpool(
id int(11) NOT NULL auto_increment,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state enum('queued','process') default 'queued',
primary key(id)
) ENGINE=InnoDB;
"""
self.create['threadpool_buffer_in'] = """
CREATE TABLE tp_threadpool_buffer_in(
id int(11) NOT NULL auto_increment,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state enum('queued','process') default 'queued',
primary key(id)
) ENGINE=InnoDB;
"""
self.create['threadpool_buffer_out'] = """
def __init__(self, logger = None, dbi = None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['a_transaction'] = """
SET AUTOCOMMIT = 0; """
self.create['threadpool'] = """
CREATE TABLE tp_threadpool(
id int(11) NOT NULL auto_increment,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state enum('queued','process') default 'queued',
primary key(id)
) ENGINE=InnoDB;
"""
self.create['threadpool_buffer_in'] = """
CREATE TABLE tp_threadpool_buffer_in(
id int(11) NOT NULL auto_increment,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state enum('queued','process') default 'queued',
primary key(id)
) ENGINE=InnoDB;
"""
self.create['threadpool_buffer_out'] = """
def execute(self, conn=None, transaction=None):
"""
_execute_
"""
DBCreator.execute(self, conn, transaction)
return True
def execute(self, conn = None, transaction = None):
"""
_execute_
"""
DBCreator.execute(self, conn, transaction)
return True
def __init__(self, logger = None, dbi = None, params = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.requiredTables = ["01bl_status", "02bl_runjob"]
self.create['01bl_status'] = \
"""CREATE TABLE bl_status
(
id INT auto_increment,
name VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)
ENGINE = InnoDB DEFAULT CHARSET=latin1;
"""
self.create['02bl_runjob'] = \
"""CREATE TABLE bl_runjob
(
id INT auto_increment,
wmbs_id INT,
grid_id VARCHAR(255),
bulk_id VARCHAR(255),
status CHAR(1) DEFAULT '1',
sched_status INT,
retry_count INT,
status_time INT,
location INT,
user_id INT,
PRIMARY KEY (id),
FOREIGN KEY (wmbs_id) REFERENCES wmbs_job(id) ON DELETE CASCADE,
FOREIGN KEY (sched_status) REFERENCES bl_status(id),
FOREIGN KEY (user_id) REFERENCES wmbs_users(id) ON DELETE CASCADE,
FOREIGN KEY (location) REFERENCES wmbs_location(id) ON DELETE CASCADE,
UNIQUE (retry_count, wmbs_id)
)
ENGINE = InnoDB DEFAULT CHARSET=latin1;
"""
return
def __init__(self, logger = None, dbi = None, params = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.requiredTables = ["01bl_status", "02bl_runjob"]
self.create['01bl_status'] = \
"""CREATE TABLE bl_status
(
id INT auto_increment,
name VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)
ENGINE = InnoDB DEFAULT CHARSET=latin1;
"""
self.create['02bl_runjob'] = \
"""CREATE TABLE bl_runjob
(
id INT auto_increment,
wmbs_id INT,
grid_id VARCHAR(255),
bulk_id VARCHAR(255),
status CHAR(1) DEFAULT '1',
sched_status INT,
retry_count INT,
status_time INT,
location INT,
user_id INT,
PRIMARY KEY (id),
FOREIGN KEY (wmbs_id) REFERENCES wmbs_job(id) ON DELETE CASCADE,
FOREIGN KEY (sched_status) REFERENCES bl_status(id),
FOREIGN KEY (user_id) REFERENCES wmbs_users(id) ON DELETE CASCADE,
FOREIGN KEY (location) REFERENCES wmbs_location(id) ON DELETE CASCADE,
UNIQUE (retry_count, wmbs_id)
)
ENGINE = InnoDB DEFAULT CHARSET=latin1;
"""
return
def __init__(self):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.delete["02managed_feeders"] = "DROP TABLE managed_feeders"
self.delete["01managed_filesets"] = "DROP TABLE managed_filesets"
def __init__(self):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.delete["02managed_feeders"] = "DROP TABLE managed_feeders"
self.delete["01managed_filesets"] = "DROP TABLE managed_filesets"
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# Define create statements for each table
self.create['b_filemetadata'] = """
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# Define create statements for each table
self.create['b_filemetadata'] = """
def __init__(self):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['threadpool'] = """
CREATE TABLE tp_threadpool(
id integer PRIMARY KEY AUTOINCREMENT,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state varchard(20) NOT NULL default 'queued',
FOREIGN KEY(state) references tp_queued_process_enum(value)
)
"""
self.create['threadpool_buffer_in'] = """
CREATE TABLE tp_threadpool_buffer_in(
id integer PRIMARY KEY AUTOINCREMENT,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state varchard(20) NOT NULL default 'queued',
FOREIGN KEY(state) references tp_queued_process_enum(value)
)
"""
self.create['threadpool_buffer_out'] = """
CREATE TABLE tp_threadpool_buffer_out(
id integer PRIMARY KEY AUTOINCREMENT,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state varchard(20) NOT NULL default 'queued',
FOREIGN KEY(state) references tp_queued_process_enum(value)
)
"""
self.create['tp_queued_process_enum'] = """
CREATE TABLE tp_queued_process_enum (
value varchar(20) PRIMARY KEY NOT NULL
)"""
self.create['tp_queued_process_enum_insert1'] = """
INSERT INTO tp_queued_process_enum VALUES('queued')
"""
self.create['tp_queued_process_enum_insert2'] = """
def __init__(self):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.delete["02wm_managed_workflow_location"] = \
"DROP TABLE wm_managed_workflow_location"
self.delete["01wm_managed_workflow"] = "DROP TABLE wm_managed_workflow"
def __init__(self):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.delete["02wm_managed_workflow_location"] = \
"DROP TABLE wm_managed_workflow_location"
self.delete["01wm_managed_workflow"] = "DROP TABLE wm_managed_workflow"
def __init__(self):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['threadpool'] = """
CREATE TABLE tp_threadpool(
id integer PRIMARY KEY AUTOINCREMENT,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state varchard(20) NOT NULL default 'queued',
FOREIGN KEY(state) references tp_queued_process_enum(value)
)
"""
self.create['threadpool_buffer_in'] = """
CREATE TABLE tp_threadpool_buffer_in(
id integer PRIMARY KEY AUTOINCREMENT,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state varchard(20) NOT NULL default 'queued',
FOREIGN KEY(state) references tp_queued_process_enum(value)
)
"""
self.create['threadpool_buffer_out'] = """
CREATE TABLE tp_threadpool_buffer_out(
id integer PRIMARY KEY AUTOINCREMENT,
event varchar(255) NOT NULL,
component varchar(255) NOT NULL,
payload text NOT NULL,
thread_pool_id varchar(255) NOT NULL,
state varchard(20) NOT NULL default 'queued',
FOREIGN KEY(state) references tp_queued_process_enum(value)
)
"""
self.create['tp_queued_process_enum'] = """
CREATE TABLE tp_queued_process_enum (
value varchar(20) PRIMARY KEY NOT NULL
)"""
self.create['tp_queued_process_enum_insert1'] = """
INSERT INTO tp_queued_process_enum VALUES('queued')
"""
self.create['tp_queued_process_enum_insert2'] = """
def execute(self, conn=None, transaction=None):
"""
_execute_
Check to make sure that all required tables have been defined. If
everything is in place have the DBCreator make everything.
"""
for requiredTable in self.requiredTables:
if requiredTable not in self.create.keys():
raise WMException("The table '%s' is not defined." % requiredTable, "WMCORE-2")
DBCreator.execute(self, conn, transaction)
return True
def execute(self, conn=None, transaction=None):
"""
_execute_
Check to make sure that all required tables have been defined. If
everything is in place have the DBCreator make everything.
"""
for requiredTable in self.requiredTables:
if requiredTable not in self.create.keys():
raise WMException("The table '%s' is not defined." % \
requiredTable, "WMCORE-2")
DBCreator.execute(self, conn, transaction)
return True
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# //
# // Define create statements for each table
#//
# //255 chars for tm_task_status is even too much
self.create['b_tasks'] = """
def __init__(self, logger=None, dbi=None, params=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger is None:
logger = myThread.logger
if dbi is None:
dbi = myThread.dbi
tablespaceTable = ""
tablespaceIndex = ""
if params:
if "tablespace_table" in params:
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
if "tablespace_index" in params:
tablespaceIndex = "USING INDEX TABLESPACE %s" % params[
"tablespace_index"]
DBCreator.__init__(self, logger, dbi)
self.create["01wm_components"] = \
"""CREATE TABLE wm_components (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
pid INTEGER NOT NULL,
update_threshold INTEGER NOT NULL,
UNIQUE (name))"""
self.create["02wm_workers"] = \
"""CREATE TABLE wm_workers (
component_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
last_updated INTEGER NOT NULL,
state VARCHAR(255),
pid INTEGER,
poll_interval INTEGER NOT NULL,
last_error INTEGER,
cycle_time FLOAT DEFAULT 0 NOT NULL,
outcome VARCHAR(1000),
error_message VARCHAR(1000),
UNIQUE (name))"""
self.constraints["FK_wm_component_worker"] = \
"""ALTER TABLE wm_workers ADD CONSTRAINT FK_wm_component_worker
def __init__(self,logger=None, dbi=None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['taa'] = """
SET AUTOCOMMIT = 0; """
self.create['ta_managed_feeders'] = """
CREATE TABLE `managed_feeders` (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
feeder_type VARCHAR(256) NOT NULL,
feeder_state VARCHAR(256) NOT NULL,
insert_time INT(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
"""
self.create['ta_managed_filesets'] = """
def __init__(self,logger=None, dbi=None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['taa'] = """
SET AUTOCOMMIT = 0; """
self.create['ta_managed_feeders'] = """
CREATE TABLE `managed_feeders` (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
feeder_type VARCHAR(256) NOT NULL,
feeder_state VARCHAR(256) NOT NULL,
insert_time INT(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
"""
self.create['ta_managed_filesets'] = """
def __init__(self, logger = None, dbi = None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create["rc_threshold"] = """
CREATE TABLE rc_threshold(
site_id INTEGER NOT NULL,
sub_type_id INTEGER NOT NULL,
max_slots INTEGER NOT NULL,
priority INTEGER DEFAULT 1,
FOREIGN KEY (site_id) REFERENCES wmbs_location(id) ON DELETE CASCADE,
FOREIGN KEY (sub_type_id) REFERENCES wmbs_sub_types(id) ON DELETE CASCADE)"""
return
def __init__(self, logger=None, dbi=None, params=None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create["rc_threshold"] = """
CREATE TABLE rc_threshold(
site_id INTEGER NOT NULL,
sub_type_id INTEGER NOT NULL,
pending_slots INTEGER NOT NULL,
max_slots INTEGER NOT NULL,
FOREIGN KEY (site_id) REFERENCES wmbs_location(id) ON DELETE CASCADE,
FOREIGN KEY (sub_type_id) REFERENCES wmbs_sub_types(id) ON DELETE CASCADE)"""
return
def __init__(self, logger = None, dbi = None, params = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
tablespaceTable = ""
tablespaceIndex = ""
if params:
if "tablespace_table" in params:
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
if "tablespace_index" in params:
tablespaceIndex = "USING INDEX TABLESPACE %s" % params["tablespace_index"]
DBCreator.__init__(self, logger, dbi)
self.create["01wm_components"] = \
"""CREATE TABLE wm_components (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
pid INTEGER NOT NULL,
update_threshold INTEGER NOT NULL,
UNIQUE (name))"""
self.create["02wm_workers"] = \
"""CREATE TABLE wm_workers (
component_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
last_updated INTEGER NOT NULL,
state VARCHAR(255),
pid INTEGER,
poll_interval INTEGER NOT NULL,
last_error INTEGER,
cycle_time DECIMAL(9,4) DEFAULT 0 NOT NULL,
outcome VARCHAR(1000),
error_message VARCHAR(1000),
UNIQUE (component_id, name))"""
self.constraints["FK_wm_component_worker"] = \
"""ALTER TABLE wm_workers ADD CONSTRAINT FK_wm_component_worker
def __init__(self, logger = None, dbi = None, params = None):
"""
__init__
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.delete["01test_tablea"] = "DROP TABLE test_tablea"
self.delete["02test_tableb"] = "DROP TABLE test_tableb"
self.delete["03test_tablec"] = "DROP TABLE test_tablec"
self.delete["04test_bigcol"] = "DROP TABLE test_bigcol"
return
def __init__(self, logger=None, dbi=None, params=None):
"""
__init__
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.delete["01test_tablea"] = "DROP TABLE test_tablea"
self.delete["02test_tableb"] = "DROP TABLE test_tableb"
self.delete["03test_tablec"] = "DROP TABLE test_tablec"
self.delete["04test_bigcol"] = "DROP TABLE test_bigcol"
return
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.delete = {}
self.delete["03tp_threadpool"] = "DROP TABLE tp_threadpool"
self.delete["02tp_threadpool_buffer_in"] = "DROP TABLE tp_threadpool_buffer_in"
self.delete["01tp_threadpool_buffer_out"] = "DROP TABLE tp_threadpool_buffer_out"
def __init__(self, logger=None, dbi=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.delete = {}
self.delete["rc_threshold"] = "DROP TABLE rc_threshold"
return
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# //
# // Define create statements for each table
# //
# //
# tm_id - unique ID which is also the primary key (len 60 chars)
# tm_user - username (len 30 chars is ENOUGH)
# tm_workflow - taskName (len 255 chars)
# tm_group - group which is used inside cert
# tm_role - role which is used inside cert
self.create['b_transfers'] = """
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.delete = {}
self.delete["rc_threshold"] = "DROP TABLE rc_threshold"
return
def __init__(self, logger=None, dbi=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create['01bl_runjob'] = "DROP TABLE bl_runjob"
self.create['02bl_status'] = "DROP TABLE bl_status"
self.requiredTables = []
return
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
orderedTables = Create.requiredTables[:]
orderedTables.reverse()
i = 0
for tableName in orderedTables:
i += 1
prefix = string.zfill(i, 2)
self.create[prefix + tableName] = "DROP TABLE %s" % tableName
def __init__(self,logger=None, dbi=None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['taa'] = """
SET AUTOCOMMIT = 0; """
self.create['ta_wm_managed_workflow'] = """
CREATE TABLE `wm_managed_workflow` (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
workflow INT(11) NOT NULL,
fileset_match VARCHAR(256) NOT NULL,
split_algo VARCHAR(256) NOT NULL,
type VARCHAR(256) NOT NULL,
UNIQUE (workflow, fileset_match),
FOREIGN KEY(workflow) REFERENCES wmbs_workflow(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
"""
self.create['ta_wm_managed_workflow_location'] = """
def __init__(self):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.delete["11dbsbuffer_block"] = "DROP TABLE dbsbuffer_block"
self.delete["10dbsbuffer_dataset"] = "DROP TABLE dbsbuffer_dataset"
self.delete["09dbsbuffer_algo"] = "DROP TABLE dbsbuffer_algo"
self.delete["08dbsbuffer_algo_dataset_assoc"] = "DROP TABLE dbsbuffer_algo_dataset_assoc"
self.delete["07dbsbuffer_file"] = "DROP TABLE dbsbuffer_file"
self.delete["06dbsbuffer_file_parent"] = "DROP TABLE dbsbuffer_file_parent"
self.delete["05dbsbuffer_file_runlumi_map"] = "DROP TABLE dbsbuffer_file_runlumi_map"
self.delete["04dbsbuffer_location"] = "DROP TABLE dbsbuffer_location"
self.delete["03dbsbuffer_file_location"] = "DROP TABLE dbsbuffer_file_location"
self.delete["02dbsbuffer_checksum_type"] = "DROP TABLE dbsbuffer_checksum_type"
self.delete["01dbsbuffer_file_checksums"] = "DROP TABLE dbsbuffer_file_checksums"
def __init__(self, logger=None, dbi=None, params=None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
self.create['taa'] = """
SET AUTOCOMMIT = 0; """
self.create['ta_wm_managed_workflow'] = """
CREATE TABLE `wm_managed_workflow` (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
workflow INT(11) NOT NULL,
fileset_match VARCHAR(256) NOT NULL,
split_algo VARCHAR(256) NOT NULL,
type VARCHAR(256) NOT NULL,
UNIQUE (workflow, fileset_match),
FOREIGN KEY(workflow) REFERENCES wmbs_workflow(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
"""
self.create['ta_wm_managed_workflow_location'] = """
def __init__(self, logger=None, dbi=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
orderedTables = Create.requiredTables[:]
orderedTables.reverse()
i = 0
for tableName in orderedTables:
i += 1
prefix = string.zfill(i, 2)
self.create[prefix + tableName] = "DROP TABLE %s" % tableName
def __init__(self, logger=None, dbi=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.delete = {}
self.delete["03tp_threadpool"] = "DROP TABLE tp_threadpool"
self.delete[
"02tp_threadpool_buffer_in"] = "DROP TABLE tp_threadpool_buffer_in"
self.delete[
"01tp_threadpool_buffer_out"] = "DROP TABLE tp_threadpool_buffer_out"
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.delete["22wmbs_fileset"] = "DROP TABLE wmbs_fileset"
self.delete["21wmbs_file_details"] = "DROP TABLE wmbs_file_details"
self.delete["20wmbs_fileset_files"] = "DROP TABLE wmbs_fileset_files"
self.delete["19wmbs_file_parent"] = "DROP TABLE wmbs_file_parent"
self.delete["18wmbs_file_runlumi_map"] = "DROP TABLE wmbs_file_runlumi_map"
self.delete["17wmbs_location"] = "DROP TABLE wmbs_location"
self.delete["16wmbs_file_location"] = "DROP TABLE wmbs_file_location"
self.delete["15wmbs_users"] = "DROP TABLE wmbs_users"
self.delete["14wmbs_workflow"] = "DROP TABLE wmbs_workflow"
self.delete["13wmbs_workflow_output"] = "DROP TABLE wmbs_workflow_output"
self.delete["12wmbs_sub_types"] = "DROP TABLE wmbs_sub_types"
self.delete["11wmbs_subscription"] = "DROP TABLE wmbs_subscription"
self.delete["10wmbs_subscription_validation"] = "DROP TABLE wmbs_subscription_validation"
self.delete["10wmbs_sub_files_acquired"] = "DROP TABLE wmbs_sub_files_acquired"
self.delete["10wmbs_sub_files_available"] = "DROP TABLE wmbs_sub_files_available"
self.delete["09wmbs_sub_files_failed"] = "DROP TABLE wmbs_sub_files_failed"
self.delete["08wmbs_sub_files_complete"] = "DROP TABLE wmbs_sub_files_complete"
self.delete["07wmbs_jobgroup"] = "DROP TABLE wmbs_jobgroup"
self.delete["06wmbs_job_state"] = "DROP TABLE wmbs_job_state"
self.delete["05wmbs_job"] = "DROP TABLE wmbs_job"
self.delete["04wmbs_job_assoc"] = "DROP TABLE wmbs_job_assoc"
self.delete["03wmbs_job_mask"] = "DROP TABLE wmbs_job_mask"
self.delete["02wmbs_job_mask"] = "DROP TABLE wmbs_checksum_type"
self.delete["01wmbs_job_mask"] = "DROP TABLE wmbs_file_checksums"
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
self.create['i_transfers'] = "CREATE INDEX TM_TASKNAME_IDX ON FILETRANSFERSDB (TM_TASKNAME)"
self.create['i_workers'] = "CREATE INDEX TM_WORKER_STATE ON FILETRANSFERSDB (TM_ASO_WORKER, TM_TRANSFER_STATE) COMPRESS 2"
# //
# // Define create statements for each table
# //
# //
# tm_id - unique ID which is also the primary key (len 60 chars)
# tm_user - username (len 30 chars is ENOUGH)
# tm_workflow - taskName (len 255 chars)
# tm_group - group which is used inside cert
# tm_role - role which is used inside cert
self.create['b_transfers'] = """
def __init__(self, logger = None, dbi = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create['01bl_runjob'] = "DROP TABLE bl_runjob"
self.create['02bl_status'] = "DROP TABLE bl_status"
self.requiredTables = []
return
def __init__(self, logger=None, dbi=None, params=None):
"""
__init__
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create["01test_tablea"] = \
"""CREATE TABLE test_tablea (
column1 INTEGER,
column2 INTEGER,
column3 VARCHAR(255))"""
self.create["01test_tableb"] = \
"""CREATE TABLE test_tableb (
column1 VARCHAR(255),
column2 INTEGER,
column3 VARCHAR(255))"""
self.create["01test_tablec"] = \
"""CREATE TABLE test_tablec (
column1 VARCHAR(255),
column2 VARCHAR(255),
column3 VARCHAR(255))"""
self.create["01test_bigcol"] = \
"""CREATE TABLE test_bigcol (
column1 DEC(35))"""
return
def __init__(self, logger = None, dbi = None, params = None):
"""
__init__
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
self.create["01test_tablea"] = \
"""CREATE TABLE test_tablea (
column1 INTEGER,
column2 INTEGER,
column3 VARCHAR(255))"""
self.create["01test_tableb"] = \
"""CREATE TABLE test_tableb (
column1 VARCHAR(255),
column2 INTEGER,
column3 VARCHAR(255))"""
self.create["01test_tablec"] = \
"""CREATE TABLE test_tablec (
column1 VARCHAR(255),
column2 VARCHAR(255),
column3 VARCHAR(255))"""
self.create["01test_bigcol"] = \
"""CREATE TABLE test_bigcol (
column1 DEC(35))"""
return
def __init__(self, logger = None, dbi = None, params = None):
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create = {}
self.constraints = {}
tablespaceTable = ""
tablespaceIndex = ""
if params:
if "tablespace_table" in params:
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
if "tablespace_index" in params:
tablespaceIndex = "USING INDEX TABLESPACE %s" % params["tablespace_index"]
self.create["tp_threadpool"] = \
"""CREATE TABLE tp_threadpool (
id NUMBER(11) NOT NULL ENABLE,
event VARCHAR2(255) NOT NULL ENABLE,
component VARCHAR2(255) NOT NULL ENABLE,
payload CLOB NOT NULL ENABLE,
thread_pool_id VARCHAR2(255) NOT NULL ENABLE,
state VARCHAR(20) DEFAULT 'queued' NOT NULL ENABLE
) %s""" % tablespaceTable
self.create["tp_threadpool_seq"] = \
"""CREATE SEQUENCE tp_threadpool_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.indexes["tp_threadpool_pk"] = \
"""ALTER TABLE tp_threadpool ADD
(CONSTRAINT tp_threadpool_pk PRIMARY KEY (id) %s)""" % tablespaceIndex
self.constraints["tp_threadpool_fk"] = \
"""ALTER TABLE tp_threadpool ADD
(CONSTRAINT tp_threadpool_state CHECK(state IN ('queued', 'process')))"""
self.create["tp_threadpool_trg"] = \
"""CREATE TRIGGER tp_threadpool_trg
BEFORE INSERT ON tp_threadpool
FOR EACH ROW
DECLARE m_no INTEGER;
BEGIN
SELECT tp_threadpool_seq.nextval INTO :new.id FROM dual;
END;"""
self.create["tp_threadpool_buffer_in"] = \
"""CREATE TABLE tp_threadpool_buffer_in (
id NUMBER(11) NOT NULL ENABLE,
event VARCHAR(255) NOT NULL ENABLE,
component VARCHAR(255) NOT NULL ENABLE,
payload CLOB NOT NULL ENABLE,
thread_pool_id VARCHAR(255) NOT NULL ENABLE,
state VARCHAR2(20) DEFAULT 'queued' NOT NULL ENABLE
) %s""" % tablespaceTable
self.create["tp_threadpool_buffer_in_seq"] = \
"""CREATE SEQUENCE tp_buffer_in_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create["tp_threadpool_buffer_in_trg"] = \
"""CREATE TRIGGER tp_buffer_in_trg
BEFORE INSERT ON tp_threadpool_buffer_in
FOR EACH ROW
BEGIN
SELECT tp_buffer_in_seq.nextval INTO :new.id FROM dual;
END;"""
self.constraints["tp_threadpool_buffer_in_fk"] = \
"""ALTER TABLE tp_threadpool_buffer_in ADD
(CONSTRAINT tp_threadpool_buffer_in_state CHECK(state IN ('queued', 'process')))"""
self.indexes["tp_threadpool_buffer_in_pk"] = \
"""ALTER TABLE tp_threadpool_buffer_in ADD
(CONSTRAINT tp_threadpool_buffer_in_pk PRIMARY KEY (id) %s)""" % tablespaceIndex
self.create["tp_threadpool_buffer_out"] = \
"""CREATE TABLE tp_threadpool_buffer_out (
id NUMBER(11) NOT NULL ENABLE,
event varchar(255) NOT NULL ENABLE,
component varchar(255) NOT NULL ENABLE,
payload clob NOT NULL ENABLE,
thread_pool_id varchar(255) NOT NULL ENABLE,
state varchar2(20) DEFAULT 'queued' NOT NULL ENABLE
) %s""" % tablespaceTable
self.create["threadpool_buffer_out_seq"] = \
"""CREATE SEQUENCE tp_buffer_out_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create["tp_threadpool_buffer_out_trg"] = \
"""CREATE TRIGGER tp_buffer_out_trg
BEFORE INSERT ON tp_threadpool_buffer_out
FOR EACH ROW
BEGIN
SELECT tp_buffer_out_seq.nextval INTO :new.id FROM dual;
END;"""
self.indexes["tp_threadpool_buffer_out_pk"] = \
"""ALTER TABLE tp_threadpool_buffer_out ADD
(CONSTRAINT tp_threadpool_buffer_out_pk PRIMARY KEY (id) %s)""" % tablespaceIndex
self.constraints["tp_threadpool_buffer_out_ck"] = \
"""ALTER TABLE tp_threadpool_buffer_out ADD
def __init__(self, logger = None, dbi = None, params = None):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
self.create["01dbsbuffer_dataset"] = \
"""CREATE TABLE dbsbuffer_dataset
(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
path VARCHAR(500) COLLATE latin1_general_cs UNIQUE NOT NULL,
processing_ver VARCHAR(255),
acquisition_era VARCHAR(255),
valid_status VARCHAR(20),
global_tag VARCHAR(255),
parent VARCHAR(500),
custodial_site VARCHAR(255),
subscribed int default 0,
primary key(id)
) ENGINE=InnoDB"""
self.create["02dbsbuffer_algo"] = \
"""CREATE TABLE dbsbuffer_algo
(
id BIGINT UNSIGNED not null auto_increment,
app_name varchar(100),
app_ver varchar(100),
app_fam varchar(100),
pset_hash varchar(700),
config_content LONGTEXT,
in_dbs int,
primary key(ID),
unique (app_name, app_ver, app_fam, pset_hash)
) ENGINE=InnoDB"""
self.create["03dbsbuffer_algo_dataset_assoc"] = \
"""CREATE TABLE dbsbuffer_algo_dataset_assoc
(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
algo_id BIGINT UNSIGNED,
dataset_id BIGINT UNSIGNED,
in_dbs INTEGER DEFAULT 0,
primary key(id),
FOREIGN KEY (algo_id) REFERENCES dbsbuffer_algo(id)
ON DELETE CASCADE,
FOREIGN KEY (dataset_id) REFERENCES dbsbuffer_dataset(id)
ON DELETE CASCADE
) ENGINE = InnoDB"""
self.create["03dbsbuffer_workflow"] = \
"""CREATE TABLE dbsbuffer_workflow (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
task VARCHAR(255),
spec VARCHAR(255),
UNIQUE(name, task)) ENGINE = InnoDB"""
self.create["04dbsbuffer_file"] = \
"""CREATE TABLE dbsbuffer_file (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
lfn VARCHAR(500) NOT NULL,
filesize BIGINT,
events INTEGER,
dataset_algo BIGINT UNSIGNED not null,
block_id BIGINT UNSIGNED,
status varchar(20),
in_phedex INTEGER DEFAULT 0,
workflow INTEGER,
LastModificationDate BIGINT,
FOREIGN KEY (workflow) references dbsbuffer_workflow(id)
ON DELETE CASCADE,
UNIQUE(lfn)) ENGINE=InnoDB"""
self.create["06dbsbuffer_file_parent"] = \
"""CREATE TABLE dbsbuffer_file_parent (
child INTEGER NOT NULL,
parent INTEGER NOT NULL,
FOREIGN KEY (child) references dbsbuffer_file(id)
ON DELETE CASCADE,
FOREIGN KEY (parent) references dbsbuffer_file(id),
UNIQUE(child, parent))ENGINE=InnoDB"""
self.create["07dbsbuffer_file_runlumi_map"] = \
"""CREATE TABLE dbsbuffer_file_runlumi_map (
filename INTEGER NOT NULL,
run INTEGER NOT NULL,
lumi INTEGER NOT NULL,
FOREIGN KEY (filename) references dbsbuffer_file(id)
ON DELETE CASCADE)ENGINE=InnoDB"""
self.create["08dbsbuffer_location"] = \
"""CREATE TABLE dbsbuffer_location (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
se_name VARCHAR(255) NOT NULL,
UNIQUE(se_name))ENGINE=InnoDB"""
self.create["09dbsbuffer_file_location"] = \
"""CREATE TABLE dbsbuffer_file_location (
filename INTEGER NOT NULL,
location INTEGER NOT NULL,
UNIQUE(filename, location)) ENGINE=InnoDB"""
self.create["10dbsbuffer_block"] = \
"""CREATE TABLE dbsbuffer_block (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
blockname VARCHAR(250) NOT NULL,
location INTEGER NOT NULL,
create_time INTEGER,
status VARCHAR(20),
UNIQUE(blockname, location))ENGINE=InnoDB"""
self.create["11dbsbuffer_checksum_type"] = \
"""CREATE TABLE dbsbuffer_checksum_type (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(255) ) ENGINE=InnoDB"""
self.create["12dbsbuffer_file_checksums"] = \
"""CREATE TABLE dbsbuffer_file_checksums (
fileid INTEGER,
typeid INTEGER,
cksum VARCHAR(100),
UNIQUE (fileid, typeid),
FOREIGN KEY (typeid) REFERENCES dbsbuffer_checksum_type(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES dbsbuffer_file(id)
ON DELETE CASCADE) ENGINE=InnoDB"""
checksumTypes = ['cksum', 'adler32', 'md5']
for i in checksumTypes:
checksumTypeQuery = """INSERT INTO dbsbuffer_checksum_type (type) VALUES ('%s')
""" % (i)
self.inserts["wmbs_checksum_type_%s" % (i)] = checksumTypeQuery
def __init__(self, logger=None, dbi=None, params=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger is None:
logger = myThread.logger
if dbi is None:
dbi = myThread.dbi
tablespaceIndex = ""
if params:
if "tablespace_index" in params:
tablespaceIndex = "USING INDEX TABLESPACE %s" % params[
"tablespace_index"]
DBCreator.__init__(self, logger, dbi)
self.requiredTables = ["01bl_status", "02bl_runjob"]
self.create['01bl_status'] = \
"""CREATE TABLE bl_status
(
id INT auto_increment,
name VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)
ENGINE = InnoDB DEFAULT CHARSET=latin1;
"""
self.create['02bl_runjob'] = \
"""CREATE TABLE bl_runjob
(
id INT auto_increment,
wmbs_id INT,
grid_id VARCHAR(255),
bulk_id VARCHAR(255),
status CHAR(1) DEFAULT '1',
sched_status INT NOT NULL,
retry_count INT,
status_time INT,
location INT,
user_id INT,
PRIMARY KEY (id),
FOREIGN KEY (wmbs_id) REFERENCES wmbs_job(id) ON DELETE CASCADE,
FOREIGN KEY (sched_status) REFERENCES bl_status(id),
FOREIGN KEY (user_id) REFERENCES wmbs_users(id) ON DELETE CASCADE,
FOREIGN KEY (location) REFERENCES wmbs_location(id) ON DELETE CASCADE,
UNIQUE (retry_count, wmbs_id)
)
ENGINE = InnoDB DEFAULT CHARSET=latin1;
"""
self.constraints["01_idx_bl_runjob"] = \
"""CREATE INDEX idx_bl_runjob_wmbs ON bl_runjob(wmbs_id) %s""" % tablespaceIndex
self.constraints["02_idx_bl_runjob"] = \
"""CREATE INDEX idx_bl_runjob_status ON bl_runjob(sched_status) %s""" % tablespaceIndex
self.constraints["03_idx_bl_runjob"] = \
"""CREATE INDEX idx_bl_runjob_users ON bl_runjob(user_id) %s""" % tablespaceIndex
self.constraints["04_idx_bl_runjob"] = \
"""CREATE INDEX idx_bl_runjob_location ON bl_runjob(location) %s""" % tablespaceIndex
return
def __init__(self, logger=None, dbi=None, params=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
tablespaceTable = ""
tablespaceIndex = ""
self.create = {}
self.constraints = {}
self.indexes = {}
if params:
if params.has_key("tablespace_table"):
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
if params.has_key("tablespace_index"):
tablespaceIndex = "USING INDEX TABLESPACE %s" % params[
"tablespace_index"]
self.requiredTables = ["01bl_status", "02bl_runjob"]
self.sequence_tables = []
self.sequence_tables.append("bl_status")
self.sequence_tables.append("bl_runjob")
self.create['01bl_status'] = \
"""CREATE TABLE bl_status (
id INTEGER NOT NULL,
name VARCHAR(255)
) %s """ % (tablespaceTable)
self.create['02bl_runjob'] = \
"""CREATE TABLE bl_runjob
(
id INTEGER NOT NULL,
wmbs_id INTEGER,
grid_id VARCHAR(255),
bulk_id VARCHAR(255),
status CHAR(1) DEFAULT '1',
sched_status INTEGER,
retry_count INTEGER,
status_time INTEGER,
location INTEGER,
user_id INTEGER
) %s """ % (tablespaceTable)
self.indexes["02_pk_bl_runjob"] = \
"""ALTER TABLE bl_runjob ADD
(CONSTRAINT bl_runjob_pk PRIMARY KEY (id) %s)""" % tablespaceIndex
self.indexes["01_pk_bl_status"] = \
"""ALTER TABLE bl_status ADD
(CONSTRAINT bl_status_pk PRIMARY KEY (id) %s)""" % tablespaceIndex
self.constraints["01_fk_bl_runjob"] = \
"""ALTER TABLE bl_runjob ADD
(CONSTRAINT bl_runjob_fk1 FOREIGN KEY(wmbs_id)
REFERENCES wmbs_job(id) ON DELETE CASCADE)"""
self.constraints["02_fk_bl_runjob"] = \
"""ALTER TABLE bl_runjob ADD
(CONSTRAINT bl_runjob_fk2 FOREIGN KEY(sched_status)
REFERENCES bl_status(id) ON DELETE CASCADE)"""
self.constraints["03_fk_bl_runjob"] = \
"""ALTER TABLE bl_runjob ADD
(CONSTRAINT bl_runjob_fk3 FOREIGN KEY(user_id)
REFERENCES wmbs_users(id) ON DELETE CASCADE)"""
self.constraints["04_fk_bl_runjob"] = \
"""ALTER TABLE bl_runjob ADD
(CONSTRAINT bl_runjob_fk4 FOREIGN KEY(location)
REFERENCES wmbs_location(id) ON DELETE CASCADE)"""
j = 50
for i in self.sequence_tables:
seqname = '%s_SEQ' % i
self.create["%s%s" % (j, seqname)] = \
"CREATE SEQUENCE %s start with 1 increment by 1 nomaxvalue cache 100" \
% seqname
return
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# //
# // Define create statements for each table
#//
# //
self.create['b_tasks'] = """
CREATE TABLE tasks(
tm_taskname VARCHAR(255) NOT NULL,
tm_activity VARCHAR(255),
panda_jobset_id NUMBER(11),
tm_task_status VARCHAR(255) NOT NULL,
tm_start_time TIMESTAMP,
tm_start_injection TIMESTAMP,
tm_end_injection TIMESTAMP,
tm_task_failure CLOB,
tm_job_sw VARCHAR(255) NOT NULL,
tm_job_arch VARCHAR(255),
tm_input_dataset VARCHAR(500),
tm_use_parent NUMBER(1),
tm_site_whitelist VARCHAR(4000),
tm_site_blacklist VARCHAR(4000),
tm_split_algo VARCHAR(255) NOT NULL,
tm_split_args CLOB NOT NULL,
tm_totalunits NUMBER(38),
tm_user_sandbox VARCHAR(255) NOT NULL,
tm_cache_url VARCHAR(255) NOT NULL,
tm_username VARCHAR(255) NOT NULL,
tm_user_dn VARCHAR(255) NOT NULL,
tm_user_vo VARCHAR(255) NOT NULL,
tm_user_role VARCHAR(255),
tm_user_group VARCHAR(255),
tm_publish_name VARCHAR(500),
tm_asyncdest VARCHAR(255) NOT NULL,
tm_dbs_url VARCHAR(255) NOT NULL,
tm_publish_dbs_url VARCHAR(255),
tm_publication VARCHAR(1) NOT NULL,
tm_outfiles CLOB,
tm_tfile_outfiles CLOB,
tm_edm_outfiles CLOB,
tm_transformation VARCHAR(255) NOT NULL,
tm_job_type VARCHAR(255) NOT NULL,
tm_generator VARCHAR(255),
tm_events_per_lumi NUMBER(38),
tm_arguments CLOB,
panda_resubmitted_jobs CLOB,
tm_save_logs VARCHAR(1) NOT NULL,
tw_name VARCHAR(255),
tm_user_infiles VARCHAR(4000),
tm_maxjobruntime NUMBER(38),
tm_numcores NUMBER(38),
tm_maxmemory NUMBER(38),
tm_priority NUMBER(38),
tm_output_dataset CLOB,
tm_task_warnings CLOB DEFAULT '[]',
tm_user_webdir VARCHAR(1000),
tm_scriptexe VARCHAR(255),
tm_scriptargs VARCHAR(4000),
tm_extrajdl VARCHAR(1000),
tm_asourl VARCHAR(4000),
CONSTRAINT taskname_pk PRIMARY KEY(tm_taskname),
CONSTRAINT check_tm_publication CHECK (tm_publication IN ('T', 'F')),
CONSTRAINT check_tm_save_logs CHECK (tm_save_logs IN ('T', 'F'))
)
"""
self.create['c_jobgroups'] = """
CREATE TABLE jobgroups(
tm_jobgroups_id NUMBER(38) NOT NULL,
tm_taskname VARCHAR(255) NOT NULL,
panda_jobdef_id NUMBER(11),
panda_jobdef_status VARCHAR(255) NOT NULL,
tm_data_blocks CLOB,
panda_jobgroup_failure CLOB,
tm_user_dn VARCHAR(255) NOT NULL,
CONSTRAINT taskname_fk FOREIGN KEY(tm_taskname) references
tasks(tm_taskname)
ON DELETE CASCADE,
CONSTRAINT jobgroup_id_pk PRIMARY KEY(tm_jobgroups_id)
)
"""
self.create['c_jobgroups_id_seq'] = """
CREATE SEQUENCE jobgroups_id_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['c_jobgroups_id_trg'] = """
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# //
# // Define create statements for each table
#//
# //
# // State/Type tables: enumerated type lists
#// Type will be the type of workflow: MC, CmsGen, Reco, Skim etc
self.create['a_reqmgr_request_type'] = """
CREATE TABLE reqmgr_request_type(
type_id NUMBER(11) NOT NULL,
type_name VARCHAR(255) NOT NULL,
PRIMARY KEY(type_id), UNIQUE(type_name)
)
"""
self.create['a_reqmgr_request_type_seq'] = """
CREATE SEQUENCE reqmgr_request_type_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['a_reqmgr_request_type_trg'] = """
CREATE TRIGGER reqmgr_request_type_trg
BEFORE INSERT ON reqmgr_request_type
FOR EACH ROW
BEGIN
SELECT reqmgr_request_type_seq.nextval INTO :new.type_id FROM dual;
END;"""
# //
# // states for a request to be in
#// new, untested, tested, approved, failed, aborted, junk etc
self.create['b_reqmgr_request_status'] = """
CREATE TABLE reqmgr_request_status(
status_id NUMBER(11) NOT NULL,
status_name VARCHAR(255) NOT NULL,
PRIMARY KEY(status_id), UNIQUE(status_name)
)
"""
self.create['b_reqmgr_request_status_seq'] = """
CREATE SEQUENCE reqmgr_request_status_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['b_reqmgr_request_status_trg'] = """
CREATE TRIGGER reqmgr_request_status_trg
BEFORE INSERT ON reqmgr_request_status
FOR EACH ROW
BEGIN
SELECT reqmgr_request_status_seq.nextval INTO :new.status_id FROM dual;
END;"""
# //
# // Group definitions
#// Which group to bill, also allows a base priority modifier
# //which will allow certain groups to be raised/lowered
# // with respect to each other.
#//
self.create['c_reqmgr_group'] = """
CREATE TABLE reqmgr_group(
group_id NUMBER(11) NOT NULL,
group_name VARCHAR(255) NOT NULL,
group_base_priority NUMBER(11) DEFAULT 0,
UNIQUE(group_name),
PRIMARY KEY(group_id)
)
"""
self.create['c_reqmgr_group_seq'] = """
CREATE SEQUENCE reqmgr_group_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['c_reqmgr_group_trg'] = """
CREATE TRIGGER reqmgr_group_trg
BEFORE INSERT ON reqmgr_group
FOR EACH ROW
BEGIN
SELECT reqmgr_group_seq.nextval INTO :new.group_id FROM dual;
END;"""
# //
# // Request Owner details
#// Actual person who makes a request.
# //
# //
#//
# //
# //
#// Can also be used to prioritise users if need be. (We may want
# //to use this to track users who make a lot of bad requests
# // and lower their priorities...)
#//
self.create['d_reqmgr_requestor'] = """
CREATE TABLE reqmgr_requestor (
requestor_id NUMBER(11) NOT NULL,
requestor_hn_name VARCHAR(255) NOT NULL,
contact_email VARCHAR(255) NOT NULL,
requestor_dn_name VARCHAR(255),
requestor_base_priority NUMBER(11) DEFAULT 0,
PRIMARY KEY(requestor_id),
UNIQUE (requestor_hn_name)
)
"""
self.create['d_reqmgr_requestor_seq'] = """
CREATE SEQUENCE reqmgr_requestor_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['d_reqmgr_requestor_trg'] = """
CREATE TRIGGER reqmgr_requestor_trg
BEFORE INSERT ON reqmgr_requestor
FOR EACH ROW
BEGIN
SELECT reqmgr_requestor_seq.nextval INTO :new.requestor_id FROM dual;
END;"""
# //
# // requestor/group association, allows users to belong
#// to multiple groups
# //
# //
#//
self.create['e_reqmgr_group_association'] = """
CREATE TABLE reqmgr_group_association (
association_id NUMBER(11) NOT NULL,
requestor_id NUMBER(11) NOT NULL,
group_id NUMBER(11) NOT NULL,
PRIMARY KEY(association_id),
UNIQUE(requestor_id, group_id),
FOREIGN KEY(requestor_id) references reqmgr_requestor(requestor_id)
ON DELETE CASCADE,
FOREIGN KEY(group_id) references reqmgr_group(group_id)
)
"""
self.create['e_reqmgr_group_association_seq'] = """
CREATE SEQUENCE reqmgr_group_association_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['e_reqmgr_group_association_trg'] = """
CREATE TRIGGER reqmgr_group_association_trg
BEFORE INSERT ON reqmgr_group_association
FOR EACH ROW
BEGIN
SELECT reqmgr_group_association_seq.nextval INTO :new.association_id FROM dual;
END;"""
# //
# // Production/Processing teams
#// Simple team name for now, could be expanded to include
# //contact details, ProdAgent URLs etc
# //
#//
self.create['f_reqmgr_teams'] = """
CREATE TABLE reqmgr_teams (
team_id NUMBER(11) NOT NULL,
team_name VARCHAR(255),
UNIQUE(team_name),
PRIMARY KEY(team_id)
)
"""
self.create['f_reqmgr_teams_seq'] = """
CREATE SEQUENCE reqmgr_teams_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['f_reqmgr_teams_trg'] = """
CREATE TRIGGER reqmgr_teams_trg
BEFORE INSERT ON reqmgr_teams
FOR EACH ROW
BEGIN
SELECT reqmgr_teams_seq.nextval INTO :new.team_id FROM dual;
END;"""
# //
# // Main request table
#//
# // just basics for now, anything else we want here?
# // Could generate GUIDs as request name.
#//
# // workflow will be an LFN like name for the workflow spec file
# //
#// basic estimates of request size in events or files can be added
self.create['g_reqmgr_request'] = """
CREATE TABLE reqmgr_request(
request_id NUMBER(11) NOT NULL,
request_name VARCHAR(255) NOT NULL,
request_type NUMBER(11) NOT NULL,
request_status NUMBER(11) NOT NULL,
request_priority NUMBER(11) NOT NULL,
requestor_group_id NUMBER(11) NOT NULL,
workflow VARCHAR(255) NOT NULL,
request_size_events NUMBER(11) DEFAULT 0,
request_size_files NUMBER(11) DEFAULT 0,
prep_id VARCHAR(255),
UNIQUE(request_name),
FOREIGN KEY(request_type) REFERENCES reqmgr_request_type(type_id),
FOREIGN KEY(request_status) references
reqmgr_request_status(status_id),
FOREIGN KEY(requestor_group_id) references
reqmgr_group_association(association_id),
PRIMARY KEY(request_id)
)
"""
self.create['g_reqmgr_request_seq'] = """
CREATE SEQUENCE reqmgr_request_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['g_reqmgr_request_trg'] = """
CREATE TRIGGER reqmgr_request_trg
BEFORE INSERT ON reqmgr_request
FOR EACH ROW
BEGIN
SELECT reqmgr_request_seq.nextval INTO :new.request_id FROM dual;
END;"""
# //
# // Assignment of request to team(s)
#// Also includes priority modifier to
# //allow assignments to teams at different priorities
# //
#//
self.create['h_reqmgr_assignment'] = """
CREATE TABLE reqmgr_assignment (
request_id NUMBER(11) NOT NULL,
team_id NUMBER(11) NOT NULL,
priority_modifier NUMBER(11) DEFAULT 0,
UNIQUE( request_id, team_id),
FOREIGN KEY (request_id) references
reqmgr_request(request_id) ON DELETE CASCADE,
FOREIGN KEY (team_id) references
reqmgr_teams(team_id)
)
"""
# //
# // Request Attributes useful for scheduling and
#// prioritisation
# //
# // Input datasets are needed for scheduling, need to track
#// which one is main input, any secondary input or pileup
self.create['i_reqmgr_input_dataset'] = """
CREATE TABLE reqmgr_input_dataset (
dataset_id NUMBER(11) NOT NULL,
request_id NUMBER(11) NOT NULL,
dataset_name VARCHAR(255) NOT NULL,
dataset_type VARCHAR(11),
CONSTRAINT dataset_type_cons
CHECK (dataset_type IN ('source', 'secondary', 'pileup')),
FOREIGN KEY(request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE,
PRIMARY KEY(dataset_id)
)
"""
self.create['i_reqmgr_input_dataset_seq'] = """
CREATE SEQUENCE reqmgr_input_dataset_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['i_reqmgr_input_dataset_trg'] = """
CREATE TRIGGER reqmgr_input_dataset_trg
BEFORE INSERT ON reqmgr_input_dataset
FOR EACH ROW
BEGIN
SELECT reqmgr_input_dataset_seq.nextval INTO :new.dataset_id FROM dual;
END;"""
# //
# // Output datasets arent necessarily needed for scheduling
#// but we need some idea of what comes from each job
# // and tests will give us size per event estimates which will
# // be needed for storage planning
#//
self.create['j_reqmgr_output_dataset'] = """
CREATE TABLE reqmgr_output_dataset (
dataset_id NUMBER(11) NOT NULL,
request_id NUMBER(11) NOT NULL,
dataset_name VARCHAR(255) NOT NULL,
size_per_event NUMBER(11),
custodial_site VARCHAR(255),
FOREIGN KEY(request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE,
PRIMARY KEY(dataset_id)
)
"""
self.create['j_reqmgr_output_dataset_seq'] = """
CREATE SEQUENCE reqmgr_output_dataset_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['j_reqmgr_output_dataset_trg'] = """
CREATE TRIGGER reqmgr_output_dataset_trg
BEFORE INSERT ON reqmgr_output_dataset
FOR EACH ROW
BEGIN
SELECT reqmgr_output_dataset_seq.nextval INTO :new.dataset_id FROM dual;
END;"""
# //
# // CMSSW version requirements are needed for planning
#// associate a set of SW versions with a request
# //
# // Since ReqMgr will have to create workflows, using
#// a release, this may be something we can use to show what
# // releases are available for production as well.
# //
#//
self.create['k_reqmgr_software'] = """
CREATE TABLE reqmgr_software (
software_id NUMBER(11) NOT NULL,
software_name VARCHAR(255) NOT NULL,
UNIQUE(software_name),
PRIMARY KEY(software_id)
)
"""
self.create['k_reqmgr_software_seq'] = """
CREATE SEQUENCE reqmgr_software_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['k_reqmgr_software_trg'] = """
CREATE TRIGGER reqmgr_software_trg
BEFORE INSERT ON reqmgr_software
FOR EACH ROW
BEGIN
SELECT reqmgr_software_seq.nextval INTO :new.software_id FROM dual;
END;"""
# //
# // Association between SW versions and a request
#// (chained processing could be multiple versions)
self.create['l_reqmgr_software_dependency'] = """
CREATE TABLE reqmgr_software_dependency (
request_id NUMBER(11) NOT NULL,
software_id NUMBER(11) NOT NULL,
UNIQUE(request_id, software_id),
FOREIGN KEY(software_id) references
reqmgr_software(software_id),
FOREIGN KEY(request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE
)
"""
# //
# // Request status and assignment tables used for tracking
#// and monitoring progress of a request
# // Should be coarse grained wide interval update (Eg per alloc
# // in PM) enough to give a big picture. Finer details can come
#// from drilling down to the PM/PA if needed
self.create['m_reqmgr_progress_update'] = """
CREATE TABLE reqmgr_progress_update (
request_id NUMBER(11) NOT NULL,
update_time TIMESTAMP,
events_written NUMBER(11) DEFAULT 0,
events_merged NUMBER(11) DEFAULT 0,
files_written NUMBER(11) DEFAULT 0,
files_merged NUMBER(11) DEFAULT 0,
associated_dataset VARCHAR(255) DEFAULT NULL,
time_per_event NUMBER(11) DEFAULT 0,
size_per_event NUMBER(11) DEFAULT 0,
percent_success FLOAT(11) DEFAULT 0,
percent_complete FLOAT(11) DEFAULT 0,
FOREIGN KEY(request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE
)
"""
# //
# // Attach messages to a request, eg:
#// "Request is now 50% done"
# // "Request was aborted due to failures"
# // "Which utter truncheon gave us a broken cfg?"
#// etc. Some kind of verbose summary
self.create['n_reqmgr_message'] = """
CREATE TABLE reqmgr_message(
request_id NUMBER(11) NOT NULL,
update_time TIMESTAMP,
message VARCHAR2(1000) NOT NULL,
FOREIGN KEY(request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE
)
"""
# //
# // track the assigned ProdMgr
#//
self.create['o_reqmgr_assigned_prodmgr'] = """
CREATE TABLE reqmgr_assigned_prodmgr (
request_id NUMBER(11) NOT NULL,
prodmgr_id VARCHAR(255) NOT NULL,
UNIQUE(request_id, prodmgr_id),
FOREIGN KEY (request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE
)
"""
# //
# // can also track assigned prodagents
#// expect this to flesh out more later, when we add
# //details of all PA instances and resources to enable
# // better scheduling decisions, For now will probably just
#// serve as a link to the HTTPFrontend to provide detailed
# //monitoring.
# //
#//
self.create['p_reqmgr_assigned_prodagent'] = """
CREATE TABLE reqmgr_assigned_prodagent (
request_id NUMBER(11) NOT NULL,
prodagent_id VARCHAR(255) NOT NULL,
UNIQUE(request_id, prodagent_id),
FOREIGN KEY (request_id) references
reqmgr_request(request_id)
ON DELETE CASCADE
)
"""
self.create['q_reqmgr_campaign'] = """
CREATE TABLE reqmgr_campaign (
campaign_id NUMBER(11) NOT NULL,
campaign_name VARCHAR(255),
UNIQUE(campaign_name),
PRIMARY KEY(campaign_id)
)
"""
self.create['q_reqmgr_campaign_seq'] = """
CREATE SEQUENCE reqmgr_campaign_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['q_reqmgr_campaign_trg'] = """
CREATE TRIGGER reqmgr_campaign_trg
BEFORE INSERT ON reqmgr_campaign
FOR EACH ROW
BEGIN
SELECT reqmgr_campaign_seq.nextval INTO :new.campaign_id FROM dual;
END;"""
self.create['r_reqmgr_campaign_assoc'] = """
CREATE TABLE reqmgr_campaign_assoc (
request_id NUMBER(11) NOT NULL,
campaign_id NUMBER(11) NOT NULL,
UNIQUE( request_id, campaign_id),
FOREIGN KEY (request_id) references
reqmgr_request(request_id) ON DELETE CASCADE,
FOREIGN KEY (campaign_id) references
reqmgr_campaign(campaign_id)
)
"""
for typeName in TypesList:
sql = "INSERT INTO reqmgr_request_type (type_name) VALUES ('%s')" % typeName
self.inserts["reqtype" + typeName] = sql
i = 0
for status in StatusList:
sql = "INSERT INTO reqmgr_request_status (status_name) VALUES('%s')" % status
self.inserts["%3d_reqstatus" % i] = sql
i = i + 1
def __init__(self, logger=None, dbi=None, params=None):
"""
_init_
Call the base class's constructor and create all necessary tables,
constraints and inserts.
"""
myThread = threading.currentThread()
DBCreator.__init__(self, myThread.logger, myThread.dbi)
#
# Tables, functions, procedures and sequences
#
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_dataset (
id INTEGER AUTO_INCREMENT,
path VARCHAR(500) COLLATE latin1_general_cs NOT NULL,
processing_ver VARCHAR(255),
acquisition_era VARCHAR(255),
valid_status VARCHAR(20),
global_tag VARCHAR(255),
parent VARCHAR(500),
prep_id VARCHAR(255),
PRIMARY KEY (id),
CONSTRAINT uq_dbs_dat UNIQUE (path)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_dataset_subscription (
id INTEGER AUTO_INCREMENT,
dataset_id INTEGER NOT NULL,
site VARCHAR(100) NOT NULL,
custodial INTEGER DEFAULT 0,
auto_approve INTEGER DEFAULT 0,
move INTEGER DEFAULT 0,
priority VARCHAR(10) DEFAULT 'Low',
subscribed INTEGER DEFAULT 0,
phedex_group VARCHAR(100),
delete_blocks INTEGER,
PRIMARY KEY (id),
CONSTRAINT uq_dbs_dat_sub UNIQUE (dataset_id, site, custodial, auto_approve, move, priority)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_algo (
id INTEGER AUTO_INCREMENT,
app_name VARCHAR(100),
app_ver VARCHAR(100),
app_fam VARCHAR(100),
pset_hash VARCHAR(700),
config_content LONGTEXT,
in_dbs INTEGER,
PRIMARY KEY (id),
CONSTRAINT uq_dbs_alg UNIQUE (app_name, app_ver, app_fam, pset_hash)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_algo_dataset_assoc (
id INTEGER AUTO_INCREMENT,
algo_id INTEGER NOT NULL,
dataset_id INTEGER NOT NULL,
in_dbs INTEGER DEFAULT 0,
PRIMARY KEY (id)
) ENGINE = InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_workflow (
id INTEGER AUTO_INCREMENT,
name VARCHAR(700),
task VARCHAR(700),
block_close_max_wait_time INTEGER,
block_close_max_files INTEGER,
block_close_max_events INTEGER,
block_close_max_size BIGINT,
completed INTEGER DEFAULT 0,
PRIMARY KEY (id),
CONSTRAINT uq_dbs_wor UNIQUE (name, task)
) ENGINE = InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_file (
id INTEGER AUTO_INCREMENT,
lfn VARCHAR(500) NOT NULL,
filesize BIGINT,
events INTEGER,
dataset_algo INTEGER NOT NULL,
block_id INTEGER,
status VARCHAR(20),
in_phedex INTEGER DEFAULT 0,
workflow INTEGER,
LastModificationDate INTEGER,
PRIMARY KEY (id),
CONSTRAINT uq_dbs_fil UNIQUE (lfn)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_file_parent (
child INTEGER NOT NULL,
parent INTEGER NOT NULL,
CONSTRAINT pk_dbs_fil_par PRIMARY KEY (child, parent)
) ENGINE=InnoDB
"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_file_runlumi_map (
filename INTEGER NOT NULL,
run INTEGER NOT NULL,
lumi INTEGER NOT NULL
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_location (
id INTEGER AUTO_INCREMENT,
se_name VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT uq_dbs_loc UNIQUE (se_name)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_file_location (
filename INTEGER NOT NULL,
location INTEGER NOT NULL,
CONSTRAINT pk_dbs_fil_loc PRIMARY KEY (filename, location)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_block (
id INTEGER AUTO_INCREMENT,
dataset_id INTEGER NOT NULL,
blockname VARCHAR(250) NOT NULL,
location INTEGER NOT NULL,
create_time INTEGER,
status VARCHAR(20),
deleted INTEGER DEFAULT 0,
PRIMARY KEY (id),
CONSTRAINT uq_dbs_blo UNIQUE (blockname, location)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_checksum_type (
id INTEGER AUTO_INCREMENT,
type VARCHAR(255),
PRIMARY KEY (id)
) ENGINE=InnoDB"""
self.create[len(self.create)] = \
"""CREATE TABLE dbsbuffer_file_checksums (
fileid INTEGER,
typeid INTEGER,
cksum VARCHAR(100),
CONSTRAINT pk_dbs_fil_che PRIMARY KEY (fileid, typeid)
) ENGINE=InnoDB"""
#
# Indexes
#
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_che_1 ON dbsbuffer_file_checksums (typeid)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_che_2 ON dbsbuffer_file_checksums (fileid)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_dat_sub_1 ON dbsbuffer_dataset_subscription (dataset_id)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_par_1 ON dbsbuffer_file_parent (child)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_par_2 ON dbsbuffer_file_parent (parent)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_blo_1 ON dbsbuffer_block (location)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_blo_2 ON dbsbuffer_block (dataset_id)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_alg_ass_1 ON dbsbuffer_algo_dataset_assoc (dataset_id)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_alg_ass_2 ON dbsbuffer_algo_dataset_assoc (algo_id)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_run_1 ON dbsbuffer_file_runlumi_map (filename)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_loc_1 ON dbsbuffer_file_location (location)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_loc_2 ON dbsbuffer_file_location (filename)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_dbs_fil_1 ON dbsbuffer_file (workflow)"""
#
# Constraints
#
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_checksums
ADD CONSTRAINT fk_file_checksums_typeid
FOREIGN KEY (typeid)
REFERENCES dbsbuffer_checksum_type(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_checksums
ADD CONSTRAINT fk_file_checksums_fileid
FOREIGN KEY (fileid)
REFERENCES dbsbuffer_file(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_dataset_subscription
ADD CONSTRAINT fk_dsetsubscription_datasetid
FOREIGN KEY (dataset_id)
REFERENCES dbsbuffer_dataset(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_parent
ADD CONSTRAINT fk_file_parent_child
FOREIGN KEY (child)
REFERENCES dbsbuffer_file(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_parent
ADD CONSTRAINT fk_file_parent_parent
FOREIGN KEY (parent)
REFERENCES dbsbuffer_file(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_block
ADD CONSTRAINT fk_block_location
FOREIGN KEY (location)
REFERENCES dbsbuffer_location(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_block
ADD CONSTRAINT fk_block_dataset_id
FOREIGN KEY (dataset_id)
REFERENCES dbsbuffer_dataset(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_algo_dataset_assoc
ADD CONSTRAINT fk_algodset_assoc_dataset_id
FOREIGN KEY (dataset_id)
REFERENCES dbsbuffer_dataset(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_algo_dataset_assoc
ADD CONSTRAINT fk_algodset_assoc_algo_id
FOREIGN KEY (algo_id)
REFERENCES dbsbuffer_algo(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_runlumi_map
ADD CONSTRAINT fk_file_runlumi_filename
FOREIGN KEY (filename)
REFERENCES dbsbuffer_file(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_location
ADD CONSTRAINT fk_file_location_location
FOREIGN KEY (location)
REFERENCES dbsbuffer_location(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file_location
ADD CONSTRAINT fk_file_location_filename
FOREIGN KEY (filename)
REFERENCES dbsbuffer_file(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE dbsbuffer_file
ADD CONSTRAINT fk_file_workflow
FOREIGN KEY (workflow)
REFERENCES dbsbuffer_workflow(id)
ON DELETE CASCADE"""
checksumTypes = ['cksum', 'adler32', 'md5']
for i in checksumTypes:
checksumTypeQuery = """INSERT INTO dbsbuffer_checksum_type
(type)
VALUES ('%s')
""" % (i)
self.inserts["wmbs_checksum_type_%s" % (i)] = checksumTypeQuery
def __init__(self, logger=None, dbi=None, param=None):
if dbi == None:
myThread = threading.currentThread()
dbi = myThread.dbi
logger = myThread.logger
DBCreator.__init__(self, logger, dbi)
self.create = {}
self.constraints = {}
# //
# // Define create statements for each table
#//
# //255 chars for tm_task_status is even too much
self.create['b_tasks'] = """
CREATE TABLE tasks(
tm_taskname VARCHAR(255) NOT NULL,
tm_activity VARCHAR(255),
panda_jobset_id NUMBER(11),
tm_task_status VARCHAR(255) NOT NULL,
tm_task_command VARCHAR(20),
tm_start_time TIMESTAMP,
tm_start_injection TIMESTAMP,
tm_end_injection TIMESTAMP,
tm_task_failure CLOB,
tm_job_sw VARCHAR(255) NOT NULL,
tm_job_arch VARCHAR(255),
tm_input_dataset VARCHAR(500),
tm_nonvalid_input_dataset VARCHAR(1) DEFAULT 'T',
tm_use_parent NUMBER(1),
tm_site_whitelist VARCHAR(4000),
tm_site_blacklist VARCHAR(4000),
tm_split_algo VARCHAR(255) NOT NULL,
tm_split_args CLOB NOT NULL,
tm_totalunits NUMBER(38),
tm_user_sandbox VARCHAR(255) NOT NULL,
tm_cache_url VARCHAR(255) NOT NULL,
tm_username VARCHAR(255) NOT NULL,
tm_user_dn VARCHAR(255) NOT NULL,
tm_user_vo VARCHAR(255) NOT NULL,
tm_user_role VARCHAR(255),
tm_user_group VARCHAR(255),
tm_publish_name VARCHAR(500),
tm_publish_groupname VARCHAR(1) DEFAULT 'F',
tm_asyncdest VARCHAR(255) NOT NULL,
tm_dbs_url VARCHAR(255) NOT NULL,
tm_publish_dbs_url VARCHAR(255),
tm_publication VARCHAR(1) NOT NULL,
tm_outfiles CLOB,
tm_tfile_outfiles CLOB,
tm_edm_outfiles CLOB,
tm_job_type VARCHAR(255) NOT NULL,
tm_generator VARCHAR(255),
tm_events_per_lumi NUMBER(38),
tm_arguments CLOB,
panda_resubmitted_jobs CLOB,
tm_save_logs VARCHAR(1) NOT NULL,
tw_name VARCHAR(255),
tm_user_infiles VARCHAR(4000),
tm_maxjobruntime NUMBER(38),
tm_numcores NUMBER(38),
tm_maxmemory NUMBER(38),
tm_priority NUMBER(38),
tm_output_dataset CLOB,
tm_task_warnings CLOB DEFAULT '[]',
tm_user_webdir VARCHAR(1000),
tm_scriptexe VARCHAR(255),
tm_scriptargs VARCHAR(4000),
tm_extrajdl VARCHAR(1000),
tm_asourl VARCHAR(4000),
tm_collector VARCHAR(1000),
tm_schedd VARCHAR(255),
tm_dry_run VARCHAR(1),
tm_user_files CLOB DEFAULT '[]',
tm_transfer_outputs VARCHAR(1),
tm_output_lfn VARCHAR(1000),
tm_ignore_locality VARCHAR(1),
tm_fail_limit NUMBER(38),
tm_one_event_mode VARCHAR(1),
tm_secondary_input_dataset VARCHAR(500),
tm_primary_dataset VARCHAR(255),
tm_last_publication TIMESTAMP,
tm_debug_files VARCHAR(255),
clusterid NUMBER(10),
tm_asodb VARCHAR(20),
tm_ignore_global_blacklist VARCHAR(1),
tm_submitter_ip_addr VARCHAR(45),
tm_DDM_reqid NUMBER(38),
CONSTRAINT taskname_pk PRIMARY KEY(tm_taskname),
CONSTRAINT check_tm_publication CHECK (tm_publication IN ('T', 'F')),
CONSTRAINT check_tm_publish_groupname CHECK (tm_publish_groupname IN ('T', 'F')),
CONSTRAINT check_tm_save_logs CHECK (tm_save_logs IN ('T', 'F')),
CONSTRAINT check_tm_dry_run CHECK (tm_dry_run IN ('T', 'F')),
CONSTRAINT check_tm_transfer_outputs CHECK (tm_transfer_outputs IN ('T', 'F')),
CONSTRAINT check_tm_ignore_locality CHECK (tm_ignore_locality IN ('T', 'F')),
CONSTRAINT check_tm_one_event_mode CHECK (tm_one_event_mode IN ('T', 'F')),
CONSTRAINT ck_tm_nonvalid_input_dataset CHECK (tm_nonvalid_input_dataset IN ('T', 'F'))
)
"""
self.create['c_jobgroups'] = """
CREATE TABLE jobgroups(
tm_jobgroups_id NUMBER(38) NOT NULL,
tm_taskname VARCHAR(255) NOT NULL,
panda_jobdef_id NUMBER(11),
panda_jobdef_status VARCHAR(255) NOT NULL,
tm_data_blocks CLOB,
panda_jobgroup_failure CLOB,
tm_user_dn VARCHAR(255) NOT NULL,
CONSTRAINT taskname_fk FOREIGN KEY(tm_taskname) references
tasks(tm_taskname)
ON DELETE CASCADE,
CONSTRAINT jobgroup_id_pk PRIMARY KEY(tm_jobgroups_id)
)
"""
self.create['c_jobgroups_id_seq'] = """
CREATE SEQUENCE jobgroups_id_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE"""
self.create['c_jobgroups_id_trg'] = """
def __init__(self, logger = None, dbi = None, params = None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
tablespaceTable = ""
tablespaceIndex = ""
if params:
if params.has_key("tablespace_table"):
tablespaceTable = "TABLESPACE %s" % params["tablespace_table"]
if params.has_key("tablespace_index"):
tablespaceIndex = "USING INDEX TABLESPACE %s" % params["tablespace_index"]
DBCreator.__init__(self, logger, dbi)
self.requiredTables = ["01wmbs_fileset",
"02wmbs_file_details",
"03wmbs_fileset_files",
"04wmbs_file_parent",
"05wmbs_file_runlumi_map",
"05wmbs_location_state",
"06wmbs_location",
"07wmbs_file_location",
"07wmbs_users",
"07wmbs_workflow",
"08wmbs_sub_types",
"08wmbs_workflow_output",
"09wmbs_subscription",
"10wmbs_subscription_validation",
"10wmbs_sub_files_acquired",
"10wmbs_sub_files_available",
"11wmbs_sub_files_failed",
"12wmbs_sub_files_complete",
"13wmbs_jobgroup",
"14wmbs_job_state",
"15wmbs_job",
"16wmbs_job_assoc",
"17wmbs_job_mask",
"18wmbs_checksum_type",
"19wmbs_file_checksums",
"20wmbs_location_senames",]
self.create["01wmbs_fileset"] = \
"""CREATE TABLE wmbs_fileset (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(500) NOT NULL,
open INT(1) NOT NULL DEFAULT 0,
last_update INTEGER NOT NULL,
UNIQUE (name))"""
self.create["02wmbs_file_details"] = \
"""CREATE TABLE wmbs_file_details (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
lfn VARCHAR(500) NOT NULL,
filesize BIGINT,
events INTEGER,
first_event BIGINT NOT NULL DEFAULT 0,
merged INT(1) NOT NULL DEFAULT 0,
UNIQUE (lfn))"""
self.create["03wmbs_fileset_files"] = \
"""CREATE TABLE wmbs_fileset_files (
fileid INTEGER NOT NULL,
fileset INTEGER NOT NULL,
insert_time INTEGER NOT NULL,
UNIQUE (fileid, fileset),
FOREIGN KEY(fileset) references wmbs_fileset(id))"""
self.create["04wmbs_file_parent"] = \
"""CREATE TABLE wmbs_file_parent (
child INTEGER NOT NULL,
parent INTEGER NOT NULL,
FOREIGN KEY (child) references wmbs_file_details(id)
ON DELETE CASCADE,
FOREIGN KEY (parent) references wmbs_file_details(id),
UNIQUE(child, parent))"""
self.create["05wmbs_file_runlumi_map"] = \
"""CREATE TABLE wmbs_file_runlumi_map (
fileid INTEGER NOT NULL,
run INTEGER NOT NULL,
lumi INTEGER NOT NULL,
FOREIGN KEY (fileid) references wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["05wmbs_location_state"] = \
"""CREATE TABLE wmbs_location_state (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL)"""
self.create["06wmbs_location"] = \
"""CREATE TABLE wmbs_location (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
site_name VARCHAR(255) NOT NULL,
cms_name VARCHAR(255),
ce_name VARCHAR(255),
running_slots INTEGER,
pending_slots INTEGER,
plugin VARCHAR(255),
state INTEGER NOT NULL,
UNIQUE(site_name),
FOREIGN KEY (state) REFERENCES wmbs_location_state(id))"""
self.create["07wmbs_users"] = \
"""CREATE TABLE wmbs_users (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
cert_dn VARCHAR(255) NOT NULL,
name_hn VARCHAR(255),
owner VARCHAR(255),
grp VARCHAR(255),
group_name VARCHAR(255),
role_name VARCHAR(255),
UNIQUE(cert_dn, group_name, role_name))"""
self.create["07wmbs_file_location"] = \
"""CREATE TABLE wmbs_file_location (
fileid INTEGER NOT NULL,
location INTEGER NOT NULL,
UNIQUE(fileid, location),
FOREIGN KEY(fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE,
FOREIGN KEY(location) REFERENCES wmbs_location(id)
ON DELETE CASCADE)"""
self.create["07wmbs_workflow"] = \
"""CREATE TABLE wmbs_workflow (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
spec VARCHAR(700) NOT NULL,
name VARCHAR(700) NOT NULL,
task VARCHAR(700) NOT NULL,
type VARCHAR(255),
owner INTEGER NOT NULL,
alt_fs_close INT(1) NOT NULL,
injected INT(1) DEFAULT 0,
priority INTEGER UNSIGNED DEFAULT 0,
FOREIGN KEY (owner)
REFERENCES wmbs_users(id) ON DELETE CASCADE) """
self.indexes["03_pk_wmbs_workflow"] = \
"""ALTER TABLE wmbs_workflow ADD
(CONSTRAINT wmbs_workflow_unique UNIQUE (name, spec, task))"""
self.create["08wmbs_workflow_output"] = \
"""CREATE TABLE wmbs_workflow_output (
workflow_id INTEGER NOT NULL,
output_identifier VARCHAR(255) NOT NULL,
output_fileset INTEGER NOT NULL,
merged_output_fileset INTEGER,
FOREIGN KEY(workflow_id) REFERENCES wmbs_workflow(id)
ON DELETE CASCADE,
FOREIGN KEY(output_fileset) REFERENCES wmbs_fileset(id)
ON DELETE CASCADE,
FOREIGN KEY(merged_output_fileset) REFERENCES wmbs_fileset(id)
ON DELETE CASCADE)
"""
self.create["08wmbs_sub_types"] = \
"""CREATE TABLE wmbs_sub_types (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
priority INTEGER DEFAULT 0,
UNIQUE(name))"""
self.create["09wmbs_subscription"] = \
"""CREATE TABLE wmbs_subscription (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
fileset INTEGER NOT NULL,
workflow INTEGER NOT NULL,
split_algo VARCHAR(255) NOT NULL,
subtype INTEGER NOT NULL,
last_update INTEGER NOT NULL,
finished INT(1) DEFAULT 0,
FOREIGN KEY(fileset)
REFERENCES wmbs_fileset(id) ON DELETE CASCADE,
FOREIGN KEY(workflow)
REFERENCES wmbs_workflow(id) ON DELETE CASCADE,
FOREIGN KEY(subtype)
REFERENCES wmbs_sub_types(id) ON DELETE CASCADE)"""
self.create["10wmbs_subscription_validation"] = \
"""CREATE TABLE wmbs_subscription_validation (
subscription_id INTEGER NOT NULL,
location_id INTEGER NOT NULL,
valid INTEGER,
UNIQUE (subscription_id, location_id),
FOREIGN KEY(subscription_id) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY(location_id) REFERENCES wmbs_location(id)
ON DELETE CASCADE)"""
self.create["10wmbs_sub_files_acquired"] = \
"""CREATE TABLE wmbs_sub_files_acquired (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)
"""
self.create["10wmbs_sub_files_available"] = \
"""CREATE TABLE wmbs_sub_files_available (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)
"""
self.create["11wmbs_sub_files_failed"] = \
"""CREATE TABLE wmbs_sub_files_failed (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["12wmbs_sub_files_complete"] = \
"""CREATE TABLE wmbs_sub_files_complete (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["13wmbs_jobgroup"] = \
"""CREATE TABLE wmbs_jobgroup (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
subscription INTEGER NOT NULL,
guid VARCHAR(255),
output INTEGER,
last_update INTEGER NOT NULL,
location INTEGER,
UNIQUE(guid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (output) REFERENCES wmbs_fileset(id)
ON DELETE CASCADE)"""
self.create["14wmbs_job_state"] = \
"""CREATE TABLE wmbs_job_state (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100))"""
self.create["15wmbs_job"] = \
"""CREATE TABLE wmbs_job (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
jobgroup INTEGER NOT NULL,
name VARCHAR(255),
state INTEGER NOT NULL,
state_time INTEGER NOT NULL,
retry_count INTEGER DEFAULT 0,
couch_record VARCHAR(255),
location INTEGER,
outcome INTEGER DEFAULT 0,
cache_dir VARCHAR(700) DEFAULT 'None',
fwjr_path VARCHAR(700),
FOREIGN KEY (jobgroup)
REFERENCES wmbs_jobgroup(id) ON DELETE CASCADE,
FOREIGN KEY (state) REFERENCES wmbs_job_state(id),
FOREIGN KEY (location) REFERENCES wmbs_location(id))"""
self.indexes["03_pk_wmbs_job"] = \
"""ALTER TABLE wmbs_job ADD
(CONSTRAINT wmbs_job_unique UNIQUE (name, cache_dir, fwjr_path))"""
self.create["16wmbs_job_assoc"] = \
"""CREATE TABLE wmbs_job_assoc (
job INTEGER NOT NULL,
fileid INTEGER NOT NULL,
FOREIGN KEY (job) REFERENCES wmbs_job(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["17wmbs_job_mask"] = \
"""CREATE TABLE wmbs_job_mask (
job INTEGER NOT NULL,
FirstEvent BIGINT,
LastEvent BIGINT,
FirstLumi INTEGER,
LastLumi INTEGER,
FirstRun INTEGER,
LastRun INTEGER,
inclusivemask BOOLEAN DEFAULT TRUE,
FOREIGN KEY (job) REFERENCES wmbs_job(id)
ON DELETE CASCADE)"""
self.create["18wmbs_checksum_type"] = \
"""CREATE TABLE wmbs_checksum_type (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(255) )
"""
self.create["19wmbs_file_checksums"] = \
"""CREATE TABLE wmbs_file_checksums (
fileid INTEGER,
typeid INTEGER,
cksum VARCHAR(100),
UNIQUE (fileid, typeid),
FOREIGN KEY (typeid) REFERENCES wmbs_checksum_type(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["20wmbs_location_senames"] = \
"""CREATE TABLE wmbs_location_senames (
location INTEGER,
se_name VARCHAR(255),
UNIQUE(location, se_name),
FOREIGN KEY (location) REFERENCES wmbs_location(id)
ON DELETE CASCADE)"""
self.constraints["01_idx_wmbs_fileset_files"] = \
"""CREATE INDEX wmbs_fileset_files_idx_fileset ON wmbs_fileset_files(fileset) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_fileset_files"] = \
"""CREATE INDEX wmbs_fileset_files_idx_fileid ON wmbs_fileset_files(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_runlumi_map"] = \
"""CREATE INDEX wmbs_file_runlumi_map_fileid ON wmbs_file_runlumi_map(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_location"] = \
"""CREATE INDEX wmbs_file_location_fileid ON wmbs_file_location(fileid) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_file_location"] = \
"""CREATE INDEX wmbs_file_location_location ON wmbs_file_location(location) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_parent"] = \
"""CREATE INDEX wmbs_file_parent_parent ON wmbs_file_parent(parent) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_file_parent"] = \
"""CREATE INDEX wmbs_file_parent_child ON wmbs_file_parent(child) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_workflow_output"] = \
"""CREATE INDEX idx_wmbs_workf_out_workflow ON wmbs_workflow_output(workflow_id) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_workflow_output"] = \
"""CREATE INDEX idx_wmbs_workf_out_fileset ON wmbs_workflow_output(output_fileset) %s""" % tablespaceIndex
self.constraints["03_idx_wmbs_workflow_output"] = \
"""CREATE INDEX idx_wmbs_workf_mout_fileset ON wmbs_workflow_output(merged_output_fileset) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_subscription"] = \
"""CREATE INDEX idx_wmbs_subscription_fileset ON wmbs_subscription(fileset) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_subscription"] = \
"""CREATE INDEX idx_wmbs_subscription_subtype ON wmbs_subscription(subtype) %s""" % tablespaceIndex
self.constraints["03_idx_wmbs_subscription"] = \
"""CREATE INDEX idx_wmbs_subscription_workflow ON wmbs_subscription(workflow) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_acquired"] = \
"""CREATE INDEX idx_wmbs_sub_files_acq_sub ON wmbs_sub_files_acquired(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_acquired"] = \
"""CREATE INDEX idx_wmbs_sub_files_acq_file ON wmbs_sub_files_acquired(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_available"] = \
"""CREATE INDEX idx_wmbs_sub_files_ava_sub ON wmbs_sub_files_available(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_available"] = \
"""CREATE INDEX idx_wmbs_sub_files_ava_file ON wmbs_sub_files_available(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_failed"] = \
"""CREATE INDEX idx_wmbs_sub_files_fail_sub ON wmbs_sub_files_failed(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_failed"] = \
"""CREATE INDEX idx_wmbs_sub_files_fail_file ON wmbs_sub_files_failed(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_complete"] = \
"""CREATE INDEX idx_wmbs_sub_files_comp_sub ON wmbs_sub_files_complete(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_complete"] = \
"""CREATE INDEX idx_wmbs_sub_files_comp_file ON wmbs_sub_files_complete(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_jobgroup"] = \
"""CREATE INDEX idx_wmbs_jobgroup_sub ON wmbs_jobgroup(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_jobgroup"] = \
"""CREATE INDEX idx_wmbs_jobgroup_out ON wmbs_jobgroup(output) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_job"] = \
"""CREATE INDEX idx_wmbs_job_jobgroup ON wmbs_job(jobgroup) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_job"] = \
"""CREATE INDEX idx_wmbs_job_loc ON wmbs_job(location) %s""" % tablespaceIndex
self.constraints["03_idx_wmbs_job"] = \
"""CREATE INDEX idx_wmbs_job_state ON wmbs_job(state) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_job_assoc"] = \
"""CREATE INDEX idx_wmbs_job_assoc_job ON wmbs_job_assoc(job) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_job_assoc"] = \
"""CREATE INDEX idx_wmbs_job_assoc_file ON wmbs_job_assoc(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_job_mask"] = \
"""CREATE INDEX idx_wmbs_job_mask_job ON wmbs_job_mask(job) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_checksums"] = \
"""CREATE INDEX idx_wmbs_file_checksums_type ON wmbs_file_checksums(typeid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_checksums"] = \
"""CREATE INDEX idx_wmbs_file_checksums_file ON wmbs_file_checksums(fileid) %s""" % tablespaceIndex
# The transitions class holds all states and allowed transitions, use
# that to populate the wmbs_job_state table
for jobState in Transitions().states():
jobStateQuery = "INSERT INTO wmbs_job_state (name) VALUES ('%s')" % \
(jobState)
self.inserts["job_state_%s" % jobState] = jobStateQuery
self.subTypes = [("Processing", 0), ("Merge", 5), ("Harvesting", 3), ("Cleanup", 5),
("LogCollect", 3), ("Skim", 3), ("Analysis", 0), ("Production", 0),
("MultiProcessing", 0), ("MultiProduction", 0)]
for pair in self.subTypes:
subTypeQuery = """INSERT INTO wmbs_sub_types (name, priority)
VALUES ('%s', %d)""" % (pair[0], pair[1])
self.inserts["wmbs_sub_types_%s" % pair[0]] = subTypeQuery
locationStates = ["Normal", "Down", "Draining", "Aborted"]
for i in locationStates:
locationStateQuery = """INSERT INTO wmbs_location_state (name)
VALUES ('%s')""" % i
self.inserts["wmbs_location_state_%s" % i] = locationStateQuery
checksumTypes = ['cksum', 'adler32', 'md5']
for i in checksumTypes:
checksumTypeQuery = """INSERT INTO wmbs_checksum_type (type) VALUES ('%s')
""" % (i)
self.inserts["wmbs_checksum_type_%s" % (i)] = checksumTypeQuery
return
def __init__(self, logger=None, dbi=None, params=None):
"""
_init_
Call the DBCreator constructor and create the list of required tables.
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
tablespaceIndex = ""
if params:
if "tablespace_index" in params:
tablespaceIndex = "USING INDEX TABLESPACE %s" % params[
"tablespace_index"]
DBCreator.__init__(self, logger, dbi)
self.requiredTables = [
"01wmbs_fileset",
"02wmbs_file_details",
"03wmbs_fileset_files",
"04wmbs_file_parent",
"05wmbs_file_runlumi_map",
"05wmbs_location_state",
"06wmbs_location",
"07wmbs_file_location",
"07wmbs_users",
"07wmbs_workflow",
"08wmbs_sub_types",
"08wmbs_workflow_output",
"09wmbs_subscription",
"10wmbs_subscription_validation",
"10wmbs_sub_files_acquired",
"10wmbs_sub_files_available",
"11wmbs_sub_files_failed",
"12wmbs_sub_files_complete",
"13wmbs_jobgroup",
"14wmbs_job_state",
"15wmbs_job",
"16wmbs_job_assoc",
"17wmbs_job_mask",
"18wmbs_checksum_type",
"19wmbs_file_checksums",
"20wmbs_location_pnns",
]
self.create["01wmbs_fileset"] = \
"""CREATE TABLE wmbs_fileset (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(700) NOT NULL,
open INT(1) NOT NULL DEFAULT 0,
last_update INTEGER NOT NULL,
UNIQUE (name))"""
self.create["02wmbs_file_details"] = \
"""CREATE TABLE wmbs_file_details (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
lfn VARCHAR(700) NOT NULL,
filesize BIGINT,
events INTEGER,
first_event BIGINT NOT NULL DEFAULT 0,
merged INT(1) NOT NULL DEFAULT 0,
UNIQUE (lfn))"""
self.create["03wmbs_fileset_files"] = \
"""CREATE TABLE wmbs_fileset_files (
fileid INTEGER NOT NULL,
fileset INTEGER NOT NULL,
insert_time INTEGER NOT NULL,
UNIQUE (fileid, fileset),
FOREIGN KEY(fileset) references wmbs_fileset(id))"""
self.create["04wmbs_file_parent"] = \
"""CREATE TABLE wmbs_file_parent (
child INTEGER NOT NULL,
parent INTEGER NOT NULL,
FOREIGN KEY (child) references wmbs_file_details(id)
ON DELETE CASCADE,
FOREIGN KEY (parent) references wmbs_file_details(id),
UNIQUE(child, parent))"""
self.create["05wmbs_file_runlumi_map"] = \
"""CREATE TABLE wmbs_file_runlumi_map (
fileid INTEGER NOT NULL,
run INTEGER NOT NULL,
lumi INTEGER NOT NULL,
FOREIGN KEY (fileid) references wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["05wmbs_location_state"] = \
"""CREATE TABLE wmbs_location_state (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL)"""
self.create["06wmbs_location"] = \
"""CREATE TABLE wmbs_location (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
site_name VARCHAR(255) NOT NULL,
state INTEGER NOT NULL,
cms_name VARCHAR(255),
ce_name VARCHAR(255),
running_slots INTEGER,
pending_slots INTEGER,
plugin VARCHAR(255),
UNIQUE(site_name),
FOREIGN KEY (state) REFERENCES wmbs_location_state(id))"""
self.create["07wmbs_users"] = \
"""CREATE TABLE wmbs_users (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
cert_dn VARCHAR(255) NOT NULL,
name_hn VARCHAR(255),
owner VARCHAR(255),
grp VARCHAR(255),
group_name VARCHAR(255),
role_name VARCHAR(255),
UNIQUE(cert_dn, group_name, role_name))"""
self.create["07wmbs_file_location"] = \
"""CREATE TABLE wmbs_file_location (
fileid INTEGER NOT NULL,
location INTEGER NOT NULL,
UNIQUE(fileid, location),
FOREIGN KEY(fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE,
FOREIGN KEY(location) REFERENCES wmbs_location(id)
ON DELETE CASCADE)"""
self.create["07wmbs_workflow"] = \
"""CREATE TABLE wmbs_workflow (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
spec VARCHAR(700) NOT NULL,
name VARCHAR(700) NOT NULL,
task VARCHAR(700) NOT NULL,
type VARCHAR(255),
owner INTEGER NOT NULL,
alt_fs_close INT(1) NOT NULL,
injected INT(1) DEFAULT 0,
priority INTEGER UNSIGNED DEFAULT 0,
FOREIGN KEY (owner)
REFERENCES wmbs_users(id) ON DELETE CASCADE) """
self.indexes["03_pk_wmbs_workflow"] = \
"""ALTER TABLE wmbs_workflow ADD
(CONSTRAINT wmbs_workflow_unique UNIQUE (name, spec, task))"""
self.create["08wmbs_workflow_output"] = \
"""CREATE TABLE wmbs_workflow_output (
workflow_id INTEGER NOT NULL,
output_identifier VARCHAR(255) NOT NULL,
output_fileset INTEGER NOT NULL,
merged_output_fileset INTEGER,
FOREIGN KEY(workflow_id) REFERENCES wmbs_workflow(id)
ON DELETE CASCADE,
FOREIGN KEY(output_fileset) REFERENCES wmbs_fileset(id)
ON DELETE CASCADE,
FOREIGN KEY(merged_output_fileset) REFERENCES wmbs_fileset(id)
ON DELETE CASCADE)
"""
self.create["08wmbs_sub_types"] = \
"""CREATE TABLE wmbs_sub_types (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
priority INTEGER DEFAULT 0,
UNIQUE(name))"""
self.create["09wmbs_subscription"] = \
"""CREATE TABLE wmbs_subscription (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
fileset INTEGER NOT NULL,
workflow INTEGER NOT NULL,
split_algo VARCHAR(255) NOT NULL,
subtype INTEGER NOT NULL,
last_update INTEGER NOT NULL,
finished INT(1) DEFAULT 0,
FOREIGN KEY(fileset)
REFERENCES wmbs_fileset(id) ON DELETE CASCADE,
FOREIGN KEY(workflow)
REFERENCES wmbs_workflow(id) ON DELETE CASCADE,
FOREIGN KEY(subtype)
REFERENCES wmbs_sub_types(id) ON DELETE CASCADE)"""
self.create["10wmbs_subscription_validation"] = \
"""CREATE TABLE wmbs_subscription_validation (
subscription_id INTEGER NOT NULL,
location_id INTEGER NOT NULL,
valid INTEGER,
UNIQUE (subscription_id, location_id),
FOREIGN KEY(subscription_id) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY(location_id) REFERENCES wmbs_location(id)
ON DELETE CASCADE)"""
self.create["10wmbs_sub_files_acquired"] = \
"""CREATE TABLE wmbs_sub_files_acquired (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)
"""
self.create["10wmbs_sub_files_available"] = \
"""CREATE TABLE wmbs_sub_files_available (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)
"""
self.create["11wmbs_sub_files_failed"] = \
"""CREATE TABLE wmbs_sub_files_failed (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["12wmbs_sub_files_complete"] = \
"""CREATE TABLE wmbs_sub_files_complete (
subscription INTEGER NOT NULL,
fileid INTEGER NOT NULL,
PRIMARY KEY (subscription, fileid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["13wmbs_jobgroup"] = \
"""CREATE TABLE wmbs_jobgroup (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
subscription INTEGER NOT NULL,
guid VARCHAR(255),
output INTEGER,
last_update INTEGER NOT NULL,
location INTEGER,
UNIQUE(guid),
FOREIGN KEY (subscription) REFERENCES wmbs_subscription(id)
ON DELETE CASCADE,
FOREIGN KEY (output) REFERENCES wmbs_fileset(id)
ON DELETE CASCADE)"""
self.create["14wmbs_job_state"] = \
"""CREATE TABLE wmbs_job_state (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100))"""
self.create["15wmbs_job"] = \
"""CREATE TABLE wmbs_job (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
jobgroup INTEGER NOT NULL,
name VARCHAR(255),
state INTEGER NOT NULL,
state_time INTEGER NOT NULL,
retry_count INTEGER DEFAULT 0,
couch_record VARCHAR(255),
location INTEGER,
outcome INTEGER DEFAULT 0,
cache_dir VARCHAR(767) DEFAULT 'None',
fwjr_path VARCHAR(767),
FOREIGN KEY (jobgroup)
REFERENCES wmbs_jobgroup(id) ON DELETE CASCADE,
FOREIGN KEY (state) REFERENCES wmbs_job_state(id),
FOREIGN KEY (location) REFERENCES wmbs_location(id))"""
self.indexes["03_pk_wmbs_job"] = \
"""ALTER TABLE wmbs_job ADD
(CONSTRAINT wmbs_job_unique UNIQUE (name, cache_dir, fwjr_path))"""
self.create["16wmbs_job_assoc"] = \
"""CREATE TABLE wmbs_job_assoc (
job INTEGER NOT NULL,
fileid INTEGER NOT NULL,
FOREIGN KEY (job) REFERENCES wmbs_job(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["17wmbs_job_mask"] = \
"""CREATE TABLE wmbs_job_mask (
job INTEGER NOT NULL,
FirstEvent BIGINT,
LastEvent BIGINT,
FirstLumi INTEGER,
LastLumi INTEGER,
FirstRun INTEGER,
LastRun INTEGER,
inclusivemask BOOLEAN DEFAULT TRUE,
FOREIGN KEY (job) REFERENCES wmbs_job(id)
ON DELETE CASCADE)"""
self.create["18wmbs_checksum_type"] = \
"""CREATE TABLE wmbs_checksum_type (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(255) )
"""
self.create["19wmbs_file_checksums"] = \
"""CREATE TABLE wmbs_file_checksums (
fileid INTEGER,
typeid INTEGER,
cksum VARCHAR(100),
UNIQUE (fileid, typeid),
FOREIGN KEY (typeid) REFERENCES wmbs_checksum_type(id)
ON DELETE CASCADE,
FOREIGN KEY (fileid) REFERENCES wmbs_file_details(id)
ON DELETE CASCADE)"""
self.create["20wmbs_location_pnns"] = \
"""CREATE TABLE wmbs_location_pnns (
location INTEGER,
pnn VARCHAR(255),
UNIQUE(location, pnn),
FOREIGN KEY (location) REFERENCES wmbs_location(id)
ON DELETE CASCADE)"""
self.constraints["01_idx_wmbs_fileset_files"] = \
"""CREATE INDEX wmbs_fileset_files_idx_fileset ON wmbs_fileset_files(fileset) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_fileset_files"] = \
"""CREATE INDEX wmbs_fileset_files_idx_fileid ON wmbs_fileset_files(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_runlumi_map"] = \
"""CREATE INDEX wmbs_file_runlumi_map_fileid ON wmbs_file_runlumi_map(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_location"] = \
"""CREATE INDEX wmbs_file_location_fileid ON wmbs_file_location(fileid) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_file_location"] = \
"""CREATE INDEX wmbs_file_location_location ON wmbs_file_location(location) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_parent"] = \
"""CREATE INDEX wmbs_file_parent_parent ON wmbs_file_parent(parent) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_file_parent"] = \
"""CREATE INDEX wmbs_file_parent_child ON wmbs_file_parent(child) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_workflow_output"] = \
"""CREATE INDEX idx_wmbs_workf_out_workflow ON wmbs_workflow_output(workflow_id) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_workflow_output"] = \
"""CREATE INDEX idx_wmbs_workf_out_fileset ON wmbs_workflow_output(output_fileset) %s""" % tablespaceIndex
self.constraints["03_idx_wmbs_workflow_output"] = \
"""CREATE INDEX idx_wmbs_workf_mout_fileset ON wmbs_workflow_output(merged_output_fileset) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_subscription"] = \
"""CREATE INDEX idx_wmbs_subscription_fileset ON wmbs_subscription(fileset) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_subscription"] = \
"""CREATE INDEX idx_wmbs_subscription_subtype ON wmbs_subscription(subtype) %s""" % tablespaceIndex
self.constraints["03_idx_wmbs_subscription"] = \
"""CREATE INDEX idx_wmbs_subscription_workflow ON wmbs_subscription(workflow) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_acquired"] = \
"""CREATE INDEX idx_wmbs_sub_files_acq_sub ON wmbs_sub_files_acquired(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_acquired"] = \
"""CREATE INDEX idx_wmbs_sub_files_acq_file ON wmbs_sub_files_acquired(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_available"] = \
"""CREATE INDEX idx_wmbs_sub_files_ava_sub ON wmbs_sub_files_available(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_available"] = \
"""CREATE INDEX idx_wmbs_sub_files_ava_file ON wmbs_sub_files_available(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_failed"] = \
"""CREATE INDEX idx_wmbs_sub_files_fail_sub ON wmbs_sub_files_failed(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_failed"] = \
"""CREATE INDEX idx_wmbs_sub_files_fail_file ON wmbs_sub_files_failed(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_files_complete"] = \
"""CREATE INDEX idx_wmbs_sub_files_comp_sub ON wmbs_sub_files_complete(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_files_complete"] = \
"""CREATE INDEX idx_wmbs_sub_files_comp_file ON wmbs_sub_files_complete(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_sub_jobgroup"] = \
"""CREATE INDEX idx_wmbs_jobgroup_sub ON wmbs_jobgroup(subscription) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_sub_jobgroup"] = \
"""CREATE INDEX idx_wmbs_jobgroup_out ON wmbs_jobgroup(output) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_job"] = \
"""CREATE INDEX idx_wmbs_job_jobgroup ON wmbs_job(jobgroup) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_job"] = \
"""CREATE INDEX idx_wmbs_job_loc ON wmbs_job(location) %s""" % tablespaceIndex
self.constraints["03_idx_wmbs_job"] = \
"""CREATE INDEX idx_wmbs_job_state ON wmbs_job(state) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_job_assoc"] = \
"""CREATE INDEX idx_wmbs_job_assoc_job ON wmbs_job_assoc(job) %s""" % tablespaceIndex
self.constraints["02_idx_wmbs_job_assoc"] = \
"""CREATE INDEX idx_wmbs_job_assoc_file ON wmbs_job_assoc(fileid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_job_mask"] = \
"""CREATE INDEX idx_wmbs_job_mask_job ON wmbs_job_mask(job) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_checksums"] = \
"""CREATE INDEX idx_wmbs_file_checksums_type ON wmbs_file_checksums(typeid) %s""" % tablespaceIndex
self.constraints["01_idx_wmbs_file_checksums"] = \
"""CREATE INDEX idx_wmbs_file_checksums_file ON wmbs_file_checksums(fileid) %s""" % tablespaceIndex
# The transitions class holds all states and allowed transitions, use
# that to populate the wmbs_job_state table
for jobState in Transitions().states():
jobStateQuery = "INSERT INTO wmbs_job_state (name) VALUES ('%s')" % \
(jobState)
self.inserts["job_state_%s" % jobState] = jobStateQuery
self.subTypes = [("Processing", 0), ("Merge", 5), ("Harvesting", 3),
("Cleanup", 5), ("LogCollect", 3), ("Skim", 3),
("Production", 0)]
for pair in self.subTypes:
subTypeQuery = """INSERT INTO wmbs_sub_types (name, priority)
VALUES ('%s', %d)""" % (pair[0], pair[1])
self.inserts["wmbs_sub_types_%s" % pair[0]] = subTypeQuery
locationStates = ["Normal", "Down", "Draining", "Aborted"]
for i in locationStates:
locationStateQuery = """INSERT INTO wmbs_location_state (name)
VALUES ('%s')""" % i
self.inserts["wmbs_location_state_%s" % i] = locationStateQuery
checksumTypes = ['cksum', 'adler32', 'md5']
for i in checksumTypes:
checksumTypeQuery = """INSERT INTO wmbs_checksum_type (type) VALUES ('%s')
""" % (i)
self.inserts["wmbs_checksum_type_%s" % (i)] = checksumTypeQuery
return
def __init__(self, logger = None, dbi = None, params = None):
"""
_init_
Call the DBCreator constructor and initialize the schema
"""
myThread = threading.currentThread()
if logger == None:
logger = myThread.logger
if dbi == None:
dbi = myThread.dbi
DBCreator.__init__(self, logger, dbi)
#
# Tables, functions, procedures and sequences
#
self.create[len(self.create)] = \
"""CREATE TABLE t0_config (
run_id int not null,
config varchar2(255) not null,
primary key(run_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE run_status (
id int not null,
name varchar2(25) not null,
primary key(id),
constraint run_sta_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE processing_style (
id int not null,
name varchar2(25) not null,
primary key(id),
constraint pro_sty_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE event_scenario (
id int not null,
name varchar2(25) not null,
primary key(id),
constraint eve_sce_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE data_tier (
id int not null,
name varchar2(25) not null,
primary key(id),
constraint dat_tie_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE cmssw_version (
id int not null,
name varchar2(255) not null,
primary key(id),
constraint cms_ver_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE stream (
id int not null,
name varchar2(255) not null,
primary key(id),
constraint str_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE trigger_label (
id int not null,
name varchar2(255) not null,
primary key(id),
constraint tri_lab_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE primary_dataset (
id int not null,
name varchar2(255) not null,
primary key(id),
constraint pri_dat_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE storage_node (
id int not null,
name varchar2(255) not null,
primary key(id),
constraint sto_nod_name_uq unique(name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE run (
run_id int not null,
status int default 1 not null,
last_updated int not null,
express_released int default 0 not null,
hltkey varchar2(255) not null,
start_time int not null,
end_time int default 0 not null,
close_time int default 0 not null,
lumicount int default 0 not null,
process varchar2(255),
acq_era varchar2(255),
lfn_prefix varchar2(255),
bulk_data_type varchar2(255),
bulk_data_loc varchar2(255),
dqmuploadurl varchar2(255),
ah_timeout int,
ah_dir varchar2(255),
cond_timeout int,
db_host varchar2(255),
valid_mode int,
primary key(run_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE run_trig_primds_assoc (
run_id int not null,
primds_id int not null,
trig_id int not null,
primary key(run_id, primds_id, trig_id)
) ORGANIZATION INDEX COMPRESS 2"""
self.create[len(self.create)] = \
"""CREATE TABLE run_primds_stream_assoc (
run_id int not null,
primds_id int not null,
stream_id int not null,
primary key(run_id, primds_id)
)"""
self.create[len(self.create)] = \
"""CREATE TABLE run_primds_scenario_assoc (
run_id int not null,
primds_id int not null,
scenario_id int not null,
primary key(run_id, primds_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE run_stream_style_assoc (
run_id int not null,
stream_id int not null,
style_id int not null,
primary key(run_id, stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE run_stream_cmssw_assoc (
run_id int not null,
stream_id int not null,
online_version int not null,
override_version int not null,
primary key(run_id, stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE run_stream_fileset_assoc (
run_id int not null,
stream_id int not null,
fileset int not null,
primary key(run_id, stream_id),
constraint run_str_fil_ass_fil_uq unique(fileset)
using index
(create unique index idx_run_stream_fileset_assoc_1 on run_stream_fileset_assoc (fileset))
)"""
self.create[len(self.create)] = \
"""CREATE TABLE reco_release_config (
run_id int not null,
primds_id int not null,
fileset int not null,
delay int not null,
delay_offset int not null,
released int default 0 not null,
primary key(run_id, primds_id),
constraint rec_rel_con_fil_uq unique(fileset)
using index
(create unique index idx_reco_release_config_1 on reco_release_config (fileset))
)"""
self.create[len(self.create)] = \
"""CREATE TABLE stream_special_primds_assoc (
stream_id int not null,
primds_id int not null,
primary key(stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE primds_error_primds_assoc (
parent_id int not null,
error_id int not null,
primary key(parent_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE lumi_section (
run_id int not null,
lumi_id int not null,
primary key(run_id, lumi_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE lumi_section_closed (
run_id int not null,
stream_id int not null,
lumi_id int not null,
filecount int not null,
insert_time int not null,
close_time int default 0 not null,
primary key(run_id, stream_id, lumi_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE lumi_section_split_active (
subscription int not null,
run_id int not null,
lumi_id int not null,
primary key(subscription, run_id, lumi_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE streamer (
id int not null,
run_id int not null,
stream_id int not null,
lumi_id int not null,
insert_time int not null,
used int default 0 not null,
deleted int default 0 not null,
primary key(id)
)"""
self.create[len(self.create)] = \
"""CREATE TABLE repack_config (
run_id int not null,
stream_id int not null,
proc_version int not null,
max_size_single_lumi int not null,
max_size_multi_lumi int not null,
min_size int not null,
max_size int not null,
max_edm_size int not null,
max_over_size int not null,
max_events int not null,
max_files int not null,
primary key (run_id, stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE express_config (
run_id int not null,
stream_id int not null,
proc_version int not null,
write_tiers varchar2(255) not null,
global_tag varchar2(255) not null,
max_events int not null,
max_size int not null,
max_files int not null,
max_latency int not null,
alca_skim varchar2(1000),
dqm_seq varchar2(1000),
primary key (run_id, stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE prompt_calib (
run_id int not null,
stream_id int not null,
finished int default 0 not null,
subscription int,
primary key (run_id, stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE prompt_calib_file (
fileid int not null,
run_id int not null,
stream_id int not null,
primary key (fileid, run_id, stream_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE reco_config (
run_id int not null,
primds_id int not null,
do_reco int not null,
cmssw_id int not null,
reco_split int not null,
write_reco int not null,
write_dqm int not null,
write_aod int not null,
proc_version int not null,
alca_skim varchar2(1000),
dqm_seq varchar2(1000),
global_tag varchar2(255),
primary key (run_id, primds_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE phedex_config (
run_id int not null,
primds_id int not null,
node_id int not null,
custodial int not null,
request_only char(1) not null,
priority varchar2(10) not null,
primary key (run_id, primds_id, node_id)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE promptskim_config (
run_id int not null,
primds_id int not null,
tier_id int not null,
skim_name varchar2(255) not null,
node_id int not null,
cmssw_id int not null,
two_file_read int not null,
proc_version int not null,
global_tag varchar2(255),
config_url varchar2(255),
primary key (run_id, primds_id, tier_id, skim_name)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE TABLE workflow_monitoring (
workflow int not null,
tracked int default 0 not null,
closeout int default 0 not null,
primary key (workflow)
) ORGANIZATION INDEX"""
self.create[len(self.create)] = \
"""CREATE FUNCTION checkForZeroState (value IN int)
RETURN int DETERMINISTIC IS
BEGIN
IF value = 0 THEN
RETURN 0;
ELSE
RETURN NULL;
END IF;
END checkForZeroState;
"""
self.create[len(self.create)] = \
"""CREATE FUNCTION checkForZeroOneState (value IN int)
RETURN int DETERMINISTIC IS
BEGIN
IF value = 0 THEN
RETURN 0;
ELSIF value = 1 THEN
RETURN 1;
ELSE
RETURN NULL;
END IF;
END checkForZeroOneState;
"""
self.create[len(self.create)] = \
"""CREATE SEQUENCE cmssw_version_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 10
"""
self.create[len(self.create)] = \
"""CREATE SEQUENCE stream_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 10
"""
self.create[len(self.create)] = \
"""CREATE SEQUENCE trigger_label_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 100
"""
self.create[len(self.create)] = \
"""CREATE SEQUENCE primary_dataset_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 100
"""
self.create[len(self.create)] = \
"""CREATE SEQUENCE storage_node_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 100
"""
#
# Indexes
#
#
# Usual rules is to put an index on all FK. I don't follow it
# strictly here because some tables are only used rarely and
# with well defined filter conditions. In these cases I might
# just add an index on the condition I need.
#
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_run_1 ON run (checkForZeroState(express_released))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_run_2 ON run (status)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_run_primds_stream_1 ON run_primds_stream_assoc (run_id, stream_id)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_reco_release_config_2 ON reco_release_config (checkForZeroState(released))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_lumi_section_closed_1 ON lumi_section_closed (checkForZeroState(close_time))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_streamer_1 ON streamer (run_id, stream_id, lumi_id)"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_streamer_2 ON streamer (checkForZeroState(used))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_streamer_3 ON streamer (checkForZeroOneState(deleted))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_prompt_calib_1 ON prompt_calib (checkForZeroState(finished))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_workflow_monitoring_0 ON workflow_monitoring (checkForZeroState(tracked))"""
self.indexes[len(self.indexes)] = \
"""CREATE INDEX idx_workflow_monitoring_1 ON workflow_monitoring (checkForZeroState(closeout))"""
#
# Constraints
#
self.constraints[len(self.constraints)] = \
"""ALTER TABLE t0_config
ADD CONSTRAINT t0_conf_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run
ADD CONSTRAINT run_sta_fk
FOREIGN KEY (status)
REFERENCES run_status(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_trig_primds_assoc
ADD CONSTRAINT run_tri_pri_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_trig_primds_assoc
ADD CONSTRAINT run_tri_pri_pri_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_trig_primds_assoc
ADD CONSTRAINT run_tri_pri_tri_id_fk
FOREIGN KEY (trig_id)
REFERENCES trigger_label(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_primds_stream_assoc
ADD CONSTRAINT run_pri_tri_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_primds_stream_assoc
ADD CONSTRAINT run_pri_tri_pri_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_primds_stream_assoc
ADD CONSTRAINT run_pri_tri_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_primds_scenario_assoc
ADD CONSTRAINT run_pri_sce_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_primds_scenario_assoc
ADD CONSTRAINT run_pri_sce_pri_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_primds_scenario_assoc
ADD CONSTRAINT run_pri_sce_sce_id_fk
FOREIGN KEY (scenario_id)
REFERENCES event_scenario(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_style_assoc
ADD CONSTRAINT run_str_sty_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_style_assoc
ADD CONSTRAINT run_str_sty_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_style_assoc
ADD CONSTRAINT run_str_sty_sty_id_fk
FOREIGN KEY (style_id)
REFERENCES processing_style(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_cmssw_assoc
ADD CONSTRAINT run_str_cms_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_cmssw_assoc
ADD CONSTRAINT run_str_cms_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_cmssw_assoc
ADD CONSTRAINT run_str_cms_onl_ver_fk
FOREIGN KEY (online_version)
REFERENCES cmssw_version(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_cmssw_assoc
ADD CONSTRAINT run_str_cms_ove_ver_fk
FOREIGN KEY (override_version)
REFERENCES cmssw_version(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_fileset_assoc
ADD CONSTRAINT run_str_fil_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_fileset_assoc
ADD CONSTRAINT run_str_fil_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE run_stream_fileset_assoc
ADD CONSTRAINT run_str_fil_fil_id_fk
FOREIGN KEY (fileset)
REFERENCES wmbs_fileset(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE reco_release_config
ADD CONSTRAINT rec_rel_con_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE reco_release_config
ADD CONSTRAINT rec_rel_con_pri_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE reco_release_config
ADD CONSTRAINT rec_rel_con_fil_id_fk
FOREIGN KEY (fileset)
REFERENCES wmbs_fileset(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE stream_special_primds_assoc
ADD CONSTRAINT str_spe_pri_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE stream_special_primds_assoc
ADD CONSTRAINT str_spe_pri_pri_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE primds_error_primds_assoc
ADD CONSTRAINT pri_err_pri_par_id_fk
FOREIGN KEY (parent_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE primds_error_primds_assoc
ADD CONSTRAINT pri_err_pri_err_id_fk
FOREIGN KEY (error_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE lumi_section
ADD CONSTRAINT lum_sec_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE lumi_section_closed
ADD CONSTRAINT lum_sec_clo_rl_id_fk
FOREIGN KEY (run_id, lumi_id)
REFERENCES lumi_section(run_id, lumi_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE lumi_section_closed
ADD CONSTRAINT lum_sec_clo_stre_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE lumi_section_split_active
ADD CONSTRAINT lum_sec_spli_act_rl_id_fk
FOREIGN KEY (run_id, lumi_id)
REFERENCES lumi_section(run_id, lumi_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE lumi_section_split_active
ADD CONSTRAINT lum_sec_spli_act_stre_id_fk
FOREIGN KEY (subscription)
REFERENCES wmbs_subscription(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE streamer
ADD CONSTRAINT str_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE streamer
ADD CONSTRAINT str_rl_id_fk
FOREIGN KEY (run_id, lumi_id)
REFERENCES lumi_section(run_id, lumi_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE streamer
ADD CONSTRAINT str_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE repack_config
ADD CONSTRAINT rep_con_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE repack_config
ADD CONSTRAINT rep_con_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE express_config
ADD CONSTRAINT exp_con_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE express_config
ADD CONSTRAINT exp_con_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE prompt_calib
ADD CONSTRAINT pro_cal_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE prompt_calib
ADD CONSTRAINT pro_cal_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE prompt_calib
ADD CONSTRAINT pro_cal_sub_fk
FOREIGN KEY (subscription)
REFERENCES wmbs_subscription(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE prompt_calib_file
ADD CONSTRAINT pro_cal_fil_fil_id_fk
FOREIGN KEY (fileid)
REFERENCES wmbs_file_details(id)
ON DELETE CASCADE"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE prompt_calib_file
ADD CONSTRAINT pro_cal_fil_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE prompt_calib_file
ADD CONSTRAINT pro_cal_fil_str_id_fk
FOREIGN KEY (stream_id)
REFERENCES stream(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE reco_config
ADD CONSTRAINT rec_con_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE reco_config
ADD CONSTRAINT rec_con_primds_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE reco_config
ADD CONSTRAINT rec_con_cms_id_fk
FOREIGN KEY (cmssw_id)
REFERENCES cmssw_version(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE phedex_config
ADD CONSTRAINT phe_con_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE phedex_config
ADD CONSTRAINT phe_con_primds_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE phedex_config
ADD CONSTRAINT phe_con_nod_id_fk
FOREIGN KEY (node_id)
REFERENCES storage_node(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE promptskim_config
ADD CONSTRAINT pro_con_run_id_fk
FOREIGN KEY (run_id)
REFERENCES run(run_id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE promptskim_config
ADD CONSTRAINT pro_con_primds_id_fk
FOREIGN KEY (primds_id)
REFERENCES primary_dataset(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE promptskim_config
ADD CONSTRAINT pro_con_tie_id_fk
FOREIGN KEY (tier_id)
REFERENCES data_tier(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE promptskim_config
ADD CONSTRAINT pro_con_nod_id_fk
FOREIGN KEY (node_id)
REFERENCES storage_node(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE promptskim_config
ADD CONSTRAINT pro_con_cms_id_fk
FOREIGN KEY (cmssw_id)
REFERENCES cmssw_version(id)"""
self.constraints[len(self.constraints)] = \
"""ALTER TABLE workflow_monitoring
ADD CONSTRAINT wor_mon_wor_fk
FOREIGN KEY (workflow)
REFERENCES wmbs_workflow(id)
ON DELETE CASCADE"""
subTypes = ["Express", "Repack"]
for name in subTypes:
sql = """INSERT INTO wmbs_sub_types
(ID, NAME)
SELECT wmbs_sub_types_SEQ.nextval, '%s'
FROM DUAL
WHERE NOT EXISTS (
SELECT id FROM wmbs_sub_types WHERE name = '%s'
)
""" % (name, name)
self.inserts[len(self.inserts)] = sql
runStates = { 1 : "Active",
2 : "CloseOutRepack",
3 : "CloseOutRepackMerge",
4 : "CloseOutPromptReco",
5 : "CloseOutRecoMerge",
6 : "CloseOutAlcaSkim",
7 : "CloseOutAlcaSkimMerge",
8 : "CloseOutExport",
9 : "CloseOutT1Skimming",
10 : "Complete" }
for id, name in runStates.items():
sql = """INSERT INTO run_status
(ID, NAME)
VALUES (%d, '%s')
""" % (id, name)
self.inserts[len(self.inserts)] = sql
processingStyles = { 1 : "Bulk",
2 : "Express",
3 : "Register",
4 : "Convert",
5 : "RegisterAndConvert",
6 : "Ignore" }
for id, name in processingStyles.items():
sql = """INSERT INTO processing_style
(ID, NAME)
VALUES (%d, '%s')
""" % (id, name)
self.inserts[len(self.inserts)] = sql
eventScenarios = { 1 : "pp",
2 : "cosmics",
3 : "hcalnzs",
4 : "HeavyIons",
5 : "AlCaTestEnable",
6 : "AlCaP0",
7 : "AlCaPhiSymEcal",
8 : "AlCaLumiPixels",
9 : "DataScouting" }
for id, name in eventScenarios.items():
sql = """INSERT INTO event_scenario
(ID, NAME)
VALUES (%d, '%s')
""" % (id, name)
self.inserts[len(self.inserts)] = sql
dataTiers = { 1 : "RAW",
2 : "RECO",
3 : "FEVT",
4 : "FEVTHLTALL",
5 : "AOD",
6 : "ALCARECO",
7 : "DQM",
8 : "ALCAPROMPT" }
for id, name in dataTiers.items():
sql = """INSERT INTO data_tier
(ID, NAME)
VALUES (%d, '%s')
""" % (id, name)
self.inserts[len(self.inserts)] = sql
return
