documentation-developer/docs/users/sql/install_postgresql.sql at 92a1c3534b157469ac362a65ca135338e47fb0f9 · ibexa/documentation-developer · 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
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
CREATE TABLE
    ibexa_oauth2_client (
        id SERIAL NOT NULL,
        client_name VARCHAR(128) NOT NULL,
        client_identifier VARCHAR(32) NOT NULL,
        client_secret VARCHAR(128) DEFAULT NULL,
        client_active BOOLEAN DEFAULT 'false' NOT NULL,
        client_plain_pkce BOOLEAN DEFAULT 'false' NOT NULL,
        PRIMARY KEY (id)
    );

CREATE UNIQUE INDEX ibexa_oauth2_client_identifier_idx ON ibexa_oauth2_client (client_identifier);

CREATE TABLE
    ibexa_oauth2_client_redirect_uri (
        id SERIAL NOT NULL,
        client_id INT NOT NULL,
        client_redirect_uri VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_client_redirect_uri_client_id_idx ON ibexa_oauth2_client_redirect_uri (client_id);

CREATE INDEX ibexa_oauth2_client_redirect_uri_client_redirect_uri_idx ON ibexa_oauth2_client_redirect_uri (client_redirect_uri);

CREATE UNIQUE INDEX ibexa_oauth2_client_redirect_uri_unique_idx ON ibexa_oauth2_client_redirect_uri (client_id, client_redirect_uri);

CREATE TABLE
    ibexa_oauth2_client_grant (
        id SERIAL NOT NULL,
        client_id INT NOT NULL,
        client_grant VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_client_grant_client_id_idx ON ibexa_oauth2_client_grant (client_id);

CREATE INDEX ibexa_oauth2_client_grant_client_grant_idx ON ibexa_oauth2_client_grant (client_grant);

CREATE UNIQUE INDEX ibexa_oauth2_client_grant_unique_idx ON ibexa_oauth2_client_grant (client_id, client_grant);

CREATE TABLE
    ibexa_oauth2_client_token (
        id SERIAL NOT NULL,
        client_id INT NOT NULL,
        token_id INT NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_client_token_client_id_idx ON ibexa_oauth2_client_token (client_id);

CREATE INDEX ibexa_oauth2_client_token_token_id_idx ON ibexa_oauth2_client_token (token_id);

CREATE UNIQUE INDEX ibexa_oauth2_client_token_unique_idx ON ibexa_oauth2_client_token (client_id, token_id);

CREATE TABLE
    ibexa_oauth2_client_scope (
        id SERIAL NOT NULL,
        client_id INT NOT NULL,
        client_scope VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_client_scope_client_id_idx ON ibexa_oauth2_client_scope (client_id);

CREATE INDEX ibexa_oauth2_client_scope_client_scope_idx ON ibexa_oauth2_client_scope (client_scope);

CREATE UNIQUE INDEX ibexa_oauth2_client_scope_unique_idx ON ibexa_oauth2_client_scope (client_id, client_scope);

CREATE TABLE
    ibexa_oauth2_token_scope (
        id SERIAL NOT NULL,
        token_id INT NOT NULL,
        token_scope VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_token_scope_token_id_idx ON ibexa_oauth2_token_scope (token_id);

CREATE INDEX ibexa_oauth2_token_scope_scope_idx ON ibexa_oauth2_token_scope (token_scope);

CREATE UNIQUE INDEX ibexa_oauth2_token_scope_unique_idx ON ibexa_oauth2_token_scope (token_id, token_scope);

CREATE TABLE
    ibexa_oauth2_refresh_access_token (
        id SERIAL NOT NULL,
        access_token_id INT NOT NULL,
        refresh_token_id INT NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_refresh_access_token_access_token_id_idx ON ibexa_oauth2_refresh_access_token (access_token_id);

CREATE INDEX ibexa_oauth2_refresh_access_token_refresh_token_id_idx ON ibexa_oauth2_refresh_access_token (refresh_token_id);

CREATE UNIQUE INDEX ibexa_oauth2_refresh_access_token_unique_idx ON ibexa_oauth2_refresh_access_token (access_token_id, refresh_token_id);

CREATE TABLE
    ibexa_oauth2_consent (
        id SERIAL NOT NULL,
        user_identifier VARCHAR(150) NOT NULL,
        client_identifier VARCHAR(32) NOT NULL,
        created INT DEFAULT 0 NOT NULL,
        updated INT DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX IDX_40497C0FD0494586 ON ibexa_oauth2_consent (user_identifier);

CREATE INDEX IDX_40497C0FE77ABE2B ON ibexa_oauth2_consent (client_identifier);

CREATE INDEX ibexa_oauth2_consent_consent_idx ON ibexa_oauth2_consent (user_identifier, client_identifier);

CREATE UNIQUE INDEX ibexa_oauth2_consent_unique_idx ON ibexa_oauth2_consent (user_identifier, client_identifier);

CREATE TABLE
    ibexa_oauth2_consent_scope (
        id SERIAL NOT NULL,
        consent_id INT NOT NULL,
        consent_scope VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );

CREATE INDEX ibexa_oauth2_consent_scope_consent_id_idx ON ibexa_oauth2_consent_scope (consent_id);

CREATE INDEX ibexa_oauth2_consent_scope_consent_scope_idx ON ibexa_oauth2_consent_scope (consent_scope);

CREATE UNIQUE INDEX ibexa_oauth2_consent_scope_unique_idx ON ibexa_oauth2_consent_scope (consent_id, consent_scope);

ALTER TABLE ibexa_oauth2_client_redirect_uri ADD CONSTRAINT ibexa_oauth2_client_redirect_uri_fk FOREIGN KEY (client_id) REFERENCES ibexa_oauth2_client (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_client_grant ADD CONSTRAINT ibexa_oauth2_client_grant_fk FOREIGN KEY (client_id) REFERENCES ibexa_oauth2_client (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_client_token ADD CONSTRAINT ibexa_oauth2_client_token_client_fk FOREIGN KEY (client_id) REFERENCES ibexa_oauth2_client (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_client_token ADD CONSTRAINT ibexa_oauth2_client_token_token_fk FOREIGN KEY (token_id) REFERENCES ibexa_token (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_client_scope ADD CONSTRAINT ibexa_oauth2_client_scope_fk FOREIGN KEY (client_id) REFERENCES ibexa_oauth2_client (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_token_scope ADD CONSTRAINT ibexa_oauth2_token_scope_fk FOREIGN KEY (token_id) REFERENCES ibexa_token (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_refresh_access_token ADD CONSTRAINT ibexa_oauth2_refresh_access_token_access_token_fk FOREIGN KEY (access_token_id) REFERENCES ibexa_token (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_refresh_access_token ADD CONSTRAINT ibexa_oauth2_refresh_access_token_refresh_token_fk FOREIGN KEY (refresh_token_id) REFERENCES ibexa_token (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_consent ADD CONSTRAINT ibexa_oauth2_consent_user_fk FOREIGN KEY (user_identifier) REFERENCES ezuser (login) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_consent ADD CONSTRAINT ibexa_oauth2_consent_client_fk FOREIGN KEY (client_identifier) REFERENCES ibexa_oauth2_client (client_identifier) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE ibexa_oauth2_consent_scope ADD CONSTRAINT ibexa_oauth2_consent_scope_fk FOREIGN KEY (consent_id) REFERENCES ibexa_oauth2_consent (id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;