Class: IsterSqlFunction

Source Location: /IsterSqlFunction.php

Class Overview

IsterObject
   |
   --IsterSqlConnection
      |
      --IsterSqlQuery
         |
         --IsterSqlFunction

This class provides execution of SQL functions aka templates.

Author(s): Ingo Schramm

Inherited Constants

Inherited Variables

Inherited Methods

Class: IsterSqlQuery

IsterSqlQuery::IsterSqlQuery(): Constructor
IsterSqlQuery::asyncQuery(): Execute an asynchronous database query.
IsterSqlQuery::countRows(): Count the rows of the current result set.
IsterSqlQuery::escapeString(): Escape a string.
IsterSqlQuery::fetchAll(): Fetch all IsterSqlRow objects from the current result set.
IsterSqlQuery::fetchObject(): Alias of fetchRow().
IsterSqlQuery::fetchRow(): Fetch an IsterSqlRow object from the current result set.
IsterSqlQuery::free(): Free the current result set.
IsterSqlQuery::lastWasRead(): Check whether the last query was a read (SELECT) operation or not.
IsterSqlQuery::query(): Execute a database query.
IsterSqlQuery::unescapeString(): Unescape a previously escaped string.

Class: IsterSqlConnection

IsterSqlConnection::IsterSqlConnection(): Constructor
IsterSqlConnection::close(): Close the current connection.
IsterSqlConnection::connect(): Establish a connection to the databse server.
IsterSqlConnection::getConnection(): Return the current connection parameters.
IsterSqlConnection::getDriver(): Get the driver object of this connection.
IsterSqlConnection::getLastError(): Get the last error of the driver.
IsterSqlConnection::getLink(): Get the link ressource.
IsterSqlConnection::info(): Get an array with information about the driver and the connection.
IsterSqlConnection::isConnected(): Check whether a connection is established.
IsterSqlConnection::pconnect(): Establish a persistent connection to the databse server.
IsterSqlConnection::setConnection(): Setup the connection.

Class: IsterObject

IsterObject::IsterObject(): Constructor
IsterObject::abstractMethodError(): Report attempt to call an abstract method.
IsterObject::addLogger(): Add an IsterLogger to process log messages.
IsterObject::deleteLogger(): Delete an already registered IsterLogger.
IsterObject::getLoggerNames(): Fetch the names of all currently registered IsterLoggers.
IsterObject::getMem(): Return maximum amount of memory an application has allocated at this point.
IsterObject::log(): Trigger a log message.
IsterObject::passPHPmessage(): Catch PHP E_WARNING and E_NOTICE messages.
IsterObject::serialize(): Serialize the object.
IsterObject::setLogLocal(): Set logging local for the current object.
IsterObject::setupLogger(): Setup a logger.
IsterObject::triggerError(): Alias for log();
IsterObject::unserialize(): Unserialize the object.
IsterObject::__sleep(): Executed prior to serialize().
IsterObject::__wakeup(): Executed prior to unserialize().

Class Details

This class provides execution of SQL functions aka templates.

The idea behind SQL functions is to separate SQL code and PHP code. A SQL function - or SQL template - may be stored in a separate file or even in a database and read prior to execution. Then some marked strings (varuables) will be replaced by given data and the resulting SQL query is executed on the DBMS.

This is a kind of "medium abstraction". The abstraction layer remains small to reduce overhead. On the other hand, if you want to change your DBMS you only have to change the separated SQL code and no PHP code. You can still use all sophisticated features of your DBMS. Last but not least you may let one person write all SQL and another person write all PHP - if you have carefully written your specifications. Finally, it is easy to maintain the code base.

If you have to pass binary data to functions (e.g. images) you should encode these data with base64_encode() and decode it with base64_decode() afterwards. This is needed because preg_replace (used in resolve()) is not binary safe.

 -- simple SQL function
 SELECT name FROM table WHERE id='%id%';
 
 -- with escaped separator
 SELECT name FROM table WHERE continent LIKE 'A\%ika\%';
 
 -- fill a database table 'functions'
 --
 -- signature for your specs:
 --     sql_get_name(integer id) returns string 'name'
 --
 INSERT INTO functions (name, body) VALUES
   ('sql_get_name', "SELECT name FROM table WHERE id='%id%';");
 
 ---
 
 //write PHP like this
  $f = new IsterSqlFunction('IsterDriverMysql');
 
 // this line should be the only one line in your scripts containing SQL code
  $f->query("SELECT body FROM functions WHERE name = 'sql_get_name';");
 
 $row = $f->fetchRow();
 $func = $row->getColumnValue('body');
 
 $f->execute($func, args('id' => 1));
 $row = $f->fetchRow();
 print $row->getColumnValue('name');

A function may be composed of multiple lines. They are splitted at each ";\n" prior to execution.

 -- MySQL
 --
 -- sql_get_last_name() returns string 'firstname', string 'lastname'
 --
 SELECT @id:=MAX(id) from table;
 SELECT firstname, lastname FROM table WHERE id=@id;

copyright:	Copyright (c) 2005 Ister.ORG Ingo Schramm
author:	Ingo Schramm

[ Top ]

Class Methods

constructor IsterSqlFunction

object IsterSqlFunction(
string
$driver)

Constructor

Parameters:

string

$driver

name of the IsterSqlDriver to use

[ Top ]

method execute

boolean execute(
string
$func, array
$args, [object IsterSQLQuery
$connection = false], [boolean
$resolve = true])

Execute a (Lambda :-)function

If connection is given, the function will be executed on connection. Otherwise it will be executed on the object's own connection.

Parameters:

string	$func	function body
array	$args
object IsterSQLQuery	$connection
boolean	$resolve	whether to resolve function or not (if it already has been resolved)

[ Top ]

method executeByName

boolean executeByName(
string
$name, array
$args, [object IsterSQLQuery
$connection = false])

Execute a function identified by name.

see:

IsterSqlFunction::execute()

Parameters:

string	$name
array	$args
object IsterSQLQuery	$connection

[ Top ]

method getFunction

string getFunction(
string
$name)

Get the function body of a function.

Parameters:

string

$name

[ Top ]

method hasFunction

boolean hasFunction(
string
$name)

Check whether a function is set in the function table.

Parameters:

string

$name

[ Top ]

method resolve

string resolve(
string
$func, array
$args, [boolean
$escape = false])

Resolve a SQL function, but do not execute.

return:

The function with all variables resolved.

Parameters:

string	$func	function body
array	$args
boolean	$escape	whether to escape each value automatically or not

[ Top ]

method setFunction

boolean setFunction(
string
$name, string
$body)

Add a function to the function table.

Parameters:

string	$name	name of the function
string	$body	body of the function

[ Top ]

method setFunctionTable

boolean setFunctionTable(
array
$array)

Add a whole function table in one single step.

Parameters:

array

$array

[ Top ]

method setLookupFunction

boolean setLookupFunction(
string
$sql, string
$namecol, string
$bodycol)

Set an SQL function to lookup a function in a function table stored in a database table.

Parameters:

string	$sql	sql query
string	$namecol	column name of function name
string	$bodycol	column name of function body

[ Top ]

method unsetFunction

boolean unsetFunction(
string
$name)

Unset a function of the function table

Parameters:

string

$name

[ Top ]

Documentation generated on Tue, 20 Dec 2005 11:21:17 +0100 by phpDocumentor 1.3.0RC4

	sql
	[ doc home ] [ class tree: sql ] [ index: sql ] [ all elements ]