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  }