Created
September 4, 2019 11:46
-
-
Save 0x777/0ae7c3a662913cc1fe04d4e22ab5ab25 to your computer and use it in GitHub Desktop.
down migration from catalog version 20 to 19
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ALTER TABLE hdb_catalog.hdb_table DROP COLUMN is_enum; | |
CREATE FUNCTION hdb_catalog.hdb_table_oid_check() RETURNS trigger AS | |
$function$ | |
BEGIN | |
IF (EXISTS (SELECT 1 FROM information_schema.tables st WHERE st.table_schema = NEW.table_schema AND st.table_name = NEW.table_name)) THEN | |
return NEW; | |
ELSE | |
RAISE foreign_key_violation using message = 'table_schema, table_name not in information_schema.tables'; | |
return NULL; | |
END IF; | |
END; | |
$function$ | |
LANGUAGE plpgsql; | |
CREATE TRIGGER hdb_table_oid_check BEFORE INSERT OR UPDATE ON hdb_catalog.hdb_table | |
FOR EACH ROW EXECUTE PROCEDURE hdb_catalog.hdb_table_oid_check(); | |
DROP VIEW hdb_catalog.hdb_table_info_agg; | |
DROP VIEW hdb_catalog.hdb_column; | |
DROP VIEW hdb_catalog.hdb_foreign_key_constraint; | |
CREATE VIEW hdb_catalog.hdb_foreign_key_constraint AS | |
SELECT | |
q.table_schema :: text, | |
q.table_name :: text, | |
q.constraint_name :: text, | |
min(q.constraint_oid) :: integer as constraint_oid, | |
min(q.ref_table_table_schema) :: text as ref_table_table_schema, | |
min(q.ref_table) :: text as ref_table, | |
json_object_agg(ac.attname, afc.attname) as column_mapping, | |
min(q.confupdtype) :: text as on_update, | |
min(q.confdeltype) :: text as on_delete | |
FROM | |
(SELECT | |
ctn.nspname AS table_schema, | |
ct.relname AS table_name, | |
r.conrelid AS table_id, | |
r.conname as constraint_name, | |
r.oid as constraint_oid, | |
cftn.nspname AS ref_table_table_schema, | |
cft.relname as ref_table, | |
r.confrelid as ref_table_id, | |
r.confupdtype, | |
r.confdeltype, | |
UNNEST (r.conkey) AS column_id, | |
UNNEST (r.confkey) AS ref_column_id | |
FROM | |
pg_catalog.pg_constraint r | |
JOIN pg_catalog.pg_class ct | |
ON r.conrelid = ct.oid | |
JOIN pg_catalog.pg_namespace ctn | |
ON ct.relnamespace = ctn.oid | |
JOIN pg_catalog.pg_class cft | |
ON r.confrelid = cft.oid | |
JOIN pg_catalog.pg_namespace cftn | |
ON cft.relnamespace = cftn.oid | |
WHERE | |
r.contype = 'f' | |
) q | |
JOIN pg_catalog.pg_attribute ac | |
ON q.column_id = ac.attnum | |
AND q.table_id = ac.attrelid | |
JOIN pg_catalog.pg_attribute afc | |
ON q.ref_column_id = afc.attnum | |
AND q.ref_table_id = afc.attrelid | |
GROUP BY q.table_schema, q.table_name, q.constraint_name; | |
CREATE VIEW hdb_catalog.hdb_table_info_agg AS ( | |
select | |
tables.table_name as table_name, | |
tables.table_schema as table_schema, | |
coalesce(columns.columns, '[]') as columns, | |
coalesce(pk.columns, '[]') as primary_key_columns, | |
coalesce(constraints.constraints, '[]') as constraints, | |
coalesce(views.view_info, 'null') as view_info | |
from | |
information_schema.tables as tables | |
left outer join ( | |
select | |
c.table_name, | |
c.table_schema, | |
json_agg( | |
json_build_object( | |
'name', | |
column_name, | |
'type', | |
udt_name, | |
'is_nullable', | |
is_nullable :: boolean | |
) | |
) as columns | |
from | |
information_schema.columns c | |
group by | |
c.table_schema, | |
c.table_name | |
) columns on ( | |
tables.table_schema = columns.table_schema | |
AND tables.table_name = columns.table_name | |
) | |
left outer join ( | |
select * from hdb_catalog.hdb_primary_key | |
) pk on ( | |
tables.table_schema = pk.table_schema | |
AND tables.table_name = pk.table_name | |
) | |
left outer join ( | |
select | |
c.table_schema, | |
c.table_name, | |
json_agg(constraint_name) as constraints | |
from | |
information_schema.table_constraints c | |
where | |
c.constraint_type = 'UNIQUE' | |
or c.constraint_type = 'PRIMARY KEY' | |
group by | |
c.table_schema, | |
c.table_name | |
) constraints on ( | |
tables.table_schema = constraints.table_schema | |
AND tables.table_name = constraints.table_name | |
) | |
left outer join ( | |
select | |
table_schema, | |
table_name, | |
json_build_object( | |
'is_updatable', | |
(is_updatable::boolean OR is_trigger_updatable::boolean), | |
'is_deletable', | |
(is_updatable::boolean OR is_trigger_deletable::boolean), | |
'is_insertable', | |
(is_insertable_into::boolean OR is_trigger_insertable_into::boolean) | |
) as view_info | |
from | |
information_schema.views v | |
) views on ( | |
tables.table_schema = views.table_schema | |
AND tables.table_name = views.table_name | |
) | |
); | |
UPDATE hdb_catalog.hdb_version | |
SET version = '19' | |
WHERE version = '20'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment