jobbr-storage-mssql/source/Jobbr.Storage.MsSql/CreateTables.sql at develop · jobbrIO/jobbr-storage-mssql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Jobs](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[UniqueName] [varchar](100) NULL,
	[Title] [varchar](100) NULL,
	[Parameters] [nvarchar](max) NULL,
	[Type] [varchar](100) NULL,
	[UpdatedDateTimeUtc] [datetime] NULL,
	[CreatedDateTimeUtc] [datetime] NULL,
	[Deleted] [bit] NOT NULL,
    [MaxConcurrentJobRuns] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
	[UniqueName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Triggers](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Type] [varchar](255) NOT NULL,
	[JobId] [bigint] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[UserId] [varchar](100) NULL,
	[UserDisplayName] [varchar](100) NULL,
	[Parameters] [nvarchar](max) NULL,
	[Comment] [varchar](8000) NULL,
	[CreatedDateTimeUtc] [datetime] NOT NULL,
	[StartDateTimeUtc] [datetime] NULL,
	[EndDateTimeUtc] [datetime] NULL,
	[Definition] [varchar](8000) NULL,
	[NoParallelExecution] [bit] NOT NULL,
	[DelayedMinutes] [int] NOT NULL,
	[Deleted] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Triggers]  WITH CHECK ADD  CONSTRAINT [FK_Triggers_Jobs_JobId] FOREIGN KEY([JobId])
REFERENCES [dbo].[Jobs] ([Id])
GO

ALTER TABLE [dbo].[Triggers] CHECK CONSTRAINT [FK_Triggers_Jobs_JobId]
GO

CREATE TABLE [dbo].[JobRuns](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[JobId] [bigint] NOT NULL,
	[TriggerId] [bigint] NOT NULL,
	[State] [varchar](255) NOT NULL,
	[Progress] [float] NULL,
	[PlannedStartDateTimeUtc] [datetime] NOT NULL,
	[ActualStartDateTimeUtc] [datetime] NULL,
	[ActualEndDateTimeUtc] [datetime] NULL,
	[EstimatedEndDateTimeUtc] [datetime] NULL,
	[JobParameters] [nvarchar](max) NULL,
	[InstanceParameters] [nvarchar](max) NULL,
	[Pid] [int] NULL,
	[Deleted] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobRuns]  WITH CHECK ADD  CONSTRAINT [FK_JobRuns_Jobs_JobId] FOREIGN KEY([JobId])
REFERENCES [dbo].[Jobs] ([Id])
GO

ALTER TABLE [dbo].[JobRuns] CHECK CONSTRAINT [FK_JobRuns_Jobs_JobId]
GO

ALTER TABLE [dbo].[JobRuns]  WITH CHECK ADD  CONSTRAINT [FK_JobRuns_Triggers_TriggerId] FOREIGN KEY([TriggerId])
REFERENCES [dbo].[Triggers] ([Id])
GO

ALTER TABLE [dbo].[JobRuns] CHECK CONSTRAINT [FK_JobRuns_Triggers_TriggerId]
GO