Go 언어 Back End 제작해보기(데이터베이스) 5장

◆ MySQL에 테이블 생성

create database GoMusic;

use GoMusic;

create table customer(
  id int PRIMARY KEY AUTO_INCREMENT,
  firstname varchar(50) not null,
  lastnamename varchar(50) not null,
  email varchar(100) unique not null,
  cc_customerid varchar(50) not null,
  looggedin tinyint not null,
  created_at timestamp  DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp  DEFAULT CURRENT_TIMESTAMP,
  deleted_ar timestamp
);

create table products(
  id int PRIMARY KEY AUTO_INCREMENT,
  image varchar(100),
  imgalt varchar(50),
  description text,
  productname varchar(50),
  price float,
  promotion float,
  created_at timestamp  DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp  DEFAULT CURRENT_TIMESTAMP,
  deleted_ar timestamp
);

create table orders(
  id int PRIMARY KEY AUTO_INCREMENT,
  custome_id int not null,
  product_id int not null,
  price int not null,
  purchase_date timestamp  DEFAULT CURRENT_TIMESTAMP,
  created_at timestamp  DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp  DEFAULT CURRENT_TIMESTAMP,
  deleted_ar timestamp
);

◆ GORM

▶ GORM 패키지는 Go 언어에서 가장 많이 사용되는 ORM 패키지

▶ 도큐먼트: https://gorm.io/

▶ 설치: go get -u github.com/jinzhu/gorm

▶ model.go 파일을 열어서 모델 확장

package models

import (
  "time"
  "github.com/jinzhu/gorm"
)

type Product struct {
  gorm.Model
  Image       string  `json:"img"`
  ImagAlt     string  `json:"imgalt" gorm:"column:imgalt"`
  Price       float64 `json:"price"`
  Promotion   float64 `json:"promotion"`
  PoructName  string  `gorm:"column:productname" json:"productname"`
  Description string
}

func (Product) TableName() string {
  return "products"
}

type Customer struct {
  gorm.Model
  FirstName string `gorm:"column:firstname" json:"firstname"`
  LastName  string `gorm:"column:lastname" json:"lastname"`
  Email     string `gorm:"column:email" json:"email"`
  Pass      string `json:"password"`
  CCToken   string `gorm:"column:cctoken" json:"cctoken"`
  LoggedIn  bool   `gorm:"column:loggedin" json:"loggedin"`
}

func (Customer) TableName() string {
  return "customers"
}

type Order struct {
  gorm.Model
  Product
  Customer
  CustomerID   int       `gorm:"column:customer_id"`
  ProductID    int       `gorm:"column:product_id"`
  Price        float64   `gorm:"column:price" json:"sell_price"`
  PurchaseDate time.Time `gorm:"column:purchase_date" json:"purchase_date"`
}

func (Order) TableName() string {
  return "orders"
}

▶ mysql 드라이버 설치

go get github.com/go-sql-driver/mysql

▶ dblayer 디렉토리에 orm.go 파일을 생성하고 작성

package dblayer

import (
  "errors"
  "models"
  _ "github.com/go-sql-driver/mysql"
  "github.com/jinzhu/gorm"
)

type DBORM struct {
  *gorm.DB
}

func NewORM(dbname, con string) (*DBORM, error) {
  db, err := gorm.Open(dbname, con)
  return &DBORM{
  DB: db,
  }, err
}

func (db *DBORM) GetAllProducts() (products []models.Product, err error) {
  return products, db.Find(&products).Error
}

func (db *DBORM) GetPromos() (products []models.Product, err error) {
  return products, db.Where("promotion IS NOT NULL").Find(&products).Error
}

func (db *DBORM) GetCustomerByName(firstname string, lastname string) (customer models.Customer, err error) {
  return customer, db.Where(&models.Customer{FirstName: firstname, LastName: lastname}).Find(&customer).Error
}

func (db *DBORM) GetCustomerByID(id int) (customer models.Customer, err error) {
  return customer, db.First(&customer, id).Error
}

func (db *DBORM) GetProduct(id int) (product models.Product, error error) {
  return product, db.First(&product, id).Error
}

func (db *DBORM) AddUser(customer models.Customer) (models.Customer, error) {
  hashPassword(&customer.Pass)
  customer.LoggedIn = true
  return customer, db.Create(&customer).Error
}

func (db *DBORM) SignInUser(email, pass string) (customer models.Customer, err error) {

  if !checkPassword(customer.Pass, pass) {
  return customer, errors.New("Invalid password")
  }

  result := db.Table("Customers").Where(&models.Customer{Email: email})

  err = result.Update("logged", 1).Error
  if err != nil {
  return customer, err
  }

  return customer, result.Find(&customer).Error
}

func (db *DBORM) SignOutUserById(id int) error {
  customer := models.Customer{
  Model: gorm.Model{
  ID: uint(id),
  },
  }
  return db.Table("Customers").Where(&customer).Update("loggedin", 0).Error
}

func (db *DBORM) GetCustomerOrdersByID(id int) (orders []models.Order, err error) {
  return orders, db.Table("orders").Select("*").Joins("join customers on customers.id = customer_id").Joins("join products on products.id = product_id").Where("customer_id=?", id).Scan(&orders).Error //db.Find(&orders, models.Order{CustomerID: id}).Error
}