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