github.com/demisto/mattermost-server@v4.9.0-rc3+incompatible/store/sqlstore/supplier.go (about) 1 // Copyright (c) 2016-present Mattermost, Inc. All Rights Reserved. 2 // See License.txt for license information. 3 4 package sqlstore 5 6 import ( 7 "context" 8 dbsql "database/sql" 9 "encoding/json" 10 "errors" 11 "fmt" 12 sqltrace "log" 13 "os" 14 "strings" 15 "sync/atomic" 16 "time" 17 18 l4g "github.com/alecthomas/log4go" 19 "github.com/go-sql-driver/mysql" 20 "github.com/lib/pq" 21 "github.com/mattermost/gorp" 22 "github.com/mattermost/mattermost-server/einterfaces" 23 "github.com/mattermost/mattermost-server/model" 24 "github.com/mattermost/mattermost-server/store" 25 "github.com/mattermost/mattermost-server/utils" 26 ) 27 28 const ( 29 INDEX_TYPE_FULL_TEXT = "full_text" 30 INDEX_TYPE_DEFAULT = "default" 31 MAX_DB_CONN_LIFETIME = 60 32 DB_PING_ATTEMPTS = 18 33 DB_PING_TIMEOUT_SECS = 10 34 ) 35 36 const ( 37 EXIT_CREATE_TABLE = 100 38 EXIT_DB_OPEN = 101 39 EXIT_PING = 102 40 EXIT_NO_DRIVER = 103 41 EXIT_TABLE_EXISTS = 104 42 EXIT_TABLE_EXISTS_MYSQL = 105 43 EXIT_COLUMN_EXISTS = 106 44 EXIT_DOES_COLUMN_EXISTS_POSTGRES = 107 45 EXIT_DOES_COLUMN_EXISTS_MYSQL = 108 46 EXIT_DOES_COLUMN_EXISTS_MISSING = 109 47 EXIT_CREATE_COLUMN_POSTGRES = 110 48 EXIT_CREATE_COLUMN_MYSQL = 111 49 EXIT_CREATE_COLUMN_MISSING = 112 50 EXIT_REMOVE_COLUMN = 113 51 EXIT_RENAME_COLUMN = 114 52 EXIT_MAX_COLUMN = 115 53 EXIT_ALTER_COLUMN = 116 54 EXIT_CREATE_INDEX_POSTGRES = 117 55 EXIT_CREATE_INDEX_MYSQL = 118 56 EXIT_CREATE_INDEX_FULL_MYSQL = 119 57 EXIT_CREATE_INDEX_MISSING = 120 58 EXIT_REMOVE_INDEX_POSTGRES = 121 59 EXIT_REMOVE_INDEX_MYSQL = 122 60 EXIT_REMOVE_INDEX_MISSING = 123 61 EXIT_REMOVE_TABLE = 134 62 ) 63 64 type SqlSupplierOldStores struct { 65 team store.TeamStore 66 channel store.ChannelStore 67 post store.PostStore 68 user store.UserStore 69 audit store.AuditStore 70 cluster store.ClusterDiscoveryStore 71 compliance store.ComplianceStore 72 session store.SessionStore 73 oauth store.OAuthStore 74 system store.SystemStore 75 webhook store.WebhookStore 76 command store.CommandStore 77 commandWebhook store.CommandWebhookStore 78 preference store.PreferenceStore 79 license store.LicenseStore 80 token store.TokenStore 81 emoji store.EmojiStore 82 status store.StatusStore 83 fileInfo store.FileInfoStore 84 reaction store.ReactionStore 85 job store.JobStore 86 userAccessToken store.UserAccessTokenStore 87 plugin store.PluginStore 88 channelMemberHistory store.ChannelMemberHistoryStore 89 role store.RoleStore 90 } 91 92 type SqlSupplier struct { 93 // rrCounter and srCounter should be kept first. 94 // See https://github.com/mattermost/mattermost-server/pull/7281 95 rrCounter int64 96 srCounter int64 97 next store.LayeredStoreSupplier 98 master *gorp.DbMap 99 replicas []*gorp.DbMap 100 searchReplicas []*gorp.DbMap 101 oldStores SqlSupplierOldStores 102 settings *model.SqlSettings 103 } 104 105 func NewSqlSupplier(settings model.SqlSettings, metrics einterfaces.MetricsInterface) *SqlSupplier { 106 supplier := &SqlSupplier{ 107 rrCounter: 0, 108 srCounter: 0, 109 settings: &settings, 110 } 111 112 supplier.initConnection() 113 114 supplier.oldStores.team = NewSqlTeamStore(supplier) 115 supplier.oldStores.channel = NewSqlChannelStore(supplier, metrics) 116 supplier.oldStores.post = NewSqlPostStore(supplier, metrics) 117 supplier.oldStores.user = NewSqlUserStore(supplier, metrics) 118 supplier.oldStores.audit = NewSqlAuditStore(supplier) 119 supplier.oldStores.cluster = NewSqlClusterDiscoveryStore(supplier) 120 supplier.oldStores.compliance = NewSqlComplianceStore(supplier) 121 supplier.oldStores.session = NewSqlSessionStore(supplier) 122 supplier.oldStores.oauth = NewSqlOAuthStore(supplier) 123 supplier.oldStores.system = NewSqlSystemStore(supplier) 124 supplier.oldStores.webhook = NewSqlWebhookStore(supplier, metrics) 125 supplier.oldStores.command = NewSqlCommandStore(supplier) 126 supplier.oldStores.commandWebhook = NewSqlCommandWebhookStore(supplier) 127 supplier.oldStores.preference = NewSqlPreferenceStore(supplier) 128 supplier.oldStores.license = NewSqlLicenseStore(supplier) 129 supplier.oldStores.token = NewSqlTokenStore(supplier) 130 supplier.oldStores.emoji = NewSqlEmojiStore(supplier, metrics) 131 supplier.oldStores.status = NewSqlStatusStore(supplier) 132 supplier.oldStores.fileInfo = NewSqlFileInfoStore(supplier, metrics) 133 supplier.oldStores.job = NewSqlJobStore(supplier) 134 supplier.oldStores.userAccessToken = NewSqlUserAccessTokenStore(supplier) 135 supplier.oldStores.channelMemberHistory = NewSqlChannelMemberHistoryStore(supplier) 136 supplier.oldStores.plugin = NewSqlPluginStore(supplier) 137 138 initSqlSupplierReactions(supplier) 139 initSqlSupplierRoles(supplier) 140 141 err := supplier.GetMaster().CreateTablesIfNotExists() 142 if err != nil { 143 l4g.Critical(utils.T("store.sql.creating_tables.critical"), err) 144 time.Sleep(time.Second) 145 os.Exit(EXIT_CREATE_TABLE) 146 } 147 148 UpgradeDatabase(supplier) 149 150 supplier.oldStores.team.(*SqlTeamStore).CreateIndexesIfNotExists() 151 supplier.oldStores.channel.(*SqlChannelStore).CreateIndexesIfNotExists() 152 supplier.oldStores.post.(*SqlPostStore).CreateIndexesIfNotExists() 153 supplier.oldStores.user.(*SqlUserStore).CreateIndexesIfNotExists() 154 supplier.oldStores.audit.(*SqlAuditStore).CreateIndexesIfNotExists() 155 supplier.oldStores.compliance.(*SqlComplianceStore).CreateIndexesIfNotExists() 156 supplier.oldStores.session.(*SqlSessionStore).CreateIndexesIfNotExists() 157 supplier.oldStores.oauth.(*SqlOAuthStore).CreateIndexesIfNotExists() 158 supplier.oldStores.system.(*SqlSystemStore).CreateIndexesIfNotExists() 159 supplier.oldStores.webhook.(*SqlWebhookStore).CreateIndexesIfNotExists() 160 supplier.oldStores.command.(*SqlCommandStore).CreateIndexesIfNotExists() 161 supplier.oldStores.commandWebhook.(*SqlCommandWebhookStore).CreateIndexesIfNotExists() 162 supplier.oldStores.preference.(*SqlPreferenceStore).CreateIndexesIfNotExists() 163 supplier.oldStores.license.(*SqlLicenseStore).CreateIndexesIfNotExists() 164 supplier.oldStores.token.(*SqlTokenStore).CreateIndexesIfNotExists() 165 supplier.oldStores.emoji.(*SqlEmojiStore).CreateIndexesIfNotExists() 166 supplier.oldStores.status.(*SqlStatusStore).CreateIndexesIfNotExists() 167 supplier.oldStores.fileInfo.(*SqlFileInfoStore).CreateIndexesIfNotExists() 168 supplier.oldStores.job.(*SqlJobStore).CreateIndexesIfNotExists() 169 supplier.oldStores.userAccessToken.(*SqlUserAccessTokenStore).CreateIndexesIfNotExists() 170 supplier.oldStores.plugin.(*SqlPluginStore).CreateIndexesIfNotExists() 171 172 supplier.oldStores.preference.(*SqlPreferenceStore).DeleteUnusedFeatures() 173 174 return supplier 175 } 176 177 func (s *SqlSupplier) SetChainNext(next store.LayeredStoreSupplier) { 178 s.next = next 179 } 180 181 func (s *SqlSupplier) Next() store.LayeredStoreSupplier { 182 return s.next 183 } 184 185 func setupConnection(con_type string, dataSource string, settings *model.SqlSettings) *gorp.DbMap { 186 db, err := dbsql.Open(*settings.DriverName, dataSource) 187 if err != nil { 188 l4g.Critical(utils.T("store.sql.open_conn.critical"), err) 189 time.Sleep(time.Second) 190 os.Exit(EXIT_DB_OPEN) 191 } 192 193 for i := 0; i < DB_PING_ATTEMPTS; i++ { 194 l4g.Info("Pinging SQL %v database", con_type) 195 ctx, cancel := context.WithTimeout(context.Background(), DB_PING_TIMEOUT_SECS*time.Second) 196 defer cancel() 197 err = db.PingContext(ctx) 198 if err == nil { 199 break 200 } else { 201 if i == DB_PING_ATTEMPTS-1 { 202 l4g.Critical("Failed to ping DB, server will exit err=%v", err) 203 time.Sleep(time.Second) 204 os.Exit(EXIT_PING) 205 } else { 206 l4g.Error("Failed to ping DB retrying in %v seconds err=%v", DB_PING_TIMEOUT_SECS, err) 207 time.Sleep(DB_PING_TIMEOUT_SECS * time.Second) 208 } 209 } 210 } 211 212 db.SetMaxIdleConns(*settings.MaxIdleConns) 213 db.SetMaxOpenConns(*settings.MaxOpenConns) 214 db.SetConnMaxLifetime(time.Duration(MAX_DB_CONN_LIFETIME) * time.Minute) 215 216 var dbmap *gorp.DbMap 217 218 connectionTimeout := time.Duration(*settings.QueryTimeout) * time.Second 219 220 if *settings.DriverName == "sqlite3" { 221 dbmap = &gorp.DbMap{Db: db, TypeConverter: mattermConverter{}, Dialect: gorp.SqliteDialect{}, QueryTimeout: connectionTimeout} 222 } else if *settings.DriverName == model.DATABASE_DRIVER_MYSQL { 223 dbmap = &gorp.DbMap{Db: db, TypeConverter: mattermConverter{}, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8MB4"}, QueryTimeout: connectionTimeout} 224 } else if *settings.DriverName == model.DATABASE_DRIVER_POSTGRES { 225 dbmap = &gorp.DbMap{Db: db, TypeConverter: mattermConverter{}, Dialect: gorp.PostgresDialect{}, QueryTimeout: connectionTimeout} 226 } else { 227 l4g.Critical(utils.T("store.sql.dialect_driver.critical")) 228 time.Sleep(time.Second) 229 os.Exit(EXIT_NO_DRIVER) 230 } 231 232 if settings.Trace { 233 dbmap.TraceOn("", sqltrace.New(os.Stdout, "sql-trace:", sqltrace.Lmicroseconds)) 234 } 235 236 return dbmap 237 } 238 239 func (s *SqlSupplier) initConnection() { 240 s.master = setupConnection("master", *s.settings.DataSource, s.settings) 241 242 if len(s.settings.DataSourceReplicas) == 0 { 243 s.replicas = make([]*gorp.DbMap, 1) 244 s.replicas[0] = s.master 245 } else { 246 s.replicas = make([]*gorp.DbMap, len(s.settings.DataSourceReplicas)) 247 for i, replica := range s.settings.DataSourceReplicas { 248 s.replicas[i] = setupConnection(fmt.Sprintf("replica-%v", i), replica, s.settings) 249 } 250 } 251 252 if len(s.settings.DataSourceSearchReplicas) == 0 { 253 s.searchReplicas = s.replicas 254 } else { 255 s.searchReplicas = make([]*gorp.DbMap, len(s.settings.DataSourceSearchReplicas)) 256 for i, replica := range s.settings.DataSourceSearchReplicas { 257 s.searchReplicas[i] = setupConnection(fmt.Sprintf("search-replica-%v", i), replica, s.settings) 258 } 259 } 260 } 261 262 func (ss *SqlSupplier) DriverName() string { 263 return *ss.settings.DriverName 264 } 265 266 func (ss *SqlSupplier) GetCurrentSchemaVersion() string { 267 version, _ := ss.GetMaster().SelectStr("SELECT Value FROM Systems WHERE Name='Version'") 268 return version 269 } 270 271 func (ss *SqlSupplier) GetMaster() *gorp.DbMap { 272 return ss.master 273 } 274 275 func (ss *SqlSupplier) GetSearchReplica() *gorp.DbMap { 276 rrNum := atomic.AddInt64(&ss.srCounter, 1) % int64(len(ss.searchReplicas)) 277 return ss.searchReplicas[rrNum] 278 } 279 280 func (ss *SqlSupplier) GetReplica() *gorp.DbMap { 281 rrNum := atomic.AddInt64(&ss.rrCounter, 1) % int64(len(ss.replicas)) 282 return ss.replicas[rrNum] 283 } 284 285 func (ss *SqlSupplier) TotalMasterDbConnections() int { 286 return ss.GetMaster().Db.Stats().OpenConnections 287 } 288 289 func (ss *SqlSupplier) TotalReadDbConnections() int { 290 291 if len(ss.settings.DataSourceReplicas) == 0 { 292 return 0 293 } 294 295 count := 0 296 for _, db := range ss.replicas { 297 count = count + db.Db.Stats().OpenConnections 298 } 299 300 return count 301 } 302 303 func (ss *SqlSupplier) TotalSearchDbConnections() int { 304 if len(ss.settings.DataSourceSearchReplicas) == 0 { 305 return 0 306 } 307 308 count := 0 309 for _, db := range ss.searchReplicas { 310 count = count + db.Db.Stats().OpenConnections 311 } 312 313 return count 314 } 315 316 func (ss *SqlSupplier) MarkSystemRanUnitTests() { 317 if result := <-ss.System().Get(); result.Err == nil { 318 props := result.Data.(model.StringMap) 319 unitTests := props[model.SYSTEM_RAN_UNIT_TESTS] 320 if len(unitTests) == 0 { 321 systemTests := &model.System{Name: model.SYSTEM_RAN_UNIT_TESTS, Value: "1"} 322 <-ss.System().Save(systemTests) 323 } 324 } 325 } 326 327 func (ss *SqlSupplier) DoesTableExist(tableName string) bool { 328 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 329 count, err := ss.GetMaster().SelectInt( 330 `SELECT count(relname) FROM pg_class WHERE relname=$1`, 331 strings.ToLower(tableName), 332 ) 333 334 if err != nil { 335 l4g.Critical(utils.T("store.sql.table_exists.critical"), err) 336 time.Sleep(time.Second) 337 os.Exit(EXIT_TABLE_EXISTS) 338 } 339 340 return count > 0 341 342 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 343 344 count, err := ss.GetMaster().SelectInt( 345 `SELECT 346 COUNT(0) AS table_exists 347 FROM 348 information_schema.TABLES 349 WHERE 350 TABLE_SCHEMA = DATABASE() 351 AND TABLE_NAME = ? 352 `, 353 tableName, 354 ) 355 356 if err != nil { 357 l4g.Critical(utils.T("store.sql.table_exists.critical"), err) 358 time.Sleep(time.Second) 359 os.Exit(EXIT_TABLE_EXISTS_MYSQL) 360 } 361 362 return count > 0 363 364 } else { 365 l4g.Critical(utils.T("store.sql.column_exists_missing_driver.critical")) 366 time.Sleep(time.Second) 367 os.Exit(EXIT_COLUMN_EXISTS) 368 return false 369 } 370 } 371 372 func (ss *SqlSupplier) DoesColumnExist(tableName string, columnName string) bool { 373 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 374 count, err := ss.GetMaster().SelectInt( 375 `SELECT COUNT(0) 376 FROM pg_attribute 377 WHERE attrelid = $1::regclass 378 AND attname = $2 379 AND NOT attisdropped`, 380 strings.ToLower(tableName), 381 strings.ToLower(columnName), 382 ) 383 384 if err != nil { 385 if err.Error() == "pq: relation \""+strings.ToLower(tableName)+"\" does not exist" { 386 return false 387 } 388 389 l4g.Critical(utils.T("store.sql.column_exists.critical"), err) 390 time.Sleep(time.Second) 391 os.Exit(EXIT_DOES_COLUMN_EXISTS_POSTGRES) 392 } 393 394 return count > 0 395 396 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 397 398 count, err := ss.GetMaster().SelectInt( 399 `SELECT 400 COUNT(0) AS column_exists 401 FROM 402 information_schema.COLUMNS 403 WHERE 404 TABLE_SCHEMA = DATABASE() 405 AND TABLE_NAME = ? 406 AND COLUMN_NAME = ?`, 407 tableName, 408 columnName, 409 ) 410 411 if err != nil { 412 l4g.Critical(utils.T("store.sql.column_exists.critical"), err) 413 time.Sleep(time.Second) 414 os.Exit(EXIT_DOES_COLUMN_EXISTS_MYSQL) 415 } 416 417 return count > 0 418 419 } else { 420 l4g.Critical(utils.T("store.sql.column_exists_missing_driver.critical")) 421 time.Sleep(time.Second) 422 os.Exit(EXIT_DOES_COLUMN_EXISTS_MISSING) 423 return false 424 } 425 } 426 427 func (ss *SqlSupplier) CreateColumnIfNotExists(tableName string, columnName string, mySqlColType string, postgresColType string, defaultValue string) bool { 428 429 if ss.DoesColumnExist(tableName, columnName) { 430 return false 431 } 432 433 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 434 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ADD " + columnName + " " + postgresColType + " DEFAULT '" + defaultValue + "'") 435 if err != nil { 436 l4g.Critical(utils.T("store.sql.create_column.critical"), err) 437 time.Sleep(time.Second) 438 os.Exit(EXIT_CREATE_COLUMN_POSTGRES) 439 } 440 441 return true 442 443 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 444 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ADD " + columnName + " " + mySqlColType + " DEFAULT '" + defaultValue + "'") 445 if err != nil { 446 l4g.Critical(utils.T("store.sql.create_column.critical"), err) 447 time.Sleep(time.Second) 448 os.Exit(EXIT_CREATE_COLUMN_MYSQL) 449 } 450 451 return true 452 453 } else { 454 l4g.Critical(utils.T("store.sql.create_column_missing_driver.critical")) 455 time.Sleep(time.Second) 456 os.Exit(EXIT_CREATE_COLUMN_MISSING) 457 return false 458 } 459 } 460 461 func (ss *SqlSupplier) RemoveColumnIfExists(tableName string, columnName string) bool { 462 463 if !ss.DoesColumnExist(tableName, columnName) { 464 return false 465 } 466 467 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " DROP COLUMN " + columnName) 468 if err != nil { 469 l4g.Critical("Failed to drop column %v", err) 470 time.Sleep(time.Second) 471 os.Exit(EXIT_REMOVE_COLUMN) 472 } 473 474 return true 475 } 476 477 func (ss *SqlSupplier) RemoveTableIfExists(tableName string) bool { 478 if !ss.DoesTableExist(tableName) { 479 return false 480 } 481 482 _, err := ss.GetMaster().ExecNoTimeout("DROP TABLE " + tableName) 483 if err != nil { 484 l4g.Critical("Failed to drop table %v", err) 485 time.Sleep(time.Second) 486 os.Exit(EXIT_REMOVE_TABLE) 487 } 488 489 return true 490 } 491 492 func (ss *SqlSupplier) RenameColumnIfExists(tableName string, oldColumnName string, newColumnName string, colType string) bool { 493 if !ss.DoesColumnExist(tableName, oldColumnName) { 494 return false 495 } 496 497 var err error 498 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 499 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " CHANGE " + oldColumnName + " " + newColumnName + " " + colType) 500 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 501 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " RENAME COLUMN " + oldColumnName + " TO " + newColumnName) 502 } 503 504 if err != nil { 505 l4g.Critical(utils.T("store.sql.rename_column.critical"), err) 506 time.Sleep(time.Second) 507 os.Exit(EXIT_RENAME_COLUMN) 508 } 509 510 return true 511 } 512 513 func (ss *SqlSupplier) GetMaxLengthOfColumnIfExists(tableName string, columnName string) string { 514 if !ss.DoesColumnExist(tableName, columnName) { 515 return "" 516 } 517 518 var result string 519 var err error 520 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 521 result, err = ss.GetMaster().SelectStr("SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE table_name = '" + tableName + "' AND COLUMN_NAME = '" + columnName + "'") 522 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 523 result, err = ss.GetMaster().SelectStr("SELECT character_maximum_length FROM information_schema.columns WHERE table_name = '" + strings.ToLower(tableName) + "' AND column_name = '" + strings.ToLower(columnName) + "'") 524 } 525 526 if err != nil { 527 l4g.Critical(utils.T("store.sql.maxlength_column.critical"), err) 528 time.Sleep(time.Second) 529 os.Exit(EXIT_MAX_COLUMN) 530 } 531 532 return result 533 } 534 535 func (ss *SqlSupplier) AlterColumnTypeIfExists(tableName string, columnName string, mySqlColType string, postgresColType string) bool { 536 if !ss.DoesColumnExist(tableName, columnName) { 537 return false 538 } 539 540 var err error 541 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 542 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " MODIFY " + columnName + " " + mySqlColType) 543 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 544 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + strings.ToLower(tableName) + " ALTER COLUMN " + strings.ToLower(columnName) + " TYPE " + postgresColType) 545 } 546 547 if err != nil { 548 l4g.Critical(utils.T("store.sql.alter_column_type.critical"), err) 549 time.Sleep(time.Second) 550 os.Exit(EXIT_ALTER_COLUMN) 551 } 552 553 return true 554 } 555 556 func (ss *SqlSupplier) CreateUniqueIndexIfNotExists(indexName string, tableName string, columnName string) bool { 557 return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_DEFAULT, true) 558 } 559 560 func (ss *SqlSupplier) CreateIndexIfNotExists(indexName string, tableName string, columnName string) bool { 561 return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_DEFAULT, false) 562 } 563 564 func (ss *SqlSupplier) CreateCompositeIndexIfNotExists(indexName string, tableName string, columnNames []string) bool { 565 return ss.createIndexIfNotExists(indexName, tableName, columnNames, INDEX_TYPE_DEFAULT, false) 566 } 567 568 func (ss *SqlSupplier) CreateFullTextIndexIfNotExists(indexName string, tableName string, columnName string) bool { 569 return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_FULL_TEXT, false) 570 } 571 572 func (ss *SqlSupplier) createIndexIfNotExists(indexName string, tableName string, columnNames []string, indexType string, unique bool) bool { 573 574 uniqueStr := "" 575 if unique { 576 uniqueStr = "UNIQUE " 577 } 578 579 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 580 _, errExists := ss.GetMaster().SelectStr("SELECT $1::regclass", indexName) 581 // It should fail if the index does not exist 582 if errExists == nil { 583 return false 584 } 585 586 query := "" 587 if indexType == INDEX_TYPE_FULL_TEXT { 588 if len(columnNames) != 1 { 589 l4g.Critical("Unable to create multi column full text index") 590 os.Exit(EXIT_CREATE_INDEX_POSTGRES) 591 } 592 columnName := columnNames[0] 593 postgresColumnNames := convertMySQLFullTextColumnsToPostgres(columnName) 594 query = "CREATE INDEX " + indexName + " ON " + tableName + " USING gin(to_tsvector('english', " + postgresColumnNames + "))" 595 } else { 596 query = "CREATE " + uniqueStr + "INDEX " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")" 597 } 598 599 _, err := ss.GetMaster().ExecNoTimeout(query) 600 if err != nil { 601 l4g.Critical(utils.T("store.sql.create_index.critical"), errExists) 602 l4g.Critical(utils.T("store.sql.create_index.critical"), err) 603 time.Sleep(time.Second) 604 os.Exit(EXIT_CREATE_INDEX_POSTGRES) 605 } 606 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 607 608 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) 609 if err != nil { 610 l4g.Critical(utils.T("store.sql.check_index.critical"), err) 611 time.Sleep(time.Second) 612 os.Exit(EXIT_CREATE_INDEX_MYSQL) 613 } 614 615 if count > 0 { 616 return false 617 } 618 619 fullTextIndex := "" 620 if indexType == INDEX_TYPE_FULL_TEXT { 621 fullTextIndex = " FULLTEXT " 622 } 623 624 _, err = ss.GetMaster().ExecNoTimeout("CREATE " + uniqueStr + fullTextIndex + " INDEX " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")") 625 if err != nil { 626 l4g.Critical(utils.T("store.sql.create_index.critical"), err) 627 time.Sleep(time.Second) 628 os.Exit(EXIT_CREATE_INDEX_FULL_MYSQL) 629 } 630 } else { 631 l4g.Critical(utils.T("store.sql.create_index_missing_driver.critical")) 632 time.Sleep(time.Second) 633 os.Exit(EXIT_CREATE_INDEX_MISSING) 634 } 635 636 return true 637 } 638 639 func (ss *SqlSupplier) RemoveIndexIfExists(indexName string, tableName string) bool { 640 641 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 642 _, err := ss.GetMaster().SelectStr("SELECT $1::regclass", indexName) 643 // It should fail if the index does not exist 644 if err != nil { 645 return false 646 } 647 648 _, err = ss.GetMaster().ExecNoTimeout("DROP INDEX " + indexName) 649 if err != nil { 650 l4g.Critical(utils.T("store.sql.remove_index.critical"), err) 651 time.Sleep(time.Second) 652 os.Exit(EXIT_REMOVE_INDEX_POSTGRES) 653 } 654 655 return true 656 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 657 658 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) 659 if err != nil { 660 l4g.Critical(utils.T("store.sql.check_index.critical"), err) 661 time.Sleep(time.Second) 662 os.Exit(EXIT_REMOVE_INDEX_MYSQL) 663 } 664 665 if count <= 0 { 666 return false 667 } 668 669 _, err = ss.GetMaster().ExecNoTimeout("DROP INDEX " + indexName + " ON " + tableName) 670 if err != nil { 671 l4g.Critical(utils.T("store.sql.remove_index.critical"), err) 672 time.Sleep(time.Second) 673 os.Exit(EXIT_REMOVE_INDEX_MYSQL) 674 } 675 } else { 676 l4g.Critical(utils.T("store.sql.create_index_missing_driver.critical")) 677 time.Sleep(time.Second) 678 os.Exit(EXIT_REMOVE_INDEX_MISSING) 679 } 680 681 return true 682 } 683 684 func IsUniqueConstraintError(err error, indexName []string) bool { 685 unique := false 686 if pqErr, ok := err.(*pq.Error); ok && pqErr.Code == "23505" { 687 unique = true 688 } 689 690 if mysqlErr, ok := err.(*mysql.MySQLError); ok && mysqlErr.Number == 1062 { 691 unique = true 692 } 693 694 field := false 695 for _, contain := range indexName { 696 if strings.Contains(err.Error(), contain) { 697 field = true 698 break 699 } 700 } 701 702 return unique && field 703 } 704 705 func (ss *SqlSupplier) GetAllConns() []*gorp.DbMap { 706 all := make([]*gorp.DbMap, len(ss.replicas)+1) 707 copy(all, ss.replicas) 708 all[len(ss.replicas)] = ss.master 709 return all 710 } 711 712 func (ss *SqlSupplier) Close() { 713 l4g.Info(utils.T("store.sql.closing.info")) 714 ss.master.Db.Close() 715 for _, replica := range ss.replicas { 716 replica.Db.Close() 717 } 718 } 719 720 func (ss *SqlSupplier) Team() store.TeamStore { 721 return ss.oldStores.team 722 } 723 724 func (ss *SqlSupplier) Channel() store.ChannelStore { 725 return ss.oldStores.channel 726 } 727 728 func (ss *SqlSupplier) Post() store.PostStore { 729 return ss.oldStores.post 730 } 731 732 func (ss *SqlSupplier) User() store.UserStore { 733 return ss.oldStores.user 734 } 735 736 func (ss *SqlSupplier) Session() store.SessionStore { 737 return ss.oldStores.session 738 } 739 740 func (ss *SqlSupplier) Audit() store.AuditStore { 741 return ss.oldStores.audit 742 } 743 744 func (ss *SqlSupplier) ClusterDiscovery() store.ClusterDiscoveryStore { 745 return ss.oldStores.cluster 746 } 747 748 func (ss *SqlSupplier) Compliance() store.ComplianceStore { 749 return ss.oldStores.compliance 750 } 751 752 func (ss *SqlSupplier) OAuth() store.OAuthStore { 753 return ss.oldStores.oauth 754 } 755 756 func (ss *SqlSupplier) System() store.SystemStore { 757 return ss.oldStores.system 758 } 759 760 func (ss *SqlSupplier) Webhook() store.WebhookStore { 761 return ss.oldStores.webhook 762 } 763 764 func (ss *SqlSupplier) Command() store.CommandStore { 765 return ss.oldStores.command 766 } 767 768 func (ss *SqlSupplier) CommandWebhook() store.CommandWebhookStore { 769 return ss.oldStores.commandWebhook 770 } 771 772 func (ss *SqlSupplier) Preference() store.PreferenceStore { 773 return ss.oldStores.preference 774 } 775 776 func (ss *SqlSupplier) License() store.LicenseStore { 777 return ss.oldStores.license 778 } 779 780 func (ss *SqlSupplier) Token() store.TokenStore { 781 return ss.oldStores.token 782 } 783 784 func (ss *SqlSupplier) Emoji() store.EmojiStore { 785 return ss.oldStores.emoji 786 } 787 788 func (ss *SqlSupplier) Status() store.StatusStore { 789 return ss.oldStores.status 790 } 791 792 func (ss *SqlSupplier) FileInfo() store.FileInfoStore { 793 return ss.oldStores.fileInfo 794 } 795 796 func (ss *SqlSupplier) Reaction() store.ReactionStore { 797 return ss.oldStores.reaction 798 } 799 800 func (ss *SqlSupplier) Job() store.JobStore { 801 return ss.oldStores.job 802 } 803 804 func (ss *SqlSupplier) UserAccessToken() store.UserAccessTokenStore { 805 return ss.oldStores.userAccessToken 806 } 807 808 func (ss *SqlSupplier) ChannelMemberHistory() store.ChannelMemberHistoryStore { 809 return ss.oldStores.channelMemberHistory 810 } 811 812 func (ss *SqlSupplier) Plugin() store.PluginStore { 813 return ss.oldStores.plugin 814 } 815 816 func (ss *SqlSupplier) Role() store.RoleStore { 817 return ss.oldStores.role 818 } 819 820 func (ss *SqlSupplier) DropAllTables() { 821 ss.master.TruncateTables() 822 } 823 824 type mattermConverter struct{} 825 826 func (me mattermConverter) ToDb(val interface{}) (interface{}, error) { 827 828 switch t := val.(type) { 829 case model.StringMap: 830 return model.MapToJson(t), nil 831 case map[string]string: 832 return model.MapToJson(model.StringMap(t)), nil 833 case model.StringArray: 834 return model.ArrayToJson(t), nil 835 case model.StringInterface: 836 return model.StringInterfaceToJson(t), nil 837 case map[string]interface{}: 838 return model.StringInterfaceToJson(model.StringInterface(t)), nil 839 } 840 841 return val, nil 842 } 843 844 func (me mattermConverter) FromDb(target interface{}) (gorp.CustomScanner, bool) { 845 switch target.(type) { 846 case *model.StringMap: 847 binder := func(holder, target interface{}) error { 848 s, ok := holder.(*string) 849 if !ok { 850 return errors.New(utils.T("store.sql.convert_string_map")) 851 } 852 b := []byte(*s) 853 return json.Unmarshal(b, target) 854 } 855 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 856 case *map[string]string: 857 binder := func(holder, target interface{}) error { 858 s, ok := holder.(*string) 859 if !ok { 860 return errors.New(utils.T("store.sql.convert_string_map")) 861 } 862 b := []byte(*s) 863 return json.Unmarshal(b, target) 864 } 865 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 866 case *model.StringArray: 867 binder := func(holder, target interface{}) error { 868 s, ok := holder.(*string) 869 if !ok { 870 return errors.New(utils.T("store.sql.convert_string_array")) 871 } 872 b := []byte(*s) 873 return json.Unmarshal(b, target) 874 } 875 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 876 case *model.StringInterface: 877 binder := func(holder, target interface{}) error { 878 s, ok := holder.(*string) 879 if !ok { 880 return errors.New(utils.T("store.sql.convert_string_interface")) 881 } 882 b := []byte(*s) 883 return json.Unmarshal(b, target) 884 } 885 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 886 case *map[string]interface{}: 887 binder := func(holder, target interface{}) error { 888 s, ok := holder.(*string) 889 if !ok { 890 return errors.New(utils.T("store.sql.convert_string_interface")) 891 } 892 b := []byte(*s) 893 return json.Unmarshal(b, target) 894 } 895 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 896 } 897 898 return gorp.CustomScanner{}, false 899 } 900 901 func convertMySQLFullTextColumnsToPostgres(columnNames string) string { 902 columns := strings.Split(columnNames, ", ") 903 concatenatedColumnNames := "" 904 for i, c := range columns { 905 concatenatedColumnNames += c 906 if i < len(columns)-1 { 907 concatenatedColumnNames += " || ' ' || " 908 } 909 } 910 911 return concatenatedColumnNames 912 }