Fix file table unique constraint

[jtniehof]

The file table has a uniqueness constraint that's basically meant to have the date, product, version be unique:

schema.UniqueConstraint('utc_file_date',
    'product_id',
    'interface_version',
    'quality_comment',
    'revision_version', name='Unique file tuple'), )

This is almost certainly wrong; it should be quality_version not quality_comment.

Fixing this should be as simple as changing CreateDB.py. We then should also have a script for updating existing databases with the old constraint; it will need to check if there are existing violations of the new constraint, and then change the constraint.

Closure condition

This issue should be closed when there is agreement on this change and the script to update old databases is merged.

[balarsen]

At least in my old hope database the database is correct

sqlite> PRAGMA table_info(file);
0|file_id|INTEGER|1||1
1|filename|VARCHAR(250)|1||0
2|utc_file_date|DATE|0||0
3|utc_start_time|DATETIME|0||0
4|utc_stop_time|DATETIME|0||0
5|data_level|FLOAT|1||0
6|interface_version|SMALLINT|1||0
7|quality_version|SMALLINT|1||0
8|revision_version|SMALLINT|1||0
9|verbose_provenance|TEXT|0||0
10|check_date|DATETIME|0||0
11|quality_comment|TEXT|0||0
12|caveats|TEXT|0||0
13|file_create_date|DATETIME|1||0
14|met_start_time|FLOAT|0||0
15|met_stop_time|FLOAT|0||0
16|exists_on_disk|BOOLEAN|1||0
17|quality_checked|BOOLEAN|0||0
18|product_id|INTEGER|1||0
19|shasum|VARCHAR(40)|0||0
20|process_keywords|TEXT|0||0

[balarsen]

A better test is that a freshly made table is also correct

$ ~/dbUtils/CreateDB.py test.sqlite
$ sqlite3 test.sqlite
sqlite> PRAGMA table_info(code);
0|code_id|INTEGER|1||1
1|filename|VARCHAR(250)|1||0
2|relative_path|VARCHAR(100)|1||0
3|code_start_date|DATE|1||0
4|code_stop_date|DATE|1||0
5|code_description|TEXT|1||0
6|process_id|INTEGER|1||0
7|interface_version|SMALLINT|1||0
8|quality_version|SMALLINT|1||0
9|revision_version|SMALLINT|1||0
10|output_interface_version|SMALLINT|1||0
11|active_code|BOOLEAN|1||0
12|date_written|DATE|1||0
13|shasum|VARCHAR(40)|0||0
14|newest_version|BOOLEAN|1||0
15|arguments|TEXT|0||0
16|ram|FLOAT|0||0
17|cpu|SMALLINT|0||0

[jtniehof]

Neither of what you have posted includes the constraint; the second is the code table, and this only applies to the file table.

[jtniehof]

select sql from sqlite_master where type='table' and name='file';

CREATE TABLE file (
    file_id INTEGER NOT NULL, 
    filename VARCHAR(250) NOT NULL, 
    utc_file_date DATE, 
    utc_start_time TIMESTAMP, 
    utc_stop_time TIMESTAMP, 
    data_level FLOAT NOT NULL, 
    interface_version SMALLINT NOT NULL, 
    quality_version SMALLINT NOT NULL, 
    revision_version SMALLINT NOT NULL, 
    verbose_provenance TEXT, 
    check_date TIMESTAMP, 
    quality_comment TEXT, 
    caveats TEXT, 
    file_create_date TIMESTAMP NOT NULL, 
    met_start_time FLOAT, 
    met_stop_time FLOAT, 
    exists_on_disk BOOLEAN NOT NULL, 
    quality_checked BOOLEAN, 
    product_id INTEGER NOT NULL, 
    shasum VARCHAR(40), 
    newest_version BOOLEAN NOT NULL, 
    process_keywords TEXT, 
    PRIMARY KEY (file_id), 
     FOREIGN KEY(product_id) REFERENCES product (product_id), 
     CHECK (utc_stop_time is not NULL OR met_stop_time is not NULL), 
     CHECK (met_start_time <= met_stop_time), 
     CHECK (utc_start_time is not NULL OR met_start_time is not NULL), 
     CHECK (utc_start_time <= utc_stop_time), 
     CHECK (interface_version >= 1), 
    CONSTRAINT "Unique file tuple"  UNIQUE (utc_file_date, product_id, interface_version, quality_comment, revision_version)
)

The problem is the CONSTRAINT "Unique file tuple" at the very end.

[balarsen]

Ahh sure ok, why this never made an issue is that sqlite does not enforce constraints itself so I think the database I created above would have no information on the constraint. Likely just update and move on. Would likely play hell with postgres.

Or at least did not... dunno about more modern.

[jtniehof]

Run that SQL....sqlite is definitely storing the constraint, although it might not be enforcing (I'm not seeing anything to indicate it never does/can't.) I haven't put anything in to intentionally try and violate the constraint.

"For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs"...since our quality_comment is usually NULL, this essentially makes the constraint meaningless. docs

[balarsen]

OK, I agree. There is the constraint.

That NULL is an insidious feature.

sqlite> .schema file
CREATE TABLE file (
        file_id INTEGER NOT NULL,
        filename VARCHAR(250) NOT NULL,
        utc_file_date DATE,
        utc_start_time DATETIME,
        utc_stop_time DATETIME,
        data_level FLOAT NOT NULL,
        interface_version SMALLINT NOT NULL,
        quality_version SMALLINT NOT NULL,
        revision_version SMALLINT NOT NULL,
        verbose_provenance TEXT,
        check_date DATETIME,
        quality_comment TEXT,
        caveats TEXT,
        file_create_date DATETIME NOT NULL,
        met_start_time FLOAT,
        met_stop_time FLOAT,
        exists_on_disk BOOLEAN NOT NULL,
        quality_checked BOOLEAN,
        product_id INTEGER NOT NULL,
        shasum VARCHAR(40),
        process_keywords TEXT,
        PRIMARY KEY (file_id),
        CHECK (utc_stop_time is not NULL OR met_stop_time is not NULL),
        CHECK (utc_start_time is not NULL OR met_start_time is not NULL),
        CHECK (met_start_time <= met_stop_time),
        CHECK (utc_start_time <= utc_stop_time),
        CHECK (interface_version >= 1),
        CONSTRAINT "Unique file tuple" UNIQUE (utc_file_date, product_id, interface_version, quality_comment, revision_version),
        CHECK (exists_on_disk IN (0, 1)),
        CHECK (quality_checked IN (0, 1)),
        FOREIGN KEY(product_id) REFERENCES product (product_id)
);
CREATE INDEX ix_file_data_level ON file (data_level);
CREATE UNIQUE INDEX ix_file_big ON file (filename, utc_file_date, utc_start_time, utc_stop_time);
CREATE INDEX ix_file_utc_file_date ON file (utc_file_date);
CREATE UNIQUE INDEX ix_file_filename ON file (filename);
CREATE INDEX ix_file_utc_start_time ON file (utc_start_time);
CREATE INDEX ix_file_utc_stop_time ON file (utc_stop_time);
CREATE INDEX ix_file_file_id ON file (file_id);