From e0bc27a3b4d77232ba2b7daa08ad91297361e26b Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Thu, 27 Aug 2015 16:01:17 -0700 Subject: Adding ability to upgrade postgres schema --- store/sql_channel_store.go | 3 +- store/sql_post_store.go | 5 -- store/sql_store.go | 148 ++++++++++++++++++++++++++++----------------- store/sql_team_store.go | 10 --- store/sql_user_store.go | 28 +-------- 5 files changed, 95 insertions(+), 99 deletions(-) (limited to 'store') diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go index d503d2225..d2e3943df 100644 --- a/store/sql_channel_store.go +++ b/store/sql_channel_store.go @@ -37,8 +37,7 @@ func NewSqlChannelStore(sqlStore *SqlStore) ChannelStore { } func (s SqlChannelStore) UpgradeSchemaIfNeeded() { - s.CreateColumnIfNotExists("Channels", "ExtraUpdateAt", "TotalMsgCount", "bigint(20)", "0") - s.CreateColumnIfNotExists("Channels", "CreatorId", "ExtraUpdateAt", "varchar(26)", "") + s.CreateColumnIfNotExists("Channels", "CreatorId", "varchar(26)", "character varying(26)", "") } func (s SqlChannelStore) CreateIndexesIfNotExists() { diff --git a/store/sql_post_store.go b/store/sql_post_store.go index 4ea28507b..e7c3a59a0 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -35,11 +35,6 @@ func NewSqlPostStore(sqlStore *SqlStore) PostStore { } func (s SqlPostStore) UpgradeSchemaIfNeeded() { - - // These execs are for upgrading currently created databases to full utf8mb4 compliance - // Will be removed as seen fit for upgrading - s.GetMaster().Exec("ALTER TABLE Posts charset=utf8mb4") - s.GetMaster().Exec("ALTER TABLE Posts MODIFY COLUMN Message varchar(4000) CHARACTER SET utf8mb4") } func (s SqlPostStore) CreateIndexesIfNotExists() { diff --git a/store/sql_store.go b/store/sql_store.go index 0d4f76a72..98c67d668 100644 --- a/store/sql_store.go +++ b/store/sql_store.go @@ -123,13 +123,29 @@ func setupConnection(con_type string, driver string, dataSource string, maxIdle } func (ss SqlStore) DoesColumnExist(tableName string, columnName string) bool { - // XXX TODO FIXME this should be removed after 0.6.0 if utils.Cfg.SqlSettings.DriverName == "postgres" { - return false - } + count, err := ss.GetMaster().SelectInt( + `SELECT COUNT(0) + FROM pg_attribute + WHERE attrelid = $1::regclass + AND attname = $2 + AND NOT attisdropped`, + strings.ToLower(tableName), + strings.ToLower(columnName), + ) + + if err != nil { + l4g.Critical("Failed to check if column exists %v", err) + time.Sleep(time.Second) + panic("Failed to check if column exists " + err.Error()) + } + + return count > 0 - count, err := ss.GetMaster().SelectInt( - `SELECT + } else if utils.Cfg.SqlSettings.DriverName == "mysql" { + + count, err := ss.GetMaster().SelectInt( + `SELECT COUNT(0) AS column_exists FROM information_schema.COLUMNS @@ -137,81 +153,101 @@ func (ss SqlStore) DoesColumnExist(tableName string, columnName string) bool { TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`, - tableName, - columnName, - ) - if err != nil { - l4g.Critical("Failed to check if column exists %v", err) + tableName, + columnName, + ) + + if err != nil { + l4g.Critical("Failed to check if column exists %v", err) + time.Sleep(time.Second) + panic("Failed to check if column exists " + err.Error()) + } + + return count > 0 + + } else { + l4g.Critical("Failed to check if column exists because of missing driver") time.Sleep(time.Second) - panic("Failed to check if column exists " + err.Error()) + panic("Failed to check if column exists because of missing driver") } - return count > 0 } -func (ss SqlStore) CreateColumnIfNotExists(tableName string, columnName string, afterName string, colType string, defaultValue string) bool { - - // XXX TODO FIXME this should be removed after 0.6.0 - if utils.Cfg.SqlSettings.DriverName == "postgres" { - return false - } +func (ss SqlStore) CreateColumnIfNotExists(tableName string, columnName string, mySqlColType string, postgresColType string, defaultValue string) bool { if ss.DoesColumnExist(tableName, columnName) { return false } - _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " ADD " + columnName + " " + colType + " DEFAULT '" + defaultValue + "'" + " AFTER " + afterName) - if err != nil { - l4g.Critical("Failed to create column %v", err) + if utils.Cfg.SqlSettings.DriverName == "postgres" { + _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " ADD " + columnName + " " + postgresColType + " DEFAULT '" + defaultValue + "'") + if err != nil { + l4g.Critical("Failed to create column %v", err) + time.Sleep(time.Second) + panic("Failed to create column " + err.Error()) + } + + return true + + } else if utils.Cfg.SqlSettings.DriverName == "mysql" { + _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " ADD " + columnName + " " + mySqlColType + " DEFAULT '" + defaultValue + "'") + if err != nil { + l4g.Critical("Failed to create column %v", err) + time.Sleep(time.Second) + panic("Failed to create column " + err.Error()) + } + + return true + + } else { + l4g.Critical("Failed to create column because of missing driver") time.Sleep(time.Second) - panic("Failed to create column " + err.Error()) + panic("Failed to create column because of missing driver") } - - return true } -func (ss SqlStore) RemoveColumnIfExists(tableName string, columnName string) bool { +// func (ss SqlStore) RemoveColumnIfExists(tableName string, columnName string) bool { - // XXX TODO FIXME this should be removed after 0.6.0 - if utils.Cfg.SqlSettings.DriverName == "postgres" { - return false - } +// // XXX TODO FIXME this should be removed after 0.6.0 +// if utils.Cfg.SqlSettings.DriverName == "postgres" { +// return false +// } - if !ss.DoesColumnExist(tableName, columnName) { - return false - } +// if !ss.DoesColumnExist(tableName, columnName) { +// return false +// } - _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " DROP COLUMN " + columnName) - if err != nil { - l4g.Critical("Failed to drop column %v", err) - time.Sleep(time.Second) - panic("Failed to drop column " + err.Error()) - } +// _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " DROP COLUMN " + columnName) +// if err != nil { +// l4g.Critical("Failed to drop column %v", err) +// time.Sleep(time.Second) +// panic("Failed to drop column " + err.Error()) +// } - return true -} +// return true +// } -func (ss SqlStore) RenameColumnIfExists(tableName string, oldColumnName string, newColumnName string, colType string) bool { +// func (ss SqlStore) RenameColumnIfExists(tableName string, oldColumnName string, newColumnName string, colType string) bool { - // XXX TODO FIXME this should be removed after 0.6.0 - if utils.Cfg.SqlSettings.DriverName == "postgres" { - return false - } +// // XXX TODO FIXME this should be removed after 0.6.0 +// if utils.Cfg.SqlSettings.DriverName == "postgres" { +// return false +// } - if !ss.DoesColumnExist(tableName, oldColumnName) { - return false - } +// if !ss.DoesColumnExist(tableName, oldColumnName) { +// return false +// } - _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " CHANGE " + oldColumnName + " " + newColumnName + " " + colType) +// _, err := ss.GetMaster().Exec("ALTER TABLE " + tableName + " CHANGE " + oldColumnName + " " + newColumnName + " " + colType) - if err != nil { - l4g.Critical("Failed to rename column %v", err) - time.Sleep(time.Second) - panic("Failed to drop column " + err.Error()) - } +// if err != nil { +// l4g.Critical("Failed to rename column %v", err) +// time.Sleep(time.Second) +// panic("Failed to drop column " + err.Error()) +// } - return true -} +// return true +// } func (ss SqlStore) CreateIndexIfNotExists(indexName string, tableName string, columnName string) { ss.createIndexIfNotExists(indexName, tableName, columnName, false) diff --git a/store/sql_team_store.go b/store/sql_team_store.go index 7b0be0540..2784f8630 100644 --- a/store/sql_team_store.go +++ b/store/sql_team_store.go @@ -5,7 +5,6 @@ package store import ( "github.com/mattermost/platform/model" - "github.com/mattermost/platform/utils" ) type SqlTeamStore struct { @@ -29,15 +28,6 @@ func NewSqlTeamStore(sqlStore *SqlStore) TeamStore { } func (s SqlTeamStore) UpgradeSchemaIfNeeded() { - defaultValue := "0" - if utils.Cfg.TeamSettings.AllowValetDefault { - defaultValue = "1" - } - s.CreateColumnIfNotExists("Teams", "AllowValet", "AllowedDomains", "tinyint(1)", defaultValue) - if !s.DoesColumnExist("Teams", "DisplayName") { - s.RenameColumnIfExists("Teams", "Name", "DisplayName", "varchar(64)") - s.RenameColumnIfExists("Teams", "Domain", "Name", "varchar(64)") - } } func (s SqlTeamStore) CreateIndexesIfNotExists() { diff --git a/store/sql_user_store.go b/store/sql_user_store.go index 4b1189c2e..64a18545a 100644 --- a/store/sql_user_store.go +++ b/store/sql_user_store.go @@ -5,9 +5,9 @@ package store import ( "fmt" - "strings" "github.com/mattermost/platform/model" "github.com/mattermost/platform/utils" + "strings" ) type SqlUserStore struct { @@ -40,32 +40,8 @@ func NewSqlUserStore(sqlStore *SqlStore) UserStore { } func (us SqlUserStore) UpgradeSchemaIfNeeded() { - us.CreateColumnIfNotExists("Users", "LastPictureUpdate", "LastPasswordUpdate", "bigint(20)", "0") - - // migrating the FullName column to Nickname and adding the FirstName and LastName columns for MM-825 - if us.RenameColumnIfExists("Users", "FullName", "Nickname", "varchar(64)") { - us.CreateColumnIfNotExists("Users", "FirstName", "Nickname", "varchar(64)", "") - us.CreateColumnIfNotExists("Users", "LastName", "FirstName", "varchar(64)", "") - - // infer values of first and last name by splitting the previous full name - if _, err := us.GetMaster().Exec("UPDATE Users SET FirstName = SUBSTRING_INDEX(SUBSTRING_INDEX(Nickname, ' ', 1), ' ', -1)"); err != nil { - panic("Failed to set first name from nickname " + err.Error()) - } - - // only set the last name from full names that are comprised of multiple words (ie that have at least one space in them) - if _, err := us.GetMaster().Exec("Update Users SET LastName = SUBSTRING(Nickname, INSTR(Nickname, ' ') + 1) " + - "WHERE CHAR_LENGTH(REPLACE(Nickname, ' ', '')) < CHAR_LENGTH(Nickname)"); err != nil { - panic("Failed to set last name from nickname " + err.Error()) - } - } - - us.CreateColumnIfNotExists("Users", "AuthService", "AuthData", "varchar(32)", "") // for OAuth Client - - us.CreateColumnIfNotExists("Users", "FailedAttempts", "LastPictureUpdate", "int(11)", "0") } -//func (ss SqlStore) CreateColumnIfNotExists(tableName string, columnName string, afterName string, colType string, defaultValue string) bool { - func (us SqlUserStore) CreateIndexesIfNotExists() { us.CreateIndexIfNotExists("idx_users_team_id", "Users", "TeamId") us.CreateIndexIfNotExists("idx_users_email", "Users", "Email") @@ -168,7 +144,7 @@ func (us SqlUserStore) Update(user *model.User, allowActiveUpdate bool) StoreCha nonUsernameKeys := []string{} splitKeys := strings.Split(user.NotifyProps["mention_keys"], ",") for _, key := range splitKeys { - if key != oldUser.Username && key != "@" + oldUser.Username { + if key != oldUser.Username && key != "@"+oldUser.Username { nonUsernameKeys = append(nonUsernameKeys, key) } } -- cgit v1.2.3-1-g7c22