DB Schema

Contents

[edit] Header 

-- schema revision: $LastChangedRevision: 358 $
SET FOREIGN_KEY_CHECKS=0;
start transaction;
 
-- Tags_order: Project, Machine, User, Patch, MJConf, FsPSelect, Rep_file_chng, Jobs, Machine_sessions, Test_runs, Benchmark_runs, Files_paths, IRC_robot, Config.

[edit] Tables

[edit] Table user 

CREATE TABLE user (
    user_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key',
    login       VARCHAR(20) NOT NULL,
    passwd      VARCHAR(20) NOT NULL,
    first_name  VARCHAR(255) NOT NULL DEFAULT '',
    last_name   VARCHAR(255) NOT NULL DEFAULT '',
    irc_nick    VARCHAR(25) DEFAULT NULL,
    active      BOOLEAN NOT NULL DEFAULT 1 COMMENT 'Zero means historical data.',
    created     DATETIME NOT NULL COMMENT 'Account creation time.',
    last_login  DATETIME DEFAULT NULL,
    INDEX i_login (login)
) TYPE=InnoDB COMMENT='User info. Tag:User.';

[edit] Table machine (m) 

CREATE TABLE machine (
    machine_id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    name            VARCHAR(20) NOT NULL,
    passwd          VARCHAR(20) NOT NULL,
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.',
    ip              VARCHAR(15) DEFAULT NULL,
    cpuarch         VARCHAR(50) DEFAULT NULL,
    osname          VARCHAR(50) DEFAULT NULL,
    archname        VARCHAR(255) DEFAULT NULL,
    disabled        BOOLEAN NOT NULL DEFAULT 0,
    created         DATETIME NOT NULL,
    prev_machine_id INT UNSIGNED DEFAULT NULL,
    farm_id         INT UNSIGNED DEFAULT NULL,
    CONSTRAINT fk_machine_user_id FOREIGN KEY (user_id) REFERENCES user (user_id),
    CONSTRAINT fk_machine_prev_machine_id FOREIGN KEY (prev_machine_id) REFERENCES machine (machine_id),
    CONSTRAINT fk_machine_farm_id FOREIGN KEY (farm_id) REFERENCES farm (farm_id)
) TYPE=InnoDB COMMENT='Machine (computer or virtual machine) info. Tag:Machine.';

[edit] Table farm 

CREATE TABLE farm (
    farm_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(30) NOT NULL,
    has_same_hw     BOOLEAN NOT NULL DEFAULT 0,
    has_same_sw     BOOLEAN NOT NULL DEFAULT 0,
    `desc`          TEXT DEFAULT NULL COMMENT 'Description. Tag:Machine.'
) TYPE=InnoDB COMMENT='Group of machines with same HW, SW or both configurations working in farm. Tag:Machine.';

[edit] Table project 

CREATE TABLE project (
    project_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    url         VARCHAR(255) NOT NULL,
    `desc`      TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Project. Tag:Project.';

[edit] Table rep 

CREATE TABLE rep (
    rep_id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id      INT UNSIGNED NOT NULL,
    name            VARCHAR(255) NOT NULL,
    path            VARCHAR(255) NOT NULL,
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.',
    active          BOOLEAN NOT NULL DEFAULT 1 COMMENT '1 .. repository is active, 0 .. old (deprecated) repository',
    default_layout  BOOLEAN NOT NULL DEFAULT 1 COMMENT 'Has default Subversion repository layout (trunk, tags, branches).',
    CONSTRAINT fk_rep_project_id FOREIGN KEY (project_id) REFERENCES project (project_id),
    UNIQUE KEY unique_rep_id_path (rep_id, path)
) TYPE=InnoDB COMMENT='Project repository. Each project can has only one active repository. Tag:Project.';

[edit] Table rep_author 

CREATE TABLE rep_author (
    rep_author_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rep_id          INT UNSIGNED NOT NULL,
    rep_login       VARCHAR(255) DEFAULT NULL COMMENT 'Can be null (see Parrot rev 1346 created by cvs2svn).',
    user_id         INT UNSIGNED DEFAULT NULL COMMENT 'Some authors has TapTinder user_id too.',
    INDEX i_rep_id (rep_id),
    INDEX i_rep_login (rep_login),
    INDEX i_user_id (user_id),
    CONSTRAINT fk_user_rep_rep_id FOREIGN KEY (rep_id) REFERENCES rep (rep_id),
    CONSTRAINT fk_user_rep_user_id FOREIGN KEY (user_id) REFERENCES user (user_id)
) TYPE=InnoDB COMMENT='Repository and patch authors. Some authors are also users. Tag:Project.';

[edit] Table rev 

CREATE TABLE rev (
    rev_id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rep_id          INT UNSIGNED NOT NULL COMMENT 'Cache for rev_rep_path.rep_path_id -> rep_path.rep_id.',
    rev_num         INT UNSIGNED NOT NULL,
    author_id       INT UNSIGNED NOT NULL,
    date            DATETIME NOT NULL,
    msg             TEXT,
    INDEX i_rep_id (rep_id),
    INDEX i_rev_num (rev_num),
    CONSTRAINT fk_rev_rep_id FOREIGN KEY (rep_id) REFERENCES rep (rep_id),
    CONSTRAINT fk_rev_rep_author_id FOREIGN KEY (author_id) REFERENCES rep_author (rep_author_id)
) TYPE=InnoDB COMMENT='Revision info. Tag:Project.';

[edit] Table rep_path 

CREATE TABLE rep_path (
    rep_path_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rep_id          INT UNSIGNED NOT NULL,
    path            VARCHAR(255) NOT NULL,
    rev_num_from    INT UNSIGNED NOT NULL,
    rev_num_to      INT UNSIGNED DEFAULT NULL,
    INDEX i_rep_id (rep_id),
    INDEX i_path (path),
    INDEX i_rev_num_from (rev_num_from),
    INDEX i_rev_num_to (rev_num_to),
    CONSTRAINT fk_rep_path_rep_id FOREIGN KEY (rep_id) REFERENCES rep (rep_id)
) TYPE=InnoDB COMMENT='Repository path (trunk/, branch/pdd17pmc/, ...). Tag:Project.';

[edit] Table rev_rep_path 

CREATE TABLE rev_rep_path (
    rep_path_id         INT UNSIGNED NOT NULL,
    rev_id              INT UNSIGNED NOT NULL,
    UNIQUE KEY unique_rep_id_path (rep_path_id, rev_id),
    CONSTRAINT fk_rev_rep_path_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id),
    CONSTRAINT fk_rev_rep_path_rev_id FOREIGN KEY (rev_id) REFERENCES rev (rev_id)
) TYPE=InnoDB COMMENT='Revision info to repository path map (M:N). Tag:Project.';

[edit] Table rep_file 

CREATE TABLE rep_file (
    rep_file_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rep_path_id     INT UNSIGNED NOT NULL,
    sub_path        VARCHAR(255) NOT NULL,
    rev_num_from    INT UNSIGNED NOT NULL COMMENT 'Revision where this file was added.',
    rev_num_to      INT UNSIGNED DEFAULT NULL COMMENT 'Revision number where this file was last seen (file was deleted in revision rev_num_to+1).',
    INDEX i_rep_path_id (rep_path_id),
    INDEX i_rev_num_from (rev_num_from),
    INDEX i_rev_num_to (rev_num_to),
    CONSTRAINT fk_rep_file_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id)
) TYPE=InnoDB COMMENT='Files in repository path. Tag:Project.';

[edit] Table rep_change_type 

CREATE TABLE rep_change_type (
    rep_change_type_id  INT UNSIGNED NOT NULL PRIMARY KEY,
    abbr                VARCHAR(1) NOT NULL,
    `desc`              VARCHAR(10) NOT NULL COMMENT 'Description.',
    INDEX i_abbr (abbr)
) TYPE=InnoDB COMMENT='Change types and info. Tag:Rep_file_chng.';

[edit] Table rep_file_change 

CREATE TABLE rep_file_change (
    rev_id          INT UNSIGNED NOT NULL,
    rev_num         INT UNSIGNED NOT NULL COMMENT 'Cache for rev.rev_num.',
    rep_file_id     INT UNSIGNED NOT NULL,
    change_type_id  INT UNSIGNED NOT NULL,
    INDEX i_rev_id (rev_id),
    INDEX i_rev_num (rev_num),
    INDEX i_rev_id_rep_file_id (rev_id, rep_file_id),
    CONSTRAINT fk_rep_file_change_rev_id FOREIGN KEY (rev_id) REFERENCES rev (rev_id),
    CONSTRAINT fk_rep_file_change_rep_file_id FOREIGN KEY (rep_file_id) REFERENCES rep_file (rep_file_id),
    CONSTRAINT fk_rep_file_change_change_type_id FOREIGN KEY (change_type_id) REFERENCES rep_change_type (rep_change_type_id)
) TYPE=InnoDB COMMENT='Tag:Rep_file_chng.';

[edit] Table rep_file_change_from 

CREATE TABLE rep_file_change_from (
    rev_id              INT UNSIGNED NOT NULL,
    rep_file_id         INT UNSIGNED NOT NULL,
    from_rev_id         INT UNSIGNED DEFAULT NULL,
    from_rep_file_id    INT UNSIGNED DEFAULT NULL,
    INDEX i_rev_id_rep_file_id (rev_id, rep_file_id),
    CONSTRAINT fk_rep_file_change_from_rev_id FOREIGN KEY (rev_id) REFERENCES rev (rev_id),
    CONSTRAINT fk_rep_file_change_from_rep_file_id FOREIGN KEY (rep_file_id) REFERENCES rep_file (rep_file_id),
    CONSTRAINT fk_rep_file_change_from_from_rev_id FOREIGN KEY (from_rev_id) REFERENCES rev (rev_id),
    CONSTRAINT fk_rep_file_change_from_from_rep_file_id FOREIGN KEY (from_rep_file_id) REFERENCES rep_file (rep_file_id)
) TYPE=InnoDB COMMENT='From which revision file was copied. Tag:Rep_file_chng.';

[edit] Table patch 

CREATE TABLE patch (
    patch_id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rep_path_id     INT UNSIGNED NOT NULL,
    rev_num         INT UNSIGNED NOT NULL COMMENT 'Revison number found in patch.',
    rev_num_to      INT UNSIGNED NOT NULL COMMENT 'Cache, server checks if patch apply ok.',
    author_id       INT UNSIGNED NOT NULL,
    date            DATETIME NOT NULL,
    msg             TEXT,
    fsfile_id       INT UNSIGNED NOT NULL,
    new_patch_id    INT UNSIGNED DEFAULT NULL COMMENT 'If not null then patch has newer version.',
    INDEX i_rep_path_id (rep_path_id),
    INDEX i_rev_num (rev_num),
    CONSTRAINT fk_patch_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id),
    CONSTRAINT fk_patch_rep_author_id FOREIGN KEY (author_id) REFERENCES rep_author (rep_author_id),
    CONSTRAINT fk_patch_file_id FOREIGN KEY (fsfile_id) REFERENCES fsfile (fsfile_id),
    CONSTRAINT fk_patch_new_patch_id FOREIGN KEY (new_patch_id) REFERENCES patch (patch_id)
) TYPE=InnoDB COMMENT='Only one file for each patch. Tag:Patch.';

[edit] Table job 

CREATE TABLE job (
    job_id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_min_rev  INT UNSIGNED DEFAULT 1 COMMENT 'Minimal clietn revision number to run this job.',
    priority        INT UNSIGNED NOT NULL DEFAULT 1,
    name            VARCHAR(25),
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Job table can contain one part or more related parts. Tag:Jobs.';

[edit] Table jobp 

CREATE TABLE jobp (
    jobp_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_id          INT UNSIGNED NOT NULL,
    rep_path_id     INT UNSIGNED DEFAULT NULL,
    `order`         INT UNSIGNED NOT NULL,
    name            VARCHAR(25),
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.',
    max_age         INT UNSIGNED DEFAULT NULL COMMENT 'Maximal commit age in hours.',
    depends_on_id   INT UNSIGNED DEFAULT NULL COMMENT 'Depends on jobp.jobp_id. Job part (jobp_id) can not start if jobp.depends_on_id does not finished ok.',
    extends         BOOLEAN NOT NULL DEFAULT 0 COMMENT 'Default 0. 1 if this part extends part defined in depends_on_id.',
    INDEX i_job_id (job_id),
    INDEX i_rep_path_id (rep_path_id),
    CONSTRAINT fk_jobp_job_id FOREIGN KEY (job_id) REFERENCES job (job_id),
    CONSTRAINT fk_jobp_depedns_on_id FOREIGN KEY (depends_on_id) REFERENCES jobp (jobp_id),
    CONSTRAINT fk_jobp_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id)
) TYPE=InnoDB COMMENT='Job part. Tag:Jobs.';

[edit] Table cmd 

CREATE TABLE cmd (
    cmd_id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(25),
    `desc`      TEXT DEFAULT NULL COMMENT 'Description.',
    params      TEXT DEFAULT NULL
) TYPE=InnoDB COMMENT='Possible commands to run on clients. Tag:Jobs.';

Client should known what to run for each cmd_id. E.g. 1 (perlconf) means run 'perl Configure.pl' or 'perl.exe Configure.pl' (under win32). Server shoul know, which params client needs.

[edit] Table jobp_cmd 

CREATE TABLE jobp_cmd (
    jobp_cmd_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    jobp_id         INT UNSIGNED NOT NULL,
    `order`         INT UNSIGNED NOT NULL,
    cmd_id          INT UNSIGNED NOT NULL,
    params          TEXT,
    INDEX i_jobp_id (jobp_id),
    CONSTRAINT fk_jobp_cmd_jobp_id FOREIGN KEY (jobp_id) REFERENCES jobp (jobp_id),
    CONSTRAINT fk_jobp_cmd_cmd_id FOREIGN KEY (cmd_id) REFERENCES cmd (cmd_id)
) TYPE=InnoDB COMMENT='Job part command. Tag:Jobs.';

[edit] Table machine_job_conf 

CREATE TABLE machine_job_conf (
    machine_job_conf_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    machine_id          INT UNSIGNED NOT NULL,
    rep_id              INT UNSIGNED DEFAULT NULL,
    rep_path_id         INT UNSIGNED DEFAULT NULL,
    job_id              INT UNSIGNED DEFAULT NULL,
    priority            INT UNSIGNED NOT NULL DEFAULT 1,
    allow_patches       BOOLEAN NOT NULL DEFAULT 0,
    INDEX i_machine_id (machine_id),
    CONSTRAINT fk_machine_job_conf_machine_id FOREIGN KEY (machine_id) REFERENCES machine (machine_id),
    CONSTRAINT fk_machine_job_conf_rep_id FOREIGN KEY (rep_id) REFERENCES rep (rep_id),
    CONSTRAINT fk_machine_job_conf_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id),
    CONSTRAINT fk_machine_job_conf_job_id FOREIGN KEY (job_id) REFERENCES job (job_id)
) TYPE=InnoDB COMMENT='Machine job configuration. Tag:MJConf.';

Defined rep_id is the most general config. Defined job_id the most specific config.

[edit] Table msession (ms) 

CREATE TABLE msession (
    msession_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    machine_id          INT UNSIGNED NOT NULL,
    client_rev          VARCHAR(21) NOT NULL COMMENT 'Client revision (11, 10:12M, ...).',
    pid                 INT UNSIGNED DEFAULT NULL COMMENT 'System process id on machine.',
    start_time          DATETIME NOT NULL COMMENT 'Time when machine session was started.',
    end_time            DATETIME DEFAULT NULL COMMENT 'Time when machine session ended.',
    abort_reason_id     INT UNSIGNED DEFAULT NULL COMMENT 'FK to msession_abort_reason if exists.',
    INDEX i_machine_id (machine_id),
    CONSTRAINT fk_msession_machine_id FOREIGN KEY (machine_id) REFERENCES machine (machine_id),
    CONSTRAINT fk_msession_abort_reason_id FOREIGN KEY (abort_reason_id) REFERENCES msabort_reason (msabort_reason_id)
) TYPE=InnoDB COMMENT='Machine session. Each machine can run more than one processes at a time. Tag:Machine_sessions.';

[edit] Table msjob 

CREATE TABLE msjob (
    msjob_id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msession_id         INT UNSIGNED NOT NULL,
    job_id              INT UNSIGNED NOT NULL,
    start_time          DATETIME NOT NULL COMMENT 'Time when machine job was created.',
    end_time            DATETIME DEFAULT NULL COMMENT 'Time when machine job ended.',
    pid                 INT UNSIGNED DEFAULT NULL,
    INDEX i_msession_id (msession_id),
    INDEX i_job_id (job_id),
    CONSTRAINT fk_msjob_msession_id FOREIGN KEY (msession_id) REFERENCES msession (msession_id),
    CONSTRAINT fk_msjob_job_id FOREIGN KEY (job_id) REFERENCES job (job_id)
) TYPE=InnoDB COMMENT='Machine session job part. Tag:Machine_sessions.';

Abort reason is determined by msession.

[edit] Table msjobp 

CREATE TABLE msjobp (
    msjobp_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msjob_id        INT UNSIGNED NOT NULL,
    jobp_id         INT UNSIGNED NOT NULL,
    rev_id          INT UNSIGNED NOT NULL,
    patch_id        INT UNSIGNED DEFAULT NULL,
    start_time      DATETIME NOT NULL COMMENT 'Time when machine job part was created.',
    end_time        DATETIME DEFAULT NULL COMMENT 'Time when machine job part ended.',
    INDEX i_msjob_id (msjob_id),
    INDEX i_jobp_id (jobp_id),
    INDEX i_rev_id (rev_id),
    CONSTRAINT fk_msjobp_msjob_id FOREIGN KEY (msjob_id) REFERENCES msjob (msjob_id),
    CONSTRAINT fk_msjobp_jobp_id FOREIGN KEY (jobp_id) REFERENCES jobp (jobp_id),
    CONSTRAINT fk_msjobp_rev_id FOREIGN KEY (rev_id) REFERENCES rev (rev_id),
    CONSTRAINT fk_msjobp_patch_id FOREIGN KEY (patch_id) REFERENCES patch (patch_id)
) TYPE=InnoDB COMMENT='Machine session job part. Tag:Machine_sessions.';

[edit] Table cmd_status 

CREATE TABLE cmd_status (
    cmd_status_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(25),
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Command status. Tag:Machine_sessions.';

Values:

  • 1 .. created -- created in DB, not started yet
  • 2 .. running
  • 3 .. paused -- paused by user
  • 4 .. ok -- finished ok
  • 5 .. interrupted
  • 6 .. error -- finished with error

[edit] Table msjobp_cmd 

CREATE TABLE msjobp_cmd (
    msjobp_cmd_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msjobp_id       INT UNSIGNED NOT NULL,
    jobp_cmd_id     INT UNSIGNED NOT NULL,
    status_id       INT UNSIGNED NOT NULL,
    pid             INT UNSIGNED DEFAULT NULL,
    start_time      DATETIME NOT NULL COMMENT  'Time when machine job part command was created.',
    end_time        DATETIME DEFAULT NULL COMMENT 'Time when machine job part command ended.',
    output_id       INT UNSIGNED DEFAULT NULL COMMENT 'Command output (stderr and stdout, .txt file).',
    outdata_id      INT UNSIGNED DEFAULT NULL COMMENT 'Command data output (one .tar.gz file).',
    INDEX i_msjobp_id (msjobp_id),
    INDEX i_jobp_cmd_id (jobp_cmd_id),
    CONSTRAINT fk_msjobp_cmd_msjobp_id FOREIGN KEY (msjobp_id) REFERENCES msjobp (msjobp_id),
    CONSTRAINT fk_msjobp_cmd_jobp_cmd_id FOREIGN KEY (jobp_cmd_id) REFERENCES jobp_cmd (jobp_cmd_id),
    CONSTRAINT fk_msjobp_cmd_job_status_id FOREIGN KEY (status_id) REFERENCES cmd_status (cmd_status_id),
    CONSTRAINT fk_msjobp_cmd_output_id FOREIGN KEY (output_id) REFERENCES fsfile (fsfile_id),
    CONSTRAINT fk_msjobp_cmd_outdata_id FOREIGN KEY (outdata_id) REFERENCES fsfile (fsfile_id)
) TYPE=InnoDB COMMENT='Machine session job part command. Tag:Machine_sessions.';

[edit] Table mslog 

CREATE TABLE mslog (
    mslog_id                INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msession_id             INT UNSIGNED NOT NULL,
    msstatus_id             INT UNSIGNED NOT NULL COMMENT 'Value os msstatus_id could stay same (waiting for new job) but attempt_number is increased each time.',
    attempt_number          INT UNSIGNED DEFAULT 1,
    change_time             DATETIME NOT NULL COMMENT 'Time when machine change msession state to this status_id.',
    estimated_finish_time   DATETIME DEFAULT NULL COMMENT 'Time when machine will probably change msession status again.',
    INDEX i_msession_id (msession_id),
    CONSTRAINT fk_mslog_msession_id FOREIGN KEY (msession_id) REFERENCES msession (msession_id),
    CONSTRAINT fk_mslog_msstatus_id FOREIGN KEY (msstatus_id) REFERENCES msstatus (msstatus_id)
) TYPE=InnoDB COMMENT='Machine session log. Tag:Machine_sessions.';

Could be part of msession table, but separate table is better for logging (and log cleaning).

[edit] Table msstatus 

CREATE TABLE msstatus (
    msstatus_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(25),
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Machine session status messages. Tag:Machine_sessions.';

Values:

  • 1 .. unknown status
  • 2 .. msession just created
  • 3 .. waiting for new job
  • 4 .. command preparation
  • 5 .. running command
  • 6 .. paused by user
  • 7 .. stop by user
  • 8 .. stop by web server -- see also msabort_reason table
  • 9 .. stop by anything else

[edit] Table msabort_reason 

CREATE TABLE msabort_reason (
    msabort_reason_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(25),
    `desc`              TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Machine session status messages. Tag:Machine_sessions.';

Values:

  • 1 .. unknown reason
  • 2 .. deprecated client revision
  • 3 .. machine was disabled
  • 4 .. bad client behavior
  • 5 .. iterrupted by user

[edit] Table trun_status 

CREATE TABLE trun_status (
    trun_status_id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(25),
    `desc`              TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Test run loading status. Tag:Test_runs.';

[edit] Table trun 

CREATE TABLE trun (
    trun_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msjobp_cmd_id   INT UNSIGNED NOT NULL,
    trun_status_id  INT UNSIGNED NOT NULL,
    parse_errors    INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    not_seen        INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    failed          INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    todo            INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    skip            INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    bonus           INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    ok              INT UNSIGNED NOT NULL COMMENT 'Table ttest sum cache.',
    INDEX i_msjobp_cmd_id (msjobp_cmd_id),
    CONSTRAINT fk_trun_trun_status_id FOREIGN KEY (trun_status_id) REFERENCES trun_status (trun_status_id),
    CONSTRAINT fk_trun_msjobp_cmd_id FOREIGN KEY (msjobp_cmd_id) REFERENCES msjobp_cmd (msjobp_cmd_id)
) TYPE=InnoDB COMMENT='Test run info and test run statistics (cache). Tag:Test_runs.';

[edit] Table tskipall_msg 

CREATE TABLE tskipall_msg (
    tskipall_msg_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msg                 TEXT,
    hash                CHAR(30) NOT NULL,
    INDEX i_hash (hash)
) TYPE=InnoDB COMMENT='Reasons to skipp all tests. Tag:Test_runs.';

[edit] Table tfile 

CREATE TABLE tfile (
    tfile_id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    trun_id             INT UNSIGNED NOT NULL,
    rep_file_id         INT UNSIGNED NOT NULL,
    all_passed          BOOLEAN NOT NULL DEFAULT 0 COMMENT 'In Harness::Straps max == seen == ok.',
    tskipall_msg_id     INT UNSIGNED DEFAULT NULL,
    hang                BOOLEAN DEFAULT 0 COMMENT 'One if test hang and was killed.',
    INDEX i_trun_id (trun_id),
    CONSTRAINT fk_tfile_trun_id FOREIGN KEY (trun_id) REFERENCES trun (trun_id),
    CONSTRAINT fk_tfile_rep_file_id FOREIGN KEY (rep_file_id) REFERENCES rep_file (rep_file_id),
    CONSTRAINT fk_tfile_tskipall_msg_id FOREIGN KEY (tskipall_msg_id) REFERENCES tskipall_msg (tskipall_msg_id)
) TYPE=InnoDB COMMENT='Test file summary results. Summary result for all tests in file. All_passed is shortcup to data test. Tag:Test_runs.';

[edit] Table trest 

CREATE TABLE trest (
    trest_id    INT UNSIGNED NOT NULL PRIMARY KEY,
    name        VARCHAR(25),
    `desc`      TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='Test result info. Tag:Test_runs.';

Values:

  • 1 .. not seen
  • 2 .. failed
  • 3 .. unknown
  • 4 .. todo
  • 5 .. bonus -- unexpectedly succeeded
  • 6 .. skip
  • 7 .. ok

[edit] Table rep_test 

CREATE TABLE rep_test (
    rep_test_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rep_file_id         INT UNSIGNED NOT NULL,
    number              INT UNSIGNED NOT NULL COMMENT 'Test number in rep_file.',
    name                VARCHAR(255) NOT NULL COMMENT 'First inserted name of the test (if any and not empty).',
    has_another_name    BOOLEAN NOT NULL DEFAULT 0 COMMENT 'One if another name was found.',
    INDEX i_rep_file_id (rep_file_id),
    INDEX i_rep_file_id_number (rep_file_id, number),
    CONSTRAINT fk_rep_test_rep_file_id FOREIGN KEY (rep_file_id) REFERENCES rep_file (rep_file_id)
) TYPE=InnoDB COMMENT='Number and name of the test. Each rep_file can contains many tests. Rep file gets new id after each file change. Tag:Test_runs.';

[edit] Table ttest 

CREATE TABLE ttest (
    ttest_id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    trun_id         INT UNSIGNED NOT NULL,
    rep_test_id     INT UNSIGNED NOT NULL,
    trest_id        INT UNSIGNED NOT NULL,
    INDEX i_rep_test_id (rep_test_id),
    INDEX i_trun_id (trun_id),
    INDEX i_trest_id (trest_id),
    CONSTRAINT fk_ttest_trun_id FOREIGN KEY (trun_id) REFERENCES trun (trun_id),
    CONSTRAINT fk_ttest_rep_test_id FOREIGN KEY (rep_test_id) REFERENCES rep_test (rep_test_id),
    CONSTRAINT fk_ttest_trest_id FOREIGN KEY (trest_id) REFERENCES trest (trest_id)
) TYPE=InnoDB COMMENT='Test results for each test (test case, subtest) submitted in trun. Tag:Test_runs.';

[edit] Table tdiag_msg 

CREATE TABLE tdiag_msg (
    tdiag_msg_id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ttest_id        INT UNSIGNED NOT NULL,
    msg             TEXT,
    hash            CHAR(30) NOT NULL,
    INDEX i_ttest_id (ttest_id),
    INDEX i_hash (hash),
    CONSTRAINT fk_tdiag_msg_ttest_id FOREIGN KEY (ttest_id) REFERENCES ttest (ttest_id)
) TYPE=InnoDB COMMENT='Diagnostic messages. Tag:Test_runs.';

[edit] Table brun_conf 

CREATE TABLE brun_conf (
    brun_conf_id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    hash            CHAR(30) NOT NULL,
    args            VARCHAR(255) DEFAULT NULL,
    alias_conf_id   INT UNSIGNED DEFAULT NULL COMMENT 'Another conf with the same semantics, but different syntax.',
    INDEX i_hash (hash),
    CONSTRAINT fk_brun_conf_alias_conf_id FOREIGN KEY (alias_conf_id) REFERENCES brun_conf (brun_conf_id)
) TYPE=InnoDB COMMENT='Benchmark run. Tag:Benchmark_runs.';

[edit] Table brun 

CREATE TABLE brun (
    brun_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msjobp_cmd_id   INT UNSIGNED NOT NULL,
    conf_id         INT UNSIGNED NOT NULL,
    INDEX i_msjobp_cmd_id (msjobp_cmd_id),
    INDEX i_conf_id (conf_id),
    CONSTRAINT fk_brun_msjobp_cmd_id FOREIGN KEY (msjobp_cmd_id) REFERENCES msjobp_cmd (msjobp_cmd_id),
    CONSTRAINT fk_brun_conf_id FOREIGN KEY (conf_id) REFERENCES brun_conf (brun_conf_id)
) TYPE=InnoDB COMMENT='Benchmark runs. Tag:Benchmark_runs.';

[edit] Table bfile 

CREATE TABLE bfile (
    bfile_id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    brun_id             INT UNSIGNED NOT NULL,
    rep_file_id         INT UNSIGNED NOT NULL,
    run_time            INT UNSIGNED DEFAULT NULL,
    hang                BOOLEAN DEFAULT 0 COMMENT 'If test hang and was killed.',
    INDEX i_brun_id (brun_id),
    CONSTRAINT fk_bfile_brun_id FOREIGN KEY (brun_id) REFERENCES brun (brun_id),
    CONSTRAINT fk_bfile_rep_file_id FOREIGN KEY (rep_file_id) REFERENCES rep_file (rep_file_id)
) TYPE=InnoDB COMMENT='Bench file results. Tag:Benchmark_runs.';

[edit] Table fspath 

CREATE TABLE fspath (
    fspath_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    path            VARCHAR(1023) COMMENT 'Full path from root, e.g. /tmp/taptinder/patches/parrot-01/.',
    web_path        VARCHAR(255),
    public          BOOLEAN DEFAULT 0,
    created         DATETIME NOT NULL,
    deleted         DATETIME DEFAULT NULL,
    name            VARCHAR(25),
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='List of directories with files. Tag:Files_paths.';

[edit] Table fsfile 

CREATE TABLE fsfile (
    fsfile_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fspath_id       INT UNSIGNED NOT NULL,
    name            VARCHAR(255),
    size            INT UNSIGNED NOT NULL COMMENT 'File size in bytes.',
    created         DATETIME NOT NULL,
    deleted         DATETIME DEFAULT NULL,
    INDEX i_fspath_id (fspath_id),
    CONSTRAINT fk_fsfile_fspath_id FOREIGN KEY (fspath_id) REFERENCES fspath (fspath_id)
) TYPE=InnoDB COMMENT='List of saved files. Tag:Files_paths.';

[edit] Table fsfile_ext 

CREATE TABLE fsfile_ext (
    fsfile_ext_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    archive_id      INT UNSIGNED NOT NULL,
    org_name        VARCHAR(255) COMMENT 'Orginal file name inside archive.',
    fsfile_id       INT UNSIGNED NOT NULL,
    INDEX i_archive_id_fsfile_id (archive_id, fsfile_id),
    CONSTRAINT fk_fsfile_ext_archive_id FOREIGN KEY (archive_id) REFERENCES fsfile (fsfile_id),
    CONSTRAINT fk_fsfile_ext_fsfile_id FOREIGN KEY (fsfile_id) REFERENCES fsfile (fsfile_id)
) TYPE=InnoDB COMMENT='Files extracted from another files (archives). Tag:Files_paths.';

[edit] Table fsfile_type 

CREATE TABLE fsfile_type (
    fsfile_type_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(25),
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='List of file types. Used to select proper path. Tag:FsPSelect.';

[edit] Table fspath_select 

CREATE TABLE fspath_select (
    fspath_select_id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fsfile_type_id      INT UNSIGNED NOT NULL,
    rep_path_id         INT UNSIGNED DEFAULT NULL,
    fspath_id           INT UNSIGNED NOT NULL,
    INDEX i_fsfile_type_id_rep_path_id (fsfile_type_id,rep_path_id),
    CONSTRAINT fk_fspath_select_fsfile_type_id FOREIGN KEY (fsfile_type_id) REFERENCES fsfile_type (fsfile_type_id),
    CONSTRAINT fk_fspath_select_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id),
    CONSTRAINT fk_fspath_select_fspath_id FOREIGN KEY (fspath_id) REFERENCES fspath (fspath_id)
) TYPE=InnoDB COMMENT='Default fsfile_path to save files for each rep_path and file_type. Tag:FsPSelect.';

[edit] Table param_type 

CREATE TABLE param_type (
    param_type_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(20) NOT NULL,
    `desc`          TEXT DEFAULT NULL COMMENT 'Description.',
    INDEX i_name (name)
) TYPE=InnoDB COMMENT='Types of parameters. Tag:Config.';

[edit] Table param 

CREATE TABLE param (
    param_id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    param_type_id   INT UNSIGNED NOT NULL,
    value           VARCHAR(255) DEFAULT NULL,
    INDEX i_name (param_type_id),
    CONSTRAINT fk_param_param_type_id FOREIGN KEY (param_type_id) REFERENCES param_type (param_type_id)
) TYPE=InnoDB COMMENT='Parameters values. Tag:Config.';

[edit] Table ibot 

CREATE TABLE ibot (
    ibot_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nick            VARCHAR(50) NOT NULL,
    full_name       VARCHAR(255) NOT NULL,
    server          VARCHAR(50) NOT NULL,
    port            INT UNSIGNED NOT NULL,
    operator_id     INT UNSIGNED NOT NULL COMMENT 'User operating the robot.',
    CONSTRAINT fk_ibot_operator_id FOREIGN KEY (operator_id) REFERENCES user (user_id)
) TYPE=InnoDB COMMENT='IRC bots. Tag:IRC_robot.';

[edit] Table ichannel 

CREATE TABLE ichannel (
    ichannel_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(50) NOT NULL
) TYPE=InnoDB COMMENT='IRC channel. Tag:IRC_robot.';

[edit] Table ireport_type_id 

CREATE TABLE ireport_type (
    ireport_type_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(15) NOT NULL,
    `desc`              TEXT DEFAULT NULL COMMENT 'Description.'
) TYPE=InnoDB COMMENT='IRC robot report types. Tag:IRC_robot.';

[edit] Table ichannel_conf 

CREATE TABLE ichannel_conf (
    ichannel_conf_id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ibot_id             INT UNSIGNED NOT NULL,
    ichannel_id         INT UNSIGNED NOT NULL,
    ireport_type_id     INT UNSIGNED NOT NULL,
    errors_only         BOOLEAN NOT NULL DEFAULT 1,
    jobp_cmd_id         INT UNSIGNED DEFAULT NULL,
    max_age             INT UNSIGNED DEFAULT NULL COMMENT 'Maximal commit age in hours.',
    CONSTRAINT fk_ichannel_conf_ibot_id FOREIGN KEY (ibot_id) REFERENCES ibot (ibot_id),
    CONSTRAINT fk_ichannel_conf_ichannel_id FOREIGN KEY (ichannel_id) REFERENCES ichannel (ichannel_id),
    CONSTRAINT fk_ichannel_conf_ireport_type_id FOREIGN KEY (ireport_type_id) REFERENCES ireport_type (ireport_type_id),
    CONSTRAINT fk_ichannel_conf_jobp_cmd_id FOREIGN KEY (jobp_cmd_id) REFERENCES jobp_cmd (jobp_cmd_id)
) TYPE=InnoDB COMMENT='IRC robot parameter values. Tag:IRC_robot.';

[edit] Table ibot_log 

CREATE TABLE ibot_log (
    ibot_log_id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ibot_id             INT UNSIGNED NOT NULL,
    ichannel_conf_id    INT UNSIGNED NOT NULL,
    rep_path_id         INT UNSIGNED NOT NULL,
    rev_id              INT UNSIGNED NOT NULL,
    CONSTRAINT fk_ibot_log_ibot_id FOREIGN KEY (ibot_id) REFERENCES ibot (ibot_id),
    CONSTRAINT fk_ibot_log_ichannel_conf_id FOREIGN KEY (ichannel_conf_id) REFERENCES ichannel_conf (ichannel_conf_id),
    CONSTRAINT fk_ibot_log_rep_path_id FOREIGN KEY (rep_path_id) REFERENCES rep_path (rep_path_id),
    CONSTRAINT fk_ibot_log_rev_id FOREIGN KEY (rev_id) REFERENCES rev (rev_id)
) TYPE=InnoDB COMMENT='IRC bot log. Tag:IRC_robot.';


[edit] Footer 

commit;

[edit] Repository changes

  • rev_num_from = 100, rev_num_to = 100 -> file inserted in rev 100 and deleted in 101
  • rev_num_to = null - file exists in "max(rev_num) for rep_id"

[edit] A .. added (copied from)

  • insert into rep_file, rev_num_from=rev_num, rev_num_to=null
  • insert into rep_file_change
  • insert into rep_file_change_from

[edit] M .. modified

  • insert into rep_file_change

[edit] D .. deleted

  • change rev_num_to

[edit] R .. replacing

  • change rev_num_to to rev_num-1 for prev rep_file
  • insert into rep_file, rev_num_from=rev_num, rev_num_to=null
  • insert into rep_file_change
  • insert into rep_file_change_from
Retrieved from "http://dev.taptinder.org/wiki/DB_Schema"
Personal tools
Language