surveyService/model/migration.go

package model

import (
	"fmt"
	"os"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

var DB *gorm.DB

func Construct(isMigrate bool) {
	var err error
	DB, err = gorm.Open(mysql.Open(fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		os.Getenv("DB_USERNAME"),
		os.Getenv("DB_PASSWORD"),
		os.Getenv("DB_HOST"),
		os.Getenv("DB_PORT"),
		os.Getenv("DB_DATABASE"))),
		&gorm.Config{
			NamingStrategy: schema.NamingStrategy{
				SingularTable: true,
				TablePrefix:   os.Getenv("DB_PREFIX"),
			},
			DisableForeignKeyConstraintWhenMigrating: true,
			SkipDefaultTransaction:                   true,
			QueryFields:                              true,
		})
	if err != nil {
		panic(err)
	}

	// debug模式输出所有sql语句
	// if os.Getenv("APP_DEBUG") == "true" {
	DB = DB.Debug()
	// }

	sqlDB, err := DB.DB()
	if err != nil {
		panic(err)
	}
	sqlDB.SetMaxIdleConns(10)
	sqlDB.SetMaxOpenConns(100)

	// 开始迁移
	if isMigrate {
		Migrate()
	}
}

func Migrate() {
	// 问卷
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&Survey{})
	// 问卷授权
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&SurveyMechanism{})
	// 问卷结果
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&SurveyResult{})

	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&QuestionnaireSubject{})
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&QuestionnaireSurvey{})
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&QuestionnaireSurveyQuestionnaireSubject{})
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&QuestionnaireTemplate{})
	DB.Set("gorm:table_options", "ENGINE=InnoDB default charset = utf8mb4 COLLATE=utf8mb4_0900_as_cs").AutoMigrate(&QuestionnaireTemplateSubject{})
}

// 分页查询的Scope
func Paginate(page int, pageSize int) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if page == 0 {
			page = 1
		}
		switch {
		case pageSize > 100:
			pageSize = 100
		case pageSize <= 0:
			pageSize = 10
		}

		offset := (page - 1) * pageSize
		return db.Offset(offset).Limit(pageSize)
	}
}

// 分页查询的Scope
func Limit(limit int) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		switch {
		case limit > 100:
			limit = 100
		case limit <= 0:
			limit = 10
		}

		return db.Limit(limit)
	}
}

// 指定机构的Scope
func MechanismQuery(mechanismId string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if mechanismId != "" {
			return db.Where("mechanism_id = ?", mechanismId)
		}
		return db
	}
}