Forum PHP.pl > drzewka for bsp

Pomoc - Szukaj - Użytkownicy - Kalendarz

Forum PHP.pl > Forum > Bazy danych > PostgreSQL

Jabol

10.07.2003, 18:56:00

dobra, mam kod na postgresa i dziala z tym usuwanie bardzo slicznie

[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

11.07.2003, 22:22:19

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

Jabol

12.07.2003, 15:43:43

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";

Jabol

13.07.2003, 14:57:06

Troche mnie nie bedzie od jutra wiec jakby ktos chcial zobaczyc co do tej pory wyskrobalem to ponizej podaje link do wyniku polecenia `pg_dump -f php_pl_dump.sql -d -D -O -R -x postgres`. Zrobione jest uzytkownika php_pl i dla niego tworzy "schema" (jak to jest po polsku?), wiec jezeli ktos by chcial uruchomic to trzeba najpierw dodac takiego usera a potem wystarotwac ten kod. Link do kou: http://adamjabol.w.interia.pl/php_pl_dump.sql

Ps. skrypt trzeba troche przerobic i pierwsze instrukcje (create function plpgsql_call_handler i create language wykonac jako admin)

Ps.2 Dla zgodnosci z adodb nalezy zmienic metode uzyskiwania auto_increment z default nextval('xxx'); na SERIAL. Jak wroce to to zmienie.

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.