dac

package module
v0.0.0-...-2f13c3a Latest Latest
Warning

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

 Go to latest Published: May 24, 2024 License: Apache-2.0 Imports: 7 Imported by: 0

README

数据访问组件

数据库访问组件设计文档

  1. 引言 数据库访问组件旨在提供一个通用的接口，以屏蔽不同数据库间的差异，使得在不同数据库间切换更加方便，并提供一致的数据访问方式。该组件基于GORM库实现，支持多种主流数据库协议，如MySQL、Oracle、PostgreSQL等。

  2. 设计目标

  • 提供统一的接口，以封装底层数据库的差异性。
  • 支持常见的数据访问操作，如查询、更新、删除等。
  • 提供函数方法接口，以支持数据库函数的调用。
  • 提供操作符接口，用于构建查询条件。
  • 使得在服务中的数据库访问操作更加灵活和可扩展。
  • 兼容不同数据库，并保持代码的简洁性和可读性。
  1. 数据结构
// DataAccess 数据访问接口
type DataAccess interface {
	Table(db *gorm.DB, name string) *gorm.DB
	Find(db *gorm.DB, out interface{}) *gorm.DB
	First(db *gorm.DB, out interface{}) *gorm.DB
	Last(db *gorm.DB, out interface{}) *gorm.DB
	Count(db *gorm.DB, count *int64) *gorm.DB
	Select(db *gorm.DB, fields []Field) *gorm.DB
	Limit(db *gorm.DB, page, pageSize int64) *gorm.DB
	Group(db *gorm.DB, group string) *gorm.DB
	Order(db *gorm.DB, order string) *gorm.DB
}

// FunctionProvider 接口定义了一个获取函数的方法
type FunctionProvider interface {
	Max() string
	Min() string
	Count() string
	CountDistinct() string
	Avg() string
	Sum() string
	DateFormat() string
	Upper() string
	Lower() string
	Concat() string
	Length() string
	ToDateTime() string
	Distinct() string
}

type OperatorI interface {
    BuildQuery(condition Condition, query *QueryFilter)
}
  1. 接口说明
  • DataAccess: 数据访问接口，定义了统一的数据访问操作
  • FunctionProvider: 函数方法接口，定义了数据库函数的调用方法
  • OperatorI: 操作符接口，用于构建查询条件。

  1. 数据库实现
    实现不同数据库的访问功能，可以根据具体需求分别编写实现。例如，针对MySQL、Oracle、PostgreSQL等数据库，分别实现对应的DataAccess接口方法、FunctionProvider接口方法和OperatorI接口方法。

  2. 使用示例

   
func TestConditionBuilder(t *testing.T) {
	conditon := NewConditionBuilder()
	conditon.AppendCondition("name", Equal, "John", And)

	sql, qf := conditon.Build(Mysql)
	// 生成 SQL 条件语句
	fmt.Printf("sql :%s", sql)
	fmt.Printf("args:%v", qf)
}

type CountList struct {
	Id uint `json:"id"`
	Count int64 `json:"count"`
}
func TestCreate(t *testing.T) {
	option := NewBuilderOption()
	builder := NewConditionBuilder()
	builder.AppendCondition("app_id", Equal, "APP123")
	builder.AppendCondition("id", Equal, 1)
	option.AppendBuilder(builder)
	var cs []CountList
	err := NewDatabase(Mysql).Use(nil).Where(option).Select("id",Count(1).As("count")).
		Group("id").Find(&cs).Error()
	if err != nil {
		t.Logf(err.Error())
	}
	fmt.Println(cs)
}
  1. 总结 通过以上设计，我们实现了一个通用的数据库访问组件，可以灵活地支持多种主流数据库，提供了统一的接口以及常见的数据访问操作，同时也提供了函数方法接口以及操作符接口，使得在服务中进行数据库访问更加方便、可扩展和灵活，并且保持了代码的简洁性和可读性。

Documentation

Index

Constants

View Source 
const (
	TYPE_INT       = "int"    // 整数类型
	TYPE_BIGINT    = "bigint" // 长整型
	TYPE_TINYINT   = "tinyint"
	TYPE_DECIMAL   = "decimal"   // 十进制数
	TYPE_NUMERIC   = "numeric"   // 数值
	TYPE_REAL      = "real"      // 实数
	TYPE_DOUBLE    = "double"    // 双精度浮点数
	TYPE_SMALLINT  = "smallint"  // 短整型
	TYPE_BOOLEAN   = "boolean"   // 布尔型
	TYPE_CHAR      = "char"      // 字符串（定长）
	TYPE_VARCHAR   = "varchar"   // 字符串（变长）
	TYPE_DATE      = "date"      // 日期
	TYPE_TIME      = "time"      // 时间
	TYPE_TIMESTAMP = "timestamp" // 时间戳
	TYPE_INTERVAL  = "interval"  // 时间间隔
	TYPE_BYTEA     = "bytea"     // 二进制数据
	TYPE_UUID      = "uuid"      // UUID
	//不受其他数据库支持的字段类型
	TYPE_TEXT      = "text" // 文本
	TYPE_LONG_TEXT = "longtext"
	TYPE_ENUM      = "enum"
	TYPE_BLOB      = "blob"
)
View Source 
const (
	GO_TYPE_UINT          = "uint"          // 无符号整数类型
	GO_TYPE_UINT8         = "uint8"         // 无符号 8 位整数类型
	GO_TYPE_UINT16        = "uint16"        // 无符号 16 位整数类型
	GO_TYPE_UINT32        = "uint32"        // 无符号 32 位整数类型
	GO_TYPE_UINT64        = "uint64"        // 无符号 64 位整数类型
	GO_TYPE_INT           = "int"           // 整数类型       -> int
	GO_TYPE_INT8          = "int8"          // 8 位整数类型    -> int8
	GO_TYPE_INT16         = "int16"         // 16 位整数类型   -> int16
	GO_TYPE_INT32         = "int32"         // 32 位整数类型   -> int32
	GO_TYPE_INT64         = "int64"         // 64 位整数类型   -> int64
	GO_TYPE_FLOAT32       = "float32"       // 单精度浮点数   -> float32
	GO_TYPE_FLOAT64       = "float64"       // 双精度浮点数   -> float64
	GO_TYPE_BOOLEAN       = "bool"          // 布尔型         -> bool
	GO_TYPE_STRING        = "string"        // 文本           -> string
	GO_TYPE_TIME          = "time.Time"     // 时间           -> time.Time 或者自定义的 Time 类型
	GO_TYPE_INTERVAL      = "time.Duration" // 时间间隔       -> time.Duration 或者自定义的 Interval 类型
	GO_TYPE_BYTEA         = "[]byte"        // 二进制数据     -> []byte
	GO_TYPE_UUID          = "string"        // UUID           -> string 或者自定义的 UUID 类型
	GO_TYPE_SQL_NULL_TIME = "sql.NullTime"  // SQL 空时间 -> sql.NullTime
)

Variables

View Source 
var OperatorMap = map[DBType]OperatorI{}

Functions

func GetFunctionHandlerSQL 

func GetFunctionHandlerSQL(function FunctionType, fp FunctionProvider) string

func GetFunctionSQL 

func GetFunctionSQL(function FunctionType) string

GetFunctionSQL 获取 functionMap 中的值

func IsConstantTypeSupported 

func IsConstantTypeSupported(constantType string) bool

IsConstantTypeSupported 检查常量类型是否受支持

func IsDatabaseTypeSupported 

func IsDatabaseTypeSupported(fieldType string) bool

IsDatabaseTypeSupported 检查数据库类型是否受支持

func IsFunctionTypeValid 

func IsFunctionTypeValid(function FunctionType) bool

IsFunctionTypeValid 判断是否在该 map 中

func PrintCallerInfo 

func PrintCallerInfo(err error)

PrintCallerInfo 打印调用者信息

func RegisterDatabase 

func RegisterDatabase(dbType DBType, dataAccess DataAccess)

RegisterDatabase 注册不同数据库类型的方法

func RegisterFunctionProvider 

func RegisterFunctionProvider(dbType DBType, dataAccess FunctionProvider)

RegisterFunctionProvider 注册不同数据库类型的函数方法

func RegisterOperator 

func RegisterOperator(dbType DBType, operator OperatorI)

func ReplaceFieldType 

func ReplaceFieldType(dbType DBType, fieldType string) string

Types

type BuilderOption 

type BuilderOption struct {
	// contains filtered or unexported fields
}

func NewBuilderOption 

func NewBuilderOption() *BuilderOption

func (*BuilderOption) AppendBuilder 

func (b *BuilderOption) AppendBuilder(builder *ConditionBuilder) *BuilderOption

func (*BuilderOption) NewBuilder 

func (b *BuilderOption) NewBuilder() *ConditionBuilder

type ClickHouseDatabase 

type ClickHouseDatabase struct {
	DataAccess
}

ClickHouseDatabase 结构体实现 ClickHouse 数据库访问方法

type ClickhouseOperator 

type ClickhouseOperator struct {
	OperatorI
}

func (*ClickhouseOperator) Blank 

func (co *ClickhouseOperator) Blank(condition Condition, qf *QueryFilter)

func (*ClickhouseOperator) BuildQuery 

func (co *ClickhouseOperator) BuildQuery(condition Condition, qf *QueryFilter)

func (*ClickhouseOperator) Equals 

func (co *ClickhouseOperator) Equals(condition Condition, qf *QueryFilter)

Equals 等于

func (*ClickhouseOperator) NotBlank 

func (co *ClickhouseOperator) NotBlank(condition Condition, qf *QueryFilter)

func (*ClickhouseOperator) NotEqual 

func (co *ClickhouseOperator) NotEqual(condition Condition, qf *QueryFilter)

type Condition 

type Condition struct {
	Field    any // 字段名
	Key      string
	Operator Operator    // 操作符
	Value    interface{} // 值
	Joiner   JoinerType  // 条件连接词：AND 或 OR
}

Condition 条件结构体

func NewCondition 

func NewCondition() *Condition

func (*Condition) Build 

func (cb *Condition) Build(field any, operator Operator, value any, joiner ...JoinerType) *Condition

type ConditionBuilder 

type ConditionBuilder struct {
	// contains filtered or unexported fields
}

ConditionBuilder 用于生成 SQL 条件语句的结构体

func NewConditionBuilder 

func NewConditionBuilder() *ConditionBuilder

func (*ConditionBuilder) AddCondition 

func (cb *ConditionBuilder) AddCondition(condition *Condition) *ConditionBuilder

AddCondition 方法用于添加条件

func (*ConditionBuilder) And 

func (cb *ConditionBuilder) And() *ConditionBuilder

And 方法用于设置 AND 连接词

func (*ConditionBuilder) Append 

func (cb *ConditionBuilder) Append(builder ConditionBuilder)

Append 方法用于添加条件

func (*ConditionBuilder) AppendCondition 

func (cb *ConditionBuilder) AppendCondition(field any, operator Operator, value any, joiner ...JoinerType) *ConditionBuilder

AppendCondition AddCondition 方法用于添加条件

func (*ConditionBuilder) Build 

func (cb *ConditionBuilder) Build(dbType DBType) (string, []interface{})

Build 方法用于生成 SQL 条件语句

func (*ConditionBuilder) Or 

func (cb *ConditionBuilder) Or() *ConditionBuilder

Or 方法用于设置 OR 连接词

type DBType 

type DBType string
const (
	Clickhouse DBType = "clickhouse"
	Mysql      DBType = "mysql"
	Postgres   DBType = "postgres"
)

type DataAccess 

type DataAccess interface {
	Table(db *gorm.DB, name string) *gorm.DB
	Find(db *gorm.DB, out interface{}) *gorm.DB
	First(db *gorm.DB, out interface{}) *gorm.DB
	Last(db *gorm.DB, out interface{}) *gorm.DB
	Count(db *gorm.DB, count *int64) *gorm.DB
	Select(db *gorm.DB, fields []Field) *gorm.DB
	Limit(db *gorm.DB, page, pageSize int64) *gorm.DB
	Group(db *gorm.DB, group string) *gorm.DB
	Order(db *gorm.DB, order string) *gorm.DB
}

DataAccess 数据访问接口

func GetDataAccess 

func GetDataAccess(dbType DBType) DataAccess

GetDataAccess 方法根据外部传入的数据库类型执行相应的操作

type Database 

type Database struct {
	// contains filtered or unexported fields
}

Database 结构体定义

func NewDatabase 

func NewDatabase(dbType DBType) *Database

NewDatabase 函数用于创建数据库实例

func (*Database) AutoMigrate 

func (d *Database) AutoMigrate(dst ...interface{}) error

AutoMigrate 创建表 AutoMigrate 创建表

func (*Database) Count 

func (d *Database) Count(count *int64) *Database

Count 查询数量

func (*Database) Create 

func (d *Database) Create(out interface{}) *Database

Create 创建

func (*Database) DB 

func (d *Database) DB() *gorm.DB

DB 获取原始的 db

func (*Database) Delete 

func (d *Database) Delete(out interface{}) *Database

Delete 删除

func (*Database) Error 

func (d *Database) Error() error

Error 获取错误

func (*Database) Find 

func (d *Database) Find(out interface{}) *Database

Find 查询

func (*Database) First 

func (d *Database) First(out interface{}) *Database

First 查询第一条

func (*Database) Group 

func (d *Database) Group(group string) *Database

Group 分组

func (*Database) Having 

func (d *Database) Having(builder *ConditionBuilder) *Database

Having having条件查询

func (*Database) Join 

func (d *Database) Join(tableWithAlias, condition string) *Database

func (*Database) Joins 

func (d *Database) Joins(query string, args ...interface{}) *Database

Joins 连接查询

func (*Database) Last 

func (d *Database) Last(out interface{}) *Database

Last 查询最后一条

func (*Database) LeftJoin 

func (d *Database) LeftJoin(tableWithAlias, condition string) *Database

func (*Database) Limit 

func (d *Database) Limit(page, pageSize int) *Database

Limit 分页

func (*Database) Model 

func (d *Database) Model(model interface{}) *Database

Model 设置模型

func (*Database) Order 

func (d *Database) Order(order string) *Database

Order 排序

func (*Database) Pluck 

func (d *Database) Pluck(column any, desc any) *Database

Pluck 查询字段

func (*Database) Preload 

func (d *Database) Preload(query string, args ...interface{}) *Database

func (*Database) Save 

func (d *Database) Save(out interface{}) *Database

func (*Database) Scan 

func (d *Database) Scan(out interface{}) *Database

Scan 将数据输出到指定的结构体

func (*Database) Select 

func (d *Database) Select(fields ...any) *Database

Select 查询字段

func (*Database) Table 

func (d *Database) Table(name string) *Database

func (*Database) Update 

func (d *Database) Update(column string, value interface{}) *Database

Update 更新单个列

func (*Database) Updates 

func (d *Database) Updates(out interface{}) *Database

Updates 根据 `struct` 更新属性，只会更新非零值的字段

func (*Database) Use 

func (d *Database) Use(db *gorm.DB) *Database

Use 传入 db

func (*Database) Where 

func (d *Database) Where(buildOption *BuilderOption) *Database

Where 构建查询条件

type Field 

type Field struct {
	Name any // 字段名

	Alias string //别名
	// contains filtered or unexported fields
}

Field 结构表示一个字段，可能包含聚合函数

func Avg 

func Avg(field string) *Field

func Concat 

func Concat(fields ...string) *Field

func Count 

func Count(field any) *Field

func CountDistinct 

func CountDistinct(field string) *Field

func DateFormat 

func DateFormat(field string) *Field

func Distinct 

func Distinct(field string) *Field

func Length 

func Length(field string) *Field

func Lower 

func Lower(field string) *Field

func Max 

func Max(field string) *Field

func Min 

func Min(field string) *Field

func NewField 

func NewField(name string) *Field

NewField 创建一个字段，允许不传入聚合函数

func Sum 

func Sum(field string) *Field

func ToDateTime 

func ToDateTime(field string) *Field

func Upper 

func Upper(field string) *Field

func (*Field) As 

func (f *Field) As(as string) *Field

func (*Field) Avg 

func (f *Field) Avg() string

func (*Field) Concat 

func (f *Field) Concat() string

func (*Field) Count 

func (f *Field) Count() string

func (*Field) CountDistinct 

func (f *Field) CountDistinct() string

func (*Field) DateFormat 

func (f *Field) DateFormat() string

func (*Field) Distinct 

func (f *Field) Distinct() string

func (*Field) GenerateSelectSQL 

func (f *Field) GenerateSelectSQL(fp string) string

GenerateSelectSQL 生成 SELECT 语句

func (*Field) Length 

func (f *Field) Length() string

func (*Field) Lower 

func (f *Field) Lower() string

func (*Field) Max 

func (f *Field) Max() string

func (*Field) Min 

func (f *Field) Min() string

func (*Field) Sum 

func (f *Field) Sum() string

func (*Field) ToDateTime 

func (f *Field) ToDateTime() string

func (*Field) Upper 

func (f *Field) Upper() string

type FunctionProvider 

type FunctionProvider interface {
	Max() string
	Min() string
	Count() string
	CountDistinct() string
	Avg() string
	Sum() string
	DateFormat() string
	Upper() string
	Lower() string
	Concat() string
	Length() string
	ToDateTime() string
	Distinct() string
}

FunctionProvider 接口定义了一个获取函数的方法

func GetDataFunctionProvider 

func GetDataFunctionProvider(dbType DBType) FunctionProvider

type FunctionType 

type FunctionType string
const (
	MaxFunc           FunctionType = "max"
	MinFunc           FunctionType = "min"
	CountFunc         FunctionType = "count"
	CountNoFieldFunc  FunctionType = "count_no_field"
	CountDistinctFunc FunctionType = "count_distinct"
	AvgFunc           FunctionType = "avg"
	SumFunc           FunctionType = "sum"
	DateFormatFunc    FunctionType = "date_format"
	UpperFunc         FunctionType = "upper"
	LowerFunc         FunctionType = "lower"
	ConcatFunc        FunctionType = "concat"
	LengthFunc        FunctionType = "length" // MySQL中为length，PostgresSQL中为LENGTH
	ToDateTimeFunc    FunctionType = "toDateTime"
	DistinctFunc      FunctionType = "distinct"
)

常量定义

type JoinerType 

type JoinerType string
const (
	And JoinerType = "AND"
	Or  JoinerType = "OR"
)

type MySQLDatabase 

type MySQLDatabase struct {
	DataAccess
}

MySQLDatabase 结构体实现 MySQL 数据库访问方法

func (*MySQLDatabase) Limit 

func (m *MySQLDatabase) Limit(db *gorm.DB, page, pageSize int64) *gorm.DB

Limit 实现Limit方法

type MysqlOperator 

type MysqlOperator struct {
	OperatorI
}

func (MysqlOperator) BuildQuery 

func (m MysqlOperator) BuildQuery(condition Condition, qf *QueryFilter)

func (MysqlOperator) Equal 

func (m MysqlOperator) Equal(condition Condition, qf *QueryFilter)

func (MysqlOperator) GreaterThan 

func (m MysqlOperator) GreaterThan(condition Condition, qf *QueryFilter)

func (MysqlOperator) GreaterThanOrEqual 

func (m MysqlOperator) GreaterThanOrEqual(condition Condition, qf *QueryFilter)

func (MysqlOperator) LessThanOrEqual 

func (m MysqlOperator) LessThanOrEqual(condition Condition, qf *QueryFilter)

func (MysqlOperator) NotEqual 

func (m MysqlOperator) NotEqual(condition Condition, qf *QueryFilter)

type Operator 

type Operator string
const (
	Equal              Operator = "equal"
	NotEqual           Operator = "notEqual"
	GreaterThan        Operator = "greaterThan"
	GreaterThanOrEqual Operator = "greaterThanOrEqual"
	LessThanOrEqual    Operator = "lessThanOrEqual"
	LessThan           Operator = "lessThan"
	Like               Operator = "like"
	In                 Operator = "in"
	NotIn              Operator = "notIn"
	IsNull             Operator = "isNull"
	IsNotNull          Operator = "isNotNull"
	NotLike            Operator = "notLike"
	NotBetween         Operator = "notBetween"
	Between            Operator = "between"
)

操作符常量

type OperatorI 

type OperatorI interface {
	BuildQuery(condition Condition, query *QueryFilter)
}

OperatorI 定义了操作符接口

func GetOperatorI 

func GetOperatorI(dbType DBType) OperatorI

type PostgresDatabase 

type PostgresDatabase struct {
	DataAccess
	PostgresOperator
}

type PostgresOperator 

type PostgresOperator struct {
	OperatorI
}

func (PostgresOperator) BuildQuery 

func (m PostgresOperator) BuildQuery(condition Condition, qf *QueryFilter)

func (PostgresOperator) Equal 

func (m PostgresOperator) Equal(condition Condition, qf *QueryFilter)

func (PostgresOperator) NotEqual 

func (m PostgresOperator) NotEqual(condition Condition, qf *QueryFilter)

type PostgresProvider 

type PostgresProvider struct {
	Field
}

func (*PostgresProvider) Length 

func (p *PostgresProvider) Length() string

func (*PostgresProvider) Max 

func (p *PostgresProvider) Max() string

type QueryFilter 

type QueryFilter struct {
	Query string
	Args  []any
}

func (*QueryFilter) And 

func (qf *QueryFilter) And(query string, args ...any) *QueryFilter

func (*QueryFilter) Or 

func (qf *QueryFilter) Or(query string, args ...any) *QueryFilter

type SelectStr 

type SelectStr struct {
	Value string
}

SelectStr 是一个自定义类型，用于字符串的拼接操作。

func NewSelectStr 

func NewSelectStr(str string) *SelectStr

NewSelectStr 构造函数，用于创建一个新的 SelectStr 实例。

func (*SelectStr) Join 

func (s *SelectStr) Join(str string) *SelectStr

Join 方法用于将当前实例与另一个字符串进行拼接。 如果当前实例为空，则直接使用另一个字符串；如果不为空，则将其值逗号分隔拼接到当前实例之后。

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.