Changeset: 9de7b67436c6 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9de7b67436c6
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
sql/test/Dependencies/Tests/dependency_DBobjects.test
sql/test/Dependencies/Tests/dependency_functions.test
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Removed function sys.ALTER_TABLE(s STRING, t STRING) as it is only used in 1
place and can easily be inlined.
Optimized function sys.FQN(s STRING, t STRING) by inlining code of sys.DQ()
directly and thereby eliminating 2 function calls and 2 concatenation of
strings calls.
Replaced usages of pattern: DQ(s) || '.' || DQ(t)
into usage of FQN(s, t) in several dump views.
Also updated upgrade program.
diffs (truncated from 781 to 300 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -3654,10 +3654,11 @@ sql_update_default(Client c, mvc *sql, c
"drop function
sys.get_merge_table_partition_expressions(int);\n"
"drop view sys.describe_constraints;\n"
"drop function sys.alter_table(string,
string);\n"
+ "drop function sys.FQN(string,
string);\n"
"drop function sys.sq(string);\n");
pos += snprintf(buf + pos, bufsize - pos,
"CREATE FUNCTION sys.SQ (s STRING)
RETURNS STRING BEGIN RETURN '''' || sys.replace(s,'''','''''') || ''''; END;\n"
- "CREATE FUNCTION sys.ALTER_TABLE(s
STRING, t STRING) RETURNS STRING BEGIN RETURN 'ALTER TABLE ' || sys.FQN(s, t);
END;\n"
+ "CREATE FUNCTION sys.FQN(s STRING, t
STRING) RETURNS STRING BEGIN RETURN '\"' || sys.replace(s,'\"','\"\"') ||
'\".\"' || sys.replace(t,'\"','\"\"') || '\"'; END;\n"
"CREATE VIEW sys.describe_constraints
AS\n"
" SELECT\n"
" s.name sch,\n"
@@ -3882,7 +3883,7 @@ sql_update_default(Client c, mvc *sql, c
" LEFT OUTER JOIN
sys.function_languages fl ON f.language = fl.language_id\n"
" WHERE s.name <> 'tmp' AND NOT
f.system;\n");
pos += snprintf(buf + pos, bufsize - pos,
- "update sys.functions set system = true
where system <> true and name in ('sq', 'alter_table',
'get_merge_table_partition_expressions', 'get_remote_table_expressions') and
schema_id = 2000 and type = %d;\n", F_FUNC);
+ "update sys.functions set system = true
where system <> true and name in ('sq', 'fqn',
'get_merge_table_partition_expressions', 'get_remote_table_expressions') and
schema_id = 2000 and type = %d;\n", F_FUNC);
pos += snprintf(buf + pos, bufsize - pos,
"update sys._tables set system = true where
name in ('describe_constraints', 'describe_tables', 'describe_comments',
'describe_privileges', 'describe_partition_tables', 'describe_functions') AND
schema_id = 2000;\n");
@@ -3930,8 +3931,7 @@ sql_update_default(Client c, mvc *sql, c
" WHERE a1.id = ur.login_id AND a2.id
= ur.role_id;\n"
"CREATE VIEW
sys.dump_table_constraint_type AS\n"
" SELECT\n"
- " 'ALTER TABLE ' || sys.DQ(sch) ||
'.' || sys.DQ(tbl) ||\n"
- " ' ADD CONSTRAINT ' ||
sys.DQ(con) || ' '||\n"
+ " 'ALTER TABLE ' || sys.FQN(sch,
tbl) || ' ADD CONSTRAINT ' || sys.DQ(con) || ' '||\n"
" tpe || ' (' ||
GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt,\n"
" sch schema_name,\n"
" tbl table_name,\n"
@@ -3941,11 +3941,12 @@ sql_update_default(Client c, mvc *sql, c
" WITH table_grants (sname, tname,
grantee, grants, grantor, grantable)\n"
" AS (SELECT s.name, t.name, a.name,
sum(p.privileges), g.name, p.grantable\n"
" FROM sys.schemas s, sys.tables
t, sys.auths a, sys.privileges p, sys.auths g\n"
- " WHERE p.obj_id = t.id AND
p.auth_id = a.id AND t.schema_id = s.id AND t.system = FALSE AND p.grantor =
g.id GROUP BY s.name, t.name, a.name, g.name, p.grantable\n"
+ " WHERE p.obj_id = t.id AND
p.auth_id = a.id AND t.schema_id = s.id AND t.system = FALSE AND p.grantor =
g.id\n"
+ " GROUP BY s.name, t.name,
a.name, g.name, p.grantable\n"
" ORDER BY s.name, t.name,
a.name, g.name, p.grantable)\n"
" SELECT\n"
- " 'GRANT ' || pc.privilege_code_name
|| ' ON TABLE ' || sys.dq(sname)\n"
- " || '.' || sys.dq(tname) || ' TO
' || ifthenelse(grantee = 'public', 'PUBLIC', sys.dq(grantee))\n"
+ " 'GRANT ' || pc.privilege_code_name
|| ' ON TABLE ' || sys.FQN(sname, tname)\n"
+ " || ' TO ' || ifthenelse(grantee
= 'public', 'PUBLIC', sys.dq(grantee))\n"
" || CASE WHEN grantable = 1 THEN
' WITH GRANT OPTION' ELSE '' END || ';' stmt,\n"
" sname schema_name,\n"
" tname table_name,\n"
@@ -3953,8 +3954,8 @@ sql_update_default(Client c, mvc *sql, c
" FROM table_grants LEFT OUTER JOIN
sys.privilege_codes pc ON grants = pc.privilege_code_id;\n"
"CREATE VIEW sys.dump_column_grants
AS\n"
" SELECT\n"
- " 'GRANT ' || pc.privilege_code_name
|| '(' || sys.dq(c.name) || ') ON '\n"
- " || sys.dq(s.name) || '.' ||
sys.dq(t.name) || ' TO ' || ifthenelse(a.name = 'public', 'PUBLIC',
sys.dq(a.name))\n"
+ " 'GRANT ' || pc.privilege_code_name
|| '(' || sys.dq(c.name) || ') ON ' || sys.FQN(s.name, t.name)\n"
+ " || ' TO ' || ifthenelse(a.name =
'public', 'PUBLIC', sys.dq(a.name))\n"
" || CASE WHEN p.grantable = 1
THEN ' WITH GRANT OPTION' ELSE '' END || ';' stmt,\n"
" s.name schema_name,\n"
" t.name table_name,\n"
@@ -3986,9 +3987,8 @@ sql_update_default(Client c, mvc *sql, c
" func_args(func_id, func_arg) AS\n"
" (SELECT func_id, func_arg FROM
func_args_all WHERE number = max_number)\n"
" SELECT\n"
- " 'GRANT ' || pc.privilege_code_name
|| ' ON '\n"
- " || ft.function_type_keyword || '
'\n"
- " || sys.dq(s.name) || '.' ||
sys.dq(f.name) || '(' || coalesce(fa.func_arg, '') || ') TO '\n"
+ " 'GRANT ' || pc.privilege_code_name
|| ' ON ' || ft.function_type_keyword || ' '\n"
+ " || sys.FQN(s.name, f.name) ||
'(' || coalesce(fa.func_arg, '') || ') TO '\n"
" || ifthenelse(a.name = 'public',
'PUBLIC', sys.dq(a.name))\n"
" || CASE WHEN p.grantable = 1
THEN ' WITH GRANT OPTION' ELSE '' END || ';' stmt,\n"
" s.name schema_name,\n"
@@ -4011,9 +4011,7 @@ sql_update_default(Client c, mvc *sql, c
" ORDER BY s.name, f.name, a.name,
g.name, p.grantable;\n"
"CREATE VIEW sys.dump_indices AS\n"
" SELECT\n"
- " 'CREATE ' || tpe || ' ' ||\n"
- " sys.DQ(ind) || ' ON ' ||
sys.DQ(sch) || '.' || sys.DQ(tbl) ||\n"
- " '(' || GROUP_CONCAT(col) || ');'
stmt,\n"
+ " 'CREATE ' || tpe || ' ' ||
sys.DQ(ind) || ' ON ' || sys.FQN(sch, tbl) || '(' || GROUP_CONCAT(col) || ');'
stmt,\n"
" sch schema_name,\n"
" tbl table_name,\n"
" ind index_name\n"
@@ -4028,7 +4026,7 @@ sql_update_default(Client c, mvc *sql, c
" SELECT\n"
" 'ALTER TABLE ' || sys.DQ(fk_s) ||
'.'|| sys.DQ(fk_t) || ' ADD CONSTRAINT ' || sys.DQ(fk) || ' ' ||\n"
" 'FOREIGN KEY(' ||
GROUP_CONCAT(sys.DQ(fk_c), ',') ||') ' ||\n"
- " 'REFERENCES ' || sys.DQ(pk_s) ||
'.' || sys.DQ(pk_t) || '(' || GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||\n"
+ " 'REFERENCES ' || sys.FQN(pk_s,
pk_t) || '(' || GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||\n"
" 'ON DELETE ' || on_delete || '
ON UPDATE ' || on_update ||\n"
" ';' stmt,\n"
" fk_s foreign_schema_name,\n"
@@ -4039,7 +4037,7 @@ sql_update_default(Client c, mvc *sql, c
" FROM sys.describe_foreign_keys
GROUP BY fk_s, fk_t, pk_s, pk_t, fk, on_delete, on_update;\n"
"CREATE VIEW sys.dump_partition_tables
AS\n"
" SELECT\n"
- " sys.ALTER_TABLE(m_sch, m_tbl) || '
ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
+ " 'ALTER TABLE ' || sys.FQN(m_sch,
m_tbl) || ' ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
" CASE \n"
" WHEN tpe = 'VALUES' THEN ' AS
PARTITION IN (' || pvalues || ')'\n"
" WHEN tpe = 'RANGE' THEN ' AS
PARTITION FROM ' || ifthenelse(minimum IS NOT NULL, sys.SQ(minimum), 'RANGE
MINVALUE') || ' TO ' || ifthenelse(maximum IS NOT NULL, sys.SQ(maximum), 'RANGE
MAXVALUE')\n"
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
@@ -147,8 +147,7 @@ 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;
+CREATE FUNCTION sys.FQN(s STRING, t STRING) RETURNS STRING BEGIN RETURN '"' ||
sys.replace(s,'"','""') || '"."' || sys.replace(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";
diff --git a/sql/scripts/76_dump.sql b/sql/scripts/76_dump.sql
--- a/sql/scripts/76_dump.sql
+++ b/sql/scripts/76_dump.sql
@@ -51,8 +51,7 @@ CREATE VIEW sys.dump_grant_user_privileg
CREATE VIEW sys.dump_table_constraint_type AS
SELECT
- 'ALTER TABLE ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
- ' ADD CONSTRAINT ' || sys.DQ(con) || ' '||
+ 'ALTER TABLE ' || sys.FQN(sch, tbl) || ' ADD CONSTRAINT ' || sys.DQ(con)
|| ' '||
tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt,
sch schema_name,
tbl table_name,
@@ -63,11 +62,12 @@ CREATE VIEW sys.dump_table_grants AS
WITH table_grants (sname, tname, grantee, grants, grantor, grantable)
AS (SELECT s.name, t.name, a.name, sum(p.privileges), g.name, p.grantable
FROM sys.schemas s, sys.tables t, sys.auths a, sys.privileges p,
sys.auths g
- WHERE p.obj_id = t.id AND p.auth_id = a.id AND t.schema_id = s.id AND
t.system = FALSE AND p.grantor = g.id GROUP BY s.name, t.name, a.name, g.name,
p.grantable
+ WHERE p.obj_id = t.id AND p.auth_id = a.id AND t.schema_id = s.id AND
t.system = FALSE AND p.grantor = g.id
+ GROUP BY s.name, t.name, a.name, g.name, p.grantable
ORDER BY s.name, t.name, a.name, g.name, p.grantable)
SELECT
- 'GRANT ' || pc.privilege_code_name || ' ON TABLE ' || sys.dq(sname)
- || '.' || sys.dq(tname) || ' TO ' || ifthenelse(grantee = 'public',
'PUBLIC', sys.dq(grantee))
+ 'GRANT ' || pc.privilege_code_name || ' ON TABLE ' || sys.FQN(sname, tname)
+ || ' TO ' || ifthenelse(grantee = 'public', 'PUBLIC', sys.dq(grantee))
|| CASE WHEN grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END || ';'
stmt,
sname schema_name,
tname table_name,
@@ -76,8 +76,8 @@ CREATE VIEW sys.dump_table_grants AS
CREATE VIEW sys.dump_column_grants AS
SELECT
- 'GRANT ' || pc.privilege_code_name || '(' || sys.dq(c.name) || ') ON '
- || sys.dq(s.name) || '.' || sys.dq(t.name) || ' TO ' ||
ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name))
+ 'GRANT ' || pc.privilege_code_name || '(' || sys.dq(c.name) || ') ON ' ||
sys.FQN(s.name, t.name)
+ || ' TO ' || ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name))
|| CASE WHEN p.grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END ||
';' stmt,
s.name schema_name,
t.name table_name,
@@ -110,9 +110,8 @@ CREATE VIEW sys.dump_function_grants AS
func_args(func_id, func_arg) AS
(SELECT func_id, func_arg FROM func_args_all WHERE number = max_number)
SELECT
- 'GRANT ' || pc.privilege_code_name || ' ON '
- || ft.function_type_keyword || ' '
- || sys.dq(s.name) || '.' || sys.dq(f.name) || '(' ||
coalesce(fa.func_arg, '') || ') TO '
+ 'GRANT ' || pc.privilege_code_name || ' ON ' || ft.function_type_keyword
|| ' '
+ || sys.FQN(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ') TO '
|| ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name))
|| CASE WHEN p.grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END ||
';' stmt,
s.name schema_name,
@@ -136,9 +135,7 @@ CREATE VIEW sys.dump_function_grants AS
CREATE VIEW sys.dump_indices AS
SELECT
- 'CREATE ' || tpe || ' ' ||
- sys.DQ(ind) || ' ON ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
- '(' || GROUP_CONCAT(col) || ');' stmt,
+ 'CREATE ' || tpe || ' ' || sys.DQ(ind) || ' ON ' || sys.FQN(sch, tbl) ||
'(' || GROUP_CONCAT(col) || ');' stmt,
sch schema_name,
tbl table_name,
ind index_name
@@ -153,9 +150,9 @@ CREATE VIEW sys.dump_column_defaults AS
CREATE VIEW sys.dump_foreign_keys AS
SELECT
- 'ALTER TABLE ' || sys.DQ(fk_s) || '.'|| sys.DQ(fk_t) || ' ADD CONSTRAINT '
|| sys.DQ(fk) || ' ' ||
+ 'ALTER TABLE ' || sys.FQN(fk_s, fk_t) || ' ADD CONSTRAINT ' || sys.DQ(fk)
|| ' ' ||
'FOREIGN KEY(' || GROUP_CONCAT(sys.DQ(fk_c), ',') ||') ' ||
- 'REFERENCES ' || sys.DQ(pk_s) || '.' || sys.DQ(pk_t) || '(' ||
GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||
+ 'REFERENCES ' || sys.FQN(pk_s, pk_t) || '(' ||
GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||
'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update ||
';' stmt,
fk_s foreign_schema_name,
@@ -167,7 +164,7 @@ CREATE VIEW sys.dump_foreign_keys AS
CREATE VIEW sys.dump_partition_tables AS
SELECT
- sys.ALTER_TABLE(m_sch, m_tbl) || ' ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||
+ 'ALTER TABLE ' || sys.FQN(m_sch, m_tbl) || ' ADD TABLE ' || sys.FQN(p_sch,
p_tbl) ||
CASE
WHEN tpe = 'VALUES' THEN ' AS PARTITION IN (' || pvalues || ')'
WHEN tpe = 'RANGE' THEN ' AS PARTITION FROM ' || ifthenelse(minimum IS
NOT NULL, sys.SQ(minimum), 'RANGE MINVALUE') || ' TO ' || ifthenelse(maximum IS
NOT NULL, sys.SQ(maximum), 'RANGE MAXVALUE')
diff --git a/sql/test/Dependencies/Tests/dependency_DBobjects.test
b/sql/test/Dependencies/Tests/dependency_DBobjects.test
--- a/sql/test/Dependencies/Tests/dependency_DBobjects.test
+++ b/sql/test/Dependencies/Tests/dependency_DBobjects.test
@@ -404,9 +404,6 @@ describe_type
describe_columns
DEP_FUNC
dq
-fqn
-DEP_FUNC
-dq
get_merge_table_partition_expressions
DEP_FUNC
dq
@@ -419,9 +416,6 @@ f1
f2
DEP_FUNC
fqn
-alter_table
-DEP_FUNC
-fqn
schema_guard
DEP_FUNC
getproj4
diff --git a/sql/test/Dependencies/Tests/dependency_functions.test
b/sql/test/Dependencies/Tests/dependency_functions.test
--- a/sql/test/Dependencies/Tests/dependency_functions.test
+++ b/sql/test/Dependencies/Tests/dependency_functions.test
@@ -47,9 +47,6 @@ describe_type
describe_columns
DEP_FUNC
dq
-fqn
-DEP_FUNC
-dq
get_merge_table_partition_expressions
DEP_FUNC
dq
@@ -68,9 +65,6 @@ f1
f3
DEP_FUNC
fqn
-alter_table
-DEP_FUNC
-fqn
schema_guard
DEP_FUNC
getproj4
@@ -117,9 +111,6 @@ describe_type
describe_columns
DEP_FUNC
dq
-fqn
-DEP_FUNC
-dq
get_merge_table_partition_expressions
DEP_FUNC
dq
@@ -129,9 +120,6 @@ dump_table_data
dump_database
DEP_FUNC
fqn
-alter_table
-DEP_FUNC
-fqn
schema_guard
DEP_FUNC
getproj4
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -281,9 +281,6 @@ describe_type
describe_columns
DEP_FUNC
dq
-fqn
-DEP_FUNC
-dq
get_merge_table_partition_expressions
DEP_FUNC
dq
@@ -293,9 +290,6 @@ dump_table_data
dump_database
DEP_FUNC
fqn
-alter_table
-DEP_FUNC
-fqn
schema_guard
DEP_FUNC
getproj4
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -147,7 +147,6 @@
\dSv sys.var_values
\dSf sys."abbrev"
\dSf sys."alpha"
-\dSf sys."alter_table"
\dSf sys."analyze"
\dSf sys."bbp"
\dSf sys."broadcast"
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
