From b818238fa24e92227af8a0a47db09efc13858aac Mon Sep 17 00:00:00 2001 From: Vamshi Surabhi Date: Tue, 27 Nov 2018 18:10:22 +0530 Subject: [PATCH] remove dependency on 'first' aggregate --- server/src-exec/Ops.hs | 23 ++++++++----- server/src-rsr/first_last.sql | 25 -------------- server/src-rsr/initialise.sql | 10 +++--- server/src-rsr/migrate_from_5_to_6.sql | 45 ++++++++++++++++++++++++++ 4 files changed, 65 insertions(+), 38 deletions(-) delete mode 100644 server/src-rsr/first_last.sql create mode 100644 server/src-rsr/migrate_from_5_to_6.sql diff --git a/server/src-exec/Ops.hs b/server/src-exec/Ops.hs index b83530a634a97..c3b0c787fe43a 100644 --- a/server/src-exec/Ops.hs +++ b/server/src-exec/Ops.hs @@ -30,7 +30,7 @@ import qualified Database.PG.Query.Connection as Q import qualified Network.HTTP.Client as HTTP curCatalogVer :: T.Text -curCatalogVer = "5" +curCatalogVer = "6" initCatalogSafe :: UTCTime -> HTTP.Manager -> Q.TxE QErr String initCatalogSafe initTime httpMgr = do @@ -65,18 +65,13 @@ initCatalogSafe initTime httpMgr = do initCatalogStrict :: Bool -> UTCTime -> HTTP.Manager -> Q.TxE QErr String initCatalogStrict createSchema initTime httpMgr = do - Q.catchE defaultTxErrorHandler $ do + Q.catchE defaultTxErrorHandler $ when createSchema $ do Q.unitQ "CREATE SCHEMA hdb_catalog" () False -- This is where the generated views and triggers are stored Q.unitQ "CREATE SCHEMA hdb_views" () False - flExtExists <- isExtAvailable "first_last_agg" - if flExtExists - then Q.unitQ "CREATE EXTENSION first_last_agg SCHEMA hdb_catalog" () False - else Q.multiQ $(Q.sqlFromFile "src-rsr/first_last.sql") >>= \(Q.Discard _) -> return () - pgcryptoExtExists <- Q.catchE defaultTxErrorHandler $ isExtAvailable "pgcrypto" if pgcryptoExtExists -- only if we created the schema, create the extension @@ -227,6 +222,13 @@ from3To4 = Q.catchE defaultTxErrorHandler $ do WHERE name = $2 |] (Q.AltJ $ A.toJSON etc, name) True +from5To6 :: Q.TxE QErr () +from5To6 = do + -- migrate database + Q.Discard () <- Q.multiQE defaultTxErrorHandler + $(Q.sqlFromFile "src-rsr/migrate_from_5_to_6.sql") + return () + migrateCatalog :: HTTP.Manager -> UTCTime -> Q.TxE QErr String migrateCatalog httpMgr migrationTime = do preVer <- getCatalogVersion @@ -237,12 +239,17 @@ migrateCatalog httpMgr migrationTime = do | preVer == "2" -> from2ToCurrent | preVer == "3" -> from3ToCurrent | preVer == "4" -> from4ToCurrent + | preVer == "5" -> from5ToCurrent | otherwise -> throw400 NotSupported $ "migrate: unsupported version : " <> preVer where + from5ToCurrent = do + from5To6 + postMigrate + from4ToCurrent = do from4To5 httpMgr - postMigrate + from5ToCurrent from3ToCurrent = do from3To4 diff --git a/server/src-rsr/first_last.sql b/server/src-rsr/first_last.sql deleted file mode 100644 index 38fbc740891ee..0000000000000 --- a/server/src-rsr/first_last.sql +++ /dev/null @@ -1,25 +0,0 @@ --- Create a function that always returns the first non-NULL item -CREATE OR REPLACE FUNCTION hdb_catalog.first_agg ( anyelement, anyelement ) -RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$ - SELECT $1; -$$; - --- And then wrap an aggregate around it -CREATE AGGREGATE hdb_catalog.FIRST ( - sfunc = hdb_catalog.first_agg, - basetype = anyelement, - stype = anyelement -); - --- Create a function that always returns the last non-NULL item -CREATE OR REPLACE FUNCTION hdb_catalog.last_agg ( anyelement, anyelement ) -RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$ - SELECT $2; -$$; - --- And then wrap an aggregate around it -CREATE AGGREGATE hdb_catalog.LAST ( - sfunc = hdb_catalog.last_agg, - basetype = anyelement, - stype = anyelement -); diff --git a/server/src-rsr/initialise.sql b/server/src-rsr/initialise.sql index 57b5a8339a1a9..4f4463ab37078 100644 --- a/server/src-rsr/initialise.sql +++ b/server/src-rsr/initialise.sql @@ -83,12 +83,12 @@ SELECT q.table_schema :: text, q.table_name :: text, q.constraint_name :: text, - hdb_catalog.first(q.constraint_oid) :: integer as constraint_oid, - hdb_catalog.first(q.ref_table_table_schema) :: text as ref_table_table_schema, - hdb_catalog.first(q.ref_table) :: text as ref_table, + 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, - hdb_catalog.first(q.confupdtype) :: text as on_update, - hdb_catalog.first(q.confdeltype) :: text as on_delete + min(q.confupdtype) :: text as on_update, + min(q.confdeltype) :: text as on_delete FROM (SELECT ctn.nspname AS table_schema, diff --git a/server/src-rsr/migrate_from_5_to_6.sql b/server/src-rsr/migrate_from_5_to_6.sql new file mode 100644 index 0000000000000..403659ef5de1b --- /dev/null +++ b/server/src-rsr/migrate_from_5_to_6.sql @@ -0,0 +1,45 @@ +CREATE OR REPLACE 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;