Liz::SQL - generic SQL access and subroutine library
@ISA = qw(... Liz::SQL); use Liz::SQL;
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.
The following methods are supposed to be called as class methods for client modules.
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''.
1..N method specifications to define
in category.lp:
$class->define( qw(CategorySets CategorySet) );
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
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.
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)
1 reference to list of identification names in the database 2 reference to hash with full names 3 reference to hash with database versions
($token,$name) = $client->tracksets; in Client.pm:
sub tracksets { shift->SUPER::sets( shift,'prefix',@ ) }
The following methods return objects and are either called as class methods or as subroutines..
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.
1 reference to routine for database connection or Liz::SQL compatible object (default: &Connect in the client module's namespace)
1 instantiated client object
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' ) }
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!
Embed a Liz::SQL object in the object that calls this method. Creates connection with the database of your choice.
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)
1 reference to instantiated database object (returns undef if connect failed)
$SQL = $self->connect( $SQL,\&Connect );
Check the variable $Liz::SQL::ERROR if there was an error when creating this object.
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.
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)
1 MySQL object or error message
sub Connect { Liz::SQL::MySQL( 'database','password' ) }
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.
1 reference to instantiated sanity object (returns undef if failed)
These methods are usually not used directly on the Liz::SQL object, but rather through the modules that inherit from Liz::SQL.
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.
1 the original object
$object = $module->Object( $ID ); $object->clone; $object->update;
$module->Object( $ID )->clone->update;
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.
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)
1 flag whether table already existed
$existed = $self->create( $table,<<EOD ); ID INT INTEGER NOT NULL, sortname VARCHAR(255) BINARY NOT NULL, UNIQUE (ID), INDEX (sortname) EOD
Delete one or more records associated with the Liz::SQL inherited object from the database.
1..N ID's of records to be deleted
(default: only delete record of object's ID itself)
1 flag: whether action was successful
$message->delete; $message->delete( 100,101,102 );
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.
1..N postfixes of table names that are associated with the object
1 new object
$categoryset->reset;
$categoryset = $categoryset->reset;
These methods allow loading of entire tables into memory, while allowing transparent access for creating objects.
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)
$object->Bulk;
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.
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.
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.
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)
1 flag: whether operation successful ( 1 = success, undef if failed)
unless( $object->Obtain( $ID ) ) {
print "There is no information with ID $ID\n";
}
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.
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..
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.
$object->UnBulk;
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.
These methods are primarily used within the module that makes use of the Liz::SQL module and not by ``end-user'' applications.
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.
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)
1 list of tablenames that matched (in scalar context: number that matched)
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.
$object->Query( 'DROP TABLE zip' ) if $object->Exists( 'zip' );
print "All tables in the database:\n";
foreach( $object->Exists ) {
print " $_\n";
}
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.
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.
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)
1 list of fieldnames that matched (in scalar context: number that matched)
print "All fields in the table $table:\n";
foreach( $object->FieldExists( '',$table ) {
print " $_\n";
}
Return the names of tables and fields in which the specified ID exists. Names are returned as ``table/field/ID'' triplets.
1 ID for which to check existence 2 wildcard specification of tables to check (default: all tables)
1 list of table::fieldname::ID that contains the ID (in scalar context: number that matched)
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.
print "ID $ID occurs in:\n";
foreach( $object->IDExists( $ID ) {
print " $_\n";
}
Log information of an event in the database. Log messages may be up to 255 bytes long and are kept in monthly archives.
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'})
1 ID under which this log entry is known
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'.
1..N the names of the keys to be quoted
1..N list with quoted values (can be used in SQL queries)
my( $city,$street,$zipcode ) = $object->Quote( qw(CITY STREET ZIPCODE) );
Return zeroed strings for a number of keys of an object that have the value 'undef'.
1..N the names of the keys to return zeroes for when they are undef
1..N list with zeroed values (can be used in mySQL queries)
my( $parentID,$originalID ) = $object->Zero( qw(PARENTID ORIGINALID) );
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.
$object->KeepID;
Initialize the numerical ID's of a table or of all tables
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
$object->InitID; $object->InitID( 100000 ); $object->InitID( 'table' );
Return the next ID for a new object in a table. Can be used as a method for any HN (sub-)object.
1 name of the table (default: table of object) 2 flag: do not lock table (already locked elsewhere) (default: lock table NextID when updating)
1 ID value for the new object
$ID = $object->NextID( 'table' );
Return the ID of a table that will be issued next.
1 name of table of which to obtain ID to be issued next
1 ID to be issued next (undef if not found)
$ID = $object->LastID( 'table' );
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.
$object->UnKeepID;
Return the ID boundaries of a table.
1 table name or valid ID of table (default: field 'TABLE' in the object)
1 low boundary of table 2 high boundary of table
$low = $object->Boundaries( 'table' ); ($low,$high) = $object->Boundaries( 'table' );
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.
1 step value (usually 1000000)
if( $_[0] =~ m#^(\d+)$# and $1 < $self->Step ) {
print "It is a flag!\n";
}
Return the name of the table to which an ID belongs.
1 ID value
1 name of table
$table = $object->Table( $ID );
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.
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')
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)
If the name is already escaped and sortname is already set with lowercase characters, then you should use method ``_Name2ID''.
$ID = $object->Name2ID( 'table','Norway' );
Return the description of an ID.
1 ID value 2 table of ID (default: look up name of table with ID)
1 description
$description = $object->ID2Description( $ID );
Return the name and optionally the sorted name of an ID.
1 ID value 2 table of ID (default: look up name of table with ID) 3 name of field containing name (default: 'name')
1 name of ID 2 name on which the ID is sorted
$name = $object->ID2Name( $ID );
Convert a list of ID's to a category list string.
1..N list of ID's of the field to be converted
1 categoryLIST field string
$categoryLIST = $self->ID2CategoryList( @ID );
Convert a list of ID's to a string that can be used in a query.
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
1 string to be used in a query
$query = $self->ID2CategoryQuery( 'categoryLIST',0,@ID ); $result = $self->Query( "SELECT ID FROM table WHERE $query" );
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.
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)
1..N list of ID's of the field
@ID = $self->CategoryList2ID( $categoryLIST );
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.
1 old categorylist format string 2 base ID to be used for the ID's
1..N old ID's (B<not> using the new numbering format)
@oldID = $adformatie->OldCategoryLis2ID( $categorylist,200000 );
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.
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')
1..N list of parent ID's (own parent first, then grandparent, etc).
@ID = $hn->ID2AncestorIDs( $ID );
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.
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')
1..N list of parent names (own parent first, then grandparent, etc).
@names = $hn->ID2AncestorNames( $ID );
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.
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)
1 reference to list of children ID's (own first, then grandchildren, etc). 2 reference to list of level (0=children, 1=grandchildren, etc)
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.
($ID,$level) = $hn->ID2ChildrenIDs( $ID );
foreach( 0..$#$ID ) {
print "$$level[$_]: $$ID[$_]\n";
}
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.
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)
1 reference to list of children names
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.
($name,$level) = $hn->ID2ChildrenNames( $ID );
foreach( 0..$#$name ) {
print "$$level[$_]: $$name[$_]\n";
}
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.
1 reference to list of ID's to process 2 reference to subroutine to perform the checking 3..N additional parameters to be passed
1 reference to list, sorted in the order indicated
$list = $forum->SortIDs( $list,\&SortByName );
sub SortByName {
my( $self,$ID1,$ID2 ) = @_;
$self->ID2Name( $ID1 ) cmp $self->ID2Name( $ID2 );
}
These methods allow you to specify meta-information with tables or any other meta-information associated with a database.
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.
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")
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
@value = $object->Meta( 'table','*' );
$object->Meta( 'table','variable',$value ); $value = $object->Meta( 'table','variable' ); ($value,$updated) = $object->Meta( 'table','variable' );
Return the variable names of the Meta-information associated with the indicated table.
1 name of table to associate information with
2 identification name of information (like the name of a variable)
(default: '*')
1..N names of 'variables'
print "Variables with table 'table':\n";
foreach( $object->MetaNames( 'table' ) {
print " $_\n";
}
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).
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)
1 reference to a hash, keyed to table names
$hash = $trackset->NameTablesMeta2Hash( 'Name',@token );
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.
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)
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
@value = $object->TableMeta( '*' );
$object->TableMeta( 'key',$value ); $value = $object->TableMeta( 'key' ); ($value,$updated) = $object->TableMeta( 'key' );
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.
1 name of the field to remove Meta information of (default: all, may include wildcards such as * and ?)
$object->TableUnMeta;
$object->TableUnMeta( 'Score' );
Remove meta-information associated with a table from the database.
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 ?)
$object->UnMeta( 'table' ); $object->UnMeta( 'table','variable' );
These methods are available for performing SQL queries and managing its results in the Perl namespace in various ways.
Perform one or more generic queries on the HN object. Returns a standard object on which MySQL methods can be applied.
1..N queries to be performed
1 result object of last executed query
(currently a standard MySQL statement handle)
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'.
$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" );
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.
1 SQL query to be performed
2 prefix to remove from keys
(default: none)
1 reference to list with values of first field (usually ID) 2..N reference to hash with values of Nth field
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.
1 SQL result handle
2 prefix to remove from keys
(default: none)
1 reference to list with values of first field (usually ID) 2..N reference to hash with values of Nth field
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.
1 SQL query to be performed 2 namespace in which to store the variables (default: namespace of calling routine)
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.
1 SQL result handle 2 namespace in which to store the variables (default: namespace of calling routine)
1 number of variables set (undef if no result, 0 if no more records, >0 vars set)
$result = $object->Query( "SELECT * FROM table" );
while ($object->Result2Vars( $result )) {
# do something with the variables
}
Convert a specification of a table and a number of fields into an SQL result handle, optionally with extra limitations.
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)
1 SQL statement handle
$result = $object->TableFields2Result( 'table','ID,name' );
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.
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)
Limit a list of ID's to a query on the table to which the object belongs.
1 extra query to be performed 2..N ID's of which should be determined they match the query
1..N ID's that also match the query
@sendID = $contact->LimitIDs2Query( "options LIKE '%W%'",@memberID );
Return the field names of the fields of a query.
1 result handle for which to return the names of fields
1..N names of the fields in the query
foreach( $client->Result2FieldNames ) {
print "$_\n";
}
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.
Return the value of a field in a specific record in a result-handle.
1 result handle 2 fieldname 3 ordinal number of record (default: next; first if not called before for this result)
1 value of the specified field in the specified record
$value = $client->ResultFieldNameIndex2Value( $result,'MessID',4 );
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.
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.
The following methods are used mainly when handling objects within objects, and the updating of these objects to the database.
Abort a number of child objects, Object names are specified as ``object key''.``type'' where type can be either ``ID'' or ``LIST''.
1..N child object field name specification
(default for type: 'ID')
$self->Abort( qw(ADDRESS) );
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.
1..N objects to be adopted
$self->Adopt( $hn->City );
Propagate a method of an object to all its children. Children can be specified with the Adopt method.
1 method to be propagated
$self->Propagate( 'update' );
Create a number of child objects, Object names are specified as ``object key''.``type'' where type can be either ``ID'' or ``LIST''.
1 Liz::... object for spawning the children
2..N child object field name specification
(default for type: 'ID')
$self->Incarnate( $hn,qw(CATEGORY.LIST WEB CONTACT) );
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.
1..N list of field names to check
Reap result values from child objects, Object names are specified as ``object key''.``type'' where type can be either ``ID'' or ``LIST''.
1..N child object field name specification
(default for type: 'ID')
$self->Reap( qw(CATEGORY.LIST WEB CONTACT) );
Return the object associated to a field of the current object.
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)
$contact = $news->ActivateObject( 'CONTACT' ); $organisation = $news->ActivateObject( 'ORGANISATION','ADDRESS' );
Associate an object to a field of the current object and adopt the object to allow for method propagation.
1 object of which to associate the ID 2 field to associate object with within self
$news->AssociateObject( $contact,'CONTACT' );
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.
1 method name that will be calculated 2 method name that should be used
$hn->SpecialMethod( 'Bannergroup' => 'BannerGroup' );
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.
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)
1..N old/current ID's associated with ID
$event->AllAssociations( 'web',@webID ); @webID = $event->AllAssociationsID( 'web' );
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.
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)
$event->AllAssociationsID( $ID,'web',@webID ); @webID = $event->AllAssociationsID( $ID,'web' );
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.
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
$event1->Associate( @webID ); $event2->Associate( 1,$webID4 );
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.
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
$event1->AssociateID( $ID,@webID ); $event2->AssociateID( $ID,1,$webID4 );
Returns an array with the associated IDs of this object.
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)
1..N IDs of associated objects
@associatedIDs = $graphic->Associations2IDs;
foreach( $graphic->Associations2IDs( 'web' ) ) {
# perform action
}
Set/Return the Comments for the specified object, either from existing information or for a new entry.
1 text of comment to be associated (default: no change) 2 ID with which to associate comments (default: field 'ID' in object)
1 current/old comments
$web = $hn->Web; $web->Comments( <<EOD ); This is a test entry for the Hospitality Net Index EOD $comments = $web->Comments;
Break the associations to the object's own ID with the given IDs.
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
$graphic->DisAssociate( @webIDs );
Break the associations of the given ID with the given other IDs.
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
$graphic->DisAssociateID( $eventID,@webIDs );
Return the ID's that match a string and the ``sortname'' field in the table associated with the object.
1 (part of) the name to search for
2 extra SQL condition to apply
(default: none)
3 records to return
(default: all)
1..N ID's of objects that match the specification
@ID = $libris->Title->Find( 'apenootjes' );
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.
1 table in which to search
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)
$ID = $self->FindID( 'venue',$name,$sortname );
Return the ID's that are associated with the foreign key of the table of the Liz::SQL compatible object.
1 foreign key to look for
(default: empty foreign key)
2 name of foreign key field
(default: 'foreignkey')
1..N list of IDs that occur with that foreign key in the table of the object
$ID = $forum->ForeignKey2IDs( $messageID );
Associate one or more ID's to a foreign key.
1 foreign key to associate with
(default: empty foreign key)
2..N list of IDs to be associated with the foreign key
$event->ForeignKeyAssociate( $foreignkey,@webID );
Returns a list of the foreign keys associated with an ID.
1 ID of which to return foreign keys (default: ID of object itself)
@foreignkeys = $graphic->ForeignKeyAssociations2ForeignKeys( $ID );
Returns a list of the IDs associated with a foreign key.
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)
@associatedIDs = $graphic->ForeignKeyAssociations2IDs( $foreignkey );
foreach( $graphic->ForeignKeyAssociations2IDs( $foreignkey,'web' ) ) {
# perform action
}
Remove associations between the foreign key and any ID's belonging to a specific table or specific ID's.
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
$graphic->ForeignKeyDisAssociate( foreignkey,'web' );
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.
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)
1 value of specified field
$organisationID = $vendor->GetFieldFromID( 'organisationID' ); $contactID = $hn->GetFieldFromID( 'contactID',$eventID );
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.
1 prefix letters to be used 2 ID to get a mnemonic handle for (default: ID of object)
1 mnemonic handle
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.
$handle = $hn->Handle( 'EM',$contactID );
$handle = $hn->Handle( Initials('Willem','de','Wit'),$contactID );
$handle = $contact->Handle( $contact->Initials( qw(FirstName Prefix Name) );
Return the ID associated with a handle, previously created with Handle.
1 mnemonic handle to return the ID of
1 ID associated with handle or undef if not found
$ID = $hn->Handle2ID( $query );
Return references to a hash table and an array from the specified object with the given keys.
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")
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
The restriction must be specified without the ``WHERE''. So a good restriction would be ``name LIKE '%liz%'''.
If two values are returned, then only references to the hash and array are returned.
$category->NewsCategory;
%category = $category->Hash;
foreach( keys %category ) {
print "$_ = $category{$_}\n";
}
Return the handle associated with an ID, previously created with Handle.
1 ID to return the handle of
1 mnemonic handle associated with ID or undef
$handle = $hn->ID2Handle( $superID );
Return a random string for an ID, if it doesn't exist yet, the random string is created
1 ID to return the random string of 2 Length of the random string required (default is 16)
1 random string associated with ID
$random = $client->ID2Random( $ID );
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.
1..N list of values of which to take the first letter
1 uppercase string consisting of the first letters
$handle = $hn->Handle( $contact->_ID, $contact->Initials( qw(FirstName Prefix Name) );
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.
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.
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.
Return the ID for the random string given, if it doesn't exist, the result is undef
1 random string to return the ID of
1 ID corresponding to the random string
$ID = $client->Random2ID( '!WYEkP0yQO1wB_qC' );
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.
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)
$vendor->SetFieldInID( $organisationID,'organisationID' ); $hn->SetFieldInID( $contactID,'contactID',$eventID );
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.
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)
1 reference to a list of ID's 2 reference to hash with names
($list,$name) = $pictureset->Parent2ListHash( 'AUTHOR' );
Assumes a field CATEGORYSET exists in the object and contains the Liz::CategorySet object to work with.
Return the SQL version string with which queries are executed.
1 string as returned by the SQL-server
$sqlversion = $self->SQLVersion;
Liz::SQL specific contents methods for objects, usually just read-only.
Return the tokens of the (non-empty) alternates of the object.
1 table name to be prefixed
(default: no table prefix, do not prefix "__" either)
1..N the tokens of the alternates of this object
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.
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.
1 additional extra conditional SQL (default: none) 2 table to be used for searching (default: table of object)
1 number of records (undef: table does not exist)
$records = $contact->Count; $records = $categoryset->Count( "parentID=0" );
print "Table exists\n" if defined($self->Count);
Return the timestamp when the database record was created.
1 timestamp when database record was created
Return the description of a specific field in the table.
1 name of field of which to return the description 2 name of table in which the field exists (default: table of the object)
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
$type = $record->Describe( $field );
if ($type =~ m^(text|blob)#io) {
print "<TEXTAREA>...</TEXTAREA>\n";
} else {
print "<INPUT TYPE=text....>\n";
}
Return the timestamp of when the last record in the current table was updated.
1 table to check for records (default: table associated with object)
1 timestamp when the last record was changed
FrozenContent( $akzonobel->Company->LastUpdated );
Return the timestamp when the database record was updated.
1 timestamp when database record was updated
Some methods for returning HTML from information in the database.
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.
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
1 HTML of complete check boxes
$self->Query2Check(
"SELECT ID,name
FROM $self->{'TABLE'}
WHERE parentID=$parentID
ORDER BY sortname",
$variable,$extra,$dummy );
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.
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
1 HTML of complete pulldown menu
$self->Query2Pulldown(
"SELECT ID,name
FROM $self->{'TABLE'}
WHERE parentID=$parentID
ORDER BY sortname",
$variable,$noempty,$dummy );
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.
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
1 HTML of complete radio buttons
$self->Query2Radio(
"SELECT ID,name
FROM $self->{'TABLE'}
WHERE parentID=$parentID
ORDER BY sortname",
$variable,$extra,$dummy );
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.
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
1 HTML of complete checkbox sequence
$self->Result2Check( $self->List( $self,$parentID,'ID,name' ),$variable,$extra,$dummy );
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.
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)
1 HTML of complete pulldown menu
$self->Result2Pulldown( $self->List( $self,$parentID,'ID,name' ),$variable,qq(SIZE="3"),$dummy,$noempty );
$self->Result2Pulldown( $self->List( $self,$parentID,'ID,firstname,name' ) );
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.
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
1 HTML of complete radio button sequence
$self->Result2Radio( $self->List( $self,$parentID,'ID,name' ),$variable,$extra,$dummy );
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.
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.
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)
1 flag: whether successful
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'.
Fields of type ``ID'' automatically get the string ``ID'' postfixed to their name, if it is not specified already.
my ($self,$new) = $client->Forum( shift );
if ($new) {
if (!$self->AddExtraField( 'client','ID' )) {
$ERROR = $self->Error;
return;
}
}
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.
1 name of field to add 2 type of field to add 3 flag: whether field should be indexed (repeat until no more triplets)
1 flag: whether successful
$self->AddExtraFields( qw( aap ID 1 noot string 0 mies text 0 ) );
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.
1 the object itself, handy for oneliners
$self->AllowExtraFields;
Delete an extra field that was previously added with AddExtraField or AddExtraFields.
1 name of extra field to be removed 2 table to adapt for extra field (default: field TABLE in the object)
1 flag to indicate success
$self->DeleteExtraField( 'client' );
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.
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)
1..N names of extra fields
@fields = $self->ExtraFields;
$self->ExtraFields;
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.
1 string to be added to the SQL update statement
$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");
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.
Specify which tables are to be associated extra to the object. Usually only called in the ``new'' method of the object.
1..N names of extra tables
(names starting with "_" are assumed to be postfixes to the base table name)
$self->ExtraIDTables( '_ExtraNames' );
Elizabeth Mattijsen ( lizperl@INC.nl )
with contributions by:
- Sjoerd Lawende ( sjoerd@xxLINK.nl )
(C) 1998-2000 International Network Consultants
Added extra error messages to method Obtain.
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.
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.
Put module name between quotes to fix obscure bug in Perl 5.005x under ModPerl in method Sanity.
New method clone added to create a copy of an object record.
Added support for MiniSum and MiniSums to class method define.
Fixed a documentation glitch at Result2Pulldown.
Removed warn from Associations2IDs which would generate error messages when none where needed.
Now no longer puts Exporter in ISA: it was not needed.
Applied changes of Result2Pulldown to Result2Check and Result2Radio also.
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)
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.
Support for ``Survey'' and ``Surveys'' added to class method define, to allow for the new Liz::Survey module family.
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.
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.
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.
Method TableUnMeta added: remove Meta-information related to the table of the object.
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.
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.
Support for Pingset(s) added to define.
Method Query: #debug now always displays the query, #report displays the query only if there is an error.
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.
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.
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.
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.
Added new method UnKeepID, as opposed to KeepID.
Simplified method KeepID.
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.
Added method LastUpdated for returning the timestamp of the last updated record in a table.
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.
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.
Added extra input parameter to method Obtain to allow for key fields other than 'ID'.
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.
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).
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.
Method AllowExtraFields now returns the object, so that it can be used in one-liners.
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.
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.
Added return parameter and example to Result2Vars: this method can now be used in a while {} structure, just as a ``fetchrow''.
Fixed problem in reset, which would call Bounds instead of Boundaries.
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.
Error reporting in Query now correctly checks for Liz::Perl and calls the appropriate print routine when necessary.
New method SortID added: allows easy sorting of ID's using a sort-routine that accepts the object as one of the parameters.
Fixed problem in method delete if any categories were involved (table name was incorrectly spelled as ``ID2CategoryList'').
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.
Moved the Timestamp2Date suboutine and all associated methods and internal subroutines (Nederlands, Francais, Deutsch, English, SetLanguage, ConvertNL, ConvertFR, ConvertDE) to the Liz.pm module.
New methods ID2Random and Random2ID: to link IDs to random strings.
New method ID2Description: return the description field associated with an ID.
Added fieldname specification option to ID2Name.
Added LIMIT-option to Find.
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.
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.
Method Step added: returns the step between ID's from different tables.
New method OldCategoryList2ID added: allows for conversion of categorylists from older versions of Liz::SQL.
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.
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.
Removed bug from <DisAssociate>, variable $ID2 put
between single quotes didn't work...
Added methods <AllAssociations> and <AllAssociationsID> to set or return all associated ID's for a specific table.
Now uses the Liz::Calc::Intel subroutine to determine whether we're running on an Intel Architecture.
Methods delete and reset now properly remove affected ID's from the Associations and ForeignKeyAssociations table, as well as any associated categorylist tables.
Method Find now inserts SQL-wildcard characters for any non-alphanumeric character in the search string. This should increase the number of hits significantly.
Added extra SQL-condition to Find.
New method Count: return the number of records that are associated with an object.
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.
Method Boundaries now also accepts an ID value as parameter.
Fixed another severe problem with ID2CategoryQuery and ID2Name, which would produce strange values for NextID step sizes <1000000. Now, any step size is ok.
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.
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.
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.
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.
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.
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.
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.
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.
Method Timestamp2Date now also accepts ``unix time'' values (number of seconds since Jan. 1, 1970).
Added method Result2ListHash so that we can easily generate these from result handles that are returned from sub-module methods.
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.
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.
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.
Added extra HTML parameter to Result2Pulldown.
Reduced memory footprint by using fully qualified global variables and external subroutines only.
New method TableFields2Result added: allows for easy queries on tables associated with an object.
New language methods Francais and Deutsch added. Also added internal conversion routines ConvertFR and ConvertDE.
Method Obtain now checks for valid ID.
Method ID2Name now returns undef without querying the database if the specified ID is 0.
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).
New method ID2CategoryQuery: returns the string to be used in a query were any or all or a number of categories should match.
Method Obtain now correctly handles translation of database field 'deta' to object field 'DATA'.
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.
Method ID2CategoryList now ignores 0 entries.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Method Initials now checks for digits as the first character and ignores those if applicable.
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.
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.
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''.
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.
New method FieldExists: check whether one or more fields exists in a given table.
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.
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
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.
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.
Extra flag for Variable added: now optionally creates a list of fields that were changed.
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.
Method Name2ID now allows adding a new record even if there is a record with the indicated name already.
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.
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.
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.
Changed auto-append function in Variable from values starting with space, to values starting with ``+=''. The ``+='' itself is not appended.
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.
Fixed problem with method Handle: now works ok! Method Handle2ID added.
First version of this true Perl module.