function.sql 2.59 KB
Newer Older
Marc Egger's avatar
Marc Egger committed
1
2
3
4
5
###
#
# GETFUNCTIONSHASH() is used for checking whether this file has been played properly in DatabaseUpdate.php
#
DROP FUNCTION IF EXISTS GETFUNCTIONSHASH;
Carsten  Rose's avatar
Carsten Rose committed
6
7
8
CREATE FUNCTION GETFUNCTIONSHASH()
  RETURNS TEXT
  DETERMINISTIC
9
  SQL SECURITY INVOKER
Marc Egger's avatar
Marc Egger committed
10
BEGIN
Carsten  Rose's avatar
Carsten Rose committed
11
  RETURN '%%FUNCTIONSHASH%%';
Marc Egger's avatar
Marc Egger committed
12
13
14
END;


15
16
17
18
19
20
###
#
# QMORE(input, maxlen)
# inserts a span into `input` after `maxlen` number of characters and returns it.
#
DROP FUNCTION IF EXISTS QMORE;
Carsten  Rose's avatar
Carsten Rose committed
21
22
23
CREATE FUNCTION QMORE(input TEXT, maxlen INT)
  RETURNS TEXT
  DETERMINISTIC
24
  SQL SECURITY INVOKER
25
BEGIN
Carsten  Rose's avatar
Carsten Rose committed
26
27
28
29
30
31
32
33
34
35
  DECLARE output TEXT;
  IF maxlen < 1 THEN
    SET maxlen = 1;
  END IF;
  IF CHAR_LENGTH(input) > maxlen THEN
    SET output = CONCAT(INSERT(input, maxlen, 0, '<span class="qfq-more-text">'), '</span>');
  ELSE
    SET output = input;
  END IF;
  RETURN output;
Marc Egger's avatar
Marc Egger committed
36
END;
37
38
39
40
41
42
43
44


###
#
# QBAR(input)
# replaces '|' in `input` with '\|'
#
DROP FUNCTION IF EXISTS QBAR;
Carsten  Rose's avatar
Carsten Rose committed
45
46
47
CREATE FUNCTION QBAR(input TEXT)
  RETURNS TEXT
  DETERMINISTIC
48
  SQL SECURITY INVOKER
49
BEGIN
Carsten  Rose's avatar
Carsten Rose committed
50
51
52
  DECLARE output TEXT;
  SET output = REPLACE(input, '|', '\\|');
  RETURN output;
53
54
55
56
57
58
59
60
END;

###
#
# QNL2BR(input)
# replaces '|' in `input` with '\|'
#
DROP FUNCTION IF EXISTS QNL2BR;
Carsten  Rose's avatar
Carsten Rose committed
61
62
63
CREATE FUNCTION QNL2BR(input TEXT)
  RETURNS TEXT
  DETERMINISTIC
64
  SQL SECURITY INVOKER
65
BEGIN
Carsten  Rose's avatar
Carsten Rose committed
66
67
68
  DECLARE output TEXT;
  SET output = REPLACE(REPLACE(input, CHAR(13), ''), CHAR(10), '<br>');
  RETURN output;
Carsten  Rose's avatar
Carsten Rose committed
69
70
71
72
73
END;

###
#
# QIFEMPTY(input, token)
74
# If 'input' is empty|0|0000-00-00|0000-00-00 00:00:00, replace by 'token'
Carsten  Rose's avatar
Carsten Rose committed
75
76
77
78
79
#
DROP FUNCTION IF EXISTS QIFEMPTY;
CREATE FUNCTION QIFEMPTY(input TEXT, token TEXT)
  RETURNS TEXT
  DETERMINISTIC
80
  SQL SECURITY INVOKER
Carsten  Rose's avatar
Carsten Rose committed
81
82
BEGIN
  DECLARE output TEXT;
83
  SET output =
84
      IF(ISNULL(input) OR input = '' OR input = '0' OR input = '0000-00-00' OR input = '0000-00-00 00:00:00', token,
85
         input);
Carsten  Rose's avatar
Carsten Rose committed
86
87
  RETURN output;
END;
Carsten  Rose's avatar
Carsten Rose committed
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

###
#
# strip_tags(input) - copied from https://stackoverflow.com/questions/2627940/remove-html-tags-from-record
#
DROP FUNCTION IF EXISTS strip_tags;
CREATE FUNCTION `strip_tags`(str TEXT)
  RETURNS TEXT
  DETERMINISTIC
  SQL SECURITY INVOKER
BEGIN
  DECLARE start, end INT DEFAULT 1;
  LOOP
    SET start = LOCATE("<", str, start);
    IF (!start) THEN RETURN str; END IF;
    SET end = LOCATE(">", str, start);
    IF (!end) THEN SET end = start; END IF;
    SET str = INSERT(str, start, end - start + 1, "");
  END LOOP;
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
END;

###
#
# QDATE_FORMAT(timestamp)
#
DROP FUNCTION IF EXISTS QDATE_FORMAT;
CREATE FUNCTION `QDATE_FORMAT`(ts DATETIME)
  RETURNS TEXT
  DETERMINISTIC
  SQL SECURITY INVOKER
BEGIN
  DECLARE output TEXT;
  SET output = IF(ts = 0, '-', DATE_FORMAT(ts, "%d.%m.%Y %H:%i"));
  RETURN output;
END;