Skip to content

Instantly share code, notes, and snippets.

@omad

omad/delete_odc_product.sql

Last active March 17, 2021 17:22
Show Gist options
  • Save omad/1ae3463a123f37a9acf37213bebfde86 to your computer and use it in GitHub Desktop.
Save omad/1ae3463a123f37a9acf37213bebfde86 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. omad revised this gist Jul 30, 2019. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion delete_odc_product.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@
    --
    -- Use with psql from the command line:
    --
    -- psql -v product_name=<product-to-delete> -f delete_odc_product.sql
    -- psql -v product_name=<product-to-delete> -f delete_odc_product.sql -h <database-hostname> <dbname>
    --

    --
    @@ -23,6 +23,9 @@

    -- Are there any datasets that are descendents of this product?
    -- If so, they will need to be removed first!

    set search_path = 'agdc';

    select count(*)
    from dataset_source
    left join dataset d on dataset_source.source_dataset_ref = d.id
  2. omad revised this gist Jul 16, 2019. 1 changed file with 22 additions and 17 deletions.
    39 changes: 22 additions & 17 deletions delete_odc_product.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,16 +1,21 @@
    ------------------------------------------
    -- Some SQL to Delete a Data Cube Product
    ------------------------------------------
    --------------------------------------
    -- SQL to Delete a Data Cube Product
    --------------------------------------
    --
    -- Use with psql from the command line:
    --
    -- psql -v product_name=<product-to-delete> -f delete_odc_product.sql
    --

    --
    -- COUNT NUMBER OF DATASETS OF EACH TYPE (including archived)
    --
    select
    count(*),
    t.name
    from dataset
    left join dataset_type t on dataset.dataset_type_ref = t.id
    group by t.name;
    -- select
    -- count(*),
    -- t.name
    -- from dataset
    -- left join dataset_type t on dataset.dataset_type_ref = t.id
    -- group by t.name;

    --
    -- CHECK FOR LINEAGE RECORDS
    @@ -23,7 +28,7 @@ from dataset_source
    left join dataset d on dataset_source.source_dataset_ref = d.id
    where d.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');
    where dataset_type.name = :'product_name');

    -- Are there any lineage records which need deleting?
    -- These are the lineage history of the product we're deleting.
    @@ -32,15 +37,15 @@ from dataset_source
    left join dataset d on dataset_source.dataset_ref = d.id
    where d.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');
    where dataset_type.name = :'product_name');
    --
    -- DELETE LINEAGE RECORDS
    --
    WITH datasets as (SELECT id
    FROM dataset
    where dataset.dataset_type_ref = (select id
    FROM dataset_type
    WHERE name = 'ls8_fc_albers_archived'))
    WHERE name = :'product_name'))
    DELETE FROM dataset_source
    USING datasets
    where dataset_source.dataset_ref = datasets.id;
    @@ -54,14 +59,14 @@ from dataset_location
    left join dataset d on dataset_location.dataset_ref = d.id
    where d.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');
    where dataset_type.name = :'product_name');


    WITH datasets as (SELECT id
    FROM dataset
    where dataset.dataset_type_ref = (select id
    FROM dataset_type
    WHERE name = 'ls8_fc_albers_archived'))
    WHERE name = :'product_name'))
    select count(*)
    from dataset_location, datasets
    where dataset_location.dataset_ref = datasets.id;
    @@ -74,7 +79,7 @@ WITH datasets as (SELECT id
    FROM dataset
    where dataset.dataset_type_ref = (select id
    FROM dataset_type
    WHERE name = 'ls8_fc_albers_archived'))
    WHERE name = :'product_name'))
    DELETE FROM dataset_location
    USING datasets
    where dataset_location.dataset_ref = datasets.id;
    @@ -85,11 +90,11 @@ where dataset_location.dataset_ref = datasets.id;
    DELETE FROM dataset
    where dataset.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');
    where dataset_type.name = :'product_name');


    --
    -- FINALLY, DELETE THE PRODUCT
    --
    DELETE FROM dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived';
    where dataset_type.name = :'product_name';
  3. omad revised this gist Feb 11, 2019. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion README.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1 +0,0 @@
    # Danger
  4. omad created this gist Feb 11, 2019.
    1 change: 1 addition & 0 deletions README.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1 @@
    # Danger
    95 changes: 95 additions & 0 deletions delete_odc_product.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,95 @@
    ------------------------------------------
    -- Some SQL to Delete a Data Cube Product
    ------------------------------------------

    --
    -- COUNT NUMBER OF DATASETS OF EACH TYPE (including archived)
    --
    select
    count(*),
    t.name
    from dataset
    left join dataset_type t on dataset.dataset_type_ref = t.id
    group by t.name;

    --
    -- CHECK FOR LINEAGE RECORDS
    --

    -- Are there any datasets that are descendents of this product?
    -- If so, they will need to be removed first!
    select count(*)
    from dataset_source
    left join dataset d on dataset_source.source_dataset_ref = d.id
    where d.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');

    -- Are there any lineage records which need deleting?
    -- These are the lineage history of the product we're deleting.
    select count(*)
    from dataset_source
    left join dataset d on dataset_source.dataset_ref = d.id
    where d.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');
    --
    -- DELETE LINEAGE RECORDS
    --
    WITH datasets as (SELECT id
    FROM dataset
    where dataset.dataset_type_ref = (select id
    FROM dataset_type
    WHERE name = 'ls8_fc_albers_archived'))
    DELETE FROM dataset_source
    USING datasets
    where dataset_source.dataset_ref = datasets.id;


    --
    -- CHECK FOR LOCATION RECORDS
    --
    select count(*)
    from dataset_location
    left join dataset d on dataset_location.dataset_ref = d.id
    where d.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');


    WITH datasets as (SELECT id
    FROM dataset
    where dataset.dataset_type_ref = (select id
    FROM dataset_type
    WHERE name = 'ls8_fc_albers_archived'))
    select count(*)
    from dataset_location, datasets
    where dataset_location.dataset_ref = datasets.id;


    --
    -- DELETE LOCATION RECORDS
    --
    WITH datasets as (SELECT id
    FROM dataset
    where dataset.dataset_type_ref = (select id
    FROM dataset_type
    WHERE name = 'ls8_fc_albers_archived'))
    DELETE FROM dataset_location
    USING datasets
    where dataset_location.dataset_ref = datasets.id;

    --
    -- DELETE DATASET RECORDS
    --
    DELETE FROM dataset
    where dataset.dataset_type_ref = (select id
    from dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived');


    --
    -- FINALLY, DELETE THE PRODUCT
    --
    DELETE FROM dataset_type
    where dataset_type.name = 'ls8_fc_albers_archived';