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;`, }), }) }