mysql-strictmode

strictmode.php

<?
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";