# CVS id string $Id: Brief_description_of_DBHandler.txt,v 1.10 2006/12/08 11:48:38 pjx Exp $

=================================================
Brief description of Python DB Handler:

     July 2006 Wanjuan Yang
==================================================

1. Background:
--------------
Python DB Handler is a single server runs at any one time for a given user. If
you try to start a second instance then the new process will give a message
telling you the port number of the running handler and shutdown itself.


2. Usage:
---------
2.1. Check out all the files in "dbccp4i" from Bioxhit_db repository. 

2.2. Set environment variables.
      a. Set CCP4I_TOP point to CCP4i directory.
         To do this, source ccp4.setup in the ccp4 installation.
         e.g. source /home/wy45/ccp4/ccp4-6.0/include/ccp4.setup 

      b. Set BIOXHIT_TOP point to the Bioxhit_db directory. Also set
         GRAPHVIZ_BIN to point to the directory containing the
         Graphviz dot program (see http://www.graphviz.org) if you want
         to use the viewer application.

         To do this, source Bioxhit_db.setup in Bioxhit_db/dbccp4i/bin.
         e.g  source /home/wy45/Bioxhit_db/dbccp4i/bin/Bioxhit_db.setup

2.3. Run the script in $BIOXHIT_TOP/dbccp4i/bin directory:
      a. dbccp4i  --- run the handler only. can have option 'debug'.
         e.g ./dbccp4i --- run the handler and the output is written in the
                           file dbhandler.log in the directory .CCP4.
             ./dbccp4i debug --- run the handler and the output is on the 
                                 screen.

         Alternatively, go to the directory  $BIOXHIT_TOP/dbccp4i/dbccp4i,
         type:  python dbccp4i.py 

      b. dbconsole --- run the application dbconsole.tcl. This will invoke a
         handler if there is no handler running. Type:
         ./dbconsole

         Alternatively, go to the directory  $BIOXHIT_TOP/dbccp4i/application,
         type:  tclsh dbconsole.tcl
 
2.4. To shutdown the handler: 
      a. If the client applications disconnect the handler cleanly, then the
          handler will stop automatically if no any other clients connect for
          30 seconds. 

      b. Alternative way for shutdowning handler is under development. 
         However, there is a temp script can do the job. Go to   
         $BIOXHIT_TOP/dbccp4i/application, type:
         python handler_shutdown.py

3. Communication protocols:
----------------------------

Messages sent between the handler and client applications are in form of XML.

The communication protocols are now described in a separate document
communications.html.

4. Available commands:
-----------------------
As described above, commands are sent to the handler in the form of XML
wrapping. The following sections list the commands without the XML, in the
form:
command arg1 arg2...

4.1. Commands for interacting with the handler:

4.1.1 DbRequestStatus 
      Get the status of the handler. Should return 'Active'.

4.1.2 DbRegister <username> <pid> [<broadcast_flag>]
      Register client with the handler. This needs to be done before any other
      commands can be issued.

4.1.3 DbSupported <dbtype>
      Test if the handler support def backend or SQLite backend. dbtype should
      be either 'def'/'Def'/'DEF' or 'SQLite'/'sqlite'/'SQLITE'. Return True
      or False.

4.1.4 DbDisconnect
      Disconnect client from handler.

4.1.5 ShutDown
      Command to shut down the handler.


4.2. Commands for database.def backend

4.2.1 DbOpen <project>
      Open an existing project with alias <project>.
 
4.2.2 NewDb <project> <dir>
      Create a new project with alias <project> and in directory <dir>.

4.2.3 DbNewRecord <project> [ <taskname> <status> <title> ]
      Create a new record in a <project> and return job id. 

4.2.4 DbDeleteRecord <project> <jobid>
      Remove a job record in a project.

4.2.5 DbSetData <project> <jobid> <item1> <value1> [<item2> <value2> ...]
      Set the value(s) of one or more items in a given record in given
      project.

4.2.6 Updatetime <project> <jobid> 
      Update time of the job in a <project>.

4.2.7 DbGetData <project> <jobid> <item1> [<item2> [...]]
      Retrieve the values of one or more data <item>s from a job in a <project>  
4.2.8 DbItemExists <project> <jobid> <item>
      Check that <item> exists a job in <project>.

4.2.9 DbSelectRecords <project> <item> <pattern>
      Return a list of record ids in <project> for which the regular
      expression <pattern> matches the value of <item>.
      
4.2.10 ListJobs <project>
       Return a list of job in <project>.
       
4.2.11 DbReturnRecord <project> <<record_list> <item_list> <format>
       Given a list of record ids <record_list>, return a list of descriptions
       whcih are generated from the suppied lists of data items in <item_list>
       and <format>.

4.2.12 DbClose <project>
       Close an open project with alias <project>.

4.2.13 ListProjects
       List all the projects' names which are recorded in directories.def.

4.2.14 DeleteProject <project>
       Delete the reference of a project in directories.def. 
       
4.2.15 GetProjectDir <project>
       Return the project directory name.
 
4.2.16 ListDefDirs
       List the default directories names.

4.2.17 GetDefDir <def_dir>
       Return the default directory. ?

4.2.18 GetNJOB <project>
       Return the last number of record in <project>
       
4.2.19 GetNProjects
       Get the total number of projects.

4.2.20 GetNextJobList <project> <job>
       Get the children job list of a given job.

4.2.21 ImportProject <alias> <path>
       Add a reference of a project(alias and path) in the directories.def
       file.

4.2.22 GetAllLinks <project>
       Get all the links between jobs for a project.

4.2.23 GetLinks <project> <joblist>
       For a particular project, given the joblist, get all the links between
       these jobs.

4.2.24 DbGetListofRecords <project>
       Get all the records for a particular project.

4.2.25 GetAllChildren <project> <jobid>
       For a particular project, given a job id, get all the jobs which are
       children(all level) of this job.

4.2.26 GetAllParents <project> <jobid>
       For a particular project, given a job id, get all the jobs which are
       parent(all level) of this job.

4.3. Commands for SQLite backend

4.3.1 NewDB_sql <db> <dir>
      Create a new database.

4.3.2 OpenDB_sql <db> <dir>
      Given the database name and directory, connect the database.

4.3.3 CloseDB_sql <db>
      Close a SQLite database.

4.3.4 NewProject_sql <project>
      Create a project. Return project id.

4.3.5 NewJob_sql <project>
      Create a new job associated a <project>.
      
4.3.6 SetData_sql <jobid> <item> <value>[<item2> <value2> ...]
      Set one or more items' values in a job.
 
4.3.7 GetData_sql <jobid> <item> [<item2> ...]
      Return the value(s) of one or more items in a job.

4.3.8 GetProjectId <project>
      Return project id for a given project.

4.3.9 GetData <table> <id> <attribute1> [<attribute2>...]
      Given a table name, id and attributes, return the values of the
      attributes. 

4.3.10 SetData <table> <id> <attribute> <value> [<attribute2> <value2> ...]
      Given a table name, id, attributes and values, return True if
      successful, return error message otherwise. 

4.3.11 GetProjectList 
      Return a list of projects in the database.

4.3.12 GetJobList <project>
       Return a list of jobs for a given project.

4.3.13 GetTableSchema <table>
       Return schema for a given table.

4.3.14 GetRowofTable <table> <id>
       Return a row of a table.

4.3.15 NewRecord <table>
       Insert a new record in a table.      

5. Files included in the package:
----------------------------------
5.1. dbccp4i.py --- Main file of db handler.
5.2. manager.py --- Supported module for dealing wiht client & project
     management in the handler. Provide class Manager, Project, Client, User &
     messager and response_wrapper function. 
5.3. DBcommand.py --- Supported module containing commands for Def & SQLite
     back end.
5.4. ccp4i.py --- APIs for CCP4i database backend.
5.5. dbapi_sqlite.py --- APIs for SQLite backend. 

