util.go

TLDR

This file contains the implementation of the SQLStore struct, which provides utility functions for interacting with a SQL database. It also includes the definition of the ErrInvalidDBType struct. The PrepareNewTestDatabase function creates a new database for testing purposes.

Methods

CloseRows

Closes the given sql.Rows object. It logs an error if closing the rows fails.

IsErrNotFound

Checks if the given error is of type model.ErrNotFound. Returns true if it is, false otherwise.

MarshalJSONB

Marshals the given data object into JSON format. If isBinaryParam is true, it appends a byte with value 0x01 at the beginning of the JSON data.

deleteBoardRecord

Deletes a board record without deleting any child records in the blocks table. This method is for unit testing purposes only.

deleteBlockRecord

Deletes a blocks record without deleting any child records in the blocks table. This method is for unit testing purposes only.

castInt

Casts the given integer value as the specified type (as) in the SQL dialect being used (MySQL or other databases).

GetSchemaName

Returns the current schema name of the connected database. The returned schema name varies depending on the database type (MySQL or PostgreSQL).

Classes

None

package sqlstore

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"strings"

	sq "github.com/Masterminds/squirrel"
	"github.com/mattermost/focalboard/server/model"
	"github.com/mattermost/focalboard/server/utils"

	"github.com/mattermost/mattermost-server/v6/shared/mlog"
)

func (s *SQLStore) CloseRows(rows *sql.Rows) {
	if err := rows.Close(); err != nil {
		s.logger.Error("error closing MattermostAuthLayer row set", mlog.Err(err))
	}
}

func (s *SQLStore) IsErrNotFound(err error) bool {
	return model.IsErrNotFound(err)
}

func (s *SQLStore) MarshalJSONB(data interface{}) ([]byte, error) {
	b, err := json.Marshal(data)
	if err != nil {
		return nil, err
	}

	if s.isBinaryParam {
		b = append([]byte{0x01}, b...)
	}

	return b, nil
}

func PrepareNewTestDatabase() (dbType string, connectionString string, err error) {
	dbType = strings.TrimSpace(os.Getenv("FOCALBOARD_STORE_TEST_DB_TYPE"))
	if dbType == "" {
		dbType = model.SqliteDBType
	}
	if dbType == "mariadb" {
		dbType = model.MysqlDBType
	}

	var dbName string
	var rootUser string

	if dbType == model.SqliteDBType {
		file, err := os.CreateTemp("", "fbtest_*.db")
		if err != nil {
			return "", "", err
		}
		connectionString = file.Name() + "?_busy_timeout=5000"
		_ = file.Close()
	} else if port := strings.TrimSpace(os.Getenv("FOCALBOARD_STORE_TEST_DOCKER_PORT")); port != "" {
		// docker unit tests take priority over any DSN env vars
		var template string
		switch dbType {
		case model.MysqlDBType:
			template = "%s:mostest@tcp(localhost:%s)/%s?charset=utf8mb4,utf8&writeTimeout=30s"
			rootUser = "root"
		case model.PostgresDBType:
			template = "postgres://%s:mostest@localhost:%s/%s?sslmode=disable\u0026connect_timeout=10"
			rootUser = "mmuser"
		default:
			return "", "", newErrInvalidDBType(dbType)
		}

		connectionString = fmt.Sprintf(template, rootUser, port, "")

		// create a new database each run
		sqlDB, err := sql.Open(dbType, connectionString)
		if err != nil {
			return "", "", fmt.Errorf("cannot connect to %s database: %w", dbType, err)
		}
		defer sqlDB.Close()

		err = sqlDB.Ping()
		if err != nil {
			return "", "", fmt.Errorf("cannot ping %s database: %w", dbType, err)
		}

		dbName = "testdb_" + utils.NewID(utils.IDTypeNone)[:8]
		_, err = sqlDB.Exec(fmt.Sprintf("CREATE DATABASE %s;", dbName))
		if err != nil {
			return "", "", fmt.Errorf("cannot create %s database %s: %w", dbType, dbName, err)
		}

		if dbType != model.PostgresDBType {
			_, err = sqlDB.Exec(fmt.Sprintf("GRANT ALL PRIVILEGES ON %s.* TO mmuser;", dbName))
			if err != nil {
				return "", "", fmt.Errorf("cannot grant permissions on %s database %s: %w", dbType, dbName, err)
			}
		}

		connectionString = fmt.Sprintf(template, "mmuser", port, dbName)
	} else {
		// mysql or postgres need a DSN (connection string)
		connectionString = strings.TrimSpace(os.Getenv("FOCALBOARD_STORE_TEST_CONN_STRING"))
	}

	return dbType, connectionString, nil
}

type ErrInvalidDBType struct {
	dbType string
}

func newErrInvalidDBType(dbType string) error {
	return ErrInvalidDBType{
		dbType: dbType,
	}
}

func (e ErrInvalidDBType) Error() string {
	return "unsupported database type: " + e.dbType
}

// deleteBoardRecord deletes a boards record without deleting any child records in the blocks table.
// FOR UNIT TESTING ONLY.
func (s *SQLStore) deleteBoardRecord(db sq.BaseRunner, boardID string, modifiedBy string) error {
	return s.deleteBoardAndChildren(db, boardID, modifiedBy, true)
}

// deleteBlockRecord deletes a blocks record without deleting any child records in the blocks table.
// FOR UNIT TESTING ONLY.
func (s *SQLStore) deleteBlockRecord(db sq.BaseRunner, blockID, modifiedBy string) error {
	return s.deleteBlockAndChildren(db, blockID, modifiedBy, true)
}

func (s *SQLStore) castInt(val int64, as string) string {
	if s.dbType == model.MysqlDBType {
		return fmt.Sprintf("cast(%d as unsigned) AS %s", val, as)
	}
	return fmt.Sprintf("cast(%d as bigint) AS %s", val, as)
}

func (s *SQLStore) GetSchemaName() (string, error) {
	var query sq.SelectBuilder

	switch s.dbType {
	case model.MysqlDBType:
		query = s.getQueryBuilder(s.db).Select("DATABASE()")
	case model.PostgresDBType:
		query = s.getQueryBuilder(s.db).Select("current_schema()")
	case model.SqliteDBType:
		return "", nil
	default:
		return "", ErrUnsupportedDatabaseType
	}

	scanner := query.QueryRow()

	var result string
	err := scanner.Scan(&result)
	if err != nil && !model.IsErrNotFound(err) {
		return "", err
	}

	return result, nil
}