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

fix fetching primary key columns for non table owners (fix #1269) #1300

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
Jan 4, 2019
Merged

fix fetching primary key columns for non table owners (fix #1269) #1300

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
0410bf8
fix fetching primary key columns for non table owners, fix #1269
rakeshkky Jan 3, 2019
a514c12
Merge branch 'master' into issue-1269-primary-key
rakeshkky Jan 4, 2019
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
16 changes: 14 additions & 2 deletions server/src-exec/Ops.hs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -24,7 +24,7 @@ import qualified Database.PG.Query as Q
import qualified Database.PG.Query.Connection as Q

curCatalogVer :: T.Text
curCatalogVer = "6"
curCatalogVer = "7"

initCatalogSafe
:: (QErrM m, UserInfoM m, CacheRWM m, MonadTx m, MonadIO m, HasHttpManager m)
Expand Down Expand Up @@ -234,6 +234,13 @@ from5To6 = liftTx $ do
$(Q.sqlFromFile "src-rsr/migrate_from_5_to_6.sql")
return ()

from6To7 :: (MonadTx m) => m ()
from6To7 = liftTx $ do
-- migrate database
Q.Discard () <- Q.multiQE defaultTxErrorHandler
$(Q.sqlFromFile "src-rsr/migrate_from_6_to_7.sql")
return ()

migrateCatalog
:: (MonadTx m, CacheRWM m, MonadIO m, UserInfoM m, HasHttpManager m)
=> UTCTime -> m String
Expand All @@ -247,12 +254,17 @@ migrateCatalog migrationTime = do
| preVer == "3" -> from3ToCurrent
| preVer == "4" -> from4ToCurrent
| preVer == "5" -> from5ToCurrent
| preVer == "6" -> from6ToCurrent
| otherwise -> throw400 NotSupported $
"unsupported version : " <> preVer
where
from6ToCurrent = do
from6To7
postMigrate

from5ToCurrent = do
from5To6
postMigrate
from6ToCurrent

from4ToCurrent = do
from4To5
Expand Down
108 changes: 100 additions & 8 deletions server/src-rsr/initialise.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -156,18 +156,110 @@ GROUP BY

CREATE VIEW hdb_catalog.hdb_primary_key AS
SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name,
json_agg(ccu.column_name) as columns
tc.table_schema,
tc.table_name,
tc.constraint_name,
json_agg(constraint_column_usage.column_name) AS columns
FROM
(
information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
JOIN (
SELECT
x.tblschema AS table_schema,
x.tblname AS table_name,
x.colname AS column_name,
x.cstrname AS constraint_name
FROM
(
SELECT
DISTINCT nr.nspname,
r.relname,
a.attname,
c.conname
FROM
pg_namespace nr,
pg_class r,
pg_attribute a,
pg_depend d,
pg_namespace nc,
pg_constraint c
WHERE
(
(nr.oid = r.relnamespace)
AND (r.oid = a.attrelid)
AND (d.refclassid = ('pg_class' :: regclass) :: oid)
AND (d.refobjid = r.oid)
AND (d.refobjsubid = a.attnum)
AND (d.classid = ('pg_constraint' :: regclass) :: oid)
AND (d.objid = c.oid)
AND (c.connamespace = nc.oid)
AND (c.contype = 'c' :: "char")
AND (
r.relkind = ANY (ARRAY ['r'::"char", 'p'::"char"])
)
AND (NOT a.attisdropped)
)
UNION ALL
SELECT
nr.nspname,
r.relname,
a.attname,
c.conname
FROM
pg_namespace nr,
pg_class r,
pg_attribute a,
pg_namespace nc,
pg_constraint c
WHERE
(
(nr.oid = r.relnamespace)
AND (r.oid = a.attrelid)
AND (nc.oid = c.connamespace)
AND (
r.oid = CASE
c.contype
WHEN 'f' :: "char" THEN c.confrelid
ELSE c.conrelid
END
)
AND (
a.attnum = ANY (
CASE
c.contype
WHEN 'f' :: "char" THEN c.confkey
ELSE c.conkey
END
)
)
AND (NOT a.attisdropped)
AND (
c.contype = ANY (ARRAY ['p'::"char", 'u'::"char", 'f'::"char"])
)
AND (
r.relkind = ANY (ARRAY ['r'::"char", 'p'::"char"])
)
)
) x(
tblschema,
tblname,
colname,
cstrname
)
) constraint_column_usage ON (
(
(tc.constraint_name) :: text = (constraint_column_usage.constraint_name) :: text
AND (tc.table_schema) :: text = (constraint_column_usage.table_schema) :: text
AND (tc.table_name) :: text = (constraint_column_usage.table_name) :: text
)
)
)
WHERE
constraint_type = 'PRIMARY KEY'
((tc.constraint_type) :: text = 'PRIMARY KEY' :: text)
GROUP BY
tc.table_schema, tc.table_name, tc.constraint_name;
tc.table_schema,
tc.table_name,
tc.constraint_name;

CREATE FUNCTION hdb_catalog.inject_table_defaults(view_schema text, view_name text, tab_schema text, tab_name text) RETURNS void
LANGUAGE plpgsql AS $$
Expand Down
106 changes: 106 additions & 0 deletions server/src-rsr/migrate_from_6_to_7.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,106 @@
CREATE OR REPLACE VIEW hdb_catalog.hdb_primary_key AS
SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name,
json_agg(constraint_column_usage.column_name) AS columns
FROM
(
information_schema.table_constraints tc
JOIN (
SELECT
x.tblschema AS table_schema,
x.tblname AS table_name,
x.colname AS column_name,
x.cstrname AS constraint_name
FROM
(
SELECT
DISTINCT nr.nspname,
r.relname,
a.attname,
c.conname
FROM
pg_namespace nr,
pg_class r,
pg_attribute a,
pg_depend d,
pg_namespace nc,
pg_constraint c
WHERE
(
(nr.oid = r.relnamespace)
AND (r.oid = a.attrelid)
AND (d.refclassid = ('pg_class' :: regclass) :: oid)
AND (d.refobjid = r.oid)
AND (d.refobjsubid = a.attnum)
AND (d.classid = ('pg_constraint' :: regclass) :: oid)
AND (d.objid = c.oid)
AND (c.connamespace = nc.oid)
AND (c.contype = 'c' :: "char")
AND (
r.relkind = ANY (ARRAY ['r'::"char", 'p'::"char"])
)
AND (NOT a.attisdropped)
)
UNION ALL
SELECT
nr.nspname,
r.relname,
a.attname,
c.conname
FROM
pg_namespace nr,
pg_class r,
pg_attribute a,
pg_namespace nc,
pg_constraint c
WHERE
(
(nr.oid = r.relnamespace)
AND (r.oid = a.attrelid)
AND (nc.oid = c.connamespace)
AND (
r.oid = CASE
c.contype
WHEN 'f' :: "char" THEN c.confrelid
ELSE c.conrelid
END
)
AND (
a.attnum = ANY (
CASE
c.contype
WHEN 'f' :: "char" THEN c.confkey
ELSE c.conkey
END
)
)
AND (NOT a.attisdropped)
AND (
c.contype = ANY (ARRAY ['p'::"char", 'u'::"char", 'f'::"char"])
)
AND (
r.relkind = ANY (ARRAY ['r'::"char", 'p'::"char"])
)
)
) x(
tblschema,
tblname,
colname,
cstrname
)
) constraint_column_usage ON (
(
(tc.constraint_name) :: text = (constraint_column_usage.constraint_name) :: text
AND (tc.table_schema) :: text = (constraint_column_usage.table_schema) :: text
AND (tc.table_name) :: text = (constraint_column_usage.table_name) :: text
)
)
)
WHERE
((tc.constraint_type) :: text = 'PRIMARY KEY' :: text)
GROUP BY
tc.table_schema,
tc.table_name,
tc.constraint_name;