Przechowywanie plików w PostgreSql

28 lutego 2016, 19:01Zostaw Swój komentarzTagi: ,

PostgreSQL i MySQL to najczęściej stosowane relacyjne bazy danych dostęne na bezpłatnej licencji GPL. Każda z nich w zależności od zastosowania ma swoich zwolenników i przeciwników. W tym zestawieniu pod względem popularności pierwsze miejsce zajmuje MySQL, zdarzają się jednak sytuacje w których świadomym wyborem dewelopera jest baza danych spod znaku słonia.

Większość aplikacji webowych z jakimi osobiście mam styczność oparta jest o bazy danych MySQL. Zapewne nie jestem jedynym, u którego ma miejsce taki stan rzeczy. Dzieje się tak ponieważ MySQL stał się swego rodzaju standardem dostępnym w ramach najpopularniejszych hostingów, jak również jest podstawowym wyborem we wszelakiego rodzaju aplikacjach open-source (dla przykładu: WordPress, Joomla, Drupal czy Typo).

W przypadku serwisów, z dużą ilością relacji, oraz wymogiem wyższej wydajności przy dużych ilościach danych, popularność schodzi na dalszy plan a szala przechyla się w stronę nieco wydajniejszego PostgreSQL'a. Proszę tego stwierdzenia jednak nie traktować jako rekomendacja, sporo bowiem zależy od konfiguracji, jak i od architektury aplikacji. Nie chcę tu również podejmować tematu, która z tych baz danych jest lepsza a która nie, bo w przypadku typowych aplikacji jakie większość z nas tworzy, nie ma to bowiem większego znaczenia.

Chciałem natomiast poruszyć stosunkowo krótki epizod, jaki "napsuł mi trochę krwi", w momencie gdy przejmowałem projekt pewnego serwisu z silnikiem opartym o bazę danych PostgreSQL.

Problem dotyczył przypadku magazynowania plików w rekordach bazy danych. Tak wiem, to rzadko spotykane. Sam takiego wariantu bym nie polecał, jednak w niektórych sytuacjach warto przynajmniej rozważyć takie rozwiązanie, zwłaszcza gdy kluczowe jest filtrowanie praw dostępu do takich plików, czy konieczność dodatkowego szyfrowania.

Załóżmy najprostszy z możliwych wariantów, czyli zapis niedużego pliku w tablicy o strukturze uzyskanej przez wywołanie zapytania poniżej.

CREATE TABLE files( id serial PRIMARY KEY, name character varying(512) NOT NULL, mimetype character varying(32) NOT NULL, content bytea );

Zapisy i odczyty zawartości plików dokonujemy zapytaniami o oczywistej postaci,

-- zapis INSERT INTO files (name, mimetype, content) VALUES ('NAZWA_PLIKU','MIME_TYPE','ZAWARTOŚĆ_PLIKU'); -- odczyt SELECT * FROM files WHERE id=IDENTYFIKATOR_PLIKU;
a przed samą operacją zapisu do bazy, przygotowujemy dane funkcją pg_escape_bytea( );
<?php $dbconn = pg_connect('dbname=NAZWA_BAZY'); $filename = 'image.jpg'; $mimetype = 'image/jpeg'; $content = file_get_contents(filename); $escaped = pg_escape_bytea($content); pg_query("INSERT INTO files (name, mimetype, content) VALUES ('$filename', '$mimetype', '$escaped')"); ?>

Po odczycie, analogicznie do zapisu stosujemy podobna operację, lecz tym razem funkcją pg_unescape_bytea( );

<?php $id = 1; $dbconn = pg_connect('dbname=NAZWA_BAZY'); $res = pg_query("SELECT mimetype, content FROM files WHERE id='$id'"); $row = pg_fetch_result($res); header('Content-type: $row['mimetype']); echo pg_unescape_bytea($row['content']); ?>

Poprzedni developer, nie stosował tych funkcji, a jedynie funkcje base64_encode( ) oraz base64_decode( ), zapewne by zminimalizować problemy z nietypowymi znakami jakie składają się na zawartość pliku.

Oba rozwiązania są dobre. Ale żaden z nich najprawdopodobiej nie zadziała prawidłowo, gdy zechcemy go uruchomić na nowo utworzonej bazie danych. Wszystko w zasadzie zależy od domyślej konfiguracji bazy danych postgreSQL, a domyślna konfiguracja jest taka, że zmienne z pól typu bytea zwracane są w HEX'ie i poprzedzone identyfikatorem ciągu HEX'adecymalnego.

Co można zrobić w takim przypadku? Rozwiązaniem jest oczywiście zmiana domyślnych ustawień bazy danych, tak by zawartość pól bytea zwracała dane nie w formie HEX, a jako ciąg znaków ASCII. Zmiany tej można dokonać poprzez wywołanie zapytania i zrestartowanie usługi:

ALTER DATABASE NAZWA_BAZY SET bytea_output TO 'escape';

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Back to top