Changeset: 655805f9988b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/655805f9988b
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Jul2021
Log Message:
Simplified upgrade query.
diffs (truncated from 3351 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
@@ -3219,88 +3219,94 @@ sql_update_jul2021(Client c, mvc *sql, c
// Set the system flag for the new dump and describe
SQL objects.
pos += snprintf(buf + pos, bufsize - pos,
- "UPDATE sys.functions f SET system =
true WHERE\n"
+ "UPDATE sys.functions SET system = true
WHERE\n"
+ " system <> true AND\n"
+ " schema_id = 2000 AND\n"
+ " type = %d AND\n"
+ " name in (\n"
+ " 'describe_columns',\n"
+ " 'describe_function',\n"
+ " 'describe_table',\n"
+ " 'dump_database'\n"
+ " );\n",
+ F_UNION);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "UPDATE sys.functions SET system = true
WHERE\n"
+ " system <> true AND\n"
" schema_id = 2000 AND\n"
- " (\n"
- " (\n"
- " type = (select
function_type_id from sys.function_types where function_type_name = 'Function
returning a table') AND\n"
- " name in (\n"
- " 'dump_database',\n"
- " 'describe_table',\n"
- " 'describe_columns',\n"
- " 'describe_function'\n"
- " )\n"
- " ) OR\n"
- " (\n"
- " type = (select
function_type_id from sys.function_types where function_type_name = 'Scalar
function') AND\n"
- " name in (\n"
- " 'sq',\n"
- " 'dq',\n"
- " 'fqn',\n"
- " 'alter_table',\n"
- " 'replace_first',\n"
- " 'schema_guard',\n"
- "
'get_merge_table_partition_expressions',\n"
- "
'get_remote_table_expressions',\n"
- " 'esc',\n"
- " 'prepare_esc',\n"
- "
'current_size_dump_statements',\n"
- " 'describe_type'\n"
- " )\n"
- " ) OR\n"
- " (\n"
- " type = (select
function_type_id from sys.function_types where function_type_keyword =
'PROCEDURE') AND\n"
- " name in (\n"
- " 'eval',\n"
- " '_dump_table_data',\n"
- " 'dump_table_data'\n"
- " )\n"
- " )\n"
- ");\n"
- "UPDATE sys._tables SET system = true
WHERE \n"
+ " type = %d AND\n"
+ " name in (\n"
+ " 'alter_table',\n"
+ " 'describe_type',\n"
+ " 'dq',\n"
+ " 'esc',\n"
+ " 'fqn',\n"
+ "
'get_merge_table_partition_expressions',\n"
+ "
'get_remote_table_expressions',\n"
+ " 'prepare_esc',\n"
+ " 'replace_first',\n"
+ " 'schema_guard',\n"
+ " 'sq'\n"
+ " );\n",
+ F_FUNC);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "UPDATE sys.functions SET system = true
WHERE\n"
+ " system <> true AND\n"
" schema_id = 2000 AND\n"
- " (\n"
- " (\n"
- " type = (select
table_type_id from sys.table_types where table_type_name = 'TABLE') AND\n"
- " name = 'dump_statements'\n"
- " ) OR\n"
- " (\n"
- " type = (select
table_type_id from sys.table_types where table_type_name = 'VIEW') AND\n"
- " name in (\n"
- "
'describe_constraints',\n"
- " 'describe_indices',\n"
- "
'describe_column_defaults',\n"
- "
'describe_foreign_keys',\n"
- " 'describe_tables',\n"
- " 'describe_triggers',\n"
- " 'describe_comments',\n"
- "
'fully_qualified_functions',\n"
- "
'describe_privileges',\n"
- "
'describe_user_defined_types',\n"
- "
'describe_partition_tables',\n"
- "
'describe_sequences',\n"
- "
'describe_functions',\n"
- " 'dump_create_roles',\n"
- " 'dump_create_users',\n"
- "
'dump_create_schemas',\n"
- "
'dump_add_schemas_to_users',\n"
- "
'dump_grant_user_privileges',\n"
- "
'dump_table_constraint_type',\n"
- " 'dump_indices',\n"
- "
'dump_column_defaults',\n"
- " 'dump_foreign_keys',\n"
- "
'dump_partition_tables',\n"
- " 'dump_sequences',\n"
- "
'dump_start_sequences',\n"
- " 'dump_functions',\n"
- " 'dump_tables',\n"
- " 'dump_triggers',\n"
- " 'dump_comments',\n"
- "
'dump_user_defined_types',\n"
- " 'dump_privileges',\n"
- " 'dump_statements')\n"
- " )\n"
- " );\n");
+ " type = %d AND\n"
+ " name in (\n"
+ " '_dump_table_data',\n"
+ " 'dump_table_data',\n"
+ " 'eval'\n"
+ " );\n",
+ F_PROC);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "UPDATE sys._tables SET system = true
WHERE\n"
+ " system <> true AND\n"
+ " schema_id = 2000 AND\n"
+ " type = %d AND\n"
+ " name = 'dump_statements';\n",
+ (int) tt_table);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "UPDATE sys._tables SET system = true
WHERE\n"
+ " system <> true AND\n"
+ " schema_id = 2000 AND\n"
+ " type = %d AND\n"
+ " name in (\n"
+ " 'describe_column_defaults',\n"
+ " 'describe_comments',\n"
+ " 'describe_constraints',\n"
+ " 'describe_foreign_keys',\n"
+ " 'describe_functions',\n"
+ " 'describe_indices',\n"
+ " 'describe_partition_tables',\n"
+ " 'describe_privileges',\n"
+ " 'describe_sequences',\n"
+ " 'describe_tables',\n"
+ " 'describe_triggers',\n"
+ "
'describe_user_defined_types',\n"
+ " 'dump_add_schemas_to_users',\n"
+ " 'dump_column_defaults',\n"
+ " 'dump_comments',\n"
+ " 'dump_create_roles',\n"
+ " 'dump_create_schemas',\n"
+ " 'dump_create_users',\n"
+ " 'dump_foreign_keys',\n"
+ " 'dump_functions',\n"
+ "
'dump_grant_user_privileges',\n"
+ " 'dump_indices',\n"
+ " 'dump_partition_tables',\n"
+ " 'dump_privileges',\n"
+ " 'dump_sequences',\n"
+ " 'dump_start_sequences',\n"
+ " 'dump_statements',\n"
+ "
'dump_table_constraint_type',\n"
+ " 'dump_tables',\n"
+ " 'dump_triggers',\n"
+ " 'dump_user_defined_types',\n"
+ " 'fully_qualified_functions'\n"
+ " );\n",
+ (int) tt_view);
/* scoping2 branch changes, the 'users' view has to be
re-created because of the 'schema_path' addition on 'db_user_info' table
However 'dependency_schemas_on_users' has a
dependency on 'users', so it has to be re-created as well */
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -4949,87 +4949,84 @@ BEGIN
INSERT INTO sys.dump_statements VALUES ( (SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
RETURN sys.dump_statements;
END;
-UPDATE sys.functions f SET system = true WHERE
+UPDATE sys.functions SET system = true WHERE
+ system <> true AND
+ schema_id = 2000 AND
+ type = 5 AND
+ name in (
+ 'describe_columns',
+ 'describe_function',
+ 'describe_table',
+ 'dump_database'
+ );
+UPDATE sys.functions SET system = true WHERE
+ system <> true AND
schema_id = 2000 AND
- (
- (
- type = (select function_type_id from sys.function_types where
function_type_name = 'Function returning a table') AND
- name in (
- 'dump_database',
- 'describe_table',
- 'describe_columns',
- 'describe_function'
- )
- ) OR
- (
- type = (select function_type_id from sys.function_types where
function_type_name = 'Scalar function') AND
- name in (
- 'sq',
- 'dq',
- 'fqn',
- 'alter_table',
- 'replace_first',
- 'schema_guard',
- 'get_merge_table_partition_expressions',
- 'get_remote_table_expressions',
- 'esc',
- 'prepare_esc',
- 'current_size_dump_statements',
- 'describe_type'
- )
- ) OR
- (
- type = (select function_type_id from sys.function_types where
function_type_keyword = 'PROCEDURE') AND
- name in (
- 'eval',
- '_dump_table_data',
- 'dump_table_data'
- )
- )
-);
-UPDATE sys._tables SET system = true WHERE
+ type = 1 AND
+ name in (
+ 'alter_table',
+ 'describe_type',
+ 'dq',
+ 'esc',
+ 'fqn',
+ 'get_merge_table_partition_expressions',
+ 'get_remote_table_expressions',
+ 'prepare_esc',
+ 'replace_first',
+ 'schema_guard',
+ 'sq'
+ );
+UPDATE sys.functions SET system = true WHERE
+ system <> true AND
+ schema_id = 2000 AND
+ type = 2 AND
+ name in (
+ '_dump_table_data',
+ 'dump_table_data',
+ 'eval'
+ );
+UPDATE sys._tables SET system = true WHERE
+ system <> true AND
schema_id = 2000 AND
- (
- (
- type = (select table_type_id from sys.table_types where
table_type_name = 'TABLE') AND
- name = 'dump_statements'
- ) OR
- (
- type = (select table_type_id from sys.table_types where
table_type_name = 'VIEW') AND
- name in (
- 'describe_constraints',
- 'describe_indices',
- 'describe_column_defaults',
- 'describe_foreign_keys',
- 'describe_tables',
- 'describe_triggers',
- 'describe_comments',
- 'fully_qualified_functions',
- 'describe_privileges',
- 'describe_user_defined_types',
- 'describe_partition_tables',
- 'describe_sequences',
- 'describe_functions',
- 'dump_create_roles',
- 'dump_create_users',
- 'dump_create_schemas',
- 'dump_add_schemas_to_users',
- 'dump_grant_user_privileges',
- 'dump_table_constraint_type',
- 'dump_indices',
- 'dump_column_defaults',
- 'dump_foreign_keys',
- 'dump_partition_tables',
- 'dump_sequences',
- 'dump_start_sequences',
- 'dump_functions',
- 'dump_tables',
- 'dump_triggers',
- 'dump_comments',
- 'dump_user_defined_types',
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
