On 02/04/2013 22:13, Mike Matrigali wrote:
need ddl for both item_usage (i think you posted this in 1st posting) and tests
(don't see this one) tables, to read the query plan.
ITEM_USAGE is indeed in the first post. Here is the TESTS table:
CREATE TABLE tests (
id INTEGER GENERATED ALWAYS AS IDENTITY,
item VARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
disp SMALLINT NOT NULL DEFAULT 0,
starttime TIMESTAMP DEFAULT NULL,
endtime TIMESTAMP DEFAULT NULL,
offsetx INTEGER NOT NULL DEFAULT 0,
offsety INTEGER NOT NULL DEFAULT 0,
rate INTEGER NOT NULL DEFAULT 0,
duration INTEGER NOT NULL DEFAULT 0,
calibrate INTEGER NOT NULL DEFAULT 0,
deadline TIMESTAMP DEFAULT NULL,
stepsize INTEGER NOT NULL DEFAULT 0,
interval INTEGER NOT NULL DEFAULT 0,
stand CHAR(1) DEFAULT NULL,
hidden CHAR(1) DEFAULT NULL,
repeated CHAR(1) DEFAULT NULL,
private CHAR(1) DEFAULT NULL,
sequential CHAR(1) DEFAULT NULL,
final CHAR(1) DEFAULT NULL,
notes CLOB DEFAULT NULL,
testxml CLOB NOT NULL,
author VARCHAR(15) NOT NULL,
time TIMESTAMP NOT NULL,
CONSTRAINT tests_pk PRIMARY KEY (id),
CONSTRAINT tests_1 UNIQUE (item, title),
CONSTRAINT tests_2 FOREIGN KEY (item)
REFERENCES items(item)
ON DELETE CASCADE,
CONSTRAINT tests_3 CHECK (disp BETWEEN 0 AND 100),
CONSTRAINT tests_4 CHECK (rate BETWEEN 0 AND 100),
CONSTRAINT tests_5 CHECK (stepsize BETWEEN 0 AND 100)
);
--
John English
