cursed

cursed.sql

-- # setup
create extension "uuid-ossp";

-- # users
create table users (
    id serial not null constraint users_pk primary key,
    username varchar(255) not null,
    password bytea not null
);

alter table users
    owner to postgres;

create unique index users_id_uindex
    on users (id);

create unique index users_username_uindex
    on users (username);

-- # sessions

create table sessions
(
    id serial not null constraint sessions_pk primary key,
    user_id integer not null constraint sessions_users_id_fk references users,
    session_id varchar(36) not null
);

alter table sessions
    owner to postgres;

create unique index sessions_id_uindex
    on sessions (id);

create unique index sessions_session_id_uindex
    on sessions (session_id);

-- # posts

create table posts
(
    id serial not null constraint posts_pk primary key,
    user_id integer not null constraint posts_users_id_fk references users,
    content text,
    display_id varchar(36) not null
);

alter table posts
    owner to postgres;

create unique index posts_id_uindex
    on posts (id);

create unique index posts_display_id_uindex
    on posts (display_id);

-- # functions
create or replace function authenticate(uname varchar(255), pwd_sha bytea)
returns varchar(36) as $$
declare
    session_uuid varchar(36);
    user_id int;
begin
    user_id := (select id from users where username = uname and password = pwd_sha);
    if user_id is null then
        return 0;
    else
        session_uuid := uuid_generate_v4();
        insert into sessions (user_id, session_id) values (user_id, session_uuid);
        return session_uuid;
    end if;

end;
$$ language plpgsql;

create or replace function post(session varchar(36), content text)
returns varchar(36) as $$
declare
    userid int;
    display_uuid varchar(36);
begin
    userid := (select user_id from sessions where session_id = session);
    if userid is null then
        return 0;
    else
        display_uuid := uuid_generate_v4();
        insert into posts (user_id, content, display_id) values (userid, content, display_uuid);
        return display_uuid;
    end if;
end;
$$ language plpgsql;

-- all together now
-- create a user
insert into users (username, password) values ('someone', sha256('password'));
-- authenticate and post!
select post((select authenticate('someone', sha256('password'))), 'hewwo!');