Changeset 6721 for branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts

Timestamp:

09/08/11 13:41:25 (13 years ago)

Author:

ascheibe

Message:

#1233 Review comments: renamed Job to Task

Location:

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts

Files:

• Initialize Hive Database.sql (modified) (6 diffs)
• Prepare Hive Database.sql (modified) (10 diffs)
• Query Jobs.sql (modified) (2 diffs)

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

@@ -5 +5 @@
 CREATE TABLE [dbo].[AssignedResources](
   [ResourceId] UniqueIdentifier NOT NULL, 
-  [JobId] UniqueIdentifier NOT NULL,   
-  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
+  [TaskId] UniqueIdentifier NOT NULL,   
+  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
   )
 CREATE TABLE [dbo].[Plugin](
@@ -19 +19 @@
 CREATE TABLE [dbo].[RequiredPlugins](
   [RequiredPluginId] UniqueIdentifier NOT NULL, 
-  [JobId] UniqueIdentifier NOT NULL, 
+  [TaskId] UniqueIdentifier NOT NULL, 
   [PluginId] UniqueIdentifier NOT NULL, 
   CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
@@ -42 +42 @@
   CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
   )
-CREATE TABLE [dbo].[Job](
-  [JobId] UniqueIdentifier NOT NULL, 
-  [JobState] VarChar(30), 
+CREATE TABLE [dbo].[Task](
+  [TaskId] UniqueIdentifier NOT NULL, 
+  [TaskState] VarChar(30), 
   [ExecutionTimeMs] float, 
   [LastHeartbeat] DateTime, 
-  [ParentJobId] UniqueIdentifier, 
+  [ParentTaskId] UniqueIdentifier, 
   [Priority] Int NOT NULL, 
   [CoresNeeded] Int NOT NULL, 
   [MemoryNeeded] Int NOT NULL, 
-  [IsParentJob] Bit, 
+  [IsParentTask] Bit, 
   [FinishWhenChildJobsFinished] Bit, 
   [Command] VarChar(30), 
   [HiveExperimentId] UniqueIdentifier NOT NULL, 
   [IsPrivileged] Bit, 
-  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
+  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
   )
 CREATE TABLE [dbo].[Downtime](
@@ -79 +79 @@
   CONSTRAINT [PK_dbo.HiveExperiment] PRIMARY KEY ([HiveExperimentId])
   )
-CREATE TABLE [dbo].[JobData](
-  [JobId] UniqueIdentifier NOT NULL, 
+CREATE TABLE [dbo].[TaskData](
+  [TaskId] UniqueIdentifier NOT NULL, 
   [Data] VarBinary(MAX), 
   [LastUpdate] DateTime, 
-  CONSTRAINT [PK_dbo.JobData] PRIMARY KEY ([JobId])
+  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
   )
 CREATE TABLE [dbo].[PluginData](
@@ -96 +96 @@
   [State] VarChar(30) NOT NULL, 
   [DateTime] DateTime NOT NULL, 
-  [JobId] UniqueIdentifier NOT NULL, 
+  [TaskId] UniqueIdentifier NOT NULL, 
   [UserId] UniqueIdentifier, 
   [SlaveId] UniqueIdentifier, 
@@ -149 +149 @@
   ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
 ALTER TABLE [dbo].[AssignedResources]
-  ADD CONSTRAINT [Job_AssignedResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
 ALTER TABLE [dbo].[RequiredPlugins]
   ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
 ALTER TABLE [dbo].[RequiredPlugins]
-  ADD CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
 ALTER TABLE [dbo].[Resource]
   ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
-ALTER TABLE [dbo].[Job]
-  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([ParentJobId]) REFERENCES [dbo].[Job]([JobId])
-ALTER TABLE [dbo].[Job]
+ALTER TABLE [dbo].[Task]
+  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
+ALTER TABLE [dbo].[Task]
   ADD CONSTRAINT [HiveExperiment_Job] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
 ALTER TABLE [dbo].[Downtime]
   ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
-ALTER TABLE [dbo].[JobData]
-  ADD CONSTRAINT [Job_JobData] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[TaskData]
+  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
 ALTER TABLE [dbo].[PluginData]
   ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
 ALTER TABLE [dbo].[StateLog]
-  ADD CONSTRAINT [Job_StateLog] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
 ALTER TABLE [dbo].[StateLog]
   ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

@@ -8 +8 @@
 
 
-ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Job_AssignedResource]
-ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Job_AssignedResource] FOREIGN KEY([JobId])
-REFERENCES [dbo].[Job] ([JobId])
+ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Task_AssignedResource]
+ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Task_AssignedResource] FOREIGN KEY([TaskId])
+REFERENCES [dbo].[Task] ([TaskId])
 ON UPDATE CASCADE
 ON DELETE CASCADE
@@ -21 +21 @@
 GO
 
-ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
-ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
-GO
-
-ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Job_StateLog]
-ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Job_StateLog] FOREIGN KEY([JobId])
-REFERENCES [dbo].[Job] ([JobId])
+ALTER TABLE dbo.Task ALTER COLUMN TaskId ADD ROWGUIDCOL;
+ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId;
+GO
+
+ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Task_StateLog]
+ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId])
+REFERENCES [dbo].[Task] ([TaskId])
 ON UPDATE CASCADE
 ON DELETE CASCADE
@@ -54 +54 @@
 ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
 
-ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Job_RequiredPlugin]
-ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY([JobId])
-REFERENCES [dbo].[Job] ([JobId])
+ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Task_RequiredPlugin]
+ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId])
+REFERENCES [dbo].[Task] ([TaskId])
 ON UPDATE CASCADE
 ON DELETE CASCADE
@@ -110 +110 @@
 
 /* create indices */
-CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);
+CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
 GO
 
@@ -120 +120 @@
 CREATE VIEW [dbo].[view_FirstState]
 AS
-SELECT     sl.JobId, sl.DateTime, sl.State
+SELECT     sl.TaskId, sl.DateTime, sl.State
 FROM         dbo.StateLog AS sl INNER JOIN
-                          (SELECT     JobId, MIN(DateTime) AS DateTime
+                          (SELECT     TaskId, MIN(DateTime) AS DateTime
                             FROM          dbo.StateLog
-                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
+                            GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
 
 GO
@@ -134 +134 @@
 CREATE VIEW [dbo].[view_LastState]
 AS
-SELECT     sl.JobId, sl.DateTime, sl.State
+SELECT     sl.TaskId, sl.DateTime, sl.State
 FROM         dbo.StateLog AS sl INNER JOIN
-                          (SELECT     JobId, MAX(DateTime) AS DateTime
+                          (SELECT     TaskId, MAX(DateTime) AS DateTime
                             FROM          dbo.StateLog
-                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
+                            GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
 GO
 
@@ -176 +176 @@
 CREATE TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] INSTEAD OF DELETE AS 
 BEGIN
-  DELETE Job FROM deleted, Job WHERE deleted.HiveExperimentId = Job.HiveExperimentId
+  DELETE Task FROM deleted, Task WHERE deleted.HiveExperimentId = Task.HiveExperimentId
   DELETE HiveExperiment FROM deleted, HiveExperiment WHERE deleted.HiveExperimentId = HiveExperiment.HiveExperimentId
 END
@@ -186 +186 @@
 -- Description: Recursively deletes all child-jobs of a job when it is deleted. (Source: http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/)
 -- =============================================
-CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 
+CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS 
 BEGIN
   -- add statistics
@@ -202 +202 @@
   WHERE 
     he.HiveExperimentId = j.HiveExperimentId AND 
-    fs.JobId = j.JobId AND 
-    ls.JobId = j.JobId
+    fs.TaskId = j.TaskId AND 
+    ls.TaskId = j.TaskId
   GROUP BY he.OwnerUserId
 
   -- recursively delete jobs
   CREATE TABLE #Table( 
-    JobId uniqueidentifier 
+    TaskId uniqueidentifier 
   )
-  INSERT INTO #Table (JobId)
-  SELECT JobId FROM deleted
+  INSERT INTO #Table (TaskId)
+  SELECT TaskId FROM deleted
   
   DECLARE @c INT
   SET @c = 0
   
-  WHILE @c <> (SELECT COUNT(JobId) FROM #Table) BEGIN
-    SELECT @c = COUNT(JobId) FROM #Table
+  WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
+    SELECT @c = COUNT(TaskId) FROM #Table
     
-    INSERT INTO #Table (JobId)
-      SELECT Job.JobId
-      FROM Job
-      LEFT OUTER JOIN #Table ON Job.JobId = #Table.JobId
-      WHERE Job.ParentJobId IN (SELECT JobId FROM #Table)
-        AND #Table.JobId IS NULL
+    INSERT INTO #Table (TaskId)
+      SELECT Task.TaskId
+      FROM Task
+      LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
+      WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
+        AND #Table.TaskId IS NULL
   END
   
-  DELETE JobData FROM JobData INNER JOIN #Table ON JobData.JobId = #Table.JobId
-  DELETE Job FROM Job INNER JOIN #Table ON Job.JobId = #Table.JobId
+  DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
+  DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
 END
 GO
@@ -235 +235 @@
 -- Author:    cneumuel
 -- Create date: 17.05.2011
--- Description: Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
--- =============================================
---CREATE TRIGGER [dbo].[tr_DeletedJobStats] ON [dbo].[Job] AFTER DELETE AS
+-- Description: Writes the execution times of deleted Tasks into DeletedJobStats to ensure correct statistics
+-- =============================================
+--CREATE TRIGGER [dbo].[tr_DeletedJobStats] ON [dbo].[Task] AFTER DELETE AS
 --BEGIN
 

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Query Jobs.sql

@@ -1 +1 @@
 /****** Script for SelectTopNRows command from SSMS  ******/
-SELECT j.[JobId]
-      ,j.[JobState]
+SELECT j.[TaskId]
+      ,j.[TaskState]
       ,j.[ExecutionTimeMs]/1000/60/60 AS ExecutionTimeHours
       ,DATALENGTH(jd.data)/1024.0/1024.0 AS MB
@@ -13 +13 @@
       ,j.[IsPrivileged]
       ,j.[Command]
-      ,j.[IsParentJob]   
+      ,j.[IsParentTask]   
       ,j.[FinishWhenChildJobsFinished]
-      ,j.[ParentJobId]
+      ,j.[ParentTaskId]
       , he.HiveExperimentId
   FROM 
-  [HeuristicLab.Hive-3.3].[dbo].[Job] j
+  [HeuristicLab.Hive-3.3].[dbo].[Task] j
   ,[HeuristicLab.Hive-3.3].[dbo].[HiveExperiment] he
   ,[HeuristicLab.Authentication].dbo.aspnet_Users u
-  ,[HeuristicLab.Hive-3.3].dbo.JobData jd
+  ,[HeuristicLab.Hive-3.3].dbo.TaskData jd
   WHERE
   j.HiveExperimentId = he.HiveExperimentId
-  AND j.JobId = jd.JobId
+  AND j.TaskId = jd.TaskId
   AND he.OwnerUserId = u.UserId
   --AND he.DateCreated > '06-03-2011 10:30'
   
-ORDER BY j.JobState, he.DateCreated DESC
+ORDER BY j.TaskState, he.DateCreated DESC