Database

The Database interface is an abstraction of different database libraries to allow acces do different databases (PostgreSQL and MySQL are supported). The interface only allows easy access to the underlying database system, which will be enough in most cases. However, nearly everything you can do with the SQL features supported by the database engine, you can do with IDatabase. IDatabase won't translate the non-standard SQL syntax from one database engine to an other. So while they can (and maybe have to be) used, changing the underlying database engine will probably fail.

NOTE: The SQL engine used and provided by the AppPlatform is PostgreSQL. There is no mySQL server running on the AppPlatform. But because there are a lot of servers in the world wide web (or elsewhere) running MySQL, it is supported by the AppPlatform SDK, too. For AppPlatform app developtment, normaly only PostgreSQL knowledge is needed.

File information

Public functions

Functions to initialize

extern IDatabaseProvider * CreatePostgreSQLDatabaseProvider();
extern IDatabaseProvider * CreateMySQLDatabaseProvider();

This functions will be used to create a database provider, wich will return an instance to an IDatabase instance for the database engine to use. Note that the returned instance must be deleted, if no longer needed.

Helper functions

inline const char * DBErrorToStr(db_error_t err);

Classes

IDatabaseProvider

class IDatabaseProvider {
public:
    virtual ~IDatabaseProvider() {}
    virtual class IDatabase * CreateDatabase(class IIoMux * const iomux, class UDatabase * const user, class IInstanceLog * log) = 0;
};

A database provider is needed to create an IDatabase instance. What kind of database driver used by the returned instance is defined by the type of IDatabaseProvider itself. The IDatabase instance returned by an IDatabaseProvider must be deleted, if no longer used.

Public functions

IDatabase

lass IDatabase {
public:
    virtual ~IDatabase() {}
    virtual void Connect(const char * address, const char * dbname, const char * user, const char * password);
    virtual bool Connected();
    virtual void Shutdown();
    virtual size_t QueryPrint(char * buffer, size_t bufferSize, const char * sqlcmd, ...);
    virtual size_t QueryPrintV(char * buffer, size_t bufferSize, const char * sqlcmd, va_list argList);
    virtual void ExecSQL(UDatabase * const user, dword flags, const char * sqlcmd, ...);
    virtual void ExecSQLV(UDatabase * const user, dword flags, const char * sqlcmd, va_list argList);
    virtual void InsertSQL(UDatabase * const user, const char * sqlcmd, ...);
    virtual void InsertSQLV(UDatabase * const user, const char * sqlcmd, va_list argList);
    virtual void BeginTransaction(UDatabase * const user, const char * lockTableCmd = nullptr);
    virtual void EndTransaction(UDatabase * const user, bool doRollback = false);
    virtual void PrepareStatement(UDatabase * user, const char * sqlcmd);
    virtual void ExecSQL(UDatabase * const user, dword flags, class IPreparedStatement * const statement);
    virtual void InsertSQL(UDatabase * const user, class IPreparedStatement * const statement);
    virtual size_t EscapeSearchLiterals(const char * value, char * buffer, size_t bufferSize);
    virtual const char * GetLastErrorMessage();
    virtual const char * GetLastErrorCode();
};

This is the main calss that handels the communication between the Application and the database server. The interface is primarly for executing queries while the results of that queries need to be handled by an UDatabase instance. Each queries posted will be add to an internal queue and processed with the fifo priciple. Queries posted during a call of BeginTransaction() and EndTransaction() will be part of that transaction. Nested transactions are not allowed.

When sending SQL queries, a user can be given to override the default user set with IDatabaseProvider::CreateDatabase(). If an other user than the default one is given to IDatabase::BeginTransaction(), all queries posted of that user will be prioritized because of that transaction. So queries posted from other users (or with no special user to use the default one) will be placed at the end of the queue, until the user who opens the tansaction closes it (IDatabase::EndTransaction()). This shows also a limitation at this point: if multiple UDatabase instances as users are needed for one transaction (e. G. by starting a transaction to update a set of data from different sources), it won't work as long as there is some kind of "master" user the other ones. Gladly there is a solution. For that case, DatabaseSwitch can be used.

Using multiple users will also raise the question, how error handling worlks. Actually that depends on the error and how that error will influence the internal state of IDatabase:

• If an SQL command of a user failes, all pending commands of the user that sends that user will be removed from the internal queue. Then the user itself will receive an UDatabase::DatabaseError(). If the user had an active transaction, IDatabase will make a rollback and unlock tables if they are locked.
  
  
• If an critical error occures that makes it impossible to continue using IDatabase, all pending commands of all users will be discarded. If there is an active transaction, it will be rolled back. Then the UDatabase::DatabaseError() function will be called for all those users who had pending queries inside the queue. After all the users had been informed, IDatabase internaly will call Shutdown() to close the database connection and call UDatabase::DatabaseShutdown() callback of the main user.

Public functions

IPreparedStatement

class IPreparedStatement {
public:
    virtual ~IPreparedStatement() {}
    virtual void SetParam(dword idx, int value);
    virtual void SetParam(dword idx, dword value);
    virtual void SetParam(dword idx, long64 value);
    virtual void SetParam(dword idx, ulong64 value);
    virtual void SetParam(dword idx, bool value);
    virtual void SetParam(dword idx, double value);
    virtual void SetParam(dword idx, int * value);
    virtual void SetParam(dword idx, dword * value);
    virtual void SetParam(dword idx, long64 * value);
    virtual void SetParam(dword idx, ulong64 * value);
    virtual void SetParam(dword idx, double * value);
    virtual void SetParam(dword idx, bool * value);
    virtual void SetParam(dword idx, const char * value);
    virtual void SetParam(dword idx, const byte * buffer, size_t size);
};

Prepared statements are some kind of ready-to-use SQL queries. While they needs some managament overhead (create them, stored them on some place for usage and deleted them when no longer used), they have some other advantage: ease of use and performance. As a thumb rule you can say: for each SQL query that is used more than once on a regular basis, it is worth to use a prepared statement. Because when using prepared statment (after creating them), there is no need to build up an SQL string, escape strings to prevent SQL injection and also there is no need to parse the SQL string again on the database server side. So using prepared statements (while consuming a little memory footprint) can spead up the applications performance a bit. That said, according to the documentation of PostgreSQL and MySQL, prepared statements can also have no benefit at all or even slow down the performance under some circumstances. So finally it depends on tests. However, using prepared statements for often called SQL queries will be a good start.

To create an IPreparedStatement instance, call IDatabase::PrepareStatement(). The instance will then passed to UDatabase::DatabasePrepareStatementResult() and must be deleted, if no longer used. To execute a prepared statement, simply set the parameters and pass it to IDatabase::ExecSQL() or IDatabase::InsertSQL(). The index of a parameter depends on the position of the parameters place holder in the SQL string. So for a query like "select * from users where user_name=%s and age > %i", the index of the parameter for %s will be 0, and for the parameter for %i 1. The placeholders have the same format as for IDatabase::QueryPrint(), so read the description of that function for more information. However, because of the nature of prepared statements, there are some differences for the placeholders:

• %p will be ignored. To pass a pointer, use one of the IPreparedStatement::SetParam() functions that accept the pointer of the format needed.
• %s, %S and %n will be handled in the same way: the string will be used without beeing enclosed by ' or ".
• %s is not supported. Use %s instead and call IDatabase::EscapeSearchLiterals() to get the same effect.

After the parameters had been set and the prepared statment instance had been passed to IDatabase::ExecSQL() or IDatabase::InsertSQL(), the IPreparedStatement instance can directly be used again and the parameters can be changed. Because the prepared statements will manage the parameters internally by reference counting so that they are fixed as soon as the prepared statement instance sits inside the IDatabase execution queue.

Public functions

IDataSet

class IDataSet {
public:
    virtual ~IDataSet() {}

    virtual size_t GetColumnCount();
    virtual bool Eot();
    virtual void Next();
    virtual void FetchNextRow();
    virtual bool IsSingleRowMode();
    virtual ulong64 GetAffectedRows();

    virtual const char * GetColumnName(int column);
    virtual int GetColumnID(const char * columnName);

    virtual bool ColumnIsNull(const char * columnName);
    virtual bool ColumnIsNull(int column);

    virtual bool ColumnIsString(int column);

    virtual int GetIntValue(const char * columnName);
    virtual dword GetUIntValue(const char * columnName);
    virtual long64 GetLong64Value(const char * columnName);
    virtual ulong64 GetULong64Value(const char * columnName);
    virtual bool GetBoolValue(const char * columnName);
    virtual double GetDoubleValue(const char * columnName);
    virtual const char * GetStringValue(const char * columnName);
    virtual const char * GetStringValueWithNull(const char * columnName);
    virtual size_t GetDataSize(const char * columnName);
    virtual const byte * GetDataValue(const char * columnName);

    virtual int GetIntValue(int column);
    virtual dword GetUIntValue(int column);
    virtual long64 GetLong64Value(int column);
    virtual ulong64 GetULong64Value(int column);
    virtual bool GetBoolValue(int column);
    virtual double GetDoubleValue(int column);
    virtual const char * GetStringValue(int column);
    virtual const char * GetStringValueWithNull(int column);
    virtual size_t GetDataSize(int column);
    virtual const byte * GetDataValue(int column);
};

After executing a SQL query, the result of that query will given to the UDatabase user in form of an IDataSet instance. By using that instance, the data of the query result can be accessed. After prpcessomg all data, the application must delete the IDataSet instance to let the IDatabase process the next SQL query from the internal queue. So it is alos not allowes to store the IDataSet for some later use, because it will block the whole database system. An IDataSet with instance will be passes to UDataset::DatabaseExecSQLResult() and should be processed before the function returns.

Public functions

UDatabase

class UDatabase {
public:
    virtual void DatabaseConnectComplete(IDatabase * const database) {};
    virtual void DatabaseShutdown(IDatabase * const database, db_error_t reason) {};
    virtual void DatabaseError(IDatabase * const database, db_error_t error) = 0;
    virtual void DatabaseExecSQLResult(IDatabase * const database, class IDataSet * dataset) {};
    virtual void DatabaseInsertSQLResult(IDatabase * const database, ulong64 id) {};
    virtual void DatabaseBeginTransactionResult(IDatabase * const database) {};
    virtual void DatabaseEndTransactionResult(IDatabase * const database) {};
    virtual void DatabasePrepareStatementResult(IDatabase * const database, class IPreparedStatement * statement) {};
};

Each database request will lead to a result, wich will be redirected to an UDatabase instance. An app must provide at least on UDatabase instance to pass to IDatabase, but also can have multiple UDatabase objects to pass to database related calls. That user passed to a function (like IDatabase::ExecSQL()) will receive the result of the SQL query to execute. If nullptr will be passed as user to that functions, the default user (given to IDatabaseProvider::CreateDatabase()) will be used for callbacks.

Public functions

Data types

Defines / Statics

#define DB_EXEC_FETCH_ALL    0x01

db_error_t

typedef enum {
    DB_OK = 0,
    DB_SHUTDOWN_NORMAL = DB_OK,
    DB_ERR_CONNECT_FAILED,
    DB_ERR_SQL_COMMAND_FAILED,
    DB_ERR_SHUTDOWN_STATE,
    DB_ERR_SERVER_ERROR
} db_error_t;

This enum is used determine the reason why a database connection had been closed. The values are:

Code Example

app::app(IIoMux * iomux)
    : iomux(iomux)
{
    IDatabaseProvider * databaseProvider = CreatePostgreSQLDatabaseProvider();
    this->database = databaseProvider->CreateDatabase(iomux, this, this);
    delete databaseProvider;
    this->database->Connect("127.0.0.1", "test_db", "user", "12345");
}

void app::DatabaseConnectComplete(IDatabase * const database)
{
    this->database->ExecSQL(this, 0, "select * from test_table where name=%s and id=%u", "Noname", 42);
}

void app::DatabaseExecSQLResult(IDatabase * const database, class IDataSet * dataset)
{
    if (dataset->Eot()) {
        delete dataset;
        this->database->Shutdown();
    }
    else {
        printf("%u | %s\n", dataset->GetIntValue("id"), dataset->GetStringValue("name"));
        dataset->FetchNextRow();
    }
}

void app::DatabaseShutdown(IDatabase * const database, db_error_t reason)
{
    if (reason != DB_SHUTDOWN_NORMAL) {
        debug->printf("An error occured, look at the log of the app\n");
    }
    
    delete this->database;
    this->database = nullptr;
}