[Typo3-dev] automatic mysql db constraints for tce, etc

[Martin Kutschker]

Hi!

I've been playing around with code to avoid data corruption in the database. Corruption may occur with Mysql when you're trying to store data that's outside of the columns range (eg string too long or integer too large). Other databases may reject the insert/update statement.

The following function checks all tables and fields of the current database. The output is an asociatove array, to be used eg in conjunction with TCA. It is meant to be cached in typo3conf.

function mysql_db_typeinfo() {
// storage size in bytes
$int = array('tinyint'=>1, 'smallint'=>2, 'mediumint'=>3, 'int'=>4 , 'bigint'=>8);
// size of data length storage in bytes 
$text = array('tinytext'=>1, 'text'=>2, 'mediumtext'=>3,
'tinyblob'=>1, 'blob'=>2, 'mediumblob'=>3, 'longblob'=> 4);
// PHP seems to be able to handle at least integers of that size (platform dependent)
$max_php_bit = 30;
// $max_php_bit = 32;

$prefix = '$TCA[\'db\']';

echo $prefix." = Array (\n";

/*
* lists all columns of all tables
*/
$res_t = mysql_query('SHOW tables');
while($row_t = mysql_fetch_row($res_t)) {
$table = $row_t[0];
echo "\t'$table' => Array (\n";

$res_c = mysql_query('SHOW columns FROM '.$table);
// Field, Type
while($row_c = mysql_fetch_assoc($res_c)) {
// we skip some fields to reduce memory-footprint 

// skip well-known fields
if ($row_c['Field'] == 'uid') continue;
if ($row_c['Field'] == 'pid') continue;
// skip standard fields (in BE TCA is omni-present)
if ($row_c['Field'] == $TCA[$table]['ctrl']['tstamp']) continue;
if ($row_c['Field'] == $TCA[$table]['ctrl']['crdate']) continue;
if ($row_c['Field'] == $TCA[$table]['ctrl']['delete']) continue;
if ($row_c['Field'] == $TCA[$table]['ctrl']['enablecolumns']['disabled']) continue;

echo "\t\t'${row_c['Field']}' => Array (\n";

list($type,$mod) = split(' ',$row_c['Type'],2);
if (strstr($type,'int')) { // integer: ...int

echo "\t\t\t'type' => 'int',\n";

$type = substr($type,0,strpos($type,'(')); // strip output length
$bits = $int[$type]*8;
if ($bits <= $max_php_bit) {
if (strstr($mod,'unsigned')) {
echo "\t\t\t'min' => '0',\n";
echo "\t\t\t'max' => '".(pow(2,$bits) - 1)."',\n";
} else {
$range = pow(2,$bits-1);
echo "\t\t\t'min' => '".($range * (-1))."',\n";
echo "\t\t\t'max' => '".($range - 1)."',\n";
}
}
} elseif (strstr($type,'text') || strstr($type,'blob')) { // text: ...text, ...blob
echo "\t\t\t'type' => 'text',\n";

$bytes = $text[$type];
if ($bytes < 5) { // PHP seems to be able to handle at least integers of that size
echo "\t\t\t'len' => '".(pow(2,$bytes*8) - 1)."',\n";
}
} elseif (strstr($type,'char')) { // char: char, varchar
echo "\t\t\t'type' => 'text',\n";

ereg('([0-9]+)',$type,$match); // determine length
echo "\t\t\t'len' => '{$match[1]}',\n";
} elseif ($type == 'year') {
echo "\t\t\t'type' => 'int',\n";
echo "\t\t\t'min' => 1901,\n";
echo "\t\t\t'max' => 2155,\n";
} else {
// float: -3.402823466E+38 to 3.402823466E+38, the unsigned variant just disallows negative values
// beware: FLOAT(prec), prec<=24 = float, prec>24 = double
// double, double precision, real: -1.7976931348623157E+308 to 2.2250738585072014E-308
// decimal, dec, numeric, fixed
// beware: pre 3.23 counts sign and dot in DECIMAL(M)
// date: 1000-01-01 to 9999-12-31
// datetime: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
// timestamp: 1970-01-01 00:00:00 to 2037-... (0 to ?)
// time: -838:59:59 to 838:59:59
// enum: 0 to 65535
// set: 0 to 64

// bit, bool, boolean are synonyms for tinyint (and reported as such)
}

echo "\t\t),\n";
}
mysql_free_result($res_c);

echo "\t),\n";
}

echo ");\n";
}

A few points where this data could be used, found by grepping 3.5:

t3lib_BEfunc:

function DBcompileInsert($table,$fields_values,$slash=1)
function DBcompileUpdate($table,$where,$fields_values,$slash=1)

standard functions - might be nice to make them do the truncation (only when a flag is set?)
beware: truncation will prevent only a) DB errors and b) broken multi-byte characters - the caller really shoud check data length before

t3lib_tcemain.php:

function insertDB($table,$id,$fieldArray)
function updateDB($table,$id,$fieldArray)

same considerations as above (though with a more limited scope)

t3lib_install:

function checkTheDatabase()

here the admin account info is entered (username and password might be too long)
the code handling the form MUST check the length - we don't want a truncated login/password pair, do we?

tslib_content.php:

function DBcompileInsert($table,$fp,$vp)
function DBgetUpdate($table, $uid, $dataArr, $fieldList, $addSlashes=0)

Masi