Restore the GHTorrent database to postgres instead of mysql (based on mysql-2017-01-19 image)

ght-restore-psql

#!/usr/bin/env bash
# Evan Wilde <[email protected]>
# July 20, 2017

# defaults
user="postgres"
passwd=""
host="localhost"
db="ghtorrent"
tmpdir='/tmp'


usage()
{
  echo "Usage: $0 [-u dbuser ] [-p dbpasswd ] [-h dbhost ] [-d database] [-t tmpdir] dump_dir"
  echo
  echo "Restore a database from CSV and SQL files in dump_dir"
  echo "    -u database user (default: $user)"
  echo "    -p database passwd (default: $passwd)"
  echo "    -h database host (default: $host)"
  echo "    -d database to restore to. Must exist. (default: $db)"
  echo "    -t temporary direcotry (should be at least 50 gb) (default: $tmpdir)"
}

if [ -z $1 ]
then
  usage
  exit 1
fi

# Load arguments
while getopts "u:p:h:d:t:" o
do
  case $o in
    u) user=$OPTARG ;;
    p) passwd=$OPTARG ;;
    h) host=$OPTARG ;;
    d) db=$OPTARG ;;
    t) tmpdir=$OPTARG ;;
    \?) echo "Invalid option -$OPTARG" >&2
      usage
      exit 1
      ;;
  esac
done

# Set up psql command line
if [ -z $passwd ]; then
  psql="psql -q --dbname $db -U $user -h $host --no-password"
else # should hide the password in the cmdline
  psql=" psql -q --dbname $db -U $user -h $host --password=$passwd"
fi

shift $(expr $OPTIND - 1)
dumpDir=$1

if [ ! -e $dumpDir/psql_schema.sql ]; then
  echo "Cannot find $dumpDir/psql_schema.sql to create DB schema"
  exit 1
fi

if [ ! -e $dumpDir/ORDER ]; then
  echo "Cannot find $dumpDir/ORDER to build tables"
  exit 1
fi

# 1. Create database schema
echo "`date` Creating DB schema" >&2
cat $dumpDir/psql_schema.sql |
sed -e "/USE/d" |
sed -e "/COMMENT/s/COMMENT '',/,/" |
sed -e "/COMMENT/s/COMMENT '')/)/" |
sed -e "/SET/d" |
sed -e "/AUTO_INCREMENT/s/AUTO_INCREMENT/SERIAL/" |
sed -E -e "s/ghtorrent\.(.*)/\1/" |
sed -e "s/ghtorrent/$db/" |
sed -E -e "s/DECIMAL\(([0-9]+,[0-9]+)\)/numeric(\1)/" |
sed -e "/TINYINT/s/TINYINT([0-9]*)/int/" |
sed -e "/INT/s/INT([0-9]*)/int/" |
sed -e "/MEDIUMTEXT/s/MEDIUMTEXT/text/" |
sed -e "/VARCHAR/s/VARCHAR([0-9]*)/text/" |
grep -v "^--" | $psql

# 2. Disable FK and load data
while read table; do
  file=`readlink -f "$dumpDir/$table.csv"`
  tmpf=`readlink -f $tmpdir`/$table.csv
  echo "`date` Restoring table $table" >&2
  echo "`date` Removing 0000-00-00 00:00:00 from timestamps ($tmpf)" >&2
  sed -e "s/\"0000-00-00 00:00:00\"/\\\N/g" $file> $tmpf
  mv $tmpf $file
  echo "`date` Disabling Foreign Key Constraints on $table" >&2
  echo "ALTER TABLE $table DISABLE TRIGGER ALL;" | $psql
  echo "\copy $table FROM '$file' WITH DELIMITER AS ',' NULL AS '\\N' ESCAPE '\\' CSV;" | $psql
done < "`readlink -f $dumpDir/ORDER`"

# 3. Reenable FK constraints
while read table; do
  echo "ALTER TABLE $table ENABLE TRIGGER ALL;" | $psql
  echo "`date` Reenabled Foreign Key Constraints on $table" >&2
done < "`readlink -f $dumpDir/ORDER`"

# 4. Create Indices
echo "`date` Creating Indices"
cat $dumpDir/indexes.sql |
sed -E -e "s/\`([^ \n\t\`]*)\`/\1/g" |
sed -E -e "s/ghtorrent\.(.*)/\1/" |
sed -e "/COMMENT/s/COMMENT '')/)/" |
sed -e "/COMMENT/s/COMMENT '',/,/" |
sed -e "/COMMENT/s/COMMENT '';/;/" | $psql

indexes.sql

CREATE UNIQUE INDEX `login` ON `ghtorrent`.`users` (`login` ASC)  COMMENT '';
CREATE UNIQUE INDEX `sha` ON `ghtorrent`.`commits` (`sha` ASC)  COMMENT '';
CREATE INDEX `commit_committer` ON `ghtorrent`.`commits` (`committer_id` ASC) COMMENT '';
CREATE INDEX `commit_author` ON `ghtorrent`.`commits` (`author_id` ASC) COMMENT '';
CREATE INDEX `commit_project` ON `ghtorrent`.`commits` (`project_id` ASC) COMMENT '';
CREATE UNIQUE INDEX `comment_id` ON `ghtorrent`.`commit_comments` (`comment_id` ASC)  COMMENT '';
CREATE INDEX `follower_id` ON `ghtorrent`.`followers` (`follower_id` ASC) COMMENT '';
CREATE UNIQUE INDEX `pullreq_id` ON `ghtorrent`.`pull_requests` (`pullreq_id` ASC, `base_repo_id` ASC)  COMMENT '';
CREATE INDEX `name` ON `ghtorrent`.`projects` (`name` ASC)  COMMENT '';
CREATE INDEX `forked_cid` ON `ghtorrent`.`projects` (`forked_commit_id` ASC) COMMENT '';
CREATE INDEX `forked_from` ON `ghtorrent`.`projects` (`forked_from` ASC) COMMENT '';
CREATE INDEX `commit_id` ON `ghtorrent`.`project_commits` (`commit_id` ASC)  COMMENT '';
CREATE INDEX `project_id` ON `ghtorrent`.`project_languages` (`project_id`) COMMENT '';
CREATE INDEX `project_owner` ON `ghtorrent`.`projects` (`owner_id` ASC) COMMENT '';
CREATE INDEX `repo_id` ON `ghtorrent`.`repo_labels` (`repo_id` ASC) COMMENT '';

psql_schema.sql

DROP SCHEMA IF EXISTS ghtorrent CASCADE;
CREATE SCHEMA IF NOT EXISTS ghtorrent;
SET client_min_messages TO ERROR;

-- -----------------------------------------------------
-- Table ghtorrent.users
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.users ;

CREATE TABLE IF NOT EXISTS ghtorrent.users (
  id SERIAL NOT NULL  COMMENT '',
  login VARCHAR(255) NOT NULL COMMENT '',
  company VARCHAR(255) NULL DEFAULT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  type VARCHAR(255) NOT NULL DEFAULT 'USR' COMMENT '',
  fake TINYINT(1) NOT NULL DEFAULT '0' COMMENT '',
  deleted TINYINT(1) NOT NULL DEFAULT '0' COMMENT '',
  long DECIMAL(11,8) COMMENT '',
  lat DECIMAL(10,8) COMMENT '',
  country_code CHAR(3) COMMENT '',
  state VARCHAR(255) COMMENT '',
  city VARCHAR(255) COMMENT '',
  location VARCHAR(255) NULL DEFAULT NULL COMMENT '',
  PRIMARY KEY (id)  COMMENT '');

-- -----------------------------------------------------
-- Table ghtorrent.projects
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.projects CASCADE;

SET time_zone='+0:00';
CREATE TABLE IF NOT EXISTS ghtorrent.projects (
  id SERIAL NOT NULL COMMENT '',
  url VARCHAR(255) NULL DEFAULT NULL COMMENT '',
  owner_id INT(11) NULL DEFAULT NULL COMMENT '',
  name VARCHAR(255) NOT NULL COMMENT '',
  description VARCHAR(255) NULL DEFAULT NULL COMMENT '',
  language VARCHAR(255) NULL DEFAULT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  forked_from INT(11) NULL DEFAULT NULL COMMENT '',
  deleted TINYINT(1) NOT NULL DEFAULT '0' COMMENT '',
  updated_at TIMESTAMP COMMENT '',
  forked_commit_id INT(11) COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT projects_ibfk_1
    FOREIGN KEY (owner_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT projects_ibfk_2
    FOREIGN KEY (forked_from)
    REFERENCES ghtorrent.projects (id));

-- -----------------------------------------------------
-- Table ghtorrent.commits
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.commits CASCADE ;

CREATE TABLE IF NOT EXISTS ghtorrent.commits (
  id SERIAL NOT NULL COMMENT '',
  sha VARCHAR(40) NULL DEFAULT NULL COMMENT '',
  author_id INT(11) NULL DEFAULT NULL COMMENT '',
  committer_id INT(11) NULL DEFAULT NULL COMMENT '',
  project_id INT(11) NULL DEFAULT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT commits_ibfk_1
    FOREIGN KEY (author_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT commits_ibfk_2
    FOREIGN KEY (committer_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT commits_ibfk_3
    FOREIGN KEY (project_id)
    REFERENCES ghtorrent.projects (id));

-- -----------------------------------------------------
-- Table ghtorrent.commit_comments
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.commit_comments CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.commit_comments (
  id SERIAL NOT NULL  COMMENT '',
  commit_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  body VARCHAR(256) NULL DEFAULT NULL COMMENT '',
  line INT(11) NULL DEFAULT NULL COMMENT '',
  position INT(11) NULL DEFAULT NULL COMMENT '',
  comment_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT commit_comments_ibfk_1
    FOREIGN KEY (commit_id)
    REFERENCES ghtorrent.commits (id),
  CONSTRAINT commit_comments_ibfk_2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.commit_parents
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.commit_parents CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.commit_parents (
  commit_id INT(11) NOT NULL COMMENT '',
  parent_id INT(11) NOT NULL COMMENT '',
  CONSTRAINT commit_parents_ibfk_1
    FOREIGN KEY (commit_id)
    REFERENCES ghtorrent.commits (id),
  CONSTRAINT commit_parents_ibfk_2
    FOREIGN KEY (parent_id)
    REFERENCES ghtorrent.commits (id));

-- -----------------------------------------------------
-- Table ghtorrent.followers
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.followers CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.followers (
  follower_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  PRIMARY KEY (follower_id, user_id)  COMMENT '',
  CONSTRAINT follower_fk1
    FOREIGN KEY (follower_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT follower_fk2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.pull_requests
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.pull_requests CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.pull_requests (
  id SERIAL NOT NULL  COMMENT '',
  head_repo_id INT(11) NULL DEFAULT NULL COMMENT '',
  base_repo_id INT(11) NOT NULL COMMENT '',
  head_commit_id INT(11) NULL DEFAULT NULL COMMENT '',
  base_commit_id INT(11) NOT NULL COMMENT '',
  pullreq_id INT(11) NOT NULL COMMENT '',
  intra_branch TINYINT(1) NOT NULL COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT pull_requests_ibfk_1
    FOREIGN KEY (head_repo_id)
    REFERENCES ghtorrent.projects (id),
  CONSTRAINT pull_requests_ibfk_2
    FOREIGN KEY (base_repo_id)
    REFERENCES ghtorrent.projects (id),
  CONSTRAINT pull_requests_ibfk_3
    FOREIGN KEY (head_commit_id)
    REFERENCES ghtorrent.commits (id),
  CONSTRAINT pull_requests_ibfk_4
    FOREIGN KEY (base_commit_id)
    REFERENCES ghtorrent.commits (id));

-- -----------------------------------------------------
-- Table ghtorrent.issues
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.issues CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.issues (
  id SERIAL NOT NULL  COMMENT '',
  repo_id INT(11) NULL DEFAULT NULL COMMENT '',
  reporter_id INT(11) NULL DEFAULT NULL COMMENT '',
  assignee_id INT(11) NULL DEFAULT NULL COMMENT '',
  pull_request TINYINT(1) NOT NULL COMMENT '',
  pull_request_id INT(11) NULL DEFAULT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  issue_id INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT issues_ibfk_1
    FOREIGN KEY (repo_id)
    REFERENCES ghtorrent.projects (id),
  CONSTRAINT issues_ibfk_2
    FOREIGN KEY (reporter_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT issues_ibfk_3
    FOREIGN KEY (assignee_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT issues_ibfk_4
    FOREIGN KEY (pull_request_id)
    REFERENCES ghtorrent.pull_requests (id));

-- -----------------------------------------------------
-- Table ghtorrent.issue_comments
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.issue_comments CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.issue_comments (
  issue_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  comment_id MEDIUMTEXT NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  CONSTRAINT issue_comments_ibfk_1
    FOREIGN KEY (issue_id)
    REFERENCES ghtorrent.issues (id),
  CONSTRAINT issue_comments_ibfk_2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.issue_events
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.issue_events CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.issue_events (
  event_id MEDIUMTEXT NOT NULL COMMENT '',
  issue_id INT(11) NOT NULL COMMENT '',
  actor_id INT(11) NOT NULL COMMENT '',
  action VARCHAR(255) NOT NULL COMMENT '',
  action_specific VARCHAR(50) NULL DEFAULT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  CONSTRAINT issue_events_ibfk_1
    FOREIGN KEY (issue_id)
    REFERENCES ghtorrent.issues (id),
  CONSTRAINT issue_events_ibfk_2
    FOREIGN KEY (actor_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.repo_labels
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.repo_labels CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.repo_labels (
  id SERIAL NOT NULL COMMENT '',
  repo_id INT(11) NULL DEFAULT NULL COMMENT '',
  name VARCHAR(24) NOT NULL COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT repo_labels_ibfk_1
    FOREIGN KEY (repo_id)
    REFERENCES ghtorrent.projects (id));

-- -----------------------------------------------------
-- Table ghtorrent.issue_labels
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.issue_labels CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.issue_labels (
  label_id INT(11) NOT NULL COMMENT '',
  issue_id INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (issue_id, label_id)  COMMENT '',
  CONSTRAINT issue_labels_ibfk_1
    FOREIGN KEY (label_id)
    REFERENCES ghtorrent.repo_labels (id),
  CONSTRAINT issue_labels_ibfk_2
    FOREIGN KEY (issue_id)
    REFERENCES ghtorrent.issues (id));

-- -----------------------------------------------------
-- Table ghtorrent.organization_members
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.organization_members CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.organization_members (
  org_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  PRIMARY KEY (org_id, user_id)  COMMENT '',
  CONSTRAINT organization_members_ibfk_1
    FOREIGN KEY (org_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT organization_members_ibfk_2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.project_commits
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.project_commits CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.project_commits (
  project_id INT(11) NOT NULL DEFAULT '0' COMMENT '',
  commit_id INT(11) NOT NULL DEFAULT '0' COMMENT '',
  CONSTRAINT project_commits_ibfk_1
    FOREIGN KEY (project_id)
    REFERENCES ghtorrent.projects (id),
  CONSTRAINT project_commits_ibfk_2
    FOREIGN KEY (commit_id)
    REFERENCES ghtorrent.commits (id));

-- -----------------------------------------------------
-- Table ghtorrent.project_members
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.project_members CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.project_members (
  repo_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  ext_ref_id VARCHAR(24) NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (repo_id, user_id)  COMMENT '',
  CONSTRAINT project_members_ibfk_1
    FOREIGN KEY (repo_id)
    REFERENCES ghtorrent.projects (id),
  CONSTRAINT project_members_ibfk_2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.project_languages
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.project_languages CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.project_languages (
  project_id INT(11) NOT NULL COMMENT '',
  language VARCHAR(255) NULL DEFAULT NULL COMMENT '',
  bytes INT(11) COMMENT '',
  created_at TIMESTAMP COMMENT '',
  CONSTRAINT project_languages_ibfk_1
    FOREIGN KEY (project_id)
    REFERENCES ghtorrent.projects (id));

-- -----------------------------------------------------
-- Table ghtorrent.pull_request_comments
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.pull_request_comments CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.pull_request_comments (
  pull_request_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  comment_id MEDIUMTEXT NOT NULL COMMENT '',
  position INT(11) NULL DEFAULT NULL COMMENT '',
  body VARCHAR(256) NULL DEFAULT NULL COMMENT '',
  commit_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  CONSTRAINT pull_request_comments_ibfk_1
    FOREIGN KEY (pull_request_id)
    REFERENCES ghtorrent.pull_requests (id),
  CONSTRAINT pull_request_comments_ibfk_2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id),
  CONSTRAINT pull_request_comments_ibfk_3
    FOREIGN KEY (commit_id)
    REFERENCES ghtorrent.commits (id));

-- -----------------------------------------------------
-- Table ghtorrent.pull_request_commits
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.pull_request_commits CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.pull_request_commits (
  pull_request_id INT(11) NOT NULL COMMENT '',
  commit_id INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (pull_request_id, commit_id)  COMMENT '',
  CONSTRAINT pull_request_commits_ibfk_1
    FOREIGN KEY (pull_request_id)
    REFERENCES ghtorrent.pull_requests (id),
  CONSTRAINT pull_request_commits_ibfk_2
    FOREIGN KEY (commit_id)
    REFERENCES ghtorrent.commits (id));

-- -----------------------------------------------------
-- Table ghtorrent.pull_request_history
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.pull_request_history CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.pull_request_history (
  id SERIAL NOT NULL COMMENT '',
  pull_request_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  action VARCHAR(255) NOT NULL COMMENT '',
  actor_id INT(11) NULL DEFAULT NULL COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT pull_request_history_ibfk_1
    FOREIGN KEY (pull_request_id)
    REFERENCES ghtorrent.pull_requests (id),
  CONSTRAINT pull_request_history_ibfk_2
    FOREIGN KEY (actor_id)
    REFERENCES ghtorrent.users (id));

-- -----------------------------------------------------
-- Table ghtorrent.repo_milestones
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.repo_milestones CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.repo_milestones (
  id SERIAL NOT NULL COMMENT '',
  repo_id INT(11) NULL DEFAULT NULL COMMENT '',
  name VARCHAR(24) NOT NULL COMMENT '',
  PRIMARY KEY (id)  COMMENT '',
  CONSTRAINT repo_milestones_ibfk_1
    FOREIGN KEY (repo_id)
    REFERENCES ghtorrent.projects (id));

-- -----------------------------------------------------
-- Table ghtorrent.schema_info
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.schema_info CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.schema_info (
  version INT(11) NOT NULL DEFAULT '0' COMMENT '');

-- -----------------------------------------------------
-- Table ghtorrent.watchers
-- -----------------------------------------------------
DROP TABLE IF EXISTS ghtorrent.watchers CASCADE;

CREATE TABLE IF NOT EXISTS ghtorrent.watchers (
  repo_id INT(11) NOT NULL COMMENT '',
  user_id INT(11) NOT NULL COMMENT '',
  created_at TIMESTAMP COMMENT '',
  PRIMARY KEY (repo_id, user_id)  COMMENT '',
  CONSTRAINT watchers_ibfk_1
    FOREIGN KEY (repo_id)
    REFERENCES ghtorrent.projects (id),
  CONSTRAINT watchers_ibfk_2
    FOREIGN KEY (user_id)
    REFERENCES ghtorrent.users (id));