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 DEFAULT false,
 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
 83-- Create unique index for primary email
 84CREATE UNIQUE INDEX user_emails_user_id_is_primary_idx ON user_emails (user_id) WHERE is_primary;
 85
 86ALTER TABLE users RENAME TO _users_old;
 87
 88CREATE TABLE IF NOT EXISTS users (
 89  id INTEGER PRIMARY KEY AUTOINCREMENT,
 90  name TEXT,
 91  handle_id INTEGER NOT NULL UNIQUE,
 92  admin BOOLEAN NOT NULL,
 93  password TEXT,
 94  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 95  updated_at DATETIME NOT NULL,
 96  CONSTRAINT handle_id_fk
 97  FOREIGN KEY(handle_id) REFERENCES handles(id)
 98  ON DELETE CASCADE
 99  ON UPDATE CASCADE
100);
101
102-- Migrate user username to handles
103INSERT INTO handles (handle, updated_at) SELECT username, updated_at FROM _users_old;
104
105-- Migrate users
106INSERT INTO users (id, handle_id, admin, password, created_at, updated_at) SELECT id, (
107  SELECT id FROM handles WHERE handle = _users_old.username
108), admin, password, created_at, updated_at FROM _users_old;
109
110-- Drop old table
111DROP TABLE _users_old;
112
113ALTER TABLE repos RENAME TO _repos_old;
114
115CREATE TABLE IF NOT EXISTS repos (
116  id INTEGER PRIMARY KEY AUTOINCREMENT,
117  name TEXT NOT NULL UNIQUE,
118  project_name TEXT NOT NULL,
119  description TEXT NOT NULL,
120  private BOOLEAN NOT NULL,
121  mirror BOOLEAN NOT NULL,
122  hidden BOOLEAN NOT NULL,
123  user_id INTEGER,
124  org_id INTEGER,
125  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
126  updated_at DATETIME NOT NULL,
127  CONSTRAINT user_id_fk
128  FOREIGN KEY(user_id) REFERENCES users(id)
129  ON DELETE CASCADE
130  ON UPDATE CASCADE,
131  CONSTRAINT org_id_fk
132  FOREIGN KEY(org_id) REFERENCES organizations(id)
133  ON DELETE CASCADE
134  ON UPDATE CASCADE,
135  CONSTRAINT user_id_org_id_not_null
136  CHECK (user_id IS NULL <> org_id IS NULL)
137);
138
139-- Migrate repos
140INSERT INTO repos (id, name, project_name, description, private, mirror, hidden, user_id, created_at, updated_at)
141SELECT id, name, project_name, description, private, mirror, hidden, user_id, created_at, updated_at
142FROM _repos_old;
143
144-- Drop old table
145DROP TABLE _repos_old;
146
147-- Alter collabs table
148ALTER TABLE collabs RENAME TO _collabs_old;
149
150CREATE TABLE IF NOT EXISTS collabs (
151  id INTEGER PRIMARY KEY AUTOINCREMENT,
152  user_id INTEGER,
153  team_id INTEGER,
154  repo_id INTEGER NOT NULL,
155  access_level INTEGER NOT NULL,
156  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
157  updated_at DATETIME NOT NULL,
158  UNIQUE (user_id, team_id, repo_id),
159  CONSTRAINT user_id_fk
160  FOREIGN KEY(user_id) REFERENCES users(id)
161  ON DELETE CASCADE
162  ON UPDATE CASCADE,
163  CONSTRAINT team_id_fk
164  FOREIGN KEY(team_id) REFERENCES teams(id)
165  ON DELETE CASCADE
166  ON UPDATE CASCADE,
167  CONSTRAINT repo_id_fk
168  FOREIGN KEY(repo_id) REFERENCES repos(id)
169  ON DELETE CASCADE
170  ON UPDATE CASCADE,
171  CONSTRAINT user_id_team_id_not_null
172  CHECK (user_id IS NULL <> team_id IS NULL)
173);
174
175-- Migrate collabs
176INSERT INTO collabs (id, user_id, team_id, repo_id, access_level, created_at, updated_at)
177SELECT id, user_id, NULL, repo_id, access_level, created_at, updated_at
178FROM _collabs_old;
179
180-- Drop old table
181DROP TABLE _collabs_old;