|
|
|
|
On the contrary to data manipulation (DML) queries, data definition queries are not very well standardized (the SQL varies a lot from one DBMS to the other), and Libgda proposes to bypass the SQL usage and uses a different approach based on the information required to perform a task (or operation, thus the GdaServerOperation object).
Here is an overview the steps involved:
Make sure the database provider (associated to the connection) supports the requested operation using the gda_server_provider_supports_operation() method
Ask the provider to create a GdaServerOperation object using the gda_server_provider_create_operation() method. The resulting new object will hold all the information required (and some optional) to perform the requested operation. For example to create a table, the required information will be the table name and the description of its columns, and the optional information might be the schema in which to create the table for a PostgreSQL provider.
Additionally some options can be passed when using that function in the form of named parameters, see this section for more information.
Fill in the values (required or optional) of the GdaServerOperation object (see the GdaServerOperation object's documentation for more information).
Use that object to request the actual operation to be done using the gda_server_provider_perform_operation() method. Afterwards that object can be discarded using g_object_unref()
The information stored within a GdaServerOperation object can also be stored within an XML structure and re-loaded later, which for example allows to create "scripts" of operations to do.
The required information to perform an operation is stored entirely in a GdaServerOperation object using named paths which are strings like "/TABLE_DEF_P/TABLE_NAME" or "/TABLE_CONSTRAINTS_S/0/CONSTRAINT_STRING" (for details about the path format, refer to the gda_server_operation_set_value_at() method). Each path is similar to a Unix file path (names separated by a '/'). Each part of the path corresponds to a node (represented as a GdaServerOperationNode structure) which has a type listed as a GdaServerOperationNodeType; basically the types of node are:
a node containing a GdaHolder object which just contains one value
a node containing a GdaSet object which is just a list of named parameters (for example all the attributes of the table to create such as the table's name and storage type for a MySQL provider)
a node containing a GdaDataModel object when the needed information is tabular (rows and columns), for example the definition of all the columns to create in a table, each column having a name, type, CHECK constraint, etc
a node defining a sequence of items: the sequence node can have zero or more items composed of nodes. For example when defining the foreign keys for a new table, a sequence node can be created which can contain zero or more foreign key definition items (each composed of the referenced table, the referenced fields, the referential integrity rules, etc)
a node representing an 'item' in a sequence node (that is a list of nodes).
a node representing a data model's column.
Note: the list and complete structure of the information required to perform an operation is imposed by each provider and cannot be modified. The information stored in each node can be modified (depending on the node's type):
To list the possible/required parameters for each provider, use the gda-list-server-op tool.
For most of the possible operations, the required information from one provider to the other does not vary, only the optional features correspond to optional information, so Libgda imposes the names of the required information for some of the most common operations, which is shown next. Some optional information which is shared by several providers is also named but not enforced.
Named and required information for GDA_SERVER_OPERATION_CREATE_DB:
Table 10.
| Path | Type | Required? | Description |
|---|---|---|---|
| /DB_DEF_P | PARAMLIST | Yes | Database's description |
| /DB_DEF_P/DB_NAME | PARAM | Yes | Database name (string) |
| /DB_DEF_P/DB_CSET | PARAM | Character set (string) | |
| /DB_DEF_P/DB_OWNER | PARAM | Database owner (string) | |
| /DB_DEF_P/DB_TABLESPACE | PARAM | Tablespace the database is in (string) |
Note also that if creating a database requires an access to a server, then the access parameters to that server
will all be in the "/SERVER_CNX_P" PARAMLIST, as shown:
Table 11.
| Path | Type | Description |
|---|---|---|
| /SERVER_CNX_P | PARAMLIST | Parameters to connect to a server |
| /SERVER_CNX_P/HOST | PARAM | Name of the server (string) |
| /SERVER_CNX_P/PORT | PARAM | Port number on the server (gint) |
Named and required information for GDA_SERVER_OPERATION_DROP_DB:
Table 12.
| Path | Type | Required? | Description |
|---|---|---|---|
| /DB_DESC_P | PARAMLIST | Yes | Database's description |
| /DB_DESC_P/DB_NAME | PARAM | Yes | Database name (string) |
Note also that if creating a database requires an access to a server, then the access parameters to that server
will all be in the "/SERVER_CNX_P" PARAMLIST, as for the GDA_SERVER_OPERATION_CREATE_DB operation.
Named and required information for GDA_SERVER_OPERATION_CREATE_TABLE:
Table 13.
| Path | Type | Required? | Description |
|---|---|---|---|
| /TABLE_DEF_P | PARAMLIST | Yes | Table's description |
| /TABLE_DEF_P/TABLE_TABLESPACE | PARAM | Tablespace in which to create the table (string) | |
| /TABLE_DEF_P/TABLE_NAME | PARAM | Yes | Table name (string) |
| /TABLE_DEF_P/TABLE_TEMP | PARAM | TRUE if the table to create is temporary (boolean) | |
| /TABLE_DEF_P/TABLE_COMMENT | PARAM | Comment on a table (string) | |
| /FIELDS_A | DATA_MODEL | Yes | Table's fields description |
| /FIELDS_A/@COLUMN_NAME | DATA_MODEL_COLUMN | Yes | A table's field name (string) |
| /FIELDS_A/@COLUMN_TYPE | DATA_MODEL_COLUMN | Yes | A table's field type (string) |
| /FIELDS_A/@COLUMN_SIZE | DATA_MODEL_COLUMN | A table's field defined size (unsigned integer) | |
| /FIELDS_A/@COLUMN_SCALE | DATA_MODEL_COLUMN | A table's field number of decimals (unsigned integer) | |
| /FIELDS_A/@COLUMN_NNUL | DATA_MODEL_COLUMN | TRUE if table's field can't be NULL (boolean) | |
| /FIELDS_A/@COLUMN_AUTOINC | DATA_MODEL_COLUMN | TRUE if table's field must be auto incremented (boolean) | |
| /FIELDS_A/@COLUMN_UNIQUE | DATA_MODEL_COLUMN | TRUE if table's field must be unique (boolean) | |
| /FIELDS_A/@COLUMN_PKEY | DATA_MODEL_COLUMN | TRUE if table's field is a primary key (boolean) | |
| /FIELDS_A/@COLUMN_DEFAULT | DATA_MODEL_COLUMN | A table's field default value (string) | |
| /FIELDS_A/@COLUMN_CHECK | DATA_MODEL_COLUMN | A table's field CHECK condition (string) | |
| /FIELDS_A/@COLUMN_COMMENT | DATA_MODEL_COLUMN | A comment on the table's field (string) | |
| /FKEY_S | DATA_SEQUENCE | Sequence representing foreign keys description | |
| /FKEY_S/FKEY_REF_TABLE | PARAM | The foreign key's referenced table (string) | |
| /FKEY_S/FKEY_FIELDS_A | DATA_MODEL | The foreign key's field's being used | |
| /FKEY_S/FKEY_FIELDS_A/@FK_FIELD | DATA_MODEL_COLUMN | A foreign key's field's from the table to create | |
| /FKEY_S/FKEY_FIELDS_A/@FK_REF_PK_FIELD | DATA_MODEL_COLUMN | A foreign key's field's from the referenced table | |
| /FKEY_S/FKEY_ONUPDATE | PARAM | The action to take when the referenced field is updated (string) | |
| /FKEY_S/FKEY_ONDELETE | PARAM | The action to take when the referenced field is deleted (string) | |
| /FKEY_S/FKEY_DEFERRABLE | PARAM | TRUE if the foreign key is deferrable until the end of a transaction (boolean) | |
| /TABLE_CONSTRAINTS_S | SEQUENCE | Sequence representing table constraints | |
| /TABLE_CONSTRAINTS_S/CONSTRAINT_STRING | SEQUENCE | A table constraint (string) |
Named and required information for GDA_SERVER_OPERATION_DROP_TABLE:
Table 14.
| Path | Type | Required? | Description |
|---|---|---|---|
| /TABLE_DESC_P | PARAMLIST | Yes | Table's description |
| /TABLE_DESC_P/TABLE_NAME | PARAM | Yes | Table's name (string) |
| /TABLE_DESC_P/REFERENCED_ACTION | PARAM | Action to perform for objects referencing the table to drop (CASCADE, RESTRICT, ...) (string) |
Optional parameters for the creation of a GdaServerOperation object for such an operation type:
Named and required information for GDA_SERVER_OPERATION_RENAME_TABLE:
Table 16.
| Path | Type | Required? | Description |
|---|---|---|---|
| /TABLE_DESC_P | PARAMLIST | Yes | Table's description |
| /TABLE_DESC_P/TABLE_NAME | PARAM | Yes | Table's name (string) |
| /TABLE_DESC_P/TABLE_NEW_NAME | PARAM | Yes | Table's new name (string) |
Named and required information for GDA_SERVER_OPERATION_ADD_COLUMN:
Table 17.
| Path | Type | Required? | Description |
|---|---|---|---|
| /COLUMN_DEF_P | PARAMLIST | Yes | Column's description |
| /COLUMN_DEF_P/TABLE_NAME | PARAM | Yes | Name of the table to add a column to (string) |
| /COLUMN_DEF_P/COLUMN_NAME | PARAM | Yes | New column's name (string) |
| /COLUMN_DEF_P/COLUMN_TYPE | PARAM | Yes | New column's type (string) |
| /COLUMN_DEF_P/COLUMN_SIZE | PARAM | New column's defined size (unsigned integer) | |
| /COLUMN_DEF_P/COLUMN_SCALE | PARAM | New column's number of decimals (unsigned integer) |
The other bits of information for the column description, which are not required, are the same as for "/FIELDS_A" array of the GDA_SERVER_OPERATION_CREATE_TABLE (replace "/FIELDS_A" with "/COLUMN_DEF_P").
Named and required information for GDA_SERVER_OPERATION_DROP_COLUMN:
Table 18.
| Path | Type | Required? | Description |
|---|---|---|---|
| /COLUMN_DESC_P | PARAMLIST | Yes | Column's description |
| /COLUMN_DESC_P/TABLE_NAME | PARAM | Yes | Name of the table to remove a column from (string) |
| /COLUMN_DESC_P/COLUMN_NAME | PARAM | Yes | Name of the column to remove (string) |
Named and required information for GDA_SERVER_OPERATION_CREATE_INDEX:
Table 19.
| Path | Type | Required? | Description |
|---|---|---|---|
| /INDEX_DEF_P | PARAMLIST | Yes | Index's description |
| /INDEX_DEF_P/INDEX_NAME | PARAM | Yes | Index name (string) |
| /INDEX_DEF_P/INDEX_TYPE | PARAM | Index type: UNIQUE, etc (string) | |
| /INDEX_DEF_P/INDEX_ON_TABLE | PARAM | Yes | Table on which the index is applied (string) |
| /INDEX_FIELDS_S | SEQUENCE | Yes | List of fields on which the index is applied (min: 1 item) |
| /INDEX_FIELDS_S/INDEX_FIELD | PARAM | Yes | A field on which the index is applied (string) |
Named and required information for GDA_SERVER_OPERATION_DROP_INDEX:
Table 20.
| Path | Type | Required? | Description |
|---|---|---|---|
| /INDEX_DESC_P | PARAMLIST | Yes | Index's description |
| /INDEX_DESC_P/INDEX_NAME | PARAM | Yes | Index's name (string) |
| /INDEX_DEF_P/INDEX_ON_TABLE | PARAM | Table on which the index is applied (string) | |
| /INDEX_DESC_P/REFERENCED_ACTION | PARAM | Action to perform for objects referencing the index to drop (CASCADE, RESTRICT, ...) (string) |
Named and required information for GDA_SERVER_OPERATION_CREATE_USER:
Table 21.
| Path | Type | Required? | Description |
|---|---|---|---|
| /USER_DEF_P | PARAMLIST | Yes | User's description |
| /USER_DEF_P/USER_NAME | PARAM | Yes | User name (string) |
| /USER_DEF_P/PASSWORD | PARAM | User's password (string) | |
| /USER_DEF_P/CAP_CREATEDB | PARAM | Set to TRUE if the user is allowed to create databases (boolean) | |
| /USER_DEF_P/CAP_CREATEUSER | PARAM | Set to TRUE if the user is allowed to create users (boolean) | |
| /USER_DEF_P/VALIDITY | PARAM | Set the expiration timestamp (timestamp) |