Remove redundant flags complete_logic_tree_gmf and complete_logic_tree_ses

[Michele Simionato]

Such flags appear as fields in four tables: gmf_collection, gmf_set,
ses_collection and ses. They are redundant, because the check constraints
fully specify them. This can be seen directly from the table definitions::

CREATE TABLE hzrdr.gmf_collection (
    id SERIAL PRIMARY KEY,
    output_id INTEGER NOT NULL, -- FK to output.id
    -- FK to lt_realization.id
    lt_realization_id INTEGER CONSTRAINT gmf_collection_lt_realization_check
        CHECK(
            -- Case 1: Normal GMF collection
            ((lt_realization_id IS NOT NULL) AND (complete_logic_tree_gmf= FALSE))
            -- Case 2: GMF collection containing all ground motion fields for the entire
            -- logic tree.
            OR ((lt_realization_id IS NULL) AND (complete_logic_tree_gmf = TRUE))),
    -- A flag to indicate that this is a `complete logic
    -- tree` GMF collection.
    complete_logic_tree_gmf BOOLEAN NOT NULL DEFAULT FALSE
) TABLESPACE hzrdr_ts;

CREATE TABLE hzrdr.gmf_set (
    id SERIAL PRIMARY KEY,
    gmf_collection_id INTEGER NOT NULL, -- FK to gmf_collection.id
    investigation_time float NOT NULL,
    -- Keep track of the stochastic event set which this GMF set is associated with
    ses_ordinal INTEGER CONSTRAINT gmf_set_ses_ordinal_check
        CHECK(
            -- Case 1: Normal GMF set
            ((ses_ordinal IS NOT NULL) AND (complete_logic_tree_gmf = FALSE))
            -- Case 2: GMF set containing all ground motion fields for the entire
            -- logic tree.
            OR ((ses_ordinal IS NULL) AND (complete_logic_tree_gmf = TRUE))),
    complete_logic_tree_gmf BOOLEAN NOT NULL DEFAULT FALSE
) TABLESPACE hzrdr_ts;

CREATE TABLE hzrdr.ses_collection (
    id SERIAL PRIMARY KEY,
    output_id INTEGER NOT NULL,
    -- If `lt_realization_id` is NULL, this is a `complete logic tree`
    -- Stochastic Event Set Collection, containing a single stochastic
    -- event set containing all of the ruptures from the entire
    -- calculation.
    lt_realization_id INTEGER CONSTRAINT ses_collection_lt_realization_check
        CHECK(
            -- Case 1: Normal stochastic event set
            ((lt_realization_id IS NOT NULL) AND (complete_logic_tree_ses = FALSE))
            -- Case 2: Stochastic event set containing all ruptures for the entire
            -- logic tree.
            OR ((lt_realization_id IS NULL) AND (complete_logic_tree_ses = TRUE))),
    -- A flag to indicate that this is a `complete logic
    -- tree` SES collection.
    complete_logic_tree_ses BOOLEAN NOT NULL DEFAULT FALSE
) TABLESPACE hzrdr_ts;

-- Stochastic Event Set: A container for 1 or more ruptures associated with a
-- specific investigation time span.
CREATE TABLE hzrdr.ses (
    id SERIAL PRIMARY KEY,
    ses_collection_id INTEGER NOT NULL,
    investigation_time float NOT NULL,
    -- Order number of this Stochastic Event Set in a series of SESs
    -- (for a given logic tree realization).
    ordinal INTEGER CONSTRAINT ses_ordinal_check
        CHECK(
            -- Case 1: Normal stochastic event set
            ((ordinal IS NOT NULL) AND (complete_logic_tree_ses = FALSE))
            -- Case 2: Stochastic event set containing all ruptures for the entire
            -- logic tree.
            OR (ordinal IS NULL) AND (complete_logic_tree_ses = TRUE)),
    -- A flag to indicate that this is a `complete logic
    -- tree` SES.
    -- If `true`, there should be no `ordinal` specified.
    complete_logic_tree_ses BOOLEAN NOT NULL DEFAULT FALSE
) TABLESPACE hzrdr_ts;

After two minutes to parse the above definitions, one sees that

  - gmf_collection.complete_logic_tree_gmf true <=> gmf_collection.lt_realization_id IS NULL
  - gmf_set.complete_logic_tree_gmf true <=> gmf_set.ses_ordinal IS NULL
  - ses_collection.complete_logic_tree_ses true <=> ses_collection.lt_realization_id IS NULL
  - ses.complete_logic_tree_ses true <=> ses.ordinal IS NULL

  (and false means NOT NULL)

Therefore one can avoid storing such flags, since their value can be inferred with a
IS NULL check on the corresponding fields (gmf_collection.lt_realization_id,
gmf_set.ses_ordinal, ses_collection.lt_realization_id, ses.ordinal).

[Lars Butler (lars-butler)]

Has this been resolved? The status is fix committed, but there is no link to a patch.

[Michele Simionato (michele-simionato)]

Yes, it was done a long time ago, before we had the convention of putting
links to the pull request,

On Mon, Jul 1, 2013 at 9:50 AM, Lars Butler <email address hidden>wrote:

> Has this been resolved? The status is fix committed, but there is no
> link to a patch.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1155904
>
> Title:
> Remove redundant flags complete_logic_tree_gmf and
> complete_logic_tree_ses
>
> Status in OpenQuake:
> Fix Committed
>
> Bug description:
> Such flags appear as fields in four tables: gmf_collection, gmf_set,
> ses_collection and ses. They are redundant, because the check constraints
> fully specify them. This can be seen directly from the table
> definitions::
>
> CREATE TABLE hzrdr.gmf_collection (
> id SERIAL PRIMARY KEY,
> output_id INTEGER NOT NULL, -- FK to output.id
> -- FK to lt_realization.id
> lt_realization_id INTEGER CONSTRAINT
> gmf_collection_lt_realization_check
> CHECK(
> -- Case 1: Normal GMF collection
> ((lt_realization_id IS NOT NULL) AND
> (complete_logic_tree_gmf= FALSE))
> -- Case 2: GMF collection containing all ground motion
> fields for the entire
> -- logic tree.
> OR ((lt_realization_id IS NULL) AND (complete_logic_tree_gmf
> = TRUE))),
> -- A flag to indicate that this is a `complete logic
> -- tree` GMF collection.
> complete_logic_tree_gmf BOOLEAN NOT NULL DEFAULT FALSE
> ) TABLESPACE hzrdr_ts;
>
> CREATE TABLE hzrdr.gmf_set (
> id SERIAL PRIMARY KEY,
> gmf_collection_id INTEGER NOT NULL, -- FK to gmf_collection.id
> investigation_time float NOT NULL,
> -- Keep track of the stochastic event set which this GMF set is
> associated with
> ses_ordinal INTEGER CONSTRAINT gmf_set_ses_ordinal_check
> CHECK(
> -- Case 1: Normal GMF set
> ((ses_ordinal IS NOT NULL) AND (complete_logic_tree_gmf =
> FALSE))
> -- Case 2: GMF set containing all ground motion fields for
> the entire
> -- logic tree.
> OR ((ses_ordinal IS NULL) AND (complete_logic_tree_gmf =
> TRUE))),
> complete_logic_tree_gmf BOOLEAN NOT NULL DEFAULT FALSE
> ) TABLESPACE hzrdr_ts;
>
> CREATE TABLE hzrdr.ses_collection (
> id SERIAL PRIMARY KEY,
> output_id INTEGER NOT NULL,
> -- If `lt_realization_id` is NULL, this is a `complete logic tree`
> -- Stochastic Event Set Collection, containing a single stochastic
> -- event set containing all of the ruptures from the entire
> -- calculation.
> lt_realization_id INTEGER CONSTRAINT
> ses_collection_lt_realization_check
> CHECK(
> -- Case 1: Normal stochastic event set
> ((lt_realization_id IS NOT NULL) AND
> (complete_logic_tree_ses = FALSE))
> -- Case 2: Stochastic event set containing all ruptures for
> the entire
> -- logic tree.
> OR ((lt_realization_id IS NULL) AND (complete_logic_tree_ses
> = TRUE))),
> -- A flag...