Data #tree using #sql

SQL 2.0.sql

drop table if exists categorytree;
drop table if exists category;

create table category (
id int not null AUTO_INCREMENT,
name varchar (50) not null,
primary key(id) 
);


create table categorytree(
parent_id int not null,
child_id int not null ,
depth int,
FOREIGN KEY (parent_id) references category(id) ON DELETE CASCADE,
FOREIGN KEY (child_id) references category(id) ON DELETE CASCADE
);

drop procedure if exists addcategory;

delimiter //
create procedure addcategory (IN name varchar(50), IN parent int unsigned, in parentbyname varchar(50))
begin
declare cat_id int unsigned;


if parentbyname is not null then 

  select id into parent
					 from category c
					where c.name = parentbyname;
           
end if;

Insert INTO category (name) VALUES (name);
select LAST_INSERT_ID() into cat_id;
INSERT INTO categorytree(parent_id, child_id, depth) VALUES(cat_id,cat_id,0);

if parent is not null then
		INSERT INTO categorytree(parent_id, child_id, depth) 
			SELECT parent_id, cat_id, depth+1 from categorytree WHERE child_id = parent;
end if;

end//
delimiter ;

CAll addcategory('kategoria', null, null);
CAll addcategory('sub', null, 'kategoria');
CAll addcategory('sub2', null, 'sub');
CAll addcategory('sub3', null, 'sub2');
CAll addcategory('sub4', null, 'sub3');
CAll addcategory('sub5', null, 'sub4');


drop function if exists path_cat;
/*
delimiter //

create function path_cat(cat_id int) 
returns varchar(255)

begin
declare row1_name varchar(50);
declare reply varchar(255);
declare c1_done int default false;
declare c1 cursor for select c.name from category c join categorytree ct on c.id = ct.parent_id where ct.child_id = cat_id order by ct.depth desc;
declare CONTINUE HANDLER FOR NOT FOUND SET c1_done = TRUE;
set reply := '/'; 

open c1;
read_loop: loop
fetch c1 into row1_name ;
if c1_done then leave read_loop;
end if;
select concat(reply,row1_name,'/') into reply;
end loop;
close c1;
return reply;
end; //
DELIMITER ;
*/

delimiter //
create function path_cat(cat_id int)
returns varchar(255)

begin

select group_concat(c.name order by ct.depth desc separator '/') into @var from category c join categorytree ct on c.id = ct.parent_id where ct.child_id = cat_id ;
return @var;
end;
//

drop procedure if exists del_cat;


delimiter //
create procedure del_cat(cat_id int)
begin
delete con, cc from categorytree con 
join categorytree ct on con.child_id = ct.child_id
join category cc on con.child_id = cc.id
where con.parent_id = cat_id;
end;
//

/* call del_cat(2); */