lcmaps-plugins-jobrep-admin 1.5.6-1build1 / usr / share / jobrepository / sql / jobrep-create-basic.sql

create database jobrepository;

use jobrepository;


/* Generic */


CREATE TABLE certificates (
   cert_id         INT                   NOT NULL AUTO_INCREMENT,
   subject         VARCHAR(255)          NOT NULL,
   issuer          VARCHAR(255)          NOT NULL,
   purpose         INT                   NOT NULL,
   serialnr        VARCHAR(255)          NOT NULL,
   valid_from      DATETIME              NULL,
   valid_until     DATETIME              NULL,

   PRIMARY KEY (cert_id),
   UNIQUE (subject, issuer, serialnr),
   INDEX (subject)
)
TYPE=InnoDB;


CREATE TABLE users (
   user_id         INT                   NOT NULL AUTO_INCREMENT,
   cert_id         INT                   NOT NULL, 

   PRIMARY KEY (user_id),
   UNIQUE (cert_id),
   FOREIGN KEY (cert_id)  REFERENCES certificates (cert_id)
           ON DELETE CASCADE
)
TYPE=InnoDB;

CREATE TABLE voms_fqans (
   voms_fqan_id    INT                   NOT NULL AUTO_INCREMENT,
   fqan            VARCHAR(255)          NOT NULL,

   PRIMARY KEY (voms_fqan_id),
   unique(fqan)
)
TYPE=InnoDB;


CREATE TABLE unix_uids (
   unix_uid_id       INT                  NOT NULL AUTO_INCREMENT,
   uid               INT                  NOT NULL,
   uid_name          VARCHAR(255)         NULL,
   PRIMARY KEY (unix_uid_id),
   INDEX (uid),
   UNIQUE (uid, uid_name)
)
TYPE=InnoDB;


/* Note: must add the primary_gid note in it's new N-M use table */
CREATE TABLE unix_gids (
   unix_gid_id       INT                  NOT NULL AUTO_INCREMENT,
   gid               INT                  NOT NULL,
   gid_name          VARCHAR(255)         NULL,

   PRIMARY KEY (unix_gid_id),
   INDEX(gid),
   UNIQUE (gid, gid_name)
)
TYPE=InnoDB;

CREATE TABLE unix_uid_voms_fqans (
   unix_uid_voms_fqan_id INT              NOT NULL AUTO_INCREMENT,
   registration_datetime DATETIME         NOT NULL,
   unix_uid_id           INT              NOT NULL,
   voms_fqan_id          INT              NOT NULL,

   PRIMARY KEY (unix_uid_voms_fqan_id),
   UNIQUE (unix_uid_id, voms_fqan_id)
)
TYPE=InnoDB;

CREATE TABLE unix_gid_voms_fqans (
   unix_gid_voms_fqan_id INT              NOT NULL AUTO_INCREMENT,
   registration_datetime DATETIME         NOT NULL,
   unix_gid_id           INT              NOT NULL,
   voms_fqan_id          INT              NOT NULL,
   is_primary            INT              NOT NULL,

   PRIMARY KEY (unix_gid_voms_fqan_id),
   UNIQUE (unix_gid_id, voms_fqan_id, is_primary)
)
TYPE=InnoDB;


CREATE TABLE credential_sources (
   credential_source_id  INT              NOT NULL,
   credential_source     VARCHAR(255)     NOT NULL,

   PRIMARY KEY (credential_source_id),
   UNIQUE (credential_source)
)
TYPE=InnoDB;

CREATE TABLE effective_credentials (
   eff_cred_id           INT              NOT NULL AUTO_INCREMENT,
   registration_datetime DATETIME         NOT NULL,
   credential_source_id  INT              NULL,

   PRIMARY KEY (eff_cred_id),
   FOREIGN KEY (credential_source_id) REFERENCES credential_sources (credential_source_id)
           ON DELETE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE compute_jobs (
   eff_cred_id           INT              NOT NULL,
   gatekeeper_jm_id      VARCHAR(255)     NOT NULL,

   PRIMARY KEY (eff_cred_id, gatekeeper_jm_id)
)
TYPE=InnoDB;

CREATE TABLE effective_credentials_unix_uid_voms (
   eff_cred_id           INT              NOT NULL,
   unix_uid_voms_fqan_id INT              NOT NULL,

   PRIMARY KEY (eff_cred_id, unix_uid_voms_fqan_id)
)
TYPE=InnoDB;

CREATE TABLE effective_credentials_unix_gid_voms (
   eff_cred_id           INT              NOT NULL,
   unix_gid_voms_fqan_id INT              NOT NULL,

   PRIMARY KEY (eff_cred_id, unix_gid_voms_fqan_id)
)
TYPE=InnoDB;

CREATE TABLE effective_credential_users (
   eff_cred_id           INT              NOT NULL,
   user_id               INT              NOT NULL,

   PRIMARY KEY (eff_cred_id, user_id)
)
TYPE=InnoDB;

CREATE TABLE effective_credential_unix_uids (
   eff_cred_id           INT              NOT NULL,
   unix_uid_id           INT              NOT NULL,

   PRIMARY KEY (eff_cred_id, unix_uid_id)
)
TYPE=InnoDB;

CREATE TABLE effective_credential_unix_gids (
   eff_cred_id           INT              NOT NULL,
   unix_gid_id           INT              NOT NULL,
   is_primary            INT              NOT NULL,

   PRIMARY KEY (eff_cred_id, unix_gid_id, is_primary)
)
TYPE=InnoDB;


CREATE TABLE voms_servers (
   server_id       INT                   NOT NULL AUTO_INCREMENT,
   server_dn       VARCHAR(255)          NOT NULL,
   server_ca       VARCHAR(255)          NULL,

   PRIMARY KEY (server_id)
)
TYPE=InnoDB;


CREATE TABLE voms_attributes (
   voms_attribute_id  INT                NOT NULL AUTO_INCREMENT,
   valid_from         DATETIME           NULL,
   valid_until        DATETIME           NULL,
   voms_fqan_id       INT                NOT NULL,
   server_id          INT                NULL,
   user_id            INT                NOT NULL,

   PRIMARY KEY (voms_attribute_id),
   INDEX (voms_fqan_id),
   FOREIGN KEY (voms_fqan_id) REFERENCES voms_fqans (voms_fqan_id)
           ON DELETE NO ACTION
           ON UPDATE CASCADE,
   FOREIGN KEY (server_id) REFERENCES voms_servers (server_id)
           ON DELETE NO ACTION
           ON UPDATE CASCADE,
   INDEX (user_id),
   FOREIGN KEY (user_id) REFERENCES users (user_id)
           ON DELETE NO ACTION
           ON UPDATE CASCADE
)
TYPE=InnoDB;


/*

  grant select                  on JobRepository.* to jobrep_script@'%.nikhef.nl' IDENTIFIED BY 'jobrep_script';
  grant insert                  on JobRepository.jobstatus to jobrep_script@'%.nikhef.nl' IDENTIFIED BY 'jobrep_script';
  grant select, insert, update  on JobRepository.* to lcmaps_jobrep@'%.nikhef.nl' IDENTIFIED BY 'jobrep_lcmaps';

*/