[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpgroupware-cvs] phpgwapi/inc class.schema_proc_pgsql.inc.php [Version
|
From: |
Sigurd Nes |
|
Subject: |
[Phpgroupware-cvs] phpgwapi/inc class.schema_proc_pgsql.inc.php [Version-0_9_16-branch] |
|
Date: |
Mon, 20 Mar 2006 13:52:57 +0000 |
CVSROOT: /sources/phpgwapi
Module name: phpgwapi
Branch: Version-0_9_16-branch
Changes by: Sigurd Nes <address@hidden> 06/03/20 13:52:57
Modified files:
inc : class.schema_proc_pgsql.inc.php
Log message:
no longer creates temporary tables
CVSWeb URLs:
http://cvs.savannah.gnu.org/viewcvs/phpgwapi/phpgwapi/inc/class.schema_proc_pgsql.inc.php.diff?only_with_tag=Version-0_9_16-branch&tr1=1.5.2.13&tr2=1.5.2.14&r1=text&r2=text
Patches:
Index: phpgwapi/inc/class.schema_proc_pgsql.inc.php
diff -u phpgwapi/inc/class.schema_proc_pgsql.inc.php:1.5.2.13
phpgwapi/inc/class.schema_proc_pgsql.inc.php:1.5.2.14
--- phpgwapi/inc/class.schema_proc_pgsql.inc.php:1.5.2.13 Tue Mar 14
07:42:18 2006
+++ phpgwapi/inc/class.schema_proc_pgsql.inc.php Mon Mar 20 13:52:57 2006
@@ -12,7 +12,7 @@
* @license http://www.fsf.org/licenses/gpl.html GNU General Public
License
* @package phpgwapi
* @subpackage database
- * @version $Id: class.schema_proc_pgsql.inc.php,v 1.5.2.13 2006/03/14
07:42:18 sigurdne Exp $
+ * @version $Id: class.schema_proc_pgsql.inc.php,v 1.5.2.14 2006/03/20
13:52:57 sigurdne Exp $
* @link http://www.greatbridge.org/project/phppgadmin
* @internal SQL for table properties taken from phpPgAdmin Version 2.2.1
*/
@@ -370,66 +370,6 @@
return false;
}
- function _CopyAlteredTable($oProc, &$aTables, $sSource, $sDest)
- {
- $oDB = $oProc->m_odb;
- $oProc->m_odb->query("select * from $sSource");
- while ($oProc->m_odb->next_record())
- {
- $sSQL = "INSERT INTO $sDest (";
- $i=0;
- @reset($aTables[$sDest]['fd']);
- while (list($name,$arraydef) =
@each($aTables[$sDest]['fd']))
- {
- if ($i > 0)
- {
- $sSQL .= ',';
- }
-
- $sSQL .= $name;
- $i++;
- }
-
- $sSQL .= ') VALUES (';
- @reset($aTables[$sDest]['fd']);
- $i=0;
- while (list($name,$arraydef) =
@each($aTables[$sDest]['fd']))
- {
- if ($i > 0)
- {
- $sSQL .= ',';
- }
-
- if ($oProc->m_odb->f($name) != null)
- {
- switch ($arraydef['type'])
- {
- case 'blob':
- case 'char':
- case 'date':
- case 'text':
- case 'timestamp':
- case 'varchar':
- $sSQL .= "'" .
$oProc->m_odb->db_addslashes($oProc->m_odb->f($name)) . "'";
- break;
- default:
- $sSQL .=
intval($oProc->m_odb->f($name));
- }
- }
- else
- {
- $sSQL .= 'null';
- }
- $i++;
- }
- $sSQL .= ')';
-
- $oDB->query($sSQL);
- }
-
- return true;
- }
-
function GetSequenceForTable($oProc,$table,&$sSequenceName)
{
global $DEBUG;
@@ -480,73 +420,19 @@
function DropColumn($oProc, &$aTables, $sTableName,
$aNewTableDef, $sColumnName, $bCopyData = true)
{
- if ($bCopyData)
- {
- $oProc->m_odb->query("SELECT * INTO
$sTableName" . "_tmp FROM $sTableName");
- }
-
- $this->DropTable($oProc, $aTables, $sTableName);
-
- $oProc->_GetTableSQL($sTableName, $aNewTableDef,
$sTableSQL, $sSequenceSQL);
- if($sSequenceSQL)
- {
- $oProc->m_odb->query($sSequenceSQL);
- }
- $query = "CREATE TABLE $sTableName ($sTableSQL)";
- if (!$bCopyData)
- {
- return !!($oProc->m_odb->query($query));
- }
-
- $oProc->m_odb->query($query);
- $this->_GetColumns($oProc, $sTableName . '_tmp',
$sColumns, $sColumnName);
- $query = "INSERT INTO $sTableName SELECT $sColumns FROM
$sTableName" . '_tmp';
+ $query = "ALTER TABLE $sTableName DROP COLUMN
$sColumnName CASCADE";
$bRet = !!($oProc->m_odb->query($query));
- return ($bRet && $this->DropTable($oProc, $aTables,
$sTableName . '_tmp'));
+ return $bRet;
+
}
function RenameTable($oProc, &$aTables, $sOldTableName,
$sNewTableName)
{
global $DEBUG;
- if ($DEBUG) { echo '<br />RenameTable(): Fetching old
sequence for: ' . $sOldTableName; }
-
$this->GetSequenceForTable($oProc,$sOldTableName,$sSequenceName);
- if ($DEBUG) { echo ' - ' . $sSequenceName; }
- if ($DEBUG) { echo '<br />RenameTable(): Fetching
sequence field for: ' . $sOldTableName; }
-
$this->GetSequenceFieldForTable($oProc,$sOldTableName,$sField);
- if ($DEBUG) { echo ' - ' . $sField; }
-
- if ($sSequenceName)
- {
- $oProc->m_odb->query("SELECT last_value FROM
seq_$sOldTableName",__LINE__,__FILE__);
- $oProc->m_odb->next_record();
- $lastval = $oProc->m_odb->f(0);
-
- if ($DEBUG) { echo '<br />RenameTable():
dropping old sequence: ' . $sSequenceName . ' used on field: ' . $sField; }
-
$this->DropSequenceForTable($oProc,$sOldTableName);
-
- if ($lastval)
- {
- $lastval = ' start ' . $lastval;
- }
-
$this->GetSequenceSQL($sNewTableName,$sSequenceSQL);
- if ($DEBUG) { echo '<br />RenameTable(): Making
new sequence using: ' . $sSequenceSQL . $lastval; }
- $oProc->m_odb->query($sSequenceSQL .
$lastval,__LINE__,__FILE__);
- if ($DEBUG) { echo '<br />RenameTable():
Altering column default for: ' . $sField; }
- $oProc->m_odb->query("ALTER TABLE
$sOldTableName ALTER $sField SET DEFAULT nextval('seq_" . $sNewTableName .
"')",__LINE__,__FILE__);
- }
- $indexnames = $oProc->m_odb->index_names();
- while(list($key,$val) = @each($indexnames))
- {
- $indexes[] = $val['index_name'];
- }
- if(!in_array($sOldTableName . '_pkey',$indexes))
// no idea how this can happen
- {
- $oProc->m_odb->query("DROP INDEX " .
$sOldTableName . "_pkey",__LINE__,__FILE__);
- }
- else // rename the index
+ if ($DEBUG)
{
- $oProc->m_odb->query('ALTER TABLE
'.$sOldTableName.'_pkey RENAME TO '.$sNewTableName.'_pkey');
+ echo '<br />RenameTable(): Renamed: ' .
$sOldTableName . 'to: ' . $sNewTableName;
}
return !!($oProc->m_odb->query("ALTER TABLE
$sOldTableName RENAME TO $sNewTableName"));
@@ -554,57 +440,73 @@
function RenameColumn($oProc, &$aTables, $sTableName,
$sOldColumnName, $sNewColumnName, $bCopyData = true)
{
- /*
- This really needs testing - it can affect primary
keys, and other table-related objects
- like sequences and such
- */
- if ($bCopyData)
- {
- $oProc->m_odb->query("SELECT * INTO
$sTableName" . "_tmp FROM $sTableName");
- }
-
- $this->DropTable($oProc, $aTables, $sTableName);
-
- if (!$bCopyData)
- {
- return $this->CreateTable($oProc, $aTables,
$sTableName, $oProc->m_aTables[$sTableName], false);
- }
-
- $this->CreateTable($oProc, $aTables, $sTableName,
$aTables[$sTableName], True);
- $this->_GetColumns($oProc, $sTableName . "_tmp",
$sColumns);
- $query = "INSERT INTO $sTableName SELECT $sColumns FROM
$sTableName" . "_tmp";
-
- $bRet = !!($oProc->m_odb->query($query));
- return ($bRet && $this->DropTable($oProc, $aTables,
$sTableName . "_tmp"));
+ $query = "ALTER TABLE $sTableName RENAME COLUMN
$sOldColumnName TO $sNewColumnName";
+ return !!($oProc->m_odb->query($query));
}
function AlterColumn($oProc, &$aTables, $sTableName,
$sColumnName, &$aColumnDef, $bCopyData = true)
{
- if ($bCopyData)
- {
- $oProc->m_odb->query("SELECT * INTO
$sTableName" . "_tmp FROM $sTableName");
+ $sType = '';
+ $iPrecision = 0;
+ $iScale = 0;
+ $sDefault = '';
+ $bNullable = true;
+
+ reset($aColumnDef);
+ while(list($sAttr, $vAttrVal) = each($aColumnDef))
+ {
+ switch ($sAttr)
+ {
+ case 'type':
+ $sType = $vAttrVal;
+ break;
+ case 'precision':
+ $iPrecision = (int)$vAttrVal;
+ break;
+ case 'scale':
+ $iScale = (int)$vAttrVal;
+ break;
+ case 'default':
+ $sDefault = $vAttrVal;
+ break;
+ case 'nullable':
+ $bNullable = $vAttrVal;
+ break;
+ }
}
- $this->DropTable($oProc, $aTables, $sTableName);
+ $sFieldSQL = $this->TranslateType($sType, $iPrecision,
$iScale);
+ $query = "ALTER TABLE $sTableName ALTER COLUMN
$sColumnName TYPE $sFieldSQL";
+ $Ok = !!($oProc->m_odb->query($query));
- if (!$bCopyData)
+ if($bNullable == False || $bNullable == 'False')
{
- return $this->CreateTable($oProc, $aTables,
$sTableName, $aTables[$sTableName], True);
+ $sFieldSQL = ' NOT NULL';
+ $query = "ALTER TABLE $sTableName ALTER COLUMN
$sColumnName SET $sFieldSQL";
+ $Ok = !!($oProc->m_odb->query($query));
}
- $this->CreateTable($oProc, $aTables, $sTableName,
$aTables[$sTableName], True);
- $this->_GetColumns($oProc, $sTableName . "_tmp",
$sColumns, '', $sColumnName, $aColumnDef['type'] == 'auto' ? 'int4' :
$aColumnDef['type']);
-
- /*
- TODO: analyze the type of change and determine if this
is used or _CopyAlteredTable
- this is a performance consideration only,
_CopyAlteredTable should be safe
- $query = "INSERT INTO $sTableName SELECT $sColumns
FROM $sTableName" . "_tmp";
- $bRet = !!($oProc->m_odb->query($query));
- */
+ if($sDefault == '0')
+ {
+ $defaultSQL = " DEFAULT 0";
+ }
+ elseif(!is_numeric($sDefault) && $sDefault != '')
+ {
+ $sTranslatedDefault =
$this->TranslateDefault($sDefault);
+ $defaultSQL = " DEFAULT $sTranslatedDefault";
+ }
+ elseif($sDefault)
+ {
+ $defaultSQL = " DEFAULT $sDefault";
+ }
- $bRet = $this->_CopyAlteredTable($oProc, $aTables,
$sTableName . '_tmp', $sTableName);
+ if($defaultSQL)
+ {
+ $query = "ALTER TABLE $sTableName ALTER COLUMN
$sColumnName SET $defaultSQL";
+ $Ok = !!($oProc->m_odb->query($query));
+ }
- return ($bRet && $this->DropTable($oProc, $aTables,
$sTableName . "_tmp"));
+ return $Ok;
}
function AddColumn($oProc, &$aTables, $sTableName,
$sColumnName, &$aColumnDef)
@@ -624,7 +526,21 @@
if (($Ok = !!($oProc->m_odb->query($query))) &&
isset($default))
{
- $query = "ALTER TABLE $sTableName ALTER COLUMN
$sColumnName SET DEFAULT '$default';\n";
+ if($default == '0')
+ {
+ $defaultSQL = " DEFAULT 0";
+ }
+ elseif(!is_numeric($default) && $default != '')
+ {
+ $sTranslatedDefault =
$this->TranslateDefault($default);
+ $defaultSQL = " DEFAULT
$sTranslatedDefault";
+ }
+ elseif($default)
+ {
+ $defaultSQL = " DEFAULT $default";
+ }
+
+ $query = "ALTER TABLE $sTableName ALTER COLUMN
$sColumnName SET $defaultSQL;\n";
$query .= "UPDATE $sTableName SET
$sColumnName='$default';\n";