Workspace 6.21.5
Public Types | Public Member Functions | Static Public Member Functions | Protected Types | Protected Member Functions | List of all members
SqlDataModel Class Reference

Provides an interface to access data from an SQL database as bound objects. The created objects can then be manipulated by calling code and submitted back to the model to write back to the database. More...

#include <DataAnalysis/DataStructures/sqldatamodel.h>

Public Types

enum  RelationOptions { NoRelations , AllRelations , NominatedRelations }
 Specifies the type of relations to retrieve when performing a fetch or update operation. More...
 

Public Member Functions

 SqlDataModel (QSqlDatabase &database, bool strictTypeChecking=false)
 
virtual ~SqlDataModel ()
 
void clearCachedAdaptors ()
 
template<typename T >
SqlDataModelResult fetchAll (DataExecution::ObjectArray &objects, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList())
 
template<typename T >
SqlDataModelResult fetchAllMapped (DataCollection &objects, const QString &mapField, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList(), bool ignoreDuplicates=false)
 
template<typename T >
SqlDataModelResult fetchById (T &object, const typename T::IdType &id, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList())
 
template<typename T >
SqlDataModelResult fetchByQuery (DataExecution::ObjectArray &objects, const QString &filterQueryText, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList())
 
template<typename T >
SqlDataModelResult fetchByQueryMapped (DataCollection &objects, const QString &mapField, const QString &filterQueryText, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList(), bool ignoreDuplicates=false)
 
QSqlDatabase & getDatabase ()
 
const QSqlDatabase & getDatabase () const
 
template<typename T >
SqlDataModelResult save (DataExecution::ObjectArray &array, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList())
 
template<typename T >
SqlDataModelResult save (T &object, RelationOptions relOpts=NoRelations, const QStringList &relations=QStringList())
 
SqlDataModelResult validateAllRegisteredObjectTypes (const Application::WorkspacePlugin *plugin=nullptr) const
 

Static Public Member Functions

template<typename T >
static QString getBoundTableName ()
 

Protected Types

typedef QHash< SqlDataModelPrivate::PrimaryKeyValue, SqlBoundObjectGroup * > ObjectCache
 

Protected Member Functions

template<typename T >
T * adaptToBaseClassType (DataExecution::DataObject &obj) const
 
template<typename T >
SqlDataModelResult addOrUpdateObject (DataExecution::ObjectArray &container, const QSqlRecord &record, QSqlQuery &query, const QStringList &relations=QStringList())
 
template<typename T >
QSqlQuery getInsertQuery (const T &obj, const QStringList &relations, const QString &foreignKeyName=QString(), const QVariant &foreignKeyValue=QVariant()) const
 
template<typename T >
ObjectCachegetObjectCache ()
 
template<typename T >
bool getPrimaryKeyValue (const QSqlQuery &query, DataExecution::TypedObject< typename T::IdType > &pkValue) const
 
template<typename T >
QSqlQuery getSelectAllQuery (const T &obj, const QStringList &relations, const QString &customWhereClause=QString()) const
 
template<typename T >
QSqlQuery getSelectByIdQuery (const T &obj, typename T::IdType id, const QStringList &relations) const
 
template<typename T >
QSqlQuery getUpdateQuery (const T &obj, const QStringList &relations, const QString &foreignKeyName=QString(), const QVariant &foreignKeyValue=QVariant()) const
 

Detailed Description

The SqlDataModel wraps a QSqlDatabase in order to provide a type-safe interface for interacting with database records. Users invoke the variations of the fetch() and save() methods in order to retreive and update data in the database respectively.

Objects returned are all of user-defined classes that derive from SqlBoundObjectGroup.

Member Typedef Documentation

◆ ObjectCache

Member Enumeration Documentation

◆ RelationOptions

Enumerator
NoRelations 
AllRelations 
NominatedRelations 

Constructor & Destructor Documentation

◆ SqlDataModel()

SqlDataModel ( QSqlDatabase &  database,
bool  strictTypeChecking = false 
)
Parameters
databaseThe database that this model is bound to.
strictTypeCheckingWhether or not to strictly check data types to see whether they directly match the underlying data types in the database. For example, if strict typing is enabled, a TypedObject<int> can be bound only to INT types in the database: SHORT, LONG etc will fail. If strict type checking is disabled (i.e. set to false), then type checking is still performed, just not as strictly, for example, SHORT, LONG and INT can all be bound to TypedObject<int>, but TypedObject<QString> would fail.

Constructs an SqlDataModel bound to the specific database. All objects created using this model will source data from this database, and any objects saved back will be written to this database.

If required, strictTypeChecking can be enabled to ensure that the data model exactly matches the underlying data types. This is false by default, meaning that fuzzy matching for similar data types (e.g. INT, SHORT and LONG -> Integer) is accepted.

◆ ~SqlDataModel()

~SqlDataModel ( )
virtual

Member Function Documentation

◆ adaptToBaseClassType()

T * adaptToBaseClassType ( DataExecution::DataObject obj) const
protected
Template Parameters
TThe base class type to adapt to.
Parameters
objThe data object to adapt to a specific class type.
Returns
A pointer of type T, referring to the underlying data referenced by obj.

Convenience function for adapting a data object to a specific base class type. Most frequently, this is used to convert known SqlBoundObjectGroups and ObjectArrays to their specific object types.

◆ addOrUpdateObject()

SqlDataModelResult addOrUpdateObject ( DataExecution::ObjectArray container,
const QSqlRecord &  record,
QSqlQuery &  query,
const QStringList relations = QStringList() 
)
protected
Template Parameters
TThe type of object to add or update.
Parameters
containerThe container in to which items will be added, or existing items will be updated depending on query.
recordFor efficiency reasons, this method accepts a reference to a record object containing the names of the columns that the query refers to.
queryThe query object containing the results of a SELECT query.
relationsA list of relations to recursively follow and populate.
Returns
A result object containing the result code and (if applicable) any error codes and error text.

Adds new bound objects to container, or updates objects already inside container, based on the results contained within query, which must be a SELECT query. If specified, this method will follow the list of relations specified in relations, recursively adding child objects to any objects created during this method call.

◆ clearCachedAdaptors()

void clearCachedAdaptors ( )

◆ fetchAll()

SqlDataModelResult fetchAll ( DataExecution::ObjectArray objects,
SqlDataModel::RelationOptions  relOpts = NoRelations,
const QStringList relations = QStringList() 
)
Template Parameters
TThe type of object to retrieve.
Parameters
objectsObjects fetched will be added to this object array. The object array must be empty when passed to the function or an error result will be returned.
relOpts(Optional) If provided, this argument tells the function to retrieve data from related tables in order to populate related data objects.
relations(Optional) If the relOpts argument is set to NominatedRelations, this argument should contain the list of relation names which are to be populated.
Returns
A result object indicating the success or failure reason of the operation.

Fetches all objects of the template type from model, optionally populating related bound objects depending on the relOpts and relations parameters.

◆ fetchAllMapped()

SqlDataModelResult fetchAllMapped ( DataCollection objects,
const QString &  mapField,
SqlDataModel::RelationOptions  relOpts = NoRelations,
const QStringList relations = QStringList(),
bool  ignoreDuplicates = false 
)
Template Parameters
TThe type of object to retrieve.
Parameters
objectsObjects fetched will be added to this object array. The object array must be empty when passed to the function or an error result will be returned.
mapFieldName of a field in the object to use as the data collection key.
relOpts(Optional) If provided, this argument tells the function to retrieve data from related tables in order to populate related data objects.
relations(Optional) If the relOpts argument is set to NominatedRelations, this argument should contain the list of relation names which are to be populated.
ignoreDuplicatesIf true, duplicate objects will be ignored.
Returns
A result object indicating the success or failure reason of the operation.

Fetches all objects of the template type from model, optionally populating related bound objects depending on the relOpts and relations parameters. Rather than populating a flat array, this method populates a DataCollection, using the values from the specified field as the keys in the data collection.

Note
The method assumes that each record has a unique value for mapField, but will report duplicates to the log.

◆ fetchById()

SqlDataModelResult fetchById ( T &  object,
const typename T::IdType &  id,
SqlDataModel::RelationOptions  relOpts = NoRelations,
const QStringList nominatedRelations = QStringList() 
)
Template Parameters
TThe type of object to retrieve.
Parameters
objectObject to populate with the fetched data.
idThe ID of the object to retrieve from the model.
relOpts(Optional) If provided, this argument tells the function to retrieve data from related tables in order to populate related data objects.
nominatedRelations(Optional) If the relOpts argument is set to NominatedRelations, this argument should contain the list of relation names which are to be populated.
Returns
A result object indicating the success or failure reason of the operation.

Fetches an object of the template type with the specified ID from model, optionally populating related bound objects depending on the relOpts and relations parameters.

◆ fetchByQuery()

SqlDataModelResult fetchByQuery ( DataExecution::ObjectArray objects,
const QString &  filterQueryText,
SqlDataModel::RelationOptions  relOpts = NoRelations,
const QStringList relations = QStringList() 
)
Template Parameters
TThe type of object to retrieve.
Parameters
objectsObjects fetched will be added to this object array. The object array must be empty when passed to the function or an error result will be returned.
filterQueryTextFilter query (WHERE clause of an SQL statement) which will be prepended with the standard select query. Depending on the results of the implementation of getSelectAllQuery() for the provided object type, an invalid query may be generated.
relOpts(Optional) If provided, this argument tells the function to retrieve data from related tables in order to populate related data objects.
relations(Optional) If the relOpts argument is set to NominatedRelations, this argument should contain the list of relation names which are to be populated.
Returns
A result object indicating the success or failure reason of the operation.

Fetches all objects of the template type from model, optionally populating related bound objects depending on the relOpts and relations parameters.

◆ fetchByQueryMapped()

SqlDataModelResult fetchByQueryMapped ( DataCollection objects,
const QString &  mapField,
const QString &  filterQueryText,
SqlDataModel::RelationOptions  relOpts = NoRelations,
const QStringList relations = QStringList(),
bool  ignoreDuplicates = false 
)
Template Parameters
TThe type of object to retrieve.
Parameters
objectsObjects fetched will be added to this object array. The object array must be empty when passed to the function or an error result will be returned.
mapFieldName of a field in the object to use as the data collection key.
filterQueryTextFilter query (WHERE clause of an SQL statement) which will be prepended with the standard select query. Depending on the results of the implementation of getSelectAllQuery() for the provided object type, an invalid query may be generated.
relOpts(Optional) If provided, this argument tells the function to retrieve data from related tables in order to populate related data objects.
relations(Optional) If the relOpts argument is set to NominatedRelations, this argument should contain the list of relation names which are to be populated.
ignoreDuplicatesWhether or not to ignore or fail when duplicate keys are encountered during the mapping process.
Returns
A result object indicating the success or failure reason of the operation.

Fetches all objects of the template type from model, optionally populating related bound objects depending on the relOpts and relations parameters. Rather than populating a flat array, this method populates a DataCollection, using the values from the specified field as the keys in the data collection.

Note
The method assumes that each record has a unique value for mapField, but will report duplicates to the log.

◆ getBoundTableName()

QString getBoundTableName
static
Template Parameters
TThe type of object for which we are searching for the bound table name. Is assumed to be a type of SqlBoundObjectGroup.
Returns
The table name that the specified object type is bound to.

Classes derived from SqlBoundObjectGroup must specialise this function such that it returns the name of the table or view to which the object is bound. If the object contains data from multiple tables, it must be bound to one primary table, and use relations to specify the others, or must bind to a view that pulls in data from the required tabales.

◆ getDatabase() [1/2]

QSqlDatabase & getDatabase ( )
Returns
The database that this data model is bound to.

◆ getDatabase() [2/2]

const QSqlDatabase & getDatabase ( ) const
Returns
The database that this data model is bound to.

◆ getInsertQuery()

QSqlQuery getInsertQuery ( const T &  obj,
const QStringList relations,
const QString &  foreignKeyName = QString(),
const QVariant &  foreignKeyValue = QVariant() 
) const
protected
Template Parameters
TThe type of object for which we are retrieving its insert query.
Parameters
objThe object we want to insert.
relationsAny relationships to follow when generating the query. Relation names must match those defined in the object.
foreignKeyName(optional) If needed, a foreign key column to write into. This is only usually called by internal code specifying the column to write into in the case of bi-directional relationships.
foreignKeyValue(optional) The value to write into the foreign key column if used.
Returns
The SQL insert query that inserts a single object's values into the model.

Returns the SQL insert query that can be used to populate a single object of type T. Developers can specialise this function for classes derived from SqlBoundDataObject, allowing them to implement optimised SQL querys or call stored procedures.

◆ getObjectCache()

SqlDataModel::ObjectCache & getObjectCache
protected
Template Parameters
TThe type of object for which we are retrieving the object cache.
Returns
Returns the object cache associated with the object type T. Each class deriving from SqlBoundObjectGroup is allocated its own cache, ensuring that objects are only created once during the processing of a query.

When processing a fetch() operation, we store references to each object we create so that when another reference to that object is identified, we can simply copy the reference. This means our generated object tree correctly refers to shared objects, rather than containing duplicates of them.

See also
clearObjectCaches

◆ getPrimaryKeyValue()

bool getPrimaryKeyValue ( const QSqlQuery &  query,
DataExecution::TypedObject< typename T::IdType > &  pkValue 
) const
protected
Template Parameters
TThe type of object for which we are retrieving its primary key value.
Parameters
queryThe SQL query object which contains our current record.
pkValue(out) A data object of the type matching the primary key type of objects of type T.
Returns
true if the primary key value was able to be retrieved successfully, false otherwise.

Retrieves the primary key value from the current record in the specified query. It will use the template parameter T in order to deduce the correct type for the PK.

◆ getSelectAllQuery()

QSqlQuery getSelectAllQuery ( const T &  obj,
const QStringList relations,
const QString &  customWhereClause = QString() 
) const
protected
Template Parameters
TThe type of object for which we are retrieving its selectAll query.
Parameters
objThe object for which we are obtaining its select all query.
relationsAny relations to follow when generating the query. Relation names must match those defined in the object.
customWhereClause(optional) A WHERE clause to append to the SQL to filter the results..
Returns
The SQL Select query that returns all values from the model.

Returns the SQL select all query that can be used to populate a set of objects of type T. Developers can specialise this function for classes derived from SqlBoundDataObject, allowing them to implement optimised SQL querys or call stored procedures or user-defined functions.

◆ getSelectByIdQuery()

QSqlQuery getSelectByIdQuery ( const T &  obj,
typename T::IdType  id,
const QStringList relations 
) const
protected
Template Parameters
TThe type of object for which we are retrieving its selectById query
Parameters
objThe object for which we are obtaining its select by ID query.
idThe id of the object to select.
relationsAny relationships to follow when generating the query. Relation names must match those defined in the object.
Returns
The SQL Select query that returns a single object's values from the model.

Returns the SQL select by ID query that can be used to populate a single object of type T. Developers can specialise this function for classes derived from SqlBoundDataObject, allowing them to implement optimised SQL querys or call stored procedures.

◆ getUpdateQuery()

QSqlQuery getUpdateQuery ( const T &  obj,
const QStringList relations,
const QString &  foreignKeyName = QString(),
const QVariant &  foreignKeyValue = QVariant() 
) const
protected
Template Parameters
TThe type of object for which we are retrieving its update query.
Parameters
objThe object we want to insert.
relationsAny relationships to follow when generating the query. Relation names must match those defined in the object.
foreignKeyName(optional) If needed, a foreign key column to write into. This is only usually called by internal code specifying the column to write into in the case of bi-directional relationships.
foreignKeyValue(optional) The value to write into the foreign key column if used.
Returns
The SQL insert query that inserts a single object's values into the model.

Returns the SQL insert query that can be used to populate a single object of type T. Developers can specialise this function for classes derived from SqlBoundDataObject, allowing them to implement optimised SQL querys or call stored procedures.

◆ save() [1/2]

SqlDataModelResult save ( DataExecution::ObjectArray array,
RelationOptions  relOpts = NoRelations,
const QStringList nominatedRelations = QStringList() 
)
Template Parameters
TThe type of object to save.
Parameters
arrayThe array of objects to save.
relOptsWhich (if any) relations to follow when performing the save.
nominatedRelationsIf NominatedRelations was selected for relOpts, a list of named relations to follow.
Returns
an object containing the results of the save operation.

Saves a array of objects to the underlying database, attempting to insert them if they are not already persistent, and updating them if they are (SqlBoundObjectGroup::isPersistent()).

Note
Attempts to save an object that is not currently persistent, but for which its primary key already exists in the database, will fail. To avoid such cases, ensure that the object is first retrieved through use of a 'fetch' call before it is modified and saved.

◆ save() [2/2]

SqlDataModelResult save ( T &  object,
RelationOptions  relOpts = NoRelations,
const QStringList nominatedRelations = QStringList() 
)
Template Parameters
TThe type of object to save.
Parameters
objectThe object to save.
relOptsWhich (if any) relations to follow when performing the save.
nominatedRelationsIf NominatedRelations was selected for relOpts, a list of named relations to follow.
Returns
an object containing the results of the save operation.

Saves an object to the underlying database, attempting to insert it if it is not already persistent, and updating it if it is already persistent (SqlBoundObjectGroup::isPersistent()).

Note
Attempts to save an object that is not currently persistent, but for which its primary key already exists in the database, will fail. To avoid such cases, ensure that the object is first retrieved through use of a 'fetch' call before it is modified and saved.

◆ validateAllRegisteredObjectTypes()

SqlDataModelResult validateAllRegisteredObjectTypes ( const Application::WorkspacePlugin plugin = nullptr) const
Parameters
pluginThe name of the plugin containing object types we wish to validate. If left as its default (nullptr), all registered object types in all registered plugins will be validated.
Returns
An SqlDataModelResult indicating whether or not all registered objects are valid.

Validates all registered data objects that derive from SqlBoundObjectGroup (i.e. have the appropriately registered DerivedToBaseAdaptor). If the objects map correctly to the columns of the tables in the underlying database, this will return a result with the error type SqlDataModelResult::NoError, otherwise an error of type SqlDataModelResult::InvalidBindingError will be returned.