source: branches/3.3-HiveMigration/tools/prepareHiveDatabase.sql @ 4320

/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */

/*
use these DROP commands if you want to run this query a second time on the same db */
/*
ALTER TABLE dbo.AssignedResources ALTER COLUMN AssignedRessourcesId DROP ROWGUIDCOL;
ALTER TABLE dbo.Job ALTER COLUMN JobId DROP ROWGUIDCOL;
ALTER TABLE dbo.SlaveConfig ALTER COLUMN SlaveConfigId DROP ROWGUIDCOL;
ALTER TABLE dbo.SlaveGroup_Resource ALTER COLUMN SlaveGroup_RessourceId DROP ROWGUIDCOL;
ALTER TABLE dbo.PluginInfo ALTER COLUMN PluginId DROP ROWGUIDCOL;
ALTER TABLE dbo.Project ALTER COLUMN ProjectId DROP ROWGUIDCOL;
ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId DROP ROWGUIDCOL;
ALTER TABLE dbo.Resource ALTER COLUMN ResourceId DROP ROWGUIDCOL;
ALTER TABLE dbo.UptimeCalendar ALTER COLUMN UptimeCalendarId DROP ROWGUIDCOL;
ALTER TABLE dbo.UptimeStatistics ALTER COLUMN UptimeStatisticsId DROP ROWGUIDCOL;

ALTER TABLE dbo.AssignedResources DROP CONSTRAINT [DF_AssignedResources_AssignedRessourcesId];
ALTER TABLE dbo.Job DROP CONSTRAINT [DF_Job_JobId];
ALTER TABLE dbo.SlaveConfig DROP CONSTRAINT [DF_SlaveConfig_SlaveConfigId];
ALTER TABLE dbo.SlaveGroup_Resource DROP CONSTRAINT [DF_SlaveGroup_ResourceSlaveGroup_RessourceId];
ALTER TABLE dbo.PluginInfo DROP CONSTRAINT [DF_PluginInfo_PluginId];
ALTER TABLE dbo.Project DROP CONSTRAINT [DF_Project_ProjectId];
ALTER TABLE dbo.RequiredPlugins DROP CONSTRAINT [DF_RequiredPlugins_RequiredPluginId];
ALTER TABLE dbo.Resource DROP CONSTRAINT [DF_Resource_ResourceId];
ALTER TABLE dbo.UptimeCalendar DROP CONSTRAINT [DF_UptimeCalendar_UptimeCalendarId];
ALTER TABLE dbo.UptimeStatistics DROP CONSTRAINT [DF_UptimeStatistics_UptimeStatisticsId];
*/


ALTER TABLE dbo.AssignedResources ALTER COLUMN AssignedRessourcesId ADD ROWGUIDCOL;
ALTER TABLE dbo.AssignedResources WITH NOCHECK ADD CONSTRAINT [DF_AssignedResources_AssignedRessourcesId] DEFAULT (newid()) FOR AssignedRessourcesId;

ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (newid()) FOR JobId;

ALTER TABLE dbo.SlaveConfig ALTER COLUMN SlaveConfigId ADD ROWGUIDCOL;
ALTER TABLE dbo.SlaveConfig WITH NOCHECK ADD CONSTRAINT [DF_SlaveConfig_SlaveConfigId] DEFAULT (newid()) FOR SlaveConfigId;

ALTER TABLE dbo.SlaveGroup_Resource ALTER COLUMN SlaveGroup_RessourceId ADD ROWGUIDCOL;
ALTER TABLE dbo.SlaveGroup_Resource WITH NOCHECK ADD CONSTRAINT [DF_SlaveGroup_Resource_SlaveGroup_RessourceId] DEFAULT (newid()) FOR SlaveGroup_RessourceId;

ALTER TABLE dbo.PluginInfo ALTER COLUMN PluginId ADD ROWGUIDCOL;
ALTER TABLE dbo.PluginInfo WITH NOCHECK ADD CONSTRAINT [DF_PluginInfo_PluginId] DEFAULT (newid()) FOR PluginId;

ALTER TABLE dbo.Project ALTER COLUMN ProjectId ADD ROWGUIDCOL;
ALTER TABLE dbo.Project WITH NOCHECK ADD CONSTRAINT [DF_Project_ProjectId] DEFAULT (newid()) FOR ProjectId;

ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (newid()) FOR RequiredPluginId;

ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (newid()) FOR ResourceId;

ALTER TABLE dbo.UptimeCalendar ALTER COLUMN UptimeCalendarId ADD ROWGUIDCOL;
ALTER TABLE dbo.UptimeCalendar WITH NOCHECK ADD CONSTRAINT [DF_UptimeCalendar_UptimeCalendarId] DEFAULT (newid()) FOR UptimeCalendarId;

ALTER TABLE dbo.UptimeStatistics ALTER COLUMN UptimeStatisticsId ADD ROWGUIDCOL;
ALTER TABLE dbo.UptimeStatistics WITH NOCHECK ADD CONSTRAINT [DF_UptimeStatistics_UptimeStatisticsId] DEFAULT (newid()) FOR UptimeStatisticsId;

/* create indices */
CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);