From 1bb85818ca5d7569c17e56d523d9668b83973e65 Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Mon, 22 Feb 2016 16:08:40 -0800 Subject: Fixing device id column for droid --- i18n/en.json | 12 +++++ store/sql_post_store.go | 4 +- store/sql_session_store.go | 9 +++- store/sql_store.go | 110 +++++++++++++++++++++++++++++++++++++++------ store/sql_store_test.go | 60 +++++++++++++++++++++++++ store/sql_user_store.go | 2 +- 6 files changed, 179 insertions(+), 18 deletions(-) diff --git a/i18n/en.json b/i18n/en.json index f45f65526..cafae022e 100644 --- a/i18n/en.json +++ b/i18n/en.json @@ -2359,6 +2359,10 @@ "id": "store.sql.create_index.critical", "translation": "Failed to create index %v" }, + { + "id": "store.sql.remove_index.critical", + "translation": "Failed to remove index %v" + }, { "id": "store.sql.create_index_missing_driver.critical", "translation": "Failed to create index because of missing driver" @@ -2403,6 +2407,14 @@ "id": "store.sql.rename_column.critical", "translation": "Failed to rename column %v" }, + { + "id": "store.sql.alter_column_type.critical", + "translation": "Failed to alter column type %v" + }, + { + "id": "store.sql.maxlength_column.critical", + "translation": "Failed to get max length of column %v" + }, { "id": "store.sql.schema_out_of_date.warn", "translation": "The database schema version of %v appears to be out of date" diff --git a/store/sql_post_store.go b/store/sql_post_store.go index dfb9563eb..6a614b6a7 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -38,10 +38,10 @@ func NewSqlPostStore(sqlStore *SqlStore) PostStore { } func (s SqlPostStore) UpgradeSchemaIfNeeded() { - // ADDED for 1.3 REMOVE for 1.6 + // ADDED for 1.3 REMOVE for 2.2 s.RemoveColumnIfExists("Posts", "ImgCount") - // ADDED for 1.3 REMOVE for 1.6 + // ADDED for 1.3 REMOVE for 2.2 s.GetMaster().Exec(`UPDATE Preferences SET Type = :NewType WHERE Type = :CurrentType`, map[string]string{"NewType": model.POST_JOIN_LEAVE, "CurrentType": "join_leave"}) } diff --git a/store/sql_session_store.go b/store/sql_session_store.go index 6532947f4..8dccc0770 100644 --- a/store/sql_session_store.go +++ b/store/sql_session_store.go @@ -22,7 +22,7 @@ func NewSqlSessionStore(sqlStore *SqlStore) SessionStore { table.ColMap("Token").SetMaxSize(26) table.ColMap("UserId").SetMaxSize(26) table.ColMap("TeamId").SetMaxSize(26) - table.ColMap("DeviceId").SetMaxSize(128) + table.ColMap("DeviceId").SetMaxSize(512) table.ColMap("Roles").SetMaxSize(64) table.ColMap("Props").SetMaxSize(1000) } @@ -31,6 +31,11 @@ func NewSqlSessionStore(sqlStore *SqlStore) SessionStore { } func (me SqlSessionStore) UpgradeSchemaIfNeeded() { + // ADDED for 2.1 REMOVE for 2.5 + deviceIdLength := me.GetMaxLengthOfColumnIfExists("Sessions", "DeviceId") + if len(deviceIdLength) > 0 && deviceIdLength != "512" { + me.AlterColumnTypeIfExists("Sessions", "DeviceId", "VARCHAR(512)", "VARCHAR(512)") + } } func (me SqlSessionStore) CreateIndexesIfNotExists() { @@ -239,7 +244,7 @@ func (me SqlSessionStore) UpdateDeviceId(id, deviceId string) StoreChannel { go func() { result := StoreResult{} if _, err := me.GetMaster().Exec("UPDATE Sessions SET DeviceId = :DeviceId WHERE Id = :Id", map[string]interface{}{"DeviceId": deviceId, "Id": id}); err != nil { - result.Err = model.NewLocAppError("SqlSessionStore.UpdateDeviceId", "store.sql_session.update_device_id.app_error", nil, "") + result.Err = model.NewLocAppError("SqlSessionStore.UpdateDeviceId", "store.sql_session.update_device_id.app_error", nil, err.Error()) } else { result.Data = deviceId } diff --git a/store/sql_store.go b/store/sql_store.go index a994ec57e..9e22191b4 100644 --- a/store/sql_store.go +++ b/store/sql_store.go @@ -139,7 +139,7 @@ func NewSqlStore() Store { sqlStore.preference.(*SqlPreferenceStore).DeleteUnusedFeatures() - if model.IsPreviousVersionsSupported(schemaVersion) { + if model.IsPreviousVersionsSupported(schemaVersion) && !model.IsCurrentVersion(schemaVersion) { sqlStore.system.Update(&model.System{Name: "Version", Value: model.CurrentVersion}) l4g.Warn(utils.T("store.sql.upgraded.warn"), model.CurrentVersion) } @@ -379,6 +379,49 @@ func (ss SqlStore) RenameColumnIfExists(tableName string, oldColumnName string, return true } +func (ss SqlStore) GetMaxLengthOfColumnIfExists(tableName string, columnName string) string { + if !ss.DoesColumnExist(tableName, columnName) { + return "" + } + + var result string + var err error + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_MYSQL { + result, err = ss.GetMaster().SelectStr("SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE table_name = '" + tableName + "' AND COLUMN_NAME = '" + columnName + "'") + } else if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + result, err = ss.GetMaster().SelectStr("SELECT character_maximum_length FROM information_schema.columns WHERE table_name = '" + tableName + "' AND column_name = '" + columnName + "'") + } + + if err != nil { + l4g.Critical(utils.T("store.sql.maxlength_column.critical"), err) + time.Sleep(time.Second) + panic(fmt.Sprintf(utils.T("store.sql.maxlength_column.critical"), err.Error())) + } + + return result +} + +func (ss SqlStore) AlterColumnTypeIfExists(tableName string, columnName string, mySqlColType string, postgresColType string) bool { + if !ss.DoesColumnExist(tableName, columnName) { + return false + } + + var err error + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_MYSQL { + _, err = ss.GetMaster().Exec("ALTER TABLE " + tableName + " MODIFY " + columnName + " " + mySqlColType) + } else if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + _, err = ss.GetMaster().Exec("ALTER TABLE " + tableName + " ALTER COLUMN " + columnName + " TYPE " + mySqlColType) + } + + if err != nil { + l4g.Critical(utils.T("store.sql.alter_column_type.critical"), err) + time.Sleep(time.Second) + panic(fmt.Sprintf(utils.T("store.sql.alter_column_type.critical"), err.Error())) + } + + return true +} + func (ss SqlStore) CreateIndexIfNotExists(indexName string, tableName string, columnName string) { ss.createIndexIfNotExists(indexName, tableName, columnName, INDEX_TYPE_DEFAULT) } @@ -440,25 +483,66 @@ func (ss SqlStore) createIndexIfNotExists(indexName string, tableName string, co } } +func (ss SqlStore) RemoveIndexIfExists(indexName string, tableName string) { + + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + _, err := ss.GetMaster().SelectStr("SELECT $1::regclass", indexName) + // It should fail if the index does not exist + if err == nil { + return + } + + _, err = ss.GetMaster().Exec("DROP INDEX " + indexName) + if err != nil { + l4g.Critical(utils.T("store.sql.remove_index.critical"), err) + time.Sleep(time.Second) + panic(fmt.Sprintf(utils.T("store.sql.remove_index.critical"), err.Error())) + } + } else if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_MYSQL { + + count, err := ss.GetMaster().SelectInt("SELECT COUNT(0) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name = ? AND index_name = ?", tableName, indexName) + if err != nil { + l4g.Critical(utils.T("store.sql.check_index.critical"), err) + time.Sleep(time.Second) + panic(fmt.Sprintf(utils.T("store.sql.check_index.critical"), err.Error())) + } + + if count > 0 { + return + } + + _, err = ss.GetMaster().Exec("DROP INDEX " + indexName + " ON " + tableName) + if err != nil { + l4g.Critical(utils.T("store.sql.remove_index.critical"), err) + time.Sleep(time.Second) + panic(fmt.Sprintf(utils.T("store.sql.remove_index.critical"), err.Error())) + } + } else { + l4g.Critical(utils.T("store.sql.create_index_missing_driver.critical")) + time.Sleep(time.Second) + panic(utils.T("store.sql.create_index_missing_driver.critical")) + } +} + func IsUniqueConstraintError(err string, mysql string, postgres string) bool { unique := strings.Contains(err, "unique constraint") || strings.Contains(err, "Duplicate entry") field := strings.Contains(err, mysql) || strings.Contains(err, postgres) return unique && field } -func (ss SqlStore) GetColumnDataType(tableName, columnName string) string { - dataType, err := ss.GetMaster().SelectStr("SELECT data_type FROM INFORMATION_SCHEMA.COLUMNS where table_name = :Tablename AND column_name = :Columnname", map[string]interface{}{ - "Tablename": tableName, - "Columnname": columnName, - }) - if err != nil { - l4g.Critical(utils.T("store.sql.table_column_type.critical"), columnName, tableName, err.Error()) - time.Sleep(time.Second) - panic(fmt.Sprintf(utils.T("store.sql.table_column_type.critical"), columnName, tableName, err.Error())) - } +// func (ss SqlStore) GetColumnDataType(tableName, columnName string) string { +// dataType, err := ss.GetMaster().SelectStr("SELECT data_type FROM INFORMATION_SCHEMA.COLUMNS where table_name = :Tablename AND column_name = :Columnname", map[string]interface{}{ +// "Tablename": tableName, +// "Columnname": columnName, +// }) +// if err != nil { +// l4g.Critical(utils.T("store.sql.table_column_type.critical"), columnName, tableName, err.Error()) +// time.Sleep(time.Second) +// panic(fmt.Sprintf(utils.T("store.sql.table_column_type.critical"), columnName, tableName, err.Error())) +// } - return dataType -} +// return dataType +// } func (ss SqlStore) GetMaster() *gorp.DbMap { return ss.master diff --git a/store/sql_store_test.go b/store/sql_store_test.go index 1be87dec9..783d72b83 100644 --- a/store/sql_store_test.go +++ b/store/sql_store_test.go @@ -85,3 +85,63 @@ func TestEncrypt(t *testing.T) { t.Fatal("error in encrypt") } } + +func TestAlertDbCmds(t *testing.T) { + Setup() + + sqlStore := store.(*SqlStore) + + if !sqlStore.DoesTableExist("Systems") { + t.Fatal("Failed table exists") + } + + if sqlStore.DoesColumnExist("Systems", "Test") { + t.Fatal("Column should not exist") + } + + if !sqlStore.CreateColumnIfNotExists("Systems", "Test", "VARCHAR(50)", "VARCHAR(50)", "") { + t.Fatal("Failed to create column") + } + + maxLen := sqlStore.GetMaxLengthOfColumnIfExists("Systems", "Test") + + if maxLen != "50" { + t.Fatal("Failed to get max length") + } + + if !sqlStore.AlterColumnTypeIfExists("Systems", "Test", "VARCHAR(25)", "VARCHAR(25)") { + t.Fatal("failed to alter column size") + } + + maxLen2 := sqlStore.GetMaxLengthOfColumnIfExists("Systems", "Test") + + if maxLen2 != "25" { + t.Fatal("Failed to get max length") + } + + if !sqlStore.RenameColumnIfExists("Systems", "Test", "Test1", "VARCHAR(25)") { + t.Fatal("Failed to rename column") + } + + if sqlStore.DoesColumnExist("Systems", "Test") { + t.Fatal("Column should not exist") + } + + if !sqlStore.DoesColumnExist("Systems", "Test1") { + t.Fatal("Column should exist") + } + + sqlStore.CreateIndexIfNotExists("idx_systems_test1", "Systems", "Test1") + sqlStore.RemoveIndexIfExists("idx_systems_test1", "Systems") + + sqlStore.CreateFullTextIndexIfNotExists("idx_systems_test1", "Systems", "Test1") + sqlStore.RemoveIndexIfExists("idx_systems_test1", "Systems") + + if !sqlStore.RemoveColumnIfExists("Systems", "Test1") { + t.Fatal("Failed to remove columns") + } + + if sqlStore.DoesColumnExist("Systems", "Test1") { + t.Fatal("Column should not exist") + } +} diff --git a/store/sql_user_store.go b/store/sql_user_store.go index d866f949a..47c6ea61a 100644 --- a/store/sql_user_store.go +++ b/store/sql_user_store.go @@ -46,7 +46,7 @@ func NewSqlUserStore(sqlStore *SqlStore) UserStore { } func (us SqlUserStore) UpgradeSchemaIfNeeded() { - // ADDED for 1.5 REMOVE for 1.8 + // ADDED for 2.0 REMOVE for 2.4 us.CreateColumnIfNotExists("Users", "Locale", "varchar(5)", "character varying(5)", model.DEFAULT_LOCALE) } -- cgit v1.2.3-1-g7c22