|
|
|
This tutorial intends to explain why you would want to use the PhpXDb API and it's Xml, Dbase and SQL implementaions - PhpXmlDb, PhpDbaseDb and PhpPgDb. It will explain what kind of projects it will help you with, and then how to implement them. It will also be of use where you are converting one database application over to using the PhpXDb API framework.
A database is a structured store of data. Usually the main subject is a collection of "records" where each record has a number of properties or "fields". The database then has a way of collecting and grouping records permiting management of those records. So for example you could have a database of people, where each record represents what we know about a person. A person would have fields of things like FirstName, SurName, Phone Number, Address. etc etc.
If you have an application that you are maintaining, or authoring, where there are several items in play that each have similair logical properties, and you are looking for a good way to select a subset, or add, remove, edit using some kind of generic tool, then you probably want to use a database. Typical examples in the webdesign world would be guestbook entries, website users, contact details, high scores, feedback data, news feeds, forums etc. Once you start thinking in terms of databases, you soon find that there are possibilities throughout a site!
By using a database, you can separate the data from the data-viewer, and build several views from the same data store. So you can have index pages that allow you to navigate to the particular record of concern, allowing quick location of the information the user is looking for. You can have forms for editing the record once you have found it, or forms for adding new records into the database without having to alter any webpages.
There are many formats available to choose as your data format, each with it's advantages and disadvantages. Usually each data format will have it's own interface to it, and to switch between data format is a costly expensive exercise. A quick glance in the Php Documentation in the function reference shows you just how many there are. Indeed searching for the word database in the php manual will return almost 3000 results! So why should you be interested in the PhpXDb API?
There are currently two implementations of the API, with two more planned for the future. The pros and cons of each are discussed below but if you are not that interested just now and are keen to get going, then start with the XML implementation as it is the most convenient and read this section later. When you find that performance is too slow, then consider switching to the Dbase one. Remember making the switch will require negligible if any changes to your application due to the abstract API :o)
In the beginning was PhpXmlDb. This uses XML as it's data format, using the Php.XPath class so that it does not rely on any extensions that are not enabled by default. XML is a text file format that can be easily edited without the API.
Advantages:
- Always available: With rare exception, you will almost always be able to use this implementation, as it requires few php extensions which are enabled by default.
- Editable by text editor: XML is a text format rather than binary, so you can use any editor you please to create view and edit the file.
- Quick to add fields: Because the records are stored as XML elements, there is a "soft" schema which can be amended as simply as adding a new element to a record.
- XSL can be used against the database: As the data format is an XML file, you can use XSL directly against your data format to produce a web page. This makes it especially suitable when you have lots of text in your database, rare updates, and static views into the data.
- No space wasted by one long field: As fields are bounded by xml tags, if you have one record with a long tag, and another record with a short tag, then the format only uses as many characters as needed to store that data. This makes the format very efficient where there are several long text fields in a record.
Disadvantages:
- Performance doesn't scale: The more records you add, the slower it becomes to parse the db object from the XML file. Thus it is only really suitable for databases less than about 30Kb.
- Slow to extract the schema: The schema can only be extracted after reading all records in the database.
- Space is wasted by xml tags: If you have many short fields, like lots of numbers, then the overhead in the file of the xml tags means that a lot of space will be wasted with the tags and your database will be much larger than it could be.
- Expensive to rename fields: All open and close element tags for all records must be renamed.
After discovering that the server farm that was running our website was pitifully underpowered, it soon became clear that we needed a database format with greater capacity for our larger databases. So PhpDbaseDb was authored which uses Dbase as it's data format. Dbase is effectively a flat file data format, where each record is the same number of characters long. This allows the computer to quickly read each record, as it is a predictable length long.
Advantages:
- Greater capacity than XML: Because of the predictable record length, it is very quick to read in records. This means that much bigger databases can be supported, potentially as high as 500Kb.
- Faster than a csv or txt file: Many web applications use text files to store data and use either commas (csv), tabs (txt) or other characters like "::" to separate the fields. This results in the least wasted space, however as each record is not a predictable number of bytes long they are not as fast as in Dbase.
- Fast to remove records: Removing a record means flagging it to say that it has been deleted. This results in a one character change to the file, which is very quick to make.
- Fast to add records: Records can be added to the end of the file very quickly without upsetting the other records
- Fast to search the file: The computer can quickly skip past each record as they are all the same number of bytes long. Records that have short fields are padded to make them the same length long.
- Schema is immediately available: The schema of the database is stored in the first few bytes of the file and can be returned immediately upon request.
- Quick to rename fields: As this requires only a modification to the schema which is in the header of the file.
Disadvantages:
- Requires the dbase extension: This is not enabled by default, so will not always be available to you.
- Can't be edited easily by hand: It is a binary format so either you have to use a binary editor, or rely on editing it using the API.
- Long fields can waste space: If you have text fields that are rarely set, but when they are set they can be long, then in order to make sure that all records are the same length, many records will contain lots of padding. This can lead to a large amount of wasted space in your database usually when there are a lot of text fields.
- Field names have a maximum length: of ten characters. This means you can't always be as descriptive as you'd like to be. You'll hit this limit more often that you'd imagine. Fortunately field names are for the computer to deal with, not for your users to see.
- Slow to add fields: Adding a field means that you will have to adjust every record in the database to make it the same length long which could be slow. In practise this should be relatively rare though.
- No distinction between unset and empty: Because of the padding of fields, there can be no distinction between empty and unset. Sometimes this distinction can be valuable, but this format does not allow it.
Eventually our website moved to a virtual server, where we had full control over the applications we could install. This meant we could harness the power of a SQL based Relational Database Management System for no additional extra cost. The dynamic schema capability of PhpDbaseDb had resulted in one database table being over 6MB in size, yet it was extremely sparsely populated. Several tables had grown to several thousand rows with extensive foreign key relationships between tables that were all managed in PHP. Performance continued to degrade and several areas of funcionality stopped working while we worked on an SQL implementation of the PhpXDb API. PostGreSQL was chosen over the alternative of MySQL as at the time of writing, PostGreSQL gave better support for stored procedures and foreign keys. There are however no known technical reasons why a PhpMySqlDb would not be an option for the future.
Advantages:
- Fast: both to query, and to modify
- Scalable: will cope with MBs worth of data without problems.
- Resistant to corruption: support for explicit foreign key relationships means you can't delete a row when other tables refer to its data. You can also use transactions to make sure complex operations are seen as atomic.
- Established and well supported: There are many other SQL gui's around that you could use instead of the the default one built upon CXDbGui.
- More secure: An SQL database allows you to create database users and grant each user different permissions for different tables. This means you can prevent your application from accessing or manipulating data that it otherwise might be able to should your application be malliciously exploited.
Disadvantages:
- Requires PostGreSQL extension: which is not enabled by default.
- Requires access to a SQL Db: which will likely cost additional hosting money.
- Most complex solution: Setting up PostGresDbs with its database users and security is complex and takes time to learn and get right. As a solution it will take longer to set up than a simple XML file on the disk
- No Dynamic Schema support: Tables are designed in advance with each column strongly typed and sized. This makes for a more reliable solution, but means you have to spend a little more time designing your tables and writing error handling.
With the move to the virtual host it is looking like it will make sense to have user and member data stored in an LDAP database. There is no reason why we can't have an LDAP implementation of the API allowing us to do searches on the data through the same API. This will end up as a PhpLdapDb.
Advantages:
- Fast to search: It's a database format that is specifically designed to be very fast to query.
Disadvantages:
- Not implemented yet: well it's a disadvantage isn't it :o)
- Requires LDAP extension: which is not enabled by default.
- Slower to modify than SQL: It's a "directory access protocol". so it's designed more for searching than updating.
So how do you go about designing your project to use PhpXDb API? There are two things you need to do; firstly design and construct your database, and then construct your gui(s) to access it. If you are converting an existing application to use the API, then these decisions will probably have been taken for you and it will simply be a case of conversion.
To design your database you need to think about what your data is, and how to structure it. To do this, you should consider what "use cases" there are for your data.
Think of your Use cases
Use case examples
- Guestbook: You need to browse the guestbook, you probably don't care too much about sorting and can accept the historical default sort order. You rarely need to edit, and you need your users to be able to add records.
- A links database: You need to be able to browse and search through the database looking for links of interest. You may need to modify links to correct out of date urls, delete entries that are out of date, and permit users to add them.
You need to think about what kind of searches your users are going to want to do on your data. Then with that in mind you want to make sure that your database can deliver the results of that search with the minimum of effort.
Work out what a Record is and what Fields you will have
Fundamental as it sounds, it isn't always clear. You may for example be designing a user database where some users are members of different lists. Do you have a database of lists where each list is a record, and a record has a field that contains the membership? Or do you have a database of users, where a user has fields that specify if it is a member of a list or not? The decision again comes back to thinking about the use cases. Think about what is going to be needed most often, and optimize for that. If it is to query the number of users, then create a users database. If it is to extract list membership then create a lists database. If it is both then maybe you need two different databases that relate to each other, and are updated in tandem.
Think about what kind of information you will need to have about each record and try to make sure this information is not duplicated. Suppose in our previous example we decided that we need to be able to quickly query all the users and quickly query list membership, so we decide that we need two databases that relate to each other. While we could put in the list database details of the first name, last name, email address of each of the members, it would be better to hold a list of member record ids. Then we can look up the member record ids in the member database to find first name, last name, email address, and we have both saved space, and have less data to maintain.
Pick your database implementation
Read through the pros and cons section of the Api Implementations section and decide on the best format. Don't be too worried about making the wrong choice, as you can switch later. If in doubt, start with the XML version and move to the more scalable versions as the need arises.
Construct your database
This can be done in one of two ways. Either you can create a PhpXmlDb by hand, or by doing an XML export from some other system, then morphing it into the PhpXmlDb schema, possibly by using XSL, or else you create your self a tab separated data file, and use the Import() feature of the PhpXDb API Manager.
Constructing a basic gui is very easy, as the CXDbGui class provides default methods for almost everything you will want to do. This means out of the box you will get a gui that allows you to search, sort, list all, add, edit and delete. From this default, you then override the default behaviour to further customise the data and provide the views into it that support your use cases.
Customizations start sounding like a great idea, until you upgrade the base versions of the scripts most specifically the version of CXDbGui. When this happens, the improvements and bug fixes made to the base versions will not trickle up to your applications, because your customization will mask the change. For this reason before you customize, ask yourself if this really is a customization, or something that every application would benefit from, and if so then customize CXDbGui instead of the specific application you are authoring.
Start with the example
First off copy the example gui code from ..\dbutils\examplegui\carols.php and store it in the folder where your .php db gui will live. Theres now a number of things that you will need to tweak:
- Path to the CXDbGui: at the top there is a require_once() command that you will need to alter to point to the gui class on your webserver.
- Table and Record name: in the class constructor you will see lines that set the $this->TableTag and $this->RecordTag. You need to change these to match the table and record name of your database.
- The path to the database that this gui is for: After the class definition is a line that sets the $DbName variable. You need to change this to point at your database.
- ImagesPath: the default implementation makes use of some images that it will need to locate. You will probably need to alter this path to point at wherever you put the images.
Doing only the above will give you a fully fledged database gui with default behaviour that works. :o)
Begin customizing
While the default behaviour is likely to be enough to get going, there are likely to be several things that won't be suitable for displaying to your users, so you will need to customize. The list of likely candidates for customization are mainly listed in the constructor, with comments guiding you as to exactly what effect customizing that value will have. Likely candidates are:
- FriendlyRecordName: you probably don't want to talk to your users in terms of records, but rather they will have a friendly name like "member", "link" "entry" or "book". By setting this the messages will begin to look suitable.
- aJavascriptDrawShortRecordParameters: as you will see if you read the API documentation, it is specially optimized for returning queries by using javascript to effectively compress the search results. By default the class will echo all fields in the database, but it may be that you want to hide some of the fields from the search results like confidential information (passwords) or information that is of secondary interset (ISBN number of a book). By manually setting this array to the name of the fields that you want to publish, you will change the contents of the search results to only display those fields.
There are also several other customizable fields. Have a look at the source of the CXDbGui to see the list of public class members.
Select the Security Strategy
Secuirty examplesGuestbook: Your normal users would only need to be able to search and add, but your administrators would need to be able to modify and delete. So bDbSecureMode and bGuiSecureMode would both be TRUE, and you would grant access to the "ListAll" and "Add" actions by default. If you detect that the logged in user is and administrator you would also grant access to the "Update record" and "Delete record" actions.
<?php
$this->bGuiSecureMode = TRUE;
$this->aActions['Add']['Permissions']
= XMLDB_PERMISSION_ENABLE;
$this->aActions['ListAll']['Permissions']
= XMLDB_PERMISSION_ENABLE;
$this->bDbSecureMode = TRUE;
$this->aDbPermissions = array(
'GetFieldNames' => XMLDB_PERMISSION_ENABLE,
'GetTableNames' => XMLDB_PERMISSION_ENABLE,
'GetRecordNames' => XMLDB_PERMISSION_ENABLE,
'GetSchema' => XMLDB_PERMISSION_ENABLE,
'Search' => XMLDB_PERMISSION_ENABLE,
'SortSearch' => XMLDB_PERMISSION_ENABLE,
'Display' => XMLDB_PERMISSION_ENABLE,
);
if (IsAdministrator($_SERVER['REMOTE_USER']) {
$this->aActions['Update record']['Permissions']
= XMLDB_PERMISSION_ENABLE;
$this->aActions['Delete record']['Permissions']
= XMLDB_PERMISSION_ENABLE;
$this->aDbPermissions['ModifyRecord']
= XMLDB_PERMISSION_ENABLE;
$this->aDbPermissions['DeleteRecord']
= XMLDB_PERMISSION_ENABLE;
}
?>If you are running your test on a local server, then the chances are that everything will be working ok, but you will notice both by the warning displayed on the example page and if you upload the server to your website, that by default you will only have specific read only permissions. So you may want to customize this.
The quick solution is to set bDbSecureMode and bGuiSecureMode to FALSE, making everthing available by default. But I'd encourage you to spend a little more time thinking about security than that.
Providing any kind of write or modify access to your database is a security risk, so you need to think carefully about who will have access to your gui. Then balance up the risk of them being mallicious, against the consequences of their actions and decide on an approprate startegy. Security can be applied at two places, the db class itself, and then also the db gui class. There are two startegies you can employ for each:
- Everything enabled by default: all actions or methods will be available by default, unless explicitly disabled. This is the least secure and corresponds to setting secure mode to FALSE.
- Everything disabled by default: all actions or methods will be disabled by default, and must be explicitly enabled. This is the default setting, with secure mode to TRUE.
The security for the db class is covered by the bDbSecureMode flag and the entries in the aDbPermissions array, and the security for the gui class is handled by bGuiSecureMode and the 'Permissions' attribute of an action.
Now admittedly your users will only be able to do what your gui allows them to do, so if you don't have an add form, then they won't be able to add, but by using these security settings you can lock the security down, and also have gui's that have multiple levels of permissions depending on who is logged in.
Input Data Validation
You will also probably want to add validation to the data that is supplied when the user adds a record. For example some fields if supplied may want to have default values. Names should be in lowercase with an upper case first letter. Duplicate record protection will probably be needed to. All of these issues are addressed by overriding the _ModifyRecord() function.
Forms
Form TipsIf you choose to override one of the default forms, there are a number of helper functions in the CXDbGui that may be of interest:
- _DrawInputControl()
- _DrawOptions()
- _DrawSelectInput()
- _DrawHiddenFormInputs()
By default, all the forms are drawn with text inputs and text areas. The schema is queried to work out if any of the fields are multiline and how long they are so that the input controls are the right size for the data they contain.
It is likely that some of your data will consist of one of several specific values. For example a field might contain a book of the bible, of which there are several specific options. So you don't want the inputs that represent that field to be freeform, rather you want a select control with a list of potential options. For this reason alone you will probably want to customize the forms that are available in the gui. The forms available for customization are as follows:
- Add/Modify Form: used when adding a new record, or modifying an existing one. You override the _DrawAddModifyForm() method to draw the form that you would like.
- Search Form: used for constructing a search in your database. You override the _DrawSearchForm() method to draw the form that you would like.
Logging strategy
Designing web applications can be a somewhat lonely experience. You don't see your users; their expressions of appreciation, or hatred as they visit parts of your site that they like or dislike - all you get to do is log where they went and where they went back to. So be it for reasons of encouragement, a desire to optimize for what your users want from your application, looking up an audit trail of the changes to a database, or just from plain nosiness, you are likely to be interested in logging accesses to your page.
The CXDbGui class contains built in logging capabilities, off by default, that are easy to switch on and customize. If you set the LogFileName member variable to a file name (including path), then all access to the page will be logged to that file.
Log format exampleThe default log format is the Common Log File Format as defined by the W3C. An example is shown below:
68.185.203.59 - - [30/Nov/2003:07:01:12 +0000] "/resources/links/index.php" 200 26 68.185.203.59 - - [30/Nov/2003:07:01:24 +0000] "/resources/links/index.php?Action=ShowCategory&Category=Christian Unions" 200 72 81.152.62.85 - Nigel [30/Nov/2003:09:19:25 +0000] "/resources/links/index.php?Action=ShowCategory&Category=Shops of Interest" 200 73
Logging TipA good strategy for choosing a log file is shown below. It will cope with being copy pasted between all of your Gui applications and makes for a predictable strategy:
<?php
$this->LogFileName = __FILE__.'.log';
?>Logging every access to the page will actually generate a lot more logging that you would expect. With database related guis, usually your user will visit the default view of the gui, then spend some time navigating to the record of interest, and finally perform some action on the record that they have found. Every one of these clicks or refreshes will add another log entry.
It may be that you are only interested in the actual actions performed, or how the user went about finding the record of interest; either way after you have read the log a few times, you will soon find out what is interesting, and what is dull, and want to hide the dull. You do this by overriding the _BuildLogRequestLine() function. By default this function will take the page name, and append to it all the request vars, but if the function returns an empty string, then the page access will not be logged.
Selective Logging ExampleBy looking for page accesses where the Action is "View All" we return '' to ommit a log entry, but log the rest as default.
<?php
function _BuildLogRequestLine() {
if ($_SERVER['Action'] == 'ListAll')) return '';
return CXDbGui::_BuildLogRequestLine();
}
?>
Custom actions
Finally the most advanced form of customization, but not necessarily the hardest, is the addition of custom actions. The CXDbGui class works by running actions. There are a predefined set of actions which allow you to do thinks like search, view all, add, delete and so forth, but depending on your data you may have some more specific needs. These will usually take the form of customized searches, or they may be alternative ways to view or add data to the system. Adding an action requires two modifications:
- Add an entry to the aActions array: The aActions array contains a list of all the actions that this database supports. Actions have a number of properties including the name of the function that will carry out that action, it's name, it's permissions, and some other attributes for the action.
- Add a handler to implement the action: The handler is a method of the class of the form _HandleAction_[Action Name](). This ties up with the 'Method' attribute of an action. So when the database tries to execute that action it will do so by calling that method.
As with anything you do on your website, it will be a good idea to document the work you've done, indicating why it exists, how to maintain it, and any security concerns that it might raise. :o)
Designing a database application using the PhpXDb API and CXDbGui class is a quick process, with a usable database and gui produced almost immediately. You will not be bound to your initial choice of data format, but will be able to upgrade it as the need arises with next to no changes to your application code. You can customize everything in the gui, with the most common customizations made easily available, with more complex custimizations still available should you need them. Even if you end up customizing the entire CXDbGui the design pattern of the class will make the design cycle much faster and smoother than if you started with a blank page, the end result being a scalable and maintainable database gui. :o)
Last updated: 17 April 2008 02:15:13.
© 2008 Carrubbers Christian Centre | Registered Charity No. SC011455