In the past weeks, I worked a lot on refactoring the way we work with the database.
One goal was to get away from one global database connection object, and use many database connections instead. For some operations, a database transaction must be passed instead of using the global database connection.
This will make it easier to have multi tenancy, and host more instances on one server.
Unfortunately, this meant a lot of files in the code needed to be modified. The Unit tests proved to be very useful, because they helped to know if the patient is still alive after such a big operation...
We support 3 databases: MySQL, PostgreSQL and SQLite. I had some thoughts of dropping support for PostgreSQL and/or SQLite. But then I found different bugs and issues on the other database systems, that would work with MySQL but were still not nice to have.
Also SQLite is quite useful for the developer, to do first steps with developing for OpenPetra, without needing to configure a database system.
So back to the refactoring:
A database operation usually should look like this:
private void MyExample(TDataBase ADataBase = null)
{
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
TDataBase db = DBAccess.Connect("Test", ADataBase);
string sql = "INSERT INTO a_gift(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i) " +
"VALUES(43, 99999999, 1)";
db.WriteTransaction(ref t, ref SubmissionOK,
delegate
{
db.ExecuteNonQuery(sql, t);
SubmissionOK = true;
});
DataTable example = null;
db.ReadTransaction(ref t,
delegate
{
sql = "SELECT * FROM a_ledger";
example = db.SelectDT(sql, "tablename", t);
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
}
Note: DBAccess.Connect will only create a new database connection, if ADataBase is null.
At the end of the procedure, we should close the database connection, if it was not passed to the procedure.
WriteTransaction: this would create a new serializable transaction, if none is already open on the database connection.
If the transaction was created with this call, and SubmissionOK is true, the transaction will be committed.
ReadTransaction is similar, but it creates a readcommitted transaction, and reuses an existing serializable or readcommitted transaction if it exists.
We also have a function GetNewOrExistingTransaction on the database object, which will leave it to the developer to commit or rollback the transaction.
We also have DBAccess.WriteTransaction and DBAccess.ReadTransaction, which will create a database connection internally.
I hope this refactoring makes it easier for new developers to join.
