· 7 years ago · Jan 16, 2019, 12:10 PM
1# Datamodel for OAuthStoreMySQL
2#
3# You need to add the foreign key constraints for the user ids your are using.
4# I have commented the constraints out, just look for 'usa_id_ref' to enable them.
5#
6# The --SPLIT-- markers are used by the install.php script
7#
8# @version $Id: mysql.sql 156 2010-09-16 15:46:49Z brunobg@corollarium.com $
9# @author Marc Worrell
10#
11
12# Changes:
13#
14# 2010-09-15
15# ALTER TABLE oauth_server_token MODIFY ost_referrer_host varchar(128) not null default '';
16#
17# 2010-07-22
18# ALTER TABLE oauth_consumer_registry DROP INDEX ocr_consumer_key;
19# ALTER TABLE oauth_consumer_registry ADD UNIQUE ocr_consumer_key(ocr_consumer_key,ocr_usa_id_ref,ocr_server_uri)
20#
21# 2010-04-20 (on 103 and 110)
22# ALTER TABLE oauth_consumer_registry MODIFY ocr_consumer_key varchar(128) binary not null;
23# ALTER TABLE oauth_consumer_registry MODIFY ocr_consumer_secret varchar(128) binary not null;
24#
25# 2010-04-20 (on 103 and 110)
26# ALTER TABLE oauth_server_token ADD ost_verifier char(10);
27# ALTER TABLE oauth_server_token ADD ost_callback_url varchar(512);
28#
29# 2008-10-15 (on r48) Added ttl to consumer and server tokens, added named server tokens
30#
31# ALTER TABLE oauth_server_token
32# ADD ost_token_ttl datetime not null default '9999-12-31',
33# ADD KEY (ost_token_ttl);
34#
35# ALTER TABLE oauth_consumer_token
36# ADD oct_name varchar(64) binary not null default '',
37# ADD oct_token_ttl datetime not null default '9999-12-31',
38# DROP KEY oct_usa_id_ref,
39# ADD UNIQUE KEY (oct_usa_id_ref, oct_ocr_id_ref, oct_token_type, oct_name),
40# ADD KEY (oct_token_ttl);
41#
42# 2008-09-09 (on r5) Added referrer host to server access token
43#
44# ALTER TABLE oauth_server_token ADD ost_referrer_host VARCHAR(128) NOT NULL;
45#
46
47
48#
49# Log table to hold all OAuth request when you enabled logging
50#
51
52CREATE TABLE IF NOT EXISTS oauth_log (
53 olg_id int(11) not null auto_increment,
54 olg_osr_consumer_key varchar(64) binary,
55 olg_ost_token varchar(64) binary,
56 olg_ocr_consumer_key varchar(64) binary,
57 olg_oct_token varchar(64) binary,
58 olg_usa_id_ref int(11),
59 olg_received text not null,
60 olg_sent text not null,
61 olg_base_string text not null,
62 olg_notes text not null,
63 olg_timestamp timestamp not null default current_timestamp,
64 olg_remote_ip bigint not null,
65
66 primary key (olg_id),
67 key (olg_osr_consumer_key, olg_id),
68 key (olg_ost_token, olg_id),
69 key (olg_ocr_consumer_key, olg_id),
70 key (olg_oct_token, olg_id),
71 key (olg_usa_id_ref, olg_id)
72
73# , foreign key (olg_usa_id_ref) references any_user_auth (usa_id_ref)
74# on update cascade
75# on delete cascade
76) engine=InnoDB default charset=utf8;
77
78#--SPLIT--
79
80#
81# /////////////////// CONSUMER SIDE ///////////////////
82#
83
84# This is a registry of all consumer codes we got from other servers
85# The consumer_key/secret is obtained from the server
86# We also register the server uri, so that we can find the consumer key and secret
87# for a certain server. From that server we can check if we have a token for a
88# particular user.
89
90CREATE TABLE IF NOT EXISTS oauth_consumer_registry (
91 ocr_id int(11) not null auto_increment,
92 ocr_usa_id_ref int(11),
93 ocr_consumer_key varchar(128) binary not null,
94 ocr_consumer_secret varchar(128) binary not null,
95 ocr_signature_methods varchar(255) not null default 'HMAC-SHA1,PLAINTEXT',
96 ocr_server_uri varchar(255) not null,
97 ocr_server_uri_host varchar(128) not null,
98 ocr_server_uri_path varchar(128) binary not null,
99
100 ocr_request_token_uri varchar(255) not null,
101 ocr_authorize_uri varchar(255) not null,
102 ocr_access_token_uri varchar(255) not null,
103 ocr_timestamp timestamp not null default current_timestamp,
104
105 primary key (ocr_id),
106 unique key (ocr_consumer_key, ocr_usa_id_ref, ocr_server_uri),
107 key (ocr_server_uri),
108 key (ocr_server_uri_host, ocr_server_uri_path),
109 key (ocr_usa_id_ref)
110
111# , foreign key (ocr_usa_id_ref) references any_user_auth(usa_id_ref)
112# on update cascade
113# on delete set null
114) engine=InnoDB default charset=utf8;
115
116#--SPLIT--
117
118# Table used to sign requests for sending to a server by the consumer
119# The key is defined for a particular user. Only one single named
120# key is allowed per user/server combination
121
122CREATE TABLE IF NOT EXISTS oauth_consumer_token (
123 oct_id int(11) not null auto_increment,
124 oct_ocr_id_ref int(11) not null,
125 oct_usa_id_ref int(11) not null,
126 oct_name varchar(64) binary not null default '',
127 oct_token varchar(64) binary not null,
128 oct_token_secret varchar(64) binary not null,
129 oct_token_type enum('request','authorized','access'),
130 oct_token_ttl datetime not null default '9999-12-31',
131 oct_timestamp timestamp not null default current_timestamp,
132
133 primary key (oct_id),
134 unique key (oct_ocr_id_ref, oct_token),
135 unique key (oct_usa_id_ref, oct_ocr_id_ref, oct_token_type, oct_name),
136 key (oct_token_ttl),
137
138 foreign key (oct_ocr_id_ref) references oauth_consumer_registry (ocr_id)
139 on update cascade
140 on delete cascade
141
142# , foreign key (oct_usa_id_ref) references any_user_auth (usa_id_ref)
143# on update cascade
144# on delete cascade
145) engine=InnoDB default charset=utf8;
146
147#--SPLIT--
148
149
150#
151# ////////////////// SERVER SIDE /////////////////
152#
153
154# Table holding consumer key/secret combos an user issued to consumers.
155# Used for verification of incoming requests.
156
157CREATE TABLE IF NOT EXISTS oauth_server_registry (
158 osr_id int(11) not null auto_increment,
159 osr_usa_id_ref int(11),
160 osr_consumer_key varchar(64) binary not null,
161 osr_consumer_secret varchar(64) binary not null,
162 osr_enabled tinyint(1) not null default '1',
163 osr_status varchar(16) not null,
164 osr_requester_name varchar(64) not null,
165 osr_requester_email varchar(64) not null,
166 osr_callback_uri varchar(255) not null,
167 osr_application_uri varchar(255) not null,
168 osr_application_title varchar(80) not null,
169 osr_application_descr text not null,
170 osr_application_notes text not null,
171 osr_application_type varchar(20) not null,
172 osr_application_commercial tinyint(1) not null default '0',
173 osr_issue_date datetime not null,
174 osr_timestamp timestamp not null default current_timestamp,
175
176 primary key (osr_id),
177 unique key (osr_consumer_key),
178 key (osr_usa_id_ref)
179
180# , foreign key (osr_usa_id_ref) references any_user_auth(usa_id_ref)
181# on update cascade
182# on delete set null
183) engine=InnoDB default charset=utf8;
184
185#--SPLIT--
186
187# Nonce used by a certain consumer, every used nonce should be unique, this prevents
188# replaying attacks. We need to store all timestamp/nonce combinations for the
189# maximum timestamp received.
190
191CREATE TABLE IF NOT EXISTS oauth_server_nonce (
192 osn_id int(11) not null auto_increment,
193 osn_consumer_key varchar(64) binary not null,
194 osn_token varchar(64) binary not null,
195 osn_timestamp bigint not null,
196 osn_nonce varchar(80) binary not null,
197
198 primary key (osn_id),
199 unique key (osn_consumer_key, osn_token, osn_timestamp, osn_nonce)
200) engine=InnoDB default charset=utf8;
201
202#--SPLIT--
203
204# Table used to verify signed requests sent to a server by the consumer
205# When the verification is succesful then the associated user id is returned.
206
207CREATE TABLE IF NOT EXISTS oauth_server_token (
208 ost_id int(11) not null auto_increment,
209 ost_osr_id_ref int(11) not null,
210 ost_usa_id_ref int(11) not null,
211 ost_token varchar(64) binary not null,
212 ost_token_secret varchar(64) binary not null,
213 ost_token_type enum('request','access'),
214 ost_authorized tinyint(1) not null default '0',
215 ost_referrer_host varchar(128) not null default '',
216 ost_token_ttl datetime not null default '9999-12-31',
217 ost_timestamp timestamp not null default current_timestamp,
218 ost_verifier char(10),
219 ost_callback_url varchar(512),
220
221 primary key (ost_id),
222 unique key (ost_token),
223 key (ost_osr_id_ref),
224 key (ost_token_ttl),
225
226 foreign key (ost_osr_id_ref) references oauth_server_registry (osr_id)
227 on update cascade
228 on delete cascade
229
230# , foreign key (ost_usa_id_ref) references any_user_auth (usa_id_ref)
231# on update cascade
232# on delete cascade
233) engine=InnoDB default charset=utf8;