從昨天的架構可以知道有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接上