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

> - The macro `GET_DICTIONARY` (defined in `mcoapi.h`) defines the `get*_dictionary()` function as an `“extern C”` function (embedding the database name into the function name).
> - The C structure `_Person` is defined in the application to correspond exactly with the class Person in the database `schema`. The C type (and consequently memory size) of each element of the structure must correspond exactly to the eXtremeDB type used to define that field in the database class. (For type mappings see the table below.)
> - The `_Person` field `name` is of type `char const*`, so to assign string values to this field we set the pointer value to the address of a static string instead of using a character array (`char[64]`) field type and then performing some variant of `strcpy()` to copy values.

> >         p.name = "John Smith";

> - The specialized eXtremeSQL `%r` syntax for copying the entire contents of structure `_Person` into the SQL `insert` statement saves considerable programming:

> >         engine.executeStatement("insert into Person %r", &p);

> - The QueryResult class provides a `cursor` that is conveniently used by function `showPersons()` to iterate through the result set.
> - When instantiated within a function like `showPersons()` the QueryResult object, its `cursor` (result set) and all memory associated with it is released by the class destructor when the object goes out of scope; i.e. when the function returns.
> - Again the `_Person` structure is used to extract data from the database records retrieved by the SQL `select`. This works because the alignment of data in the structure exactly corresponds to the class definition.
> - The `executeStatement` method for `Update` and `Delete` statements returns a count of the actual rows updated or deleted.

> >         count = engine.executeStatement("update Person set age=%i, weight=%f where name=%s",age, weight, name)

> - The specialized eXtremeSQL `%s`, `%i` and `%f` format specifiers for copying character strings, integer and floating-point values into the SQL `select`, `update` and `delete` statements directly substitutes the values in the parameters list into the SQL statement. (Beware that there is a maximum of 10 parameter values in this list. Please refer to the Argument Substitution page for the complete list of eXtremeDB format specifiers.)

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

> - `rtree` indexes are defined for three `Coordinates` fields in the Consumer class with the extended SQL clause `using rtree index`.
> - fields used in `rtree` indexes must be declared as arrays with dimension 4 to be treated as rectangles: eg. `array(double, 4)`.
> - values are specified for the `insert` statement using `set` syntax like `{1.0,1.0,2.0,2.0}` to specify the `upper-left` (`1.0,1.0`) and `lower-right` (`2.0,2.0`) points of the rectangle.
> - the `numformat` command is used to restrict the floating-point output (using standard C `printf` field width and precision specification)
> - the SQL `union` construct is used to `or` the selection over fields `Coordinate1`, `Coordinate2` and `Coordinate3`.
> - the SQL extensions `Contains`, `Overlaps` and `Near` implement the `spatial` search
> - the result for the `Contains` and `Overlaps` searches is `record 1` whose `Coordinate2` rectangle does indeed contain and overlap the point (`3.5,3.5`) specified as a rectangle with zero width and height.
> - the result for the `Near` search is both records in order of distance from the point (`3.5,3.5`).

> ## 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 shor`t
> `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.