tables.sqlite 24.5 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
/*
    This is SQLite version of EnsEMBL Hive database schema file.
    
    It has been annotated with @-tags.
    The following command is used to create HTML documentation:
        perl $ENSEMBL_CVS_ROOT_DIR/ensembl/misc-scripts/sql2html.pl -i $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/sql/tables.sqlite \
             -o $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/docs/hive_schema.html -d Hive -sort_headers 0 -sort_tables 0

    Adding the following line into the header of the previous output will make it look prettier (valid in rel.71):
        <link rel="stylesheet" type="text/css" media="all" href="http://static.ensembl.org/minified/f75db6b3a877e4e04329aa1283dec34e.css" />

*/


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

/**
@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
@column module                  the Perl module name that runs this Analysis
@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
*/
41

42
CREATE TABLE analysis_base (
43 44 45 46 47 48 49 50
    analysis_id             INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    logic_name              VARCHAR(40) NOT NULL,
    module                  VARCHAR(255),
    parameters              TEXT,
    resource_class_id       INTEGER NOT NULL,
    failed_job_tolerance    int(10) NOT NULL DEFAULT 0,
    max_retry_count         int(10) default 3 NOT NULL,
    can_be_empty            TINYINT UNSIGNED DEFAULT 0 NOT NULL,
51 52 53
    priority                TINYINT DEFAULT 0 NOT NULL,
    meadow_type             varchar(40) DEFAULT NULL,
    analysis_capacity       int(10) DEFAULT NULL
54
);
55
CREATE UNIQUE INDEX IF NOT EXISTS logic_name_idx ON analysis_base (logic_name);
56 57


58 59
/**
@table  analysis_stats
60

61
@colour #C70C09
62

63 64 65 66
@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
67

68 69 70 71
@column analysis_id             foreign-keyed to the corresponding analysis_base entry
@column batch_size              how many jobs are claimed in one claiming operation before Worker starts executing them
@column hive_capacity           a reciprocal limiter on the number of Workers running at the same time (dependent on Workers of other Analyses)
@column status                  cached state of the Analysis
72

73 74 75 76 77 78 79 80
@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
@column num_required_workers    extra number of Workers of this Analysis needed to execute all READY jobs
81

82 83 84
@column behaviour               whether hive_capacity is set or is dynamically calculated based on timers
@column input_capacity          used to compute hive_capacity in DYNAMIC mode
@column output_capacity         used to compute hive_capacity in DYNAMIC mode
85

86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
@column avg_msec_per_job        weighted average used to compute DYNAMIC hive_capacity
@column avg_input_msec_per_job  weighted average used to compute DYNAMIC hive_capacity
@column avg_run_msec_per_job    weighted average used to compute DYNAMIC hive_capacity
@column avg_output_msec_per_job weighted average used to compute DYNAMIC hive_capacity

@column last_update             when this entry was last updated
@column sync_lock               a binary lock flag to prevent simultaneous updates
*/

CREATE TABLE analysis_stats (
    analysis_id             INTEGER NOT NULL,
    batch_size              int(10) default 1 NOT NULL,
    hive_capacity           int(10) default NULL,
    status                  TEXT DEFAULT 'EMPTY' NOT NULL, /* enum('BLOCKED', 'LOADING', 'SYNCHING', 'EMPTY', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED') DEFAULT 'EMPTY' NOT NULL, */

    total_job_count         int(10) NOT NULL DEFAULT 0,
    semaphored_job_count    int(10) NOT NULL DEFAULT 0,
    ready_job_count         int(10) NOT NULL DEFAULT 0,
    done_job_count          int(10) NOT NULL DEFAULT 0,
    failed_job_count        int(10) NOT NULL DEFAULT 0,
    num_running_workers     int(10) NOT NULL DEFAULT 0,
    num_required_workers    int(10) NOT NULL DEFAULT 0,
108

109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149
    behaviour               TEXT DEFAULT 'STATIC' NOT NULL, /* enum('STATIC', 'DYNAMIC') DEFAULT 'STATIC' NOT NULL, */
    input_capacity          int(10) NOT NULL DEFAULT 4,
    output_capacity         int(10) NOT NULL DEFAULT 4,

    avg_msec_per_job        int(10) default 0 NOT NULL,
    avg_input_msec_per_job  int(10) default 0 NOT NULL,
    avg_run_msec_per_job    int(10) default 0 NOT NULL,
    avg_output_msec_per_job int(10) default 0 NOT NULL,

    last_update             datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sync_lock               int(10) NOT NULL DEFAULT 0,

    PRIMARY KEY (analysis_id)
);


/**
@table  dataflow_rule

@colour #C70C09

@desc Extension of simple_rule design except that goal(to) is now in extended URL format e.g.
	mysql://ensadmin:<pass>@ecs2:3361/compara_hive_test?analysis.logic_name='blast_NCBI34'
	(full network address of an analysis).
	The only requirement is that there are rows in the job, analysis, dataflow_rule,
	and worker tables so that the following join works on the same database 
	   WHERE analysis.analysis_id = dataflow_rule.from_analysis_id 
	   AND   analysis.analysis_id = job.analysis_id
	   AND   analysis.analysis_id = worker.analysis_id
	These are the rules used to create entries in the job table where the
	input_id (control data) is passed from one analysis to the next to define work.
	The analysis table will be extended so that it can specify different read and write
	databases, with the default being the database the analysis is on

@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)
@column to_analysis_url      	foreign key to net distributed analysis logic_name reference
@column input_id_template    	a template for generating a new input_id (not necessarily a hashref) in this dataflow; if undefined is kept original
*/
150

151 152 153 154
CREATE TABLE dataflow_rule (
  dataflow_rule_id    INTEGER PRIMARY KEY AUTOINCREMENT,
  from_analysis_id    INTEGER NOT NULL,
  branch_code         int(10) default 1 NOT NULL,
155
  funnel_dataflow_rule_id INTEGER default NULL,
156
  to_analysis_url     varchar(255) default '' NOT NULL,
157 158
  input_id_template   TEXT DEFAULT NULL
);
159
CREATE UNIQUE INDEX IF NOT EXISTS from_to_branch_template_idx ON dataflow_rule (from_analysis_id, branch_code, funnel_dataflow_rule_id, to_analysis_url, input_id_template);
160 161


162 163 164 165 166 167 168 169 170 171 172 173 174 175 176
/**
@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.

@column condition_analysis_url foreign key to net distributed analysis reference
@column ctrled_analysis_id     foreign key to analysis table analysis_id
*/
177 178 179 180 181 182 183 184

CREATE TABLE analysis_ctrl_rule (
  condition_analysis_url     VARCHAR(255) DEFAULT '' NOT NULL,
  ctrled_analysis_id         UNSIGNED INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS condition_ctrled_idx ON analysis_ctrl_rule (condition_analysis_url, ctrled_analysis_id);


185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257
/**
@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 (
    resource_class_id   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name                varchar(40) NOT NULL

);
CREATE UNIQUE INDEX IF NOT EXISTS resource_class_name_idx ON resource_class (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...
@column parameters          ... the following resource line should be given to it.
*/

CREATE TABLE resource_description (
    resource_class_id     INTEGER NOT NULL,
    meadow_type           varchar(40) NOT NULL,
    parameters            varchar(255) DEFAULT '' NOT NULL,

    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
@column prev_job_id             previous job which created this one (and passed input_id)
@column analysis_id             the analysis_id needed to accomplish this job.
@column input_id                input data passed into Analysis:RunnableDB to control the work
@column worker_id               link to worker table to define which worker claimed this job
@column status                  state the job is in
@column retry_count             number times job had to be reset when worker failed to run it
@column completed               datetime when job was completed
@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
@column semaphore_count         if this count is >0, the job is conditionally blocked (until this count drops to 0 or below). Default=0 means "nothing is blocking me by default".
@column semaphored_job_id       the job_id of job S that is waiting for this job to decrease S's semaphore_count. Default=NULL means "I'm not blocking anything by default".
*/
258 259 260 261 262 263 264

CREATE TABLE job (
  job_id                    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  prev_job_id               INTEGER DEFAULT NULL,  /* the job that created this one using a dataflow rule */
  analysis_id               INTEGER NOT NULL,
  input_id                  char(255) NOT NULL,
  worker_id                 INTEGER DEFAULT NULL,
265
  status                    TEXT DEFAULT 'READY' NOT NULL, /* enum('SEMAPHORED','READY','CLAIMED','COMPILATION','FETCH_INPUT','RUN','WRITE_OUTPUT','DONE','FAILED','PASSED_ON') DEFAULT 'READY' NOT NULL, */
266 267 268 269 270 271 272 273 274
  retry_count               int(10) DEFAULT 0 NOT NULL,
  completed                 datetime DEFAULT NULL,
  runtime_msec              int(10) DEFAULT NULL, 
  query_count               int(10) DEFAULT NULL, 

  semaphore_count           INTEGER DEFAULT 0 NOT NULL,
  semaphored_job_id         INTEGER DEFAULT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS input_id_analysis_id_idx ON job (input_id, analysis_id);
275
CREATE        INDEX IF NOT EXISTS analysis_status_retry_idx ON job (analysis_id, status, retry_count);
276 277 278
CREATE        INDEX IF NOT EXISTS worker_idx ON job (worker_id);


279 280
/**
@table  job_file
281

282
@colour #1D73DA
283

284 285 286 287
@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.
288

289 290 291 292 293 294
@column job_id             foreign key
@column worker_id          link to worker table to define which worker claimed this job
@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
*/
295 296

CREATE TABLE job_file (
297 298 299 300
  job_id                INTEGER NOT NULL,
  retry                 int(10) NOT NULL,
  worker_id             INTEGER NOT NULL,
  stdout_file           varchar(255),
301 302 303
  stderr_file           varchar(255),

  PRIMARY KEY (job_id, retry)
304 305 306 307
);
CREATE        INDEX IF NOT EXISTS worker_idx ON job_file (worker_id);


308 309
/**
@table  accu
310

311 312 313
@colour #1D73DA

@desc   Accumulator for funneled dataflow.
314

315 316 317 318 319 320
@column sending_job_id     semaphoring job in the "box"
@column receiving_job_id   semaphored job 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
*/
321

322 323 324 325 326 327 328 329 330 331 332 333
CREATE TABLE accu (
    sending_job_id          int(10),
    receiving_job_id        int(10) NOT NULL,
    struct_name             varchar(255) NOT NULL,
    key_signature           varchar(255) NOT NULL,
    value                   varchar(255)

);
CREATE        INDEX IF NOT EXISTS sending_idx   ON accu (sending_job_id);
CREATE        INDEX IF NOT EXISTS receiving_idx ON accu (receiving_job_id);


334 335
/**
@table  analysis_data
336

337 338 339 340 341 342 343 344 345 346 347 348 349 350
@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
@column data                text blob which holds the data
*/

CREATE TABLE analysis_data (
  analysis_data_id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  data              longtext
351
);
352
CREATE        INDEX IF NOT EXISTS data_idx ON analysis_data (data);
353 354


355 356
/**
@table  meta
357

358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378
@colour #000000

@desc This table comes from the Ensembl core schema.
	It is created here with the 'IF NOT EXISTS' option to avoid a potential clash
	 if we are dealing with core-hive hybrid that is created in the wrong order.
	At the moment meta table is used
		(1) for compatibility with the Core API ('schema_version'),
		(2) to keep some Hive-specific meta-information ('pipeline_name') and
		(3) to keep pipeline-wide parameters.

@column meta_id			auto-incrementing primary key, not really used per se
@column species_id		always 1, kept for compatibility with the Core API
@column	meta_key		the KEY of KEY-VALUE pairs
@column	meta_value		the VALUE of KEY-VALUE pairs
*/

CREATE TABLE IF NOT EXISTS meta (
  meta_id                     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  species_id                  INT UNSIGNED DEFAULT 1,
  meta_key                    VARCHAR(40) NOT NULL,
  meta_value                  TEXT NOT NULL
379
);
380 381
CREATE UNIQUE INDEX IF NOT EXISTS species_key_value_idx ON meta (species_id, meta_key, meta_value);
CREATE        INDEX IF NOT EXISTS species_value_idx     ON meta (species_id, meta_value);
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 407 408 409 410 411 412 413
/**
@header worker table
@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.

@column worker_id		unique ID of the Worker
@column meadow_type		type of the Meadow it is running on
@column meadow_name		name of the Meadow it is running on (for 'LOCAL' type is the same as host)
@column host			execution host name
@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 analysis_id		Analysis the Worker is specified into
@column work_done		how many jobs the Worker has completed successfully
@column status			current status of the Worker
@column born			when the Worker process was started
@column last_check_in		when the Worker last checked into the database
@column died			if defined, when the Worker died (or its premature death was first detected by GC)
@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
*/
414

415 416 417 418 419 420 421
CREATE TABLE worker (
    worker_id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    meadow_type         varchar(40) NOT NULL,
    meadow_name         varchar(40) NOT NULL,
    host                varchar(40) NOT NULL,
    process_id          varchar(40) NOT NULL,
    resource_class_id   INTEGER DEFAULT NULL,
422

423 424 425 426 427 428 429 430
  analysis_id      INTEGER DEFAULT NULL,
  work_done        int(11) DEFAULT '0' NOT NULL,
  status           TEXT DEFAULT 'READY' NOT NULL, /* enum('SPECIALIZATION','COMPILATION','READY','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','DEAD') DEFAULT 'READY' NOT NULL */
  born             timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_check_in    datetime NOT NULL,
  died             datetime DEFAULT NULL,
  cause_of_death   TEXT DEFAULT NULL, /* enum('NO_ROLE', 'NO_WORK', 'JOB_LIMIT', 'HIVE_OVERLOAD', 'LIFESPAN', 'CONTAMINATED', 'KILLED_BY_USER', 'MEMLIMIT', 'RUNLIMIT', 'SEE_MSG', 'UNKNOWN') DEFAULT NULL */
  log_dir          varchar(80) DEFAULT NULL
431

432 433
);
CREATE        INDEX IF NOT EXISTS analysis_id_status_idx ON worker (analysis_id, status);
434 435


436 437 438 439
/**
@header Logging and monitoring
@colour #F4D20C
*/
440

441 442
/**
@table  log_message
443

444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469
@colour #08DAD8

@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)
@column      worker_id  the 'current' worker
@column           time  when the message was thrown
@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
@column       is_error  binary flag
*/

CREATE TABLE log_message (
  log_message_id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  job_id                    INTEGER DEFAULT NULL,
  worker_id                 INTEGER NOT NULL,
  time                      timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  retry                     int(10) DEFAULT NULL,
  status                    TEXT DEFAULT 'UNKNOWN', /* enum('UNKNOWN','SPECIALIZATION','COMPILATION','READY','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','PASSED_ON') DEFAULT 'UNKNOWN' */
  msg                       TEXT,
  is_error                  BOOLEAN
470
);
471 472 473 474 475 476 477 478
CREATE        INDEX IF NOT EXISTS worker_idx ON log_message (worker_id);
CREATE        INDEX IF NOT EXISTS job_idx ON log_message (job_id);


/**
@table  analysis_stats_monitor

@colour #F4D20C
479

480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509
@desc   A regular timestamped snapshot of the analysis_stats table.

@column time                    when this snapshot was taken

@column analysis_id             foreign-keyed to the corresponding analysis_base entry
@column batch_size              how many jobs are claimed in one claiming operation before Worker starts executing them
@column hive_capacity           a reciprocal limiter on the number of Workers running at the same time (dependent on Workers of other Analyses)
@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
@column num_required_workers    extra number of Workers of this Analysis needed to execute all READY jobs

@column behaviour               whether hive_capacity is set or is dynamically calculated based on timers
@column input_capacity          used to compute hive_capacity in DYNAMIC mode
@column output_capacity         used to compute hive_capacity in DYNAMIC mode

@column avg_msec_per_job        weighted average used to compute DYNAMIC hive_capacity
@column avg_input_msec_per_job  weighted average used to compute DYNAMIC hive_capacity
@column avg_run_msec_per_job    weighted average used to compute DYNAMIC hive_capacity
@column avg_output_msec_per_job weighted average used to compute DYNAMIC hive_capacity

@column last_update             when this entry was last updated
@column sync_lock               a binary lock flag to prevent simultaneous updates
*/
510 511

CREATE TABLE analysis_stats_monitor (
512 513 514 515
    time                    datetime NOT NULL default '0000-00-00 00:00:00',

    analysis_id             INTEGER NOT NULL,
    batch_size              int(10) default 1 NOT NULL,
516
    hive_capacity           int(10) default NULL,
517
    status                  TEXT DEFAULT 'EMPTY' NOT NULL, /* enum('BLOCKED', 'LOADING', 'SYNCHING', 'EMPTY', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED') DEFAULT 'EMPTY' NOT NULL, */
518 519

    total_job_count         int(10) NOT NULL DEFAULT 0,
520
    semaphored_job_count    int(10) NOT NULL DEFAULT 0,
521
    ready_job_count         int(10) NOT NULL DEFAULT 0,
522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537
    done_job_count          int(10) NOT NULL DEFAULT 0,
    failed_job_count        int(10) NOT NULL DEFAULT 0,
    num_running_workers     int(10) default 0 NOT NULL,
    num_required_workers    int(10) NOT NULL DEFAULT 0,

    behaviour               TEXT DEFAULT 'STATIC' NOT NULL, /* enum('STATIC', 'DYNAMIC') DEFAULT 'STATIC' NOT NULL, */
    input_capacity          int(10) default 4 NOT NULL,
    output_capacity         int(10) default 4 NOT NULL,

    avg_msec_per_job        int(10) default 0 NOT NULL,
    avg_input_msec_per_job  int(10) default 0 NOT NULL,
    avg_run_msec_per_job    int(10) default 0 NOT NULL,
    avg_output_msec_per_job int(10) default 0 NOT NULL,

    last_update             datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sync_lock               int(10) default 0 NOT NULL
538 539
);

540 541 542 543 544 545 546 547 548 549 550 551 552 553 554

/**
@table  monitor

@colour #F4D20C

@desc   A regular collated snapshot of the Worker table.

@column time            when this snapshot was taken
@column workers         number of running workers
@column throughput      average numb of completed Jobs per sec. of the hive (this number is calculated using running workers only)
@column per_worker      average numb of completed Jobs per sec. per Worker (this number is calculated using running workers only)
@column analysis        a comma-separated list of analyses running at the time of snapshot

*/
555 556 557 558 559 560 561 562 563 564 565

CREATE TABLE monitor (
  time                  datetime NOT NULL default '0000-00-00 00:00:00',
  workers               int(10) NOT NULL default '0',
  throughput            float default NULL,
  per_worker            float default NULL,
  analysis              varchar(255) default NULL  /* not just one, but a list of logic_names */
);


-- Auto add schema version to database (should be overridden by Compara's table.sql)
566
INSERT OR IGNORE INTO meta (species_id, meta_key, meta_value) VALUES (NULL, 'schema_version', '72');
567