Changeset: 2207edc0e078 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2207edc0e078
Modified Files:
clients/mapiclient/mhelp.c
sql/ChangeLog
sql/server/rel_schema.c
sql/server/rel_sequence.c
sql/server/sql_parser.y
sql/test/Tests/seq-default.test
sql/test/Users/Tests/create_user_options.test
sql/test/Users/Tests/role.test
sql/test/miscellaneous/Tests/sequences.test
sql/test/sql_xml/Tests/cleanupfuncs.test
Branch: default
Log Message:
Add support for using IF NOT EXIST in CREATE SEQUENCE, CREATE USER, CREATE
ROLE, CREATE TYPE, CREATE INDEX, CREATE IMPRINTS INDEX and CREATE ORDERED INDEX
statements.
Add support for using IF EXIST in DROP SEQUENCE, DROP USER, DROP ROLE, DROP
TYPE and DROP INDEX statements.
Add support for using IF EXIST in ALTER SEQUENCE and ALTER USER statements.
diffs (truncated from 1546 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
@@ -61,7 +61,7 @@ SQLhelp sqlhelp1[] = {
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/schema-definitions/"},
{"ALTER SEQUENCE",
"",
- "ALTER SEQUENCE qname [AS seq_int_datatype] [RESTART [WITH
{bigint|subquery}] ] [INCREMENT BY bigint]\n"
+ "ALTER SEQUENCE [ IF EXISTS ] qname [AS seq_int_datatype] [RESTART
[WITH {bigint|subquery}] ] [INCREMENT BY bigint]\n"
" [MINVALUE bigint | NO MINVALUE] [MAXVALUE bigint | NO MAXVALUE]
[CACHE bigint] [[NO] CYCLE]",
"seq_int_datatype",
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/serial-types/"},
@@ -83,9 +83,9 @@ SQLhelp sqlhelp1[] = {
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/alter-statement/"},
{"ALTER USER",
"Change a user's login name or password or default schema",
- "ALTER USER ident RENAME TO ident\n"
+ "ALTER USER [ IF EXISTS ] ident RENAME TO ident\n"
"ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD string USING OLD
PASSWORD string\n"
- "ALTER USER ident\n"
+ "ALTER USER [ IF EXISTS ] ident\n"
" [WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string]\n"
" [SET SCHEMA ident] [SCHEMA PATH string] [DEFAULT ROLE ident]\n"
" [MAX_MEMORY posbytes | MAX_MEMORY sizestr | NO MAX_MEMORY]
[MAX_WORKERS poscount | NO MAX_WORKERS]",
@@ -175,7 +175,7 @@ SQLhelp sqlhelp1[] = {
"See also
https://www.monetdb.org/documentation/user-guide/sql-programming/function-definitions/"},
{"CREATE INDEX",
"Create a hint for a secondary index on a column or set of columns of
a table",
- "CREATE [ UNIQUE | ORDERED | IMPRINTS ] INDEX ident ON qname '('
ident_list ')'",
+ "CREATE [ UNIQUE | ORDERED | IMPRINTS ] INDEX [ IF NOT EXISTS ] ident
ON qname '(' ident_list ')'",
NULL,
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/index-definitions/"},
{"CREATE LOADER",
@@ -217,7 +217,7 @@ SQLhelp sqlhelp1[] = {
{"CREATE ROLE",
"Create a new role. You can grant privileges to a role and next\n"
"grant a role (or multiple roles) to specific users",
- "CREATE ROLE ident [ WITH ADMIN { CURRENT_USER | CURRENT_ROLE } ]",
+ "CREATE ROLE [ IF NOT EXISTS ] ident [ WITH ADMIN { CURRENT_USER |
CURRENT_ROLE } ]",
"ident",
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/#roles"},
{"CREATE SCHEMA",
@@ -227,7 +227,7 @@ SQLhelp sqlhelp1[] = {
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/schema-definitions/"},
{"CREATE SEQUENCE",
"Define a new integer number sequence generator",
- "CREATE SEQUENCE qname [AS seq_int_datatype] [START WITH bigint]
[INCREMENT BY bigint]\n"
+ "CREATE SEQUENCE [ IF NOT EXISTS ] qname [AS seq_int_datatype] [START
WITH bigint] [INCREMENT BY bigint]\n"
" [MINVALUE bigint | NO MINVALUE] [MAXVALUE bigint | NO MAXVALUE]
[CACHE bigint] [[NO] CYCLE]",
"seq_int_datatype",
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/serial-types/"},
@@ -247,14 +247,14 @@ SQLhelp sqlhelp1[] = {
"See also
https://www.monetdb.org/documentation/user-guide/sql-programming/trigger-definition/"},
{"CREATE TYPE",
"Add user defined type to the type system ",
- "CREATE TYPE qname EXTERNAL NAME ident",
+ "CREATE TYPE [ IF NOT EXISTS ] qname EXTERNAL NAME ident",
NULL,
NULL},
{"CREATE USER",
"Create a new database user",
- "CREATE USER ident WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string NAME
string [SCHEMA ident] [SCHEMA PATH string]\n"
- "[MAX_MEMORY posbytes | MAX_MEMORY sizestr | NO MAX_MEMORY]
[MAX_WORKERS poscount | NO MAX_WORKERS]\n"
- "[OPTIMIZER string] [DEFAULT ROLE ident]",
+ "CREATE USER [ IF NOT EXISTS ] ident WITH [ENCRYPTED | UNENCRYPTED]
PASSWORD string NAME string\n"
+ " [SCHEMA ident] [SCHEMA PATH string] [MAX_MEMORY posbytes |
MAX_MEMORY sizestr | NO MAX_MEMORY]\n"
+ " [MAX_WORKERS poscount | NO MAX_WORKERS] [OPTIMIZER string]
[DEFAULT ROLE ident]",
"ident",
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/"},
{"CREATE VIEW",
@@ -343,7 +343,7 @@ SQLhelp sqlhelp1[] = {
NULL},
{"DROP INDEX",
"",
- "DROP INDEX qname",
+ "DROP INDEX [ IF EXISTS ] qname",
NULL,
NULL},
{"DROP LOADER",
@@ -360,7 +360,7 @@ SQLhelp sqlhelp1[] = {
NULL},
{"DROP ROLE",
"",
- "DROP ROLE ident",
+ "DROP ROLE [ IF EXISTS ] ident",
NULL,
NULL},
{"DROP SCHEMA",
@@ -370,7 +370,7 @@ SQLhelp sqlhelp1[] = {
NULL},
{"DROP SEQUENCE",
"",
- "DROP SEQUENCE qname",
+ "DROP SEQUENCE [ IF EXISTS ] qname",
NULL,
NULL},
{"DROP TABLE",
@@ -385,12 +385,12 @@ SQLhelp sqlhelp1[] = {
NULL},
{"DROP TYPE",
"",
- "DROP TYPE qname [ RESTRICT | CASCADE ]",
+ "DROP TYPE [ IF EXISTS ] qname [ RESTRICT | CASCADE ]",
NULL,
NULL},
{"DROP USER",
"",
- "DROP USER ident",
+ "DROP USER [ IF EXISTS ] ident",
NULL,
NULL},
{"DROP VIEW",
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,8 +1,58 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Thu Jul 24 2025 Martin van Dinther <[email protected]>
+- Added the possibility to specify IF NOT EXIST for the following
+ CREATE statements:
+
+ CREATE SEQUENCE IF NOT EXIST seq_name ...
+
+ CREATE USER IF NOT EXIST user_name ...
+
+ CREATE ROLE IF NOT EXIST role_name ...
+
+ CREATE TYPE IF NOT EXIST type_name ...
+
+ CREATE INDEX IF NOT EXIST index_name ON ...
+
+ CREATE IMPRINTS INDEX IF NOT EXIST index_name ON ...
+
+ CREATE ORDERED INDEX IF NOT EXIST index_name ON ...
+
+ With IF NOT EXIST specified these CREATE statements will not return
+ an error when an object with the same name already exist. See doc:
+ https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/
+
+- Added the possibility to specify IF EXIST for the following DROP statements:
+
+ DROP SEQUENCE IF EXIST seq_name ...
+
+ DROP USER IF EXIST user_name
+
+ DROP ROLE IF EXIST role_name
+
+ DROP TYPE IF EXIST type_name ...
+
+ DROP INDEX IF EXIST index_name
+
+ With IF EXIST specified these statements will not return an error
+ when the object does not exist. See doc:
+
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/drop-statement/
+
+- Added the possibility to specify IF EXIST for two ALTER statements:
+
+ ALTER SEQUENCE IF EXIST seq_name ...
+
+ ALTER USER IF EXIST user_name ...
+
+ With IF EXIST specified these statements will not return an error
+ when the object does not exist. See doc:
+
https://www.monetdb.org/documentation/user-guide/sql-manual/data-types/serial-types/
+ and
+
https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/privileges/
+
* Tue Jun 3 2025 Sjoerd Mullender <[email protected]>
-- It is now possible to change the type of a column by using a query
+- It is now possible to change the type of a column by using a statement
like ALTER TABLE t ALTER COLUMN c type;. The content of the column
is converted to the new type using code similar to CAST(c AS type).
If the conversion fails, the column type isn't changed.
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
@@ -401,14 +401,14 @@ foreign_key_check_types(sql_subtype *lt,
static key_type
token2key_type(int token)
{
- switch (token) {
+ switch (token) {
case SQL_UNIQUE: return
ukey;
case SQL_UNIQUE_NULLS_NOT_DISTINCT: return unndkey;
case SQL_PRIMARY_KEY: return pkey;
case SQL_CHECK: return
ckey;
- }
- assert(0);
- return -1;
+ }
+ assert(0);
+ return -1;
}
static sql_rel*
@@ -1092,8 +1092,8 @@ create_column(sql_query *query, symbol *
(void) ss;
if (alter && !(isTable(t) || ((isMergeTable(t) || isReplicaTable(t)) &&
list_length(t->members)==0))) {
(void) sql_error(sql, 02, SQLSTATE(42000) "ALTER TABLE: cannot
add column to %s '%s'%s",
- TABLE_TYPE_DESCRIPTION(t->type,
t->properties),
- t->base.name, ((isMergeTable(t) ||
isReplicaTable(t)) && list_length(t->members)) ? " while it has partitions" :
"");
+ TABLE_TYPE_DESCRIPTION(t->type, t->properties),
+ t->base.name, ((isMergeTable(t) ||
isReplicaTable(t)) && list_length(t->members)) ? " while it has partitions" :
"");
return SQL_ERR;
}
if (l->h->next->next)
@@ -1143,8 +1143,9 @@ table_element(sql_query *query, symbol *
((isMergeTable(t) || isReplicaTable(t)) && (s->token !=
SQL_TABLE && s->token != SQL_DROP_TABLE && list_length(t->members))) ||
(isTable(t) && (s->token == SQL_TABLE || s->token ==
SQL_DROP_TABLE)) ||
(partition_find_part(sql->session->tr, t, NULL) &&
- (s->token == SQL_DROP_COLUMN || s->token == SQL_COLUMN
|| s->token == SQL_CONSTRAINT ||
- s->token == SQL_DEFAULT || s->token ==
SQL_DROP_DEFAULT || s->token == SQL_NOT_NULL || s->token == SQL_NULL ||
s->token == SQL_DROP_CONSTRAINT)))){
+ (s->token == SQL_DROP_COLUMN || s->token == SQL_COLUMN
|| s->token == SQL_CONSTRAINT ||
+ s->token == SQL_DEFAULT || s->token ==
SQL_DROP_DEFAULT || s->token == SQL_NOT_NULL ||
+ s->token == SQL_NULL || s->token ==
SQL_DROP_CONSTRAINT)))) {
const char *msg = "";
switch (s->token) {
@@ -1401,7 +1402,7 @@ table_element(sql_query *query, symbol *
sql_error(sql, 02, SQLSTATE(2BM37) "%s: cannot drop
column '%s': there are database objects which depend on it\n", action, cname);
return SQL_ERR;
}
- if (!drop_action && t->keys) {
+ if (!drop_action && t->keys) {
node *n, *m;
for (n = ol_first_node(t->keys); n; n = n->next) {
@@ -1814,21 +1815,27 @@ rel_schema3(allocator *sa, int cat_type,
}
static sql_rel *
-rel_drop_type(mvc *sql, dlist *qname, int drop_action)
+rel_drop_type(mvc *sql, dlist *qname, int drop_action, int if_exists)
{
char *name = qname_schema_object(qname);
char *sname = qname_schema(qname);
sql_type *t = NULL;
- if (!(t = find_type_on_scope(sql, sname, name, "DROP TYPE")))
+ if (!(t = find_type_on_scope(sql, sname, name, "DROP TYPE"))) {
+ if (if_exists) {
+ sql->errstr[0] = '\0'; /* reset type not found error */
+ sql->session->status = 0;
+ return rel_psm_block(sql->sa, new_exp_list(sql->sa));
+ }
return NULL;
+ }
if (!mvc_schema_privs(sql, t->s))
return sql_error(sql, 02, SQLSTATE(42000) "DROP TYPE: access
denied for %s to schema '%s'", get_string_global_var(sql, "current_user"),
t->s->base.name);
return rel_schema2(sql->sa, ddl_drop_type, t->s->base.name, name,
drop_action);
}
static sql_rel *
-rel_create_type(mvc *sql, dlist *qname, char *impl)
+rel_create_type(mvc *sql, dlist *qname, char *impl, int if_not_exists)
{
char *name = qname_schema_object(qname);
char *sname = qname_schema(qname);
@@ -1836,8 +1843,11 @@ rel_create_type(mvc *sql, dlist *qname,
if (sname && !(s = mvc_bind_schema(sql, sname)))
return sql_error(sql, ERR_NOTFOUND, SQLSTATE(3F000) "CREATE
TYPE: no such schema '%s'", sname);
- if (schema_bind_type(sql, s, name) != NULL)
+ if (schema_bind_type(sql, s, name) != NULL) {
+ if (if_not_exists == 1)
+ return rel_psm_block(sql->sa, new_exp_list(sql->sa));
return sql_error(sql, 02, SQLSTATE(42S01) "CREATE TYPE: name
'%s' already in use", name);
+ }
if (!mvc_schema_privs(sql, s))
return sql_error(sql, 02, SQLSTATE(42000) "CREATE TYPE: access
denied for %s to schema '%s'", get_string_global_var(sql, "current_user"),
s->base.name);
return rel_schema3(sql->sa, ddl_create_type, s->base.name, name, impl);
@@ -1979,7 +1989,7 @@ sql_drop_view(sql_query *query, dlist *q
if (!(t = find_table_or_view_on_scope(sql, NULL, sname, tname, "DROP
VIEW", true))) {
if (if_exists) {
- sql->errstr[0] = '\0'; /* reset table not found error */
+ sql->errstr[0] = '\0'; /* reset view not found error */
sql->session->status = 0;
return rel_psm_block(sql->sa, new_exp_list(sql->sa));
}
@@ -2439,14 +2449,16 @@ rel_grant_or_revoke_privs(mvc *sql, dlis
/* iname, itype, sname.tname (col1 .. coln) */
static sql_rel *
-rel_create_index(mvc *sql, char *iname, idx_type itype, dlist *qname, dlist
*column_list)
+rel_create_index(mvc *sql, char *iname, idx_type itype, dlist *qname, dlist
*column_list, int if_not_exists)
{
sql_table *t = NULL, *nt;
sql_rel *r, *res;
sql_exp **updates, *e;
sql_idx *i;
dnode *n;
- char *sname = qname_schema(qname), *tname = qname_schema_object(qname),
*s = iname;
+ char *sname = qname_schema(qname);
+ char *tname = qname_schema_object(qname);
+ char *s = iname;
if (!(t = find_table_or_view_on_scope(sql, NULL, sname, tname, "CREATE
INDEX", false)))
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
