Esempio n. 1
0
func NewPostgresFilesystem(db *sql.DB) (Filesystem, error) {
	m := postgres.NewMigrations()
	m.Add(1,
		`CREATE TABLE files (
	file_id oid PRIMARY KEY DEFAULT lo_create(0),
	name text UNIQUE NOT NULL,
	size bigint,
	type text,
	digest text,
	created_at timestamp with time zone NOT NULL DEFAULT current_timestamp
);`,
		`CREATE FUNCTION delete_file() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM lo_unlink(OLD.file_id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;`,
		`CREATE TRIGGER delete_file
    AFTER DELETE ON files
    FOR EACH ROW EXECUTE PROCEDURE delete_file();`,
	)
	return &PostgresFilesystem{db: db}, m.Migrate(db)
}
Esempio n. 2
0
func NewPostgresFilesystem(db *sql.DB) (Filesystem, error) {
	m := postgres.NewMigrations()
	m.Add(1,
		`CREATE TABLE files (
	file_id oid PRIMARY KEY DEFAULT lo_create(0),
	name text UNIQUE NOT NULL,
	size bigint,
	type text,
	digest text,
	created_at timestamp with time zone NOT NULL DEFAULT current_timestamp
);`,
		`CREATE FUNCTION delete_file() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM lo_unlink(OLD.file_id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;`,
		`CREATE TRIGGER delete_file
    AFTER DELETE ON files
    FOR EACH ROW EXECUTE PROCEDURE delete_file();`,
	)
	// TODO(jpg) reuse pkg/postgres connection when converted
	connConf := pgx.ConnConfig{
		Host:     os.Getenv("PGHOST"),
		User:     os.Getenv("PGUSER"),
		Password: os.Getenv("PGPASSWORD"),
		Database: os.Getenv("PGDATABASE"),
	}
	pgxpool, err := pgx.NewConnPool(pgx.ConnPoolConfig{
		ConnConfig: connConf,
	})
	if err != nil {
		return nil, err
	}
	return &PostgresFilesystem{db: pgxpool}, m.Migrate(db)
}
Esempio n. 3
0
func migrateDB(db *sql.DB) error {
	m := postgres.NewMigrations()
	m.Add(1,
		`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,

		`CREATE TABLE artifacts (
    artifact_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    type text NOT NULL,
    uri text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,
		`CREATE UNIQUE INDEX ON artifacts (type, uri) WHERE deleted_at IS NULL`,

		`CREATE TABLE releases (
    release_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    artifact_id uuid REFERENCES artifacts (artifact_id),
    data jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,

		`CREATE TYPE deployment_strategy AS ENUM ('all-at-once', 'one-by-one', 'postgres')`,

		`CREATE TABLE apps (
    app_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL,
    release_id uuid REFERENCES releases (release_id),
	meta jsonb,
	strategy deployment_strategy NOT NULL DEFAULT 'all-at-once',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,
		`CREATE UNIQUE INDEX ON apps (name) WHERE deleted_at IS NULL`,

		`CREATE SEQUENCE event_ids`,
		`CREATE TYPE event_type AS ENUM ('app_deletion', 'app', 'app_release', 'deployment', 'job', 'scale', 'release', 'artifact', 'provider', 'resource', 'resource_deletion', 'key', 'key_deletion', 'route', 'route_deletion')`,
		`CREATE TABLE events (
    event_id    bigint         PRIMARY KEY DEFAULT nextval('event_ids'),
    app_id      uuid           REFERENCES apps (app_id),
    object_type event_type NOT NULL,
    object_id   text           NOT NULL,
    unique_id   text,
    data        jsonb,
    created_at  timestamptz    NOT NULL DEFAULT now()
)`,

		`CREATE INDEX ON events (object_type)`,
		`CREATE UNIQUE INDEX ON events (unique_id)`,
		`CREATE FUNCTION notify_event() RETURNS TRIGGER AS $$
    BEGIN
  IF NEW.app_id IS NOT NULL THEN
    PERFORM pg_notify('events', NEW.event_id || ':' || NEW.app_id);
  ELSE
		PERFORM pg_notify('events', NEW.event_id::text);
  END IF;
	RETURN NULL;
    END;
$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER notify_event
    AFTER INSERT ON events
    FOR EACH ROW EXECUTE PROCEDURE notify_event()`,

		`CREATE TABLE formations (
    app_id uuid NOT NULL REFERENCES apps (app_id),
    release_id uuid NOT NULL REFERENCES releases (release_id),
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (app_id, release_id)
)`,

		`CREATE FUNCTION notify_formation() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM pg_notify('formations', NEW.app_id || ':' || NEW.release_id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql`,

		`CREATE TRIGGER notify_formation
    AFTER INSERT OR UPDATE ON formations
    FOR EACH ROW EXECUTE PROCEDURE notify_formation()`,

		`CREATE TABLE providers (
    provider_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL UNIQUE,
    url text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,

		`CREATE TABLE resources (
    resource_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    provider_id uuid NOT NULL REFERENCES providers (provider_id),
    external_id text NOT NULL,
    env jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    UNIQUE (provider_id, external_id)
)`,

		`CREATE TABLE app_resources (
    app_id uuid NOT NULL REFERENCES apps (app_id),
    resource_id uuid NOT NULL REFERENCES resources (resource_id),
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (app_id, resource_id)
)`,
		`CREATE INDEX ON app_resources (resource_id)`,

		`CREATE TYPE job_state AS ENUM ('starting', 'up', 'down', 'crashed', 'failed')`,
		`CREATE TABLE job_cache (
    job_id text PRIMARY KEY,
    app_id uuid NOT NULL REFERENCES apps (app_id),
    release_id uuid NOT NULL REFERENCES releases (release_id),
    process_type text,
    state job_state NOT NULL,
    meta jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
)`,
		`CREATE FUNCTION check_job_state() RETURNS OPAQUE AS $$
    BEGIN
        IF NEW.state < OLD.state THEN
	    RAISE EXCEPTION 'invalid job state transition: % -> %', OLD.state, NEW.state USING ERRCODE = 'check_violation';
        ELSE
	    RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER job_state_trigger
    AFTER UPDATE ON job_cache
    FOR EACH ROW EXECUTE PROCEDURE check_job_state()`,

		`CREATE SEQUENCE name_ids MAXVALUE 4294967295`,
		`CREATE TABLE deployments (
    deployment_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    app_id uuid NOT NULL,
    old_release_id uuid REFERENCES releases (release_id),
    new_release_id uuid NOT NULL REFERENCES releases (release_id),
    strategy deployment_strategy NOT NULL,
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz)`,

		`CREATE UNIQUE INDEX isolate_deploys ON deployments (app_id)
    WHERE finished_at is NULL`,
	)
	m.Add(2,
		`CREATE TABLE que_jobs (
    priority     smallint    NOT NULL DEFAULT 100,
    run_at       timestamptz NOT NULL DEFAULT now(),
    job_id       bigserial   NOT NULL,
    job_class    text        NOT NULL,
    args         json        NOT NULL DEFAULT '[]'::json,
    error_count  integer     NOT NULL DEFAULT 0,
    last_error   text,
    queue        text        NOT NULL DEFAULT '',
    locked_until timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT que_jobs_pkey PRIMARY KEY (queue, priority, run_at, job_id))`,
		`COMMENT ON TABLE que_jobs IS '3'`,
	)
	return m.Migrate(db)
}
Esempio n. 4
0
func migrateDB(db *postgres.DB) error {
	m := postgres.NewMigrations()
	m.Add(1,
		`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,
		`CREATE FUNCTION set_updated_at_column() RETURNS TRIGGER AS $$
	BEGIN
		NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
		RETURN NEW;
	END;
$$ language 'plpgsql'`,

		// tcp routes

		`
CREATE TABLE tcp_routes (
	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
	parent_ref varchar(255) NOT NULL,
	service varchar(255) NOT NULL CHECK (service <> ''),
	port integer NOT NULL CHECK (port > 0 AND port < 65535),
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	deleted_at timestamptz
)`,
		`
CREATE UNIQUE INDEX tcp_routes_port_key ON tcp_routes
USING btree (port) WHERE deleted_at IS NULL`,
		`
CREATE TRIGGER set_updated_at_tcp_routes
	BEFORE UPDATE ON tcp_routes FOR EACH ROW
	EXECUTE PROCEDURE set_updated_at_column()`,
		`
CREATE OR REPLACE FUNCTION notify_tcp_route_update() RETURNS TRIGGER AS $$
BEGIN
	PERFORM pg_notify('tcp_routes', NEW.id::varchar);
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_tcp_route_update
	AFTER INSERT OR UPDATE OR DELETE ON tcp_routes
	FOR EACH ROW EXECUTE PROCEDURE notify_tcp_route_update()`,

		// http routes

		`
CREATE TABLE http_routes (
	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
	parent_ref varchar(255) NOT NULL,
	service varchar(255) NOT NULL CHECK (service <> ''),
	domain varchar(255) NOT NULL CHECK (domain <> ''),
	sticky bool NOT NULL DEFAULT FALSE,
	tls_cert text,
	tls_key text,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	deleted_at timestamptz
)`,
		`
CREATE UNIQUE INDEX http_routes_domain_key ON http_routes
	USING btree (domain) WHERE deleted_at IS NULL`,
		`
CREATE TRIGGER set_updated_at_http_routes
	BEFORE UPDATE ON http_routes FOR EACH ROW
	EXECUTE PROCEDURE set_updated_at_column()`,
		`
CREATE OR REPLACE FUNCTION notify_http_route_update() RETURNS TRIGGER AS $$
BEGIN
	PERFORM pg_notify('http_routes', NEW.id::varchar);
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_http_route_update
	AFTER INSERT OR UPDATE OR DELETE ON http_routes
	FOR EACH ROW EXECUTE PROCEDURE notify_http_route_update()`,
	)
	m.Add(2,
		`ALTER TABLE http_routes ADD COLUMN path text NOT NULL DEFAULT '/'`,
		`DROP INDEX http_routes_domain_key`,
		`CREATE UNIQUE INDEX http_routes_domain_path_key ON http_routes
		 USING btree (domain, path) WHERE deleted_at IS NULL`,
		`
CREATE OR REPLACE FUNCTION check_http_route_update() RETURNS TRIGGER AS $$
DECLARE
	default_route RECORD;
	dependent_routes int;
BEGIN
    -- If NEW.deleted_at is NOT NULL then we are processing a delete
	-- We also catch entire row deletions here but they shouldn't occur.
    IF NEW IS NULL OR NEW.deleted_at IS NOT NULL THEN
		-- If we are removing a default route ensure no dependent routes left
		IF OLD.path = '/' THEN
			SELECT count(*) INTO dependent_routes FROM http_routes
			WHERE domain = OLD.domain AND path <> '/' AND deleted_at IS NULL;
			IF dependent_routes > 0 THEN
				RAISE EXCEPTION 'default route for % has dependent routes', OLD.domain;
			END IF;
		END IF;
		RETURN NEW;
	END IF;

	-- If no path supplied then override it to '/', the default path
	IF NEW.path = '' OR NULL THEN
		NEW.path := '/';
	END IF;

	-- If path isn't terminated by a slash then add it
	IF substring(NEW.path from '.$') != '/' THEN
		NEW.path := NEW.path || '/';
	END IF;

	-- Validate the path
	IF NEW.path !~* '^\/(.*\/)?$' THEN
		RAISE EXCEPTION 'path % is not valid', NEW.path;
	END IF;

	-- If path not the default then validate that a default route exists
	IF NEW.path <> '/' THEN
		SELECT INTO default_route FROM http_routes
		WHERE domain = NEW.domain AND path = '/' AND deleted_at IS NULL;
		IF NOT FOUND THEN
			RAISE EXCEPTION 'default route for domain % not found', NEW.domain;
		END IF;
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER check_http_route_update
	BEFORE INSERT OR UPDATE OR DELETE ON http_routes
	FOR EACH ROW
	EXECUTE PROCEDURE check_http_route_update()`,
	)
	m.Add(3,
		// Ensure the default is set on the path column. We set this above, but
		// releases v20151214.1, v20151214.0, v20151213.1, and v20151213.0
		// didn't have the default specified, so this will fix any databases
		// from those versions that have the broken release and have already run
		// migration 2.
		`ALTER TABLE http_routes ALTER COLUMN path SET DEFAULT '/'`,
	)
	m.Add(4,
		`ALTER TABLE tcp_routes ADD COLUMN leader boolean NOT NULL DEFAULT FALSE`,
		`ALTER TABLE http_routes ADD COLUMN leader boolean NOT NULL DEFAULT FALSE`,
	)
	return m.Migrate(db)
}
Esempio n. 5
0
func init() {
	migrations = postgres.NewMigrations()
	migrations.Add(1,
		`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,

		`CREATE TABLE artifacts (
    artifact_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    type text NOT NULL,
    uri text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,
		`CREATE UNIQUE INDEX ON artifacts (type, uri) WHERE deleted_at IS NULL`,

		`CREATE TABLE releases (
    release_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    artifact_id uuid REFERENCES artifacts (artifact_id),
    meta jsonb,
    env jsonb,
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,

		`CREATE TYPE deployment_strategy AS ENUM ('all-at-once', 'one-by-one', 'postgres')`,

		`CREATE TABLE apps (
    app_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL,
    release_id uuid REFERENCES releases (release_id),
	meta jsonb,
	strategy deployment_strategy NOT NULL DEFAULT 'all-at-once',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,
		`CREATE UNIQUE INDEX ON apps (name) WHERE deleted_at IS NULL`,

		`CREATE SEQUENCE event_ids`,
		`CREATE TYPE event_type AS ENUM ('app_deletion', 'app', 'app_release', 'deployment', 'job', 'scale', 'release', 'artifact', 'provider', 'resource', 'resource_deletion', 'key', 'key_deletion', 'route', 'route_deletion', 'domain_migration')`,
		`CREATE TABLE events (
    event_id    bigint         PRIMARY KEY DEFAULT nextval('event_ids'),
    app_id      uuid           REFERENCES apps (app_id),
    object_type event_type NOT NULL,
    object_id   text           NOT NULL,
    unique_id   text,
    data        jsonb,
    created_at  timestamptz    NOT NULL DEFAULT now()
)`,

		`CREATE INDEX ON events (object_type)`,
		`CREATE UNIQUE INDEX ON events (unique_id)`,
		`CREATE FUNCTION notify_event() RETURNS TRIGGER AS $$
    BEGIN
  IF NEW.app_id IS NOT NULL THEN
    PERFORM pg_notify('events', NEW.event_id || ':' || NEW.app_id);
  ELSE
		PERFORM pg_notify('events', NEW.event_id::text);
  END IF;
	RETURN NULL;
    END;
$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER notify_event
    AFTER INSERT ON events
    FOR EACH ROW EXECUTE PROCEDURE notify_event()`,

		`CREATE TABLE formations (
    app_id uuid NOT NULL REFERENCES apps (app_id),
    release_id uuid NOT NULL REFERENCES releases (release_id),
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (app_id, release_id)
)`,

		`CREATE FUNCTION notify_formation() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM pg_notify('formations', NEW.app_id || ':' || NEW.release_id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql`,

		`CREATE TRIGGER notify_formation
    AFTER INSERT OR UPDATE ON formations
    FOR EACH ROW EXECUTE PROCEDURE notify_formation()`,

		`CREATE TABLE providers (
    provider_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL UNIQUE,
    url text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,

		`CREATE TABLE resources (
    resource_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    provider_id uuid NOT NULL REFERENCES providers (provider_id),
    external_id text NOT NULL,
    env jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    UNIQUE (provider_id, external_id)
)`,

		`CREATE TABLE app_resources (
    app_id uuid NOT NULL REFERENCES apps (app_id),
    resource_id uuid NOT NULL REFERENCES resources (resource_id),
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (app_id, resource_id)
)`,
		`CREATE INDEX ON app_resources (resource_id)`,

		`CREATE TYPE job_state AS ENUM ('starting', 'up', 'down', 'crashed', 'failed')`,
		`CREATE TABLE job_cache (
    job_id text PRIMARY KEY,
    app_id uuid NOT NULL REFERENCES apps (app_id),
    release_id uuid NOT NULL REFERENCES releases (release_id),
    process_type text,
    state job_state NOT NULL,
    meta jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
)`,
		`CREATE FUNCTION check_job_state() RETURNS OPAQUE AS $$
    BEGIN
        IF NEW.state < OLD.state THEN
	    RAISE EXCEPTION 'invalid job state transition: % -> %', OLD.state, NEW.state USING ERRCODE = 'check_violation';
        ELSE
	    RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER job_state_trigger
    AFTER UPDATE ON job_cache
    FOR EACH ROW EXECUTE PROCEDURE check_job_state()`,

		`CREATE SEQUENCE name_ids MAXVALUE 4294967295`,
		`CREATE TABLE deployments (
    deployment_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    app_id uuid NOT NULL,
    old_release_id uuid REFERENCES releases (release_id),
    new_release_id uuid NOT NULL REFERENCES releases (release_id),
    strategy deployment_strategy NOT NULL,
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz)`,

		`CREATE UNIQUE INDEX isolate_deploys ON deployments (app_id)
    WHERE finished_at is NULL`,

		`CREATE TABLE domain_migrations (
			migration_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
			old_domain text NOT NULL,
			domain text NOT NULL,
			old_tls_cert jsonb,
			tls_cert jsonb,
			created_at timestamptz NOT NULL DEFAULT now(),
			finished_at timestamptz)`,
	)
	migrations.Add(2,
		`CREATE TABLE que_jobs (
    priority     smallint    NOT NULL DEFAULT 100,
    run_at       timestamptz NOT NULL DEFAULT now(),
    job_id       bigserial   NOT NULL,
    job_class    text        NOT NULL,
    args         json        NOT NULL DEFAULT '[]'::json,
    error_count  integer     NOT NULL DEFAULT 0,
    last_error   text,
    queue        text        NOT NULL DEFAULT '',
    locked_until timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT que_jobs_pkey PRIMARY KEY (queue, priority, run_at, job_id))`,
		`COMMENT ON TABLE que_jobs IS '3'`,
	)
	migrations.Add(3,
		`ALTER TABLE apps ADD COLUMN deploy_timeout integer NOT NULL DEFAULT 30`,
		`UPDATE apps SET deploy_timeout = 120 WHERE name = 'controller'`,
		`UPDATE apps SET deploy_timeout = 120 WHERE name = 'postgres'`,
	)
	migrations.Add(4,
		`CREATE TABLE deployment_strategies (name text PRIMARY KEY)`,
		`INSERT INTO deployment_strategies (name) VALUES
			('all-at-once'), ('one-by-one'), ('postgres')`,
		`ALTER TABLE apps ALTER COLUMN strategy TYPE text`,
		`ALTER TABLE apps ALTER COLUMN strategy SET DEFAULT 'all-at-once'`,
		`ALTER TABLE apps ADD CONSTRAINT apps_strategy_fkey FOREIGN KEY (strategy) REFERENCES deployment_strategies (name)`,
		`ALTER TABLE deployments ALTER COLUMN strategy TYPE text`,
		`ALTER TABLE deployments ADD CONSTRAINT deployments_strategy_fkey FOREIGN KEY (strategy) REFERENCES deployment_strategies (name)`,
		`DROP TYPE deployment_strategy`,

		`CREATE TABLE event_types (name text PRIMARY KEY)`,
		`INSERT INTO event_types (name) VALUES
			('app_deletion'), ('app'), ('app_release'), ('deployment'),
			('job'),('scale'), ('release'), ('artifact'), ('provider'),
			('resource'), ('resource_deletion'), ('key'), ('key_deletion'),
			('route'), ('route_deletion'), ('domain_migration')`,
		`ALTER TABLE events ALTER COLUMN object_type TYPE text`,
		`ALTER TABLE events ADD CONSTRAINT events_object_type_fkey FOREIGN KEY (object_type) REFERENCES event_types (name)`,
		`DROP TYPE event_type`,
	)
	migrations.Add(5,
		`ALTER TABLE deployments ADD COLUMN deploy_timeout integer NOT NULL DEFAULT 30`,
	)
	migrations.Add(6,
		`INSERT INTO deployment_strategies (name) VALUES ('discoverd-meta')`,
	)
	migrations.Add(7,
		`ALTER TABLE job_cache ADD COLUMN exit_status integer`,
		`ALTER TABLE job_cache ADD COLUMN host_error text`,
	)
	migrations.Add(8,
		`CREATE TABLE job_states (name text PRIMARY KEY)`,
		`INSERT INTO job_states (name) VALUES ('starting'), ('up'), ('down'), ('crashed'), ('failed')`,
		`ALTER TABLE job_cache ALTER COLUMN state TYPE text`,
		`ALTER TABLE job_cache ADD CONSTRAINT job_state_fkey FOREIGN KEY (state) REFERENCES job_states (name)`,
		`DROP TRIGGER job_state_trigger ON job_cache`,
		`DROP TYPE job_state`,
	)
	migrations.Add(9,
		`INSERT INTO job_states (name) VALUES ('pending')`,
		`ALTER TABLE job_cache ADD COLUMN run_at timestamptz`,
		`ALTER TABLE job_cache ADD COLUMN restarts integer`,
		`ALTER TABLE job_cache RENAME COLUMN job_id TO cluster_id`,
		`ALTER TABLE job_cache ALTER COLUMN cluster_id TYPE text`,
		`ALTER TABLE job_cache DROP CONSTRAINT job_cache_pkey`,
		`ALTER TABLE job_cache ADD COLUMN job_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()`,
		`ALTER TABLE job_cache ADD COLUMN host_id text`,
		`UPDATE job_cache SET host_id = s.split[1], job_id = s.split[2]::uuid FROM (SELECT cluster_id, regexp_matches(cluster_id, '([^-]+)-(.*)') AS split FROM job_cache) AS s WHERE job_cache.cluster_id = s.cluster_id`,
	)
	migrations.Add(10,
		`ALTER TABLE formations ADD COLUMN tags jsonb`,
	)
	migrations.Add(11,
		`INSERT INTO deployment_strategies VALUES ('sirenia')`,
		`UPDATE apps SET strategy = 'sirenia' WHERE name = 'postgres'`,
		`DELETE FROM deployment_strategies WHERE name = 'postgres'`,
	)
	migrations.Add(12,
		`INSERT INTO event_types (name) VALUES ('resource_app_deletion')`,
	)
	migrations.Add(13,
		// define a function to merge two JSON objects, taken from:
		// https://gist.github.com/inindev/2219dff96851928c2282
		`CREATE OR REPLACE FUNCTION public.jsonb_merge(data jsonb, merge_data jsonb)
		RETURNS jsonb
		IMMUTABLE
		LANGUAGE sql
		AS $$
		    SELECT json_object_agg(key, value)::jsonb
		    FROM (
			WITH to_merge AS (
			    SELECT * FROM jsonb_each(merge_data)
			)
			SELECT *
			FROM jsonb_each(data)
			WHERE key NOT IN (SELECT key FROM to_merge)
			UNION ALL
			SELECT * FROM to_merge
		    ) t;
		$$`,
		`UPDATE apps SET meta = jsonb_merge(meta, '{"flynn-system-critical":"true"}') WHERE name IN ('discoverd', 'flannel', 'postgres', 'controller')`,
	)
	migrations.Add(14,
		`CREATE TABLE backup_statuses (name text PRIMARY KEY)`,
		`INSERT INTO backup_statuses (name) VALUES
			('running'), ('complete'), ('error')`,
		`CREATE TABLE backups (
			backup_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
			status text NOT NULL REFERENCES backup_statuses (name),
			sha512 text,
			size bigint,
			error text,
			created_at timestamptz NOT NULL DEFAULT now(),
			updated_at timestamptz NOT NULL DEFAULT now(),
			completed_at timestamptz,
			deleted_at timestamptz
		)`,
		`INSERT INTO event_types (name) VALUES ('cluster_backup')`,
	)
	migrations.Add(15,
		`ALTER TABLE artifacts ADD COLUMN meta jsonb`,
		`CREATE TABLE release_artifacts (
			release_id uuid NOT NULL REFERENCES releases (release_id),
			artifact_id uuid NOT NULL REFERENCES artifacts (artifact_id),
			created_at timestamptz NOT NULL DEFAULT now(),
			deleted_at timestamptz,
			PRIMARY KEY (release_id, artifact_id))`,

		// add a check to ensure releases only have a single "docker"
		// artifact, and that artifact is added first
		`CREATE FUNCTION check_release_artifacts() RETURNS OPAQUE AS $$
			BEGIN
			    IF (
			      SELECT COUNT(*)
			      FROM release_artifacts r
			      INNER JOIN artifacts a ON r.artifact_id = a.artifact_id
			      WHERE r.release_id = NEW.release_id AND a.type = 'docker'
			    ) != 1 THEN
			      RAISE EXCEPTION 'must have exactly one artifact of type "docker"' USING ERRCODE = 'check_violation';
			    END IF;

			    RETURN NULL;
			END;
			$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER release_artifacts_trigger
			AFTER INSERT ON release_artifacts
			FOR EACH ROW EXECUTE PROCEDURE check_release_artifacts()`,
		`INSERT INTO release_artifacts (release_id, artifact_id) (SELECT release_id, artifact_id FROM releases WHERE artifact_id IS NOT NULL)`,

		// set "git=true" for releases with SLUG_URL set
		`UPDATE releases SET meta = jsonb_merge(CASE WHEN meta = 'null' THEN '{}' ELSE meta END, '{"git":"true"}') WHERE env ? 'SLUG_URL'`,

		// create file artifacts for any releases with SLUG_URL set,
		// taking care not to create duplicate artifacts
		`DO $$
		DECLARE
			release RECORD;
			artifact uuid;
		BEGIN
			FOR release IN SELECT * FROM releases WHERE env ? 'SLUG_URL' LOOP
				SELECT INTO artifact artifact_id FROM artifacts WHERE type = 'file' AND uri = release.env->>'SLUG_URL';

				IF NOT FOUND THEN
					INSERT INTO artifacts (type, uri, meta)
					VALUES ('file', release.env->>'SLUG_URL', '{"blobstore":"true"}')
					RETURNING artifact_id INTO artifact;
				END IF;

				INSERT INTO release_artifacts (release_id, artifact_id) VALUES(release.release_id, artifact);
			END LOOP;
		END $$`,
		`ALTER TABLE releases DROP COLUMN artifact_id`,
	)
	migrations.Add(16,
		// set "blobstore=true" for artifacts stored in the blobstore
		`UPDATE artifacts SET meta = jsonb_merge(CASE WHEN meta = 'null' THEN '{}' ELSE meta END, '{"blobstore":"true"}') WHERE uri LIKE 'http://blobstore.discoverd/%'`,

		`INSERT INTO event_types (name) VALUES ('release_deletion')`,

		// add a trigger to prevent current app releases from being deleted
		`CREATE FUNCTION check_release_delete() RETURNS OPAQUE AS $$
			BEGIN
				IF NEW.deleted_at IS NOT NULL AND (SELECT COUNT(*) FROM apps WHERE release_id = NEW.release_id) != 0 THEN
					RAISE EXCEPTION 'cannot delete current app release' USING ERRCODE = 'check_violation';
				END IF;

				RETURN NULL;
			END;
		$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER check_release_delete AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE check_release_delete()`,
	)
	migrations.Add(17,
		// add an "index" column to release_artifacts which is set
		// explicitly to the array index of release.ArtifactIDs
		`ALTER TABLE release_artifacts ADD COLUMN index integer`,
		`CREATE UNIQUE INDEX ON release_artifacts (release_id, index) WHERE deleted_at IS NULL`,
		`DO $$
		DECLARE
			row RECORD;
			release uuid;
			i int;
		BEGIN
			FOR release IN SELECT DISTINCT(release_id) FROM release_artifacts LOOP
				i := 0;
				FOR row IN SELECT * FROM release_artifacts WHERE release_id = release ORDER BY created_at ASC LOOP
					UPDATE release_artifacts SET index = i WHERE release_id = release AND artifact_id = row.artifact_id;
					i := i + 1;
				END LOOP;
			END LOOP;
		END $$`,
		`ALTER TABLE release_artifacts ALTER COLUMN index SET NOT NULL`,
	)
	migrations.Add(18,
		`INSERT INTO event_types (name) VALUES ('app_garbage_collection')`,
	)
	migrations.AddSteps(19,
		migrateProcessArgs,
	)
	migrations.Add(20,
		// update Redis app service name to match the app name
		`UPDATE releases
		SET processes = r.processes
		FROM (
			SELECT r.release_id AS id, jsonb_set(r.processes, '{redis,service}', ('"' || a.name || '"')::jsonb, true) AS processes
			FROM releases r
			INNER JOIN apps a USING (release_id)
			WHERE a.meta->>'flynn-system-app' = 'true' AND a.name LIKE 'redis-%'
		) r
		WHERE release_id = r.id`,
	)
	migrations.Add(21,
		`INSERT INTO job_states (name) VALUES ('stopping')`,
	)
	migrations.Add(22,
		`DROP TRIGGER notify_formation ON formations`,
		`DROP FUNCTION notify_formation()`,
	)
	migrations.Add(23,
		`ALTER TABLE job_cache ADD COLUMN args jsonb`,
	)
	migrations.Add(24,
		`UPDATE apps SET meta = jsonb_merge(CASE WHEN meta = 'null' THEN '{}' ELSE meta END, '{"gc.max_inactive_slug_releases":"10"}') WHERE meta->>'gc.max_inactive_slug_releases' IS NULL`,
	)
	migrations.AddSteps(25,
		migrateProcessData,
	)
	migrations.Add(26,
		`DROP TRIGGER release_artifacts_trigger ON release_artifacts`,
		`DROP FUNCTION check_release_artifacts()`,
		`ALTER TABLE artifacts ADD COLUMN manifest jsonb`,
		`ALTER TABLE artifacts ADD COLUMN hashes jsonb`,
		`ALTER TABLE artifacts ADD COLUMN size integer`,
		`ALTER TABLE artifacts ADD COLUMN layer_url_template text`,
		`CREATE FUNCTION check_artifact_manifest() RETURNS OPAQUE AS $$
			BEGIN
				IF NEW.type = 'flynn' AND NEW.manifest IS NULL THEN
					RAISE EXCEPTION 'flynn artifacts must have a manifest' USING ERRCODE = 'check_violation';
				END IF;

				RETURN NULL;
			END;
		$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER check_artifact_manifest AFTER INSERT ON artifacts FOR EACH ROW EXECUTE PROCEDURE check_artifact_manifest()`,
	)
}
Esempio n. 6
0
func migrateDB(db *postgres.DB) error {
	m := postgres.NewMigrations()
	m.Add(1,
		`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,
		`CREATE FUNCTION set_updated_at_column() RETURNS TRIGGER AS $$
	BEGIN
		NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
		RETURN NEW;
	END;
$$ language 'plpgsql'`,

		// tcp routes

		`
CREATE TABLE tcp_routes (
	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
	parent_ref varchar(255) NOT NULL,
	service varchar(255) NOT NULL CHECK (service <> ''),
	port integer NOT NULL CHECK (port > 0 AND port < 65535),
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	deleted_at timestamptz
)`,
		`
CREATE UNIQUE INDEX tcp_routes_port_key ON tcp_routes
USING btree (port) WHERE deleted_at IS NULL`,
		`
CREATE TRIGGER set_updated_at_tcp_routes
	BEFORE UPDATE ON tcp_routes FOR EACH ROW
	EXECUTE PROCEDURE set_updated_at_column()`,
		`
CREATE OR REPLACE FUNCTION notify_tcp_route_update() RETURNS TRIGGER AS $$
BEGIN
	PERFORM pg_notify('tcp_routes', NEW.id::varchar);
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_tcp_route_update
	AFTER INSERT OR UPDATE OR DELETE ON tcp_routes
	FOR EACH ROW EXECUTE PROCEDURE notify_tcp_route_update()`,

		// http routes

		`
CREATE TABLE http_routes (
	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
	parent_ref varchar(255) NOT NULL,
	service varchar(255) NOT NULL CHECK (service <> ''),
	domain varchar(255) NOT NULL CHECK (domain <> ''),
	sticky bool NOT NULL DEFAULT FALSE,
	tls_cert text,
	tls_key text,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	deleted_at timestamptz
)`,
		`
CREATE UNIQUE INDEX http_routes_domain_key ON http_routes
	USING btree (domain) WHERE deleted_at IS NULL`,
		`
CREATE TRIGGER set_updated_at_http_routes
	BEFORE UPDATE ON http_routes FOR EACH ROW
	EXECUTE PROCEDURE set_updated_at_column()`,
		`
CREATE OR REPLACE FUNCTION notify_http_route_update() RETURNS TRIGGER AS $$
BEGIN
	PERFORM pg_notify('http_routes', NEW.id::varchar);
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_http_route_update
	AFTER INSERT OR UPDATE OR DELETE ON http_routes
	FOR EACH ROW EXECUTE PROCEDURE notify_http_route_update()`,
	)
	return m.Migrate(db)
}
Esempio n. 7
0
	log.Println("Blobstore serving files on " + addr)

	mux.Handle("/", handler(repo))
	mux.Handle(status.Path, status.Handler(func() status.Status {
		if err := db.Exec("SELECT 1"); err != nil {
			return status.Unhealthy
		}
		return status.Healthy
	}))

	h := httphelper.ContextInjector("blobstore", httphelper.NewRequestLogger(mux))
	return http.ListenAndServe(addr, h)
}

var dbMigrations = postgres.NewMigrations()

func init() {
	dbMigrations.Add(1,
		`CREATE TABLE files (
	file_id oid PRIMARY KEY DEFAULT lo_create(0),
	name text UNIQUE NOT NULL,
	size bigint,
	type text,
	digest text,
	created_at timestamp with time zone NOT NULL DEFAULT current_timestamp
)`,
		`CREATE FUNCTION delete_file() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM lo_unlink(OLD.file_id);
        RETURN NULL;
Esempio n. 8
0
func init() {
	migrations = postgres.NewMigrations()
	migrations.Add(1,
		`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,
		`CREATE FUNCTION set_updated_at_column() RETURNS TRIGGER AS $$
	BEGIN
		NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
		RETURN NEW;
	END;
$$ language 'plpgsql'`,

		// tcp routes

		`
CREATE TABLE tcp_routes (
	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
	parent_ref varchar(255) NOT NULL,
	service varchar(255) NOT NULL CHECK (service <> ''),
	port integer NOT NULL CHECK (port > 0 AND port < 65535),
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	deleted_at timestamptz
)`,
		`
CREATE UNIQUE INDEX tcp_routes_port_key ON tcp_routes
USING btree (port) WHERE deleted_at IS NULL`,
		`
CREATE TRIGGER set_updated_at_tcp_routes
	BEFORE UPDATE ON tcp_routes FOR EACH ROW
	EXECUTE PROCEDURE set_updated_at_column()`,
		`
CREATE OR REPLACE FUNCTION notify_tcp_route_update() RETURNS TRIGGER AS $$
BEGIN
	PERFORM pg_notify('tcp_routes', NEW.id::varchar);
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_tcp_route_update
	AFTER INSERT OR UPDATE OR DELETE ON tcp_routes
	FOR EACH ROW EXECUTE PROCEDURE notify_tcp_route_update()`,

		// http routes

		`
CREATE TABLE http_routes (
	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
	parent_ref varchar(255) NOT NULL,
	service varchar(255) NOT NULL CHECK (service <> ''),
	domain varchar(255) NOT NULL CHECK (domain <> ''),
	sticky bool NOT NULL DEFAULT FALSE,
	tls_cert text,
	tls_key text,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	deleted_at timestamptz
)`,
		`
CREATE UNIQUE INDEX http_routes_domain_key ON http_routes
	USING btree (domain) WHERE deleted_at IS NULL`,
		`
CREATE TRIGGER set_updated_at_http_routes
	BEFORE UPDATE ON http_routes FOR EACH ROW
	EXECUTE PROCEDURE set_updated_at_column()`,
		`
CREATE OR REPLACE FUNCTION notify_http_route_update() RETURNS TRIGGER AS $$
BEGIN
	PERFORM pg_notify('http_routes', NEW.id::varchar);
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_http_route_update
	AFTER INSERT OR UPDATE OR DELETE ON http_routes
	FOR EACH ROW EXECUTE PROCEDURE notify_http_route_update()`,
	)
	migrations.Add(2,
		`ALTER TABLE http_routes ADD COLUMN path text NOT NULL DEFAULT '/'`,
		`DROP INDEX http_routes_domain_key`,
		`CREATE UNIQUE INDEX http_routes_domain_path_key ON http_routes
		 USING btree (domain, path) WHERE deleted_at IS NULL`,
		`
CREATE OR REPLACE FUNCTION check_http_route_update() RETURNS TRIGGER AS $$
DECLARE
	default_route RECORD;
	dependent_routes int;
BEGIN
    -- If NEW.deleted_at is NOT NULL then we are processing a delete
	-- We also catch entire row deletions here but they shouldn't occur.
    IF NEW IS NULL OR NEW.deleted_at IS NOT NULL THEN
		-- If we are removing a default route ensure no dependent routes left
		IF OLD.path = '/' THEN
			SELECT count(*) INTO dependent_routes FROM http_routes
			WHERE domain = OLD.domain AND path <> '/' AND deleted_at IS NULL;
			IF dependent_routes > 0 THEN
				RAISE EXCEPTION 'default route for % has dependent routes', OLD.domain;
			END IF;
		END IF;
		RETURN NEW;
	END IF;

	-- If no path supplied then override it to '/', the default path
	IF NEW.path = '' OR NULL THEN
		NEW.path := '/';
	END IF;

	-- If path isn't terminated by a slash then add it
	IF substring(NEW.path from '.$') != '/' THEN
		NEW.path := NEW.path || '/';
	END IF;

	-- Validate the path
	IF NEW.path !~* '^\/(.*\/)?$' THEN
		RAISE EXCEPTION 'path % is not valid', NEW.path;
	END IF;

	-- If path not the default then validate that a default route exists
	IF NEW.path <> '/' THEN
		SELECT INTO default_route FROM http_routes
		WHERE domain = NEW.domain AND path = '/' AND deleted_at IS NULL;
		IF NOT FOUND THEN
			RAISE EXCEPTION 'default route for domain % not found', NEW.domain;
		END IF;
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER check_http_route_update
	BEFORE INSERT OR UPDATE OR DELETE ON http_routes
	FOR EACH ROW
	EXECUTE PROCEDURE check_http_route_update()`,
	)
	migrations.Add(3,
		// Ensure the default is set on the path column. We set this above, but
		// releases v20151214.1, v20151214.0, v20151213.1, and v20151213.0
		// didn't have the default specified, so this will fix any databases
		// from those versions that have the broken release and have already run
		// migration 2.
		`ALTER TABLE http_routes ALTER COLUMN path SET DEFAULT '/'`,
	)
	migrations.Add(4,
		`ALTER TABLE tcp_routes ADD COLUMN leader boolean NOT NULL DEFAULT FALSE`,
		`ALTER TABLE http_routes ADD COLUMN leader boolean NOT NULL DEFAULT FALSE`,
	)
	migrations.Add(5,
		`CREATE EXTENSION IF NOT EXISTS "pgcrypto"`,
		`CREATE TABLE certificates (
			id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
			cert text NOT NULL,
			key text NOT NULL,
			cert_sha256 bytea NOT NULL,
			created_at timestamptz NOT NULL DEFAULT now(),
			updated_at timestamptz NOT NULL DEFAULT now(),
			deleted_at timestamptz
		)`,
		`CREATE UNIQUE INDEX ON certificates (cert_sha256) WHERE deleted_at IS NULL`,
		`CREATE TABLE route_certificates (
			http_route_id uuid NOT NULL REFERENCES http_routes (id) ON DELETE CASCADE,
			certificate_id uuid NOT NULL REFERENCES certificates (id) ON DELETE RESTRICT,
			PRIMARY KEY (http_route_id, certificate_id)
		)`,
		// Create certificate for http_routes with tls_key set,
		// taking care not to create duplicates
		`DO $$
		DECLARE
			http_route RECORD;
			cert RECORD;
			certdigest bytea;
		BEGIN
			FOR http_route IN SELECT * FROM http_routes WHERE tls_key IS NOT NULL LOOP
				SELECT INTO certdigest digest(regexp_replace(regexp_replace(http_route.tls_cert, E'^[ \\n]+', '', ''), E'[ \\n]+$', '', ''), 'sha256');
				SELECT INTO cert * FROM certificates WHERE cert_sha256 = certdigest;

				IF NOT FOUND THEN
					INSERT INTO certificates (cert, key, cert_sha256)
					VALUES (http_route.tls_cert, http_route.tls_key, certdigest)
					RETURNING * INTO cert;
				END IF;

				INSERT INTO route_certificates (http_route_id, certificate_id) VALUES(http_route.id, cert.id);
			END LOOP;
		END $$`,
		`ALTER TABLE http_routes DROP COLUMN tls_cert`,
		`ALTER TABLE http_routes DROP COLUMN tls_key`,
		`
CREATE OR REPLACE FUNCTION notify_route_certificates_update() RETURNS TRIGGER AS $$
BEGIN
	IF (TG_OP = 'DELETE') THEN
		PERFORM pg_notify('http_routes', OLD.http_route_id::varchar);
	ELSIF (TG_OP = 'UPDATE') THEN
		PERFORM pg_notify('http_routes', OLD.http_route_id::varchar);
		PERFORM pg_notify('http_routes', NEW.http_route_id::varchar);
	ELSIF (TG_OP = 'INSERT') THEN
		PERFORM pg_notify('http_routes', NEW.http_route_id::varchar);
	END IF;
	RETURN NULL;
END;
$$ LANGUAGE plpgsql`,
		`
CREATE TRIGGER notify_route_certificates_update
	AFTER INSERT OR UPDATE OR DELETE ON route_certificates
	FOR EACH ROW EXECUTE PROCEDURE notify_route_certificates_update()`,
	)
}
Esempio n. 9
0
func init() {
	migrations = postgres.NewMigrations()
	migrations.Add(1,
		`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,

		`CREATE TABLE artifacts (
    artifact_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    type text NOT NULL,
    uri text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,
		`CREATE UNIQUE INDEX ON artifacts (type, uri) WHERE deleted_at IS NULL`,

		`CREATE TABLE releases (
    release_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    artifact_id uuid REFERENCES artifacts (artifact_id),
    meta jsonb,
    env jsonb,
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,

		`CREATE TYPE deployment_strategy AS ENUM ('all-at-once', 'one-by-one', 'postgres')`,

		`CREATE TABLE apps (
    app_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL,
    release_id uuid REFERENCES releases (release_id),
	meta jsonb,
	strategy deployment_strategy NOT NULL DEFAULT 'all-at-once',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,
		`CREATE UNIQUE INDEX ON apps (name) WHERE deleted_at IS NULL`,

		`CREATE SEQUENCE event_ids`,
		`CREATE TYPE event_type AS ENUM ('app_deletion', 'app', 'app_release', 'deployment', 'job', 'scale', 'release', 'artifact', 'provider', 'resource', 'resource_deletion', 'key', 'key_deletion', 'route', 'route_deletion', 'domain_migration')`,
		`CREATE TABLE events (
    event_id    bigint         PRIMARY KEY DEFAULT nextval('event_ids'),
    app_id      uuid           REFERENCES apps (app_id),
    object_type event_type NOT NULL,
    object_id   text           NOT NULL,
    unique_id   text,
    data        jsonb,
    created_at  timestamptz    NOT NULL DEFAULT now()
)`,

		`CREATE INDEX ON events (object_type)`,
		`CREATE UNIQUE INDEX ON events (unique_id)`,
		`CREATE FUNCTION notify_event() RETURNS TRIGGER AS $$
    BEGIN
  IF NEW.app_id IS NOT NULL THEN
    PERFORM pg_notify('events', NEW.event_id || ':' || NEW.app_id);
  ELSE
		PERFORM pg_notify('events', NEW.event_id::text);
  END IF;
	RETURN NULL;
    END;
$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER notify_event
    AFTER INSERT ON events
    FOR EACH ROW EXECUTE PROCEDURE notify_event()`,

		`CREATE TABLE formations (
    app_id uuid NOT NULL REFERENCES apps (app_id),
    release_id uuid NOT NULL REFERENCES releases (release_id),
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (app_id, release_id)
)`,

		`CREATE FUNCTION notify_formation() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM pg_notify('formations', NEW.app_id || ':' || NEW.release_id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql`,

		`CREATE TRIGGER notify_formation
    AFTER INSERT OR UPDATE ON formations
    FOR EACH ROW EXECUTE PROCEDURE notify_formation()`,

		`CREATE TABLE providers (
    provider_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL UNIQUE,
    url text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
)`,

		`CREATE TABLE resources (
    resource_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    provider_id uuid NOT NULL REFERENCES providers (provider_id),
    external_id text NOT NULL,
    env jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    UNIQUE (provider_id, external_id)
)`,

		`CREATE TABLE app_resources (
    app_id uuid NOT NULL REFERENCES apps (app_id),
    resource_id uuid NOT NULL REFERENCES resources (resource_id),
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (app_id, resource_id)
)`,
		`CREATE INDEX ON app_resources (resource_id)`,

		`CREATE TYPE job_state AS ENUM ('starting', 'up', 'down', 'crashed', 'failed')`,
		`CREATE TABLE job_cache (
    job_id text PRIMARY KEY,
    app_id uuid NOT NULL REFERENCES apps (app_id),
    release_id uuid NOT NULL REFERENCES releases (release_id),
    process_type text,
    state job_state NOT NULL,
    meta jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
)`,
		`CREATE FUNCTION check_job_state() RETURNS OPAQUE AS $$
    BEGIN
        IF NEW.state < OLD.state THEN
	    RAISE EXCEPTION 'invalid job state transition: % -> %', OLD.state, NEW.state USING ERRCODE = 'check_violation';
        ELSE
	    RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql`,
		`CREATE TRIGGER job_state_trigger
    AFTER UPDATE ON job_cache
    FOR EACH ROW EXECUTE PROCEDURE check_job_state()`,

		`CREATE SEQUENCE name_ids MAXVALUE 4294967295`,
		`CREATE TABLE deployments (
    deployment_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    app_id uuid NOT NULL,
    old_release_id uuid REFERENCES releases (release_id),
    new_release_id uuid NOT NULL REFERENCES releases (release_id),
    strategy deployment_strategy NOT NULL,
    processes jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz)`,

		`CREATE UNIQUE INDEX isolate_deploys ON deployments (app_id)
    WHERE finished_at is NULL`,

		`CREATE TABLE domain_migrations (
			migration_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
			old_domain text NOT NULL,
			domain text NOT NULL,
			old_tls_cert jsonb,
			tls_cert jsonb,
			created_at timestamptz NOT NULL DEFAULT now(),
			finished_at timestamptz)`,
	)
	migrations.Add(2,
		`CREATE TABLE que_jobs (
    priority     smallint    NOT NULL DEFAULT 100,
    run_at       timestamptz NOT NULL DEFAULT now(),
    job_id       bigserial   NOT NULL,
    job_class    text        NOT NULL,
    args         json        NOT NULL DEFAULT '[]'::json,
    error_count  integer     NOT NULL DEFAULT 0,
    last_error   text,
    queue        text        NOT NULL DEFAULT '',
    locked_until timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT que_jobs_pkey PRIMARY KEY (queue, priority, run_at, job_id))`,
		`COMMENT ON TABLE que_jobs IS '3'`,
	)
	migrations.Add(3,
		`ALTER TABLE apps ADD COLUMN deploy_timeout integer NOT NULL DEFAULT 30`,
		`UPDATE apps SET deploy_timeout = 120 WHERE name = 'controller'`,
		`UPDATE apps SET deploy_timeout = 120 WHERE name = 'postgres'`,
	)
	migrations.Add(4,
		`CREATE TABLE deployment_strategies (name text PRIMARY KEY)`,
		`INSERT INTO deployment_strategies (name) VALUES
			('all-at-once'), ('one-by-one'), ('postgres')`,
		`ALTER TABLE apps ALTER COLUMN strategy TYPE text`,
		`ALTER TABLE apps ALTER COLUMN strategy SET DEFAULT 'all-at-once'`,
		`ALTER TABLE apps ADD CONSTRAINT apps_strategy_fkey FOREIGN KEY (strategy) REFERENCES deployment_strategies (name)`,
		`ALTER TABLE deployments ALTER COLUMN strategy TYPE text`,
		`ALTER TABLE deployments ADD CONSTRAINT deployments_strategy_fkey FOREIGN KEY (strategy) REFERENCES deployment_strategies (name)`,
		`DROP TYPE deployment_strategy`,

		`CREATE TABLE event_types (name text PRIMARY KEY)`,
		`INSERT INTO event_types (name) VALUES
			('app_deletion'), ('app'), ('app_release'), ('deployment'),
			('job'),('scale'), ('release'), ('artifact'), ('provider'),
			('resource'), ('resource_deletion'), ('key'), ('key_deletion'),
			('route'), ('route_deletion'), ('domain_migration')`,
		`ALTER TABLE events ALTER COLUMN object_type TYPE text`,
		`ALTER TABLE events ADD CONSTRAINT events_object_type_fkey FOREIGN KEY (object_type) REFERENCES event_types (name)`,
		`DROP TYPE event_type`,
	)
	migrations.Add(5,
		`ALTER TABLE deployments ADD COLUMN deploy_timeout integer NOT NULL DEFAULT 30`,
	)
	migrations.Add(6,
		`INSERT INTO deployment_strategies (name) VALUES ('discoverd-meta')`,
	)
	migrations.Add(7,
		`ALTER TABLE job_cache ADD COLUMN exit_status integer`,
		`ALTER TABLE job_cache ADD COLUMN host_error text`,
	)
	migrations.Add(8,
		`CREATE TABLE job_states (name text PRIMARY KEY)`,
		`INSERT INTO job_states (name) VALUES ('starting'), ('up'), ('down'), ('crashed'), ('failed')`,
		`ALTER TABLE job_cache ALTER COLUMN state TYPE text`,
		`ALTER TABLE job_cache ADD CONSTRAINT job_state_fkey FOREIGN KEY (state) REFERENCES job_states (name)`,
		`DROP TRIGGER job_state_trigger ON job_cache`,
		`DROP TYPE job_state`,
	)
	migrations.Add(9,
		`INSERT INTO job_states (name) VALUES ('pending')`,
		`ALTER TABLE job_cache ADD COLUMN run_at timestamptz`,
		`ALTER TABLE job_cache ADD COLUMN restarts integer`,
		`ALTER TABLE job_cache RENAME COLUMN job_id TO cluster_id`,
		`ALTER TABLE job_cache ALTER COLUMN cluster_id TYPE text`,
		`ALTER TABLE job_cache DROP CONSTRAINT job_cache_pkey`,
		`ALTER TABLE job_cache ADD COLUMN job_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()`,
		`ALTER TABLE job_cache ADD COLUMN host_id text`,
		`UPDATE job_cache SET host_id = s.split[1], job_id = s.split[2]::uuid FROM (SELECT cluster_id, regexp_matches(cluster_id, '([^-]+)-(.*)') AS split FROM job_cache) AS s WHERE job_cache.cluster_id = s.cluster_id`,
	)
	migrations.Add(10,
		`ALTER TABLE formations ADD COLUMN tags jsonb`,
	)
}