Changeset:
c43758b6c49f
https://sourceforge.net/p/mrbs/hg-code/ci/c43758b6c49f02f4cdee61562d54563229621785
Author:
John Beranek <[email protected]>
Date:
Tue Oct 04 18:55:40 2016 +0100
Log message:
Updated README.sqlapi for new DB class
diffstat:
README.sqlapi | 252 +++++++++++++++++++++++++++++++--------------------------
1 files changed, 136 insertions(+), 116 deletions(-)
diffs (truncated from 348 to 300 lines):
diff -r 59e938d185dc -r c43758b6c49f README.sqlapi
--- a/README.sqlapi Tue Oct 04 16:28:22 2016 +0100
+++ b/README.sqlapi Tue Oct 04 18:55:40 2016 +0100
@@ -1,14 +1,15 @@
-README.sqlapi - Database wrappers for MRBS
+README.sqlapi - Database abstraction class for MRBS
-----------------------------------------------------------------------------
-This is a simple set of PHP database call wrapper routines, currently
-implemented for MySQL (>= 5.1) and PostgreSQL (>= 8.2). It was written
-for MRBS but may be useful in other applications.
+MRBS utilises a database abstraction class, currently implemented for
+MySQL (>= 5.1) and PostgreSQL (>= 8.2). It was written for MRBS but may
+be useful in other applications.
-This package supports multiple connections to arbitrary databases,
-but also handles the default MRBS database connection without the user
-always passing a database connection handle around by storing this
-default connection handle in a global variable inside the database
-abstraction. It supports multiple pending results for each connection. It
+The class supports multiple connections to arbitrary databases,
+but there is also a simple wrapper function to allow use of the default
+MRBS database without the user always passing a database object
+around. This is the function "db()" defined in dbsys.inc.
+
+The class supports multiple pending results for each connection. It
can be configured to use PHP persistent (pooled) database connections,
or normal (single use) connections.
@@ -20,7 +21,7 @@
combinations of user/password/database) implemented on your site. Note that
the default for PostgreSQL is a maximum of 32 connections, and the default
for Apache MaxClients is 150. If you want to use persistent connections,
-see the $persist parameter to sql_connect() below.
+see the $persist parameter to DBFactory::create() below.
-----------------------------------------------------------------------------
@@ -32,8 +33,8 @@
$db_password = The database account password
$db_database = The database name
Optionally, you can define:
- $db_nopersist = 1;
-if you do not want to use persistent connections.
+ $db_persist = true;
+if you want to use persistent connections.
If using PostgreSQL, and the database server is on the same host as the web
server, you can specify $db_host="localhost" to use TCP, or $db_host="" to
@@ -41,12 +42,11 @@
your server runs without the -i option, it will only accept Unix Domain
Socket connections, so you must use $db_host="".
-After your script includes the file, it will be connected to the database,
-by calling the abstracted function sql_$dbsys_default_connect().
+After your script includes the file, you can get the default database
+connection object by calling db().
If an error occurs while trying to connect, a message will be output
-followed by a PHP exit. (Failure to connect to the database is the only
-reason the functions defined here will output an error message and exit.)
+followed by a PHP exit.
The way MRBS uses this is to define a configuration file config.inc.php with
the above variables plus:
@@ -80,12 +80,6 @@
Non-standard features used, available in both PostgreSQL and MySQL (this
information is provided for anyone attempting to port MRBS to another
database system):
- + Escaping quoted strings. The SQL-92 standard says to double embedded
- single quotes, and that's the only escaping used or defined. So:
- insert into mytable values ('CAN''T');
- But both MySQL and PostgreSQL support backslash-escaping, which is
- easier to do in PHP, so I have not attempted to wrap database string
- quoting.
+ MySQL implicitly assigns "DEFAULT current_timestamp" to a timestamp
column; this must be done explicitly in other database systems.
+ The column called TIMESTAMP is not legal in SQL-92. It would be legal
@@ -108,74 +102,40 @@
-----------------------------------------------------------------------------
-The database-independent wrapper functions are documented here:
+The database class methods are documented here:
-Each of the functions below takes an optional final argument that is the
-MRBS database handle returned by sql_connect() call.
+To make a new connection to a database, use the method DBFactory::create(), as:
-sql_command($sql, $params)
+DBFactory::create($db_system,
+ $db_host,
+ $db_username,
+ $db_password,
+ $db_name,
+ $persist = 0,
+ $db_port = null)
+ Here $db_system is either 'mysql' or 'pgsql' and $db_name is the name of
+ the database to access. This method returns an object of the class "DB".
+
+The "DB" class has the following object methods:
+
+->command($sql, $params)
Execute a non-SELECT SQL command (for example: insert, update, delete).
Returns the number of tuples affected if OK (a number >= 0).
- Returns -1 on error; use sql_error() to get the error message.
+ Raises a "DBException" exception on error.
-sql_query($sql, $params)
- Execute an SQL query. Returns a database-dependent result handle, which
- should be passed back to sql_row() or sql_row_keyed() to get the results.
- If sql_row() or sql_row_keyed() isn't used to read all rows (plus 1), you
- must call sql_free() to free the result structure.
- Returns 0 on error; use sql_error() to get the error message.
+->query($sql, $params)
+ Execute an SQL query. Returns an object of class "DBStatement" (see methods
further below).
-sql_row($result, $rownumber)
- Return a row from a result. The first row is row number 0.
- The row is returned as an array with index 0=first column, etc.
- When called with i >= number of rows in the result, this frees up the
- results structure and returns 0. This is designed to be used in a loop
- like this to retrieve all the rows:
+->query1($sql, $params)
+ Execute an SQL query which should return a single non-negative number value.
+ Returns the value of the single column in the single row of the query
|
+ result or -1 if the query returns no result, or a single NULL value, such as
from
+ a MIN or MAX aggregate function applied over no rows.
+ Raises a "DBException" exception on error.
+ This is a short-cut alternative to ->query(), good for use with count(*)
+ and similar queries.
- for ($i = 0; (($row = sql_row($r, $i)); $i++) { ... process the row ... }
-
- When used this way, there is no need to call sql_free($result). But if
- rows are accessed in other way, sql_free() must be called when you are
- done with the result handle.
-
-sql_row_keyed($result, $rownumber)
- Return a row from a result. The first row is row number 0.
- The row is returned as an associative array with column (field) names as
- the indexes. (PHP also makes numeric indexes for the same data.)
- When called with i >= number of rows in the result, this frees up the
- results structure and returns 0. This is designed to be used in a loop
- like this to retrieve all the rows:
-
- for ($i = 0; (($row = sql_row_keyed($r, $i)); $i++) { ... }
-
- When used this way, there is no need to call sql_free($result). But if
- rows are accessed in other ways, sql_free() must be called when you are
- done with the result handle.
- NOTE: You should explicitly name each column in your SQL statement which
- is not a simple field name, because databases differ in how they assume
- a default name. For example, don't use sql_row_keyed() on a query
- like: SELECT name, COUNT(*) FROM ...
- Instead use: SELECT name, COUNT(*) AS totals FROM ...
- so you can reliably refer to the count as row["totals"].
-
-sql_count($result)
- Return the number of rows returned by a result handle from sql_query().
-
-sql_query1($sql, $params)
- Execute an SQL query which should return a single non-negative number value.
- Returns the value of the single column in the single row of the query
- result, or -1 on error.
- This is a short-cut alternative to sql_query(), good for use with count(*)
- and similar queries. Error checking is limited (caller cannot tell if
- an error occurred or no values were returned), but it is easier to use.
-
-sql_free($result)
- Free a result handle $result, returned by a previous call to sql_query().
- You must call this if you do not use sql_row() or sql_row_keyed() to fetch
- all rows of the query result and then call it once more. See sql_row() for
- more information.
-
-sql_insert_id($table, $fieldname)
+->insert_id($table, $fieldname)
Return the value of an autoincrement/serial field from the last insert.
This must be called right after the insert on that table. The $fieldname
is the name of the autoincrement or serial field in the table. The
@@ -185,23 +145,23 @@
specified, and it must name the only autoincrement/serial field in the
row inserted by the most recent INSERT.
-sql_error()
- Return the text of the last error message. Only call this after an
- error return from sql_query() or sql_command() - it is not reliable
- as an indicator if an error did in fact occur.
+->error()
+ Return the text of the last error message.
-sql_begin()
+->begin()
Begin a transaction, if the database supports it. This is used to
improve performance for multiple insert/delete/updates on databases
- which support transactions, and using it is not required. There is
- no support for rollback, because not all databases can do it. Do
- not attempt to have both sql_begin() and sql_mutex_lock() active since
+ which support transactions, and using it is not required. Do
+ not attempt to have both ->begin() and ->mutex_lock() active since
then both may be implemented with a shared underlying mechanism.
-sql_commit()
- Commit (end) a transaction. See sql_begin().
+->commit()
+ Commit (end) a transaction. See ->begin().
-sql_mutex_lock($name)
+->rollback()
+ Rollback a transaction. See ->begin().
+
+->mutex_lock($name)
Acquire a mutual-exclusion lock on the named table. For portability:
* This will not lock out SELECTs.
* It may lock out DELETE/UPDATE/INSERT or it may not.
@@ -216,51 +176,111 @@
automatically release the lock if the script exits. (With persistent
connections, the locks would not otherwise be released on exit, and a
deadlock will occur.)
- This call effectively calls sql_begin(), so do not use it inside an
- sql_begin()/sql_end() block, nor use sql_begin() between calls to
- sql_mutex_lock() and sql_mutex_unlock().
+ This call effectively calls ->begin(), so do not use it inside an
+ ->begin()/->end() block, nor use ->begin() between calls to
+ ->mutex_lock() and ->mutex_unlock().
-sql_mutex_unlock($name)
- Release a mutual-exclusion lock on the named table. See sql_mutex_lock().
- This also effectively calls sql_commit().
+->mutex_unlock($name)
+ Release a mutual-exclusion lock on the named table. See ->mutex_lock().
+ This also effectively calls ->commit().
-sql_version()
+->version()
Return a string identifying the database system and version.
-sql_connect($host, $username, $password, $db_name, $persist, $db_port)
- Connects to the specified database using the specified credentials,
- optionally using persistent database connections and a port number.
- Returns an MRBS specific database handle, which can be passed as the
- last argument of all the other sql_*() functions.
-
-----------------------------------------------------------------------------
-The following sql_syntax_* routines are intended to help you build up SQL
+The following ->syntax_* methods are intended to help you build up SQL
statements using non-standard features. Each returns a portion of SQL (with
leading and trailing spaces) which implements the named non-standard feature
-for the selected database.
+for the selected database. Some methods must also be passed (by reference) an
+array object for building the SQL parameters to pass to the query/command
method.
-sql_syntax_limit($count, $offset)
+->syntax_limit($count, $offset)
Generate non-standard SQL for LIMIT clauses, to make the query return
no more than $count records, starting at position $offset (basis 0).
-sql_syntax_timestamp_to_unix($fieldname)
+->syntax_timestamp_to_unix($fieldname)
Generate non-standard SQL to output a TIMESTAMP as a Unix time_t. The
argument must be the name of a timestamp field.
-sql_syntax_caseless_contains($fieldname, $s, $params)
+->syntax_caseless_contains($fieldname, $s, &$params)
Generate a non-standard SQL predicate clause which will be true if the
string $s is contained anywhere in the named field, using case insensitive
string compare. This uses LIKE or Regular Expression matching, depending
- on the database system. It handles all quoting on the $s argument of the
- characters needed by the selected method, so don't call this with an
- argument which has already been "magic quoted".
+ on the database system. This method modifies the passed $params array
+ to add the appropriate SQL parameters.
+
+->syntax_casesensitive_equals($fieldname, $string, &$params)
+ Generates a non-standard SQL predicate clause for a case-sensitive equals.
+ This method modifies the passed $params array to add the appropriate
+ SQL parameters.
+
+->syntax_addcolumn_after($fieldname)
+ Generate non-standard SQL to add a table column after another specified
+ column.
+
+->syntax_createtable_autoincrementcolumn()
+ Generate non-standard SQL to specify a column as an auto-incrementing
+ integer while doing a CREATE TABLE.
+
+->syntax_bitwise_xor()
+ Returns the syntax for a bitwise XOR operator.
Example usage:
- $sql = "SELECT * FROM mytable ORDER BY id" . sql_syntax_limit(100,20);
+ $sql = "SELECT * FROM mytable ORDER BY id" . $db_obj->syntax_limit(100,20);
With PostgreSQL this gives you:
$sql = "SELECT * FROM mytable ORDER BY id LIMIT 100 OFFSET 20";
With MySQL this gives you:
$sql = "SELECT * FROM mytable ORDER BY id LIMIT 20,100";
-----------------------------------------------------------------------------
+
+DBStatement methods:
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
