From 9b3e7b5f2d4ec4feae793ef90454506addbb19e1 Mon Sep 17 00:00:00 2001 From: Derek Allard Date: Mon, 4 Feb 2008 23:20:34 +0000 Subject: Added and documented Active Record caching. Made AR fully database-prefix aware --- system/database/DB_active_rec.php | 313 ++++++++++++++++++--- system/database/drivers/mssql/mssql_driver.php | 7 + system/database/drivers/mysql/mysql_driver.php | 7 + system/database/drivers/mysqli/mysqli_driver.php | 7 + system/database/drivers/oci8/oci8_driver.php | 7 + system/database/drivers/odbc/odbc_driver.php | 7 + system/database/drivers/postgre/postgre_driver.php | 9 +- system/database/drivers/sqlite/sqlite_driver.php | 7 + user_guide/changelog.html | 18 +- user_guide/database/active_record.html | 39 ++- 10 files changed, 373 insertions(+), 48 deletions(-) diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php index e93b99e3d..b3bf02a8b 100644 --- a/system/database/DB_active_rec.php +++ b/system/database/DB_active_rec.php @@ -43,6 +43,23 @@ class CI_DB_active_record extends CI_DB_driver { var $ar_set = array(); var $ar_wherein = array(); var $ar_aliased_tables = array(); + var $ar_store_array = array(); + + // Active Record Caching variables + var $ar_caching = FALSE; + var $ar_cache_select = array(); + var $ar_cache_from = array(); + var $ar_cache_join = array(); + var $ar_cache_where = array(); + var $ar_cache_like = array(); + var $ar_cache_groupby = array(); + var $ar_cache_having = array(); + var $ar_cache_limit = FALSE; + var $ar_cache_offset = FALSE; + var $ar_cache_order = FALSE; + var $ar_cache_orderby = array(); + var $ar_cache_set = array(); + /** * DB Prefix @@ -87,12 +104,23 @@ class CI_DB_active_record extends CI_DB_driver { if ($val != '*' && $protect_identifiers !== FALSE) { - $val = $this->_protect_identifiers($val); + if (strpos($val, '.') !== FALSE) + { + $val = $this->dbprefix.$val; + } + else + { + $val = $this->_protect_identifiers($val); + } } if ($val != '') { $this->ar_select[] = $val; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_select[] = $val; + } } } return $this; @@ -122,6 +150,10 @@ class CI_DB_active_record extends CI_DB_driver { $sql = 'MAX('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); $this->ar_select[] = $sql; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_select[] = $sql; + } return $this; } @@ -150,7 +182,11 @@ class CI_DB_active_record extends CI_DB_driver { $sql = 'MIN('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); $this->ar_select[] = $sql; - + if ($this->ar_caching === TRUE) + { + $this->ar_cache_select[] = $sql; + } + return $this; } @@ -178,7 +214,11 @@ class CI_DB_active_record extends CI_DB_driver { $sql = 'AVG('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); $this->ar_select[] = $sql; - + if ($this->ar_caching === TRUE) + { + $this->ar_cache_select[] = $sql; + } + return $this; } @@ -206,7 +246,11 @@ class CI_DB_active_record extends CI_DB_driver { $sql = 'SUM('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); $this->ar_select[] = $sql; - + if ($this->ar_caching === TRUE) + { + $this->ar_cache_select[] = $sql; + } + return $this; } @@ -243,6 +287,10 @@ class CI_DB_active_record extends CI_DB_driver { foreach ((array)$from as $val) { $this->ar_from[] = $this->_protect_identifiers($this->_track_aliases($val)); + if ($this->ar_caching === TRUE) + { + $this->ar_cache_from[] = $this->_protect_identifiers($val); + } } return $this; @@ -289,7 +337,14 @@ class CI_DB_active_record extends CI_DB_driver { $cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond); } - $this->ar_join[] = $type.'JOIN '.$this->_protect_identifiers($this->dbprefix.$table, TRUE).' ON '.$cond; + $join = $type.'JOIN '.$this->_protect_identifiers($this->dbprefix.$table, TRUE).' ON '.$cond; + + $this->ar_join[] = $join; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_join[] = $join; + } + return $this; } @@ -360,7 +415,7 @@ class CI_DB_active_record extends CI_DB_driver { { $key = array($key => $value); } - + foreach ($key as $k => $v) { $prefix = (count($this->ar_where) == 0) ? '' : $type; @@ -393,10 +448,18 @@ class CI_DB_active_record extends CI_DB_driver { } $v = ' '.$this->escape($v); - } - + else + { + $k = $this->_protect_identifiers($k, TRUE); + } + $this->ar_where[] = $prefix.$k.$v; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_where[] = $prefix.$k.$v; + } + } return $this; } @@ -507,7 +570,13 @@ class CI_DB_active_record extends CI_DB_driver { $prefix = (count($this->ar_where) == 0) ? '' : $type; - $this->ar_where[] = $prefix . $this->_protect_identifiers($key) . $not . " IN (" . implode(", ", $this->ar_wherein) . ") "; + $where_in = $prefix . $this->_protect_identifiers($key) . $not . " IN (" . implode(", ", $this->ar_wherein) . ") "; + + $this->ar_where[] = $where_in; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_where[] = $where_in; + } // reset the array for multiple calls $this->ar_wherein = array(); @@ -629,16 +698,23 @@ class CI_DB_active_record extends CI_DB_driver { if ($side == 'before') { - $this->ar_like[] = $prefix." $k $not LIKE '%{$v}'"; + $like_statement = $prefix." $k $not LIKE '%{$v}'"; } elseif ($side == 'after') { - $this->ar_like[] = $prefix." $k $not LIKE '{$v}%'"; + $like_statement = $prefix." $k $not LIKE '{$v}%'"; } else { - $this->ar_like[] = $prefix." $k $not LIKE '%{$v}%'"; + $like_statement = $prefix." $k $not LIKE '%{$v}%'"; } + + $this->ar_like[] = $like_statement; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_like[] = $like_statement; + } + } return $this; } @@ -664,7 +740,13 @@ class CI_DB_active_record extends CI_DB_driver { $val = trim($val); if ($val != '') + { $this->ar_groupby[] = $this->_protect_identifiers($val); + if ($this->ar_caching === TRUE) + { + $this->ar_cache_groupby[] = $this->_protect_identifiers($val); + } + } } return $this; } @@ -724,6 +806,7 @@ class CI_DB_active_record extends CI_DB_driver { * * @access private * @param string + * @param string * @return object */ @@ -737,14 +820,20 @@ class CI_DB_active_record extends CI_DB_driver { foreach ($key as $k => $v) { $prefix = (count($this->ar_having) == 0) ? '' : $type; + $k = $this->_protect_identifiers($k); if ($v != '') { - $v = ' '.$this->escape($v); + $v = ' '.$this->escape_str($v); } $this->ar_having[] = $prefix.$k.$v; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_having[] = $prefix.$k.$v; + } } + return $this; } @@ -770,7 +859,14 @@ class CI_DB_active_record extends CI_DB_driver { $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC'; } - $this->ar_orderby[] = $this->_protect_identifiers($orderby, TRUE).$direction; + $orderby_statement = $this->_protect_identifiers($orderby, TRUE).$direction; + + $this->ar_orderby[] = $orderby_statement; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_orderby[] = $orderby_statement; + } + return $this; } @@ -799,9 +895,19 @@ class CI_DB_active_record extends CI_DB_driver { function limit($value, $offset = '') { $this->ar_limit = $value; - + if ($this->ar_caching === TRUE) + { + $this->ar_cache_limit[] = $value; + } + if ($offset != '') + { $this->ar_offset = $offset; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_offset[] = $offset; + } + } return $this; } @@ -815,9 +921,14 @@ class CI_DB_active_record extends CI_DB_driver { * @param integer the offset value * @return object */ - function offset($value) + function offset($offset) { - $this->ar_offset = $value; + $this->ar_offset = $offset; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_offset[] = $offset; + } + return $this; } @@ -846,12 +957,19 @@ class CI_DB_active_record extends CI_DB_driver { if ($escape === FALSE) { $this->ar_set[$this->_protect_identifiers($k)] = $v; + if ($this->ar_caching === TRUE) + { + $this->ar_cache_offset[$this->_protect_identifiers($k)] = $v; + } } else { $this->ar_set[$this->_protect_identifiers($k)] = $this->escape($v); + if ($this->ar_caching === TRUE) + { + $this->ar_cache_offset[$this->_protect_identifiers($k)] = $this->escape($v); + } } - } return $this; @@ -1331,6 +1449,11 @@ class CI_DB_active_record extends CI_DB_driver { */ function _compile_select($select_override = FALSE) { + if ($this->ar_caching === TRUE) + { + $this->_merge_cache(); + } + $sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT '; $sql .= (count($this->ar_select) == 0) ? '*' : implode(', ', $this->ar_select); @@ -1455,6 +1578,107 @@ class CI_DB_active_record extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * Start Cache + * + * Starts AR caching + * + * @access public + * @return void + */ + function start_cache() + { + $this->ar_caching = TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Stop Cache + * + * Stops AR caching + * + * @access public + * @return void + */ + function stop_cache() + { + $this->ar_caching = FALSE; + } + + + // -------------------------------------------------------------------- + + /** + * Flush Cache + * + * Empties the AR cache + * + * @access public + * @return void + */ + function flush_cache() + { + $ar_reset_items = array( + 'ar_cache_select' => array(), + 'ar_cache_from' => array(), + 'ar_cache_join' => array(), + 'ar_cache_where' => array(), + 'ar_cache_like' => array(), + 'ar_cache_groupby' => array(), + 'ar_cache_having' =>array(), + 'ar_cache_orderby' => array(), + 'ar_cache_set' => array() + ); + + $this->_reset_run($ar_reset_items); + } + + // -------------------------------------------------------------------- + + /** + * Merge Cache + * + * When called, this function merges any cached AR arrays with + * locally called ones. + * + * @access private + * @return void + */ + function _merge_cache() + { + $ar_items = array('select', 'from', 'join', 'where', 'like', 'groupby', 'having', 'orderby', 'set'); + + foreach ($ar_items as $ar_item) + { + $ar_cache_item = 'ar_cache_'.$ar_item; + $ar_item = 'ar_'.$ar_item; + $this->$ar_item = array_unique(array_merge($this->$ar_item, $this->$ar_cache_item)); + } + } + + // -------------------------------------------------------------------- + + /** + * Resets the active record values. Called by the get() function + * + * @access private + * @param array An array of fields to reset + * @return void + */ + function _reset_run($ar_reset_items) + { + foreach ($ar_reset_items as $item => $default_value) + { + if (!in_array($item, $this->ar_store_array)) + { + $this->$item = $default_value; + } + } + } + + // -------------------------------------------------------------------- + /** * Resets the active record values. Called by the get() function * @@ -1463,20 +1687,24 @@ class CI_DB_active_record extends CI_DB_driver { */ function _reset_select() { - $this->ar_select = array(); - $this->ar_distinct = FALSE; - $this->ar_from = array(); - $this->ar_join = array(); - $this->ar_where = array(); - $this->ar_like = array(); - $this->ar_groupby = array(); - $this->ar_having = array(); - $this->ar_limit = FALSE; - $this->ar_offset = FALSE; - $this->ar_order = FALSE; - $this->ar_orderby = array(); - $this->ar_wherein = array(); - $this->ar_aliased_tables = array(); + $ar_reset_items = array( + 'ar_select' => array(), + 'ar_from' => array(), + 'ar_join' => array(), + 'ar_where' => array(), + 'ar_like' => array(), + 'ar_groupby' => array(), + 'ar_having' => array(), + 'ar_orderby' => array(), + 'ar_wherein' => array(), + 'ar_aliased_tables' => array(), + 'ar_distinct' => FALSE, + 'ar_limit' => FALSE, + 'ar_offset' => FALSE, + 'ar_order' => FALSE, + ); + + $this->_reset_run($ar_reset_items); } // -------------------------------------------------------------------- @@ -1490,16 +1718,19 @@ class CI_DB_active_record extends CI_DB_driver { * @return void */ function _reset_write() - { - $this->ar_set = array(); - $this->ar_from = array(); - $this->ar_where = array(); - $this->ar_like = array(); - $this->ar_limit = FALSE; - $this->ar_order = FALSE; - $this->ar_orderby = array(); + { + $ar_reset_items = array( + 'ar_set' => array(), + 'ar_from' => array(), + 'ar_where' => array(), + 'ar_like' => array(), + 'ar_orderby' => array(), + 'ar_limit' => FALSE, + 'ar_order' => FALSE + ); + + $this->_reset_run($ar_reset_items); } } - ?> \ No newline at end of file diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php index ad747d4ad..ecd404b52 100644 --- a/system/database/drivers/mssql/mssql_driver.php +++ b/system/database/drivers/mssql/mssql_driver.php @@ -433,6 +433,13 @@ class CI_DB_mssql_driver extends CI_DB { function _protect_identifiers($item, $affect_spaces = TRUE, $first_word_only = FALSE) { // MSSQL doesn't use backticks + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + return $item; } diff --git a/system/database/drivers/mysql/mysql_driver.php b/system/database/drivers/mysql/mysql_driver.php index f00257250..a5082d1b3 100644 --- a/system/database/drivers/mysql/mysql_driver.php +++ b/system/database/drivers/mysql/mysql_driver.php @@ -454,6 +454,13 @@ class CI_DB_mysql_driver extends CI_DB { // we may need "`item1` `item2`" and not "`item1 item2`" if (ctype_alnum($item) === FALSE) { + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + // This function may get "field >= 1", and need it to return "`field` >= 1" $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; diff --git a/system/database/drivers/mysqli/mysqli_driver.php b/system/database/drivers/mysqli/mysqli_driver.php index b2e97f927..9e7cc0c9f 100644 --- a/system/database/drivers/mysqli/mysqli_driver.php +++ b/system/database/drivers/mysqli/mysqli_driver.php @@ -448,6 +448,13 @@ class CI_DB_mysqli_driver extends CI_DB { // we may need "`item1` `item2`" and not "`item1 item2`" if (ctype_alnum($item) === FALSE) { + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + // This function may get "field >= 1", and need it to return "`field` >= 1" $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; diff --git a/system/database/drivers/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php index ddc0fba99..aa2aeca42 100644 --- a/system/database/drivers/oci8/oci8_driver.php +++ b/system/database/drivers/oci8/oci8_driver.php @@ -559,6 +559,13 @@ class CI_DB_oci8_driver extends CI_DB { // we may need "`item1` `item2`" and not "`item1 item2`" if (ctype_alnum($item) === FALSE) { + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + // This function may get "field >= 1", and need it to return "`field` >= 1" $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; diff --git a/system/database/drivers/odbc/odbc_driver.php b/system/database/drivers/odbc/odbc_driver.php index 03c0e6a86..88fff4365 100644 --- a/system/database/drivers/odbc/odbc_driver.php +++ b/system/database/drivers/odbc/odbc_driver.php @@ -424,6 +424,13 @@ class CI_DB_odbc_driver extends CI_DB { // we may need "`item1` `item2`" and not "`item1 item2`" if (ctype_alnum($item) === FALSE) { + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + // This function may get "field >= 1", and need it to return "`field` >= 1" $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php index e72981348..ae8bd86eb 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -444,6 +444,13 @@ class CI_DB_postgre_driver extends CI_DB { // we may need ""item1" "item2"" and not ""item1 item2"" if (ctype_alnum($item) === FALSE) { + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + // This function may get "field >= 1", and need it to return ""field" >= 1" $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; @@ -486,7 +493,7 @@ class CI_DB_postgre_driver extends CI_DB { $tables = array($tables); } - return implode(', ', $tables); + return '('.implode(', ', $tables).')'; } // -------------------------------------------------------------------- diff --git a/system/database/drivers/sqlite/sqlite_driver.php b/system/database/drivers/sqlite/sqlite_driver.php index 38febca29..dad5eeedf 100644 --- a/system/database/drivers/sqlite/sqlite_driver.php +++ b/system/database/drivers/sqlite/sqlite_driver.php @@ -440,6 +440,13 @@ class CI_DB_sqlite_driver extends CI_DB { // we may need "`item1` `item2`" and not "`item1 item2`" if (ctype_alnum($item) === FALSE) { + if (strpos($item, '.') !== FALSE) + { + $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; + $table_name = substr($item, 0, strpos($item, '.')+1); + $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; + } + // This function may get "field >= 1", and need it to return "`field` >= 1" $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; diff --git a/user_guide/changelog.html b/user_guide/changelog.html index 1732e505d..573db864f 100644 --- a/user_guide/changelog.html +++ b/user_guide/changelog.html @@ -62,10 +62,24 @@ Change Log

Release Date: -- still in development

+
  • Active Record + +
    • +
  • Core Changes + +
    • + +

    Bugfixes for 1.6.1

    +

    Version 1.6.0

    Release Date: January 30, 2008

    @@ -698,8 +699,8 @@ $this->db->truncate('mytable');
    // TRUNCATE mytable

    Note: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table".

    -  -

    Method Chaining

    + +

     Method Chaining

    Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:

    @@ -710,8 +711,38 @@ $query = $this->db->get();

    Note: Method chaining only works with PHP 5.

    - - +

     

    + +

     Active Record Caching

    +

    While not "true" caching, Active Record enables you to save (or "cache") certain parts of your queries for reuse later. Normally, when an Active Record call is completed, all stored information is reset for the next call. With caching, you can prevent this reset, and reuse information easily.

    +

    Cached calls are cumulative.  If you makes 2 cached select() calls, and then 2 uncached select() calls, this will result in 4 select() calls. There are three Caching functions available:

    +

    $this->db->start_cache()

    +

    This function must be called to begin caching. All Active Record queries of the correct type (see below for supported queries) are stored for later use.

    +

    $this->db->stop_cache()

    +

    This function can be called to stop caching.

    +

    $this->db->flush_cache()

    +

    This function deletes all items from the Active Record cache.

    +

    Here's a usage example:

    +

    $this->db->start_cache();
    + $this->db->select('field1');
    + $this->db->stop_cache();
    + $this->db->get('tablename');
    + // Results in:
    + // SELECT `field1` FROM (`tablename`)
    +
    + this->db->select('field2');
    + $this->db->get('tablename');
    + // Results in:
    + // SELECT `field1`, `field2` FROM (`tablename`)
    +
    + $this->db->flush_cache();
    +
    + this->db->select('field2');
    + $this->db->get('tablename');
    + // Results in:
    + // SELECT `field2` FROM (`tablename`)

    +

    Note: The following fields can be cached: ‘select’, ‘from’, ‘join’, ‘where’, ‘like’, ‘groupby’, ‘having’, ‘orderby’, ‘set’

    +

     

    -- cgit v1.2.3-24-g4f1b