TeamWox SDK: Interaction with DBMS

Introduction

In our previous article TeamWox SDK: Building User Interface you've got familiar with concepts of creating user interface in TeamWox using examples of several typical controls. But the data displayed on pages were static, i.e. we could not change them.

In this article we will discuss how to organize permanent storage of data and their modification. For this purpose TeamWox uses the embeded version of Firebird DBMS. We'll continue to work with Hello World training module. In its manager we will add new methods of working with data so that they can be loaded not from static arrays, but from database.

As a part of module deployment, we'll design a simple table in database, and then organize request for output and change its values.

Then, making use of skills acquired from the previous article, we will modify the existing UI so that it will allow us to create, display, modify, and delete data from table.

Note: This article describes a simplified way of interaction with data - reading from database every time when data are needed. This is not optimal in terms of performance.

More correct is to organize data caching in memory. In a later article we will discuss how to implement caching and expand manager architecture.

 

TeamWox Modules Deployment

Deployment is a stage of TeamWox module life-cycle. Conventionally, module life-cycle can be represented as following steps:

  • Step 1. Develop module
  • Step 2. Install and configure module on TeamWox server
  • Step 3. Find errors, collect users feedback
  • Step 4. Expand module functionality, fix errors
  • Step 5. Update module on server

Then steps 3, 4 and 5 are iterated during the whole period of module support by its developers.

We will expand the functionality of Hello World module by adding the ability to work with data stored in database. But first of all we need to create a table with certain structure (if necessary, you can also initialize this table with data). With each subsequent module update the structure of table will be checked and changed if necessary.

SDK provides all the necessary tools to work with TeamWox DBMS.

1. In the HelloWorld.vcproj project include smart_sql.h into stdafx.h. This tool - is a set of classes for easy work with database by means of SQL (it is located in the \TeamWox SDK\SDK\Common folder). Follow the sequence of files inclusion. Also add it into your project (\Header Files\Common).

#include "..\..\SDK\Common\SmartLogger.h"
#include "..\..\SDK\Common\smart_sql.h"
#include "..\..\SDK\Common\tools_errors.h"
#include "..\..\SDK\Common\tools_strings.h"
#include "..\..\SDK\Common\Page.h"

2. Define the structure of table. To do this, create function named DBTableCheck in module manager.

//+------------------------------------------------------------------+
//| Module Manager                                                   |
//+------------------------------------------------------------------+
class CHelloWorldManager
  {
private:
   static HelloWorldRecord m_info_records[];         // List of public information
   static HelloWorldRecord m_advanced_records[];     // List of information with limited access
   //---
   IServer          *m_server;                       // Reference to server
   //---
   CSync             m_sync;                         // Synchronizing access to class members

public:
                     CHelloWorldManager();
                    ~CHelloWorldManager();
   //---
   TWRESULT          Initialize(IServer *server);
   //--- Working with information
   TWRESULT          InfoGet(const Context *context,HelloWorldRecord *records,int *count);
   TWRESULT          InfoAdvacedGet(const Context *context,HelloWorldRecord *records,int *count);

private:
   TWRESULT          DBTableCheck(ISqlBase *sql);    // Check/Create/Modify table structure
  };
//+------------------------------------------------------------------+
//| Create the HELLOWORLD table                                      |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::DBTableCheck(ISqlBase *sql)
  {
//--- checks
   if(sql==NULL) ReturnError(RES_E_INVALID_ARGS);
//---
   TWRESULT res=RES_S_OK;
//---
   if(RES_FAILED(res=sql->CheckTable("HELLOWORLD",
       "ID     BIGINT        DEFAULT 0  NOT NULL,"
       "NAME   VARCHAR(256)  DEFAULT '' NOT NULL",
       "PRIMARY KEY (ID)",
       "DESCENDING INDEX IDX_HELLOWORLD_ID_DESC (ID)",
        NULL,NULL,0)))
      ReturnError(res);
//---   
   ExtLogger(NULL,LOG_STATUS_INFO) << "Table 'HELLOWORLD' checked";
//---
   return(RES_S_OK);
  }
The ISqlBase::CheckTable method checks/creates/modifies table and its structure. Descriptions of table fields are separated by commas and are enclosed in quotation marks, since this whole text is passed as the second argument of the CheckTable function.

Text describing the field in CheckTable corresponds to the syntax CREATE TABLE [table name] ([fields description]) [table parameters].
Logging

It's always recommended for you to write key stages of module's work into a log-file, because it significantly simplifies finding errors in modules on client's server. For this purpose SDK contains the special class called CSmartLogger.

In our case, we print the message about the CheckTable method's work into log using the ExtLogger object of this class. It is better to print messages into log in English because of possible encodings conflict.

3. Connect manager to server DB. To do this, in \Managers\HelloWorldManager.cpp add the following lines of code.

//+------------------------------------------------------------------+
//| Initialize module                                                |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::Initialize(IServer *server)
  {
   if(server==NULL) ReturnError(RES_E_INVALID_ARGS);
//---
   TWRESULT res=RES_S_OK;
//---
   CLocker lock(m_sync);
//---
   m_server=server;
//---
   CSmartSql sql(server);
   if(sql==NULL) ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to make sql connection");
//---
   if(RES_FAILED(res=DBTableCheck(sql))) ReturnErrorExt(res,NULL,"failed to check table");
//---
   return(RES_S_OK);
  }

4. In the HelloWorldRecord structure (\API\HelloWorld.h) adjust data types to make them appropriate to use in SQL requests (in TeamWox it's recommended to use 64-bit IDs for records).

//+------------------------------------------------------------------+
//| Record structure                                                 |
//+------------------------------------------------------------------+
struct HelloWorldRecord
  {
   INT64             id;
   wchar_t           name[256];
  };

Compile the module and start TeamWox server. The console window displays a message, telling us that HELLOWORLD table is checked (created).

Check table creation

Now when you install Hello World module on any TeamWox server, HELLOWORLD table will be automatically created with the ID and NAME fields. Everything is implemented in manager's source code - you don't need any additional tools for this.

 

Manager Methods of Working with Data

Working with any DBMS is based on four fundamental operations called C.R.U.D.:

  • Create new records (Create)
  • Read records (Retrieve)
  • Edit records (Update)
  • Delete records (Delete)

These methods provide comprehensive work with data. We will implement them in module's manager. In module's page we'll create HTTP API, which allow us to query data in JSON format.

In manager declare main and auxiliary methods and members.

//+------------------------------------------------------------------+
//| Module's manager                                                 |
//+------------------------------------------------------------------+
class CHelloWorldManager
  {
private:
   static HelloWorldRecord m_info_records[];           // List of public information
   static HelloWorldRecordAdv m_advanced_records[];    // List of information with limited access
   //---
   IServer          *m_server;                         // Reference to server
   //---
   CSync             m_sync;                           // Synchronizing access to class members
   //---
   INT64             m_next_id;                        // Next ID of record  

public:
                     CHelloWorldManager();
                    ~CHelloWorldManager();
   //---
   TWRESULT          Initialize(IServer *server, int prev_build);
   //--- Working with information
   TWRESULT          InfoAdvacedGet(const Context *context,HelloWorldRecordAdv *records,int *count);
   
   //--- C.R.U.D. methods (Create, Retrieve, Update и Delete)
   //--- RETRIEVE
   //    Get list of public information
   TWRESULT          InfoGet(const Context *context,HelloWorldRecord *records,int start,int *count);
   //    Get row from HELLOWORLD table
   TWRESULT          InfoGet(const Context *context,INT64 id,HelloWorldRecord *record);
   //    Get number of records from HELLOWORLD table
   TWRESULT          InfoCount(const Context *context,int *count);
   //--- CREATE and UPDATE
   //--- Modify record or add new record in HELLOWORLD table
   TWRESULT          InfoUpdate(const Context *context,HelloWorldRecord *record);
   //--- DELETE
   //--- Delete record from HELLOWORLD table
   TWRESULT          InfoDelete(const Context *context,INT64 id); 

private:
   TWRESULT          DBTableCheck(ISqlBase *sql);      // Check/Create/Modify table structure
   //--- Get maximal ID of record in HELLOWORLD table
   TWRESULT          LoadMaxId(ISqlBase *sql);
   //--- Get next ID
   INT64             NextId();
   };

 

1. Read records (Retrieve)

Begin with the reading, which consists of searching, retrieving and viewing table records.

1.1. To output data from database, we need to change the existing manager's function InfoGet that receives data to displayed on pages.

//+------------------------------------------------------------------+
//| Get list of public information                                   |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::InfoGet(const Context *context,HelloWorldRecord *records,int start,int *count)
  {
   HelloWorldRecord rec_info_get          ={0};
//--- checks (The 'start' parameter should begin with 1, 
//    since records in Firebird's tables also start from 1)
   if(context==NULL || m_server==NULL || records==NULL || start<1 || count==NULL || *count<=0) 
     ReturnError(RES_E_INVALID_ARGS);
   if(context->sql==NULL)                                                                      
     ReturnError(RES_E_INVALID_CONTEXT);
//--- Initialize variables to get request results in loop
   int              max_count             =*count;
   int              index                 =0;
   int              end                   =start+max_count; // Last record number
   *count                                 =0;
//--- Text of SQL request to select records from HELLOWORLD table and sort them by ID. 
   char             query_select[]        ="SELECT id,name FROM helloworld ORDER BY id ROWS ? TO ?";
//--- "Bind" data to parameters of request
   SqlParam         params_query_select[] ={
      SQL_INT64,&rec_info_get.id,  sizeof(rec_info_get.id),
      SQL_WTEXT, rec_info_get.name,sizeof(rec_info_get.name)
      };
//--- Parameters of request
   SqlParam         params_rows[]         ={
      SQL_LONG,&start,sizeof(start),
      SQL_LONG,&end,  sizeof(end)
      };
//--- send request
   if(!context->sql->Query(query_select, 
                           params_rows, _countof(params_rows), 
                           params_query_select, _countof(params_query_select)))
      ReturnErrorExt(RES_E_SQL_ERROR,NULL,"HELLOWORLD records query failed");
//---
   CLocker lock(m_sync);
//--- In loop get all elements of request results
   while(context->sql->QueryFetch())
     {
      if(*count<max_count)
        {
         memcpy(&records[*count],&rec_info_get,sizeof(records[*count]));
         //---
         (*count)++;
        }
      //---
      index++;
     }
//---
   context->sql->QueryFree();
//---
   return(RES_S_OK);
  }
The ISqlBase::Query method makes a request (with the specified parameters) to retrieve data from HELLOWORLD table without changing these data. In the SELECT request, the ROWS operand limits range of records derived from the table.

The ISqlBase::QueryFetch method retrieves rows one by one from selection after the read request. These rows are written to the records array and in this way are prepared to be displayed on the page.

Using the ISqlBase::QueryFree method the query and its results are released.

1.2. The InfoCount function will be used to get the number of records from DB.

Note: In terms of performance, getting the number of records without caching is not effective. With large amount of data it will lead to significant slowdown of server.
//+------------------------------------------------------------------+
//| Get number of records from HELLOWORLD table                      |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::InfoCount(const Context *context,int *count)
  {
   HelloWorldRecord rec_page_count            ={0};
//--- checks
   if(context==NULL || m_server==NULL || count==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->sql==NULL)                             ReturnError(RES_E_INVALID_CONTEXT);
//--- Text of SQL request for number of ID records in HELLOWORLD table
   char             query_page_count[]        ="SELECT COUNT(id) FROM helloworld";
//--- "Bind" data to parameters of request
   SqlParam         params_query_page_count[] ={
      SQL_LONG,count,sizeof(*count)
   };
//--- Send request
   if(!context->sql->Query(query_page_count,
                           NULL,0,
                           params_query_page_count,_countof(params_query_page_count)))
      ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to query helloworld records");
//--- Provide atomicity
   CLocker lock(m_sync);
//--- In loop get all elements
   if(!context->sql->QueryFetch()) 
      ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to get count");
//---
   context->sql->QueryFree();
//---
   return(RES_S_OK);
  }
In context of request (Context) we've already created connection, and it's not needed to create it again. The server has a pool of connections to DBMS.

Database connection is created only in the case of calling methods for request. In other words, if you've just asked for SQL connection, but did not use it, the real connection to DB will not be requested.

 

2. Create New Records (Create) And Edit Existing Records (Update)

2.1. Initialize the m_next_id variable:

//+------------------------------------------------------------------+
//| Constructor                                                      |
//+------------------------------------------------------------------+
CHelloWorldManager::CHelloWorldManager():m_server(NULL),m_next_id(0)
  {
//---
//---
  }

2.2. The LoadMaxId function uses SQL request to retrieve maximal ID (i.e. number of last record) from HELLOWORLD table.

//+------------------------------------------------------------------+
//| Get maximal ID of record in HELLOWORLD table                     |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::LoadMaxId(ISqlBase *sql)
  {
//--- Checks
   if(sql==NULL) ReturnError(RES_E_INVALID_ARGS);
//--- Text of SQL request to get maximal value of ID field
   char     query_select_max[]    ="SELECT MAX(id) FROM helloworld";
//--- Parameter of request
   SqlParam params_max_id[]       ={
      SQL_INT64,&m_next_id,sizeof(m_next_id)
   };
//--- Send request
   if(!sql->Query(query_select_max,NULL,0,params_max_id,_countof(params_max_id)))
      ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed query to get max id from helloworld");
//--- Get value
   sql->QueryFetch();
   sql->QueryFree();
//---
   return(RES_S_OK);
  }

2.3. The NextId function increments the value of m_next_id variable. It will be used when adding new records.

//+------------------------------------------------------------------+
//| Get next ID of record                                            |
//+------------------------------------------------------------------+
INT64 CHelloWorldManager::NextId()
  {
//---
   CLocker lock(m_sync);
   return(++m_next_id);   
//---
  }
Attention: You should always perform synchronization when changing/reading m_next_id. TeamWox server can handle multiple requests simultaneously.

2.4. Add a check for result of this function in manager initialization block.

//+------------------------------------------------------------------+
//| Initialize module                                                |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::Initialize(IServer *server, int prev_build)
  {
   if(server==NULL) ReturnError(RES_E_INVALID_ARGS);
//---
   TWRESULT res=RES_S_OK;
//---
   CLocker lock(m_sync);
//---
   m_server=server;
//---
   CSmartSql sql(server);
   if(sql==NULL) ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to make sql connection");
//---
   if(RES_FAILED(res=DBTableCheck(sql))) ReturnErrorExt(res,NULL,"failed to check table");
//--- проверка максимального ID для записи в таблице HELLOWORLD
   if(RES_FAILED(res=LoadMaxId(sql))) ReturnErrorExt(res,NULL,"failed to load max id for HELLOWORLD table");
//---
   if(prev_build<101)
     {
//--- If necessary change data on update from previous version of module
     }
//---
   return(RES_S_OK);
  }

2.5. The InfoUpdate method will add new entries in HELLOWORLD table or modify existing ones.

//+------------------------------------------------------------------+
//| Adding and saving record in HELLOWORLD table                     |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::InfoUpdate(const Context *context,HelloWorldRecord *record)
  {
//--- Checks
   if(context==NULL || record==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->sql==NULL)            ReturnError(RES_E_INVALID_CONTEXT);
//--- Text of SQL request to add new record in HELLOWORLD table
   char     query_insert[] ="INSERT INTO helloworld(name,id) VALUES(?,?)";
//--- Text of SQL request to modify existing record in HELLOWORLD table
   char     query_update[] ="UPDATE helloworld SET name=? WHERE id=?";
//--- "Bind" data to parameters of request
   SqlParam params_query[] ={
      SQL_WTEXT, record->name,   sizeof(record->name),
      SQL_INT64,&record->id,     sizeof(record->id)
      };
//--- Check if this is a new record
   if(record->id<=0)
     {
      record->id=NextId();
      //---
      if(!context->sql->QueryImmediate(query_insert, params_query, _countof(params_query)))
         ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to insert helloworld record");
     }
   else
     {
      if(!context->sql->QueryImmediate(query_update, params_query, _countof(params_query)))
         ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to update helloworld record");
     }
//---
   return(RES_S_OK);
  }
The ISqlBase::QueryImmediate method is used when it is not required to get results of request (unlike ISqlBase::Query).

2.6. In the second version of the InfoGet method SQL request gets rows from HELLOWORLD table by specified ID.

//+------------------------------------------------------------------+
//| Get row from HELLOWORLD table                                    |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::InfoGet(const Context *context,INT64 id,HelloWorldRecord *record)
  {
//--- Checks
   if(context==NULL || record==NULL || id<1) ReturnError(RES_E_INVALID_ARGS);
   if(context->sql==NULL)                    ReturnError(RES_E_INVALID_CONTEXT);
//--- Text of SQL request to get row from HELLOWORLD table by specified ID. 
   char query_select_string[]="SELECT id,name FROM helloworld WHERE id=?";
//--- Parameter of request
   SqlParam params_string[]={
      SQL_INT64,&id,sizeof(id)
      };
//--- "Bind" data to parameters of request
   SqlParam params_query_select_string[] ={
      SQL_INT64,&record->id,      sizeof(record->id),
      SQL_WTEXT, record->name,    sizeof(record->name)
      };
//---
   ZeroMemory(record,sizeof(*record));
//--- Send request
   if(!context->sql->Query(query_select_string, 
                           params_string, _countof(params_string), 
                           params_query_select_string, _countof(params_query_select_string)))
      ReturnErrorExt(RES_E_SQL_ERROR,NULL,"helloworld record query failed");
//--- Get element
   context->sql->QueryFetch();
   context->sql->QueryFree();
//---
   if(record->id!=id)
      return(RES_E_NOT_FOUND);
//---
   return(RES_S_OK);
  }

 

3. Delete records (Delete)

The InfoDelete method is implemented the easiest way.

//+------------------------------------------------------------------+
//| Delete record from HELLOWORLD table                             |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldManager::InfoDelete(const Context *context,INT64 id)
  {
//--- Checks
   if(context==NULL || id<=0) ReturnError(RES_E_INVALID_ARGS);
   if(context->sql==NULL)     ReturnError(RES_E_INVALID_CONTEXT);
//--- Text of SQL request to delete row from HELLOWORLD table by specified ID.
   char delete_string[]="DELETE FROM helloworld WHERE id=?";
//--- Parameter of request
   SqlParam params_delete_string[] ={
      SQL_INT64,&id,sizeof(id)
      };
//---
   if(!context->sql->QueryImmediate(delete_string,params_delete_string,_countof(params_delete_string)))
      ReturnErrorExt(RES_E_SQL_ERROR,NULL,"failed to delete helloworld record");
//---
   ExtLogger(context,LOG_STATUS_INFO) << "Delete record #" << id;
//---
   return(RES_S_OK);
  }

 

HTTP API to Work with Data

Now we need to create HTTP API, that will utilize implemented C.R.U.D. methods. Displaying and deleting records will be performed on existing PageNumberTwo, and to create and edit records we will make a new PageEdit. Data obtained by page from manager will be displayed as JSON objects.

 

Display and Delete Data

1. In PageNumberTwo declare functions that will use methods of reading and deleting records, as well as auxiliary members.

//+------------------------------------------------------------------+
//| Second page                                                  |
//+------------------------------------------------------------------+
class CPageNumberTwo : public CPage
  {
private:
   IServer          *m_server;
   //---
   HelloWorldRecord     m_info[5];     // Number of displayed records per page
   HelloWorldRecord    *m_info_current;
   CHelloWorldManager  *m_manager;     // Connect to manager
   int                  m_info_count;
   //--- For numerator of HELLOWORLD table
   int                  m_start;       // Number of current record
   int                  m_info_total;  // Total number of records
   //---
   HelloWorldRecordAdv  m_info_advanced[5];
   HelloWorldRecordAdv *m_info_advanced_current;
   int                  m_info_advanced_count;

public:
                        CPageNumberTwo();
                       ~CPageNumberTwo();
   //--- Page handler
   TWRESULT             Process(const Context *context,
                                IServer *server,
                                const wchar_t *path,
                                CHelloWorldManager *manager);
   //--- Function of displaying
   bool                 Tag(const Context *context,const TagInfo *tag);
   //--- READ
   //--- Data output on page in JSON format
   TWRESULT             JSONDataOutput(const Context *context,CHelloWorldManager *manager);
   //--- DELETE
   //--- Delete row
   TWRESULT             OnDelete(const Context *context,
                                 IServer *server,
                                 const wchar_t *path,
private:
   //--- Prepare pages list for numerator
   TWRESULT             PreparePages(const Context *context,CHelloWorldManager *manager);

  };

2. Initialize auxiliary members in page constructor. These variables will be used to create a special control - numerator - which displays specified number of records on page and put the remainder on subsequent tabs.

//+------------------------------------------------------------------+
//| Constructor                                                      |
//+------------------------------------------------------------------+
CPageNumberTwo::CPageNumberTwo() : m_server(NULL),m_start(0),m_info_total(0),
                                   m_info_count(0),m_info_advanced_count(0),
                                   m_info_advanced_current(NULL),m_info_current(NULL)
  {
//---
   ZeroMemory(m_info,         sizeof(m_info));
   ZeroMemory(m_info_advanced,sizeof(m_info_advanced));
//---
  } 

3. The method of preparing numerator pages will calculate the maximum number of records, as well as the record number from which to begin displaying data.

//+------------------------------------------------------------------+
//| Prepare the pages list for numerator                             |
//+------------------------------------------------------------------+
TWRESULT CPageNumberTwo::PreparePages(const Context *context,CHelloWorldManager *manager)
  {
   TWRESULT res = RES_S_OK;
//---
   if(context==NULL || manager==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->request==NULL)         ReturnError(RES_E_INVALID_CONTEXT);
//---
   if(RES_FAILED(res=manager->InfoCount(context,&m_info_total)))
      ReturnErrorExt(res,context,"failed get count of records");
//---
   m_start=max(1,context->request->GetInt32(IRequest::GET,L"from")+1);
   int max_page   =max(0,int((m_info_total+_countof(m_info))/_countof(m_info))-1);
//---
   if(m_start>max_page*_countof(m_info)+1)
      m_start=max_page*_countof(m_info)+1;
   return(res);
  }

4. Method of displaying records as JSON array.

//+------------------------------------------------------------------+
//| Data output on page in JSON format                               |
//+------------------------------------------------------------------+
TWRESULT CPageNumberTwo::JSONDataOutput(const Context *context,CHelloWorldManager *manager)
  {
   TWRESULT res = RES_S_OK;
//---
   if(context==NULL || manager==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->response==NULL)        ReturnError(RES_E_INVALID_ARGS);
//---
   m_info_count=_countof(m_info);
   if(RES_FAILED(res=manager->InfoGet(context,m_info,m_start,&m_info_count)))
      ReturnErrorExt(res,context,"failed get info records");
//---
   CJSON json(context->response);
   json << CJSON::ARRAY;
   for(int i=0;i<m_info_count;i++)
     {
      json << CJSON::OBJECT;
      //---
      json << L"id"   << m_info[i].id;
      json << L"name" << m_info[i].name;
      //---
      json << CJSON::CLOSE;
     }
//---
   json << CJSON::CLOSE;
   return(RES_S_OK);
  }

5. In CPageNumberTwo::Tag place a call of this method into the info_list token.

TWRESULT CPageNumberTwo::Tag(const Context *context,const TagInfo *tag)
  {
..............................
//---
   if(TagCompare(L"info_list",tag))
     {
      JSONDataOutput(context,m_manager);
      //---
      return(false);
     }
..............................
  }

6. Change the block of request processing by adding there calls of JSONDataOutput and PreparePages.

//+------------------------------------------------------------------+
//| Process request                                                  |
//+------------------------------------------------------------------+
TWRESULT CPageNumberTwo::Process(const Context *context,
                                 IServer *server,
                                 const wchar_t *path,
                                 CHelloWorldManager *manager)
  {
//--- checks
   if(context==NULL || path==NULL || manager==NULL || server==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->request==NULL || context->user==NULL)                ReturnError(RES_E_INVALID_CONTEXT);
//---
   TWRESULT res=RES_S_OK;
//---
   m_server=server;
   m_manager = manager;
//--- Prepare the pages list for numerator
   if(RES_FAILED(res=PreparePages(context,manager)))
      return(res);
//--- 
   if(context->user->PermissionCheck(HELLOWORLD_MODULE_ID,HELLOWORLD_PERM_ADVANCED))
     {
      m_info_advanced_count=_countof(m_info_advanced);
      if(RES_FAILED(res=manager->InfoAdvacedGet(context,m_info_advanced,&m_info_advanced_count)))
         ReturnErrorExt(res,context,"failed get advanced info records");
     }
//--- AJAX request for data to display on page
   if(context->request->AJAXRequest())
     {
      return(JSONDataOutput(context,manager));
     }
//--- Display page
   return(server->PageProcess(context, L"templates\\number2.tpl", this, TW_PAGEPROCESS_NOCACHE));
  }

7. Implement method of removing records. It checks whether there is a record in table by specified id, remove it using the InfoDelete method and then generates a response in JSON format. This response simply rebuilds the old data based on changes made.

//+------------------------------------------------------------------+
//| Delete record                                                    |
//+------------------------------------------------------------------+
TWRESULT CPageNumberTwo::OnDelete(const Context *context,
                                  IServer *server,
                                  const wchar_t *path,
                                  CHelloWorldManager *manager)
  {
   INT64    id =0;
   TWRESULT res=RES_S_OK;
//--- checks
   if(context==NULL || path==NULL || manager==NULL)      ReturnError(RES_E_INVALID_ARGS);
   if(context->request==NULL || context->response==NULL) ReturnError(RES_E_INVALID_CONTEXT);
//---
   if(!context->request->Exist(IRequest::POST,L"id") || 
     (id=context->request->GetInt64(IRequest::POST,L"id"))<=0)
      return(RES_E_NOT_FOUND);
//---
   if(RES_FAILED(res=manager->InfoDelete(context,id)))
      ReturnErrorExt(res,context,"failed to delete record");
//---
   if(RES_FAILED(res=PreparePages(context,manager)))
      return(res);
//---
   CJSON json(context->response);
   json << CJSON::OBJECT
        << L"from" << m_start
        << L"count" << m_info_total
        << L"data" << CJSON::DATA;
   if(RES_FAILED(res = JSONDataOutput(context,manager)))
      return(res);
   json << CJSON::CLOSE << CJSON::CLOSE;
   return(res);
  }

 

Add and Modify Data

1. In our project create new PageEdit based on the source code of existing pages. Declare a function that will use methods of creating new and modifying existing records.

//+------------------------------------------------------------------+
//| Page of editing records                                          |
//+------------------------------------------------------------------+
class CPageEdit : public CPage
  {
private:
   IServer          *m_server;
   HelloWorldRecord  m_record;

public:
                     CPageEdit();
                    ~CPageEdit();
   //--- handler
   TWRESULT          Process(const Context *context,
                             IServer *server,
                             const wchar_t *path,
                             CHelloWorldManager *manager);
   //--- functions of displaying
   bool              Tag(const Context *context,const TagInfo *tag);
   //--- СОЗДАНИЕ (Create) и РЕДАКТИРОВАНИЕ (Update)
   TWRESULT          OnUpdate(const Context *context,
                              IServer *server,
                              const wchar_t *path,
                              CHelloWorldManager *manager);
};

2. When requesting data using InfoGet method get a row from the table and call OnUpdate method for it.

//+------------------------------------------------------------------+
//| Process request                                                  |
//+------------------------------------------------------------------+
TWRESULT CPageEdit::Process(const Context *context,
                            IServer *server,
                            const wchar_t *path,
                            CHelloWorldManager *manager)
  {
   TWRESULT res=RES_S_OK;
   INT64    id =0;
//--- checks
   if(context==NULL || path==NULL || server==NULL || manager==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->request==NULL)                                       ReturnError(RES_E_INVALID_CONTEXT);
//---
   m_server=server;
//--- Request data
   if(PathCompare(L"number_two/edit/",path))
     {
      id=_wtoi64(path+16); // 16 - is length of path 'number_two/edit/'
      //---
      if(id>0 && RES_FAILED(res=manager->InfoGet(context,id,&m_record)))
         ReturnErrorExt(res,context,"failed to get record");
     }
//--- AJAX request for data to display on page
   if(context->request->AJAXRequest())
     {
      return(OnUpdate(context,server,path,manager));
     }
   return(server->PageProcess(context, L"templates\\edit.tpl", this, TW_PAGEPROCESS_NOCACHE));
  }

3. Implement the OnUpdate method.

//+------------------------------------------------------------------+
//| Save record in HELLOWORLD table                                  |
//+------------------------------------------------------------------+
TWRESULT CPageEdit::OnUpdate(const Context *context,
                             IServer *server,
                             const wchar_t *path,
                             CHelloWorldManager *manager)
  {
   TWRESULT res=RES_S_OK;
   INT64    id =0;
//--- checks
   if(context==NULL || path==NULL || manager==NULL) ReturnError(RES_E_INVALID_ARGS);
   if(context->request==NULL)                       ReturnError(RES_E_INVALID_CONTEXT);
//--- Fill out fields
   StringCchCopy(m_record.name, _countof(m_record.name), context->request->GetString(IRequest::POST,L"name"));
//--- save
   if(RES_FAILED(res=manager->InfoUpdate(context,&m_record)))
      ReturnErrorExt(res,context,"failed to update record");
//---
   return(RES_S_OK);
  }

 

Routing Rules

1. In module add two new routing rules: one - to delete records, another - to add/edit records.

//+------------------------------------------------------------------+
//| Routing by URL                                                   |
//+------------------------------------------------------------------+
TWRESULT CHelloWorldModule::ProcessPage(const Context *context, IServer *server, const wchar_t *path)
  {
   if(context==NULL || path==NULL) ReturnError(RES_E_INVALID_ARGS);
//---
   if(PathCompare(L"index",path))             return(CPageIndex().Process(context,m_server,path));
   if(PathCompare(L"number_one",path))        return(CPageNumberOne().Process(context,m_server,path));
   if(PathCompare(L"number_two/delete",path)) return(CPageNumberTwo().OnDelete(context,m_server,path,&m_manager));
   if(PathCompare(L"number_two/edit",path))   return(CPageEdit().Process(context,m_server,path,&m_manager));
   if(PathCompare(L"number_two",path))        return(CPageNumberTwo().Process(context,m_server,path,&m_manager));
//--- by default
   return(CPageIndex().Process(context,m_server,path));
  }

Note that when carrying out checks, we are moving from more detailed to less detailed URL. I.e. if we have first checked path number_two, and then number_two/delete and number_two/edit, then after number_two the check would have ended, and there were no further testing of paths. Keep this in mind.

2. Include new page in the module.

//+------------------------------------------------------------------+
//|                                                          TeamWox |
//|                 Copyright © 2006-2008, MetaQuotes Software Corp. |
//|                                        https://www.metaquotes.net |
//+------------------------------------------------------------------+
#include "stdafx.h"
#include "HelloWorldModule.h"
#include "Pages\PageIndex.h"
#include "Pages\PageNumberOne.h"
#include "Pages\PageNumberTwo.h"
#include "Pages\PageEdit.h"

 

User Interface

Now we need to prepare a user interface for data management. In the PageNumberTwo we will add controls that will call C.R.U.D. methods. Also we will add numerator - control that displays tabs with page numbers under table with data. In the PageEdit we will add the records editor.

 

PageNumberTwo: Display, Create and Delete Records

1. Add a new control - PageNumerator.

//--- Create table and fill it with data
var table = TeamWox.Control("ViewTable",table_cfg.id,table_cfg.header, RecordToTable(<tw:info_list/>))
               .AddHandler(top.TableHandlers.Ordering);
var pages = TeamWox.Control('PageNumerator',true,<tw:item_from/>,<tw:items_total/>,<tw:items_per_page/>)
       .Append('onchange',PageNumeratorChanged);

The first argument - is the flag that defines location of numerator below the table border. The second argument (the item_from token) - is the number of first element on the page. The third argument (the items_total token) - it the total number of elements in the table. The last argument (the items_per_page token) - is the number of elements displayed per page. As you can see, the last three parameters are given in the form of custom tokens, that we will implement below.

2. The Append method adds handler to event. In this case, the onchange event is processed by the PageNumeratorChanged function:

function PageNumeratorChanged(startFrom,perPage)
  {
   TeamWox.Ajax.get('/helloworld/number_two/',{from:startFrom},
     {
      onready:function (text)
        {
         var data;
         data = TeamWox.Ajax.json(text);
         table.SetData(RecordToTable(data));
         pages.Show(startFrom,pages.Total(),perPage);
        },
      onerror:function (status)
        {
         alert(status);
        }
     });
  }

The TeamWox.Ajax.get method sends data to server via background request using GET method. The first argument is URL we are sending data to. The second argument are key/value parameters for sending. The key names are used as parameters, the values - as their values. The third argument - is object used for callbacks.

This object, in turn, is created using the TeamWox.Ajax.json method. It converts JSON string into object and vice versa. If you pass a string, the method tries to recognize it as JSON object, if you pass an object - it is encoded into string.

3. In the CPageNumberTwo::Tag implement the item_from, items_total and items_per_page tokens:

//---
   if(TagCompare(L"item_from",tag))
     {
      StringCchPrintf(tmp,_countof(tmp),L"%d",m_info_current);
      context->response->Write(tmp);
      return(false);
     }
//---
   if(TagCompare(L"items_total",tag))
     {
      StringCchPrintf(tmp,_countof(tmp),L"%d",m_info_total);
      context->response->Write(tmp);
      return(false);
     }
//---
   if(TagCompare(L"items_per_page",tag))
     {
      StringCchPrintf(tmp,_countof(tmp),L"%d",_countof(m_info));
      context->response->Write(tmp);
      return(false);
     }

4. In page header add a command that creates new records.

//+----------------------------------------------+
//| Page Header                                  |
//+----------------------------------------------+
var header = TeamWox.Control("PageHeader","#41633C")
   .Command("<lngj:MENU_HELLOWORLD_LIST>","/helloworld/index",           "<lngj:MENU_HELLOWORLD_LIST>")
   .Command("<lngj:MENU_HELLOWORLD_NEW>", "/helloworld/number_two/edit/","<lngj:MENU_HELLOWORLD_NEW_DESCR>")
   .Help("/helloworld/index")
   .Search(65536);

5. In table add a toolbar with two buttons (edit and delete) and also a handler of their pressing.

//--- Function that writes data from manager into table (array)
function RecordToTable(data)
  {
   var records = [];
   for(var i in data)
     {
      //--- Write data into the records array
      records.push([
            {id:'number', content:data[i].id},
            {id:'name',   content:data[i].name,toolbar:[
                                                        ['edit',top.Toolbar.Edit],
                                                        ['delete',top.Toolbar.Delete]
                                                       ]}
      ]);
     }
   //---
   return records;
  }
//--- Create table and fill it out with data
var table = TeamWox.Control("ViewTable",table_cfg.id,table_cfg.header, RecordToTable(<tw:info_list/>))
            .AddHandler(top.TableHandlers.Ordering)
            .AddHandler(top.TableHandlers.Toolbar)
            .Append('ontoolbar',ToolbarCommand);
var pages = TeamWox.Control('PageNumerator',true,<tw:item_from/>,<tw:items_total/>,<tw:items_per_page/>)
            .Append('onchange',PageNumeratorChanged);               

6. When the ondelete event occurs the ToolbarCommand function will open URL (set in routing rules), which will call the OnDelete function to delete records.

function ToolbarCommand(id,data)
  {
   switch(id)
     {
      case 'delete':
        TeamWox.Ajax.post('/helloworld/number_two/delete',{id:data[0].content,from:pages.Item()},false,
          {
           onready:function (text)
              {
               var data;
               data = TeamWox.Ajax.json(text);
               table.SetData(RecordToTable(data.data));
               pages.Show(data.from,data.count,pages.PerPage())
              },
            onerror:function (status)
              {
               alert(status);
              }
           });
        break;
      case 'edit':
        document.location = '/helloworld/number_two/edit/'+data[0].content;
        break;
     }
  }

 

PageEdit: Editing Records

1. In our project create a template edit.tpl for the PageEdit, add necessary HTML tags into it and include TeamWox library to prepare basic environment.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
   <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
   <link href="<tws:filestamp path='/res/style.css' />" rel="stylesheet" type="text/css" />
</head>
<body>
   <script type="text/javascript">
   top.TeamWox.Start(window);
   </script>
</body>
</html>

2. Create page header, that will contain only one command redirecting us to PageNumberTwo.

TeamWox.Control("PageHeader","#41633C")
       .Command("<lngj:MENU_HELLOWORLD_LIST>","/helloworld/number_two","<lngj:MENU_HELLOWORLD_LIST>");

3. Create new object - the input form.

TeamWox.Control('Form',
  {
   action:'/helloworld/number_two/edit/<tw:id />',
   method:'post',
   type:'table',
   buttons:'save',
   items:[
          [TeamWox.Control('Label','Name','name'),TeamWox.Control('Input','text','name','<tw:name />')]
         ]
  }).Style({'width':'500px','margin':'5px'})
    .Append('onformsend',function (){document.location='/helloworld/number_two/'})
    .Append('onformerror',function (){alert('Network error');});

This control has table type display (type: 'table') and button (buttons: 'save'). This button utilizes POST method (method: 'post') to send HTTP request to URL specified in the action parameter.

This table has one row with two columns. The first column displays the text label (object of Label type) with text Name that is tied to the name field. Accordingly, the second column - is the input form (object of Input type) named text, in which the name value is entered. If this is a new record in table, then this field will be blank, so you can enter text. If you edit an existing record - the current value of record is inserted into this field as the fourth parameter using the tw:name token.

If data were successfully sent via the form (the onformsend event), you will be automatically redirected to PageNumberTwo. If error occurs on data sending (the onformerror event), e.g. connection failure, the browser will inform you of this using standard method.

4. Implement previously used tokens id and name in the PageEdit source code.

//+------------------------------------------------------------------+
//| Process token                                                    |
//+------------------------------------------------------------------+
bool CPageEdit::Tag(const Context *context,const TagInfo *tag)
  {
   wchar_t str[64]={0};
//--- checks
   if(context==NULL || tag==NULL || m_server==NULL)  ReturnError(false);
   if(context->request==NULL || context->user==NULL) ReturnError(false);
//---
   if(TagCompare(L"id",tag))
     {
      if(m_record.id>0)
        {
         StringCchPrintf(str,_countof(str),L"%I64d",m_record.id);
         context->response->WriteSafe(str,IResponse::REPLACE_JAVASCRIPT);
        }
      return(false);
     }
//---
   if(TagCompare(L"name",tag))
     {
      if(m_record.name!=NULL && m_record.name[0]!=NULL)
        {
          context->response->WriteSafe(m_record.name,IResponse::REPLACE_JAVASCRIPT);
        }
      return(false);
     }
//--- 
   return(false);
  }
//+------------------------------------------------------------------+

 

UI Demonstration

We have implemented the necessary C.R.U.D. functionality. Now it's time to see how it works. Compile the module, copy templates to server and run TeamWox.

 

Adding Records

1. Go to PageNumberTwo then click command to create new record.

Create New Record

2. Enter some text and click save button.

Entering text for record

Record has been added to table.

New record in table

 

Editing Records

1. On the page move mouse cursor on a record. A toolbar appears with two buttons of editing and deleting records. Select edit record.

Editing Record

2. Edit the record by entering new data.

Modifying record

Record has changed.

Record has changed

 

Numerator

1. Add a few records into table. In the m_info[5] array we've set the number of records displayed on numerator tab. If there will be more than 5 record in table, numerator will create tabs. Each tab will display up to 5 records.

First 5 records

2. When you switch to the next tab the next portion of data will be displayed.

The next portion of data

 

Deleting Records

1. Test the deletion of data. For this click on delete button in table.

Deleting record

2. Record is deleted and data in table are automatically updated.

Record deleted

 

Conclusion

We've considered how to use SDK tools to interact with TeamWox DBMS. Article became quite a big in volume, as it thoroughly covers changes in module source code.


2010.11.04