Aktualizując ostanio serwer miałem problem z upgrade na nowszą wersje PostgreSQLa więc opiszę swoje rozwiązanie na blogu.

Jeśli mamy już zainstalowaną wcześniejszą wersje PostgreSQLa to robimy backup.

pg_dumpall > postgres-backup.dump

Następnie wrzucamy do /etc/portage/package.keywords wpisy w celu odblokowania wersji niestabilnych.

echo "dev-db/postgresql-base ~amd64" >> /etc/portage/package.keywords
echo "dev-db/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-base ~amd64" >> /etc/portage/package.keywords
echo "app-admin/eselect-postgresql ~amd64" >> /etc/portage/package.keywords

Kolejnym etapem jest usunięcie dev-db/postgresql dev-db/libpq

emerge --unmerge dev-db/postgresql dev-db/libpq

I możemy instalować nową wersję PostgreSQLa

emerge virtual/postgresql-base virtual/postgresql-server
emerge --config =dev-db/postgresql-server-8.3.5

Zostaje tylko wystartowanie bazy i dopisanie do skryptów startowych.

/etc/init.d/postgresql-8.3 start
rc-update add postgresql-8.3 default

Jeśli z jakiś przyczyn php wyrzuca, że nie chce działać to musimy przekompilować.

Podobnie jak Kasia - moja dziewczyna link, wrzuciłem na bloga (http://blog.chylek.pl/muzyczne-wypady/) listę koncertów - imprez muzycznych, w których miałem zaszczyt uczestniczyć. Z biegiem czasu będę aktualizował tą listę, jak i uzupełnie koncerty na których byłem kilka lat temu - na pewno pomocny będzie google

Moja dzisiejsza notatka będzie pewnym przykładem rozwiązania problemu, z którym często się spotykam, ale z pewnych ograniczeń, postanowiłem to rozwiązać trochę w inny sposób. Metoda ta oczywiście znajduje się poniżej.

Często z baz danych usuwamy rekordy, które są odzwierciedleniem obrazków na dysku, ale jak usunąć rekord a wraz z nim plik zależny, który nie zawsze znajduje się fizycznie na tej samej maszynie? Dlaczego akurat uzależniać, usuwanie pliku od bazy danych? Powodów może być kilka, np wiele powtarzających się mechanizmów obsługujących ten sam mechanizm. Wydawać by się mogło że refaktoryzacja kody to załatwi ale niestety różnie to bywa i często do ideału daleka droga.

Przykład z życia – obliczamy punkty fotek w funkcji plpgsql a następnie niepotrzebne rekordy są usuwane wraz z fotkami zapisanymi fizycznie na dysku, oczywiście PHP w tym momencie nie ma dostępu do tego rekordu.

Konkrety: PostgreSQL i język proceduralny

Baza danych taka jak PostgreSQL, umożliwia obsługę wielu proceduralnych języków np. :

• pl/pgsql
• pl/tcl
• pl/python
• pl/perl
• pl/sh

Dla przykładu wykorzystam pl/python

Załóżmy, że mamy tabele images z polami, id, created_at, name, path, gdzie path bedzie scieżka na naszym serwerze

CREATE TABLE images
(
   id serial,
   created_at timestamp without time zone DEFAULT NOW(),
   image_name character varying(100) NOT NULL,
   path character varying(255) NOT NULL,
   CONSTRAINT pkey_id PRIMARY KEY (id)
);

Usuwając rekord z tabeli images chcemy usunąć plik fizycznie z dysku. Pierwszą rzeczą jest dodanie obsługi języka plpython, oczywiście z poziomu superuser’a.

CREATE LANGUAGE plpythonu;

Pozostaje nam tylko stworzenie triggera, który wywoła funkcję usuwania pliku z dysku po usunięciu rekordu.

CREATE OR REPLACE FUNCTION delete_images()
RETURNS trigger AS
'
  import os
  os.unlink( TD["old"]["path"] )

  return "OK"
'
LANGUAGE 'plpythonu' VOLATILE;

CREATE TRIGGER delete_images_trigger
BEFORE DELETE
ON images
FOR EACH ROW
EXECUTE PROCEDURE delete_images();

Pozostaje nam jeszcze dodać przykładowy rekord a następnie go usunąć

INSERT INTO images (name, path) values ('test', '/home/songoq/pg_test/a')

Dla testu tworzymy plik na dysku

touch /home/songoq/pg_test/a

Usuwamy rekord

DELETE FROM images WHERE id = 1

Przykład można bardziej rozbudować o obsługę błędów, sprawdzanie czy plik istnieje lub poprzez wywołanie shella usunąć go z zewnętrznego serwera, ograniczeniem jest tylko wyobraźnia programisty

Lazy load (wzorzec projektowy) – w przypadku Propela oznaczenie atrybutu modelu lazyLoad powoduje, że zostanie on zwrócony w momencie jego jawnego wywołania. Wykorzystuje się to w przypadku jeśli nie potrzebujemy danego atrybutu (z powodu jego rozmiaru) lub chcemy wykorzystać w późniejszym etapie (w widoku). Najczęściej stosuje się to do typów text (longvarchar), blob, clob.

Definicja w schema.yml

schema.yml

documents:
  id:         { type: integer, required: true, primaryKey: true, autoincrement: true }
  created_at: { type: timestamp }
  name:       { type: varchar, size: 100 }
  content:    { type: longvarchar, lazyLoad: true }

Pobieramy obiekt documents, atrybut content ma ustawionym lazyLoad, zostaje on pominięty w zapytaniu SQL. Jeśli wywołamy metodę ->getContent(), zostanie wykonane dodatkowe zapytanie i pole zostanie zwrócone. Lazy load można wykorzystać w celu zoptymalizowania obiektów Propela, jednak trzeba pamiętać że w momencie żądania zwrócenia atrybutu zostanie wykonane dodatkowe zapytanie do bazy danych.

Szybkie serwisy, zoptymalizowane pod względem zużycia pamięci i ilości odwołań do bazy – czasami mogą być kluczowym elementem w powodzeniu naszego projektu. Chciałbym przedstawić jedną z takich możliwości, czyli użycie w Symfony widoków baz danych. Przykład można zastosować w bazach: PostgreSQL, MySQL, Oracle, itd. Najważniejszym elementem jest to, czy baza danych obsługuje widoki.

Dla przykładu użyję tabeli użytkownik i grupa.

schema.yml

propel:

  groups:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    owner_id:        { type: integer, foreignTable: users, foreignReference: id, index: true }
    last_user_id:    { type: integer, foreignTable: users, foreignReference: id, index: true }
    name:            { type: varchar, size: 80, index: true }
    routing_name:    { type: varchar, size: 80, index: true, uniq: true }
    description:     { type: varchar, size: 400 }
    created_at:      { type: timestamp }
    user_count:      { type: integer, index: true }
    position_rank:   { type: integer }

  users:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    login:           { type: varchar, size: 15, index: true, uniq: true }
    password:        { type: varchar, size: 255 }
    name:            { type: varchar, size: 255 }
    created_at:      { type: timestamp }
    updated_at:      { type: timestamp }
    last_login_at:   { type: timestamp }
    last_request_at: { type: timestamp }
    group_id:        { type: integer, foreignTable: groups, foreignReference: id, index: true }

Załóżmy, że mamy template w którym pokazujemy 20 grup według ilości użytkowników, w której wyświetlamy: nazwę grupy wraz z linkiem do profilu grupy, właściciela grupy, ostatniego dodanego użytkownika do tej grupy i ilość użytkowników w grupie. Stosując klasyczne wykorzystanie modelów Propela, dostaniemy modele grup z zależnymi 2 modelami użytkowników – właściciel i ostatnio dodany użytkownik. Od razu widać że większość danych jest zbędna, a co z tym idzie wykorzystanie pamięci będzie większe i czas zwracania danych z bazy będzie dłuższy.

Definiowanie widoku (views) w schema

Aby rozdzielić pliki w którym są definicje tabel i widoki, warto utworzyć osobny plik w którym zapiszemy naszą definicje widoku.

config/views_schema.yml

propel:
  groups_view:
    _attributes:              { skipSQL: true, readOnly: true }
    id:                       { type: integer }
    name:                     { type: varchar }
    routing_name:             { type: varchar }
    user_count:               { type: integer }
    owner_name:               { type: varchar }
    last_user:                { type: varchar }

Kolejnym krokiem jest przygotowanie SQL z definicją naszego vidoku. Dobrym rozwiązaniem jest by każdy widok umieszczać w osobnym pliku i dodawać do sqldb.map, w celu zbudowania widoku z automatu (nie musimy już ręcznie wywoływać SQL).

Tworzymy plik sql

data/sql/views/groups_view.sql

CREATE OR REPLACE VIEW groups_view AS
  SELECT groups.id,
         groups.name ,
         groups.routing_name,
         groups.user_count,
         owner.login AS owner_name,
         last.login AS last_user
    FROM groups
    JOIN users owner ON owner.id = groups.owner_id
    JOIN users last ON last.id = groups.ast_user_id;

Dodajemy widok do sqldb.map

Edytujemy plik sqldb.map i dodajemy nową pozycję

data/sql/sdldb.map

views/groups_view.sql=propel

Kolejnym krokiem jest zbudowanie bazy danych, dla przykładu użyje polecenia, które tworzy modele, dodaje SQL do bazy i wczytuje przykładowe dane.

./symfony propel:build-all-load

Po wygenerowanie powstał model o nazwie GroupsView, teraz tylko nam zostaje dodanie metody, która będzie zwracać 20 grup z największą ilością użytkowników.

Edytujemy GroupsViewPeer.class.php

Dodajemy metodę

public static function getTopGroups( $limit = 20 )
{
  $c = new Criteria();
  $c->addDescendingOrderByColumn( GroupsViewPeer::USER_COUNT );
  $c->setLimit( $limit );

  return GroupsViewPeer::doSelect( $c );
}

Metoda ta zwraca 20 obiektów, w których mamy tylko i wyłącznie pola, które będą nam potrzebne do wyświetlenia.

Często na rożnych forach spotyka się opinie, że Symfony jest wolnym i zasobożernym frameworkiem, jeśli nie znamy możliwości, zasad jego działania jak i sposobów optymalizacji to niestety ale taka teoria dla pewnych osób jest prawdziwa. Wykorzystując możliwości baz, takie jak plsql, vidoki, triggery i funkcje można osiągnąć rewelacyjne efekty i obsługa milionowych tabel na “słabym” sprzęcie jest czymś realnym.