Changeset: 6def2eeb4335 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6def2eeb4335
Branch: default
Log Message:
merged
diffs (truncated from 1458 to 300 lines):
diff --git
a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
@@ -4365,19 +4365,16 @@ BEGIN
END
END;
END;
-
CREATE FUNCTION sys.SQ (s STRING) RETURNS STRING BEGIN RETURN ' ''' ||
sys.replace(s,'''','''''') || ''' '; END;
CREATE FUNCTION sys.DQ (s STRING) RETURNS STRING BEGIN RETURN '"' ||
sys.replace(s,'"','""') || '"'; END; --TODO: Figure out why this breaks with
the space
CREATE FUNCTION sys.FQN(s STRING, t STRING) RETURNS STRING BEGIN RETURN
sys.DQ(s) || '.' || sys.DQ(t); END;
CREATE FUNCTION sys.ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN
RETURN 'ALTER TABLE ' || sys.FQN(s, t) || ' '; END;
-
--We need pcre to implement a header guard which means adding the schema of an
object explicitely to its identifier.
CREATE FUNCTION sys.replace_first(ori STRING, pat STRING, rep STRING, flg
STRING) RETURNS STRING EXTERNAL NAME "pcre"."replace_first";
CREATE FUNCTION sys.schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
RETURN
SELECT sys.replace_first(stmt, '(\\s*"?' || sch || '"?\\s*\\.|)\\s*"?' ||
nme || '"?\\s*', ' ' || sys.FQN(sch, nme) || ' ', 'imsx');
END;
-
CREATE VIEW sys.describe_constraints AS
SELECT
s.name sch,
@@ -4392,7 +4389,6 @@ CREATE VIEW sys.describe_constraints AS
AND t.system = FALSE
AND k.type in (0, 1)
AND t.type IN (0, 6);
-
CREATE VIEW sys.describe_indices AS
WITH it (id, idx) AS (VALUES (0, 'INDEX'), (4, 'IMPRINTS INDEX'), (5,
'ORDERED INDEX')) --UNIQUE INDEX wraps to INDEX.
SELECT
@@ -4417,7 +4413,6 @@ CREATE VIEW sys.describe_indices AS
AND k.type IS NULL
AND i.type = it.id
ORDER BY i.name, kc.nr;
-
CREATE VIEW sys.describe_column_defaults AS
SELECT
s.name sch,
@@ -4431,7 +4426,6 @@ CREATE VIEW sys.describe_column_defaults
s.name <> 'tmp' AND
NOT t.system AND
c."default" IS NOT NULL;
-
CREATE VIEW sys.describe_foreign_keys AS
WITH action_type (id, act) AS (VALUES
(0, 'NO ACTION'),
@@ -4471,7 +4465,6 @@ CREATE VIEW sys.describe_foreign_keys AS
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 sys.get_merge_table_partition_expressions(tid INT) RETURNS
STRING
BEGIN
@@ -4494,12 +4487,10 @@ BEGIN
END
FROM (VALUES (tid)) t(id) LEFT JOIN sys.table_partitions tp ON t.id =
tp.table_id;
END;
-
--TODO: gives mergejoin errors when inlined
CREATE FUNCTION sys.get_remote_table_expressions(s STRING, t STRING) RETURNS
STRING BEGIN
RETURN SELECT ' ON ' || sys.SQ(uri) || ' WITH USER ' || sys.SQ(username)
|| ' ENCRYPTED PASSWORD ' || sys.SQ("hash") FROM sys.remote_table_credentials(s
||'.' || t);
END;
-
CREATE VIEW sys.describe_tables AS
SELECT
t.id o,
@@ -4531,7 +4522,6 @@ CREATE VIEW sys.describe_tables AS
AND s.id = t.schema_id
AND ts.table_type_id = t.type
AND s.name <> 'tmp';
-
CREATE VIEW sys.describe_triggers AS
SELECT
s.name sch,
@@ -4540,7 +4530,6 @@ CREATE VIEW sys.describe_triggers AS
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;
-
CREATE VIEW sys.describe_comments AS
SELECT
o.id id,
@@ -4549,32 +4538,20 @@ CREATE VIEW sys.describe_comments AS
c.remark rem
FROM (
SELECT id, 'SCHEMA', sys.DQ(name) FROM sys.schemas
-
UNION ALL
-
SELECT t.id, CASE WHEN ts.table_type_name = 'VIEW' THEN 'VIEW'
ELSE 'TABLE' END, sys.FQN(s.name, t.name)
FROM sys.schemas s JOIN sys.tables t ON s.id = t.schema_id JOIN
sys.table_types ts ON t.type = ts.table_type_id
WHERE NOT s.name <> 'tmp'
-
UNION ALL
-
SELECT c.id, 'COLUMN', sys.FQN(s.name, t.name) || '.' ||
sys.DQ(c.name) FROM sys.columns c, sys.tables t, sys.schemas s WHERE c.table_id
= t.id AND t.schema_id = s.id
-
UNION ALL
-
SELECT idx.id, 'INDEX', sys.FQN(s.name, idx.name) FROM sys.idxs
idx, sys._tables t, sys.schemas s WHERE idx.table_id = t.id AND t.schema_id =
s.id
-
UNION ALL
-
SELECT seq.id, 'SEQUENCE', sys.FQN(s.name, seq.name) FROM
sys.sequences seq, sys.schemas s WHERE seq.schema_id = s.id
-
UNION ALL
-
SELECT f.id, ft.function_type_keyword, sys.FQN(s.name, f.name)
FROM sys.functions f, sys.function_types ft, sys.schemas s WHERE f.type =
ft.function_type_id AND f.schema_id = s.id
-
) AS o(id, tpe, nme)
JOIN sys.comments c ON c.id = o.id;
-
CREATE VIEW sys.fully_qualified_functions AS
WITH fqn(id, tpe, sig, num) AS
(
@@ -4597,7 +4574,6 @@ CREATE VIEW sys.fully_qualified_function
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);
-
CREATE VIEW sys.describe_privileges AS
SELECT
CASE
@@ -4636,7 +4612,6 @@ CREATE VIEW sys.describe_privileges AS
p.privileges = pc.privilege_code_id AND
p.auth_id = a.id AND
p.grantor = g.id;
-
CREATE FUNCTION sys.describe_table(schemaName string, tableName string)
RETURNS TABLE(name string, query string, type string, id integer, remark
string)
BEGIN
@@ -4648,7 +4623,6 @@ BEGIN
AND t.name = tableName
AND t.type = tt.table_type_id;
END;
-
CREATE VIEW sys.describe_user_defined_types AS
SELECT
s.name sch,
@@ -4661,7 +4635,6 @@ CREATE VIEW sys.describe_user_defined_ty
(s.name = 'sys' AND t.sqlname not in ('geometrya', 'mbr', 'url',
'inet', 'json', 'uuid', 'xml')) OR
(s.name <> 'sys')
);
-
CREATE VIEW sys.describe_partition_tables AS
SELECT
m_sch,
@@ -4717,7 +4690,6 @@ CREATE VIEW sys.describe_partition_table
FROM
subq LEFT OUTER JOIN tp
ON subq.m_tid = tp.table_id) AS tmp_pi;
-
CREATE VIEW sys.describe_sequences AS
SELECT
s.name as sch,
@@ -4733,7 +4705,6 @@ CREATE VIEW sys.describe_sequences AS
WHERE s.id = seq.schema_id
AND s.name <> 'tmp'
ORDER BY s.name, seq.name;
-
CREATE VIEW sys.describe_functions AS
SELECT
f.id o,
@@ -4741,7 +4712,6 @@ CREATE VIEW sys.describe_functions AS
f.name fun,
f.func def
FROM sys.functions f JOIN sys.schemas s ON f.schema_id = s.id WHERE s.name
<> 'tmp' AND NOT f.system;
-
CREATE FUNCTION sys.describe_columns(schemaName string, tableName string)
RETURNS TABLE(name string, type string, digits integer, scale integer,
Nulls boolean, cDefault string, number integer, sqltype string, remark string)
BEGIN
@@ -4754,7 +4724,6 @@ BEGIN
AND s.name = schemaName
ORDER BY c.number;
END;
-
CREATE FUNCTION sys.describe_function(schemaName string, functionName string)
RETURNS TABLE(id integer, name string, type string, language string,
remark string)
BEGIN
@@ -4766,12 +4735,52 @@ BEGIN
LEFT OUTER JOIN sys.comments c ON f.id = c.id
WHERE f.name=functionName AND s.name = schemaName;
END;
+drop procedure sys.storagemodelinit();
+create procedure sys.storagemodelinit()
+begin
+ delete from sys.storagemodelinput;
+ insert into sys.storagemodelinput
+ select "schema", "table", "column", "type", typewidth, "count",
+ -- assume all variable size types contain distinct values
+ case when ("unique" or "type" IN ('varchar', 'char', 'clob', 'json',
'url', 'blob', 'geometry', 'geometrya'))
+ then "count" else 0 end,
+ case when "count" > 0 and heapsize >= 8192 and "type" in ('varchar',
'char', 'clob', 'json', 'url')
+ -- string heaps have a header of 8192
+ then cast((heapsize - 8192) / "count" as bigint)
+ when "count" > 0 and heapsize >= 32 and "type" in ('blob', 'geometry',
'geometrya')
+ -- binary data heaps have a header of 32
+ then cast((heapsize - 32) / "count" as bigint)
+ else typewidth end,
+ FALSE, case sorted when true then true else false end, "unique", TRUE
+ from sys."storage"; -- view sys."storage" excludes system tables (as
those are not useful to be modeled for storagesize by application users)
+ update sys.storagemodelinput
+ set reference = TRUE
+ where ("schema", "table", "column") in (
+ SELECT fkschema."name", fktable."name", fkkeycol."name"
+ FROM sys."keys" AS fkkey,
+ sys."objects" AS fkkeycol,
+ sys."tables" AS fktable,
+ sys."schemas" AS fkschema
+ WHERE fktable."id" = fkkey."table_id"
+ AND fkkey."id" = fkkeycol."id"
+ AND fkschema."id" = fktable."schema_id"
+ AND fkkey."rkey" > -1 );
+ update sys.storagemodelinput
+ set isacolumn = FALSE
+ where ("schema", "table", "column") NOT in (
+ SELECT sch."name", tbl."name", col."name"
+ FROM sys."schemas" AS sch,
+ sys."tables" AS tbl,
+ sys."columns" AS col
+ WHERE sch."id" = tbl."schema_id"
+ AND tbl."id" = col."table_id");
+end;
+update sys.functions set system = true where name = 'storagemodelinit' and
schema_id = 2000;
CREATE VIEW sys.dump_create_roles AS
SELECT
'CREATE ROLE ' || sys.dq(name) || ';' stmt FROM sys.auths
WHERE name NOT IN (SELECT name FROM sys.db_user_info)
AND grantor <> 0;
-
CREATE VIEW sys.dump_create_users AS
SELECT
'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' ||
@@ -4781,13 +4790,11 @@ CREATE VIEW sys.dump_create_users AS
WHERE ui.default_schema = s.id
AND ui.name <> 'monetdb'
AND ui.name <> '.snapshot';
-
CREATE VIEW sys.dump_create_schemas AS
SELECT
'CREATE SCHEMA ' || sys.dq(s.name) || ifthenelse(a.name <>
'sysadmin', ' AUTHORIZATION ' || a.name, ' ') || ';' stmt
FROM sys.schemas s, sys.auths a
WHERE s.authorization = a.id AND s.system = FALSE;
-
CREATE VIEW sys.dump_add_schemas_to_users AS
SELECT
'ALTER USER ' || sys.dq(ui.name) || ' SET SCHEMA ' || sys.dq(s.name)
|| ';' stmt
@@ -4796,31 +4803,26 @@ CREATE VIEW sys.dump_add_schemas_to_user
AND ui.name <> 'monetdb'
AND ui.name <> '.snapshot'
AND s.name <> 'sys';
-
CREATE VIEW sys.dump_grant_user_privileges AS
SELECT
'GRANT ' || sys.dq(a2.name) || ' ' || ifthenelse(a1.name = 'public',
'PUBLIC', sys.dq(a1.name)) || ';' stmt
FROM sys.auths a1, sys.auths a2, sys.user_role ur
WHERE a1.id = ur.login_id AND a2.id = ur.role_id;
-
CREATE VIEW sys.dump_table_constraint_type AS
SELECT
'ALTER TABLE ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
' ADD CONSTRAINT ' || sys.DQ(con) || ' '||
tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt
FROM sys.describe_constraints GROUP BY sch, tbl, con, tpe;
-
CREATE VIEW sys.dump_indices AS
SELECT
'CREATE ' || tpe || ' ' ||
sys.DQ(ind) || ' ON ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
'(' || GROUP_CONCAT(col) || ');' stmt
FROM sys.describe_indices GROUP BY ind, tpe, sch, tbl;
-
CREATE VIEW sys.dump_column_defaults AS
SELECT 'ALTER TABLE ' || sys.FQN(sch, tbl) || ' ALTER COLUMN ' ||
sys.DQ(col) || ' SET DEFAULT ' || def || ';' stmt
FROM sys.describe_column_defaults;
-
CREATE VIEW sys.dump_foreign_keys AS
SELECT
'ALTER TABLE ' || sys.DQ(fk_s) || '.'|| sys.DQ(fk_t) || ' ADD
CONSTRAINT ' || sys.DQ(fk) || ' ' ||
@@ -4829,7 +4831,6 @@ CREATE VIEW sys.dump_foreign_keys AS
'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update ||
';' stmt
FROM sys.describe_foreign_keys GROUP BY fk_s, fk_t, pk_s, pk_t, fk,
on_delete, on_update;
-
CREATE VIEW sys.dump_partition_tables AS
SELECT
sys.ALTER_TABLE(m_sch, m_tbl) || ' ADD TABLE ' || sys.FQN(p_sch,
p_tbl) ||
@@ -4842,7 +4843,6 @@ CREATE VIEW sys.dump_partition_tables AS
CASE WHEN tpe in ('VALUES', 'RANGE') AND with_nulls THEN ' WITH NULL
VALUES' ELSE '' END ||
';' stmt
FROM sys.describe_partition_tables;
-
CREATE VIEW sys.dump_sequences AS
SELECT
'CREATE SEQUENCE ' || sys.FQN(sch, seq) || ' AS BIGINT ' ||
@@ -4853,17 +4853,14 @@ CREATE VIEW sys.dump_sequences AS
CASE WHEN "cache" <> 1 THEN ' CACHE ' || "cache" ELSE '' END ||
CASE WHEN "cycle" THEN ' CYCLE' ELSE '' END || ';' stmt
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
