Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?
- include "common.inc.php";
- include "header.inc.php";
- ?>
- <h2>MySQL Strict Mode Column Compatiblility</h2>
- <?
- $r_resultTables = dbQuery("SHOW TABLES");
- echo '<textarea style="width: 95%; height: 80vh;">';
- while ($a_rowTable = dbFetchRow($r_resultTables)) {
- $table = dbEscape($a_rowTable[0]);
- $tableNameAdded = false;
- $r_result = dbQuery("SHOW FULL FIELDS FROM `$table`");
- while ($a_row = dbFetchAssoc($r_result)) {
- $comment = null;
- $default = (($a_row['Default'] != NULL) ? "DEFAULT '" . $a_row['Default'] . "'" : '');
- $extra = null;
- $type = strtok($a_row['Type'],'(');
- $field = dbEscape($a_row['Field']);
- // Is the field allowed to be null?
- if ($a_row['Null'] == 'YES') {
- $nullable = 'NULL';
- } else {
- $nullable = 'NOT NULL';
- }
- // skip nullable fields with no set default (they will default to NULL)
- if (($a_row['Default'] === NULL) AND ($a_row['Null'] == 'YES')) {
- continue;
- }
- // skip PK fields
- if ($a_row['Key'] == 'PRI') { continue; }
- // fields that need a set default if they don't have one
- if ($a_row['Default'] === NULL) {
- switch ($type) {
- case 'integer':
- case 'int':
- case 'smallint':
- case 'tinyint':
- case 'mediumint':
- case 'bigint':
- case 'decimal':
- case 'numeric':
- case 'float':
- case 'double':
- $comment = "Numeric field needs '0' as default";
- $default = "DEFAULT '0'";
- break;
- case 'char':
- case 'varchar':
- $comment = "(VAR)CHAR fields need empty string as default";
- $default = "DEFAULT ''";
- break;
- }
- }
- // fields that need changing anyway if they're not already nullable, or can be skipped
- switch ($type) {
- case 'datetime':
- case 'date':
- case 'timestamp':
- case 'year':
- case 'time':
- if (substr($a_row['Default'],0,2) == '00') {
- $comment = "Date/Time types cannot default to zeroes, must be NULLable";
- $extra = "UPDATE `$table` SET `$field` = NULL WHERE `$field` = '" . $a_row['Default'] . "';";
- $default = "DEFAULT NULL";
- } elseif ($a_row['Default'] == 'CURRENT_TIMESTAMP') {
- continue 2;
- } else {
- $comment = "Date/Time types should be NULLable";
- }
- $nullable = 'NULL';
- break;
- case 'enum':
- case 'set':
- continue 2; // ENUM and SET types don't need any change
- break;
- case 'tinytext':
- case 'text':
- case 'mediumtext':
- case 'longtext':
- continue 2; // TEXT types cannot have a default value
- break;
- default:
- if ($a_row['Default'] !== NULL) { continue 2; } // skip any other types that already have defaults
- break;
- }
- if (!$tableNameAdded) {
- echo "\r\n# `$table`\r\n\r\n";
- $tableNameAdded = true;
- }
- // Alter table query
- if ($comment) { echo '# `' . $field . '` - ' . $comment . "\r\n"; } else { echo "# UNKNOWN\r\n" . '# '; }
- echo "ALTER TABLE `$table` CHANGE `$field` `$field` $a_row[Type] $nullable $default;\r\n";
- if ($extra) { echo $extra . "\r\n"; }
- echo "\r\n";
- }
- }
- echo '</textarea>';
- include "footer.inc.php";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement