$smcFunc['db_create_table'] - bug?

[inter]

Code Select Expand

$columns = array(
array(
'name' => 'id',
'type' => 'int',
'size' => 10,
'unsigned' => TRUE,
'auto' => TRUE,
),
);

$indexes = array(
array(
'name' => 'keys',
'type' => 'unique',
'columns' => array('left_key', 'right_key'),
),
);

$smcFunc['db_create_table']('{db_prefix}my', $columns, $indexes, array());

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keys (left_key,right_key), ...

Code Select Expand

$columns = array(
array(
'name' => 'id',
'type' => 'int',
'size' => 10,
'unsigned' => TRUE,
'auto' => TRUE,
),
);

$indexes = array(
array(
'name' => '`keys`',
'type' => 'unique',
'columns' => array('left_key', 'right_key'),
),
);

$smcFunc['db_create_table']('{db_prefix}my', $columns, $indexes, array());

Good

[emanuele]

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

I would discourage the use of reserved words for names of columns or indexes.

[Arantor]

Using reserved words is always a minefield - especially because you have three different systems to deal with (and the suggested code probably wouldn't work properly on the other systems as they do things differently anyway)

So ultimately, we've taken the decision that this is not going to be changed; just because you *can* force it to use a keyword as a column or index doesn't mean you *should* especially as it means that in a query you should generally backtick or otherwise escape it (which can have problems in itself)

The solution is not to use keywords in the first place