db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Peculiar sorting behaviour?
Date Wed, 03 Apr 2013 10:29:22 GMT
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

Mime
View raw message