github.com/jfrerich/mattermost-server@v5.8.0-rc2+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 "github.com/dyatlov/go-opengraph/opengraph" 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/mlog" 24 "github.com/mattermost/mattermost-server/model" 25 "github.com/mattermost/mattermost-server/store" 26 "github.com/mattermost/mattermost-server/utils" 27 ) 28 29 const ( 30 INDEX_TYPE_FULL_TEXT = "full_text" 31 INDEX_TYPE_DEFAULT = "default" 32 DB_PING_ATTEMPTS = 18 33 DB_PING_TIMEOUT_SECS = 10 34 ) 35 36 const ( 37 EXIT_GENERIC_FAILURE = 1 38 EXIT_CREATE_TABLE = 100 39 EXIT_DB_OPEN = 101 40 EXIT_PING = 102 41 EXIT_NO_DRIVER = 103 42 EXIT_TABLE_EXISTS = 104 43 EXIT_TABLE_EXISTS_MYSQL = 105 44 EXIT_COLUMN_EXISTS = 106 45 EXIT_DOES_COLUMN_EXISTS_POSTGRES = 107 46 EXIT_DOES_COLUMN_EXISTS_MYSQL = 108 47 EXIT_DOES_COLUMN_EXISTS_MISSING = 109 48 EXIT_CREATE_COLUMN_POSTGRES = 110 49 EXIT_CREATE_COLUMN_MYSQL = 111 50 EXIT_CREATE_COLUMN_MISSING = 112 51 EXIT_REMOVE_COLUMN = 113 52 EXIT_RENAME_COLUMN = 114 53 EXIT_MAX_COLUMN = 115 54 EXIT_ALTER_COLUMN = 116 55 EXIT_CREATE_INDEX_POSTGRES = 117 56 EXIT_CREATE_INDEX_MYSQL = 118 57 EXIT_CREATE_INDEX_FULL_MYSQL = 119 58 EXIT_CREATE_INDEX_MISSING = 120 59 EXIT_REMOVE_INDEX_POSTGRES = 121 60 EXIT_REMOVE_INDEX_MYSQL = 122 61 EXIT_REMOVE_INDEX_MISSING = 123 62 EXIT_REMOVE_TABLE = 134 63 EXIT_CREATE_INDEX_SQLITE = 135 64 EXIT_REMOVE_INDEX_SQLITE = 136 65 EXIT_TABLE_EXISTS_SQLITE = 137 66 EXIT_DOES_COLUMN_EXISTS_SQLITE = 138 67 ) 68 69 type SqlSupplierOldStores struct { 70 team store.TeamStore 71 channel store.ChannelStore 72 post store.PostStore 73 user store.UserStore 74 audit store.AuditStore 75 cluster store.ClusterDiscoveryStore 76 compliance store.ComplianceStore 77 session store.SessionStore 78 oauth store.OAuthStore 79 system store.SystemStore 80 webhook store.WebhookStore 81 command store.CommandStore 82 commandWebhook store.CommandWebhookStore 83 preference store.PreferenceStore 84 license store.LicenseStore 85 token store.TokenStore 86 emoji store.EmojiStore 87 status store.StatusStore 88 fileInfo store.FileInfoStore 89 reaction store.ReactionStore 90 job store.JobStore 91 userAccessToken store.UserAccessTokenStore 92 plugin store.PluginStore 93 channelMemberHistory store.ChannelMemberHistoryStore 94 role store.RoleStore 95 scheme store.SchemeStore 96 TermsOfService store.TermsOfServiceStore 97 group store.GroupStore 98 UserTermsOfService store.UserTermsOfServiceStore 99 linkMetadata store.LinkMetadataStore 100 } 101 102 type SqlSupplier struct { 103 // rrCounter and srCounter should be kept first. 104 // See https://github.com/mattermost/mattermost-server/pull/7281 105 rrCounter int64 106 srCounter int64 107 next store.LayeredStoreSupplier 108 master *gorp.DbMap 109 replicas []*gorp.DbMap 110 searchReplicas []*gorp.DbMap 111 oldStores SqlSupplierOldStores 112 settings *model.SqlSettings 113 lockedToMaster bool 114 } 115 116 func NewSqlSupplier(settings model.SqlSettings, metrics einterfaces.MetricsInterface) *SqlSupplier { 117 supplier := &SqlSupplier{ 118 rrCounter: 0, 119 srCounter: 0, 120 settings: &settings, 121 } 122 123 supplier.initConnection() 124 125 supplier.oldStores.team = NewSqlTeamStore(supplier) 126 supplier.oldStores.channel = NewSqlChannelStore(supplier, metrics) 127 supplier.oldStores.post = NewSqlPostStore(supplier, metrics) 128 supplier.oldStores.user = NewSqlUserStore(supplier, metrics) 129 supplier.oldStores.audit = NewSqlAuditStore(supplier) 130 supplier.oldStores.cluster = NewSqlClusterDiscoveryStore(supplier) 131 supplier.oldStores.compliance = NewSqlComplianceStore(supplier) 132 supplier.oldStores.session = NewSqlSessionStore(supplier) 133 supplier.oldStores.oauth = NewSqlOAuthStore(supplier) 134 supplier.oldStores.system = NewSqlSystemStore(supplier) 135 supplier.oldStores.webhook = NewSqlWebhookStore(supplier, metrics) 136 supplier.oldStores.command = NewSqlCommandStore(supplier) 137 supplier.oldStores.commandWebhook = NewSqlCommandWebhookStore(supplier) 138 supplier.oldStores.preference = NewSqlPreferenceStore(supplier) 139 supplier.oldStores.license = NewSqlLicenseStore(supplier) 140 supplier.oldStores.token = NewSqlTokenStore(supplier) 141 supplier.oldStores.emoji = NewSqlEmojiStore(supplier, metrics) 142 supplier.oldStores.status = NewSqlStatusStore(supplier) 143 supplier.oldStores.fileInfo = NewSqlFileInfoStore(supplier, metrics) 144 supplier.oldStores.job = NewSqlJobStore(supplier) 145 supplier.oldStores.userAccessToken = NewSqlUserAccessTokenStore(supplier) 146 supplier.oldStores.channelMemberHistory = NewSqlChannelMemberHistoryStore(supplier) 147 supplier.oldStores.plugin = NewSqlPluginStore(supplier) 148 supplier.oldStores.TermsOfService = NewSqlTermsOfServiceStore(supplier, metrics) 149 supplier.oldStores.UserTermsOfService = NewSqlUserTermsOfServiceStore(supplier) 150 supplier.oldStores.linkMetadata = NewSqlLinkMetadataStore(supplier) 151 152 initSqlSupplierReactions(supplier) 153 initSqlSupplierRoles(supplier) 154 initSqlSupplierSchemes(supplier) 155 initSqlSupplierGroups(supplier) 156 157 err := supplier.GetMaster().CreateTablesIfNotExists() 158 if err != nil { 159 mlog.Critical(fmt.Sprintf("Error creating database tables: %v", err)) 160 time.Sleep(time.Second) 161 os.Exit(EXIT_CREATE_TABLE) 162 } 163 164 UpgradeDatabase(supplier) 165 166 supplier.oldStores.team.(*SqlTeamStore).CreateIndexesIfNotExists() 167 supplier.oldStores.channel.(*SqlChannelStore).CreateIndexesIfNotExists() 168 supplier.oldStores.post.(*SqlPostStore).CreateIndexesIfNotExists() 169 supplier.oldStores.user.(*SqlUserStore).CreateIndexesIfNotExists() 170 supplier.oldStores.audit.(*SqlAuditStore).CreateIndexesIfNotExists() 171 supplier.oldStores.compliance.(*SqlComplianceStore).CreateIndexesIfNotExists() 172 supplier.oldStores.session.(*SqlSessionStore).CreateIndexesIfNotExists() 173 supplier.oldStores.oauth.(*SqlOAuthStore).CreateIndexesIfNotExists() 174 supplier.oldStores.system.(*SqlSystemStore).CreateIndexesIfNotExists() 175 supplier.oldStores.webhook.(*SqlWebhookStore).CreateIndexesIfNotExists() 176 supplier.oldStores.command.(*SqlCommandStore).CreateIndexesIfNotExists() 177 supplier.oldStores.commandWebhook.(*SqlCommandWebhookStore).CreateIndexesIfNotExists() 178 supplier.oldStores.preference.(*SqlPreferenceStore).CreateIndexesIfNotExists() 179 supplier.oldStores.license.(*SqlLicenseStore).CreateIndexesIfNotExists() 180 supplier.oldStores.token.(*SqlTokenStore).CreateIndexesIfNotExists() 181 supplier.oldStores.emoji.(*SqlEmojiStore).CreateIndexesIfNotExists() 182 supplier.oldStores.status.(*SqlStatusStore).CreateIndexesIfNotExists() 183 supplier.oldStores.fileInfo.(*SqlFileInfoStore).CreateIndexesIfNotExists() 184 supplier.oldStores.job.(*SqlJobStore).CreateIndexesIfNotExists() 185 supplier.oldStores.userAccessToken.(*SqlUserAccessTokenStore).CreateIndexesIfNotExists() 186 supplier.oldStores.plugin.(*SqlPluginStore).CreateIndexesIfNotExists() 187 supplier.oldStores.TermsOfService.(SqlTermsOfServiceStore).CreateIndexesIfNotExists() 188 supplier.oldStores.UserTermsOfService.(SqlUserTermsOfServiceStore).CreateIndexesIfNotExists() 189 supplier.oldStores.linkMetadata.(*SqlLinkMetadataStore).CreateIndexesIfNotExists() 190 191 supplier.CreateIndexesIfNotExistsGroups() 192 193 supplier.oldStores.preference.(*SqlPreferenceStore).DeleteUnusedFeatures() 194 195 return supplier 196 } 197 198 func (s *SqlSupplier) SetChainNext(next store.LayeredStoreSupplier) { 199 s.next = next 200 } 201 202 func (s *SqlSupplier) Next() store.LayeredStoreSupplier { 203 return s.next 204 } 205 206 func setupConnection(con_type string, dataSource string, settings *model.SqlSettings) *gorp.DbMap { 207 db, err := dbsql.Open(*settings.DriverName, dataSource) 208 if err != nil { 209 mlog.Critical(fmt.Sprintf("Failed to open SQL connection to err:%v", err.Error())) 210 time.Sleep(time.Second) 211 os.Exit(EXIT_DB_OPEN) 212 } 213 214 for i := 0; i < DB_PING_ATTEMPTS; i++ { 215 mlog.Info(fmt.Sprintf("Pinging SQL %v database", con_type)) 216 ctx, cancel := context.WithTimeout(context.Background(), DB_PING_TIMEOUT_SECS*time.Second) 217 defer cancel() 218 err = db.PingContext(ctx) 219 if err == nil { 220 break 221 } else { 222 if i == DB_PING_ATTEMPTS-1 { 223 mlog.Critical(fmt.Sprintf("Failed to ping DB, server will exit err=%v", err)) 224 time.Sleep(time.Second) 225 os.Exit(EXIT_PING) 226 } else { 227 mlog.Error(fmt.Sprintf("Failed to ping DB retrying in %v seconds err=%v", DB_PING_TIMEOUT_SECS, err)) 228 time.Sleep(DB_PING_TIMEOUT_SECS * time.Second) 229 } 230 } 231 } 232 233 db.SetMaxIdleConns(*settings.MaxIdleConns) 234 db.SetMaxOpenConns(*settings.MaxOpenConns) 235 db.SetConnMaxLifetime(time.Duration(*settings.ConnMaxLifetimeMilliseconds) * time.Millisecond) 236 237 var dbmap *gorp.DbMap 238 239 connectionTimeout := time.Duration(*settings.QueryTimeout) * time.Second 240 241 if *settings.DriverName == model.DATABASE_DRIVER_SQLITE { 242 dbmap = &gorp.DbMap{Db: db, TypeConverter: mattermConverter{}, Dialect: gorp.SqliteDialect{}, QueryTimeout: connectionTimeout} 243 } else if *settings.DriverName == model.DATABASE_DRIVER_MYSQL { 244 dbmap = &gorp.DbMap{Db: db, TypeConverter: mattermConverter{}, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8MB4"}, QueryTimeout: connectionTimeout} 245 } else if *settings.DriverName == model.DATABASE_DRIVER_POSTGRES { 246 dbmap = &gorp.DbMap{Db: db, TypeConverter: mattermConverter{}, Dialect: gorp.PostgresDialect{}, QueryTimeout: connectionTimeout} 247 } else { 248 mlog.Critical("Failed to create dialect specific driver") 249 time.Sleep(time.Second) 250 os.Exit(EXIT_NO_DRIVER) 251 } 252 253 if settings.Trace { 254 dbmap.TraceOn("", sqltrace.New(os.Stdout, "sql-trace:", sqltrace.Lmicroseconds)) 255 } 256 257 return dbmap 258 } 259 260 func (s *SqlSupplier) initConnection() { 261 s.master = setupConnection("master", *s.settings.DataSource, s.settings) 262 263 if len(s.settings.DataSourceReplicas) > 0 { 264 s.replicas = make([]*gorp.DbMap, len(s.settings.DataSourceReplicas)) 265 for i, replica := range s.settings.DataSourceReplicas { 266 s.replicas[i] = setupConnection(fmt.Sprintf("replica-%v", i), replica, s.settings) 267 } 268 } 269 270 if len(s.settings.DataSourceSearchReplicas) > 0 { 271 s.searchReplicas = make([]*gorp.DbMap, len(s.settings.DataSourceSearchReplicas)) 272 for i, replica := range s.settings.DataSourceSearchReplicas { 273 s.searchReplicas[i] = setupConnection(fmt.Sprintf("search-replica-%v", i), replica, s.settings) 274 } 275 } 276 } 277 278 func (ss *SqlSupplier) DriverName() string { 279 return *ss.settings.DriverName 280 } 281 282 func (ss *SqlSupplier) GetCurrentSchemaVersion() string { 283 version, _ := ss.GetMaster().SelectStr("SELECT Value FROM Systems WHERE Name='Version'") 284 return version 285 } 286 287 func (ss *SqlSupplier) GetMaster() *gorp.DbMap { 288 return ss.master 289 } 290 291 func (ss *SqlSupplier) GetSearchReplica() *gorp.DbMap { 292 if len(ss.settings.DataSourceSearchReplicas) == 0 { 293 return ss.GetReplica() 294 } 295 296 rrNum := atomic.AddInt64(&ss.srCounter, 1) % int64(len(ss.searchReplicas)) 297 return ss.searchReplicas[rrNum] 298 } 299 300 func (ss *SqlSupplier) GetReplica() *gorp.DbMap { 301 if len(ss.settings.DataSourceReplicas) == 0 || ss.lockedToMaster { 302 return ss.GetMaster() 303 } 304 305 rrNum := atomic.AddInt64(&ss.rrCounter, 1) % int64(len(ss.replicas)) 306 return ss.replicas[rrNum] 307 } 308 309 func (ss *SqlSupplier) TotalMasterDbConnections() int { 310 return ss.GetMaster().Db.Stats().OpenConnections 311 } 312 313 func (ss *SqlSupplier) TotalReadDbConnections() int { 314 if len(ss.settings.DataSourceReplicas) == 0 { 315 return 0 316 } 317 318 count := 0 319 for _, db := range ss.replicas { 320 count = count + db.Db.Stats().OpenConnections 321 } 322 323 return count 324 } 325 326 func (ss *SqlSupplier) TotalSearchDbConnections() int { 327 if len(ss.settings.DataSourceSearchReplicas) == 0 { 328 return 0 329 } 330 331 count := 0 332 for _, db := range ss.searchReplicas { 333 count = count + db.Db.Stats().OpenConnections 334 } 335 336 return count 337 } 338 339 func (ss *SqlSupplier) MarkSystemRanUnitTests() { 340 if result := <-ss.System().Get(); result.Err == nil { 341 props := result.Data.(model.StringMap) 342 unitTests := props[model.SYSTEM_RAN_UNIT_TESTS] 343 if len(unitTests) == 0 { 344 systemTests := &model.System{Name: model.SYSTEM_RAN_UNIT_TESTS, Value: "1"} 345 <-ss.System().Save(systemTests) 346 } 347 } 348 } 349 350 func (ss *SqlSupplier) DoesTableExist(tableName string) bool { 351 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 352 count, err := ss.GetMaster().SelectInt( 353 `SELECT count(relname) FROM pg_class WHERE relname=$1`, 354 strings.ToLower(tableName), 355 ) 356 357 if err != nil { 358 mlog.Critical(fmt.Sprintf("Failed to check if table exists %v", err)) 359 time.Sleep(time.Second) 360 os.Exit(EXIT_TABLE_EXISTS) 361 } 362 363 return count > 0 364 365 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 366 367 count, err := ss.GetMaster().SelectInt( 368 `SELECT 369 COUNT(0) AS table_exists 370 FROM 371 information_schema.TABLES 372 WHERE 373 TABLE_SCHEMA = DATABASE() 374 AND TABLE_NAME = ? 375 `, 376 tableName, 377 ) 378 379 if err != nil { 380 mlog.Critical(fmt.Sprintf("Failed to check if table exists %v", err)) 381 time.Sleep(time.Second) 382 os.Exit(EXIT_TABLE_EXISTS_MYSQL) 383 } 384 385 return count > 0 386 387 } else if ss.DriverName() == model.DATABASE_DRIVER_SQLITE { 388 count, err := ss.GetMaster().SelectInt( 389 `SELECT name FROM sqlite_master WHERE type='table' AND name=?`, 390 tableName, 391 ) 392 393 if err != nil { 394 mlog.Critical(fmt.Sprintf("Failed to check if table exists %v", err)) 395 time.Sleep(time.Second) 396 os.Exit(EXIT_TABLE_EXISTS_SQLITE) 397 } 398 399 return count > 0 400 401 } else { 402 mlog.Critical("Failed to check if column exists because of missing driver") 403 time.Sleep(time.Second) 404 os.Exit(EXIT_COLUMN_EXISTS) 405 return false 406 } 407 } 408 409 func (ss *SqlSupplier) DoesColumnExist(tableName string, columnName string) bool { 410 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 411 count, err := ss.GetMaster().SelectInt( 412 `SELECT COUNT(0) 413 FROM pg_attribute 414 WHERE attrelid = $1::regclass 415 AND attname = $2 416 AND NOT attisdropped`, 417 strings.ToLower(tableName), 418 strings.ToLower(columnName), 419 ) 420 421 if err != nil { 422 if err.Error() == "pq: relation \""+strings.ToLower(tableName)+"\" does not exist" { 423 return false 424 } 425 426 mlog.Critical(fmt.Sprintf("Failed to check if column exists %v", err)) 427 time.Sleep(time.Second) 428 os.Exit(EXIT_DOES_COLUMN_EXISTS_POSTGRES) 429 } 430 431 return count > 0 432 433 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 434 435 count, err := ss.GetMaster().SelectInt( 436 `SELECT 437 COUNT(0) AS column_exists 438 FROM 439 information_schema.COLUMNS 440 WHERE 441 TABLE_SCHEMA = DATABASE() 442 AND TABLE_NAME = ? 443 AND COLUMN_NAME = ?`, 444 tableName, 445 columnName, 446 ) 447 448 if err != nil { 449 mlog.Critical(fmt.Sprintf("Failed to check if column exists %v", err)) 450 time.Sleep(time.Second) 451 os.Exit(EXIT_DOES_COLUMN_EXISTS_MYSQL) 452 } 453 454 return count > 0 455 456 } else if ss.DriverName() == model.DATABASE_DRIVER_SQLITE { 457 count, err := ss.GetMaster().SelectInt( 458 `SELECT COUNT(*) FROM pragma_table_info(?) WHERE name=?`, 459 tableName, 460 columnName, 461 ) 462 463 if err != nil { 464 mlog.Critical(fmt.Sprintf("Failed to check if column exists %v", err)) 465 time.Sleep(time.Second) 466 os.Exit(EXIT_DOES_COLUMN_EXISTS_SQLITE) 467 } 468 469 return count > 0 470 471 } else { 472 mlog.Critical("Failed to check if column exists because of missing driver") 473 time.Sleep(time.Second) 474 os.Exit(EXIT_DOES_COLUMN_EXISTS_MISSING) 475 return false 476 } 477 } 478 479 func (ss *SqlSupplier) DoesTriggerExist(triggerName string) bool { 480 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 481 count, err := ss.GetMaster().SelectInt(` 482 SELECT 483 COUNT(0) 484 FROM 485 pg_trigger 486 WHERE 487 tgname = $1 488 `, triggerName) 489 490 if err != nil { 491 mlog.Critical(fmt.Sprintf("Failed to check if trigger exists %v", err)) 492 time.Sleep(time.Second) 493 os.Exit(EXIT_GENERIC_FAILURE) 494 } 495 496 return count > 0 497 498 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 499 count, err := ss.GetMaster().SelectInt(` 500 SELECT 501 COUNT(0) 502 FROM 503 information_schema.triggers 504 WHERE 505 trigger_schema = DATABASE() 506 AND trigger_name = ? 507 `, triggerName) 508 509 if err != nil { 510 mlog.Critical(fmt.Sprintf("Failed to check if trigger exists %v", err)) 511 time.Sleep(time.Second) 512 os.Exit(EXIT_GENERIC_FAILURE) 513 } 514 515 return count > 0 516 517 } else { 518 mlog.Critical("Failed to check if column exists because of missing driver") 519 time.Sleep(time.Second) 520 os.Exit(EXIT_GENERIC_FAILURE) 521 return false 522 } 523 } 524 525 func (ss *SqlSupplier) CreateColumnIfNotExists(tableName string, columnName string, mySqlColType string, postgresColType string, defaultValue string) bool { 526 527 if ss.DoesColumnExist(tableName, columnName) { 528 return false 529 } 530 531 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 532 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ADD " + columnName + " " + postgresColType + " DEFAULT '" + defaultValue + "'") 533 if err != nil { 534 mlog.Critical(fmt.Sprintf("Failed to create column %v", err)) 535 time.Sleep(time.Second) 536 os.Exit(EXIT_CREATE_COLUMN_POSTGRES) 537 } 538 539 return true 540 541 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 542 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ADD " + columnName + " " + mySqlColType + " DEFAULT '" + defaultValue + "'") 543 if err != nil { 544 mlog.Critical(fmt.Sprintf("Failed to create column %v", err)) 545 time.Sleep(time.Second) 546 os.Exit(EXIT_CREATE_COLUMN_MYSQL) 547 } 548 549 return true 550 551 } else { 552 mlog.Critical("Failed to create column because of missing driver") 553 time.Sleep(time.Second) 554 os.Exit(EXIT_CREATE_COLUMN_MISSING) 555 return false 556 } 557 } 558 559 func (ss *SqlSupplier) CreateColumnIfNotExistsNoDefault(tableName string, columnName string, mySqlColType string, postgresColType string) bool { 560 561 if ss.DoesColumnExist(tableName, columnName) { 562 return false 563 } 564 565 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 566 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ADD " + columnName + " " + postgresColType) 567 if err != nil { 568 mlog.Critical(fmt.Sprintf("Failed to create column %v", err)) 569 time.Sleep(time.Second) 570 os.Exit(EXIT_CREATE_COLUMN_POSTGRES) 571 } 572 573 return true 574 575 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 576 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ADD " + columnName + " " + mySqlColType) 577 if err != nil { 578 mlog.Critical(fmt.Sprintf("Failed to create column %v", err)) 579 time.Sleep(time.Second) 580 os.Exit(EXIT_CREATE_COLUMN_MYSQL) 581 } 582 583 return true 584 585 } else { 586 mlog.Critical("Failed to create column because of missing driver") 587 time.Sleep(time.Second) 588 os.Exit(EXIT_CREATE_COLUMN_MISSING) 589 return false 590 } 591 } 592 593 func (ss *SqlSupplier) RemoveColumnIfExists(tableName string, columnName string) bool { 594 595 if !ss.DoesColumnExist(tableName, columnName) { 596 return false 597 } 598 599 _, err := ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " DROP COLUMN " + columnName) 600 if err != nil { 601 mlog.Critical(fmt.Sprintf("Failed to drop column %v", err)) 602 time.Sleep(time.Second) 603 os.Exit(EXIT_REMOVE_COLUMN) 604 } 605 606 return true 607 } 608 609 func (ss *SqlSupplier) RemoveTableIfExists(tableName string) bool { 610 if !ss.DoesTableExist(tableName) { 611 return false 612 } 613 614 _, err := ss.GetMaster().ExecNoTimeout("DROP TABLE " + tableName) 615 if err != nil { 616 mlog.Critical(fmt.Sprintf("Failed to drop table %v", err)) 617 time.Sleep(time.Second) 618 os.Exit(EXIT_REMOVE_TABLE) 619 } 620 621 return true 622 } 623 624 func (ss *SqlSupplier) RenameColumnIfExists(tableName string, oldColumnName string, newColumnName string, colType string) bool { 625 if !ss.DoesColumnExist(tableName, oldColumnName) { 626 return false 627 } 628 629 var err error 630 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 631 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " CHANGE " + oldColumnName + " " + newColumnName + " " + colType) 632 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 633 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " RENAME COLUMN " + oldColumnName + " TO " + newColumnName) 634 } 635 636 if err != nil { 637 mlog.Critical(fmt.Sprintf("Failed to rename column %v", err)) 638 time.Sleep(time.Second) 639 os.Exit(EXIT_RENAME_COLUMN) 640 } 641 642 return true 643 } 644 645 func (ss *SqlSupplier) GetMaxLengthOfColumnIfExists(tableName string, columnName string) string { 646 if !ss.DoesColumnExist(tableName, columnName) { 647 return "" 648 } 649 650 var result string 651 var err error 652 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 653 result, err = ss.GetMaster().SelectStr("SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE table_name = '" + tableName + "' AND COLUMN_NAME = '" + columnName + "'") 654 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 655 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) + "'") 656 } 657 658 if err != nil { 659 mlog.Critical(fmt.Sprintf("Failed to get max length of column %v", err)) 660 time.Sleep(time.Second) 661 os.Exit(EXIT_MAX_COLUMN) 662 } 663 664 return result 665 } 666 667 func (ss *SqlSupplier) AlterColumnTypeIfExists(tableName string, columnName string, mySqlColType string, postgresColType string) bool { 668 if !ss.DoesColumnExist(tableName, columnName) { 669 return false 670 } 671 672 var err error 673 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 674 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " MODIFY " + columnName + " " + mySqlColType) 675 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 676 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + strings.ToLower(tableName) + " ALTER COLUMN " + strings.ToLower(columnName) + " TYPE " + postgresColType) 677 } 678 679 if err != nil { 680 mlog.Critical(fmt.Sprintf("Failed to alter column type %v", err)) 681 time.Sleep(time.Second) 682 os.Exit(EXIT_ALTER_COLUMN) 683 } 684 685 return true 686 } 687 688 func (ss *SqlSupplier) AlterColumnDefaultIfExists(tableName string, columnName string, mySqlColDefault *string, postgresColDefault *string) bool { 689 if !ss.DoesColumnExist(tableName, columnName) { 690 return false 691 } 692 693 var defaultValue = "" 694 if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 695 // Some column types in MySQL cannot have defaults, so don't try to configure anything. 696 if mySqlColDefault == nil { 697 return true 698 } 699 700 defaultValue = *mySqlColDefault 701 } else if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 702 // Postgres doesn't have the same limitation, but preserve the interface. 703 if postgresColDefault == nil { 704 return true 705 } 706 707 tableName = strings.ToLower(tableName) 708 columnName = strings.ToLower(columnName) 709 defaultValue = *postgresColDefault 710 } else if ss.DriverName() == model.DATABASE_DRIVER_SQLITE { 711 // SQLite doesn't support altering column defaults, but we don't use this in 712 // production so just ignore. 713 return true 714 } else { 715 mlog.Critical("Failed to alter column default because of missing driver") 716 time.Sleep(time.Second) 717 os.Exit(EXIT_GENERIC_FAILURE) 718 return false 719 } 720 721 var err error 722 if defaultValue == "" { 723 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ALTER COLUMN " + columnName + " DROP DEFAULT") 724 } else { 725 _, err = ss.GetMaster().ExecNoTimeout("ALTER TABLE " + tableName + " ALTER COLUMN " + columnName + " SET DEFAULT " + defaultValue) 726 } 727 728 if err != nil { 729 mlog.Critical(fmt.Sprintf("Failed to alter column %s.%s default %s: %v", tableName, columnName, defaultValue, err)) 730 time.Sleep(time.Second) 731 os.Exit(EXIT_GENERIC_FAILURE) 732 return false 733 } 734 735 return true 736 } 737 738 func (ss *SqlSupplier) CreateUniqueIndexIfNotExists(indexName string, tableName string, columnName string) bool { 739 return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_DEFAULT, true) 740 } 741 742 func (ss *SqlSupplier) CreateIndexIfNotExists(indexName string, tableName string, columnName string) bool { 743 return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_DEFAULT, false) 744 } 745 746 func (ss *SqlSupplier) CreateCompositeIndexIfNotExists(indexName string, tableName string, columnNames []string) bool { 747 return ss.createIndexIfNotExists(indexName, tableName, columnNames, INDEX_TYPE_DEFAULT, false) 748 } 749 750 func (ss *SqlSupplier) CreateFullTextIndexIfNotExists(indexName string, tableName string, columnName string) bool { 751 return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_FULL_TEXT, false) 752 } 753 754 func (ss *SqlSupplier) createIndexIfNotExists(indexName string, tableName string, columnNames []string, indexType string, unique bool) bool { 755 756 uniqueStr := "" 757 if unique { 758 uniqueStr = "UNIQUE " 759 } 760 761 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 762 _, errExists := ss.GetMaster().SelectStr("SELECT $1::regclass", indexName) 763 // It should fail if the index does not exist 764 if errExists == nil { 765 return false 766 } 767 768 query := "" 769 if indexType == INDEX_TYPE_FULL_TEXT { 770 if len(columnNames) != 1 { 771 mlog.Critical("Unable to create multi column full text index") 772 os.Exit(EXIT_CREATE_INDEX_POSTGRES) 773 } 774 columnName := columnNames[0] 775 postgresColumnNames := convertMySQLFullTextColumnsToPostgres(columnName) 776 query = "CREATE INDEX " + indexName + " ON " + tableName + " USING gin(to_tsvector('english', " + postgresColumnNames + "))" 777 } else { 778 query = "CREATE " + uniqueStr + "INDEX " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")" 779 } 780 781 _, err := ss.GetMaster().ExecNoTimeout(query) 782 if err != nil { 783 mlog.Critical(fmt.Sprintf("Failed to create index %v, %v", errExists, err)) 784 time.Sleep(time.Second) 785 os.Exit(EXIT_CREATE_INDEX_POSTGRES) 786 } 787 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 788 789 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) 790 if err != nil { 791 mlog.Critical(fmt.Sprintf("Failed to check index %v", err)) 792 time.Sleep(time.Second) 793 os.Exit(EXIT_CREATE_INDEX_MYSQL) 794 } 795 796 if count > 0 { 797 return false 798 } 799 800 fullTextIndex := "" 801 if indexType == INDEX_TYPE_FULL_TEXT { 802 fullTextIndex = " FULLTEXT " 803 } 804 805 _, err = ss.GetMaster().ExecNoTimeout("CREATE " + uniqueStr + fullTextIndex + " INDEX " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")") 806 if err != nil { 807 mlog.Critical(fmt.Sprintf("Failed to create index %v", err)) 808 time.Sleep(time.Second) 809 os.Exit(EXIT_CREATE_INDEX_FULL_MYSQL) 810 } 811 } else if ss.DriverName() == model.DATABASE_DRIVER_SQLITE { 812 _, err := ss.GetMaster().ExecNoTimeout("CREATE INDEX IF NOT EXISTS " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")") 813 if err != nil { 814 mlog.Critical(fmt.Sprintf("Failed to create index %v", err)) 815 time.Sleep(time.Second) 816 os.Exit(EXIT_CREATE_INDEX_SQLITE) 817 } 818 } else { 819 mlog.Critical("Failed to create index because of missing driver") 820 time.Sleep(time.Second) 821 os.Exit(EXIT_CREATE_INDEX_MISSING) 822 } 823 824 return true 825 } 826 827 func (ss *SqlSupplier) RemoveIndexIfExists(indexName string, tableName string) bool { 828 829 if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES { 830 _, err := ss.GetMaster().SelectStr("SELECT $1::regclass", indexName) 831 // It should fail if the index does not exist 832 if err != nil { 833 return false 834 } 835 836 _, err = ss.GetMaster().ExecNoTimeout("DROP INDEX " + indexName) 837 if err != nil { 838 mlog.Critical(fmt.Sprintf("Failed to remove index %v", err)) 839 time.Sleep(time.Second) 840 os.Exit(EXIT_REMOVE_INDEX_POSTGRES) 841 } 842 843 return true 844 } else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL { 845 846 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) 847 if err != nil { 848 mlog.Critical(fmt.Sprintf("Failed to check index %v", err)) 849 time.Sleep(time.Second) 850 os.Exit(EXIT_REMOVE_INDEX_MYSQL) 851 } 852 853 if count <= 0 { 854 return false 855 } 856 857 _, err = ss.GetMaster().ExecNoTimeout("DROP INDEX " + indexName + " ON " + tableName) 858 if err != nil { 859 mlog.Critical(fmt.Sprintf("Failed to remove index %v", err)) 860 time.Sleep(time.Second) 861 os.Exit(EXIT_REMOVE_INDEX_MYSQL) 862 } 863 } else if ss.DriverName() == model.DATABASE_DRIVER_SQLITE { 864 _, err := ss.GetMaster().ExecNoTimeout("DROP INDEX IF EXISTS " + indexName) 865 if err != nil { 866 mlog.Critical(fmt.Sprintf("Failed to remove index %v", err)) 867 time.Sleep(time.Second) 868 os.Exit(EXIT_REMOVE_INDEX_SQLITE) 869 } 870 } else { 871 mlog.Critical("Failed to create index because of missing driver") 872 time.Sleep(time.Second) 873 os.Exit(EXIT_REMOVE_INDEX_MISSING) 874 } 875 876 return true 877 } 878 879 func IsUniqueConstraintError(err error, indexName []string) bool { 880 unique := false 881 if pqErr, ok := err.(*pq.Error); ok && pqErr.Code == "23505" { 882 unique = true 883 } 884 885 if mysqlErr, ok := err.(*mysql.MySQLError); ok && mysqlErr.Number == 1062 { 886 unique = true 887 } 888 889 field := false 890 for _, contain := range indexName { 891 if strings.Contains(err.Error(), contain) { 892 field = true 893 break 894 } 895 } 896 897 return unique && field 898 } 899 900 func (ss *SqlSupplier) GetAllConns() []*gorp.DbMap { 901 all := make([]*gorp.DbMap, len(ss.replicas)+1) 902 copy(all, ss.replicas) 903 all[len(ss.replicas)] = ss.master 904 return all 905 } 906 907 func (ss *SqlSupplier) Close() { 908 mlog.Info("Closing SqlStore") 909 ss.master.Db.Close() 910 for _, replica := range ss.replicas { 911 replica.Db.Close() 912 } 913 } 914 915 func (ss *SqlSupplier) LockToMaster() { 916 ss.lockedToMaster = true 917 } 918 919 func (ss *SqlSupplier) UnlockFromMaster() { 920 ss.lockedToMaster = false 921 } 922 923 func (ss *SqlSupplier) Team() store.TeamStore { 924 return ss.oldStores.team 925 } 926 927 func (ss *SqlSupplier) Channel() store.ChannelStore { 928 return ss.oldStores.channel 929 } 930 931 func (ss *SqlSupplier) Post() store.PostStore { 932 return ss.oldStores.post 933 } 934 935 func (ss *SqlSupplier) User() store.UserStore { 936 return ss.oldStores.user 937 } 938 939 func (ss *SqlSupplier) Session() store.SessionStore { 940 return ss.oldStores.session 941 } 942 943 func (ss *SqlSupplier) Audit() store.AuditStore { 944 return ss.oldStores.audit 945 } 946 947 func (ss *SqlSupplier) ClusterDiscovery() store.ClusterDiscoveryStore { 948 return ss.oldStores.cluster 949 } 950 951 func (ss *SqlSupplier) Compliance() store.ComplianceStore { 952 return ss.oldStores.compliance 953 } 954 955 func (ss *SqlSupplier) OAuth() store.OAuthStore { 956 return ss.oldStores.oauth 957 } 958 959 func (ss *SqlSupplier) System() store.SystemStore { 960 return ss.oldStores.system 961 } 962 963 func (ss *SqlSupplier) Webhook() store.WebhookStore { 964 return ss.oldStores.webhook 965 } 966 967 func (ss *SqlSupplier) Command() store.CommandStore { 968 return ss.oldStores.command 969 } 970 971 func (ss *SqlSupplier) CommandWebhook() store.CommandWebhookStore { 972 return ss.oldStores.commandWebhook 973 } 974 975 func (ss *SqlSupplier) Preference() store.PreferenceStore { 976 return ss.oldStores.preference 977 } 978 979 func (ss *SqlSupplier) License() store.LicenseStore { 980 return ss.oldStores.license 981 } 982 983 func (ss *SqlSupplier) Token() store.TokenStore { 984 return ss.oldStores.token 985 } 986 987 func (ss *SqlSupplier) Emoji() store.EmojiStore { 988 return ss.oldStores.emoji 989 } 990 991 func (ss *SqlSupplier) Status() store.StatusStore { 992 return ss.oldStores.status 993 } 994 995 func (ss *SqlSupplier) FileInfo() store.FileInfoStore { 996 return ss.oldStores.fileInfo 997 } 998 999 func (ss *SqlSupplier) Reaction() store.ReactionStore { 1000 return ss.oldStores.reaction 1001 } 1002 1003 func (ss *SqlSupplier) Job() store.JobStore { 1004 return ss.oldStores.job 1005 } 1006 1007 func (ss *SqlSupplier) UserAccessToken() store.UserAccessTokenStore { 1008 return ss.oldStores.userAccessToken 1009 } 1010 1011 func (ss *SqlSupplier) ChannelMemberHistory() store.ChannelMemberHistoryStore { 1012 return ss.oldStores.channelMemberHistory 1013 } 1014 1015 func (ss *SqlSupplier) Plugin() store.PluginStore { 1016 return ss.oldStores.plugin 1017 } 1018 1019 func (ss *SqlSupplier) Role() store.RoleStore { 1020 return ss.oldStores.role 1021 } 1022 1023 func (ss *SqlSupplier) TermsOfService() store.TermsOfServiceStore { 1024 return ss.oldStores.TermsOfService 1025 } 1026 1027 func (ss *SqlSupplier) UserTermsOfService() store.UserTermsOfServiceStore { 1028 return ss.oldStores.UserTermsOfService 1029 } 1030 1031 func (ss *SqlSupplier) Scheme() store.SchemeStore { 1032 return ss.oldStores.scheme 1033 } 1034 1035 func (ss *SqlSupplier) Group() store.GroupStore { 1036 return ss.oldStores.group 1037 } 1038 1039 func (ss *SqlSupplier) LinkMetadata() store.LinkMetadataStore { 1040 return ss.oldStores.linkMetadata 1041 } 1042 1043 func (ss *SqlSupplier) DropAllTables() { 1044 ss.master.TruncateTables() 1045 } 1046 1047 type mattermConverter struct{} 1048 1049 func (me mattermConverter) ToDb(val interface{}) (interface{}, error) { 1050 1051 switch t := val.(type) { 1052 case model.StringMap: 1053 return model.MapToJson(t), nil 1054 case map[string]string: 1055 return model.MapToJson(model.StringMap(t)), nil 1056 case model.StringArray: 1057 return model.ArrayToJson(t), nil 1058 case model.StringInterface: 1059 return model.StringInterfaceToJson(t), nil 1060 case map[string]interface{}: 1061 return model.StringInterfaceToJson(model.StringInterface(t)), nil 1062 case JSONSerializable: 1063 return t.ToJson(), nil 1064 case *opengraph.OpenGraph: 1065 return json.Marshal(t) 1066 } 1067 1068 return val, nil 1069 } 1070 1071 func (me mattermConverter) FromDb(target interface{}) (gorp.CustomScanner, bool) { 1072 switch target.(type) { 1073 case *model.StringMap: 1074 binder := func(holder, target interface{}) error { 1075 s, ok := holder.(*string) 1076 if !ok { 1077 return errors.New(utils.T("store.sql.convert_string_map")) 1078 } 1079 b := []byte(*s) 1080 return json.Unmarshal(b, target) 1081 } 1082 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 1083 case *map[string]string: 1084 binder := func(holder, target interface{}) error { 1085 s, ok := holder.(*string) 1086 if !ok { 1087 return errors.New(utils.T("store.sql.convert_string_map")) 1088 } 1089 b := []byte(*s) 1090 return json.Unmarshal(b, target) 1091 } 1092 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 1093 case *model.StringArray: 1094 binder := func(holder, target interface{}) error { 1095 s, ok := holder.(*string) 1096 if !ok { 1097 return errors.New(utils.T("store.sql.convert_string_array")) 1098 } 1099 b := []byte(*s) 1100 return json.Unmarshal(b, target) 1101 } 1102 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 1103 case *model.StringInterface: 1104 binder := func(holder, target interface{}) error { 1105 s, ok := holder.(*string) 1106 if !ok { 1107 return errors.New(utils.T("store.sql.convert_string_interface")) 1108 } 1109 b := []byte(*s) 1110 return json.Unmarshal(b, target) 1111 } 1112 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 1113 case *map[string]interface{}: 1114 binder := func(holder, target interface{}) error { 1115 s, ok := holder.(*string) 1116 if !ok { 1117 return errors.New(utils.T("store.sql.convert_string_interface")) 1118 } 1119 b := []byte(*s) 1120 return json.Unmarshal(b, target) 1121 } 1122 return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true 1123 } 1124 1125 return gorp.CustomScanner{}, false 1126 } 1127 1128 type JSONSerializable interface { 1129 ToJson() string 1130 } 1131 1132 func convertMySQLFullTextColumnsToPostgres(columnNames string) string { 1133 columns := strings.Split(columnNames, ", ") 1134 concatenatedColumnNames := "" 1135 for i, c := range columns { 1136 concatenatedColumnNames += c 1137 if i < len(columns)-1 { 1138 concatenatedColumnNames += " || ' ' || " 1139 } 1140 } 1141 1142 return concatenatedColumnNames 1143 }