bigdata.sql

-- It's a joke.
drop schema if exists bigdata cascade;

create schema bigdata;
create extension if not exists citext;
set search_path to bigdata, public, pg_catalog;

create table users (
  id serial primary key,
  username citext not null unique,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table forums (
  id serial primary key,
  name text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table topics (
  id serial primary key,
  forum_id int not null references forums on delete cascade,
  author_id int not null references users on delete cascade,
  title text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table posts (
  id serial primary key,
  topic_id int not null references topics on delete cascade,
  author_id int not null references users on delete cascade,
  body text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

do $$
declare
  NUMBER_OF_USERS int = 10000;
  NUMBER_OF_TOPICS_PER_FORUM int = 10000;
begin

  insert into users (id, username)
    select
      user_number,
      'user' || user_number
    from generate_series(1, 100000) user_number;

  insert into forums (id, name) values
    (1, 'People'),
    (2, 'Cats'),
    (3, 'Dogs'),
    (4, 'Fish'),
    (5, 'Birds'),
    (6, 'Plants'),
    (7, 'Secrets');

  insert into topics (forum_id, author_id, title)
    select
      forums.id,
      1 + ((forums.id * NUMBER_OF_TOPICS_PER_FORUM + topic_number) % NUMBER_OF_USERS),
      'There are ' || topic_number || ' ' || forums.name || ', the rest of you are all wrong.'
    from forums, generate_series(1, NUMBER_OF_TOPICS_PER_FORUM) topic_number;

  insert into posts (topic_id, author_id, body)
    select
      topics.id,
      1 + ((topics.id + post_number) % NUMBER_OF_USERS),
      'Reply to ' || topics.title || ' number ' || post_number
    from topics, generate_series(1, power(topics.id % 10, 2)::int) post_number;

end;
$$ language plpgsql;

create index on topics(forum_id);
create index on topics(author_id);

create index on posts(topic_id);
create index on posts(author_id);