|
Moodle
2.2.1
http://www.collinsharper.com
|
|
Moodle
2.2.1
http://www.collinsharper.com
|
00001 <?php 00002 00003 // This file is part of Moodle - http://moodle.org/ 00004 // 00005 // Moodle is free software: you can redistribute it and/or modify 00006 // it under the terms of the GNU General Public License as published by 00007 // the Free Software Foundation, either version 3 of the License, or 00008 // (at your option) any later version. 00009 // 00010 // Moodle is distributed in the hope that it will be useful, 00011 // but WITHOUT ANY WARRANTY; without even the implied warranty of 00012 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 00013 // GNU General Public License for more details. 00014 // 00015 // You should have received a copy of the GNU General Public License 00016 // along with Moodle. If not, see <http://www.gnu.org/licenses/>. 00017 00018 00029 defined('MOODLE_INTERNAL') || die(); 00030 00031 require_once($CFG->libdir.'/ddl/sql_generator.php'); 00032 00036 00037 class mssql_sql_generator extends sql_generator { 00038 00040 00041 public $statement_end = "\ngo"; // String to be automatically added at the end of each statement 00042 00043 public $number_type = 'DECIMAL'; // Proper type for NUMBER(x) in this DB 00044 00045 public $unsigned_allowed = false; // To define in the generator must handle unsigned information 00046 public $default_for_char = ''; // To define the default to set for NOT NULLs CHARs without default (null=do nothing) 00047 00048 public $specify_nulls = true; //To force the generator if NULL clauses must be specified. It shouldn't be necessary 00049 //but some mssql drivers require them or everything is created as NOT NULL :-( 00050 00051 public $sequence_extra_code = false; //Does the generator need to add extra code to generate the sequence fields 00052 public $sequence_name = 'IDENTITY(1,1)'; //Particular name for inline sequences in this generator 00053 public $sequence_only = false; //To avoid to output the rest of the field specs, leaving only the name and the sequence_name variable 00054 00055 public $enum_inline_code = false; //Does the generator need to add inline code in the column definition 00056 00057 public $add_table_comments = false; // Does the generator need to add code for table comments 00058 00059 public $concat_character = '+'; //Characters to be used as concatenation operator. If not defined 00060 //MySQL CONCAT function will be use 00061 00062 public $rename_table_sql = "sp_rename 'OLDNAME', 'NEWNAME'"; //SQL sentence to rename one table, both 00063 //OLDNAME and NEWNAME are dynamically replaced 00064 00065 public $rename_column_sql = "sp_rename 'TABLENAME.OLDFIELDNAME', 'NEWFIELDNAME', 'COLUMN'"; 00067 00068 public $drop_index_sql = 'DROP INDEX TABLENAME.INDEXNAME'; //SQL sentence to drop one index 00069 //TABLENAME, INDEXNAME are dynamically replaced 00070 00071 public $rename_index_sql = "sp_rename 'TABLENAME.OLDINDEXNAME', 'NEWINDEXNAME', 'INDEX'"; //SQL sentence to rename one index 00072 //TABLENAME, OLDINDEXNAME, NEWINDEXNAME are dynamically replaced 00073 00074 public $rename_key_sql = null; //SQL sentence to rename one key 00075 //TABLENAME, OLDKEYNAME, NEWKEYNAME are dynamically replaced 00076 00082 public function getResetSequenceSQL($table) { 00083 00084 if (is_string($table)) { 00085 $table = new xmldb_table($table); 00086 } 00087 00088 // From http://msdn.microsoft.com/en-us/library/ms176057.aspx 00089 $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'. $table->getName() . '}'); 00090 if ($value == 0) { 00091 $value = 1; 00092 } 00093 return array("DBCC CHECKIDENT ('" . $this->getTableName($table) . "', RESEED, $value)"); 00094 } 00095 00104 public function getTableName(xmldb_table $xmldb_table, $quoted=true) { 00106 if ($this->temptables->is_temptable($xmldb_table->getName())) { 00107 $tablename = $this->temptables->get_correct_name($xmldb_table->getName()); 00108 } else { 00109 $tablename = $this->prefix . $xmldb_table->getName(); 00110 } 00111 00113 if ($quoted) { 00114 $tablename = $this->getEncQuoted($tablename); 00115 } 00116 00117 return $tablename; 00118 } 00119 00120 00125 public function getCreateTempTableSQL($xmldb_table) { 00126 $this->temptables->add_temptable($xmldb_table->getName()); 00127 $sqlarr = $this->getCreateTableSQL($xmldb_table); 00128 return $sqlarr; 00129 } 00130 00135 public function getDropTempTableSQL($xmldb_table) { 00136 $sqlarr = $this->getDropTableSQL($xmldb_table); 00137 $this->temptables->delete_temptable($xmldb_table->getName()); 00138 return $sqlarr; 00139 } 00140 00144 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) { 00145 00146 switch ($xmldb_type) { 00147 case XMLDB_TYPE_INTEGER: // From http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp?frame=true 00148 if (empty($xmldb_length)) { 00149 $xmldb_length = 10; 00150 } 00151 if ($xmldb_length > 9) { 00152 $dbtype = 'BIGINT'; 00153 } else if ($xmldb_length > 4) { 00154 $dbtype = 'INTEGER'; 00155 } else { 00156 $dbtype = 'SMALLINT'; 00157 } 00158 break; 00159 case XMLDB_TYPE_NUMBER: 00160 $dbtype = $this->number_type; 00161 if (!empty($xmldb_length)) { 00163 if ($xmldb_length > 38) { 00164 $xmldb_length = 38; 00165 } 00166 $dbtype .= '(' . $xmldb_length; 00167 if (!empty($xmldb_decimals)) { 00168 $dbtype .= ',' . $xmldb_decimals; 00169 } 00170 $dbtype .= ')'; 00171 } 00172 break; 00173 case XMLDB_TYPE_FLOAT: 00174 $dbtype = 'FLOAT'; 00175 if (!empty($xmldb_decimals)) { 00176 if ($xmldb_decimals < 6) { 00177 $dbtype = 'REAL'; 00178 } 00179 } 00180 break; 00181 case XMLDB_TYPE_CHAR: 00182 $dbtype = 'NVARCHAR'; 00183 if (empty($xmldb_length)) { 00184 $xmldb_length='255'; 00185 } 00186 $dbtype .= '(' . $xmldb_length . ')'; 00187 break; 00188 case XMLDB_TYPE_TEXT: 00189 $dbtype = 'NTEXT'; 00190 break; 00191 case XMLDB_TYPE_BINARY: 00192 $dbtype = 'IMAGE'; 00193 break; 00194 case XMLDB_TYPE_DATETIME: 00195 $dbtype = 'DATETIME'; 00196 break; 00197 } 00198 return $dbtype; 00199 } 00200 00206 public function getDropFieldSQL($xmldb_table, $xmldb_field) { 00207 $results = array(); 00208 00210 $tablename = $this->getTableName($xmldb_table); 00211 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 00212 00214 if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) { 00215 $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname; 00216 } 00217 00219 if ($drop_check = $this->getDropEnumSQL($xmldb_table, $xmldb_field)) { 00220 $results = array_merge($results, $drop_check); 00221 } 00222 00224 $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname; 00225 00226 return $results; 00227 } 00228 00235 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { 00236 00237 $results = array(); //Array where all the sentences will be stored 00238 00246 if ($xmldb_field->getName() == 'id') { 00247 return array(); 00248 } 00249 00251 $results = array_merge($results, parent::getRenameFieldSQL($xmldb_table, $xmldb_field, $newname)); 00252 00253 return $results; 00254 } 00255 00259 public function getRenameTableExtraSQL($xmldb_table, $newname) { 00260 00261 $results = array(); 00262 00263 $newt = new xmldb_table($newname); //Temporal table for name calculations 00264 00265 $oldtablename = $this->getTableName($xmldb_table); 00266 $newtablename = $this->getTableName($newt); 00267 00269 $oldconstraintprefix = $this->getNameForObject($xmldb_table->getName(), ''); 00270 $newconstraintprefix = $this->getNameForObject($newt->getName(), '', ''); 00271 00272 if ($constraints = $this->getCheckConstraintsFromDB($xmldb_table)) { 00273 foreach ($constraints as $constraint) { 00275 $results[] = 'ALTER TABLE ' . $newtablename . ' DROP CONSTRAINT ' . $constraint->name; 00276 } 00277 } 00278 00279 return $results; 00280 } 00281 00285 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 00286 00287 $results = array(); 00288 00290 $tablename = $xmldb_table->getName(); 00291 $fieldname = $xmldb_field->getName(); 00292 00294 $meta = $this->mdb->get_columns($tablename); 00295 $metac = $meta[$fieldname]; 00296 $oldmetatype = $metac->meta_type; 00297 00298 $oldlength = $metac->max_length; 00299 $olddecimals = empty($metac->scale) ? null : $metac->scale; 00300 $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; 00301 //$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':'); 00302 00303 $typechanged = true; //By default, assume that the column type has changed 00304 $lengthchanged = true; //By default, assume that the column length has changed 00305 00307 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') || 00308 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') || 00309 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') || 00310 ($xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C') || 00311 ($xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X') || 00312 ($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) { 00313 $typechanged = false; 00314 } 00315 00318 if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') { 00319 if ($xmldb_field->getLength() > 9) { // Convert our new lenghts to detailed meta types 00320 $newmssqlinttype = 'I8'; 00321 } else if ($xmldb_field->getLength() > 4) { 00322 $newmssqlinttype = 'I'; 00323 } else { 00324 $newmssqlinttype = 'I2'; 00325 } 00326 if ($metac->type == 'bigint') { // Convert current DB type to detailed meta type (our metatype is not enough!) 00327 $oldmssqlinttype = 'I8'; 00328 } else if ($metac->type == 'smallint') { 00329 $oldmssqlinttype = 'I2'; 00330 } else { 00331 $oldmssqlinttype = 'I'; 00332 } 00333 if ($newmssqlinttype != $oldmssqlinttype) { // Compare new and old meta types 00334 $typechanged = true; // Change in meta type means change in type at all effects 00335 } 00336 } 00337 00340 if ($xmldb_field->getLength() == $oldlength) { 00341 $lengthchanged = false; 00342 } 00343 00345 if ($typechanged || $lengthchanged) { 00346 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); 00347 } 00348 00352 $multiple_alter_stmt = array(); 00353 $targettype = $xmldb_field->getType(); 00354 00355 if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'I') { // integer to text 00356 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 00357 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 00358 $multiple_alter_stmt[0]->length = 255; 00359 00360 } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'N') { // decimal to text 00361 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 00362 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 00363 $multiple_alter_stmt[0]->length = 255; 00364 00365 } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'F') { // float to text 00366 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 00367 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 00368 $multiple_alter_stmt[0]->length = 255; 00369 00370 } else if ($targettype == XMLDB_TYPE_INTEGER && $oldmetatype == 'X') { // text to integer 00371 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 00372 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 00373 $multiple_alter_stmt[0]->length = 255; 00374 $multiple_alter_stmt[1] = new stdClass; // and also needs conversion to decimal 00375 $multiple_alter_stmt[1]->type = XMLDB_TYPE_NUMBER; // without decimal positions 00376 $multiple_alter_stmt[1]->length = 10; 00377 00378 } else if ($targettype == XMLDB_TYPE_NUMBER && $oldmetatype == 'X') { // text to decimal 00379 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 00380 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 00381 $multiple_alter_stmt[0]->length = 255; 00382 00383 } else if ($targettype == XMLDB_TYPE_FLOAT && $oldmetatype == 'X') { // text to float 00384 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 00385 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 00386 $multiple_alter_stmt[0]->length = 255; 00387 } 00388 00390 if (empty($multiple_alter_stmt)) { // Direct implicit conversion allowed, launch it 00391 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 00392 00393 } else { // Direct implicit conversion forbidden, use the intermediate ones 00394 $final_type = $xmldb_field->getType(); // Save final type and length 00395 $final_length = $xmldb_field->getLength(); 00396 foreach ($multiple_alter_stmt as $alter) { 00397 $xmldb_field->setType($alter->type); // Put our intermediate type and length and alter to it 00398 $xmldb_field->setLength($alter->length); 00399 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 00400 } 00401 $xmldb_field->setType($final_type); // Set the final type and length and alter to it 00402 $xmldb_field->setLength($final_length); 00403 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 00404 } 00405 00407 if ($typechanged || $lengthchanged) { 00408 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); 00409 } 00410 00412 return $results; 00413 } 00414 00418 public function getModifyDefaultSQL($xmldb_table, $xmldb_field) { 00421 00422 $results = array(); 00423 00425 if ($xmldb_field->getDefault() === null) { 00426 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop but, under some circumstances, re-enable 00427 $default_clause = $this->getDefaultClause($xmldb_field); 00428 if ($default_clause) { //If getDefaultClause() it must have one default, create it 00429 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify 00430 } 00431 } else { 00432 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop (only if exists) 00433 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify 00434 } 00435 00436 return $results; 00437 } 00438 00445 public function getDropEnumSQL($xmldb_table, $xmldb_field) { 00447 if ($check_constraints = $this->getCheckConstraintsFromDB($xmldb_table, $xmldb_field)) { 00448 $check_constraint = array_shift($check_constraints); 00449 $constraint_name = strtolower($check_constraint->name); 00450 00451 return array('ALTER TABLE ' . $this->getTableName($xmldb_table) . 00452 ' DROP CONSTRAINT ' . $constraint_name); 00453 } else { 00454 return array(); 00455 } 00456 } 00457 00462 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { 00464 00465 $results = array(); 00466 00468 $tablename = $this->getTableName($xmldb_table); 00469 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 00470 00472 $default_clause = $this->getDefaultClause($xmldb_field); 00473 if ($default_clause) { 00475 $sql = 'ALTER TABLE ' . $tablename . ' ADD' . $default_clause . ' FOR ' . $fieldname; 00476 $results[] = $sql; 00477 } 00478 00479 return $results; 00480 } 00481 00486 public function getDropDefaultSQL($xmldb_table, $xmldb_field) { 00488 00489 $results = array(); 00490 00492 $tablename = $this->getTableName($xmldb_table); 00493 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 00494 00496 if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) { 00497 $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname; 00498 } 00499 00500 return $results; 00501 } 00502 00508 public function getDefaultConstraintName($xmldb_table, $xmldb_field) { 00509 00511 $tablename = $this->getTableName($xmldb_table); 00512 $fieldname = $xmldb_field->getName(); 00513 00515 if ($default = $this->mdb->get_record_sql("SELECT id, object_name(cdefault) AS defaultconstraint 00516 FROM syscolumns 00517 WHERE id = object_id(?) 00518 AND name = ?", array($tablename, $fieldname))) { 00519 return $default->defaultconstraint; 00520 } else { 00521 return false; 00522 } 00523 } 00524 00535 public function getCheckConstraintsFromDB($xmldb_table, $xmldb_field = null) { 00536 00537 00538 $results = array(); 00539 00540 $tablename = $this->getTableName($xmldb_table); 00541 00542 if ($constraints = $this->mdb->get_records_sql("SELECT o.name, c.text AS description 00543 FROM sysobjects o, 00544 sysobjects p, 00545 syscomments c 00546 WHERE p.id = o.parent_obj 00547 AND o.id = c.id 00548 AND o.xtype = 'C' 00549 AND p.name = ?", array($tablename))) { 00550 foreach ($constraints as $constraint) { 00551 $results[$constraint->name] = $constraint; 00552 } 00553 } 00554 00556 if ($xmldb_field) { 00557 $filtered_results = array(); 00558 $filter = $xmldb_field->getName(); 00560 foreach ($results as $key => $result) { 00561 $description = trim(preg_replace('/[\(\)]/', '', $result->description)); // Parenthesis out & trim 00563 if (preg_match("/^\[{$filter}\]/i", $description)) { 00564 $filtered_results[$key] = $result; 00565 } 00566 } 00568 $results = $filtered_results; 00569 } 00570 00571 return $results; 00572 } 00573 00589 public function getNameForObject($tablename, $fields, $suffix='') { 00590 if ($this->temptables->is_temptable($tablename)) { // Is temp table, inject random field names 00591 $random = strtolower(random_string(12)); // 12cc to be split in 4 parts 00592 $fields = $fields . ', ' . implode(', ', str_split($random, 3)); 00593 } 00594 return parent::getNameForObject($tablename, $fields, $suffix); // Delegate to parent (common) algorithm 00595 } 00596 00602 public function isNameInUse($object_name, $type, $table_name) { 00603 switch($type) { 00604 case 'seq': 00605 case 'trg': 00606 case 'pk': 00607 case 'uk': 00608 case 'fk': 00609 case 'ck': 00610 if ($check = $this->mdb->get_records_sql("SELECT name 00611 FROM sysobjects 00612 WHERE lower(name) = ?", array(strtolower($object_name)))) { 00613 return true; 00614 } 00615 break; 00616 case 'ix': 00617 case 'uix': 00618 if ($check = $this->mdb->get_records_sql("SELECT name 00619 FROM sysindexes 00620 WHERE lower(name) = ?", array(strtolower($object_name)))) { 00621 return true; 00622 } 00623 break; 00624 } 00625 return false; //No name in use found 00626 } 00627 00631 public function getCommentSQL($xmldb_table) { 00632 return array(); 00633 } 00634 00635 public function addslashes($s) { 00636 // do not use php addslashes() because it depends on PHP quote settings! 00637 $s = str_replace("'", "''", $s); 00638 return $s; 00639 } 00640 00644 public static function getReservedWords() { 00647 $reserved_words = array ( 00648 'add', 'all', 'alter', 'and', 'any', 'as', 'asc', 'authorization', 00649 'avg', 'backup', 'begin', 'between', 'break', 'browse', 'bulk', 00650 'by', 'cascade', 'case', 'check', 'checkpoint', 'close', 'clustered', 00651 'coalesce', 'collate', 'column', 'commit', 'committed', 'compute', 00652 'confirm', 'constraint', 'contains', 'containstable', 'continue', 00653 'controlrow', 'convert', 'count', 'create', 'cross', 'current', 00654 'current_date', 'current_time', 'current_timestamp', 'current_user', 00655 'cursor', 'database', 'dbcc', 'deallocate', 'declare', 'default', 'delete', 00656 'deny', 'desc', 'disk', 'distinct', 'distributed', 'double', 'drop', 'dummy', 00657 'dump', 'else', 'end', 'errlvl', 'errorexit', 'escape', 'except', 'exec', 00658 'execute', 'exists', 'exit', 'external', 'fetch', 'file', 'fillfactor', 'floppy', 00659 'for', 'foreign', 'freetext', 'freetexttable', 'from', 'full', 'function', 00660 'goto', 'grant', 'group', 'having', 'holdlock', 'identity', 'identitycol', 00661 'identity_insert', 'if', 'in', 'index', 'inner', 'insert', 'intersect', 'into', 00662 'is', 'isolation', 'join', 'key', 'kill', 'left', 'level', 'like', 'lineno', 00663 'load', 'max', 'min', 'mirrorexit', 'national', 'nocheck', 'nonclustered', 00664 'not', 'null', 'nullif', 'of', 'off', 'offsets', 'on', 'once', 'only', 'open', 00665 'opendatasource', 'openquery', 'openrowset', 'openxml', 'option', 'or', 'order', 00666 'outer', 'over', 'percent', 'perm', 'permanent', 'pipe', 'pivot', 'plan', 'precision', 00667 'prepare', 'primary', 'print', 'privileges', 'proc', 'procedure', 'processexit', 00668 'public', 'raiserror', 'read', 'readtext', 'reconfigure', 'references', 00669 'repeatable', 'replication', 'restore', 'restrict', 'return', 'revoke', 00670 'right', 'rollback', 'rowcount', 'rowguidcol', 'rule', 'save', 'schema', 00671 'select', 'serializable', 'session_user', 'set', 'setuser', 'shutdown', 'some', 00672 'statistics', 'sum', 'system_user', 'table', 'tape', 'temp', 'temporary', 00673 'textsize', 'then', 'to', 'top', 'tran', 'transaction', 'trigger', 'truncate', 00674 'tsequal', 'uncommitted', 'union', 'unique', 'update', 'updatetext', 'use', 00675 'user', 'values', 'varying', 'view', 'waitfor', 'when', 'where', 'while', 00676 'with', 'work', 'writetext' 00677 ); 00678 return $reserved_words; 00679 } 00680 }
1.7.6.1 
