Hi ...
I'm aware that this is not 100% Cake specific ... but I got to deal
with it atm in my Cake project so I hope you guys can help me out.
What I got here is:
A table containing around 1000 jobs (table framejobs) that need to be
in a specific order. For that reason I created a table column called
jobcolumn_position which I made a UNIQUE INDEX. Storage is MyISAM.
Version is 1.2.0.5875 pre-beta.
So if I want to add another job I use the beforeSave callback to get
the highest jobcolumn_position, add 1 to it and save the latest
data ... like this (in model):
function beforeSave()
{
$this->query("LOCK TABLE {$this->table} AS `Framejob` WRITE,{$this-
>table} WRITE");
$this->data['Framejob']['modified'] = date("Y-m-d
H:i:s");
//new job create or column move!
if (!isset($this->data['Framejob']['jobcolumn_position']))
{
$this->data['Framejob']['jobcolumn_position'] =>$this-
>getNextPos($this->data['Framejob']['jobcolumn_id']);
}
}
function afterSave()
{$this->query("UNLOCK TABLES"); }
/**
* @desc get next free position for this season
*/
function getNextPos($jobcolumn_id)
{
$result = $this->field('jobcolumn_position','jobcolumn_id = '.
$jobcolumn_id,'jobcolumn_position DESC');
if (!empty($result) || $result == 0)
{
$position = $result;
$position++;
return $position;
}
else
{
$position = 0;
return $position;
}
}
So all this works nicely when inserting data but the problems arise
when I'm deleting entries ... I need to renumber alls jobs "above" the
deleted jobs jobcolumn_position so I don't get a hole in the
enumeration.
So
function beforeDelete()
{
$this->query("LOCK TABLE {$this->table} AS `Framejob` WRITE,
{$this->table} WRITE");
$this->read('',$this->id);
$this->query("UPDATE $this->table SET jobcolumn_position =
jobcolumn_position -1
WHERE jobcolumn_id = '".$this->data['Framejob']
['jobcolumn_id']."'
AND jobcolumn_position >= '".$this-
>data['Framejob']['jobcolumn_position']."'
ORDER BY jobcolumn_position ASC
");
return parent::beforeDelete();
}
function afterDelete()
{
$this->query("UNLOCK TABLES");
}
All this also works nicely when I manually add and remove entries ...
I wrote a script though that insert and deletes a few 1000 entries at
random and there it freaks out on me. I'm getting MySQL errors on
"duplicate entry on key jobcolumn_position" ...
So something wrong with the locking I suppose ... but then all this
locking seems to be terrible in style. Is there any other way to
handle a situation like this nice and easy without hacks like this? It
should be a something that is encountered often in web apps?
Thanks in advance for any advices ...
Best wishes
wirtsi
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
