the database scripts src/database/wildfire_[type].sql look all very similar. Maybe there are some databases available which do not support foreign keys, but I’'m very sure that the embedded HSQLDB and Oracle RDBMS both support foreign keys.
Is there a reason why no foreign keys are used?
I recently deleted a user using the web admin console. The user was removed from the JIVEUSER table but some entries remained in the JIVEUSERPROP or JIVEPRIVATE table. The Wildfire administrator may have a serious data grave if cascaded deletes will be handled by the application server instead of the database.
We definitely need to ensure that the app doesn’‘t depend on foreign keys for databases that don’‘t support them. However, you’‘re right that it’'s not a bad idea to sprinkle them into the schemas where it makes sense.
For this particular issue – I think you might be right. Looking through the code, I can’‘t see where the delete for jiveUserProp and jivePrivate are happening. I’'ve filed JM-510 on this issue.
It would also be nice to have UserManager delete any entries in jiveRoster, jiveRosterGroups and jiveVCard tables if possible.
Not deleting these entries may become a problem for us. Lets say user 1234@foo.bar retires and we delete their account. Then lets say a new user gets the username 1234@foo.bar. They will automatically have all the old users contacts in their roster and VCard information because the database records still exist from the original 1234@foo.bar user.
Wildfire is a small application and therefore it can try to handle this.
But I think that the idea itself is evil, because it must not commit a ‘‘delete user’’ action before all references are removed. This is usually very bad for database performance.
May I ask which databases do not support foreign keys?
LG
PS: Columns whose name and type imply a relationship to another table’'s primary key (auto generated so maybe sometimes wrong):
I just wrote a SQL script that will cleanup all the things that UserManager currently does not handle. I ran this successfully on SQL Server 2000. It should work on other RDMS’‘s with little modification because it’'s fairly ANSI SQL compliant.
/* This script removes everything about a deleted user that UserManager
didn’'t handle. This includes personal contacts from their roster list,
the user from other user’'s roster lists and VCard entries.
*/
/* Declare the user whose records are to be deleted. */
DECLARE @UserName nvarchar(32);
DECLARE @ServerName nvarchar(1024);
SET @UserName = ‘‘xxxxx’’;
SET @ServerName = ‘’@xxxxx’’;
/* Remove the user’'s roster list. The jiveRosterGroups table goes
first due to key constraints with the jiveRoster table.
*/
DELETE FROM jiveRosterGroups
WHERE rosterID IN
(SELECT rosterID
FROM jiveRoster
WHERE username = @UserName);
/* Now remove the user’'s entry in the jiveRoster table. */
DELETE FROM jiveRoster
WHERE username = @UserName;
/* Now remove the user from other user’'s roster lists. Once again the
jiveRosterGroups table goes first due to key constraints with the
jiveRoster table.
*/
DELETE FROM jiveRosterGroups
WHERE rosterID IN
(SELECT rosterID
FROM jiveRoster
WHERE jid = @UserName + @ServerName);
/* Now remove the user’'s entry in the jiveRoster table. */
DELETE FROM jiveRoster
WHERE jid = @UserName + @ServerName;
/* Now remove the user’'s entry in the VCard table. */