Changeset: 096237b9ec5b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=096237b9ec5b
Added Files:
sql/test/Tests/create-or-replace-triggers.sql
sql/test/Tests/create-or-replace-triggers.stable.err
sql/test/Tests/create-or-replace-triggers.stable.out
sql/test/Tests/create-or-replace-views.sql
sql/test/Tests/create-or-replace-views.stable.err
sql/test/Tests/create-or-replace-views.stable.out
Modified Files:
clients/mapiclient/mhelp.c
sql/server/rel_psm.c
sql/server/rel_schema.c
sql/server/sql_parser.y
sql/test/Tests/All
Branch: sqlextra
Log Message:
Ups I was wrong, I meant create or replace views. Also added for triggers
diffs (truncated from 553 to 300 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -205,8 +205,8 @@ SQLhelp sqlhelp[] = {
NULL},
{"CREATE TRIGGER",
"",
- "CREATE TRIGGER wname { BEFORE | AFTER } { INSERT | DELETE | TRUNCATE
| UPDATE [ OF ident [',' ident]] } ...\n"
- "ON qname REFERENCING trigger_reference... triggered_action",
+ "CREATE [ OR REPLACE ] TRIGGER wname { BEFORE | AFTER } { INSERT |
DELETE | TRUNCATE ...\n"
+ " | UPDATE [ OF ident [',' ident]] } ON qname REFERENCING
trigger_reference... triggered_action",
"trigger_reference",
NULL},
{"CREATE TYPE",
@@ -216,7 +216,7 @@ SQLhelp sqlhelp[] = {
NULL},
{"CREATE VIEW",
"",
- "CREATE VIEW [ IF NOT EXISTS ] qname [ column_list ] AS {
query_expression | '(' query_expression ')' }\n"
+ "CREATE [ OR REPLACE ] VIEW qname [ column_list ] AS {
query_expression | '(' query_expression ')' }\n"
"[ WITH CHECK OPTION ]",
"column_list,query_expression",
NULL},
diff --git a/sql/server/rel_psm.c b/sql/server/rel_psm.c
--- a/sql/server/rel_psm.c
+++ b/sql/server/rel_psm.c
@@ -1132,29 +1132,30 @@ static void
}
static sql_rel *
-create_trigger(mvc *sql, dlist *qname, int time, symbol *trigger_event, dlist
*tqname, dlist *opt_ref, dlist *triggered_action)
+create_trigger(mvc *sql, dlist *qname, int time, symbol *trigger_event, dlist
*tqname, dlist *opt_ref, dlist *triggered_action, int replace)
{
const char *triggername = qname_table(qname);
const char *sname = qname_schema(tqname);
const char *tname = qname_table(tqname);
sql_schema *ss = cur_schema(sql);
sql_table *t = NULL;
+ sql_trigger *st = NULL;
int instantiate = (sql->emode == m_instantiate);
int create = (!instantiate && sql->emode != m_deps), event, orientation;
list *sq = NULL;
sql_rel *r = NULL;
- char *q;
+ char *q, *base = replace ? "CREATE OR REPLACE" : "CREATE";
dlist *columns = trigger_event->data.lval;
const char *old_name = NULL, *new_name = NULL;
dlist *stmts = triggered_action->h->next->next->data.lval;
symbol *condition = triggered_action->h->next->data.sym;
-
+
if (!sname)
sname = ss->base.name;
if (sname && !(ss = mvc_bind_schema(sql, sname)))
- return sql_error(sql, 02, "3F000!CREATE TRIGGER: no such schema
'%s'", sname);
+ return sql_error(sql, 02, "3F000!%s TRIGGER: no such schema
'%s'", base, sname);
if (opt_ref) {
dnode *dl = opt_ref->h;
@@ -1169,16 +1170,21 @@ create_trigger(mvc *sql, dlist *qname, i
new_name = n;
}
}
+
if (create && !mvc_schema_privs(sql, ss))
- return sql_error(sql, 02, "CREATE TRIGGER: access denied for %s
to schema ;'%s'", stack_get_string(sql, "current_user"), ss->base.name);
- if (create && mvc_bind_trigger(sql, ss, triggername) != NULL)
- return sql_error(sql, 02, "CREATE TRIGGER: name '%s' already in
use", triggername);
-
+ return sql_error(sql, 02, "%s TRIGGER: access denied for %s to
schema ;'%s'", base, stack_get_string(sql, "current_user"), ss->base.name);
if (create && !(t = mvc_bind_table(sql, ss, tname)))
- return sql_error(sql, 02, "CREATE TRIGGER: unknown table '%s'",
tname);
- if (create && isView(t))
- return sql_error(sql, 02, "CREATE TRIGGER: cannot create
trigger on view '%s'", tname);
-
+ return sql_error(sql, 02, "%s TRIGGER: unknown table '%s'",
base, tname);
+ if (create && isView(t))
+ return sql_error(sql, 02, "%s TRIGGER: cannot create trigger on
view '%s'", base, tname);
+ if (create && (st = mvc_bind_trigger(sql, ss, triggername)) != NULL) {
+ if (replace) {
+ mvc_drop_trigger(sql, ss, st);
+ } else {
+ return sql_error(sql, 02, "%s TRIGGER: name '%s'
already in use", base, triggername);
+ }
+ }
+
if (create) {
switch (trigger_event->token) {
case SQL_INSERT:
@@ -1443,7 +1449,7 @@ rel_psm(mvc *sql, symbol *s)
dlist *l = s->data.lval;
assert(l->h->next->type == type_int);
- ret = create_trigger(sql, l->h->data.lval,
l->h->next->data.i_val, l->h->next->next->data.sym,
l->h->next->next->next->data.lval, l->h->next->next->next->next->data.lval,
l->h->next->next->next->next->next->data.lval);
+ ret = create_trigger(sql, l->h->data.lval,
l->h->next->data.i_val, l->h->next->next->data.sym,
l->h->next->next->next->data.lval, l->h->next->next->next->next->data.lval,
l->h->next->next->next->next->next->data.lval,
l->h->next->next->next->next->next->next->data.i_val);
sql->type = Q_SCHEMA;
}
break;
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
@@ -993,7 +993,7 @@ rel_add_intern(mvc *sql, sql_rel *rel)
static sql_rel *
-rel_create_view(mvc *sql, sql_schema *ss, dlist *qname, dlist *column_spec,
symbol *query, int check, int persistent, int if_not_exists)
+rel_create_view(mvc *sql, sql_schema *ss, dlist *qname, dlist *column_spec,
symbol *query, int check, int persistent, int replace)
{
char *name = qname_table(qname);
char *sname = qname_schema(qname);
@@ -1002,23 +1002,35 @@ rel_create_view(mvc *sql, sql_schema *ss
int instantiate = (sql->emode == m_instantiate || !persistent);
int deps = (sql->emode == m_deps);
int create = (!instantiate && !deps);
+ char *base = replace ? "CREATE OR REPLACE" : "CREATE";
-(void)ss;
+ (void) ss;
(void) check; /* Stefan: unused!? */
if (sname && !(s = mvc_bind_schema(sql, sname)))
return sql_error(sql, 02, "3F000!CREATE VIEW: no such schema
'%s'", sname);
if (s == NULL)
s = cur_schema(sql);
- if (create && mvc_bind_table(sql, s, name) != NULL) {
- if (if_not_exists) {
- return NULL;
+ if (create && (!mvc_schema_privs(sql, s) && !(isTempSchema(s) &&
persistent == SQL_LOCAL_TEMP))) {
+ return sql_error(sql, 02, "42000!%s VIEW: access denied for %s
to schema ;'%s'", base, stack_get_string(sql, "current_user"), s->base.name);
+ }
+
+ if (create && (t = mvc_bind_table(sql, s, name)) != NULL) {
+ if (replace) {
+ if (!isView(t)) {
+ return sql_error(sql, 02, "42000!%s VIEW:
unable to drop view '%s': is a table", base, name);
+ } else if (t->system) {
+ return sql_error(sql, 02, "42000!%s VIEW:
cannot replace system view '%s'", base, name);
+ } else if (mvc_check_dependency(sql, t->base.id,
VIEW_DEPENDENCY, NULL)) {
+ return sql_error(sql, 02, "42000!%s VIEW:
cannot replace view '%s', there are database objects which depend on it", base,
t->base.name);
+ } else {
+ mvc_drop_table(sql, s, t, 0);
+ }
} else {
- return sql_error(sql, 02, "42S01!CREATE VIEW: name '%s'
already in use", name);
+ return sql_error(sql, 02, "42S01!%s VIEW: name '%s'
already in use", base, name);
}
- } else if (create && (!mvc_schema_privs(sql, s) && !(isTempSchema(s) &&
persistent == SQL_LOCAL_TEMP))) {
- return sql_error(sql, 02, "42000!CREATE VIEW: access denied for
%s to schema ;'%s'", stack_get_string(sql, "current_user"), s->base.name);
- } else if (query) {
+ }
+ if (query) {
sql_rel *sq = NULL;
char *q = QUERY(sql->scanner);
@@ -1026,7 +1038,7 @@ rel_create_view(mvc *sql, sql_schema *ss
SelectNode *sn = (SelectNode *) query;
if (sn->limit)
- return sql_error(sql, 01, "0A000!42000!CREATE
VIEW: LIMIT not supported");
+ return sql_error(sql, 01, "0A000!42000!%s VIEW:
LIMIT not supported", base);
}
sq = schema_selects(sql, s, query);
@@ -2012,7 +2024,7 @@ rel_schemas(mvc *sql, symbol *s)
l->h->next->next->data.sym,
l->h->next->next->next->data.i_val,
l->h->next->next->next->next->data.i_val,
-
l->h->next->next->next->next->next->data.i_val); /* if not exists */
+
l->h->next->next->next->next->next->data.i_val); /* or replace */
} break;
case SQL_DROP_TABLE:
{
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -1169,7 +1169,7 @@ opt_column:
create_statement:
create role_def { $$ = $2; }
| create table_def { $$ = $2; }
- | create view_def { $$ = $2; }
+ | view_def { $$ = $1; }
| type_def
| func_def
| index_def
@@ -1745,14 +1745,14 @@ like_table:
;
view_def:
- VIEW if_not_exists qname opt_column_list AS query_expression_def
opt_with_check_option
+ create_or_replace VIEW qname opt_column_list AS query_expression_def
opt_with_check_option
{ dlist *l = L();
append_list(l, $3);
append_list(l, $4);
append_symbol(l, $6);
append_int(l, $7);
append_int(l, TRUE); /* persistent view */
- append_int(l, $2);
+ append_int(l, $1);
$$ = _symbol_create_list( SQL_CREATE_VIEW, l );
}
;
@@ -2314,7 +2314,7 @@ Define triggered SQL-statements.
*/
trigger_def:
- create TRIGGER qname trigger_action_time trigger_event
+ create_or_replace TRIGGER qname trigger_action_time trigger_event
ON qname opt_referencing_list triggered_action
{ dlist *l = L();
append_list(l, $3);
@@ -2323,6 +2323,7 @@ trigger_def:
append_list(l, $7);
append_list(l, $8);
append_list(l, $9);
+ append_int(l, $1);
$$ = _symbol_create_list(SQL_CREATE_TRIGGER, l);
}
;
@@ -3065,7 +3066,7 @@ with_list_element:
append_symbol(l, $4);
append_int(l, FALSE); /* no with check */
append_int(l, FALSE); /* inlined view (ie not persistent) */
- append_int(l, FALSE); /* no if not exists clause */
+ append_int(l, FALSE); /* no replace clause */
$$ = _symbol_create_list( SQL_CREATE_VIEW, l );
}
;
diff --git a/sql/test/Tests/All b/sql/test/Tests/All
--- a/sql/test/Tests/All
+++ b/sql/test/Tests/All
@@ -113,3 +113,5 @@ alastair_udf_mergetable_bug
copy-into-fwf
truncate-statements
+create-or-replace-triggers
+create-or-replace-views
diff --git a/sql/test/Tests/create-or-replace-triggers.sql
b/sql/test/Tests/create-or-replace-triggers.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/create-or-replace-triggers.sql
@@ -0,0 +1,16 @@
+CREATE TABLE testing (ab INT);
+CREATE TABLE testing2 (abc INT);
+
+CREATE TRIGGER nanani AFTER INSERT ON testing FOR EACH STATEMENT BEGIN ATOMIC
INSERT INTO testing2 VALUES (1); END;
+
+INSERT INTO testing values (1);
+
+SELECT abc FROM testing2; --should get a single row with value 1
+
+CREATE OR REPLACE TRIGGER nanani AFTER INSERT ON testing FOR EACH STATEMENT
BEGIN ATOMIC INSERT INTO testing2 VALUES (2); END;
+
+INSERT INTO testing values (1);
+
+SELECT abc FROM testing2; --the previous row plus 2
+
+CREATE OR REPLACE TRIGGER failedtrigger AFTER INSERT ON testing3 FOR EACH
STATEMENT BEGIN ATOMIC INSERT INTO testing2 VALUES (3); END; --error
diff --git a/sql/test/Tests/create-or-replace-triggers.stable.err
b/sql/test/Tests/create-or-replace-triggers.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/create-or-replace-triggers.stable.err
@@ -0,0 +1,38 @@
+stderr of test 'create-or-replace-triggers` in directory 'sql/test` itself:
+
+
+# 09:43:33 >
+# 09:43:33 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=35381" "--set"
"mapi_usock=/var/tmp/mtest-7671/.s.monetdb.35381" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test"
"--set" "embedded_py=true"
+# 09:43:33 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/MonetDB-default/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 = 35381
+# cmdline opt mapi_usock = /var/tmp/mtest-7671/.s.monetdb.35381
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test
+# cmdline opt embedded_py = true
+# cmdline opt gdk_debug = 536870922
+
+# 09:43:33 >
+# 09:43:33 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-7671" "--port=35381"
+# 09:43:33 >
+
+MAPI = (monetdb) /var/tmp/mtest-7671/.s.monetdb.35381
+QUERY = CREATE OR REPLACE TRIGGER failedtrigger AFTER INSERT ON testing3 FOR
EACH STATEMENT BEGIN ATOMIC INSERT INTO testing2 VALUES (3); END; --error
+
+
+# 09:43:34 >
+# 09:43:34 > "Done."
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
