backend classes for setup of postgresql tables
authorFlávio Gomes da Silva Lisboa <flaviogomesdasilva@yahoo.com.br>
Sun, 24 Jul 2011 20:21:22 +0000 (22:21 +0200)
committerLars Kneschke <l.kneschke@metaways.de>
Sun, 24 Jul 2011 20:21:22 +0000 (22:21 +0200)
tine20/Setup/Backend/Pgsql.php [new file with mode: 0755]
tine20/Setup/Backend/Schema/Field/Pgsql.php [new file with mode: 0755]
tine20/Setup/Backend/Schema/Index/Pgsql.php [new file with mode: 0755]
tine20/Setup/Backend/Schema/Table/Pgsql.php [new file with mode: 0755]

diff --git a/tine20/Setup/Backend/Pgsql.php b/tine20/Setup/Backend/Pgsql.php
new file mode 100755 (executable)
index 0000000..89e417c
--- /dev/null
@@ -0,0 +1,401 @@
+<?php
+/**
+ * Tine 2.0
+ *
+ * @package     Setup
+ * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
+ * @author      Flávio Gomes da Silva Lisboa <flavio.lisboa@serpro.gov.br>
+ * @copyright   Copyright (c) 2011 SERPRO (http://www.serpro.gov.br)
+ * @version     $Id: Pgsql.php
+ *
+ */
+
+/**
+ * setup backend class for PostgreSQL 8.3 +
+ * based on class Setup_Backend_Mysql
+ * @package     Setup
+ */
+class Setup_Backend_Pgsql extends Setup_Backend_Abstract
+{
+       /**
+        * Define how database agnostic data types get mapped to postgresql data types
+        * @todo reviews data type
+        * @var array
+        */
+       protected $_typeMappings = array(
+        'integer' => array(
+               'lengthTypes' => array(
+                               4 => 'smallint',
+                               19 => 'integer',
+                               64 => 'bigint'),
+            'defaultType' => 'integer',
+               'defaultLength' => self::INTEGER_DEFAULT_LENGTH),            
+        'boolean' => array(
+            'defaultType' => 'NUMERIC',
+            'defaultScale' => 0,
+            'defaultLength' => 1),
+               'text' => array(
+            'lengthTypes' => array(
+                               256 => 'character varying', //@todo this should be 255 indeed but we have 256 in our setup.xml files
+                               65535 => 'character varying',
+                               16777215 => 'character varying',
+                               4294967295 => 'character varying'),
+            'defaultType' => 'text',
+            'defaultLength' => null),
+        'float' => array(
+            'defaultType' => 'double precision',
+               'defaultLength' => null ),
+        'decimal' => array(
+            'defaultType' => 'numeric',
+               'defaultLength' => null ),
+        'datetime' => array(
+            'defaultType' => 'timestamp with time zone',
+               'defaultLength' => null ),
+        'time' => array(
+            'defaultType' => 'time with timezone',
+               'defaultLength' => null ),
+        'date' => array(
+            'defaultType' => 'date',
+               'defaultLength' => null ),
+        'blob' => array(
+            'defaultType' => 'text',
+               'defaultLength' => null ),
+        'clob' => array(
+            'defaultType' => 'text',
+               'defaultLength' => null ),
+        'enum' => array(
+            'defaultType' => 'enum',
+                       'defaultLength' => null     )
+       );
+       
+       public function getFalseValue()
+       {
+               return 'false';
+       }
+       
+       public function getTrueValue()
+       {
+               return 'true';
+       }       
+       
+       /**
+        * Generates an SQL CREATE STATEMENT
+        * @param Setup_Backend_Schema_Table_Abstract $_table
+        * @return array CREATE TABLE statement, enum types, indexes
+        * @throws Setup_Exception_NotFound
+        */
+       public function getCreateStatement(Setup_Backend_Schema_Table_Abstract  $_table)
+       {
+               
+               $enums = array();
+               $statement = "CREATE TABLE " . SQL_TABLE_PREFIX . $_table->name . " (\n";
+               $statementSnippets = array();
+                
+               foreach ($_table->fields as $field) {
+                       if (isset($field->name)) {
+                               // getFieldDeclarations() adds 'unsigned' that it doesn't exist in PortgreSQL
+                               if (isset($field->unsigned)) $field->unsigned = false;
+                               $fieldDeclarations = $this->getFieldDeclarations($field,$_table->name);
+                               // identifies enum types and creates declaration
+                               $position = strpos($fieldDeclarations,'enum');
+                               if ($position !== false)
+                               {
+                                       $enums = $this->_getCreateTypeStatements($field,$fieldDeclarations,$_table->name, $position, $enums);                                   
+                                       // replaces enum function with enum type
+                                       $fieldDeclarations = substr($fieldDeclarations, 0, $position) . $this->_getLastEnumType($enums) . ' NOT NULL';
+                               }
+                               // removes length of integer between parenthesis 
+                               $fieldDeclarations = preg_replace('/integer\([0-9][0-9]\)/', 'integer', $fieldDeclarations);
+                               $fieldDeclarations = preg_replace('/smallint\([0-9][0-9]\)/', 'smallint', $fieldDeclarations);
+                               $fieldDeclarations = preg_replace('/bigint\([0-9][0-9]\)/', 'bigint', $fieldDeclarations);
+                               // replaces integer auto_increment with serial
+                               $fieldDeclarations = str_replace('integer NOT NULL auto_increment', 'serial', $fieldDeclarations);
+                               $statementSnippets[] = $fieldDeclarations;
+                       }
+               }
+
+               $createIndexStatement = '';
+               
+               foreach ($_table->indices as $index) {
+                       if ($index->foreign) {
+                               $statementSnippets[] = $this->getForeignKeyDeclarations($index);
+                       } else {
+                               $statementSnippet = $this->getIndexDeclarations($index,$_table->name);
+                               if (strpos($statementSnippet, 'CREATE INDEX')!==false)
+                               {
+                                       $createIndexStatement = $statementSnippet;
+                               }
+                               else
+                               {
+                                       $statementSnippets[] = $statementSnippet;
+                               }
+                       }
+               }
+
+               $statement .= implode(",\n", $statementSnippets) . "\n)";
+
+               Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . print_r($enums,true) . "\n" .  $statement . "\n" . $createIndexStatement);
+
+               return array($statement,$enums,$createIndexStatement);
+       }
+       
+       /**
+        * 
+        * Considers an associated array with one single dimension 
+        * @param array $associatedArray
+        */
+       private function _getLastEnumType(array $associatedArray)
+       {
+               $values = array_keys($associatedArray);
+               $values = $values[count($values)-1];
+               return $values;
+       }
+       
+       /**
+        *      /**
+        * 
+        * creates CREATE TYPE statement for enum types
+        * @param string $field object with info about table field
+        * @param string $fieldDeclarations original field declaration
+        * @param string $tableName table name
+        * @param string $endOfEnum position in $fieldDeclarations where begins enumeration 
+        * @param array $enums
+        */
+       private function _getCreateTypeStatements($field, $fieldDeclarations,$tableName, $endOfEnum, array $enums)
+       {
+               $createTypeStatement = 'CREATE TYPE ' . $tableName . '_enum_' . $field->name . ' AS '. substr($fieldDeclarations, $endOfEnum);
+               $enumType = $tableName . '_enum_' . $field->name;
+               $strlength = strpos($createTypeStatement,')');
+               $createTypeStatement = substr($createTypeStatement, 0, $strlength + 1) . ';';
+               $enums[$enumType] = $createTypeStatement;
+               return $enums;
+       }
+
+       /**
+        * Get schema of existing table
+        *
+        * @param String $_tableName
+        *
+        * @return Setup_Backend_Schema_Table_Pgsql
+        */
+       public function getExistingSchema($_tableName)
+       {
+               // Get common table information
+               $select = $this->_db->select()
+               ->from('information_schema.tables')
+               ->where($this->_db->quoteIdentifier('TABLE_SCHEMA') . ' = ?', $this->_config->database->dbname)
+               ->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?',  SQL_TABLE_PREFIX . $_tableName);
+
+
+               $stmt = $select->query();
+               $tableInfo = $stmt->fetchObject();
+
+               //$existingTable = new Setup_Backend_Schema_Table($tableInfo);
+               $existingTable = Setup_Backend_Schema_Table_Factory::factory('Pgsql', $tableInfo);
+               // get field informations
+               $select = $this->_db->select()
+               ->from('information_schema.COLUMNS')
+               ->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX .  $_tableName);
+
+               $stmt = $select->query();
+               $tableColumns = $stmt->fetchAll();
+
+               foreach ($tableColumns as $tableColumn) {
+                       $field = Setup_Backend_Schema_Field_Factory::factory('Pgsql', $tableColumn);
+                       $existingTable->addField($field);
+
+                       if ($field->primary === 'true' || $field->unique === 'true' || $field->mul === 'true') {
+                               $index = Setup_Backend_Schema_Index_Factory::factory('Pgsql', $tableColumn);
+
+                               // get foreign keys
+                               $select = $this->_db->select()
+                               ->from('information_schema.KEY_COLUMN_USAGE')
+                               ->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX .  $_tableName)
+                               ->where($this->_db->quoteIdentifier('COLUMN_NAME') . ' = ?', $tableColumn['COLUMN_NAME']);
+
+                               $stmt = $select->query();
+                               $keyUsage = $stmt->fetchAll();
+
+                               foreach ($keyUsage as $keyUse) {
+                                       if ($keyUse['REFERENCED_TABLE_NAME'] != NULL) {
+                                               $index->setForeignKey($keyUse);
+                                       }
+                               }
+                               $existingTable->addIndex($index);
+                       }
+               }
+
+               return $existingTable;
+       }
+
+       /**
+        * add column/field to database table
+        *
+        * @param string tableName
+        * @param Setup_Backend_Schema_Field_Abstract declaration
+        * @param int position of future column
+        */
+       public function addCol($_tableName, Setup_Backend_Schema_Field_Abstract $_declaration, $_position = NULL)
+       {
+               $statement = "ALTER TABLE '" . SQL_TABLE_PREFIX . $_tableName . "' ADD COLUMN " ;
+
+               $statement .= $this->getFieldDeclarations($_declaration);
+
+               if ($_position !== NULL) {
+                       if ($_position == 0) {
+                               $statement .= ' FIRST ';
+                       } else {
+                               $before = $this->execQuery('DESCRIBE \'' . SQL_TABLE_PREFIX . $_tableName . '\' ');
+                               $statement .= ' AFTER \'' . $before[$_position]['Field'] . '\'';
+                       }
+               }
+
+               $this->execQueryVoid($statement);
+       }
+
+       /**
+        * rename or redefines column/field in database table
+        *
+        * @param string tableName
+        * @param Setup_Backend_Schema_Field_Abstract declaration
+        * @param string old column/field name
+        */
+       public function alterCol($_tableName, Setup_Backend_Schema_Field_Abstract $_declaration, $_oldName = NULL)
+       {
+               $statement = "ALTER TABLE '" . SQL_TABLE_PREFIX . $_tableName . "' CHANGE COLUMN " ;
+
+               if ($_oldName === NULL) {
+                       $oldName = $_declaration->name;
+               } else {
+                       $oldName = $_oldName;
+               }
+
+               $statement .= " '" . $oldName .  "' " . $this->getFieldDeclarations($_declaration);
+               $this->execQueryVoid($statement);
+       }
+
+       /**
+        * add a key to database table
+        *
+        * @param string tableName
+        * @param Setup_Backend_Schema_Index_Abstract declaration
+        */
+       public function addIndex($_tableName ,  Setup_Backend_Schema_Index_Abstract $_declaration)
+       {
+               $statement = "ALTER TABLE '" . SQL_TABLE_PREFIX . $_tableName . "' ADD "
+               . $this->getIndexDeclarations($_declaration);
+               $this->execQueryVoid($statement);
+       }
+
+       /**
+        * create the right pgsql-statement-snippet for keys.
+        * return constraints to add to create table statement or
+        * return create index statement
+        * @param   Setup_Backend_Schema_Index_Abstract $_key
+        * @param String | optional $_tableName [is not used in this Backend (PgSQL)]
+        * @return  string
+        * @throws  Setup_Exception_NotFound
+        */
+       public function getIndexDeclarations(Setup_Backend_Schema_Index_Abstract $_key, $_tableName = '')
+       {
+               $isNotIndex = false;
+               
+               $keys = array();
+
+               $indexes = str_replace('-', ',', $_key->name);
+               $snippet = 'CREATE INDEX  ' . $_tableName . '_' . $_key->name . ' ON ' . SQL_TABLE_PREFIX . $_tableName . "($indexes);";
+               $snippet = str_replace('-', '_', $snippet);
+               if (!empty($_key->primary)) {
+                       $pkey = $_tableName . '_pkey';
+                       $pkey = str_replace('-', '_', $pkey);
+                       $snippet = " CONSTRAINT $pkey PRIMARY KEY ";
+                       $isNotIndex = true;
+               } else if (!empty($_key->unique)) {
+                       $unique = $_tableName . '_' . $_key->name . '_' . 'key';
+                       $unique = str_replace('-', '_', $unique);
+                       $snippet = "CONSTRAINT $unique UNIQUE " ;
+                       $isNotIndex = true;
+               }
+
+               foreach ($_key->field as $keyfield) {
+                       $key = (string)$keyfield;
+                       $keys[] = $key;
+               }
+
+               if (empty($keys)) {
+                       throw new Setup_Exception_NotFound('no keys for index found');
+               }
+
+               if ($isNotIndex)
+               {
+                       $snippet .= ' (' . implode(",", $keys) . ')';
+               }
+
+               return $snippet;
+       }
+
+       /**
+        *  create the right mysql-statement-snippet for foreign keys
+        *
+        * @param object $_key the xml index definition
+        * @return string
+        */
+       public function getForeignKeyDeclarations(Setup_Backend_Schema_Index_Abstract $_key)
+       {
+               $snippet = '  CONSTRAINT ' . SQL_TABLE_PREFIX . $_key->referenceTable . '_' . $_key->field . ' FOREIGN KEY ';
+               $snippet .= '(' . $_key->field . ") REFERENCES " . SQL_TABLE_PREFIX
+               . $_key->referenceTable .
+                    " (" . $_key->referenceField . ")";
+
+               if (!empty($_key->referenceOnDelete)) {
+                       $snippet .= " ON DELETE " . strtoupper($_key->referenceOnDelete);
+               }
+               if (!empty($_key->referenceOnUpdate)) {
+                       $snippet .= " ON UPDATE " . strtoupper($_key->referenceOnUpdate);
+               }               
+               
+               return $snippet;
+       }
+
+       /**
+        * enable/disabled foreign key checks
+        *
+        * @param integer|string|boolean $_value
+        */
+       public function setForeignKeyChecks($_value)
+       {
+               if ($_value == 0 || $_value == 1) {
+                       $this->_db->query("SET FOREIGN_KEY_CHECKS=" . $_value);
+               }
+       }
+       
+       /**
+     * takes the xml stream and creates a table
+     *
+     * @param object $_table xml stream
+     */        
+    public function createTable(Setup_Backend_Schema_Table_Abstract  $_table)
+    {
+       // receives an array where 0 is CREATE STATEMENT, 1 are CREATE TYPE statements and 2 is CREATE INDEX statement 
+        $statements = $this->getCreateStatement($_table);
+        
+        try {
+                       // creates enum types before creates declaration
+                       foreach($statements[1] as $enumType => $createTypeStatement)
+                       {
+                               $this->execQueryVoid('DROP TYPE IF EXISTS '. $enumType . ' CASCADE;');
+                               $this->execQueryVoid($createTypeStatement);
+                       }
+        
+               $this->execQueryVoid($statements[0]);
+               
+               if (!empty($statements[2])) $this->execQueryVoid($statements[2]);
+        }
+        catch (Exception $e)
+        {
+               Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Exception: ' . $e->getMessage() . ' Trace: ' . $e->getTraceAsString());
+        }
+    }
+       
+}
+
diff --git a/tine20/Setup/Backend/Schema/Field/Pgsql.php b/tine20/Setup/Backend/Schema/Field/Pgsql.php
new file mode 100755 (executable)
index 0000000..4a1bb48
--- /dev/null
@@ -0,0 +1,94 @@
+<?php
+/**
+ * Tine 2.0 - http://www.tine20.org
+ * 
+ * @package     Setup
+ * @license     http://www.gnu.org/licenses/agpl.html
+ * @copyright   Copyright (c) 2011 SERPRO (http://www.serpro.gov.br)
+ * @author      Flávio Gomes da Silva Lisboa <flavio.lisboa@serpro.gov.br>
+ * @version     $Id: Pgsql.php
+ */
+
+class Setup_Backend_Schema_Field_Pgsql extends Setup_Backend_Schema_Field_Abstract
+{
+
+    public function __construct($_declaration)
+    {
+        $this->_setField($_declaration);
+        
+        parent::__construct($_declaration);
+    }
+    
+    /**
+     * set Setup_Backend_Schema_Table from a given database query 
+     *
+     * @todo this function does not work is_array and -> does not fit together
+     * @param stdClass $_declaration
+     */    
+    protected function _setField($_declaration)
+    {    
+        if (is_array($_declaration)) {
+            $this->name = $_declaration['COLUMN_NAME'];
+            $type       = $_declaration['DATA_TYPE'];
+            $default    = $_declaration['COLUMN_DEFAULT'];
+            $length     = $_declaration['CHARACTER_MAXIMUM_LENGTH'];
+            $scale      = null;
+            
+            switch ($_declaration['DATA_TYPE']) {
+                case('tinyint'):
+                case('mediumint'):
+                case('bigint'):
+                case('int'):
+                    $type = 'integer';
+                    $default = intval($default);
+                    $matches = null;
+                    if (preg_match('/\((\d+)\)/', $_declaration['COLUMN_TYPE'], $matches)) {
+                        $length = $matches[1];
+                    } else {
+                        $length = $_declaration['NUMERIC_PRECISION'] + 1;
+                    }
+                    break;
+
+                case('decimal'):
+                    $length = $_declaration['NUMERIC_PRECISION'];
+                    $scale  = $_declaration['NUMERIC_SCALE'];
+                    break;
+                
+                case('double'):
+                    $length = null;
+                    $type = 'float';
+                    break;
+                    
+                case('enum'):
+                    $this->value = explode(',', str_replace("'", '', substr($_declaration['COLUMN_TYPE'], 5, (strlen($_declaration['COLUMN_TYPE']) - 6))));
+                    break;
+                    
+                case('longblob'):
+                    $type = 'blob';
+                    $length = null;
+                    break;
+                
+                case('longtext'): //@todo should return clob?
+                case('varchar'):
+                    $type = 'text';
+                    break;
+                }
+
+            if ($_declaration['EXTRA'] == 'auto_increment') {
+                $this->autoincrement = 'true';
+            }
+
+            ($_declaration['IS_NULLABLE'] == 'NO')? $this->notnull = 'true': $this->notnull = 'false';
+            ($_declaration['COLUMN_KEY'] == 'UNI')? $this->unique = 'true': $this->unique = 'false';
+            ($_declaration['COLUMN_KEY'] == 'PRI')? $this->primary = 'true': $this->primary = 'false';
+            ($_declaration['COLUMN_KEY'] == 'MUL')? $this->mul = 'true': $this->mul = 'false';
+            
+            $this->default  = $default;
+            $this->comment  = $_declaration['COLUMN_COMMENT'];
+            $this->length   = $length;
+            $this->scale    = $scale;
+            $this->type     = $type;
+        }
+    }
+}
diff --git a/tine20/Setup/Backend/Schema/Index/Pgsql.php b/tine20/Setup/Backend/Schema/Index/Pgsql.php
new file mode 100755 (executable)
index 0000000..a1bc7a9
--- /dev/null
@@ -0,0 +1,83 @@
+<?php
+/**
+ * Tine 2.0 - http://www.tine20.org
+ * 
+ * @package     Setup
+ * @license     http://www.gnu.org/licenses/agpl.html
+ * @copyright   Copyright (c) 2011 SERPRO (http://www.serpro.gov.br)
+ * @author      Flávio Gomes da Silva Lisboa <flavio.lisboa@serpro.gov.br>
+ * @version     $Id: Pgsql.php
+ */
+
+class Setup_Backend_Schema_Index_Pgsql extends Setup_Backend_Schema_Index_Abstract
+{
+
+    public function __construct($_declaration)
+    {
+        $this->_setIndex($_declaration);
+    }
+
+    public function setForeignKey($_declaration)
+    {
+        parent::setForeignKey($_declaration);
+        $this->referencetable = substr($_declaration['REFERENCED_TABLE_NAME'], strlen(SQL_TABLE_PREFIX));
+        $this->referencefield = $_declaration['REFERENCED_COLUMN_NAME'];
+    }
+    
+    
+    /**
+     * set Setup_Backend_Schema_Table from a given database query 
+     *
+     * @param stdClass $_declaration
+     */    
+    protected function _setIndex($_declaration)
+    {    
+        $this->setName($_declaration['COLUMN_NAME']);
+        $type = '';
+        $length= '';
+        
+        switch ($_declaration['DATA_TYPE']) {
+            case('int'):
+                $type = 'integer';
+                $length = $_declaration['NUMERIC_PRECISION'] + 1;
+                break;
+        
+            case('tinyint'):
+                $type = 'integer';
+                $length = $_declaration['NUMERIC_PRECISION'] + 1;
+                break;
+            
+            case('enum'):
+                $type = $_declaration['DATA_TYPE'];
+                $this->value = explode(',', str_replace("'", '', substr($_declaration['COLUMN_TYPE'], 5, (strlen($_declaration['COLUMN_TYPE']) - 6))));
+                break;
+            
+            case('varchar'):
+                $length = $_declaration['CHARACTER_MAXIMUM_LENGTH'];
+                $type = 'text';
+                break;
+            
+            default:
+                $length = $_declaration['CHARACTER_MAXIMUM_LENGTH'];
+                $type = $_declaration['DATA_TYPE'];
+            }
+
+        if ($_declaration['EXTRA'] == 'auto_increment') {
+            $this->autoincrement = 'true';
+        }
+
+        if (preg_match('/unsigned/', $_declaration['COLUMN_TYPE'])) {
+            $this->unsigned = 'true';
+        }
+
+        ($_declaration['IS_NULLABLE'] == 'NO')? $this->notnull = 'true': $this->notnull = 'false';
+        ($_declaration['COLUMN_KEY'] == 'UNI')? $this->unique = 'true': $this->unique = 'false';
+        ($_declaration['COLUMN_KEY'] == 'PRI')? $this->primary = 'true': $this->primary = 'false';
+        ($_declaration['COLUMN_KEY'] == 'MUL')? $this->mul = 'true': $this->mul = 'false';
+
+        $this->comment = $_declaration['COLUMN_COMMENT'];
+        $this->length = $length;
+        $this->type = $type;
+    }
+}
diff --git a/tine20/Setup/Backend/Schema/Table/Pgsql.php b/tine20/Setup/Backend/Schema/Table/Pgsql.php
new file mode 100755 (executable)
index 0000000..5e75f0e
--- /dev/null
@@ -0,0 +1,27 @@
+<?php
+/**
+ * Tine 2.0 - http://www.tine20.org
+ * 
+ * @package     Setup
+ * @license     http://www.gnu.org/licenses/agpl.html
+ * @copyright   Copyright (c) 2011 SERPRO (http://www.serpro.gov.br)
+ * @author      Flávio Gomes da Silva Lisboa <flavio.lisboa@serpro.gov.br>
+ * @version     $Id: Pgsql.php
+ */
+
+
+class Setup_Backend_Schema_Table_Pgsql extends Setup_Backend_Schema_Table_Abstract
+{
+
+    public function __construct($_tableDefinition)
+    {
+         $this->setName($_tableDefinition->TABLE_NAME);
+    }
+      
+    public function setFields($_fieldDefinitions)
+    {
+        foreach ($_fieldDefinitions as $fieldDefinition) {
+            $this->addField(Setup_Backend_Schema_Field_Factory::factory('Pgsql', $fieldDefinition));
+        }
+    }
+}