0.4 MSSQL install script

[TechMonster]

/trunk/roundcubemail/SQL/mssql.initial.sql ? Roundcube Webmail

When running the install script i am getting this error.
Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'FK_contactgroupmembers_contact_id' on table 'contactgroupmembers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.


It does not like the foreign keys being created in this statement.

ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contact_id]
    FOREIGN KEY ([contact_id]) REFERENCES [dbo].[contacts] ([contact_id])
    ON DELETE CASCADE ON UPDATE CASCADE
GO

Any idea's?

[JohnDoh]

I think this was fixed already in r3891, you can get a new copy of the script here /trunk/roundcubemail/SQL/mssql.initial.sql ? Roundcube Webmail

[TechMonster]

That is the version I test as the one in the original .4 download is busted even worse. The r3891 only has one error.

Travis

[TechMonster]

Any ideas? bump

[JohnDoh]

whats the error?

[alec]

The error is in the first message. Currently I don't see other solution than using ON DELETE NO ACTION ON UPDATE NO ACTION with some mssql-specific code on user/contact delete action.
We need some MSSQL guru, maybe we can handle this with triggers instead?

[TechMonster]

Does any one have an idea. As it is this is broke for upgrade/installs

[sm69th]

Does any one have an idea. As it is this is broke for upgrade/installs

I agree,
I am trying a fresh install and with this error(mentioned at the start of this post) I cannot create the database properly.

I am using:
Windows 2008 Standard X64
Microsoft SQL Server 2008 R2 Standard
hMailServer
Latest RoundCube download.

This one SQL error in the initial mssql file is all that is preventing me from launching webmail.

I will take a look at a solution via triggers, etc, but I am not sure if I have enough MSSQL knowledge to find a good solution...

[alec]

Try to replace

Code Select Expand


ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contact_id]
FOREIGN KEY ([contact_id]) REFERENCES [dbo].[contacts] ([contact_id])
ON DELETE CASCADE ON UPDATE CASCADE
GO

with

Code Select Expand


CREATE TRIGGER [contact_member_delete]
ON [dbo].[contacts]
AFTER DELETE
AS
DELETE FROM [dbo].[contactgroupmembers] WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)

or something like that

[TechMonster]

I got a chance to work with this again and tested 4.2 and it still errors out of the box. I made the change alec suggested and it works. I know MSSQL just not triggers. Does this work correctly or will I have issues down the road? Also why has not roundcube folks fixed this?
Travis

[alec]

Roundcube devs are using linux.

[TechMonster]

I understand.. I did not want to sound mean.. Why I chosen to fix this issue as I really like roundcube.
Travis

PS. The trigger does not work..

[TechMonster]

The trigger does not work correctly as when contacts are removed the del column is changed from 0 to 1 not actually removing the row with the delete command. So I wrote a new trigger that does the job. It checks the update command for the del column and that its going from 0 to 1. If so it deletes from the contactgroupmembers table. Here is the code.  This fixes the mssql.initial. SQL code. The mssql.update still has errors.


ALTER TRIGGER [contact_member_delete]
ON [dbo].[contacts]
AFTER UPDATE
AS
IF (SELECT del FROM [contacts]) = 1
BEGIN
DELETE FROM [dbo].[contactgroupmembers] WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
END