2014

JanFebMarApr
MayJunJulAug
SepOctNovDec

2013

JanFebMarApr
MayJunJulAug
SepOctNovDec

more...

2011

JanFebMarApr
MayJunJulAug
SepOctNovDec

2010

JanFebMarApr
MayJunJulAug
SepOctNovDec

2009

JanFebMarApr
MayJunJulAug
SepOctNovDec

2008

JanFebMarApr
MayJunJulAug
SepOctNovDec

2007

JanFebMarApr
MayJunJulAug
SepOctNovDec

2006

JanFebMarApr
MayJunJulAug
SepOctNovDec

2005

JanFebMarApr
MayJunJulAug
SepOctNovDec

2004

JanFebMarApr
MayJunJulAug
SepOctNovDec

2003

JanFebMarApr
MayJunJulAug
SepOctNovDec

Photolog

Through the Looking-Glass
2010-10-12: Through the Looking-Glass
My radio speaks is binary!
2010-10-10: My radio speaks is binary!
Gigaminx: (present for my birthday)
2010-09-16: Gigaminx: (present for my birthday)
Trini on bike
2010-09-05: Trini on bike
Valporquero
2010-08-28: Valporquero
My new bike!
2010-08-22: My new bike!
Mario and Ana's wedding
2010-08-13: Mario and Ana's wedding
Canyoning in Guara
2010-08-07: Canyoning in Guara
Trini and Mari in Marbella
2010-08-05: Trini and Mari in Marbella
Trini and Chelo in Tabarca
2010-08-03: Trini and Chelo in Tabarca
Valid XHTML 1.1
Log in
Labels: SQL Perl how-to

(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...