The scope of this tutorial is to learn, how to add new tables, prepared statements etc. into your App.
The tutorial is based on a newly created innovaphone App with the Visual Studio plugin.
We will also make use of the javascript innovaphone.ui1.Scrolling object and the UTaskTemplate of the task classes.
Task
Create a history table where every change of the counter is tracked with a timestamp and the sip of the logged in user.
Use a transaction, as multiple writing queries are executed which belong to the same logical action.
Use prepared statemens for better query performance.
The history is to be displayed inside a scrolling area below the counter and queried once after opening the app.
Step by step
The used file and class names in this example are based on a newly created App with the name NewApp1 and the company name innovaphone.
Your filenames might be different according to your settings.
Creation of a new table
Header file NewApp1_tasks.h, cpp file NewApp1_tasks.cpp inside the NewApp1 folder.
Tables and columns are always initialized on each start of the App instance, so you can add tables and columns later without issues.
Add a new class member class TaskPostgreSQLInitTable initHistory; into the class TaskDatabaseInit. This will be used to create a new table.
Initialize this class member after the constructor definition in the cpp file. Use the name history for the table.
Add the neccessary columns with a foreign key and index for this key to the users table inside the constructor of the class TaskDatabaseInit.
Call the Start function of the initHistory task inside the TaskComplete function of the class TaskDatabaseInit to start the task which creates the table and columns.
class TaskDatabaseInit in NewApp1_tasks.h after these changes
class TaskDatabaseInit : public ITask, public UTask {
class TaskPostgreSQLInitTable initUsers;
class TaskPostgreSQLInitTable initCounter;
class TaskPostgreSQLInitTable initHistory; // tutorial change
void TaskComplete(class ITask * const task) override;
void TaskFailed(class ITask * const task) override;
public:
TaskDatabaseInit(IDatabase * database);
virtual ~TaskDatabaseInit();
void Start(class UTask * user) override;
};
class TaskDatabaseInit in NewApp1_tasks.cpp after these changes (just changed functions)
TaskDatabaseInit::TaskDatabaseInit(IDatabase * database)
: initUsers(database, "users"),
initCounter(database, "counter"),
initHistory(database, "history") // tutorial change
{
// users
initUsers.AddColumn("id", "BIGSERIAL PRIMARY KEY NOT NULL");
initUsers.AddColumn("guid", "UUID UNIQUE");
initUsers.AddColumn("sip", "VARCHAR(256) UNIQUE NOT NULL");
initUsers.AddColumn("dn", "VARCHAR(256)");
initUsers.AddColumn("count", "BIGINT");
// counter
initCounter.AddColumn("id", "BIGSERIAL PRIMARY KEY NOT NULL");
initCounter.AddColumn("name", "VARCHAR(32) UNIQUE NOT NULL");
initCounter.AddColumn("value", "BIGINT");
// history
initHistory.AddColumn("id", "BIGSERIAL PRIMARY KEY NOT NULL"); // tutorial change
initHistory.AddColumn("user_id", "BIGINT REFERENCES users(id) ON DELETE CASCADE NOT NULL"); // foreign key definition
initHistory.AddColumn("value", "BIGINT");
initHistory.AddColumn("timestamp", "BIGINT DEFAULT EXTRACT(EPOCH FROM now()) * 1000"); // we save UTC in milliseconds
initHistory.AddIndex("history_user_id_index", "user_id", false); // foreign keys need an own index to be fast
}
void TaskDatabaseInit::TaskComplete(class ITask * const task)
{
if (task == &initUsers) initCounter.Start(this);
else if (task == &initCounter) initHistory.Start(this); // tutorial change
else if (task == &initHistory) Complete(); // tutorial change
else ASSERT(false, "TaskDatabaseInit::TaskComplete");
}
Creation of prepared statements
Header file NewApp1.h, cpp file NewApp1.cpp inside the NewApp1 folder.
Prepared statements live as long as the database connection lives. So we create two statements to read and write the history inside the class, which setups the database connection: class NewApp1.
Add two somehow from outside accessible members of class IPreparedStatement * to your NewApp1 class, stmtHistoryInsert and stmtHistoryRead
Add an overload of the callback function DatabasePrepareStatementResult to the same class. This function will be called when statements are prepared.
Prepare the two statements inside the cpp file void NewApp1::DatabaseInitComplete instead of calling new CountInit (note that you'll receive one callback for each database->PrepareStatement call!).
stmtHistoryInsert will use an "INSERT INTO ..." query and stmtHistoryRead a "SELECT ... FROM history" query
Add the implementation of void NewApp1::DatabasePrepareStatementResult and assign the two members to the callback statement value. Call currentTask = new CountInit(this); afterwards.
Delete the statements in the DatabaseShutdown callback and set the pointers to zero.
Use of the prepared statements inside the already existing class TaskReadWriteCount
Header file NewApp1_tasks.h, cpp file NewApp1_tasks.cpp inside the NewApp1 folder.
Add overloads for DatabaseBeginTransactionResult and DatabaseEndTransactionResult to the TaskReadWriteCount class.
Add a member class IPreparedStatement * stmtHistoryInsert.
Add a bool member fetchValue, as we need to modify the state machine.
Add class IPreparedStatement * stmtHistoryInsert as parameter to the constructor and set it inside the constructor, as we need the stmtHistoryInsert pointer of the NewApp1 class.
Begin a new transaction in the TaskReadWriteCount::Start function.
Set fetchValue=true (default false) in the Start function when the existing database->ExecSQL is called to prevent multiple WriteCount calls in the DatabaseExecSQLResult function.
Use the prepared statement inside TaskReadWriteCount::WriteCount() with the sip and count members and call EndTransaction afterwards instead of Complete().
Do not call Complete in TaskReadWriteCount::DatabaseInsertSQLResult anymore, as EndTransaction is always called instead.
Implement DatabaseBeginTransactionResult (empty function) and DatabaseEndTransactionResult. Call Complete() inside DatabaseEndTransactionResult.
There is no need to call EndTransaction in the DatabaseError function, as the database implementation itself rollsback any transaction on errors.
There are several calls of the TaskReadWriteCount constructor (MonitorCount, IncrementCount, ResetCount and CountInit). Pass the stmtHistoryInsert pointer of the NewApp1 instance here (e.g. session->instance->stmtHistoryInsert)
class TaskReadWriteCount in NewApp1_tasks.h after these changes
class TaskReadWriteCount in NewApp1_tasks.cpp after these changes
TaskReadWriteCount::TaskReadWriteCount(IDatabase * database, class IPreparedStatement * stmtHistoryInsert, const char * sip, const char * dn, long64 count, bool update)
{
this->database = database;
this->stmtHistoryInsert = stmtHistoryInsert; // tutorial change
this->sip = _strdup(sip);
this->dn = _strdup(dn);
this->count = count;
this->update = update;
this->fetchValue = false; // tutorial change
id = 0;
}
TaskReadWriteCount::~TaskReadWriteCount()
{
if (sip) free((void *)sip);
if (dn) free((void *)dn);
}
void TaskReadWriteCount::Start(class UTask * user)
{
this->user = user;
database->BeginTransaction(this); // tutorial change
if (update) {
database->InsertSQL(this, "INSERT INTO counter (name, value) VALUES ('user', %llu) ON CONFLICT (name) DO UPDATE SET value=EXCLUDED.value", count);
}
else if (count < 0) {
database->ExecSQL(this, DB_EXEC_FETCH_ALL, "SELECT value FROM counter WHERE name='user'");
fetchValue = true; // tutorial change
}
else {
WriteCount();
}
}
void TaskReadWriteCount::DatabaseExecSQLResult(IDatabase * const database, class IDataSet * dataset)
{
// tutorial change
if (fetchValue) {
count = !dataset->Eot() ? dataset->GetULong64Value(dataset->GetColumnID("value")) : 0;
WriteCount();
fetchValue = false; // tutorial change
}
delete dataset;
}
void TaskReadWriteCount::WriteCount()
{
if (sip && dn) {
database->InsertSQL(this, "INSERT INTO users (sip, dn, count) VALUES (%s, %s, %llu) ON CONFLICT (sip) DO UPDATE SET dn=EXCLUDED.dn, count=EXCLUDED.count", sip, dn, count);
// tutorial change
stmtHistoryInsert->SetParam(0, this->sip); // set the first parameter to the sip
stmtHistoryInsert->SetParam(1, this->count); // the second one to count
database->ExecSQL(this, DB_EXEC_FETCH_ALL, stmtHistoryInsert); // exec the stmt
}
database->EndTransaction(this); // tutorial change
}
void TaskReadWriteCount::DatabaseInsertSQLResult(IDatabase * const database, ulong64 id)
{
if (update) {
update = false;
WriteCount();
}
else {
this->id = id;
// tutorial change, do not call Complete() here anymore, as an DatabaseEndTransactionResult will be received instead
}
}
void TaskReadWriteCount::DatabaseError(IDatabase * const database, db_error_t error)
{
Failed();
}
// tutorial change
void TaskReadWriteCount::DatabaseBeginTransactionResult(IDatabase * const database)
{
}
// tutorial change
void TaskReadWriteCount::DatabaseEndTransactionResult(IDatabase * const database)
{
Complete();
}
Create a new class TaskGetHistory to retrieve the history rows
Header file NewApp1_tasks.h, cpp file NewApp1_tasks.cpp inside the NewApp1 folder.
Add overloads for DatabaseExecSQLResult and DatabaseError to the TaskGetHistory class.
Add a member class IPreparedStatement * stmtHistoryRead.
Add class IPreparedStatement * stmtHistoryRead as parameter to the constructor and set it inside the constructor, as we need the stmtHistoryRead pointer of the NewApp1 class.
Implement the Start function and exec the prepared statemnt without parameters.
Iterate through the dataset in DatabaseExecSQLResult and call user->TaskProgress for each row.
Implement the GetItem function. This will fill the handed json with the dataset data.
Call user->TaskComplete afterwards.
Call user->TaskFailed inside DatabaseError.
class TaskGetHistory in NewApp1_tasks.h
class TaskGetHistory : public ITask, public UDatabase {
void DatabaseExecSQLResult(IDatabase * const database, class IDataSet * dataset) override;
void DatabaseError(IDatabase * const database, db_error_t error) override;
class IDatabase * database;
class IDataSet * dataset;
class IPreparedStatement * stmtHistoryRead;
public:
TaskGetHistory(IDatabase * database, class IPreparedStatement * stmtHistoryRead);
~TaskGetHistory();
void Start(class UTask * user) override;
void GetItem(class json_io * json, word base, char *& tmp);
};
class TaskGetHistory in NewApp1_tasks.cpp
Add the json.h as include after the platform.h include at the top of the cpp file.
Implement the message type GetHistory in the NewApp1Session class
Header file NewApp1.h, cpp file NewApp1.cpp inside the NewApp1 folder.
Add a class UTaskTemplate member taskGetHistory with the class TaskGetHistory to your NewApp1Session class.
Add the task callback function TaskGetHistoryProgress and TaskGetHistoryComplete to the same class.
Initialize the taskGetHistory in the constructor definition in the cpp file with these callback functions.
Add a strcmp for GetHistory inside the NewApp1Session::AppWebsocketMessage callback and set currentTask to new class TaskGetHistory.
Start this task with a reference to the taskGetHistory member.
Implement the two callback functions TaskGetHistoryProgress (send out a json message with GetHistoryResult and the data of one row) and TaskGetHistoryFinished
class NewApp1Session in NewApp1.h after these changes
Add the innovaphone.ui1.Scrolling class to our project
NewApp1.mak in the root folder, innovaphone-newapp1.htm in NewApp1/apps
Add the web1/ui1.scrolling/ui1.scrolling.mak to the NewApp1.mak in the root folder of your App.
Add the javascript source file web/ui1.scrolling/innovaphone.ui1.scrolling.js to the innovaphone-newapp1.htm.
NewApp1.mak after the change
OUT = NewApp1
include sdk/sdk-defs.mak
include web1/appwebsocket/appwebsocket.mak
include web1/fonts/fonts.mak
include web1/lib1/lib1.mak
include web1/ui1.lib/ui1.lib.mak
include web1/ui1.scrolling/ui1.scrolling.mak # tutorial change
include NewApp1/NewApp1.mak
APP_OBJS += $(OUTDIR)/obj/NewApp1-main.o
$(OUTDIR)/obj/NewApp1-main.o: NewApp1-main.cpp force
include sdk/sdk.mak
Retrieve the history with GetHistory in javascript and add it to the ui1.Scrolling class
innovaphone-newapp1.js inside NewApp1/apps
Add a reference at the top with /// to ../../web1/ui1.scrolling/innovaphone.ui1.scrolling.js for intellisense
Add a new absolute positioned with fixed height innovaphone.ui1.Scrolling object after the counter div.
Send mt: "GetHistory" after the mt: "MonitorCount" send to receive the history.
Get the GetHistoryResult in the update function and a one div per result to the scrolling object.
innovaphone-newapp1.js after the change
/// <reference path="../../web1/lib1/innovaphone.lib1.js" />
/// <reference path="../../web1/appwebsocket/innovaphone.appwebsocket.Connection.js" />
/// <reference path="../../web1/ui1.lib/innovaphone.ui1.lib.js" />
/// <reference path="../../web1/ui1.scrolling/innovaphone.ui1.scrolling.js" />
var innovaphone = innovaphone || {};
innovaphone.NewApp1 = innovaphone.NewApp1 || function (start, args) {
this.createNode("body");
var that = this;
var colorSchemes = {
dark: {
"--bg": "#191919",
"--button": "#303030",
"--text-standard": "#f2f5f6",
},
light: {
"--bg": "white",
"--button": "#e0e0e0",
"--text-standard": "#4a4a49",
}
};
var schemes = new innovaphone.ui1.CssVariables(colorSchemes, start.scheme);
start.onschemechanged.attach(function () { schemes.activate(start.scheme) });
var texts = new innovaphone.lib1.Languages(innovaphone.NewApp1Texts, start.lang);
var counter = this.add(new innovaphone.ui1.Div("position:absolute; left:0px; width:100%; top:calc(50% - 50px); font-size:100px; text-align:center", "-"));
// tutorial change
var history = this.add(new innovaphone.ui1.Scrolling("position:absolute; left:0px; width:100%; top:calc(50% + 70px); height:100px"));
var app = new innovaphone.appwebsocket.Connection(start.url, start.name);
app.checkBuild = true;
app.onconnected = app_connected;
function app_connected(domain, user, dn, appdomain) {
var src = new app.Src(update);
src.send({ mt: "MonitorCount" });
// tutorial change
src.send({ mt: "GetHistory" });
that.add(new innovaphone.ui1.Div(null, null, "button")).addTranslation(texts, "count").addEvent("click", function () {
app.sendSrc({ mt: "IncrementCount" }, function (msg) {
counter.addHTML("" + msg.count);
});
});
function update(msg) {
if (msg.mt == "MonitorCountResult" || msg.mt == "UpdateCount") {
counter.addHTML("" + msg.count);
}
else if (msg.mt == "GetHistoryResult") { // tutorial change
var d = new Date(msg.ts);
history.add(new innovaphone.ui1.Div("", d.toLocaleDateString(start.lang) + " " + d.toLocaleTimeString(start.lang) + " " + msg.sip + ": " + msg.count.toLocaleString(start.lang)));
}
}
}
}
innovaphone.NewApp1.prototype = innovaphone.ui1.nodePrototype;
Try it out
If you know compile and run your NewApp1 App, you can open it from myApps.
You can play around with the counter, close the App and open it again.
Then you should see the last history entries (the history entries are not live updated, just received once on start of the App).