Hi,
We are inserting data into a table which has a unique index (relationA+relationB+relationC sort of thing). We are creating these rows using the standard SS method, e.g.
$array = array(
'RelationAID' => $x,
'RelationBID' => $y,
'RelationCID' => $z
);
$obj = MyObject::get()->filter($array)->first();
if( ! $obj ) {
$obj = MyObject::create($array);
}
....
$obj->write();
That works fine. However, for some reason we ended up with an entry where the columns for the relations are all 0. This means no further rows can be added to the database because, AFAIK, the way Silverstripe writes to the Database is that it firstly creates an "empty" record and then adds the details with a second write. This stops any record from being added.
What is the best way to "fix" this? I am still researching it a bit more (my assumptions may be wrong), but it would be good to get some ideas .... Here are some solutions that I thought of:
a. remove unique index because it does not work well with SS? Instead use SS core code to avoid duplicates.
b. work out why the unique entry was created in the first place and avoid it happening at all times (table locking / rollback code / switch to Postgres or whatever other more sophisticated methods are out there).
c. add a bit of code to requireDefaultRecords (or similar) to remove any empty records from time to time.
d. before creating any records, remove any "empty" ones (onBeforeWrite) that are older than a minute or so (to avoid deleting ones that are being written at the same time).
e. validate before write?
OR ....
I wish this problem could solve itself, but otherwise (d) sounds like the most practicable solution (albeit a big hack).
Has anyone dealt with this problem before?
--