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]

MAM FUNKCJE MV !!!
Wow, ale sie podniecilem... Ale mysle, ze warto. Bo w koncu mam funkcje mv (do przenoszenia)!!!

Kod

CREATE FUNCTION mv(BIGINT, BIGINT) RETURN BOOLEAN AS '

DECLARE



cid ALIAS FOR $1;

np ALIAS FOR $2;

temp1 RECORD;

temp2 RECORD;



BEGIN



    FOR temp1 IN SELECT MAX(depth) AS max, second_id FROM asociations WHERE first_id = cid GROUP BY second_id LOOP



        DELETE FROM asociations

        WHERE second_id = temp1.second_id

        AND depth > temp1.max;



    END LOOP;



    UPDATE categories SET parent = np WHERE id = cid;



    INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, cid, depth + 1 FROM asociations WHERE second_id = np;



    FOR temp2 IN SELECT second_id, depth FROM asociations WHERE first_id = cid AND depth <> 0 LOOP



        INSERT INTO asociations (first_id, second_id, depth)

        SELECT first_id, temp2.second_id, depth + temp2.depth

        FROM asociations

        WHERE second_id = cid AND depth <> 0;



    END LOOP;



RETURN true;



END;

' LANGUAGE "plpgsql";