This repository was archived by the owner on Sep 20, 2025. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathSQLHistory.sql
More file actions
158 lines (125 loc) · 5.77 KB
/
SQLHistory.sql
File metadata and controls
158 lines (125 loc) · 5.77 KB
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
-- 2022-06-14
ALTER TABLE `scpsl_unigamia`.`scpsl_user_id_bans`
ADD COLUMN `date_unbanned` DATETIME NULL DEFAULT NULL AFTER `unbanned`,
ADD COLUMN `date_modified` DATETIME NULL DEFAULT NULL AFTER `date_display`,
CHANGE COLUMN `display_end_date` `date_display` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `display_start_date` `date_created` DATETIME NULL DEFAULT NULL ;
ALTER TABLE `scpsl_unigamia`.`scpsl_ip_bans`
ADD COLUMN `date_unbanned` DATETIME NULL DEFAULT NULL AFTER `unbanned`,
ADD COLUMN `date_display` DATETIME NULL DEFAULT NULL AFTER `date_unbanned`,
ADD COLUMN `date_modified` DATETIME NULL DEFAULT NULL AFTER `date_display`,
ADD COLUMN `date_created` DATETIME NULL DEFAULT NULL AFTER `date_modified`;
DROP TRIGGER IF EXISTS `scpsl_unigamia`.`scpsl_user_id_bans_BEFORE_INSERT`;
DELIMITER $$
USE `scpsl_unigamia`$$
CREATE DEFINER=`scpsl`@`localhost` TRIGGER `scpsl_unigamia`.`scpsl_user_id_bans_BEFORE_INSERT` BEFORE INSERT ON `scpsl_user_id_bans` FOR EACH ROW
BEGIN
IF NEW.end_date < 636503616170000000 -- 2018-01-01 00:00:17
OR NEW.start_date < 636503616170000000
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ban created with start date or end date before 2018.';
END IF;
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `scpsl_unigamia`.`scpsl_ip_bans_BEFORE_INSERT`;
DELIMITER $$
USE `scpsl_unigamia`$$
CREATE DEFINER=`scpsl`@`localhost` TRIGGER `scpsl_ip_bans_BEFORE_INSERT` BEFORE INSERT ON `scpsl_ip_bans` FOR EACH ROW BEGIN
IF NEW.end_date < 636503616170000000 -- 2018-01-01 00:00:17
OR NEW.start_date < 636503616170000000
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ban created with start date or end date before 2018.';
END IF;
END$$
DELIMITER ;
SET SQL_SAFE_UPDATES = 0;
-- DELETE FROM scpsl_user_id_bans WHERE id = 13654;
-- DELETE FROM scpsl_user_id_bans WHERE id = 5988;
-- DELETE FROM scpsl_user_id_bans WHERE id = 6173;
-- SELECT * FROM scpsl_user_id_bans WHERE user_id = '76561198877453127@steam' AND end_date = 652573631124072530 ; -- 1235
-- DELETE FROM scpsl_user_id_bans WHERE id = 13655;
-- SELECT * FROM scpsl_user_id_bans WHERE user_id = '76561198447338613@steam' AND end_date = 637150098340000000; -- 6181
-- DELETE FROM scpsl_user_id_bans WHERE id = 6407;
-- DELETE FROM scpsl_user_id_bans WHERE id = 13656;
-- SELECT * FROM scpsl_user_id_bans WHERE user_id = '76561198868518061@steam' AND end_date = 652743380629150470; -- 3845
-- DELETE FROM scpsl_user_id_bans WHERE id = 13657;
-- DELETE FROM scpsl_user_id_bans WHERE id = 8826;
-- SELECT * FROM scpsl_user_id_bans WHERE user_id = '76561198364814880@steam' AND end_date = 637350447508893270; -- 8750
-- SELECT * FROM scpsl_user_id_bans WHERE start_date < 636503616170000000;
UPDATE scpsl_user_id_bans SET
date_created = FROM_DOTNETTICKS(start_date)
WHERE
date_created IS NULL;
UPDATE scpsl_user_id_bans SET
date_modified = date_created
WHERE
date_modified IS NULL;
UPDATE scpsl_user_id_bans SET
date_display = FROM_DOTNETTICKS(end_date)
WHERE
date_display IS NULL;
UPDATE scpsl_user_id_bans SET
date_modified = FROM_DOTNETTICKS(end_date)
WHERE
`active` = 0 AND `unbanned` = 0;
ALTER TABLE `scpsl_unigamia`.`scpsl_user_id_bans`
CHANGE COLUMN `date_display` `date_display` DATETIME NOT NULL ,
CHANGE COLUMN `date_modified` `date_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
CHANGE COLUMN `date_created` `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;
-- DELETE FROM scpsl_ip_bans WHERE id = 10812;
-- DELETE FROM scpsl_ip_bans WHERE id = 5134;
-- SELECT * FROM scpsl_ip_bans WHERE user_id = '73.74.44.103' AND end_date = 637134321903887160 ;
-- DELETE FROM scpsl_ip_bans WHERE id = 5822;
-- DELETE FROM scpsl_ip_bans WHERE id = 10813;
-- SELECT * FROM scpsl_ip_bans WHERE user_id = '50.53.130.144' AND end_date = 652446129555760100 ; -- 209
-- SELECT * FROM scpsl_ip_bans WHERE start_date < 636503616170000000;
UPDATE scpsl_ip_bans SET
date_created = FROM_DOTNETTICKS(start_date)
WHERE
date_created IS NULL;
UPDATE scpsl_ip_bans SET
date_modified = date_created
WHERE
date_modified IS NULL;
UPDATE scpsl_ip_bans SET
date_display = FROM_DOTNETTICKS(end_date)
WHERE
date_display IS NULL;
UPDATE scpsl_ip_bans SET
date_modified = FROM_DOTNETTICKS(end_date)
WHERE
`active` = 0 AND `unbanned` = 0;
ALTER TABLE `scpsl_unigamia`.`scpsl_ip_bans`
CHANGE COLUMN `date_display` `date_display` DATETIME NOT NULL ,
CHANGE COLUMN `date_modified` `date_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
CHANGE COLUMN `date_created` `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;
DROP TRIGGER IF EXISTS `scpsl_unigamia`.`scpsl_user_id_bans_BEFORE_UPDATE`;
DELIMITER $$
USE `scpsl_unigamia`$$
CREATE DEFINER = `scpsl`@`localhost` TRIGGER `scpsl_unigamia`.`scpsl_user_id_bans_BEFORE_UPDATE` BEFORE UPDATE ON `scpsl_user_id_bans` FOR EACH ROW
BEGIN
-- If a readonly column is being modified
IF OLD.id != NEW.id
OR OLD.user_id != NEW.user_id
OR OLD.end_date != NEW.end_date
OR OLD.start_date != NEW.start_date
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unique ban columns are read-only.';
END IF;
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `scpsl_unigamia`.`scpsl_ip_bans_BEFORE_UPDATE`;
DELIMITER $$
USE `scpsl_unigamia`$$
CREATE DEFINER = `scpsl`@`localhost` TRIGGER `scpsl_unigamia`.`scpsl_ip_bans_BEFORE_UPDATE` BEFORE UPDATE ON `scpsl_ip_bans` FOR EACH ROW
BEGIN
-- If a readonly column is being modified
IF OLD.id != NEW.id
OR OLD.user_id != NEW.user_id
OR OLD.end_date != NEW.end_date
OR OLD.start_date != NEW.start_date
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unique ban columns are read-only.';
END IF;
END$$
DELIMITER ;