github.com/quay/claircore@v1.5.28/datastore/postgres/migrations/indexer/02-digests.sql (about)

     1  /* First lets drop all the foreign key constraints so we can
     2     work with the manifest and layer tables without dependencies */
     3  ALTER TABLE dist_scanartifact DROP CONSTRAINT dist_scanartifact_layer_hash_fkey;
     4  ALTER TABLE indexreport DROP CONSTRAINT indexreport_manifest_hash_fkey;
     5  ALTER TABLE manifest_index DROP CONSTRAINT manifest_index_manifest_hash_fkey;
     6  ALTER TABLE manifest_layer DROP CONSTRAINT manifest_layer_layer_hash_fkey;
     7  ALTER TABLE manifest_layer DROP CONSTRAINT manifest_layer_manifest_hash_fkey;
     8  ALTER TABLE package_scanartifact DROP CONSTRAINT package_scanartifact_layer_hash_fkey;
     9  ALTER TABLE repo_scanartifact DROP CONSTRAINT repo_scanartifact_layer_hash_fkey;
    10  ALTER TABLE scanned_layer DROP CONSTRAINT scanned_layer_layer_hash_fkey;
    11  ALTER TABLE scanned_manifest DROP CONSTRAINT scanned_manifest_manifest_hash_fkey;
    12  
    13  
    14  /* next lets alter the manifest and layer tables
    15     - add bigserial id columns to layer and manifest tables
    16     - drop existing primary key and add the id as primary key for both tables
    17     - add unique constraints on hash so duplicate layers and manifest hashes are not allowed
    18   */
    19  ALTER TABLE manifest ADD COLUMN id bigserial;
    20  ALTER TABLE layer ADD COLUMN id bigserial;
    21  
    22  ALTER TABLE manifest DROP CONSTRAINT manifest_pkey;
    23  ALTER TABLE manifest ADD PRIMARY KEY (id);
    24  CREATE INDEX ON manifest(hash);
    25  ALTER TABLE layer DROP CONSTRAINT layer_pkey;
    26  ALTER TABLE layer ADD PRIMARY KEY (id);
    27  CREATE INDEX ON layer(hash);
    28  
    29  ALTER TABLE manifest ADD CONSTRAINT manifest_hash_unique UNIQUE (hash);
    30  ALTER TABLE layer ADD CONSTRAINT layer_hash_unique UNIQUE (hash);
    31  
    32  /* next for each table with a foreign key to layer or manifest table:
    33     - create the layer or manifest id column
    34     - create new foreign key relationships that replace the hash strings
    35     - insert layer and manifest ids into new columns*/
    36  ALTER TABLE dist_scanartifact ADD COLUMN layer_id bigint;
    37  ALTER TABLE dist_scanartifact ADD FOREIGN KEY (layer_id) REFERENCES layer(id);
    38  UPDATE dist_scanartifact AS ds SET layer_id = (SELECT id FROM layer WHERE hash = ds.layer_hash);
    39  
    40  ALTER TABLE indexreport ADD COLUMN manifest_id bigint;
    41  ALTER TABLE indexreport ADD FOREIGN KEY (manifest_id) REFERENCES manifest(id);
    42  UPDATE indexreport AS ir SET manifest_id = (SELECT id FROM manifest WHERE hash = ir.manifest_hash);
    43  
    44  ALTER TABLE manifest_index ADD COLUMN manifest_id bigint;
    45  ALTER TABLE manifest_index ADD FOREIGN KEY (manifest_id) REFERENCES manifest(id);
    46  UPDATE manifest_index AS r SET manifest_id = (SELECT id FROM manifest WHERE hash = r.manifest_hash);
    47  
    48  ALTER TABLE manifest_layer ADD COLUMN manifest_id bigint;
    49  ALTER TABLE manifest_layer ADD FOREIGN KEY (manifest_id) REFERENCES manifest(id);
    50  UPDATE manifest_layer AS ml SET manifest_id = (SELECT id FROM manifest WHERE hash = ml.manifest_hash);
    51  
    52  ALTER TABLE manifest_layer ADD COLUMN layer_id bigint;
    53  ALTER TABLE manifest_layer ADD FOREIGN KEY (layer_id) REFERENCES layer(id);
    54  UPDATE manifest_layer AS ml SET layer_id = (SELECT id FROM layer WHERE hash = ml.layer_hash);
    55  
    56  ALTER TABLE package_scanartifact ADD COLUMN layer_id bigint;
    57  ALTER TABLE package_scanartifact ADD FOREIGN KEY (layer_id) REFERENCES layer(id);
    58  UPDATE package_scanartifact AS r SET layer_id = (SELECT id FROM layer WHERE hash = r.layer_hash);
    59  
    60  ALTER TABLE repo_scanartifact ADD COLUMN layer_id bigint;
    61  ALTER TABLE repo_scanartifact ADD FOREIGN KEY (layer_id) REFERENCES layer(id);
    62  UPDATE repo_scanartifact AS r SET layer_id = (SELECT id FROM layer WHERE hash = r.layer_hash);
    63  
    64  ALTER TABLE scanned_layer ADD COLUMN layer_id bigint;
    65  ALTER TABLE scanned_layer ADD FOREIGN KEY (layer_id) REFERENCES layer(id);
    66  UPDATE scanned_layer AS r SET layer_id = (SELECT id FROM layer WHERE hash = r.layer_hash);
    67  
    68  ALTER TABLE scanned_manifest ADD COLUMN manifest_id bigint;
    69  ALTER TABLE scanned_manifest ADD FOREIGN KEY (manifest_id) REFERENCES manifest(id);
    70  UPDATE scanned_manifest AS r SET manifest_id = (SELECT id FROM manifest WHERE hash = r.manifest_hash);
    71  
    72  /* next for each table with with a manifest or layer hash column
    73     - drop the string column
    74     - optionally (re)create indexes */
    75  ALTER TABLE dist_scanartifact DROP COLUMN layer_hash;
    76  ALTER TABLE dist_scanartifact ADD PRIMARY KEY (layer_id, scanner_id, dist_id);
    77  
    78  ALTER TABLE indexreport DROP COLUMN manifest_hash;
    79  ALTER TABLE indexreport ADD PRIMARY KEY (manifest_id);
    80  
    81  ALTER TABLE manifest_index DROP COLUMN manifest_hash;
    82  CREATE INDEX ON manifest_index(manifest_id, package_id, dist_id, repo_id);
    83  
    84  ALTER TABLE manifest_layer DROP COLUMN manifest_hash;
    85  ALTER TABLE manifest_layer DROP COLUMN layer_hash;
    86  ALTER TABLE manifest_layer ADD PRIMARY KEY (manifest_id, layer_id, i);
    87  
    88  ALTER TABLE package_scanartifact DROP COLUMN layer_hash;
    89  ALTER TABLE package_scanartifact ADD PRIMARY KEY (layer_id, package_id, source_id, scanner_id, package_db, repository_hint);
    90  
    91  ALTER TABLE repo_scanartifact DROP COLUMN layer_hash;
    92  ALTER TABLE repo_scanartifact ADD PRIMARY KEY (layer_id, repo_id, scanner_id);
    93  
    94  ALTER TABLE scanned_layer DROP COLUMN layer_hash;
    95  ALTER TABLE scanned_layer ADD PRIMARY KEY (layer_id, scanner_id);
    96  
    97  ALTER TABLE scanned_manifest DROP COLUMN manifest_hash;
    98  ALTER TABLE scanned_manifest ADD PRIMARY KEY (manifest_id, scanner_id)