Author Topic: 0.4 MSSQL install script  (Read 7850 times)

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« on: August 15, 2010, 10:03:07 PM »
/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?

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,845
0.4 MSSQL install script
« Reply #1 on: August 16, 2010, 02:48:58 AM »
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
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and more…

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« Reply #2 on: August 16, 2010, 11:17:34 AM »
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

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« Reply #3 on: August 17, 2010, 09:31:26 AM »
Any ideas? bump

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,845
0.4 MSSQL install script
« Reply #4 on: August 18, 2010, 02:56:45 AM »
whats the error?
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and more…

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
0.4 MSSQL install script
« Reply #5 on: August 18, 2010, 09:08:52 AM »
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?

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« Reply #6 on: August 26, 2010, 02:49:53 PM »
Does any one have an idea. As it is this is broke for upgrade/installs

Offline sm69th

  • Newbie
  • *
  • Posts: 6
0.4 MSSQL install script
« Reply #7 on: August 26, 2010, 04:53:58 PM »
Quote from: TechMonster;29672
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...

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
0.4 MSSQL install script
« Reply #8 on: August 27, 2010, 04:40:27 AM »
Try to replace
Code: [Select]

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]

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

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« Reply #9 on: November 08, 2010, 10:10:55 PM »
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

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
0.4 MSSQL install script
« Reply #10 on: November 09, 2010, 02:35:18 AM »
Roundcube devs are using linux.

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« Reply #11 on: November 10, 2010, 12:31:07 PM »
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..

Offline TechMonster

  • Jr. Member
  • **
  • Posts: 34
0.4 MSSQL install script
« Reply #12 on: November 10, 2010, 01:03:47 PM »
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