Changeset: 01c6dab96e78 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=01c6dab96e78
Modified Files:
dump_output.sql
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
Branch: monetdbe-proxy
Log Message:
Use views instead functions where possible.
diffs (truncated from 604 to 300 lines):
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -88,9 +88,9 @@ COMMENT ON WINDOW "sys"."stddev" IS 'Th
TRUNCATE sys.privileges;
INSERT INTO sys.privileges VALUES ((SELECT t.id FROM sys.schemas s, tables t
WHERE s.id = t.schema_id AND s.name || '.' || t.name = 'sys.foo' ),(SELECT id
FROM auths a WHERE a.name = 'voc' ),(SELECT pc.privilege_code_id FROM
privilege_codes pc WHERE pc.privilege_code_name = 'SELECT' ),(SELECT id FROM
auths g WHERE g.name = 'monetdb' ),false);
INSERT INTO sys.privileges VALUES ((SELECT c.id FROM sys.schemas s, tables t,
columns c WHERE s.id = t.schema_id AND t.id = c.table_id AND s.name || '.' ||
t.name || '.' || c.name = 'sys.foo.i' ),(SELECT id FROM auths a WHERE a.name =
'voc' ),(SELECT pc.privilege_code_id FROM privilege_codes pc WHERE
pc.privilege_code_name = 'UPDATE' ),(SELECT id FROM auths g WHERE g.name =
'monetdb' ),false);
-INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions() fqn WHERE fqn.nme = 'sys.f1(INTEGER)' AND fqn.tpe
= 'FUNCTION' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),false);
-INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions() fqn WHERE fqn.nme = 'sys.f1(INTEGER)' AND fqn.tpe
= 'PROCEDURE' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),false);
-INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions() fqn WHERE fqn.nme = 'sys.f1()' AND fqn.tpe =
'PROCEDURE' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),true);
+INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions fqn WHERE fqn.nme = 'sys.f1(INTEGER)' AND fqn.tpe =
'FUNCTION' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),false);
+INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions fqn WHERE fqn.nme = 'sys.f1(INTEGER)' AND fqn.tpe =
'PROCEDURE' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),false);
+INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions fqn WHERE fqn.nme = 'sys.f1()' AND fqn.tpe =
'PROCEDURE' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),true);
INSERT INTO sys.privileges VALUES (0,(SELECT id FROM auths a WHERE a.name =
'voc' ),(SELECT pc.privilege_code_id FROM privilege_codes pc WHERE
pc.privilege_code_name = 'UPDATE' ),(SELECT id FROM auths g WHERE g.name =
'monetdb' ),false);
COPY 3 RECORDS INTO "sys"."tbl_with_data"("c1", "c2", "c3", "c4", "c5", "c6",
"c7", "c8", "c9", "c10", "c11", "c12", "c13", "c14", "c18", "c19", "c20",
"c21", "c22", "c23", "c24", "c25", "c26", "c27", "c28", "c29", "c30", "c31",
"c32", "c33") FROM STDIN USING DELIMITERS '|','\n','"';
1234|5678|90|true|"Hello\n \\|\"
World"|2020-12-20|10.000|1023.345|12345|123.45|1123.455|1122133.5|121233.45|"POINT
(5.1
34.5)"|2000|4000|8000|65333.414|8000.000|4000.000|2000.000|1000.000|14:18:18|2015-05-22
14:18:17.780331|2015-05-22 00:00:00.00|2015-05-22
13:18:17.780331+01:00|"{\"price\":9}"|10.1.0.0/16|"https://[email protected]:458/Doc/Abc.html?lang=nl&sort=asc#example";|65950c76-a2f6-4543-660a-b849cf5f2453
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -157,28 +157,31 @@ RETURN
SELECT replace_first(stmt, '(\\s*"?' || sch || '"?\\s*\\.|)\\s*"?' ||
nme || '"?\\s*', ' ' || FQN(sch, nme) || ' ', 'imsx');
END;
-CREATE FUNCTION describe_constraints() RETURNS TABLE(s STRING, "table" STRING,
nr INT, col STRING, con STRING, type STRING) BEGIN
- RETURN
- SELECT s.name, t.name, kc.nr, kc.name, k.name, CASE WHEN k.type
= 0 THEN 'PRIMARY KEY' WHEN k.type = 1 THEN 'UNIQUE' END
- FROM sys.schemas s, sys._tables t, sys.objects kc, sys.keys k
- WHERE kc.id = k.id
- AND k.table_id = t.id
- AND s.id = t.schema_id
- AND t.system = FALSE
- AND k.type in (0, 1)
- AND t.type IN (0, 6);
-END;
+CREATE VIEW describe_constraints AS
+ SELECT
+ s.name s,
+ t.name "table",
+ kc.nr nr,
+ kc.name col,
+ k.name con,
+ CASE WHEN k.type = 0 THEN 'PRIMARY KEY' WHEN k.type = 1 THEN
'UNIQUE' END "type"
+ FROM sys.schemas s, sys._tables t, sys.objects kc, sys.keys k
+ WHERE kc.id = k.id
+ AND k.table_id = t.id
+ AND s.id = t.schema_id
+ AND t.system = FALSE
+ AND k.type in (0, 1)
+ AND t.type IN (0, 6);
-CREATE FUNCTION describe_indices() RETURNS TABLE (i STRING, o INT, s STRING, t
STRING, c STRING, it STRING) BEGIN
-RETURN
+CREATE VIEW describe_indices AS
WITH it (id, idx) AS (VALUES (0, 'INDEX'), (4, 'IMPRINTS INDEX'), (5,
'ORDERED INDEX')) --UNIQUE INDEX wraps to INDEX.
SELECT
- i.name,
- kc.nr, --TODO: Does this determine the concatenation order?
- s.name,
- t.name,
- c.name,
- it.idx
+ i.name i,
+ kc.nr o, --TODO: Does this determine the concatenation order?
+ s.name s,
+ t.name t,
+ c.name c,
+ it.idx it
FROM
sys.idxs AS i LEFT JOIN sys.keys AS k ON i.name = k.name,
sys.objects AS kc,
@@ -195,15 +198,13 @@ RETURN
AND k.type IS NULL
AND i.type = it.id
ORDER BY i.name, kc.nr;
-END;
-CREATE FUNCTION describe_column_defaults() RETURNS TABLE(sch STRING, tbl
STRING, col STRING, def STRING) BEGIN
-RETURN
+CREATE VIEW describe_column_defaults AS
SELECT
- s.name,
- t.name,
- c.name,
- c."default"
+ s.name sch,
+ t.name tbl,
+ c.name col,
+ c."default" def
FROM schemas s, tables t, columns c
WHERE
s.id = t.schema_id AND
@@ -211,15 +212,8 @@ RETURN
s.name <> 'tmp' AND
NOT t.system AND
c."default" IS NOT NULL;
-END;
-CREATE FUNCTION describe_foreign_keys() RETURNS TABLE(
- fk_s STRING, fk_t STRING, fk_c STRING,
- o INT, fk STRING,
- pk_s STRING, pk_t STRING, pk_c STRING,
- on_update STRING, on_delete STRING) BEGIN
-
- RETURN
+CREATE VIEW describe_foreign_keys AS
WITH action_type (id, act) AS (VALUES
(0, 'NO ACTION'),
(1, 'CASCADE'),
@@ -227,33 +221,37 @@ CREATE FUNCTION describe_foreign_keys()
(3, 'SET NULL'),
(4, 'SET DEFAULT'))
SELECT
- fs.name AS fsname, fkt.name AS ktname, fkkc.name AS fcname,
- fkkc.nr AS o, fkk.name AS fkname,
- ps.name AS psname, pkt.name AS ptname, pkkc.name AS pcname,
- ou.act as on_update, od.act as on_delete
- FROM sys._tables fkt,
- sys.objects fkkc,
- sys.keys fkk,
- sys._tables pkt,
- sys.objects pkkc,
- sys.keys pkk,
- sys.schemas ps,
- sys.schemas fs,
- action_type ou,
- action_type od
-
- WHERE fkt.id = fkk.table_id
- AND pkt.id = pkk.table_id
- AND fkk.id = fkkc.id
- AND pkk.id = pkkc.id
- AND fkk.rkey = pkk.id
- AND fkkc.nr = pkkc.nr
- AND pkt.schema_id = ps.id
- AND fkt.schema_id = fs.id
- AND (fkk."action" & 255) = od.id
- AND ((fkk."action" >> 8) & 255) = ou.id
- ORDER BY fkk.name, fkkc.nr;
-END;
+ fs.name fk_s,
+ fkt.name fk_t,
+ fkkc.name fk_c,
+ fkkc.nr o,
+ fkk.name fk,
+ ps.name pk_s,
+ pkt.name pk_t,
+ pkkc.name pk_c,
+ ou.act on_update,
+ od.act on_delete
+ FROM sys._tables fkt,
+ sys.objects fkkc,
+ sys.keys fkk,
+ sys._tables pkt,
+ sys.objects pkkc,
+ sys.keys pkk,
+ sys.schemas ps,
+ sys.schemas fs,
+ action_type ou,
+ action_type od
+ WHERE fkt.id = fkk.table_id
+ AND pkt.id = pkk.table_id
+ AND fkk.id = fkkc.id
+ AND pkk.id = pkkc.id
+ AND fkk.rkey = pkk.id
+ AND fkkc.nr = pkkc.nr
+ AND pkt.schema_id = ps.id
+ AND fkt.schema_id = fs.id
+ AND (fkk."action" & 255) = od.id
+ AND ((fkk."action" >> 8) & 255) = ou.id
+ ORDER BY fkk.name, fkkc.nr;
--TODO: CRASHES when this function gets inlined into describe_tables
CREATE FUNCTION get_merge_table_partition_expressions(tid INT) RETURNS STRING
@@ -283,13 +281,12 @@ CREATE FUNCTION get_remote_table_express
RETURN SELECT ' ON ' || SQ(uri) || ' WITH USER ' || SQ(username) || '
ENCRYPTED PASSWORD ' || SQ("hash") FROM sys.remote_table_credentials(s ||'.' ||
t);
END;
-CREATE FUNCTION describe_tables() RETURNS TABLE(o INT, sch STRING, tab STRING,
typ STRING, col STRING, opt STRING) BEGIN
-RETURN
+CREATE VIEW describe_tables AS
SELECT
- t.id,
- s.name,
- t.name,
- ts.table_type_name,
+ t.id o,
+ s.name sch,
+ t.name tab,
+ ts.table_type_name typ,
(SELECT
' (' ||
GROUP_CONCAT(
@@ -298,7 +295,7 @@ RETURN
ifthenelse(c."null" = 'false', ' NOT NULL', '')
, ', ') || ')'
FROM sys._columns c
- WHERE c.table_id = t.id),
+ WHERE c.table_id = t.id) col,
CASE
WHEN ts.table_type_name = 'REMOTE TABLE' THEN
get_remote_table_expressions(s.name, t.name)
@@ -308,26 +305,30 @@ RETURN
schema_guard(s.name, t.name, t.query)
ELSE
''
- END
+ END opt
FROM sys.schemas s, table_types ts, sys.tables t
WHERE ts.table_type_name IN ('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE
TABLE', 'REPLICA TABLE')
AND t.system = FALSE
AND s.id = t.schema_id
AND ts.table_type_id = t.type
AND s.name <> 'tmp';
-END;
-CREATE FUNCTION describe_triggers() RETURNS TABLE (sch STRING, tab STRING, tri
STRING, def STRING) BEGIN
- RETURN
- SELECT s.name, t.name, tr.name, tr.statement
+CREATE VIEW describe_triggers AS
+ SELECT
+ s.name sch,
+ t.name tab,
+ tr.name tri,
+ tr.statement def
FROM sys.schemas s, sys.tables t, sys.triggers tr
WHERE s.id = t.schema_id AND t.id = tr.table_id AND NOT
t.system;
-END;
-CREATE FUNCTION describe_comments() RETURNS TABLE(id INT, tpe STRING, fqn
STRING, rem STRING) BEGIN
- RETURN
- SELECT o.id, o.tpe, o.nme, c.remark FROM (
-
+CREATE VIEW describe_comments AS
+ SELECT
+ o.id id,
+ o.tpe tpe,
+ o.nme fqn,
+ c.remark rem
+ FROM (
SELECT id, 'SCHEMA', DQ(name) FROM schemas
UNION ALL
@@ -354,10 +355,8 @@ CREATE FUNCTION describe_comments() RETU
) AS o(id, tpe, nme)
JOIN comments c ON c.id = o.id;
-END;
-CREATE FUNCTION fully_qualified_functions() RETURNS TABLE(id INT, tpe STRING,
nme STRING) BEGIN
-RETURN
+CREATE VIEW fully_qualified_functions AS
WITH fqn(id, tpe, sig, num) AS
(
SELECT
@@ -373,59 +372,57 @@ RETURN
WHERE s.id= f.schema_id AND f.type = ft.function_type_id
)
SELECT
- fqn1.id,
- fqn1.tpe,
- fqn1.sig
+ fqn1.id id,
+ fqn1.tpe tpe,
+ fqn1.sig nme
FROM
fqn fqn1 JOIN (SELECT id, max(num) FROM fqn GROUP BY id)
fqn2(id, num)
ON fqn1.id = fqn2.id AND (fqn1.num = fqn2.num OR fqn1.num IS
NULL AND fqn2.num is NULL);
-END;
-CREATE FUNCTION describe_privileges() RETURNS TABLE(o_id INT, o_nme STRING,
o_tpe STRING, p_nme STRING, a_nme STRING, g_nme STRING, grantable BOOLEAN) BEGIN
-RETURN SELECT
- CASE
- WHEN o.id IS NULL THEN
- 0
- ELSE
- o.id
- END,
- CASE
- WHEN o.tpe IS NULL AND pc.privilege_code_name = 'SELECT' THEN
--GLOBAL privileges: SELECT maps to COPY FROM
- 'COPY FROM'
- WHEN o.tpe IS NULL AND pc.privilege_code_name = 'UPDATE' THEN
--GLOBAL privileges: UPDATE maps to COPY INTO
- 'COPY INTO'
- ELSE
- o.nme
- END,
- CASE
- WHEN o.tpe IS NOT NULL THEN
- o.tpe
- ELSE
- 'GLOBAL'
- END,
- pc.privilege_code_name,
- a.name,
- g.name,
- p.grantable
-FROM
- privileges p LEFT JOIN
- (
- SELECT t.id, s.name || '.' || t.name , 'TABLE'
- from sys.schemas s, sys.tables t where s.id = t.schema_id
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
