# Using eXtremeSQL Structures, Arrays and Strings in C++

> The following eXtremeSQL sample code snippets illustrate the use of the Field, Struct and Array classes to access elements of structures. The sample code uses the following schema definition:

>         declare database structuresdb;
>         struct aPoint
>         {
>             int4 x;
>             int4 y;
>         };

>         struct aLine
>         {
>             aPoint begin;
>             aPoint end;
>         };

>         struct aPolygon
>         {
>             vector<aPoint> points;
>         };

>         class aRecord
>         {
>             string s;
>             int2   i2;
>             aPoint p;
>             int1   i1;
>             int8   i8;
>             float  f;
>             double d;
>             aLine  l;
>             int1   ai1[100];
>             vector<aPolygon> vp;
>             aLine  lines[10];
>             int4   i4;
>             hash<i4> by_i4[1000];
>             list;
>             autoid[1000];
>         };

> Note the embedded structures `aPoint`, `aLine` and the vector or `aPolygon` structures defined in class aRecord. These are mirrored by the C++ structure definitions at the head of the following code snippets.

>         const char * db_name = "structuresdb";
>         …
>         // Define global SQL engine
>         using namespace McoSql;
>         McoSqlEngine engine;

>         // Define the structure correponding to database tables
>         struct _Point
>         {
>             int x;
>             int y;
>         };

>         struct _Line
>         {
>             _Point begin;
>             _Point end;
>         };

>         struct _Polygon
>         {
>             Array* points;
>         };

>         struct _Record
>         {
>             char* s;
>             short i2;
>             _Point p;
>             char i1;
>             int64_t i8;
>             float f;
>             double d;
>             _Line l;
>             Array* ai1;
>             Array* vp;
>             Array* lines;
>             int i4;
>         };
>         …
>         int main( int argc, char* argv[] )
>         {
>             …
>             engine.open( db_name, structuresdb_get_dictionary(), DATABASE_SIZE, MEMORY_PAGE_SIZE);

>             // Insert records
>             insertRecords();

>             // Show contents (without arrays)
>             showRecords();

>             // Add arrays of ints, Polygons and Lines to Records
>             addArrays();

>             // Show contents with arrays
>             showRecordArrays();
>             engine.close();

>             sample_pause_end("\n\nPress any key to continue . . . ");
>             return 0;
>         }

>         void insertRecords()
>         {
>             …
>             for (int i = 1; i <= nRecords; i++)
>             {
>                 _Record r;
>                 char buf[256];
>                 sprintf(buf, "Record %d", i);
>                 r.s = buf;
>                 r.i2 = i * 10;
>                 r.p.x = i + 1;
>                 r.p.y = i + 2;
>                 r.i1 = i % 10;
>                 r.i8 = i * 100;
>                 r.f = (float)i / 10;
>                 r.d = (double)i / 100;
>                 r.l.begin.x = i + 2;
>                 r.l.begin.y = i + 2;
>                 r.l.end.x = i + 3;
>                 r.l.end.y = i + 4;
>                 r.i4 = i;
>                 r.ai1 = NULL;
>                 r.vp = NULL;
>                 r.lines = NULL;
>                 engine.executeStatement("insert into aRecord %r", &r);
>             }
>         }

> Some programming points to note in the code snippet “insertRecords” above:

> - The `Array` elements of the _Record object are initialized `NULL`. These are given values in the function `addArrays()` (following code snippet).
> - The begin and end points of the `_Line` element `l` are scalar integer values set by simple assignment

>         void addArrays()
>         {
>             int i,j, k;
>             ...
>             for ( i = 1; i <= nRecords; i++ )
>             {
>                 // Get record i for updating
>                 QueryResult result(engine.executeQuery( "select * from aRecord where i4=%i for update", i ) );
>                 Cursor* cursor = result->records();
>                 Record* rec = cursor->next();

>                 // Get field values into Field variables
>                 Field* vp = result->findField("vp");
>                 Field* points = vp->element()->findComponent("points");
>                 Field* x = points->element()->findComponent("x");
>                 Field* y = points->element()->findComponent("y");
>                 Field* lines = result->findField("lines");
>                 Field* beg = lines->element()->findComponent("begin");
>                 Field* end = lines->element()->findComponent("end");

>                 // Get current contents of record into struct _Record
>                 _Record r;
>                 result->extract(rec, &r, sizeof(r));

>                 // Set values of Byte array
>                 memset( byteArray, 0, nBytes );
>                 for ( j=0; j < nBytes; j++ )
>                 byteArray[j] = ( i * nBytes) + j;

>                 r.ai1->setBody( byteArray, 0, nBytes );

>                 // Set Polygons
>                 r.vp->setSize( nPolygons );
>                 for ( j = 0; j < nPolygons; j++ )
>                 {
>                     Struct* poly = (Struct*)r.vp->updateAt( j );
>                     Array* pa = (Array*)points->update( poly );
>                     pa->setSize(nPoints);
>                     for ( k = 0; k < nPoints; k++)
>                     {
>                         Struct* point = (Struct*)pa->updateAt(k);
>                         x->set( point, new IntValue( (i * 100) + j + k + 1 ) );
>                         y->set( point, new IntValue( (i * 100) - j - k - 1 ) );
>                     }
>                 }

>                 // Set lines
>                 for ( j = 0; j < nLines; j++ )
>                 {
>                     Struct* line = (Struct*)r.lines->updateAt(j);
>                     Struct* bp = (Struct*)beg->update(line);
>                     Struct* ep = (Struct*)end->update(line);
>                     x->set( bp, new IntValue( (i * 100) + j + 1 ) );
>                     y->set( bp, new IntValue( (i * 100) - j - 1 ) );
>                     x->set( ep, new IntValue( (i * 100) + j + 3 ) );
>                     y->set( ep, new IntValue( (i * 100) - j - 3 ) );
>                 }

>                 // Update the database record
>                 rec->updateRecord();
>                 }
>             }
>         }

> Some programming points to note in the code snippet `addArray` above:

> - The McoSql class Record (variable `rec`) is used to position to each record in the cursor and then initialize its array fields.
> - The McoSql class Field is used to get pointers to each of the structure fields `vp`, `aPolygon.points`, and `lines` as well as the scalar fields `x`, `y`, `begin` and `end`. These pointers are then used to initialize the array and structure values.
> - The `Array` `ail` (a `byte` array in the database `schema`) is set by calling the Array class `setBody()` method.
> - The McoSql class Struct (variable `poly`) is initialized to point to the next aPolygon element of the vector of aPolygons `vp` then the `Array` variable `pa` is initialized to point to the array of `aPoints` in the aPolygon structure.
> - The Array method `setSize()` is called to dimension the array of `aPoints`.
> - The Struct class is used again (variable `point`) to point to each `aPoint` structure in the Array
> - The Field varibles `x` and `y` are used to set the `aPoint` values by calling method `set()` and instantiating the value argument with the IntValue constructor.
> - The `lines` Array field of aRecord is similarly set using the Struct and Field classes.

>         void showRecords()
>         {
>             printf("\n\tRecord table contents:" );
>             for ( int i=1; i <= nRecords; i++ )
>             {
>                 _Record r;
>                 QueryResult result( engine.executeQuery( "select * from aRecord where i4=%i", i ) );
>                 Cursor* cursor = result->records();
>                 if ( cursor->hasNext() )
>                 {
>                     Record* rec = cursor->next();
>                     result->extract( rec, &r, sizeof(r) );
>                     printf( "\n\t%d) %s: i2=%d, i4=%d, x=%d, y=%d, f=%f, d=%f, \n\t\t"
>                         "line(x1,y1,x2,y2)=%d,%d,%d,%d",
>                         i, r.s, r.i2, r.i4, r.p.x, r.p.y, r.f, r.d,
>                         r.l.begin.x, r.l.begin.y, r.l.end.x, r.l.end.y );
>                 }
>             }
>         }

>         void showRecordArrays()
>         {
>             int i,j,k;
>             printf("\n\n\tRecord Array contents:" );
>             for ( i=1; i <= nRecords; i++ )
>             {

>                 // Get record i
>                 QueryResult result(engine.executeQuery( "select * from aRecord where i4=%i", i ) );
>                 Cursor* cursor = result->records();
>                 Record* rec = cursor->next();
>                 _Record r;
>                 result->extract(rec, &r, sizeof(r));
>                 printf( "\n\n\t%d) %s: ", i, r.s );

>                 // Display Byte Array
>                 printf( "\n\t\tArray of %d 'i1' values: ", nBytes );
>                     r.ai1->getBody( byteArray, 0, nBytes );
>                 for ( j=0; j < nBytes; j++)
>                 {
>                     printf( "%d%s", byteArray[j], ( j == nBytes-1 ? "" : ", " ) );
>                 }

>                 // Display Polygon points
>                 printf( "\n\t\tPoints for %d Polygons : ", nPolygons );

>                 for ( j=0; j < nPolygons; j++ )
>                 {
>                     Struct* poly = (Struct*)r.vp->getAt(j);
>                     Array* points = (Array*)poly->get(0);
>                     printf( "\n\t\t\t Polygon %d:", j+1 );

>                     for ( k=0; k < nPoints; k++ )
>                     {
>                         Struct* point = (Struct*)points->getAt(k);
>                         printf( "\n\t\t\t\t%d) x=%d, y=%d ", k+1, (int)point->get(0)->intValue(), (int)point->get(1)->intValue() );
>                     }
>                 }

>                 // Display Line end points
>                 printf( "\n\t\tEnd points for %d Lines : ", nLines );

>                 for ( j=0; j < nLines; j++ )
>                 {
>                     Struct* line = (Struct*)r.lines->getAt(j);
>                     Struct* beg = (Struct*)line->get(0);
>                     Struct* end = (Struct*)line->get(1);
>                     printf( "\n\t\t\t\t%d) begin=(%d,%d) end=(%d,%d)", j+1, (int)beg->get(0)->intValue(), (int)beg->get(1)->intValue(),
>                     (int)end->get(0)->intValue(), (int)end->get(1)->intValue() );
>                 }
>             }
>         }

> Some programming points to note in the code snippets “`showRecor`d” and “`showRecordArrays`” above:

> - The coordinates for element point “`p`” of aRecord objects can be accessed directly from the aRecord structure as `r.p.x` and `r.p.y`.
> - The coordinates of points of the aPolygon structures within vector element `vp` must be accessed through the Struct and Array classes; the Struct object `poly` is first initialized to element `j` of `vp` by method `getAt()`, then the Array `points` is set to `aPolygons.points` by the Struct method `get(0)` and each `point` is initialized to point to element `k` of the Array `points` and the Struct method `get(0)->intValue()` is called to extract and convert to integer type the value of `point.x` and `get(1)->intValue()` for `point.y`.
> - Similarly, the coordinates `begin` and `end` for the `lines` array are accessed and converted to integer by the Struct method `get(n)->IntValue()`.

>         // Select a struct element and string field from aRecord
>         void showStruct( int x )
>         {
>             printf("\n\n\tExtract p.y and s fields from aRecord with p.x=%d:", x );
>             QueryResult result(engine.executeQuery( "select p.y, s from aRecord where p.x=%i", x ) );
>             Cursor* cursor = result->records();
>             Record* rec = cursor->next();

>             // Use Struct::get(int index) method to refer to the result column by index (0-based)
>             int2 y = (int2)rec->get(0)->intValue();
>             printf( "\n\t\tUsing Struct::get(int index): y=%d, s='%s'", y, rec->get(1)->pointer() );

>             // Use field descriptors
>             printf( "\n\t\tUsing QueryResult::Fields iterator:" );
>             int iField = 0;
>             Iterator <Field> * fields = result->fields();
>             Field * f;
>             while ( f = fields->next() )
>             {

>                 switch ( iField )
>                 {
>                     case 0:
>                         printf( "\n\t\t\tField %s=%d", f->name()->cstr(), (int)f->get(rec)->intValue() );
>                         break;
>                     case 1:
>                         printf( "\n\t\t\tField %s='%s'", f->name()->cstr(), f->get(rec)->pointer() );
>                 }
>                 iField++;
>             }

>             // Use the DataSource.extract() method. NB: The structure used must have same number
>             // of fields as specified in the select statement.
>             struct
>             {
>                 int y;
>                 char * s;
>             } row;
>             result->extract( rec, &row, sizeof( row ) );
>             printf( "\n\t\tUsing DataSource::extract(): y=%d, s='%s'\n", row.y, row.s );
>         }

> Some programming points to note in the code snippet “`showStruct()`” above:

> - The condition in the `select` statement specifies “`where p.x=%i`" to filter on the value of an element in the embedded Point struct.
> - Three different methods are used to extract the result set values. In particular, note how value of the `struct` element `p.y` is managed:
>   1. Using the `Struct::get(int index)` method to refer to the columns in the result row by index;
>   2. Using the `QueryResult::Fields` iterator to iterate over the columns in the result row;
>   3. Using the `DataSource::extract()` method to extract a `struct` that exactly corresponds with the number of columns in the result row.

> ## Arrays and Their Usage

> eXtremeSQL accepts eXtremeDB arrays (fixed length) and vectors (dynamic length) as components of records. As with eXtremeDB, multidimensional arrays are not supported. eXtremeSQL provides a set of special constructions for dealing with arrays and vectors (hereafter collectively referred to as ‘`array`’):

> - It is possible to get the number of elements in the array by using the `length()` function.
> - Array elements can be retrieved by using the `[]` operator. If the index expression is out of the array range, then an exception will be raised.
> - The operator `in` can be used for checking if an array contains values specified by the left operand. This operation can be used only for arrays of atomic types; with `boolean`, `numeric`, `reference` or `string` components.
> - Iteration through array elements is performed by the `exists` operator. Variables specified after the `exists` keyword can be used as an index in the arrays for the expression preceded by the `exists` quantor. This index variable will iterate through all the possible array index values, until the value of expression becomes true or the index exceeds the array’s range.

> > For example, given the following database schema:

> >         class Company
> >         {
> >             string location;
> >             autoid[1000];
> >         };

> >         class Contract
> >         {
> >             autoid_t<Company> company;
> >             unsigned<8> quantity;
> >             date        delivery;
> >             autoid[1000];
> >         };

> >         class Detail
> >         {
> >             string name;
> >             vector<autoid_t> contract;
> >         };

> > the query:

> >         select * from Detail where exists i: (contract[i].company.location = 'US');

> > selects all the Detail records of companies located in the US.

> > While the query:

> >         not exists i: (contract[i].company.location = 'US')

> > selects all the Detail records of companies outside the US.

> ## Vectors and Arrays

> eXtremeSQL supports all eXtremeDB data types, including the `vector` (dynamic array) type. An Array is created by

>         McoSql::Array *arr = McoSql::Array::create(allocator, McoSql::tpInt8, 0, nElem);

> Fixed size items can be assigned by means of a pointer to an internal buffer as follows

>         McoSql::Array * arr = McoSql::Array::create(allocator,
>                     McoSql::tpInt8, 0, nElem);
>         int64_t * carr = (int64_t *)arr->pointer();
>         for (Py_ssize_t j = 0; j < nelem; j++)
>         {
>             int64_t v = PyInt_AsLong(pitem);
>             carr[j] = v;
>         }

> or, alternatively, a safer method for assigning values to individual elements is

>         int64_t val = 0;
>         arr->setAt(j, &val);

> eXtremeDB also supports nullable arrays. A nullable array can be created by calling the Array method `makeNullable()`. For example:

>         McoSql::Array *someArray;
>         ...
>         McoSql::Array *nullableArray = someArray->makeNullable();

> The newly created `nullableArray` is an instance of NullableArray with the contents of the original array and corresponding nullBitmap. The nullBitmap is an instance of McoSql::Array with elements of type `uint64_t`, where each set bit indicates that the element at the corresponding position of the array is null. It is possible to retrieve this bitmap using the `getNullBitmap()` method. For example:

>         McoSql::Array *nullBitmap = nullableArray->getNullBitmap();

> Note that the application is not required to inspect or modify this nullBitmap directly. It is possible to set a null element by passing the pointer to the `McoSql::Null` object to method `setAt()`. For example:

>         nullableArray->setAt(0, &McoSql::Null);

> Methods `getAt()` and `getCopyAt()` will return a pointer to the `McoSql::Null` object for the null entries of the NullableArray.

> ## Strings

> All operations that are applicable to `arrays` are also applicable to `strings`, which also have their own set of operations. For example, strings can be compared with each other using the standard relational operators.

> The construction like can be used for matching a string with a pattern containing the special wildcard characters '`%`' and '`_`'. The character '`_`' matches any single character, while the character '`%`' matches any number of characters (including 0). The extended form of the `like` operator with an escape part can be used to handle characters '`%`' and '`_`' in the pattern as normal characters only if they are preceded by the special escape character, which is specified after the escape keyword.

> For example,

>         select * from T where name like '#%x%' escape '#'

> will select all records where the name field starts with “`%x`”

> It is possible to search a substring within the string by using the `in` operator. So the expression '`blue`' in color will be true for all the records for which the color field contains the string '`blue`'. For example,

>         select * from car where 'blue' in color;

> will select car objects with color "`dark-blue`", "`blue`", "`white-and-blue`", "`light-blue`"...

> `Strings` can be concatenated by using the `+` or `||` operators (+ and || may be used interchangeably). eXtremeSQL doesn't support implicit conversions to the `string` type in expressions, so the semantics of the operator + has been redefined for strings. In other words, in many SQL implementations it is possible to write:

>         1+'1'

> and the result will be 2 (here implicit conversion is performed from string to integer).

> The result of

>         1||'1'

> will be '11'.

> eXtremeSQL doesn't allow implicit conversion from strings, so the result of 1+'1' will be '11', the same as 1||'1'.

> It should be noted that every class that is inherited from base class Value implements a method `String *stringValue`. In all implementations of this method (except for the class String) a new object of type `*String` is created, and it should be deleted after its use. The class String method returns `this` instead. Thus if the application deletes the string object, and then also deletes the object that the current object was inherited from, the object is deleted twice! Also once deleted, it is not possible to use the parent object either.

> So, to avoid confusion, instead of using the `String *stringValue` method for converting the Value into the `string`, it is advisable to use the `Ref<String> stringRef(Allocator* allocator)` method. This method watches over the lifecycle of the object and removes it when appropriate. There are two ways to use it:

>         Ref<String> strval(v->stringRef(allocator));

> or

>         Ref<String> str = v->stringRef(allocator);

> The scope and usage of the reference is illustrated as follows:

>         {
>             Ref<String> strval(v->stringRef(allocator));
>             /* use a reference as a local object String */
>             printf("%s", strval.cstr());
>         } /* Ref is automatically deleted here */

> or

>         {
>             Ref<String> str = v->stringRef(allocator);
>             /* use reference as a pointer to the  String object*/
>             printf("%s", str->cstr());
>         } /* Ref is automatically deleted here */

> ## StringLiteral

> The StringLiteral constructor has a “`chars`” member variable with a size of 1. This illustrates the standard allocation of a structure with a variable size. When this structure is allocated, it is necessary to add the size of the fixed and the variable portions of the object. In C++, it can be done by using the operator `new` with an extra parameter. However, such an operator is not predefined, and you would need to define it yourself (as it is done in the DynamicObject class):

>         inline void* operator new(size_t fixed, size_t var)
>         {
>             return ::new char[fixed + var];
>         }

> Now the string literal can be created in the following way:

>         char const* str = "Hello World";
>         size_t length = strlen(str);
>         Value* value = ::new (length) StringLiteral(str, length);

> There are two alternatives:

> > 1. Use the StringRef class for which the constructor is given a pointer to the `string` and it is the responsibility of the programmer to de-allocate the `string` when it is not needed any longer.

> > 2. Define your own class extending the String class:

> >         class UserString : public String
> >         {
> >             public:
> >             virtual int size();
> >             virtual char* body();
> >             virtual char* cstr();

> >             private:
> >             const int length;
> >             char* chars;

> >             public:
> >             UserString(char const* s, int l);
> >             ~UserString();
> >         };

> >         int UserString::size()
> >         {
> >             return length;
> >         }

> >         UserString::UserString(char const* s, int l) : length(l)
> >         {
> >             chars = new char[l+1];
> >             memcpy(chars, s, l);
> >             chars[l] = '\0';
> >         }

> >         UserString::~UserString()
> >         {
> >             delete[] chars;
> >         }

> >         char* UserString::body()
> >         {
> >             return chars;
> >         }

> >         char* UserString::cstr()
> >         {
> >             return chars;
> >         }

> ## Binary Data

> Binary and varbinary data that may contain zeroes can be stored in an eXtremeSQL `byte`-array or fields of type `binary` or `varbinary`. To insert binary data with embedded zeroes into or retrieve from a field of type `byte`-array, use the Array `putBody()` or getBody() methods. For example, in the schema definition:

>         class t
>         {
>             signed<1> b[16];
>             list;
>         };

> the field “`b`” is a `byte`-array and will be accessed within application code as an Array - use the `Array::getBody()` method to get the contents of the array.

> If an index is required on binary data, the field must be of type `binary` or `varbinary`. For example, in the schema definition:

>         class bin_array
>         {
>             unsigned<4>             idx;
>             binary<16>              bin_array;

>             userdef tree<bin_array> binkey;
>             kdtree <idx, bin_array> binkey_kd;
>             patricia<bin_array>     binkey_p;
>         };

>         class bin_vector
>         {
>             unsigned<4>                     idx;
>             varbinary                       bin_vector;

>             userdef tree<idx, bin_vector>   binkey;
>             kdtree <idx, bin_vector>        binkey_kd;
>             patricia<bin_vector>            binkey_p;
>         };

> The C++ class Binary is used to access binary fields. The following code snippet demonstrates how binary data access might be implemented:

>         struct _bin_cls
>         {
>             int             idx;
>             Value       *bin;
>         }
>         ...
>         _bin_cls bin_cls;

>         //
>         // Put new data to a database
>         //
>         char buf_in[4096];
>         ...
>         // Assume buf_in contains valuable data
>         // Create Binary object and copy
>         Binary* b = Binary::create(engine.getAllocator(), sizeof(buf_in));
>         memcpy(b->body(), buf_in, sizeof(buf_in));
>         bin_cls.idx = 1;
>         bin_cls.bin = (Value *)b;
>         engine.executeStatement("insert into bin_cls %r", _bin_cls);
>         DELETE_OBJ(engine.getAllocator(), b);
>         ...

>         //
>         // Get query result
>         //
>         QueryResult result( engine.executeQuery( "select * from bin_cls" ) );
>         Cursor* cursor = result->records();
>         Record* rec = cursor->next();

>         McoSql::ValueRef eref(rec->get(1));
>         McoSql::Binary *bin = eref.as<McoSql::Binary>(); // will be deleted automatically by resultSet's allocator

>         size_t len = bin->length;
>         char buf_out[len];
>         memcpy(buf_out, bin->body(), len);

> Alternatively, placeholder `%v` can be used to substitute a `binary` or `varbinary` value into an SQL string:

>         //
>         // Put new data to a database
>         //
>         char buf_in[4096];

>         // Assume buf_in contains valuable data
>         // Create Binary object and copy
>         Binary* b = Binary::create(engine.getAllocator(), sizeof(buf_in));
>         memcpy(b->body(), buf_in, sizeof(buf_in));
>         engine.executeStatement("insert into bin_cls values (%i, %v)",  1, b);
>         DELETE_OBJ(engine.getAllocator(), b);

> From the perspective of SQL there are two data types: `binary(n)` that maps to storage type `binary<n>`, and `varbinary` that maps to storage type `varbinary`. (Both types are mapped internally to the `tpBinary` type from the perspective of the SQL C++ API class `Value`). Data for binary fields can be passed as a string in hex-format in SQL. For example:

>         create table t (idx int, bin binary(4));
>         insert into t values(1, '0A0D0A0D' );

> Also it is possible to create arrays and vectors of `binary` and `varbinary` via SQL. For example:

>         create table t (idx int, bin array(binary(4), 10)); // Array of binary
>         create table t (idx int, bin array(varbinary)); // Vector of varbinary

> ## Dynamic objects

> The `new` operator is now overloaded to use the static StdAllocator and all dynamic objects must pass the allocator as an argument to the `new` operator. Whereas in versions of eXtremeSQL previous to 6.5, dynamic objects were created with code like the following:

>         int val = 1;
>         ...
>         return new IntValue(val);

> Now dynamic objects must use the StdAllocator in code like the following:

>         Allocator * allocator = engine.getAllocator();
>         int val = 1;
>         ...
>         return new (allocator) IntValue(val);

> or alternatively use the static `create()` method in code like the following:

>         Allocator * allocator = engine.getAllocator();
>         int val = 1;
>         ...
>         return IntValue::create(allocator,val);

> (Note that in any API calls like `new` requiring the allocator, a pointer to the allocator can be obtained from the McoSqlEngine as in the example code above or from any of the Container subclasses such as Record, DataSource, Array, Blob. Any objects allocated with the McoSqlEngine allocator will not be implicitly deleted; i.e. they will remain in memory until the engine itself is destroyed unless explicitly destroyed (see below). However, if operating within a Query, the allocator can be obtained from the QueryResult object; and this allocator and all objects allocated with it will be destroyed when the query finishes.)

> To explicitly remove dynamic objects created with the `new` operator, use the macros `DELETE` or `DESTROY` (provided in the public header file `basedef.h`).

> # Using References

> In eXtremeSQL tables are often joined by using `references` to implement an SQL “foreign keys” relation between tables that provide fast and direct access to the record by `AUTOID`. Reference fields can also be indexed and used in the `ORDER BY` clause. References can be de-referenced using the same dot notation as used in accessing structure components.

> For example, the following database schema:

>         class Address
>         {
>             string city;
>             autoid[1000];
>             hash<city> city_index[1000];
>         };

>         class Company
>         {
>             autoid_t<Address> address;
>             autoid[10000];
>             hash<address> address_index[10000];
>         };

>         class Contract
>         {
>             autoid_t<Company> company;
>             autoid[10000];
>             hash<company> company_index[10000];
>         };

> when queried with the statement

>         select * from Contract where company.address.city = 'Chicago';

> will retrieve Contract records where the referenced Company references an Address with the `city` field equal to ‘Chicago’. The query execution plan for this request is the following:

> 1. Perform an index search of the Address table using `city_index (city = ‘Chicago’)`
> 2. For all selected Address records, locate Company records referencing these addresses using `address_index`
> 3. For all selected Company records, locate Contract records referencing these Company records using `company_index`

> References can be checked for `null` by the `is null` or `is not null` predicates. They can also be compared for equality with each other, as well as with the special null keyword. When a null reference is de-referenced, an exception is raised by eXtremeSQL.

> The following sample schema illustrates three tables that could be used to model a Customer-Orders application:

>         declare database referencesdb;
>         class Address
>         {
>             int4   zipcode;
>             string city;
>             string street;
>             hash<zipcode> iaddr[100000];
>             autoid[100000];
>             list;
>         };

>         class Company
>         {
>             autoid_t<Address> location;
>             string            name;
>             hash<name> iname[100000];
>             hash<location> iaddr[100000];
>             autoid[100000];
>             list;
>         };

>         class Orders
>         {
>             autoid_t<Company> company;
>             date              shipment;
>             uint8             amount;
>             string            description;

>             hash<company> icomp[100000];
>             list;
>         };

> Note that there is a one-many relationship between the Company and Orders tables, i.e. many instances of the class Order might have the same value for the `autoid` field “`company`”. This field `company` is a `reference` to exactly one Company object that results in optimal lookup time. Likewise the relationship between Company and Address tables is implemented in the reference field `location`. The following sample code snippets demonstrate how these reference fields are used in eXtremeSQL:

>         const char * db_name = "joindb";

>         // Define global SQL engine
>         using namespace McoSql;
>         McoSqlEngine engine;

>         // Define the structure correponding to database tables
>         struct _Address
>         {
>             int4        zipcode;
>             char const* city;
>             char const* street;
>         };

>         int main()
>         {
>             …
>             engine.open( db_name, referencesdb_get_dictionary(), DATABASE_SIZE, MEMORY_PAGE_SIZE);

>             // Insert records
>             insertRecords();

>             // Search Addresses by zip
>             searchAddresses();

>             // Show Company-Addresses
>             showJoin1();

>             // Show Company-Orders
>             showJoin2();

>             // Show Company-Orders where desc like '%1%'
>             showJoin3();

>             // Clean up
>             deleteRecords();
>             engine.close();

>             sample_pause_end("\n\nPress any key to continue . . . ");
>             return 0;
>         }

>         void showJoin1()
>         {
>             printf("\n\n\tSELECT C.name FROM Address A,Company C ...");
>             for (int i = 1; i <= nRecords; i++)
>             {
>                 QueryResult result( engine.executeQuery( "SELECT C.name FROM Address A,Company C "
>                         "WHERE A.zipcode=%i AND
>                         A.autoid=C.location", i ) );
>                 Cursor* iterator = result->records();
>                 assert(iterator->hasNext());
>                 Record* rec = iterator->next();
>                 String * pName = (String*)rec->get(0);
>                 printf( "\n\t\t%d) %s", i, pName->body() );
>             }
>         }

>         void showJoin2()
>         {
>             printf("\n\n\tSELECT C.name, O.description FROM Address A,Company C,Orders O ...");
>             for (int i = 1; i <= nRecords; i++)
>             {
>                 QueryResult result( engine.executeQuery( "SELECT C.name, O.description "
>                             "FROM Address A,Company C,Orders O "
>                             "WHERE A.zipcode=%i AND A.autoid=C.location"
>                             " AND C.autoid=O.company", i ) );
>                 Cursor* iterator = result->records();
>                 assert(iterator->hasNext());
>                 Record* rec = iterator->next();
>                 String * pName = (String*)rec->get(0);
>                 String * pDesc = (String*)rec->get(1);
>                 printf( "\n\t\t%d) %s: %s", i, pName->body(), pDesc->body() );
>             }
>         }

>         void showJoin3()
>         {
>             printf("\n\n\tSELECT C.name, O.description ... WHERE O.description like '%%1%%' ...");
>             for (int i = 1; i <= nRecords; i++)
>             {
>                 QueryResult result( engine.executeQuery( "SELECT C.name, O.description "
>                             "FROM Address A,Company C,Orders O "
>                             "WHERE O.description like '%1%' AND "
>                             "A.zipcode=%i AND A.autoid=C.location "
>                             "AND C.autoid=O.company", i ) );
>                 Cursor* iterator = result->records();
>                 while ( iterator->hasNext() )
>                 {
>                     Record* rec = iterator->next();
>                     String * pName = (String*)rec->get(0);
>                     String * pDesc = (String*)rec->get(1);
>                     printf( "\n\t\t%d) %s: %s", i, pName->body(), pDesc->body() );
>                 }
>             }
>         }

> Some programming points to note in the code snippets `showJoin1`, `showJoin2` and `showJoin3` above:

> - The McoSQL class String is used to access the `Company.Name` and `Order.Description` fields by calling method `get(n)`.
> - The `string` values are printed out by calling the `String` method “`body()`”.

> # Searching through a Vector or Array

> It is possible to search through a vector or array using the SQL `IN` operator, for example:

>         select * from T where ? in T.vector;

> The above statement works for vectors of `scalar` types, but for vectors of compound types an eXtremeDB specific expression must be used. For example, using the following schema definition:

>         struct E
>         {
>             uint4 q1;
>             uint4 q2;
>             vector<D> q3;
>         };

>         class R
>         {
>             uint4 r1;
>             E     r2;
>             tree <r2.q1> by_r2e1;
>         };

> a proper `select` statement would be like the following:

>         select * from R where exists i: (exists j: (r2.q3[i].d3[j].c1=1));

> However, note that such a query requires a sequential search and a quadratic iteration through all of the possible values of the indexes, and will consequently be quite slow (thus is inadvisable).