ToC path:

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 “showRecord” 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:
Using the Struct::get(int index) method to refer to the columns in the result row by index;
Using the QueryResult::Fields iterator to iterate over the columns in the result row;
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:

Perform an index search of the Address table using city_index (city = ‘Chicago’)

For all selected Address records, locate Company records referencing these addresses using address_index

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).