drzewka for bsp Opcje

[Jabol]

dobra, mam kod na postgresa i dziala z tym usuwanie bardzo slicznie (IMG:http://forum.php.pl/style_emoticons/default/biggrin.gif)

[SQL] pobierz, plaintext 
CREATE SEQUENCE categories_increment
INCREMENT 1
START 1
MINVALUE 1;
 
CREATE TABLE categories (
id BIGINT NOT NULL DEFAULT NEXTVAL('categories_increment'),
parent BIGINT,
name VARCHAR(100) DEFAULT NULL,
UNIQUE (parent, name)
);
 
ALTER TABLE categories ADD PRIMARY KEY (id);
 
CREATE INDEX parent_ind ON categories (parent);
 
ALTER TABLE categories ADD FOREIGN KEY (parent) REFERENCES categories (id) ON DELETE CASCADE;
 
CREATE TABLE asociations (
first_id BIGINT NOT NULL DEFAULT 0,
second_id BIGINT NOT NULL DEFAULT 0,
depth BIGINT NOT NULL DEFAULT 0
);
 
ALTER TABLE asociations ADD PRIMARY KEY (first_id, second_id);
 
CREATE INDEX f_ind ON asociations (first_id);
 
CREATE INDEX s_ind ON asociations (second_id);
 
ALTER TABLE asociations ADD FOREIGN KEY (second_id) REFERENCES categories (id) ON DELETE CASCADE;
 
ALTER TABLE asociations ADD FOREIGN KEY (first_id) REFERENCES categories (id) ON DELETE CASCADE;
 
INSERT INTO categories (parent, name) VALUES (NULL,'sql');
INSERT INTO categories (parent, name) VALUES (1,'postgresql');
INSERT INTO categories (parent, name) VALUES (1,'oracle');
INSERT INTO categories (parent, name) VALUES (2,'linux');
INSERT INTO categories (parent, name) VALUES (3,'sco');
INSERT INTO categories (parent, name) VALUES (3,'linux');
INSERT INTO categories (parent, name) VALUES (7,'windows');
INSERT INTO categories (parent, name) VALUES (6,'glibc1');
INSERT INTO categories (parent, name) VALUES (6,'glibc2');
 
INSERT INTO asociations VALUES (1,1,0);
INSERT INTO asociations VALUES (1,2,1);
INSERT INTO asociations VALUES (1,3,1);
INSERT INTO asociations VALUES (1,4,2);
INSERT INTO asociations VALUES (1,5,2);
INSERT INTO asociations VALUES (1,6,2);
INSERT INTO asociations VALUES (1,7,2);
INSERT INTO asociations VALUES (1,8,3);
INSERT INTO asociations VALUES (1,9,3);
INSERT INTO asociations VALUES (2,2,0);
INSERT INTO asociations VALUES (2,4,1);
INSERT INTO asociations VALUES (3,3,0);
INSERT INTO asociations VALUES (3,5,1);
INSERT INTO asociations VALUES (3,6,1);
INSERT INTO asociations VALUES (3,7,1);
INSERT INTO asociations VALUES (3,8,2);
INSERT INTO asociations VALUES (3,9,2);
INSERT INTO asociations VALUES (4,4,0);
INSERT INTO asociations VALUES (5,5,0);
INSERT INTO asociations VALUES (6,6,0);
INSERT INTO asociations VALUES (6,8,1);
INSERT INTO asociations VALUES (6,9,1);
INSERT INTO asociations VALUES (7,7,0);
INSERT INTO asociations VALUES (8,8,0);
INSERT INTO asociations VALUES (9,9,0);
[SQL] pobierz, plaintext 

Zrobile pare przemian. Zmienilem typ na BIGINT oraz pozmienialem nazwy na angielskie. Teraz jeszcze poproboje sie pobawic w przenoszenie :? i takie tam.

[Jabol]

a co powiecie na to?

Kod

--mkdir, tworzy katalog

CREATE FUNCTION mkdir (VARCHAR(100), BIGINT) RETURNS BOOLEAN AS '

INSERT INTO categories (name, parent) VALUES ($1, $2);

INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, (SELECT id FROM categories WHERE name = $1 AND parent = $2), depth + 1 FROM asociations WHERE second_id = $2;

INSERT INTO asociations (first_id, second_id, depth) VALUES((SELECT id FROM categories WHERE name = $1 AND parent = $2), (SELECT id FROM categories WHERE name = $1 AND parent = $2), 0);

SELECT true;

' LANGUAGE "sql";



--get_path, pobiera sciezke do katalogu

CREATE FUNCTION get_path (BIGINT) RETURNS TEXT AS '

DECLARE

cid ALIAS FOR $1;

path TEXT;

item RECORD;

BEGIN

path := '/';

FOR item IN SELECT c.name FROM categories c, asociations a WHERE c.id = a.first_id AND a.second_id = cid ORDER BY a.depth DESC LOOP

path := path || item.name || '/'::TEXT;

END LOOP;

RETURN path;

END;

' LANGUAGE "plpgsql";

To jest kod w sql'u i plpgsql'u zoptymalizowany dla postgres'q ale mozna z niego biorac przyklad zrobic inny, np. php.

Ps. kod jest w znacznikach code bo w sql'u brzydko wygladal