-
Notifications
You must be signed in to change notification settings - Fork 0
/
functions.sql
75 lines (68 loc) · 2.23 KB
/
functions.sql
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
-- Creates the workspace.
CREATE TABLE IF NOT EXISTS public_suffix (
suffix varchar(255) not null,
primary key (suffix)
) engine=InnoDB default charset=utf8 collate=utf8_general_ci;
-- Loads the public suffix list.
LOAD DATA LOCAL INFILE '/opt/public-suffix/registry.dat'
IGNORE INTO TABLE public_suffix
LINES TERMINATED BY '\n';
DELIMITER $$
-- Extracts the hostname, like `www.rv.net`
-- Sample: http://username:password@hostname:9090/path?arg=value#anchor
DROP FUNCTION IF EXISTS HOSTNAME;
CREATE FUNCTION HOSTNAME (uri VARCHAR(255) CHARSET utf8)
RETURNS VARCHAR(255) CHARSET utf8 NO SQL
BEGIN
DECLARE host VARCHAR(255) CHARSET utf8;
-- removes the scheme
SET host = SUBSTRING_INDEX(uri, '://', -1);
-- removes all chars before [at]
SET host = SUBSTRING_INDEX(host, '@', -1);
-- removes all before the protocol
SET host = SUBSTRING_INDEX(host, ':', 1);
-- removes the path
SET host = SUBSTRING_INDEX(host, '/', 1);
RETURN host;
END $$
-- Returns only the second level domain, like `co.uk`
DROP FUNCTION IF EXISTS SLD;
CREATE FUNCTION SLD (host VARCHAR(255) CHARSET utf8)
RETURNS VARCHAR(50) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE sld VARCHAR(50) CHARSET utf8;
SELECT suffix INTO sld
FROM public_suffix
WHERE suffix = SUBSTRING_INDEX(host, '.', -2);
RETURN sld;
END $$
-- Returns only the top level domain, like `fr`
DROP FUNCTION IF EXISTS TLD;
CREATE FUNCTION TLD (host VARCHAR(255) CHARSET utf8)
RETURNS VARCHAR(5) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE tld VARCHAR(5) CHARSET utf8;
SELECT suffix INTO tld
FROM public_suffix
WHERE suffix = SUBSTRING_INDEX(host, '.', -1);
RETURN tld;
END $$
-- Returns the root domain like `rv.net` for `www.rv.net`
DROP FUNCTION IF EXISTS DOMAIN;
CREATE FUNCTION DOMAIN (uri VARCHAR(255) CHARSET utf8)
RETURNS VARCHAR(255) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE root, host VARCHAR(255) CHARSET utf8;
SET host = HOSTNAME(uri);
SET root = TLD(host);
IF root != '' THEN
SET root = SLD(host);
IF root != '' THEN
SET root = SUBSTRING_INDEX(host, '.', -3);
ELSE
SET root = SUBSTRING_INDEX(host, '.', -2);
END IF;
END IF;
RETURN root;
END $$
DELIMITER ;