-- $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');

-- CREATE a list of numbers (useful to convert '8' into '1', '1', '1', ...)
CREATE VIEW i1000 AS
    WITH RECURSIVE cnt(i) AS (
        VALUES(0) 
    UNION ALL 
        SELECT i+1 FROM cnt WHERE i<1000
    )
    SELECT i FROM cnt;

-- resource types and quantities available and whether resources is 'affinous'
CREATE TABLE resources (
    resource_id CHAR,
    quantity_total INTEGER,
    affinous CHAR,
    PRIMARY KEY (resource_id),
    FOREIGN KEY (affinous) REFERENCES valid_logicals(logical)
);

-- convert affinous resources into a list (i.e. 8 cpus becomes cpu#0, cpu#1, ... cpu#7)
-- non-affinous resources are simply added to the list as a union. The actual conversion
-- is done in the code, immediately after the resource specification has been unpacked
-- and loaded in to the resources table.
CREATE TABLE resource_pools (
    resource_id CHAR,
    resource_pool_id INTEGER,
    quantity_total INTEGER,
    resource_pool_priority INTEGER DEFAULT 0,
    -- quantity_used INTEGER DEFAULT 0,
    PRIMARY KEY (resource_id, resource_pool_id),
    FOREIGN KEY (resource_id) REFERENCES resources(resource_id)
);

-- define 'pending', 'running' and 'completed' 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')
);
INSERT INTO valid_job_states VALUES ('pending');
INSERT INTO valid_job_states VALUES ('running');
INSERT INTO valid_job_states VALUES ('completed');

-- 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,
    command       CHAR,
    pid           INTEGER default 0,
    exit_code     INTEGER default 0,
    PRIMARY KEY (job_id),
    FOREIGN KEY (job_state) REFERENCES valid_job_states(job_state)
);

-- how much of each resource does each pending job require?
CREATE TABLE pending_job_resources (
    job_id CHAR,
    resource_id CHAR,
    quantity_wanted INTEGER,
    PRIMARY KEY (job_id, resource_id),
    FOREIGN KEY (job_id) REFERENCES jobs(job_id),
    FOREIGN KEY (resource_id) REFERENCES resources(resource_id)
);

CREATE TRIGGER quantity_wanted_less_than_quantity_total 
    BEFORE INSERT ON pending_job_resources 
    WHEN (new.quantity_wanted > (SELECT resources.quantity_total FROM resources WHERE resources.resource_id == NEW.resource_id)) 
    BEGIN
        SELECT RAISE(ABORT, 'REPORT_TO_USER: maximum resource value exceeded');
    END;

-- how much of each resource pool is each running job using? This gets filled
-- by code, subject to advice in the pending_job_preferred_resource_pool_sets view.
CREATE TABLE running_job_resource_pools (
    job_id INTEGER,
    resource_id CHAR,
    resource_pool_id INTEGER,
    quantity_used INTEGER,
    PRIMARY KEY (job_id, resource_id, resource_pool_id),
    FOREIGN KEY (job_id) REFERENCES jobs(job_id),
    FOREIGN KEY (resource_id, resource_pool_id) REFERENCES resource_pools(resource_id, resource_pool_id)
);

CREATE VIEW resource_pools_quantity_available AS
    SELECT   resource_pools.resource_id, 
             resource_pools.resource_pool_id, 
             quantity_total-SUM(coalesce(quantity_used,0)) AS quantity_available 
    FROM     resource_pools left outer join running_job_resource_pools ON resource_pools.resource_id = running_job_resource_pools.resource_id AND resource_pools.resource_pool_id = running_job_resource_pools.resource_pool_id 
    GROUP BY resource_pools.resource_id, resource_pools.resource_pool_id 
    ORDER BY resource_pools.resource_id, resource_pools.resource_pool_id;

CREATE VIEW resource_pool_set_counts AS 
    SELECT   resource_id, 
             1<<COUNT(*) AS resource_pool_set_count 
    FROM     resource_pools 
    GROUP BY resource_id
    ORDER BY resource_id;

CREATE VIEW resource_pool_sets AS 
    SELECT   resource_pool_set_counts.resource_id, 
             i AS resource_pool_set_id, 
             group_concat(resource_pool_id) AS resource_pools_list 
    FROM     i1000, 
             resource_pool_set_counts, 
             resource_pools 
    WHERE   
             -- common fields
              resource_pool_set_counts.resource_id = resource_pools.resource_id AND
             -- limit combinations
              resource_pool_set_id<resource_pool_set_count AND
             -- resource pool can only be in a set if it *should* be in that set
             (1<<resource_pools.resource_pool_id) & resource_pool_set_id 
    -- squash items that should be in the same list into the same list.
    GROUP BY resource_pool_set_counts.resource_id, resource_pool_set_id 
    ORDER BY resource_pool_set_counts.resource_id, resource_pool_set_id;

-- for each job, how closely does each resource_pool_set match its requirements for
-- a particular resource?
CREATE VIEW pending_job_resource_pool_set_properties AS 
--    SELECT pending_job_resources.job_id,
--           pending_job_resources.resource_id,
--           resource_pool_sets.resource_pool_set_id,
--           SUM(quantity_total-coalesce(quantity_used,0)) AS job_resource_pool_set_closeness,
--           SUM(resource_pool_priority) AS job_resource_pool_set_priority 
--    FROM   pending_job_resources,
--           resource_pool_sets, 
--           resource_pools left outer join running_job_resource_pools ON resource_pools.resource_id = running_job_resource_pools.resource_id AND resource_pools.resource_pool_id = running_job_resource_pools.resource_pool_id 
--    WHERE  pending_job_resources.resource_id = resource_pool_sets.resource_id AND
--           resource_pool_sets.resource_id = resource_pools.resource_id AND
--           (1<<resource_pools.resource_pool_id) & resource_pool_sets.resource_pool_set_id 
--    GROUP BY pending_job_resources.job_id, pending_job_resources.resource_id, resource_pool_sets.resource_pool_set_id 
--    -- HAVING job_resource_pool_set_closeness >= 0 
--    ORDER BY pending_job_resources.job_id, pending_job_resources.resource_id, resource_pool_sets.resource_pool_set_id;
    SELECT   pending_job_resources.job_id, 
             resource_pool_sets.resource_id, 
             resource_pool_sets.resource_pool_set_id, 
             SUM(quantity_available)-quantity_wanted AS job_resource_pool_set_closeness, 
             SUM(resource_pool_priority)             AS job_resource_pool_set_priority 
    FROM     resource_pool_sets, 
             resource_pools_quantity_available, 
             pending_job_resources, 
             resource_pools 
    WHERE    resource_pool_sets.resource_id=resource_pools_quantity_available.resource_id AND
             resource_pool_sets.resource_pool_set_id&(1<<resource_pools_quantity_available.resource_pool_id) AND
             pending_job_resources.resource_id=resource_pool_sets.resource_id AND
             pending_job_resources.resource_id=resource_pools.resource_id AND
             resource_pools_quantity_available.resource_pool_id=resource_pools.resource_pool_id 
    GROUP BY pending_job_resources.job_id, resource_pool_sets.resource_id, resource_pool_sets.resource_pool_set_id 
    -- we cannot delay filtering out negative closenesses because negative closenesses would
    -- (possibly) break the simple weighting function we use in the next view to work around
    -- not having windows support in this version of sqlite. All of this is so we can find
    -- the lowest closeness and priority (in that priority) *within* each job/resource group.
    HAVING   job_resource_pool_set_closeness >= 0
    ORDER BY pending_job_resources.job_id, resource_pool_sets.resource_id, resource_pool_sets.resource_pool_set_id;

-- we can find the minimum closeness and the minimum priority, but they might not be in the
-- same row, so we can't use these to find the best set for each job/resource combination -
-- At least not until we have a version of sqlite that supports windows. 
--
-- Formerly, we did exactly that: we had *two* views: one showing the minimum closeness AND
-- one showing the minimum priority, then we looked through pending_job_resource_pool_set_properties
-- for the row whose closeness was the minimum and whose priority was the minimum, but there
-- didn't have to be such a row! Because it's not guaranteed that it is the *same* row that
-- has the minimum of each property. We really needed to order on the two properties (with
-- one listed before the other in the 'ORDER BY' clause, and choose the first row in each
-- job_id/resource_id group, but that requires windows!
--
-- What we can do is to use a function to combine the closeness and the priority in a way that
-- maintains the ordering that we want - i.e. closeness is even more important than priority.
--
-- So here's the single weighting added, which combines the two things we're interested in
-- minimising.
CREATE VIEW pending_job_resource_pool_set_properties_with_single_weighting AS 
    SELECT *, 
           -- this formula intends that closeness should always be more important that priority.
           job_resource_pool_set_closeness*100+job_resource_pool_set_priority AS weighting 
    FROM pending_job_resource_pool_set_properties 
    WHERE weighting >= 0;

-- Then we can create a view that maps each job/resource pair to the lowest *combined* 
-- weighted resource pool set.
CREATE VIEW  pending_job_preferred_resource_pool_sets as
    SELECT   pending_job_resource_pool_set_properties_with_single_weighting.job_id, 
             pending_job_resource_pool_set_properties_with_single_weighting.resource_id, 
             pending_job_resource_pool_set_properties_with_single_weighting.resource_pool_set_id 
    FROM     pending_job_resource_pool_set_properties_with_single_weighting, 
             (
                 SELECT   job_id, 
                          resource_id, 
                          MIN(weighting) AS minimum_weighting 
                 FROM     pending_job_resource_pool_set_properties_with_single_weighting 
                 GROUP BY job_id, resource_id
             ) AS minimum_weightings 
    WHERE    pending_job_resource_pool_set_properties_with_single_weighting.job_id=minimum_weightings.job_id AND
             pending_job_resource_pool_set_properties_with_single_weighting.resource_id=minimum_weightings.resource_id AND
             pending_job_resource_pool_set_properties_with_single_weighting.weighting=minimum_weightings.minimum_weighting;

-- This is the really useful view. For each job (though actually we are only interested in the
-- job we've decided to run) from which resource pools to take resources and the maximum that
-- can be taken.
CREATE VIEW pending_job_preferred_resource_pool_quantities_available as
    SELECT   pending_job_preferred_resource_pool_sets.job_id,
             pending_job_preferred_resource_pool_sets.resource_id,
             resource_pools_quantity_available.resource_pool_id,
             quantity_available 
    -- NEXT: we're selecting from the pool_sets, one row of which refers to *multiple* pools. So
    -- we should be joining also on the resource_pools, with some jiggery to make sure we only generate
    -- rows when the pool *is* in the set.
    FROM     pending_job_preferred_resource_pool_sets, 
             resource_pools_quantity_available 
    WHERE    pending_job_preferred_resource_pool_sets.resource_id = resource_pools_quantity_available.resource_id AND
             (1<<resource_pools_quantity_available.resource_pool_id) & pending_job_preferred_resource_pool_sets.resource_pool_set_id 
    ORDER BY job_id, pending_job_preferred_resource_pool_sets.resource_id, resource_pools_quantity_available.resource_pool_id;

-- support for next_runnable_job view.
CREATE VIEW meetable_pending_job_resources AS 
    SELECT   pending_job_resources.job_id, resources.resource_id
    -- left outer join needed to avoid no info on meetable job resource requests in the case where no 
    -- jobs are currently running. 
    FROM     resources, 
             pending_job_resources left outer join running_job_resource_pools ON resources.resource_id = running_job_resource_pools.resource_id
    WHERE    resources.resource_id = pending_job_resources.resource_id
    GROUP BY pending_job_resources.job_id, resources.resource_id 
    HAVING   quantity_wanted <= quantity_total-SUM(coalesce(quantity_used,0));

-- next job that can be run (there's no point in not limiting this, since starting
-- a job will affect what else is runnable).
CREATE VIEW runnable_jobs AS
    SELECT   jobs.job_id 
    FROM     jobs, meetable_pending_job_resources 
    WHERE    jobs.job_id = meetable_pending_job_resources.job_id 
    GROUP BY jobs.job_id 
    HAVING   COUNT(*) = (SELECT COUNT(*) FROM resources) 
    ORDER BY priority;

-- for clients (jss, jsq, jsc) to pass commands ('scan_jobs', 'quit', ...).
CREATE TABLE commands (
    command_id integer,
    command char,
    PRIMARY KEY (command_id)
);
