Data definition (DDL) queries

GdaServerOperation — Handles any DDL query in an abstract way
GdaServerOperation: individual nodes — Getting information about parts (nodes) composing a path
GdaServerOperation: sequences — Manipulating sequences

General words about DDL queries

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.

Organization of the required information

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):

Provider specific information

To list the possible/required parameters for each provider, use the gda-list-server-op tool.

Standard named information

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:

    Table 15. 

    Id Type Description
    TABLE_DESC_P/TABLE_NAME string Name of the table to delete



  • 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)