Adding Valid dataset view

[pdrillin]

• Ticket : #31

[mdouchin]

I think the query is way too complicated, and not easy to read (even if it does the job). I think you shold not use another view v_orphan_xxx to find the valid datasets, but just check if the dataset items have corresponding table.

Please use a simple JOIN betwee pgmetadata.dataset table and the system table to check for validity

[mdouchin]

@drillinP @Gustry Here is a simple way to find orphan or valid dataset items

-- Valid dataset
SELECT 
    d.schema_name,
    d.table_name
FROM pgmetadata.dataset AS d
LEFT JOIN pg_tables AS t
    ON d.schema_name = t.schemaname AND d.table_name = t.tablename
WHERE t.tablename IS NOT NULL
ORDER BY d.schema_name, d.table_name;

Replace IS NOT NULL par IS NULL to find the orphan dataset items. Way more simple than

-- v_valid_dataset
CREATE VIEW pgmetadata.v_valid_dataset AS
 SELECT dataset.schema_name,
    dataset.table_name
   FROM pgmetadata.dataset
  WHERE ((NOT (concat(dataset.schema_name, '.', dataset.table_name) IN ( SELECT concat(v_orphan_dataset_items.schema_name, '.', v_orphan_dataset_items.table_name) AS concat
           FROM pgmetadata.v_orphan_dataset_items))) AND (NOT (concat(dataset.schema_name, '.', dataset.table_name) IN ( SELECT concat(dataset.schema_name, '.', dataset.table_name) AS concat
           FROM pgmetadata.v_orphan_tables))));

By the way we have not decided yet if we would like to support metadata dataset items for views or materialized views ? If so, we will need to change some logic.

[Gustry]

LGTM for me. Is-it ready for review ? Can we squash and merge ?

I would like to release a version this morning if possible.

[Gustry]

Thanks, I'm going to release the new version. I'm squashing.