From ce71c8a0561cf110b910c98c18d33247e3f30280 Mon Sep 17 00:00:00 2001 From: Vamshi Surabhi Date: Wed, 19 Dec 2018 15:23:03 +0530 Subject: [PATCH 1/2] faster retrieval of table information from postgres --- server/src-lib/Hasura/RQL/DDL/Schema/Table.hs | 80 ++---------- server/src-lib/Hasura/RQL/Types/Common.hs | 2 +- .../src-lib/Hasura/RQL/Types/SchemaCache.hs | 116 +++--------------- server/src-rsr/table_info.sql | 78 ++++++++++++ 4 files changed, 106 insertions(+), 170 deletions(-) create mode 100644 server/src-rsr/table_info.sql diff --git a/server/src-lib/Hasura/RQL/DDL/Schema/Table.hs b/server/src-lib/Hasura/RQL/DDL/Schema/Table.hs index 1697ae28449de..f99269577063f 100644 --- a/server/src-lib/Hasura/RQL/DDL/Schema/Table.hs +++ b/server/src-lib/Hasura/RQL/DDL/Schema/Table.hs @@ -43,82 +43,16 @@ saveTableToCatalog (QualifiedTable sn tn) = INSERT INTO "hdb_catalog"."hdb_table" VALUES ($1, $2) |] (sn, tn) False -getViewInfo :: QualifiedTable -> Q.TxE QErr (Maybe ViewInfo) -getViewInfo (QualifiedTable sn tn) = do - tableTy <- runIdentity . Q.getRow <$> Q.withQE defaultTxErrorHandler - [Q.sql| - SELECT table_type FROM information_schema.tables - WHERE table_schema = $1 - AND table_name = $2 - |] (sn, tn) False - - bool (return Nothing) buildViewInfo $ isView tableTy - where - buildViewInfo = do - (is_upd, is_ins, is_trig_upd, is_trig_del, is_trig_ins) - <- Q.getRow <$> Q.withQE defaultTxErrorHandler - [Q.sql| - SELECT is_updatable :: boolean, - is_insertable_into::boolean, - is_trigger_updatable::boolean, - is_trigger_deletable::boolean, - is_trigger_insertable_into::boolean - FROM information_schema.views - WHERE table_schema = $1 - AND table_name = $2 - |] (sn, tn) False - return $ Just $ ViewInfo - (is_upd || is_trig_upd) - (is_upd || is_trig_del) - (is_ins || is_trig_ins) - -- Build the TableInfo with all its columns getTableInfo :: QualifiedTable -> Bool -> Q.TxE QErr TableInfo getTableInfo qt@(QualifiedTable sn tn) isSystemDefined = do - tableExists <- Q.catchE defaultTxErrorHandler $ Q.listQ [Q.sql| - SELECT true from information_schema.tables - WHERE table_schema = $1 - AND table_name = $2; - |] (sn, tn) False - - -- if no columns are found, there exists no such view/table - unless (tableExists == [Identity True]) $ - throw400 NotExists $ "no such table/view exists in postgres : " <>> qt - - -- Fetch View information - viewInfo <- getViewInfo qt - - -- Fetch the column details - colData <- Q.catchE defaultTxErrorHandler $ Q.listQ [Q.sql| - SELECT column_name, to_json(udt_name), is_nullable::boolean - FROM information_schema.columns - WHERE table_schema = $1 - AND table_name = $2 - |] (sn, tn) False - - -- Fetch primary key columns - rawPrimaryCols <- Q.listQE defaultTxErrorHandler [Q.sql| - SELECT columns - FROM hdb_catalog.hdb_primary_key - WHERE table_schema = $1 - AND table_name = $2 - |] (sn, tn) False - pkeyCols <- mkPKeyCols rawPrimaryCols - - -- Fetch the constraint details - rawConstraints <- Q.catchE defaultTxErrorHandler $ Q.listQ [Q.sql| - SELECT constraint_type, constraint_name - FROM information_schema.table_constraints - WHERE table_schema = $1 - AND table_name = $2 - |] (sn, tn) False - let colDetails = flip map colData $ \(colName, Q.AltJ colTy, isNull) - -> (colName, colTy, isNull) - return $ mkTableInfo qt isSystemDefined rawConstraints colDetails pkeyCols viewInfo - where - mkPKeyCols [] = return [] - mkPKeyCols [Identity (Q.AltJ pkeyCols)] = return pkeyCols - mkPKeyCols _ = throw500 "found multiple rows for a table in hdb_primary_key" + tableData <- Q.catchE defaultTxErrorHandler $ + Q.listQ $(Q.sqlFromFile "src-rsr/table_info.sql")(sn, tn) True + case tableData of + [] -> throw400 NotExists $ "no such table/view exists in postgres : " <>> qt + [(Q.AltJ cols, Q.AltJ pkeyCols, Q.AltJ cons, Q.AltJ viewInfoM)] -> + return $ mkTableInfo qt isSystemDefined cons cols pkeyCols viewInfoM + _ -> throw500 $ "more than one row found for: " <>> qt newtype TrackTable = TrackTable diff --git a/server/src-lib/Hasura/RQL/Types/Common.hs b/server/src-lib/Hasura/RQL/Types/Common.hs index 88afc1b981dbe..e0b10943141a4 100644 --- a/server/src-lib/Hasura/RQL/Types/Common.hs +++ b/server/src-lib/Hasura/RQL/Types/Common.hs @@ -35,7 +35,7 @@ data PGColInfo , pgiIsNullable :: !Bool } deriving (Show, Eq) -$(deriveToJSON (aesonDrop 3 snakeCase) ''PGColInfo) +$(deriveJSON (aesonDrop 3 snakeCase) ''PGColInfo) newtype RelName = RelName {getRelTxt :: T.Text} diff --git a/server/src-lib/Hasura/RQL/Types/SchemaCache.hs b/server/src-lib/Hasura/RQL/Types/SchemaCache.hs index cacff9c58c034..abb9e4a85bde6 100644 --- a/server/src-lib/Hasura/RQL/Types/SchemaCache.hs +++ b/server/src-lib/Hasura/RQL/Types/SchemaCache.hs @@ -85,7 +85,6 @@ module Hasura.RQL.Types.SchemaCache , getDependentObjsWith ) where -import qualified Database.PG.Query as Q import qualified Hasura.GraphQL.Context as GC import Hasura.Prelude import Hasura.RQL.Types.BoolExp @@ -106,68 +105,10 @@ import Data.Aeson.TH import qualified Data.HashMap.Strict as M import qualified Data.HashSet as HS import qualified Data.Text as T -import qualified PostgreSQL.Binary.Decoding as PD - --- data TableObjId --- = TOCol !PGCol --- | TORel !RelName --- | TOCons !ConstraintName --- | TOPerm !RoleName !PermType --- | TOTrigger !TriggerName --- deriving (Show, Eq, Generic) - --- instance Hashable TableObjId - --- data SchemaObjId --- = SOTable !QualifiedTable --- | SOQTemplate !TQueryName --- | SOTableObj !QualifiedTable !TableObjId --- deriving (Eq, Generic) - ---instance Hashable SchemaObjId - --- reportSchemaObj :: SchemaObjId -> T.Text --- reportSchemaObj (SOTable tn) = "table " <> qualTableToTxt tn --- reportSchemaObj (SOQTemplate qtn) = --- "query-template " <> getTQueryName qtn --- reportSchemaObj (SOTableObj tn (TOCol cn)) = --- "column " <> qualTableToTxt tn <> "." <> getPGColTxt cn --- reportSchemaObj (SOTableObj tn (TORel cn)) = --- "relationship " <> qualTableToTxt tn <> "." <> getRelTxt cn --- reportSchemaObj (SOTableObj tn (TOCons cn)) = --- "constraint " <> qualTableToTxt tn <> "." <> getConstraintTxt cn --- reportSchemaObj (SOTableObj tn (TOPerm rn pt)) = --- "permission " <> qualTableToTxt tn <> "." <> getRoleTxt rn --- <> "." <> permTypeToCode pt --- reportSchemaObj (SOTableObj tn (TOTrigger trn )) = --- "event-trigger " <> qualTableToTxt tn <> "." <> trn reportSchemaObjs :: [SchemaObjId] -> T.Text reportSchemaObjs = T.intercalate ", " . map reportSchemaObj --- instance Show SchemaObjId where --- show soi = T.unpack $ reportSchemaObj soi - --- instance ToJSON SchemaObjId where --- toJSON = String . reportSchemaObj - --- data SchemaDependency --- = SchemaDependency --- { sdObjId :: !SchemaObjId --- , sdReason :: !T.Text --- } deriving (Show, Eq) - --- instance ToJSONKey SchemaObjId where --- toJSONKey = toJSONKeyText reportSchemaObj - --- data SchemaDependency --- = SchemaDependency --- { sdObjId :: !SchemaObjId --- , sdReason :: !T.Text --- } deriving (Show, Eq, Generic) - --- $(deriveToJSON (aesonDrop 2 snakeCase) ''SchemaDependency) - mkParentDep :: QualifiedTable -> SchemaDependency mkParentDep tn = SchemaDependency (SOTable tn) "table" @@ -185,15 +126,6 @@ $(deriveToJSON (aesonDrop 3 snakeCase) ''QueryTemplateInfo) type QTemplateCache = M.HashMap TQueryName QueryTemplateInfo --- data PGColInfo --- = PGColInfo --- { pgiName :: !PGCol --- , pgiType :: !PGColType --- , pgiIsNullable :: !Bool --- } deriving (Show, Eq) - --- $(deriveToJSON (aesonDrop 3 snakeCase) ''PGColInfo) - onlyIntCols :: [PGColInfo] -> [PGColInfo] onlyIntCols = filter (isIntegerType . pgiType) @@ -210,18 +142,6 @@ getColInfos :: [PGCol] -> [PGColInfo] -> [PGColInfo] getColInfos cols allColInfos = flip filter allColInfos $ \ci -> pgiName ci `elem` cols --- data RelInfo --- = RelInfo --- { riName :: !RelName --- , riType :: !RelType --- , riMapping :: ![(PGCol, PGCol)] --- , riRTable :: !QualifiedTable --- , riDeps :: ![SchemaDependency] --- , riIsManual :: !Bool --- } deriving (Show, Eq) - --- $(deriveToJSON (aesonDrop 2 snakeCase) ''RelInfo) - type WithDeps a = (a, [SchemaDependency]) data FieldInfo @@ -351,13 +271,13 @@ constraintTyToTxt ty = case ty of instance Show ConstraintType where show = T.unpack . constraintTyToTxt -instance Q.FromCol ConstraintType where - fromCol bs = flip Q.fromColHelper bs $ PD.enum $ \case - "CHECK" -> Just CTCHECK - "FOREIGN KEY" -> Just CTFOREIGNKEY - "PRIMARY KEY" -> Just CTPRIMARYKEY - "UNIQUE" -> Just CTUNIQUE - _ -> Nothing +instance FromJSON ConstraintType where + parseJSON = withText "ConstraintType" $ \case + "CHECK" -> return CTCHECK + "FOREIGN KEY" -> return CTFOREIGNKEY + "PRIMARY KEY" -> return CTPRIMARYKEY + "UNIQUE" -> return CTUNIQUE + c -> fail $ "unexpected ConstraintType: " <> T.unpack c data TableConstraint = TableConstraint @@ -365,7 +285,7 @@ data TableConstraint , tcName :: !ConstraintName } deriving (Show, Eq) -$(deriveToJSON (aesonDrop 2 snakeCase) ''TableConstraint) +$(deriveJSON (aesonDrop 2 snakeCase) ''TableConstraint) isUniqueOrPrimary :: TableConstraint -> Bool isUniqueOrPrimary (TableConstraint ty _) = case ty of @@ -381,7 +301,7 @@ data ViewInfo , viIsInsertable :: !Bool } deriving (Show, Eq) -$(deriveToJSON (aesonDrop 2 snakeCase) ''ViewInfo) +$(deriveJSON (aesonDrop 2 snakeCase) ''ViewInfo) isMutable :: (ViewInfo -> Bool) -> Maybe ViewInfo -> Bool isMutable _ Nothing = True @@ -408,15 +328,19 @@ data TableInfo $(deriveToJSON (aesonDrop 2 snakeCase) ''TableInfo) -mkTableInfo :: QualifiedTable -> Bool -> [(ConstraintType, ConstraintName)] - -> [(PGCol, PGColType, Bool)] -> [PGCol] - -> Maybe ViewInfo -> TableInfo -mkTableInfo tn isSystemDefined rawCons cols pcols mVI = - TableInfo tn isSystemDefined colMap (M.fromList []) constraints pcols mVI (M.fromList []) +mkTableInfo + :: QualifiedTable + -> Bool + -> [TableConstraint] + -> [PGColInfo] + -> [PGCol] + -> Maybe ViewInfo -> TableInfo +mkTableInfo tn isSystemDefined constraints cols pcols mVI = + TableInfo tn isSystemDefined colMap (M.fromList []) + constraints pcols mVI (M.fromList []) where - constraints = flip map rawCons $ uncurry TableConstraint colMap = M.fromList $ map f cols - f (cn, ct, b) = (fromPGCol cn, FIColumn $ PGColInfo cn ct b) + f colInfo = (fromPGCol $ pgiName colInfo, FIColumn colInfo) type TableCache = M.HashMap QualifiedTable TableInfo -- info of all tables diff --git a/server/src-rsr/table_info.sql b/server/src-rsr/table_info.sql new file mode 100644 index 0000000000000..df76c37a6639b --- /dev/null +++ b/server/src-rsr/table_info.sql @@ -0,0 +1,78 @@ +select + columns.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_schema, + c.table_name, + 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( + json_build_object( + 'name', + constraint_name, + 'type', + constraint_type + ) + ) as constraints + from + information_schema.table_constraints c + 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 + ) +where + tables.table_schema = $1 AND + tables.table_name = $2 From e8cbd0e1dd0727d0f7c48f62762564d8eec1c015 Mon Sep 17 00:00:00 2001 From: Vamshi Surabhi Date: Wed, 19 Dec 2018 15:40:25 +0530 Subject: [PATCH 2/2] guard against empty columns --- server/src-rsr/table_info.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/server/src-rsr/table_info.sql b/server/src-rsr/table_info.sql index df76c37a6639b..406fc0b9d25a0 100644 --- a/server/src-rsr/table_info.sql +++ b/server/src-rsr/table_info.sql @@ -1,5 +1,5 @@ select - columns.columns, + 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