这是indexloc提供的服务，不要输入任何密码
Skip to content

faster retrieval of table information from postgres #1235

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Dec 19, 2018
Merged

faster retrieval of table information from postgres #1235

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
ce71c8a
faster retrieval of table information from postgres
Dec 19, 2018
e8cbd0e
guard against empty columns
Dec 19, 2018
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
80 changes: 7 additions & 73 deletions server/src-lib/Hasura/RQL/DDL/Schema/Table.hs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -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
Expand Down
2 changes: 1 addition & 1 deletion server/src-lib/Hasura/RQL/Types/Common.hs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -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}
Expand Down
116 changes: 20 additions & 96 deletions server/src-lib/Hasura/RQL/Types/SchemaCache.hs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -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
Expand All @@ -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"

Expand All @@ -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)

Expand All @@ -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
Expand Down Expand Up @@ -351,21 +271,21 @@ 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
{ tcType :: !ConstraintType
, tcName :: !ConstraintName
} deriving (Show, Eq)

$(deriveToJSON (aesonDrop 2 snakeCase) ''TableConstraint)
$(deriveJSON (aesonDrop 2 snakeCase) ''TableConstraint)

isUniqueOrPrimary :: TableConstraint -> Bool
isUniqueOrPrimary (TableConstraint ty _) = case ty of
Expand All @@ -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
Expand All @@ -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

Expand Down
78 changes: 78 additions & 0 deletions server/src-rsr/table_info.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,78 @@
select
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_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