func init() {
RegisterMigration(migrate.Migration{
ID: 4,
Up: migrate.Queries([]string{
`CREATE INDEX vulnerability_notification_deleted_at_idx ON Vulnerability_Notification (deleted_at);`,
}),
Down: migrate.Queries([]string{
`DROP INDEX vulnerability_notification_deleted_at_idx;`,
}),
})
}
func init() {
RegisterMigration(migrate.Migration{
ID: 4,
Up: migrate.Queries([]string{
`CREATE INDEX layer_diff_featureversion_layer_id_modification_idx ON Layer_diff_FeatureVersion (layer_id, modification);`,
}),
Down: migrate.Queries([]string{
`DROP INDEX layer_diff_featureversion_layer_id_modification_idx;`,
}),
})
}
func Example() {
migrations := []migrate.Migration{
{
ID: 1,
Up: func(tx *sql.Tx) error {
_, err := tx.Exec("CREATE TABLE people (id int)")
return err
},
Down: func(tx *sql.Tx) error {
_, err := tx.Exec("DROP TABLE people")
return err
},
},
{
ID: 2,
// For simple sql migrations, you can use the migrate.Queries
// helper.
Up: migrate.Queries([]string{
"ALTER TABLE people ADD COLUMN first_name text",
}),
Down: func(tx *sql.Tx) error {
// It's not possible to remove a column with
// sqlite.
_, err := tx.Exec("SELECT 1 FROM people")
return err
},
},
}
db, _ := sql.Open("sqlite3", ":memory:")
_ = migrate.Exec(db, migrate.Up, migrations...)
}
func init() {
// This migration removes the data maintained by the previous migration tool
// (liamstask/goose), and if it was present, mark the 00002_initial_schema
// migration as done.
RegisterMigration(migrate.Migration{
ID: 1,
Up: func(tx *sql.Tx) error {
// Verify that goose was in use before, otherwise skip this migration.
var e bool
err := tx.QueryRow("SELECT true FROM pg_class WHERE relname = $1", "goose_db_version").Scan(&e)
if err == sql.ErrNoRows {
return nil
}
if err != nil {
return err
}
// Delete goose's data.
_, err = tx.Exec("DROP TABLE goose_db_version CASCADE")
if err != nil {
return err
}
// Mark the '00002_initial_schema' as done.
_, err = tx.Exec("INSERT INTO schema_migrations (version) VALUES (2)")
return err
},
Down: migrate.Queries([]string{}),
})
}
func init() {
RegisterMigration(migrate.Migration{
ID: 3,
Up: migrate.Queries([]string{
`CREATE UNIQUE INDEX namespace_name_key ON Namespace (name);`,
`CREATE INDEX vulnerability_name_idx ON Vulnerability (name);`,
`CREATE INDEX vulnerability_namespace_id_name_idx ON Vulnerability (namespace_id, name);`,
`CREATE UNIQUE INDEX featureversion_feature_id_version_key ON FeatureVersion (feature_id, version);`,
}),
Down: migrate.Queries([]string{
`DROP INDEX namespace_name_key;`,
`DROP INDEX vulnerability_name_idx;`,
`DROP INDEX vulnerability_namespace_id_name_idx;`,
`DROP INDEX featureversion_feature_id_version_key;`,
}),
})
}
Up: migrate.Queries([]string{
`CREATE EXTENSION IF NOT EXISTS hstore`,
`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`,
`CREATE TABLE apps (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
name varchar(30) NOT NULL,
github_repo text,
docker_repo text,
created_at timestamp without time zone default (now() at time zone 'utc')
)`,
`CREATE TABLE configs (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
app_id uuid NOT NULL references apps(id) ON DELETE CASCADE,
vars hstore,
created_at timestamp without time zone default (now() at time zone 'utc')
)`,
`CREATE TABLE slugs (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
image text NOT NULL,
process_types hstore NOT NULL
)`,
`CREATE TABLE releases (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
app_id uuid NOT NULL references apps(id) ON DELETE CASCADE,
config_id uuid NOT NULL references configs(id) ON DELETE CASCADE,
slug_id uuid NOT NULL references slugs(id) ON DELETE CASCADE,
version int NOT NULL,
description text,
created_at timestamp without time zone default (now() at time zone 'utc')
)`,
`CREATE TABLE processes (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
release_id uuid NOT NULL references releases(id) ON DELETE CASCADE,
"type" text NOT NULL,
quantity int NOT NULL,
command text NOT NULL
)`,
`CREATE TABLE jobs (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
app_id uuid NOT NULL references apps(id) ON DELETE CASCADE,
release_version int NOT NULL,
process_type text NOT NULL,
instance int NOT NULL,
environment hstore NOT NULL,
image text NOT NULL,
command text NOT NULL,
updated_at timestamp without time zone default (now() at time zone 'utc')
)`,
`CREATE TABLE deployments (
id uuid NOT NULL DEFAULT uuid_generate_v4() primary key,
app_id uuid NOT NULL references apps(id) ON DELETE CASCADE,
release_id uuid references releases(id),
image text NOT NULL,
status text NOT NULL,
error text,
created_at timestamp without time zone default (now() at time zone 'utc'),
finished_at timestamp without time zone
)`,
`CREATE UNIQUE INDEX index_apps_on_name ON apps USING btree (name)`,
`CREATE UNIQUE INDEX index_apps_on_github_repo ON apps USING btree (github_repo)`,
`CREATE UNIQUE INDEX index_apps_on_docker_repo ON apps USING btree (docker_repo)`,
`CREATE UNIQUE INDEX index_processes_on_release_id_and_type ON processes USING btree (release_id, "type")`,
`CREATE UNIQUE INDEX index_slugs_on_image ON slugs USING btree (image)`,
`CREATE UNIQUE INDEX index_releases_on_app_id_and_version ON releases USING btree (app_id, version)`,
`CREATE UNIQUE INDEX index_jobs_on_app_id_and_release_version_and_process_type_and_instance ON jobs (app_id, release_version, process_type, instance)`,
`CREATE INDEX index_configs_on_created_at ON configs (created_at)`,
}),
ID: 1,
Up: func(tx *sql.Tx) error {
_, err := tx.Exec("CREATE TABLE people (id int)")
return err
},
Down: func(tx *sql.Tx) error {
_, err := tx.Exec("DROP TABLE people")
return err
},
},
{
ID: 2,
// For simple sql migrations, you can use the migrate.Queries
// helper.
Up: migrate.Queries([]string{
"ALTER TABLE people ADD COLUMN first_name text",
}),
Down: func(tx *sql.Tx) error {
// It's not possible to remove a column with
// sqlite.
_, err := tx.Exec("SELECT 1 FROM people")
return err
},
},
}
func TestMigrate(t *testing.T) {
db := newDB(t)
defer db.Close()
migrations := testMigrations[:]
func init() {
// This migration creates the initial Clair's schema.
RegisterMigration(migrate.Migration{
ID: 2,
Up: migrate.Queries([]string{
`CREATE TABLE IF NOT EXISTS Namespace (
id SERIAL PRIMARY KEY,
name VARCHAR(128) NULL);`,
`CREATE TABLE IF NOT EXISTS Layer (
id SERIAL PRIMARY KEY,
name VARCHAR(128) NOT NULL UNIQUE,
engineversion SMALLINT NOT NULL,
parent_id INT NULL REFERENCES Layer ON DELETE CASCADE,
namespace_id INT NULL REFERENCES Namespace,
created_at TIMESTAMP WITH TIME ZONE);`,
`CREATE INDEX ON Layer (parent_id);`,
`CREATE INDEX ON Layer (namespace_id);`,
`CREATE TABLE IF NOT EXISTS Feature (
id SERIAL PRIMARY KEY,
namespace_id INT NOT NULL REFERENCES Namespace,
name VARCHAR(128) NOT NULL,
UNIQUE (namespace_id, name));`,
`CREATE TABLE IF NOT EXISTS FeatureVersion (
id SERIAL PRIMARY KEY,
feature_id INT NOT NULL REFERENCES Feature,
version VARCHAR(128) NOT NULL);`,
`CREATE INDEX ON FeatureVersion (feature_id);`,
`CREATE TYPE modification AS ENUM ('add', 'del');`,
`CREATE TABLE IF NOT EXISTS Layer_diff_FeatureVersion (
id SERIAL PRIMARY KEY,
layer_id INT NOT NULL REFERENCES Layer ON DELETE CASCADE,
featureversion_id INT NOT NULL REFERENCES FeatureVersion,
modification modification NOT NULL,
UNIQUE (layer_id, featureversion_id));`,
`CREATE INDEX ON Layer_diff_FeatureVersion (layer_id);`,
`CREATE INDEX ON Layer_diff_FeatureVersion (featureversion_id);`,
`CREATE INDEX ON Layer_diff_FeatureVersion (featureversion_id, layer_id);`,
`CREATE TYPE severity AS ENUM ('Unknown', 'Negligible', 'Low', 'Medium', 'High', 'Critical', 'Defcon1');`,
`CREATE TABLE IF NOT EXISTS Vulnerability (
id SERIAL PRIMARY KEY,
namespace_id INT NOT NULL REFERENCES Namespace,
name VARCHAR(128) NOT NULL,
description TEXT NULL,
link VARCHAR(128) NULL,
severity severity NOT NULL,
metadata TEXT NULL,
created_at TIMESTAMP WITH TIME ZONE,
deleted_at TIMESTAMP WITH TIME ZONE NULL);`,
`CREATE TABLE IF NOT EXISTS Vulnerability_FixedIn_Feature (
id SERIAL PRIMARY KEY,
vulnerability_id INT NOT NULL REFERENCES Vulnerability ON DELETE CASCADE,
feature_id INT NOT NULL REFERENCES Feature,
version VARCHAR(128) NOT NULL,
UNIQUE (vulnerability_id, feature_id));`,
`CREATE INDEX ON Vulnerability_FixedIn_Feature (feature_id, vulnerability_id);`,
`CREATE TABLE IF NOT EXISTS Vulnerability_Affects_FeatureVersion (
id SERIAL PRIMARY KEY,
vulnerability_id INT NOT NULL REFERENCES Vulnerability ON DELETE CASCADE,
featureversion_id INT NOT NULL REFERENCES FeatureVersion,
fixedin_id INT NOT NULL REFERENCES Vulnerability_FixedIn_Feature ON DELETE CASCADE,
UNIQUE (vulnerability_id, featureversion_id));`,
`CREATE INDEX ON Vulnerability_Affects_FeatureVersion (fixedin_id);`,
`CREATE INDEX ON Vulnerability_Affects_FeatureVersion (featureversion_id, vulnerability_id);`,
`CREATE TABLE IF NOT EXISTS KeyValue (
id SERIAL PRIMARY KEY,
key VARCHAR(128) NOT NULL UNIQUE,
value TEXT);`,
`CREATE TABLE IF NOT EXISTS Lock (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL UNIQUE,
owner VARCHAR(64) NOT NULL,
until TIMESTAMP WITH TIME ZONE);`,
`CREATE INDEX ON Lock (owner);`,
`CREATE TABLE IF NOT EXISTS Vulnerability_Notification (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL UNIQUE,
created_at TIMESTAMP WITH TIME ZONE,
notified_at TIMESTAMP WITH TIME ZONE NULL,
deleted_at TIMESTAMP WITH TIME ZONE NULL,
old_vulnerability_id INT NULL REFERENCES Vulnerability ON DELETE CASCADE,
new_vulnerability_id INT NULL REFERENCES Vulnerability ON DELETE CASCADE);`,
`CREATE INDEX ON Vulnerability_Notification (notified_at);`,
}),
Down: migrate.Queries([]string{
`DROP TABLE IF EXISTS
Namespace,
Layer,
Feature,
FeatureVersion,
Layer_diff_FeatureVersion,
Vulnerability,
Vulnerability_FixedIn_Feature,
Vulnerability_Affects_FeatureVersion,
Vulnerability_Notification,
KeyValue,
Lock
CASCADE;`,
}),
})
}
