1: <?php
  2: /**
  3:  * This file contains CONTENIDO database functions.
  4:  *
  5:  * @package          Core
  6:  * @subpackage       Backend
  7:  * @version          SVN Revision $Rev:$
  8:  *
  9:  * @author           Timo Hummel
 10:  * @copyright        four for business AG <www.4fb.de>
 11:  * @license          http://www.contenido.org/license/LIZENZ.txt
 12:  * @link             http://www.4fb.de
 13:  * @link             http://www.contenido.org
 14:  */
 15: 
 16: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
 17: 
 18: /**
 19:  * Returns existing indexes of a specific table.
 20:  * @param   cDb  $db
 21:  * @param   string  $table
 22:  * @return  array  Assoziative array where the key and the value is the index name
 23:  */
 24: function dbGetIndexes($db, $table)
 25: {
 26:     if (!is_object($db)) {
 27:         return false;
 28:     }
 29: 
 30:     $sql = 'SHOW INDEX FROM '.cSecurity::escapeDB($table, $db);
 31:     $db->query($sql);
 32: 
 33:     $indexes = array();
 34: 
 35:     while ($db->nextRecord()) {
 36:         $indexes[$db->f('Key_name')] = $db->f('Key_name');
 37:     }
 38: 
 39:     return ($indexes);
 40: }
 41: 
 42: 
 43: /**
 44:  * Updates a specific table. Used e. g. by CONTENIDO setup to create or update
 45:  * tables.
 46:  * Function logic:
 47:  * 1 .) Check, if the table exists
 48:  * 2a.) If not, create it with the field specification, exit
 49:  * 2b.) If the table exists, check, if the field exist
 50:  * 3 .) If not, try to find the field using previous names (if specified in $field like 'name1,name2')
 51:  * 4a.) If the field hasn't been found, create the field as specified, exit
 52:  * 4b.) If the field has been found using a previous name (if specified) rename the column to $field
 53:  * 5 .) As the field has been found, check, if the field's type is matching
 54:  * 5a.) If the type is matching, exit
 55:  * 5b.) If the field's content type is not matching, try to convert first (e.g. string to int
 56:  *      or int to string), then use the upgrade statement if applicable
 57:  *
 58:  * Note about the upgrade statement:
 59:  *  - the code must be eval'able
 60:  *  - the code needs to read $oldVal (old field value) and needs to set $newVal (value to which the field will be set)
 61:  *  - $oldVal might be empty if the field didn't exist
 62:  *  - $tableValues['fieldname'] contains the already existing values
 63:  *
 64:  * @param  cDb  $db  Database instance
 65:  * @param  string  $table  Name of table to create/update
 66:  * @param  string  $field  Name of field to create/update
 67:  * @param  string  $type  Data type of field. Feasible values are all possible data types
 68:  *                        e. g. int(10), varchar(32), datetime, varchar(255), text, tinyint(1)
 69:  * @param  string  $null  Parameter to forbid null values, feasible values '', 'NULL' or 'YES'
 70:  *                        where 'NULL' or 'YES' allows null values and '' doesn't
 71:  * @param  string  $key   The field will be added as a primary key, if value is 'PRI',
 72:  *                        otherwhise the value should be empty ''
 73:  * @param  string  $default  The default value for the field. Feasible is each possible
 74:  *                           value depending on passed $type
 75:  * @param  string  $extra  Additional info for the field, e. g. 'auto_increment', if the
 76:  *                         field should have the AUTO_INCREMENT attribute and empty otherwise.
 77:  * @param  string  $upgradeStatement  NOT USED AT THE MOMENT
 78:  * @param  bool  $bRemoveIndexes  Flag to remove all indexes
 79:  * @return  bool
 80:  */
 81: function dbUpgradeTable($db, $table, $field, $type, $null, $key, $default, $extra,
 82:                         $upgradeStatement, $bRemoveIndexes = false)
 83: {
 84:     global $columnCache;
 85:     global $tableCache;
 86: 
 87:     if (!is_object($db)) {
 88:         return false;
 89:     }
 90: 
 91:     $parameter = array();
 92: 
 93:     // Parameter checking for $null. If parameter is '' or 'NULL' or 'YES', we
 94:     // know that we want the colum to forbid null entries.
 95:     if ($null == 'NULL' || $null == 'YES') {
 96:         $parameter['NULL'] = 'NULL';
 97:         $null = 'YES';
 98:     } else {
 99:         $parameter['NULL'] = 'NOT NULL';
100:         $null = '';
101:     }
102: 
103:     // Parameter checking for $key. If parameter is '' or 'NULL' or 'YES', we
104:     // know that we want the primary key.
105:     if ($key == 'PRI') {
106:         $parameter['KEY'] = 'PRIMARY KEY';
107:     } else {
108:         $parameter['KEY'] = '';
109:     }
110: 
111:     // Parameter check for $default. If set, create a default value
112:     if ($default != '') {
113:         if (((strpos($type, 'timestamp') !== FALSE) && ($default != '')) || ($default == 'NULL')) {
114:             $parameter['DEFAULT'] = "DEFAULT " . $db->escape($default);
115:         } else {
116:             $parameter['DEFAULT'] = "DEFAULT '" . $db->escape($default) . "'";
117:         }
118:     } else {
119:         $parameter['DEFAULT'] = '';
120:     }
121: 
122:     if (!dbTableExists($db, $table)) {
123:         $sql = "CREATE TABLE `".$db->escape($table)."` (`".$db->escape($field)."` $type ".$parameter['NULL']." ".$parameter['DEFAULT']." ".$parameter['KEY'] .")";
124:         $db->query($sql);
125:         $tableCache[] = $table;
126:         return true;
127:     }
128: 
129:     // Remove auto_increment
130:     $structure = dbGetColumns($db, $table);
131:     if (isset($structure[$field]) && $structure[$field]['Extra'] == 'auto_increment') {
132:         if ($structure[$field]['NULL'] == '') {
133:             $structure[$field]['NULL'] = 'NOT NULL';
134:         }
135:         $sql = "ALTER TABLE `".$db->escape($table)."` CHANGE COLUMN `".$db->escape($field)."` `".$db->escape($field)."` ".$db->escape($type)." ".$structure[$field]['NULL']." ".$structure[$field]['DEFAULT']." ".$structure[$field]['KEY'];
136:         $db->query($sql);
137:     }
138: 
139:     // Remove all keys, as they are being recreated during an upgrade
140:     if ($bRemoveIndexes == true) {
141:         $indexes = dbGetIndexes($db, $table);
142:         foreach ($indexes as $index) {
143:             $sql = '';
144:             if ($index == 'PRIMARY') {
145:                 if (isset($structure[$field]) && $structure[$field]['Key'] == 'PRI') {
146:                     $sql = 'ALTER TABLE `'.$db->escape($table).'` DROP PRIMARY KEY';
147:                 }
148:             } else {
149:                 $sql = 'ALTER TABLE `'.$db->escape($table).'` DROP INDEX '.$db->escape($index);
150:             }
151:             if (!empty($sql)) {
152:                 $db->query($sql);
153:             }
154:         }
155:         unset($columnCache[$table]);
156:     }
157: 
158:     $structure = dbGetColumns($db, $table);
159: 
160:     // If $field contains ',' previous names has been specified; separate from $field
161:     $sepPos = strpos($field, ',');
162:     if ($sepPos === false) {
163:         $previousName = '';
164:     } else {
165:         $previousName = substr($field, $sepPos + 1);
166:         $field = substr($field, 0, $sepPos);
167:     }
168: 
169:     if (!array_key_exists($field, $structure)) {
170:         // HerrB: Search field using $previousName
171:         $blnFound = false;
172:         if ($previousName != '') {
173:             $arrPreviousName = explode(',', $previousName);
174:             foreach ($arrPreviousName as $strPrevious) {
175:                 // Maybe someone has used field1, field2, ..., trim spaces
176:                 $strPrevious = trim($strPrevious);
177:                 if (array_key_exists($strPrevious,$structure)) {
178:                     $blnFound = true;
179:                     break;
180:                 }
181:             }
182:         }
183: 
184:         if ($blnFound) {
185:             // Rename column, update array, proceed
186:             if ($structure[$strPrevious]['Null'] == 'YES') {
187:                 $sql = "ALTER TABLE `".$db->escape($table)."` CHANGE COLUMN `".$db->escape($strPrevious)."` `".$db->escape($field)."` ".$structure[$strPrevious]['Type']." DEFAULT '".$structure[$strPrevious]['Default']."'";
188:             } else {
189:                 $sql = "ALTER TABLE `".$db->escape($table)."` CHANGE COLUMN `".$db->escape($strPrevious)."` `".$db->escape($field)."` ".$structure[$strPrevious]['Type']." NOT NULL DEFAULT '".$structure[$strPrevious]['Default']."'";
190:             }
191:             $db->query($sql);
192: 
193:             $columnCache[$table] = '';
194:             $structure = dbGetColumns($db, $table);
195:         } else {
196:             // Add column as specified
197:             $sql = "ALTER TABLE `".$db->escape($table)."` ADD COLUMN `".$db->escape($field)."` ".$db->escape($type)." ".$parameter['NULL']." ".$parameter['DEFAULT']." ".$parameter['KEY'];
198:             $db->query($sql);
199: 
200:             $columnCache[$table] = '';
201:             return true;
202:         }
203:     }
204: 
205:     $structure = dbGetColumns($db, $table);
206: 
207:     // Third check: Compare field properties
208:     if (($structure[$field]['Type'] != $type) ||
209:        ($structure[$field]['Null'] != $null) ||
210:        ($structure[$field]['Key'] != $key) ||
211:        ($structure[$field]['Default'] != $default) ||
212:        ($structure[$field]['Extra'] != $extra)) {
213: 
214:         if ($structure[$field]['Key'] == 'PRI') {
215:             $sql = "ALTER TABLE `".$db->escape($table)."` ADD PRIMARY KEY (".$db->escape($field).") ";
216:         } else {
217:             $sql = "ALTER TABLE `".$db->escape($table)."` CHANGE COLUMN `".$db->escape($field)."` `".$db->escape($field)."` ".$db->escape($type)." ".$parameter['NULL']." ".$parameter['DEFAULT']." ".$parameter['KEY'];
218:         }
219:         $db->query($sql);
220: 
221:         $columnCache[$table] = '';
222:     }
223: 
224:     return true;
225: }
226: 
227: 
228: /**
229:  * Checks, if passed table exists in the database
230:  * @param   cDb  $db
231:  * @param   string  $table
232:  * @return  bool
233:  */
234: function dbTableExists($db, $table)
235: {
236:     global $tableCache;
237: 
238:     if (!is_object($db)) {
239:         return false;
240:     }
241: 
242:     if (!is_array($tableCache)) {
243:         $tableCache = array();
244:         $sql = 'SHOW TABLES';
245:         $db->query($sql);
246:         while ($db->nextRecord()) {
247:             $tableCache[] = $db->f(0);
248:         }
249:     }
250: 
251:     if (in_array($table, $tableCache)) {
252:         return true;
253:     } else {
254:         return false;
255:     }
256: }
257: 
258: 
259: /**
260:  * Returns the column structure of a table
261:  * @param   cDb  $db
262:  * @param   string  $table
263:  * @return  array|bool  Either assoziative column array or false
264:  */
265: function dbGetColumns($db, $table)
266: {
267:     global $columnCache;
268: 
269:     if (!is_object($db)) {
270:         return false;
271:     }
272: 
273:     if (isset($columnCache[$table]) && is_array($columnCache[$table])) {
274:         return $columnCache[$table];
275:     }
276: 
277:     $structure = array();
278: 
279:     $sql = 'SHOW COLUMNS FROM ' . $db->escape($table);
280:     $db->query($sql);
281:     while ($db->nextRecord()) {
282:         $structure[$db->f('Field')] = $db->toArray();
283:     }
284: 
285:     $columnCache[$table] = $structure;
286: 
287:     return $structure;
288: }
289: 
290: 
291: /**
292:  * Returns the primary key column of a table
293:  * @param   cDb  $db
294:  * @param   string  $table
295:  * @return  string
296:  */
297: function dbGetPrimaryKeyName($db, $table)
298: {
299:     $sReturn = '';
300:     $structure = dbGetColumns($db, $table);
301: 
302:     if (is_array($structure)) {
303:         foreach ($structure as $mykey => $value) {
304:             if ($value['Key'] == 'PRI') {
305:                 $sReturn = $mykey;
306:             }
307:         }
308:     }
309: 
310:     return $sReturn;
311: }
312: 
313: ?>