diff options
Diffstat (limited to 'includes/class.database.php')
| -rw-r--r-- | includes/class.database.php | 434 |
1 files changed, 434 insertions, 0 deletions
diff --git a/includes/class.database.php b/includes/class.database.php new file mode 100644 index 0000000..652cc98 --- /dev/null +++ b/includes/class.database.php @@ -0,0 +1,434 @@ +<?php +/** + * Flyspray + * + * Database class class + * + * This class is a wrapper for ADOdb functions. + * + * @license http://opensource.org/licenses/lgpl-license.php Lesser GNU Public License + * @package flyspray + * @author Tony Collins + * @author Cristian Rodriguez + */ + +if (!defined('IN_FS')) { + die('Do not access this file directly.'); +} + +class Database +{ + /** + * Table prefix, usually flyspray_ + * @var string + * @access private + */ + public $dbprefix; + + /** + * Cache for queries done by cached_query() + * @var array + * @access private + * @see cached_query(); + */ + private $cache = array(); + + /** + * dblink + * adodb handler object + * @var object + * @access public + */ + public $dblink = null; + + /** + * Open a connection to the database quickly + * @param array $conf connection data + * @return void + */ + public function dbOpenFast($conf) + { + if(!is_array($conf) || extract($conf, EXTR_REFS|EXTR_SKIP) < 5) { + + die( 'Flyspray was unable to connect to the database. ' + .'Check your settings in flyspray.conf.php'); + } + + $this->dbOpen($dbhost, $dbuser, $dbpass, $dbname, $dbtype, isset($dbprefix) ? $dbprefix : ''); + } + + /** + * Open a connection to the database and set connection parameters + * @param string $dbhost hostname where the database server uses + * @param string $dbuser username to connect to the database + * @param string $dbpass password to connect to the database + * @param string $dbname + * @param string $dbtype database driver to use, currently : "mysql", "mysqli", "pgsql" + * "pdo_mysql" and "pdo_pgsql" experimental + * @param string $dbprefix database prefix. + */ + public function dbOpen($dbhost = '', $dbuser = '', $dbpass = '', $dbname = '', $dbtype = '', $dbprefix = '') + { + + $this->dbtype = $dbtype; + $this->dbprefix = $dbprefix; + $ADODB_COUNTRECS = false; + + # 20160408 peterdd: hack to enable database socket usage with adodb-5.20.3 + # For instance on german 1und1 managed linux servers, e.g. $dbhost='localhost:/tmp/mysql5.sock' + if( ($dbtype=='mysqli' || $dbtype='pdo_mysql') && 'localhost:/'==substr($dbhost,0,11) ){ + $dbsocket=substr($dbhost,10); + $dbhost='localhost'; + if($dbtype=='mysqli'){ + ini_set('mysqli.default_socket', $dbsocket ); + }else{ + ini_set('pdo_mysql.default_socket',$dbsocket); + } + } + + # adodb for pdo is a bit different then the others at the moment (adodb 5.20.4) + # see http://adodb.org/dokuwiki/doku.php?id=v5:database:pdo + if($this->dbtype=='pdo_mysql'){ + $this->dblink = ADOnewConnection('pdo'); + $dsnString= 'host='.$dbhost.';dbname='.$dbname.';charset=utf8mb4'; + $this->dblink->connect('mysql:' . $dsnString, $dbuser, $dbpass); + }else{ + $this->dblink = ADOnewConnection($this->dbtype); + $this->dblink->connect($dbhost, $dbuser, $dbpass, $dbname); + } + + if ($this->dblink === false || (!empty($this->dbprefix) && !preg_match('/^[a-z][a-z0-9_]+$/i', $this->dbprefix))) { + + die('Flyspray was unable to connect to the database. ' + .'Check your settings in flyspray.conf.php'); + } + $this->dblink->setFetchMode(ADODB_FETCH_BOTH); + + if($dbtype=='mysqli'){ + $sinfo=$this->dblink->serverInfo(); + if(version_compare($sinfo['version'], '5.5.3')>=0 ){ + $this->dblink->setCharSet('utf8mb4'); + }else{ + $this->dblink->setCharSet('utf8'); + } + }else{ + $this->dblink->setCharSet('utf8'); + } + + // enable debug if constant DEBUG_SQL is defined. + !defined('DEBUG_SQL') || $this->dblink->debug = true; + + if($dbtype === 'mysql' || $dbtype === 'mysqli') { + $dbinfo = $this->dblink->serverInfo(); + if(isset($dbinfo['version']) && version_compare($dbinfo['version'], '5.0.2', '>=')) { + $this->dblink->execute("SET SESSION SQL_MODE='TRADITIONAL'"); + } + } + } + + /** + * Closes the database connection + * @return void + */ + public function dbClose() + { + $this->dblink->close(); + } + + /** + * insert_ID + * + * @access public + */ + public function insert_ID() + { + return $this->dblink->insert_ID(); + } + + /** + * countRows + * Returns the number of rows in a result + * @param object $result + * @access public + * @return int + */ + public function countRows($result) + { + return (int) $result->recordCount(); + } + + /** + * affectedRows + * + * @access public + * @return int + */ + public function affectedRows() + { + return (int) $this->dblink->affected_Rows(); + } + + /** + * fetchRow + * + * @param $result + * @access public + * @return void + */ + + public function fetchRow($result) + { + return $result->fetchRow(); + } + + /** + * fetchCol + * + * @param $result + * @param int $col + * @access public + * @return void + */ + + public function fetchCol($result, $col=0) + { + $tab = array(); + while ($tmp = $result->fetchRow()) { + $tab[] = $tmp[$col]; + } + return $tab; + } + + /** + * query + * + * @param mixed $sql + * @param mixed $inputarr + * @param mixed $numrows + * @param mixed $offset + * @access public + * @return void + */ + + public function query($sql, $inputarr = false, $numrows = -1, $offset = -1) + { + // auto add $dbprefix where we have {table} + $sql = $this->_add_prefix($sql); + // remove conversions for MySQL + if (strcasecmp($this->dbtype, 'pgsql') != 0) { + $sql = str_replace('::int', '', $sql); + $sql = str_replace('::text', '', $sql); + } + + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + + if (($numrows >= 0 ) or ($offset >= 0 )) { + /* adodb drivers are inconsisent with the casting of $numrows and $offset so WE + * cast to integer here anyway */ + $result = $this->dblink->selectLimit($sql, (int) $numrows, (int) $offset, $inputarr); + } else { + $result = $this->dblink->execute($sql, $inputarr); + } + + if (!$result) { + + if(function_exists("debug_backtrace") && defined('DEBUG_SQL')) { + echo "<pre style='text-align: left;'>"; + var_dump(debug_backtrace()); + echo "</pre>"; + } + + $query_params = ''; + + if(is_array($inputarr) && count($inputarr)) { + $query_params = implode(',', array_map(array('Filters','noXSS'), $inputarr)); + } + + die(sprintf("Query {%s} with params {%s} failed! (%s)", + Filters::noXSS($sql), $query_params, Filters::noXSS($this->dblink->errorMsg()))); + + } + + return $result; + } + + /** + * cached_query + * + * @param mixed $idx + * @param mixed $sql + * @param array $sqlargs + * @access public + * @return array + */ + public function cached_query($idx, $sql, $sqlargs = array()) + { + if (isset($this->cache[$idx])) { + return $this->cache[$idx]; + } + + $sql = $this->query($sql, $sqlargs); + return ($this->cache[$idx] = $this->fetchAllArray($sql)); + } + + /** + * fetchOne + * + * @param $result + * @access public + * @return array + */ + public function fetchOne($result) + { + $row = $this->fetchRow($result); + return (isset($row[0]) ? $row[0] : ''); + } + + /** + * fetchAllArray + * + * @param $result + * @access public + * @return array + */ + public function fetchAllArray($result) + { + return $result->getArray(); + } + + /** + * groupBy + * + * This groups a result by a single column the way + * MySQL would do it. Postgre doesn't like the queries MySQL needs. + * + * @param object $result + * @param string $column + * @access public + * @return array process the returned array with foreach ($return as $row) {} + */ + public function groupBy($result, $column) + { + $rows = array(); + while ($row = $this->fetchRow($result)) { + $rows[$row[$column]] = $row; + } + return array_values($rows); + } + + /** + * getColumnNames + * + * @param mixed $table + * @param mixed $alt + * @param mixed $prefix + * @access public + * @return void + */ + + public function getColumnNames($table, $alt, $prefix) + { + global $conf; + + if (strcasecmp($conf['database']['dbtype'], 'pgsql')) { + return $alt; + } + + $table = $this->_add_prefix($table); + $fetched_columns = $this->query('SELECT column_name FROM information_schema.columns WHERE table_name = ?', + array(str_replace('"', '', $table))); + $fetched_columns = $this->fetchAllArray($fetched_columns); + + foreach ($fetched_columns as $key => $value) + { + $col_names[$key] = $prefix . $value[0]; + } + + $groupby = implode(', ', $col_names); + + return $groupby; + } + + /** + * replace + * + * Try to update a record, + * and if the record is not found, + * an insert statement is generated and executed. + * + * @param string $table + * @param array $field + * @param array $keys + * @param bool $autoquote + * @access public + * @return integer 0 on error, 1 on update. 2 on insert + */ + public function replace($table, $field, $keys, $autoquote = true) + { + $table = $this->_add_prefix($table); + return $this->dblink->replace($table, $field, $keys, $autoquote); + } + + /** + * Adds the table prefix + * @param string $sql_data table name or sql query + * @return string sql with correct,quoted table prefix + * @access private + * @since 0.9.9 + */ + private function _add_prefix($sql_data) + { + return preg_replace('/{([\w\-]*?)}/', $this->quoteIdentifier($this->dbprefix . '\1'), $sql_data); + } + + /** + * Helper method to quote an indentifier + * (table or field name) with the database specific quote + * @param string $ident table or field name to be quoted + * @return string + * @access public + * @since 0.9.9 + */ + public function quoteIdentifier($ident) + { + return (string) $this->dblink->nameQuote . $ident . $this->dblink->nameQuote ; + } + + /** + * Quote a string in a safe way to be entered to the database + * (for the very few cases we don't use prepared statements) + * + * @param string $string string to be quoted + * @return string quoted string + * @access public + * @since 0.9.9 + * @notes please use this little as possible, always prefer prepared statements + */ + public function qstr($string) + { + return $this->dblink->qstr($string, false); + } + + /** + * fill_placeholders + * a convenience function to fill sql query placeholders + * according to the number of columns to be used. + * @param array $cols + * @param integer $additional generate N additional placeholders + * @access public + * @return string comma separated "?" placeholders + * @static + */ + public function fill_placeholders($cols, $additional=0) + { + if(is_array($cols) && count($cols) && is_int($additional)) { + + return join(',', array_fill(0, (count($cols) + $additional), '?')); + + } else { + //this is not an user error, is a programmer error. + trigger_error("incorrect data passed to fill_placeholders", E_USER_ERROR); + } + } + // End of Database Class +} |