Database headache - row gets mysteriously deleted.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Database headache - row gets mysteriously deleted.

Post by Heavy »

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:

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?';
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. :oops:


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);
			}
		}
	}
}

?>
And here is the initiating function call that occurs when submitting permission spreading either up or down:

Code: Select all

<?php
	SpreadPermissions($_POST['PermWrite'], $_POST['PermRead'], $_POST['PermSpreadUp'], $_POST['PermSpreadDown'], $_GET['ID']);

?>
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)

Code: Select all

&lt;tr&gt;
  &lt;td&gt;User:Heavy&lt;/td&gt;
  &lt;td&gt;&lt;input type="checkbox"  name="AssignUser&#1111;2]"&gt;&lt;/td&gt;&lt;td&gt;&lt;input type="checkbox"  name="PermRead&#1111;2]"&gt;&lt;/td&gt;
  &lt;td&gt;&lt;input type="checkbox"  name="PermWrite&#1111;2]"&gt;&lt;/td&gt;&lt;td&gt;&lt;input type="checkbox"  name="PermSpreadUp&#1111;2]"&gt;&lt;/td&gt;
  &lt;td&gt;&lt;input type="checkbox"  name="PermSpreadDown&#1111;2]"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;!-- and so on, one for each user--&gt;
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:

Code: Select all

delete from PermRead where TodoID='494' and UserID='66'
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.... :cry:
Last edited by Heavy on Tue Jul 08, 2003 5:12 pm, edited 1 time in total.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Sigh!
Here is what I do:

Code: Select all

DELETE FROM PermRead WHERE
(TodoID = 492 and UserID = 66) 
or (TodoID = 493 and UserID = 66) 
or (TodoID = 497 and UserID = 66) 
or (TodoID = 494 and UserID = 66) 
or (TodoID = 495 and UserID = 66) 
or (TodoID = 496 and UserID = 66)
I refer to the post above for how the tables are arranged.
When I execute the query above from within my system, the result is ALSO just like I had ALSO executed this:

Code: Select all

#Please note that this made up query affects another table:

DELETE FROM PermWrite WHERE
(TodoID = 492 and UserID = 30) 
or (TodoID = 493 and UserID = 30) 
or (TodoID = 497 and UserID = 30) 
or (TodoID = 494 and UserID = 30) 
or (TodoID = 495 and UserID = 30) 
or (TodoID = 496 and UserID = 30)
I never run this second query.

However, if I call the first query (with PermRead) from within phpMyAdmin, it works as expected. phpMyAdmin is running on the same Linux server under the same servers; Apache, MySQL 3.23.52-Max-log, PHP 4.2.3.

The only thing left that I can blame is that MySQL does something crazy when this delete query we are talking about is preceded with other queries that mess up the result (Read: Heavy thinks there is a bug in mysql-InnoDB).

Here is a list of all queries that are run by that failing script:

Code: Select all

select UserID from Assignees where TodoID='486' and UserID='30'
SELECT DISTINCT t1.ID FROM Todo as t1 left join PermWrite as t2 on (t1.ID = t2.TodoID ) where t1.ID = '486' and t2.UserID='30'
insert into AccessLog set UserID='30', URL='/xxxxxxxxx?xxxxx=xxxxxxxxxx&ID=486', TimeStamp='1057663779', Action='Updating item'
begin
update Todo set Txt='b', Description='', Createdate='2003-07-08 11:29:39' , Priority=NULL , DeadLine=NULL where ID='486'
commit
select Parent from Todo where ID = '486' LIMIT 0,1
select Parent from Todo where ID = '484' LIMIT 0,1
select ID,Username,AliasName,email,EmailReminded from Users where IsAdmin =0
INSERT IGNORE INTO PermRead set TodoID ='486', UserID='30'
delete from PermRead where TodoID ='486' and UserID='57'
delete from PermRead where TodoID ='486' and UserID='64'
delete from PermRead where TodoID ='486' and UserID='65'
delete from PermRead where TodoID ='486' and UserID='66'
delete from PermRead where TodoID ='486' and UserID='70'
select ID,Username,AliasName,email,EmailReminded from Users where IsAdmin =0
INSERT IGNORE INTO PermWrite set TodoID ='486', UserID='30'
delete from PermWrite where TodoID ='486' and UserID='57'
delete from PermWrite where TodoID ='486' and UserID='64'
delete from PermWrite where TodoID ='486' and UserID='65'
delete from PermWrite where TodoID ='486' and UserID='66'
delete from PermWrite where TodoID ='486' and UserID='70'
delete from Assignees where TodoID ='486'
select distinct t1.UserID, t2.email, t2.AliasName from PermRead t1 left join Users t2 on t1.UserID = t2.ID where t1.TodoID=486 order by t2.AliasName, t2.email
select IsFile from Todo where ID='486' LIMIT 0,1
select ID from Mail where UserID='30' and TopicID='486' LIMIT 0,1
update Mail set UserID='30', TopicID='486' , TopicTxt='b' where ID=37
INSERT IGNORE INTO MailReasons set MailID=37, Reason='Permissions', TimeStamp=1057663779
INSERT IGNORE INTO MailAdressees set MailID=37, UserID=30
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 486
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 492
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 493
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 494
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 495
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 496
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 497
begin

#Here is the failing one:

DELETE FROM PermRead WHERE(TodoID = 492 and UserID = 66) or (TodoID = 493 and UserID = 66) or (TodoID = 497 and UserID = 66) or (TodoID = 494 and UserID = 66) or (TodoID = 495 and UserID = 66) or (TodoID = 496 and UserID = 66)
commit
There are no missing queries. The queries are now run through a DB class that is in debug mode and outputs all queries to the browser.
The result is the same if I remove the begin - commit pair at the end.

I know there is potential for optimisations... Don't flame me right now.

Please, also note that this crazy behaviour has occured with use of InnoDB tables on MySQL 3.23.52-Max-log aswell as on MySQL 4.0.12.

I post this without asking for anything. I just report it as part of my problem solving process, to give you something to bite.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Here follows some bug isolation progress:
If I remove execution of the last query from the system:

Code: Select all

<?php
	//Inside Spread function of the PermSpread class...
	$this->objDB->DisConnect();
	$this->objDB->DefaultConnect();
	//Yepp.. even tried to disconnect and reconnect to mysql while searching for a possible way around the bug. It still didn't work

	//The next line is the query that fails:
	//$this->objDB->query('DELETE FROM PermRead WHERE' . $strIDsList );

?>
This generates the following series of queries.

Code: Select all

select UserID from Assignees where TodoID='486' and UserID='30'
SELECT DISTINCT t1.ID FROM Todo as t1 left join PermWrite as t2 on (t1.ID = t2.TodoID ) where t1.ID = '486' and t2.UserID='30'
insert into AccessLog set UserID='30', URL='/xxxxxxxxx?xxxxx=xxxxxxxxxx&amp;ID=486', TimeStamp='1057663779', Action='Updating item'
begin
update Todo set Txt='b', Description='', Createdate='2003-07-08 11:29:39' , Priority=NULL , DeadLine=NULL where ID='486'
commit
select Parent from Todo where ID = '486' LIMIT 0,1
select Parent from Todo where ID = '484' LIMIT 0,1
select ID,Username,AliasName,email,EmailReminded from Users where IsAdmin =0
INSERT IGNORE INTO PermRead set TodoID ='486', UserID='30'
delete from PermRead where TodoID ='486' and UserID='57'
delete from PermRead where TodoID ='486' and UserID='64'
delete from PermRead where TodoID ='486' and UserID='65'
delete from PermRead where TodoID ='486' and UserID='66'
delete from PermRead where TodoID ='486' and UserID='70'
select ID,Username,AliasName,email,EmailReminded from Users where IsAdmin =0
INSERT IGNORE INTO PermWrite set TodoID ='486', UserID='30'
delete from PermWrite where TodoID ='486' and UserID='57'
delete from PermWrite where TodoID ='486' and UserID='64'
delete from PermWrite where TodoID ='486' and UserID='65'
delete from PermWrite where TodoID ='486' and UserID='66'
delete from PermWrite where TodoID ='486' and UserID='70'
delete from Assignees where TodoID ='486'
select distinct t1.UserID, t2.email, t2.AliasName from PermRead t1 left join Users t2 on t1.UserID = t2.ID where t1.TodoID=486 order by t2.AliasName, t2.email
select IsFile from Todo where ID='486' LIMIT 0,1
select ID from Mail where UserID='30' and TopicID='486' LIMIT 0,1
update Mail set UserID='30', TopicID='486' , TopicTxt='b' where ID=37
INSERT IGNORE INTO MailReasons set MailID=37, Reason='Permissions', TimeStamp=1057663779
INSERT IGNORE INTO MailAdressees set MailID=37, UserID=30
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 486
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 492
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 493
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 494
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 495
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 496
SELECT distinct t1.ID FROM Todo t1 left join PermWrite t2 on t1.ID = t2.TodoID where t2.UserID = 30 and t1.Parent = 497
and if I execute it later through phpMyAdmin:

Code: Select all

DELETE FROM PermRead WHERE(TodoID = 492 and UserID = 66) or (TodoID = 493 and UserID = 66) or (TodoID = 497 and UserID = 66) or (TodoID = 494 and UserID = 66) or (TodoID = 495 and UserID = 66) or (TodoID = 496 and UserID = 66);
It also happens! The records with UserID=30 also gets deleted. [edit]from the PermWrite table.[/edit] This is even though mysql is queried from another script with another mysql connection. But I can only reproduce the bug with phpMyAdmin if I precede the failing query with all of the others.

Can anyone see any obvious reason why this is happening?
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Forget about the other posts now and read below instead:

I had forgotten to put UNIQUE keys for the todoid-userid pairs in these tables. I changed that. The problem is still there.
Here are my tables as they look right now:

Code: Select all

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),
  UNIQUE KEY TodoUser (TodoID,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),
  UNIQUE KEY TodoUser (TodoID,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?';
Here follows a snapshot of a mysql shell session, it starts out with PermRead empty:

Code: Select all

heavy@deb129:~$ mysql -u xxxx -p xxxxx
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 175 to server version: 4.0.12-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSERT IGNORE into PermRead set TodoID=3 , UserID=2;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE into PermWrite set TodoID=3 , UserID=2;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE into PermRead set TodoID=3 , UserID=3;
Query OK, 1 row affected (0.06 sec)

mysql> INSERT IGNORE into PermWrite set TodoID=3 , UserID=3;
Query OK, 1 row affected (0.07 sec)

mysql> DELETE FROM PermRead WHERE(TodoID = 3 and UserID = 3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from PermWrite;
Empty set (0.00 sec)

mysql> exit
Bye
Can anyone state their opinion on how these tables are designed?

I guess that the PermWrite table could be foreign keyed to the ID column of PermRead. Does anyone think that the foreign keys as they are designed right now is the cause of my problem?

It would mean quite a lot of work to redesign this just to test. That's why I'm asking instead.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

May I refer to the following text:
If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row.
...found at:
http://www.mysql.com/doc/en/InnoDB_fore ... aints.html

I knew that. But I didn't realize until yesterday that this might be what I am doing. Since there are two foreign keys in PermWrite, I thought that there is some kind och AND relationsship needed for deletion. There is not (of course)*. I tried to create a FOREIGN KEY with reference to the UNIQUE keys between the tables, but then mysql said: errno 150.

* This is called a brain fart, I presume...

This forces me to redesign the application with a single parent column in PermRead and a single foregin key in PermWrite.

DOH! :evil:
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Ok. Done. It took me a day to redesign the database, create a database convert script, and redesign the php code to use the new structure.

Works flawlessly now.
New tables:

Code: Select all

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 ID (ID),
  KEY TodoID (TodoID),
  KEY UserID (UserID),
  UNIQUE KEY TodoUser (TodoID,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,
  PermReadID int(11) NOT NULL,
  TodoID int(11) NOT NULL ,
  UserID int(11) NOT NULL ,
  PRIMARY KEY  (ID),
  KEY ID (ID),
  KEY TodoID (TodoID),
  KEY UserID (UserID),
  KEY PermReadID (PermReadID),
  UNIQUE KEY PermUser (PermReadID),
  FOREIGN KEY (`PermReadID`) REFERENCES `PermRead` (`ID`) ON DELETE CASCADE
) TYPE=InnoDB COMMENT='Who may change what?';

CREATE TABLE Assignees (
  ID int(11) NOT NULL auto_increment,
  PermReadID int(11) NOT NULL,
  TodoID int(11) NOT NULL ,
  UserID int(11) NOT NULL ,
  PRIMARY KEY  (ID),
  KEY ID (ID),
  KEY TodoID (TodoID),
  KEY UserID (UserID),
  KEY PermReadID (PermReadID),
  UNIQUE KEY PermUser (PermReadID),
  FOREIGN KEY (`PermReadID`) REFERENCES `PermRead` (`ID`) ON DELETE CASCADE
) TYPE=InnoDB COMMENT='Who does what?';
What a nice feeling I have right now. It took me two weeks all together.
Post Reply