Hello,
I wanted to share something I ran across and it might help others. In most joomla designs for components etc there are no real strict foreign keys and constraints and the database is usually MyIsam (which does not support this anyway). But there might by situations where it is needed to enforce integrity and set-up a InnoDB database with proper constraints and foreign keys.
In that case, you will need to add the required constraints to the database etc, but you will also need to make some changes to the code. This is from Cook 2.0 a bit easier as there is already a sub class of JTable defined of which all component tables inherited. (In Cook 1.5 you will have to create this yourself).
Anyway, there are two things you will need to add to this sub class. First you need a method which converts foreign keys to null when they are not set (Joomla default is '0'). And the 2nd thing is that you will need to override the 'store' method (to call this new method first) and then call its parent with the parameter 'true', to make sure Joomla will update the field even when it is null.
My version of these two methods are:
/**
* Method to nullify foreign key fields
*
* @access protected
*
* @return int Item id value, 0 if empty
*
*/
protected function nullifyEmptyStrings() {
$conf = & JFactory::getConfig();
$database = $conf->getValue('config.db');
$prefix = $conf->getValue('config.dbprefix');
$fields = $this->getFields();
$tablename = str_replace('#__', $prefix, $this->getTableName());
$fields = $this->getFields();
//print_r($fields);
foreach (get_object_vars($this) as $fieldName => $fieldValue) {
if (substr($fieldName, 0, 1) == '_') {
// ignore internal properties that aren't fields in the table
continue;
}
if (!isset($fields[$fieldName])) {
// ignore internal properties that aren't fields in the table
continue;
}
$fieldInfo = $fields[$fieldName];
if ($fieldInfo->Null == 'NO') {
// ignore fields that don't allow NULL
continue;
}
$query = "SELECT
1
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = '" . $database . "'
AND TABLE_NAME = '" . $tablename . "'
AND REFERENCED_TABLE_NAME IS NOT NULL";
$this->_db->setQuery($query);
$fkeys = $this->_db->loadObjectList();
if ((count($fkeys) > 0) && ($fieldValue == '0')) {
// set field value to NULL
$this->$fieldName = NULL;
}
}
}
/* Override the store method to always update fields, even if null */
public function store($updateNulls = false) {
$this->nullifyEmptyStrings();
return (parent::store(true));
}
Just to be clear: this is not a Cook issue, but more a Joomla issue (or lack of support for it), but this solution works for me.
Kind regards,
Misha
