Class: Query
- Package: saf.Database
- Author: John Luxford <lux@simian.ca>
- Copyright: Copyright (C) 2001-2003, Simian Systems Inc.
- License: http://www.sitellite.org/index/license Simian Open Software License
- Version: 2.8, 2003-05-13, $Id: Query.php,v 1.3 2007/10/06 00:06:30 lux Exp $
- Access: public
Query is the counterpart class to Database. It provides the framework
for querying a database. As of version 2.0, Query supports automatic result caching
through the use of PHP's dba functions and Sleepycat Software's Berkeley DB,
in order to relieve the underlying database system of potentially a tons of
valuable processing power. Caching seems to show better results on more complex
queries and queries that return large amounts of data. Caching to the underlying
filesystem is not supported due to the fact that Berkeley DB already handles
concurrent reads and automatic locking and integrity guarantees on writes.
2.0 also introduces the fetchXML () method, which returns a row in XML
format (ie. <row><id></id><title></title><etc></etc></row>),
so that it can be easily manipulated with XSLT.
Note: the Query API has not changed other than the caching features and is
still compatible with version 1.0.
To enable caching, you must define two constants: SITELLITE_QUERY_CACHING, which
contains the duration of a cache in seconds, and SITELLITE_QC_LOCATION, which
contains the absolute path to the folder where you want to store your Berkeley
DB files.
New in 2.2:
- Fixed a bug in the bind_values() logic.
New in 2.4:
- Removed a bunch of duplicate code between fetch() and fetchXML().
- Added a fetchArray() method.
New in 2.6:
- Rewrote bind_values() to better handle quoted and escaped content, to only quote
binded values where appropriate (using the is_numeric() function), and to handle
both the ?? and ? syntax for denoting bind values. It should also be a bit
faster now.
- Made some improvements to the toXML() method, including an htmlentities_compat()
call and the ability to name the root node something other than 'row'.
New in 2.8:
- Fixed a bug in bind_values() where a string like '0023' would not be quoted, and
so would end up stored simply as '23'.
Usage Example
<?php
$db = new Database ("Driver:Host:Database", "Username", "Password", 1);
// create a database query object
$q = $db->query ("SELECT * FROM table");
if ($q->execute ()) {
while ($row = $q->fetch ()) {
// do something with the $row object
}
$q->free ();
} else {
// query failed
}
?>
Return to Top
Properties
$connection = ''
Contains a local copy of the database connection resource.
$path
Path to the Berkeley Database.
$mode
Mode to use when opening the database (corresponds to
the modes available to the dba_open () function.
$handler
The database implementation used (db2, db3, gdbm, etc.).
$persistent
A 1 or 0 (true or false, and true by default), specifying whether
to establish a persistent connection or not.
$transactions = 0
Boolean value denoting whether to enable transactions in the
current database.
$driver
Contains the name of the database driver being used.
$host
Contains the name of the database host.
$name
Contains the name of the database being used.
$user
Contains the username used to connect to the current database.
$pass
Contains the password used to connect to the current database.
$dbd
Contains the loaded database driver.
$error
Will contain an error message if one occurs.
$sql = ''
Contains the SQL query to be executed.
$rows
Contains the number of rows returned by the previous fetch() call.
$lastid
Contains the lastid() of the last insert query sent to the execute()
method.
$tables = false
Contains a list of tables in the database. Set by getTables().
$pearEmu = false
$sequenceFormat = '%s_seq'
$fetchMode = DB_FETCHMODE_OBJECT
$result = ''
Contains the result identifier for the current execution.
$field = ''
Currently unused.
$_fetchModeFunctions = array ()
$typemap = array (
'bpchar' => 'text',
'char' => 'text',
'varchar' => 'text',
'text' => 'textarea',
'date' => 'date',
'time' => 'time',
'timestamp' => 'datetime',
'.*' => 'text',
// arrays
// booleans
// blobs
)
Contains a key/value list of database types (regular
expressions are used here to save repeating ourselves) and their
corresponding MailForm widget types.
$column
Column name of the facet.
$title
Dispaly name of the facet.
$extra
Extra info about the facet.
$tableObj
DatabaseTable object.
$items = array ()
List of options in the facet.
$type = 'normal'
Type of the facet. Possible values are 'normal', 'self_ref', 'date', and 'time'.
This value is auto-determined by compile(), but can be customized as well.
$table
Table name.
$fields = array ()
Field list.
$pkey = ''
Primary key field.
$fkey = ''
Optional name of a foreign key field, for use in simplified find() calls.
$listFields = '*'
The list of fields to return on find(). Default is '*' to return all fields.
$isAuto = true
Whether the pkey is auto-incrementing or not.
$orderBy = ''
Contains the "order by" clause value (ie. "name asc") for calls to find().
$groupBy = ''
Contains the "group by" clause value (ie. "name asc") for calls to find().
$limit = false
Contains the "limit" clause value for calls to find().
$offset = false
Contains the "offset" clause value for calls to find().
$invalid = array ()
A list of invalid fields and their corresponding error messages,
from the previous validate() call.
$usePermissions = false
Determines whether to automatically add access control to find(), count(),
and get() calls.
$multilingual = false
Enables automatic translation of items pulled from the database through
Sitellite's new multilingual capabilities.
$_cascade = array ()
This is the list of external types to cascade deletions across. In the
case of external objects, the keys are the object names and the values
are the referencing field in the external object's table. In the case
of join tables for many-to-many relationships, the keys are simply
numeric and the value is an array containing the join table name and the
name of the field referring to the current object's primary key field.
Ordinarily, these values are set automatically via the load() method,
and the relationships are defined in an INI file.
$server
The name of the server to connect to.
$port
The port to use to connect to the server.
$rdn
The rdn (username, essentially) to use to bind to
the connection.
$password
The password to use to bind to the connection.
$secure
Whether to use TLS for LDAP connections.
$resource
The current search resource.
$firstCalled
Says whether or not the first search result entry has
been returned.
$errno
The errno of the previous error.
$connections = array ()
Connection list.
$current
Active tree root ID.
$key
Primary key field name.
$root
Root field name.
$left
Left field name.
$right
Right field name.
$order
Sorting order field name.
$level
Level field name.
$indexes = array ()
Index list.
$collection
Name of the collection PropertySet should map onto. Usually this is
the name of another database table.
$entity
ID of the item from the collection which properties should map onto.
This is usually the value of the primary key of a database record.
Return to Top
Methods
Query ($sql = '', &$connection, $cache = 0)
Constructor method.
$sql is the SQL query you wish to execute with this object.
bind_values ($values)
- Access: private
- Return: string
Replaces any occurrences of ?? or ? in the $sql property with the
proper value from $values. Called automatically just prior to executing
the current query. Note: Quoted or escaped occurrences of ?? and ? are
ignored.
$values is an array of values to substitute. If $values[0] is an array,
it will be used as the array of substitutes
Returns the new SQL query. Note: does not modify the actual $sql property.
Instead it sets a $tmp_sql property.
execute ()
- Access: public
- Return: resource
Executes the current SQL query.
$values is an array of values to substitute.
Returns the new SQL query. Note: does not modify the actual $sql property.
field ($num = 0)
- Access: public
- Return: string
Returns the name of the specified column in the table currently being queried.
$num is the column number. Note: Some database systems begin with
column 0, while others with 1.
rows ()
- Access: public
- Return: integer
Returns the number of rows affected or found by the current query.
lastid ()
- Access: public
- Return: integer
Returns the row ID generated by the database during the previous
SQL insert query.
fetch ()
- Access: public
- Return: object
Returns the next row of data from the current query, always in the
form of an object, with each column as its properties.
toXML ($data_obj, $root_node = 'row')
- Access: public
- Return: string
Returns the given result object represented as XML.
$root_node allows you to specify the name of the root node of
the XML structure.
fetchXML ()
- Access: public
- Return: string
Returns the next row of data from the current query in XML format,
so that it can easily be repurposed with XSL stylesheets or shared with external
sources.
fetchArray ()
- Access: public
- Return: associate array
Returns the next row of data from the current query as an associative array,
because sometimes an object isn't appropriate.
free ()
Tells the database system to let go of its data from the previous
query.
errno ()
- Access: public
- Return: integer
Returns the database error number in case of error. This number
will be database-specific, and in some cases may even be a string. Please
refer to your database documentation for a list of values and their
meanings.
error ()
- Access: public
- Return: string
Returns the database error message in case of error. This message
will be database-specific. Please refer to your database documentation for
a list of messages and their meanings.
Return to Top