· 7 years ago · Feb 04, 2019, 02:22 PM
1 -- 1. [dbo].[TWParameters] - #####
2
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7
8print 'Beginning of script - Intellirent 01 Tables.sql';
9GO
10
11IF db_name()<>'master' AND
12 NOT EXISTS (SELECT * FROM information_schema.tables
13 WHERE table_name = 'LBProReturnVal' AND table_type = 'BASE TABLE')
14BEGIN
15 CREATE TABLE LBProReturnVal
16 (
17 ConversionType NVARCHAR(50),
18 RequiredConversionType NVARCHAR(50),
19 RequiredType SMALLINT,
20 DateUTC DATETIME
21 );
22END;
23GO
24
25--------------------------------------------------------------------------------------------
26--- LBPro SOFTWARE SCRIPT SQL
27--- TABLE DEFINITION
28--- dbo.LBProConversion - ##### DESCRIPTION #####
29--- CREATED BY: #####
30--- drop table [LBProConversion]
31--------------------------------------------------------------------------------------------
32
33IF db_name()<>'master' AND
34 NOT EXISTS (SELECT * FROM information_schema.tables
35 WHERE table_name = 'LBProConversion' AND table_type = 'BASE TABLE')
36BEGIN
37 PRINT 'Create table LBProConversion';
38 CREATE TABLE [dbo].[LBProConversion](
39
40 [Id] bigint IDENTITY(1, 1) NOT NULL , -- primary key
41 [ConversionType] varchar(50) NOT NULL DEFAULT ('') , -- #####
42 [IsDone] tinyint NOT NULL DEFAULT ((0)) , -- #####
43 [Type] smallint NOT NULL DEFAULT ((1)) , -- #####
44 [ConversionDateUTC] datetime NOT NULL DEFAULT (getutcdate()) , -- #####
45
46 );
47END;
48GO
49--- end LBProConversion
50
51IF NOT EXISTS (SELECT * FROM sysobjects o INNER JOIN syscolumns AS c ON c.Id = o.Id AND (c.Name = 'RequiredConversionType')
52 WHERE o.XType = 'U' AND o.name = 'LBProConversion')
53BEGIN
54 PRINT 'add LBProConversion.RequiredConversionType'
55 ALTER TABLE LBProConversion ADD RequiredConversionType NVARCHAR(50) NULL
56END
57
58IF NOT EXISTS (SELECT * FROM sysobjects o INNER JOIN syscolumns AS c ON c.Id = o.Id AND (c.Name = 'RequiredType')
59 WHERE o.XType = 'U' AND o.name = 'LBProConversion')
60BEGIN
61 PRINT 'add LBProConversion.RequiredType'
62 ALTER TABLE LBProConversion ADD RequiredType SMALLINT NULL
63END
64
65DELETE FROM LBProReturnVal
66
67
68--------------------------------------------------------------------------------------------
69--- LBPro SOFTWARE SCRIPT SQL
70--- TABLE DEFINITION
71--- dbo.TWUser - ##### DESCRIPTION #####
72--- CREATED BY: #####
73--- drop table [TWUser]
74--------------------------------------------------------------------------------------------
75
76IF db_name()<>'master' AND
77 NOT EXISTS (SELECT * FROM information_schema.tables
78 WHERE table_name = 'TWUser' AND table_type = 'BASE TABLE')
79BEGIN
80 PRINT 'Create table TWUser';
81 CREATE TABLE [dbo].[TWUser](
82
83 [ID] bigint IDENTITY(1, 1) NOT NULL , -- primary key
84 [ProjectUserID] bigint NOT NULL , -- indentification user id in project external
85 [LastGetDateUTC] datetime NOT NULL DEFAULT (getutcdate()) , -- #####
86 [LastGetTweetID] bigint NULL ,
87 [IsDeleted] bit NOT NULL DEFAULT (0) , -- #####
88 [DeleteDateUTC] datetime NULL , -- #####
89
90
91
92 --ALTER TABLE [dbo].[TabName] ADD CONSTRAINT [DF_TabName_ColName] DEFAULT (DefaultValue) FOR [ColumnNmae]
93 --ALTER TABLE [dbo].[TWUser] ADD CONSTRAINT [DF_TWUser_IsDeleted] DEFAULT (0) FOR [IsDeleted]
94
95 CONSTRAINT PK_TWUser PRIMARY KEY CLUSTERED ([ID])
96 );
97END;
98GO
99--- end TWUser
100
101
102--------------------------------------------------------------------------------------------
103--- LBPro SOFTWARE SCRIPT SQL
104--- TABLE DEFINITION
105--- dbo.TWParameters - ##### DESCRIPTION #####
106--- CREATED BY: #####
107--- drop table [TWParameters]
108--------------------------------------------------------------------------------------------
109
110IF db_name()<>'master' AND
111 NOT EXISTS (SELECT * FROM information_schema.tables
112 WHERE table_name = 'TWParameters' AND table_type = 'BASE TABLE')
113BEGIN
114 PRINT 'Create table TWParameters';
115 CREATE TABLE [dbo].[TWParameters](
116
117 [ID] bigint IDENTITY(1, 1) NOT NULL , -- primary key
118 [IdentificationKey] nvarchar(100) NOT NULL , -- text identification parameter
119 [Value] nvarchar(max) NOT NULL , -- parameter value
120 [Description] nvarchar(max) NULL , -- description of what is the parameter and how it is used
121 [IsDeleted] bit NOT NULL DEFAULT (0) , -- #####
122 [DeleteDateUTC] datetime NULL , -- #####
123
124 CONSTRAINT PK_TWParameters PRIMARY KEY CLUSTERED ([ID]),
125 CONSTRAINT UQ_TWParameters_IdentificationKey UNIQUE([IdentificationKey])
126 );
127
128END;
129GO
130--- end TWParameters
131
132
133IF db_name()<>'master' AND
134 NOT EXISTS (SELECT * FROM information_schema.tables
135 WHERE table_name = 'TWProfile' AND table_type = 'BASE TABLE')
136BEGIN
137 PRINT 'Create table TWProfile';
138 CREATE TABLE [dbo].[TWProfile](
139
140 [ID] bigint IDENTITY(1, 1) NOT NULL , -- primary key
141 [Name] nvarchar(200) NOT NULL , -- name twitter profile
142 [GUID] nvarchar(200) NOT NULL , -- indentyfication key
143 [IsDeleted] bit NOT NULL DEFAULT (0) , -- #####
144 [DeleteDateUTC] datetime NULL , -- #####
145
146 CONSTRAINT PK_TWProfile PRIMARY KEY CLUSTERED ([ID]),
147 );
148
149END;
150GO
151--- end TWProfile
152
153
154IF db_name()<>'master' AND
155 NOT EXISTS (SELECT * FROM information_schema.tables
156 WHERE table_name = 'TWProfileTweet' AND table_type = 'BASE TABLE')
157BEGIN
158 PRINT 'Create table TWProfileTweet';
159 CREATE TABLE [dbo].[TWProfileTweet](
160
161 [ID] bigint IDENTITY(1, 1) NOT NULL , -- primary key
162 [TWProfileID] bigint NOT NULL , -- twitter profile
163 [TweetID] bigint NOT NULL , -- id tweet
164 [CreateDateUTC] datetime NOT NULL , -- #####
165 [IsDeleted] bit NOT NULL DEFAULT (0) , -- #####
166 [DeleteDateUTC] datetime NULL , -- #####
167
168 CONSTRAINT PK_TWProfileTweet PRIMARY KEY CLUSTERED ([ID]),
169 );
170
171END;
172GO
173--- end TWProfileTweet
174
175
176--IF db_name()<>'master' AND
177-- NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS
178-- WHERE CONSTRAINT_NAME ='DF_TWUser_IsDeleted')
179--BEGIN
180-- PRINT 'Create contraintDF_TWUser_IsDeleted';
181-- ALTER TABLE [dbo].[TWUser]
182-- ADD CONSTRAINT [DF_TWUser_IsDeleted] DEFAULT (0) FOR [IsDeleted]
183
184--END
185--GO
186
187
188IF db_name()<>'master' AND
189 NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS
190 WHERE CONSTRAINT_NAME ='FK_TWProfileTweet_TWProfile')
191BEGIN
192 PRINT 'Create contraint FK_TWProfileTweet_TWProfile';
193 ALTER TABLE TWProfileTweet
194 ADD CONSTRAINT [FK_TWProfileTweet_TWProfile] FOREIGN KEY([TWProfileID]) REFERENCES [dbo].[TWProfile] ([ID])
195END
196GO
197
198
199IF db_name()<>'master' AND
200 NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS
201 WHERE CONSTRAINT_NAME ='DF_TWProfileTweet_CreateDateUTC')
202BEGIN
203 PRINT 'Create contraint DF_TWProfileTweet_CreateDateUTC';
204 ALTER TABLE [dbo].[TWProfileTweet]
205 ADD CONSTRAINT [DF_TWProfileTweet_CreateDateUTC] DEFAULT (getutcdate()) FOR [CreateDateUTC]
206
207END
208GO
209
210
211SELECT * FROM LBProReturnVal
212
213
214SET QUOTED_IDENTIFIER OFF
215GO
216SET ANSI_NULLS ON
217GO