1
|
###
|
2
|
###
|
3
|
#
|
4
|
# GETFUNCTIONSHASH() is used for checking whether this file has been played properly in DatabaseUpdate.php
|
5
|
#
|
6
|
DROP FUNCTION IF EXISTS GETFUNCTIONSHASH;
|
7
|
CREATE FUNCTION GETFUNCTIONSHASH()
|
8
|
RETURNS TEXT
|
9
|
DETERMINISTIC
|
10
|
SQL SECURITY INVOKER
|
11
|
BEGIN
|
12
|
RETURN '%%FUNCTIONSHASH%%';
|
13
|
END;
|
14
|
|
15
|
|
16
|
###
|
17
|
#
|
18
|
# QMORE(input, maxlen)
|
19
|
# inserts a span into `input` after `maxlen` number of characters and returns it.
|
20
|
#
|
21
|
DROP FUNCTION IF EXISTS QMORE;
|
22
|
CREATE FUNCTION QMORE(input TEXT, maxlen INT)
|
23
|
RETURNS TEXT
|
24
|
DETERMINISTIC
|
25
|
SQL SECURITY INVOKER
|
26
|
BEGIN
|
27
|
DECLARE output TEXT;
|
28
|
IF maxlen < 1 THEN
|
29
|
SET maxlen = 1;
|
30
|
END IF;
|
31
|
IF CHAR_LENGTH(input) > maxlen THEN
|
32
|
SET output = CONCAT(INSERT(input, maxlen, 0, '<span class="qfq-more-text">'), '</span>');
|
33
|
ELSE
|
34
|
SET output = input;
|
35
|
END IF;
|
36
|
RETURN output;
|
37
|
END;
|
38
|
|
39
|
|
40
|
###
|
41
|
#
|
42
|
# QBAR(input)
|
43
|
# replaces '|' in `input` with '\|'
|
44
|
#
|
45
|
DROP FUNCTION IF EXISTS QBAR;
|
46
|
CREATE FUNCTION QBAR(input TEXT)
|
47
|
RETURNS TEXT
|
48
|
DETERMINISTIC
|
49
|
SQL SECURITY INVOKER
|
50
|
BEGIN
|
51
|
DECLARE output TEXT;
|
52
|
SET output = REPLACE(input, '|', '\\|');
|
53
|
RETURN output;
|
54
|
END;
|
55
|
|
56
|
###
|
57
|
#
|
58
|
# QCC(input)
|
59
|
# replaces ':' (colon) and ',' (coma) in `input` with '\:' and '\,'
|
60
|
#
|
61
|
DROP FUNCTION IF EXISTS QCC;
|
62
|
CREATE FUNCTION QCC(input TEXT)
|
63
|
RETURNS TEXT
|
64
|
DETERMINISTIC
|
65
|
SQL SECURITY INVOKER
|
66
|
BEGIN
|
67
|
DECLARE output TEXT;
|
68
|
SET output = REPLACE(REPLACE(input, ':', '\\:'), ',', '\\,');
|
69
|
RETURN output;
|
70
|
END;
|
71
|
|
72
|
###
|
73
|
#
|
74
|
# QNL2BR(input)
|
75
|
# replaces '|' in `input` with '\|'
|
76
|
#
|
77
|
DROP FUNCTION IF EXISTS QNL2BR;
|
78
|
CREATE FUNCTION QNL2BR(input TEXT)
|
79
|
RETURNS TEXT
|
80
|
DETERMINISTIC
|
81
|
SQL SECURITY INVOKER
|
82
|
BEGIN
|
83
|
DECLARE output TEXT;
|
84
|
SET output = REPLACE(REPLACE(input, CHAR(13), ''), CHAR(10), '<br>');
|
85
|
RETURN output;
|
86
|
END;
|
87
|
|
88
|
###
|
89
|
#
|
90
|
# QIFEMPTY(input, token)
|
91
|
# If 'input' is empty|0|0000-00-00|0000-00-00 00:00:00, replace by 'token'
|
92
|
#
|
93
|
DROP FUNCTION IF EXISTS QIFEMPTY;
|
94
|
CREATE FUNCTION QIFEMPTY(input TEXT, token TEXT)
|
95
|
RETURNS TEXT
|
96
|
DETERMINISTIC
|
97
|
SQL SECURITY INVOKER
|
98
|
BEGIN
|
99
|
DECLARE output TEXT;
|
100
|
SET output =
|
101
|
IF(ISNULL(input) OR input = '' OR input = '0' OR input = '0000-00-00' OR input = '0000-00-00 00:00:00', token,
|
102
|
input);
|
103
|
RETURN output;
|
104
|
END;
|
105
|
|
106
|
###
|
107
|
#
|
108
|
# strip_tags(input) - copied from https://stackoverflow.com/questions/2627940/remove-html-tags-from-record
|
109
|
#
|
110
|
DROP FUNCTION IF EXISTS strip_tags;
|
111
|
CREATE FUNCTION `strip_tags`(str TEXT)
|
112
|
RETURNS TEXT
|
113
|
DETERMINISTIC
|
114
|
SQL SECURITY INVOKER
|
115
|
BEGIN
|
116
|
DECLARE start, end INT DEFAULT 1;
|
117
|
LOOP
|
118
|
SET start = LOCATE("<", str, start);
|
119
|
IF (!start) THEN RETURN str; END IF;
|
120
|
SET end = LOCATE(">", str, start);
|
121
|
IF (!end) THEN SET end = start; END IF;
|
122
|
SET str = INSERT(str, start, end - start + 1, "");
|
123
|
END LOOP;
|
124
|
END;
|
125
|
|
126
|
###
|
127
|
#
|
128
|
# QDATE_FORMAT(timestamp)
|
129
|
#
|
130
|
DROP FUNCTION IF EXISTS QDATE_FORMAT;
|
131
|
CREATE FUNCTION `QDATE_FORMAT`(ts DATETIME)
|
132
|
RETURNS TEXT
|
133
|
DETERMINISTIC
|
134
|
SQL SECURITY INVOKER
|
135
|
BEGIN
|
136
|
DECLARE output TEXT;
|
137
|
SET output = IF(ts = 0, '-', DATE_FORMAT(ts, "%d.%m.%Y %H:%i"));
|
138
|
RETURN output;
|
139
|
END;
|
140
|
|
141
|
###
|
142
|
#
|
143
|
# QSLUGIFY(string)
|
144
|
#
|
145
|
|
146
|
/*
|
147
|
The MIT License (MIT)
|
148
|
Copyright (c) 2014 jose reis<jose.reis@artbit.pt>
|
149
|
Permission is hereby granted, free of charge, to any person obtaining a copy
|
150
|
of this software and associated documentation files (the "Software"), to deal
|
151
|
in the Software without restriction, including without limitation the rights
|
152
|
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
|
153
|
copies of the Software, and to permit persons to whom the Software is
|
154
|
furnished to do so, subject to the following conditions:
|
155
|
The above copyright notice and this permission notice shall be included in all
|
156
|
copies or substantial portions of the Software.
|
157
|
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
|
158
|
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
|
159
|
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
|
160
|
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
|
161
|
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
|
162
|
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
|
163
|
SOFTWARE.
|
164
|
Credits:
|
165
|
- http://stackoverflow.com/questions/5409831/mysql-stored-function-to-create-a-slug
|
166
|
*/
|
167
|
|
168
|
DROP FUNCTION IF EXISTS `QSLUGIFY`;
|
169
|
CREATE FUNCTION `QSLUGIFY`(dirty_string varchar(255)) RETURNS varchar(255) CHARSET utf8
|
170
|
DETERMINISTIC
|
171
|
BEGIN
|
172
|
DECLARE x, y , z , k INT;
|
173
|
DECLARE temp_string, new_string, accents, noAccents VARCHAR(255);
|
174
|
DECLARE is_allowed BOOL;
|
175
|
DECLARE c, check_char VARCHAR(1);
|
176
|
|
177
|
-- IF NULL DO NOT PROCEED
|
178
|
If dirty_string IS NULL Then
|
179
|
return dirty_string;
|
180
|
End If;
|
181
|
|
182
|
set temp_string = LOWER(dirty_string);
|
183
|
|
184
|
-- REPLACE ACCENTS
|
185
|
-- WITH CAPS
|
186
|
-- set accents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
|
187
|
-- set noAccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
|
188
|
-- ONLY SMALL CAPS
|
189
|
set accents = 'šžàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
|
190
|
set noAccents = 'szaaaaaaaceeeeiiiinoooooouuuuyybf';
|
191
|
set k = CHAR_LENGTH(accents);
|
192
|
|
193
|
while k > 0
|
194
|
do
|
195
|
set temp_string = REPLACE(temp_string, SUBSTRING(accents, k, 1), SUBSTRING(noAccents, k, 1));
|
196
|
set k = k - 1;
|
197
|
end while;
|
198
|
|
199
|
-- CONVERT & TO EMPTY SPACE
|
200
|
Set temp_string = REPLACE(temp_string, '&', '');
|
201
|
|
202
|
-- REPLACE ALL UNWANTED CHARS
|
203
|
Select temp_string REGEXP ('[^a-z0-9\-]+') into x;
|
204
|
If x = 1 then
|
205
|
set z = 1;
|
206
|
set k = CHAR_LENGTH(temp_string);
|
207
|
While z <= k
|
208
|
Do
|
209
|
Set c = SUBSTRING(temp_string, z, 1);
|
210
|
Set is_allowed = FALSE;
|
211
|
If !((ascii(c) = 45) or (ascii(c) >= 48 and ascii(c) <= 57) or
|
212
|
(ascii(c) >= 97 and ascii(c) <= 122)) Then
|
213
|
Set temp_string = REPLACE(temp_string, c, '-');
|
214
|
End If;
|
215
|
set z = z + 1;
|
216
|
End While;
|
217
|
End If;
|
218
|
|
219
|
Select temp_string REGEXP ("^-|-$|'") into x;
|
220
|
If x = 1 Then
|
221
|
Set temp_string = Replace(temp_string, "'", '');
|
222
|
Set z = CHAR_LENGTH(temp_string);
|
223
|
Set y = CHAR_LENGTH(temp_string);
|
224
|
Dash_check:
|
225
|
While z > 1
|
226
|
Do
|
227
|
If STRCMP(SUBSTRING(temp_string, -1, 1), '-') = 0 Then
|
228
|
Set temp_string = SUBSTRING(temp_string, 1, y - 1);
|
229
|
Set y = y - 1;
|
230
|
Else
|
231
|
Leave Dash_check;
|
232
|
End If;
|
233
|
Set z = z - 1;
|
234
|
End While;
|
235
|
End If;
|
236
|
|
237
|
Repeat
|
238
|
Select temp_string REGEXP ("--") into x;
|
239
|
If x = 1 Then
|
240
|
Set temp_string = REPLACE(temp_string, "--", "-");
|
241
|
End If;
|
242
|
Until x <> 1 End Repeat;
|
243
|
|
244
|
If LOCATE('-', temp_string) = 1 Then
|
245
|
Set temp_string = SUBSTRING(temp_string, 2);
|
246
|
End If;
|
247
|
|
248
|
Return temp_string;
|
249
|
END;
|
250
|
|