Help file for administrators of Xtrack

Xtrack is a web-based database system for keeping track of X-ray crystallographic data collection and processing.

The database is a glorified notebook system that allows different people involved in a crystallographic project to remember what they have done, and find out what other people have contributed.

The interface is located at : http://xray.bmc.uu.se/markh/php/xtrack.php
and the main public access page is : http://xray.bmc.uu.se/xtrack
There is a demo version of the program designed to be used as a playground at : http://xray.bmc.uu.se/markh/php/xtrack_demo.php
Users logged in as 'Demo' (pass word 'DemoPass') can add and modify records here without having to worry about trashing real data.

You should be familiar with the user help file before continuing with this administrators guide.

System overview

The system is based on a PHP web interface backed by a PostgreSQL database. (See schematic)
The structure of the database is defined by SQL commands contained in the file xtrack.sql, and this can be loaded with the command :
/usr/local/pgsql/bin/psql markh -f xtrack.sql (or interactively "\i xtrack.sql" instead of cmdline parameter)
Doing this erases any user data that was in the database, so at the moment, during development, I put any user data by hand into a dump file, which then gets called from xtrack.sql.

Design

The system is based on information entered for a collection, an entity which corresponds to a set of X-ray data collected for a particular structure determination. For each collection, a few dozen data items are stored, and for convenience these data items are split into a handful of pages. Each data item corresponds to a single table in the database, and each page table contains pointers to the data tables assigned to that page.

The database contains as much information as possible about prompting, formatting and data typing, so that generic reusable PHP code can be written. The idea is that future administrators can easily add and subtract pages and fields of data using standard SQL commands in the database, and need never touch the PHP server code.
To this end the first 10 entries in each table contain fake entries.
Row 0 Contains nicely-formatted versions of the column names.
Row 1 Contains the format to be used when prompting for the field, or a pointer to a table from which to make a select list.
Row 2 Contains dummy data, for example the project named 'any' used in search options.
Row 3 Contains optional flags that associated with each field.
Row 4 Contains other dummy data, for example 'template', used as a starting point for new entries.
Row 5 Contains brief help that is flashed up in the browser status bar.
Row 6 Contains strings describing the units associated with each field.
Rows 7 through 10 are reserved for future system use.
Some dummy names are actually preceded by a space, to make sure they filter up to the top (default) position when sorted.

Despite this plan to encode everything in the SQL part of the system, there are some functions that cannot easily be put there. These include code for extracting data from log files, and JavaScript code for checking data input, both of which are coded in the PHP files. Still, a lot of new functionality can be added without touching these luxury items.

Data pages : Structure, Chemistry, Expression, Crystallisation, Collection, Data_reduction, Structure_solution, Refinement, Analysis, Deposition.

Other major tables : users, projects, groups, laboratories, summary

Other maintenance tables : pages (list of page tables), all_tables (list of all tables used for dumps), log (accumulates access data), serial_* (keep track of counters for new entries), also various tables of input options.

The summary table The summary table contains a list of fields that are commonly included in a publication. The fields are listed as "tablename.itemname", in the order in which they should be listed. The special tablename "label" causes a full-label to be included in the listing, whose text corresponds to the ficticious item that follows. The synch_summary table This table is like the summary table, but contains a list of fields used in a synchrotron report.

Select tables These are tables that may be specified in the format record of a table, if a selection box is wanted for input. In that case a selection box is constructed from the contents of the table specified, alongside a free-text input box. In the format record, the select table is specified as the name of the table preceded by the string 'table_'. The selection table may contain multiple fields, and if so, the extra fields can be used to set values for other variables, if the name is preceded by the string 'ref_'. For example, the table beamlineOptions contains the options field, but also a ref_site field, which can be used to set the site variable, and a ref_detector field, so when 'ID14' is selected for the beamline, the site can automatically be set to 'Grenoble', and the detector is set to 'MAR CCD'.

Security : Currently, security is based on group membership. When a user is registered, they are assigned to a number of groups, and they may read and write data only within those groups. Users may be members of more than one group, and they become so by issuing separate 'Add User' commands, which means that they can have different passwords associated with the different groups. If no password is entered when adding a user to a new group, then the password assigned to the user for that group will be the same as for their first group. Data records only belong to a group, not a user, although the identity of the last user to update a record is stored internally. Any user in the group has the same rights to read and modify data as the user who originally entered the data. If more security than this is wanted, a group with a single member can be created.

The user 'guest' exists for outsiders to be able to play with the system, but they can only see dummy data within the guest group, and cannot add data. General users can add projects within their group, but only members of the PRIV group can add groups or users, or delete anything. Records belonging to the PUBLIC group can be read by anyone, and this membership is used, for example, for the template records that are copied when a new structure is added. Members of the READALL group have read access to all files, but cannot edit them, unless their username is in the responsible field of the Structure page. This allows a generic user to collect data for any group without being able to modify old data.

Adding new users and groups

General users can be added using the 'Add User' entry form that is available to administrators with 'PRIV' permission, but adding new privileged users must be done directly in the SQL database.

Modifying the SQL database

Most of the structure of this system is encoded in the database, rather than in the executable code, which means that fields and pages of information can be added, deleted or changed without touching the PHP code.
Each page of data associated with a collection contains fields for each individual piece of information, and records 11 and upwards are associated with individual data entries. The first 10 records contain information that the interface uses for labelling fields, formatting, and setting defaults.
If a new field is added to a page, these records should also be updated. For example, if you decided that the deposition page should also store information about where the celebratory party will be held, you would submit the following SQL commands to the database manager :


\c xtrack
ALTER TABLE deposition ADD COLUMN party VARCHAR(100); // Add the new field
UPDATE deposition SET party = 'Party Location' WHERE sequence = 0; // Set the label for prompting
UPDATE deposition SET party = '12' WHERE sequence = 1; // Set the default width of the field when prompting
UPDATE deposition SET party = 'nocopy optional jscript_party' WHERE sequence = 3;  // Set behaviour flags
UPDATE deposition SET party = 'Party details, perhaps a link to URL' WHERE sequence = 5;  // Set help string
UPDATE deposition SET party = 'No party planned' WHERE sequence = 4;  // Data for template
\q

Alternatively you can edit the xtrack.sql script to reflect your changes, and then reload the database from scratch. In order to do this, you should use the 'dump' option avaialble to privileged users, which will produce a file of SQL commands that will reconstruct user data. The database must be reinitialised with xtrack.sql before restoring this user data, so be careful.

Some functions are inevitably hard coded in the PHP script. Those that extract data from external files can be found under the comment "Extraction Functions", and have hopefully intuitive names such as "extract_from_cns_pdb()". JavaScript functions that are intimately related to the function that constructs form option lists are included immediately under the routine form_options(), and all others are created by a universal JavaScript function generator called writeJscriptFunc(), which takes as a parameter the name of the name of the function specified in the database entry, and creates a function with that name whose content is hard-coded within writeJscriptFunc().

Developing the SQL database

For more extensive changes to the database, the following procedure is more efficient :

Edit xtrack.php to temporarily disable use of the system.
Make a manual dump of the current user records (eg to /tmp/dump_xtrack_20020829.sql)
Edit xtrack.sql to point to the chosen database (either xtrack_new for testing, or xtrack for final version)
Run update_db.csh

Feeding and dumping the database

Data can be read and written to the database using the provided XML functions. When groups, projects or collections are listed, there is sometimes an option to write out the selected items to an XML file containing all the information needed to move the item to another database or simply to save it. Because Xtrack has a flat schema, these XML files have a very simple structure with one root entity under which there will be a series of entities corresponding to tables in the Xtrack database. The dtd for these entities is at : http://xray.bmc.uu.se/markh/php/xtrack.dtd.

Privileged users have the option to read in these XML files through the "read XML" option on the main page.

Misc

Note the following :

1) All data fields are stored as VARCHARS, so that they can be handled consistently. Even numbers and dates are stored in these strings, and interpreted later.

2) Row three in each table contains a set of optional flags with the following meanings :

'nocopy' means that even if a user asked to copy an existing collection as a template, this data item will not be copied (used for dates and other data that will most likely need to be changed).
'optional' means that the interface will not object if the user leaves that item empty.
'newline' forces the next item to be on a new line when displayed (for prettier alignment).
A flag of the form tie_otherfieldname means that the field for which the flag is set is dependent upon the contents of 'otherfieldname', for example that may be a flag which determines whether or not the first field should be displayed. The details of the relationship are hard-coded in the PHP script.
A flag of the form jscript_functionname means that there is a JavaScript function hard-coded in the interface that should be called when the contents of the field are changed by the user. That function may, for example, check for sensible input or check for consistency with other fields.

3) On some pages there is an option to upload files to a central archive directory on the server, the idea being to store some data more permenantly than simply having a link to users' own copies of the files. In principle this could be applied to all external files (refinement log files etc), but we have chosen to be selective, and only store photographs of crystals and gels, and protocol files (which are often in Word or PowerPoint format). When this facility is used, the chosen file is uploaded to the archive area on the server, and put under a subdirectory named after the user's group, in a file named with the format : project_collection_field_number.ext. eg a protocol might be stored as : server:xtrack_archive/cellulase/cbh1_lig1_protocol_1.ppt. When this page is subsequently viewed, the linked text is abbreviated to Archive::lig1_protocol_1.ppt, and if the browser is set up correctly, will cause PowerPoint to be fired up when the link is clicked. An image on the other hand, will have a thumbnail inserted inline, which is linked to the full-sized image.

User help file

Installation file

Mark Harris