NAME

Liz::SQL - generic SQL access and subroutine library


SYNOPSIS

 @ISA = qw(... Liz::SQL);
 use Liz::SQL;


DESCRIPTION

The Liz::SQL module is intended to be used in other modules only. It does not create its own objects.

It embeds itself in objects presented to it. To make sure the methods of this module are called, the Liz::SQL module should be included in the @ISA array of the module in which Liz::SQL is used.


CLASS METHODS

The following methods are supposed to be called as class methods for client modules.


define

Define method(s) in the client modules matching the specification(s). These are usually used only in general maintenance scripts.

For instance, a generic maintenance script for maintaining categorysets needs to have method ``CategorySets'' to be defined in the client module to be able to list the available categorysets, and a method ``CategorySet'' to be able to read/write a categoryset. Instead of having always to define these methods in the client module, it is now possible to define this ``at run-time'' as it were.

These methods are also referred to as ``glue methods''.

Input Parameters

 1..N method specifications to define
Example

 in category.lp:

 $class->define( qw(CategorySets CategorySet) );
Note

 The following method specifications are currently recognized:

 CategorySet
 CategorySets
 CounterSet
 CounterSets
 EventSet
 EventSets
 Forum
 Forums
 FileSet
 FileSets
 MaintainAccess
 MemberAccess
 MiniSum
 MiniSums
 OCASet
 OCAsets
 PictureSet
 PictureSets
 PingSet
 PingSets
 ProjectSet
 ProjectSets
 Session
 Survey
 Surveys
 TrackSet
 TrackSets
 Tree
 Trees
 URLSet
 URLSets


sets

Return a list of sets for a module that inherits from Liz::SQL. It is not intended to be called as a class method of this module, but instead to be inherited by sub-modules.

Input Parameters

 1 reference to routine for database connection or Liz::SQL compatible object
   (default: &Connect in the caller module's namespace)
 2 prefix being used by the sub-module
 3 wildcard specification to match
   (default: all)
 4 postfix to use (in case no "bare" table in module)
Output Parameters

 1 reference to list of identification names in the database
 2 reference to hash with full names
 3 reference to hash with database versions
Example

 ($token,$name) = $client->tracksets;
 
 in Client.pm:

 sub tracksets { shift->SUPER::sets( shift,'prefix',@ ) }


CONSTRUCTOR METHODS

The following methods return objects and are either called as class methods or as subroutines..


new

Create a new object for a client module and return it blessed as a new object of the client module. It is not intended to be called as a class method of this module, but instead to be inherited by client modules.

Creates connection with the database (as specified by the Connect subroutine in the client module's namespace).

Sets fields BASEDIR, DEBUG and TMP according to the global variable specification in the client module's namespace.

Input Parameters

 1 reference to routine for database connection or Liz::SQL compatible object
   (default: &Connect in the client module's namespace)
Output Parameters

 1 instantiated client object
Example

 unless ($client = new Client) {
   print "Error: $ERROR\n";
 }
 
 in Client.pm:

 sub new {
   my ($self) = shift->SUPER::new( \&Connect );
   return unless $self;
 # extra actions
   $self;
   sub Connect { Liz::SQL::MySQL( 'database','password' ) }
 }

 or, in Client.pm (no special actions, so "new" does not need to exist):

 #INHERITED FROM Liz::SQL
 sub Connect { Liz::SQL::MySQL( 'database','password' ) }
Note

Check the variable $Client::ERROR if there was an error when creating this object for the module 'Client'. Please substitute 'Client' with the name of your client module, of course!


connect

Embed a Liz::SQL object in the object that calls this method. Creates connection with the database of your choice.

Input Parameters

 1 reference to previous database object
   (default: use routine to create new database object)
 2 reference to routine that performs the connect to the database/table
   (this routine is assumed to make the connection to the database
   and return a blessed object or an error message)
Output Parameters

 1 reference to instantiated database object
   (returns undef if connect failed)
Example

 $SQL = $self->connect( $SQL,\&Connect );
Note

Check the variable $Liz::SQL::ERROR if there was an error when creating this object.


MySQL

Make a connection to a MySQL server for the specified database, password, user and physical server. Either returns a MySQL object or the text of an error message.

This subroutine is provided to facilitate migration between different version of the MySQL.pm module, and to make a future migration to DBD::MySQL module more easy. It can be called either as a method or as a subroutine.

It is usually called in the Connect subroutine of a client module.

Input Parameters

 1 database to be USEd
 2 password to be used to make the connection
 3 username to be used
   (default: name of database)
 4 server to be used
   (default: '' = UNIX socket)
Output Parameters

 1 MySQL object or error message
Example

 sub Connect { Liz::SQL::MySQL( 'database','password' ) }


Sanity

Create a Liz::SQL::Sanity object. Allows databases that are handled by Liz::SQL to be checked for internal consistency and be repaired if necessary. More documentation can be found with the Liz::SQL::Sanity.pm module itself.

Output Parameters

 1 reference to instantiated sanity object
   (returns undef if failed)


INDIRECT METHODS

These methods are usually not used directly on the Liz::SQL object, but rather through the modules that inherit from Liz::SQL.


clone

Remove the ID and KEEPID field from the object and set the CHANGED field. If the object is updated, a new ID will be issued and thus a clone of the original object is created.

Output Parameters

 1 the original object
Example

 $object = $module->Object( $ID );
 $object->clone;
 $object->update;
Example

 $module->Object( $ID )->clone->update;


create

Create a table with the given specification if it doesn't exist yet or drop the table first when so indicated. Returns flag indicating whether the table already existed.

No error checking is done to make sure the highest CPU efficiency is reached.

Input Parameters

 1 table to create
 2 specification to create (everything between the first and last parentheses)
 3 force re-creation of table by first DROPping the table
   (default: do not drop)
Output Parameters

 1 flag whether table already existed
Example

 $existed = $self->create( $table,<<EOD );
 ID INT INTEGER NOT NULL,
 sortname VARCHAR(255) BINARY NOT NULL,
 UNIQUE (ID),
 INDEX (sortname)
 EOD


delete

Delete one or more records associated with the Liz::SQL inherited object from the database.

Input Parameters

 1..N ID's of records to be deleted
      (default: only delete record of object's ID itself)
Output Parameters

 1    flag: whether action was successful
Example

 $message->delete;
 $message->delete( 100,101,102 );


reset

Delete all records and tables associated with the Liz::SQL inherited object from the database. Usually not called directly, but indirectly in each module. Assumes fields 'TABLE' and 'TOKEN' are set in the object.

Input Parameters

 1..N postfixes of table names that are associated with the object
Output Parameters

 1    new object
Example

 $categoryset->reset;
Example

 $categoryset = $categoryset->reset;


BULK METHODS

These methods allow loading of entire tables into memory, while allowing transparent access for creating objects.


Bulk

Activate bulk operation on the object. This causes all fields and all records of the table of the object to be read into memory. Subsequent ``new'' operations of that object will then obtain information from memory rather than from the database. This can be especially handy when generating HTML-pages from the database.

Method Obtain can be used to set all the fields in the object to the values associated with an ID, either from memory or from the database directly (if Bulk had not been called previously)

Example

  $object->Bulk;
Note

Assumes that the module of the object is prepared for this operation. More precisely, it expects two global variables to exist in the module's namespace:

 $FIELDS

A comma separated list of names of fields that can be read from a database record. This should be initialised at module initialisation time.

 %BULK

A global hash that is filled with a reference to a hash table in which all the data of the table is located when bulk operation is activated. The global hash is keyed to the name of the table. The referenced hash is keyed in the ID value and contains a reference to a list for each of the records found. As such, it can be used as an alternative ``fetchrow'' method, which is precisely what the Obtain method does.

Note

In a modperl context, the Bulk operation will also affect all other requests that will be made to the same Apache child, possibly causing things to get out of sync with the database. For exactly those cases, the UnBulk method can be used.


Obtain

Set all the fields in the object to the values associated with an ID, either from memory (if method Bulk had been called previously) or from the database directly.

Input Parameters

 1 ID to obtain information of
   (default: field as (indirectly) specified by the second parameter)
 2 name of field to use as ID
   (default: 'ID' );
 3 raw additional SQL (must start with " AND")
   (default: none)
Output Parameters

 1 flag: whether operation successful
   ( 1 = success, undef if failed)
Example

  unless( $object->Obtain( $ID ) ) {
    print "There is no information with ID $ID\n";
  }
Note

Assumes that the module of the object is prepared for this operation. More precisely, it expects this global variable to exist in the module's namespace:

 $FIELDS

A comma seperated list of names of fields that can be read from a database record. This should be initialised at module initialisation time.

Note

When specifying a second input parameter, and the field specified is not a numeric field, any value used for that field should already be escaped for SQL special characters, or should be sure to not contain any SQL special characters. Regardless of what is specified with the second input parameter, the value will always be stored in the 'ID' field in the object to ensure compatibility with other functions of this and other modules..


UnBulk

De-activate bulk operation on the object. The opposite of the Bulk method. After a call to this method, calls to method Obtain will allways obtain values from the database.

Example

  $object->UnBulk;
Note

Assumes that the module of the object is prepared for this operation. More precisely, it expects one global variable to exist in the module's namespace:

 %BULK

The field in that hash table that contains the reference for the table of the object, will be removed from that hash.


MODULE METHODS

These methods are primarily used within the module that makes use of the Liz::SQL module and not by ``end-user'' applications.


Exists

Return whether one or more tables already exist. Can also be used to create a list of all tables in the current database. Use method Count to check for the existence of a specific database.

Input Parameters

 1 name of table of which to check the existence (may use wildcards)
   (default: return all currently existing tables)
 2 flag: whether to fetch live copy from daemon
   (default: use copy obtained first time)
Output Parameters

 1 list of tablenames that matched
   (in scalar context: number that matched)
Note

The wildcard characters are Perl regular expressions: the ! character may be used in the beginning of a wildcard specification to indicate negation of the condition.

Example

 $object->Query( 'DROP TABLE zip' ) if $object->Exists( 'zip' );

 print "All tables in the database:\n";
 foreach( $object->Exists ) {
   print " $_\n";
 }
Note

Please note that this method scales badly with the number of tables in a database. When using MySQL, the complete list of tables is always read into memory, which can take a very significant amount of time for very large databases. To check the existence of a specific table, it is much wiser to use the Count method instead.


FieldExists

Return the names of fields that exist in the specified table. Can also be used to create a list of fields for the specified table.

Input Parameters

 1 name of field of which to check the existence (may use wildcards)
   (default: return all currently existing fields)
 2 table from which to obtain fields
   (default: field TABLE or CATEGORYTABLE)
Output Parameters

 1 list of fieldnames that matched
   (in scalar context: number that matched)
Example

 print "All fields in the table $table:\n";
 foreach( $object->FieldExists( '',$table ) {
   print " $_\n";
 }


IDExists

Return the names of tables and fields in which the specified ID exists. Names are returned as ``table/field/ID'' triplets.

Input Parameters

 1 ID for which to check existence
 2 wildcard specification of tables to check
   (default: all tables)
Output Parameters

 1 list of table::fieldname::ID that contains the ID
   (in scalar context: number that matched)
Note

The wildcard characters are Perl regular expressions: the ! character may be used in the beginning of a wildcard specification to indicate negation of the condition.

Example

 print "ID $ID occurs in:\n";
 foreach( $object->IDExists( $ID ) {
   print " $_\n";
 }


Log

Log information of an event in the database. Log messages may be up to 255 bytes long and are kept in monthly archives.

Input Parameters

 1 message to be logged (may be a reference)
 2 author ID of message
   (default: none)
 3 category ID of message
   (default: none)
 4 IP-number to associate with message
   (default: $ENV{'REMOTE_HOST'})
Output Parameters

 1 ID under which this log entry is known


Quote

Return quoted strings for a number of keys of an object. If one of the keys is 'SORTNAME' and it is not filled yet, it will be filled with a lowercase, alphanumeric only copy of the key 'NAME'.

Input Parameters

 1..N the names of the keys to be quoted
Output Parameters

 1..N list with quoted values (can be used in SQL queries)
Example

 my( $city,$street,$zipcode ) = $object->Quote( qw(CITY STREET ZIPCODE) );


Zero

Return zeroed strings for a number of keys of an object that have the value 'undef'.

Input Parameters

 1..N the names of the keys to return zeroes for when they are undef
Output Parameters

 1..N list with zeroed values (can be used in mySQL queries)
Example

 my( $parentID,$originalID ) = $object->Zero( qw(PARENTID ORIGINALID) );


KeepID

Set the flag to indicate that the ID field should be kept with the object when other fields are changed. Normally, the ID field of an object is reset as soon as one of the fields is changed.

Example

 $object->KeepID;


InitID

Initialize the numerical ID's of a table or of all tables

Input Parameters

 1 type of initialisation to perform
   a. number = initialise all tables with step specified
   b. table = initialise only this table
   default: initialise all tables with step 1000000
Example

 $object->InitID;
 $object->InitID( 100000 );
 $object->InitID( 'table' );


NextID

Return the next ID for a new object in a table. Can be used as a method for any HN (sub-)object.

Input Parameters

 1 name of the table
   (default: table of object)
 2 flag: do not lock table (already locked elsewhere)
   (default: lock table NextID when updating)
Output Parameters

 1 ID value for the new object
Example

 $ID = $object->NextID( 'table' );


LastID

Return the ID of a table that will be issued next.

Input Parameters

 1 name of table of which to obtain ID to be issued next
Output Parameters

 1 ID to be issued next (undef if not found)
Example

 $ID = $object->LastID( 'table' );


UnKeepID

Reset the flag to indicate that the ID field should be kept with the object. Also resets the ID field so that most likely a new ID will be issued for the object if other fields are changed.

Example

 $object->UnKeepID;


Boundaries

Return the ID boundaries of a table.

Input Parameters

 1 table name or valid ID of table
   (default: field 'TABLE' in the object)
Output Parameters

 1 low boundary of table
 2 high boundary of table
Example

 $low = $object->Boundaries( 'table' );
 ($low,$high) = $object->Boundaries( 'table' );


Step

Return the step size that is used between ID's of different tables. It can e.g. be used to check whether a numeric parameter is a valid ID or a numeric flag value.

The step value can be explicitely set by initializing all ID's and setting a new step value with InitID.

Output Parameters

 1 step value (usually 1000000)
Example

 if( $_[0] =~ m#^(\d+)$# and $1 < $self->Step ) {
   print "It is a flag!\n";
 }


ID2Table

Return the name of the table to which an ID belongs.

Input Parameters

 1 ID value
Output Parameters

 1 name of table
Example

 $table = $object->Table( $ID );


Name2ID

Return the ID of a string in a table. Assumes the name of the field to be search is 'sortname' and the field containing the ID is called 'ID'. If the string is not found, it is added to the table unless that has been inhibited.

Input Parameters

 1 name of table of which to obtain ID
 2 string to obtain the ID of
 3 sortname associated with string (lowercase string alpha only)
   (default: same as string)
 4 flag: action to be performed
   (-1=always add, 0=default: add string and issue new ID, 1=do not add)
 5 extra conditional SQL to check for
   (default: none)
 6 name of ID field
   (default: 'ID')
 7 name of name field: causes the name field to be searched also if specified
   (default: name field not searched)
 8 name of sortname field
   (default: 'sortname')
Output Parameters

 1 ID of string (undef if not found and not added)
 2 flag: whether a new ID was added
 3 name with which the record exists now (between quotes and escaped)
 4 sortname with which the record exists now (between quotes and escaped)
Note

If the name is already escaped and sortname is already set with lowercase characters, then you should use method ``_Name2ID''.

Example

 $ID = $object->Name2ID( 'table','Norway' );


ID2Description

Return the description of an ID.

Input Parameters

 1 ID value
 2 table of ID
   (default: look up name of table with ID)
Output Parameters

 1 description
Example

 $description = $object->ID2Description( $ID );


ID2Name

Return the name and optionally the sorted name of an ID.

Input Parameters

 1 ID value
 2 table of ID
   (default: look up name of table with ID)
 3 name of field containing name
   (default: 'name')
Output Parameters

 1 name of ID
 2 name on which the ID is sorted
Example

 $name = $object->ID2Name( $ID );


ID2CategoryList

Convert a list of ID's to a category list string.

Input Parameters

 1..N list of ID's of the field to be converted
Output Parameters

 1    categoryLIST field string
Example

 $categoryLIST = $self->ID2CategoryList( @ID );


ID2CategoryQuery

Convert a list of ID's to a string that can be used in a query.

Input Parameters

 1    name of field to be searched
 2    flag: whether OR (0) or AND (-1) or number of matches (>0)
      (default: OR)
 3..N list of ID's of the field to be converted
Output Parameters

 1    string to be used in a query
Example

 $query = $self->ID2CategoryQuery( 'categoryLIST',0,@ID );
 $result = $self->Query( "SELECT ID FROM table WHERE $query" );


CategoryList2ID

Convert a category list to a list. Assumes the ID's belong to the key 'CATEGORYTABLE', or if that is not set, the key 'TABLE' of the object specified.

Input Parameters

 1    categoryLIST field string to be converted
      (default: field categoryLIST of object itself)
 2    table to which ID's belong
      (default: field CATEGORYTABLE or field TABLE)
Output Parameters

 1..N list of ID's of the field
Example

 @ID = $self->CategoryList2ID( $categoryLIST );


OldCategoryList2ID

In older version of Liz::SQL, category lists consisted of 1-byte entries, instead of the new category lists with 2-byte entries. This method allows conversion of an old categorylist field to proper ID's. It is intended for conversion purposes only and.

Input Parameters

 1    old categorylist format string
 2    base ID to be used for the ID's
Output Parameters

 1..N old ID's (B<not> using the new numbering format)
Example

 @oldID = $adformatie->OldCategoryLis2ID( $categorylist,200000 );


ID2AncestorIDs

Convert an ID to a list of IDs of the parents and grandparents and grandgrandparents etc. of the ID. This assumes that each record in the table to which thia applies, contains an ID field which contains the ID of the parent.

Input Parameters

 1    ID of which to return the parents
 2    table to search ID's in
      (default: table to which the ID belongs})
 3    name of field for the parent ID
      (default: 'parentID')
Output Parameters

 1..N list of parent ID's (own parent first, then grandparent, etc).
Example

 @ID = $hn->ID2AncestorIDs( $ID );


ID2AncestorNames

Convert an ID to a list of names of the parents and grandparents and grandgrandparents etc. of the ID. This assumes that each record in the table to which this applies, contains an ID field which contains the ID of the parent.

Input Parameters

 1    ID of which to return the parents
 2    table to search ID's in
      (default: table to which the ID belongs})
 3    name of field for the parent ID
      (default: 'parentID')
Output Parameters

 1..N list of parent names (own parent first, then grandparent, etc).
Example

 @names = $hn->ID2AncestorNames( $ID );


ID2ChildrenIDs

Convert an ID to a list of IDs of the children and grandchildren and grandgrandchildren etc. of the ID. This assumes that each record in the table to which this applies, contains an ID field which contains the ID of the parent.

Input Parameters

 1 ID of which to return the children
   (default: 0 = all records in family tree)
 2 table to search ID's in
   (default: table to which the ID belongs})
 3 name of field for the parent ID
   (default: 'parentID')
 4 number of recursion levels
   (default: 2, -1 = unlimited, >0 = number of levels)
Output Parameters

 1 reference to list of children ID's (own first, then grandchildren, etc).
 2 reference to list of level (0=children, 1=grandchildren, etc)
Note

It is very expensive to run an unlimited number of recursion levels. This will cause an SQL query to be performed for every ID in the table. Although this is not a very heavy load on the SQL server, it does cause a lot of real-time delay because of all the locking involved.

Example

 ($ID,$level) = $hn->ID2ChildrenIDs( $ID );
 foreach( 0..$#$ID ) {
   print "$$level[$_]: $$ID[$_]\n";
 }


ID2ChildrenNames

Convert an ID to a list of names of the children and grandchildren and grandgrandchildren etc. of the ID. This assumes that each record in the table to which this applies, contains an ID field which contains the ID of the parent.

Input Parameters

 1 ID of which to return the children
   (default: 0 = all records in family tree)
 2 table to search ID's in
   (default: table to which the ID belongs})
 3 name of field for the parent ID
   (default: 'parentID')
 4 number of recursion levels
   (default: 2, -1 = unlimited, >0 = number of levels)
Output Parameters

 1 reference to list of children names
Note

It is very expensive to run an unlimited number of recursion levels. This will cause an SQL query to be performed for every ID in the table. Although this is not a very heavy load on the SQL server, it does cause a lot of real-time delay because of all the locking involved.

Example

 ($name,$level) = $hn->ID2ChildrenNames( $ID );
 foreach( 0..$#$name ) {
   print "$$level[$_]: $$name[$_]\n";
 }


SortID

Sort a list of IDs with a sort order that is indicated by a subroutine.

The sort subroutine should expect at least 3 parameters: 1 the object with which this method is called 2 ID of first element 3 ID of second element 4..N additional parameters

It should return -1 if first is smaller than second, 0 if they are equal, and 1 if first is larger than second. This can e.g. be achieved by the ``<=>'' operator for numerical values, or the ``cmp'' operator for string values.

Input Parameters

 1    reference to list of ID's to process
 2    reference to subroutine to perform the checking
 3..N additional parameters to be passed
Output Parameters

 1    reference to list, sorted in the order indicated
Example

 $list = $forum->SortIDs( $list,\&SortByName );

 sub SortByName {
 my( $self,$ID1,$ID2 ) = @_;
 $self->ID2Name( $ID1 ) cmp $self->ID2Name( $ID2 );
 }


META METHODS

These methods allow you to specify meta-information with tables or any other meta-information associated with a database.


Meta

Set or return Meta-information associated with a major key (usually a table name) and a minor key (usually a variable name). Use method TableMeta to associate information with the table belonging to the object.

Input Parameters

 1    major key to associate information with (usually a table name)
 2    identification name of information (like the name of a variable)
      (this may include wildcards such as * and ? when B<not> setting a value)
 3    information to be associated (e.g. value of a variable) if no wildcard
      (default: no change)
 4    flag: do B<not> lock tables here
      (default: lock tables)
 5    string to use for appending
      (default: field APPENDSTRING or "\0")
Output Parameters

 1..N current values of meta-information matching the variable name

or (if no wildcard was specified)

 1    current/previous value of meta-information
 2    last updated timestamp
Example

 @value = $object->Meta( 'table','*' ); 
Example

 $object->Meta( 'table','variable',$value );
 $value = $object->Meta( 'table','variable' );
 ($value,$updated) = $object->Meta( 'table','variable' );


MetaNames

Return the variable names of the Meta-information associated with the indicated table.

Input Parameters

 1    name of table to associate information with
 2    identification name of information (like the name of a variable)
      (default: '*')
Output Parameters

 1..N names of 'variables'
Example

 print "Variables with table 'table':\n";
 foreach( $object->MetaNames( 'table' ) {
   print " $_\n";
 }


NameTablesMeta2Hash

Return a reference to a hash with the names associated with a list of given table names, keyed to the table name. This method is usually used in the ``xxxsets'' method of a module (e.g. ``ocasets'' of the Liz::OCASet module).

Input Parameters

 1 name of the variable field
 2 reference to list of names of tables for which to return value of variable
 3 prefix to remove from table names
   (default: none)
Output Parameters

 1 reference to a hash, keyed to table names
Example

 $hash = $trackset->NameTablesMeta2Hash( 'Name',@token );


TableMeta

Set or return meta-information associated with the table (as indicated by the TABLE field in the object). Use the more general method Meta for a more general way of setting meta-information.

Input Parameters

 1 name of the field to associate Meta information with
   (return values matching wildcard only if wildcard)
 2 new value to set if no wildcard
   (default: no change)
Output Parameters

 1..N current values of meta-information matching the variable name

or (if no wildcard was specified)

 1    current/previous value of meta-information
 2    last updated timestamp
Example

 @value = $object->TableMeta( '*' );
Example

 $object->TableMeta( 'key',$value );
 $value = $object->TableMeta( 'key' );
 ($value,$updated) = $object->TableMeta( 'key' );


TableUnMeta

Remove meta-information associated with the table (as indicated by the TABLE field in the object). Use the more general method UnMeta for a more general way of removing meta-information.

Input Parameters

 1 name of the field to remove Meta information of
   (default: all, may include wildcards such as * and ?)
Example

 $object->TableUnMeta;
Example

 $object->TableUnMeta( 'Score' );


UnMeta

Remove meta-information associated with a table from the database.

Input Parameters

 1    name of table of which to delete associated information
 2    identification name of information (like the name of a variable)
      (default: all, may include wildcards such as * and ?)
Example

 $object->UnMeta( 'table' );
 $object->UnMeta( 'table','variable' );


QUERY AND RESULT METHODS

These methods are available for performing SQL queries and managing its results in the Perl namespace in various ways.


Query

Perform one or more generic queries on the HN object. Returns a standard object on which MySQL methods can be applied.

Input Parameters

 1..N queries to be performed
Output Parameters

 1    result object of last executed query
      (currently a standard MySQL statement handle)
Note

The value '#debug' can be specified as a query to set the flag that will output the queries being performed with any extra information. The value '#nodebug' can be specified as a query to switch off this flag. The default setting of this flag is taken from the DEBUG field in the object.

The value '#report' can be specified as a query to set the flag that will output information about a query if there is an error. The value '#noreport' can be specified as a query to switch off this flag. The default setting is '#noreport'.

The value '#ignore' can be specified to not output any errors in queries (e.g. when checking for the existence of a table with a SELECT). The value '#noignore' can be specified as a query to switch off this flag. The default setting is '#noignore'.

Example

 $result = $self->Query(
  "SELECT ID FROM table WHERE name=$name and ID>70 and startdate<$start" );
 $self->Query(
  "LOCK TABLES table WRITE",
  "INSERT INTO table (ID,name) VALUES ($ID,$name)",
  "INSERT INTO table (ID,name) VALUES ($ID2,$name2)",
  "UNLOCK TABLES" );


Query2ListHash

Return a list and a hash of a query. Assumes two fields are selected in the query and that the first field should be used as a key for the hash.

Input Parameters

 1    SQL query to be performed
 2    prefix to remove from keys
      (default: none)
Output Parameters

 1    reference to list with values of first field (usually ID)
 2..N reference to hash with values of Nth field


Result2ListHash

Return a list and a hash of a result handle. Assumes two fields are selected in the query and that the first field should be used as a key for the hash.

Input Parameters

 1    SQL result handle
 2    prefix to remove from keys
      (default: none)
Output Parameters

 1    reference to list with values of first field (usually ID)
 2..N reference to hash with values of Nth field


Query2Vars

Return the result of the first record of a query into variables of the indicated namespace, using the fieldnames as the names of the variables. More or less the opposite action to Vars2Query.

Input Parameters

 1 SQL query to be performed
 2 namespace in which to store the variables
   (default: namespace of calling routine)


Result2Vars

Return the result of the next record of a SQL result into variables of the indicated namespace, using the fieldnames as the names of the variables.

Input Parameters

 1 SQL result handle
 2 namespace in which to store the variables
   (default: namespace of calling routine)
Output Parameters

 1 number of variables set (undef if no result, 0 if no more records, >0 vars set)
Example

 $result = $object->Query( "SELECT * FROM table" );
 while ($object->Result2Vars( $result )) {
 # do something with the variables
 }


TableFields2Result

Convert a specification of a table and a number of fields into an SQL result handle, optionally with extra limitations.

Input Parameters

 1 name of table
   (default: field 'TABLE' in the object itself)
 2 fields to be obtained
   (default: 'ID')
 3 extra WHERE information
   (default: none)
 4 extra ORDER information
   (default: none)
 5 extra LIMIT information
   (default: none)
Output Parameters

 1 SQL statement handle
Example

 $result = $object->TableFields2Result( 'table','ID,name' );


Vars2Query

Store the values of variables into a record for the indicated field in the indicated table from the indicated namespace. More or less the opposite action to Query2Vars.

Input Parameters

 1 table in which to store data
 2 name of field to match on
   (default: ID)
 3 namespace from which to read the variables
   (default: namespace of calling routine)


LimitIDs2Query

Limit a list of ID's to a query on the table to which the object belongs.

Input Parameters

 1    extra query to be performed
 2..N ID's of which should be determined they match the query
Output Parameters

 1..N ID's that also match the query
Example

 @sendID = $contact->LimitIDs2Query( "options LIKE '%W%'",@memberID );


Result2FieldNames

Return the field names of the fields of a query.

Input Parameters

 1    result handle for which to return the names of fields
Output Parameters

 1..N names of the fields in the query
Example

 foreach( $client->Result2FieldNames ) {
   print "$_\n";
 }
Note

The reference to the list of names is stored in the field 'RESULT2FIELDNAMES' of the object for the result, so that multiple calls for the same result can be handled faster.


ResultFieldNameIndex2Value

Return the value of a field in a specific record in a result-handle.

Input Parameters

 1 result handle
 2 fieldname
 3 ordinal number of record
   (default: next; first if not called before for this result)
Output Parameters

 1 value of the specified field in the specified record
Example

 $value = $client->ResultFieldNameIndex2Value( $result,'MessID',4 );
Note

Moves the pointer of the next record to be read with the ``dataseek'' method if the third input parameter is specified. After a call to this method, the next record will always be read if no other ``dataseek'' call is performed.

Note

A reference to a hash of fieldname to index translations is kept with the object in the field RESULTFIELDNAMEINDEX2VALUE so that multiple calls can be handled faster.


OBJECT INHERITANCE METHODS

The following methods are used mainly when handling objects within objects, and the updating of these objects to the database.


Abort

Abort a number of child objects, Object names are specified as ``object key''.``type'' where type can be either ``ID'' or ``LIST''.

Input Parameters

 1..N child object field name specification
      (default for type: 'ID')
Example

 $self->Abort( qw(ADDRESS) );


Adopt

Adopt one or more objects with the current object. Mainly intended to supply information for the Propagate method that allows similar methods to be executed of all the children of a given object.

Input Parameters

 1..N   objects to be adopted
Example

 $self->Adopt( $hn->City );


Propagate

Propagate a method of an object to all its children. Children can be specified with the Adopt method.

Input Parameters

 1 method to be propagated
Example

 $self->Propagate( 'update' );


Incarnate

Create a number of child objects, Object names are specified as ``object key''.``type'' where type can be either ``ID'' or ``LIST''.

Input Parameters

 1    Liz::... object for spawning the children
 2..N child object field name specification
      (default for type: 'ID')
Example

 $self->Incarnate( $hn,qw(CATEGORY.LIST WEB CONTACT) );


MergeFields

Merge existing values of a record (as indicated by the field ID) with the values in the object, so that the information will not be lost when a partial match occurred.

Input Parameters

 1..N list of field names to check


Reap

Reap result values from child objects, Object names are specified as ``object key''.``type'' where type can be either ``ID'' or ``LIST''.

Input Parameters

 1..N child object field name specification
      (default for type: 'ID')
Example

 $self->Reap( qw(CATEGORY.LIST WEB CONTACT) );


ActivateObject

Return the object associated to a field of the current object.

Input Parameters

 1 field in which to store the object
 2 name of field of which to use the ID
   (default: same as field in which to store the object)
Example

 $contact = $news->ActivateObject( 'CONTACT' );
 $organisation = $news->ActivateObject( 'ORGANISATION','ADDRESS' );


AssociateObject

Associate an object to a field of the current object and adopt the object to allow for method propagation.

Input Parameters

 1 object of which to associate the ID
 2 field to associate object with within self
Example

 $news->AssociateObject( $contact,'CONTACT' );


SpecialMethod

All of the object inheritance methods assume that the method associated with a key, can be devised by removing any LIST or ID from the end of the name of the key and then lowercasing all letters except the first. This may not always be the case: with this method you can specify which method names should be used when a key processed in such a way, would not yield the correct method name.

Input Parameters

 1 method name that will be calculated
 2 method name that should be used
Example

 $hn->SpecialMethod( 'Bannergroup' => 'BannerGroup' );


UTILITY METHODS


AllAssociations

Set or return all associations to the ID of the object for ID's that belong to a specific table. This is a more specific version of AllAssociationsID.

Input Parameters

 1    type of association (0 == ID1->ID2, 1 == ID2->ID1) (optional)
      (default: 0)
 2    table for which to set/return the associations
 3..N list the IDs of the objects to be associated with ID
      (default: no change)
Output Parameters

 1..N old/current ID's associated with ID
Example

 $event->AllAssociations( 'web',@webID );
 @webID = $event->AllAssociationsID( 'web' );


AllAssociationsID

Set or return all associations to an ID for ID's that belong to a specific table. This potentially updates information in the Associations-table, containing the object's ID's in the fields ID1 and ID2, respectively. This is a more general version of methods Associations2IDs and AllAssociations.

Input Parameters

 1    ID of which to set/return associations
 2    type of association (0 == ID1->ID2, 1 == ID2->ID1) (optional)
      (default: 0)
 3    table for which to set/return the associations
 4..N list the IDs of the objects to be associated with ID
      (default: no change)
Example

 $event->AllAssociationsID( $ID,'web',@webID );
 @webID = $event->AllAssociationsID( $ID,'web' );


Associate

Associate two objects by adding a record to the Associations-table, containing the object's ID's in the fields ID1 and ID2, respectively. A more general version is available with method AssociateID.

Input Parameters

 1    type of association (0 == ID1->ID2, 1 == ID2->ID1)
      (default: 0, which may be omitted)
 2..N list the IDs of the objects to be associated with the calling object
Example

 $event1->Associate( @webID );
 $event2->Associate( 1,$webID4 );


AssociateID

Associate two or more objects to another object by adding records to the Associations-table, containing the object's ID's in the fields ID1 and ID2, respectively. This is a more general version of method Associate.

Input Parameters

 1    ID to associate other ID's to
 2    type of association (0 == ID1->ID2, 1 == ID2->ID1) (optional)
      (default: 0)
 3..N list the IDs of the objects to be associated with ID
Example

 $event1->AssociateID( $ID,@webID );
 $event2->AssociateID( $ID,1,$webID4 );


Associations2IDs

Returns an array with the associated IDs of this object.

Input Parameters

 1    type of association (0 == ID1->ID2, 1 == ID2->ID1)
      (default: 0, which may be omitted)
 2    table or ID of table of which to return associated ID's only
      (default: all ID's associated with the ID)
 3..N ID('s) to obtain associations of
      (default: ID of object itself)
Output Parameters

 1..N IDs of associated objects
Example

 @associatedIDs = $graphic->Associations2IDs;
 foreach( $graphic->Associations2IDs( 'web' ) ) {
 # perform action
 }


Comments

Set/Return the Comments for the specified object, either from existing information or for a new entry.

Input Parameters

 1 text of comment to be associated
   (default: no change)
 2 ID with which to associate comments
   (default: field 'ID' in object)
Output Parameters

 1 current/old comments
Example

 $web = $hn->Web;
 $web->Comments( <<EOD );
 This is a test entry for the Hospitality Net Index
 EOD
 $comments = $web->Comments;


DisAssociate

Break the associations to the object's own ID with the given IDs.

Input Parameters

 1    type of association (0 == ID1->ID2, 1 == ID2->ID1)
      (default: 0, which may be omitted)
 2    table or ID to delete association of
      (default: all ID's associated with the ID of the object)
 3..N additional ID's to remove from association
Example

 $graphic->DisAssociate( @webIDs );


DisAssociateID

Break the associations of the given ID with the given other IDs.

Input Parameters

 1    ID of which to remove the associations
 2    type of association (0 == ID1->ID2, 1 == ID2->ID1)
      (default: 0, which may be omitted)
 3    table or ID to delete association of
      (default: all ID's associated with the ID specified)
 4..N additional ID's to remove from association
Example

 $graphic->DisAssociateID( $eventID,@webIDs );


Find

Return the ID's that match a string and the ``sortname'' field in the table associated with the object.

Input Parameters

 1    (part of) the name to search for
 2    extra SQL condition to apply
      (default: none)
 3    records to return
      (default: all) 
Output Parameters

 1..N ID's of objects that match the specification
Example

 @ID = $libris->Title->Find( 'apenootjes' );


FindID

Find an ID for a name/sortname in the given table. Usually used in the ``new'' method of an object. If the name already consists of numeric characters only, then only that is returned.

Input Parameters

 1 table in which to search
Output Parameters

 1 ID of name/sortname in specified table or undef
 2 name to search for
 3 sorted name to search for
   (default: name, lowercase alphanumeric only)
 4 extra SQL to be used
   (default: none)
Example

 $ID = $self->FindID( 'venue',$name,$sortname );


ForeignKey2IDs

Return the ID's that are associated with the foreign key of the table of the Liz::SQL compatible object.

Input Parameters

 1    foreign key to look for
      (default: empty foreign key)
 2    name of foreign key field
      (default: 'foreignkey')
Output Parameters

 1..N list of IDs that occur with that foreign key in the table of the object
Example

 $ID = $forum->ForeignKey2IDs( $messageID );


ForeignKeyAssociate

Associate one or more ID's to a foreign key.

Input Parameters

 1    foreign key to associate with
      (default: empty foreign key)
 2..N list of IDs to be associated with the foreign key
Example

 $event->ForeignKeyAssociate( $foreignkey,@webID );


ForeignKeyAssociations2ForeignKeys

Returns a list of the foreign keys associated with an ID.

Input Parameters

 1 ID of which to return foreign keys
   (default: ID of object itself)
Output Parameters 1 list of foreign keys of associated objects
Example

 @foreignkeys = $graphic->ForeignKeyAssociations2ForeignKeys( $ID );


ForeignKeyAssociations2IDs

Returns a list of the IDs associated with a foreign key.

Input Parameters

 1 foreign key of which to return ID's
   (default: empty foreign key)
 2 table or ID of table of which to return associated ID's only
   (default: all ID's associated with the ID)
Output Parameters 1 list of IDs of associated objects
Example

 @associatedIDs = $graphic->ForeignKeyAssociations2IDs( $foreignkey );
 foreach( $graphic->ForeignKeyAssociations2IDs( $foreignkey,'web' ) ) {
 # perform action
 }


ForeignKeyDisAssociate

Remove associations between the foreign key and any ID's belonging to a specific table or specific ID's.

Input Parameters

 1    foreign key to remove associations of
      (default: empty foreign key)
 2    table or ID to delete association of
      (default: all ID's associated with the ID of the object)
 3..N additional ID's to remove from association
Example

 $graphic->ForeignKeyDisAssociate( foreignkey,'web' );


GetFieldFromID

Return the value of a field in the record of the specified ID. This directly returns values found in the database without creating any object.

It can either be used to obtain values from the database without updating the object, or to obtain values from the database without having to create an object altogether.

It performs the function opposite to SetFieldInID.

Input Parameters

 1 field to return
 2 ID of record from which to return field
   (default: key 'ID' of object)
 3 table in which to search
   (default: table associated with ID)
Output Parameters

 1 value of specified field
Example

 $organisationID = $vendor->GetFieldFromID( 'organisationID' );
 $contactID = $hn->GetFieldFromID( 'contactID',$eventID );


Handle

Associate a mnemonic handle to an ID and return that mnemonic handle. Such a handle is nothing more than 2 or more letters followed by a number.

Input Parameters

 1 prefix letters to be used
 2 ID to get a mnemonic handle for
   (default: ID of object)
Output Parameters

 1 mnemonic handle
Note

The prefix value is only used when the ID has not had a mnemonic handle assigned to it yet. A default prefix value can be generated with Initials.

Example

 $handle = $hn->Handle( 'EM',$contactID );
 $handle = $hn->Handle( Initials('Willem','de','Wit'),$contactID );
 $handle = $contact->Handle( $contact->Initials( qw(FirstName Prefix Name) );


Handle2ID

Return the ID associated with a handle, previously created with Handle.

Input Parameters

 1 mnemonic handle to return the ID of
Output Parameters

 1 ID associated with handle or undef if not found
Example

 $ID = $hn->Handle2ID( $query );


Hash

Return references to a hash table and an array from the specified object with the given keys.

Input Parameters

 1 field to be used as key in the hash
   (default: 'ID')
 2 field(s) to be used as a value in the hash
   (default: 'name'; multiple fields separated by comma's)
 3 restriction to apply to list
   (default: none)
 4 name of table to query
   (default: name of HN submodule of object in lowercase)
 5 delimiter to be used when multiple value fields are requested
   (default: "\0")
Output Parameters

 1 reference to hash table with all entries in the database for the object
 2 reference to array with ID's order on the value fields
Note

The restriction must be specified without the ``WHERE''. So a good restriction would be ``name LIKE '%liz%'''.

Note

If two values are returned, then only references to the hash and array are returned.

Example

 $category->NewsCategory;
 %category = $category->Hash;
 foreach( keys %category ) {
   print "$_ = $category{$_}\n";
 }


ID2Handle

Return the handle associated with an ID, previously created with Handle.

Input Parameters

 1 ID to return the handle of
Output Parameters

 1 mnemonic handle associated with ID or undef
Example

 $handle = $hn->ID2Handle( $superID );


ID2Random

Return a random string for an ID, if it doesn't exist yet, the random string is created

Input Parameters

 1 ID to return the random string of
 2 Length of the random string required (default is 16)
Output Parameters

 1 random string associated with ID 
Example

 $random = $client->ID2Random( $ID );


Initials

Returns a string composed of the first letters of the values of the keys of the object that are passed to it. Can e.g. be used to create a default prefix for a mnemonic Handle.

Can also be used as an ordinary subroutine: in that case it will return a string composed of the first letters of the list passed to it, uppercased.

Input Parameters

 1..N list of values of which to take the first letter
Output Parameters

 1    uppercase string consisting of the first letters
Example

 $handle = $hn->Handle(
   $contact->_ID,
   $contact->Initials( qw(FirstName Prefix Name) );


Int2Timestamp

This subroutine has moved to the Liz.pm module. If you are using this subroutine as a method, nothing is changed since this module inherits from the Liz.pm module. If you are calling this subroutine, change the call to Liz::Int2Timestamp2(), as this way to access this subroutine will be faded out.


Timestamp2Date

This subroutine has moved to the Liz.pm module. If you are using this subroutine as a method, nothing is changed since this module inherits from the Liz.pm module. If you are calling this subroutine, change the call to Liz::Timestamp2Date(), as this way to access this subroutine will be faded out.


Timestamp2Int

This subroutine has moved to the Liz.pm module. If you are using this subroutine as a method, nothing is changed since this module inherits from the Liz.pm module. If you are calling this subroutine, change the call to Liz::Timestamp2Int(), as this way to access this subroutine will be faded out.


Random2ID

Return the ID for the random string given, if it doesn't exist, the result is undef

Input Parameters

 1 random string to return the ID of
Output Parameters

 1 ID corresponding to the random string
Example

 $ID = $client->Random2ID( '!WYEkP0yQO1wB_qC' );


SetFieldInID

Set the value of a field in the record of the specified ID. This directly sets values found in the database without creating any object.

It can either be used to set values in the database without updating the object, or to set values in the database without having to create an object altogether.

It performs the function opposite to GetFieldFromID.

Input Parameters

 1 value to set
 2 name of field to set in
 2 ID of record in which to set field
   (default: key 'ID' of object)
 3 table in which to search
   (default: table associated with ID)
Example

 $vendor->SetFieldInID( $organisationID,'organisationID' );
 $hn->SetFieldInID( $contactID,'contactID',$eventID );


Parent2ListHash

Return a reference to a list and a hash for a given parent fieldname of Liz::CategorySet. For more information, see Liz::CategorySet::List.

Because most Liz::xxx modules inherit from Liz::SQL, and not from Liz::CategorySet, this method is included here although it should probably be live in Liz::CategorySet.

Input Parameters

 1 field name (in uppercase, B<without> PARENT prefix and ID postfix)
 2 fieldname on which to order the result
   (default: 'sortname')
 3 extra condition to be applied
   (default: none)
Output Parameters

 1 reference to a list of ID's
 2 reference to hash with names
Example

 ($list,$name) = $pictureset->Parent2ListHash( 'AUTHOR' );
Note

Assumes a field CATEGORYSET exists in the object and contains the Liz::CategorySet object to work with.


SQLVersion

Return the SQL version string with which queries are executed.

Output Parameters

 1 string as returned by the SQL-server
Example

 $sqlversion = $self->SQLVersion;


CONTENT METHODS

Liz::SQL specific contents methods for objects, usually just read-only.


Alternates

Return the tokens of the (non-empty) alternates of the object.

Input Parameters

 1    table name to be prefixed
      (default: no table prefix, do not prefix "__" either)
Output Parameters

 1..N the tokens of the alternates of this object
Note

If a table name is specified, then the table name itself (the default alternate) will also be returned. If no table name is specified, then only the non-empty tokens of the alternate (languages) will be returned.


Count

Return the number of records that are current in the table associated with the object. Can also be used as a quick way to find out whether a table exists or not, without having to read the entire list of tables into memory.

Input Parameters

 1 additional extra conditional SQL
   (default: none)
 2 table to be used for searching
   (default: table of object)
Output Parameters

 1 number of records
   (undef: table does not exist)
Example

 $records = $contact->Count;
 $records = $categoryset->Count( "parentID=0" );
Example

 print "Table exists\n" if defined($self->Count);


Created

Return the timestamp when the database record was created.

Output Parameters

 1 timestamp when database record was created


Describe

Return the description of a specific field in the table.

Input Parameters

 1 name of field of which to return the description
 2 name of table in which the field exists
   (default: table of the object)
Output Parameters

 1 SQL type of field (e.g. 'varchar', 'int' or 'blob')
 2 precision (e.g. 10 positions)
 3 modifier(s) (e.g. "UNSIGNED" or "BINARY")
 4 flag: whether null values are allowed
   (1 = allowed, 0 = not allowed)
 5 whether the field is indexed
   (-1 = unique, 1 = multiple, 0 = not indexed)
 6 the default value used if null
 7 any extra information as returned by the SQL driver
Example

 $type = $record->Describe( $field );
 if ($type =~ m^(text|blob)#io) {
   print "<TEXTAREA>...</TEXTAREA>\n";
 } else {
   print "<INPUT TYPE=text....>\n";
 }


LastUpdated

Return the timestamp of when the last record in the current table was updated.

Input Parameters

 1 table to check for records
   (default: table associated with object)
Output Parameters

 1 timestamp when the last record was changed
Example

 FrozenContent( $akzonobel->Company->LastUpdated );


Updated

Return the timestamp when the database record was updated.

Output Parameters

 1 timestamp when database record was updated


HTML METHODS

Some methods for returning HTML from information in the database.


Query2Check

Return the HTML for a specified set of check buttons specified by a SQL query: the first field is considered to be the value, the second field is considered to be name of the field.

Usually not called directly but rather indirectly from another module so that the query innards do not need to be exposed to the innocent user.

Input Parameters

 1 query to perform
 2 name of variable (optionally with namespace if checking is to be performed)
 3 extra HTML to be added between CHECK boxes
 4 flag: return HTML as dummy <CHECK> HTML
Output Parameters

 1 HTML of complete check boxes
Example

 $self->Query2Check(
  "SELECT ID,name
   FROM $self->{'TABLE'}
   WHERE parentID=$parentID
   ORDER BY sortname",
   $variable,$extra,$dummy );


Query2Pulldown

Return the HTML for a pulldown menu specified by a SQL query: the first field is considered to be the value, the second field is considered to be name of the field.

Usually not called directly but rather indirectly from another module so that the query innards do not need to be exposed to the innocent user.

Input Parameters

 1 query to perform
 2 name of variable (optionally with namespace if checking is to be performed)
 3 flag: do not add empty field at beginning
 4 flag: return HTML as dummy <PULLDOWN> HTML
Output Parameters

 1 HTML of complete pulldown menu
Example

 $self->Query2Pulldown(
  "SELECT ID,name
   FROM $self->{'TABLE'}
   WHERE parentID=$parentID
   ORDER BY sortname",
   $variable,$noempty,$dummy );


Query2Radio

Return the HTML for a specified set of radio buttons specified by a SQL query: the first field is considered to be the value, the second field is considered to be name of the field.

Usually not called directly but rather indirectly from another module so that the query innards do not need to be exposed to the innocent user.

Input Parameters

 1 query to perform
 2 name of variable (optionally with namespace if checking is to be performed)
 3 extra HTML to be added between RADIO buttons
 4 flag: return HTML as dummy <RADIO> HTML
Output Parameters

 1 HTML of complete radio buttons
Example

 $self->Query2Radio(
  "SELECT ID,name
   FROM $self->{'TABLE'}
   WHERE parentID=$parentID
   ORDER BY sortname",
   $variable,$extra,$dummy );


Result2Check

Return the HTML for a range of CHECK boxes specified by a SQL result: the first field is considered to be the value, the second field is considered to be name of the field.

Usually not called directly but rather indirectly from another module so that the query innards do not need to be exposed to the innocent user.

Input Parameters

 1 SQL result handle to process
 2 name of variable (optionally with namespace if checking is to be performed)
 3 extra HTML to be put between CHECK buttons
 4 flag: return HTML as dummy <CHECK...> HTML
Output Parameters

 1 HTML of complete checkbox sequence
Example

 $self->Result2Check(
  $self->List( $self,$parentID,'ID,name' ),$variable,$extra,$dummy );


Result2Pulldown

Return the HTML for a pulldown menu specified by a SQL result: the first field is considered to be the value, the second field is considered to be name of the field.

Usually not called directly but rather indirectly from another module so that the query innards do not need to be exposed to the innocent user.

Input Parameters

 1 SQL result handle to process
 2 name of variable (optionally with namespace if checking is to be performed)
 3 extra HTML to be added at the beginning
 4 flag: return HTML as dummy <PULLDOWN...> HTML
 5 flag: extra fields (0=add empty field at beginning, 1=do not, ref(@list)=list)
   (default: add empty field at beginning)
Output Parameters

 1 HTML of complete pulldown menu
Example

 $self->Result2Pulldown(
 $self->List( $self,$parentID,'ID,name' ),$variable,qq(SIZE="3"),$dummy,$noempty );

 $self->Result2Pulldown( $self->List( $self,$parentID,'ID,firstname,name' ) );


Result2Radio

Return the HTML for a range of RADIO buttons specified by a SQL result: the first field is considered to be the value, the second field is considered to be name of the field.

Usually not called directly but rather indirectly from another module so that the query innards do not need to be exposed to the innocent user.

Input Parameters

 1 SQL result handle to process
 2 name of variable (optionally with namespace if checking is to be performed)
 3 extra HTML to be put between RADIO buttons
 4 flag: return HTML as dummy <RADIO...> HTML
Output Parameters

 1 HTML of complete radio button sequence
Example

 $self->Result2Radio(
  $self->List( $self,$parentID,'ID,name' ),$variable,$extra,$dummy );


SUBCLASS METHODS

Some methods for expanding on standard modules such as Liz::Forum, Liz::OCASet and Liz::CategorySet, for client modules. These methods allow fields to be added to or removed from standard modules and to transparently integrate these with the normal operation of the module.


AddExtraField

Add one field to the table of the object and update the meta-information so that the standard module can ``know'' about the additional field.

This method is usually only called when the table is created for the first time, e.g. in the ``new'' method of a sub-classed client module.

Call method AddExtraFields if you need to add more than one field in the table specified by the TABLE field. Use method DeleteExtraField to remove a field that was previously added.

Call method ExtraFields when the extra field(s) should be activated with an object.

Input Parameters

 1 name of field to add
 2 type of field to add
   (default: 'string')
 3 flag: whether field should be indexed (-1=UNIQUE, 0=none, 1=INDEX)
   (default: no, except type=ID and type=timestamp which are always indexed)
 4 table to adapt for extra field
   (default: field TABLE in the object)
Output Parameters

 1 flag: whether successful
Note

Possible values for type are:

 type       SQL-type       what can be placed in field
 ------------------------------------------------------------------
 char       CHAR(1)        a single ASCII character
 string     VARCHAR(255)   a string of upto 255 characters
 ID         INT UNSIGNED   a numeric Liz::SQL compatible ID
 data       BLOB           binary data, up to 64Kbyte
 text       TEXT           text data, up to 64 Kbyte
 timestamp  TIMESTAMP      a timestamp in the form YYYYMMDDHHMMSS
 1          TINYINT        a one-byte integer (-128..127)
 2          SMALLINT       a two-byte integer
 3          MEDIUMINT      a three-byte integer
 4|integer  INT            a four-byte integer
 (other)    direct SQL field specification (you're on your own then)

The integer fields can also have the 'UNSIGNED' modifier specified, e.g. '1 UNSIGNED'.

Note

Fields of type ``ID'' automatically get the string ``ID'' postfixed to their name, if it is not specified already.

Example

 my ($self,$new) = $client->Forum( shift );
 if ($new) {
   if (!$self->AddExtraField( 'client','ID' )) {
     $ERROR = $self->Error;
     return;
   }
 }


AddExtraFields

Add one or more extra fields to the table as specified with the TABLE field in the object. Use method AddExtraField if you need to add fields to another table, or have only one field to add.

Fields are specified in triplets and are added until there are no parameters left.

Please note that there are no default values for the parameters specified with this method. The types of values that can be specified are documented with the AddExtraField method.

Input Parameters

 1 name of field to add
 2 type of field to add
 3 flag: whether field should be indexed
 (repeat until no more triplets)
Output Parameters

 1 flag: whether successful
Example

 $self->AddExtraFields( qw(
  aap   ID      1
  noot  string  0
  mies  text    0
 ) );


AllowExtraFields

Method to set the ``ALLOWED'' field to include any extra fields that are available, so that AUTOLOADed methods will work for the extra fields.

Assumes fields EXTRAFIELDS_ALPHA and EXTRAFIELDS_NUMERIC are already set in the object.

This method is usually used in the ``new'' method of a subclassed module, after a call to method ExtraFields in a void context.

Output Parameters

 1 the object itself, handy for oneliners
Example

 $self->AllowExtraFields;


DeleteExtraField

Delete an extra field that was previously added with AddExtraField or AddExtraFields.

Input Parameters

 1 name of extra field to be removed
 2 table to adapt for extra field
   (default: field TABLE in the object)
Output Parameters

 1 flag to indicate success
Example

 $self->DeleteExtraField( 'client' );


ExtraFields

Return the names of any extra fields that are known to have been added with method AddExtraField.

If called in a void context, sets the fields EXTRAFIELDS_ALPHA and EXTRAFIELDS_NUMERIC.

This method is usually used in the ``new'' method of a subclassed module, after it is determined that it was not a new object as far as the database is concerned.

Input parameters

 1    type of fields to return
      (default: all, 'ALPHA' for alpha fields, 'NUMERIC' for numeric fields)
 2    name of table for which to return the names of extra fields
      (default: field TABLE in object)
Output Parameters

 1..N names of extra fields
Example

 @fields = $self->ExtraFields;
Example

 $self->ExtraFields;


ExtraFieldsUpdate

Contrary to what the name of this method may indicate, it does not update any extra fields in the database. Instead, it returns a complete string that should be added to any SQL UPDATE statement string at the end.

As such, it is usually only called within the ``update'' method of a module.

Output Parameters

 1 string to be added to the SQL update statement
Example

 $extra = $self->ExtraFieldsUpdate;
 $self->Query( "UPDATE $self->{'TABLE'} SET
  name=$name
  sortname=$sortname
  lastseen=$lastseen$extra
   WHERE ID=$ID");

or without any intermediate variable:

 $self->Query( "UPDATE $self->{'TABLE'} SET
  name=$name
  sortname=$sortname
  lastseen=$lastseen".$self->ExtraFieldsUpdate."
   WHERE ID=$ID");


EXTRA TABLES METHODS

Sometimes a module that is subclassed, needs some extra tables that are keyed to an ID also. These methods allow specification of those extra tables, so that e.g. the delete reset methods can do their work cleaning up properly.


ExtraIDTables

Specify which tables are to be associated extra to the object. Usually only called in the ``new'' method of the object.

Input Parameters

 1..N names of extra tables
      (names starting with "_" are assumed to be postfixes to the base table name)
Example

 $self->ExtraIDTables( '_ExtraNames' );


AUTHOR

Elizabeth Mattijsen ( lizperl@INC.nl )

with contributions by:

- Sjoerd Lawende ( sjoerd@xxLINK.nl )


COPYRIGHT

(C) 1998-2000 International Network Consultants


HISTORY

Version 2.69, 30 March 2000

Added extra error messages to method Obtain.

Version 2.68, 27 December 1999

Changed default meaning of third input parameter of ResultFieldNameIndex2Value: now it means the next record if there is no record number specified, instead of always the first. This should make usage in a loop-structure much easier.

Version 2.67, 21 December 1999

New method ResultFieldNameIndex2Value added: return the value of a specific field in a specific record of a result.

New method Result2FieldNames added: return the names of the fields in a query.

Version 2.66, 25 November 1999

Put module name between quotes to fix obscure bug in Perl 5.005x under ModPerl in method Sanity.

Version 2.65, 7 November 1999

New method clone added to create a copy of an object record.

Version 2.64, 17 October 1999

Added support for MiniSum and MiniSums to class method define.

Fixed a documentation glitch at Result2Pulldown.

Version 2.63, 12 October 1999

Removed warn from Associations2IDs which would generate error messages when none where needed.

Version 2.62, 30 September 1999

Now no longer puts Exporter in ISA: it was not needed.

Version 2.61, 28 September 1999

Applied changes of Result2Pulldown to Result2Check and Result2Radio also.

Version 2.60, 27 September 1999

Altered Result2Pulldown so that it can be fed a result object with more than two columns; this means a result obtained from a query like ``SELECT ID,firstname,name ...'' yields a pulldown listing a person's full name (first and last). (SL)

Version 2.59, 19 September 1999

It is now possible to specify an ID with the Comments method, so that comments can be added even though there is no object. Also added check so that any old value is not obtained from the database if it is not necessary.

Version 2.58, 16 September 1999

Support for ``Survey'' and ``Surveys'' added to class method define, to allow for the new Liz::Survey module family.

Version 2.57, 14 September 1999

Added support for extra raw SQL to method Obtain.

Added support for SQL version info in method connect. Created new method SQLVersion for obtaining the SQL version that is being used.

Fixed problem in InitID which would cause freshly created databases to start with an empty NextID table that would cause all other access to fail because of missing entries in the NextID table. Problem probably introduced on September 6.

Method Name2ID now limits the size of the string for the sortname to 255 characters.

Version 2.56, 6 September 1999

Added additional list feature to Result2Pulldown.

Fixed problem with extra HTML in Result2Pulldown.

Removed all ``o'' from s/// and m// where they were not necessary.

Internally shortened methods LastID, Query2Vars, Handle2ID, ID2Handle, Query2Check, Query2Pulldown and Query2Radio.

Removed last reference to ``SELECT count(*)...'' from method InitID.

Changed source to new typography.

Version 2.55, 3 September 1999

Changed the query in create from ``SELECT count(*)...'' to ``SELECT 1...LIMIT 1'' in the hope that this will fix the occasional crash on Search.NL. Did the same in method connect for the check whether table NextID exists.

Version 2.54, 1 September 1999

Method TableUnMeta added: remove Meta-information related to the table of the object.

Version 2.53, 31 August 1999

Method DeleteExtraField added: delete fields that were previously added.

Fixed problem with ExtraFields that would erroneously would include the last updated information of the Meta-field if extra fields of a specific type were requested.

Version 2.52, 30 August 1999

Method Parent2ListHash added: easy for those modules that store ID's in lists and want to convert easily to names.

Support for Forum and Forums added to define. These were somehow forgotten.

Support for MaintainAccess and MemberAccess added to define.

Version 2.51, 29 August 1999

Support for Pingset(s) added to define.

Version 2.50, 17 August 1999

Method Query: #debug now always displays the query, #report displays the query only if there is an error.

Version 2.49, 16 August 1999

Methods Query2ListHash and Result2ListHash now accept an optional third argument: a prefix to be removed from the key to which the other values are keyed in the hash. This was specifically needed for the new sets method.

Added method define, to define often used methods ``at run-time''.

Added method sets, to be inherited by modules that inherit from Liz::SQL.

Added new method NameTablesMeta2Hash which returns a hash of the meta information of a specific name and a list of table names.

Version 2.48, 15 August 1999

Changed method new to also allow a Liz::SQL compatible object as a connection specification: this allows all the Liz::xxx modules to use this ``new'' method also, apart from client modules.

Added method AddExtraFields to facilitate adding multiple fields more easily.

Version 2.47, 14 August 1999

Made sure that the username field in MySQL is limited to 16 characters when the default (the name of the database) is used.

Added method new, to be inherited by client modules.

Added method TableMeta, for associating Meta-information with the table of the object.

Version 2.46, 12 August 1999

Method ID2CategoryQuery now limits to maximum 2048 NOverM instances to prevent e.g. the MySQL engine from crashing from a query that is too large.

Added extra parameter to create which allows specification of whether the table should be dropped first before being (re-)created.

Version 2.45, 9 August 1999

Added new method UnKeepID, as opposed to KeepID.

Simplified method KeepID.

Version 2.44, 3 August 1999

Adapted methods delete and reset to take into account the tables specified with ExtraIDTables.

Added method ExtraIDTables, for specifying extra tables that are also keyed to the ID in an object.

Added method Describe, which returns information about specific fields in a table.

Version 2.43, 25 July 1999

Added method LastUpdated for returning the timestamp of the last updated record in a table.

Version 2.42, 9 July 1999

Changed all occurences of CREATE TABLE to use the new create method when possible.

New method create made to quickly check for the existence of a table and create it if necessary.

Method/Subroutine MySQL added to facilitate migrations between versions of modules and/or different modules.

Changed another 5 occurrences of calls to Exists to defined(Count) in method connect (this should mean a lot with websites that have large databases) and some 5 other, not so important, occurrences of Exists.

Version 2.41, 8 July 1999

Rewrite of method reset so that it now drops all tables affected and optionally creates a new object of the same type, thereby creating the necessary tables from scratch.

Version 2.40, 7 July 1999

Added extra input parameter to method Obtain to allow for key fields other than 'ID'.

Version 2.39, 29 June 1999

Adapted method Count to add new ``#ignore'' query so that no error output is displayed when the requested table does not exist yet.

Added special query values ``#ignore'' and ``#noignore'' to method Query.

Version 2.38, 23 June 1999

Changed method Count so that it can also be used to check for the existence of a table (returns undef if the table specified does not exist yet).

Version 2.37, 13 May 1999

New method LimitIDs2Query added: allows a list of ID's to be checked on another condition.

Method Obtain now sets the KEEPID field automatically. This should not produce any new problems anywhere, and fixes a number of problems.

Version 2.36, 12 May 1999

Method AllowExtraFields now returns the object, so that it can be used in one-liners.

Version 2.35, 10 May 1999

New method ExtraFieldsUpdate added: allows generic modules to automatically update any extra fields from subclasses.

Upgraded methods Obtain and MergeFields to handle extra fields as specified with method AddExtraField.

Fixed problem in method AddExtraField for extrafields with names ``text'' and ``data''. There should probably be more checks here for generic names that would cause conflicts at the SQL-level.

Version 2.34, 9 May 1999

Initial implementation of extra fields for sub-classable modules added: methods AddExtraField, AllowExtraFields, ExtraFields.

Fixed potential problem with Meta that would cause the updated information of a one-record match on variable name wildcards to be interpreted as the value of the second record. A single match on wildcard specification will now only return the value.

Added Liz.pm compatible append functionality to Meta: any value that starts with ``+='' will now be appended to the current value, with either a null-byte as a delimiter, or what was previously specified with the AppendString method.

Version 2.33, 7 May 1999

Added return parameter and example to Result2Vars: this method can now be used in a while {} structure, just as a ``fetchrow''.

Version 2.32, 5 May 1999

Fixed problem in reset, which would call Bounds instead of Boundaries.

Version 2.31, 21 April 1999

New global variable Liz::SQL::QUERY now always contains the complete query of the last (indirect) call to method Query.

Global variable Liz::SQL::ERROR is now set after each query with the error message (if any) of the last call to method Query.

Version 2.30, 12 April 1999

Error reporting in Query now correctly checks for Liz::Perl and calls the appropriate print routine when necessary.

Version 2.29, 9 April 1999

New method SortID added: allows easy sorting of ID's using a sort-routine that accepts the object as one of the parameters.

Version 2.28, 18 March 1999

Fixed problem in method delete if any categories were involved (table name was incorrectly spelled as ``ID2CategoryList'').

Version 2.27, 12 March 1999

Check all source for m// and s/// and added ``o'' flag when appropriate. This should introduce a significant speed increase at the expense of a larger (but shared) memory footprint.

Moved the Int2Timestamp and Timestamp2Int methods/subroutines to the Liz.pm module.

Version 2.26, 9 March 1999

Moved the Timestamp2Date suboutine and all associated methods and internal subroutines (Nederlands, Francais, Deutsch, English, SetLanguage, ConvertNL, ConvertFR, ConvertDE) to the Liz.pm module.

Version 2.25, 2 March 1999

New methods ID2Random and Random2ID: to link IDs to random strings.

Version 2.24, 2 February 1999

New method ID2Description: return the description field associated with an ID.

Added fieldname specification option to ID2Name.

Version 2.23, 1 February 1999

Added LIMIT-option to Find.

Version 2.22, 24 January 1999

Method NextID now defaults to the field TABLE of the object itself if no name specified and returns without an ID is no valid name is specified.

Version 2.21, 20 January 1999

Adapted AssociateID so that a 0 is not a valid 2nd ID anymore. This allows a number of other routines and methods to use a list with just a zero in it as an indication to remove all associations.

Version 2.20, 14 January 1999

Method Step added: returns the step between ID's from different tables.

Version 2.19, 13 January 1999

New method OldCategoryList2ID added: allows for conversion of categorylists from older versions of Liz::SQL.

Version 2.18, 11 January 1999

Method Associations2IDs now returns an empty list if no ID's have been issued for the table specified.

Method Count now also allows for the specification of the table in which to count records.

Version 2.17, 10 January 1999

New method DisAssociateID: remove some or all associations to a specific ID.

Changed method DisAssociate to be just a front-end to DisAssociateID instead of a complete routine of its own.

Fixed problem in DisAssociate that would prevent ID's of a specific table from being removed.

Fixed problem with AllAssociationsID which would cause the wrong ID to be associated when being applied to an ID other than the object's own ID.

Added extra checks to Associations2IDs that will take care of the problem where a table was created after the current process made the initial connection to the database.

Version 2.16, 8 January 1999

Removed bug from <DisAssociate>, variable $ID2 put between single quotes didn't work...

Version 2.15, 6 January 1999

Added methods <AllAssociations> and <AllAssociationsID> to set or return all associated ID's for a specific table.

Version 2.14, 4 January 1999

Now uses the Liz::Calc::Intel subroutine to determine whether we're running on an Intel Architecture.

Version 2.13, 29 December 1998

Methods delete and reset now properly remove affected ID's from the Associations and ForeignKeyAssociations table, as well as any associated categorylist tables.

Version 2.12, 24 December 1998

Method Find now inserts SQL-wildcard characters for any non-alphanumeric character in the search string. This should increase the number of hits significantly.

Version 2.11, 22 December 1998

Added extra SQL-condition to Find.

Version 2.10, 20 December 1998

New method Count: return the number of records that are associated with an object.

Version 2.09, 15 December 1998

New method AssociateID: a more general version of method Associate.

Method delete now also removes categoryLIST entries if the object itself is a Liz::CategorySet-like object.

Version 2.08, 9 December 1998

Method Boundaries now also accepts an ID value as parameter.

Version 2.07, 7 December 1998

Fixed another severe problem with ID2CategoryQuery and ID2Name, which would produce strange values for NextID step sizes <1000000. Now, any step size is ok.

Version 2.06, 4 December 1998

Method Associations2IDs now allows multiple ID's to be specified to obtain the associated ID's of, and returns only different (DISTINCT) ID's if looking for more than one ID.

Version 2.05, 3 December 1998

Fixed conceptual problem in NextID: all ID's issued before today have potential problems when used in a CategoryLIST environment.

Fixed another severe problem with ID2CategoryList, which would produce strange values for NextID step sizes <1000000. Now, any step size is ok.

Version 2.04, 2 December 1998

Method ID2CategoryQuery now takes a shortcut if maximum number of matches is specified.

New method ForeignKeyAssociations2ForeignKeys added: return the foreign keys associated with an ID.

Version 2.03, 30 November 1998

New method Find added: search the sortname field for matching strings.

Method Name2ID now does not check the name field anymore. This was probably causing way too many problems, and huge slowdowns because of unindexed ``name'' fields. This ** MAY ** cause problems in some situations. In those cases, specify 'name' as the 7th input parameter to return to the ``old'' behaviour.

Version 2.02, 29 November 1998

New method ForeignKey2IDs added: allows quick search on foreign keys and their associated ID's, particularly useful for the Liz::Forum and Liz::OCASet... modules.

Version 2.01, 26 November 1998

Streamlined deleting of ID's in DisAssociate by using IN () SQL clause.

Removed extraneous code in Associations2IDs which apparently was intended to perform a function similar to DisAssociate.

Support for associations of foreign keys (any string up to 255 bytes) and ID's added. It is now possible for all Liz::SQL compatible objects to associate ID's to a foreign key with ForeignKeyAssociate, to obtain the ID's associated with a foreign key with ForeignKeyAssociations2IDs and to remove the associations between a foreign key and ID's with ForeignKeyDisAssociate.

Any Liz::SQL compatible object now also creates the table ``ForeignKeyAssociations'' in the connected database.

Version 2.0, 25 November 1998

Methods Deutsch, Francais, English and Nederlands now return the object itself for handy in-liners.

Fixed problem in Log which was introduced on October 7th, 1998.

Version 1.99, 21 November 1998

Added internal INTEL order flag to fix problem in NextID when being run on architectures that user INTEL byte order. Internal INTEL flag is now being set in the connect method.

Version 1.98, 18 November 1998

Method Timestamp2Date now also accepts ``unix time'' values (number of seconds since Jan. 1, 1970).

Version 1.97, 11 November 1998

Added method Result2ListHash so that we can easily generate these from result handles that are returned from sub-module methods.

Version 1.96, 28 October 1998

Fixed problem with NextID number generation if total number was >64K (which was almost always the case) ;-( This means that a lot of databases so far have problems with ID's that are used with categoryFIELDs.

Version 1.95, 27 October 1998

Fixed problems in Obtain and ID2Name, which were not yet properly set up to handle Alternates.

Method Name2ID now also returns the name and the sortname of the record. Also upgraded to handle alternates internally rather than externally (this was needed because otherwise NextID would create entries for each alternate seperately, which is not what we want).

New method Alternates added, several other methods now call this method instead of doing the splitting themselves.

Version 1.94, 24 October 1998

Support for alternates added to delete and reset. Also fixed problem with deleting ID's from associated categoryset.

Support for alternates added to Obtain and Bulk. UnBulk did not need to be changed.

Version 1.93, 10 October 1998

Added extra HTML parameter to Result2Pulldown.

Version 1.92, 7 October 1998

Reduced memory footprint by using fully qualified global variables and external subroutines only.

Version 1.91, 4 October 1998

New method TableFields2Result added: allows for easy queries on tables associated with an object.

Version 1.90, 27 September 1998

New language methods Francais and Deutsch added. Also added internal conversion routines ConvertFR and ConvertDE.

Version 1.89, 23 September 1998

Method Obtain now checks for valid ID.

Version 1.88, 21 September 1998

Method ID2Name now returns undef without querying the database if the specified ID is 0.

Version 1.87, 20 September 1998

Method MergeFields now return immediately if there is no ID in the object.

Method delete now also removes any associated categories from the category set associated with the ID('s).

Version 1.86, 15 September 1998

New method ID2CategoryQuery: returns the string to be used in a query were any or all or a number of categories should match.

Version 1.85, 13 September 1998

Method Obtain now correctly handles translation of database field 'deta' to object field 'DATA'.

Version 1.84, 8 September 1998

Fixed problem in NextID: byte value 92 (\) was being allowed for ID values, when it shouldn't as the \ can not be used in the clear inside a SQL query.

Version 1.83, 7 September 1998

Method ID2CategoryList now ignores 0 entries.

Version 1.82, 6 September 1998

New method ID2CategoryQuery: returns string that can be used for AND or OR searching of categories.

Method ID2CategoryList now removes double entries from the list of ID's specified.

Method Boundaries now defaults to field 'TABLE' in the object.

Version 1.81, 24 August 1998

New method Result2Check added: return HTML for check boxes from a result in the database.

New method Query2Check added: return HTML for check boxes from a query in the database.

New method Result2Radio added: return HTML for radio buttons from a result in the database.

New method Query2Radio added: return HTML for radio buttons from a query in the database.

New method Result2Pulldown added: return HTML for a pulldown menu from a result in the database.

New method Query2Pulldown added: return HTML for a pulldown menu from a query in the database.

Added AuthorID field to method Log.

Version 1.80, 21 August 1998

New module method Log added: generic methods for logging information about events in a database.

All ID2... methods now check for valid ID and return immediately if an invalid ID was passed.

Version 1.79, 19 August 1998

New method reset added: removes all records from the table that belongs to the object.

New method Result2Vars added: sets the fields of the next record of a SQL result handle in the indicated namespace as variables.

New read-only content methods Created and Updated added.

Version 1.78, 15 August 1998

Method Query2ListHash now allows any number of fields to occur in the query: for all but the first field references to hashes will be returned.

New method Query2Vars: obtains all fields from a query and stores them into variables of the indicated namespace.

New method Vars2Query: obtains values of variables in a namespace and stores them into a record of the indicated table.

Version 1.77, 13 August 1998

Method delete now also allows a number of ID's to be deleted instead of just deleting the record of the current object.

New method Query2ListHash: returns references to a list and a hash according to the specified query.

Version 1.76, 11 August 1998

Method delete now also resets the KEEPID flag: this should be done so that the object can always be updated and get a new ID.

Version 1.75, 30 July 1998

New method MetaNames added: returns the names of all the variables associated with a given table.

New method UnMeta added: deletes meta-information from a given table.

Version 1.74, 27 July 1998

Method Exists now allows excluding wildcard specification (anything that starts with !). Since method IDExists inherits this, that function is now also available for that method.

Method ID2Name now optimised to only look for name field if name only requested.

Version 1.73, 26 July 1998

Method CategoryList2ID now defaults to CategoryList within object itself.

Method Query now calls the currently active Warn routine of ModPerl in a ModPerl environment. The previous fix did not work.

Version 1.72, 18 July 1998

Method Query now calls the currently active print routine of ModPerl in a ModPerl environment. This should place pre-HTTP header error output inside a document instead of make it part of the header and cause all sorts of problems.

Method Meta now also returns the last updated time of the information if there is only one match with the requested table and name.

Version 1.71, 8 July 1998

Now does not import anything from the Liz.pm module anymore: it was not needed and it fixed a name clash with the new Hash subroutine of the Liz.pm module.

Version 1.7, 29 June 1998

Method InitID now defaults to 1000000 for a step (was 100000) and creates a 32 byte index instead of a 8 byte index. These should be better defaults for a lot of cases.

Language support added in method Timestamp2Date.

New Language changing methods: English and Nederlands.

Version 1.62, 15 June 1998

Method Initials now checks for digits as the first character and ignores those if applicable.

Version 1.61, 13 June 1998

Method Associations2IDs now allows direct specification of the ID to obtain associated ID's of. This is of use when there is no object, but just an ID and it is too much trouble to create the object.

Version 1.6, 7 June 1998

New method IDExists: checks whether an ID value exists in the database and returns a list of table/field/ID triplets in which the ID occurs.

Version 1.51, 4 June 1998

Fixed table creation problem in connect that occurred on newer versions of MySQL.

Fixed problem in Obtain: database field ``taxt'' is now converted to fieldname ``TEXT'' instead of ``TAXT''.

Version 1.5, 1 June 1998

New method Bulk: obtains all records of a table into memory to facilitate faster access e.g. to generate HTML-pages.

New method Obtain: obtains all fields of a record into the fields of the object, either from the database or from memory (if method Bulk had been called previously).

New method UnBulk: makes sure all subsequent calls to method Obtain are done from the database instead of from memory.

New method MergeFields: merge fields from existing database record. Especially handy when there was a partial match on name and/or other fields.

Version 1.4, 29 May 1998

New method FieldExists: check whether one or more fields exists in a given table.

Version 1.3, 27 May 1998

Method Incarnate will now remove the KeepID marker in the objects it creates, thereby ensuring that incarnated objects can not change the tables to which they belong.

Version 1.2, 26 May 1998

Method ActivateObject now always activates the object, even if there is no ID for the object yet.

Fixed problem in method Name2ID when using different fieldnames.

Method Incarnate will now not remove existing objects in the indicated field.

New method Password added: allows setting of the PASSWORD field and automatically encrypts that field.

New method UserName added: allows setting of the USERNAME field and automatically makes sure the contents meet the username specification (single word lowercase).

Moved all special content methods to the Liz.pm module:

 FirstName
 IP
 Name
 Password
 SortName
 UserName
Version 1.1, 25 May 1998

New method IP for storing IP-number with an object.

Method Meta now locks tables unless inhibited.

Method/Subroutine SysTime2Timestamp moved to the Liz.pm module.

Version 1.0, 18 May 1998

Migrated a lot of methods to the Liz module itself, so they can be inherited by other modules as well without having to inherit from Liz::SQL. These are now inherited from Liz.pm.

Subroutines moved are:

 allow
 AUTOLOAD
 Debug
 Dump
 Error
 Get
 Inherit
 TMP
 Variable

Generic ``delete'' method added.

Version 0.51, 14 May 1998

Extra flag for Variable added: now optionally creates a list of fields that were changed.

Version 0.5, 9 May 1998

Support for Meta information added. New method Meta allows specification of meta-information, primarily, but not neccesarily, associated with tables.

All methods now support the TABLE field to indicate the table on which the object is supposed to operate.

Version 0.43, 5 May 1998

Method Name2ID now allows adding a new record even if there is a record with the indicated name already.

Version 0.42, 3 May 1998

Constructor method Sanity added: creates the Liz::SQL::Sanity object for checking the consistency of the database and optionally repairing it. object for checking the consistency of the database and optionally repairing it.

Method NextID adapted so that it will first use empty ``slots'' in the NextID table before moving on to higher numbers.

Version 0.41, 27 April 1998

Method Abort added: allows an object that was previously associated to the current object, to be removed.

Method AssociateObject now marks the object, to which another object is associated, as changed.

Method Incarnate now first takes the table associated with the ID as the name of the method to perform. Previously the name of the field was always used to create the name of the method. This allows the Address object of e.g. Contact in HN to be an Organisation.

Version 0.4, 25 April 1998

Support for Associations added to ``connect'': table 'Associations' now gets created if it doesn't exist yet.

Method Associate added. Associate on or more ID's with the object.

Method Associations2IDs added. Returns ID's that are associated with the object.

Method DisAssociate added. Delete associations with ID's that are associated with the object.

Method FindID now automatically sets the KeepID flag for the object when an ID was already specified.

Version 0.31, 20 April 1998

Changed auto-append function in Variable from values starting with space, to values starting with ``+=''. The ``+='' itself is not appended.

Version 0.3, 5 April 1998

Method SetFieldInID added. Allows direct database access to a field associated with an ID without having to create objects.

Method ID2Handle added. Return the handle associated with an ID or creates one.

Method GetFieldFromID added. Allows direct database access to a field associated with an ID without having to create objects.

Method Timestamp2Date added. Convert a SQL timestamp to a date in ``strftime'' format.

Methods Int2Timestamp and Timestamp2Int added. Convert between integer date values (year,month,day etc.) and SQL timestamp format.

Version 0.2, 29 March 1998

Fixed problem with method Handle: now works ok! Method Handle2ID added.

Version 0.1, 14 March 1998

First version of this true Perl module.