iT邦幫忙

第 12 屆 iT 邦幫忙鐵人賽

DAY 9
0

從昨天的架構可以知道有user, owner, blog等主體需要CRUD,今天來寫owner所需要的資料庫操作

我們已經建好資料庫了,所以直接用docker跑起來,不需要再給-e參數,輸入

docker run -v $(pwd)/database/maindata:/var/lib/mysql -p 3306:3306 -d mysql --name mysqlcontainer

啟動容器

owner

接著進入容器

docker exec -it mysqlcontainer /bin/bash

使用mysql

mysql -u root -pyour_password

選擇database

use app;

再來要開始寫procedure

create

一個owner需要他的user id, 用於網址的唯一名稱(uniquename),對外展示的可重複名稱(nickname),以及簡短介紹(description)

輸入

DELIMITER ;;
CREATE PROCEDURE `create_owner`(
  userid INT UNSIGNED,
  owner_name VARCHAR(50),
  nick_name VARCHAR(50),
  descript varchar(255)
)
BEGIN

  INSERT INTO `owner` (`uid`, `nickname`, `uniquename`, `description`) VALUES (userid, nick_name, owner_name, descript);

END ;;
DELIMITER ;

read

只要輸入網址(uniquename)就能得到owner的資料,但資料不只owner這個table,我們還需要知道他有什麼blog,blog需要送到前端的資料有blog id(bid), blog名稱(name), blog簡介(description), 建立與跟新時間(createtime, updatetime)
輸入

DELIMITER ;;
CREATE PROCEDURE `get_owner`(
  name varchar(50)
)
BEGIN

  SELECT owner.*, GROUP_CONCAT(sub.bid SEPARATOR "  ") AS subBid, GROUP_CONCAT(sub.name SEPARATOR "  ") AS subBname, GROUP_CONCAT(sub.description SEPARATOR "  ") AS subBdescription, GROUP_CONCAT(sub.createtime SEPARATOR "  ") AS subBcreatetime, GROUP_CONCAT(sub.updatetime SEPARATOR "  ") AS subBupdatetime
  FROM   owner
         Left JOIN blog AS sub
           ON sub.oid = owner.oid
             AND sub.super IS NULL
  WHERE  owner.uniquename = name;

END ;;
DELIMITER ;

解釋:

  • 因為一個owner底下會有多個blog,所以用GROUP_CONCAT串起來,之後再用字串處理分隔開

update

跟新需要檢查uid,oid與原來網址的uniquename,預防有人用別人的oid去做update,uid要避免被盜用就需要權限檢查來管理,之後會提到

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_owner`(
  userid INT UNSIGNED,
  ownerid INT UNSIGNED,
  owner_name VARCHAR(50),
  newo_name VARCHAR(50),
  nick_name VARCHAR(50),
  descript varchar(255)
)
BEGIN

    UPDATE `owner`
    SET `owner`.`nickname` = nick_name, `owner`.`description` = descript, `owner`.`uniquename` = newo_name,
    WHERE `owner`.`oid` = ownerid
    AND `owner`.`uid` = userid
    AND `owner`.`uniquename` = owner_name;
  
END ;;
DELIMITER ;

delete

基本與update相同

DELIMITER ;;
CREATE PROCEDURE `del_owner`(
  userid INT UNSIGNED,
  ownerid INT unsigned,
  owner_name VARCHAR(50)
)
BEGIN

  DELETE FROM `owner`
  WHERE `owner`.`oid` = ownerid
    AND `owner`.`uid` = userid
    AND `owner`.`uniquename` = owner_name;

END ;;
DELIMITER ;

sql寫完了,現在就要用go去呼叫了,到scheme.go裡面補上需要output的struct

type OwnerOut struct {
	Owner   `xorm:"extends"`
	BlogSub `xorm:"extends"`
}

type BlogSub struct {
	SubBid         string `json:"subBid" xorm:"VARCHAR(512) 'subBid'"`
	SubName        string `json:"subBname" xorm:"VARCHAR(512) 'subBname'"`
	SubDescription string `json:"subBdescription" xorm:"VARCHAR(512) 'subBdescription'"`
	SubCreatetime  string `json:"subBcreatetime" xorm:"VARCHAR(512) 'subBcreatetime'"`
	SubUpdatetime  string `json:"subBupdatetime" xorm:"VARCHAR(512) 'subBupdatetime'"`
}

解釋:

  • extend能讓xorm知道這是embedding struct

總結

今天先體驗procedure,明天再把這些procedure接上


上一篇
Day8 資料庫架構
下一篇
Day10 Owner API串接
系列文
從coding到上線-打造自己的blog系統30

尚未有邦友留言

立即登入留言