Database headache - row gets mysteriously deleted.
Posted: Wed Jul 02, 2003 12:48 pm
EDIT: scroll down here instead.
Allright. Nothing is magic, but I'm burned out on this bug of mine, so I blame it on the universe.
Some tables:
The following paragraph has nothing to do with my problem, so you could skip it if you're skimming.
Now, here comes a fat confession. When I started playing with this I called the 'tree' table Todo, because that whas what it was all about. Then came permissions, then came files. Along this unpredicted development we changed terms on what all this is called.
First those entries were called Todo's. Then they were called Topics. Nowadays, when the system holds files these entries are called Items, to let us mount anything (small texts, files, mini-forums and stuff) onto this tree structure. The bad thing is that terminology has changed and been updated in the user interface, but not in the database nor in the PHP code. I want to clean this mess up, but haven't got time.
Because of all this, TodoID, TopicID and ItemID are exactly the same things.
I am Using InnoDB tables to be able to cascade or restrict deletes.
A user needs an entry in PermRead to be able to see an Item with a corresponding ID. An entry in PermWrite can only exist if a corresponding entry is present in PermRead.
These permissions can be SPREAD up (towards root) or down (away from root). Everything works as expected except for when spreading no permissions downwards for a user.
Here are the functions that are use to spread down:And here is the initiating function call that occurs when submitting permission spreading either up or down:
These checkboxes are arranged like this: (I left out as much as possible of the style, JS and stuff that has nothing to do with the problem)
Finally! I've come down do specifying my problem:
When I choose to remove all permissions for a user and click on "spread down" the function SetPermissionsSingleUser does this for that user on all the children rows:
That works. But also, and I don't understand why, my PermWrite (UserID=30) on the rows affected by the spread are deleted. These functions are the only ones executed and I certainly don't get it ?????
The 'begin', 'commit' and 'rollback' queries are for starting, and ending transactions in InnoDB tables. I tried spreading without those queries, but the problem was still there.
It is not a checkbox error, I have checked that. There are no other queries executing deletes on these tables (PermRead,PermWrite), and what is echoed is the same TodoIDs and UserIDs that are put into the query.
I want my vacation....
Allright. Nothing is magic, but I'm burned out on this bug of mine, so I blame it on the universe.
Some tables:
Code: Select all
CREATE TABLE Users (
ID int(11) NOT NULL auto_increment,
Username varchar(30) NOT NULL,
Password tinytext NOT NULL,
AliasName tinytext NOT NULL,
email tinytext NOT NULL,
EmailReminded int(1) NOT NULL default '0',
IsAdmin int(1) unsigned NOT NULL default '0',
PRIMARY KEY (ID),
UNIQUE KEY Username (Username)
) TYPE=InnoDB COMMENT='Table with todo users';
#Note that in the table 'todo' there are IDs and Parents
#Parent points to ID of another row in the same table
#This forms a tree structure looking alot like a filesystem tree
#The 'root parent' does not exist and has ID 0, that is,
#Any row with Parent=0 is mounted on root.
CREATE TABLE IF NOT EXISTS Todo (
ID int(11) NOT NULL auto_increment,
OwnerID int(11) NOT NULL default '0',
Txt tinytext NOT NULL,
Description text,
Priority smallint(6) default NULL,
Parent int(11) NOT NULL default '0',
Finished smallint(1) NOT NULL default '0',
Archived smallint(1) NOT NULL default '0',
CreateDate datetime NOT NULL ,
DeadLine date default NULL,
IsFile int(1) default NULL,
PRIMARY KEY (ID),
Index(OwnerID),
FOREIGN KEY (OwnerID) REFERENCES `Users` (ID) on delete restrict
) TYPE=InnoDB COMMENT='Tasks to be done';
CREATE TABLE PermRead (
ID int(11) NOT NULL auto_increment,
TodoID int(11) NOT NULL default '0',
UserID int(11) NOT NULL default '0',
PRIMARY KEY (ID),
KEY TodoID (TodoID),
KEY UserID (UserID),
FOREIGN KEY (`TodoID`) REFERENCES `Todo` (`ID`) ON DELETE CASCADE,
FOREIGN KEY (`UserID`) REFERENCES `Users` (`ID`) ON DELETE CASCADE
) TYPE=InnoDB COMMENT='Who can view what?';
CREATE TABLE PermWrite (
ID int(11) NOT NULL auto_increment,
TodoID int(11) NOT NULL default '0',
UserID int(11) NOT NULL default '0',
PRIMARY KEY (ID),
KEY TodoID (TodoID),
KEY UserID (UserID),
FOREIGN KEY (`TodoID`) REFERENCES `PermRead` (`TodoID`) ON DELETE CASCADE,
FOREIGN KEY (`UserID`) REFERENCES `Users` (`ID`) ON DELETE CASCADE
) TYPE=InnoDB COMMENT='Who may change what?';Now, here comes a fat confession. When I started playing with this I called the 'tree' table Todo, because that whas what it was all about. Then came permissions, then came files. Along this unpredicted development we changed terms on what all this is called.
First those entries were called Todo's. Then they were called Topics. Nowadays, when the system holds files these entries are called Items, to let us mount anything (small texts, files, mini-forums and stuff) onto this tree structure. The bad thing is that terminology has changed and been updated in the user interface, but not in the database nor in the PHP code. I want to clean this mess up, but haven't got time.
Because of all this, TodoID, TopicID and ItemID are exactly the same things.
I am Using InnoDB tables to be able to cascade or restrict deletes.
A user needs an entry in PermRead to be able to see an Item with a corresponding ID. An entry in PermWrite can only exist if a corresponding entry is present in PermRead.
These permissions can be SPREAD up (towards root) or down (away from root). Everything works as expected except for when spreading no permissions downwards for a user.
Here are the functions that are use to spread down:
Code: Select all
<?php
//all of the echoes are just for debugging.
function SetPermissionsSingleUser($ChkWrite, $ChkRead, $ItemID, $UserID){
mysql_query('begin');
if (mysql_query_got_row('select t1.TodoID from Todo t2
left join PermWrite t1 on t1.TodoID=t2.ID
where t1.TodoID = ' . $ItemID . '
and t1.UserID = ' . $_SESSION['LoggedInID'] . '
and t2.OwnerID != ' . $UserID)){
if ($ChkRead == 'on'){
mysql_query('insert into PermRead set TodoID ='''.$ItemID.''' , UserID='''.$UserID.'''');
echo "<br>sets permRead for user $UserID. ".mysql_error();
//PermWrite depends on PermRead, so we only need to ask this question here:
if ($ChkWrite == 'on'){
mysql_query('insert into PermWrite set TodoID ='''.$ItemID.''' , UserID='''.$UserID.'''');
echo "<br>sets permWrite for user $UserID. ".mysql_error();
}else{
mysql_query('delete from PermWrite where TodoID ='''.$ItemID.''' and UserID='''.$UserID.'''');
echo '<br>deletes permWrite where TodoID ='''.$ItemID.''' and UserID='''.$UserID.''''.mysql_error();
}
}else{
mysql_query('delete from PermRead where TodoID ='''.$ItemID.''' and UserID='''.$UserID.'''');
echo '<br>deletes permRead where TodoID ='''.$ItemID.''' and UserID='''.$UserID.''''.mysql_error();
}
mysql_query('commit');
}else{
mysql_query('rollback');
}
}
function SpreadPermDown(&$ChkWrite, &$ChkRead, $ItemID, $SpreadToUserID ){
$Items = GetWriteChildren($ItemID,$_SESSION['LoggedInID']);
if (count($Items)){
foreach ($Items as $ItemArr){
$ItemID = $ItemArr['ID'];
echo "<br>ChkWrite: $ChkWrite ChkRead: $ChkRead ItemID: $ItemID SpreadToUserID: $SpreadToUserID";
SetPermissionsSingleUser($ChkWrite, $ChkRead, $ItemID, $SpreadToUserID);
SpreadPermDown($ChkWrite, $ChkRead, $ItemID, $SpreadToUserID);
}
}
}
function SpreadPermissions(&$ArrChkWrite, &$ArrChkRead, &$ArrChkSpreadUp, &$ArrChkSpreadDown, $ItemID){
// Wrapper for the spreading permissions functions.
if (is_array($ArrChkSpreadUp)){
foreach ($ArrChkSpreadUp as $SpreadToUserID => $SpreadChk){
if ($SpreadChk == "on"){
SpreadPermReadUp($ItemID, $SpreadToUserID);
}
}
}
if (is_array($ArrChkSpreadDown)){
foreach ($ArrChkSpreadDown as $SpreadToUserID => $SpreadChk){
if ($SpreadChk == "on"){
echo "<br>ArrChkWrite[\$SpreadToUserID]: $ArrChkWrite[$SpreadToUserID]
ChkRead: $ChkRead ItemID: $ItemID SpreadToUserID: $SpreadToUserID";
SpreadPermDown($ArrChkWrite[$SpreadToUserID], $ArrChkRead[$SpreadToUserID], $ItemID, $SpreadToUserID);
}
}
}
}
?>Code: Select all
<?php
SpreadPermissions($_POST['PermWrite'], $_POST['PermRead'], $_POST['PermSpreadUp'], $_POST['PermSpreadDown'], $_GET['ID']);
?>Code: Select all
<tr>
<td>User:Heavy</td>
<td><input type="checkbox" name="AssignUserї2]"></td><td><input type="checkbox" name="PermReadї2]"></td>
<td><input type="checkbox" name="PermWriteї2]"></td><td><input type="checkbox" name="PermSpreadUpї2]"></td>
<td><input type="checkbox" name="PermSpreadDownї2]"></td>
</tr>
<!-- and so on, one for each user-->When I choose to remove all permissions for a user and click on "spread down" the function SetPermissionsSingleUser does this for that user on all the children rows:
Code: Select all
delete from PermRead where TodoID='494' and UserID='66'The 'begin', 'commit' and 'rollback' queries are for starting, and ending transactions in InnoDB tables. I tried spreading without those queries, but the problem was still there.
It is not a checkbox error, I have checked that. There are no other queries executing deletes on these tables (PermRead,PermWrite), and what is echoed is the same TodoIDs and UserIDs that are put into the query.
I want my vacation....