Author: Sergey Alexeev
Date: 2007-05-03 21:19:57 +0200 (Thu, 03 May 2007)
New Revision: 5028
Log:
- Added feature #7772: SQL Server implementation for the Database package.
Based on contributions by Friedel Hill.
Added:
trunk/Database/src/handlers/mssql.php
trunk/Database/src/sqlabstraction/implementations/expression_mssql.php
trunk/Database/src/sqlabstraction/implementations/query_select_mssql.php
Modified:
trunk/Database/ChangeLog
trunk/Database/src/db_autoload.php
trunk/Database/src/factory.php
trunk/Database/src/query_autoload.php
trunk/Database/tests/factory_test.php
trunk/Database/tests/handler_test.php
trunk/Database/tests/pdo_test.php
trunk/Database/tests/sqlabstraction/expression_test.php
trunk/Database/tests/sqlabstraction/query_insert_test.php
trunk/Database/tests/sqlabstraction/query_select_test_impl.php
trunk/Database/tests/sqlabstraction/query_subselect_test.php
trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
trunk/Database/tests/suite.php
Modified: trunk/Database/ChangeLog
===================================================================
--- trunk/Database/ChangeLog 2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/ChangeLog 2007-05-03 19:19:57 UTC (rev 5028)
@@ -10,6 +10,8 @@
- Fixed issue #9526: Implemented expression now() for each database handler.
- Fixed issue #10529: Tests for quoting of strings in query expressions.
- Added feature #8448: Select Distinct not supported
+- Added feature #7772: SQL Server implementation for the Database package.
+ Based on contributions by Friedel Hill.
1.2 - Monday 18 December 2006
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Modified: trunk/Database/src/db_autoload.php
===================================================================
--- trunk/Database/src/db_autoload.php 2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/src/db_autoload.php 2007-05-03 19:19:57 UTC (rev 5028)
@@ -17,6 +17,7 @@
'ezcDbHandler' => 'Database/handler.php',
'ezcDbUtilities' =>
'Database/sqlabstraction/utilities.php',
'ezcDbFactory' => 'Database/factory.php',
+ 'ezcDbHandlerMssql' => 'Database/handlers/mssql.php',
'ezcDbHandlerMysql' => 'Database/handlers/mysql.php',
'ezcDbHandlerOracle' => 'Database/handlers/oracle.php',
'ezcDbHandlerPgsql' => 'Database/handlers/pgsql.php',
Modified: trunk/Database/src/factory.php
===================================================================
--- trunk/Database/src/factory.php 2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/src/factory.php 2007-05-03 19:19:57 UTC (rev 5028)
@@ -53,7 +53,8 @@
static private $implementations = array( 'mysql' => 'ezcDbHandlerMysql',
'pgsql' => 'ezcDbHandlerPgsql',
'oracle' => 'ezcDbHandlerOracle',
- 'sqlite' => 'ezcDbHandlerSqlite'
);
+ 'sqlite' => 'ezcDbHandlerSqlite',
+ 'mssql' => 'ezcDbHandlerMssql', );
/**
* Adds a database implementation to the list of known implementations.
@@ -340,7 +341,6 @@
}
}
}
-
return $parsed;
}
}
Added: trunk/Database/src/handlers/mssql.php
===================================================================
--- trunk/Database/src/handlers/mssql.php 2007-05-03 16:43:33 UTC (rev
5027)
+++ trunk/Database/src/handlers/mssql.php 2007-05-03 19:19:57 UTC (rev
5028)
@@ -0,0 +1,217 @@
+<?php
+/**
+ * File containing the ezcDbHandlerMssql class.
+ *
+ * @package Database
+ * @version //autogentag//
+ * @copyright Copyright (C) 2005-2007 eZ systems as. All rights reserved.
+ * @license http://ez.no/licenses/new_bsd New BSD License
+ */
+
+/**
+ * MS SQL Server driver implementation
+ *
+ * @see ezcDbHandler
+ * @package Database
+ */
+
+class ezcDbHandlerMssql extends ezcDbHandler
+{
+ /**
+ * Constructs a handler object from the parameters $dbParams.
+ *
+ * Supported database parameters are:
+ * - dbname|database: Database name
+ * - host|hostspec: Name of the host database is running on
+ * - port: TCP port
+ * - user|username: Database user name
+ * - pass|password: Database user password
+ *
+ * @param array $dbparams Database connection parameters (key=>value
pairs).
+ * @throws ezcDbMissingParameterException if the database name was not
specified.
+ */
+ public function __construct( $dbParams )
+ {
+ $database = null;
+ $host = null;
+ $port = null;
+
+ foreach ( $dbParams as $key => $val )
+ {
+ switch ( $key )
+ {
+ case 'database':
+ case 'dbname':
+ $database = $val;
+ break;
+
+ case 'host':
+ case 'hostspec':
+ $host = $val;
+ break;
+
+ case 'port':
+ $port = $val;
+ break;
+ }
+ }
+
+ if ( !isset( $database ) )
+ {
+ throw new ezcDbMissingParameterException( 'database', 'dbParams' );
+ }
+
+ $dsn = "dblib:dbname=$database";
+
+ if ( isset( $host ) && $host )
+ {
+ $dsn .= ";host=$host";
+ if ( isset( $port ) && $port )
+ {
+ $dsn .= ":$port";
+ }
+
+ }
+
+ parent::__construct( $dbParams, $dsn );
+ }
+
+
+ /**
+ * Returns a new ezcQueryExpression derived object with SQL Server
implementation specifics.
+ *
+ * @return ezcQueryExpressionMssql
+ */
+ public function createExpression()
+ {
+ return new ezcQueryExpressionMssql( $this );
+ }
+
+ /**
+ * Returns 'mssql'.
+ *
+ * @return string
+ */
+ static public function getName()
+ {
+ return 'mssql';
+ }
+
+ /**
+ * Returns a new ezcQuerySelectMssql derived object with SQL Server
implementation specifics.
+ *
+ * @return ezcQuerySelectMssql
+ */
+ public function createSelectQuery()
+ {
+ return new ezcQuerySelectMssql( $this );
+ }
+
+ /**
+ * Begins a transaction.
+ *
+ * This method executes a begin transaction query unless a
+ * transaction has already been started (transaction nesting level > 0 )
+ *
+ * Each call to begin() must have a corresponding commit() or rollback()
call.
+ *
+ * @see commit()
+ * @see rollback()
+ * @return bool
+ */
+ public function beginTransaction()
+ {
+ $retval = true;
+ if ( $this->transactionNestingLevel == 0 )
+ {
+ $retval = $this->exec("BEGIN TRANSACTION");
+ }
+ // else NOP
+
+ $this->transactionNestingLevel++;
+ return $retval;
+ }
+
+ /**
+ * Commits a transaction.
+ *
+ * If this this call to commit corresponds to the outermost call to
+ * begin() and all queries within this transaction were successful,
+ * a commit query is executed. If one of the queries
+ * returned with an error, a rollback query is executed instead.
+ *
+ * This method returns true if the transaction was successful. If the
+ * transaction failed and rollback was called, false is returned.
+ *
+ * @see begin()
+ * @see rollback()
+ * @return bool
+ */
+ public function commit()
+ {
+ if ( $this->transactionNestingLevel <= 0 )
+ {
+ $this->transactionNestingLevel = 0;
+
+ throw new ezcDbTransactionException( "commit() called before
beginTransaction()." );
+ }
+
+ $retval = true;
+ if ( $this->transactionNestingLevel == 1 )
+ {
+ if ( $this->transactionErrorFlag )
+ {
+ $this->exec("ROLLBACK TRANSACTION");
+ $this->transactionErrorFlag = false; // reset error flag
+ $retval = false;
+ }
+ else
+ {
+ $this->exec("COMMIT TRANSACTION");
+ }
+ }
+ // else NOP
+
+ $this->transactionNestingLevel--;
+ return $retval;
+ }
+
+ /**
+ * Rollback a transaction.
+ *
+ * If this this call to rollback corresponds to the outermost call to
+ * begin(), a rollback query is executed. If this is an inner transaction
+ * (nesting level > 1) the error flag is set, leaving the rollback to the
+ * outermost transaction.
+ *
+ * This method always returns true.
+ *
+ * @see begin()
+ * @see commit()
+ * @return bool
+ */
+ public function rollback()
+ {
+ if ( $this->transactionNestingLevel <= 0 )
+ {
+ $this->transactionNestingLevel = 0;
+ throw new ezcDbTransactionException( "rollback() called without
previous beginTransaction()." );
+ }
+
+ if ( $this->transactionNestingLevel == 1 )
+ {
+ $this->exec("ROLLBACK TRANSACTION");
+ $this->transactionErrorFlag = false; // reset error flag
+ }
+ else
+ {
+ // set the error flag, so that if there is outermost commit
+ // then ROLLBACK will be done instead of COMMIT
+ $this->transactionErrorFlag = true;
+ }
+
+ $this->transactionNestingLevel--;
+ return true;
+ }
+}
+?>
Property changes on: trunk/Database/src/handlers/mssql.php
___________________________________________________________________
Name: svn:eol-style
+ native
Modified: trunk/Database/src/query_autoload.php
===================================================================
--- trunk/Database/src/query_autoload.php 2007-05-03 16:43:33 UTC (rev
5027)
+++ trunk/Database/src/query_autoload.php 2007-05-03 19:19:57 UTC (rev
5028)
@@ -17,10 +17,12 @@
'ezcQueryExpression' =>
'Database/sqlabstraction/expression.php',
'ezcQuerySelect' =>
'Database/sqlabstraction/query_select.php',
'ezcQueryDelete' =>
'Database/sqlabstraction/query_delete.php',
+ 'ezcQueryExpressionMssql' =>
'Database/sqlabstraction/implementations/expression_mssql.php',
'ezcQueryExpressionOracle' =>
'Database/sqlabstraction/implementations/expression_oracle.php',
'ezcQueryExpressionPgsql' =>
'Database/sqlabstraction/implementations/expression_pgsql.php',
'ezcQueryExpressionSqlite' =>
'Database/sqlabstraction/implementations/expression_sqlite.php',
'ezcQueryInsert' =>
'Database/sqlabstraction/query_insert.php',
+ 'ezcQuerySelectMssql' =>
'Database/sqlabstraction/implementations/query_select_mssql.php',
'ezcQuerySelectOracle' =>
'Database/sqlabstraction/implementations/query_select_oracle.php',
'ezcQuerySelectSqlite' =>
'Database/sqlabstraction/implementations/query_select_sqlite.php',
'ezcQuerySqliteFunctions' =>
'Database/sqlabstraction/implementations/query_sqlite_function_implementations.php',
Added: trunk/Database/src/sqlabstraction/implementations/expression_mssql.php
===================================================================
--- trunk/Database/src/sqlabstraction/implementations/expression_mssql.php
2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/src/sqlabstraction/implementations/expression_mssql.php
2007-05-03 19:19:57 UTC (rev 5028)
@@ -0,0 +1,128 @@
+<?php
+/**
+ * File containing the ezcQueryExpressionMssql class.
+ *
+ * @package Database
+ * @version 1.0
+ * @copyright Copyright (C) 2005, 2006 eZ systems as. All rights reserved.
+ * @license http://ez.no/licenses/new_bsd New BSD License
+ */
+
+/**
+ * The ezcQueryExpressionMssql class is used to create SQL expression for SQL
Server.
+ *
+ * This class reimplements the methods that have a different syntax in SQL
Server.
+ *
+ * @package Database
+ */
+class ezcQueryExpressionMssql extends ezcQueryExpression
+{
+
+ /**
+ * Returns the remainder of the division operation
+ * $expression1 / $expression2.
+ *
+ * @param string $expression1
+ * @param string $expression2
+ * @return string
+ */
+ public function mod( $expression1, $expression2 )
+ {
+ $expression1 = $this->getIdentifier( $expression1 );
+ $expression2 = $this->getIdentifier( $expression2 );
+ return "{$expression1} % {$expression2}";
+ }
+
+ /**
+ * Returns the md5 sum of a field.
+ * There is two variants of implementation for this feature.
+ * Both not ideal though.
+ * First don't require additional setup of MS SQL Server
+ * and uses undocumented function master.dbo.fn_varbintohexstr()
+ * to convert result of Transact-SQL HashBytes() function to string.
+ *
+ * Second one requires the stored procedure
+ * from http://www.thecodeproject.com/database/xp_md5.asp to
+ * be installed and wrapped by the user defined function fn_md5
+ *
+ * @return string
+ */
+ public function md5( $column )
+ {
+ $column = $this->getIdentifier( $column );
+ return "SUBSTRING( master.dbo.fn_varbintohexstr( HashBytes( 'MD5',
{$column} ) ), 3, 32)";
+ // alternative
+ // return "dbo.fn_md5( {$column} )";
+ }
+
+ /**
+ * Returns the length of a text field.
+ *
+ * @param string $column
+ * @return string
+ */
+ public function length( $column )
+ {
+ $column = $this->getIdentifier( $column );
+ return "LEN( {$column} )";
+ }
+
+ /**
+ * Returns the current system date.
+ *
+ * @return string
+ */
+ public function now()
+ {
+ return "GETDATE()";
+ }
+
+ /**
+ * Returns part of a string.
+ *
+ * Note: Not SQL92, but common functionality.
+ *
+ * @param string $value the target $value the string or the string column.
+ * @param int $from extract from this characeter.
+ * @param int $len extract this amount of characters. If $len is not
+ * provided it's assumed to be the number of characters
+ * to get the whole remainder of the string.
+ * @return string sql that extracts part of a string.
+ */
+ public function subString( $value, $from, $len = null )
+ {
+ $value = $this->getIdentifier( $value );
+ if ( $len === null )
+ {
+ return "SUBSTRING( {$value}, {$from}, LEN({$value})-({$from}-1) )";
+ }
+ else
+ {
+ $len = $this->getIdentifier( $len );
+ return "SUBSTRING( {$value}, {$from}, {$len} )";
+ }
+ }
+
+ /**
+ * Returns a series of strings concatinated
+ *
+ * concat() accepts an arbitrary number of parameters. Each parameter
+ * must contain an expression or an array with expressions.
+ *
+ * @param string|array(string) strings that will be concatinated.
+ */
+ public function concat()
+ {
+ $args = func_get_args();
+ $cols = ezcQuerySelect::arrayFlatten( $args );
+
+ if ( count( $cols ) < 1 )
+ {
+ throw new ezcQueryVariableParameterException( 'concat', count(
$args ), 1 );
+ }
+
+ $cols = $this->getIdentifiers( $cols );
+ return join( ' + ', $cols );
+ }
+}
+?>
Property changes on:
trunk/Database/src/sqlabstraction/implementations/expression_mssql.php
___________________________________________________________________
Name: svn:eol-style
+ native
Added: trunk/Database/src/sqlabstraction/implementations/query_select_mssql.php
===================================================================
--- trunk/Database/src/sqlabstraction/implementations/query_select_mssql.php
2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/src/sqlabstraction/implementations/query_select_mssql.php
2007-05-03 19:19:57 UTC (rev 5028)
@@ -0,0 +1,127 @@
+<?php
+/**
+ * File containing the ezcQuerySelectMssql class.
+ *
+ * @package Database
+ * @version 1.0
+ * @copyright Copyright (C) 2005, 2006 eZ systems as. All rights reserved.
+ * @license http://ez.no/licenses/new_bsd New BSD License
+ */
+
+/**
+ * SQL Server specific implementation of ezcQuery.
+ *
+ * This class reimplements the LIMIT method in which the
+ * SQL Server differs from the standard implementation in ezcQuery.
+ *
+ * @see ezcQuery
+ * @package Database
+ */
+class ezcQuerySelectMssql extends ezcQuerySelect
+{
+ /**
+ * If a limit and/or offset has been set for this query.
+ */
+ private $hasLimit = false;
+ private $limit = 0;
+ private $offset = 0;
+
+ /**
+ * Same as ezcQuerySelect::$orderString but inverted
+ * for use in the LIMIT functionality.
+ *
+ * @var string
+ */
+ private $invertedOrderString = null;
+
+ /**
+ * Resets the query object for reuse.
+ *
+ * @return void
+ */
+ public function reset()
+ {
+ $this->hasLimit = false;
+ $this->limit = 0;
+ $this->offset = 0;
+ $this->orderColumns = array();
+ parent::reset();
+ }
+
+ /**
+ * Returns SQL that limits the result set.
+ *
+ * $limit controls the maximum number of rows that will be returned.
+ * $offset controls which row that will be the first in the result
+ * set from the total amount of matching rows.
+ *
+ * @param $limit integer expression
+ * @param $offset integer expression
+ * @return void
+ */
+ public function limit( $limit, $offset = 0 )
+ {
+ $this->hasLimit = true;
+ $this->limit = $limit;
+ $this->offset = $offset;
+ return $this;
+ }
+
+ /**
+ * Saves the ordered columns in an internal array so we can invert that
order
+ * if we need to in the limit() workaround
+ */
+ public function orderBy( $column, $type = self::ASC )
+ {
+ if($this->invertedOrderString) {
+ $this->invertedOrderString .= ', ';
+ }
+ else
+ {
+ $this->invertedOrderString = 'ORDER BY ';
+ }
+ $this->invertedOrderString .= $column . ' ' . ( $type == self::ASC ?
self::DESC : self::ASC );
+ return parent::orderBy($column, $type);
+ }
+
+ /**
+ * Transforms the $query to make it only select the $rowCount first rows
+ *
+ * @param int $rowCount number of rows to return
+ * @param string $query SQL select query
+ * @return string
+ */
+ static private function top($rowCount, $query) {
+ return 'SELECT TOP ' . $rowCount . substr( $query, strlen( 'SELECT' )
);
+ }
+
+ /**
+ * Transforms the query from the parent to provide LIMIT functionality.
+ *
+ * Note: doesn't work exactly like the MySQL equivalent; it will always
return
+ * $limit rows even if $offset + $limit exceeds the total number of rows.
+ *
+ * @throws ezcQueryInvalidException if offset is used and orderBy is not.
+ * @return string
+ */
+ public function getQuery()
+ {
+ $query = parent::getQuery();
+ if ( $this->hasLimit )
+ {
+ if( $this->offset)
+ {
+ if(!$this->orderString)
+ {
+ //Uh ow. We need some columns to sort in the oposite order
to make this work
+ throw new ezcQueryInvalidException( "LIMIT workaround for
MS SQL", "orderBy() was not called before getQuery()." );
+ }
+ return 'SELECT * FROM ( SELECT TOP ' . $this->limit . ' * FROM
( ' . self::top( $this->offset + $this->limit, $query ) . ' ) AS ezcDummyTable1
' . $this->invertedOrderString . ' ) AS ezcDummyTable2 ' . $this->orderString;
+ }
+ return self::top( $this->limit, $query );
+ }
+ return $query;
+ }
+}
+
+?>
Property changes on:
trunk/Database/src/sqlabstraction/implementations/query_select_mssql.php
___________________________________________________________________
Name: svn:eol-style
+ native
Modified: trunk/Database/tests/factory_test.php
===================================================================
--- trunk/Database/tests/factory_test.php 2007-05-03 16:43:33 UTC (rev
5027)
+++ trunk/Database/tests/factory_test.php 2007-05-03 19:19:57 UTC (rev
5028)
@@ -45,14 +45,14 @@
public function testGetImplementations()
{
$array = ezcDbFactory::getImplementations();
- $this->assertEquals( array( 'mysql', 'pgsql', 'oracle', 'sqlite' ),
$array );
+ $this->assertEquals( array( 'mysql', 'pgsql', 'oracle', 'sqlite',
'mssql' ), $array );
}
public function testGetImplementationsAfterAddingOne()
{
ezcDbFactory::addImplementation( 'test', 'ezcDbHandlerTest' );
$array = ezcDbFactory::getImplementations();
- $this->assertEquals( array( 'mysql', 'pgsql', 'oracle', 'sqlite',
'test' ), $array );
+ $this->assertEquals( array( 'mysql', 'pgsql', 'oracle', 'sqlite',
'mssql', 'test' ), $array );
}
public function testSqliteDSN1()
Modified: trunk/Database/tests/handler_test.php
===================================================================
--- trunk/Database/tests/handler_test.php 2007-05-03 16:43:33 UTC (rev
5027)
+++ trunk/Database/tests/handler_test.php 2007-05-03 19:19:57 UTC (rev
5028)
@@ -52,6 +52,7 @@
case "ezcDbHandlerOracle":
case "ezcDbHandlerPgsql":
case "ezcDbHandlerSqlite":
+ case "ezcDbHandlerMssql":
$quoteChars = array( '"', '"' );
break;
@@ -76,6 +77,7 @@
case "ezcDbHandlerOracle":
case "ezcDbHandlerPgsql":
case "ezcDbHandlerSqlite":
+ case "ezcDbHandlerMssql":
$quoteChars = array( '"', '"' );
break;
Modified: trunk/Database/tests/pdo_test.php
===================================================================
--- trunk/Database/tests/pdo_test.php 2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/pdo_test.php 2007-05-03 19:19:57 UTC (rev 5028)
@@ -14,7 +14,7 @@
* @package Database
* @subpackage Tests
*/
-class PDOTest extends ezcTestCase
+class ezcPdoTest extends ezcTestCase
{
protected function setUp()
{
@@ -52,6 +52,11 @@
public function testIdNotFound()
{
$db = ezcDbInstance::get();
+ if ( $db->getName() != 'mysql' )
+ {
+ return; // no need to test it in RDBMS other than MySQL
+ }
+
$q = $db->prepare("INSERT INTO query_test VALUES( 1, 'name',
'section', 22)" );
$q->execute();
@@ -68,6 +73,7 @@
// Works in PHP 5.2.1RC2, segfaults in PHP 5.1.4
+/*
public function testSegfaultWrongFunctionCall()
{
$db = ezcDbInstance::get();
@@ -77,12 +83,15 @@
$q->oasdfa(); // Wrong method call.
}
-
-
+*/
// Works in PHP 5.1.4, Fails (hangs) in PHP 5.2.1RC2-dev.
public function testInsertWithWrongColon()
{
$db = ezcDbInstance::get();
+ if ( $db->getName() != 'mysql' )
+ {
+ return; // no need to test it in RDBMS other than MySQL.
+ }
$q = $db->prepare("INSERT INTO query_test VALUES( ':id', 'name',
'section', 22)" ); // <-- ':id' should be :id (or a string without ":")
$q->execute();
@@ -90,7 +99,7 @@
public static function suite()
{
- return new PHPUnit_Framework_TestSuite( "PDOTest" );
+ return new PHPUnit_Framework_TestSuite( "ezcPdoTest" );
}
}
Modified: trunk/Database/tests/sqlabstraction/expression_test.php
===================================================================
--- trunk/Database/tests/sqlabstraction/expression_test.php 2007-05-03
16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/sqlabstraction/expression_test.php 2007-05-03
19:19:57 UTC (rev 5028)
@@ -45,7 +45,7 @@
catch ( Exception $e ) {} // eat
// insert some data
- $this->db->exec( 'CREATE TABLE query_test ( id int, company
VARCHAR(255), section VARCHAR(255), employees int )' );
+ $this->db->exec( 'CREATE TABLE query_test ( id int, company
VARCHAR(255), section VARCHAR(255), employees int NULL)' );
$this->db->exec( "INSERT INTO query_test VALUES ( 1, 'eZ systems',
'Norway', 20 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 2, 'IBM', 'Norway',
500 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 3, 'eZ systems',
'Ukraine', 10 )" );
@@ -360,6 +360,10 @@
$reference = " encode( digest( name, 'md5' ), 'hex' ) ";
}
}
+ else if ( $this->db->getName() == 'mssql' )
+ {
+ $reference = "SUBSTRING( master.dbo.fn_varbintohexstr( HashBytes(
'MD5', name ) ), 3, 32)";
+ }
else
{
$reference = 'MD5( name )';
@@ -370,6 +374,10 @@
public function testLength()
{
$reference = 'LENGTH( name )';
+ if ( $this->db->getName() == 'mssql' )
+ {
+ $reference = 'LEN( name )';
+ }
$this->assertEquals( $reference, $this->e->length( 'name' ) );
}
@@ -382,6 +390,10 @@
public function testMod()
{
$reference = 'MOD( 10, 3 )';
+ if ( $this->db->getName() == 'mssql' )
+ {
+ $reference = '10 % 3';
+ }
$this->assertEquals( $reference, $this->e->mod( 10, 3 ) );
}
@@ -404,9 +416,11 @@
case 'ezcDbHandlerOracle':
$reference = "LOCALTIMESTAMP";
break;
+
+ case 'ezcDbHandlerMssql':
default:
- $reference = 'NOW()';
+ $reference = 'GETDATE()';
break;
}
@@ -779,14 +793,32 @@
public function testMd5Impl()
{
$company = 'eZ systems';
- $this->q->select( 'company',
- $this->e->md5( $this->e->round( $this->e->avg(
'employees' ), 0 ) ) )
- ->from( 'query_test' )
- ->where( $this->e->eq( 'company', $this->q->bindParam( $company )
) )
- ->groupBy( 'company' );
- $stmt = $this->q->prepare();
- $stmt->execute();
-
+ if ( $this->db->getName() == 'mssql' ) // use a bit different test for
MSSQL as it's MD5() implementation
+ // requires text parameter but
result of round() has type int.
+ {
+ $this->q->select( 'company', $this->e->round( $this->e->avg(
'employees' ), 0 ) )
+ ->from( 'query_test' )
+ ->where( $this->e->eq( 'company', $this->q->bindParam(
$company ) ) )
+ ->groupBy( 'company' );
+ $stmt = $this->q->prepare();
+ $stmt->execute();
+
+ $tmpValue = $stmt->fetchColumn( 1 );
+ $this->q->reset();
+ $this->q->select( 0, $this->q->expr->md5( "'$tmpValue'" ) ) ;
+ $stmt = $this->q->prepare();
+ $stmt->execute();
+ }
+ else
+ {
+ $this->q->select( 'company',
+ $this->e->md5( $this->e->round( $this->e->avg(
'employees' ), 0 ) ) )
+ ->from( 'query_test' )
+ ->where( $this->e->eq( 'company', $this->q->bindParam(
$company ) ) )
+ ->groupBy( 'company' );
+ $stmt = $this->q->prepare();
+ $stmt->execute();
+ }
$this->assertEquals( '9bf31c7ff062936a96d3c8bd1f8f2ff3',
$stmt->fetchColumn( 1 ) );
}
@@ -1134,14 +1166,33 @@
{
$this->q->setAliases( array( 'text' => 'company', 'empl' =>
'employees' ) );
$company = 'eZ systems';
- $this->q->select( 'text',
- $this->q->expr->md5( $this->q->expr->round(
$this->q->expr->avg( 'empl' ), 0 ) ) )
- ->from( 'query_test' )
- ->where( $this->q->expr->eq( 'text', $this->q->bindParam( $company
) ) )
- ->groupBy( 'text' );
- $stmt = $this->q->prepare();
- $stmt->execute();
-
+
+ if ( $this->db->getName() == 'mssql' ) // use a bit different test for
MSSQL as it's MD5() implementation
+ // requires text parameter but
result of round() has type int.
+ {
+ $this->q->select( 'text', $this->q->expr->round(
$this->q->expr->avg( 'empl' ), 0 ) )
+ ->from( 'query_test' )
+ ->where( $this->q->expr->eq( 'text', $this->q->bindParam(
$company ) ) )
+ ->groupBy( 'text' );
+ $stmt = $this->q->prepare();
+ $stmt->execute();
+
+ $this->q->reset();
+ $tmpValue = $stmt->fetchColumn( 1 );
+ $this->q->select( 0, $this->q->expr->md5( "'$tmpValue'" ) ) ;
+ $stmt = $this->q->prepare();
+ $stmt->execute();
+ }
+ else
+ {
+ $this->q->select( 'text',
+ $this->q->expr->md5( $this->q->expr->round(
$this->q->expr->avg( 'empl' ), 0 ) ) )
+ ->from( 'query_test' )
+ ->where( $this->q->expr->eq( 'text', $this->q->bindParam(
$company ) ) )
+ ->groupBy( 'text' );
+ $stmt = $this->q->prepare();
+ $stmt->execute();
+ }
$this->assertEquals( '9bf31c7ff062936a96d3c8bd1f8f2ff3',
$stmt->fetchColumn( 1 ) );
}
Modified: trunk/Database/tests/sqlabstraction/query_insert_test.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_insert_test.php 2007-05-03
16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/sqlabstraction/query_insert_test.php 2007-05-03
19:19:57 UTC (rev 5028)
@@ -214,9 +214,9 @@
$section1 = "Norway";
$section2 = "Ukraine";
- if ( $db->getName() == 'mysql' || $db->getName() == 'sqlite' )
+ if ( $db->getName() == 'mysql' || $db->getName() == 'sqlite' ||
$db->getName() == 'mssql')
{
- return; // no need to test it in MySQL and SQLite as they have
autoincrement
+ return; // no need to test it in MySQL, SQLite and MSSQL as they
have autoincrement
}
if ( $db->getName() == 'oracle' )
Modified: trunk/Database/tests/sqlabstraction/query_select_test_impl.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_select_test_impl.php
2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/sqlabstraction/query_select_test_impl.php
2007-05-03 19:19:57 UTC (rev 5028)
@@ -46,14 +46,14 @@
catch ( Exception $e ) {} // eat
// insert some data
- $this->db->exec( 'CREATE TABLE query_test ( id int, company
VARCHAR(255), section VARCHAR(255), employees int )' );
+ $this->db->exec( 'CREATE TABLE query_test ( id int, company
VARCHAR(255), section VARCHAR(255), employees int NULL )' );
$this->db->exec( "INSERT INTO query_test VALUES ( 1, 'eZ systems',
'Norway', 20 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 2, 'IBM', 'Norway',
500 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 3, 'eZ systems',
'Ukraine', 10 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 4, 'IBM', 'Germany',
null )" );
// insert some data
- $this->db->exec( 'CREATE TABLE query_test2 ( id int, company
VARCHAR(255), section VARCHAR(255), employees int )' );
+ $this->db->exec( 'CREATE TABLE query_test2 ( id int, company
VARCHAR(255), section VARCHAR(255), employees int NULL )' );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 1, 'eZ systems',
'Norway', 20 )" );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 2, 'IBM', 'Norway',
500 )" );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 3, 'eZ systems',
'Ukraine', 10 )" );
Modified: trunk/Database/tests/sqlabstraction/query_subselect_test.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_subselect_test.php
2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/sqlabstraction/query_subselect_test.php
2007-05-03 19:19:57 UTC (rev 5028)
@@ -104,7 +104,7 @@
->selectDistinct( '*' )
->from( 'table' )
->where(
- $this->q->expr->eq( 'id', $q2 )
+ $this->q->expr->eq( 'id', $q2->getQuery() )
);
$this->assertEquals( $reference, $this->q->getQuery() );
Modified: trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
2007-05-03 19:19:57 UTC (rev 5028)
@@ -46,14 +46,14 @@
catch ( Exception $e ) {} // eat
// insert some data
- $this->db->exec( 'CREATE TABLE query_test ( id int, company
VARCHAR(255), section VARCHAR(255), employees int )' );
+ $this->db->exec( 'CREATE TABLE query_test ( id int, company
VARCHAR(255), section VARCHAR(255), employees int NULL )' );
$this->db->exec( "INSERT INTO query_test VALUES ( 1, 'eZ systems',
'Norway', 20 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 2, 'IBM', 'Norway',
500 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 3, 'eZ systems',
'Ukraine', 10 )" );
$this->db->exec( "INSERT INTO query_test VALUES ( 4, 'IBM', 'Germany',
null )" );
// insert some data
- $this->db->exec( 'CREATE TABLE query_test2 ( id int, company
VARCHAR(255), section VARCHAR(255), employees int )' );
+ $this->db->exec( 'CREATE TABLE query_test2 ( id int, company
VARCHAR(255), section VARCHAR(255), employees int NULL )' );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 1, 'eZ systems',
'Norway', 20 )" );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 2, 'IBM', 'Norway',
500 )" );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 3, 'eZ systems',
'Ukraine', 10 )" );
@@ -108,12 +108,12 @@
// bind values
$q2->selectDistinct( 'section' )
- ->from( 'query_test' );
+ ->from( 'query_test' )
+ ->where( ' id = 1 OR id = 2 ');
$q->selectDistinct( 'company' )
->from( 'query_test2' )
- ->where( $q->expr->in( 'section', $q2 ) );
-
+ ->where( $q->expr->in( 'section', $q2->getQuery() ) );
$stmt = $q->prepare();
$stmt->execute();
Modified: trunk/Database/tests/suite.php
===================================================================
--- trunk/Database/tests/suite.php 2007-05-03 16:43:33 UTC (rev 5027)
+++ trunk/Database/tests/suite.php 2007-05-03 19:19:57 UTC (rev 5028)
@@ -14,6 +14,7 @@
require_once 'factory_test.php';
require_once 'transactions_test.php';
require_once 'instance_test.php';
+require_once 'pdo_test.php';
require_once 'instance_delayed_init_test.php';
require_once 'handler_test.php';
require_once 'sqlabstraction/expression_test.php';
@@ -52,6 +53,7 @@
$this->addTest( ezcQueryInsertTest::suite() );
$this->addTest( ezcQueryUpdateTest::suite() );
$this->addTest( ezcQueryDeleteTest::suite() );
+ $this->addTest( ezcPdoTest::suite() );
}
public static function suite()
--
svn-components mailing list
[email protected]
http://lists.ez.no/mailman/listinfo/svn-components
