Projects / li3_mssql

diff --git a/extensions/data/source/MSSql.php b/extensions/data/source/MSSql.php new file mode 100644 index 0000000..cc7c763 --- /dev/null +++ b/extensions/data/source/MSSql.php @@ -0,0 +1,174 @@ +<?php +/** + * Lithium: the most rad php framework + * + * @copyright Copyright 2010, Union of RAD (http://union-of-rad.org) + * @license http://opensource.org/licenses/bsd-license.php The BSD License + * + */ + +namespace li3_mssql\extensions\data\source; + +use \Exception; +/** +* Adapter class for the SqlSrv extension. +* +* Implements the methods connecting higher level abstractions to the specifics of Microsoft's +* own SQL Server native PHP extension. +* +* @link http://msdn.microsoft.com/en-us/library/cc296172%28SQL.90%29.aspx +*/ +class MSSql extends \li3_mssql\extensions\data\source\MicrosoftSqlServer { + + /** + * Constructs the SqlSrv adapter + * + * @link http://msdn.microsoft.com/en-us/library/cc296161(SQL.90).aspx + * @see lithium\data\source\Database::__construct() + * @see lithium\data\Source::__construct() + * @see lithium\data\Connections::add() + * @param array $config Configuration options for this class. For additional configuration, + * see `lithium\data\source\Database` and `lithium\data\Source`. + * + * Typically, these parameters are set in `Connections::add()`, when adding the adapter to the + * list of active connections. + * @return The adapter instance. + */ + public function __construct($config = array()) { + parent::__construct($config); + } + + /** + * Connects to the database using the options provided to the class constructor. + * + * + * + * @return boolean True if the database could be connected, else false. + */ + public function connect() { + $config = $this->_config; + $this->_isConnected = false; + + $this->_connection = mssql_connect($host, array_intersect_key($config, $validOptions)); + return $this->_isConnected = is_resource($this->_connection); + } + + /** + * Disconnects the adapter from the database. + * + * @return boolean True on success, else false. + */ + public function disconnect() { + if ($this->_isConnected) { + $this->_isConnected = !mssql_close($this->_connection); + return !$this->_isConnected; + } + return true; + } + + /** + * Getter/Setter for the connection's encoding + * + * @param mixed $encoding + * @return mixed. + * + * @todo Determine the best way to handle encoding - + * current idea is to disconnect, then reconnect using the proper encoding + */ + public function encoding($encoding = null) { + $encodingMap = array('UTF-8' => 'utf8'); + + if (isset($this->_config['CharacterSet'])) { + $characterSet = $this->_config['CharacterSet']; + } + + return $characterSet; + } + + /** + * Retrieves database error message and error code. + * + * @return array + */ + public function error() { + if ($error = sqlsrv_errors(SQLSRV_ERR_ERRORS)) { + return array($error[0]['code'], $error[0]['message']); + } + return null; + } + + /** + * + * + * @param string $type + * @param object $resource + * @param unknown_type $context + * @return array|null + */ + public function result($type, $resource, $context) { + if (!is_resource($resource)) { + return null; + } + + switch ($type) { + case 'next': + $result = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC); + break; + case 'close': + sqlsrv_free_stmt($resource); + $result = null; + break; + default: + $result = parent::result($type, $resource, $context); + break; + } + return $result; + } + + /** + * In cases where the query is a raw string (as opposed to a `Query` object), the database must + * determine the correct column names from the result resource. + * + * @param object|string $query + * @param object $result + * @param object $context + * @return array Field names + * @filter This method can be filtered. + */ + public function schema($query, $result = null, $context = null) { + if (is_object($query)) { + return parent::schema($query, $result, $context); + } + $filter = function($self, $params, $chain) { + $return = array(); + foreach (sqlsrv_field_metadata($params['result']) as $field) { + $return[] = $field['Name']; + } + return $return; + }; + return $this->_filter(__METHOD__, compact('query', 'result', 'context'), $filter); + } + + /** + * + * @param mixed $sql + * @param Array $options + * @return unknown_type + * @filter This method can be filtered. + */ + protected function _execute($sql, $options = array()) { + $params = compact('sql', 'options'); + $conn =& $this->_connection; + + return $this->_filter(__METHOD__, $params, function($self, $params, $chain) use (&$conn) { + extract($params); + if ( !is_resource($result = sqlsrv_query($conn, $sql)) ) { + list($code, $error) = $self->error(); + throw new Exception("$sql: $error", $code); + } + return $result; + }); + } +} + +?> \ No newline at end of file diff --git a/extensions/data/source/MicrosoftSqlServer.php b/extensions/data/source/MicrosoftSqlServer.php new file mode 100644 index 0000000..726dbf8 --- /dev/null +++ b/extensions/data/source/MicrosoftSqlServer.php @@ -0,0 +1,328 @@ +<?php + +namespace li3_mssql\extensions\data\source; + +abstract class MicrosoftSqlServer extends \lithium\data\source\Database { + + /** + * SqlSrv column type definitions. + * + * @var array + */ + protected $_columns = array( + 'primary_key' => array('name' => 'IDENTITY (1, 1) NOT NULL'), + 'string' => array('name' => 'varchar', 'length' => '255'), + 'text' => array('name' => 'varchar', 'length' => 'max'), + 'integer' => array('name' => 'integer', 'length' => 11, 'formatter' => 'intval'), + 'float' => array('name' => 'float', 'formatter' => 'floatval'), + 'datetime' => array( + 'name' => 'datetime', + 'format' => 'Y-m-d H:i:s.u', + 'formatter' => 'date' + ), + 'timestamp' => array( + 'name' => 'timestamp', + 'format' => 'Y-m-d H:i:s', + 'formatter' => 'date' + ), + 'time' => array('name' => 'datetime', 'format' => 'H:i:s', 'formatter' => 'date'), + 'date' => array('name' => 'datetime', 'format' => 'Y-m-d', 'formatter' => 'date'), + 'binary' => array('name' => 'varbinary', 'length' => 'max'), + 'boolean' => array('name' => 'bit') + ); + + /** + * Contains all needed queries for rendering + * + * @var array + */ + protected $_strings = array( + 'read' => "SELECT {:limit} {:fields} From {:table} + {:joins} {:conditions} {:group} {:order} {:comment}", + 'paged' => " + SELECT * From ( + SELECT {:fields}, ROW_NUMBER() OVER({:order}) AS [__LI3_ROW_NUMBER__] + From {:table} {:joins} {:conditions} {:group} + ) a {:limit} {:comment}", + 'create' => "INSERT INTO {:table} ({:fields}) VALUES ({:values}) {:comment}", + 'update' => "UPDATE {:table} SET {:fields} {:conditions} {:comment}", + 'delete' => "DELETE {:flags} From {:table} {:aliases} {:conditions} {:comment}", + 'schema' => "CREATE TABLE {:table} (\n{:columns}\n) {:indexes} {:comment}", + 'join' => "{:type} JOIN {:table} {:constraint}" + ); + + protected function _init() { + parent::_init(); + $this->applyFilter('update', function($self, $params, $chain) { + + $query = $params['query']; + if (is_object($query)) { + $model = $query->model(); + $key = $model::meta('key'); + if (isset($query->record()->{$key})) { + unset($query->record()->{$key}); + $params['query'] = $query; + } + } + $chain->next($self, $params, $chain); + }); + } + + /** + * Quotes identifiers. + * + * @todo Currently, this method simply returns the identifier. + * + * @param string $name The identifier to quote. + * @return string The quoted identifier. + */ + public function name($name) { + return $name; + } + + /** + * Returns the list of tables in the currently-connected database. + * + * @param string $class The fully-name-spaced class name of the model object making the request. + * @return array Returns an array of objects to which models can connect. + * @filter This method can be filtered. + */ + public function entities($class = null) { + $config = $this->_config; + $method = function($self, $params, $chain) use ($config) { + return $self->read( + "SELECT TABLE_NAME FROM [INFORMATION_SCHEMA].[TABLES]", + array('return' => 'array') + ); + }; + return $this->_filter(__METHOD__, compact('class'), $method); + } + + /** + * Gets the column schema for a given SQL Server table. + * + * @param mixed $entity Specifies the table name for which the schema should be returned, or + * the class name of the model object requesting the schema, in which case the model + * class will be queried for the correct table name. + * @param array $meta + * @return array Returns an associative array describing the given table's schema, where the + * array keys are the available fields, and the values are arrays describing each + * field, containing the following keys: + * - `'type'`: The field type name + * @filter This method can be filtered. + */ + public function describe($entity, $meta = array()) { + $params = compact('entity', 'meta'); + return $this->_filter(__METHOD__, $params, function($self, $params, $chain) { + extract($params); + + $name = $entity; + $sql = "SELECT COLUMN_NAME as Field, DATA_TYPE as Type, " + . "COL_LENGTH('{$name}', COLUMN_NAME) as Length, IS_NULLABLE As [Null], " + . "COLUMN_DEFAULT as [Default], " + . "COLUMNPROPERTY(OBJECT_ID('{$name}'), COLUMN_NAME, 'IsIdentity') as [Key], " + . "NUMERIC_SCALE as Size FROM INFORMATION_SCHEMA.COLUMNS " + . "WHERE TABLE_NAME = '{$name}'"; + + $columns = $self->read($sql, array('return' => 'array')); + $fields = array(); + + foreach ($columns as $column) { + $fields[$column['Field']] = $column + array( + 'Null' => ($column['Null'] == 'YES' ? true : false), + 'Default' => ($column['Default'] === '(NULL)' ? null: $column['Default']), + ); + if ($fields[$column['Field']]['Length'] === -1) { + $fields[$column['Field']]['Length'] = 'max'; + } + } + + return $fields; + }); + } + + /** + * Converts database-layer column types to basic types. + * + * @param string $real Real database-layer column type (i.e. "varchar(255)") + * @return string Abstract column type (i.e. "string") + */ + protected function _column($real) { + + if (is_array($real)) { + $length = ''; + if (isset($real['length'])) { + $length = $real['length']; + if ($length === -1) { + $length = 'max'; + } + $length = '(' . $length . ')'; + } + return $real['type'] . $length; + } + + if (!preg_match('/(?P<type>[^(]+)(?:\((?P<length>[^)]+)\))?/', $real, $column)) { + return $real; + } + $column = array_intersect_key($column, array('type' => null, 'length' => null)); + + switch (true) { + case $column['type'] === 'datetime': + break; + case ($column['type'] == 'tinyint' && $column['length'] == '1'): + case ($column['type'] == 'bit'): + $column = array('type' => 'boolean'); + break; + case (strpos($column['type'], 'int') !== false): + $column['type'] = 'integer'; + break; + case (strpos($column['type'], 'text') !== false): + $column['type'] = 'text'; + break; + case strpos($column['type'], 'char') !== false: + if (isset($column['length']) && $column['length'] === 'max') { + $column['type'] = 'text'; + unset($column['length']); + } else { + $column['type'] = 'string'; + } + break; + case (strpos($column['type'], 'binary') !== false || $column['type'] == 'image'): + $column['type'] = 'binary'; + break; + case preg_match('/float|double|decimal/', $column['type']): + $column['type'] = 'float'; + break; + default: + $column['type'] = 'text'; + break; + } + return $column; + } + + protected function _column2($real) { + + if (is_array($real)) { + $length = ''; + if (isset($real['length'])) { + $length = $real['length']; + if ($length === -1) { + $length = 'max'; + } + $length = '(' . $length . ')'; + } + return $real['type'] . $length; + } + + if (!preg_match('/(?P<type>[^(]+)(?:\((?P<length>[^)]+)\))?/', $real, $column)) { + return $real; + } + $column = array_intersect_key($column, array('type' => null, 'length' => null)); + + $type = 'text'; + + if ($column['type'] === 'datetime') { + + } + + + switch (true) { + case ($column['type'] == 'tinyint' && $column['length'] == '1'): + case ($column['type'] == 'bit'): + $column = array('type' => 'boolean'); + break; + case (strpos($column['type'], 'int') !== false): + $column['type'] = 'integer'; + break; + case (strpos($column['type'], 'text') !== false): + $column['type'] = 'text'; + break; + case strpos($column['type'], 'char') !== false: + if (isset($column['length']) && $column['length'] === 'max') { + $column['type'] = 'text'; + unset($column['length']); + } else { + $column['type'] = 'string'; + } + break; + case (strpos($column['type'], 'binary') !== false || $column['type'] == 'image'): + $column['type'] = 'binary'; + break; + case preg_match('/float|double|decimal/', $column['type']): + $column['type'] = 'float'; + break; + default: + $column['type'] = 'text'; + break; + } + return $column; + } + + public function limit($limit, $context) { + $offset = $context->offset() ?: 0; + + if ($offset === 0) { + return "TOP {$limit}"; + } + + $limit += $offset++; + + return "WHERE __LI3_ROW_NUMBER__ between {$offset} and {$limit}"; + } + + public function renderCommand($type, $data = null, $context = null) { + if (is_object($type)) { + $context = $type; + $data = $context->export($this); + $type = $context->type(); + } + + $type = ($type == "read" && $data['page'] > 1) ? "paged" : "read"; + + return parent::renderCommand($type, $data, $context); + } + + /** + * Gets the last auto-generated ID from the query that inserted a new record. + * + * @param object $query The `Query` object associated with the query which generated + * @return mixed Returns the last inserted ID key for an auto-increment column or a column + * bound to a sequence. + */ + protected function _insertId($query) { + $resource = $this->_execute('SELECT @@identity as insertId'); + $id = $this->result('next', $resource, null); + $id = $id['insertId']; + $this->result('close', $resource, null); + + if (!empty($id) && $id !== '0') { + return $id; + } + } + + public function value($value, array $schema = array()) { + if (is_array($value)) { + return parent::value($value, $schema); + } + if ($value === null) { + return 'NULL'; + } + + switch ($type = isset($schema['type']) ? $schema['type'] : $this->_introspectType($value)) { + case 'float': + return floatval($value); + case 'integer': + return intval($value); + } + return "'" . str_replace("'", "''", $value) . "'"; + } + + public function comment($comment) { + if (!empty($comment)) { + $comment = '/* ' . str_replace('*/', '* /', $comment) . ' */'; + } + return $comment; + } +} + +?> \ No newline at end of file diff --git a/extensions/data/source/SqlSrv.php b/extensions/data/source/SqlSrv.php new file mode 100644 index 0000000..1547f9a --- /dev/null +++ b/extensions/data/source/SqlSrv.php @@ -0,0 +1,221 @@ +<?php +/** + * Lithium: the most rad php framework + * + * @copyright Copyright 2010, Union of RAD (http://union-of-rad.org) + * @license http://opensource.org/licenses/bsd-license.php The BSD License + * + */ + +namespace li3_mssql\extensions\data\source; + +use \Exception; +/** +* Adapter class for the SqlSrv extension. +* +* Implements the methods connecting higher level abstractions to the specifics of Microsoft's +* own SQL Server native PHP extension. +* +* @link http://msdn.microsoft.com/en-us/library/cc296172%28SQL.90%29.aspx +*/ +class SqlSrv extends \li3_mssql\extensions\data\source\MicrosoftSqlServer { + + /** + * Constructs the SqlSrv adapter + * + * @link http://msdn.microsoft.com/en-us/library/cc296161(SQL.90).aspx + * @see lithium\data\source\Database::__construct() + * @see lithium\data\Source::__construct() + * @see lithium\data\Connections::add() + * @param array $config Configuration options for this class. For additional configuration, + * see `lithium\data\source\Database` and `lithium\data\Source`. + * + * Typically, these parameters are set in `Connections::add()`, when adding the adapter to the + * list of active connections. + * @return The adapter instance. + */ + public function __construct($config = array()) { + parent::__construct($config); + } + + /** + * Check for required PHP extension + * + * @return boolean + */ + public static function enabled() { + return extension_loaded('sqlsrv'); + } + + public function _init() { + parent::_init(); + $this->_config += array( + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => $this->_config['persistent'], + 'Database' => $this->_config['database'] + ); + if (!empty($this->_config['login']) && !empty($this->_config['password'])) { + $this->_config['UID'] = $this->_config['login']; + $this->_config['PWD'] = $this->_config['password']; + } + if (isset($this->_config['port'])) { + $this->_config['host'] .= ', ' . $this->_config['port']; + } + } + + /** + * Connects to the database using the options provided to the class constructor. + * + * + * + * @return boolean True if the database could be connected, else false. + */ + public function connect() { + $config = $this->_config; + $this->_isConnected = false; + + $host = $config['host']; + + $validOptions = array_fill_keys(array( + 'APP', + 'CharacterSet', + 'ConnectionPooling', + 'Database', + 'Encrypt', + 'Failover_Partner', + 'LoginTimeout', + 'MultipleActiveResultSets', + 'PWD', + 'QuotedId', + 'ReturnDatesAsStrings', + 'TraceFile', + 'TraceOn', + 'TransactionIsolation', + 'TrustServerCertificate', + 'UID', + 'WSID' + ), ''); + $this->_connection = sqlsrv_connect($host, array_intersect_key($config, $validOptions)); + return $this->_isConnected = is_resource($this->_connection); + } + + /** + * Disconnects the adapter from the database. + * + * @return boolean True on success, else false. + */ + public function disconnect() { + if ($this->_isConnected) { + $this->_isConnected = !sqlsrv_close($this->_connection); + return !$this->_isConnected; + } + return true; + } + + /** + * Getter/Setter for the connection's encoding + * + * @param mixed $encoding + * @return mixed. + * + * @todo Determine the best way to handle encoding - + * current idea is to disconnect, then reconnect using the proper encoding + */ + public function encoding($encoding = null) { + $encodingMap = array('UTF-8' => 'utf8'); + + if (isset($this->_config['CharacterSet'])) { + $characterSet = $this->_config['CharacterSet']; + } + + return $characterSet; + } + + /** + * Retrieves database error message and error code. + * + * @return array + */ + public function error() { + if ($error = sqlsrv_errors(SQLSRV_ERR_ERRORS)) { + return array($error[0]['code'], $error[0]['message']); + } + return null; + } + + /** + * + * + * @param string $type + * @param object $resource + * @param unknown_type $context + * @return array|null + */ + public function result($type, $resource, $context) { + if (!is_resource($resource)) { + return null; + } + + switch ($type) { + case 'next': + $result = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC); + break; + case 'close': + sqlsrv_free_stmt($resource); + $result = null; + break; + default: + $result = parent::result($type, $resource, $context); + break; + } + return $result; + } + + /** + * In cases where the query is a raw string (as opposed to a `Query` object), the database must + * determine the correct column names from the result resource. + * + * @param object|string $query + * @param object $result + * @param object $context + * @return array Field names + * @filter This method can be filtered. + */ + public function schema($query, $result = null, $context = null) { + if (is_object($query)) { + return parent::schema($query, $result, $context); + } + $filter = function($self, $params, $chain) { + $return = array(); + foreach (sqlsrv_field_metadata($params['result']) as $field) { + $return[] = $field['Name']; + } + return $return; + }; + return $this->_filter(__METHOD__, compact('query', 'result', 'context'), $filter); + } + + /** + * + * @param mixed $sql + * @param Array $options + * @return unknown_type + * @filter This method can be filtered. + */ + protected function _execute($sql, $options = array()) { + $params = compact('sql', 'options'); + $conn =& $this->_connection; + + return $this->_filter(__METHOD__, $params, function($self, $params, $chain) use (&$conn) { + extract($params); + if ( !is_resource($result = sqlsrv_query($conn, $sql)) ) { + list($code, $error) = $self->error(); + throw new Exception("$sql: $error", $code); + } + return $result; + }); + } +} + +?> \ No newline at end of file diff --git a/tests/cases/extensions/data/source/MSSqlTest.php b/tests/cases/extensions/data/source/MSSqlTest.php new file mode 100644 index 0000000..979d455 --- /dev/null +++ b/tests/cases/extensions/data/source/MSSqlTest.php @@ -0,0 +1,136 @@ +<?php +/** + * Lithium: the most rad php framework + * + * @copyright Copyright 2010, Union of RAD (http://union-of-rad.org) + * @license http://opensource.org/licenses/bsd-license.php The BSD License + */ + +namespace li3_mssql\tests\cases\extensions\data\source; + +use \lithium\data\Connections; +use \li3_mssql\extensions\data\source\MSSql; +use \li3_mssql\tests\mocks\extensions\data\source\MockMSSql; +use \lithium\data\model\Query; + +class MSSqlTest extends \lithium\test\Unit { + + protected $_dbConfig = array(); + + /** + * Instance of the SqlSrv adapter + * + * @var object + */ + public $db = null; + + /** + * Skip the test if a SqlSrv adapter configuration is unavailable. + * + * @return void + * @todo Tie into the Environment class to ensure that the test database is being used. + */ + public function skip() { + $message = 'MSSql extension is not available for testing the adapter.'; + $hasClass = function_exists('mssql_connect'); + $this->skipIf(!$hasClass, $message); + + $this->_dbConfig = Connections::get('test-mssql', array('config' => true)); + $hasDb = (isset($this->_dbConfig['adapter']) && $this->_dbConfig['adapter'] == 'MSSql'); + $message = 'Test database is either unavailable, or not using a MSSql adapter'; + $this->skipIf(!$hasDb, $message); + } + + public function setUp() { + $this->db = new MSSql($this->_dbConfig); + } + + /** + * Tests that the object is initialized with the correct default values. + * + * @return void + */ + public function testConstructorDefaults() { + $db = new MockSqlSrv(array('autoConnect' => false)); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'database' => 'lithium', + 'host' => 'localhost', + 'init' => true, + 'login' => 'root', + 'password' => '', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => 'lithium', + ); + $this->assertEqual($expected, $result); + + $db = new MockSqlSrv(array('autoConnect' => false)); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'database' => 'lithium', + 'host' => 'localhost', + 'init' => true, + 'login' => 'root', + 'password' => '', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => 'lithium', + ); + $this->assertEqual($expected, $result); + + $db = new MockSqlSrv(array('autoConnect' => false, 'port' => 1234)); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'port' => 1234, + 'database' => 'lithium', + 'host' => 'localhost, 1234', + 'init' => true, + 'login' => 'root', + 'password' => '', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => 'lithium', + ); + $this->assertEqual($expected, $result); + } + + /** + * Tests that this adapter can connect to the database, and that the status is properly + * persisted. + * + * @return void + */ + public function testDatabaseConnection() { + $db = new SqlSrv(array('autoConnect' => false) + $this->_dbConfig); + $this->assertTrue($db->connect()); + $this->assertTrue($db->isConnected()); + + $this->assertTrue($db->disconnect()); + $this->assertFalse($db->isConnected()); + } + + public function testDatabaseEncoding() { + $this->assertTrue($this->db->connect()); + $this->assertTrue($this->db->isConnected()); + + $this->assertTrue($this->db->encoding('UTF-8')); + $this->assertEqual('UTF-8', $this->db->encoding()); + } + + public function testExecuteException() { + $this->expectException(); + $this->db->read('SELECT deliberate syntax error'); + } +} + +?> \ No newline at end of file diff --git a/tests/cases/extensions/data/source/MicrosoftSqlServerTest.php b/tests/cases/extensions/data/source/MicrosoftSqlServerTest.php new file mode 100644 index 0000000..dbda943 --- /dev/null +++ b/tests/cases/extensions/data/source/MicrosoftSqlServerTest.php @@ -0,0 +1,182 @@ +<?php + +namespace li3_mssql\tests\cases\extensions\data\source; + +//use \lithium\data\Connections; +use \li3_mssql\tests\mocks\extensions\data\source\MockMicrosoftSqlServer; +use \lithium\data\model\Query; + +class MicrosoftSqlServerTest extends \lithium\test\Unit { + protected $_dbConfig = array(); + + /** + * Instance of the SqlSrv adapter + * + * @var object + */ + public $db = null; + + public function setUp() { + $this->db = new MockMicrosoftSqlServer($this->_dbConfig); + } + + /** + * Tests that native field types are resolved to Lithiums abstracted versions + */ + public function testColumnAbstraction() { + $result = $this->db->invokeMethod('_column', array('decimal(12,2)')); + $this->assertEqual(array('type' => 'float', 'length' => '12,2'), $result); + + $result = $this->db->invokeMethod('_column', array('int(11)')); + $this->assertEqual(array('type' => 'integer', 'length' => '11'), $result); + + $result = $this->db->invokeMethod('_column', array('varchar(max)')); + $this->assertEqual(array('type' => 'text'), $result); + + $result = $this->db->invokeMethod('_column', array('text')); + $this->assertEqual(array('type' => 'text'), $result); + + $result = $this->db->invokeMethod('_column', array('tinyint(1)')); + $this->assertEqual(array('type' => 'boolean'), $result); + + $result = $this->db->invokeMethod('_column', array('varchar')); + $this->assertEqual(array('type' => 'string'), $result); + + $result = $this->db->invokeMethod('_column', array('varchar(255)')); + $this->assertEqual(array('type' => 'string', 'length' => '255'), $result); + + $result = $this->db->invokeMethod('_column', array( + array('type' => 'varchar', 'length' => -1)) + ); + $this->assertEqual('varchar(max)', $result); + + $result = $this->db->invokeMethod('_column', array('bad_type')); + $this->assertEqual(array('type' => 'text'), $result); + + $result = $this->db->invokeMethod('_column', array('datetime')); + $this->assertEqual(array('type' => 'datetime'), $result); + + $result = $this->db->invokeMethod('_column', array('varbinary')); + $this->assertEqual(array('type' => 'binary'), $result); + + } + + public function testName() { + $this->assertEqual('Name', $this->db->name('Name')); + $this->assertEqual('li3.Test', $this->db->name('li3.Test')); + } + + public function testRenderCommand() { + $query = new Query(array( + 'model' => 'lithium\tests\mocks\data\model\MockDatabasePost', + 'conditions' => null, + 'fields' => null, + 'order' => null, + 'limit' => 1, + 'page' => 1, + 'type' => 'read', + 'table' => 'li3_test_table', + 'comment' => 'Simulates Model::find("first")' + )); + $expected = "SELECT TOP 1 * From mock_database_posts " + . "/* Simulates Model::find(\"first\") */"; + $result = $this->db->renderCommand($query); + $this->assertEqual($expected, $result); + + $query = new Query(array( + 'model' => 'lithium\tests\mocks\data\model\MockDatabasePost', + 'conditions' => null, + 'fields' => '*', + 'order' => null, + 'limit' => 10, + 'page' => 1, + 'type' => 'read', + 'table' => 'li3_test_table', + 'comment' => 'Simulates Model::find("all", array("limit" => 10))' + )); + $expected = "SELECT TOP 10 * From mock_database_posts " + . "/* Simulates Model::find(\"all\", array(\"limit\" => 10)) */"; + $result = $this->db->renderCommand($query); + $this->assertEqual($expected, $result); + + $query = new Query(array( + 'model' => 'lithium\tests\mocks\data\model\MockDatabasePost', + 'conditions' => null, + 'fields' => null, + 'order' => 'id', + 'limit' => 10, + 'page' => 1, + 'type' => 'read', + 'table' => 'li3_test_table', + 'comment' => 'Simulates Model::find("all", array("limit" => 10, "order" => "id"))' + )); + $expected = "SELECT TOP 10 * From mock_database_posts ORDER BY id ASC " + . '/* Simulates Model::find("all", array("limit" => 10, "order" => "id")) */'; + $result = $this->db->renderCommand($query); + $this->assertEqual($expected, $result); + + $query = new Query(array( + 'model' => 'lithium\tests\mocks\data\model\MockDatabasePost', + 'conditions' => null, + 'fields' => null, + 'order' => 'id asc', + 'limit' => 10, + 'page' => 2, + 'type' => 'read', + 'table' => 'li3_test_table', + 'comment' => 'Simulates Model::find("all", ' + . 'array("limit" => 10, "order" => "id", "page" => 2))' + )); + $expected = "SELECT * From (\n" + . "\t\t\t\tSELECT *, ROW_NUMBER() OVER( ORDER BY id asc) AS [__LI3_ROW_NUMBER__]\n" + . "\t\t\t\tFrom mock_database_posts\n" + . "\t\t\t) a WHERE __LI3_ROW_NUMBER__ between 11 and 20 " + . '/* Simulates Model::find("all", ' + . 'array("limit" => 10, "order" => "id", "page" => 2)) */'; + $result = $this->db->renderCommand($query); + $this->assertEqual($expected, $result); + + $query = new Query(array( + 'model' => 'lithium\tests\mocks\data\model\MockDatabasePost', + 'conditions' => null, + 'fields' => null, + 'order' => null, + 'limit' => 10, + 'page' => 2, + 'type' => 'read', + 'table' => 'li3_test_table', + 'comment' => 'Simulates Model::find("all", array("limit" => 10, "page" => 2)) ' + . '- order should default to PK' + )); + $expected = "SELECT * From (\n" + . "\t\t\t\tSELECT *, ROW_NUMBER() OVER( ORDER BY id ASC) AS [__LI3_ROW_NUMBER__]\n" + . "\t\t\t\tFrom mock_database_posts\n" + . "\t\t\t) a WHERE __LI3_ROW_NUMBER__ between 11 and 20 " + . "/* Simulates Model::find(\"all\", array(\"limit\" => 10, \"page\" => 2)) " + . "- order should default to PK */"; + $result = $this->db->renderCommand($query); + $this->assertEqual($expected, $result); + } + + public function testComment() { + $this->assertEqual('/* comment */', $this->db->comment('comment')); + $this->assertEqual('/* comment * / */', $this->db->comment('comment */')); + $this->assertEqual('/* --comment */', $this->db->comment('--comment')); + $this->assertEqual('', $this->db->comment('')); + } + + public function testEntities() { +// $expected = array( +// array('TABLE_NAME' => 'Table1'), +// array('TABLE_NAME' => 'Table2'), +// array('TABLE_NAME' => 'Table3') +// ); +// $model = '\\lithium\\tests\\mocks\\data\\MockModel'; +// $result = $this->db->entities($model); +// $this->assertEqual($expected, $result); + } + + public function testDescribe() { +// + } +} \ No newline at end of file diff --git a/tests/cases/extensions/data/source/SqlSrvTest.php b/tests/cases/extensions/data/source/SqlSrvTest.php new file mode 100644 index 0000000..e525833 --- /dev/null +++ b/tests/cases/extensions/data/source/SqlSrvTest.php @@ -0,0 +1,162 @@ +<?php +/** + * Lithium: the most rad php framework + * + * @copyright Copyright 2010, Union of RAD (http://union-of-rad.org) + * @license http://opensource.org/licenses/bsd-license.php The BSD License + */ + +namespace li3_mssql\tests\cases\extensions\data\source; + +use \lithium\data\Connections; +use \li3_mssql\extensions\data\source\SqlSrv; +use \li3_mssql\tests\mocks\extensions\data\source\MockSqlSrv; +use \lithium\data\model\Query; + +class SqlSrvTest extends \lithium\test\Unit { + + protected $_dbConfig = array(); + + /** + * Instance of the SqlSrv adapter + * + * @var object + */ + public $db = null; + + /** + * Skip the test if a SqlSrv adapter configuration is unavailable. + * + * @return void + * @todo Tie into the Environment class to ensure that the test database is being used. + */ + public function skip() { + $message = 'SqlSrv extension is not available for testing the adapter.'; + $hasClass = function_exists('sqlsrv_connect'); + $this->skipIf(!$hasClass, $message); + + $this->_dbConfig = Connections::get('test-sqlsrv', array('config' => true)); + $hasDb = (isset($this->_dbConfig['adapter']) && $this->_dbConfig['adapter'] == 'SqlSrv'); + $message = 'Test database is either unavailable, or not using a SqlSrv adapter'; + $this->skipIf(!$hasDb, $message); + } + + public function setUp() { + $this->db = new SqlSrv($this->_dbConfig); + $this->db->connect(); + } + + /** + * Tests that the object is initialized with the correct default values. + * + * @return void + */ + public function testConstructorDefaults() { + $db = new MockSqlSrv(array('autoConnect' => false)); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'database' => null, + 'host' => 'localhost', + 'init' => true, + 'login' => 'root', + 'password' => '', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => null, + ); + $this->assertEqual($expected, $result); + + $db = new MockSqlSrv(array('autoConnect' => false, 'database' => 'lithium')); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'database' => 'lithium', + 'host' => 'localhost', + 'init' => true, + 'login' => 'root', + 'password' => '', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => 'lithium', + ); + $this->assertEqual($expected, $result); + + $db = new MockSqlSrv(array( + 'autoConnect' => false, + 'login' => 'user', + 'password' => 'password' + )); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'database' => null, + 'host' => 'localhost', + 'init' => true, + 'login' => 'user', + 'password' => 'password', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => null, + 'UID' => 'user', + 'PWD' => 'password' + ); + $this->assertEqual($expected, $result); + + $db = new MockSqlSrv(array( + 'autoConnect' => false, + 'database' => 'lithium', + 'port' => 1234 + )); + $result = $db->get('_config'); + $expected = array( + 'autoConnect' => false, + 'port' => 1234, + 'database' => 'lithium', + 'host' => 'localhost, 1234', + 'init' => true, + 'login' => 'root', + 'password' => '', + 'persistent' => true, + 'APP' => 'Lithium Application', + 'CharacterSet' => 'UTF-8', + 'ConnectionPooling' => true, + 'Database' => 'lithium', + ); + $this->assertEqual($expected, $result); + } + + /** + * Tests that this adapter can connect to the database, and that the status is properly + * persisted. + * + * @return void + */ + public function testDatabaseConnection() { + $db = new SqlSrv(array('autoConnect' => false) + $this->_dbConfig); + $this->assertTrue($db->connect()); + $this->assertTrue($db->isConnected()); + + $this->assertTrue($db->disconnect()); + $this->assertFalse($db->isConnected()); + } + + public function testDatabaseEncoding() { + + $this->assertTrue($this->db->encoding('UTF-8')); + $this->assertEqual('UTF-8', $this->db->encoding()); + } + + public function testExecuteException() { + $this->expectException(); + $this->db->read('SELECT deliberate syntax error'); + } +} + +?> \ No newline at end of file diff --git a/tests/integration/extensions/data/SourceTest.php b/tests/integration/extensions/data/SourceTest.php new file mode 100644 index 0000000..d5edb41 --- /dev/null +++ b/tests/integration/extensions/data/SourceTest.php @@ -0,0 +1,65 @@ +<?php +/** + * Lithium: the most rad php framework + * + * @copyright Copyright 2010, Union of RAD (http://union-of-rad.org) + * @license http://opensource.org/licenses/bsd-license.php The BSD License + */ + +namespace li3_mssql\tests\integration\extensions\data; + +use \Exception; +use \ArrayAccess; +use \lithium\data\Connections; +use \lithium\tests\integration\data\Company; + +class SourceTest extends \lithium\tests\integration\data\SourceTest { + + /** + * Tests that a single record with a manually specified primary key can be created, persisted + * to an arbitrary data store, re-read and updated. + * + * @return void + */ + public function testSingleReadWriteWithKey() { + Company::invokeMethod('_connection')->invokeMethod('_execute', array( + 'SET IDENTITY_INSERT ' . Company::meta('source') . ' ON') + ); + + $key = Company::meta('key'); + + $new = Company::create(array($key => 12345, 'name' => 'Acme, Inc.')); + + $result = $new->data(); + $expected = array($key => 12345, 'name' => 'Acme, Inc.'); + $this->assertEqual($expected[$key], $result[$key]); + $this->assertEqual($expected['name'], $result['name']); + + $this->assertFalse($new->exists()); + $this->assertTrue($new->save()); + $this->assertTrue($new->exists()); + + $existing = Company::find(12345); + $result = $existing->data(); + $this->assertEqual($expected[$key], $result[$key]); + $this->assertEqual($expected['name'], $result['name']); + $this->assertTrue($existing->exists()); + + $existing->name = 'Big Brother and the Holding Company'; + $this->assertTrue($existing->save()); + + $existing = Company::find(12345); + $result = $existing->data(); + $expected['name'] = 'Big Brother and the Holding Company'; + $this->assertEqual($expected[$key], $result[$key]); + $this->assertEqual($expected['name'], $result['name']); + + $existing->delete(); + + Company::invokeMethod('_connection')->invokeMethod('_execute', array( + 'SET IDENTITY_INSERT ' . Company::meta('source') . ' OFF') + ); + } +} + +?> \ No newline at end of file diff --git a/tests/mocks/extensions/data/source/MockMicrosoftSqlServer.php b/tests/mocks/extensions/data/source/MockMicrosoftSqlServer.php new file mode 100644 index 0000000..2bd3ff8 --- /dev/null +++ b/tests/mocks/extensions/data/source/MockMicrosoftSqlServer.php @@ -0,0 +1,28 @@ +<?php + +namespace li3_mssql\tests\mocks\extensions\data\source; + +class MockMicrosoftSqlServer extends \li3_mssql\extensions\data\source\MicrosoftSqlServer { + public function connect() {} + public function disconnect() {} + public function encoding($encoding = null) {} + public function result($type, $resource, $context) {} + public function error() {} + protected function _execute($sql, $options = array()) {} +// public function read($query, $options) { +// switch (true) { +// case $query === 'SELECT TABLE_NAME FROM [INFORMATION_SCHEMA].[TABLES]': +// return array( +// array('TABLE_NAME' => 'Table1'), +// array('TABLE_NAME' => 'Table2'), +// array('TABLE_NAME' => 'Table3') +// ); +// break; +// case $query: +// break; +// } +// return null; +// } +} + +?> \ No newline at end of file diff --git a/tests/mocks/extensions/data/source/MockSqlSrv.php b/tests/mocks/extensions/data/source/MockSqlSrv.php new file mode 100644 index 0000000..6074ab3 --- /dev/null +++ b/tests/mocks/extensions/data/source/MockSqlSrv.php @@ -0,0 +1,18 @@ +<?php +/** + * Lithium: the most rad php framework + * + * @copyright Copyright 2010, Union of RAD (http://union-of-rad.org) + * @license http://opensource.org/licenses/bsd-license.php The BSD License + */ + +namespace li3_mssql\tests\mocks\extensions\data\source; + +class MockSqlSrv extends \li3_mssql\extensions\data\source\SqlSrv { + + public function get($var) { + return $this->{$var}; + } +} + +?> \ No newline at end of file