Skip to content

Fix file table unique constraint #6

Open
Open
Fix file table unique constraint#6
Labels
bug

Description

@jtniehof
jtniehof
opened on Jul 16, 2020

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.

Activity

  1. jtniehof
    added
    bug
    on Jul 16, 2020

  2. balarsen commented on Sep 15, 2020

    @balarsen
    balarsen
    on Sep 15, 2020
    Contributor

    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

  3. balarsen commented on Sep 15, 2020

    @balarsen
    balarsen
    on Sep 15, 2020
    Contributor

    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

  4. jtniehof commented on Sep 15, 2020

    @jtniehof
    jtniehof
    on Sep 15, 2020
    MemberAuthor

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

  5. jtniehof commented on Sep 15, 2020

    @jtniehof
    jtniehof
    on Sep 15, 2020
    MemberAuthor
    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.

  6. balarsen commented on Sep 15, 2020

    @balarsen
    balarsen
    on Sep 15, 2020
    Contributor

    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.

  7. jtniehof commented on Sep 15, 2020

    @jtniehof
    jtniehof
    on Sep 15, 2020
    MemberAuthor

    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

  8. balarsen commented on Sep 15, 2020

    @balarsen
    balarsen
    on Sep 15, 2020
    Contributor

    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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bug

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions