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