tables.pgsql 31.9 KB
Newer Older
1 2 3 4 5
/*
    This is PostgreSQL version of EnsEMBL Hive database schema file.

    It has been annotated with @-tags.
    The following command is used to create HTML documentation:
Leo Gordon's avatar
Leo Gordon committed
6 7 8 9 10
        perl $ENSEMBL_CVS_ROOT_DIR/ensembl-production/scripts/sql2html.pl \
            -i $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/sql/tables.pgsql -d Hive -intro $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/docs/hive_schema.inc \
            -sort_headers 0 -sort_tables 0 -o $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/docs/hive_schema.html


11 12 13
    Adding the following line into the header of the previous output will make it look prettier:
        <link rel="stylesheet" type="text/css" media="all" href="ehive_doc.css" />

14

15

16 17 18

LICENSE

19
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
Matthieu Muffato's avatar
Matthieu Muffato committed
20
    Copyright [2016-2017] EMBL-European Bioinformatics Institute
21 22 23 24 25 26 27 28 29 30

    Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software distributed under the License
    is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and limitations under the License.

31 32 33 34
CONTACT

    Please subscribe to the Hive mailing list:  http://listserver.ebi.ac.uk/mailman/listinfo/ehive-users  to discuss Hive-related questions or to be notified of our updates

35 36 37 38 39 40 41 42
*/


/**
@header Pipeline structure
@colour #C70C09
*/

Leo Gordon's avatar
Leo Gordon committed
43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
/**
@table  hive_meta

@colour #000000

@desc This table keeps several important hive-specific pipeline-wide key-value pairs
        such as hive_sql_schema_version, hive_use_triggers and hive_pipeline_name.

@column meta_key        the KEY of KEY-VALUE pairs (primary key)
@column meta_value      the VALUE of KEY-VALUE pairs
*/

CREATE TABLE hive_meta (
    meta_key                VARCHAR(255) NOT NULL PRIMARY KEY,
    meta_value              TEXT

);


/**
@table  pipeline_wide_parameters

@colour #000000

@desc This table contains a simple hash between pipeline_wide_parameter names and their values.
      The same data used to live in 'meta' table until both the schema and the API were finally separated from Ensembl Core.

@column param_name      the KEY of KEY-VALUE pairs (primary key)
@column param_value     the VALUE of KEY-VALUE pairs
*/

CREATE TABLE pipeline_wide_parameters (
    param_name              VARCHAR(255) NOT NULL PRIMARY KEY,
    param_value             TEXT

);
CREATE        INDEX ON pipeline_wide_parameters (param_value);


82 83 84 85 86 87 88 89 90 91 92
/**
@table  analysis_base

@colour #C70C09

@desc   Each Analysis is a node of the pipeline diagram.
        It acts both as a "class" to which Jobs belong (and inherit from it certain properties)
        and as a "container" for them (Jobs of an Analysis can be blocking all Jobs of another Analysis).

@column analysis_id             a unique ID that is also a foreign key to most of the other tables
@column logic_name              the name of the Analysis object
Matthieu Muffato's avatar
Matthieu Muffato committed
93
@column module                  the name of the module / package that runs this Analysis
94
@column language                the language of the module, if not Perl
95 96 97 98 99 100 101 102
@column parameters              a stingified hash of parameters common to all jobs of the Analysis
@column resource_class_id       link to the resource_class table
@column failed_job_tolerance    % of tolerated failed Jobs
@column max_retry_count         how many times a job of this Analysis will be retried (unless there is no point)
@column can_be_empty            if TRUE, this Analysis will not be blocking if/while it doesn't have any jobs
@column priority                an Analysis with higher priority will be more likely chosen on Worker's specialization
@column meadow_type             if defined, forces this Analysis to be run only on the given Meadow
@column analysis_capacity       if defined, limits the number of Workers of this particular Analysis that are allowed to run in parallel
103
@column hive_capacity           a reciprocal limiter on the number of Workers running at the same time (dependent on Workers of other Analyses)
104
@column batch_size              how many jobs are claimed in one claiming operation before Worker starts executing them
105 106
@column comment                 human-readable textual description of the analysis
@column tags                    machine-readable list of tags useful for grouping analyses together
107 108 109
*/

CREATE TABLE analysis_base (
Leo Gordon's avatar
Leo Gordon committed
110
    analysis_id             SERIAL PRIMARY KEY,
111
    logic_name              VARCHAR(255) NOT NULL,
112
    module                  VARCHAR(255) NOT NULL,
113
    language                VARCHAR(255),
114
    parameters              TEXT,
Leo Gordon's avatar
Leo Gordon committed
115 116
    resource_class_id       INTEGER     NOT NULL,
    failed_job_tolerance    INTEGER     NOT NULL DEFAULT 0,
117
    max_retry_count         INTEGER,
Leo Gordon's avatar
Leo Gordon committed
118 119
    can_be_empty            SMALLINT    NOT NULL DEFAULT 0,
    priority                SMALLINT    NOT NULL DEFAULT 0,
120
    meadow_type             VARCHAR(255)          DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
121
    analysis_capacity       INTEGER              DEFAULT NULL,
122
    hive_capacity           INTEGER              DEFAULT NULL,
123
    batch_size              INTEGER     NOT NULL DEFAULT 1,
124 125
    comment                 TEXT        NOT NULL DEFAULT '',
    tags                    TEXT        NOT NULL DEFAULT '',
Leo Gordon's avatar
Leo Gordon committed
126

127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
    UNIQUE  (logic_name)
);


/**
@table  analysis_stats

@colour #C70C09

@desc   Parallel table to analysis_base which provides high level statistics on the
        state of an analysis and it's jobs.  Used to provide a fast overview, and to
        provide final approval of 'DONE' which is used by the blocking rules to determine
        when to unblock other analyses.  Also provides

@column analysis_id             foreign-keyed to the corresponding analysis_base entry
@column status                  cached state of the Analysis

@column total_job_count         total number of Jobs of this Analysis
@column semaphored_job_count    number of Jobs of this Analysis that are in SEMAPHORED state
@column ready_job_count         number of Jobs of this Analysis that are in READY state
@column done_job_count          number of Jobs of this Analysis that are in DONE state
@column failed_job_count        number of Jobs of this Analysis that are in FAILED state

@column num_running_workers     number of running Workers of this Analysis

152 153 154 155
@column avg_msec_per_job        weighted average
@column avg_input_msec_per_job  weighted average
@column avg_run_msec_per_job    weighted average
@column avg_output_msec_per_job weighted average
156

157
@column when_updated            when this entry was last updated
158
@column sync_lock               a binary lock flag to prevent simultaneous updates
159
@column is_excluded             set to exclude analysis from beekeeper scheduling
160 161 162 163
*/

CREATE TYPE analysis_status AS ENUM ('BLOCKED', 'LOADING', 'SYNCHING', 'EMPTY', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED');
CREATE TABLE analysis_stats (
Leo Gordon's avatar
Leo Gordon committed
164 165 166 167 168 169 170 171
    analysis_id             INTEGER     NOT NULL,
    status                  analysis_status NOT NULL DEFAULT 'EMPTY',

    total_job_count         INTEGER     NOT NULL DEFAULT 0,
    semaphored_job_count    INTEGER     NOT NULL DEFAULT 0,
    ready_job_count         INTEGER     NOT NULL DEFAULT 0,
    done_job_count          INTEGER     NOT NULL DEFAULT 0,
    failed_job_count        INTEGER     NOT NULL DEFAULT 0,
172

Leo Gordon's avatar
Leo Gordon committed
173 174 175 176 177 178 179
    num_running_workers     INTEGER     NOT NULL DEFAULT 0,

    avg_msec_per_job        INTEGER              DEFAULT NULL,
    avg_input_msec_per_job  INTEGER              DEFAULT NULL,
    avg_run_msec_per_job    INTEGER              DEFAULT NULL,
    avg_output_msec_per_job INTEGER              DEFAULT NULL,

180
    when_updated            TIMESTAMP            DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
181
    sync_lock               SMALLINT    NOT NULL DEFAULT 0,
182
    is_excluded             SMALLINT    NOT NULL DEFAULT 0,
Leo Gordon's avatar
Leo Gordon committed
183 184

    PRIMARY KEY (analysis_id)
185 186 187 188 189 190 191 192
);


/**
@table  dataflow_rule

@colour #C70C09

193 194 195 196
@desc Each entry of this table defines a starting point for dataflow (via from_analysis_id and branch_code)
      to which point a group of dataflow_target entries can be linked. This grouping is used in two ways:
      (1) dataflow_target entries that link into the same dataflow_rule share the same from_analysis, branch_code and funnel_dataflow_rule
      (2) to define the conditions for DEFAULT or ELSE case (via excluding all conditions explicitly listed in the group)
197 198 199 200 201 202 203 204

@column dataflow_rule_id        internal ID
@column from_analysis_id        foreign key to analysis table analysis_id
@column branch_code             branch_code of the fan
@column funnel_dataflow_rule_id dataflow_rule_id of the semaphored funnel (is NULL by default, which means dataflow is not semaphored)
*/

CREATE TABLE dataflow_rule (
Leo Gordon's avatar
Leo Gordon committed
205 206 207
    dataflow_rule_id        SERIAL PRIMARY KEY,
    from_analysis_id        INTEGER     NOT NULL,
    branch_code             INTEGER     NOT NULL DEFAULT 1,
208 209 210 211 212 213 214 215
    funnel_dataflow_rule_id INTEGER              DEFAULT NULL
);


/**
@table  dataflow_target

@colour #C70C09
Leo Gordon's avatar
Leo Gordon committed
216

217 218
@desc This table links specific conditions with the target object (Analysis/Table/Accu) and optional input_id_template.

219
@column dataflow_target_id      internal ID
220 221 222
@column source_dataflow_rule_id foreign key to the dataflow_rule object that defines grouping (see description of dataflow_rule table)
@column on_condition            param-substitutable string evaluated at the moment of dataflow event that defines whether or not this case produces any dataflow; NULL means DEFAULT or ELSE
@column input_id_template       a template for generating a new input_id (not necessarily a hashref) in this dataflow; if undefined is kept original
223
@column extend_param_stack      the boolean value defines whether the newly created jobs will inherit both the parameters and the accu of the prev_job
224 225 226 227
@column to_analysis_url         the URL of the dataflow target object (Analysis/Table/Accu)
*/

CREATE TABLE dataflow_target (
228
    dataflow_target_id      SERIAL PRIMARY KEY,
229 230 231
    source_dataflow_rule_id INTEGER     NOT NULL,
    on_condition            VARCHAR(255)          DEFAULT NULL,
    input_id_template       TEXT                  DEFAULT NULL,
232
    extend_param_stack      SMALLINT     NOT NULL DEFAULT 0,
233 234 235
    to_analysis_url         VARCHAR(255) NOT NULL DEFAULT '',       -- to be renamed 'target_url'

    UNIQUE (source_dataflow_rule_id, on_condition, input_id_template, to_analysis_url)
236 237 238
);


239

240 241 242 243 244 245 246 247 248 249 250 251
/**
@table  analysis_ctrl_rule

@colour #C70C09

@desc   These rules define a higher level of control.
        These rules are used to turn whole anlysis nodes on/off (READY/BLOCKED).
        If any of the condition_analyses are not 'DONE' the ctrled_analysis is set to BLOCKED.
        When all conditions become 'DONE' then ctrled_analysis is set to READY
        The workers switch the analysis.status to 'WORKING' and 'DONE'.
        But any moment if a condition goes false, the analysis is reset to BLOCKED.

252
@column analysis_ctrl_rule_id  internal ID
253 254 255 256 257
@column condition_analysis_url foreign key to net distributed analysis reference
@column ctrled_analysis_id     foreign key to analysis table analysis_id
*/

CREATE TABLE analysis_ctrl_rule (
258
    analysis_ctrl_rule_id   SERIAL PRIMARY KEY,
Leo Gordon's avatar
Leo Gordon committed
259 260
    condition_analysis_url  VARCHAR(255) NOT NULL DEFAULT '',
    ctrled_analysis_id      INTEGER     NOT NULL,
261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282

    UNIQUE (condition_analysis_url, ctrled_analysis_id)
);


/**
@header Resources
@colour #FF7504
*/

/**
@table  resource_class

@colour #FF7504

@desc   Maps between resource_class numeric IDs and unique names.

@column resource_class_id   unique ID of the ResourceClass
@column name                unique name of the ResourceClass
*/

CREATE TABLE resource_class (
Leo Gordon's avatar
Leo Gordon committed
283
    resource_class_id       SERIAL PRIMARY KEY,
284
    name                    VARCHAR(255) NOT NULL,
285 286 287 288 289 290 291 292 293 294 295 296 297 298

    UNIQUE  (name)
);


/**
@table  resource_description

@colour #FF7504

@desc   Maps (ResourceClass, MeadowType) pair to Meadow-specific resource lines.

@column resource_class_id   foreign-keyed to the ResourceClass entry
@column meadow_type         if the Worker is about to be executed on the given Meadow...
299 300
@column submission_cmd_args ... these are the resource arguments (queue, memory,...) to give to the submission command
@column worker_cmd_args     ... and these are the arguments that are given to the worker command being submitted
301 302 303
*/

CREATE TABLE resource_description (
Leo Gordon's avatar
Leo Gordon committed
304
    resource_class_id       INTEGER     NOT NULL,
305
    meadow_type             VARCHAR(255) NOT NULL,
306 307
    submission_cmd_args     VARCHAR(255) NOT NULL DEFAULT '',
    worker_cmd_args         VARCHAR(255) NOT NULL DEFAULT '',
308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328

    PRIMARY KEY(resource_class_id, meadow_type)
);


/**
@header Job-related
@colour #1D73DA
*/

/**
@table  job

@colour #1D73DA

@desc The job is the heart of this system.  It is the kiosk or blackboard
    where workers find things to do and then post work for other works to do.
    These jobs are created prior to work being done, are claimed by workers,
    are updated as the work is done, with a final update on completion.

@column job_id                  autoincrement id
329
@column prev_job_id             previous job which created this one
330 331
@column analysis_id             the analysis_id needed to accomplish this job.
@column input_id                input data passed into Analysis:RunnableDB to control the work
332 333
@column param_id_stack          a CSV of job_ids whose input_ids contribute to the stack of local variables for the job
@column accu_id_stack           a CSV of job_ids whose accu's contribute to the stack of local variables for the job
334
@column role_id                 links to the Role that claimed this job (NULL means it has never been claimed)
335 336
@column status                  state the job is in
@column retry_count             number times job had to be reset when worker failed to run it
337
@column when_completed          when the job was completed
338 339
@column runtime_msec            how long did it take to execute the job (or until the moment it failed)
@column query_count             how many SQL queries were run during this job
340
@column controlled_semaphore_id the dbID of the semaphore that is controlled by this job (and whose counter it will decrement by 1 upon successful completion)
341 342
*/

343
CREATE TYPE job_status AS ENUM ('SEMAPHORED','READY','CLAIMED','COMPILATION','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_HEALTHCHECK','POST_CLEANUP','DONE','FAILED','PASSED_ON');
344
CREATE TABLE job (
Leo Gordon's avatar
Leo Gordon committed
345 346 347
    job_id                  SERIAL PRIMARY KEY,
    prev_job_id             INTEGER              DEFAULT NULL,  -- the job that created this one using a dataflow rule
    analysis_id             INTEGER     NOT NULL,
348 349 350
    input_id                TEXT        NOT NULL,
    param_id_stack          TEXT        NOT NULL DEFAULT '',
    accu_id_stack           TEXT        NOT NULL DEFAULT '',
351
    role_id                 INTEGER              DEFAULT NULL,
352
    status                  job_status  NOT NULL DEFAULT 'READY',
Leo Gordon's avatar
Leo Gordon committed
353
    retry_count             INTEGER     NOT NULL DEFAULT 0,
354
    when_completed          TIMESTAMP            DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
355 356 357
    runtime_msec            INTEGER              DEFAULT NULL,
    query_count             INTEGER              DEFAULT NULL,

358
    controlled_semaphore_id INTEGER              DEFAULT NULL,      -- terminology: fan jobs CONTROL semaphores; funnel jobs or remote semaphores DEPEND ON (local) semaphores
Leo Gordon's avatar
Leo Gordon committed
359

360
    UNIQUE (input_id, param_id_stack, accu_id_stack, analysis_id)   -- to avoid repeating tasks
361 362
);
CREATE INDEX ON job (analysis_id, status, retry_count); -- for claiming jobs
363
CREATE INDEX ON job (role_id, status);                  -- for fetching and releasing claimed jobs
364

365
/*
366 367 368 369 370 371 372 373 374
-- PostgreSQL is lacking INSERT IGNORE, so we need a RULE to silently
-- discard the insertion of duplicated entries in the job table
CREATE OR REPLACE RULE job_table_ignore_duplicate_inserts AS
    ON INSERT TO job
    WHERE EXISTS (
	SELECT 1
	FROM job
	WHERE job.input_id=NEW.input_id AND job.param_id_stack=NEW.param_id_stack AND job.accu_id_stack=NEW.accu_id_stack AND job.analysis_id=NEW.analysis_id)
    DO INSTEAD NOTHING;
375
*/
376

377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406

/**
@table  semaphore

@colour #1D73DA

@desc The semaphore table is our primary inter-job dependency relationship.
        Any job may control up to one semaphore, but the semaphore can be controlled by many jobs.
        This includes remote jobs, so the semaphore keeps two counters - one for local blockers, one for remote ones.
        As soon as both counters reach zero (0 and 0), the semaphore unblocks one dependent job -
        either a local one, or through a chain of dependent remote semaphores.

@column semaphore_id            autoincrement id
@column local_jobs_counter      the number of local jobs that control this semaphore
@column remote_jobs_counter     the number of remote semaphores that control this one
@column dependent_job_id        either NULL or points at a local job to be unblocked when the semaphore opens (exclusive with dependent_semaphore_url)
@column dependent_semaphore_url either NULL or points at a remote semaphore to be decremented when this semaphore opens (exclusive with dependent_job_id)
*/

CREATE TABLE semaphore (
    semaphore_id                SERIAL PRIMARY KEY,
    local_jobs_counter          INTEGER             DEFAULT 0,
    remote_jobs_counter         INTEGER             DEFAULT 0,
    dependent_job_id            INTEGER             DEFAULT NULL,                                   -- Both should never be NULLs at the same time,
    dependent_semaphore_url     VARCHAR(255)        DEFAULT NULL,                                   --  we expect either one or the other to be set.

    UNIQUE (dependent_job_id)                                                                       -- make sure two semaphores do not block the same job
);


407 408 409 410 411 412 413 414 415 416 417
/**
@table  job_file

@colour #1D73DA

@desc   For testing/debugging purposes both STDOUT and STDERR streams of each Job
        can be redirected into a separate log file.
        This table holds filesystem paths to one or both of those files.
        There is max one entry per job_id and retry.

@column job_id             foreign key
418
@column role_id            links to the Role that claimed this job
419 420 421 422 423 424
@column retry              copy of retry_count of job as it was run
@column stdout_file        path to the job's STDOUT log
@column stderr_file        path to the job's STDERR log
*/

CREATE TABLE job_file (
Leo Gordon's avatar
Leo Gordon committed
425 426
    job_id                  INTEGER     NOT NULL,
    retry                   INTEGER     NOT NULL,
427
    role_id                 INTEGER     NOT NULL,
428 429 430 431 432
    stdout_file             VARCHAR(255),
    stderr_file             VARCHAR(255),

    PRIMARY KEY (job_id, retry)
);
433
CREATE INDEX ON job_file (role_id);
434 435 436 437 438 439 440 441 442


/**
@table  accu

@colour #1D73DA

@desc   Accumulator for funneled dataflow.

443 444 445 446 447
@column sending_job_id          semaphoring job in the "box"
@column receiving_semaphore_id  semaphore just outside the "box"
@column struct_name             name of the structured parameter
@column key_signature           locates the part of the structured parameter
@column value                   value of the part
448 449 450 451
*/

CREATE TABLE accu (
    sending_job_id          INTEGER,
452
    receiving_semaphore_id  INTEGER      NOT NULL,
453 454
    struct_name             VARCHAR(255) NOT NULL,
    key_signature           VARCHAR(255) NOT NULL,
455
    value                   TEXT
456
);
Leo Gordon's avatar
Leo Gordon committed
457
CREATE INDEX ON accu (sending_job_id);
458
CREATE INDEX ON accu (receiving_semaphore_id);
459 460 461 462 463 464 465 466 467 468 469 470 471


/**
@table  analysis_data

@colour #1D73DA

@desc   A generic blob-storage hash.
        Currently the only legitimate use of this table is "overflow" of job.input_ids:
        when they grow longer than 254 characters the real data is stored in analysis_data instead,
        and the input_id contains the corresponding analysis_data_id.

@column analysis_data_id    primary id
472
@column md5sum              checksum over the data to quickly detect (potential) collisions
473 474 475 476
@column data                text blob which holds the data
*/

CREATE TABLE analysis_data (
Leo Gordon's avatar
Leo Gordon committed
477
    analysis_data_id        SERIAL PRIMARY KEY,
478 479
    md5sum                  CHAR(32) NOT NULL,
    data                    TEXT     NOT NULL
480
);
481
CREATE INDEX ON analysis_data (md5sum);
482 483 484


/**
485
@header execution tables
486 487 488 489 490 491 492 493 494 495 496 497 498
@colour #24DA06
*/

/**
@table  worker

@colour #24DA06

@desc Entries of this table correspond to Worker objects of the API.
        Workers are created by inserting into this table
        so that there is only one instance of a Worker object in the database.
        As Workers live and do work, they update this table, and when they die they update again.

Leo Gordon's avatar
Leo Gordon committed
499 500
@column worker_id           unique ID of the Worker
@column meadow_type         type of the Meadow it is running on
501 502 503
@column meadow_name         name of the Meadow it is running on (for meadow_type=='LOCAL' it is the same as meadow_host)
@column meadow_host         execution host name
@column meadow_user         scheduling/execution user name (within the Meadow)
Leo Gordon's avatar
Leo Gordon committed
504 505 506 507
@column process_id          identifies the Worker process on the Meadow (for 'LOCAL' is the OS PID)
@column resource_class_id   links to Worker's resource class
@column work_done           how many jobs the Worker has completed successfully
@column status              current status of the Worker
508
@column beekeeper_id        beekeeper that created this worker
509 510 511 512
@column when_born           when the Worker process was started
@column when_checked_in     when the Worker last checked into the database
@column when_seen           when the Worker was last seen by the Meadow
@column when_died           if defined, when the Worker died (or its premature death was first detected by GC)
Leo Gordon's avatar
Leo Gordon committed
513 514
@column cause_of_death      if defined, why did the Worker exit (or why it was killed)
@column log_dir             if defined, a filesystem directory where this Worker's output is logged
515 516
*/

517
CREATE TYPE worker_cod AS ENUM ('NO_ROLE', 'NO_WORK', 'JOB_LIMIT', 'HIVE_OVERLOAD', 'LIFESPAN', 'CONTAMINATED', 'RELOCATED', 'KILLED_BY_USER', 'MEMLIMIT', 'RUNLIMIT', 'SEE_MSG', 'UNKNOWN');
518
CREATE TABLE worker (
Leo Gordon's avatar
Leo Gordon committed
519
    worker_id               SERIAL PRIMARY KEY,
520 521
    meadow_type             VARCHAR(255) NOT NULL,
    meadow_name             VARCHAR(255) NOT NULL,
522 523
    meadow_host             VARCHAR(255) NOT NULL,
    meadow_user             VARCHAR(255)         DEFAULT NULL,
524
    process_id              VARCHAR(255) NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
525
    resource_class_id       INTEGER              DEFAULT NULL,
526 527
    work_done               INTEGER      NOT NULL DEFAULT 0,
    status                  VARCHAR(255) NOT NULL DEFAULT 'READY',  -- expected values: 'SPECIALIZATION','COMPILATION','READY','JOB_LIFECYCLE','DEAD'
528
    beekeeper_id            INTEGER      DEFAULT NULL,
529
    when_born               TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
530 531 532
    when_checked_in         TIMESTAMP            DEFAULT NULL,
    when_seen               TIMESTAMP            DEFAULT NULL,
    when_died               TIMESTAMP            DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
533 534
    cause_of_death          worker_cod           DEFAULT NULL,
    log_dir                 VARCHAR(255)         DEFAULT NULL
535
);
536
CREATE INDEX ON worker (meadow_type, meadow_name, process_id);
537 538


539 540 541 542 543 544 545 546 547 548 549 550
/**
@table beekeeper

@colour #24DA06

@desc   Each row in this table corresponds to a beekeeper process that is running
        or has run on this pipeline.

@column beekeeper_id       unique ID for this beekeeper
@column meadow_host        hostname of machine where beekeeper started
@column meadow_user        username under which this beekeeper ran or is running
@column process_id         pid of the beekeeper
551
@column is_blocked         beekeeper will not submit workers if this flag is set
552
@column cause_of_death     last known status of this beekeeper
553 554 555 556 557 558 559 560
@column sleep_minutes      sleep interval in minutes
@column analyses_pattern   restricting analyses_pattern, if given
@column loop_limit         loop limit if given
@column loop_until         beekeeper's policy for responding to possibly loop-ending events
@column options            all options passed to the beekeeper
@column meadow_signatures  signatures for all meadows this beekeeper can submit to
*/

561
CREATE TYPE beekeeper_cod  AS ENUM ('ANALYSIS_FAILED', 'DISAPPEARED', 'JOB_FAILED', 'LOOP_LIMIT', 'NO_WORK', 'TASK_FAILED');
562 563 564 565 566 567
CREATE TYPE beekeeper_lu   AS ENUM ('ANALYSIS_FAILURE', 'FOREVER', 'JOB_FAILURE', 'NO_WORK');
CREATE TABLE beekeeper (
       beekeeper_id             SERIAL          PRIMARY KEY,
       meadow_host              VARCHAR(255)	NOT NULL,
       meadow_user              VARCHAR(255)    NOT NULL,
       process_id               INTEGER         NOT NULL,
568
       is_blocked               SMALLINT        NOT NULL DEFAULT 0,
569
       cause_of_death           beekeeper_cod   NULL,
570 571 572 573 574 575 576 577 578 579
       sleep_minutes            REAL            NULL,
       analyses_pattern         TEXT            NULL,
       loop_limit               INTEGER         NULL,
       loop_until               beekeeper_lu    NOT NULL,
       options                  TEXT            NULL,
       meadow_signatures        TEXT            NULL
);
CREATE INDEX ON beekeeper (meadow_host, meadow_user, process_id);


580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611
/**
@table  role

@colour #24DA06

@desc Entries of this table correspond to Role objects of the API.
        When a Worker specializes, it acquires a Role,
        which is a temporary link between the Worker and a resource-compatible Analysis.

@column role_id             unique ID of the Role
@column worker_id           the specialized Worker
@column analysis_id         the Analysis into which the Worker specialized
@column when_started        when this Role started
@column when_finished       when this Role finished. NULL may either indicate it is still running or was killed by an external force.
@column attempted_jobs      counter of the number of attempts
@column done_jobs           counter of the number of successful attempts
*/

CREATE TABLE role (
    role_id                 SERIAL PRIMARY KEY,
    worker_id               INTEGER     NOT NULL,
    analysis_id             INTEGER     NOT NULL,
    when_started            TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    when_finished           TIMESTAMP            DEFAULT NULL,
    attempted_jobs          INTEGER     NOT NULL DEFAULT 0,
    done_jobs               INTEGER     NOT NULL DEFAULT 0
);
CREATE        INDEX role_worker_id_idx ON role (worker_id);
CREATE        INDEX role_analysis_id_idx ON role (analysis_id);



612 613 614 615 616
/**
@header Logging and monitoring
@colour #F4D20C
*/

617 618 619 620 621 622
/**
@table  worker_resource_usage

@colour #F4D20C

@desc   A table with post-mortem resource usage statistics of a Worker.
623 624 625
	This table is not automatically populated: you first need to run
	load_resource_usage.pl. Note that some meadows (like LOCAL) do not
	support post-mortem inspection of resource usage
626 627 628 629 630 631

@column          worker_id  links to the worker table
@column        exit_status  meadow-dependent, in case of LSF it's usually 'done' (normal) or 'exit' (abnormal)
@column           mem_megs  how much memory the Worker process used
@column          swap_megs  how much swap the Worker process used
@column        pending_sec  time spent by the process in the queue before it became a Worker
632 633
@column            cpu_sec  cpu time (in seconds) used by the Worker process. It is often lower than the walltime because of time spent in I/O waits, but it can also be higher if the process is multi-threaded
@column       lifespan_sec  walltime (in seconds) used by the Worker process. It is often higher than the sum of its jobs' "runtime_msec" because of the overhead from the Worker itself
634 635 636 637 638 639 640 641
@column   exception_status  meadow-specific flags, in case of LSF it can be 'underrun', 'overrun' or 'idle'
*/

CREATE TABLE worker_resource_usage (
    worker_id               INTEGER         NOT NULL,
    exit_status             VARCHAR(255)    DEFAULT NULL,
    mem_megs                FLOAT           DEFAULT NULL,
    swap_megs               FLOAT           DEFAULT NULL,
642
    pending_sec             FLOAT           DEFAULT NULL,
643
    cpu_sec                 FLOAT           DEFAULT NULL,
644
    lifespan_sec            FLOAT           DEFAULT NULL,
645 646 647 648 649 650
    exception_status        VARCHAR(255)    DEFAULT NULL,

    PRIMARY KEY (worker_id)
);


651 652 653
/**
@table  log_message

654
@colour #F4D20C
655 656 657 658 659 660 661 662

@desc   When a Job or a job-less Worker (job_id=NULL) throws a "die" message
        for any reason, the message is recorded in this table.
        It may or may not indicate that the job was unsuccessful via is_error flag.
        Also $self->warning("...") messages are recorded with is_error=0.

@column log_message_id  an autoincremented primary id of the message
@column         job_id  the id of the job that threw the message (or NULL if it was outside of a message)
663
@column        role_id  the 'current' role
664
@column      worker_id  the 'current' worker
665
@column    beekeeper_id beekeeper that generated this message
666
@column    when_logged  when the message was thrown
667 668 669
@column          retry  retry_count of the job when the message was thrown (or NULL if no job)
@column         status  of the job or worker when the message was thrown
@column            msg  string that contains the message
670
@column  message_class  type_of_message
671 672
*/

673
CREATE TYPE msg_class AS ENUM ('INFO', 'PIPELINE_CAUTION', 'PIPELINE_ERROR', 'WORKER_CAUTION', 'WORKER_ERROR');
674
CREATE TABLE log_message (
Leo Gordon's avatar
Leo Gordon committed
675 676
    log_message_id          SERIAL PRIMARY KEY,
    job_id                  INTEGER              DEFAULT NULL,
677
    role_id                 INTEGER              DEFAULT NULL,
678
    worker_id               INTEGER              DEFAULT NULL,
679
    beekeeper_id            INTEGER              DEFAULT NULL,
680
    when_logged             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
Leo Gordon's avatar
Leo Gordon committed
681
    retry                   INTEGER              DEFAULT NULL,
682
    status                  VARCHAR(255) NOT NULL DEFAULT 'UNKNOWN',
Leo Gordon's avatar
Leo Gordon committed
683
    msg                     TEXT,
684
    message_class           msg_class            NOT NULL DEFAULT 'INFO'
685 686 687
);
CREATE INDEX ON log_message (worker_id);
CREATE INDEX ON log_message (job_id);
688
CREATE INDEX ON log_message (beekeeper_id);
689
CREATE INDEX ON log_message (message_class);
690 691 692 693 694 695 696 697 698


/**
@table  analysis_stats_monitor

@colour #F4D20C

@desc   A regular timestamped snapshot of the analysis_stats table.

699
@column when_logged             when this snapshot was taken
700 701 702 703 704 705 706 707 708 709 710 711

@column analysis_id             foreign-keyed to the corresponding analysis_base entry
@column status                  cached state of the Analysis

@column total_job_count         total number of Jobs of this Analysis
@column semaphored_job_count    number of Jobs of this Analysis that are in SEMAPHORED state
@column ready_job_count         number of Jobs of this Analysis that are in READY state
@column done_job_count          number of Jobs of this Analysis that are in DONE state
@column failed_job_count        number of Jobs of this Analysis that are in FAILED state

@column num_running_workers     number of running Workers of this Analysis

712 713 714 715
@column avg_msec_per_job        weighted average
@column avg_input_msec_per_job  weighted average
@column avg_run_msec_per_job    weighted average
@column avg_output_msec_per_job weighted average
716

717
@column when_updated            when this entry was last updated
718
@column sync_lock               a binary lock flag to prevent simultaneous updates
719
@column is_excluded             set to exclude analysis from beekeeper scheduling
720 721 722
*/

CREATE TABLE analysis_stats_monitor (
723
    when_logged             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
724

Leo Gordon's avatar
Leo Gordon committed
725 726
    analysis_id             INTEGER     NOT NULL,
    status                  analysis_status NOT NULL DEFAULT 'EMPTY',
727

Leo Gordon's avatar
Leo Gordon committed
728 729 730 731 732
    total_job_count         INTEGER     NOT NULL DEFAULT 0,
    semaphored_job_count    INTEGER     NOT NULL DEFAULT 0,
    ready_job_count         INTEGER     NOT NULL DEFAULT 0,
    done_job_count          INTEGER     NOT NULL DEFAULT 0,
    failed_job_count        INTEGER     NOT NULL DEFAULT 0,
733

Leo Gordon's avatar
Leo Gordon committed
734
    num_running_workers     INTEGER     NOT NULL DEFAULT 0,
735

Leo Gordon's avatar
Leo Gordon committed
736 737 738 739
    avg_msec_per_job        INTEGER              DEFAULT NULL,
    avg_input_msec_per_job  INTEGER              DEFAULT NULL,
    avg_run_msec_per_job    INTEGER              DEFAULT NULL,
    avg_output_msec_per_job INTEGER              DEFAULT NULL,
740

741
    when_updated            TIMESTAMP            DEFAULT NULL,
742 743
    sync_lock               SMALLINT    NOT NULL DEFAULT 0,
    is_excluded             SMALLINT    NOT NULL DEFAULT 0
744 745 746

);