Table names not following naming convention

[WillStrohl]

There are other tables that are used for the community modules and they're prefixed with Community_. This update proposes that the tables in this module are updated to match that naming convention.

I already have the SQL done and ready, but I can't update the code itself until a previous pull request is merged (after review).

/*
    Remove constraints
*/

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}Visits_Visitors' 
    AND TABLE_NAME = N'{objectQualifier}Visits')
    ALTER TABLE {databaseOwner}[{objectQualifier}Visits] DROP CONSTRAINT [FK_{objectQualifier}Visits_Visitors];
GO

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}Visits_Tabs' 
    AND TABLE_NAME = N'{objectQualifier}Visits')
    ALTER TABLE {databaseOwner}[{objectQualifier}Visits] DROP CONSTRAINT [FK_{objectQualifier}Visits_Tabs];
GO

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}Visitors_Users' 
    AND TABLE_NAME = N'{objectQualifier}Visitors')
    ALTER TABLE {databaseOwner}[{objectQualifier}Visitors] DROP CONSTRAINT [FK_{objectQualifier}Visitors_Users];
GO

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}Visitors_Portals' 
    AND TABLE_NAME = N'{objectQualifier}Visitors')
    ALTER TABLE {databaseOwner}[{objectQualifier}Visitors] DROP CONSTRAINT [FK_{objectQualifier}Visitors_Portals];
GO

/*
    Rename community tables to prevent conflicts and group the data together
*/

sp_rename N'{objectQualifier}Visitors', N'{objectQualifier}Community_Visitors';
GO

sp_rename N'{objectQualifier}Visits', N'{objectQualifier}Community_Visits';
GO

/*
    Add constraints back
*/

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visitors]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}Community_Visitors_Portals] FOREIGN KEY([portal_id])
    REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID])
    ON DELETE CASCADE;
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visitors] CHECK CONSTRAINT [FK_{objectQualifier}Community_Visitors_Portals];
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visitors]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}Community_Visitors_Users] FOREIGN KEY([user_id])
    REFERENCES {databaseOwner}[{objectQualifier}Users] ([UserID])
    ON UPDATE CASCADE 
    ON DELETE CASCADE;
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visitors] CHECK CONSTRAINT [FK_{objectQualifier}Community_Visitors_Users];
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visits]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}Community_Visits_Tabs] FOREIGN KEY([tab_id])
    REFERENCES {databaseOwner}[{objectQualifier}Tabs] ([TabID]);
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visits] CHECK CONSTRAINT [FK_{objectQualifier}Community_Visits_Tabs];
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visits]  WITH CHECK ADD  CONSTRAINT [FK_{objectQualifier}Community_Visits_Visitors] FOREIGN KEY([visitor_id])
    REFERENCES {databaseOwner}[{objectQualifier}Community_Visitors] ([id])
    ON DELETE CASCADE;
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Community_Visits] CHECK CONSTRAINT [FK_{objectQualifier}Community_Visits_Visitors];
GO

[david-poindexter]

@hismightiness I have already reviewed, approved and merged the referenced pull request #3 - you are free to proceed.

[X3Technology]

To be complete, should rename the primary keys to reflect the full table name?

[WillStrohl]

@mathisjay I think I did. Did I miss something in the SQL above?

[X3Technology]

@hismightiness It looks like you renamed all the foreign keys, but there are also 2 primary keys. "PK_Visits" and "PK_Visitors" for the Visits and Visitors tables respectively.

[WillStrohl]

Oh... Gotcha. Thanks. :)