12.4 Storing Files as Toasted Text


Up to now you have seen how binary data can be stored without using PHP's and PostgreSQL's BLOB interface. Many people don't like to work with BLOBs, so it is necessary to find a different solution. In the case of PHP and PostgreSQL, data can be converted to hex code and stored in a text variable. Up to PostgreSQL 7.0, the size of text variables was limited to the size of one page, which is usually 8192 bytes. With the arrival of PostgreSQL 7.1, things have changed and text is a so-called toasted data type now, which means that one field can be larger than one page used by PostgreSQL internally. Currently, a field can have a size of up to 1GB. In many cases this will be enough. If you want to work with fields larger than 1GB, you have to use PostgreSQL's BLOB interface.

In the next example you will see how the content of a file can be stored in a text column. Therefore a table for storing files is created:

 phpbook=> CREATE TABLE filesystem(name text, data text, tstamp timestamp); CREATE 

In the next step you can write a simple demo application you can use to import one file into the database. The next scenario shows how the file called /etc/passwd can be imported into the table you have just seen:

 <?php         # name of file you want to import         $filename = '/etc/passwd';         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         # open file and retrieve information about it         $fp = fopen($filename, "r");         $fstats = fstat($fp);         $data = fread($fp, $fstats[6]);         # converting data to hex data         $hexdata = bin2hex($data);         # inserting data into database         $sql = "INSERT INTO filesystem VALUES                 ('$filename', '$hexdata', now())";         $stat = pg_exec($dbh, $sql);         if      (!$stat)         {                 echo "data cannot be added to the table<br>\n";                 exit;         }         else         {                 echo "data has been added to the table<br>\n";         } ?> 

After connecting to the database, the file is opened for reading using fopen. The file handle returned by fopen will be needed by fstats. With the help of fstats, information about the file such as size and inode can be retrieved. You will need the size of the file in order to find out how much data you have to read from the file using fread. As you can see, these PHP functions are similar to the C functions for performing system calls. After the data is read, it is encoded using bin2hex. $hexdata is then inserted into the database and a message is displayed on the screen.

If you have a look at the content of filesystem, you will see that the data has been imported successfully:

 phpbook=# SELECT name, length(data), tstamp FROM filesystem;     name     | length |            tstamp -------------+--------+-------------------------------  /etc/passwd |   2400 | 2001-12-15 23:42:53.644758+01 (1 row) 

The filename is stored in the first column. The content of the second column is not listed completely because it is no use to display hexadecimal data. As you can see, the field has a total size of 2400 bytes. If you take a look at /etc/passwd, you will see that the amount of storage needed has doubled:

 [hs@athlon hs]$ ls -l /etc/passwd -rw-r--r--    1 root     root         1200 Dez 15 01:13 /etc/passwd 

Exporting the data is as simple as importing the data, and the next listing shows how this can be done:

 <?php         # name of the file you want to retrieve         $filename = '/etc/passwd';         # connecting to the database         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         # selecting data         $sql = "SELECT name, data, tstamp FROM filesystem                 WHERE name='$filename'";         $result = pg_exec($dbh, $sql);         $data = pg_fetch_row($result, 0) or                 exit ("an error has occurred<br>");         $bindata = hex2bin($data[1]);         # comparing data         $fp = fopen($filename, "r");         $fstats = fstat($fp);         $data = fread($fp, $fstats[6]);         if      ($bindata == $data) { echo "the data is the same<br>\n"; }         else    { echo "the data differs<br>\n"; } function hex2bin($data) {         $len = strlen($data);         return pack("H" . $len, $data); } ?> 

First, the record containing the file you need is retrieved from the database. In the next step, the content of the second column is transformed to the original format by a function called hex2bin. At the end of the PHP program, you can see how hex2bin works: First, the length of the string passed to the function is computed, and then the decoded string is returned to the main program. Now $bindata contains the original data, and this data is compared with the data in the original /etc/passwd file. We show how this works so that you can see that the data extracted from the database is the same data you have imported before.

If you execute the PHP script, it is not surprising that the data matches:

 the data is the same 

Working with PHP's BLOB functions is easy, and it takes little effort to build a powerful application.



PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net