WN - Mysql - stored procedures (hierarkisk data)

WN (https://www.wn.se/forum/index.php)

- Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)

- - Mysql - stored procedures (hierarkisk data) (https://www.wn.se/forum/showthread.php?t=1058051)

Mysql - stored procedures (hierarkisk data)

Hej alla!

Har hyfsad koll på mysql, men stored procedures är lite nytt för mig.

Jag vet inte hur jag ska börja med följande problem. Jag ska spara en katalogstruktur enl nedanstående klassiska exempel:

Katalog
id - int
parent_id - int
name - varchar

Jag vill med hjälp av en SP spara en hel sökväg i databasen. Om delar av databasen redan finns så ska den uppdatera den. Exempelvis om sökvägen /människor/man/petter ska sparas men /människor redan finns så ska människor lämnas orörd och bara uppdateras med /man/petter. Slutligen vill jag returnera id'd på petter (dvs lövet på grenen).

Någon som har tips om hur jag kan gå tillväga?

Syftet med detta är att ta bort en del av logiken från PHP för att på så vis spara lite tid genom att bara skicka en fråga till databasen.

Jag har inte en komplett lösning men lite procedurer och funktioner på vägen...

Kod:

delimiter $$



CREATE DEFINER=`root`@`localhost` FUNCTION `substrCount`(s VARCHAR(255), ss VARCHAR(255)) RETURNS tinyint(3) unsigned

    READS SQL DATA

BEGIN

 DECLARE count TINYINT(3) UNSIGNED;

 DECLARE offset TINYINT(3) UNSIGNED;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

 SET count = 0;

 SET offset = 1;

 REPEAT

 IF NOT ISNULL(s) AND offset > 0 THEN

 SET offset = LOCATE(ss, s, offset);

 IF offset > 0 THEN

 SET count = count + 1;

 SET offset = offset + 1;

 END IF;

 END IF;

 UNTIL ISNULL(s) OR offset = 0 END REPEAT;

 RETURN count;

 END$$

Kod:

delimiter $$



CREATE DEFINER=`root`@`localhost` FUNCTION `stringSplit`(

 x VARCHAR(255),

 delim VARCHAR(12),

 pos INT) RETURNS varchar(255) CHARSET latin1

RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),

 LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '')$$

Kod:

delimiter $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `splitter`(x varchar(255), delim varchar(12))

BEGIN

 SET @Valcount = substrCount(x,delim)+1;

 SET @v1=0;

 SET @v2=0;

 drop temporary table if exists _splitResults;

 create temporary table _splitResults 

 (

                id int unsigned NOT NULL PRIMARY KEY, 

                parent_id int default null, 

                split_value varchar(255) not null

 ) ENGINE=Memory;



 WHILE (@v1 < @Valcount) DO

 set @val = stringSplit(x,delim,@v1+1);

 IF @v1 >0 THEN

        BEGIN

        SET @v2 = @v2 +1;

         INSERT INTO _splitResults (id, parent_id,split_value) VALUES (@v1+1, @v2,@val);

        END;

        ELSE

        BEGIN 

         INSERT INTO _splitResults (id, parent_id,split_value) VALUES (@v1+1, NULL,@val);

        END;

 end if;

 SET @v1 = @v1 + 1;

 END WHILE;

select * from _splitResults;

 END$$

Slutligen lite testkod hur man använder dessa....

Kod:

delimiter ;

use wn;



-- http://www.montrealseocompany.com/2012/04/17/mysql-split-string-into-rows-function/



call  splitter('mange/gurra/hanna/olle/greta/sven/lena/nisse','/');



-- select * from _splitresults;



select * from _splitresults;

drop table _splitresults;

Vad som återstår är att göra en Procedur som kanske heter StoreStructure() och som i sin tur anropar Splitter() och sparar resultatet i en permanent tabellstruktur.

Här är ett förslag på lösning av proceduren Storestructure() med lite testkod. Det finns massor av optimeringar att göra om man vill det, men det har jag inte ens funderat på ännu. Detta löser i vart fall TS problem, om jag fattat det rätt.

Jag har kört med MySQL Workbench CE for Windows version 5.2.47 revision 10398.

SHOW VARIABLES LIKE "%version%"; -- Ger följande resultat....

innodb_version 1.1.8
protocol_version 10
slave_type_conversions
version 5.5.29
version_comment MySQL Community Server (GPL)
version_compile_machine x86
version_compile_os Win32

DDL-script för tabellen "katalog":

Kod:

delimiter $$



CREATE TABLE `katalog` 

(

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `parent_id` int(11) DEFAULT NULL,

  `name` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `katalog_parent_id_idx` (`parent_id`),

  CONSTRAINT `katalog_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `katalog` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

SP: StoreStructure()

Kod:

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `StoreStructure`(path varchar(255), delim varchar(12))

BEGIN

        DECLARE done INT DEFAULT FALSE;

        DECLARE RowCount int;

        DECLARE RowPtr int;

        DECLARE katalog_id int;

        DECLARE katalog_parent_id int;

        DECLARE next_parent_id int;

        DECLARE split_name varchar(45);



        set RowPtr=0;

        call splitter(path, delim);

        select count(*) INTO RowCount FROM _splitresults;

        WHILE RowCount > RowPtr DO

                select split_value INTO split_name FROM _splitresults ORDER BY id ASC LIMIT RowPtr,1;

                -- Om det inte finns någon split_parent_id så är det en root-nivå

                IF next_parent_id is null THEN

                        select id INTO katalog_id   from katalog WHERE parent_id is null AND `name`= split_name;

                        -- Kolla om namnet finns i databasen sedan tidigare på rootnivån

                        IF katalog_id is null THEN

                                -- Om den inte finns i databasen på root-nivå så är det bara att lägga till den

                                INSERT INTO katalog(`name`) VALUES (split_name);

                        END IF;

                        -- Läs upp den från databasen för att få tag i databasens id

                        select id, parent_id INTO katalog_id, katalog_parent_id   from katalog WHERE parent_id is null AND `name`= split_name;

                ELSE

                        -- Eftersom det finns en split_parent_id så är det en child-nivå

                        select id INTO katalog_id   from katalog WHERE parent_id = next_parent_id AND `name`= split_name;

                        -- Kolla om namnet finns i databasen sedan tidigare på denna child-nivå

                        IF katalog_id is null THEN

                                -- Posten finns inte sedan tidigare

                                -- Om den inte finns i databasen på root-nivå så är det bara att lägga till den

                                INSERT INTO katalog(`parent_id`, `name`) VALUES (next_parent_id, split_name);

                        END IF;

                        -- Läs upp den från databasen för att få tag i databasens id

                        select id, parent_id INTO katalog_id, katalog_parent_id   from katalog WHERE parent_id = next_parent_id AND `name`= split_name;

                END IF;

                -- Här måste vi ta vara på katalog_id för det blir parent_id för nästa nivå

                set next_parent_id=katalog_id;

                set katalog_id = null;

                set katalog_parent_id = null;

                set RowPtr = RowPtr + 1;

        END WHILE;

END

Testkod:

Kod:

use wn;



call StoreStructure_Delete();



call StoreStructure('olle11/kalle12/anna13','/');

call StoreStructure('kalle21/anna22/olle23','/');

call StoreStructure('anna31/olle32/kalle33','/');



call StoreStructure('olle11/sara12/greta13','/');

call StoreStructure('kalle21/anna22/gunnar23','/');

call StoreStructure('anna31/rune32/kalle33','/');

call StoreStructure('lena31/olle32/sara33','/');



select * from katalog;

select parent_id from katalog;

select parent_id from katalog WHERE NOT PARENT_ID IS NULL;

Om du vill radera alla poster i tabellen katalog så måst eman ta bort dem underifrån eftersom det är en forreign key constreint mellan parent_id och id i tabellen.

Jag slängde ihop en SP som fixar borttag av alla tuplerna på ett enkelt sätt:

Kod:

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `StoreStructure_Delete`()

BEGIN

        DECLARE delete_id int;

        DECLARE RowCount int;



        select count(*) as c INTO RowCount from katalog;

        WHILE RowCount > 0 DO

                select ID INTO delete_id from katalog where id NOT in (select parent_id from katalog WHERE NOT PARENT_ID IS NULL) ORDER BY ID DESC LIMIT 0,1;

                DELETE FROM katalog WHERE id = delete_id;

                commit;

                -- select count(*) as c INTO RowCount from katalog;

                set RowCount = RowCount -1;

        END WHILE;

END

Jisses Conny! Är det någon som lägger ordentligt med tid på att svara på frågor så...

Ska analysera det hela lite närmare och testa lite under kvällen.

STORT tack för detta!

Citat:

Ursprungligen postat av gregoff (Inlägg 20471386)

Jisses Conny! Är det någon som lägger ordentligt med tid på att svara på frågor så...

Ska analysera det hela lite närmare och testa lite under kvällen.

STORT tack för detta!

Hoppas det funkar som du vill ha det....

Jag gillar såna här problem att lösa, det är som att lösa korsord eller en SUDOKU...

Det ger mig även chansen att sätta mig in i databasen MySQL och det var ett antal brister i databasen som gjorde att jag bland annat fick förkasta ett par alternativa lösningar.

Bland annat så saknar MySQL möjligheten att skapa tabeller som variabler och returnera det från s.k. Table-value-functions vilket man kan i MS SQL. Så det gör att man måste använda temporära memory-tabeller i stället, vilket är besvärligare. Det skulle säkert sparat 50-75 procent av koden.

Sen hade jag en variant med en SQL Cursor som jag fick förkasta för jag fick aldrig ordning på hur cursorn.

Men med tre nivåer i en struktur så tar det 16 ms att köra StoreStructure() på min gamla Lenovo T410 så det får väl vara godkända prestanda tills vidare, med tanke på att jag inte gjort några optimeringar alls ännu.

Slängde även ihop en testprocedur:

Kod:

delimiter $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `StoreStructure_Test`()

BEGIN

        call StoreStructure_Delete();



        call StoreStructure('olle11/kalle12/anna13','/');

        call StoreStructure('kalle21/anna22/olle23','/');

        call StoreStructure('anna31/olle32/kalle33','/');



        call StoreStructure('olle11/sara12/greta13','/');

        call StoreStructure('kalle21/anna22/gunnar23','/');

        call StoreStructure('anna31/rune32/kalle33','/');

        call StoreStructure('lena31/olle32/sara33','/');



        call StoreStructure('a.b.c.d.e.f.g.h.i.j','.');

        call StoreStructure('b.c.d.e.f.g.h.i.j.k','.');

        call StoreStructure('c.d.e.f.g.h.i.j.k.l','.');

        call StoreStructure('d.e.f.g.h.i.j.k.l.m','.');

        call StoreStructure('e.f.g.h.i.j.k.l.m.n','.');

        call StoreStructure('f.g.h.i.j.k.l.m.n.o','.');

        call StoreStructure('g.h.i.j.k.l.m.n.o.p','.');

        call StoreStructure('h.i.j.k.l.m.n.o.p.q','.');

        call StoreStructure('i.j.k.l.m.n.o.p.q.r','.');

        call StoreStructure('j.k.l.m.n.o.p.q.r.s','.');

END$$

... och en procedur för profiling, dvs att kolla upp flaskhalsar vid optimering....

Kod:

delimiter $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `StoreStructure_Profling`()

BEGIN

        set profiling = 1;

        call StoreStructure_Test();

        show profiles;

        SHOW PROFILE FOR QUERY 1275; -- Här får du kolla manuellt vilken QUERY du ska så upp...

        set profiling = 0;

END$$

Profilern visar genomloppstiden för varje sats ner på nanosekundnivå (miljondelar av sekunder).

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20471394)

Sen hade jag en variant med en SQL Cursor som jag fick förkasta för jag fick aldrig ordning på hur cursorn.

Nu tror jag inte det fungerar i MySQL men i MSSQL har man ju ofta övergett cursor till förmån för CTE-tekniken som e så underbar. :)

I MSSQL har man även tabled valued functions och då skulle jag kunnat åstadkomma detta på några enstaka rader SQL-kod.

MSSQL kan man ju köra en string.split och returnera table value... Då blir det väl typ 2 rader kod eller så....

Tack alla för alla svar!

Jag har lagt logiken i min PHP-kod istället, får värma upp mig lite på stored procedures först innan jag använder det.