The eXtremeSQL C++ Class McoSqlEngine

The principle class for most eXtremeSQL C++ applications is McoSqlEngine. (However, functionality for managing transactions and executing prepared statements, i.e. SQL statements that are created once and executed multiple times, is provided in the McoSqlSession class. Please refer to the Managing Transactions page for further details.)

As explained in the Memory Allocator page, memory allocation in eXtremeSQLversion 6.5 and later is done internally through a static memory block allocator. Consequently instantiating the McoSqlEngine in global static memory should be avoided. For example, instead of the declaration

 
    McoSqlEngine engine;
     

in global static memory, engine should be instantiated in main() as follows:

 
    int main(int argc, char** argv)
    {
        McoSqlEngine engine;
        ...
    }
     

The engine is then initialized by calling its open() method and closed by the close() method:

 
    using namespace McoSql;
    char * db_name = "persondb";
    #define  DATABASE_SIZE        268435456
    #define  MEMORY_PAGE_SIZE     128
    int main(int argc, char* argv[])
    {
        McoSqlEngine engine;
     
        ...
         
        engine.open( db_name, persondb_get_dictionary(), DATABASE_SIZE, MEMORY_PAGE_SIZE);
     
        // Do normal database processing
         
        engine.close();
        return 0;
    }
     

To illustrate normal database processing, the following example creates two Person records in a simple database, then selects, updates and deletes them:

 
    /* Schema definition of database record Person */
    class Person 
    {
        char<64> name;
        int4     age;
        float    weight;
        tree<name> by_name;
    };
     
    // Forward declaration of function describing database schema
    GET_DICTIONARY(persondb);
     
    // Define the structure correponding to database record Person
    struct _Person
    {
        char const* name;
        int age;
        float weight;
    };
     
    int addPersons()
    {
        _Person p;
        int count = 0;
         
        // Add two Person records
        p.name = "John Smith";
        p.age = 35;
        p.weight = 72.1f;
        engine.executeStatement("insert into Person %r", &p);
        p.name = "Peter Brown";
        p.age = 40;
        p.weight = 62.1f;
        engine.executeStatement("insert into Person %r", &p);
     
        // List the contents of the Person table
        QueryResult result(engine.executeQuery("select * from Person order by age"));
        showPersons( &result );
         
        // Search for Person records by name
        strcpy( name, "John%" );
        engine.executeQuery("select * from Person where name like %s", name));
        strcpy( name, "%Brown%" );
        engine.executeQuery("select * from Person where name like %s", name));
         
        // Update two Person records by name
        strcpy( name, "John Smith" ); age = 36; weight = 75.2;
        count = engine.executeStatement("update Person set age=%i, weight=%f where name=%s",
                            age, weight, name)
        strcpy( name, "Peter Brown"); age = 41; weight = 65.0;
        count = engine.executeStatement("update Person set age=%i, weight=%f where name=%s",
                            age, weight, name)
     
        // Delete two Person records by name
        strcpy( name, "John Smith" );
        count = engine.executeStatement("delete from Person where name=%s", name);
        count = engine.executeStatement("delete from Person”);
     
        // Close SQL engine and database
        engine.close();
        return 0;
    }
     
    void showPersons(QueryResult * result)
    {
        Cursor* cursor = (*result)->records();
        while (cursor->hasNext())
        {
            Record* rec = cursor->next();
            _Person p;
     
            // Extract the Person record to the corresponding struct
            (*result)->extract(rec, &p, sizeof(p));
            printf("\t\t%s: Age=%d Weight=%.2f\n", p.name, p.age, p.weight);
        }
    }
     

There are some important programming points to note in the code sample above:

     
    p.name = "John Smith";
     
 
    engine.executeStatement("insert into Person %r", &p);
     
 
    count = engine.executeStatement("update Person set age=%i, weight=%f where name=%s",age, weight, name)
 

 

QueryResult usage

The main purpose of the C++ QueryResult is to control the scope of the query and automatically release all allocated resources if the query is terminated abnormally. If the scope of the query (the lifetime of the QueryResult object instance) is incorrect it will lead to a runtime error. Often this happens when the instance is used beyond the transaction it has been created in or still exists at the time the database instance is closed. The following code snippet demonstrates incorrect usage:

     
    main ()
    {
        McoSqlEngine engine;
        engine.open( ... );
        QueryResult result(engine.executeQuery("select * from aRecord"));
        Cursor* cursor = result->records();
        while (cursor->hasNext()) 
        {
            ...
        } /* end of while loop */
        engine.close(); /* database instance is destroyed here */
        return 0;
     
        /* object of class QueryResult represented by local variable 'result' will be deleted
        here. Since the database no longer exists, the QueryResult destructor crashes the
        application */
    }
     

Because the transaction context that created the object is kept within the object, if the transaction is no longer valid, any operation over its handle leads to an error. In the code above, the QueryResult destructor is called as the object result goes out of scope. But the transaction handle in result is no longer valid because the database has been closed. So the attempt to close the transaction causes the application to crash!

To avoid this, any instance of the QueryResult class must be deleted as soon as it is no longer required. This can be done by explicitly calling the delete operator on the object if it was created by new, or by using enclosing braces if the object is instantiated as a local variable.

Below are a two examples that demonstrate proper usage of the QueryResult class:

     
    main ()
    {
        McoSqlEngine engine;
        engine.open( ... );
        {
            QueryResult result(engine.executeQuery("select * from aRecord"));
            Cursor* cursor = result->records();
            Record*  rec;
            while (rec = cursor->next()) 
            {
                ...
            } /* end of while loop */
        } /* object of class QueryResult represented by local variable 'result' will be
                                        deleted here */
        engine.close(); /* database instance is destroyed here */
        return 0;
    }
     

Note that here the enclosing braces cause the QueryResult result defined within to go out of scope before the call to engine.close() causing its destructor to be called before the transaction handle in result becomes invalid. Perhaps a more convenient way to assure that QueryResult objects are properly scoped is to instantiate them in a separate query method as demonstrated below:

     
    void do_query(McoSqlEngine &engine)
    {
        QueryResult result(engine.executeQuery("select * from aRecord"));
        Cursor* cursor = result->records();
        Record*  rec;
        while ( rec = cursor->next()) 
        {
            ...
        } /* end of while loop */
        return;
    } /* object of class QueryResult represented by local variable 'result' will be deleted here */
     
    main ()
    {
        McoSqlEngine engine;
        engine.open( ... );
        do_query(engine);
        engine.close(); /* database instance is destroyed here */
        return 0;
    }
     

 

A note on statement-level read consistency

The eXtremeSQL engine assures that statement-level read consistency is enforced. This means that the database runtime guarantees that all the data returned by a single query comes from a single point in time - the time that the query began. The performance is not affected when the MVCC transaction manager is used. With the MURSIW transaction manger some queries could be slower (though this is difficult to quantify). Specifically SQL queries that browse many objects over an index in the context of a read-only transaction could be slower (i.e. select * from XXX order by key).

 

Using fixed-size structures to optimize read and write operations via SQL

eXtremeSQL allows passing structs as SQL statement parameters by use of the “place holder” specifiers %r and %R. The latter requires a fixed-size structure to be substituted in place of the %R. When fixed-size structures are used the internal SQL API implementation can optimize the read or write operation by avoiding multiple calls to *_get() or *_put().

For example, consider the following struct:

     
    struct Person
    {
        char const* name;
        int age;
        float weight;
    };
     

As this structure has a char* element, it has a variable size. To pass this structure to an SQL statement, it would be necessary to use the %r specifier. For example:

     
    void addPerson(Person* p)
    {
        // Add new record to the table.
        // Record will be initialized with data passed through the Person struct.
        engine.executeStatement("insert into Person %r", p);
    }
     

However, the structure could be defined as a fixed size struct (i.e. having no variable length elements) like the following:

     
    class Person {
        char<64> name;
        int4     age;
        float    weight;
        tree<name> by_name;
    };
     

Now this structure can be passed to an SQL statement using the more efficient %R specifier as follows:

     
    void addPerson(Person* p)
    {
        // Add new record to the table.
        // Record will be initialized with data passed through Person struct.
        engine.executeStatement("insert into Person %R", p);
    }
     

In this case the eXtremeSQL implementation is able to store data much more efficiently: it will not use data access wrappers but instead copy the data directly to the database pages. So there are no extra functions calls, and the destination page is not pinned / unpinned multiple times.

But note that this kind of optimization is possible only for classes that contain only fixed-size components.

 

eXtremeDB indexes and SQL

Indexes are typically used to optimize ordering and search capabilities for database objects. The addition of indexes can significantly speed searches and provide enhanced filtering capabilities to the select statement. Whereas indexes are often introduced into a SQL database using the standard DDL statement Create Index…, in eXtremeDB databases, indexes are more conveniently defined in the database schema. For example the following schema defines two indexes for a modified version of the Person class presented above:

     
    class Person 
    {
        int4     id;
        char<64> name;
        int4     age;
        float    weight;
         
        hash<id>   by_id[1000];
        tree<name> by_name;
    };
     

Though not necessary, these indexes improve the performance of select statements on the Person table. The tree index by_name will be used by the eXtremeSQL runtime (behind the scenes, as it were) to optimize a select statement like that performed above in the code snippet

     
    engine.executeQuery("select * from Person where name like %s", name);
     

Depending on the database design, sometimes an even better degree of optimization can be obtained using hash indexes like by_id to lookup objects by unique identifiers as, for example, in a statement like the following:

     
    engine.executeQuery("select * from Person where id = %d", personId);
     

As explained in the eXtremeDB User Guide, eXtremeDB offers a rich set of index types that can be specified in the schema definition to facilitate a variety of specialized data access operations. In particular, some extensions to the standard SQL language have been added to support one of these special index types, the rtree, which is used for spatial searches.

To illustrate a spatial search, consider a database schema like the following:

     
    class Consumer
    {
        int4 	ConsumerId;
        double 	Coordinates1[4];
        double	Coordinates2[4];
        double	Coordinates3[4];
         
        hash<ConsumerId> by_id[1000];
        rtree <Coordinates1>  ridx1;
        rtree <Coordinates2>  ridx2;
        rtree <Coordinates3>  ridx3;
    };
     

Here the Consumer class has been defined with three rtree indexes using the eXtremeDB native DDL. Now any SQL search on any of the Coordinates fields will use the appropriate index. The same schema can be defined with extended SQL as follows:

     
    create table Consumer
    (
        ConsumerId int primary key,
        Coordinates1 array(double, 4) using rtree index,
        Coordinates2 array(double, 4) using rtree index,
        Coordinates3 array (double, 4) using rtree index
    );
             

With these indexes defined we can now perform a spatial search for objects having coordinates Contained, Overlapping or Near a specified point. For example, the following script could be run with the xSQL utility to create the table, add some records and then perform a spatial search:

     
    create table Consumer
    (
        ConsumerId int primary key,
        Coordinates1 array(double, 4) using rtree index,
        Coordinates2 array(double, 4) using rtree index,
        Coordinates3 array (double, 4) using rtree index
    );
     
    insert into Consumer(ConsumerId, Coordinates1, Coordinates2, Coordinates3)
    values(1, '{1.0, 1.0, 2.0, 2.0}', '{3.0, 3.0, 4.0, 4.0}', '{5.0, 5.0, 6.0, 6.0}');
     
    insert into Consumer(ConsumerId, Coordinates1, Coordinates2, Coordinates3)
    values(2, '{10.0, 10.0, 20.0, 20.0}', '{30.0, 30.0, 40.0, 40.0}', '{50.0, 50.0, 60.0, 60.0}');
    numformat "%4.1f"
     
    select * from Consumer;
     
    select * from Consumer where Coordinates1 contains '{3.5, 3.5, 3.5, 3.5}' union
    select * from Consumer where Coordinates2 contains '{3.5, 3.5, 3.5, 3.5}' union
    select * from Consumer where Coordinates3 contains '{3.5, 3.5, 3.5, 3.5}';
     
    select * from Consumer where Coordinates1 overlaps '{3.5, 3.5, 3.5, 3.5}' union
    select * from Consumer where Coordinates2 overlaps '{3.5, 3.5, 3.5, 3.5}' union
    select * from Consumer where Coordinates3 overlaps '{3.5, 3.5, 3.5, 3.5}';
     
    select * from Consumer where Coordinates1 near '{3.5, 3.5}' union
    select * from Consumer where Coordinates2 near '{3.5, 3.5}' union
    select * from Consumer where Coordinates3 near '{3.5, 3.5}';
     

Running this script in xSQL will produce the following output:

     
    ConsumerId      Coordinates1    Coordinates2    Coordinates3
    ------------------------------------------------------------------------------
    1       [ 1.0, 1.0, 2.0, 2.0]   [ 3.0, 3.0, 4.0, 4.0]   [ 5.0, 5.0, 6.0, 6.0]
    2       [10.0,10.0,20.0,20.0]   [30.0,30.0,40.0,40.0]   [50.0,50.0,60.0,60.0]
     
    Selected records: 2
    ConsumerId      Coordinates1    Coordinates2    Coordinates3
    ------------------------------------------------------------------------------
    1       [ 1.0, 1.0, 2.0, 2.0]   [ 3.0, 3.0, 4.0, 4.0]   [ 5.0, 5.0, 6.0, 6.0]
     
    Selected records: 1
    ConsumerId      Coordinates1    Coordinates2    Coordinates3
    ------------------------------------------------------------------------------
    1       [ 1.0, 1.0, 2.0, 2.0]   [ 3.0, 3.0, 4.0, 4.0]   [ 5.0, 5.0, 6.0, 6.0]
     
    Selected records: 1
    ConsumerId      Coordinates1    Coordinates2    Coordinates3
    ------------------------------------------------------------------------------
    1       [ 1.0, 1.0, 2.0, 2.0]   [ 3.0, 3.0, 4.0, 4.0]   [ 5.0, 5.0, 6.0, 6.0]
    2       [10.0,10.0,20.0,20.0]   [30.0,30.0,40.0,40.0]   [50.0,50.0,60.0,60.0]
     
    Selected records: 2
     

Note the following in this example:

 

eXtremeDB to C struct type mappings

Currently, the schema compiler does not generate corresponding C structs for class / table definitions – the developer must supply the C structs in the application code. The following table specifies the C struct type to be used for each eXtremeDB field type:

eXtremeDB FIELD TYPE C STRUCT TYPE
signed<1> Char
signed<2> Short
signed<4> Int
signed<8> int64_t
unsigned<1> unsigned char
unsigned<2> unsigned short
unsigned<4> unsigned int
unsigned<8> int64_t
float float
double double
string char*
nstring wchar_t*
vector Array*
array Array*
struct Struct*
autoid_t Reference*
time time_t
date time_t

 

The McoSqlEngine:open() method

The prototype for McoSqlEngine::open is:

     
    open(char const* name,
        mco_dictionary_h dictionary,
        size_t size,
        size_t pageSize = 128,
        void* mapAddress = (void*)0x20000000,
        size_t maxTransSize = 0,
        int flags = ALLOCATE_MEMORY|
                SET_ERROR_HANDLER|
                START_MCO_RUNTIME|
                SET_SQL_ALLOCATOR|
                INITIALIZE_DATABASE,
        char const* databaseFile = NULL);
         

Internally, McoSqlEngine::open() does the following:

  1. Checks for local or shared memory support.
  2. If local memory and (flags & ALLOCATE_MEMORY) is true, then calls malloc() to allocate DATABASE_SIZE bytes.
  3. Sets the eXtremeDB error handler to McoDatabase::checkStatus.
  4. Initializes the eXtremeDB run-time.
  5. Sets the McoSQL memory allocator.
  6. Sets the maximal transaction size.
  7. Connects to or opens the eXtremeDB database.
  8. Opens the SQL engine.

Loading and Saving database images

The last optional parameter of the McoSqlEngine::open() method is a path to the database file, from which the database should be loaded. By default, the value of this parameter is NULL and the database is not loaded from disk. If the parameter is not NULL, then the open() method tries to load the database from the specified file and initializes and empty database if the file doesn't exist. There is also a McoSqlDatabase::save() method for saving a database in the specified file.

So to load a database from an image previously saved in a file, you must specify the last parameter of the open method, and to save a database image prior to closing the database, insert a call to the save() method before the database is closed.

Saving and Loading class data

It is sometimes useful to save and/or load the data for an entire class (table) to/from an external file. This is done with the saveClass() and loadClass() APIs:

     
    void saveClass(char const *filePath, char const *className);
     
    void loadClass(char const *filePath, char const *className = 0);
     

(Please refer to the Saving/Loading individual classes page for further details.)

 

The McoSqlEngine:close() method

Internally, McoSqlEngine::close() does the following:

  1. Releases the memory allocator.
  2. Closes the database (mco_db_disconnect() & mco_db_close()).
  3. Releases the memory, if it was allocated during initialization.
  4. Terminates the eXtremeDB run-time.
Send feedback on this topic to McObject.Download documentation.© 2001-2024 McObject LLC. All rights reserved.