Re: [gnome-db] "ALTER USER", etc, with GdaSqlBuilder?
Vivien Malerba <[email protected]>
| Newsgroups | gmane.comp.gnome.db |
|---|---|
| Message-ID | <[email protected]> |
On 20 May 2010 21:42, Vivien Malerba <[email protected]> wrote: > On 20 May 2010 21:33, Murray Cumming <[email protected]> wrote: >> On Thu, 2010-05-20 at 17:40 +0200, Vivien Malerba wrote: >>> On 20 May 2010 13:52, Murray Cumming <[email protected]> wrote: >>> > Is there any way to use GdaSQlBuilder to do thinks like this, which Glom >>> > does with PostgreSQL?: >>> > >>> > ALTER USER "bob" PASSWORD "secret" >>> > CREATE USER "bob" PASSWORD "secret" >>> > DROP USER >>> > REVOKE ALL PRIVILEGES ON "invoices" FROM "bob" >>> >>> No. For this kind of statement (other than SELECT, UPDATE, INSERT or >>> DELETE) you can either directly use SQL >> >> Yes, we do that now. >> >>> or use the GdaServerOperation >>> (though at the moment the GdaServerOperation does not support doing >>> any user manipulation, and it should be added, tell me if you would >>> like to have this in the 4.2). >> >> Thanks. Of course I'd like to have it, but it's not particularly urgent. >> It's just the last SQL-building code in Glom that we have not replaced >> with GdaSqlBuilder or GdaServerOperation. I guess that it would >> unnecessarily delay 4.2. >> > > Adding it is only a matter or declaring it in the > dta-server-operation.h file and implementing it for the PostgreSQL > provider (which is the one used by Glom), with a little documentation > along the way... should not be that hard. Here is a patch implementing the CREATE USER operation for PostgreSQL (done in 30'). If you find it usefull I'll add the other operations for users as well before a 4.1.6. You can test it with the libgda-ui/demos/gdaui-demo-4.0 and the "DDL queries" demo item. Vivien _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
GdaServerOperation-CreateUser-PostgreSQL.patch
(application/octet-stream, 12.2 KB)
diff --git a/doc/C/server-operation.xml b/doc/C/server-operation.xml
index 65caf92..2524d0e 100644
--- a/doc/C/server-operation.xml
+++ b/doc/C/server-operation.xml
@@ -682,6 +682,62 @@
</table>
</para>
</listitem>
+
+ <listitem>
+ <para>Named and required information for GDA_SERVER_OPERATION_CREATE_USER:
+ <table frame="all">
+ <tgroup cols="4" colsep="1" rowsep="1" align="justify">
+ <thead>
+ <row>
+ <entry>Path</entry>
+ <entry>Type</entry>
+ <entry>Required?</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>/USER_DEF_P</entry>
+ <entry>PARAMLIST</entry>
+ <entry>Yes</entry>
+ <entry>User's description</entry>
+ </row>
+ <row>
+ <entry>/USER_DEF_P/USER_NAME</entry>
+ <entry>PARAM</entry>
+ <entry>Yes</entry>
+ <entry>User name (string)</entry>
+ </row>
+ <row>
+ <entry>/USER_DEF_P/PASSWORD</entry>
+ <entry>PARAM</entry>
+ <entry></entry>
+ <entry>User's password (string)</entry>
+ </row>
+ <row>
+ <entry>/USER_DEF_P/CAP_CREATEDB</entry>
+ <entry>PARAM</entry>
+ <entry></entry>
+ <entry>Set to TRUE if the user is allowed to create databases (boolean)</entry>
+ </row>
+ <row>
+ <entry>/USER_DEF_P/CAP_CREATEUSER</entry>
+ <entry>PARAM</entry>
+ <entry></entry>
+ <entry>Set to TRUE if the user is allowed to create users (boolean)</entry>
+ </row>
+ <row>
+ <entry>/USER_DEF_P/VALIDITY</entry>
+ <entry>PARAM</entry>
+ <entry></entry>
+ <entry>Set the expiration timestamp (timestamp)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+ </listitem>
+
</itemizedlist>
</sect3>
diff --git a/doc/C/tmpl/gda-server-operation.sgml b/doc/C/tmpl/gda-server-operation.sgml
index 9452de9..7de4655 100644
--- a/doc/C/tmpl/gda-server-operation.sgml
+++ b/doc/C/tmpl/gda-server-operation.sgml
@@ -104,6 +104,9 @@ Handles any DDL query in an abstract way
@GDA_SERVER_OPERATION_DROP_VIEW:
@GDA_SERVER_OPERATION_COMMENT_TABLE:
@GDA_SERVER_OPERATION_COMMENT_COLUMN:
+@GDA_SERVER_OPERATION_CREATE_USER:
+@GDA_SERVER_OPERATION_ALTER_USER:
+@GDA_SERVER_OPERATION_DROP_USER:
@GDA_SERVER_OPERATION_LAST:
<!-- ##### FUNCTION gda_server_operation_get_op_type ##### -->
diff --git a/libgda/gda-server-operation.c b/libgda/gda-server-operation.c
index 7c03ebf..aea93c3 100644
--- a/libgda/gda-server-operation.c
+++ b/libgda/gda-server-operation.c
@@ -1223,6 +1223,8 @@ gda_server_operation_op_type_to_string (GdaServerOperationType type)
return "CREATE_VIEW";
case GDA_SERVER_OPERATION_DROP_VIEW:
return "DROP_VIEW";
+ case GDA_SERVER_OPERATION_CREATE_USER:
+ return "CREATE_USER";
default:
g_error (_("Non handled GdaServerOperationType, please report error to "
"http://bugzilla.gnome.org/ for the \"libgda\" product"));
diff --git a/libgda/gda-server-operation.h b/libgda/gda-server-operation.h
index 028c8fd..440e386 100644
--- a/libgda/gda-server-operation.h
+++ b/libgda/gda-server-operation.h
@@ -59,6 +59,10 @@ typedef enum {
GDA_SERVER_OPERATION_COMMENT_TABLE,
GDA_SERVER_OPERATION_COMMENT_COLUMN,
+ GDA_SERVER_OPERATION_CREATE_USER,
+ GDA_SERVER_OPERATION_ALTER_USER,
+ GDA_SERVER_OPERATION_DROP_USER,
+
GDA_SERVER_OPERATION_LAST
} GdaServerOperationType;
diff --git a/libgda/gda-server-provider.c b/libgda/gda-server-provider.c
index 8b837de..dc01ce9 100644
--- a/libgda/gda-server-provider.c
+++ b/libgda/gda-server-provider.c
@@ -429,6 +429,13 @@ static OpReq op_req_DROP_VIEW [] = {
{NULL}
};
+static OpReq op_req_CREATE_USER [] = {
+ {"/USER_DEF_P", GDA_SERVER_OPERATION_NODE_PARAMLIST, 0},
+ {"/USER_DEF_P/USER_NAME", GDA_SERVER_OPERATION_NODE_PARAM, G_TYPE_STRING},
+ {NULL}
+};
+
+
/**
* gda_server_provider_create_operation
* @provider: a #GdaServerProvider object
@@ -477,6 +484,8 @@ gda_server_provider_create_operation (GdaServerProvider *provider, GdaConnection
op_req_table [GDA_SERVER_OPERATION_COMMENT_TABLE] = op_req_COMMENT_TABLE;
op_req_table [GDA_SERVER_OPERATION_COMMENT_COLUMN] = op_req_COMMENT_COLUMN;
+
+ op_req_table [GDA_SERVER_OPERATION_CREATE_USER] = op_req_CREATE_USER;
}
g_static_mutex_unlock (&init_mutex);
diff --git a/providers/postgres/Makefile.am b/providers/postgres/Makefile.am
index da6a176..f015e2d 100644
--- a/providers/postgres/Makefile.am
+++ b/providers/postgres/Makefile.am
@@ -45,7 +45,8 @@ xml_in_files = \
postgres_specs_add_column.xml.in \
postgres_specs_drop_column.xml.in \
postgres_specs_create_view.xml.in \
- postgres_specs_drop_view.xml.in
+ postgres_specs_drop_view.xml.in \
+ postgres_specs_create_user.xml.in
@INTLTOOL_XML_RULE@
diff --git a/providers/postgres/gda-postgres-ddl.c b/providers/postgres/gda-postgres-ddl.c
index 34b9791..19666cc 100644
--- a/providers/postgres/gda-postgres-ddl.c
+++ b/providers/postgres/gda-postgres-ddl.c
@@ -749,3 +749,112 @@ gda_postgres_render_DROP_VIEW (GdaServerProvider *provider, GdaConnection *cnc,
return sql;
}
+
+gchar *
+gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc,
+ GdaServerOperation *op, GError **error)
+{
+ GString *string;
+ const GValue *value;
+ gchar *sql = NULL;
+ gchar *tmp;
+ gboolean with = FALSE;
+
+ string = g_string_new ("CREATE USER ");
+
+ tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/USER_DEF_P/USER_NAME");
+ g_string_append (string, tmp);
+ g_free (tmp);
+
+ value = gda_server_operation_get_value_at (op, "/USER_DEF_P/PASSWORD");
+ if (value && G_VALUE_HOLDS (value, G_TYPE_STRING) &&
+ g_value_get_string (value) && (*g_value_get_string (value))) {
+ GdaDataHandler *dh;
+ const GValue *value2;
+
+ g_string_append (string, " WITH");
+ with = TRUE;
+
+ value2 = gda_server_operation_get_value_at (op, "/USER_DEF_P/PASSWORD_ENCRYPTED");
+ if (value2 && G_VALUE_HOLDS (value2, G_TYPE_BOOLEAN) && g_value_get_boolean (value2))
+ g_string_append (string, " ENCRYPTED");
+
+ g_string_append (string, " PASSWORD ");
+ dh = gda_server_provider_get_data_handler_g_type (provider, cnc, G_TYPE_STRING);
+ if (!dh)
+ dh = gda_get_default_handler (G_TYPE_STRING);
+
+ tmp = gda_data_handler_get_sql_from_value (dh, value);
+ g_string_append (string, tmp);
+ g_free (tmp);
+ }
+
+ value = gda_server_operation_get_value_at (op, "/USER_DEF_P/UID");
+ if (value && G_VALUE_HOLDS (value, G_TYPE_UINT)) {
+ if (!with) {
+ g_string_append (string, " WITH");
+ with = TRUE;
+ }
+ g_string_append_printf (string, "SYSID %u", g_value_get_uint (value));
+ }
+
+ value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_CREATEDB");
+ if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
+ if (!with) {
+ g_string_append (string, " WITH");
+ with = TRUE;
+ }
+ g_string_append (string, " CREATEDB");
+ }
+
+ value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_CREATEUSER");
+ if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
+ if (!with) {
+ g_string_append (string, " WITH");
+ with = TRUE;
+ }
+ g_string_append (string, " CREATEUSER");
+ }
+
+ value = gda_server_operation_get_value_at (op, "/USER_DEF_P/GROUPS");
+ if (value && G_VALUE_HOLDS (value, G_TYPE_STRING) &&
+ g_value_get_string (value) && (*g_value_get_string (value))) {
+ GdaDataHandler *dh;
+
+ g_string_append (string, " IN GROUP ");
+ dh = gda_server_provider_get_data_handler_g_type (provider, cnc, G_TYPE_STRING);
+ if (!dh)
+ dh = gda_get_default_handler (G_TYPE_STRING);
+
+ tmp = gda_data_handler_get_sql_from_value (dh, value);
+ g_string_append (string, tmp);
+ g_free (tmp);
+ }
+
+ value = gda_server_operation_get_value_at (op, "/USER_DEF_P/VALIDITY");
+ if (value && G_VALUE_HOLDS (value, GDA_TYPE_TIMESTAMP)) {
+ const GdaTimestamp *ts;
+
+ ts = gda_value_get_timestamp (value);
+ if (value) {
+ GdaDataHandler *dh;
+ if (!with) {
+ g_string_append (string, " WITH");
+ with = TRUE;
+ }
+ dh = gda_server_provider_get_data_handler_g_type (provider, cnc, GDA_TYPE_TIMESTAMP);
+ if (!dh)
+ dh = gda_get_default_handler (GDA_TYPE_TIMESTAMP);
+
+ g_string_append (string, " VALID UNTIL ");
+ tmp = gda_data_handler_get_sql_from_value (dh, value);
+ g_string_append (string, tmp);
+ g_free (tmp);
+ }
+ }
+
+ sql = string->str;
+ g_string_free (string, FALSE);
+
+ return sql;
+}
diff --git a/providers/postgres/gda-postgres-ddl.h b/providers/postgres/gda-postgres-ddl.h
index b4221ad..b8c0ecd 100644
--- a/providers/postgres/gda-postgres-ddl.h
+++ b/providers/postgres/gda-postgres-ddl.h
@@ -50,6 +50,9 @@ gchar *gda_postgres_render_CREATE_VIEW (GdaServerProvider *provider, GdaConnect
GdaServerOperation *op, GError **error);
gchar *gda_postgres_render_DROP_VIEW (GdaServerProvider *provider, GdaConnection *cnc,
GdaServerOperation *op, GError **error);
+gchar *gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc,
+ GdaServerOperation *op, GError **error);
+
G_END_DECLS
#endif
diff --git a/providers/postgres/gda-postgres-provider.c b/providers/postgres/gda-postgres-provider.c
index 9c0ce6c..17397c1 100644
--- a/providers/postgres/gda-postgres-provider.c
+++ b/providers/postgres/gda-postgres-provider.c
@@ -702,6 +702,8 @@ gda_postgres_provider_supports_operation (GdaServerProvider *provider, GdaConnec
case GDA_SERVER_OPERATION_CREATE_VIEW:
case GDA_SERVER_OPERATION_DROP_VIEW:
+
+ case GDA_SERVER_OPERATION_CREATE_USER:
return TRUE;
default:
return FALSE;
@@ -822,6 +824,9 @@ gda_postgres_provider_render_operation (GdaServerProvider *provider, GdaConnecti
case GDA_SERVER_OPERATION_DROP_VIEW:
sql = gda_postgres_render_DROP_VIEW (provider, cnc, op, error);
break;
+ case GDA_SERVER_OPERATION_CREATE_USER:
+ sql = gda_postgres_render_CREATE_USER (provider, cnc, op, error);
+ break;
default:
g_assert_not_reached ();
}
diff --git a/providers/postgres/postgres_specs_create_user.xml.in b/providers/postgres/postgres_specs_create_user.xml.in
new file mode 100644
index 0000000..ea18664
--- /dev/null
+++ b/providers/postgres/postgres_specs_create_user.xml.in
@@ -0,0 +1,23 @@
+<?xml version="1.0"?>
+<serv_op>
+ <parameters id="USER_DEF_P" _name="User's definition">
+ <parameter id="USER_NAME" _name="Name" _descr="User's name" gdatype="gchararray" nullok="FALSE"/>
+ <parameter id="PASSWORD" _name="Password" _descr="User's password" gdatype="gchararray" plugin="string:HIDDEN=true"/>
+ <parameter id="PASSWORD_ENCRYPTED" _name="Encrypt password" _descr="Controls whether the password is stored encrypted in the system catalogs. If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is." gdatype="gboolean">
+ <gda_value>FALSE</gda_value>
+ </parameter>
+
+ <parameter id="UID" _name="User ID" _descr="Can be used to choose the PostgreSQL user ID of the new user" gdatype="guint"/>
+ <parameter id="CAP_CREATEDB" _name="Can create databases" _descr="Set to TRUE if the user is allowed to create databases" gdatype="gboolean">
+ <gda_value>FALSE</gda_value>
+ </parameter>
+ <parameter id="CAP_CREATEUSER" _name="Can create users" _descr="Set to TRUE if the user is allowed to create users" gdatype="gboolean">
+ <gda_value>FALSE</gda_value>
+ </parameter>
+
+ <parameter id="GROUPS" _name="Groups" _descr="Comma separated list of groups the user will belong to" gdatype="gchararray"/>
+
+ <parameter id="VALIDITY" _name="Valid until" _descr="Specifies an expiration time for a password only (not for the user account per se: the expiration time is not enforced when logging in using a non-password-based authentication method)" gdatype="timestamp"/>
+
+ </parameters>
+</serv_op>