[TYPO3-dev] FYI nested sets on the pages table

[Martin Kutschker]

Martin Kutschker schrieb:
> 
> PS: the fastest way to get a rootline is perhaps a stored procedure.

I'm no stored procedures crack so this may be complete rubbish. The 
procedure uses a temp. table to get the correct sorting order for the 
final select. It is fun, but is it faster? PHP will only issue one 
statement (the procedure CALL), but the server uses one additional 
statement and has to maintain a temporary table.

Masi

DELIMITER $$
CREATE PROCEDURE typo3_getrootline (IN uid INT, IN columns VARCHAR(255))
LANGUAGE SQL
READS SQL DATA
BEGIN
  DECLARE cnt MEDIUMTEXT DEFAULT 1;

  /* temp. table to make
  CREATE TEMPORARY TABLE IF NOT EXISTS typo3_getrootline_order (_uid 
INT, _sorting INT) ENGINE=Memory;
  TRUNCATE TABLE typo3_getrootline_order;

  /* get ids of rootline */
  PREPARE typo3_getrootline_stmt FROM 'SELECT pid INTO 
@typo3_getrootline_pid FROM pages WHERE uid = ?';
  SET @typo3_getrootline_uid = uid;
  SET @typo3_getrootline_cnt = 1;
  INSERT INTO typo3_getrootline_order VALUES (@typo3_getrootline_uid, 
@typo3_getrootline_cnt);
  rootline: LOOP
   EXECUTE typo3_getrootline_stmt USING @typo3_getrootline_uid;
   SET @typo3_getrootline_uid = @typo3_getrootline_pid;

   IF @typo3_getrootline_uid = 0 THEN LEAVE rootline; END IF;

   SET @typo3_getrootline_cnt = @typo3_getrootline_cnt + 1;
   INSERT INTO typo3_getrootline_order VALUES (@typo3_getrootline_pid, 
@typo3_getrootline_cnt);
  END LOOP;

  /* get real rootline */
  SET @typo3_getrootline_query = CONCAT('SELECT uid,pid,',columns,' FROM 
typo3_getrootline_order, pages WHERE _uid=uid ORDER BY _sorting');
  PREPARE typo3_getrootline_stmt FROM @typo3_getrootline_query;
  EXECUTE typo3_getrootline_stmt;

  DEALLOCATE PREPARE typo3_getrootline_stmt;
END$$

/* demo usage */
CALL typo3_getrootline(17,'title')$$