########################################################################## # # # CLAIMS_DB_LISTENER V0.22 # # # # PROTOCOL DOCUMENTATION # # # # DOCUMENT DATE: 20 JUNE 2009 # # # # COPYRIGHT B TASKER 2009 # ########################################################################## Contents --------- - About - Dependancies - Request Protocols - Method Specific Protocols - Program Output About ----- Claims_DB_listener.sh is a BASH based CGI script intended to make it possible to use a Database server on a network. Without the functionality provided by this script, Claims_DB is a standalone database and must be run on the same system as whichever front end is being used. This script makes it possible to serve requests from remote machines, allowing Databases to be accessed from multiple workstations, using multiple front-ends. This also allows a wider choice of front-end application, developers can, if they wish, write front-ends in OS Specific code and simply send the requests to this script which would be listening on a Linux/Unix server. Dependancies ------------ This script is written as a CGI script, which means that it requires a seperate daemon to be installed and running. It is left to the choice of the user which HTTP daemon will be used, though it is expected that it is likely to be a lightweight server such as lightHTTPD. So long as the daemon of choice is configured to allow CGI scripts, and the user that the daemon runs as has read/write access to Claims_DB and its files, the choice is of little consequence. It is expected that the chosen Daemon would employ the use of SSL, whilst sensible from a precautionary aspect, it is not necessarily required. All URL examples in this document will assume that SSL is being utilised. The use of SSL does not affect the Request Protocols, except that the request URL would be prefixed with https rather than http. Request Protocols ------------------ The script expects to receive requests in a certain format. The script can receive its request using either HTTP GET or HTTP POST protocols. It will automatically detect which type has been used, and action the request. It is recommended that all record insertions take place using POST requests to avoid limitations on the number of characters that can be used in a Request URI. The protocol used for each request is dependant on the desired action. There are however a number of variables that must be specified across nearly all the desired actions. These are - METHOD The METHOD variable defines exactly which action is desired. An integer is expected, and should be one of the following 1 - Insert a record into the database 2 - Output complete table 3 - Output Table Header Row only 4 - Run Query and return matching records 5 - Run Query and return matching Line Numbers only 6 - Run Database Backup 7 - Get Next Primary Key 8 - Run Select Distinct style query on named column 9 - Delete record on given line number 10 - Stream a full Database backup to client 11 - Return a total record count for the selected database 12 - Return a total record count for the selected table 13 - Clear Temporary Directory 14 - Generate a Signature for the named table (Checksum function) - DBNAME DBNAME must be defined for all requests except METHOD 6. The name given should be the database name as defined within Claims_DB - TABLE TABLE must be defined for all requests except METHOD 6. The value given must be the table name as defined within Claims_DB (e.g. Catalogue, not Catalogue.csv) The system also expects all requests to be URL encoded. Method Specific Protocols -------------------------- - METHOD 1 - Insert a record into the database When submitting a request using METHOD 1 the script expects to receive a number of variables within the Request URI. In addition to the variables mentioned above, the script also expects values for up to 20 Columns. The variable name for each column is tied to the column it will be inserted into. For example To insert data into Column 1 define it as Variable COL1 To insert data into Column 20 define it as Variable COL20 Example request To create the following record in a Table called Table1 within a Database called Sample, 1,Example Data, The Request URL would be https://someserver/cgi-bin/Claims_DB_listener.sh&METHOD=1&DBNAME=Sample&TABLE=Table1&COL1=1&COL2=Example+Data - METHOD 2 - Read Full Table When submitting a request using METHOD 2, the script requires no variables other than DBNAME and TABLE. Once the request is processed, the system will return all data within the specified table. It will not, however, return the header row. This can be obtained using METHOD 3 Example request To retrieve all data from Table1 in Database Sample we would submit the following request https://someserver/cgi-bin/Claims_DB_listener.sh&METHOD=2&DBNAME=Sample&TABLE=Table1 - METHOD 3 - Read Table Headers A METHOD 3 request requires only the Database and Table names to be specified. Thus a request would appear as follows https://someserver/cgi-bin/Claims_DB_listener.sh&METHOD=3&DBNAME=Sample&TABLE=Table1 - METHOD 4 - Run Query METHOD 4 requires two additional variables, QCOL and QSTRING. QSTRING should be defined as the string that the user wishes to query the database for. QCOL defines the Column to query. So to search for the String "Example Data" in Column 2 of Table11 (Sample Database) we would use the following address https://someserver/cgi-bin/Claims_DB_listener.sh&METHOD=4&DBNAME=Sample&TABLE=Table1&QCOL=2&QSTRING=Example+Data and would expect to be provided with all records containing the query string in Column 2 In order to speed up certain Queries, the variable PRIMKEY can be set to 1. This notifies the system that the query string is unique (e.g. a Primary Key) so the query will end as soon as a record is found. This is barely noticeable in small tables, but is very noticeable in especially large tables. An example call would be; https://someserver/cgi-bin/Claims_DB_listener.sh&METHOD=4&DBNAME=Sample&TABLE=Table1&QCOL=2&QSTRING=Example+Data&PRIMKEY=1 - METHOD 5 - Query Line Number METHOD 5 requests require the same input as METHOD 4, but will only return the line numbers of any matching data. In order to speed up certain Queries, the variable PRIMKEY can be set to 1. This notifies the system that the query string is unique (e.g. a Primary Key) so the query will end as soon as a record is found. This is barely noticeable in small tables, but is very noticeable in especially large tables. - METHOD 6 - Run Database Backup METHOD 6 does not require any variables set, except for the METHOD variable. It will generate a backup of the database on demand, and will return no status code until the backup is complete (or has failed) - METHOD 7 - Query Primary Key METHOD 7 requests calculate the value of the next available Primary Key, and return that value. This request requires one additional variable to be provided - QCOL Much like in METHOD 4 & 5 requests QCOL defines the column to examine, in this case defining the column that contains the primary keys. - METHOD 8 - Select Distinct Query METHOD 8 requests require QCOL to be defined as a column number. The system will then generate a SELECT DISTINCT style query returning all values within that column, with duplicate values removed. - METHOD 9 - Delete Record METHOD 9 requires variable LINENO to be defined as the line number of the record that should be deleted. The line number can be obtained by using a METHOD 5 Request. The system will not seek confirmation of the deletion, it is expected that the front end making these calls would confirm the action before placing a METHOD 9 Request. - METHOD 10 - Create and Stream Backup METHOD 10 requests a Gzipped Tarball containing a backup of the full database. This is then returned as the programs output, allowing the user to download the abckup to another location. This functionality must be activated within the Claims_DB.conf file. This is done by setting the variable BACKUP_DOWNLOADS to Y. The default is to have this method disabled as a security precaution, however it could be a useful function for creating remote backups. If the METHOD is requested, but is disabled, the system will simply return INHIBITED. - METHOD 11 - Return Database Row Count METHOD 11 requested a total record count for the database. The program simply returns a number which is the total number of records in the database (excluding header rows). METHOD 11 simply requires the variable DBROOT - METHOD 12 - Return table Row Count METHOD 12 requested a total record count for the table. The program simply returns a number which is the total number of records in the table (excluding header rows). METHOD 12 requires both TABLE and DBNAME. - METHOD 13 - Clear Temporary Directory METHOD 13 places a temporary bar on all requests and clears all files out of the temporary directory. Having tidied up it then re-allows connections. Any connections received whilst the bar is in palce are not rejected but simply wait until the bar is lifted. - METHOD 14 - Generate Table Signature METHOD 14 requires DBNAME and TABLE to be defined. It then returns a checksum for the specified table using whichever program is defined in the Claims_DB configuration file. This can be used to implement caching on third party applications. Program Output --------------- The output of the script depends on the request being made. If data is being requested then only that data will be output. However if an error is encountered, the program will output Claims_DB's error code. In the case of METHOD 1 & 6 requests, the system will output either SUCCESS or FAILED. Should a bad request be made the system will output ERROR. This can be distinguished from the equivalent error code in Claims_DB which is BADREQUEST One Further Possible Output is INHIBITED. This will be returned if the user tries to stream a backup (Method 10) and the functionality hasn't been activated in the Configuration file. ########################################################################## # # # DOCUMENT END # # # ##########################################################################