/** "Florian Buzin [ easywe ]" <florian.buzin#easywe.de> This function changes/adds new fields to your table. You don't have to know if the col is new or not. It will check on its own. */ function ChangeTableSQL($tablename, $flds, $tableoptions = false, $dropOldFlds = false) { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; if ($this->connection->fetchMode !== false) { $savem = $this->connection->SetFetchMode(false); } // check table exists $save_handler = $this->connection->raiseErrorFn; $this->connection->raiseErrorFn = ''; $cols = $this->MetaColumns($tablename); $this->connection->raiseErrorFn = $save_handler; if (isset($savem)) { $this->connection->SetFetchMode($savem); } $ADODB_FETCH_MODE = $save; if (empty($cols)) { return $this->CreateTableSQL($tablename, $flds, $tableoptions); } if (is_array($flds)) { // Cycle through the update fields, comparing // existing fields to fields to update. // if the Metatype and size is exactly the // same, ignore - by Mark Newham $holdflds = array(); foreach ($flds as $k => $v) { if (isset($cols[$k]) && is_object($cols[$k])) { // If already not allowing nulls, then don't change $obj = $cols[$k]; if (isset($obj->not_null) && $obj->not_null) { $v = str_replace('NOT NULL', '', $v); } if (isset($obj->auto_increment) && $obj->auto_increment && empty($v['AUTOINCREMENT'])) { $v = str_replace('AUTOINCREMENT', '', $v); } $c = $cols[$k]; $ml = $c->max_length; $mt = $this->MetaType($c->type, $ml); if ($ml == -1) { $ml = ''; } if ($mt == 'X') { $ml = $v['SIZE']; } if ($mt != $v['TYPE'] || $ml != $v['SIZE'] || isset($v['AUTOINCREMENT']) && $v['AUTOINCREMENT'] != $obj->auto_increment) { $holdflds[$k] = $v; } } else { $holdflds[$k] = $v; } } $flds = $holdflds; } // already exists, alter table instead list($lines, $pkey, $idxs) = $this->_GenFields($flds); // genfields can return FALSE at times if ($lines == null) { $lines = array(); } $alter = 'ALTER TABLE ' . $this->TableName($tablename); $sql = array(); foreach ($lines as $id => $v) { if (isset($cols[$id]) && is_object($cols[$id])) { $flds = Lens_ParseArgs($v, ','); // We are trying to change the size of the field, if not allowed, simply ignore the request. // $flds[1] holds the type, $flds[2] holds the size -postnuke addition if ($flds && in_array(strtoupper(substr($flds[0][1], 0, 4)), $this->invalidResizeTypes4) && (isset($flds[0][2]) && is_numeric($flds[0][2]))) { if ($this->debug) { ADOConnection::outp(sprintf("<h3>%s cannot be changed to %s currently</h3>", $flds[0][0], $flds[0][1])); } #echo "<h3>$this->alterCol cannot be changed to $flds currently</h3>"; continue; } $sql[] = $alter . $this->alterCol . ' ' . $v; } else { $sql[] = $alter . $this->addCol . ' ' . $v; } } if ($dropOldFlds) { foreach ($cols as $id => $v) { if (!isset($lines[$id])) { $sql[] = $alter . $this->dropCol . ' ' . $v->name; } } } return $sql; }
function ChangeTableSQL($tablename, $flds, $tableoptions = false) { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; if ($this->connection->fetchMode !== false) { $savem = $this->connection->SetFetchMode(false); } // check table exists $save_handler = $this->connection->raiseErrorFn; $this->connection->raiseErrorFn = ''; $cols = $this->MetaColumns($tablename); $this->connection->raiseErrorFn = $save_handler; if (isset($savem)) { $this->connection->SetFetchMode($savem); } $ADODB_FETCH_MODE = $save; if (empty($cols)) { return $this->CreateTableSQL($tablename, $flds, $tableoptions); } if (is_array($flds)) { // Cycle through the update fields, comparing // existing fields to fields to update. // if the Metatype and size is exactly the // same, ignore - by Mark Newham $holdflds = array(); foreach ($flds as $k => $v) { if (isset($cols[$k]) && is_object($cols[$k])) { // If already not allowing nulls, then don't change $obj = $cols[$k]; if (isset($obj->not_null) && $obj->not_null) { $v = str_replace('NOT NULL', '', $v); } $c = $cols[$k]; $ml = $c->max_length; $mt = $this->MetaType($c->type, $ml); if ($ml == -1) { $ml = ''; } if ($mt == 'X') { $ml = $v['SIZE']; } if ($mt != $v['TYPE'] || $ml != $v['SIZE']) { $holdflds[$k] = $v; } } else { $holdflds[$k] = $v; } } $flds = $holdflds; } // already exists, alter table instead list($lines, $pkey) = $this->_GenFields($flds); $alter = 'ALTER TABLE ' . $this->TableName($tablename); $sql = array(); foreach ($lines as $id => $v) { if (isset($cols[$id]) && is_object($cols[$id])) { $flds = Lens_ParseArgs($v, ','); // We are trying to change the size of the field, if not allowed, simply ignore the request. if ($flds && in_array(strtoupper(substr($flds[0][1], 0, 4)), $this->invalidResizeTypes4)) { continue; } $sql[] = $alter . $this->alterCol . ' ' . $v; } else { $sql[] = $alter . $this->addCol . ' ' . $v; } } return $sql; }
function ChangeTableSQL($tablename, $flds, $tableoptions = false) { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; if ($this->connection->fetchMode !== false) { $savem = $this->connection->SetFetchMode(false); } // check table exists $save_handler = $this->connection->raiseErrorFn; $this->connection->raiseErrorFn = ''; $cols = $this->MetaColumns($tablename); $this->connection->raiseErrorFn = $save_handler; if (isset($savem)) { $this->connection->SetFetchMode($savem); } $ADODB_FETCH_MODE = $save; if (empty($cols)) { return $this->CreateTableSQL($tablename, $flds, $tableoptions); } $tableflds = $flds; /* #2343: Null / Not Null column flag changes not respected by this code. if (is_array($flds)) { // Cycle through the update fields, comparing // existing fields to fields to update. // if the Metatype and size is exactly the // same, ignore - by Mark Newham $holdflds = array(); foreach($flds as $k=>$v) { if ( isset($cols[$k]) && is_object($cols[$k]) ) { // If already not allowing nulls, then don't change $obj = $cols[$k]; if (isset($obj->not_null) && $obj->not_null) $v = str_replace('NOT NULL','',$v); $c = $cols[$k]; $ml = $c->max_length; $mt = $this->MetaType($c->type,$ml); if ($ml == -1) $ml = ''; if ($mt == 'X') $ml = $v['SIZE']; if (($mt != $v['TYPE']) || $ml != $v['SIZE']) { $holdflds[$k] = $v; } } else { $holdflds[$k] = $v; } } $flds = $holdflds; } */ // already exists, alter table instead list($lines, $pkey) = $this->_GenFields($flds); $sql = array(); $addSql = array(); $recreate = false; // FIXME 2005-08-01 KJ - Warning, horrible kludge ahead for DBMSs that can't alter column types foreach ($lines as $id => $v) { if (isset($cols[$id]) && is_object($cols[$id])) { $flds = Lens_ParseArgs($v, ','); // We are trying to change the size of the field, if not allowed, simply ignore the request. /* #2343: Null / Not Null column flag changes not respected by this code. if ($flds && in_array(strtoupper(substr($flds[0][1],0,4)),$this->invalidResizeTypes4)) continue; */ $alter = $this->AlterColumnSQL($tablename, array($id => $tableflds[$id])); if (empty($alter)) { $recreate = true; } else { $sql[] = $alter; } } else { $add = $this->AddColumnSQL($tablename, array($id => $tableflds[$id])); unset($tableflds[$id]); $sql[] = $add; $addSql[] = $add; } } if ($recreate) { $sql = $this->AlterColumnSQL($tablename, false, $tableflds, $tableoptions); $sql[] = $addSql; } return $sql; }
function _GenFields($flds) { if (is_string($flds)) { $padding = ' '; $txt = $flds . $padding; $flds = array(); $flds0 = Lens_ParseArgs($txt, ','); $hasparam = false; foreach ($flds0 as $f0) { $f1 = array(); foreach ($f0 as $token) { switch (strtoupper($token)) { case 'CONSTRAINT': case 'DEFAULT': $hasparam = $token; break; default: if ($hasparam) { $f1[$hasparam] = $token; } else { $f1[] = $token; } $hasparam = false; break; } } $flds[] = $f1; } } $this->autoIncrement = false; $lines = array(); $pkey = array(); foreach ($flds as $fld) { $fld = _array_change_key_case($fld); $fname = false; $fdefault = false; $fautoinc = false; $ftype = false; $fsize = false; $fprec = false; $fprimary = false; $fnoquote = false; $fdefts = false; $fdefdate = false; $fconstraint = false; $fnotnull = false; $funsigned = false; //----------------- // Parse attributes foreach ($fld as $attr => $v) { if ($attr == 2 && is_numeric($v)) { $attr = 'SIZE'; } else { if (is_numeric($attr) && $attr > 1 && !is_numeric($v)) { $attr = strtoupper($v); } } switch ($attr) { case '0': case 'NAME': $fname = $v; break; case '1': case 'TYPE': $ty = $v; $ftype = $this->ActualType(strtoupper($v)); break; case 'SIZE': $dotat = strpos($v, '.'); if ($dotat === false) { $dotat = strpos($v, ','); } if ($dotat === false) { $fsize = $v; } else { $fsize = substr($v, 0, $dotat); $fprec = substr($v, $dotat + 1); } break; case 'UNSIGNED': $funsigned = true; break; case 'AUTOINCREMENT': case 'AUTO': $fautoinc = true; $fnotnull = true; break; case 'KEY': case 'PRIMARY': $fprimary = $v; $fnotnull = true; break; case 'DEF': case 'DEFAULT': $fdefault = $v; break; case 'NOTNULL': $fnotnull = $v; break; case 'NOQUOTE': $fnoquote = $v; break; case 'DEFDATE': $fdefdate = $v; break; case 'DEFTIMESTAMP': $fdefts = $v; break; case 'CONSTRAINT': $fconstraint = $v; break; } //switch } // foreach $fld //-------------------- // VALIDATE FIELD INFO if (!strlen($fname)) { if ($this->debug) { ADOConnection::outp("Undefined NAME"); } return false; } $fid = strtoupper(preg_replace('/^`(.+)`$/', '$1', $fname)); $fname = $this->NameQuote($fname); if (!strlen($ftype)) { if ($this->debug) { ADOConnection::outp("Undefined TYPE for field '{$fname}'"); } return false; } else { $ftype = strtoupper($ftype); } $ftype = $this->_GetSize($ftype, $ty, $fsize, $fprec); if ($ty == 'X' || $ty == 'X2' || $ty == 'B') { $fnotnull = false; } // some blob types do not accept nulls if ($fprimary) { $pkey[] = $fname; } // some databases do not allow blobs to have defaults if ($ty == 'X') { $fdefault = false; } //-------------------- // CONSTRUCT FIELD SQL if ($fdefts) { if (substr($this->connection->databaseType, 0, 5) == 'mysql') { $ftype = 'TIMESTAMP'; } else { $fdefault = $this->connection->sysTimeStamp; } } else { if ($fdefdate) { if (substr($this->connection->databaseType, 0, 5) == 'mysql') { $ftype = 'TIMESTAMP'; } else { $fdefault = $this->connection->sysDate; } } else { if (strlen($fdefault) && !$fnoquote) { if ($ty == 'C' or $ty == 'X' or substr($fdefault, 0, 1) != "'" && !is_numeric($fdefault)) { if (strlen($fdefault) != 1 && substr($fdefault, 0, 1) == ' ' && substr($fdefault, strlen($fdefault) - 1) == ' ') { $fdefault = trim($fdefault); } else { if (strtolower($fdefault) != 'null') { $fdefault = $this->connection->qstr($fdefault); } } } } } } $suffix = $this->_CreateSuffix($fname, $ftype, $fnotnull, $fdefault, $fautoinc, $fconstraint, $funsigned); $fname = str_pad($fname, 16); $lines[$fid] = $fname . ' ' . $ftype . $suffix; if ($fautoinc) { $this->autoIncrement = true; } } // foreach $flds return array($lines, $pkey); }
function ChangeTableSQL($tablename, $flds, $tableoptions = false) { // check table exists $cols =& $this->MetaColumns($tablename); if (empty($cols)) { return $this->CreateTableSQL($tablename, $flds, $tableoptions); } // already exists, alter table instead list($lines, $pkey) = $this->_GenFields($flds); $alter = 'ALTER TABLE ' . $this->TableName($tablename); foreach ($lines as $id => $v) { if (isset($cols[$id]) && is_object($cols[$id])) { $flds = Lens_ParseArgs($v, ','); // We are trying to change the size of the field, if not allowed, simply ignore the request. if ($flds && in_array(strtoupper(substr($flds[0][1], 0, 4)), $this->invalidResizeTypes4)) { continue; } $sql[] = $alter . $this->alterCol . ' ' . $v; } else { $sql[] = $alter . $this->addCol . ' ' . $v; } } return $sql; }
/** "Florian Buzin [ easywe ]" <florian.buzin#easywe.de> This function changes/adds new fields to your table. You don't have to know if the col is new or not. It will check on its own. */ function ChangeTableSQL($tablename, $flds, $tableoptions = false, $dropOldFlds = false) { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; if ($this->connection->fetchMode !== false) { $savem = $this->connection->SetFetchMode(false); } // check table exists $save_handler = $this->connection->raiseErrorFn; $this->connection->raiseErrorFn = ''; $cols = $this->MetaColumns($tablename); $this->connection->raiseErrorFn = $save_handler; if (isset($savem)) { $this->connection->SetFetchMode($savem); } $ADODB_FETCH_MODE = $save; if (empty($cols)) { return $this->CreateTableSQL($tablename, $flds, $tableoptions); } $tableflds = $flds; /* #2343: Null / Not Null column flag changes not respected by this code. if (is_array($flds)) { // Cycle through the update fields, comparing // existing fields to fields to update. // if the Metatype and size is exactly the // same, ignore - by Mark Newham $holdflds = array(); foreach($flds as $k=>$v) { if ( isset($cols[$k]) && is_object($cols[$k]) ) { // If already not allowing nulls, then don't change $obj = $cols[$k]; if (isset($obj->not_null) && $obj->not_null) $v = str_replace('NOT NULL','',$v); if (isset($obj->auto_increment) && $obj->auto_increment && empty($v['AUTOINCREMENT'])) $v = str_replace('AUTOINCREMENT','',$v); $c = $cols[$k]; $ml = $c->max_length; $mt = $this->MetaType($c->type,$ml); if (isset($c->scale)) $sc = $c->scale; else $sc = 99; // always force change if scale not known. if ($sc == -1) $sc = false; list($fsize, $fprec) = $this->_getSizePrec($v['SIZE']); if ($ml == -1) $ml = ''; if ($mt == 'X') $ml = $v['SIZE']; if (($mt != $v['TYPE']) || ($ml != $fsize || $sc != $fprec) || (isset($v['AUTOINCREMENT']) && $v['AUTOINCREMENT'] != $obj->auto_increment)) { $holdflds[$k] = $v; } } else { $holdflds[$k] = $v; } } $flds = $holdflds; } */ // already exists, alter table instead list($lines, $pkey, $idxs) = $this->_GenFields($flds); // genfields can return FALSE at times if ($lines == null) { $lines = array(); } $alter = 'ALTER TABLE ' . $this->TableName($tablename); $sql = array(); $addSql = array(); $recreate = false; // FIXME 2005-08-01 KJ - Warning, horrible kludge ahead for DBMSs that can't alter column types foreach ($lines as $id => $v) { if (isset($cols[$id]) && is_object($cols[$id])) { $flds = Lens_ParseArgs($v, ','); // We are trying to change the size of the field, if not allowed, simply ignore the request. // $flds[1] holds the type, $flds[2] holds the size -postnuke addition /* #2343: Null / Not Null column flag changes not respected by this code. if ($flds && in_array(strtoupper(substr($flds[0][1],0,4)),$this->invalidResizeTypes4) && (isset($flds[0][2]) && is_numeric($flds[0][2]))) { if ($this->debug) ADOConnection::outp(sprintf("<h3>%s cannot be changed to %s currently</h3>", $flds[0][0], $flds[0][1])); #echo "<h3>$this->alterCol cannot be changed to $flds currently</h3>"; continue; } */ $alter = $this->AlterColumnSQL($tablename, array($id => $tableflds[$id])); if (empty($alter)) { $recreate = true; } else { $sql[] = $alter; } } else { $add = $this->AddColumnSQL($tablename, array($id => $tableflds[$id])); unset($tableflds[$id]); $sql[] = $add; $addSql[] = $add; } } if ($dropOldFlds) { foreach ($cols as $id => $v) { if (!isset($lines[$id])) { $sql[] = $alter . $this->dropCol . ' ' . $v->name; } } } if ($recreate) { $sql = $this->AlterColumnSQL($tablename, false, $tableflds, $tableoptions); $sql[] = $addSql; } return $sql; }