Skip to content
This repository was archived by the owner on Jan 29, 2020. It is now read-only.

Fixed problems with incorrect database schema in provided example. #72

Merged
merged 2 commits into from
Dec 28, 2019
Merged

Fixed problems with incorrect database schema in provided example. #72

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
f181155
Fixed issues with incorrect mysql schema in provided example
sheridans Dec 10, 2019
64f9d4c
docs: adds CHANGELOG entry for #72
sheridans Dec 10, 2019
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 2 additions & 0 deletions CHANGELOG.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -24,6 +24,8 @@ All notable changes to this project will be documented in this file, in reverse

- [#71](https://github.com/zendframework/zend-expressive-authentication-oauth2/pull/71) adds a check to `AccessTokenRepository` to verify that a row was returned before checking if a token was revoked, raising an exception if not.

- [#72](https://github.com/zendframework/zend-expressive-authentication-oauth2/pull/72) updates the database schema in provided examples to reflect actual requirements.

## 1.2.0 - 2019-09-01

### Added
Expand Down
145 changes: 85 additions & 60 deletions data/oauth2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,66 +1,91 @@
CREATE TABLE oauth_auth_codes (
id VARCHAR(100),
user_id INTEGER,
client_id INTEGER,
scopes TEXT NULL,
revoked BOOLEAN,
expires_at TIMESTAMP NULL,
PRIMARY KEY(id)
);
--
-- Table structure for table `oauth_access_tokens`
--

CREATE TABLE oauth_access_tokens (
id VARCHAR(100),
user_id VARCHAR(40) NULL,
client_id VARCHAR(40),
name VARCHAR(255) NULL,
scopes TEXT NULL,
revoked BOOLEAN,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
expires_at TIMESTAMP NULL,
PRIMARY KEY(id)
);
CREATE INDEX idx1_oauth_access_tokens ON oauth_access_tokens(user_id);
CREATE TABLE `oauth_access_tokens` (
`id` varchar(100) NOT NULL,
`user_id` int(10) unsigned DEFAULT NULL,
`client_id` int(10) unsigned NOT NULL,
`name` varchar(255) DEFAULT NULL,
`scopes` text,
`revoked` tinyint(1) NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL,
`expires_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_CA42527CA76ED39519EB6921BDA26CCD` (`user_id`,`client_id`),
KEY `IDX_CA42527CA76ED395` (`user_id`),
KEY `IDX_CA42527C19EB6921` (`client_id`)
) ENGINE=InnoDB;

CREATE TABLE oauth_refresh_tokens (
id VARCHAR(100),
access_token_id VARCHAR(100),
revoked BOOLEAN,
expires_at TIMESTAMP NULL,
PRIMARY KEY(id)
);
CREATE INDEX idx1_oauth_refresh_tokens ON oauth_refresh_tokens(access_token_id);
--
-- Table structure for table `oauth_auth_codes`
--

CREATE TABLE oauth_clients (
name VARCHAR(40) NOT NULL,
user_id INTEGER NULL,
secret VARCHAR(100) NULL,
redirect VARCHAR(255),
personal_access_client BOOLEAN,
password_client BOOLEAN,
revoked BOOLEAN,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (name)
);
CREATE INDEX idx1_oauth_clients ON oauth_clients(user_id);
CREATE TABLE `oauth_auth_codes` (
`id` varchar(100) NOT NULL,
`user_id` int(10) unsigned DEFAULT NULL,
`client_id` int(10) unsigned NOT NULL,
`scopes` text,
`revoked` tinyint(1) NOT NULL DEFAULT '0',
`expires_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_BB493F83A76ED395` (`user_id`),
KEY `IDX_BB493F8319EB6921` (`client_id`)
) ENGINE=InnoDB;

CREATE TABLE oauth_personal_access_clients (
client_id INTEGER,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL
);
CREATE INDEX idx1_oauth_personal_access_clients ON oauth_personal_access_clients(client_id);
--
-- Table structure for table `oauth_clients`
--

CREATE TABLE oauth_users (
username VARCHAR(40) NOT NULL,
password VARCHAR(100) NOT NULL,
first_name VARCHAR(80),
last_name VARCHAR(80),
PRIMARY KEY (username)
);
CREATE TABLE `oauth_clients` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`name` varchar(100) NOT NULL,
`secret` varchar(100) DEFAULT NULL,
`redirect` varchar(255) DEFAULT NULL,
`personal_access_client` tinyint(1) DEFAULT NULL,
`password_client` tinyint(1) DEFAULT NULL,
`revoked` tinyint(1) DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_13CE81015E237E06A76ED395BDA26CCD` (`name`,`user_id`),
KEY `IDX_13CE8101A76ED395` (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE oauth_scopes (
id VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
--
-- Table structure for table `oauth_refresh_tokens`
--

CREATE TABLE `oauth_refresh_tokens` (
`id` varchar(100) NOT NULL,
`access_token_id` varchar(100) NOT NULL,
`revoked` tinyint(1) NOT NULL DEFAULT '0',
`expires_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_5AB6872CCB2688BDA26CCD` (`access_token_id`)
) ENGINE=InnoDB;

--
-- Table structure for table `oauth_scopes`
--

CREATE TABLE `oauth_scopes` (
`id` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

--
-- Table structure for table `oauth_users`
--

CREATE TABLE `oauth_users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(320) NOT NULL,
`password` varchar(100) NOT NULL,
`first_name` varchar(80) DEFAULT NULL,
`last_name` varchar(80) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_93804FF8F85E0677` (`username`)
) ENGINE=InnoDB;