diff options
Diffstat (limited to 'web/include/DB.inc')
| -rw-r--r-- | web/include/DB.inc | 330 |
1 files changed, 330 insertions, 0 deletions
diff --git a/web/include/DB.inc b/web/include/DB.inc new file mode 100644 index 0000000..4aaadbf --- /dev/null +++ b/web/include/DB.inc @@ -0,0 +1,330 @@ +<? +/* Database Abstraction Class + * + * (c) 2002 Peter Palfrader <pp@3node.com> + * Florian Reitmeir <fr@3node.com> + */ + +/** + * Database Abstraction Class + * + * This is a small database abstraction class based on ADODB + * + * It provides only a very small set of functions but has + * proven to be sufficient for most projects. + * + * @author Peter Palfrader/3node + * @author Florian Reitmeir/3node + * @version $Id$ + */ + +include('/usr/share/adodb/adodb.inc.php'); + +class Database { + var $connection; + var $errorMsg; + var $errorNo; + + /** + * create a new database object + * + * The constructor creates a database connection to the database + * specified with <code>host</code>, <code>user</code>, + * <code>password</code>, and <code>name</code>. + * + * <code>type</code> specifies the kind of database you want to use + * (postgres, mysql, etc.) + * + * @param string type of DBMS + * @param string host where the DBMS runs + * @param string the username to connect as + * @param string the password to authenticate with + * @param string the name of the database to connect to + * @returns boolean true on success, false on error + */ + function Database($type, $host, $user, $password, $name) { + $this->connection = &ADONewConnection($type); + #if (!$this->connection->PConnect($host, $user, $password, $name)) + if (!$this->connection->Connect($host, $user, $password, $name)) + return false; + + assert( $this->connection ); + $this->database['ErrorMsg'] = ''; + $this->database['ErrorNo'] = 0; + + return true; + } + + + /** + * remove/quote evil characters + * + * This function calls the database backend's quote string + * function which is supposed to quote evil charaters + * like semicolons, single and double quotes etc so that they + * can be used in SQL queries. + * + * @param string the string which should be cleaned + * @returns string the cleaned string + */ + function clean_string(&$string) + { + assert( $this->connection ); + $result = preg_replace ('/[^A-Za-z0-9_-]+/', '', $string); + return $result; + } + + + /** + * execute an SQL query with parameters + * + * This function executes the SQL query passed in <code>query</code>. + * The query is first prepared and then executed with the values + * passed in <code>params</code>. + * + * The ADODB backend emulates binding of parameters if the database + * does not support it natively. Only <code>params</code>' values + * are passed to the binding, not its keys. + * + * You probably do not want to call this function from your code. + * Use the <code>query_row</code>, <code>query_all</code>, + * <code>insert</code>, or <code>update</code> function depending + * on what you want to do. + * + * This function returns a recordset which is an ADODB result type. + * Please * refer to the ADODB documentation for details. On error + * false is * returned and <code>errorMsg</code> and <code>errorNo</code> + * are set appropriatly. + * + * @param string the SQL query to execute + * @param array the values to bind to the execute + * @returns recordset A recordset on success, false otherwhise + */ + function execute_query($query, &$params) + { + assert( $this->connection ); + $this->errormsg = ''; + $this->errorno = 0; + + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + $values = array_values($params); + $stmt = $this->connection->Prepare($query); + $recordset = $this->connection->Execute($stmt, $values); + + if ($recordset === false) + { + $this->errorMsg = $this->connection->ErrorMsg(); + $this->errorNo = $this->connection->ErrorNo(); + } + return $recordset; + } + + /** + * execute an SQL query and return the first row + * + * This function executes the SQL query passed in <code>query</code>. + * It takes an optional array of parameters and returns the fields of + * the first result row as an assoziative array. + * + * <code>execute_query</code> is called to do the real work. + * + * @param string the SQL query to execute + * @param array the values to bind to the execute + * @returns array An assiziative array on success, false otherwhise + */ + function query_row($query, $params=array()) + { + assert( $this->connection ); + $recordset = $this->execute_query($query, $params); + if ($recordset === False) + return False; + else + return $recordset->fields; + } + + /** + * execute an SQL query and return all results + * + * This function executes the SQL query passed in <code>query</code>. + * It takes an optional array of parameters and returns an array of + * assoziative arrays (one per result row). + * + * <code>execute_query</code> is called to do the real work. + * + * @param string the SQL query to execute + * @param array the values to bind to the execute + * @returns array An array of assiziative arrays on success, false otherwhise + */ + function query_all($query, $params=array()) + { + assert( $this->connection ); + $recordset = $this->execute_query($query, $params); + + if ($recordset === False) + return False; + + $output = array(); + while (! $recordset->EOF) + { + $output[] = $recordset->fields; + $recordset->MoveNext(); + } + + return $output; + } + + /** + * Insert values into a table + * + * <code>insert</code> inserts a new row into the table <code>table</code>. + * The values to insert are taken from the assoziative array + * <code>params</code>. + * + * This function handles selection of the primary key automatically if + * no <code>ref</code> value is set in the <code>params</code>. + * + * The SQL query is built using tablename and the params passed as + * arguments. <code>execute_query</code> is called to do the real work. + * + * @param string the table to insert the new data into + * @param array assoziative array of values to insert + * @params string name of the tables primary key (defaults to <code>ref</code>) + * @returns boolean True on success, false otherwhise + */ + function insert($table, $params=array(), $refname = "ref") + { + assert( $this->connection ); + $this->errorMsg = ''; + $this->errorNo = 0; + + $keys = array_map($this->clean_string, array_keys($params)); + $qmarks = array(); + $values = array_values($params); + foreach ($params as $key=>$value) + { + $qmarks[] = "?"; + } + + if ($params[$refname]) + $sqlinsert='insert into "'.$this->clean_string($table).'" ('.implode(',', $keys).') values ('.implode(',',$qmarks).')'; + else + $sqlinsert='insert into "'.$this->clean_string($table). + '" ('.$this->clean_string($refname).', '.implode(',', $keys).') + values ( (select coalesce( max('.$this->clean_string($refname).')+1, 1 ) from "'.$this->clean_string($table).'"), ' . implode(',',$qmarks).')'; + + $stmt = $this->connection->Prepare($sqlinsert); + $recordset = $this->connection->Execute($stmt, $values); + + if ($recordset === False) + { + $this->errorMsg = $this->connection->ErrorMsg(); + $this->errorNo = $this->connection->ErrorNo(); + return False; + } + else + return True; + } + + /** + * Update a row in a table + * + * This function updates the table row given by the primary key + * <code>ref</code>. The new values are to be passed via the assoziative + * array <code>params</code>. An optional list of columns which should + * be set to NULL may be given too. + * + * The SQL query is built using tablename and the params passed as + * arguments. <code>execute_query</code> is called to do the real work. + * + * @param string the table to update the row data in + * @param integer the primary key of the row to update + * @param array assoziative array of values to insert + * @param array array of columns to set to NULL + * @params string name of the tables primary key (defaults to <code>ref</code>) + * @returns boolean True on success, false otherwhise + */ + function update($table, $ref, $params=array(), $nullify = array(), $refname="ref") + { + assert( $this->connection ); + $this->errorMsg = ''; + $this->errorNo = 0; + + $sqlparams = array(); + foreach ( $params as $key=>$value ) + { + $sqlparams[]=$this->clean_string($key).'=?'; + } + foreach ( $nullify as $key ) + { + $sqlparams[]=$this->clean_string($key).'= null'; + } + + $values = array_values($params); + $values[] = $ref; + + $sqlupdate='update "'.$this->clean_string($table).'" set '.implode(',', $sqlparams).' where '.$this->clean_string($refname).'=?'; + $stmt = $this->connection->Prepare($sqlupdate); + $recordset = $this->connection->Execute($stmt, $values); + + if ($recordset === False) + { + $this->errorMsg = $this->connection->ErrorMsg(); + $this->errorNo = $this->connection->ErrorNo(); + return False; + } + else + return True; + } + + /** + * Delete a row in a table + * + * This function delete the table row given by the primary key + * <code>ref</code>. + * + * @param string the table to update the row data in + * @param integer the primary key of the row to update + * @params string name of the tables primary key (defaults to <code>ref</code>) + * @returns boolean True on success, false otherwhise + */ + function delete_row($table, $ref, $refname="ref") + { + assert( $this->connection ); + $this->errorMsg = ''; + $this->errorNo = 0; + + $values = array( $ref ); + $sqldelete='delete from "'.$this->clean_string($table).'" where '.$this->clean_string($refname).'=?'; + $stmt = $this->connection->Prepare($sqldelete); + $recordset = $this->connection->Execute($stmt, $values); + + if ($recordset === False) + { + $this->errorMsg = $this->connection->ErrorMsg(); + $this->errorNo = $this->connection->ErrorNo(); + return False; + } + else + return True; + } + + /** + * error function + * + * This function returns a string specifying the Error Code and Message + * from the last statement, or the empty string if no error was raised. + + * @returns string ErrorCode and Message if there was an error, an empty string otherwhise + */ + function error() + { + if ($this->errorNo) + return "ErrorNo: ".$this->errorNo."; ErrorMsg: ".$this->errorMsg.";"; + else + return ""; + } +}; + +# vim:set ts=4: +# vim:set shiftwidth=4: +?> |