coxomblr-tables.sql

-- Tabela: Actions
-- Descrição: Guarda todas as ações feitas num post. Ex: like (1), reply (2), share (3)

CREATE TABLE actions
(
  id serial NOT NULL,
  text text,
  creator_id integer,
  post_id integer,
  kind integer NOT NULL,
  created_at timestamp without time zone,
  blog_id integer,
  CONSTRAINT actions_pkey PRIMARY KEY (id),
  CONSTRAINT actions_blog_id_fk FOREIGN KEY (blog_id)
      REFERENCES blogs (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT actions_creator_id_fk FOREIGN KEY (creator_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT actions_post_id_fk FOREIGN KEY (post_id)
      REFERENCES posts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

-- Tabela: Blogs
-- Descrição: Guarda todos os blogs criados pelos usuários

CREATE TABLE blogs
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  url character varying(255) NOT NULL,
  private boolean NOT NULL DEFAULT false,
  creator_id integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT blogs_pkey PRIMARY KEY (id),
  CONSTRAINT blogs_creator_id_fk FOREIGN KEY (creator_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_blogs_on_name
  ON blogs
  USING btree
  (name COLLATE pg_catalog."default");

-- Tabela: Blogs e Lists
-- Descrição: Relação many-to-many entre blogs e lists

CREATE TABLE blogs_lists
(
  list_id integer,
  blog_id integer,
  CONSTRAINT blogs_lists_blog_id_fk FOREIGN KEY (blog_id)
      REFERENCES blogs (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT blogs_lists_list_id_fk FOREIGN KEY (list_id)
      REFERENCES lists (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_blogs_lists_on_list_id_and_blog_id
  ON blogs_lists
  USING btree
  (list_id, blog_id);

-- Tabela: Lists
-- Descrição: Guarda as listas que usuário cria para agrupar os blogs em categorias/interesses

CREATE TABLE lists
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  creator_id integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT lists_pkey PRIMARY KEY (id),
  CONSTRAINT lists_creator_id_fk FOREIGN KEY (creator_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_lists_on_name_and_creator_id
  ON lists
  USING btree
  (name COLLATE pg_catalog."default", creator_id);

-- Tabela: Messages
-- Descrição: Guarda as mensagens trocadas pelos usuários

CREATE TABLE messages
(
  id serial NOT NULL,
  text text,
  receiver_id integer,
  sender_id integer,
  created_at timestamp without time zone,
  CONSTRAINT messages_pkey PRIMARY KEY (id),
  CONSTRAINT messages_receiver_id_fk FOREIGN KEY (receiver_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT messages_sender_id_fk FOREIGN KEY (sender_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

-- Tabela: Posts
-- Descrição: Guarda os posts feitos pelos donos dos blogs

CREATE TABLE posts
(
  id serial NOT NULL,
  title character varying(255) NOT NULL,
  content text NOT NULL,
  blog_id integer,
  creator_id integer,
  published_at timestamp without time zone,
  url character varying(255),
  image_path character varying(255),
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT posts_pkey PRIMARY KEY (id),
  CONSTRAINT posts_blog_id_fk FOREIGN KEY (blog_id)
      REFERENCES blogs (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT posts_creator_id_fk FOREIGN KEY (creator_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

-- Tabela: Posts e Tags
-- Descrição: Relação many-to-many entre posts e tags

CREATE TABLE posts_tags
(
  post_id integer,
  tag_id integer,
  CONSTRAINT posts_tags_post_id_fk FOREIGN KEY (post_id)
      REFERENCES posts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT posts_tags_tag_id_fk FOREIGN KEY (tag_id)
      REFERENCES tags (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_posts_tags_on_post_id_and_tag_id
  ON posts_tags
  USING btree
  (post_id, tag_id);

-- Tabela: Relationships
-- Descrição: Guarda os blogs que os usuários seguem/inscrevem para receber posts no News Feed

CREATE TABLE relationships
(
  id serial NOT NULL,
  followed_id integer,
  follower_id integer,
  created_at timestamp without time zone,
  CONSTRAINT relationships_pkey PRIMARY KEY (id),
  CONSTRAINT relationships_followed_id_fk FOREIGN KEY (followed_id)
      REFERENCES blogs (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT relationships_follower_id_fk FOREIGN KEY (follower_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_relationships_on_followed_id_and_follower_id
  ON relationships
  USING btree
  (followed_id, follower_id);

-- Tabela: Tags
-- Descrição: Guarda as tags criadas durante a publicação de posts

CREATE TABLE tags
(
  id serial NOT NULL,
  tag character varying(255) NOT NULL,
  CONSTRAINT tags_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_tags_on_tag
  ON tags
  USING btree
  (tag COLLATE pg_catalog."default");

-- Tabela: Users
-- Descrição: Guarda os usuários cadastrados no sistema

CREATE TABLE users
(
  id serial NOT NULL,
  name character varying(255) NOT NULL DEFAULT ''::character varying,
  email character varying(255) NOT NULL,
  encrypted_password character varying(255) NOT NULL,
  slug character varying(255) NOT NULL,
  last_seen timestamp without time zone,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE UNIQUE INDEX index_users_on_email
  ON users
  USING btree
  (email COLLATE pg_catalog."default");

CREATE UNIQUE INDEX index_users_on_slug
  ON users
  USING btree
  (slug COLLATE pg_catalog."default");