我們的blog是巢狀的，當user輸入網址到對應的blog時，我們有兩種方式能找到對應的blog

1. 對字串進行分割，用join的方式比對往下找每個blog的name，直到找到最底層的blog
2. 直接將整個網址紀錄在table中，查找方便快速，但是如果上層的blog改了名字（網址跟動），要對下層所有blog的網址也一同跟新

由於大部分的情況query都是read比update，所以這邊使用第二種方法。

一樣先進入database，開始寫code

read

先從簡單的開始寫，如果是blog是project，那他下面就會有多個blog，這些也是我們需要的資訊，輸入

DELIMITER ;;
CREATE PROCEDURE `get_blog`(
  url varchar(750)
)
BEGIN

  SELECT owner.uniquename, owner.nickname, target.*, 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   blog AS target
         Left JOIN blog AS sub
           ON sub.super = target.bid
         JOIN owner
           ON owner.oid = target.oid
  WHERE  target.urlpath = url
  GROUP BY oid;

END ;;
DELIMITER ;

create

現在能發揮procedure方便的地方了，blog的父階層可以是owner或是blog，所以需要定義兩種狀況，我們已superid為0定義為在owner底下的情況

DELIMITER ;;
CREATE PROCEDURE `create_blog`(
  ownerid INT UNSIGNED,
  superid INT UNSIGNED,
  blogname varchar(100),
  descript varchar(255),
  typeid tinyint(3) unsigned,
  super_url varchar(649)
)
BEGIN

  SET @url = CONCAT(super_url, "/", blogname);
  
  IF superid = 0 THEN
    INSERT INTO `blog` (`oid`, `name`, `description`, `type`, `urlpath`) VALUES (ownerid, blogname, descript, typeid, @url);
  ELSE
    IF check_superBlog(ownerid, superid, super_url) = 1 THEN
      INSERT INTO `blog` (`oid`, `name`, `description`, `type`, `urlpath`, `super`) VALUES (ownerid, blogname, descript, typeid, @url, superid);
    ELSE
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'PARAMETER WRONG';
    END IF;
  END IF;
 
END ;;
DELIMITER ;

解釋：

• SIGNAL SQLSTATE用來回傳錯誤的訊號，45000是一个通用SQLSTATE值，用於說明未處理的用自訂異常。

check_superBlog是自訂的function，我們需要檢查一個父階層blog是否真的存在，需要檢查owner id(oid)，blog id(super id)，url (super_url)，寫入

DELIMITER ;;
CREATE FUNCTION `check_superBlog`(
  ownerid INT UNSIGNED,
  blogid INT UNSIGNED,
  url varchar(649)
) RETURNS tinyint(1)
    READS SQL DATA
BEGIN

  RETURN  EXISTS (SELECT 1
                  FROM   blog
                  WHERE  urlpath = url
                    AND  bid = blogid
                    AND  oid = ownerid
                    AND  type = 1);

END ;;
DELIMITER ;

update

當改變一個blog的名稱，其子blog與更往下延伸的blog都要修改path，假設沒有設定階層的上限，要如何用SQL做改動？我想到的方法有兩種

1. 使用cursor搭配巢狀procedure來做recursive
2. 使用with stament來做recursive

目前的情況還沒有複雜到需要使用cusor，這裡用with就好，輸入

DELIMITER ;;
CREATE PROCEDURE `update_blog`(
  ownerid INT UNSIGNED,
  superid INT UNSIGNED,
  newsuperid BIGINT,
  blogid INT UNSIGNED,
  blogname varchar(100),
  newname varchar(100),
  descript varchar(255),
  origin_url varchar(750),
  newsuper_url varchar(649)
)
BEGIN

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  IF NOT check_superBlog(ownerid, blogid, origin_url) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'PARAMETER WRONG';
  END IF;
    
  IF newsuperid <= -1 THEN
    SET @sid = superid;
  ELSE
    SET @sid = newsuperid;
  END IF;
  
  IF @sid <> 0 THEN
    IF check_superBlog(ownerid, @sid, newsuper_url) = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'PARAMETER WRONG';
    END IF;
  ELSE
    SET @sid = NULL;
  END IF;
  
  IF newname <> "" THEN
    SET @bname = newname;
  ELSE
    SET @bname = blogname;
  END IF;
  
  IF newname <> "" OR newsuperid >= 0 THEN
    SET @url = CONCAT(newsuper_url, "/", @bname);
    
    START TRANSACTION;
      UPDATE  `blog`
      SET     `blog`.`super` = @sid, `blog`.`description` = descript, `blog`.`name` = @bname, `blog`.`urlpath` = @url
      WHERE   `blog`.`bid` = blogid;

      WITH RECURSIVE blog_cte AS (
        SELECT blog.bid, blog.urlpath FROM blog WHERE bid = blogid
        UNION ALL
        SELECT blog.bid, CONCAT(blog_cte.urlpath, "/", blog.name) FROM blog_cte JOIN blog ON blog_cte.bid = blog.super
      ) UPDATE blog, blog_cte
      SET blog.urlpath = blog_cte.urlpath
      WHERE blog.bid = blog_cte.bid;
    COMMIT;
  ELSE
    UPDATE  `blog`
      SET   `blog`.`description` = descript
      WHERE `blog`.`bid` = blogid
        AND `blog`.`oid` = ownerid;    
  END IF;
 
END ;;
DELIMITER ;

解釋：

• 要是update的中途發生失敗，我們需要依靠transation來ROLLBACK，DECLARE EXIT HANDLER FOR SQLEXCEPTION宣告一個錯誤處理，如果發生錯誤就執行ROLLBACK，然後RESIGNAL來將錯誤回傳給client
• 因為mysql的procedure一定要全部傳參數，沒有可選的選項，所以這邊的new super id比較麻煩，我定義-1代表不更改。
• 先檢查是否擁有這個blog，如果要改到其他blog底下，也要檢查該blog是否合法
• 利用WITH RECURSIVE來更改blog底下的路徑改動，可以將blog_cte視為一個暫時性的table，利用UNION ALL來組合ROW，跑完後update進blog table

delete

DELIMITER ;;
CREATE PROCEDURE `del_blog`(
  ownerid INT unsigned,
  blogid INT unsigned,
  url VARCHAR(750)
)
BEGIN

  DELETE `blog`
  FROM `blog`
  JOIN `owner`
    ON `owner`.`oid` = `blog`.`oid`
      AND `owner`.`oid` = ownerid
  WHERE `blog`.`bid` = blogid
    AND `blog`.`urlpath` = url;

END ;;
DELIMITER ;

root

來考慮一下根目錄而非連到指定blog的情況，這時候就列出blog與該owner的資料但不顯示內文，另外也不顯示project，我們先以時間來排序，一次取10筆資料當作一頁

DELIMITER ;;
CREATE PROCEDURE `get_root`(
  page INT
)
BEGIN
  
  DECLARE offset INT;
  SET offset = (page - 1) * 10;
  SELECT owner.uniquename, owner.nickname, blog.*
  FROM blog
  JOIN owner
    ON owner.oid = blog.oid
  WHERE blog.createtime <= (
    SELECT blog.createtime
    FROM blog
    WHERE type <> 1
    ORDER BY blog.createtime DESC
    LIMIT offset, 1
  ) AND type <> 1
  ORDER BY blog.createtime DESC
  LIMIT 10;

END ;;
DELIMITER ;

解釋：

• 將offset設為(頁數-1) * 10，在where裡找出來後往前取10筆

總結

blog的CRUD完成了，明天來把他接上