adding some strings
[moodle-linuxchix.git] / lib / ddllib.php
blob81946bf4730190bd73db231cf2389b2c0f75d943
1 <?php // $Id$
3 ///////////////////////////////////////////////////////////////////////////
4 // //
5 // NOTICE OF COPYRIGHT //
6 // //
7 // Moodle - Modular Object-Oriented Dynamic Learning Environment //
8 // http://moodle.com //
9 // //
10 // Copyright (C) 2001-3001 Martin Dougiamas http://dougiamas.com //
11 // (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com //
12 // //
13 // This program is free software; you can redistribute it and/or modify //
14 // it under the terms of the GNU General Public License as published by //
15 // the Free Software Foundation; either version 2 of the License, or //
16 // (at your option) any later version. //
17 // //
18 // This program is distributed in the hope that it will be useful, //
19 // but WITHOUT ANY WARRANTY; without even the implied warranty of //
20 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the //
21 // GNU General Public License for more details: //
22 // //
23 // http://www.gnu.org/copyleft/gpl.html //
24 // //
25 ///////////////////////////////////////////////////////////////////////////
27 // This library includes all the required functions used to handle the DB
28 // structure (DDL) independently of the underlying RDBMS in use. All the functions
29 // rely on the XMLDBDriver classes to be able to generate the correct SQL
30 // syntax needed by each DB.
32 // To define any structure to be created we'll use the schema defined
33 // by the XMLDB classes, for tables, fields, indexes, keys and other
34 // statements instead of direct handling of SQL sentences.
36 // This library should be used, exclusively, by the installation and
37 // upgrade process of Moodle.
39 // For further documentation, visit http://docs.moodle.org/en/DDL_functions
41 /// Add required XMLDB constants
42 require_once($CFG->libdir . '/xmldb/classes/XMLDBConstants.php');
44 /// Add main XMLDB Generator
45 require_once($CFG->libdir . '/xmldb/classes/generators/XMLDBGenerator.class.php');
47 /// Add required XMLDB DB classes
48 require_once($CFG->libdir . '/xmldb/classes/XMLDBObject.class.php');
49 require_once($CFG->libdir . '/xmldb/classes/XMLDBFile.class.php');
50 require_once($CFG->libdir . '/xmldb/classes/XMLDBStructure.class.php');
51 require_once($CFG->libdir . '/xmldb/classes/XMLDBTable.class.php');
52 require_once($CFG->libdir . '/xmldb/classes/XMLDBField.class.php');
53 require_once($CFG->libdir . '/xmldb/classes/XMLDBKey.class.php');
54 require_once($CFG->libdir . '/xmldb/classes/XMLDBIndex.class.php');
55 require_once($CFG->libdir . '/xmldb/classes/XMLDBStatement.class.php');
57 /// Based on $CFG->dbtype, add the proper generator class
58 if (!file_exists($CFG->libdir . '/xmldb/classes/generators/' . $CFG->dbtype . '/' . $CFG->dbtype . '.class.php')) {
59 error ('DB Type: ' . $CFG->dbtype . ' not supported by XMLDDB');
61 require_once($CFG->libdir . '/xmldb/classes/generators/' . $CFG->dbtype . '/' . $CFG->dbtype . '.class.php');
64 /// Add other libraries
65 require_once($CFG->libdir . '/xmlize.php');
66 /**
67 * Add a new field to a table, or modify an existing one (if oldfield is defined).
68 * Warning: Please be careful on primary keys, as this function will eat auto_increments
70 * @uses $CFG
71 * @uses $db
72 * @param string $table the name of the table to modify. (Without the prefix.)
73 * @param string $oldfield If changing an existing column, the name of that column.
74 * @param string $field The name of the column at the end of the operation.
75 * @param string $type The type of the column at the end of the operation. TEXT, VARCHAR, CHAR, INTEGER, REAL, or TINYINT
76 * @param string $size The size of that column type. As in VARCHAR($size), or INTEGER($size).
77 * @param string $signed For numeric column types, whether that column is 'signed' or 'unsigned'.
78 * @param string $default The new default value for the column.
79 * @param string $null 'not null', or '' to allow nulls.
80 * @param string $after Which column to insert this one after. Not supported on Postgres.
82 * @return boolean Wheter the operation succeeded.
84 function table_column($table, $oldfield, $field, $type='integer', $size='10',
85 $signed='unsigned', $default='0', $null='not null', $after='') {
86 global $CFG, $db, $empty_rs_cache;
88 if (!empty($empty_rs_cache[$table])) { // Clear the recordset cache because it's out of date
89 unset($empty_rs_cache[$table]);
92 switch (strtolower($CFG->dbtype)) {
94 case 'mysql':
95 case 'mysqlt':
97 switch (strtolower($type)) {
98 case 'text':
99 $type = 'TEXT';
100 $signed = '';
101 break;
102 case 'integer':
103 $type = 'INTEGER('. $size .')';
104 break;
105 case 'varchar':
106 $type = 'VARCHAR('. $size .')';
107 $signed = '';
108 break;
109 case 'char':
110 $type = 'CHAR('. $size .')';
111 $signed = '';
112 break;
115 if (!empty($oldfield)) {
116 $operation = 'CHANGE '. $oldfield .' '. $field;
117 } else {
118 $operation = 'ADD '. $field;
121 $default = 'DEFAULT \''. $default .'\'';
123 if (!empty($after)) {
124 $after = 'AFTER `'. $after .'`';
127 return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' '. $operation .' '. $type .' '. $signed .' '. $default .' '. $null .' '. $after);
129 case 'postgres7': // From Petri Asikainen
130 //Check db-version
131 $dbinfo = $db->ServerInfo();
132 $dbver = substr($dbinfo['version'],0,3);
134 //to prevent conflicts with reserved words
135 $realfield = '"'. $field .'"';
136 $field = '"'. $field .'_alter_column_tmp"';
137 $oldfield = '"'. $oldfield .'"';
139 switch (strtolower($type)) {
140 case 'tinyint':
141 case 'integer':
142 if ($size <= 4) {
143 $type = 'INT2';
145 if ($size <= 10) {
146 $type = 'INT';
148 if ($size > 10) {
149 $type = 'INT8';
151 break;
152 case 'varchar':
153 $type = 'VARCHAR('. $size .')';
154 break;
155 case 'char':
156 $type = 'CHAR('. $size .')';
157 $signed = '';
158 break;
161 $default = '\''. $default .'\'';
163 //After is not implemented in postgesql
164 //if (!empty($after)) {
165 // $after = "AFTER '$after'";
168 //Use transactions
169 execute_sql('BEGIN');
171 //Always use temporary column
172 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
173 //Add default values
174 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default);
177 if ($dbver >= '7.3') {
178 // modifying 'not null' is posible before 7.3
179 //update default values to table
180 if (strtoupper($null) == 'NOT NULL') {
181 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default .' WHERE '. $field .' IS NULL');
182 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
183 } else {
184 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' DROP NOT NULL');
188 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET DEFAULT '. $default);
190 if ( $oldfield != '""' ) {
192 // We are changing the type of a column. This may require doing some casts...
193 $casting = '';
194 $oldtype = column_type($table, $oldfield);
195 $newtype = column_type($table, $field);
197 // Do we need a cast?
198 if($newtype == 'N' && $oldtype == 'C') {
199 $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS REAL)';
201 else if($newtype == 'I' && $oldtype == 'C') {
202 $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS INTEGER)';
204 else {
205 $casting = $oldfield;
208 // Run the update query, casting as necessary
209 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .' = '. $casting);
210 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' DROP COLUMN '. $oldfield);
213 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $field .' TO '. $realfield);
215 return execute_sql('COMMIT');
217 default:
218 switch (strtolower($type)) {
219 case 'integer':
220 $type = 'INTEGER';
221 break;
222 case 'varchar':
223 $type = 'VARCHAR';
224 break;
227 $default = 'DEFAULT \''. $default .'\'';
229 if (!empty($after)) {
230 $after = 'AFTER '. $after;
233 if (!empty($oldfield)) {
234 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $oldfield .' '. $field);
235 } else {
236 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
239 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
240 return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $default);
245 * Given one XMLDBTable, check if it exists in DB (true/false)
247 * @param XMLDBTable table to be searched for
248 * @return boolean true/false
250 function table_exists($table) {
252 global $CFG, $db;
254 $exists = true;
256 /// Do this function silenty (to avoid output in install/upgrade process)
257 $olddbdebug = $db->debug;
258 $db->debug = false;
260 /// Load the needed generator
261 $classname = 'XMLDB' . $CFG->dbtype;
262 $generator = new $classname();
263 $generator->setPrefix($CFG->prefix);
264 /// Calculate the name of the table
265 $tablename = $generator->getTableName($table, false);
267 /// Search such tablename in DB
268 $metatables = $db->MetaTables();
269 $metatables = array_flip($metatables);
270 $metatables = array_change_key_case($metatables, CASE_LOWER);
271 if (!array_key_exists($tablename, $metatables)) {
272 $exists = false;
275 /// Re-set original debug
276 $db->debug = $olddbdebug;
278 return $exists;
282 * Given one XMLDBField, check if it exists in DB (true/false)
284 * @uses, $db
285 * @param XMLDBTable the table
286 * @param XMLDBField the field to be searched for
287 * @return boolean true/false
289 function field_exists($table, $field) {
291 global $CFG, $db;
293 $exists = true;
295 /// Do this function silenty (to avoid output in install/upgrade process)
296 $olddbdebug = $db->debug;
297 $db->debug = false;
299 /// Check the table exists
300 if (!table_exists($table)) {
301 $db->debug = $olddbdebug; //Re-set original $db->debug
302 return false;
305 /// Load the needed generator
306 $classname = 'XMLDB' . $CFG->dbtype;
307 $generator = new $classname();
308 $generator->setPrefix($CFG->prefix);
309 /// Calculate the name of the table
310 $tablename = $generator->getTableName($table, false);
312 /// Get list of fields in table
313 $fields = null;
314 if ($fields = $db->MetaColumns($tablename)) {
315 $fields = array_change_key_case($fields, CASE_LOWER);
318 if (!array_key_exists($field->getName(), $fields)) {
319 $exists = false;
322 /// Re-set original debug
323 $db->debug = $olddbdebug;
325 return $exists;
329 * Given one XMLDBIndex, check if it exists in DB (true/false)
331 * @uses, $db
332 * @param XMLDBTable the table
333 * @param XMLDBIndex the index to be searched for
334 * @return boolean true/false
336 function index_exists($table, $index) {
338 global $CFG, $db;
340 $exists = true;
342 /// Do this function silenty (to avoid output in install/upgrade process)
343 $olddbdebug = $db->debug;
344 $db->debug = false;
346 /// Wrap over find_index_name to see if the index exists
347 if (!find_index_name($table, $index)) {
348 $exists = false;
351 /// Re-set original debug
352 $db->debug = $olddbdebug;
354 return $exists;
358 * This function IS NOT IMPLEMENTED. ONCE WE'LL BE USING RELATIONAL
359 * INTEGRITY IT WILL BECOME MORE USEFUL. FOR NOW, JUST CALCULATE "OFFICIAL"
360 * KEY NAMES WITHOUT ACCESSING TO DB AT ALL.
361 * Given one XMLDBKey, the function returns the name of the key in DB (if exists)
362 * of false if it doesn't exist
364 * @uses, $db
365 * @param XMLDBTable the table to be searched
366 * @param XMLDBKey the key to be searched
367 * @return string key name of false
369 function find_key_name($table, $xmldb_key) {
371 global $CFG, $db;
373 /// Extract key columns
374 $keycolumns = $xmldb_key->getFields();
376 /// Get list of keys in table
377 /// first primaries (we aren't going to use this now, because the MetaPrimaryKeys is awful)
378 ///TODO: To implement when we advance in relational integrity
379 /// then uniques (note that Moodle, for now, shouldn't have any UNIQUE KEY for now, but unique indexes)
380 ///TODO: To implement when we advance in relational integrity (note that AdoDB hasn't any MetaXXX for this.
381 /// then foreign (note that Moodle, for now, shouldn't have any FOREIGN KEY for now, but indexes)
382 ///TODO: To implement when we advance in relational integrity (note that AdoDB has one MetaForeignKeys()
383 ///but it's far from perfect.
384 /// TODO: To create the proper functions inside each generator to retrieve all the needed KEY info (name
385 /// columns, reftable and refcolumns
387 /// So all we do is to return the official name of the requested key without any confirmation!)
388 $classname = 'XMLDB' . $CFG->dbtype;
389 $generator = new $classname();
390 $generator->setPrefix($CFG->prefix);
391 /// One exception, harcoded primary constraint names
392 if ($generator->primary_key_name && $xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
393 return $generator->primary_key_name;
394 } else {
395 /// Calculate the name suffix
396 switch ($xmldb_key->getType()) {
397 case XMLDB_KEY_PRIMARY:
398 $suffix = 'pk';
399 break;
400 case XMLDB_KEY_UNIQUE:
401 $suffix = 'uk';
402 break;
403 case XMLDB_KEY_FOREIGN_UNIQUE:
404 case XMLDB_KEY_FOREIGN:
405 $suffix = 'fk';
406 break;
408 /// And simply, return the oficial name
409 return $generator->getNameForObject($table->getName(), implode(', ', $xmldb_key->getFields()), $suffix);
414 * Given one XMLDBIndex, the function returns the name of the index in DB (if exists)
415 * of false if it doesn't exist
417 * @uses, $db
418 * @param XMLDBTable the table to be searched
419 * @param XMLDBIndex the index to be searched
420 * @return string index name of false
422 function find_index_name($table, $index) {
424 global $CFG, $db;
426 /// Do this function silenty (to avoid output in install/upgrade process)
427 $olddbdebug = $db->debug;
428 $db->debug = false;
430 /// Extract index columns
431 $indcolumns = $index->getFields();
433 /// Check the table exists
434 if (!table_exists($table)) {
435 $db->debug = $olddbdebug; //Re-set original $db->debug
436 return false;
439 /// Load the needed generator
440 $classname = 'XMLDB' . $CFG->dbtype;
441 $generator = new $classname();
442 $generator->setPrefix($CFG->prefix);
443 /// Calculate the name of the table
444 $tablename = $generator->getTableName($table, false);
446 /// Get list of indexes in table
447 $indexes = null;
448 if ($indexes = $db->MetaIndexes($tablename)) {
449 $indexes = array_change_key_case($indexes, CASE_LOWER);
452 /// Iterate over them looking for columns coincidence
453 if ($indexes) {
454 foreach ($indexes as $indexname => $index) {
455 $columns = $index['columns'];
456 /// Lower case column names
457 $columns = array_flip($columns);
458 $columns = array_change_key_case($columns, CASE_LOWER);
459 $columns = array_flip($columns);
460 /// Check if index matchs queried index
461 $diferences = array_merge(array_diff($columns, $indcolumns), array_diff($indcolumns, $columns));
462 /// If no diferences, we have find the index
463 if (empty($diferences)) {
464 $db->debug = $olddbdebug; //Re-set original $db->debug
465 return $indexname;
469 /// Arriving here, index not found
470 $db->debug = $olddbdebug; //Re-set original $db->debug
471 return false;
475 * Given one XMLDBTable, the function returns the name of its sequence in DB (if exists)
476 * of false if it doesn't exist
478 * @param XMLDBTable the table to be searched
479 * @return string sequence name of false
481 function find_sequence_name($table) {
483 global $CFG, $db;
485 $sequencename = false;
487 /// Do this function silenty (to avoid output in install/upgrade process)
488 $olddbdebug = $db->debug;
489 $db->debug = false;
491 if (strtolower(get_class($table)) != 'xmldbtable') {
492 $db->debug = $olddbdebug; //Re-set original $db->debug
493 return false;
496 /// Check table exists
497 if (!table_exists($table)) {
498 debugging('Table ' . $table->getName() . ' do not exist. Sequence not found', DEBUG_DEVELOPER);
499 $db->debug = $olddbdebug; //Re-set original $db->debug
500 return false; //Table doesn't exist, nothing to do
503 $sequencename = $table->getSequenceFromDB($CFG->dbtype, $CFG->prefix);
505 $db->debug = $olddbdebug; //Re-set original $db->debug
506 return $sequencename;
510 * This function will load one entire XMLDB file, generating all the needed
511 * SQL statements, specific for each RDBMS ($CFG->dbtype) and, finally, it
512 * will execute all those statements against the DB.
514 * @uses $CFG, $db
515 * @param $file full path to the XML file to be used
516 * @return boolean (true on success, false on error)
518 function install_from_xmldb_file($file) {
520 global $CFG, $db;
522 $status = true;
525 $xmldb_file = new XMLDBFile($file);
527 if (!$xmldb_file->fileExists()) {
528 return false;
531 $loaded = $xmldb_file->loadXMLStructure();
532 if (!$loaded || !$xmldb_file->isLoaded()) {
533 /// Show info about the error if we can find it
534 if ($structure =& $xmldb_file->getStructure()) {
535 if ($errors = $structure->getAllErrors()) {
536 notify('Errors found in XMLDB file: '. implode (', ', $errors));
539 return false;
542 $structure = $xmldb_file->getStructure();
544 if (!$sqlarr = $structure->getCreateStructureSQL($CFG->dbtype, $CFG->prefix, false)) {
545 return true; //Empty array = nothing to do = no error
548 return execute_sql_arr($sqlarr);
552 * This function will create the table passed as argument with all its
553 * fields/keys/indexes/sequences, everything based in the XMLDB object
555 * @uses $CFG, $db
556 * @param XMLDBTable table object (full specs are required)
557 * @param boolean continue to specify if must continue on error (true) or stop (false)
558 * @param boolean feedback to specify to show status info (true) or not (false)
559 * @return boolean true on success, false on error
561 function create_table($table, $continue=true, $feedback=true) {
563 global $CFG, $db;
565 $status = true;
567 if (strtolower(get_class($table)) != 'xmldbtable') {
568 return false;
571 /// Check table doesn't exist
572 if (table_exists($table)) {
573 debugging('Table ' . $table->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
574 return true; //Table exists, nothing to do
577 if(!$sqlarr = $table->getCreateTableSQL($CFG->dbtype, $CFG->prefix, false)) {
578 return true; //Empty array = nothing to do = no error
581 return execute_sql_arr($sqlarr, $continue, $feedback);
585 * This function will drop the table passed as argument
586 * and all the associated objects (keys, indexes, constaints, sequences, triggers)
587 * will be dropped too.
589 * @uses $CFG, $db
590 * @param XMLDBTable table object (just the name is mandatory)
591 * @param boolean continue to specify if must continue on error (true) or stop (false)
592 * @param boolean feedback to specify to show status info (true) or not (false)
593 * @return boolean true on success, false on error
595 function drop_table($table, $continue=true, $feedback=true) {
597 global $CFG, $db;
599 $status = true;
601 if (strtolower(get_class($table)) != 'xmldbtable') {
602 return false;
605 /// Check table exists
606 if (!table_exists($table)) {
607 debugging('Table ' . $table->getName() . ' do not exist. Delete skipped', DEBUG_DEVELOPER);
608 return true; //Table don't exist, nothing to do
611 if(!$sqlarr = $table->getDropTableSQL($CFG->dbtype, $CFG->prefix, false)) {
612 return true; //Empty array = nothing to do = no error
615 return execute_sql_arr($sqlarr, $continue, $feedback);
619 * This function will create the temporary table passed as argument with all its
620 * fields/keys/indexes/sequences, everything based in the XMLDB object
622 * TRUNCATE the table immediately after creation. A previous process using
623 * the same persistent connection may have created the temp table and failed to
624 * drop it. In that case, the table will exist, and create_temp_table() will
625 * will succeed.
627 * NOTE: The return value is the tablename - some DBs (MSSQL at least) use special
628 * names for temp tables.
630 * @uses $CFG, $db
631 * @param XMLDBTable table object (full specs are required)
632 * @param boolean continue to specify if must continue on error (true) or stop (false)
633 * @param boolean feedback to specify to show status info (true) or not (false)
634 * @return string tablename on success, false on error
636 function create_temp_table($table, $continue=true, $feedback=true) {
638 global $CFG, $db;
640 $status = true;
642 if (strtolower(get_class($table)) != 'xmldbtable') {
643 return false;
647 $temporary = 'TEMPORARY';
648 switch (strtolower($CFG->dbfamily)) {
649 case 'mssql':
650 // TODO: somehow change the name to have a #
651 $temporary = '';
652 break;
653 case 'oracle':
654 $temporary = 'GLOBAL TEMPORARY';
655 break;
658 /// Check table doesn't exist
659 if (table_exists($table)) {
660 debugging('Table ' . $table->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
661 return $table->getName(); //Table exists, nothing to do
664 if(!$sqlarr = $table->getCreateTableSQL($CFG->dbtype, $CFG->prefix, false)) {
665 return $table->getName(); //Empty array = nothing to do = no error
668 if (!empty($temporary)) {
669 $sqlarr = preg_replace('/^CREATE/', "CREATE $temporary", $sqlarr);
672 if (execute_sql_arr($sqlarr, $continue, $feedback)) {
673 return $table->getName();
674 } else {
675 return false;
680 * This function will rename the table passed as argument
681 * Before renaming the index, the function will check it exists
683 * @uses $CFG, $db
684 * @param XMLDBTable table object (just the name is mandatory)
685 * @param string new name of the index
686 * @param boolean continue to specify if must continue on error (true) or stop (false)
687 * @param boolean feedback to specify to show status info (true) or not (false)
688 * @return boolean true on success, false on error
690 function rename_table($table, $newname, $continue=true, $feedback=true) {
692 global $CFG, $db;
694 $status = true;
696 if (strtolower(get_class($table)) != 'xmldbtable') {
697 return false;
700 /// Check table exists
701 if (!table_exists($table)) {
702 debugging('Table ' . $table->getName() . ' do not exist. Rename skipped', DEBUG_DEVELOPER);
703 return true; //Table doesn't exist, nothing to do
706 /// Check new table doesn't exist
707 $check = new XMLDBTable($newname);
708 if (table_exists($check)) {
709 debugging('Table ' . $check->getName() . ' already exists. Rename skipped', DEBUG_DEVELOPER);
710 return true; //Table exists, nothing to do
713 /// Check newname isn't empty
714 if (!$newname) {
715 debugging('New name for table ' . $index->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
716 return true; //Table doesn't exist, nothing to do
719 if(!$sqlarr = $table->getRenameTableSQL($CFG->dbtype, $CFG->prefix, $newname, false)) {
720 return true; //Empty array = nothing to do = no error
723 return execute_sql_arr($sqlarr, $continue, $feedback);
727 * This function will add the field to the table passed as arguments
729 * @uses $CFG, $db
730 * @param XMLDBTable table object (just the name is mandatory)
731 * @param XMLDBField field object (full specs are required)
732 * @param boolean continue to specify if must continue on error (true) or stop (false)
733 * @param boolean feedback to specify to show status info (true) or not (false)
734 * @return boolean true on success, false on error
736 function add_field($table, $field, $continue=true, $feedback=true) {
738 global $CFG, $db;
740 $status = true;
742 if (strtolower(get_class($table)) != 'xmldbtable') {
743 return false;
745 if (strtolower(get_class($field)) != 'xmldbfield') {
746 return false;
749 /// Check the field doesn't exist
750 if (field_exists($table, $field)) {
751 debugging('Field ' . $field->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
752 return true;
755 /// If NOT NULL and no default given, check the table is empty
756 if ($field->getNotNull() && $field->getDefault() === NULL && count_records($table->getName())) {
757 debugging('Field ' . $field->getName() . ' cannot be added. Not null fields added to non empty tables require default value. Create skipped', DEBUG_DEVELOPER);
758 return true;
761 if(!$sqlarr = $table->getAddFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
762 return true; //Empty array = nothing to do = no error
765 return execute_sql_arr($sqlarr, $continue, $feedback);
769 * This function will drop the field from the table passed as arguments
771 * @uses $CFG, $db
772 * @param XMLDBTable table object (just the name is mandatory)
773 * @param XMLDBField field object (just the name is mandatory)
774 * @param boolean continue to specify if must continue on error (true) or stop (false)
775 * @param boolean feedback to specify to show status info (true) or not (false)
776 * @return boolean true on success, false on error
778 function drop_field($table, $field, $continue=true, $feedback=true) {
780 global $CFG, $db;
782 $status = true;
784 if (strtolower(get_class($table)) != 'xmldbtable') {
785 return false;
787 if (strtolower(get_class($field)) != 'xmldbfield') {
788 return false;
791 /// Check the field exists
792 if (!field_exists($table, $field)) {
793 debugging('Field ' . $field->getName() . ' do not exist. Delete skipped', DEBUG_DEVELOPER);
794 return true;
797 if(!$sqlarr = $table->getDropFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
798 return true; //Empty array = nothing to do = no error
801 return execute_sql_arr($sqlarr, $continue, $feedback);
805 * This function will change the type of the field in the table passed as arguments
807 * @uses $CFG, $db
808 * @param XMLDBTable table object (just the name is mandatory)
809 * @param XMLDBField field object (full specs are required)
810 * @param boolean continue to specify if must continue on error (true) or stop (false)
811 * @param boolean feedback to specify to show status info (true) or not (false)
812 * @return boolean true on success, false on error
814 function change_field_type($table, $field, $continue=true, $feedback=true) {
816 global $CFG, $db;
818 $status = true;
820 if (strtolower(get_class($table)) != 'xmldbtable') {
821 return false;
823 if (strtolower(get_class($field)) != 'xmldbfield') {
824 return false;
827 if(!$sqlarr = $table->getAlterFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
828 return true; //Empty array = nothing to do = no error
831 return execute_sql_arr($sqlarr, $continue, $feedback);
835 * This function will change the precision of the field in the table passed as arguments
837 * @uses $CFG, $db
838 * @param XMLDBTable table object (just the name is mandatory)
839 * @param XMLDBField field object (full specs are required)
840 * @param boolean continue to specify if must continue on error (true) or stop (false)
841 * @param boolean feedback to specify to show status info (true) or not (false)
842 * @return boolean true on success, false on error
844 function change_field_precision($table, $field, $continue=true, $feedback=true) {
846 /// Just a wrapper over change_field_type. Does exactly the same processing
847 return change_field_type($table, $field, $continue, $feedback);
851 * This function will change the unsigned/signed of the field in the table passed as arguments
853 * @uses $CFG, $db
854 * @param XMLDBTable table object (just the name is mandatory)
855 * @param XMLDBField field object (full specs are required)
856 * @param boolean continue to specify if must continue on error (true) or stop (false)
857 * @param boolean feedback to specify to show status info (true) or not (false)
858 * @return boolean true on success, false on error
860 function change_field_unsigned($table, $field, $continue=true, $feedback=true) {
862 /// Just a wrapper over change_field_type. Does exactly the same processing
863 return change_field_type($table, $field, $continue, $feedback);
867 * This function will change the nullability of the field in the table passed as arguments
869 * @uses $CFG, $db
870 * @param XMLDBTable table object (just the name is mandatory)
871 * @param XMLDBField field object (full specs are required)
872 * @param boolean continue to specify if must continue on error (true) or stop (false)
873 * @param boolean feedback to specify to show status info (true) or not (false)
874 * @return boolean true on success, false on error
876 function change_field_notnull($table, $field, $continue=true, $feedback=true) {
878 /// Just a wrapper over change_field_type. Does exactly the same processing
879 return change_field_type($table, $field, $continue, $feedback);
883 * This function will change the enum status of the field in the table passed as arguments
885 * @uses $CFG, $db
886 * @param XMLDBTable table object (just the name is mandatory)
887 * @param XMLDBField field object (full specs are required)
888 * @param boolean continue to specify if must continue on error (true) or stop (false)
889 * @param boolean feedback to specify to show status info (true) or not (false)
890 * @return boolean true on success, false on error
892 function change_field_enum($table, $field, $continue=true, $feedback=true) {
894 global $CFG, $db;
896 $status = true;
898 if (strtolower(get_class($table)) != 'xmldbtable') {
899 return false;
901 if (strtolower(get_class($field)) != 'xmldbfield') {
902 return false;
905 if(!$sqlarr = $table->getModifyEnumSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
906 return true; //Empty array = nothing to do = no error
909 return execute_sql_arr($sqlarr, $continue, $feedback);
912 * This function will change the default of the field in the table passed as arguments
913 * One null value in the default field means delete the default
915 * @uses $CFG, $db
916 * @param XMLDBTable table object (just the name is mandatory)
917 * @param XMLDBField field object (full specs are required)
918 * @param boolean continue to specify if must continue on error (true) or stop (false)
919 * @param boolean feedback to specify to show status info (true) or not (false)
920 * @return boolean true on success, false on error
922 function change_field_default($table, $field, $continue=true, $feedback=true) {
924 global $CFG, $db;
926 $status = true;
928 if (strtolower(get_class($table)) != 'xmldbtable') {
929 return false;
931 if (strtolower(get_class($field)) != 'xmldbfield') {
932 return false;
935 if(!$sqlarr = $table->getModifyDefaultSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
936 return true; //Empty array = nothing to do = no error
939 return execute_sql_arr($sqlarr, $continue, $feedback);
943 * This function will rename the field in the table passed as arguments
944 * Before renaming the field, the function will check it exists
946 * @uses $CFG, $db
947 * @param XMLDBTable table object (just the name is mandatory)
948 * @param XMLDBField index object (full specs are required)
949 * @param string new name of the field
950 * @param boolean continue to specify if must continue on error (true) or stop (false)
951 * @param boolean feedback to specify to show status info (true) or not (false)
952 * @return boolean true on success, false on error
954 function rename_field($table, $field, $newname, $continue=true, $feedback=true) {
956 global $CFG, $db;
958 $status = true;
960 if (strtolower(get_class($table)) != 'xmldbtable') {
961 return false;
963 if (strtolower(get_class($field)) != 'xmldbfield') {
964 return false;
967 /// Check we have included full field specs
968 if (!$field->getType()) {
969 debugging('Field ' . $field->getName() . ' must contain full specs. Rename skipped', DEBUG_DEVELOPER);
970 return false;
973 /// Check field isn't id. Renaming over that field is not allowed
974 if ($field->getName() == 'id') {
975 debugging('Field ' . $field->getName() . ' cannot be renamed. Rename skipped', DEBUG_DEVELOPER);
976 return true; //Field is "id", nothing to do
979 /// Check field exists
980 if (!field_exists($table, $field)) {
981 debugging('Field ' . $field->getName() . ' do not exist. Rename skipped', DEBUG_DEVELOPER);
982 return true; //Field doesn't exist, nothing to do
985 /// Check newname isn't empty
986 if (!$newname) {
987 debugging('New name for field ' . $field->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
988 return true; //Field doesn't exist, nothing to do
991 if(!$sqlarr = $table->getRenameFieldSQL($CFG->dbtype, $CFG->prefix, $field, $newname, false)) {
992 return true; //Empty array = nothing to do = no error
995 return execute_sql_arr($sqlarr, $continue, $feedback);
999 * This function will create the key in the table passed as arguments
1001 * @uses $CFG, $db
1002 * @param XMLDBTable table object (just the name is mandatory)
1003 * @param XMLDBKey index object (full specs are required)
1004 * @param boolean continue to specify if must continue on error (true) or stop (false)
1005 * @param boolean feedback to specify to show status info (true) or not (false)
1006 * @return boolean true on success, false on error
1008 function add_key($table, $key, $continue=true, $feedback=true) {
1010 global $CFG, $db;
1012 $status = true;
1014 if (strtolower(get_class($table)) != 'xmldbtable') {
1015 return false;
1017 if (strtolower(get_class($key)) != 'xmldbkey') {
1018 return false;
1020 if ($key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be added (only in create table, being serious :-P)
1021 debugging('Primary Keys can be added at table create time only', DEBUG_DEVELOPER);
1022 return true;
1025 if(!$sqlarr = $table->getAddKeySQL($CFG->dbtype, $CFG->prefix, $key, false)) {
1026 return true; //Empty array = nothing to do = no error
1029 return execute_sql_arr($sqlarr, $continue, $feedback);
1033 * This function will drop the key in the table passed as arguments
1035 * @uses $CFG, $db
1036 * @param XMLDBTable table object (just the name is mandatory)
1037 * @param XMLDBKey key object (full specs are required)
1038 * @param boolean continue to specify if must continue on error (true) or stop (false)
1039 * @param boolean feedback to specify to show status info (true) or not (false)
1040 * @return boolean true on success, false on error
1042 function drop_key($table, $key, $continue=true, $feedback=true) {
1044 global $CFG, $db;
1046 $status = true;
1048 if (strtolower(get_class($table)) != 'xmldbtable') {
1049 return false;
1051 if (strtolower(get_class($key)) != 'xmldbkey') {
1052 return false;
1054 if ($key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be dropped (only in drop table, being serious :-P)
1055 debugging('Primary Keys can be deleted at table drop time only', DEBUG_DEVELOPER);
1056 return true;
1059 if(!$sqlarr = $table->getDropKeySQL($CFG->dbtype, $CFG->prefix, $key, false)) {
1060 return true; //Empty array = nothing to do = no error
1063 return execute_sql_arr($sqlarr, $continue, $feedback);
1067 * This function will rename the key in the table passed as arguments
1068 * Experimental. Shouldn't be used at all in normal installation/upgrade!
1070 * @uses $CFG, $db
1071 * @param XMLDBTable table object (just the name is mandatory)
1072 * @param XMLDBKey key object (full specs are required)
1073 * @param string new name of the key
1074 * @param boolean continue to specify if must continue on error (true) or stop (false)
1075 * @param boolean feedback to specify to show status info (true) or not (false)
1076 * @return boolean true on success, false on error
1078 function rename_key($table, $key, $newname, $continue=true, $feedback=true) {
1080 global $CFG, $db;
1082 debugging('rename_key() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER);
1084 $status = true;
1086 if (strtolower(get_class($table)) != 'xmldbtable') {
1087 return false;
1089 if (strtolower(get_class($key)) != 'xmldbkey') {
1090 return false;
1093 /// Check newname isn't empty
1094 if (!$newname) {
1095 debugging('New name for key ' . $key->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
1096 return true; //Key doesn't exist, nothing to do
1099 if(!$sqlarr = $table->getRenameKeySQL($CFG->dbtype, $CFG->prefix, $key, $newname, false)) {
1100 debugging('Some DBs do not support key renaming (MySQL, PostgreSQL, MsSQL). Rename skipped', DEBUG_DEVELOPER);
1101 return true; //Empty array = nothing to do = no error
1104 return execute_sql_arr($sqlarr, $continue, $feedback);
1108 * This function will create the index in the table passed as arguments
1109 * Before creating the index, the function will check it doesn't exists
1111 * @uses $CFG, $db
1112 * @param XMLDBTable table object (just the name is mandatory)
1113 * @param XMLDBIndex index object (full specs are required)
1114 * @param boolean continue to specify if must continue on error (true) or stop (false)
1115 * @param boolean feedback to specify to show status info (true) or not (false)
1116 * @return boolean true on success, false on error
1118 function add_index($table, $index, $continue=true, $feedback=true) {
1120 global $CFG, $db;
1122 $status = true;
1124 if (strtolower(get_class($table)) != 'xmldbtable') {
1125 return false;
1127 if (strtolower(get_class($index)) != 'xmldbindex') {
1128 return false;
1131 /// Check index doesn't exist
1132 if (index_exists($table, $index)) {
1133 debugging('Index ' . $index->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
1134 return true; //Index exists, nothing to do
1137 if(!$sqlarr = $table->getAddIndexSQL($CFG->dbtype, $CFG->prefix, $index, false)) {
1138 return true; //Empty array = nothing to do = no error
1141 return execute_sql_arr($sqlarr, $continue, $feedback);
1145 * This function will drop the index in the table passed as arguments
1146 * Before dropping the index, the function will check it exists
1148 * @uses $CFG, $db
1149 * @param XMLDBTable table object (just the name is mandatory)
1150 * @param XMLDBIndex index object (full specs are required)
1151 * @param boolean continue to specify if must continue on error (true) or stop (false)
1152 * @param boolean feedback to specify to show status info (true) or not (false)
1153 * @return boolean true on success, false on error
1155 function drop_index($table, $index, $continue=true, $feedback=true) {
1157 global $CFG, $db;
1159 $status = true;
1161 if (strtolower(get_class($table)) != 'xmldbtable') {
1162 return false;
1164 if (strtolower(get_class($index)) != 'xmldbindex') {
1165 return false;
1168 /// Check index exists
1169 if (!index_exists($table, $index)) {
1170 debugging('Index ' . $index->getName() . ' do not exist. Delete skipped', DEBUG_DEVELOPER);
1171 return true; //Index doesn't exist, nothing to do
1174 if(!$sqlarr = $table->getDropIndexSQL($CFG->dbtype, $CFG->prefix, $index, false)) {
1175 return true; //Empty array = nothing to do = no error
1178 return execute_sql_arr($sqlarr, $continue, $feedback);
1182 * This function will rename the index in the table passed as arguments
1183 * Before renaming the index, the function will check it exists
1184 * Experimental. Shouldn't be used at all!
1186 * @uses $CFG, $db
1187 * @param XMLDBTable table object (just the name is mandatory)
1188 * @param XMLDBIndex index object (full specs are required)
1189 * @param string new name of the index
1190 * @param boolean continue to specify if must continue on error (true) or stop (false)
1191 * @param boolean feedback to specify to show status info (true) or not (false)
1192 * @return boolean true on success, false on error
1194 function rename_index($table, $index, $newname, $continue=true, $feedback=true) {
1196 global $CFG, $db;
1198 debugging('rename_index() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER);
1200 $status = true;
1202 if (strtolower(get_class($table)) != 'xmldbtable') {
1203 return false;
1205 if (strtolower(get_class($index)) != 'xmldbindex') {
1206 return false;
1209 /// Check index exists
1210 if (!index_exists($table, $index)) {
1211 debugging('Index ' . $index->getName() . ' do not exist. Rename skipped', DEBUG_DEVELOPER);
1212 return true; //Index doesn't exist, nothing to do
1215 /// Check newname isn't empty
1216 if (!$newname) {
1217 debugging('New name for index ' . $index->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
1218 return true; //Index doesn't exist, nothing to do
1221 if(!$sqlarr = $table->getRenameIndexSQL($CFG->dbtype, $CFG->prefix, $index, $newname, false)) {
1222 debugging('Some DBs do not support index renaming (MySQL). Rename skipped', DEBUG_DEVELOPER);
1223 return true; //Empty array = nothing to do = no error
1226 return execute_sql_arr($sqlarr, $continue, $feedback);
1229 /* trys to change default db encoding to utf8, if empty db
1231 function change_db_encoding() {
1232 global $CFG, $db;
1233 // try forcing utf8 collation, if mysql db and no tables present
1234 if (($CFG->dbfamily=='mysql') && !$db->Metatables()) {
1235 $SQL = 'ALTER DATABASE '.$CFG->dbname.' CHARACTER SET utf8';
1236 execute_sql($SQL, false); // silent, if it fails it fails
1237 if (setup_is_unicodedb()) {
1238 configure_dbconnection();