iT邦幫忙

第 12 屆 iT 邦幫忙鐵人賽

DAY 12
0
Modern Web

從coding到上線-打造自己的blog系統系列 第 12

Day12 Blog CRUD Procedure

我們的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完成了,明天來把他接上


上一篇
Day11 模板
下一篇
Day13 Blog API串接
系列文
從coding到上線-打造自己的blog系統30

尚未有邦友留言

立即登入留言