Changing the Database Schema

Changing the database schema after an initial implementation is common during the development stages and may be necessary after application deployment. The technique for altering the database schema and programming considerations vary depending on the APIs used to access the database. It is important to understand the difference between static and dynamic schemas as they are totally different entities and can not be used together, though they can be compatible.

Typically C and C++ applications will use a static schema defined in an external text file then compiled with the mcocomp DDL compiler. For these applications, the schema file is modified as needed and recompiled to produce a new static dictionary., Then the application is recompiled and the static dictionary is loaded during the call to mco_db_open_dev() or loaded explicitly by calling mco_load_dictionary(). The dictionary can be saved to disk by calling the C API mco_db_save_dictionary() or the C++ method saveDictionary().

A static dictionary is also generated in xSQL and Python applications when they load the schema. Java and C# applications create a dynamic dictionary during execution by interpreting the native class definitions. This dictionary can then be saved to disk by calling the saveDictionary() or SaveDictionary() method.

During early development, schema changes may be frequent and the process of regenerating the database APIs and application consists of repeating the normal steps of application development for the API of choice. But once an application is in use in a production environment, changes to the database schema may require more planning and careful deployment. To address this need, eXtremeDB incorporates Binary Schema Evolution (BSE) capability.

SQL Dynamic DDL

If SQL Dynamic DDL statements such as create table, alter table or create index are used, the static schema automatically (and silently) becomes a dynamic schema. For example the SQL alter table statement can be used to change a table format. But in order to access a table that has been modified in this way from an application using a non-SQL API, the dynamic dictionary (the binary form of the schema) containing the table redefinition must be saved and reprocessed or reloaded so that the non-SQL application interfaces correctly with the new table format.

Note that xSQL must know at startup whether it is using a static or dynamic schema. If a static schema is used in the xSQL configuration file, after the user issues a SQL Dynamic DDL statement, this configuration file silently becomes invalid and it is necessary to remove the schema section. If starting xSQL with a static schema over an existing dynamic schema database that has changed its schema, the error MCO_E_SCHEMA_CHANGED will be emitted. Also, if the savemeta command is called it will not reflect the actual (changed) database schema! Please note that mixing static and dynamic dictionaries in the context of the same application is highly discouraged! Brace yourself for unexpected difficulties. For example, in any tables with an autoid, the autoid declaration must appear before any other indexes.

Saving the Database Dictionary

To save the database dictionary in a text schema file, the following APIs are provided:

Using xSQL

From xSQL, use the savedict command:

     
    savedict <file-path>
     

Embedded eXtremeDB and eXtremeSQL

From an embedded eXtremeDB or eXtremeSQL application, use one of the APIs described in the following sections.

C API

For applications using the C API, call mco_db_save_dictionary():

 
    MCO_RET mco_db_save_dictionary(void* stream_handle,
                        mco_stream_write output_stream_writer,
                        mco_db_h db);
                         

C++ API

For applications using the C++ API, call method saveDictionary():

 
    void McoSqlEngine::saveDictionary(char const *filePath);
     
    void McoSqlEngine::saveDictionary(void *stream,
                        mco_stream_write streamWriter);
                         

Java

For applications using the Java API, call the Connection class method saveDictionary():

 
    public boolean Connection.saveDictionary(String filePath);
     

C#

For applications using the C# API, call the Connection class method SaveDictionary():

 
    public void Connection.SaveDictionary(String filePath);
     

Python

For applications using the Python API, call the Connection class method saveDictionary():

 
    Connection.saveDictionary(string file path);
     

C and C++ applications must process the new schema file with the DDL schema compiler mcocomp which generates new access interfaces; the resulting .h and .c files are compiled into the C or C++ application. Then the new executable can properly access the modified table(s).

Java, C# and Python applications must reload the database schema from the saved file, or must be modified to reflect the new version of the schema.