Exemple #1
0
 def formatWorkflow(self, results):
     workflow = {}
     tran = Transitions()
     for result in results:
         if not workflow.has_key(result["wmspec"]):
             workflow[result["wmspec"]] = {}
             for state in tran.states():
                 workflow[result["wmspec"]][state] = 0
                 
             workflow[result["wmspec"]][result["state"]] = result["num_job"]
             workflow[result["wmspec"]]['total_jobs'] = result["num_job"]
             workflow[result["wmspec"]]["real_success"] = int(result["success"])
             workflow[result["wmspec"]]["id"] = result["id"]
             workflow[result["wmspec"]]["wmspec"] = result["wmspec"]
             workflow[result["wmspec"]]["pending"] = self.pendingCount(result)
             workflow[result["wmspec"]]["real_fail"] = self.failCount(result)
             workflow[result["wmspec"]]['processing'] = self.processingCount(result)
         else:
             workflow[result["wmspec"]][result["state"]] = result["num_job"]
             workflow[result["wmspec"]]['total_jobs'] += result["num_job"]
             workflow[result["wmspec"]]["real_success"] += int(result["success"])
             workflow[result["wmspec"]]["pending"] += self.pendingCount(result)
             workflow[result["wmspec"]]["real_fail"] += self.failCount(result)
             workflow[result["wmspec"]]['processing'] += self.processingCount(result)
     
     # need to order by id (client side)        
     return workflow.values()
    def processRetries(self, jobs, cooloffType):
        """
        _processRetries_

        Actually does the dirty work of figuring out what to do with jobs
        """

        if len(jobs) < 1:
            # We got no jobs?
            return

        transitions = Transitions()
        oldstate = '%scooloff' % cooloffType
        if oldstate not in transitions.keys():
            msg = 'Unknown job type %s' % cooloffType
            logging.error(msg)
            self.sendAlert(6, msg=msg)
            return
        propList = []

        newJobState = transitions[oldstate][0]

        jobList = self.loadJobsFromList(idList=jobs)

        # Now we should have the jobs
        propList = self.selectRetryAlgo(jobList, cooloffType)

        if len(propList) > 0:
            self.changeState.propagate(propList, newJobState, oldstate)

        return
Exemple #3
0
    def formatWorkflow(self, results):
        workflow = {}
        tran = Transitions()
        for result in results:
            if result["wmspec"] not in workflow:
                workflow[result["wmspec"]] = {}
                for state in tran.states():
                    workflow[result["wmspec"]][state] = 0

                workflow[result["wmspec"]][result["state"]] = result["num_job"]
                workflow[result["wmspec"]]['total_jobs'] = result["num_job"]
                workflow[result["wmspec"]]["real_success"] = int(
                    result["success"])
                workflow[result["wmspec"]]["id"] = result["id"]
                workflow[result["wmspec"]]["wmspec"] = result["wmspec"]
                workflow[result["wmspec"]]["pending"] = self.pendingCount(
                    result)
                workflow[result["wmspec"]]["real_fail"] = self.failCount(
                    result)
                workflow[result["wmspec"]][
                    'processing'] = self.processingCount(result)
            else:
                workflow[result["wmspec"]][result["state"]] = result["num_job"]
                workflow[result["wmspec"]]['total_jobs'] += result["num_job"]
                workflow[result["wmspec"]]["real_success"] += int(
                    result["success"])
                workflow[result["wmspec"]]["pending"] += self.pendingCount(
                    result)
                workflow[result["wmspec"]]["real_fail"] += self.failCount(
                    result)
                workflow[result["wmspec"]][
                    'processing'] += self.processingCount(result)

        # need to order by id (client side)
        return workflow.values()
    def processRetries(self, jobs, cooloffType):
        """
        _processRetries_

        Actually does the dirty work of figuring out what to do with jobs
        """

        if len(jobs) < 1:
            # We got no jobs?
            return

        transitions = Transitions()
        oldstate = '%scooloff' % (cooloffType)
        if not oldstate in transitions.keys():
            msg = 'Unknown job type %s' % (cooloffType)
            logging.error(msg)
            self.sendAlert(6, msg = msg)
            return
        propList = []

        newJobState  = transitions[oldstate][0]

        jobList = self.loadJobsFromList(idList = jobs)

        # Now we should have the jobs
        propList = self.selectRetryAlgo(jobList, cooloffType)

        if len(propList) > 0:
            self.changeState.propagate(propList, newJobState, oldstate)


        return
Exemple #5
0
    def check(self, newstate, oldstate):
        """
        check that the transition is allowed. return a tuple of the transition
        if it is allowed, throw up an exception if not.
        """
        newstate = newstate.lower()
        oldstate = oldstate.lower()

        # Check for wrong transitions
        transitions = Transitions()
        assert newstate in transitions[oldstate], \
            "Illegal state transition requested: %s -> %s" % (oldstate, newstate)
Exemple #6
0
    def testListJobStates(self):
        """
        _testListJobStates_

        Verify that the ListJobStates DAO works correctly.
        """
        listJobStates = self.daoFactory(classname="Monitoring.ListJobStates")
        jobStates = listJobStates.execute()

        transitionStates = Transitions().states()
        assert len(jobStates) == len(transitionStates), \
               "Error: Number of states don't match."

        for jobState in jobStates:
            assert jobState in transitionStates, \
                   "Error: Missing job state %s" % jobState

        return
Exemple #7
0
    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
Exemple #8
0
    def __init__(self, logger=None, dbi=None, params=None):
        """
        _init_

        Call the base class's constructor and create all necessary tables,
        constraints and inserts.
        """
        CreateWMBSBase.__init__(self, logger, 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"]

        self.create["01wmbs_fileset"] = \
          """CREATE TABLE wmbs_fileset (
               id          INTEGER      NOT NULL,
               name        VARCHAR(700) NOT NULL,
               open        CHAR(1)      CHECK (open IN ('0', '1' )) NOT NULL,
               last_update INTEGER      NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_fileset"] = \
          """ALTER TABLE wmbs_fileset ADD
               (CONSTRAINT wmbs_fileset_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["02_pk_wmbs_fileset"] = \
          """ALTER TABLE wmbs_fileset ADD
               (CONSTRAINT wmbs_fileset_unique UNIQUE (name) %s)""" % tablespaceIndex

        self.create["02wmbs_file_details"] = \
          """CREATE TABLE wmbs_file_details (
               id          INTEGER NOT NULL,
               lfn         VARCHAR(700) NOT NULL,
               filesize    INTEGER,
               events      INTEGER,
               first_event INTEGER      DEFAULT 0,
               merged      CHAR(1) CHECK (merged IN ('0', '1' )) NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_file_details"] = \
          """ALTER TABLE wmbs_file_details ADD
               (CONSTRAINT wmbs_file_details_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["02_pk_wmbs_file_details"] = \
          """ALTER TABLE wmbs_file_details ADD
               (CONSTRAINT wmbs_fildetails_unique UNIQUE (lfn) %s)""" % tablespaceIndex

        self.create["03wmbs_fileset_files"] = \
          """CREATE TABLE wmbs_fileset_files (
               fileid      INTEGER NOT NULL,
               fileset     INTEGER NOT NULL,
               insert_time INTEGER NOT NULL
               ) %s""" % tablespaceTable

        #self.indexes["01_pk_wmbs_fileset_files"] = \
        #  """ALTER TABLE wmbs_fileset_files ADD
        #       (CONSTRAINT wmbs_fileset_files_pk PRIMARY KEY (fileid, fileset) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_fileset_files"] = \
          """ALTER TABLE wmbs_fileset_files ADD
               (CONSTRAINT fk_filesetfiles_fileset FOREIGN KEY(fileset)
                  REFERENCES wmbs_fileset(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_fk_wmbs_fileset_files"] = \
          """ALTER TABLE wmbs_fileset_files ADD
               (CONSTRAINT fk_filesetfiles_file FOREIGN KEY(fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        self.constraints["02_idx_wmbs_fileset_files"] = \
          """CREATE INDEX wmbs_fileset_files_idx_fileid ON wmbs_fileset_files(fileid) %s""" % tablespaceIndex

        self.create["04wmbs_file_parent"] = \
          """CREATE TABLE wmbs_file_parent (
               child  INTEGER NOT NULL,
               parent INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.constraints["01_fk_wmbs_file_parent"] = \
          """ALTER TABLE wmbs_file_parent ADD
               (CONSTRAINT fk_fileparent_parent FOREIGN KEY(parent)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_file_parent"] = \
          """ALTER TABLE wmbs_file_parent ADD
               (CONSTRAINT fk_fileparent_child FOREIGN KEY(child)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        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.create["05wmbs_file_runlumi_map"] = \
          """CREATE TABLE wmbs_file_runlumi_map (
               fileid INTEGER NOT NULL,
               run    INTEGER NOT NULL,
               lumi   INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.constraints["01_fk_wmbs_file_runlumi_map"] = \
          """ALTER TABLE wmbs_file_runlumi_map ADD
               (CONSTRAINT fk_runlumi_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_file_runlumi_map"] = \
          """CREATE INDEX wmbs_file_runlumi_map_fileid ON wmbs_file_runlumi_map(fileid) %s""" % tablespaceIndex

        self.create["05wmbs_location_state"] = \
            """CREATE TABLE wmbs_location_state (
               id   INTEGER NOT NULL,
               name VARCHAR(100) NOT NULL) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_location_state"] = \
          """ALTER TABLE wmbs_location_state ADD
               (CONSTRAINT wmbs_location_state_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.create["06wmbs_location"] = \
          """CREATE TABLE wmbs_location (
               id          INTEGER      NOT NULL,
               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)
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_location"] = \
          """ALTER TABLE wmbs_location ADD
               (CONSTRAINT wmbs_location_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["02_pk_wmbs_location"] = \
          """ALTER TABLE wmbs_location ADD
               (CONSTRAINT wmbs_location_unique UNIQUE (site_name) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_location"] = \
          """ALTER TABLE wmbs_location ADD
               (CONSTRAINT fk_location_state FOREIGN KEY (state)
                  REFERENCES wmbs_location_state(id))"""

        self.create["07wmbs_users"] = \
          """CREATE TABLE wmbs_users (
             id         INTEGER      NOT NULL,
             cert_dn    VARCHAR(255) NOT NULL,
             name_hn    VARCHAR(255),
             owner      VARCHAR(255),
             grp        VARCHAR(255),
             group_name VARCHAR(255),
             role_name  VARCHAR(255)
             ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_users"] = \
          """ALTER TABLE wmbs_users ADD
               (CONSTRAINT wmbs_users_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["02_pk_wmbs_users"] = \
          """ALTER TABLE wmbs_users ADD
              (CONSTRAINT wmbs_users_unique UNIQUE (cert_dn, group_name, role_name) %s)""" % tablespaceIndex

        self.create["07wmbs_file_location"] = \
          """CREATE TABLE wmbs_file_location (
               fileid   INTEGER NOT NULL,
               location INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_file_location"] = \
          """ALTER TABLE wmbs_file_location ADD
               (CONSTRAINT wmbs_file_location_pk PRIMARY KEY (fileid, location) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_file_location"] = \
          """ALTER TABLE wmbs_file_location ADD
              (CONSTRAINT fk_location_file FOREIGN KEY(fileid)
                 REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_file_location"] = \
          """CREATE INDEX wmbs_file_location_fileid ON wmbs_file_location(fileid) %s""" % tablespaceIndex

        self.constraints["02_fk_wmbs_file_location"] = \
          """ALTER TABLE wmbs_file_location ADD
              (CONSTRAINT fk_location_location FOREIGN KEY(location)
                 REFERENCES wmbs_location(id) ON DELETE CASCADE)"""

        self.constraints["02_idx_wmbs_file_location"] = \
          """CREATE INDEX wmbs_file_location_location ON wmbs_file_location(location) %s""" % tablespaceIndex

        self.create["07wmbs_workflow"] = \
          """CREATE TABLE wmbs_workflow (
               id    INTEGER      NOT NULL,
               spec  VARCHAR(700) NOT NULL,
               name  VARCHAR(255) NOT NULL,
               task  VARCHAR(700) NOT NULL,
               type  VARCHAR(255),
               owner INTEGER      NOT NULL,
               alt_fs_close INTEGER NOT NULL,
               injected INTEGER   DEFAULT 0,
               priority INTEGER   DEFAULT 0
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_workflow"] = \
          """ALTER TABLE wmbs_workflow ADD
               (CONSTRAINT wmbs_workflow_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["uniquewfname"] = \
          """ALTER TABLE wmbs_workflow ADD
               (CONSTRAINT uniq_wf_name UNIQUE (name, task) %s)""" % tablespaceIndex

        self.indexes["02_fk_wmbs_workflow"] = \
          """ALTER TABLE wmbs_workflow ADD
              (CONSTRAINT fk_workflow_users FOREIGN KEY(owner)
                 REFERENCES wmbs_users(id) ON DELETE CASCADE)"""

        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
               ) %s""" % tablespaceTable

        self.constraints["01_fk_wmbs_workflow_output"] = \
          """ALTER TABLE wmbs_workflow_output ADD
              (CONSTRAINT fk_wfoutput_workflow FOREIGN KEY(workflow_id)
                 REFERENCES wmbs_workflow(id) ON DELETE CASCADE)"""

        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_fk_wmbs_workflow_output"] = \
          """ALTER TABLE wmbs_workflow_output ADD
              (CONSTRAINT fk_wfoutput_fileset FOREIGN KEY(output_fileset)
                 REFERENCES wmbs_fileset(id) ON DELETE CASCADE)"""

        self.constraints["03_fk_wmbs_workflow_output"] = \
          """ALTER TABLE wmbs_workflow_output ADD
              (CONSTRAINT fk_wfoutput_mfileset FOREIGN KEY(merged_output_fileset)
                 REFERENCES wmbs_fileset(id) ON DELETE CASCADE)"""

        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_out_mfileset ON wmbs_workflow_output(merged_output_fileset) %s""" % tablespaceIndex

        self.create["08wmbs_sub_types"] = \
          """CREATE TABLE wmbs_sub_types (
               id   INTEGER      NOT NULL,
               name VARCHAR(255) NOT NULL,
               priority INTEGER DEFAULT 0
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_sub_types"] = \
          """ALTER TABLE wmbs_sub_types ADD
               (CONSTRAINT wmbs_sub_types_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["02_pk_wmbs_sub_types"] = \
          """ALTER TABLE wmbs_sub_types ADD
               (CONSTRAINT wmbs_sub_types_uk UNIQUE (name) %s)""" % tablespaceIndex

        self.create["09wmbs_subscription"] = \
          """CREATE TABLE wmbs_subscription (
               id          INTEGER      NOT NULL,
               fileset     INTEGER      NOT NULL,
               workflow    INTEGER      NOT NULL,
               split_algo  VARCHAR(255) NOT NULL,
               subtype     INTEGER      NOT NULL,
               last_update INTEGER      NOT NULL,
               finished    INTEGER      DEFAULT 0
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_subscription"] = \
          """ALTER TABLE wmbs_subscription ADD
               (CONSTRAINT wmbs_subscription_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_subscription"] = \
          """ALTER TABLE wmbs_subscription ADD
               (CONSTRAINT fk_subs_fileset FOREIGN KEY(fileset)
                  REFERENCES wmbs_fileset(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_subscription"] = \
          """CREATE INDEX idx_wmbs_subscription_fileset ON wmbs_subscription(fileset) %s""" % tablespaceIndex

        self.constraints["02_fk_wmbs_subscription"] = \
          """ALTER TABLE wmbs_subscription ADD
               (CONSTRAINT fk_sub_types FOREIGN KEY(subtype)
                  REFERENCES wmbs_sub_types(id) ON DELETE CASCADE)"""

        self.constraints["02_idx_wmbs_subscription"] = \
          """CREATE INDEX idx_wmbs_subscription_subtype ON wmbs_subscription(subtype) %s""" % tablespaceIndex

        self.constraints["03_fk_wmbs_subscription"] = \
          """ALTER TABLE wmbs_subscription ADD
               (CONSTRAINT fk_subs_workflow FOREIGN KEY(workflow)
                  REFERENCES wmbs_workflow(id) ON DELETE CASCADE)"""

        self.constraints["03_idx_wmbs_subscription"] = \
          """CREATE INDEX idx_wmbs_subscription_workflow ON wmbs_subscription(workflow) %s""" % tablespaceIndex

        self.create["10wmbs_subscription_validation"] = \
          """CREATE TABLE wmbs_subscription_validation (
             subscription_id INTEGER NOT NULL,
             location_id     INTEGER NOT NULL,
             valid           INTEGER)"""

        self.indexes["01_pk_wmbs_sub_val"] = \
          """ALTER TABLE wmbs_subscription_validation ADD
               (CONSTRAINT wmbs_sub_val_pk PRIMARY KEY (subscription_id, location_id) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_sub_val"] = \
          """ALTER TABLE wmbs_subscription_validation ADD
              (CONSTRAINT fk_sub_val FOREIGN KEY(subscription_id)
                 REFERENCES wmbs_subscription(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_sub_val"] = \
          """ALTER TABLE wmbs_subscription_validation ADD
              (CONSTRAINT fk2_sub_val 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
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_sub_files_acquired"] = \
          """ALTER TABLE wmbs_sub_files_acquired ADD
               (CONSTRAINT wmbs_sub_files_acquired_pk PRIMARY KEY (subscription, fileid) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_sub_files_acquired"] = \
          """ALTER TABLE wmbs_sub_files_acquired ADD
               (CONSTRAINT fk_subsacquired_sub FOREIGN KEY (subscription)
                  REFERENCES wmbs_subscription(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_sub_files_acquired"] = \
          """ALTER TABLE wmbs_sub_files_acquired ADD
               (CONSTRAINT fk_subsacquired_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        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.create["10wmbs_sub_files_available"] = \
          """CREATE TABLE wmbs_sub_files_available (
               subscription INTEGER NOT NULL,
               fileid       INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_sub_files_available"] = \
          """ALTER TABLE wmbs_sub_files_available ADD
               (CONSTRAINT wmbs_sub_files_available_pk PRIMARY KEY (subscription, fileid) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_sub_files_available"] = \
          """ALTER TABLE wmbs_sub_files_available ADD
               (CONSTRAINT fk_subsavailable_sub FOREIGN KEY (subscription)
                  REFERENCES wmbs_subscription(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_sub_files_available"] = \
          """ALTER TABLE wmbs_sub_files_available ADD
               (CONSTRAINT fk_subsavailable_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        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.create["11wmbs_sub_files_failed"] = \
          """CREATE TABLE wmbs_sub_files_failed (
               subscription INTEGER NOT NULL,
               fileid       INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_sub_files_failed"] = \
          """ALTER TABLE wmbs_sub_files_failed ADD
               (CONSTRAINT wmbs_sub_files_failed_pk PRIMARY KEY (subscription, fileid) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_sub_files_failed"] = \
          """ALTER TABLE wmbs_sub_files_failed ADD
               (CONSTRAINT fk_subsfailed_sub FOREIGN KEY (subscription)
                  REFERENCES wmbs_subscription(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_sub_files_failed"] = \
          """ALTER TABLE wmbs_sub_files_failed ADD
               (CONSTRAINT fk_subsfailed_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        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.create["12wmbs_sub_files_complete"] = \
          """CREATE TABLE wmbs_sub_files_complete (
               subscription INTEGER NOT NULL,
               fileid       INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_sub_files_complete"] = \
          """ALTER TABLE wmbs_sub_files_complete ADD
               (CONSTRAINT wmbs_sub_files_complete_pk PRIMARY KEY (subscription, fileid) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_sub_files_complete"] = \
          """ALTER TABLE wmbs_sub_files_complete ADD
               (CONSTRAINT fk_subscomplete_sub FOREIGN KEY (subscription)
                  REFERENCES wmbs_subscription(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_sub_files_complete"] = \
          """ALTER TABLE wmbs_sub_files_complete ADD
               (CONSTRAINT fk_subscomplete_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        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.create["13wmbs_jobgroup"] = \
          """CREATE TABLE wmbs_jobgroup (
               id           INTEGER       NOT NULL,
               subscription INTEGER       NOT NULL,
               guid         VARCHAR(255),
               output       INTEGER,
               last_update  INTEGER       NOT NULL,
               location     INTEGER
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_jobgroup"] = \
          """ALTER TABLE wmbs_jobgroup ADD
               (CONSTRAINT wmbs_jobgroup_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        ##         self.indexes["02_pk_wmbs_jobgroup"] = \
        ##           """ALTER TABLE wmbs_jobgroup ADD
        ##                (CONSTRAINT wmbs_jobgroup_unique1 UNIQUE (output) %s)""" % tablespaceIndex

        self.indexes["03_pk_wmbs_jobgroup"] = \
          """ALTER TABLE wmbs_jobgroup ADD
               (CONSTRAINT wmbs_jobgroup_unique2 UNIQUE (guid) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_jobgroup"] = \
          """ALTER TABLE wmbs_jobgroup ADD
               (CONSTRAINT fk_jobgroup_subscription FOREIGN KEY (subscription)
                  REFERENCES wmbs_subscription(id) ON DELETE CASCADE)"""

        self.constraints["02_fk_wmbs_jobgroup"] = \
          """ALTER TABLE wmbs_jobgroup ADD
               (CONSTRAINT fk_jobgroup_fileset FOREIGN KEY (output)
                  REFERENCES wmbs_fileset(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_sub_jobgroup"] = \
          """CREATE INDEX idx_wmbs_jobgroup_sub ON wmbs_jobgroup(subscription) %s""" % tablespaceIndex

        self.create["14wmbs_job_state"] = \
          """CREATE TABLE wmbs_job_state (
               id   INTEGER      NOT NULL,
               name VARCHAR(100) NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_job_state"] = \
          """ALTER TABLE wmbs_job_state ADD
               (CONSTRAINT wmbs_job_state_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.create["15wmbs_job"] = \
          """CREATE TABLE wmbs_job (
               id           INTEGER       NOT NULL,
               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)
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_job"] = \
          """ALTER TABLE wmbs_job ADD
               (CONSTRAINT wmbs_job_pk PRIMARY KEY (id) %s)""" % tablespaceIndex

        self.indexes["02_pk_wmbs_job"] = \
          """ALTER TABLE wmbs_job ADD
               (CONSTRAINT wmbs_job_uk UNIQUE (name) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_job"] = \
          """ALTER TABLE wmbs_job ADD
               (CONSTRAINT wmbs_job_fk_jobgroup FOREIGN KEY (jobgroup)
                  REFERENCES wmbs_jobgroup(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_job"] = \
          """CREATE INDEX idx_wmbs_job_jobgroup ON wmbs_job(jobgroup) %s""" % tablespaceIndex

        self.constraints["02_fk_wmbs_job"] = \
          """ALTER TABLE wmbs_job ADD
               (CONSTRAINT fk_location FOREIGN KEY (location)
                  REFERENCES wmbs_location(id))"""

        self.constraints["02_idx_wmbs_job"] = \
          """CREATE INDEX idx_wmbs_job_loc ON wmbs_job(location) %s""" % tablespaceIndex

        self.constraints["03_fk_wmbs_job"] = \
          """ALTER TABLE wmbs_job ADD
               (CONSTRAINT fk_state FOREIGN KEY (state)
                  REFERENCES wmbs_job_state(id))"""

        self.constraints["03_idx_wmbs_job"] = \
          """CREATE INDEX idx_wmbs_job_state ON wmbs_job(state) %s""" % tablespaceIndex


        self.create["16wmbs_job_assoc"] = \
          """CREATE TABLE wmbs_job_assoc (
               job    INTEGER NOT NULL,
               fileid INTEGER NOT NULL
               ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_job_assoc"] = \
          """ALTER TABLE wmbs_job_assoc ADD
               (CONSTRAINT wmbs_job_assoc_pk PRIMARY KEY (job, fileid) %s)""" % tablespaceIndex

        self.constraints["01_fk_wmbs_job_assoc"] = \
          """ALTER TABLE wmbs_job_assoc ADD
               (CONSTRAINT fk_jobassoc_job FOREIGN KEY (job)
                  REFERENCES wmbs_job(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_job_assoc"] = \
          """CREATE INDEX idx_wmbs_job_assoc_job ON wmbs_job_assoc(job) %s""" % tablespaceIndex

        self.constraints["02_fk_wmbs_job_assoc"] = \
          """ALTER TABLE wmbs_job_assoc ADD
               (CONSTRAINT fk_jobassoc_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        self.constraints["02_idx_wmbs_job_assoc"] = \
          """CREATE INDEX idx_wmbs_job_assoc_file ON wmbs_job_assoc(fileid) %s""" % tablespaceIndex

        self.create["17wmbs_job_mask"] = \
          """CREATE TABLE wmbs_job_mask (
               job           INTEGER  NOT NULL,
               FirstEvent    INTEGER,
               LastEvent     INTEGER,
               FirstLumi     INTEGER,
               LastLumi      INTEGER,
               FirstRun      INTEGER,
               LastRun       INTEGER,
               inclusivemask CHAR(1) NOT NULL
               ) %s""" % tablespaceTable

        self.constraints["01_fk_wmbs_job_mask"] = \
          """ALTER TABLE wmbs_job_mask ADD
               (CONSTRAINT fk_mask_job FOREIGN KEY (job)
                  REFERENCES wmbs_job(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_job_mask"] = \
          """CREATE INDEX idx_wmbs_job_mask_job ON wmbs_job_mask(job) %s""" % tablespaceIndex

        self.create["18wmbs_checksum_type"] = \
          """CREATE TABLE wmbs_checksum_type (
              id            INTEGER,
              type          VARCHAR(255)
              ) %s""" % tablespaceTable

        self.indexes["01_pk_wmbs_checksum_type"] = \
          """ALTER TABLE wmbs_checksum_type ADD
               (CONSTRAINT wmbs_checksum_type_pk PRIMARY KEY (id) %s)""" % tablespaceIndex


        self.create["19wmbs_file_checksums"] = \
          """CREATE TABLE wmbs_file_checksums (
              fileid        INTEGER,
              typeid        INTEGER,
              cksum         VARCHAR(100)
              ) %s""" % tablespaceTable

        self.indexes["02_uk_wmbs_file_checksums"] = \
          """ALTER TABLE wmbs_file_checksums ADD
               (CONSTRAINT wmbs_file_checksums_uk UNIQUE (fileid, typeid) %s)""" % tablespaceIndex

        self.constraints["02_fk_wmbs_file_checksums"] = \
          """ALTER TABLE wmbs_file_checksums ADD
               (CONSTRAINT fk_filechecksums_cktype FOREIGN KEY (typeid)
                  REFERENCES wmbs_checksum_type(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_file_checksums"] = \
          """CREATE INDEX idx_wmbs_file_checksums_type ON wmbs_file_checksums(typeid) %s""" % tablespaceIndex

        self.constraints["03_fk_wmbs_file_checksums"] = \
          """ALTER TABLE wmbs_file_checksums ADD
               (CONSTRAINT fk_filechecksums_file FOREIGN KEY (fileid)
                  REFERENCES wmbs_file_details(id) ON DELETE CASCADE)"""

        self.constraints["01_idx_wmbs_file_checksums"] = \
          """CREATE INDEX idx_wmbs_file_checksums_file ON wmbs_file_checksums(fileid) %s""" % tablespaceIndex

        self.create["20wmbs_location_pnns"] = \
          """CREATE TABLE wmbs_location_pnns (
               location      INTEGER,
               pnn       VARCHAR(255)
               ) %s""" % tablespaceTable

        self.constraints["01_uq_wmbs_location_pnns"] = \
          """ALTER TABLE wmbs_location_pnns ADD
               (CONSTRAINT wmbs_location_pnns_uq UNIQUE (location, pnn) %s)""" % tablespaceIndex

        self.constraints["02_fk_wmbs_location_pnns"] = \
          """ALTER TABLE wmbs_location_pnns ADD
               (CONSTRAINT wmbs_location_se_fk FOREIGN KEY (location)
                 REFERENCES wmbs_location(id) ON DELETE CASCADE)"""

        for jobState in Transitions().states():
            jobStateQuery = """INSERT INTO wmbs_job_state(id, name) VALUES
                               (wmbs_job_state_SEQ.nextval, '%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 (id, name, priority)
                              VALUES (wmbs_sub_types_SEQ.nextval, '%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 (id, name)
                                    VALUES (wmbs_location_state_SEQ.nextval, '%s')""" % i
            self.inserts["wmbs_location_state_%s" % i] = locationStateQuery

        checksumTypes = ["cksum", "adler32", "md5"]
        for i in checksumTypes:
            checksumTypeQuery = \
               """INSERT INTO wmbs_checksum_type (id, type)
                  VALUES (wmbs_checksum_type_SEQ.nextval, '%s')""" % (i)
            self.inserts["wmbs_checksum_type_%s" % (i)] = checksumTypeQuery

        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