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.
There are several ways to invoke the WebDB tools:
The WebDB tool looks like this:
The important features are:
The following icons will be displayed when you have permission to perform their respective operations on a data object (table or record):
If this option is selected for a full table, then you will be taken to a summary report of the records in that table. The report will be sorted by the default sort key for that table, but may be resorted by clicking on the column headings. The report will be broken into pages, if there are more records than will fit on your screen at once.
If the view option is selected for an individual record, then you will be taken to a full record report for that record. This will display all fields that you have permission to view. It will also find all relational data from other tables that is tied to this record, and display that in a summary form. This is how you navigate your data relationally.
This brings up a form, allowing you to modify any of the record's data in those fields that you have permission to write to. Other fields will not be displayed in the form.
This actually builds a form to insert a new record, but copies the data from the selected record into the form as defaults. If you submit the form without changes, it will effectively copy the record. However, it is usually more useful for inserting multiple similar records, since you only have to enter the data that differs from the original.
Note that this only copies an individual record, not the relational data that may be tied to that record. The copied record will have the same relational pointers to its "parent" records as the original record. However, other records that pointed to the original will not be duplicated to point to the new copy as well.
The delete function marks the selected record for deletion (but waits for you to confirm). It also searches out all relational data that is tied to the selected record, and all records tied to those, and so on. All of these are marked for deletion, and all will be removed permanently if you confirm the deletion. The confirmation screen includes links to every selected record, in case you want to inspect them before confirming.
The search function throws up a form with inputs for every searchable field. Enter the data you want to match to in the fields of interest. Your selections are interpreted as substrings, and will match to any value that contains the string you entered. Every record that matches all of your inputs will be selected and displayed in a summary report.
In the case of MySQL databases (the default), the matches are case-insensitive.
This function displays a blank form, allowing you to enter data for a new record.
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).
There are two standard types of data relation that are easily managed, navigated, and viewed with WebDB:
This is a simple data relation, where any number of records can associate themselves with a single record. For example, a person could have many email addresses. Each address associates with one person, but that person has many email addresses. We sometimes view this data hierarchically, and treat the person as a "parent" record, and the email addresses as "child" records.
Inputting a many-to-one relationship is straight-forward. When a field is defined to contain a pointer to a record in another table, the field will automatically be setup to provide an option menu to select from the available records to point to. You simply select the appropriate record to point to from those that are given. If you opt to enter a new record from one of WebDB's summary reports of related records, the data relationship will automatically be set for you.
Many-to-one relationships are automatically displayed by ExSite when you view a record report. Any fields that contain record pointers are hotlinked to the "parent" record. Simply click on the field value, and you will be taken to the "parent" record. To view the "child" records, go to the full record report. All of the "child" records of the viewed record will be displayed in summary reports underneath the main record.
This is a more complex data relation, where any number of records can associate themselves with any number of records. For example, a person could have many telephone numbers, but some of those telephone numbers could also be shared with other people. Therefore a phone number relates to multiple people, and a person relates to multiple phone numbers. This type of relationship is typically set up using a join table, which is basically an intermediate table that contains fields to point to both tables that are being joined. For instance, you could make a join table that links people with phone numbers by defining two fields in that table, one of which contains a pointer to a person, and other of which contains a pointer to a phone number. The relations between people and phone numbers are defined in this join table, not in the original records.
The records in the join table appear to be "children" of the referenced records, because of those pointers. So if you view a person, you will see the join table records of that person, and those records will, in turn, link to the phone number records.
Inputting a many-to-many relationship typically involves adding (or editing) a record in the join table, and selecting two (or possibly more) record pointers from those that are given.
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:
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.
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.
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.
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.
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.
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.
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.
You can invoke the various functions of the WebDB toolkit as simple CGI programs, as follows:
To view the tables in the database
/cgi-bin/webdb.cgiTo view all records in a particular table
/cgi-bin/webdb.cgi?action=list&tab=TABLENAMEThis 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
To insert a new record into a table
/cgi-bin/webdb.cgi?action=new&tab=TABLENAMETo edit an existing record
/cgi-bin/webdb.cgi?action=edit&tab=TABLENAME&id=RECORD-IDThe following additional options are accepted when doing inserts or edits. These parameters must be provided using URL-encoding, eg. ¶meter=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.
You can invoke the various functions of the WebDB toolkit using special content management tags embedded in ExSite-generated web pages, as described below.
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)-->
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)-->
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
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:
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.
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.