-- $HeadURL$ $LastChangedRevision$

PRAGMA FOREIGN_KEYS = on;

-- define 'true' and 'false' as valid_logicals
CREATE TABLE valid_logicals (
       logical CHAR,
       PRIMARY KEY (logical),
       CHECK (logical = 'true' OR logical == 'false')
);
INSERT INTO valid_logicals VALUES ('true');
INSERT INTO valid_logicals VALUES ('false');

-- define 'pending', 'running' and 'completed', 'cancelled' as allowable job states
CREATE TABLE valid_job_states (
       job_state CHAR,
       PRIMARY KEY (job_state),
       CHECK (job_state = 'pending' OR job_state == 'running' OR job_state == 'completed' OR job_state == 'cancelled')
);
INSERT INTO valid_job_states VALUES ('pending');
INSERT INTO valid_job_states VALUES ('running');
INSERT INTO valid_job_states VALUES ('completed');
INSERT INTO valid_job_states VALUES ('cancelled');

CREATE TABLE resources (
       resource_id CHAR,
       total INT NOT NULL,
       PRIMARY KEY (resource_id)
);

-- jobs (ids assigned automatically), the actual command that is the job, and the job's state
CREATE TABLE jobs (
    job_id           INTEGER,
    job_state        CHAR DEFAULT 'pending',
    priority         INTEGER default 0,
    job_name         CHAR,
    command          CHAR,
    submit_timestamp INTEGER,
    [user]           TEXT,
    pid              INTEGER default 0,
    job_rc           INTEGER default 0,
    PRIMARY KEY (job_id),
    FOREIGN KEY (job_state) REFERENCES valid_job_states(job_state)
);

CREATE TABLE job_resource_requirements (
       job_id INT, 
       resource_id CHAR,
       requirement INT NOT NULL,
       PRIMARY KEY (job_id, resource_id),
       FOREIGN KEY (job_id) REFERENCES jobs(job_id),
       FOREIGN KEY (resource_id) REFERENCES resources(resource_id)
);

CREATE TABLE in_use_resource_instances (
       resource_id CHAR,
       instance_id INT,
       job_id INT NOT NULL,
       PRIMARY KEY (resource_id, instance_id),
       FOREIGN KEY (job_id, resource_id) REFERENCES job_resource_requirements(job_id, resource_id)
);

CREATE VIEW in_use_resource_quantities AS 
       SELECT   resources.resource_id, COALESCE(x.total,0) AS total 
       FROM     resources LEFT OUTER JOIN 
                (SELECT   resource_id, COUNT(*) AS total 
                 FROM     in_use_resource_instances 
                 GROUP BY resource_id) AS x 
       ON       resources.resource_id = x.resource_id;
CREATE VIEW free_resource_quantities AS 
       SELECT   resources.resource_id, resources.total-in_use_resource_quantities.total AS total 
       FROM     in_use_resource_quantities, resources 
       WHERE    in_use_resource_quantities.resource_id = resources.resource_id;
CREATE VIEW runnable_jobs AS 
       SELECT   jobs.job_id 
       FROM     jobs, job_resource_requirements,free_resource_quantities 
       WHERE    jobs.job_id = job_resource_requirements.job_id AND
                job_resource_requirements.resource_id = free_resource_quantities.resource_id AND
                jobs.job_state = 'pending' AND
                requirement <= total 
       GROUP BY jobs.job_id
       HAVING   count(*) == (SELECT COUNT(*) FROM resources)
       ORDER BY priority DESC, jobs.job_id;
CREATE VIEW next_runnable_job as 
       SELECT   * 
       FROM     runnable_jobs 
       LIMIT    1;

CREATE VIEW all_resource_instances AS 
       SELECT resource_id, y.value AS instance_id 
       FROM     resources,
                -- sqlite3 shell has support for GENERATE_SERIES() but 
                -- the DBI/DBD modules do not, or rather Debian still lacks
                -- the DBD::SQLite::BundledExtensions perl module. If it
                -- had support we could use this here:
                -- 
                --     GENERATE_SERIES(1,(SELECT MAX(total) FROM resources),1) AS y 
                -- 
                -- but it doesn't so we need to use this (and the i1000000 table
                -- above):
                (WITH RECURSIVE cnt(value) AS (VALUES(0) UNION ALL SELECT value+1 FROM cnt WHERE value<1000000) SELECT value FROM cnt) as y
       WHERE    y.value <= total;
CREATE VIEW free_resource_instances AS 
       SELECT   all_resource_instances.* 
       FROM     all_resource_instances LEFT OUTER JOIN 
                in_use_resource_instances 
       ON       all_resource_instances.resource_id = in_use_resource_instances.resource_id AND 
                all_resource_instances.instance_id = in_use_resource_instances.instance_id 
       WHERE    in_use_resource_instances.instance_id IS NULL; /* could have used job_id being null */
CREATE VIEW next_runnable_job_resource_instances AS 
       SELECT   b.job_id,b.resource_id,a.instance_id 
       FROM     (SELECT   *, row_number() OVER (partition by resource_id ORDER BY instance_id) AS seqid 
                 FROM     free_resource_instances) AS a, 
                (select * FROM job_resource_requirements) AS b, 
                next_runnable_job AS c 
       WHERE    b.job_id = c.job_id AND
                a.resource_id = b.resource_id AND 
                seqid <= requirement 
       ORDER BY b.job_id,a.resource_id,instance_id;