sql.initial-postgres.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of platform Show documentation

A standard set of utilities that makes it easy to build systems that need users, billing, etc giving the usefulness of a PaaS with the control of a local application

There is a newer version: 0.5.11

Show newest version

create table profound_session (
	session_key BIGSERIAL,
    user_key BIGINT NOT NULL,
    token CHAR(64) NOT NULL,
    PRIMARY KEY ("user_key")
);

CREATE INDEX p_sessions_token_idx ON profound_session USING BTREE (token);
CREATE INDEX p_sessions_user_idx ON profound_session USING BTREE (user_key);

create table profound_user_email (
    user_key BIGSERIAL,
    email_address VARCHAR(255) NOT NULL,
    validated_on TIMESTAMP,
    PRIMARY KEY (user_key, email_address)
);

CREATE INDEX p_user_email_email_idx ON profound_user_email USING BTREE (email_address);


create table profound_user_role (
    user_key BIGINT NOT NULL,
    role VARCHAR(255) NOT NULL,
    PRIMARY KEY (user_key, role)
);


create table profound_user (
	user_key BIGSERIAL,
    created_on TIMESTAMP,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    name VARCHAR(255),
    organization VARCHAR(255),
    correspondence_email_address VARCHAR(255),
    facebook_id VARCHAR(255),
    google_id VARCHAR(255),
    twitter_id INTEGER,
    linkedin_id VARCHAR(255),
    intercom_id VARCHAR(255),
    encrypted_password VARCHAR(255),
    last_seen TIMESTAMP,
    PRIMARY KEY ("user_key")
);

CREATE INDEX p_user_facebook ON profound_user USING BTREE (facebook_id);
CREATE INDEX p_user_google ON profound_user USING BTREE (google_id);
CREATE INDEX p_user_twitter ON profound_user USING BTREE (twitter_id);
CREATE INDEX p_user_intercom ON profound_user USING BTREE (intercom_id);
CREATE INDEX p_user_linkedin ON profound_user USING BTREE (linkedin_id);

ALTER TABLE profound_user ADD COLUMN created_by BIGINT;
ALTER TABLE profound_user_email ADD CONSTRAINT unique_email UNIQUE(email_address);

INSERT INTO profound_user VALUES ( 1, current_timestamp, 'Protea', 'Administration', NULL, 'Protea Corporate Consulting', '[email protected]', NULL, NULL, NULL, NULL, NULL, 'yjNTZFe5IeQqM5klc+1UWjo6nLuRJwK3', null); -- password is password
INSERT INTO profound_user_role VALUES ( 1, 'ADMIN' );

--
-- Don't forget REDIS
--
-- sadd users:1:roles ADMIN
-- sadd users:roles:ADMIN 1