In this article we provide description of high level abstraction features that help dealing with data stored in databases. These features includes migrations, queries and query builders, models and validation, active record.
Nitisa database abstraction over ODBC provides a great simplification when working with databases, but it requires to write queries directly and provides data in form of arrays. In applications data is usually represented in form of classes, and it is more convenient to work with databases using high level query builder than writing database dependent queries. This next level of abstraction is provided by the Nitisa features described below.
try...catch blocks to handle errors. The exceptions are thrown only if something is definitely wrong meaning there is an error in code/logic. If you are sure everything is Okay you may omit try...catch blocks but we strongly recommend to not because even if everything is Okay with code, something may go wrong with database engine, ODBC diver, database data, etc. and you need at least show a meaningful message to a user instead of just application crashing. The exceptions that may be thrown are the ones named DbException*. In the framework a Query Builder is an object that provides translation from high level query (will be described later) to the plain query that can be fed directly to database engine. Query builder is described by IDbQueryBuilder interface which can be implemented in different classes for different database engines. For example, CDbQueryBuilderMySql implements query builder working with MySQL database engine. The particular implementation of a query builder is required for the most objects involved in high level abstraction over database data.
Query builders used in application can be registered and retrieved with IDb methods RegisterQueryBuilder(), UnregisterQueryBuilder(), UnregisterQueryBuilders(), FindQueryBuilder(). IDb instance is always available as Application->Db. Using those methods is not mandatory. Any way providing access to the needed query builder is Okay (for example, global variable or IFeature instance can be used).
Usually application works with a single database engine (for example, MySQL, MS SQL, MongoDB) so the only one instance of query builder is required but having multiple instances for the same database engine is also Okay (though it will take more resources).
When an application that uses database first starts it usually need to create a proper database structure to store data there. When an application evolves and new versions are being released, database structure usually also evolves and need changes. Sometimes not only the database structure but data itself need changes. Migrations provide an easy and very convenient way to deal with all that tasks.
To create a migration in Nitisa you need to declare a class derived from CDbMigration. Its constructors require a name for the migration. In order to work properly it needs to be unique. Why that is required will be clear a little bit further. All the work on changing database structure and data should be implemented in your class in overrided Run() method. If you wish to provide ability to revert changes made by your migration, you need to implement that reverting changes in the overrided Revert() method. By default, that method will just throw DbExceptionRevertNotImplemented exception if attempted to revert a migration.
Applying and reverting a migration are done by calling IDb methods. The RunMigration() applies only one migration. The RunMigrations() applies a batch of migrations. And finally the RevertMigration() reverts specified migration. All this methods store information in a table name specified as migration_table argument. Those methods create that table with needed structure and use it to know which migrations are already applied. If migration with the same name already in that table, it won't be applied again. That is why migrations need unique names. The name, for instance, can include timestamp to make it unique. Also, migration table name should be the same each time you apply or revert migrations. Only one migration table should exist for correct migration tracking.
Applying and reverting migrations may throw exceptions, so calls to IDb methods needs to be enclosed with try..catch.
In the Run()/Revert() methods of the migration class you may use numerous methods provided by the CDbMigration to change a database. Below is an example of how it can look like.
void CMyMigration::Run() override
{
// Create table "author"
CreateTable(
L"author", // Table name
MigrationColumns{ // Table columns
Column(L"id")->Integer()->NotNull()->PrimaryKey()->AutoIncrement(), // Column "id"
Column(L"name")->VarChar(64)->NotNull() // Column "name"
},
MigrationKeys{ } // Table has no keys
);
// Create table "post"
CreateTable(
L"post", // Table name
MigrationColumns{ // Table columns
Column(L"id")->Integer()->NotNull()->PrimaryKey()->AutoIncrement(), // Column "id"
Column(L"title")->VarChar(64)->NotNull(), // Column "title"
Column(L"author_id")->Integer()->NotNull() // Column "author_id"
},
MigrationKeys{ // Table keys
ForeignKey(L"author_id", L"author", L"id", L"fk_author_id", IDbMigrationKey::RefOption::Cascade, IDbMigrationKey::RefOption::Restrict) // Foreign key to "author" table
}
);
}The methods of the IDb dealing with migrations requires IDbConnection or IDbStatement and IDbQueryBuilder instances. You need to create them before using any features dealing with databases described in this article. You can find how to create connections and statements in the Database article.
There are a few helper classes that simplify working with data stored in database. They are used when representation of the data in form of class instances is not required. For example, they can be used to change a bunch of records in a table depending on certain criteria or to find out minimum/maximum value of the column.
The CDbInsert class is used to add new records to a table. The following code example shows how it can be used to insert a new user into users table.
CDbInsert insert{ nullptr, statement, query_builder };
int64 id = insert.Into(L"user")->Execute(DbSet{ { L"name", L"John Dow" }, { L"age", 25 } })->Id();
Here statement and query_builder are assumed to be instances of a statement and query builder created somewhere earlier. With the Into() method we specify the table where the data should be inserted, the Execute() methods do the job (insert data into a table), and the Id() method returns the last inserted value of the auto incrementing column if there is one.
To update data the CDbUpdate class is used. It has more method which allow to specify conditions at which records will be updated. The following code example shows how it can be used.
CDbUpdate update{ nullptr, statement, query_builder };
update.Table(L"user")->Where(db::eqcv(L"name", L"John Dow"))->Limit(2)->Execute(DbSet{ { L"name", L"John Doe" } });The example shows how to fix name spelled incorrectly in the previous example where we inserted new user record. The condition building methods use condition in a special form and there are a lot of utility functions facilitating building condition in needed form. We will discuss them later.
The CDbQuery helper class allows to query data from database. It provides a lot of methods for customizing request and retrieve different kind of data. It also allows to combine many queries. The example of using the class is below.
CDbQuery query{ L"", nullptr, statement, query_builder };
float age = query.From(L"user")->Where(db::lkc(L"name", L"%John%"))->Average<float>(L"age");The example demonstrates how to get an average age of users whose name contains John part.
The constructor of the class has one extra argument called table. It is used only when From is not specified.
Final class in this group is the CDbDelete one. It is being used to delete data from a table. Here is an example demonstrating its usage.
CDbDelete del{ nullptr, statement, query_builder };
del.From(L"user")->Where(db::lkc(L"name", L"%John%"))->OrderBy(L"id", DbOrder::Desc)->Limit(1)->Execute();The example demonstrate how to delete only the last added user with name containing John (assuming id is an auto incrementing column in users table).
Some helper classes described in the previous section allow to specify conditions or filters. They are specified in form of DbCondition or array (vector) of that type. The Active Record class we will describe shortly is also using it. The DbCondition cannot be created directly but instead there are a lot of utility functions to build different condition kinds. All those functions are located in nitisa::db namespace. There are long and short versions of those functions. The long are db::Expression(), db::Equals(), db::NotEquals(), db::Greater(), db::GreaterEquals(), db::Less(), db::LessEquals(), db::And(), db::Or(), db::Not(), db::Between(), db::NotBetween(), db::In(), db::NotIn(), db::Like(), db::NotLike(), db::Exists(), db::NotExists(). It is obvious from the function names what kind of condition they help to build. Most of the functions are overloaded to support different kind of arguments in condition. There are 3 possible argument type: String argument type means the argument is a column name, DbValue argument type means the argument is just a value (like number or string), and DbCondition argument type means the argument is a custom expression (usually created with db::Expression() function). With these overloaded functions there is a small problem: both String and DbValue can be silently created from a string. For example, auto condition = db::Equals(L"name", L"John"); code seems fine but the result may be unexpected. For us, it is obvious that the first argument is a column name and the second one is a string value. But it is not obvious for the compiler. The correct way to use these overloaded functions will be to specify argument type directly: auto condition = db::Equals(String(L"name"), DbValue(L"John"));. This means you need to be careful and write more code. It also means that it is quite prone to errors.
To avoid the problems with long versions of condition building functions there are also a lot of short versions. They do the same, but they are shorter to write and have no overloaded versions. These short functions are also located in the nitisa::db namespace. They are all in lowercase. Function names are building as following: the name of the functions that correspond to the long versions with no overloads are db::a(), db::o(), db::n(), and db::expr(); the other function names start with 2 characters identifying what function does and 1 to 3 characters corresponding to possible arguments; the first 2 characters are eq for = condition, ne for != condition, gt for > condition, , ge for >= condition, ls for < condition, le for <= condition, bt for BETWEEN condition, nb for NOT BETWEEN condition, in for IN condition, ni for NOT IN condition, lk for LIKE condition, nl for NOT LIKE condition, ex for EXISTS condition, and ne for NOT EXISTS condition; the remaining 1 to 3 characters stand c for column, v for value and e for expression. For example, the previous code can be written using short function version as auto condition = db::eqcv(L"name", L"John");. Here eq means condition is =, c means the first argument is column, and v mans the second argument is a value. These set of short functions removes ambiguity as the argument types are indicated in a function name. We recommend to use them instead of long versions.
If you need to use transactions you may use a small helper class CDbTransaction. It starts transaction in a constructor and has methods Commit() to apply changes and RollBack() to revert changes. If you don't call any of these two methods, the RollBack() will be executed in destructor. Here is an example of using the class.
CDbTransaction transaction{ nullptr, statement, query_builder };
// Manipulate data
// ...
if (something_is_failed)
return;
transaction.Commit();Active Record pattern in the Nitisa is represented by TDbActiveRecord template class. This class allows to represent data stored in database tables in form of classes and work with them in true Object-Oriented way. Suppose we have a table for users having 3 columns: id - primary key, name - user name, and email - user email. With the template class just mentioned we can declare our own class having 3 properties: id, name, and email. Let's call that class an active record model. This will allow us to work with database data in more convenient way.
Let's see how to define active record models first. This may seem a little bit tricky. We need to declare a class derived from TDbActiveRecord template where template argument is our class name.
class CUser :public TDbActiveRecord<CUser>
{
// ...
};
Each active record model should provide connection or statement and query builder instances. It's the same connection/statement and query builder we described earlier. For that there is three abstract methods to be implemented: getConnection(), getStatement() and getQueryBuilder(). Usually all active record models uses the same connection/statement and query builder so the simple way is to create a parent class derived from IDbActiveRecord interface, implement its three mentioned earlier methods and have this class to be a parent of all models. For example:
class CActiveRecord :public virtual IDbActiveRecord
{
private:
static IDbConnection *m_pConnection;
static IDbStatement *m_pStatement;
static IDbQueryBuilder *m_pQueryBuilder;
public:
IDbConnection *getConnection() override
{
return m_pConnection;
}
IDbStatement *getStatement() override
{
return m_pStatement;
}
IDbQueryBuilder *getQueryBuilder() override
{
return m_pQueryBuilder;
}
};Here we suppose the private static members are initialized with correct instances somewhere right after creation of connection, statement, and query builder.
Still having separate implementation of the methods in some models may be very helpful if models describe data in different databases (which can also be of different database engines).
Having that common parent class implemented all is needed to finish creating a model is to add properties connected to table fields (columns), relations to another models if there are any, and write a constructor. Let's see how it can be done.
class CUser :public TDbActiveRecord<CUser>, public virtual CActiveRecord
{
public:
TDbAttribute<int> Id;
TDbAttribute<String> Name;
TDbAttribute<String> Email;
CUser() :
TDbActiveRecord(L"user"),
Id{ this, L"id" },
Name{ this, L"name" },
Email{ this, L"email" }
{
}
};
It's quite simple, isn't it? To add properties that a connected to columns in a table the TDbAttribute template is used. That template actually does not provide any database specific features. It simply provides a way to use named properties which changes can be tracked and which can have special value interpreted as no value assigned. We call such properties attributes. Actual connection between attributes and table columns provided by active record implementation. An attribute has a constructor that requires to specify a model the attribute belongs to and the attribute name. In order an attribute to be correctly connected with a column in a table the name should be the same as a column name. That is what is done in the model constructor above together with providing a table name (L"user") in base class constructor.
Not any types can be used with TDbAttribute template. Basically it supports numbers, strings, and a few more types. Actually it supports the same types that DbValue supports. Usually more types are needed. One very common example is enumerations. To add more types to be supported by TDbAttribute template all you need is to use partial template specialization technique. Here is an example showing how to add support of Gender attribute.
namespace nitisa
{
namespace l3
{
enum class Gender
{
Male,
Female
};
Gender StringToGender(const String &value)
{
if (value == L"male")
return Gender::Male;
if (value == L"female")
return Gender::Female;
return Gender::Male;
}
String GenderToString(const Gender value)
{
switch (value)
{
case Gender::Male: return L"male";
case Gender::Female: return L"female";
default: return L"";
}
}
}
template<>
class TDbAttribute<l3::Gender> : public virtual IDbAttribute
{
private:
struct OldValue
{
bool Null;
l3::Gender Value;
};
private:
l3::Gender m_tValue;
OldValue m_sOldValue;
protected:
void SetInternal(const DbValue& value, const bool& changed) override
{
if (changed)
{
if (!m_bChanged)
{
m_sOldValue.Null = m_bNull;
m_sOldValue.Value = m_tValue;
m_bChanged = true;
}
}
else
{
m_bNull = value.Type == DbValueType::Null;
m_bChanged = false;
}
m_tValue = l3::StringToGender((String)value);
}
public:
TDbAttribute(CDbModel* model, const String& name) :
IDbAttribute(name)
{
model->Attributes.Add(this);
}
TDbAttribute(CDbModel* model, const String& name, const l3::Gender& value) :
IDbAttribute(name)
{
model->Attributes.Add(this);
m_tValue = value;
m_bNull = false;
}
TDbAttribute(CDbModel* model, const wchar_t* name) :
IDbAttribute(name)
{
model->Attributes.Add(this);
}
TDbAttribute(CDbModel* model, const wchar_t* name, const l3::Gender& value) :
IDbAttribute(name)
{
model->Attributes.Add(this);
m_tValue = value;
m_bNull = false;
}
template<class T> TDbAttribute(T) = delete;
TDbAttribute& operator=(const l3::Gender& value)
{
if (!m_bChanged)
{
m_sOldValue.Null = m_bNull;
m_sOldValue.Value = m_tValue;
m_bChanged = true;
}
m_tValue = value;
m_bNull = false;
return *this;
}
TDbAttribute& operator=(void*)
{
if (!m_bChanged)
{
m_sOldValue.Null = m_bNull;
m_sOldValue.Value = m_tValue;
m_bChanged = true;
}
m_bNull = true;
return *this;
}
template<class T> TDbAttribute& operator=(T type) = delete;
operator l3::Gender()
{
return m_tValue;
}
void Set(const DbValue& value) override
{
SetInternal(value, true);
}
DbValue Get() const override
{
if (m_bNull)
return DbValue{ };
return DbValue{ l3::GenderToString(m_tValue) };
}
DbValue Old() const override
{
if (!m_bChanged)
return Get();
if (m_sOldValue.Null)
return DbValue{ };
return DbValue{ l3::GenderToString(m_sOldValue.Value) };
}
};
}
All that is done here besides declaring Gender enumeration and couple function to convert it to string and back is just a copy of TDbAttribute template class file with few changes related to the enumeration. After this adding an attribute will be very easy: TDbAttribute<l3::Gender> Gender;.
Reading and writing attribute values requires to specify attribute type like this
if ((int)user->Id == 3)
user->Name = String(L"Robert");You can also mark attribute as having no value. This corresponds to a Null value in a database.
user->Name.SetNull(); // Mark as NULL
if (user->Name.bNull) // Check whether attribute is marked as NULL
// do something
If attribute was changed after creation or loading from database the bChanged property will be set to true.
if (user->Name.bChanged)
// do something
Adding relations to a model is as simple as adding attributes. Suppose that we have posts user writes and posts are stored in post table which have fields: id - primary key, user_id - user who wrote the post, text - post text. The CPost model will be declared something like this:
class CPost: public TDbActiveRecord<CPost>, public virtual CActiveRecord
{
public:
TDbAttribute<int> Id;
TDbAttribute<int> UserId;
TDbAttribute<String> Text;
CPost() :
TDbAttribute(L"post"),
Id{ this, L"id" },
UserId{ this, L"user_id" },
Text{ this, L"text" }
{
}
};Here we can specify one-to-one relation between post and user who wrote it and one-to-many relation between user and his posts. In order to specify relation the TDbRelation template class is used. The template class has two constructors. The first one is used to declare one-to-one and one-to-many relations and the second one is for many-to-many relations which use intermediate table. So, declaration of one-to-one relation between post and its author will look like this:
class CPost: public TDbActiveRecord<CPost>, public virtual CActiveRecord
{
public:
TDbAttribute<int> Id;
TDbAttribute<int> UserId;
TDbAttribute<String> Text;
TDbRelation<CUser> User; // Relation to user model
CPost() :
TDbAttribute(L"post"),
Id{ this, L"id" },
UserId{ this, L"user_id" },
Text{ this, L"text" },
User{ this, L"user", L"user_id", L"id", false } // Relation constructor
{
}
};
As you can see adding a relation is very similar to adding an attribute. The only difference is relation has more arguments in constructor. The first argument is the model the relation belongs to. It's always this in both relations and attributes. The second argument is relation name. Unlike with attribute the relation name is just a name and has no special meaning for models. The third and fourth arguments are used to specify relation. They first one (user_id) is a column name (and attribute name) of this model (CPost) where user id is stored. The second one (id) is the column and attribute name of the related model/table to which that value corresponds. For example, if a post was written by user with id = 2, the value 2 will be stored in post's UserId attribute. That attribute has name (and connected to column) user_id. So, we should look a user with id = 2. That is how those two names work. The fifth argument of the constructor is just a boolean value indicating whether the relation is one-to-one or one-to-many type.
The relation between user and his posts is configured in the same way.
class CUser :public TDbActiveRecord<CUser>, public virtual CActiveRecord
{
public:
TDbAttribute<int> Id;
TDbAttribute<String> Name;
TDbAttribute<String> Email;
TDbRelation<CPost> Posts; // Relation to user posts
CUser() :
TDbActiveRecord(L"user"),
Id{ this, L"id" },
Name{ this, L"name" },
Email{ this, L"email" },
Posts{ this, L"posts", L"id", L"user_id", true } // Relation constructor
{
}
};In this case the value we are looking for is store in the id column of the user table and we search it in the user_id column of the post table. Also by the last argument of the constructor we indicate that there can be many posts so the relation is of one-to-many type.
The final relation type that can be used if of many-to-many type. Suppose we have categories and posts. Posts may belong to multiple categories and categories can have many posts. That kind of relation is most always done using intermediary table. Let's say the table is called category_post and has two columns: category_id and post_id. The following code demonstrates the many-to-many relation setup for CCategory model representing category table.
class CCategory :public TDbActiveRecord<CCategory>, public virtual CActiveRecord
{
public:
TDbAttribute<int> Id;
TDbAttribute<String> Name;
TDbRelation<CPost> Posts; // Posts of the category
CCategory() :
TDbActiveRecord(L"category"),
Id{ this, L"id" },
Name{ this, L"name" },
Posts{ this, L"posts", L"id", L"id", L"category_post", L"category_id", L"post_id" } // Many-to-many relation constructor
{
}
};The first two arguments of the Posts relation constructor have the same meaning as before. The third argument (first id in the example above) is again the column/attribute where the category model stores its identifier. The fourth argument is the column/attribute of the post model where its identifier is stored. The fifth argument is the name of the intermediate table. The sixes and sevens arguments are the columns in that table that correspond to the category and post respectively.
Both relation constructors have also one more argument which can be used to specify ordering.
Relations are loaded first time when access to them is requested (lazy loading) and working with relations is very simple.
category->Posts.size(); // Loads posts and returns count of loaded postsfor (auto post : category->Posts) // Remove post text for all posts in a category
{
post->Text.SetNull();
post->Save();
}if (post->User->Email.bChanged) ...; // Check whether post user email was changed
Active record model provides a few very useful methods. You can load single model or multiple ones using static methods FindOne() and FindAll(). You can update multiple records with static methods UpdateAll(). You can also delete multiple records using static methods DeleteAll(). Here is an example.
auto user = CUser::FindOne(db::eqcv(L"email", L"test@gmail.com")); // Load first user model with E-mail equals to test@gmail.com
After changing attributes of loaded/created models you can use Save() method to save changes into database. The Delete() method deletes corresponding record from table. To determine whether model is just created or already stored in a table the isNewRecord() method can be used. To load default values from a table definition the LoadDefaultValues() is used. If model is changed somewhere outside the Refresh() method can be used to reload attribute values from a table.
Active record model has a few Before*() and After*() methods that are called before or after saving, inserting, updating, deleting, etc. Those methods by default are empty, and you may override them to add any code you need to be executed at those events. The Before*() methods must return bool value. If false is returned, further processing is stopped.
In order to have a lot more control over condition to load active record model(s) the static method Find() exists. Instead of model(s) it returns Active Query object. It works absolutely the same as CDbQuery but it's being used with active record models instead. So, after building a query you can use One() method to load a single model and All() method to load all found models. For example:
auto users = CUser::Find()->Where(db::lkc(L"email", L"%test%"))->All();The TDbActiveQuery can also be used in another way:
TDbActiveQuery<CUser> query;
auto users = query.Where(db::lkc(L"email", L"%test%"))->All();
Active record also provides a very powerful feature - validation. You can validate data stored in attributes against some rules that are specified in model constructor. To add those rules active record has Rule() template methods. Rules are defined as a validators. There are plenty of already implemented ones in the framework. They are classes called CDbValidator*. Validators can be used not only for validation but for transformation of the attribute data as well. For example, Trim validator does not validate anything. It simply removes leading and trailing spaces from the string stored in an attribute. Here is an example of defining a set of rules for Email attribute. The first rule is the trimming transformation. The second one checks if there is anything was provided (not an empty string). The third rule checks whether attribute value length is in range 5-300 and the fourth rule checks whether a value stored in Email attribute is a valid email address.
class CUser :public TDbActiveRecord<CUser>, public virtual CActiveRecord
{
public:
TDbAttribute<int> Id;
TDbAttribute<String> Name;
TDbAttribute<String> Email;
TDbRelation<CPost> Posts;
CUser() :
TDbActiveRecord(L"user"),
Id{ this, L"id" },
Name{ this, L"name" },
Email{ this, L"email" },
Posts{ this, L"posts", L"id", L"user_id", true }
{
Rule(&Email, CDbValidatorTrim::Create());
Rule(&Email, CDbValidatorRequired::Create())
->setCode(1)
->setMessage(L"E-mail is required");
Rule(&Email, CDbValidatorString::Create())
->setMin(5)
->setMax(300)
->setTooShort(L"E-mail length should be at least 5 characters")
->setTooLong(L"E-mail maximum length is 300 character");
Rule(&Email, CDbValidatorEmail::Create());
}
};
Most validators have additional parameters that allow validator customization. The Rule() in the example above returns instance of the created validator and all set* methods of a validator return validator instance, so they can be used in chain as shown above.
To run validation on a model attributes the Validate() method is used. If everything is Okay and all attribute values correspond to rules specified for them, the method returns true, otherwise it return false and hasErrors(), getErrors(), getError(), getFirstError() methods of a model can be used to get more information about errors. In Validate() method you may specify a list of attributes to be validated. If the list is empty, all attributes will be checked. By default, the Save() method first runs validation. If validation is not required, you need to specify false as the first argument when calling Save(). The second argument is a list of attributes to be validated, and it has meaning only when validation is on.
Validation is implemented on the base class CDbModel and active record is derived from that class. The CDbModel can be used as a base class for the models that require attributes and validation features but does not store any data in a database. For example:
class CMyModel :public CDbModel
{
public:
TDbAttribute<int> Age;
TDbAttribute<String> Name;
CMyModel() :
CDbModel(),
Age{ this, L"Age" },
Name{ this, L"Name" }
{
Rule(&Age, CDbValidatorNumber::Create())
->setMin(18)
->setMax(60);
Rule(&Name, CDbValidatorRange::Create())
->setRange({ L"Joe", L"Dan", L"Sam " });
}
};The features like Active Record provide a very convenient way of working with data stored in a database. On the other hand loading models, performing checking for uniqueness and converting data to class instances takes time. So, in projects where loading and saving data happens not very often the active record models are a very good choice. Working with active record model attributes does not add any additional time consuming operations because of template nature of attributes and very simple implementation that is completely inlined by compilers.
In projects where database operations are very often, like 50% of more, using active record models may be much slower than IDbStatement methods. Anyway, it greatly depends on the ways they are used, so we recommend to check both approaches and choose the one which better suits your application needs.