SQL Data Definition Language

With eXtremeSQL there are two methods of defining the database schema. An embedded eXtremeSQL application developed with the C or C++ API will often use a database initially defined with the eXtremeDB DDL. Java, C# and Python applications will define database classes and indexes using the native language syntax. When using xSQL to create a database, the schema is specified in the schema section of the configuration file either by importing a schema definition file or by defining the schema in a text string using eXtremeDB DDL.

Alternatively the database could be created and/or altered with the dynamic SQL Data Definition Language (DDL) statements create table, create_index, alter table, alter index, drop table and drop index. These SQL DDL statements can be executed by the embedded eXtremeSQL application or by xSQL.

ODBC and JDBC drivers are also provided to allow database management from other third party database tools and applications that choose to use these "middleware" drivers.

eXtremeSQL and Dynamic DDL Statements

During the application design phase the developer must decide if dynamic schema changes will be allowed. If so then all database access must be implemented through SQL or the UDA API; any operations using the generated C or C++ API, i.e. functions generated by mcocomp, become invalid.

A dynamic schema is implemented by specifying limits for the maximum number of classes and indexes in the database. When the SQL create table statement is executed it creates a definition of the new table using the eXtremeSQL API. Then the current database dictionary (in binary form maintained by the eXtremeDB runtime) is converted to SQL DDL format, the new table is also represented using SQL DDL and appended to the existing database schema. Then this new SQL DDL schema is converted back to an eXtremeDB dictionary and stored in place of the old dictionary. In order to make this possible, space is reserved when allocating the dictionary. Hence the need to specify the maximum number of classes and indexes.

A eXtremeSQL application using the C or C++ API specifies the maximum number of classes and indexes and the maximum dictionary size in the db_params structure. For example:

 
    db_params params;
    params.max_classes = 150;
    params.max_indexes = 120;
    params.ddl_dict_size = 128 * 1024;
     

Java and C# applications set the corresponding Database::Parameters properties maxClasses, maxIndexes and maxDictionarySize. For example:

 
    Database.Parameters dbParams = new Database.Parameters();
    dbParams.MaxClasses = 150;
    dbParams.MaxIndexes = 120;
    dbParams.MaxDictionarySize = 128 * 1024;
     

Python applications pass these values as arguments max_classes, max_indexes and maxDictionarySize to function open_database(). For example:

 
    db = exdb.open_database(dbname='opendb', dictionary=dict, is_disk=is_disk,
                    db_segment_size=128*1024*1024,
                    max_classes=100, max_indexes=1000,
                    maxDictionarySize=16*1024)

When using xSQL to create a database these parameters are specified in the db_params section of the configuration file. For example:

     
    db_params: {
        ddl_dict_size: 128 * 1024,
        max_classes : 150,
        max_indexes : 120,
    }
     

 

The eXtremeSQL syntax and detailed information for the SQL DDL statements are provided in separate pages. Please use the links below to view the individual statement specs:

create table	Create an SQL table (corresponds to eXtremeDB class)
alter table	Alter an SQL table
drop table	Drop or remove an SQL table
create index	Create an index
drop index	Drop or remove an index
create synonym	Create an alias (synonym)
drop synonym	Drop or remove an alias (synonym)