"Fossies" - the Fresh Open Source Software Archive

Member "mrbs-1.9.2/README.sqlapi" (14 Oct 2020, 12721 Bytes) of package /linux/www/mrbs-1.9.2.tar.gz:


As a special service "Fossies" has tried to format the requested text file into HTML format (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file.

    1 README.sqlapi     - Database abstraction class for MRBS
    2 -----------------------------------------------------------------------------
    3 MRBS utilises a database abstraction class, currently implemented for
    4 MySQL (>= 5.1) and PostgreSQL (>= 8.2). It was written for MRBS but may
    5 be useful in other applications.
    6 
    7 The class supports multiple connections to arbitrary databases,
    8 but there is also a simple wrapper function to allow use of the default
    9 MRBS database without the user always passing a database object
   10 around. This is the function "db()" defined in dbsys.inc.
   11 
   12 The class supports multiple pending results for each connection. It
   13 can be configured to use PHP persistent (pooled) database connections,
   14 or normal (single use) connections.
   15 
   16 CAUTION: Before using PHP persistent database connections with PostgreSQL,
   17 be sure your PostgreSQL postmaster can support enough backends. In theory,
   18 and to be completely safe, it needs to be able to support at least as many
   19 concurrent connections as your Apache "MaxClients" setting times the number
   20 of unique persistent connection strings (PostgreSQL conninfo's, unique
   21 combinations of user/password/database) implemented on your site. Note that
   22 the default for PostgreSQL is a maximum of 32 connections, and the default
   23 for Apache MaxClients is 150. If you want to use persistent connections,
   24 see the $persist parameter to DBFactory::create() below.
   25 
   26 -----------------------------------------------------------------------------
   27 
   28 To use this package, include "dbsys.inc" after defining the following
   29 variables:
   30      $dbsys = The database abstraction to use, 'mysql' or 'pgsql'
   31      $db_host = The hostname of the database server, or "localhost"
   32      $db_login = The username to use when connecting to the database
   33      $db_password = The database account password
   34      $db_database = The database name
   35 Optionally, you can define:
   36      $db_persist = true;
   37 if you want to use persistent connections.
   38 
   39 If using PostgreSQL, and the database server is on the same host as the web
   40 server, you can specify $db_host="localhost" to use TCP, or $db_host="" to
   41 use Unix Domain Sockets. Generally this won't make much difference, but if
   42 your server runs without the -i option, it will only accept Unix Domain
   43 Socket connections, so you must use $db_host="".
   44 
   45 After your script includes the file, you can get the default database
   46 connection object by calling db().
   47 
   48 If an error occurs while trying to connect, a message will be output
   49 followed by a PHP exit.
   50 
   51 The way MRBS uses this is to define a configuration file config.inc.php with
   52 the above variables plus:
   53      $dbsys = "pgsql";  //  or: $dbsys = "mysql";
   54 Then, each PHP script which wants to connect to the database starts with:
   55      include "config.inc.php";
   56      include "dbsys.inc";
   57 If you do this, be sure the web server will not serve config.inc.php to
   58 clients, for security reasons.
   59 
   60 -----------------------------------------------------------------------------
   61 Notes on improving SQL portability:
   62 
   63   + Use standard SQL-92 as much as possible.
   64   + Where it is not possible to use SQL-92, use or implement an sql_syntax_*
   65     function which hides the database differences (see below).
   66   + Don't use SQL-92 reserved words as column or table names.
   67   + Use PHP functions rather than database functions where practical.
   68   + Don't reply on specific formats for output of DATETIME types.
   69   + Don't quote numeric type values in SQL statements.
   70 
   71 SQL-92 standard things to avoid because they cause trouble in MySQL:
   72   + Double quoted identifiers: SELECT "MY COLUMN" from "MY TABLE"...
   73   + The string concatenation operator ||
   74   + Subselects
   75 
   76 SQL-92 standard things to avoid because they cause trouble in PostgreSQL:
   77   + Outer joins.
   78   + "table1 JOIN table2" syntax; use WHERE clause joins instead.
   79 
   80 Non-standard features used, available in both PostgreSQL and MySQL (this
   81 information is provided for anyone attempting to port MRBS to another
   82 database system):
   83   + MySQL implicitly assigns "DEFAULT current_timestamp" to a timestamp
   84     column; this must be done explicitly in other database systems.
   85   + The column called TIMESTAMP is not legal in SQL-92. It would be legal
   86     if double-quoted in SQL statements, but MySQL doesn't like that.
   87     Changing the column name would break existing databases, and it turns
   88     out both PostgreSQL and MySQL accept this, so it has been kept.
   89   + Auto-commit is assumed. The database wrappers have begin/end calls to
   90     bracket transactions, but MRBS generally uses them only to improve
   91     performance with grouped inserts/deletes/updates. It is assumed that
   92     a single insert/delete/update SQL statement commits right away. If
   93     a database doesn't implement this, it may be possible to incorporate
   94     this into sql_command(), which is used for all data modification.
   95   + Portable use of auto-incrementing fields (PostgreSQL SERIAL, MySQL
   96     AUTO_INCREMENT) requires that:
   97       * Only one auto-increment field allowed per table; must be primary key.
   98       * Use sql_insert_id() to retrieve the value after INSERT.
   99       * Don't assume the value will either be MAX(field)+1, like MySQL,
  100         or always incremented, like PostgreSQL. These can be different
  101         when records have been deleted.
  102 
  103 -----------------------------------------------------------------------------
  104 
  105 The database class methods are documented here:
  106 
  107 To make a new connection to a database, use the method DBFactory::create(), as:
  108 
  109 DBFactory::create($db_system,
  110                   $db_host,
  111                   $db_username,
  112                   $db_password,
  113                   $db_name,
  114                   $persist = 0,
  115                   $db_port = null)
  116   Here $db_system is either 'mysql' or 'pgsql' and $db_name is the name of
  117   the database to access. This method returns an object of the class "DB".
  118 
  119 The "DB" class has the following object methods:
  120 
  121 ->command($sql, $params)
  122   Execute a non-SELECT SQL command (for example: insert, update, delete).
  123   Returns the number of tuples affected if OK (a number >= 0).
  124   Raises a "DBException" exception on error.
  125 
  126 ->query($sql, $params)
  127   Execute an SQL query. Returns an object of class "DBStatement" (see methods further below).
  128 
  129 ->query1($sql, $params)
  130   Execute an SQL query which should return a single non-negative number value.
  131   Returns the value of the single column in the single row of the query                                                                                                |
  132   result or -1 if the query returns no result, or a single NULL value, such as from
  133   a MIN or MAX aggregate function applied over no rows.
  134   Raises a "DBException" exception on error.
  135   This is a short-cut alternative to ->query(), good for use with count(*)
  136   and similar queries.
  137 
  138 ->insert_id($table, $fieldname)
  139   Return the value of an autoincrement/serial field from the last insert.
  140   This must be called right after the insert on that table. The $fieldname
  141   is the name of the autoincrement or serial field in the table. The
  142   return result will be correct even if other processes are updating the
  143   database at the same time.
  144   NOTE: To make this work with different DBMS's, the field name must be
  145   specified, and it must name the only autoincrement/serial field in the
  146   row inserted by the most recent INSERT.
  147 
  148 ->error()
  149   Return the text of the last error message.
  150 
  151 ->begin()
  152   Begin a transaction, if the database supports it. This is used to
  153   improve performance for multiple insert/delete/updates on databases
  154   which support transactions, and using it is not required.  Do
  155   not attempt to have both ->begin() and ->mutex_lock() active since
  156   then both may be implemented with a shared underlying mechanism.
  157 
  158 ->commit()
  159   Commit (end) a transaction. See ->begin().
  160 
  161 ->rollback()
  162   Rollback a transaction. See ->begin().
  163 
  164 ->mutex_lock($name)
  165   Acquire a mutual-exclusion lock on the named table. For portability:
  166   * This will not lock out SELECTs.
  167   * It may lock out DELETE/UPDATE/INSERT or it may not.
  168   * It will lock out other callers of this routine with the same name
  169     argument (which is the main reason for using it).
  170   * It may timeout in 20 seconds and return 0, or may wait forever.
  171   * It returns 1 when the lock has been acquired.
  172   * Caller must release the lock with sql_mutex_unlock().
  173   * Caller must not have more than one mutex lock at any time.
  174   You should be sure to release the lock with sql_mutex_unlock() before the
  175   script exits, although this function also establishes a shutdown handler to
  176   automatically release the lock if the script exits.  (With persistent
  177   connections, the locks would not otherwise be released on exit, and a
  178   deadlock will occur.)
  179   This call effectively calls ->begin(), so do not use it inside an
  180   ->begin()/->end() block, nor use ->begin() between calls to
  181   ->mutex_lock() and ->mutex_unlock().
  182 
  183 ->mutex_unlock($name)
  184   Release a mutual-exclusion lock on the named table. See ->mutex_lock().
  185   This also effectively calls ->commit().
  186 
  187 ->version()
  188   Return a string identifying the database system and version.
  189 
  190 -----------------------------------------------------------------------------
  191 
  192 The following ->syntax_* methods are intended to help you build up SQL
  193 statements using non-standard features. Each returns a portion of SQL (with
  194 leading and trailing spaces) which implements the named non-standard feature
  195 for the selected database. Some methods must also be passed (by reference) an
  196 array object for building the SQL parameters to pass to the query/command method.
  197 
  198 ->syntax_limit($count, $offset)
  199   Generate non-standard SQL for LIMIT clauses, to make the query return
  200   no more than $count records, starting at position $offset (basis 0).
  201 
  202 ->syntax_timestamp_to_unix($fieldname)
  203   Generate non-standard SQL to output a TIMESTAMP as a Unix time_t. The
  204   argument must be the name of a timestamp field.
  205 
  206 ->syntax_caseless_contains($fieldname, $s, &$params)
  207   Generate a non-standard SQL predicate clause which will be true if the
  208   string $s is contained anywhere in the named field, using case insensitive
  209   string compare. This uses LIKE or Regular Expression matching, depending
  210   on the database system. This method modifies the passed $params array
  211   to add the appropriate SQL parameters.
  212 
  213 ->syntax_casesensitive_equals($fieldname, $string, &$params)
  214   Generates a non-standard SQL predicate clause for a case-sensitive equals.
  215   This method modifies the passed $params array to add the appropriate
  216   SQL parameters.
  217 
  218 ->syntax_addcolumn_after($fieldname)
  219   Generate non-standard SQL to add a table column after another specified
  220   column.
  221 
  222 ->syntax_createtable_autoincrementcolumn()
  223   Generate non-standard SQL to specify a column as an auto-incrementing
  224   integer while doing a CREATE TABLE.
  225 
  226 ->syntax_bitwise_xor()
  227   Returns the syntax for a bitwise XOR operator.
  228 
  229 Example usage:
  230   $sql = "SELECT * FROM mytable ORDER BY id" . $db_obj->syntax_limit(100,20);
  231 With PostgreSQL this gives you:
  232   $sql = "SELECT * FROM mytable ORDER BY id LIMIT 100 OFFSET 20";
  233 With MySQL this gives you:
  234   $sql = "SELECT * FROM mytable ORDER BY id LIMIT 20,100";
  235 
  236 -----------------------------------------------------------------------------
  237 
  238 DBStatement methods:
  239 
  240 ->row($rownumber)
  241   Return a row from a result. The first row is row number 0.
  242   The row is returned as an array with index 0=first column, etc.
  243   When called with i >= number of rows in the result, returns 0 to signify
  244   the end of the result set. This is designed to be used in a loop
  245   like this to retrieve all the rows:
  246 
  247     for ($i = 0; (($row = $stmt->row($r, $i)); $i++) { ... process the row ... }
  248 
  249 ->row_keyed($rownumber)
  250   Return a row from a result. The first row is row number 0.
  251   The row is returned as an associative array with column (field) names as
  252   the indexes. (PHP also makes numeric indexes for the same data.)
  253   When called with i >= number of rows in the result, returns 0 to signify
  254   the end of the result set. This is designed to be used in a loop
  255   like this to retrieve all the rows:
  256 
  257     for ($i = 0; (($row = $stmt->row_keyed($i)); $i++) { ... }
  258 
  259   NOTE: You should explicitly name each column in your SQL statement which
  260   is not a simple field name, because databases differ in how they assume
  261   a default name. For example, don't use ->row_keyed() on a query
  262   like: SELECT name, COUNT(*) FROM ...
  263   Instead use: SELECT name, COUNT(*) AS totals FROM ...
  264   so you can reliably refer to the count as row["totals"].
  265 
  266 ->all_rows_keyed
  267   Return all the rows from a statement object, as an array of arrays
  268   keyed on the column name.
  269 
  270 ->count()
  271   Returns the number of rows returned by the statement.
  272 
  273 ->num_fields()
  274   Returns the number of columns/fields returned by the statement.