Juan Céspedes - SQL 

(or, how to use History Tables in PostgreSQL)

If you would like to store somewhere the full history of all the insertions, updates and deletions in all your tables (to maintain a wiki-like database), there is no simple way to do it... but here you can find a way to do it, in an article written by Thomas Liske.

It works in PostgreSQL, creating a PL/Perl database trigger called log_history() which adds a line to a new table called history.<SCHEMA>_<TABLENAME> with a timestamp (hist_ts), the operation (hist_op) and all the columns, whenever a INSERT, UPDATE or DELETE is executed. This effectively stores all the history of the tables where the trigger is used.

Here you can view the log_history trigger, and a simple function to bind the trigger on all the tables in a schema, history_create_triggers. You can call this function for schema "public" with SELECT history_create_triggers('public');

These two functions are written in PL/Perl and PL/pgSQL, respectively, so you have to add those language to your PostgreSQL database, if you have not already done so, with CREATE LANGUAGE plperl; and CREATE LANGUAGE plpgsql;

I have been working for the last few days in improving my web pages (www.cespedes.org and juancespedes.es), specifically the blog and pictures parts (in fact, I have removed all the other parts).

Regarding the blog, well, it is now a real blog :-). I can add entries (like this one) and edit previous ones from a web browser. All the entries are stored in a PostgreSQL database. Every entry has its own, permanent link. People can add comments, and all of them are visible from the permalink in each page.

Regarding the pictures, I have simplified the URLs used to navigate throw them, in order to speed up navigation and to help the web crawlers. The list of all the pictures (more than 64,000) are also stored in the database, and I have added captions to the pictures with localization (in order to show different things in Spanish and in English), and I have created labels to tag each picture.

To-Do list: improve/automate the way I upload pictures, store and show scanned images, crop/rotate pictures, create albums, save list of people in each picture, show Exif information, save the place where each picture was taken...