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