110 lines
3.9 KiB
Go
110 lines
3.9 KiB
Go
package app
|
|
|
|
import "context"
|
|
|
|
func (a *App) migrate(ctx context.Context) error {
|
|
ddl := []string{
|
|
`CREATE TABLE IF NOT EXISTS workspaces (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
unix_user TEXT NOT NULL,
|
|
UNIQUE(user_id, name),
|
|
UNIQUE(user_id, unix_user)
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS projects (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
workspace_id BIGINT REFERENCES workspaces(id) ON DELETE RESTRICT,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
repo_url TEXT NOT NULL DEFAULT '',
|
|
repo_private BOOLEAN NOT NULL DEFAULT true,
|
|
service_name TEXT NOT NULL,
|
|
route_host TEXT NOT NULL DEFAULT '',
|
|
deploy_token TEXT NOT NULL DEFAULT '',
|
|
provision_state TEXT NOT NULL DEFAULT 'provisioned',
|
|
provision_error TEXT NOT NULL DEFAULT '',
|
|
last_deployed_at TIMESTAMPTZ NULL,
|
|
last_deployed_commit TEXT NOT NULL DEFAULT '',
|
|
db_name TEXT NOT NULL DEFAULT '',
|
|
db_user TEXT NOT NULL DEFAULT '',
|
|
db_password TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (user_id, slug)
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS project_env_vars (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
key TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
UNIQUE(project_id, key)
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS user_webhook_tokens (
|
|
user_id TEXT PRIMARY KEY,
|
|
token TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);`,
|
|
}
|
|
for _, q := range ddl {
|
|
if _, err := a.db.ExecContext(ctx, q); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
alter := []string{
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS route_host TEXT NOT NULL DEFAULT '';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS workspace_id BIGINT REFERENCES workspaces(id) ON DELETE RESTRICT;`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS deploy_token TEXT NOT NULL DEFAULT '';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS repo_private BOOLEAN NOT NULL DEFAULT true;`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS provision_state TEXT NOT NULL DEFAULT 'provisioned';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS provision_error TEXT NOT NULL DEFAULT '';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS last_deployed_at TIMESTAMPTZ NULL;`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS last_deployed_commit TEXT NOT NULL DEFAULT '';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS db_name TEXT NOT NULL DEFAULT '';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS db_user TEXT NOT NULL DEFAULT '';`,
|
|
`ALTER TABLE projects ADD COLUMN IF NOT EXISTS db_password TEXT NOT NULL DEFAULT '';`,
|
|
}
|
|
for _, q := range alter {
|
|
if _, err := a.db.ExecContext(ctx, q); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
if _, err := a.db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
key TEXT PRIMARY KEY,
|
|
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);`); err != nil {
|
|
return err
|
|
}
|
|
var dropDone bool
|
|
if err := a.db.QueryRowContext(ctx, `SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE key='drop_projects_target_port')`).Scan(&dropDone); err != nil {
|
|
return err
|
|
}
|
|
if !dropDone {
|
|
if _, err := a.db.ExecContext(ctx, `ALTER TABLE projects DROP COLUMN IF EXISTS target_port;`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := a.db.ExecContext(ctx, `INSERT INTO schema_migrations(key) VALUES ('drop_projects_target_port') ON CONFLICT (key) DO NOTHING`); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
rows, err := a.db.QueryContext(ctx, `SELECT id FROM projects WHERE deploy_token=''`)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var id int64
|
|
if err := rows.Scan(&id); err != nil {
|
|
return err
|
|
}
|
|
tok, err := newDeployToken()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if _, err := a.db.ExecContext(ctx, `UPDATE projects SET deploy_token=$1 WHERE id=$2`, tok, id); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return rows.Err()
|
|
}
|