BSAdv Project on SourceForge

Users: | BSAdv Home Page | Release Notes | Using BSAdv | Youth Protection | Installation
Developers: | To Do List | Database Tables | Extending BSAdv | Database Consistency

Database Consistency

BSAdv uses several techniques to ensure consistent database state, even with multiple users modifying the same table(s). All enhancements to BSAdv should use the same techniques. Editing data with BSAdv entails three steps:

1. BSAdv PHP software on the server queries the database, creates one or more PHP data structures, converts the PHP data into either HTML or JavaScript, and transmits this to the users web browser.
2. The user interacts with the data in the browser, either by editing HTML data in simple forms, or by interacting with JavaScript and DHTML dynamic forms. The result of these edits is the submission of HTML variables, either directly from the HTML form, or directly from the DHTML JavaScript, back to the server.
3. The BSAdv server receives the data returned from the user's edit session and creates one or more MySQL commands to modify the data in the database.

The techniques that BSAdv uses to ensure consistent database state include:

1. Logging editors. When a user begins editing the database, BSAdv records the name of the user, the time, the table(s) edited, and a user-entered reason for editing. This is stored until the edit is completed, and is displayed to all subsequent users who edit the database. This is a warning, not a table lock. There can be valid reasons for one or more users to have multiple simultaneous edit sessions. Users editing the database should be aware of these warnings and make sure they avoid making duplicate changes to the database.
2. Original data forwarding. During edit operations, the browser maintains a copy of the original data from the database. This has several uses:
   1. The user can abort the changes made locally on the browser, reload the original values and continue editing. This can be done without interacting with the web server.
   2. When the user submits the edited data to the web server, the original data is submitted also. The server uses the original and edited data to calculate the changes that the user made to the data, and creates MySQL database update commands to make the database consistent with the edited data. This method allows multiple users to make simultaneous changes to different parts of the same table, without one user erasing the changes from the other user.
   3. NULL for new autoincrement indices. New table entries are created with NULL index values. When this occurs, MySQL creates a new unique index for the entry, consistent with the existing indices in the table, at the time of the update. Since the new index is created on the server by MySQL at the time of the INSERT operation, there is no chance that multiple users editing the same table will create different new rows with the same index.

   Changes made to the database are logged in the table dblog. This table stores the day, time, userid, SQL command and any error messages that may have occurred. This is used to track the person associated with a specific edit. This logging is controlled by component parameters, and can be modified in the Joomla back-end. The default log size is 1000 entries, and can be modified in the back-end.

   Sourceforge.net

   Thanks to Sourceforge.net for hosting our project.