1CREATE TABLE IF NOT EXISTS handles (
  2  id INTEGER PRIMARY KEY AUTOINCREMENT,
  3  handle TEXT NOT NULL UNIQUE,
  4  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  5  updated_at TIMESTAMP NOT NULL
  6);
  7
  8CREATE TABLE IF NOT EXISTS organizations (
  9  id INTEGER PRIMARY KEY AUTOINCREMENT,
 10  name TEXT,
 11  contact_email TEXT NOT NULL,
 12  handle_id INTEGER NOT NULL,
 13  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 14  updated_at TIMESTAMP NOT NULL,
 15  CONSTRAINT handle_id_fk
 16  FOREIGN KEY(handle_id) REFERENCES handles(id)
 17  ON DELETE CASCADE
 18  ON UPDATE CASCADE
 19);
 20
 21CREATE TABLE IF NOT EXISTS organization_members (
 22  id INTEGER PRIMARY KEY AUTOINCREMENT,
 23  org_id INTEGER NOT NULL,
 24  user_id INTEGER NOT NULL,
 25  access_level INTEGER NOT NULL,
 26  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 27  updated_at TIMESTAMP NOT NULL,
 28  UNIQUE (org_id, user_id),
 29  CONSTRAINT org_id_fk
 30  FOREIGN KEY(org_id) REFERENCES organizations(id)
 31  ON DELETE CASCADE
 32  ON UPDATE CASCADE,
 33  CONSTRAINT user_id_fk
 34  FOREIGN KEY(user_id) REFERENCES users(id)
 35  ON DELETE CASCADE
 36  ON UPDATE CASCADE
 37);
 38
 39CREATE TABLE IF NOT EXISTS teams (
 40  id INTEGER PRIMARY KEY AUTOINCREMENT,
 41  name TEXT NOT NULL,
 42  org_id INTEGER NOT NULL,
 43  access_level INTEGER NOT NULL,
 44  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 45  updated_at TIMESTAMP NOT NULL,
 46  UNIQUE (name, org_id),
 47  CONSTRAINT org_id_fk
 48  FOREIGN KEY(org_id) REFERENCES organizations(id)
 49  ON DELETE CASCADE
 50  ON UPDATE CASCADE
 51);
 52
 53CREATE TABLE IF NOT EXISTS team_members (
 54  id INTEGER PRIMARY KEY AUTOINCREMENT,
 55  team_id INTEGER NOT NULL,
 56  user_id INTEGER NOT NULL,
 57  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 58  updated_at TIMESTAMP NOT NULL,
 59  UNIQUE (team_id, user_id),
 60  CONSTRAINT team_id_fk
 61  FOREIGN KEY(team_id) REFERENCES teams(id)
 62  ON DELETE CASCADE
 63  ON UPDATE CASCADE,
 64  CONSTRAINT user_id_fk
 65  FOREIGN KEY(user_id) REFERENCES users(id)
 66  ON DELETE CASCADE
 67  ON UPDATE CASCADE
 68);
 69
 70CREATE TABLE IF NOT EXISTS user_emails (
 71  id INTEGER PRIMARY KEY AUTOINCREMENT,
 72  user_id INTEGER NOT NULL,
 73  email TEXT NOT NULL UNIQUE,
 74  is_primary BOOLEAN NOT NULL,
 75  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 76  updated_at TIMESTAMP NOT NULL,
 77  CONSTRAINT user_id_fk
 78  FOREIGN KEY(user_id) REFERENCES users(id)
 79  ON DELETE CASCADE
 80  ON UPDATE CASCADE
 81);
 82
 83ALTER TABLE users RENAME TO _users_old;
 84
 85CREATE TABLE IF NOT EXISTS users (
 86  id INTEGER PRIMARY KEY AUTOINCREMENT,
 87  name TEXT,
 88  handle_id INTEGER NOT NULL UNIQUE,
 89  admin BOOLEAN NOT NULL,
 90  password TEXT,
 91  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 92  updated_at DATETIME NOT NULL,
 93  CONSTRAINT handle_id_fk
 94  FOREIGN KEY(handle_id) REFERENCES handles(id)
 95  ON DELETE CASCADE
 96  ON UPDATE CASCADE
 97);
 98
 99-- Migrate user username to handles
100INSERT INTO handles (handle, updated_at) SELECT username, updated_at FROM _users_old;
101
102-- Migrate users
103INSERT INTO users (id, handle_id, admin, password, created_at, updated_at) SELECT id, (
104  SELECT id FROM handles WHERE handle = _users_old.username
105), admin, password, created_at, updated_at FROM _users_old;
106
107-- Drop old table
108DROP TABLE _users_old;
109
110ALTER TABLE repos RENAME TO _repos_old;
111
112CREATE TABLE IF NOT EXISTS repos (
113  id INTEGER PRIMARY KEY AUTOINCREMENT,
114  name TEXT NOT NULL UNIQUE,
115  project_name TEXT NOT NULL,
116  description TEXT NOT NULL,
117  private BOOLEAN NOT NULL,
118  mirror BOOLEAN NOT NULL,
119  hidden BOOLEAN NOT NULL,
120  user_id INTEGER,
121  org_id INTEGER,
122  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
123  updated_at DATETIME NOT NULL,
124  CONSTRAINT user_id_fk
125  FOREIGN KEY(user_id) REFERENCES users(id)
126  ON DELETE CASCADE
127  ON UPDATE CASCADE,
128  CONSTRAINT org_id_fk
129  FOREIGN KEY(org_id) REFERENCES organizations(id)
130  ON DELETE CASCADE
131  ON UPDATE CASCADE,
132  CONSTRAINT user_id_org_id_not_null
133  CHECK (user_id IS NULL <> org_id IS NULL)
134);
135
136-- Migrate repos
137INSERT INTO repos (id, name, project_name, description, private, mirror, hidden, user_id, created_at, updated_at)
138SELECT id, name, project_name, description, private, mirror, hidden, user_id, created_at, updated_at
139FROM _repos_old;
140
141-- Drop old table
142DROP TABLE _repos_old;
143
144-- Alter collabs table
145ALTER TABLE collabs RENAME TO _collabs_old;
146
147CREATE TABLE IF NOT EXISTS collabs (
148  id INTEGER PRIMARY KEY AUTOINCREMENT,
149  user_id INTEGER,
150  team_id INTEGER,
151  repo_id INTEGER NOT NULL,
152  access_level INTEGER NOT NULL,
153  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
154  updated_at DATETIME NOT NULL,
155  UNIQUE (user_id, team_id, repo_id),
156  CONSTRAINT user_id_fk
157  FOREIGN KEY(user_id) REFERENCES users(id)
158  ON DELETE CASCADE
159  ON UPDATE CASCADE,
160  CONSTRAINT team_id_fk
161  FOREIGN KEY(team_id) REFERENCES teams(id)
162  ON DELETE CASCADE
163  ON UPDATE CASCADE,
164  CONSTRAINT repo_id_fk
165  FOREIGN KEY(repo_id) REFERENCES repos(id)
166  ON DELETE CASCADE
167  ON UPDATE CASCADE,
168  CONSTRAINT user_id_team_id_not_null
169  CHECK (user_id IS NULL <> team_id IS NULL)
170);
171
172-- Migrate collabs
173INSERT INTO collabs (id, user_id, team_id, repo_id, access_level, created_at, updated_at)
174SELECT id, user_id, NULL, repo_id, access_level, created_at, updated_at
175FROM _collabs_old;
176
177-- Drop old table
178DROP TABLE _collabs_old;