database.sql

CREATE TABLE IF NOT EXISTS profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  network VARCHAR(255) NOT NULL,
  profile_name VARCHAR(255) NOT NULL,
  UNIQUE KEY `network_profile` (network, profile_name)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS profile_stats (
  profile BIGINT UNSIGNED NOT NULL,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  followers INTEGER DEFAULT 0,
  follows INTEGER DEFAULT 0,
  posts INTEGER DEFAULT 0,
  CONSTRAINT fk_stats_profile FOREIGN KEY (profile) REFERENCES profiles(id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  profile BIGINT UNSIGNED NOT NULL,
  osn_id VARCHAR(255) NOT NULL,
  creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  thumbnail_src VARCHAR(1024),
  display_src VARCHAR(1024),
  is_video BOOLEAN,
  caption TEXT,
  CONSTRAINT fk_posts_profile FOREIGN KEY (profile) REFERENCES profiles(id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS posts_tags_mm (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  CONSTRAINT fk_posts_tags_mm_post_id FOREIGN KEY (post_id) REFERENCES posts(id),
  CONSTRAINT fk_posts_tags_mm_tag_id FOREIGN KEY (tag_id) REFERENCES tags(id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS post_stats (
  post BIGINT UNSIGNED NOT NULL,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  comments INTEGER NOT NULL,
  likes INTEGER NOT NULL,
  CONSTRAINT fk_post_stats_post FOREIGN KEY (post) REFERENCES posts(id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;