oracle fixes
authorFilip Visic <visicfilip@gmail.com>
Sun, 5 May 2013 11:55:34 +0000 (13:55 +0200)
committerPhilipp Schüle <p.schuele@metaways.de>
Tue, 14 May 2013 08:26:24 +0000 (10:26 +0200)
Change-Id: Ida1359b567d4625cfa0e65dc153a94446b711e43
Reviewed-on: https://gerrit.tine20.org/tine20/1978
Tested-by: jenkins user
Reviewed-by: Philipp Schüle <p.schuele@metaways.de>
tine20/Admin/js/customfield/EditDialog.js
tine20/Tinebase/Autoloader.php
tine20/Tinebase/Backend/Sql/Abstract.php
tine20/Tinebase/Backend/Sql/Filter/FilterGroup.php
tine20/Tinebase/Core.php
tine20/Tinebase/Model/Filter/CustomField.php
tine20/Tinebase/Model/Filter/Id.php
tine20/Tinebase/Model/Filter/Query.php
tine20/Tinebase/Model/Filter/Relation.php
tine20/Zend/Db/Adapter/Oracle.php
tine20/Zend/Db/Table/Abstract.php

index 8dcb6da..0b4e837 100644 (file)
@@ -512,7 +512,7 @@ Tine.Admin.CustomfieldEditDialog = Ext.extend(Tine.widgets.dialog.EditDialog, {
             layoutConfig: {
                 align: 'stretch',
                 pack: 'start'
-            },
+            }, 
             border: false,
             items: [{
                 xtype: 'columnform',
index 889020c..661f3c3 100644 (file)
@@ -51,7 +51,7 @@ class Tinebase_Autoloader implements Zend_Loader_Autoloader_Interface
         
         include_once $fullPath;
         
-        if (!class_exists($class, false) && !interface_exists($class, false)) {
+        if (! class_exists($class, false) && !interface_exists($class, false)) {
             throw new Zend_Exception("File \"$fullPath\" does not exist or class \"$class\" was not found in the file");
         }
     }
index 8838400..2b4c09f 100644 (file)
@@ -341,15 +341,18 @@ abstract class Tinebase_Backend_Sql_Abstract extends Tinebase_Backend_Abstract i
     public function getMultipleByProperty($_value, $_property='name', $_getDeleted = FALSE, $_orderBy = NULL, $_orderDirection = 'ASC')
     {
         $columnName = $this->_db->quoteIdentifier($this->_tableName . '.' . $_property);
-        $value = empty($_value) ? array('') : (array)$_value;
-        $orderBy = $this->_tableName . '.' . ($_orderBy ? $_orderBy : $_property);
-        
-        $select = $this->_getSelect('*', $_getDeleted)
-                       ->where($columnName . 'IN (?)', $value)
-                       ->order($orderBy . ' ' . $_orderDirection);
-        
-        Tinebase_Backend_Sql_Abstract::traitGroup($select);
-        
+        if (! empty($_value)) {
+            $value = (array)$_value;
+            $orderBy = $this->_tableName . '.' . ($_orderBy ? $_orderBy : $_property);
+
+            $select = $this->_getSelect('*', $_getDeleted)
+                ->where($columnName . 'IN (?)', $value)
+                ->order($orderBy . ' ' . $_orderDirection);
+
+                Tinebase_Backend_Sql_Abstract::traitGroup($select);
+        } else {
+                $select = $this->_getSelect('*', $_getDeleted)->where('1=0');
+        }
         $stmt = $this->_db->query($select);
         
         $resultSet = $this->_rawDataToRecordSet($stmt->fetchAll());
@@ -561,6 +564,9 @@ abstract class Tinebase_Backend_Sql_Abstract extends Tinebase_Backend_Abstract i
         if (! empty($this->_additionalSearchCountCols)) {
             $result = $this->_db->fetchRow($countSelect);
         } else {
+            $countSelect->reset(Zend_Db_Select::COLUMNS); 
+            $countSelect->reset(Zend_Db_Select::GROUP);
+            $countSelect->columns(array('count' => 'COUNT(' . $defaultCountCol . ')'));
             $result = $this->_db->fetchOne($countSelect);
         }
         
@@ -915,6 +921,8 @@ abstract class Tinebase_Backend_Sql_Abstract extends Tinebase_Backend_Abstract i
         foreach ($_recordArray as $key => $value) {
             if (is_bool($value)) {
                 $_recordArray[$key] = ($value) ? new Zend_Db_Expr('1') : new Zend_Db_Expr('0');
+            } elseif (is_null($value)) {
+                $_recordArray[$key] = new Zend_Db_Expr('NULL');
             } elseif (is_int($value)) {
                 $_recordArray[$key] = new Zend_Db_Expr((string) $value);
             }
@@ -1276,6 +1284,16 @@ abstract class Tinebase_Backend_Sql_Abstract extends Tinebase_Backend_Abstract i
     }
     
     /**
+     * get table identifier
+     * 
+     * @return string
+     */
+    public function getIdentifier()
+    {
+        return $this->_identifier;
+    }
+    
+    /**
      * get db adapter
      *
      * @return Zend_Db_Adapter_Abstract
index 0bb6ada..d68e3d9 100644 (file)
@@ -30,11 +30,12 @@ class Tinebase_Backend_Sql_Filter_FilterGroup
      * @param  Zend_Db_Select                    $_select
      * @param  Tinebase_Model_Filter_FilterGroup $_filters
      * @param  Tinebase_Backend_Sql_Abstract     $_backend
+     * @param  boolean                           $_appendFilterSql
      */
-    public static function appendFilters($_select, $_filters, $_backend)
+    public static function appendFilters($_select, $_filters, $_backend, $_appendFilterSql = TRUE)
     {
         // support for direct sql filter append in derived filter groups
-        if (method_exists($_filters, 'appendFilterSql')) {
+        if ($_appendFilterSql && method_exists($_filters, 'appendFilterSql')) {
             $_filters->appendFilterSql($_select, $_backend);
         }
         
index f3b0cfc..9edb96b 100644 (file)
@@ -302,10 +302,16 @@ class Tinebase_Core
      */
     public static function getApplicationInstance($_applicationName, $_modelName = '', $_ignoreACL = FALSE)
     {
-        if (strpos($_applicationName, '_')) {
+        // modified (some model names can have both . and _ in their names and we should treat them as JS model name
+        if (strpos($_applicationName, '_') && ! strpos($_applicationName, '.')) {
             // got (complete) model name name as first param
             list($appName, $i, $modelName) = explode('_', $_applicationName, 3);
-        } else {
+        } 
+        else if (strpos($_applicationName, '.')) {
+            // got (complete) model name name as first param (JS style)
+            list($j, $appName, $i, $modelName) = explode('.', $_applicationName, 4);
+        }
+        else {
             $appName = $_applicationName;
             $modelName = $_modelName;
         }
@@ -321,6 +327,7 @@ class Tinebase_Core
             $modelName = preg_replace('/^' . $appName . '_' . 'Model_/', '', $modelName);
             $controllerNameModel = $controllerName . '_' . $modelName;
             if (! class_exists($controllerNameModel)) {
+                throw new Tinebase_Exception_NotFound('No Application Controller found (checked class ' . $controllerNameModel . ')!');
                 // check for generic app controller
                 if (! class_exists($controllerName)) {
                     throw new Tinebase_Exception_NotFound('No Controller found (checked classes '. $controllerName . ' and ' . $controllerNameModel . ')!');
@@ -957,10 +964,12 @@ class Tinebase_Core
             if ((bool) $config->queryProfiles) {
                 $data['queryProfiles'] = array();
                 foreach($profiler->getQueryProfiles() as $profile) {
-                    $data['queryProfiles'][] = array(
-                        'query'       => $profile->getQuery(),
-                        'elapsedSecs' => $profile->getElapsedSecs(),
-                    );
+                    if ((bool) $config->queryProfilesDetails) {
+                        $data['queryProfiles'][] = array(
+                            'query'       => $profile->getQuery(),
+                            'elapsedSecs' => $profile->getElapsedSecs(),
+                        );
+                    }
                     
                     if ($profile->getElapsedSecs() > $data['longestTime']) {
                         $data['longestTime']  = $profile->getElapsedSecs();
index 905632b..2818e4f 100644 (file)
@@ -72,11 +72,12 @@ class Tinebase_Model_Filter_CustomField extends Tinebase_Model_Filter_Abstract
             $valueFilter = new Tinebase_Model_Filter_Date(array('field' => 'value', 'operator' => $_fieldOrData['operator'], 'value' => $_fieldOrData['value']['value']));
             $this->_subFilter->addFilter($valueFilter);
         } elseif ($type == 'integer') {
-                   $valueFilter = new Tinebase_Model_Filter_Int($_fieldOrData, $_operator, $_value, $_options);
+            $valueFilter = new Tinebase_Model_Filter_Int($_fieldOrData, $_operator, $_value, $_options);
         } else {
             $valueFilter = new Tinebase_Model_Filter_Text($_fieldOrData, $_operator, $_value, $_options);
         }
         
+        $this->_valueFilter = $valueFilter;
         $this->_operators = $valueFilter->getOperators();
         $this->_opSqlMap = $valueFilter->getOpSqlMap();
         
@@ -114,7 +115,7 @@ class Tinebase_Model_Filter_CustomField extends Tinebase_Model_Filter_Abstract
         }
         
         // make sure $correlationName is a string
-        $correlationName = Tinebase_Record_Abstract::generateUID() . $this->_value['cfId'] . 'cf';
+        $correlationName = Tinebase_Record_Abstract::generateUID(5) . $this->_value['cfId'] . 'cf';
         // use only last 30 chars (oracle column name limit)
         $correlationName = substr($correlationName, -30);
         
index e0d5aac..cb1ed6b 100644 (file)
@@ -57,9 +57,9 @@ class Tinebase_Model_Filter_Id extends Tinebase_Model_Filter_Abstract
          // quote field identifier
          $field = $this->_getQuotedFieldName($_backend);
          
-         if (empty($this->_value)) {
+         if (empty($this->_value) && $this->_value != '0') {
              // prevent sql error
-             if ($this->_operator == 'in') {
+             if ($this->_operator == 'in' || $this->_operator == 'equals') {
                  if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ 
                      . ' Empty value with "in" operator (model: ' 
                      . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options'). ')');
index 54d1637..551e2bb 100644 (file)
@@ -31,6 +31,8 @@ class Tinebase_Model_Filter_Query extends Tinebase_Model_Filter_Abstract
     protected $_operators = array(
         0 => 'contains',
         1 => 'in',
+        2 => 'equals',
+        3 => 'startswith'
     );
     
     /**
@@ -65,6 +67,8 @@ class Tinebase_Model_Filter_Query extends Tinebase_Model_Filter_Abstract
          
          switch ($this->_operator) {
              case 'contains':
+             case 'equals':
+             case 'startswith':
                  $queries = explode(' ', $this->_value);
                  foreach ($queries as $query) {
                      $whereParts = array();
@@ -78,7 +82,15 @@ class Tinebase_Model_Filter_Query extends Tinebase_Model_Filter_Abstract
                      }
                       
                      if (!empty($whereClause)) {
-                         $_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%'));
+                         if ($this->_operator == 'equals') {
+                             $_select->where($db->quoteInto($whereClause, trim($query)));
+                         }
+                         else if ($this->_operator == 'startswith') {
+                             $_select->where($db->quoteInto($whereClause, trim($query) . '%'));
+                         }
+                         else {
+                             $_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%'));
+                         }
                      }
                  }
                  break;
index a750d13..c4e1aae 100644 (file)
@@ -93,7 +93,11 @@ class Tinebase_Model_Filter_Relation extends Tinebase_Model_Filter_ForeignRecord
         $idField = array_key_exists('idProperty', $this->_options) ? $this->_options['idProperty'] : 'id';
         $db = $_backend->getAdapter();
         $qField = $db->quoteIdentifier($_backend->getTableName() . '.' . $idField);
-        $_select->where($db->quoteInto("$qField IN (?)", empty($ownIds) ? ' ' : $ownIds));
+        if (empty($ownIds)) {
+            $_select->where('1=0');
+        } else {
+            $_select->where($db->quoteInto("$qField IN (?)", $ownIds));
+        }
     }
     
     /**
index e5ab928..3b56396 100644 (file)
@@ -130,20 +130,19 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
 
         // if set host, port and dbname create string connection or use tnsnames.ora name or string connection
         if (! empty($this->_config['host']) && ! empty($this->_config['port']) && ! empty($this->_config['dbname'])) {
-            if ($this->_config['isSID']) {
-                $this->_config['dbname']="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=".$this->_config['host'].")(PORT=".$this->_config['port'].")))(CONNECT_DATA=(SID=\"".$this->_config['dbname']."\")))";
+            if (isset($this->_config['isSID']) && $this->_config['isSID']) {
+                $this->_config['dbname'] = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=".$this->_config['host'].")(PORT=".$this->_config['port'].")))(CONNECT_DATA=(SID=\"".$this->_config['dbname']."\")))";
             } else {
-                $this->_config['dbname']="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=".$this->_config['host'].")(PORT=".$this->_config['port'].")))(CONNECT_DATA=(SERVICE_NAME=\"".$this->_config['dbname']."\")))";
+                $this->_config['dbname'] = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=".$this->_config['host'].")(PORT=".$this->_config['port'].")))(CONNECT_DATA=(SERVICE_NAME=\"".$this->_config['dbname']."\")))";
             }
         }
 
         $connectionFuncName = ($this->_config['persistent'] == true) ? 'oci_pconnect' : 'oci_connect';
-        $connectionDatabase = (($this->_config['host'] == 'localhost' || $this->_config['host'] == '127.0.0.1') ? $this->_config['dbname'] : $this->_config['host'].'/'.$this->_config['dbname']);
         
         $this->_connection = @$connectionFuncName(
                 $this->_config['username'],
                 $this->_config['password'],
-                $connectionDatabase,
+                $this->_config['dbname'],
                 $this->_config['charset']);
                 
         // check the connection
@@ -154,12 +153,14 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
             require_once 'Zend/Db/Adapter/Oracle/Exception.php';
             throw new Zend_Db_Adapter_Oracle_Exception(oci_error());
         }
+        $rs = oci_parse($this->_connection, "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD hh24:mi:ss'");
+        oci_execute($rs, OCI_COMMIT_ON_SUCCESS);
         $rs = oci_parse($this->_connection, "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'");
-        oci_execute($rs, OCI_DEFAULT);
-        $rs = oci_parse($this->_connection, "ALTER SESSION SET NLS_SORT=BINARY_CI;");
-        oci_execute($rs, OCI_DEFAULT);
-        $rs = oci_parse($this->_connection, "ALTER SESSION SET NLS_COMP=LINGUISTIC;");
-        oci_execute($rs, OCI_DEFAULT);
+        oci_execute($rs, OCI_COMMIT_ON_SUCCESS);
+//        $rs = oci_parse($this->_connection, "ALTER SESSION SET NLS_SORT=BINARY_CI;");
+//        oci_execute($rs, OCI_COMMIT_ON_SUCCESS);
+//        $rs = oci_parse($this->_connection, "ALTER SESSION SET NLS_COMP=LINGUISTIC;");
+//        oci_execute($rs, OCI_COMMIT_ON_SUCCESS);
 
     }
 
@@ -666,7 +667,13 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
                 $vals[] = $val->__toString();
                 unset($bind[$col]);
             } else {
+
+                if ($val == date('Y-m-d H:i:s',strtotime($val))) {
+                $vals[] = "TO_DATE(".':'.$col.$i.",'YYYY-MM-DD hh24:mi:ss')";
+            } else {
                 $vals[] = ':'.$col.$i;
+            }
+                
                 unset($bind[$col]);
                 $bind[':'.$col.$i] = $val;
             }
@@ -684,6 +691,77 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
         $result = $stmt->rowCount();
         return $result;
     }
+    
+    /**
+     * Updates table rows with specified data based on a WHERE clause.
+     *
+     * @param  mixed        $table The table to update.
+     * @param  array        $bind  Column-value pairs.
+     * @param  mixed        $where UPDATE WHERE clause(s).
+     * @return int          The number of affected rows.
+     */
+    public function update($table, array $bind, $where = '')
+    {
+        /**
+         * Build "col = ?" pairs for the statement,
+         * except for Zend_Db_Expr which is treated literally.
+         */
+        $set = array();
+        $i = 0;
+        foreach ($bind as $col => $val) {
+            if ($val instanceof Zend_Db_Expr) {
+                $val = $val->__toString();
+                unset($bind[$col]);
+            } else {
+                if ($this->supportsParameters('positional')) {
+                    // MOD: add to_date for date column
+                    if ($val == date('Y-m-d H:i:s',strtotime($val))) {
+                        $val = "TO_DATE(".'?'.",'YYYY-MM-DD hh24:mi:ss')";
+                    } else {
+                        $val = '?';
+                    }
+                } else {
+                    if ($this->supportsParameters('named')) {
+                        unset($bind[$col]);
+                        $bind[':'.$col.$i] = $val;
+                        // MOD: add to_date for date column
+                        if ($val == date('Y-m-d H:i:s',strtotime($val))) {
+                            $val = "TO_DATE(".':'.$col.$i.",'YYYY-MM-DD hh24:mi:ss')";
+                        } else {
+                            $val = ':'.$col.$i;
+                        }
+                        $i++;
+                    } else {
+                        /** @see Zend_Db_Adapter_Exception */
+                        require_once 'Zend/Db/Adapter/Exception.php';
+                        throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
+                    }
+                }
+            }
+            $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
+        }
+
+        $where = $this->_whereExpr($where);
+
+        /**
+         * Build the UPDATE statement
+         */
+        $sql = "UPDATE "
+             . $this->quoteIdentifier($table, true)
+             . ' SET ' . implode(', ', $set)
+             . (($where) ? " WHERE $where" : '');
+
+        /**
+         * Execute the statement and return the number of affected rows
+         */
+        if ($this->supportsParameters('positional')) {
+            $stmt = $this->query($sql, array_values($bind));
+        } else {
+            $stmt = $this->query($sql, $bind);
+        }
+        $result = $stmt->rowCount();
+        return $result;
+    }
 
     /**
      * Check if the adapter supports real SQL parameters.
@@ -724,8 +802,6 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
         }
     }
 
-    
-    
     ############################ Override methods - not included in default Zend Framework class #################
     
 
@@ -792,7 +868,7 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
      * @param  mixed  $bind An array of data to bind to the placeholders.
      * @return array
      */
-    protected function _positionalToNamedParameters($sql, $bind)
+    protected function _positionalToNamedParameters($sql, array $bind)
     {
         if ($sql instanceof Zend_Db_Select) {
             if (empty($bind)) {
@@ -804,25 +880,23 @@ class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
         
         $keyCounter = 0;
         $quotedQuestionMarksCounter = 0;
-        if (is_array($bind)) {
-            foreach ($bind as $key => $value)
-            {
-                if (is_int($key)) {
-                    unset($bind[$key]);
-                    $bind[$this->_namedParamPrefix . $keyCounter] = $value;
-
-                    $position = 0;
-                    while (false !== ($position = strpos($sql, '?', $quotedQuestionMarksCounter))) {
-                        if ($this->_isQuoted($sql, $position)) {
-                            $quotedQuestionMarksCounter++;
-                        } else {
-                            break;
-                        }
+        foreach ($bind as $key => $value)
+        {
+            if (is_int($key)) {
+                unset($bind[$key]);
+                $bind[$this->_namedParamPrefix . $keyCounter] = $value;
+                
+                $position = 0;
+                while (false !== ($position = strpos($sql, '?', $quotedQuestionMarksCounter))) {
+                    if ($this->_isQuoted($sql, $position)) {
+                        $quotedQuestionMarksCounter++;
+                    } else {
+                        break;
                     }
-                    $sql = substr_replace($sql, ':' . $this->_namedParamPrefix . $keyCounter, $position, 1);
-
-                    $keyCounter++;
                 }
+                $sql = substr_replace($sql, ':' . $this->_namedParamPrefix . $keyCounter, $position, 1);
+                
+                $keyCounter++;
             }
         }
 
index c24cb0a..2f845b5 100644 (file)
@@ -875,7 +875,8 @@ abstract class Zend_Db_Table_Abstract
                     throw new Zend_Db_Table_Exception('A table must have a primary key, but none was found');
                 }
             } else {
-                Tinebase_Core::getLogger()->warn(__METHOD__.'::'.__LINE__."No table found");
+                Tinebase_Core::getLogger()->warn(__METHOD__.'::'.__LINE__." No table found");
+                return false;
             }
         } else if (!is_array($this->_primary)) {
             $this->_primary = array(1 => $this->_primary);