#9452: Enabling Tine 2.0 for working with transaction pooling level of PgBouncer
authorFlávio Gomes da Silva Lisboa <flavio.lisboa@serpro.gov.br>
Mon, 23 Dec 2013 17:59:03 +0000 (15:59 -0200)
committerPhilipp Schüle <p.schuele@metaways.de>
Thu, 26 Dec 2013 20:56:06 +0000 (21:56 +0100)
Change-Id: Ib1b0749a4904562648ae1a78667c356ed27d0cf1
Reviewed-on: https://gerrit.tine20.org/tine20/2685
Tested-by: jenkins user
Reviewed-by: Philipp Schüle <p.schuele@metaways.de>
tine20/Tinebase/Acl/Roles.php
tine20/Tinebase/Backend/Sql/Abstract.php
tine20/Zend/Db/Adapter/Pdo/Pgsql.php [new file with mode: 0644]

index 6dab892..c455bff 100644 (file)
@@ -20,7 +20,7 @@
 class Tinebase_Acl_Roles
 {
     /**
-     * @var Zend_Db_Adapter_Pdo_Mysql
+     * @var Zend_Db_Adapter
      */
     protected $_db;
     
@@ -353,11 +353,19 @@ class Tinebase_Acl_Roles
         }
         $data['creation_time'] = Tinebase_DateTime::now()->get(Tinebase_Record_Abstract::ISO8601LONG);
         
-        $newId = $this->_rolesTable->insert($data);
-        
-        if ($newId === NULL) {
-           $newId = $this->_db->lastSequenceId(substr(SQL_TABLE_PREFIX . 'roles', 0,25) . '_s');
-        }
+        $transactionId = Tinebase_TransactionManager::getInstance()->startTransaction($this->_db);
+        try {
+            $newId = $this->_rolesTable->insert($data);
+            
+            if ($newId === NULL) {
+                $newId = $this->_db->lastSequenceId(substr(SQL_TABLE_PREFIX . 'roles', 0,25) . '_s');
+            }            
+            
+            Tinebase_TransactionManager::getInstance()->commitTransaction($transactionId);
+        } catch(Exception $e){            
+            Tinebase_TransactionManager::getInstance()->rollBack();
+            throw $e;
+        }        
         
         $role = $this->getRoleById($newId);
         return $role;
index cd67645..ad170c4 100644 (file)
@@ -895,61 +895,69 @@ abstract class Tinebase_Backend_Sql_Abstract extends Tinebase_Backend_Abstract i
      */
     public function create(Tinebase_Record_Interface $_record) 
     {
-        $identifier = $_record->getIdProperty();
-        
-        if (!$_record instanceof $this->_modelName) {
-            throw new Tinebase_Exception_InvalidArgument('invalid model type: $_record is instance of "' . get_class($_record) . '". but should be instance of ' . $this->_modelName);
-        }
-        
-        // set uid if record has hash id and id is empty
-        if ($this->_hasHashId() && empty($_record->$identifier)) {
-            $newId = $_record->generateUID();
-            $_record->setId($newId);
-        }
-        
-        $recordArray = $this->_recordToRawData($_record);
-        
-        // unset id if autoincrement & still empty
-        if (empty($_record->$identifier) || $_record->$identifier == 'NULL' ) {
-            unset($recordArray['id']);
-        }
-        
-        $recordArray = array_intersect_key($recordArray, $this->_schema);
-
-        $this->_prepareData($recordArray);
-        if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ 
-            . " Prepared data for INSERT: " . print_r($recordArray, true)
-        );
-        
-        $this->_db->insert($this->_tablePrefix . $this->_tableName, $recordArray);
-        
-        if (!$this->_hasHashId()) {
-            $newId = $this->_db->lastInsertId($this->getTablePrefix() . $this->getTableName(), $identifier);
-            if(!$newId && isset($_record[$identifier])){
-                $newId = $_record[$identifier];
+        $transactionId = Tinebase_TransactionManager::getInstance()->startTransaction($this->_db);
+        try {
+            $identifier = $_record->getIdProperty();
+            
+            if (!$_record instanceof $this->_modelName) {
+                throw new Tinebase_Exception_InvalidArgument('invalid model type: $_record is instance of "' . get_class($_record) . '". but should be instance of ' . $this->_modelName);
             }
-        }
-
-        // if we insert a record without an id, we need to get back one
-        if (empty($_record->$identifier) && $newId == 0) {
-            throw new Tinebase_Exception_UnexpectedValue("Returned record id is 0.");
-        }
-        
-        // if the record had no id set, set the id now
-        if ($_record->$identifier == NULL || $_record->$identifier == 'NULL') {
-            $_record->$identifier = $newId;
-        }
-        
-        // add custom fields
-        if ($_record->has('customfields') && !empty($_record->customfields)) {
-            Tinebase_CustomField::getInstance()->saveRecordCustomFields($_record);
-        }
-        
-        $this->_updateForeignKeys('create', $_record);
-        
-        $result = $this->get($_record->$identifier);
-        
-        $this->_inspectAfterCreate($result, $_record);
+            
+            // set uid if record has hash id and id is empty
+            if ($this->_hasHashId() && empty($_record->$identifier)) {
+                $newId = $_record->generateUID();
+                $_record->setId($newId);
+            }
+            
+            $recordArray = $this->_recordToRawData($_record);
+            
+            // unset id if autoincrement & still empty
+            if (empty($_record->$identifier) || $_record->$identifier == 'NULL' ) {
+                unset($recordArray['id']);
+            }
+            
+            $recordArray = array_intersect_key($recordArray, $this->_schema);
+            
+            $this->_prepareData($recordArray);
+            if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__
+                    . " Prepared data for INSERT: " . print_r($recordArray, true)
+            );
+            
+            $this->_db->insert($this->_tablePrefix . $this->_tableName, $recordArray);
+            
+            if (!$this->_hasHashId()) {
+                $newId = $this->_db->lastInsertId($this->getTablePrefix() . $this->getTableName(), $identifier);
+                if(!$newId && isset($_record[$identifier])){
+                    $newId = $_record[$identifier];
+                }
+            }
+            
+            // if we insert a record without an id, we need to get back one
+            if (empty($_record->$identifier) && $newId == 0) {
+                throw new Tinebase_Exception_UnexpectedValue("Returned record id is 0.");
+            }
+            
+            // if the record had no id set, set the id now
+            if ($_record->$identifier == NULL || $_record->$identifier == 'NULL') {
+                $_record->$identifier = $newId;
+            }
+            
+            // add custom fields
+            if ($_record->has('customfields') && !empty($_record->customfields)) {
+                Tinebase_CustomField::getInstance()->saveRecordCustomFields($_record);
+            }
+            
+            $this->_updateForeignKeys('create', $_record);
+            
+            $result = $this->get($_record->$identifier);
+            
+            $this->_inspectAfterCreate($result, $_record);
+            
+            Tinebase_TransactionManager::getInstance()->commitTransaction($transactionId);
+        } catch(Exception $e) {
+            Tinebase_TransactionManager::getInstance()->rollBack();
+            throw $e;
+        }       
         
         return $result;
     }
diff --git a/tine20/Zend/Db/Adapter/Pdo/Pgsql.php b/tine20/Zend/Db/Adapter/Pdo/Pgsql.php
new file mode 100644 (file)
index 0000000..d347f9c
--- /dev/null
@@ -0,0 +1,329 @@
+<?php
+/**
+ * Zend Framework
+ *
+ * LICENSE
+ *
+ * This source file is subject to the new BSD license that is bundled
+ * with this package in the file LICENSE.txt.
+ * It is also available through the world-wide-web at this URL:
+ * http://framework.zend.com/license/new-bsd
+ * If you did not receive a copy of the license and are unable to
+ * obtain it through the world-wide-web, please send an email
+ * to license@zend.com so we can send you a copy immediately.
+ *
+ * @category   Zend
+ * @package    Zend_Db
+ * @subpackage Adapter
+ * @copyright  Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
+ * @license    http://framework.zend.com/license/new-bsd     New BSD License
+ * @version    $Id: Pgsql.php 10020 2009-08-18 14:34:09Z j.fischer@metaways.de $
+ */
+
+
+/**
+ * @see Zend_Db_Adapter_Pdo_Abstract
+ */
+require_once 'Zend/Db/Adapter/Pdo/Abstract.php';
+
+
+/**
+ * Class for connecting to PostgreSQL databases and performing common operations.
+ *
+ * @category   Zend
+ * @package    Zend_Db
+ * @subpackage Adapter
+ * @copyright  Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
+ * @license    http://framework.zend.com/license/new-bsd     New BSD License
+ */
+class Zend_Db_Adapter_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Abstract
+{
+
+    /**
+     * PDO type.
+     *
+     * @var string
+     */
+    protected $_pdoType = 'pgsql';
+
+    /**
+     * Keys are UPPERCASE SQL datatypes or the constants
+     * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
+     *
+     * Values are:
+     * 0 = 32-bit integer
+     * 1 = 64-bit integer
+     * 2 = float or decimal
+     *
+     * @var array Associative array of datatypes to values 0, 1, or 2.
+     */
+    protected $_numericDataTypes = array(
+        Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,
+        Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
+        Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,
+        'INTEGER'            => Zend_Db::INT_TYPE,
+        'SERIAL'             => Zend_Db::INT_TYPE,
+        'SMALLINT'           => Zend_Db::INT_TYPE,
+        'BIGINT'             => Zend_Db::BIGINT_TYPE,
+        'BIGSERIAL'          => Zend_Db::BIGINT_TYPE,
+        'DECIMAL'            => Zend_Db::FLOAT_TYPE,
+        'DOUBLE PRECISION'   => Zend_Db::FLOAT_TYPE,
+        'NUMERIC'            => Zend_Db::FLOAT_TYPE,
+        'REAL'               => Zend_Db::FLOAT_TYPE
+    );
+
+    /**
+     * Creates a PDO object and connects to the database.
+     *
+     * @return void
+     * @throws Zend_Db_Adapter_Exception
+     */
+    protected function _connect()
+    {
+        if ($this->_connection) {
+            return;
+        }
+
+        parent::_connect();
+
+        /**
+         * Mantis #0009452 - This feature was disable for making possible to enable transaction pooling
+        if (!empty($this->_config['charset'])) {
+            $sql = "SET NAMES '" . $this->_config['charset'] . "'";
+            $this->_connection->exec($sql);
+        }
+        **/
+    }
+
+    /**
+     * Returns a list of the tables in the database.
+     *
+     * @return array
+     */
+    public function listTables()
+    {
+        // @todo use a better query with joins instead of subqueries
+        $sql = "SELECT c.relname AS table_name "
+             . "FROM pg_class c, pg_user u "
+             . "WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
+             . "AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
+             . "AND c.relname !~ '^(pg_|sql_)' "
+             . "UNION "
+             . "SELECT c.relname AS table_name "
+             . "FROM pg_class c "
+             . "WHERE c.relkind = 'r' "
+             . "AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
+             . "AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) "
+             . "AND c.relname !~ '^pg_'";
+
+        return $this->fetchCol($sql);
+    }
+
+    /**
+     * Returns the column descriptions for a table.
+     *
+     * The return value is an associative array keyed by the column name,
+     * as returned by the RDBMS.
+     *
+     * The value of each array element is an associative array
+     * with the following keys:
+     *
+     * SCHEMA_NAME      => string; name of database or schema
+     * TABLE_NAME       => string;
+     * COLUMN_NAME      => string; column name
+     * COLUMN_POSITION  => number; ordinal position of column in table
+     * DATA_TYPE        => string; SQL datatype name of column
+     * DEFAULT          => string; default expression of column, null if none
+     * NULLABLE         => boolean; true if column can have nulls
+     * LENGTH           => number; length of CHAR/VARCHAR
+     * SCALE            => number; scale of NUMERIC/DECIMAL
+     * PRECISION        => number; precision of NUMERIC/DECIMAL
+     * UNSIGNED         => boolean; unsigned property of an integer type
+     * PRIMARY          => boolean; true if column is part of the primary key
+     * PRIMARY_POSITION => integer; position of column in primary key
+     * IDENTITY         => integer; true if column is auto-generated with unique values
+     *
+     * @todo Discover integer unsigned property.
+     *
+     * @param  string $tableName
+     * @param  string $schemaName OPTIONAL
+     * @return array
+     */
+    public function describeTable($tableName, $schemaName = null)
+    {
+        $sql = "SELECT
+                a.attnum,
+                n.nspname,
+                c.relname,
+                a.attname AS colname,
+                t.typname AS type,
+                a.atttypmod,
+                FORMAT_TYPE(a.atttypid, a.atttypmod) AS complete_type,
+                d.adsrc AS default_value,
+                a.attnotnull AS notnull,
+                a.attlen AS length,
+                co.contype,
+                ARRAY_TO_STRING(co.conkey, ',') AS conkey
+            FROM pg_attribute AS a
+                JOIN pg_class AS c ON a.attrelid = c.oid
+                JOIN pg_namespace AS n ON c.relnamespace = n.oid
+                JOIN pg_type AS t ON a.atttypid = t.oid
+                LEFT OUTER JOIN pg_constraint AS co ON (co.conrelid = c.oid
+                    AND a.attnum = ANY(co.conkey) AND co.contype = 'p')
+                LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
+            WHERE a.attnum > 0 AND c.relname = ".$this->quote($tableName);
+        if ($schemaName) {
+            $sql .= " AND n.nspname = ".$this->quote($schemaName);
+        }
+        $sql .= ' ORDER BY a.attnum';
+
+        $stmt = $this->query($sql);
+
+        // Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
+        $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
+
+        $attnum        = 0;
+        $nspname       = 1;
+        $relname       = 2;
+        $colname       = 3;
+        $type          = 4;
+        $atttypemod    = 5;
+        $complete_type = 6;
+        $default_value = 7;
+        $notnull       = 8;
+        $length        = 9;
+        $contype       = 10;
+        $conkey        = 11;
+
+        $desc = array();
+        foreach ($result as $key => $row) {
+            if ($row[$type] == 'varchar') {
+                if (preg_match('/character varying(?:\((\d+)\))?/', $row[$complete_type], $matches)) {
+                    if (isset($matches[1])) {
+                        $row[$length] = $matches[1];
+                    } else {
+                        $row[$length] = null; // unlimited
+                    }
+                }
+            }
+            list($primary, $primaryPosition, $identity) = array(false, null, false);
+            if ($row[$contype] == 'p') {
+                $primary = true;
+                $primaryPosition = array_search($row[$attnum], explode(',', $row[$conkey])) + 1;
+                $identity = (bool) (preg_match('/^nextval/', $row[$default_value]));
+            }
+            $desc[$this->foldCase($row[$colname])] = array(
+                'SCHEMA_NAME'      => $this->foldCase($row[$nspname]),
+                'TABLE_NAME'       => $this->foldCase($row[$relname]),
+                'COLUMN_NAME'      => $this->foldCase($row[$colname]),
+                'COLUMN_POSITION'  => $row[$attnum],
+                'DATA_TYPE'        => $row[$type],
+                'DEFAULT'          => $row[$default_value],
+                'NULLABLE'         => (bool) ($row[$notnull] != 't'),
+                'LENGTH'           => $row[$length],
+                'SCALE'            => null, // @todo
+                'PRECISION'        => null, // @todo
+                'UNSIGNED'         => null, // @todo
+                'PRIMARY'          => $primary,
+                'PRIMARY_POSITION' => $primaryPosition,
+                'IDENTITY'         => $identity
+            );
+        }
+        return $desc;
+    }
+
+
+    /**
+     * Adds an adapter-specific LIMIT clause to the SELECT statement.
+     *
+     * @param string $sql
+     * @param integer $count
+     * @param integer $offset OPTIONAL
+     * @return string
+     */
+    public function limit($sql, $count, $offset = 0)
+    {
+        $count = intval($count);
+        if ($count <= 0) {
+            /**
+             * @see Zend_Db_Adapter_Exception
+             */
+            require_once 'Zend/Db/Adapter/Exception.php';
+            throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
+        }
+
+        $offset = intval($offset);
+        if ($offset < 0) {
+            /**
+             * @see Zend_Db_Adapter_Exception
+             */
+            require_once 'Zend/Db/Adapter/Exception.php';
+            throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
+        }
+
+        $sql .= " LIMIT $count";
+        if ($offset > 0) {
+            $sql .= " OFFSET $offset";
+        }
+
+        return $sql;
+    }
+
+    /**
+     * Return the most recent value from the specified sequence in the database.
+     * This is supported only on RDBMS brands that support sequences
+     * (e.g. Oracle, PostgreSQL, DB2).  Other RDBMS brands return null.
+     *
+     * @param string $sequenceName
+     * @return string
+     */
+    public function lastSequenceId($sequenceName)
+    {
+        $this->_connect();
+        $value = $this->fetchOne("SELECT CURRVAL(".$this->quote($sequenceName).")");
+        return $value;
+    }
+
+    /**
+     * Generate a new value from the specified sequence in the database, and return it.
+     * This is supported only on RDBMS brands that support sequences
+     * (e.g. Oracle, PostgreSQL, DB2).  Other RDBMS brands return null.
+     *
+     * @param string $sequenceName
+     * @return string
+     */
+    public function nextSequenceId($sequenceName)
+    {
+        $this->_connect();
+        $value = $this->fetchOne("SELECT NEXTVAL(".$this->quote($sequenceName).")");
+        return $value;
+    }
+
+    /**
+     * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
+     *
+     * As a convention, on RDBMS brands that support sequences
+     * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
+     * from the arguments and returns the last id generated by that sequence.
+     * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
+     * returns the last value generated for such a column, and the table name
+     * argument is disregarded.
+     *
+     * @param string $tableName   OPTIONAL Name of table.
+     * @param string $primaryKey  OPTIONAL Name of primary key column.
+     * @return string
+     */
+    public function lastInsertId($tableName = null, $primaryKey = null)
+    {
+        if ($tableName !== null) {
+            $sequenceName = $tableName;
+            if ($primaryKey) {
+                $sequenceName .= "_$primaryKey";
+            }
+            $sequenceName .= '_seq';
+            return $this->lastSequenceId($sequenceName);
+        }
+        return $this->_connection->lastInsertId($tableName);
+    }
+
+}