common

package
v0.8.1 Latest Latest
Warning

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

 Go to latest Published: Oct 23, 2018 License: Apache-2.0 Imports: 20 Imported by: 74

Documentation

Overview

Package common contain many useful functions for logging, formatting and so on.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var BaseDir string

BaseDir 日志打印在binary的根路径

View Source 
var BlackList []string

BlackList 黑名单中的SQL不会被评审

View Source 
var BuiltinCSS = `` /* 1851-byte string literal not displayed */

BuiltinCSS 内置HTML风格

View Source 
var BuiltinJavascript = `` /* 51813-byte string literal not displayed */

BuiltinJavascript 内置SQL美化Javascript脚本

View Source 
var CharSets = map[string]int{
	"armscii8": 1,
	"ascii":    1,
	"big5":     2,
	"binary":   1,
	"cp1250":   1,
	"cp1251":   1,
	"cp1256":   1,
	"cp1257":   1,
	"cp850":    1,
	"cp852":    1,
	"cp866":    1,
	"cp932":    2,
	"dec8":     1,
	"eucjpms":  3,
	"euckr":    2,
	"gb18030":  4,
	"gb2312":   2,
	"gbk":      2,
	"geostd8":  1,
	"greek":    1,
	"hebrew":   1,
	"hp8":      1,
	"keybcs2":  1,
	"koi8r":    1,
	"koi8u":    1,
	"latin1":   1,
	"latin2":   1,
	"latin5":   1,
	"latin7":   1,
	"macce":    1,
	"macroman": 1,
	"sjis":     2,
	"swe7":     1,
	"tis620":   1,
	"ucs2":     2,
	"ujis":     3,
	"utf16":    4,
	"utf16le":  4,
	"utf32":    4,
	"utf8":     3,
	"utf8mb4":  4,
}

CharSets character bytes per charcharacter bytes per char

View Source 
var Config = &Configration{
	OnlineDSN: &dsn{
		Schema:  "information_schema",
		Charset: "utf8mb4",
		Disable: true,
		Version: 999,
	},
	TestDSN: &dsn{
		Schema:  "information_schema",
		Charset: "utf8mb4",
		Disable: true,
		Version: 999,
	},
	AllowOnlineAsTest:       false,
	DropTestTemporary:       true,
	DryRun:                  true,
	OnlySyntaxCheck:         false,
	SamplingStatisticTarget: 100,
	Sampling:                false,
	Profiling:               false,
	Trace:                   false,
	Explain:                 true,
	ConnTimeOut:             3,
	QueryTimeOut:            30,
	Delimiter:               ";",

	MaxJoinTableCount:    5,
	MaxGroupByColsCount:  5,
	MaxDistinctCount:     5,
	MaxIdxColsCount:      5,
	MaxIdxBytesPerColumn: 767,
	MaxIdxBytes:          3072,
	MaxTotalRows:         9999999,
	MaxQueryCost:         9999,
	SpaghettiQueryLength: 2048,
	AllowDropIndex:       false,
	LogLevel:             3,
	LogOutput:            getDefaultLogOutput(),
	ReportType:           "markdown",
	ReportCSS:            "",
	ReportJavascript:     "",
	ReportTitle:          "SQL优化分析报告",
	BlackList:            "",
	TableAllowCharsets:   []string{"utf8", "utf8mb4"},
	TableAllowEngines:    []string{"innodb"},
	MaxIdxCount:          10,
	MaxColCount:          40,
	MaxInCount:           10,
	IdxPrefix:            "idx_",
	UkPrefix:             "uk_",
	MaxSubqueryDepth:     5,
	MaxVarcharLength:     1024,

	MarkdownExtensions: 94,
	MarkdownHTMLFlags:  0,

	ExplainSQLReportType:   "pretty",
	ExplainType:            "extended",
	ExplainFormat:          "traditional",
	ExplainWarnSelectType:  []string{""},
	ExplainWarnAccessType:  []string{"ALL"},
	ExplainMaxKeyLength:    3,
	ExplainMinPossibleKeys: 0,
	ExplainMaxRows:         10000,
	ExplainWarnExtra:       []string{"Using temporary", "Using filesort"},
	ExplainMaxFiltered:     100.0,
	ExplainWarnScalability: []string{"O(n)"},
	ShowWarnings:           false,
	ShowLastQueryCost:      false,

	IgnoreRules: []string{
		"COL.011",
	},
	RewriteRules: []string{
		"delimiter",
		"orderbynull",
		"groupbyconst",
		"dmlorderby",
		"having",
		"star2columns",
		"insertcolumns",
		"distinctstar",
	},

	ListHeuristicRules: false,
	ListRewriteRules:   false,
	ListTestSqls:       false,
	ListReportTypes:    false,
	MaxPrettySQLLength: 1024,
}

Config 默认设置

View Source 
var Log *logs.BeeLogger

Log 使用beego的log库

View Source 
var ReportTypes = []ReportType{
	{
		Name:        "lint",
		Description: "参考sqlint格式，以插件形式集成到代码编辑器，显示输出更加友好",
		Example:     `soar -report-type lint -query test.sql`,
	},
	{
		Name:        "markdown",
		Description: "该格式为默认输出格式，以markdown格式展现，可以用网页浏览器插件直接打开，也可以用markdown编辑器打开",
		Example:     `echo "select * from film" | soar`,
	},
	{
		Name:        "rewrite",
		Description: "SQL重写功能，配合-rewrite-rules参数一起使用，可以通过-list-rewrite-rules查看所有支持的SQL重写规则",
		Example:     `echo "select * from film" | soar -rewrite-rules star2columns,delimiter -report-type rewrite`,
	},
	{
		Name:        "ast",
		Description: "输出SQL的抽象语法树，主要用于测试",
		Example:     `echo "select * from film" | soar -report-type ast`,
	},
	{
		Name:        "tiast",
		Description: "输出SQL的TiDB抽象语法树，主要用于测试",
		Example:     `echo "select * from film" | soar -report-type tiast`,
	},
	{
		Name:        "fingerprint",
		Description: "输出SQL的指纹",
		Example:     `echo "select * from film where language_id=1" | soar -report-type fingerprint`,
	},
	{
		Name:        "md2html",
		Description: "markdown格式转html格式小工具",
		Example:     `soar -list-heuristic-rules | soar -report-type md2html > heuristic_rules.html`,
	},
	{
		Name:        "explain-digest",
		Description: "输入为EXPLAIN的表格，JSON或Vertical格式，对其进行分析，给出分析结果",
		Example: `soar -report-type explain-digest << EOF
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1131 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
EOF`,
	},
	{
		Name:        "duplicate-key-checker",
		Description: "对OnlineDsn中指定的DB进行索引重复检查",
		Example:     `soar -report-type duplicate-key-checker -online-dsn user:passwd@127.0.0.1:3306/db`,
	},
	{
		Name:        "html",
		Description: "以HTML格式输出报表",
		Example:     `echo "select * from film" | soar -report-type html`,
	},
	{
		Name:        "json",
		Description: "输出JSON格式报表，方便应用程序处理",
		Example:     `echo "select * from film" | soar -report-type json`,
	},
	{
		Name:        "tokenize",
		Description: "对SQL进行切词，主要用于测试",
		Example:     `echo "select * from film" | soar -report-type tokenize`,
	},
	{
		Name:        "compress",
		Description: "SQL压缩小工具，使用内置SQL压缩逻辑，测试中的功能",
		Example: `echo "select
*
from
  film" | soar -report-type compress`,
	},
	{
		Name:        "pretty",
		Description: "使用kr/pretty打印报告，主要用于测试",
		Example:     `echo "select * from film" | soar -report-type pretty`,
	},
	{
		Name:        "remove-comment",
		Description: "去除SQL语句中的注释，支持单行多行注释的去除",
		Example:     `echo "select/*comment*/ * from film" | soar -report-type remove-comment`,
	},
}

ReportTypes 命令行-report-type支持的形式

View Source 
var TestSQLs []string

TestSQLs 测试SQL大集合

Functions

func Caller 

func Caller() string

Caller returns the caller of the function that called it :) https://stackoverflow.com/questions/35212985/is-it-possible-get-information-about-caller-function-in-golang

func FormatDSN 

func FormatDSN(env *dsn) string

FormatDSN 格式化打印DSN

Example 
dsxExp := &dsn{
	Addr:     "127.0.0.1:3306",
	Schema:   "mysql",
	User:     "root",
	Password: "1t'sB1g3rt",
	Charset:  "utf8mb4",
	Disable:  false,
}

// 根据 &dsn 生成 dsnStr
fmt.Println(FormatDSN(dsxExp))

Output:

root:1t'sB1g3rt@127.0.0.1:3306/mysql?charset=utf8mb4

func GetDataTypeBase 

func GetDataTypeBase(dataType string) string

GetDataTypeBase 获取dataType中的数据类型，忽略长度

func GetDataTypeLength 

func GetDataTypeLength(dataType string) []int

GetDataTypeLength 获取dataType中的数据类型长度

func GetFunctionName 

func GetFunctionName() string

GetFunctionName 获取调当前函数名

func GoldenDiff 

func GoldenDiff(f func(), name string, update *bool) error

GoldenDiff 从gofmt学来的测试方法 https://medium.com/soon-london/testing-with-golden-files-in-go-7fccc71c43d3

func IsColsPart 

func IsColsPart(a, b []*Column) bool

IsColsPart 判断两个column队列是否是包含关系（包括相等）

Example 
// IsColsPart() 会 按照顺序 检查两个Column队列是否是包含（或相等）关系。
a := []*Column{{Name: "1"}, {Name: "2"}, {Name: "3"}}
b := []*Column{{Name: "1"}, {Name: "2"}}
c := []*Column{{Name: "1"}, {Name: "3"}}
d := []*Column{{Name: "1"}, {Name: "2"}, {Name: "3"}, {Name: "4"}}

ab := IsColsPart(a, b)
ac := IsColsPart(a, c)
ad := IsColsPart(a, d)

fmt.Println(ab, ac, ad)

Output:

true false true

func JoinColumnsName 

func JoinColumnsName(cols []*Column, sep string) string

JoinColumnsName 将所有的列合并

func ListReportTypes 

func ListReportTypes()

ListReportTypes 查看所有支持的report-type

func LogIfError 

func LogIfError(err error, format string, v ...interface{})

LogIfError 简化if err != nil打Error日志代码长度

func LogIfWarn 

func LogIfWarn(err error, format string, v ...interface{})

LogIfWarn 简化if err != nil打Warn日志代码长度

func LoggerInit 

func LoggerInit()

LoggerInit Log配置初始化

func Markdown2HTML 

func Markdown2HTML(buf string) string

Markdown2HTML markdown转HTML输出

func MarkdownEscape 

func MarkdownEscape(str string) string

MarkdownEscape markdown格式转义，原样输出

func MarkdownHTMLHeader 

func MarkdownHTMLHeader() string

MarkdownHTMLHeader markdown转HTML输出时添加HTML头

func ParseConfig 

func ParseConfig(configFile string) error

ParseConfig 加载配置文件和命令行参数

func Score 

func Score(score int) string

Score SQL评审打分

func SortedKey 

func SortedKey(m interface{}) []string

SortedKey sort map[string]interface{}, use in range clause

Example 
ages := map[string]int{
	"a": 1,
	"c": 3,
	"d": 4,
	"b": 2,
}
for _, name := range SortedKey(ages) {
	fmt.Print(ages[name])
}

Output:

1234

func StringStorageReq 

func StringStorageReq(dataType string, charset string) int

StringStorageReq String Type Storage Requirements return bytes count

Types

type Column 

type Column struct {
	Name        string   `json:"col_name"`    // 列名
	Alias       []string `json:"alias"`       // 别名
	Table       string   `json:"tb_name"`     // 表名
	DB          string   `json:"db_name"`     // 数据库名称
	DataType    string   `json:"data_type"`   // 数据类型
	Character   string   `json:"character"`   // 字符集
	Collation   string   `json:"collation"`   // collation
	Cardinality float64  `json:"cardinality"` // 散粒度
	Null        string   `json:"null"`        // 是否为空: YES/NO
	Key         string   `json:"key"`         // 键类型
	Default     string   `json:"default"`     // 默认值
	Extra       string   `json:"extra"`       // 其他
	Comment     string   `json:"comment"`     // 备注
	Privileges  string   `json:"privileges"`  // 权限
}

Column 含有列的定义属性

func ColumnSort 

func ColumnSort(colList []*Column) []*Column

ColumnSort 通过散粒度对 colList 进行排序， 散粒度排序由大到小

func MergeColumn 

func MergeColumn(dst []*Column, src ...*Column) []*Column

MergeColumn 将使用到的列按db->table组织去重 注意：Column中的db, table信息可能为空，需要提前通过env环境补齐再调用该函数。 @input: 目标列list, 源列list（可以将多个源合并到一个目标列list） @output: 合并后的列list

func (*Column) Equal 

func (col *Column) Equal(column *Column) bool

Equal 判断两个column是否相等

func (*Column) GetDataBytes 

func (col *Column) GetDataBytes(dbVersion int) int

GetDataBytes 计算数据类型字节数 https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html return -1 表示该列无法计算数据大小

type Configration 

type Configration struct {
	// +++++++++++++++测试环境+++++++++++++++++
	OnlineDSN               *dsn   `yaml:"online-dsn"`                // 线上环境数据库配置
	TestDSN                 *dsn   `yaml:"test-dsn"`                  // 测试环境数据库配置
	AllowOnlineAsTest       bool   `yaml:"allow-online-as-test"`      // 允许Online环境也可以当作Test环境
	DropTestTemporary       bool   `yaml:"drop-test-temporary"`       // 是否清理Test环境产生的临时库表
	OnlySyntaxCheck         bool   `yaml:"only-syntax-check"`         // 只做语法检查不输出优化建议
	SamplingStatisticTarget int    `yaml:"sampling-statistic-target"` // 数据采样因子，对应postgres的default_statistics_target
	Sampling                bool   `yaml:"sampling"`                  // 数据采样开关
	Profiling               bool   `yaml:"profiling"`                 // 在开启数据采样的情况下，在测试环境执行进行profile
	Trace                   bool   `yaml:"trace"`                     // 在开启数据采样的情况下，在测试环境执行进行Trace
	Explain                 bool   `yaml:"explain"`                   // Explain开关
	ConnTimeOut             int    `yaml:"conn-time-out"`             // 数据库连接超时时间，单位秒
	QueryTimeOut            int    `yaml:"query-time-out"`            // 数据库SQL执行超时时间，单位秒
	Delimiter               string `yaml:"delimiter"`                 // SQL分隔符

	// +++++++++++++++日志相关+++++++++++++++++
	// 日志级别，这里使用了beego的log包
	// [0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug]
	LogLevel int `yaml:"log-level"`
	// 日志输出位置，默认日志输出到控制台
	// 目前只支持['console', 'file']两种形式，如非console形式这里需要指定文件的路径，可以是相对路径
	LogOutput string `yaml:"log-output"`
	// 优化建议输出格式，目前支持: json, text, markdown格式，如指定其他格式会给pretty.Println的输出
	ReportType string `yaml:"report-type"`
	// 当ReportType为html格式时使用的css风格，如不指定会提供一个默认风格。CSS可以是本地文件，也可以是一个URL
	ReportCSS string `yaml:"report-css"`
	// 当ReportType为html格式时使用的javascript脚本，如不指定默认会加载SQL pretty使用的javascript。像CSS一样可以是本地文件，也可以是一个URL
	ReportJavascript string `yaml:"report-javascript"`
	// 当ReportType为html格式时，HTML的title
	ReportTitle string `yaml:"report-title"`
	// blackfriday markdown2html config
	MarkdownExtensions int `yaml:"markdown-extensions"` // markdown转html支持的扩展包, 参考blackfriday
	MarkdownHTMLFlags  int `yaml:"markdown-html-flags"` // markdown转html支持的flag, 参考blackfriday, default 0

	// ++++++++++++++优化建议相关++++++++++++++
	IgnoreRules          []string `yaml:"ignore-rules"`              // 忽略的优化建议规则
	RewriteRules         []string `yaml:"rewrite-rules"`             // 生效的重写规则
	BlackList            string   `yaml:"blacklist"`                 // blacklist中的SQL不会被评审，可以是指纹，也可以是正则
	MaxJoinTableCount    int      `yaml:"max-join-table-count"`      // 单条SQL中JOIN表的最大数量
	MaxGroupByColsCount  int      `yaml:"max-group-by-cols-count"`   // 单条SQL中GroupBy包含列的最大数量
	MaxDistinctCount     int      `yaml:"max-distinct-count"`        // 单条SQL中Distinct的最大数量
	MaxIdxColsCount      int      `yaml:"max-index-cols-count"`      // 复合索引中包含列的最大数量
	MaxTotalRows         int64    `yaml:"max-total-rows"`            // 计算散粒度时，当数据行数大于 MaxTotalRows即开启数据库保护模式，散粒度返回结果可信度下降
	MaxQueryCost         int64    `yaml:"max-query-cost"`            // last_query_cost 超过该值时将给予警告
	SpaghettiQueryLength int      `yaml:"spaghetti-query-length"`    // SQL最大长度警告，超过该长度会给警告
	AllowDropIndex       bool     `yaml:"allow-drop-index"`          // 允许输出删除重复索引的建议
	MaxInCount           int      `yaml:"max-in-count"`              // IN()最大数量
	MaxIdxBytesPerColumn int      `yaml:"max-index-bytes-percolumn"` // 索引中单列最大字节数，默认767
	MaxIdxBytes          int      `yaml:"max-index-bytes"`           // 索引总长度限制，默认3072
	TableAllowCharsets   []string `yaml:"table-allow-charsets"`      // Table允许使用的DEFAULT CHARSET
	TableAllowEngines    []string `yaml:"table-allow-engines"`       // Table允许使用的Engine
	MaxIdxCount          int      `yaml:"max-index-count"`           // 单张表允许最多索引数
	MaxColCount          int      `yaml:"max-column-count"`          // 单张表允许最大列数
	IdxPrefix            string   `yaml:"index-prefix"`              // 普通索引建议使用的前缀
	UkPrefix             string   `yaml:"unique-key-prefix"`         // 唯一键建议使用的前缀
	MaxSubqueryDepth     int      `yaml:"max-subquery-depth"`        // 子查询最大尝试
	MaxVarcharLength     int      `yaml:"max-varchar-length"`        // varchar最大长度

	// ++++++++++++++EXPLAIN检查项+++++++++++++
	ExplainSQLReportType   string   `yaml:"explain-sql-report-type"`  // EXPLAIN markdown格式输出SQL样式，支持sample, fingerprint, pretty
	ExplainType            string   `yaml:"explain-type"`             // EXPLAIN方式 [traditional, extended, partitions]
	ExplainFormat          string   `yaml:"explain-format"`           // FORMAT=[json, traditional]
	ExplainWarnSelectType  []string `yaml:"explain-warn-select-type"` // 哪些select_type不建议使用
	ExplainWarnAccessType  []string `yaml:"explain-warn-access-type"` // 哪些access type不建议使用
	ExplainMaxKeyLength    int      `yaml:"explain-max-keys"`         // 最大key_len
	ExplainMinPossibleKeys int      `yaml:"explain-min-keys"`         // 最小possible_keys警告
	ExplainMaxRows         int      `yaml:"explain-max-rows"`         // 最大扫描行数警告
	ExplainWarnExtra       []string `yaml:"explain-warn-extra"`       // 哪些extra信息会给警告
	ExplainMaxFiltered     float64  `yaml:"explain-max-filtered"`     // filtered大于该配置给出警告
	ExplainWarnScalability []string `yaml:"explain-warn-scalability"` // 复杂度警告名单
	ShowWarnings           bool     `yaml:"show-warnings"`            // explain extended with show warnings
	ShowLastQueryCost      bool     `yaml:"show-last-query-cost"`     // switch with show status like 'last_query_cost'
	// ++++++++++++++其他配置项+++++++++++++++
	Query              string `yaml:"query"`                 // 需要进行调优的SQL
	ListHeuristicRules bool   `yaml:"list-heuristic-rules"`  // 打印支持的评审规则列表
	ListRewriteRules   bool   `yaml:"list-rewrite-rules"`    // 打印重写规则
	ListTestSqls       bool   `yaml:"list-test-sqls"`        // 打印测试case用于测试
	ListReportTypes    bool   `yaml:"list-report-types"`     // 打印支持的报告输出类型
	Verbose            bool   `yaml:"verbose"`               // verbose模式，会多输出一些信息
	DryRun             bool   `yaml:"dry-run"`               // 是否在预演环境执行
	MaxPrettySQLLength int    `yaml:"max-pretty-sql-length"` // 超出该长度的SQL会转换成指纹输出
}

Configration 配置文件定义结构体

type DB 

type DB struct {
	Name  string
	Table map[string]*Table // ['table_name']*Table
}

DB 数据库相关的结构体

func NewDB 

func NewDB(db string) *DB

NewDB 用于初始化*DB

type KeyType 

type KeyType int

KeyType 用于标志每个Key的类别

type Meta 

type Meta map[string]*DB

Meta 以'database'为key, DB的map，按db->table->column组织的元数据

func (Meta) SetDefault 

func (b Meta) SetDefault(defaultDB string) Meta

SetDefault 设置默认值

func (Meta) Tables 

func (b Meta) Tables(db string) []string

Tables 获取Meta中指定db的所有表名 Input:数据库名 Output:表名组成的list

type ReportType 

type ReportType struct {
	Name        string `json:"Name"`
	Description string `json:"Description"`
	Example     string `json:"Example"`
}

ReportType 元数据结构定义

type Table 

type Table struct {
	TableName    string
	TableAliases []string
	Column       map[string]*Column
}

Table 含有表的属性

func NewTable 

func NewTable(tb string) *Table

NewTable 初始化*Table

type TableColumns 

type TableColumns map[string]map[string][]*Column

TableColumns 这个结构体中的元素是有序的 map[db]map[table][]columns

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.