NPOManagementDB/Status normalization.sql at main · LearningMissionLabsDev/NPOManagementDB · 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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AccountStatus')
BEGIN
   CREATE TABLE AccountStatus (
    Id INT PRIMARY KEY,
    Description VARCHAR(50) NOT NULL);

	INSERT INTO AccountStatus (Id, Description) VALUES
    (1, 'Pending'),
    (2, 'Active'),
    (3, 'Deleted');
END;


IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Account' AND COLUMN_NAME = 'StatusId')
BEGIN
    ALTER TABLE Account
    ADD StatusId INT;
END;

GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Account' AND COLUMN_NAME = 'Status')
BEGIN
   UPDATE [Account]
		SET StatusId =
				CASE
					WHEN [Status] = 'Activ' THEN 2
					WHEN [Status] = 'Pending' THEN 1
					WHEN [Status] = 'Deleted' THEN 3
					ELSE NULL
				END;

	ALTER TABLE [Account]
	DROP COLUMN Status;

END;

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME = 'Account'
      AND CONSTRAINT_NAME = 'FK_AccountStatus'
)
BEGIN
    ALTER TABLE [Account]
		ADD CONSTRAINT FK_AccountStatus
		FOREIGN KEY (StatusId)
		REFERENCES AccountStatus(Id);
END

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME in ('UserRoleConnection', 'Role') )
BEGIN
	ALTER TABLE [UserRoleConnection]
		DROP CONSTRAINT FK_UserRoleConnection_Role;

		DROP TABLE [Role];
		DROP TABLE [UserRoleConnection];
END;

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserStatus')
BEGIN
   CREATE TABLE UserStatus (
    Id INT PRIMARY KEY,
    Description VARCHAR(50) NOT NULL
);

INSERT INTO UserStatus (Id, Description) VALUES
    (1, 'Pending'),
    (2, 'Active'),
    (3, 'Deleted');
END;


IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'User' AND COLUMN_NAME = 'StatusId')
BEGIN
	ALTER TABLE [User]
	ADD StatusId INT;
END;

GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'User' AND COLUMN_NAME = 'Status')
BEGIN
   UPDATE [User]
				SET StatusId =
				CASE
                WHEN Status = 'Activ' THEN 2
                WHEN Status = 'Pending' THEN 1
                WHEN Status = 'Deleted' THEN 3
                ELSE NULL
              END;

	ALTER TABLE [User]
	DROP COLUMN Status;

END;

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME = 'User'
      AND CONSTRAINT_NAME = 'FK_UserStatus'
)
BEGIN
    ALTER TABLE [User]
		ADD CONSTRAINT FK_UserStatus
		FOREIGN KEY (StatusId)
		REFERENCES UserStatus(Id);
END