NAME

Apache::WeSQL::Journalled - A library of functions to deal with an SQL database in a Journalled way.

SYNOPSIS

  use Apache::WeSQL::Journalled qw( :all );

DESCRIPTION

This module contains all functions necessary to deal with SQL databases in a Journalled way. You may call them directly from any WeSQL document, however, they will probably mostly be used by direct webcalls to jadd.wsql, jdelete.wsql or jmodify.wsql, with the appropriate parameters and configuration in the 'permissions.cf' file. And, of course, in the form.cf file. For more information on the format of these .cf files, see Apache::WeSQL::Display.

For the journalling code to work, every table will need to have the following additional fields (MySQL definition): (for the PostgreSQL definition see the sample Addressbook application)

        pkey bigint(20) unsigned not null auto_increment,
        id bigint(20) unsigned not null,

        uid bigint(20) unsigned not null,
        suid bigint(20) unsigned not NULL default '0',
        epoch bigint unsigned not null,
        status tinyint default '1' NOT NULL,

In addition to that, the 'pkey' column must be defined as the primary key:

        primary key (pkey)

The underlying idea is that with the Journalling code, records are never (NEVER!) deleted from the database. Instead, when a record needs to be changed, its status is set to '0', and its data is copied to a new record with status '1' and changed. When a record needs to be deleted, its status is set to '0'.

This allows tracing all changes to the data in your database. Of course, when the database becomes too big, you can occasionally backup and delete all records with status='0'.

When the authentication has not been switched off, the userid (and possibly superuser-id) of the person doing the change is recorded in the uid and suid fields. Superusers can 'cloak' themselves to be an ordinary user. This means that when they change a record, the uid will be the id of the ordinary user, thus maintaining the ownership of the record, but the suid will be the id of the superuser.

The columns have the following functions:

• pkey: The primary key. This value is unique in the table.

• id: The key, identifying unique records. There can be only 1 record with status='1' for any give value of id.

• uid: The user id of the user who created/changed this record. This is used to determine who 'owns' a record.

• suid: The superuser id of the superuser who created/changed this record. Zero for records without superuser intervention.

• epoch: The exact date & time of the last change to the record, in seconds since January 1st 1970.

• status: 1 or 0, active or not active.

Of course, for all this business with users and logging in to work, you will need the following two tables in your database (MySQL definitions): (for the PostgreSQL definition see the sample Addressbook application)

    create table logins (
      pkey bigint(20) unsigned not null auto_increment,
      id bigint(20) unsigned not null,

      uid bigint(20) unsigned not null,
      suid bigint(20) unsigned not NULL default '0',
      epoch bigint unsigned not null,
      status tinyint default '1' NOT NULL,

      userid bigint(20) unsigned not null,
      hash varchar(16) default '' not null,
      primary key (pkey)
    );

    create table users (
      pkey bigint(20) unsigned not null auto_increment,
      id bigint(20) unsigned not null,

      uid bigint(20) unsigned not null,
      suid bigint(20) unsigned not NULL default '0',
      epoch bigint unsigned not null,
      status tinyint default '1' NOT NULL,
      active tinyint default '1' NOT NULL,

      login varchar(50) default '' not null,
      password varchar(50) default '' not null,
      superuser tinyint default '0' NOT NULL,
      primary key (pkey)
    );

jAdd.wsql

By calling /jAdd.wsql urls you can have records added to your database - provided they match certain rules set up in permissions.cf of course. See the man page for Apache::WeSQL::Display.pm for more information about that.

There is one extra feature: by setting a session parameter with name 'editpostexecute', containing valid perl code, you can have a specific block of perl be executed after the execution of the jAdd sub. You can use the string '#addid' if you need to refer to the id of the just added record.

This module is part of the WeSQL package, version 0.53

(c) 2000-2002 by Ward Vandewege

EXPORT

None by default. Possible: readConfigFile jAdd jUpdate jDelete jAddPrepare jUpdatePrepare jDeletePrepare jErrorMessage operaBugDecode operaBugEncode

AUTHOR

Ward Vandewege, <ward@pong.be>

SEE ALSO

Apache::WeSQL, Apache::WeSQL::SqlFunc, Apache::WeSQL::Display, Apache::WeSQL::Auth, Apache::WeSQL::AppHandler