proxystatistics-simplesamlphp-module/config-templates/mysql_tables.sql at 1e6d0be220e8b3406ef22b001e53af15a2ed2ece · rciam/proxystatistics-simplesamlphp-module · 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
--Statistics for IdPs
CREATE TABLE statistics (
    year INT NOT NULL,
    month INT NOT NULL,
    day INT NOT NULL,
    sourceIdp VARCHAR(255) NOT NULL,
    service VARCHAR(255) NOT NULL,
    count INT,
    INDEX (sourceIdp),
    INDEX (service),
    PRIMARY KEY (year, month, day, sourceIdp, service)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE statistics_detail (
    year INT NOT NULL,
    month INT NOT NULL,
    day INT NOT NULL,
    sourceIdp VARCHAR(255) NOT NULL,
    service VARCHAR(255) NOT NULL,
    user VARCHAR(255) NOT NULL,
    count INT,
    INDEX (sourceIdp),
    INDEX (service),
    PRIMARY KEY (year, month, day, sourceIdp, service, user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE statistics_ip (
    accessed TIMESTAMP NOT NULL,
    sourceIdp VARCHAR(255) NOT NULL,
    service VARCHAR(255) NOT NULL,
    user VARCHAR(255) NOT NULL,
    ip VARBINARY(16) NOT NULL,
    ipVersion VARCHAR(4) NOT NULL,
    INDEX (accessed),
    INDEX (sourceIdp),
    INDEX (service),
    INDEX (user),
    INDEX (ipVersion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--Tables for mapping identifier to name
CREATE TABLE identityProvidersMap(
	entityId VARCHAR(255) NOT NULL,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (entityId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE serviceProvidersMap(
	identifier VARCHAR(255) NOT NULL,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (identifier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;