schema.sql

-- Copyright 2020 Open Reaction Database Project Authors
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

-- To initialize the editor database on a fresh Postgres volume:
--
--   $ psql -p 5432 -h localhost -U postgres -f schema.sql
--
-- To populate the editor database from the db/ directory:
--
--   $ ./py/migrate.py

DROP DATABASE IF EXISTS editor;

\c postgres;

CREATE DATABASE editor;

\c editor;

CREATE TABLE users (
  user_id CHARACTER(32) PRIMARY KEY,
  name TEXT,
  created_time INTEGER NOT NULL
);

CREATE TABLE logins (
  access_token TEXT PRIMARY KEY,
  user_id CHARACTER(32) REFERENCES users,
  timestamp INTEGER NOT NULL
);

CREATE TABLE datasets (
  user_id CHARACTER(32) REFERENCES users,
  name TEXT NOT NULL,
  serialized BYTEA NOT NULL,
  PRIMARY KEY (user_id, name)
);

-- System users:
--   "review" owns read-only datasets imported from GitHub pull requests.
--   "test" owns datasets imported from db/ and used only in tests.
INSERT INTO users VALUES 
  -- review:
  ('8df09572f3c74dbcb6003e2eef8e48fc', NULL, EXTRACT(EPOCH FROM NOW())),
  -- test:
  ('680b0d9fe649417cb092d790907bd5a5', NULL, EXTRACT(EPOCH FROM NOW()));