diff options
Diffstat (limited to 'lib/db.php')
| -rw-r--r-- | lib/db.php | 199 |
1 files changed, 192 insertions, 7 deletions
diff --git a/lib/db.php b/lib/db.php index edbc2fe13ed..fb2c027cdb9 100644 --- a/lib/db.php +++ b/lib/db.php @@ -178,6 +178,13 @@ class OC_DB { $dsn = 'oci:dbname=//' . $host . '/' . $name; } break; + case 'mssql': + if ($port) { + $dsn='sqlsrv:Server='.$host.','.$port.';Database='.$name; + } else { + $dsn='sqlsrv:Server='.$host.';Database='.$name; + } + break; default: return false; } @@ -278,6 +285,15 @@ class OC_DB { $dsn['database'] = $user; } break; + case 'mssql': + $dsn = array( + 'phptype' => 'sqlsrv', + 'username' => $user, + 'password' => $pass, + 'hostspec' => $host, + 'database' => $name + ); + break; default: return false; } @@ -389,6 +405,13 @@ class OC_DB { $query = self::prepare('SELECT lastval() AS id'); $row = $query->execute()->fetchRow(); return $row['id']; + } + if( $type == 'mssql' ) { + if($table !== null) { + $prefix = OC_Config::getValue( "dbtableprefix", "oc_" ); + $table = str_replace( '*PREFIX*', $prefix, $table ); + } + return self::$connection->lastInsertId($table); }else{ if($table !== null) { $prefix = OC_Config::getValue( "dbtableprefix", "oc_" ); @@ -631,7 +654,7 @@ class OC_DB { } else { return true; } - } elseif( $type == 'pgsql' || $type == 'oci' || $type == 'mysql') { + } elseif( $type == 'pgsql' || $type == 'oci' || $type == 'mysql' || $type == 'mssql') { $query = 'INSERT INTO `' .$table . '` (' . implode(',', array_keys($input)) . ') SELECT \'' . implode('\',\'', array_values($input)) . '\' FROM ' . $table . ' WHERE '; @@ -691,7 +714,15 @@ class OC_DB { }elseif( $type == 'oci' ) { $query = str_replace( '`', '"', $query ); $query = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $query ); - } + }elseif( $type == 'mssql' ) { + $query = preg_replace( "/\`(.*?)`/", "[$1]", $query ); + $query = str_replace( 'NOW()', 'CURRENT_TIMESTAMP', $query ); + $query = str_replace( 'now()', 'CURRENT_TIMESTAMP', $query ); + $query = str_replace( 'LENGTH(', 'LEN(', $query ); + $query = str_replace( 'SUBSTR(', 'SUBSTRING(', $query ); + + $query = self::fixLimitClauseForMSSQL($query); + } // replace table name prefix $query = str_replace( '*PREFIX*', $prefix, $query ); @@ -699,6 +730,60 @@ class OC_DB { return $query; } + private static function fixLimitClauseForMSSQL($query) { + $limitLocation = stripos ($query, "LIMIT"); + + if ( $limitLocation === false ) { + return $query; + } + + // total == 0 means all results - not zero results + // + // First number is either total or offset, locate it by first space + // + $offset = substr ($query, $limitLocation + 5); + $offset = substr ($offset, 0, stripos ($offset, ' ')); + $offset = trim ($offset); + + // check for another parameter + if (stripos ($offset, ',') === false) { + // no more parameters + $offset = 0; + $total = intval ($offset); + } else { + // found another parameter + $offset = intval ($offset); + + $total = substr ($query, $limitLocation + 5); + $total = substr ($total, stripos ($total, ',')); + + $total = substr ($total, 0, stripos ($total, ' ')); + $total = intval ($total); + } + + $query = trim (substr ($query, 0, $limitLocation)); + + if ($offset == 0 && $total !== 0) { + if (strpos($query, "SELECT") === false) { + $query = "TOP {$total} " . $query; + } else { + $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP '.$total, $query); + } + } else if ($offset > 0) { + $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(10000000) ', $query); + $query = 'SELECT * + FROM (SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3 + FROM (SELECT 1 AS line2, sub1.* FROM (' . $query . ') AS sub1) as sub2) AS sub3'; + + if ($total > 0) { + $query .= ' WHERE line3 BETWEEN ' . ($offset + 1) . ' AND ' . ($offset + $total); + } else { + $query .= ' WHERE line3 > ' . $offset; + } + } + return $query; + } + /** * @brief drop a table * @param string $tableName the table to drop @@ -850,19 +935,119 @@ class PDOStatementWrapper{ * make execute return the result instead of a bool */ public function execute($input=array()) { - $this->lastArguments=$input; - if(count($input)>0) { + $this->lastArguments = $input; + if (count($input) > 0) { + + if (!isset($type)) { + $type = OC_Config::getValue( "dbtype", "sqlite" ); + } + + if ($type == 'mssql') { + $input = $this->tryFixSubstringLastArgumentDataForMSSQL($input); + } + $result=$this->statement->execute($input); - }else{ + } else { $result=$this->statement->execute(); } - if($result) { + + if ($result) { return $this; - }else{ + } else { return false; } } + private function tryFixSubstringLastArgumentDataForMSSQL($input) { + $query = $this->statement->queryString; + $pos = stripos ($query, 'SUBSTRING'); + + if ( $pos === false) { + return; + } + + try { + $newQuery = ''; + + $cArg = 0; + + $inSubstring = false; + + // Create new query + for ($i = 0; $i < strlen ($query); $i++) { + if ($inSubstring == false) { + // Defines when we should start inserting values + if (substr ($query, $i, 9) == 'SUBSTRING') { + $inSubstring = true; + } + } else { + // Defines when we should stop inserting values + if (substr ($query, $i, 1) == ')') { + $inSubstring = false; + } + } + + if (substr ($query, $i, 1) == '?') { + // We found a question mark + if ($inSubstring) { + $newQuery .= $input[$cArg]; + + // + // Remove from input array + // + array_splice ($input, $cArg, 1); + } else { + $newQuery .= substr ($query, $i, 1); + $cArg++; + } + } else { + $newQuery .= substr ($query, $i, 1); + } + } + + // The global data we need + $name = OC_Config::getValue( "dbname", "owncloud" ); + $host = OC_Config::getValue( "dbhost", "" ); + $user = OC_Config::getValue( "dbuser", "" ); + $pass = OC_Config::getValue( "dbpassword", "" ); + if (strpos($host,':')) { + list($host, $port) = explode(':', $host, 2); + } else { + $port = false; + } + $opts = array(); + + if ($port) { + $dsn = 'sqlsrv:Server='.$host.','.$port.';Database='.$name; + } else { + $dsn = 'sqlsrv:Server='.$host.';Database='.$name; + } + + $PDO = new PDO($dsn, $user, $pass, $opts); + $PDO->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); + $PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + + $this->statement = $PDO->prepare($newQuery); + + $this->lastArguments = $input; + + return $input; + } catch (PDOException $e){ + $entry = 'PDO DB Error: "'.$e->getMessage().'"<br />'; + $entry .= 'Offending command was: '.$this->statement->queryString .'<br />'; + $entry .= 'Input parameters: ' .print_r($input, true).'<br />'; + $entry .= 'Stack trace: ' .$e->getTraceAsString().'<br />'; + OC_Log::write('core', $entry, OC_Log::FATAL); + OC_User::setUserId(null); + + // send http status 503 + header('HTTP/1.1 503 Service Temporarily Unavailable'); + header('Status: 503 Service Temporarily Unavailable'); + OC_Template::printErrorPage('Failed to connect to database'); + die ($entry); + } + } + /** * provide numRows */ |