· 6 years ago · Dec 12, 2019, 08:46 AM
1--a. modify the type of a column;
2go
3create or alter procedure do1
4as
5begin
6 alter table Enchantments
7 alter column TestColumn int
8end
9go
10--execute do1
11
12go
13create or alter procedure undo1
14as
15begin
16 alter table Enchantments
17 alter column TestColumn varchar(50)
18end
19go
20--execute undo1
21
22
23
24--b. add / remove a column;
25go
26create or alter procedure do2
27as
28begin
29 alter table Players
30 add TestColumnPlayer int;
31end
32go
33--execute do2
34
35go
36create or alter procedure undo2
37as
38begin
39 alter table Players
40 drop column TestColumnPlayer;
41end
42go
43--execute undo2
44
45
46
47--c. add / remove a DEFAULT constraint;
48go
49create or alter procedure do3
50as
51begin
52 alter table Achievements add TestConstraint int,
53 constraint DF_Achievement_TestConstraint default 1 for TestConstraint;
54end
55go
56--execute do3 -- wut
57
58go
59create or alter procedure undo3
60as
61begin
62 alter table Achievements drop
63 constraint DF_Achievement_TestConstraint,
64 column TestConstraint;
65end
66go
67--execute undo3
68
69
70
71--d. add / remove a primary key;
72go
73create or alter procedure do4
74as
75begin
76 create table testTable(Number int not null);
77 alter table testTable
78 add constraint PKNumber primary key clustered(Number);
79end
80go
81--execute do4
82
83go
84create or alter procedure undo4
85as
86begin
87 alter table testTable
88drop constraint PKNumber
89drop table testTable
90end
91go
92--execute undo4
93
94
95
96--e. add / remove a candidate key;
97go
98create or alter procedure do5
99as
100begin
101 alter table Items
102 add constraint UQ_Items_id_name unique(Iid, Name)
103end
104go
105--execute do5 --wut
106
107go
108create or alter procedure undo5
109as
110begin
111 alter table Items
112 drop constraint UQ_Items_id_name
113end
114go
115--execute undo5
116
117
118
119--f. add / remove a foreign key;
120go
121create or alter procedure do6
122as
123begin
124 alter table Structures
125 add TestFK int;
126
127 alter table Structures
128 add constraint Structures_TestFK foreign key(TestFK)
129 references Players(Pid);
130end
131go
132--execute do6
133
134go
135create or alter procedure undo6
136as
137begin
138 alter table Structures
139 drop constraint Structures_TestFK;
140
141 alter table Structures
142 drop column TestFK;
143end
144go
145--execute undo6
146
147--g. create / remove a table.
148go
149create or alter procedure do7
150as
151begin
152 create table TestTableG(
153 testID int primary key,
154 testCol varchar(50)
155 );
156end
157go
158--execute do7
159
160go
161create or alter procedure undo7
162as
163begin
164 drop table if exists TestTableG
165end
166go
167--execute undo7
168
169
170
171
172--Create a new table that holds the current version of the database schema.
173
174drop table if exists DBVersion
175create table DBVersion
176 (
177 id int identity (1, 1) not null,
178 currVersion int,
179 constraint PK_version_ID primary key clustered(id)
180 )
181
182insert into DBVersion values(0);
183
184
185
186--Write another stored procedure that receives as a parameter a version number and brings the database to that version.
187go
188create or alter procedure TakeMeToVersion
189 @versionTo int
190as
191begin
192 declare @versionFrom int
193 set @versionFrom = (select V.currVersion from DBVersion V)
194 declare @query varchar(2000)
195
196 if @versionTo <= 7 and @versionTo >= 0
197 if @versionTo > @versionFrom
198 begin
199 while @versionTo > @versionFrom
200 begin
201 set @versionFrom = @versionFrom + 1
202 set @query = 'do' + CAST(@versionFrom as varchar(5))
203 exec @query
204 end
205 end
206 else
207 begin
208 while @versionTo < @versionFrom
209 begin
210 if @versionFrom != 0
211 begin
212 set @query = 'undo' + CAST(@versionFrom as varchar(5))
213 exec @query
214 end
215 set @versionFrom = @versionFrom - 1
216 end
217 end
218 else
219 begin
220 print 'version has to be <= 7 and >= 0'
221 return
222 end
223
224 update DBVersion
225 set currVersion = @versionTo
226end
227go
228
229exec TakeMeToVersion 1;
230select * from DBVersion