dump.py

from sqlalchemy import create_engine
from jupyterhub.orm import Base


def dump(sql, *multiparams, **params):
    print(sql.compile(dialect=engine.dialect))


engine = create_engine('postgresql://', strategy='mock', executor=dump)

Base.metadata.create_all(engine, checkfirst=False)

jupyterhub-1.3.0.psql

CREATE TYPE granttype AS ENUM ('authorization_code', 'implicit', 'password', 'client_credentials', 'refresh_token')

CREATE TABLE servers (
	id SERIAL NOT NULL, 
	proto VARCHAR(15), 
	ip VARCHAR(255), 
	port INTEGER, 
	base_url VARCHAR(255), 
	cookie_name VARCHAR(255), 
	PRIMARY KEY (id)
)



CREATE TABLE groups (
	id SERIAL NOT NULL, 
	name VARCHAR(255), 
	PRIMARY KEY (id), 
	UNIQUE (name)
)



CREATE TABLE users (
	id SERIAL NOT NULL, 
	name VARCHAR(255), 
	admin BOOLEAN, 
	created TIMESTAMP WITHOUT TIME ZONE, 
	last_activity TIMESTAMP WITHOUT TIME ZONE, 
	cookie_id VARCHAR(255) NOT NULL, 
	state TEXT, 
	encrypted_auth_state BYTEA, 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	UNIQUE (cookie_id)
)



CREATE TABLE oauth_clients (
	id SERIAL NOT NULL, 
	identifier VARCHAR(255), 
	description VARCHAR(1023), 
	secret VARCHAR(255), 
	redirect_uri VARCHAR(1023), 
	PRIMARY KEY (id), 
	UNIQUE (identifier)
)



CREATE TABLE user_group_map (
	user_id INTEGER NOT NULL, 
	group_id INTEGER NOT NULL, 
	PRIMARY KEY (user_id, group_id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(group_id) REFERENCES groups (id) ON DELETE CASCADE
)



CREATE TABLE spawners (
	id SERIAL NOT NULL, 
	user_id INTEGER, 
	server_id INTEGER, 
	state TEXT, 
	name VARCHAR(255), 
	started TIMESTAMP WITHOUT TIME ZONE, 
	last_activity TIMESTAMP WITHOUT TIME ZONE, 
	user_options TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(server_id) REFERENCES servers (id) ON DELETE SET NULL
)



CREATE TABLE services (
	id SERIAL NOT NULL, 
	name VARCHAR(255), 
	admin BOOLEAN, 
	_server_id INTEGER, 
	pid INTEGER, 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	FOREIGN KEY(_server_id) REFERENCES servers (id) ON DELETE SET NULL
)



CREATE TABLE oauth_access_tokens (
	id SERIAL NOT NULL, 
	client_id VARCHAR(255), 
	grant_type granttype NOT NULL, 
	expires_at INTEGER, 
	refresh_token VARCHAR(255), 
	refresh_expires_at INTEGER, 
	user_id INTEGER, 
	session_id VARCHAR(255), 
	hashed VARCHAR(255), 
	prefix VARCHAR(16), 
	created TIMESTAMP WITHOUT TIME ZONE, 
	last_activity TIMESTAMP WITHOUT TIME ZONE, 
	PRIMARY KEY (id), 
	FOREIGN KEY(client_id) REFERENCES oauth_clients (identifier) ON DELETE CASCADE, 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	UNIQUE (hashed)
)


CREATE INDEX ix_oauth_access_tokens_prefix ON oauth_access_tokens (prefix)

CREATE TABLE oauth_codes (
	id SERIAL NOT NULL, 
	client_id VARCHAR(255), 
	code VARCHAR(36), 
	expires_at INTEGER, 
	redirect_uri VARCHAR(1023), 
	session_id VARCHAR(255), 
	user_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(client_id) REFERENCES oauth_clients (identifier) ON DELETE CASCADE, 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
)



CREATE TABLE api_tokens (
	user_id INTEGER, 
	service_id INTEGER, 
	id SERIAL NOT NULL, 
	hashed VARCHAR(255), 
	prefix VARCHAR(16), 
	created TIMESTAMP WITHOUT TIME ZONE, 
	expires_at TIMESTAMP WITHOUT TIME ZONE, 
	last_activity TIMESTAMP WITHOUT TIME ZONE, 
	note VARCHAR(1023), 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(service_id) REFERENCES services (id) ON DELETE CASCADE, 
	UNIQUE (hashed)
)


CREATE INDEX ix_api_tokens_prefix ON api_tokens (prefix)