From 27f798b9d64025fecaaecbd80a8cba41d455940f Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 20 Jan 2014 18:19:13 +0200 Subject: Add support for optional table attributes to CI_DB_forge::create_table() Supersedes PRs #989, #2776 Related issue: #41 --- system/database/DB_forge.php | 36 +++++++++++++++++++--- system/database/drivers/mysql/mysql_forge.php | 30 ++++++++++++++---- system/database/drivers/mysqli/mysqli_forge.php | 30 ++++++++++++++---- .../drivers/pdo/subdrivers/pdo_mysql_forge.php | 30 ++++++++++++++---- 4 files changed, 103 insertions(+), 23 deletions(-) (limited to 'system') diff --git a/system/database/DB_forge.php b/system/database/DB_forge.php index 1cebb189c..fc4a9230f 100644 --- a/system/database/DB_forge.php +++ b/system/database/DB_forge.php @@ -314,9 +314,10 @@ abstract class CI_DB_forge { * * @param string $table Table name * @param bool $if_not_exists Whether to add IF NOT EXISTS condition + * @param array $attributes Associative array of table attributes * @return bool */ - public function create_table($table = '', $if_not_exists = FALSE) + public function create_table($table = '', $if_not_exists = FALSE, array $attributes = array()) { if ($table === '') { @@ -332,7 +333,7 @@ abstract class CI_DB_forge { show_error('Field information is required.'); } - $sql = $this->_create_table($table, $if_not_exists); + $sql = $this->_create_table($table, $if_not_exists, $attributes); if (is_bool($sql)) { @@ -368,9 +369,10 @@ abstract class CI_DB_forge { * * @param string $table Table name * @param bool $if_not_exists Whether to add 'IF NOT EXISTS' condition + * @param array $attributes Associative array of table attributes * @return mixed */ - protected function _create_table($table, $if_not_exists) + protected function _create_table($table, $if_not_exists, $attributes) { if ($if_not_exists === TRUE && $this->_create_table_if === FALSE) { @@ -406,10 +408,11 @@ abstract class CI_DB_forge { } // _create_table will usually have the following format: "%s %s (%s\n)" - $sql = sprintf($this->_create_table.';', + $sql = sprintf($this->_create_table.'%s;', $sql, $this->db->escape_identifiers($table), - $columns + $columns, + $this->_create_table_attr($attributes) ); return $sql; @@ -417,6 +420,29 @@ abstract class CI_DB_forge { // -------------------------------------------------------------------- + /** + * CREATE TABLE attributes + * + * @param array $attributes Associative array of table attributes + * @return string + */ + protected function _create_table_attr($attributes) + { + $sql = ''; + + foreach (array_keys($attributes) as $key) + { + if (is_string($key)) + { + $sql .= ' '.strtoupper($key).' '.$attributes[$key]; + } + } + + return $sql; + } + + // -------------------------------------------------------------------- + /** * Drop Table * diff --git a/system/database/drivers/mysql/mysql_forge.php b/system/database/drivers/mysql/mysql_forge.php index e251c0ea6..3b3cbdee0 100644 --- a/system/database/drivers/mysql/mysql_forge.php +++ b/system/database/drivers/mysql/mysql_forge.php @@ -82,16 +82,34 @@ class CI_DB_mysql_forge extends CI_DB_forge { // -------------------------------------------------------------------- /** - * Class constructor + * CREATE TABLE attributes * - * @param object &$db Database object - * @return void + * @param array $attributes Associative array of table attributes + * @return string */ - public function __construct(&$db) + protected function _create_table_attr($attributes) { - parent::__construct($db); + $sql = ''; + + foreach (array_keys($attributes) as $key) + { + if (is_string($key)) + { + $sql .= ' '.strtoupper($key).' = '.$attributes[$key]; + } + } - $this->_create_table .= ' DEFAULT CHARSET '.$this->db->char_set.' COLLATE '.$this->db->dbcollat; + if ( ! empty($this->db->char_set) && ! strpos($sql, 'CHARACTER SET') && ! strpos($sql, 'CHARSET')) + { + $sql .= ' DEFAULT CHARACTER SET = '.$this->db->char_set; + } + + if ( ! empty($this->db->dbcollat) && ! strpos($sql, 'COLLATE')) + { + $sql .= ' COLLATE = '.$this->db->dbcollat; + } + + return $sql; } // -------------------------------------------------------------------- diff --git a/system/database/drivers/mysqli/mysqli_forge.php b/system/database/drivers/mysqli/mysqli_forge.php index d1e5e20ff..1a568ccd9 100644 --- a/system/database/drivers/mysqli/mysqli_forge.php +++ b/system/database/drivers/mysqli/mysqli_forge.php @@ -82,16 +82,34 @@ class CI_DB_mysqli_forge extends CI_DB_forge { // -------------------------------------------------------------------- /** - * Class constructor + * CREATE TABLE attributes * - * @param object &$db Database object - * @return void + * @param array $attributes Associative array of table attributes + * @return string */ - public function __construct(&$db) + protected function _create_table_attr($attributes) { - parent::__construct($db); + $sql = ''; + + foreach (array_keys($attributes) as $key) + { + if (is_string($key)) + { + $sql .= ' '.strtoupper($key).' = '.$attributes[$key]; + } + } - $this->_create_table .= ' DEFAULT CHARSET '.$this->db->char_set.' COLLATE '.$this->db->dbcollat; + if ( ! empty($this->db->char_set) && ! strpos($sql, 'CHARACTER SET') && ! strpos($sql, 'CHARSET')) + { + $sql .= ' DEFAULT CHARACTER SET = '.$this->db->char_set; + } + + if ( ! empty($this->db->dbcollat) && ! strpos($sql, 'COLLATE')) + { + $sql .= ' COLLATE = '.$this->db->dbcollat; + } + + return $sql; } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_mysql_forge.php b/system/database/drivers/pdo/subdrivers/pdo_mysql_forge.php index 74689d91e..3ac98e6c3 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_mysql_forge.php +++ b/system/database/drivers/pdo/subdrivers/pdo_mysql_forge.php @@ -96,16 +96,34 @@ class CI_DB_pdo_mysql_forge extends CI_DB_pdo_forge { // -------------------------------------------------------------------- /** - * Class constructor + * CREATE TABLE attributes * - * @param object &$db Database object - * @return void + * @param array $attributes Associative array of table attributes + * @return string */ - public function __construct(&$db) + protected function _create_table_attr($attributes) { - parent::__construct($db); + $sql = ''; + + foreach (array_keys($attributes) as $key) + { + if (is_string($key)) + { + $sql .= ' '.strtoupper($key).' = '.$attributes[$key]; + } + } - $this->_create_table .= ' DEFAULT CHARSET '.$this->db->char_set.' COLLATE '.$this->db->dbcollat; + if ( ! empty($this->db->char_set) && ! strpos($sql, 'CHARACTER SET') && ! strpos($sql, 'CHARSET')) + { + $sql .= ' DEFAULT CHARACTER SET = '.$this->db->char_set; + } + + if ( ! empty($this->db->dbcollat) && ! strpos($sql, 'COLLATE')) + { + $sql .= ' COLLATE = '.$this->db->dbcollat; + } + + return $sql; } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From ba8bf563095657b8b6104ecc3d3a990f3e6ceb75 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 21 Jan 2014 19:04:18 +0200 Subject: SQLSRV improvements Mainly for performance (issue #2474), but also added a 'scrollable' configuration flag and auto-detection for SQLSRV_CURSOR_CLIENT_BUFFERED (only available since SQLSRV 3). --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 37 ++++++++++++++++++++++-- system/database/drivers/sqlsrv/sqlsrv_result.php | 32 +++++++++++++++++++- 2 files changed, 66 insertions(+), 3 deletions(-) (limited to 'system') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 09e6b8c9a..2759bac0b 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -48,6 +48,18 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ public $dbdriver = 'sqlsrv'; + /** + * Scrollable flag + * + * Determines what cursor type to use when executing queries. + * + * FALSE or SQLSRV_CURSOR_FORWARD would increase performance, + * but would disable num_rows() (and possibly insert_id()) + * + * @var mixed + */ + public $scrollable; + // -------------------------------------------------------------------- /** @@ -69,6 +81,27 @@ class CI_DB_sqlsrv_driver extends CI_DB { // -------------------------------------------------------------------- + /** + * Class constructor + * + * @param array $params + * @return void + */ + public function __construct($params) + { + parent::__construct($params); + + // This is only supported as of SQLSRV 3.0 + if ($this->scrollable === NULL) + { + $this->scrollable = defined('SQLSRV_CURSOR_CLIENT_BUFFERED') + ? SQLSRV_CURSOR_CLIENT_BUFFERED + : FALSE; + } + } + + // -------------------------------------------------------------------- + /** * Database connection * @@ -154,9 +187,9 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _execute($sql) { - return ($this->is_write_type($sql) && stripos($sql, 'INSERT') === FALSE) + return ($this->scrollable === FALSE OR $this->is_write_type($sql)) ? sqlsrv_query($this->conn_id, $sql) - : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => SQLSRV_CURSOR_STATIC)); + : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => $this->scrollable)); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/sqlsrv/sqlsrv_result.php b/system/database/drivers/sqlsrv/sqlsrv_result.php index 3c8148f1b..ba38f7454 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_result.php +++ b/system/database/drivers/sqlsrv/sqlsrv_result.php @@ -38,6 +38,30 @@ defined('BASEPATH') OR exit('No direct script access allowed'); */ class CI_DB_sqlsrv_result extends CI_DB_result { + /** + * Scrollable flag + * + * @var mixed + */ + public $scrollable; + + // -------------------------------------------------------------------- + + /** + * Constructor + * + * @param object $driver_object + * @return void + */ + public function __construct(&$driver_object) + { + parent::__construct($driver_object); + + $this->scrollable = $driver_object->scrollable; + } + + // -------------------------------------------------------------------- + /** * Number of rows in the result set * @@ -45,9 +69,15 @@ class CI_DB_sqlsrv_result extends CI_DB_result { */ public function num_rows() { + // sqlsrv_num_rows() doesn't work with the FORWARD and DYNAMIC cursors (FALSE is the same as FORWARD) + if ( ! in_array($this->scrollable, array(FALSE, SQLSRV_CURSOR_FORWARD, SQLSRV_CURSOR_DYNAMIC), TRUE)) + { + return parent::num_rows(); + } + return is_int($this->num_rows) ? $this->num_rows - : $this->num_rows = @sqlsrv_num_rows($this->result_id); + : $this->num_rows = sqlsrv_num_rows($this->result_id); } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 4356806dc0298363217694d727db9cad84a073e0 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 21 Jan 2014 23:52:31 +0200 Subject: Add