dbutil

package
v5.3.2+incompatible Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Jun 7, 2022 License: Apache-2.0 Imports: 29 Imported by: 239

README

dbutil library

dbutil is a library that contains a collection of auxiliary functions related to MySQL and TiDB.

Documentation

Index

Constants

View Source
const (
	// DefaultRetryTime is the default retry time to execute sql
	DefaultRetryTime = 10

	// DefaultTimeout is the default timeout for execute sql
	DefaultTimeout time.Duration = 10 * time.Second

	// SlowLogThreshold defines the duration to log debug log of sql when exec time greater than
	SlowLogThreshold = 200 * time.Millisecond

	// DefaultDeleteRowsNum is the default rows num for delete one time
	DefaultDeleteRowsNum int64 = 100000
)

Variables

View Source
var (
	// ErrVersionNotFound means can't get the database's version
	ErrVersionNotFound = errors.New("can't get the database's version")

	// ErrNoData means no data in table
	ErrNoData = errors.New("no data found in table")
)
View Source
var (
	// Retryable1105Msgs list the error messages of some retryable error with `1105` code (`ErrUnknown`).
	Retryable1105Msgs = []string{
		"Information schema is out of date",
		"Information schema is changed",
	}
)

Functions

func AnalyzeValuesFromBuckets

func AnalyzeValuesFromBuckets(valueString string, cols []*model.ColumnInfo) ([]string, error)

AnalyzeValuesFromBuckets analyze upperBound or lowerBound to string for each column. upperBound and lowerBound are looks like '(123, abc)' for multiple fields, or '123' for one field.

func CloseDB

func CloseDB(db *sql.DB) error

CloseDB closes the mysql fd

func ColumnName

func ColumnName(column string) string

ColumnName returns `column`

func DecodeTimeInBucket

func DecodeTimeInBucket(packedStr string) (string, error)

DecodeTimeInBucket decodes Time from a packed uint64 value.

func DeleteRows

func DeleteRows(ctx context.Context, db DBExecutor, schemaName string, tableName string, where string, args []interface{}) error

DeleteRows delete rows in several times. Only can delete less than 300,000 one time in TiDB.

func EqualTableInfo

func EqualTableInfo(tableInfo1, tableInfo2 *model.TableInfo) (bool, string)

EqualTableInfo returns true if this two table info have same columns and indices

func ExecSQLWithRetry

func ExecSQLWithRetry(ctx context.Context, db DBExecutor, sql string, args ...interface{}) (err error)

ExecSQLWithRetry executes sql with retry

func ExecuteSQLs

func ExecuteSQLs(ctx context.Context, db DBExecutor, sqls []string, args [][]interface{}) error

ExecuteSQLs executes some sqls in one transaction

func FindAllColumnWithIndex

func FindAllColumnWithIndex(tableInfo *model.TableInfo) []*model.ColumnInfo

FindAllColumnWithIndex returns columns with index, order is pk, uk and normal index.

func FindAllIndex

func FindAllIndex(tableInfo *model.TableInfo) []*model.IndexInfo

FindAllIndex returns all index, order is pk, uk, and normal index.

func FindColumnByName

func FindColumnByName(cols []*model.ColumnInfo, name string) *model.ColumnInfo

FindColumnByName finds column by name.

func FindSuitableColumnWithIndex

func FindSuitableColumnWithIndex(ctx context.Context, db QueryExecutor, schemaName string, tableInfo *model.TableInfo) (*model.ColumnInfo, error)

FindSuitableColumnWithIndex returns first column of a suitable index. The priority is * primary key * unique key * normal index which has max cardinality

func FormatTimeZoneOffset

func FormatTimeZoneOffset(offset time.Duration) string

func GetBucketsInfo

func GetBucketsInfo(ctx context.Context, db QueryExecutor, schema, table string, tableInfo *model.TableInfo) (map[string][]Bucket, error)

GetBucketsInfo SHOW STATS_BUCKETS in TiDB.

func GetCRC32Checksum

func GetCRC32Checksum(ctx context.Context, db QueryExecutor, schemaName, tableName string, tbInfo *model.TableInfo, limitRange string, args []interface{}) (int64, error)

GetCRC32Checksum returns checksum code of some data by given condition

func GetCreateTableSQL

func GetCreateTableSQL(ctx context.Context, db QueryExecutor, schemaName string, tableName string) (string, error)

GetCreateTableSQL returns the create table statement.

func GetDBVersion

func GetDBVersion(ctx context.Context, db QueryExecutor) (string, error)

GetDBVersion returns the database's version

func GetMinMaxValue

func GetMinMaxValue(ctx context.Context, db QueryExecutor, schema, table, column string, limitRange string, limitArgs []interface{}, collation string) (string, string, error)

GetMinMaxValue return min and max value of given column by specified limitRange condition.

func GetParserForDB

func GetParserForDB(ctx context.Context, db QueryExecutor) (*parser.Parser, error)

GetParserForDB discovers ANSI_QUOTES in db's session variables and returns a proper parser

func GetRandomValues

func GetRandomValues(ctx context.Context, db QueryExecutor, schemaName, table, column string, num int, limitRange string, limitArgs []interface{}, collation string) ([]string, error)

GetRandomValues returns some random value. Tips: limitArgs is the value in limitRange.

func GetRowCount

func GetRowCount(ctx context.Context, db QueryExecutor, schemaName string, tableName string, where string, args []interface{}) (int64, error)

GetRowCount returns row count of the table. if not specify where condition, return total row count of the table.

func GetSQLMode

func GetSQLMode(ctx context.Context, db QueryExecutor) (tmysql.SQLMode, error)

GetSQLMode returns sql_mode.

func GetSchemas

func GetSchemas(ctx context.Context, db QueryExecutor) ([]string, error)

GetSchemas returns name of all schemas

func GetSessionVariable

func GetSessionVariable(ctx context.Context, db QueryExecutor, variable string) (value string, err error)

GetSessionVariable gets server's session variable, although argument is QueryExecutor, (session) system variables may be set through DSN

func GetTableInfo

func GetTableInfo(ctx context.Context, db QueryExecutor, schemaName string, tableName string) (*model.TableInfo, error)

GetTableInfo returns table information.

func GetTableInfoBySQL

func GetTableInfoBySQL(createTableSQL string, parser2 *parser.Parser) (table *model.TableInfo, err error)

GetTableInfoBySQL returns table information by given create table sql.

func GetTables

func GetTables(ctx context.Context, db QueryExecutor, schemaName string) (tables []string, err error)

GetTables returns name of all tables in the specified schema

func GetTidbLatestTSO

func GetTidbLatestTSO(ctx context.Context, db QueryExecutor) (int64, error)

GetTidbLatestTSO returns tidb's current TSO.

func GetTimeZoneOffset

func GetTimeZoneOffset(ctx context.Context, db QueryExecutor) (time.Duration, error)

func GetViews

func GetViews(ctx context.Context, db QueryExecutor, schemaName string) (tables []string, err error)

GetViews returns names of all views in the specified schema

func IsFloatType

func IsFloatType(tp byte) bool

IsFloatType returns true if tp is float type

func IsNumberType

func IsNumberType(tp byte) bool

IsNumberType returns true if tp is number type

func IsRetryableError

func IsRetryableError(err error) bool

IsRetryableError checks whether the SQL statement can be retry directly when encountering this error. NOTE: this should be compatible with different TiDB versions. some errors are only retryable in some special cases, then we mark it as un-retryable: - errno.ErrTiKVServerTimeout - errno.ErrTableLocked

some errors are un-retryable: - errno.ErrQueryInterrupted

some errors are unknown: - errno.ErrRegionUnavailable

func IsTiDB

func IsTiDB(ctx context.Context, db QueryExecutor) (bool, error)

IsTiDB returns true if this database is tidb

func IsTimeTypeAndNeedDecode

func IsTimeTypeAndNeedDecode(tp byte) bool

IsTimeTypeAndNeedDecode returns true if tp is time type and encoded in tidb buckets.

func OpenDB

func OpenDB(cfg DBConfig, vars map[string]string) (*sql.DB, error)

OpenDB opens a mysql connection FD

func ReplacePlaceholder

func ReplacePlaceholder(str string, args []string) string

ReplacePlaceholder will use args to replace '?', used for log. tips: make sure the num of "?" is same with len(args)

func ScanRow

func ScanRow(rows *sql.Rows) (map[string]*ColumnData, error)

ScanRow scans rows into a map.

func ScanRowsToInterfaces

func ScanRowsToInterfaces(rows *sql.Rows) ([][]interface{}, error)

ScanRowsToInterfaces scans rows to interface arrary.

func SelectUniqueOrderKey

func SelectUniqueOrderKey(tbInfo *model.TableInfo) ([]string, []*model.ColumnInfo)

SelectUniqueOrderKey returns some columns for order by condition.

func ShowBinlogFormat

func ShowBinlogFormat(ctx context.Context, db QueryExecutor) (value string, err error)

ShowBinlogFormat queries variable 'binlog_format' and returns its value.

func ShowBinlogRowImage

func ShowBinlogRowImage(ctx context.Context, db QueryExecutor) (value string, err error)

ShowBinlogRowImage queries variable 'binlog_row_image' and returns its values.

func ShowGrants

func ShowGrants(ctx context.Context, db QueryExecutor, user, host string) ([]string, error)

ShowGrants queries privileges for a mysql user. For mysql 8.0, if user has granted roles, ShowGrants also extract privilege from roles.

func ShowLogBin

func ShowLogBin(ctx context.Context, db QueryExecutor) (value string, err error)

ShowLogBin queries variable 'log_bin' and returns its value.

func ShowMySQLVariable

func ShowMySQLVariable(ctx context.Context, db QueryExecutor, variable string) (value string, err error)

ShowMySQLVariable queries MySQL variable and returns its value.

func ShowServerID

func ShowServerID(ctx context.Context, db QueryExecutor) (serverID uint64, err error)

ShowServerID queries variable 'server_id' and returns its value.

func ShowVersion

func ShowVersion(ctx context.Context, db QueryExecutor) (value string, err error)

ShowVersion queries variable 'version' and returns its value.

func TableName

func TableName(schema, table string) string

TableName returns `schema`.`table`

Types

type Bucket

type Bucket struct {
	Count      int64
	LowerBound string
	UpperBound string
}

Bucket saves the bucket information from TiDB.

type ColumnData

type ColumnData struct {
	Data   []byte
	IsNull bool
}

ColumnData saves column's data.

type DBConfig

type DBConfig struct {
	Host string `toml:"host" json:"host"`

	Port int `toml:"port" json:"port"`

	User string `toml:"user" json:"user"`

	Password string `toml:"password" json:"-"` // omit it for privacy

	Schema string `toml:"schema" json:"schema"`

	Snapshot string `toml:"snapshot" json:"snapshot"`
}

DBConfig is database configuration.

func GetDBConfigFromEnv

func GetDBConfigFromEnv(schema string) DBConfig

GetDBConfigFromEnv returns DBConfig from environment

func (*DBConfig) String

func (c *DBConfig) String() string

String returns native format of database configuration

type DBExecutor

type DBExecutor interface {
	QueryExecutor
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

DBExecutor is a interface for execute read and write statements from a database.

in generate the implement should be *sql.DB or *sql.Conn

type IndexInfo

type IndexInfo struct {
	Table       string
	NoneUnique  bool
	KeyName     string
	SeqInIndex  int
	ColumnName  string
	Cardinality int
}

IndexInfo contains information of table index.

func ShowIndex

func ShowIndex(ctx context.Context, db QueryExecutor, schemaName string, table string) ([]*IndexInfo, error)

ShowIndex returns result of executing `show index`

type QueryExecutor

type QueryExecutor interface {
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

QueryExecutor is a interface for execute Query from a database.

in generate the implement should be *sql.DB or *sql.Conn

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL