Day8 資料庫架構

12th鐵人賽

linyanbin

2020-09-08 00:18:48

2250 瀏覽

database scheme

現在來寫資料庫，輸入

docker run \
--name mysqlcontainer \
-v "$(pwd)/database/maindata:/var/lib/mysql" \
-p 3306:3306 \
-e "MYSQL_ROOT_PASSWORD=your_password" \
-e "MYSQL_DATABASE=app" \
-d mysql

建立一個mysql資料庫，並將資料掛載在$(pwd)/database/maindata方便重複使用

進入容器

docker exec -it mysqlcontainer /bin/bash

使用mysql

mysql -u root -pyour_password

選擇database

use app;

來考慮一下schema

每個owner底下可以有多個blog
blog一定要屬於某個owner
blog有不同類型，如文章、相片或是blog等等...

建立table

CREATE TABLE `article` (
  `name` char(44) NOT NULL,
  `super` int unsigned NOT NULL,
  PRIMARY KEY (`name`),
  KEY `super` (`super`)
);

CREATE TABLE `blog` (
  `bid` int unsigned NOT NULL AUTO_INCREMENT,
  `oid` int unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `super` int unsigned DEFAULT NULL,
  `like` int unsigned NOT NULL DEFAULT '0',
  `hate` int unsigned NOT NULL DEFAULT '0',
  `viewtime` int unsigned NOT NULL DEFAULT '0',
  `description` varchar(255) DEFAULT NULL COMMENT 'abstract',
  `type` tinyint unsigned NOT NULL,
  `urlpath` varchar(750) NOT NULL,
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP,
  `updatetime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bid`),
  UNIQUE KEY `filepath` (`urlpath`),
  KEY `oid` (`oid`),
  KEY `type` (`type`),
  KEY `blog_name` (`name`),
  KEY `blog_ibfk_2` (`super`),
  CONSTRAINT `blog_ibfk_1` FOREIGN KEY (`oid`) REFERENCES `owner` (`oid`) ON DELETE CASCADE,
  CONSTRAINT `blog_ibfk_2` FOREIGN KEY (`super`) REFERENCES `blog` (`bid`) ON DELETE CASCADE,
  CONSTRAINT `blog_ibfk_3` FOREIGN KEY (`type`) REFERENCES `blogtype` (`typeid`)
);

CREATE TABLE `blogtype` (
  `typeid` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`typeid`)
);

CREATE TABLE `owner` (
  `oid` int unsigned NOT NULL AUTO_INCREMENT,
  `uid` int unsigned DEFAULT NULL,
  `nickname` varchar(50) NOT NULL,
  `uniquename` varchar(50) NOT NULL COMMENT 'id of User-defined',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP,
  `updatetime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'last login time',
  `description` varchar(255) DEFAULT NULL COMMENT 'abstract',
  PRIMARY KEY (`oid`),
  UNIQUE KEY `uniquename` (`uniquename`),
  KEY `uid` (`uid`),
  CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
);

CREATE TABLE `user` (
  `uid` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL COMMENT 'account number',
  `password` char(44) NOT NULL,
  `email` varchar(40) NOT NULL,
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'REGIST TIME',
  `salt` char(22) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `salt` (`salt`)
);

把blogtype補上project與article兩類

INSERT INTO `blogtype` (`name`) VALUES (`project`), (`article`);

補上設定檔調整連接的參數，在config/app.yaml補上

databases:
  main: &main_database
    driver: mysql
    user: root
    password: your_password
    name: app
    param: "parseTime=true"
    host: 127.0.0.1
    port: 3306
    option:
      MaxOpenConnects: 25
      MaxIdleConnects: 0
      ConnMaxLifetime: 300 # secondsd

寫完設定檔需要讀出來，在setting/setting.go補上

var (
	DBs      map[string]*DBStruct
)

type DBStruct struct {
	Driver   string         `yaml:"driver"`
	User     string         `yaml:"user"`
	Password string         `yaml:"password"`
	Name     string         `yaml:"name"`
	Host     string         `yaml:"host"`
	Port     string         `yaml:"port"`
	Param    string         `yaml:"param,omitempty"`
	Option   map[string]int `yaml:"option,omitempty"`
}

type ConfigStruct struct {
	Servers   map[string]*ServerStruct
	Databases map[string]*DBStruct
}

在init()中補上

Config = &c
DBs = Config.Databases
Servers = Config.Servers

接著讓go連上database，我使用的ORM工具是xorm，創建database package，在底下創建scheme.go，寫入

package database

import (
	"time"
)

type User struct {
	Uid        int       `json:"uid" xorm:"not null pk autoincr INT(11) 'uid'"`
	Username   string    `json:"uusername" xorm:"not null comment('account number') VARCHAR(40) 'username'"`
	Password   string    `json:"upassword" xorm:"not null CHAR(40) 'password'"`
	Email      string    `json:"uemail" xorm:"not null VARCHAR(40) 'email'"`
	Createtime time.Time `json:"ucreatetime" xorm:"default 'current_timestamp()' comment('REGIST TIME') DATETIME 'createtime'"`
}

type Owner struct {
	Oid         int       `json:"oid" xorm:"not null pk autoincr INT(11) 'oid'"`
	Uid         int       `json:"ouid" xorm:"not null INT(11) 'uid'"`
	Nickname    string    `json:"onickname" xorm:"not null VARCHAR(50) 'nickname'"`
	Uniquename  string    `json:"ouniquename" xorm:"not null comment('id of User-defined') VARCHAR(50) 'uniquename'"`
	Description string    `json:"odescription" xorm:"comment('abstract') VARCHAR(255) 'description'"`
	Createtime  time.Time `json:"ocreatetime" xorm:"default 'current_timestamp()' DATETIME 'createtime'"`
	Updatetime  time.Time `json:"oupdatetime" xorm:"default 'current_timestamp()' comment('last login time') DATETIME 'updatetime'"`
}

type Blogtype struct {
	Typeid int    `json:"typeid" xorm:"not null pk autoincr TINYINT(4) 'typeid'"`
	Name   string `json:"name" xorm:"not null VARCHAR(20) 'name'"`
}

type Blog struct {
	Bid         int       `json:"bid" xorm:"not null pk autoincr INT(11) 'bid'"`
	Oid         int       `json:"boid" xorm:"not null INT(11) 'oid'"`
	Name        string    `json:"bname" xorm:"not null VARCHAR(100) 'name'"`
	Super       int       `json:"bsuper" xorm:"INT(11) 'super'"`
	Like        int       `json:"blike" xorm:"not null default 0 INT(11) 'like'"`
	Hate        int       `json:"bhate" xorm:"not null default 0 INT(11) 'hate'"`
	Viewtime    int       `json:"bviewtime" xorm:"not null default 0 INT(11) 'viewtime'"`
	Description string    `json:"bdescription" xorm:"comment('abstract') VARCHAR(255) 'description'"`
	Type        int       `json:"btype" xorm:"not null TINYINT(4) 'type'"`
	Urlpath     string    `json:"burlpath" xorm:"not null VARCHAR(750) 'urlpath'"`
	Createtime  time.Time `json:"bcreatetime" xorm:"default 'current_timestamp()' DATETIME 'createtime'"`
	Updatetime  time.Time `json:"bupdatetime" xorm:"default 'current_timestamp()' DATETIME 'updatetime'"`
}

type Article struct {
	Name  string `json:"name" xorm:"not null pk CHAR(44) 'name'"`
	Super int    `json:"super" xorm:"not null INT(11) 'super'"`
}

解釋：

後面json與xorm的部份是用來給json與xorm包識別struct資料用的

這些scheme之後讀資料的時候會用到，創建error.go，寫入

package database

import "errors"

var (
	ERR_TASK_FAIL     = errors.New("FAIL TO AFFECT ROW")
	ERR_NAME_CONFLICT = errors.New("NAME CONFLICT")
	ERR_PARAMETER     = errors.New("PARAMETER WRONG")
)

接著創建connect.go，寫入

package database

import (
	"app/setting"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
	"xorm.io/xorm"
)

var (
	db *xorm.Engine
)

func init() {
	var (
		err        error
		connectStr string
		dbName     string
	)

	if setting.Servers["main"].RunMode == "debug" {
		dbName = "test"
	} else {
		dbName = "main"
	}

	connectStr = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?%s", setting.DBs[dbName].User, setting.DBs[dbName].Password,
		setting.DBs[dbName].Host, setting.DBs[dbName].Port, setting.DBs[dbName].Name, setting.DBs[dbName].Param)

	db, err = xorm.NewEngine(setting.DBs["main"].Driver, connectStr)
	if err != nil {
		panic(err)
	}

	// optimize option
	db.SetMaxOpenConns(setting.DBs["main"].Option["SetMaxOpenConnects"])
	db.SetMaxIdleConns(setting.DBs["main"].Option["SetMaxIdleConnects"])
	db.SetConnMaxLifetime(time.Duration(setting.DBs["main"].Option["SetConnMaxLifetime"]) * time.Second)

	if setting.Servers["main"].RunMode == "debug" {
		db.ShowSQL(true)
		db.Logger().SetLevel(core.LOG_DEBUG)
	}
}

func Close() error {
	if err := db.Close(); err != nil {
		return err
	}
	return nil
}

總結

先把scheme與連線弄好，明天開始處理query

目前的工作環境

.
├── app
│   ├── apperr
│   │   ├── code.go
│   │   ├── error.go
│   │   └── handle.go
│   ├── config
│   │   └── app
│   │       ├── app.yaml
│   │       └── error.yaml
│   ├── database
│   │   ├── connect.go
│   │   ├── error.go
│   │   └── scheme.go
│   ├── go.mod
│   ├── go.sum
│   ├── logger
│   │   ├── logger.go
│   │   └── logging.go
│   ├── main.go
│   ├── middleware
│   │   ├── error.go
│   │   └── log.go
│   ├── router
│   │   ├── host_switch.go
│   │   └── main.go
│   ├── serve
│   │   ├── main.go
│   │   └── main_test.go
│   ├── setting
│   │   └── setting.go
│   ├── util
│   │   └── debug
│   │       ├── stack.go
│   │       └── stack_test.go
└── database
    └── maindata