migrate

package
v0.0.0-...-020e9e7 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 28, 2024 License: AGPL-3.0 Imports: 10 Imported by: 0

Documentation

Overview

Package migrate provides database schema migrations.

Copyright (c) 2018 - 2024 PhotoPrism UG. All rights reserved. 

This program is free software: you can redistribute it and/or modify
it under Version 3 of the GNU Affero General Public License (the "AGPL"):
<https://docs.photoprism.app/license/agpl>

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Affero General Public License for more details.

The AGPL is supplemented by our Trademark and Brand Guidelines,
which describe how our Brand Assets may be used:
<https://www.photoprism.app/trademark>

Feel free to send an email to hello@photoprism.app if you have questions, want to support our work, or just want to say hello.

Additional information can be found in our Developer Guide: <https://docs.photoprism.app/developer-guide/>

Index

Constants

View Source 
const (
	MySQL   = "mysql"
	SQLite3 = "sqlite3"
)

Supported database dialects.

View Source 
const (
	StagePre  = "pre"
	StageMain = "main"
)

Variables

View Source 
var DialectMySQL = Migrations{
	{
		ID:         "20211121-094727",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"DROP INDEX IF EXISTS uix_places_place_label ON places;"},
	},
	{
		ID:         "20211124-120008",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"DROP INDEX IF EXISTS idx_places_place_label ON places;", "DROP INDEX IF EXISTS uix_places_label ON places;"},
	},
	{
		ID:         "20220329-030000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY file_projection VARBINARY(64) NULL;", "ALTER TABLE files MODIFY file_color_profile VARBINARY(64) NULL;"},
	},
	{
		ID:         "20220329-040000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"DROP INDEX IF EXISTS idx_albums_album_filter ON albums;", "ALTER TABLE albums MODIFY album_filter VARBINARY(2048) DEFAULT '';", "CREATE OR REPLACE INDEX idx_albums_album_filter ON albums (album_filter(512));"},
	},
	{
		ID:         "20220329-050000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE photos MODIFY photo_description VARCHAR(4096);"},
	},
	{
		ID:         "20220329-060000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE albums MODIFY album_caption VARCHAR(1024);", "ALTER TABLE albums MODIFY album_description VARCHAR(2048);", "ALTER TABLE albums MODIFY album_notes VARCHAR(1024);", "ALTER TABLE cameras MODIFY camera_description VARCHAR(2048);", "ALTER TABLE cameras MODIFY camera_notes VARCHAR(1024);", "ALTER TABLE countries MODIFY country_description VARCHAR(2048);", "ALTER TABLE countries MODIFY country_notes VARCHAR(1024);", "ALTER TABLE details MODIFY keywords VARCHAR(2048);", "ALTER TABLE details MODIFY notes VARCHAR(2048);", "ALTER TABLE details MODIFY subject VARCHAR(1024);", "ALTER TABLE details MODIFY artist VARCHAR(1024);", "ALTER TABLE details MODIFY copyright VARCHAR(1024);", "ALTER TABLE details MODIFY license VARCHAR(1024);", "ALTER TABLE folders MODIFY folder_description VARCHAR(2048);", "ALTER TABLE labels MODIFY label_description VARCHAR(2048);", "ALTER TABLE labels MODIFY label_notes VARCHAR(1024);", "ALTER TABLE lenses MODIFY lens_description VARCHAR(2048);", "ALTER TABLE lenses MODIFY lens_notes VARCHAR(1024);", "ALTER TABLE subjects MODIFY subj_bio VARCHAR(2048);", "ALTER TABLE subjects MODIFY subj_notes VARCHAR(1024);"},
	},
	{
		ID:         "20220329-061000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"CREATE OR REPLACE INDEX idx_files_photo_id ON files (photo_id, file_primary);"},
	},
	{
		ID:         "20220329-070000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY COLUMN IF EXISTS photo_taken_at DATETIME AFTER photo_uid;", "ALTER TABLE files ADD COLUMN IF NOT EXISTS photo_taken_at DATETIME AFTER photo_uid;"},
	},
	{
		ID:         "20220329-071000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"UPDATE files f JOIN photos p ON p.id = f.photo_id SET f.photo_taken_at = p.taken_at_local;"},
	},
	{
		ID:         "20220329-080000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY IF EXISTS media_id VARBINARY(32) AFTER photo_taken_at;", "ALTER TABLE files ADD IF NOT EXISTS media_id VARBINARY(32) AFTER photo_taken_at;"},
	},
	{
		ID:         "20220329-081000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"CREATE OR REPLACE UNIQUE INDEX idx_files_search_media ON files (media_id);"},
	},
	{
		ID:         "20220329-083000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"UPDATE files SET media_id = CASE WHEN file_missing = 0 AND deleted_at IS NULL THEN CONCAT((10000000000 - photo_id), '-', 1 + file_sidecar - file_primary, '-', file_uid) END;"},
	},
	{
		ID:         "20220329-090000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY IF EXISTS time_index VARBINARY(64) AFTER photo_taken_at;", "ALTER TABLE files ADD IF NOT EXISTS time_index VARBINARY(64) AFTER photo_taken_at;"},
	},
	{
		ID:         "20220329-091000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"CREATE OR REPLACE UNIQUE INDEX idx_files_search_timeline ON files (time_index);"},
	},
	{
		ID:         "20220329-093000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"UPDATE files SET time_index = CASE WHEN file_missing = 0 AND deleted_at IS NULL THEN CONCAT(100000000000000 - CAST(photo_taken_at AS UNSIGNED), '-', media_id) END;"},
	},
	{
		ID:         "20220421-200000",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"CREATE OR REPLACE INDEX idx_files_missing_root ON files (file_missing, file_root);"},
	},
	{
		ID:         "20220521-000001",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE photos MODIFY photo_color SMALLINT DEFAULT -1;"},
	},
	{
		ID:         "20220521-000002",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY file_diff INTEGER DEFAULT -1;"},
	},
	{
		ID:         "20220521-000003",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY file_chroma SMALLINT DEFAULT -1;"},
	},
	{
		ID:         "20220927-000100",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY time_index VARBINARY(64);"},
	},
	{
		ID:         "20221002-000100",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE links DROP COLUMN IF EXISTS can_edit;", "ALTER TABLE links DROP COLUMN IF EXISTS can_comment;"},
	},
	{
		ID:         "20221015-100000",
		Dialect:    "mysql",
		Stage:      "pre",
		Statements: []string{"RENAME TABLE IF EXISTS `accounts` TO `services`;"},
	},
	{
		ID:         "20221015-100100",
		Dialect:    "mysql",
		Stage:      "pre",
		Statements: []string{"ALTER IGNORE TABLE files_sync CHANGE account_id service_id INT UNSIGNED NOT NULL;", "ALTER IGNORE TABLE files_share CHANGE account_id service_id INT UNSIGNED NOT NULL;"},
	},
	{
		ID:         "20230102-000001",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE albums MODIFY IF EXISTS album_path VARCHAR(1024);"},
	},
	{
		ID:         "20230211-000001",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"ALTER TABLE files MODIFY IF EXISTS file_colors VARBINARY(18);", "ALTER TABLE files MODIFY IF EXISTS File_luminance VARBINARY(18);"},
	},
	{
		ID:         "20230309-000001",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"UPDATE auth_users SET auth_provider = 'local' WHERE id = 1;", "UPDATE auth_users SET auth_provider = 'none' WHERE id = -1;", "UPDATE auth_users SET auth_provider = 'token' WHERE id = -2;", "UPDATE auth_users SET auth_provider = 'default' WHERE auth_provider = '' OR auth_provider = 'password' OR auth_provider IS NULL;"},
	},
	{
		ID:         "20230313-000001",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"UPDATE auth_users SET user_role = 'contributor' WHERE user_role = 'uploader';", "UPDATE auth_sessions SET auth_provider = 'link' WHERE auth_provider = 'token';"},
	},
	{
		ID:         "20240112-000001",
		Dialect:    "mysql",
		Stage:      "main",
		Statements: []string{"TRUNCATE auth_sessions;"},
	},
}
View Source 
var DialectSQLite3 = Migrations{
	{
		ID:         "20211121-094727",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"DROP INDEX IF EXISTS idx_places_place_label;"},
	},
	{
		ID:         "20211124-120008",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"DROP INDEX IF EXISTS uix_places_place_label;", "DROP INDEX IF EXISTS uix_places_label;"},
	},
	{
		ID:         "20220329-040000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"DROP INDEX IF EXISTS idx_albums_album_filter;"},
	},
	{
		ID:         "20220329-050000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"CREATE INDEX IF NOT EXISTS idx_albums_album_filter ON albums (album_filter);"},
	},
	{
		ID:         "20220329-061000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"CREATE INDEX IF NOT EXISTS idx_files_photo_id ON files (photo_id, file_primary);"},
	},
	{
		ID:         "20220329-071000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"UPDATE files SET photo_taken_at = (SELECT taken_at_local FROM photos WHERE photos.id = photo_id) WHERE photo_id IS NOT NULL;"},
	},
	{
		ID:         "20220329-081000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"CREATE UNIQUE INDEX IF NOT EXISTS idx_files_search_media ON files (media_id);"},
	},
	{
		ID:         "20220329-083000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"UPDATE files SET media_id = CASE WHEN photo_id IS NOT NULL AND file_missing = 0 AND deleted_at IS NULL THEN ((10000000000 - photo_id) || '-' || (1 + file_sidecar - file_primary) || '-' || file_uid) END WHERE 1;"},
	},
	{
		ID:         "20220329-091000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"CREATE UNIQUE INDEX IF NOT EXISTS idx_files_search_timeline ON files (time_index);"},
	},
	{
		ID:         "20220329-093000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"UPDATE files SET time_index = CASE WHEN media_id IS NOT NULL AND photo_taken_at IS NOT NULL THEN ((100000000000000 - strftime('%Y%m%d%H%M%S', photo_taken_at)) || '-' || media_id) ELSE NULL END WHERE photo_id IS NOT NULL;"},
	},
	{
		ID:         "20220421-200000",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"CREATE INDEX IF NOT EXISTS idx_files_missing_root ON files (file_missing, file_root);"},
	},
	{
		ID:         "20221015-100000",
		Dialect:    "sqlite3",
		Stage:      "pre",
		Statements: []string{"ALTER TABLE accounts RENAME TO services;"},
	},
	{
		ID:         "20221015-100100",
		Dialect:    "sqlite3",
		Stage:      "pre",
		Statements: []string{"ALTER TABLE files_sync RENAME COLUMN account_id TO service_id;", "ALTER TABLE files_share RENAME COLUMN account_id TO service_id;"},
	},
	{
		ID:         "20230309-000001",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"UPDATE auth_users SET auth_provider = 'local' WHERE id = 1;", "UPDATE auth_users SET auth_provider = 'none' WHERE id = -1;", "UPDATE auth_users SET auth_provider = 'token' WHERE id = -2;", "UPDATE auth_users SET auth_provider = 'default' WHERE auth_provider = '' OR auth_provider = 'password' OR auth_provider IS NULL;"},
	},
	{
		ID:         "20230313-000001",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"UPDATE auth_users SET user_role = 'contributor' WHERE user_role = 'uploader';", "UPDATE auth_sessions SET auth_provider = 'link' WHERE auth_provider = 'token';"},
	},
	{
		ID:         "20240112-000001",
		Dialect:    "sqlite3",
		Stage:      "main",
		Statements: []string{"DELETE FROM auth_sessions;"},
	},
}
View Source 
var Dialects = map[string]Migrations{
	MySQL:   DialectMySQL,
	SQLite3: DialectSQLite3,
}
View Source 
var IgnoreErr = QueryErr{
	"rename":       {"no such", "already exists"},
	"replace":      {"no such", "exist", "exists"},
	" ignore ":     {"no such", "exist", "exists"},
	"drop index ":  {"drop"},
	"drop table ":  {"drop"},
	"alter table ": {"duplicate"},
}
View Source 
var UnknownVersion = Version{
	Version: "0.0.0",
	Edition: "dev",
}

Functions

func Run 

func Run(db *gorm.DB, opt Options) (err error)

Run automatically migrates the schema of the database passed as argument.

Types

type Map 

type Map map[string]interface{}

Map is an alias for map[string]interface{}.

type Migration 

type Migration struct {
	ID         string     `gorm:"size:16;primary_key;auto_increment:false;" json:"ID" yaml:"ID"`
	Dialect    string     `gorm:"size:16;" json:"Dialect" yaml:"Dialect,omitempty"`
	Stage      string     `gorm:"size:16;" json:"Stage" yaml:"Stage,omitempty"`
	Error      string     `gorm:"size:255;" json:"Error" yaml:"Error,omitempty"`
	Source     string     `gorm:"size:16;" json:"Source" yaml:"Source,omitempty"`
	Statements []string   `gorm:"-" json:"Statements" yaml:"Statements,omitempty"`
	StartedAt  time.Time  `json:"StartedAt" yaml:"StartedAt,omitempty"`
	FinishedAt *time.Time `json:"FinishedAt" yaml:"FinishedAt,omitempty"`
}

Migration represents a database schema migration.

func (*Migration) Execute 

func (m *Migration) Execute(db *gorm.DB) error

Execute runs the migration.

func (*Migration) Fail 

func (m *Migration) Fail(err error, db *gorm.DB)

Fail marks the migration as failed by adding an error message and removing the FinishedAt timestamp.

func (*Migration) Finish 

func (m *Migration) Finish(db *gorm.DB) error

Finish updates the FinishedAt timestamp and removes the error message when the migration was successful.

func (*Migration) Finished 

func (m *Migration) Finished() bool

Finished tests if the migration has been finished yet.

func (*Migration) Repeat 

func (m *Migration) Repeat(runFailed bool) bool

Repeat tests if the migration should be repeated.

func (*Migration) RunDuration 

func (m *Migration) RunDuration() time.Duration

RunDuration returns the run duration of started migrations.

func (*Migration) RunStage 

func (m *Migration) RunStage(name string) bool

RunStage checks if the run stage matches.

func (*Migration) Skip 

func (m *Migration) Skip(opt Options) bool

Skip checks if the migration should be skipped based on the options.

func (*Migration) StageName 

func (m *Migration) StageName() string

StageName returns the stage name.

func (Migration) TableName 

func (Migration) TableName() string

TableName returns the entity database table name.

type MigrationMap 

type MigrationMap map[string]Migration

MigrationMap represents a map of migrations.

func Existing 

func Existing(db *gorm.DB, stage string) MigrationMap

Existing finds and returns previously executed database schema migrations.

type Migrations 

type Migrations []Migration

Migrations represents a sorted list of migrations.

func Status 

func Status(db *gorm.DB, ids []string) (status Migrations, err error)

Status returns the current status of schema migrations.

func (*Migrations) Start 

func (m *Migrations) Start(db *gorm.DB, opt Options)

Start runs all migrations that haven't been executed yet.

type Options 

type Options struct {
	AutoMigrate    bool
	RunStage       string
	RunFailed      bool
	Migrations     []string
	DropDeprecated bool
}

Options represents migration selection options.

func Opt 

func Opt(runAll, runFailed bool, ids []string) Options

Opt returns migration options based on the specified parameters.

func (Options) Pre 

func (opt Options) Pre() Options

Pre returns options for the pre-migration stage.

func (Options) Stage 

func (opt Options) Stage(name string) Options

Stage returns options for the specified migration stage.

func (Options) StageName 

func (opt Options) StageName() string

StageName returns the stage name.

type QueryErr 

type QueryErr map[string][]string

func (QueryErr) Matches 

func (m QueryErr) Matches(query, err string) bool

Matches checks if there is a match for the specified query and error string.

type Version 

type Version struct {
	ID         uint       `gorm:"primary_key" yaml:"-"`
	Version    string     `gorm:"size:255;unique_index:idx_version_edition;" json:"Version" yaml:"Version,omitempty"`
	Edition    string     `gorm:"size:255;unique_index:idx_version_edition;" json:"Edition" yaml:"Edition,omitempty"`
	Error      string     `gorm:"size:255;" json:"Error" yaml:"Error,omitempty"`
	CreatedAt  time.Time  `yaml:"CreatedAt,omitempty"`
	UpdatedAt  time.Time  `yaml:"UpdatedAt,omitempty"`
	MigratedAt *time.Time `json:"MigratedAt" yaml:"MigratedAt,omitempty"`
}

Version represents the application version.

func FirstOrCreateVersion 

func FirstOrCreateVersion(db *gorm.DB, m *Version) *Version

FirstOrCreateVersion returns the existing row, inserts a new row or nil in case of errors.

func NewVersion 

func NewVersion(version, edition string) *Version

NewVersion creates a Version entity from a model name and a make name.

func (*Version) Create 

func (m *Version) Create(db *gorm.DB) error

Create inserts a new row to the database.

func (*Version) CreateTable 

func (m *Version) CreateTable(db *gorm.DB) (err error)

CreateTable creates the versions database table if needed.

func (*Version) Find 

func (m *Version) Find(db *gorm.DB) *Version

Find fetches an existing record from the database.

func (*Version) Migrated 

func (m *Version) Migrated(db *gorm.DB) error

Migrated flags the version as installed and migrated.

func (*Version) NeedsMigration 

func (m *Version) NeedsMigration() bool

NeedsMigration tests if the Version is not yet installed.

func (*Version) Save 

func (m *Version) Save(db *gorm.DB) error

Save saved the record in the database.

func (*Version) String 

func (m *Version) String() string

String returns an identifier that can be used in logs.

func (Version) TableName 

func (Version) TableName() string

TableName returns the entity database table name.

func (*Version) Unknown 

func (m *Version) Unknown() bool

Unknown checks if the version is unknown.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.