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.
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); }
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].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).
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); }
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.
//+------------------------------------------------------------------+ //| 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); }
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); //--- }
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); }
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.
2. Enter some text and click save button.
Record has been added to 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.
2. Edit the record by entering new data.
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.
2. When you switch to the next tab the next portion of data will be displayed.
Deleting Records
1. Test the deletion of data. For this click on delete button in table.
2. Record is deleted and data in table are automatically updated.
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.
- How to Add a Ready-made Module to TeamWox
- How to Add Page into TeamWox Module
- Building User Interface
- Interaction with DBMS
- Creating Custom Reports
- TeamWox File Storage - Part 1
- TeamWox File Storage - Part 2
- Setting Up Custom Modules Environment - Part 1
- Setting Up Custom Modules Environment - Part 2
- Search and Filtering - Part 1
- Search and Filtering - Part 2
- Setting Up Online Assistant On Your Site
- How To Create Additional Language Package For TeamWox
2010.11.04