Changeset: 91916242adc3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=91916242adc3
Added Files:
sql/test/rename/Tests/rename08.sql
sql/test/rename/Tests/rename08.stable.err
sql/test/rename/Tests/rename08.stable.out
Modified Files:
sql/backends/monet5/sql_cat.c
sql/server/rel_schema.c
sql/test/rename/Tests/All
sql/test/rename/Tests/rename05.stable.err
sql/test/rename/Tests/rename07.sql
sql/test/rename/Tests/rename07.stable.out
Branch: merge-statements
Log Message:
When setting the schema of a table, copy available sql keys and indexes as well.
diffs (truncated from 301 to 300 lines):
diff --git a/sql/backends/monet5/sql_cat.c b/sql/backends/monet5/sql_cat.c
--- a/sql/backends/monet5/sql_cat.c
+++ b/sql/backends/monet5/sql_cat.c
@@ -1606,7 +1606,7 @@ SQLrename_table(Client cntxt, MalBlkPtr
if (t->system)
throw(SQL, "sql.rename_table", SQLSTATE(42000) "ALTER TABLE:
cannot rename a system table");
if (mvc_check_dependency(sql, t->base.id, TABLE_DEPENDENCY, NULL))
- throw (SQL,"sql.rename_table", SQLSTATE(2BM37) "ALTER TABLE:
unable to rename table %s (there are database objects which depend on it)",
old_name);
+ throw (SQL,"sql.rename_table", SQLSTATE(2BM37) "ALTER TABLE:
unable to rename table '%s' (there are database objects which depend on it)",
old_name);
if (!new_name || strcmp(new_name, str_nil) == 0)
throw(SQL, "sql.rename_table", SQLSTATE(3F000) "ALTER TABLE:
invalid new table name");
if (mvc_bind_table(sql, s, new_name))
diff --git a/sql/server/rel_schema.c b/sql/server/rel_schema.c
--- a/sql/server/rel_schema.c
+++ b/sql/server/rel_schema.c
@@ -2499,7 +2499,7 @@ rel_rename_table(mvc *sql, char* schema_
if (t->system)
return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: cannot
rename a system table");
if (mvc_check_dependency(sql, t->base.id, TABLE_DEPENDENCY, NULL))
- return sql_error(sql, 02, SQLSTATE(2BM37) "ALTER TABLE: unable
to rename table %s (there are database objects which depend on it)", old_name);
+ return sql_error(sql, 02, SQLSTATE(2BM37) "ALTER TABLE: unable
to rename table '%s' (there are database objects which depend on it)",
old_name);
if (!new_name || strcmp(new_name, str_nil) == 0)
return sql_error(sql, 02, SQLSTATE(3F000) "ALTER TABLE: invalid
new table name");
if (mvc_bind_table(sql, s, new_name))
@@ -2564,9 +2564,12 @@ rel_rename_column(mvc *sql, char* schema
return rel;
}
+extern list *rel_dependencies(mvc *sql, sql_rel *r);
+
static sql_rel *
rel_set_table_schema(mvc *sql, char* old_schema, char *tname, char
*new_schema, int if_exists)
{
+ node *n;
sql_schema *os, *ns;
sql_table *ot, *nt;
sql_rel *l, *r, *inserts;
@@ -2589,12 +2592,16 @@ rel_set_table_schema(mvc *sql, char* old
return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: cannot
set schema of a system table");
if (isTempSchema(os) || isTempTable(ot))
return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: not
possible to change a temporary table schema");
+ if (isView(ot))
+ return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: not
possible to change schema of a view");
+ if (isMergeTable(ot))
+ return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: not
possible to change schema of a merge table");
if (mvc_check_dependency(sql, ot->base.id, TABLE_DEPENDENCY, NULL))
- return sql_error(sql, 02, SQLSTATE(2BM37) "ALTER TABLE: unable
to set schema of table %s (there are database objects which depend on it)",
tname);
+ return sql_error(sql, 02, SQLSTATE(2BM37) "ALTER TABLE: unable
to set schema of table '%s' (there are database objects which depend on it)",
tname);
if (!(ns = mvc_bind_schema(sql, new_schema)))
return sql_error(sql, 02, SQLSTATE(42S02) "ALTER TABLE: no such
schema '%s'", new_schema);
if (!mvc_schema_privs(sql, ns))
- return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: access
denied for %s to schema '%s'", stack_get_string(sql, "current_user"),
new_schema);
+ return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: access
denied for '%s' to schema '%s'", stack_get_string(sql, "current_user"),
new_schema);
if (isTempSchema(ns))
return sql_error(sql, 02, SQLSTATE(3F000) "ALTER TABLE: not
possible to change table's schema to temporary");
if (mvc_bind_table(sql, ns, tname))
@@ -2602,13 +2609,25 @@ rel_set_table_schema(mvc *sql, char* old
if ((nt = mvc_create_table(sql, ns, tname, ot->type, 0,
SQL_DECLARED_TABLE, ot->commit_action, -1, ot->properties)) == NULL)
return NULL;
- for (node *cn = ot->columns.set->h; cn; cn = cn->next) {
- sql_column *col = (sql_column*) cn->data;
- if (!mvc_create_column(sql, nt, col->base.name, &col->type))
- return NULL;
+
+ for (n = ot->columns.set->h; n; n = n->next) {
+ sql_column *oc = (sql_column*) n->data;
+ if (!mvc_copy_column(sql, nt, oc))
+ return sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE:
%s_%s_%s conflicts", ns->base.name, nt->base.name, oc->base.name);
}
+
+ if (ot->idxs.set)
+ for (n = ot->idxs.set->h; n; n = n->next)
+ mvc_copy_idx(sql, nt, (sql_idx*) n->data);
+
+ if (ot->keys.set)
+ for (n = ot->keys.set->h; n; n = n->next)
+ mvc_copy_key(sql, nt, (sql_key*) n->data);
+
+ if (ot->members.set || ot->triggers.set)
+ return sql_error(sql, 02, SQLSTATE(2BM37) "ALTER TABLE: unable
to set schema of table '%s' (there are database objects which depend on it)",
tname);
+
l = rel_table(sql, DDL_CREATE_TABLE, new_schema, nt, 0);
-
inserts = rel_basetable(sql, ot, tname);
inserts = rel_project(sql->sa, inserts, rel_projections(sql, inserts,
NULL, 1, 0));
l = rel_insert(sql, l, inserts);
diff --git a/sql/test/rename/Tests/All b/sql/test/rename/Tests/All
--- a/sql/test/rename/Tests/All
+++ b/sql/test/rename/Tests/All
@@ -6,3 +6,4 @@ rename04
rename05
rename06
rename07
+rename08
diff --git a/sql/test/rename/Tests/rename05.stable.err
b/sql/test/rename/Tests/rename05.stable.err
--- a/sql/test/rename/Tests/rename05.stable.err
+++ b/sql/test/rename/Tests/rename05.stable.err
@@ -34,7 +34,7 @@ ERROR = !ALTER SCHEMA: cannot rename a s
CODE = 3F000
MAPI = (monetdb) /var/tmp/mtest-23272/.s.monetdb.36538
QUERY = alter table "ntable" rename to "ttable"; --error because of
dependencies
-ERROR = !ALTER TABLE: unable to rename table ntable (there are database
objects which depend on it)
+ERROR = !ALTER TABLE: unable to rename table 'ntable' (there are database
objects which depend on it)
CODE = 2BM37
# 10:55:46 >
diff --git a/sql/test/rename/Tests/rename07.sql
b/sql/test/rename/Tests/rename07.sql
--- a/sql/test/rename/Tests/rename07.sql
+++ b/sql/test/rename/Tests/rename07.sql
@@ -24,6 +24,5 @@ rollback;
select "ss"."name" from "sys"."tables" "tt" inner join "sys"."schemas" "ss" on
"tt"."schema_id" = "ss"."id" where "tt"."name" = 'atable';
select "a" from "newtables"."atable" where false;
-drop table "newtables"."atable";
-drop schema "oldtables";
-drop schema "newtables";
+drop schema "oldtables" cascade;
+drop schema "newtables" cascade;
diff --git a/sql/test/rename/Tests/rename07.stable.out
b/sql/test/rename/Tests/rename07.stable.out
--- a/sql/test/rename/Tests/rename07.stable.out
+++ b/sql/test/rename/Tests/rename07.stable.out
@@ -125,9 +125,8 @@ Ready.
% a # name
% int # type
% 1 # length
-#drop table "newtables"."atable";
-#drop schema "oldtables";
-#drop schema "newtables";
+#drop schema "oldtables" cascade;
+#drop schema "newtables" cascade;
# 17:01:59 >
# 17:01:59 > "Done."
diff --git a/sql/test/rename/Tests/rename08.sql
b/sql/test/rename/Tests/rename08.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/rename/Tests/rename08.sql
@@ -0,0 +1,24 @@
+create schema "oldtables";
+create schema "newtables";
+create table "oldtables"."t1" ("a" int primary key, "b" int default '2');
+
+select "kk"."type" from "sys"."keys" "kk" inner join "sys"."tables" "tt" on
"kk"."table_id" = "tt"."id" where "tt"."name" = 't1';
+select "ii"."type" from "sys"."idxs" "ii" inner join "sys"."tables" "tt" on
"ii"."table_id" = "tt"."id" where "tt"."name" = 't1';
+insert into "oldtables"."t1" values (3, default);
+select "a", "b" from "oldtables"."t1";
+
+alter table "oldtables"."t1" set schema "newtables";
+
+select "kk"."type" from "sys"."keys" "kk" inner join "sys"."tables" "tt" on
"kk"."table_id" = "tt"."id" where "tt"."name" = 't1';
+select "ii"."type" from "sys"."idxs" "ii" inner join "sys"."tables" "tt" on
"ii"."table_id" = "tt"."id" where "tt"."name" = 't1';
+insert into "newtables"."t1" values (4, default);
+select "a", "b" from "newtables"."t1";
+
+create trigger "newtables"."tr1" after insert on "newtables"."t1" insert into
"newtables"."t1" values (5, default);
+alter table "newtables"."t1" set schema "oldtables"; --error, dependency on
trigger
+
+drop schema "oldtables" cascade;
+drop schema "newtables" cascade;
+
+select "kk"."type" from "sys"."keys" "kk" inner join "sys"."tables" "tt" on
"kk"."table_id" = "tt"."id" where "tt"."name" = 't1';
+select "ii"."type" from "sys"."idxs" "ii" inner join "sys"."tables" "tt" on
"ii"."table_id" = "tt"."id" where "tt"."name" = 't1';
diff --git a/sql/test/rename/Tests/rename08.stable.err
b/sql/test/rename/Tests/rename08.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/rename/Tests/rename08.stable.err
@@ -0,0 +1,39 @@
+stderr of test 'rename08` in directory 'sql/test/rename` itself:
+
+
+# 14:06:46 >
+# 14:06:46 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=31771" "--set"
"mapi_usock=/var/tmp/mtest-8540/.s.monetdb.31771" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-merge-statements/BUILD/var/MonetDB/mTests_sql_test_rename"
"--set" "embedded_c=true"
+# 14:06:46 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-merge-statements/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 31771
+# cmdline opt mapi_usock = /var/tmp/mtest-8540/.s.monetdb.31771
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-merge-statements/BUILD/var/MonetDB/mTests_sql_test_rename
+# cmdline opt embedded_c = true
+# cmdline opt gdk_debug = 553648138
+
+# 14:06:46 >
+# 14:06:46 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-8540" "--port=31771"
+# 14:06:46 >
+
+MAPI = (monetdb) /var/tmp/mtest-8540/.s.monetdb.31771
+QUERY = alter table "newtables"."t1" set schema "oldtables"; --error,
dependency on trigger
+ERROR = !ALTER TABLE: unable to set schema of table 't1' (there are database
objects which depend on it)
+CODE = 2BM37
+
+# 14:06:46 >
+# 14:06:46 > "Done."
+# 14:06:46 >
+
diff --git a/sql/test/rename/Tests/rename08.stable.out
b/sql/test/rename/Tests/rename08.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/rename/Tests/rename08.stable.out
@@ -0,0 +1,89 @@
+stdout of test 'rename08` in directory 'sql/test/rename` itself:
+
+
+# 14:06:46 >
+# 14:06:46 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=31771" "--set"
"mapi_usock=/var/tmp/mtest-8540/.s.monetdb.31771" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-merge-statements/BUILD/var/MonetDB/mTests_sql_test_rename"
"--set" "embedded_c=true"
+# 14:06:46 >
+
+# MonetDB 5 server v11.32.0 (hg id: 3c2a3f8919bb+)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_rename', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.528 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://wired-142.cwi.nl:31771/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-8540/.s.monetdb.31771
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 14:06:46 >
+# 14:06:46 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-8540" "--port=31771"
+# 14:06:46 >
+
+#create schema "oldtables";
+#create schema "newtables";
+#create table "oldtables"."t1" ("a" int primary key, "b" int default '2');
+#select "kk"."type" from "sys"."keys" "kk" inner join "sys"."tables" "tt" on
"kk"."table_id" = "tt"."id" where "tt"."name" = 't1';
+% .kk # table_name
+% type # name
+% int # type
+% 1 # length
+[ 0 ]
+#select "ii"."type" from "sys"."idxs" "ii" inner join "sys"."tables" "tt" on
"ii"."table_id" = "tt"."id" where "tt"."name" = 't1';
+% .ii # table_name
+% type # name
+% int # type
+% 1 # length
+[ 0 ]
+#insert into "oldtables"."t1" values (3, default);
+[ 1 ]
+#select "a", "b" from "oldtables"."t1";
+% oldtables.t1, oldtables.t1 # table_name
+% a, b # name
+% int, int # type
+% 1, 1 # length
+[ 3, 2 ]
+#alter table "oldtables"."t1" set schema "newtables";
+#select "kk"."type" from "sys"."keys" "kk" inner join "sys"."tables" "tt" on
"kk"."table_id" = "tt"."id" where "tt"."name" = 't1';
+% .kk # table_name
+% type # name
+% int # type
+% 1 # length
+[ 0 ]
+#select "ii"."type" from "sys"."idxs" "ii" inner join "sys"."tables" "tt" on
"ii"."table_id" = "tt"."id" where "tt"."name" = 't1';
+% .ii # table_name
+% type # name
+% int # type
+% 1 # length
+[ 0 ]
+#insert into "newtables"."t1" values (4, default);
+[ 1 ]
+#select "a", "b" from "newtables"."t1";
+% newtables.t1, newtables.t1 # table_name
+% a, b # name
+% int, int # type
+% 1, 1 # length
+[ 3, 2 ]
+[ 4, 2 ]
+#create trigger "newtables"."tr1" after insert on "newtables"."t1" insert into
"newtables"."t1" values (5, default);
+#drop schema "oldtables" cascade;
+#drop schema "newtables" cascade;
+#select "kk"."type" from "sys"."keys" "kk" inner join "sys"."tables" "tt" on
"kk"."table_id" = "tt"."id" where "tt"."name" = 't1';
+% .kk # table_name
+% type # name
+% int # type
+% 1 # length
+#select "ii"."type" from "sys"."idxs" "ii" inner join "sys"."tables" "tt" on
"ii"."table_id" = "tt"."id" where "tt"."name" = 't1';
+% .ii # table_name
+% type # name
+% int # type
+% 1 # length
+
+# 14:06:46 >
+# 14:06:46 > "Done."
+# 14:06:46 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
