• Flash sample
• Download source files - 145 Kb
  
  

This application is a sample of one of our large projects of interaction between MS Visual FoxPro and MS SQL Server.

KioskManager represents a simplified part of our large project on management and administration of mobile payment kiosks. We have taken a project’s part responsible for providing definite persons with access rights to a definite kiosk depending on their roles and implemented it as a completed application.

We use MS SQL Server 2000 as a DB server. As for environment for the client application development, we used MS Visual FoxPro 8.0 interacting with MS SQL Server by means of OLEDB using the Pass-Through technology.

We used the Pass-Through technology instead of Remote Views due to the following reasons.

Comparative analysis of the technologies shows that it’s not possible to use Remote View to build a high-performance client-server application. It is caused by the reasons:

• Only the DML (data manipulation language) commands - SELECT, INSERT, UPDATE, DELETE - can be performed on the server and the possibility of their performance is very limited in comparison with Transacat-SQL because the correlated and nested queries, the UNION expressions, server functions in queries, etc. are not supported.
• Stored procedures can’t be performed on the server.
• The Remote View technology does not guarantee sufficient database security since it does not allow usage of the security technologies that use views, stored procedures and application roles.
• The Remote View technology does not allow transaction management. Transaction integrity is guaranteed only within a separate INSERT, UPDATE, DELETE commands, i.e. only the implicit transaction is supported.

Besides, the application uses WinAPI32 functions to calculate hash code of the Qualified Person password that is stored in the corresponding column of the QualifiedPerson table.

The project objective is to provide a convenient and reliable interface for managing the Qualified Person access permissions for kiosks servicing.

According to business rules the persons may have 3 roles:

• Collector;
• Technician;
  

• Collector and Technician.

According to the rules the access permissions may be assigned the following values:

• Collector -> Collector;
• Technician -> Technician;
• Collector and Technician -> Collector and/or Technician.

Besides, a kiosk has an owner, a Qualified Persons responsible for its servicing, and an Access Permission for each definite person to access each definite kiosk.

Records navigation is performed by selecting an item from the list of string equivalents of records identifiers of a table depending on the current page related to the table. And 2 lists are used for navigation in the AccessPermission table; the first one is used to select the kiosk name, then the Qualified Persons serving the kiosk are displayed in the second one. If to select a Qualified Person in the second list, information about the person becomes available. Besides, a role that a Qualified Person can’t perform can not be selected in the AccessPermission page, i.e. the role that is not stated for the Qualified Person in the table.

Working in the multi-user mode, user can use a special button to synchronize local copy of data with the data stored on MS SQL Server.

The MS SQL Server control of integrity and consistency is used to support integrity and consistency of data. Before being sent to server, the data is checked to meet definite criteria and is modified. For example, when roles in the QualifiedPerson are changed, the roles in the AccessPermission table are automatically modified using explicit transactions both on server and local copy.

Besides, at any moment user can turn to another DB of this kind and to work with it, even if it is stored on another server.

The functionality enhances ease of use and robustness.

This sample uses the DB consisting of 4 tables: Kiosk, Owner, QualifiedPerson, AccessPermission. Presented in figure 1 is their relationship diagram.

Figure 1 – The Relationship Diagram

MS SQL Server authentication is used to authenticate user. Its form is presented in figure 2.

Figure 2 – The Authentication form

For records addition, editing and deletion and for synchronization with the DB server a set of buttons located in the right top corner of the form is used. Besides, one may use mouse double click on a list item to turn to the edit mode and edit the item information.

Presented in figure 3 is the “Kiosks” page intended for the manipulation of records of the “Kiosk” table. Kiosk’s owner is selected from the list, with the Owner table being the data source for the list. It makes impossible to enter a non-existing owner.

Figure 3 – The Kiosk Page

The “Owners” page presented in figure 4 is intended for the “Owner” table records manipulation. As shown in the picture, this table presents only 2 owners that can be selected as kiosk’s owner on the “Kiosk” page.

Figure 4 – The Owner page

Presented in figure 5 is the “Qualified Persons” page intended for manipulation of the “QualifiedPerson” table records. One or both checkboxes should be selected to enter a role. To enter or change password, a special form is displayed allowing old password to be checked and new password to be entered. The form’s feature is the password entering being performed in the string representation while the password’s hash code is written to the database instead of the

password itself. The old password verification is performed the same way: at first, the password entered by user is hashed and after that the hash codes are compared. The MD5 cryptoalgorithm is used to calculate hash code. When the Qualified Person’s role is changed, the person’s role is automatically changed in the AccessPermission table (both for server and client). This operation is performed within a transaction and both these operations are cancelled in case of failure.

Figure 5 – The Qualified Persons page

Presented in figure 6 is the “Access Permissions” page intended for manipulation of the “AccessPermission” table records. As shown in the picture, 2 lists of kiosk names and persons serving them respectively are used for records navigation. When a kiosk name is selected from the first list, the persons serving the kiosk

are presented in the second list. And when one of them is selected, in its turn, the AccessPermission information is displayed. When editing a record or adding a new one, the role that cannot be performed by the selected person becomes disabled and cannot be selected.

Figure 6 – The Access Permissions page

Available for downloading are the project files (in the root directory), all needed forms (in the FORMS directory), button icons (in the GRAPHICS directory) and procedure files sorted by forms and pages (in the PROGS directory), and application menu (in the MENUS directory).

Besides, SQL-script is available for downloading (in the SCRIPTS directory). Being executed, it creates a new “Kiosk Manager” database with four tables mentioned above on MS SQL Server and fills them with some data.