ExSite Manual: Database Administration

The WebDB Database Administration Tool

Introduction

WebDB is the application that is used for:

WebDB is a flexible and versatile tool that can often be used to reproduce most of the functionality of other database-driven web applications, if you understand the database structure. For example, most of the functions of the WebSite Editor can be reproduced using WebDB. This makes it a useful fall-back tool, as well as a generic administration tool for any web application. However, since WebDB does not know the detailed business logic of each web application, the interface to the data will necessarily be less user-friendly and somewhat more technical than a custom-built control panel.

WebDB is built upon ExSite's database-access toolkit, which has the following features:

Part 1 of this section is a User Guide, explaining how to use the WebDB tools. Part 2 is more of a Developer's Guide, explaining how to leverage the WebDB tools for use in other web applications.

Contents

Part 1: WebDB Users' Guide

Getting Started

There are several ways to invoke the WebDB tools:

  1. From the Adminstrator Webtop, launch the WebDB application by clicking on its icon:

  2. Run the webdb.cgi CGI program directly.

  3. Since WebDB is just a front-end to the database management tools in the ExSite kernel, many applications will effectively be using the same tools to do their basic reporting and form management tasks. These tools could be embedded into other control panels, or into the website front-end itself.

Overview of WebDB

The WebDB tool looks like this:

The important features are:

  1. full record reports: displays the full contents of a database record.

  2. option buttons: small icons show you your database management options for any given table or record (see below).

  3. summary reports: multiple records are listed in a summary report, which shows only selected fields from the record.

Database Management Options

The following icons will be displayed when you have permission to perform their respective operations on a data object (table or record):

Data Structure

WebDB uses a simple but reasonably versatile relational data structure that suits the requirements of ExSite, and many other common database-driven applications. It makes the following assumptions:

There is nothing special about this, and it can be easily implemented on a wide variety of database engines. Some database engines are a little more flexible in how they manage data relations. For instance, SQL databases have no requirement for a unique record ID, and so data relations are not necessarily made by associating record IDs. However, SQL databases all have the option of having "serial number" or "auto-incrementing" primary keys for a table. WebDB prefers to work with this sort of table, since it ensures that records are uniquely addressable, which might not otherwise be the case. Your database does not need to be structured this way, however, the ability of WebDB to navigate and manage it may be hampered otherwise (in which case, you will have to consider building your own control panels to manage the affected data).

Data Relations

There are two standard types of data relation that are easily managed, navigated, and viewed with WebDB:

The standard ExSite database that comes preconfigured with the ExSite distribution has examples of both of these relations. To see examples of both, follow these directions:

  1. From the list of tables on the first screen of the WebDB tool, select the view icon, , for the "Sites" table.
  2. From the list of websites, select a site that is known to contain a number of pages, and select its view icon, .

This will take you to a full record display for the selected site. Underneath this you will see a number of summary reports for the records that are related to this site.

Summary reports for "Pages", "Templates", and "Libraries" are examples of many-to-one relations. The headings for these summary reports reflect these relations, eg. "Pages for Site 'X'". Simply click on the view icons for these records to "burrow down" deeper into the data heirarchy.

The summary report for "Member-Site Links" shows an example of a many-to-many data relation. These records tie websites to administrators. Simply click on the administrator names to jump to their records. When you do this, you will also find that the "Member-Site Links" table appears again under the member table. However, in some cases it will display a different set of records than you saw from the Site side.

Automatic Reporting

The WebDB tools automatically build reports for single records and groups of records, as described above. These reports can be formatted various ways, although "table" formatting is most common. Tabular reports are pre-sorted using the default sort key for the table (shown by a "+" sign in the table heading). You can re-sort by any other column heading by clicking on the heading. You can also reverse the sort on any column heading by clicking on it again.

Long reports are broken into pages. The number of records that are shown on any given page is defined by default in the ExSite configuration file. You can jump to other pages in the report by clicking on "previous", "next", or a page number. If you re-sort a multi-page report, you will be positioned at the same page number, which may contain a completely different set of records than before you sorted.

In cases where non-administrators are browsing data using the WebDB tools, the options for operating on the records will vary depending on their access levels. Data that they own (ie. data that is tied to their member record) will have more options available for it, than data which is owned by other users (which may be read-only). In addition, some fields may display as "(no permission to view this data)" if the user does not have sufficient read access to the field.

Searching

To easily search for records in a table, click on the search icon beside the table name. The resulting form contains inputs for all searchable fields. WebDB will search for data matching ALL of the substrings or selections you enter.

It is also possible to search all fields of all tables for a single term. WebDB does not provide you with a convenient link for this function, however. To use this "general search" tool, simply invoke the webdb.cgi?action=query URL.

Automatic Form Processing

ExSite will automatically build, populate, and process forms for inserting, updating, and searching records. No special action is required to make use of these features when using the normal WebDB tools. Simply enter/edit your data, and click Submit. Information on adapting these tools to other web applications is provided in Part 2.

WebDB can highlight required fields in different ways, depending on the website and its configuration. Examples include boldfacing the field label, placing a star beside the field label, or modifying the field label using stylesheet rules. These highlighted fields are required, and the form will be rejected if no valid data is provided for them.

Form Help

Tooltip-like help can be defined for any database field. If WebDB detects that help text is available for a database field, it will automatically place a help icon beside the form input. Clicking on this icon will pop up a small help dialog that gives you more information about the field and the types of inputs it expects.

Form Validation

When you submit a form, it may be rejected by automatic validation procedures. These tend to spit out a handful of different messages, depending on the problem detected:

ownership mismatch - you do not have permission to update this record

This message will be given to non-administrator users who attempt to edit a record that they do not have access to. Administrators usually have permission to edit all records, and will not normally encounter this message.

ownership mismatch - you do not have permission to link to ...

This message will be given to non-administrator users who attempt to link to a record that they do not have ownership of. Administrators usually have permission to link to all records, and will not normally encounter this message.

"ABC" is not a valid value for "XYZ"

You entered a value "ABC" for field "XYZ", but it does not conform to the values this field will accept.

filename is too large (48K max)

You are attempting to upload a file that is too large. The maximum size may vary, depending on the configuration of the site and the field.

you must provide a value for...

You left a field blank, but it is a required input.

form is missing required field...

There is a required input for this record, but it was not present in the form in the first place. This indicates a bug in the configuration of the site or the application you are using, since WebDB if left to its own devices should always include required fields.

(other)

In special cases, special validation error messages can be kicked back by some tables. These special cases are not covered here.

Part 2: WebDB Developers Guide

This section describes how to co-opt the WebDB tools and functions to make them useful to other web applications. If the data structure of a web application reflects the application's logic in any appreciable way, then you can often build a functinoal administrator interface to your web application in minutes, simply by invoking the WebDB tools on the tables of your web application. Often these tools will even be useful for the user-driven front-end of your web application.

CGI interface to WebDB

You can invoke the various functions of the WebDB toolkit as simple CGI programs, as follows:

Inspecting Data

To view the tables in the database

/cgi-bin/webdb.cgi

To view all records in a particular table

/cgi-bin/webdb.cgi?action=list&tab=TABLENAME

This can serve as a rudimentary control panel for a small web application with a core table that all data depends upon.

To view a particular record in a particular table

/cgi-bin/webdb.cgi?action=view&tab=TABLENAME&id=RECORD-ID

Entering/Modifying Data

To insert a new record into a table

/cgi-bin/webdb.cgi?action=new&tab=TABLENAME

To edit an existing record

/cgi-bin/webdb.cgi?action=edit&tab=TABLENAME&id=RECORD-ID

The following additional options are accepted when doing inserts or edits. These parameters must be provided using URL-encoding, eg. &parameter=value&....

ptab=TABLE&pid=ID
specify a parent table and ID to relate this record to
reply=URL
specify a URL to go to once the record has been modified
hide=field1,field2
do not include the listed field names in the form. This is only to make the form less cluttered and easier to understand in cases where not all fields are relevant. It provides no security value, since anyone can edit the URL to re-enable the hidden fields.
show=field1,field2
include only the listed fields in the form. This is only to make the form less cluttered and easier to understand in cases where not all fields are relevant. It provides no security value, since anyone can edit the URL to show additional fields.

CMS interface to WebDB

You can invoke the various functions of the WebDB toolkit using special content management tags embedded in ExSite-generated web pages, as described below.

Inspecting Data

To view the tables in the database

<!--&WebDB()-->

To view all records in a particular table

<!--&WebDB(action=list&tab=TABLENAME)-->

This can serve as a rudimentary control panel for a small web application with a core table that all data depends upon.

To view a particular record in a particular table

<!--&WebDB(action=view&tab=TABLENAME&id=RECORD-ID)-->

Entering/Modifying Data

To insert a new record into a table

<!--&WebDB(action=new&tab=TABLENAME)-->

To edit an existing record

<!--&WebDB(action=edit&tab=TABLENAME&id=RECORD-ID)-->

Web Application Control Panels

If a web application has a core table on which everything depends, a WebDB report showing the records in that table could serve as the basis for a cheap-and-dirty administrator control panel for the application. If this works for you, then there is no need to develop a special control panel for your web application. Instead, simply have the 'ControlPanel' request to your web application's ioctl() method return a URL to webdb.cgi with appropriate arguments to select the table (and, perhaps, record) that is the basis for managing the application.

For example, if you have an event management application, and all the data for an event is tied to a parent record in the "event" table, then the administrator can get a rudimentary control panel to all events using this:

webdb.cgi?tab=event

If you have access to one event only, then you can get a control panel for this one event using something like:

webdb.cgi?tab=event&id=999

Security

In principle, any user can type a WebDB URL into their address bar and invoke the requested database management function. However, WebDB includes access control logic to prevent users from executing functions or accessing data they are not permitted to.

Data security is controlled by two factors:

Access Level

Every user has an access level. By default, access level 0 corresponds to an unknown user, access level 1 corresponds to a regular, authenticated user, access level 2 corresponds to a regular site manager (eg. someone who is permitted to update site pages), and access level 3 corresponds to the site administrator. The minimum access levels to read, insert, update, delete, and search are defined for every table in the database. If a user does not have this minimum access level, the operation is rejected.

Furthermore, the minimum access levels to write to each field in a record is also defined; if a user does not have the necessary access level to write to a field, that field will not be displayed in an automatically-generated form.

Access levels can be redefined to include more levels, if required.

Record Ownership

Records that contain pointers to other records are said to be "owned" by those records. These records are in turned owned by other records. A user is said to own a record if this ownership chain can be traced back to their own user record. Regular users are only permitted to modify or link to records that they own. Attempts to edit or link to unowned records will result in a validation error.