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

[Ries van Twisk]

Hey martin,

would the SP not be dangerous ?

The table get's created and used in a multi user environment
so under some conditions I think the values of the table
get's overwritten but other values.

You could modify the SP below using the nested set's fields
which will directly give you the correct sorting which
will eliminate the need for the temporary table.

In any case... it's a step forward...

Ries


> 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')$$
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

-- 
Ries van Twisk
Freelance Typo3 Developer
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk