Auditing may be achieved in an automated fashion by conforming to a few simple database conventions.

First, the table that you want to audit needs to have a "user_id" which correlates to the user making the change. So, when a user creates, updates, or deletes a row, their user_id is used to track the change. Then, a few SQL scripts may be executed to generate the triggers necessary to populate the audit table.

To create the audit triggers, the following scripts may be run to generate the corresponding SQL statements.

SELECT CONCAT('CREATE TRIGGER ', table_name, '_update_audit AFTER UPDATE ON ', table_name,
' FOR EACH ROW BEGIN DECLARE i_index BIGINT(20); SET i_index = (SELECT MAX(cluster)+1 FROM lms_audit); ',
audit_column_gen(table_name, 'UPDATE'), ' END$$') FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = 'user_id'
AND table_name NOT LIKE 'lms_post'
AND table_name NOT LIKE 'lms_message'
AND table_name NOT LIKE 'lms_user'
AND table_name NOT LIKE 'lms_user_cookie'
AND table_name NOT LIKE '%audit%'
AND table_name NOT LIKE '%object%';

SELECT CONCAT('CREATE TRIGGER ', table_name, '_insert_audit AFTER INSERT ON ', table_name,
' FOR EACH ROW BEGIN DECLARE i_index BIGINT(20); SET i_index = (SELECT MAX(cluster)+1 FROM lms_audit); ',
audit_column_gen(table_name, 'INSERT'), ' END$$') FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = 'user_id'
AND table_name NOT LIKE 'lms_post'
AND table_name NOT LIKE 'lms_message'
AND table_name NOT LIKE 'lms_user'
AND table_name NOT LIKE 'lms_user_cookie'
AND table_name NOT LIKE '%audit%'
AND table_name NOT LIKE '%object%';

SELECT CONCAT('CREATE TRIGGER ', table_name, '_delete_audit AFTER DELETE ON ', table_name,
' FOR EACH ROW BEGIN DECLARE i_index BIGINT(20); SET i_index = (SELECT MAX(cluster)+1 FROM lms_audit); ',
audit_column_gen(table_name, 'DELETE'), ' END$$') FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = 'user_id'
AND table_name NOT LIKE 'lms_post'
AND table_name NOT LIKE 'lms_message'
AND table_name NOT LIKE 'lms_user'
AND table_name NOT LIKE 'lms_user_cookie'
AND table_name NOT LIKE '%audit%'
AND table_name NOT LIKE '%object%';

The above scripts require the audit_column_gen function available in the schema/lms_functions.sql file.

To delete the audit triggers, the following scripts may be run to generate the corresponding SQL statements.

SELECT CONCAT('DROP TRIGGER IF EXISTS ', table_name, '_update_audit$$') FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = 'user_id'
AND table_name NOT LIKE 'lms_post'
AND table_name NOT LIKE 'lms_message'
AND table_name NOT LIKE 'lms_user'
AND table_name NOT LIKE 'lms_user_cookie'
AND table_name NOT LIKE '%audit%'
AND table_name NOT LIKE '%object%';

SELECT CONCAT('DROP TRIGGER IF EXISTS ', table_name, '_insert_audit$$') FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = 'user_id'
AND table_name NOT LIKE 'lms_post'
AND table_name NOT LIKE 'lms_message'
AND table_name NOT LIKE 'lms_user'
AND table_name NOT LIKE 'lms_user_cookie'
AND table_name NOT LIKE '%audit%'
AND table_name NOT LIKE '%object%';

SELECT CONCAT('DROP TRIGGER IF EXISTS ', table_name, '_delete_audit$$') FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = 'user_id'
AND table_name NOT LIKE 'lms_post'
AND table_name NOT LIKE 'lms_message'
AND table_name NOT LIKE 'lms_user'
AND table_name NOT LIKE 'lms_user_cookie'
AND table_name NOT LIKE '%audit%'
AND table_name NOT LIKE '%object%';

Using either Mouse Object constructor detailed above, a Message Editor Page Class would look like:

class MessageEditor extends Page {
    function __construct() {
        parent::__construct("Message Editor");
        $this->data = new MessageDB();
        $this->list = array();
    }

    function pre_render() {
        parent::pre_render();

        //alternatively, the following may be set as init => $_POST[xxxx] in the MOUSE object
        if (isset($_POST["subject"])) {$this->data->set_subject($_POST["subject"]);}
        if (isset($_POST["message"])) {$this->data->set_message($_POST["message"]);}

        if (isset($_REQUEST['Save'])) {
            if (count($_REQUEST['to_user_id']) > 0) {
                foreach ($_REQUEST['to_user_id'] as $id) {
                    $this->data->set_to_user_id($id);
                    $this->data->save_lms_message();
                }
            }
            if ($this->error_count() == 0) {
                my_redirect("default.php?appname=message-mgr");
            }
        }

        if (isset($_REQUEST['Cancel'])) {
            my_redirect("default.php?appname=message-mgr");
        }
    }

    function body() {
        set_form("/cgi-bin/default.php?appname=message-edit");
        auto_render_view($this->data, "Create a Message", "600px");
        render_button_bar("600px");
        echo('</form>');

    }
}

Steps to create a new page:

1) Insert entry into the lms_app table.

INSERT IGNORE INTO lms_app (NAME, module, constructor, proper_name, GLOBAL)
    VALUES ('url-ref', 'objects/dir/file.php', 'Class', 'Name to Show on Page', 'global_variable');

2) Insert an entry into the lms_menu table.

INSERT IGNORE INTO lms_menu (NAME, url, hierarchy, groups, description, visible)
    VALUES ('Audits', '/cgi-bin/default.php?appname=audit', '90.25',
        (SELECT group_id FROM lms_group WHERE groupname IN ('Master Administrator')),
        'The system audit log menu', 1);

3) Insert an entry into the lms_app_group table (if the page being created is only available to certain groups)

INSERT IGNORE INTO lms_app_group (NAME, group_id)
    VALUES ('audit',
        (SELECT group_id FROM lms_group WHERE groupname IN ('Master Administrator')));

4) Add a root .php file to the objects subdirectory (eg. objects/dir/file.php)

<?php include_once("objects/admin/data/audit.php"); include_once("objects/admin/view/audit.php"); ?>

5) Add a data .php file to the data subdirectory (eg. objects/dir/data/file.php)

<?php class NameDB extends DB {
         function __construct() {
            parent::__construct();
           $this->has(array( ... ));
         }
      }
?>

6) Add a page .php file to the view subdirectory (eg. objects/dir/view/file.php)

<?php class Name extends Page {
         function __construct() {
            parent::__construct("Name");
           $this->data = new NameDB();
         }

         function body() {
             $this->auto_render();
         }
      }
?>

Using either Mouse Object constructor detailed above, a Message Manager Page Class would look like:

class MessageManager extends FilterPage {
    function __construct() {
        parent::__construct("Message Manager");
        $this->data = new MessageDB();
        $this->list = array();
        $this->attributes = array();
        $this->page_size = 20; //overload FilterPage
        $this->total_rows = $this->data->count_lms_message(); //overload FilterPage
    }

    function pre_render() {
        parent::pre_render();
        if ($_REQUEST['print']) {
            $this->page_size = $this->total_rows; //if this is a printable page, show all of the results
        }
        //select from lms_message table
        $this->list = $this->data->list_lms_message(($this->get_current_page()-1) * $this->get_page_size(),
                                                     $this->get_page_size());
    }

    function body() {
        if (!$_REQUEST['print']) {
            render_pagination_bar($this->current_page, $this->total_pages, $this->total_rows, true);
        }
        auto_render_list($this->data, $this->list, true);
        if (!$_REQUEST['print']) {
            render_pagination_bar($this->current_page, $this->total_pages, $this->total_rows, false);
        }
    }
}

The following examples take a Mouse class and produce a number of views reflecting standard Create, Read, Update, and Delete (CRUD) functionality.

Generally, Mouse objects can be constructed in one of two ways. The first way is to create a separate Mouse Object for each of the rendering types. The second way is with a single class representing the complete Mouse Object which uses “modify_” Reflection methods to alter the Object based on the type of rendering (eg. list, editor, or viewer) being performed. The first way is cleaner with regard to code clutter; the second, with regard to file clutter.

The first method is preferred as it is easier to read and manage, but the second method could provide a smaller memory footprint and faster memory access should the developer decide to keep the PHP files resident in the web server or compiled to C byte code.

Using either Mouse Object constructor detailed above, a Message Viewer Page Class would look like:

class MessageViewer extends Page {
    function __construct() {
        parent::__construct("Message Viewer");
        $this->data = new MessageDB();
        $this->list = array();
    }

    function pre_render() {
        parent::pre_render();
        //handle deletes if a mission_id is presented
        if ($_REQUEST['delete']) { $this->handle_delete(); }

        $this->message = $this->data->read_lms_message();
        if (empty($this->message)) { $this->addError("No message associated with this message ID."); }
    }

    function body() {
        if (!empty($this->message)) {
            $this->data->update_message_read_time();
            $links = array(array(link => "default.php?appname=message-view&delete=1&message_id=" .
                     $this->data->get_message_id(), label => "Delete"));
            auto_render_view($this->data, '"' . $this->data->get_subject() . '"', "600px", $links);
        }
    }

    function handle_delete() {
        $this->data->delete_lms_message();
        my_redirect('default.php?appname=message-mgr');
    }
}