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