29 Jan 2009

Postgresql and the trigger of death

Description of the problem:
I have a table with 2 columns date_acq and time_acq as text

CREATE TABLE "shapefiles" (gid serial PRIMARY KEY,
"date_acq varchar(10),
"time_acq" varchar(8));


I want a colum with a datetime time with the informations from date_acq and time_acq so that I can use the < and > sql operator.


I added a new column datetime_acq of type datetime and a trigger getting the information from date_acq and time_acq and putting them into the new column.
The trick is that the date is express in french format that is DD/MM/YYYY.


After learning and fighting with postgres stored procedure language I finally got a nice looking solution:

BEGIN;
ALTER TABLE shapefiles DROP COLUMN datetime_acq;
ALTER TABLE shapefiles ADD COLUMN datetime_acq timestamp with time zone;
COMMIT;

DROP FUNCTION IF EXISTS datetime_acq_timestamp() CASCADE;
CREATE FUNCTION datetime_acq_timestamp() RETURNS trigger AS $datetime_acq_timestamp$
DECLARE
temp_date text;
french_pattern text := 'DD/MM/YYYY';
BEGIN
-- Check that date_acq and time_acq are given
IF NEW.date_acq IS NULL THEN
RAISE EXCEPTION 'date_acq cannot be null';
END IF;
IF NEW.time_acq IS NULL THEN
RAISE EXCEPTION 'time_acq cannot be null';
END IF;

-- Updating datetime_acq column
SELECT TO_CHAR(TO_DATE(NEW.date_acq,french_pattern), 'YYYY-MM-DD') into temp_date;
NEW.datetime_acq := temp_date || ' ' || NEW.time_acq;
RETURN NEW;
END;
$datetime_acq_timestamp$ LANGUAGE plpgsql;

CREATE TRIGGER datetime_acq_trigger BEFORE INSERT OR UPDATE ON shapefiles
FOR EACH ROW EXECUTE PROCEDURE datetime_acq_timestamp();

"Et voilĂ !" Now I have a nice column with valuable informations:

XSD schema validation for XSL and auto-completion in eclipse (second edition)

In Jan 8, 2006 I made a post about making xsl autocompletion work under eclipse... but I was in a hurry so the old post is not top quality ;)

Let fix it and make it a first class citizen :)

First you can download 2 already pre-configured empty files:

  • test-default-namespace.xsl

    <?xml version="1.0" encoding="UTF-8"?>
    <stylesheet xmlns="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemalocation="http://www.w3.org/1999/XSL/Transform
    http://www.w3.org/2005/02/schema-for-xslt20.xsd"
    version="2.0">

    </stylesheet>


  • test-not-default-namespace.xsl

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemalocation="http://www.w3.org/1999/XSL/Transform
    http://www.w3.org/2005/02/schema-for-xslt20.xsd"
    version="2.0">

    </xsl:stylesheet>


Now you need to setup eclipse, Go to Windows > Preferences then XML > XML Catalogs.
Copy the exact same informations that the screen capture below!!




Validate your the xsl with Right click on the file in eclispe > Validate

When you hit Ctrl+Space, it should work now!
You shouldn't have any error or warning, especially the warning telling you that eclipse couldn't find the dtd or xml schema for xsl namespace!

Gotchas:
  • Especially if you internet connection is slow, you need to wait a few seconds after opening the xsl before hitting Ctrl+Space because eclipse is downloading the file from http://www.w3.org/2005/02/schema-for-xslt20.xsd
  • I don't know why because I have to close/reopen the eclipse editor for changes to take place

Technorati tags: xsl eclipse

Fomatting messages

I always found formatting Strings was painful in Java. Especially since I learn Ruby where you can do:
some = 'foobar'
my_string = "Here are #{some} value"

In Java, a basic example would be:
String searchPolygon = "POLYGON((" + lowerLeft + ", " + 
lowerRight + ", " + upperRight + ", "
+ upperLeft + ", " + lowerLeft + "))";

If you have too many parameters or you are looking for more efficiency, you can do something like (you can use StringBuffer instead of StringBuilder see javadoc):
StringBuilder searchPolygon = new StringBuilder();
searchPolygon.append("POLYGON((");
searchPolygon.append(lowerLeft);
searchPolygon.append(", ");
searchPolygon.append(lowerRight);
searchPolygon.append(", ");
searchPolygon.append(upperRight);
searchPolygon.append(", ");
searchPolygon.append(upperLeft);
searchPolygon.append(", ");
searchPolygon.append(lowerLeft);
searchPolygon.append("))");
searchPolygon.toString();

But still, it's ugly :(


Or you can use MessageFormat wich make the code more much readable in my opinion ;)

MessageFormat polygonFormatter =
new MessageFormat("POLYGON(({0}, {1}, {2}, {3}, {4}))");
Object[] values = new Object[] {
lowerLeft, lowerRight, upperRight, upperLeft, lowerLeft };
String searchPolygon = polygonFormatter.format(values);


It could have even be better if we could have use a map with key/value instead of indexes... but I didn't found anyting in the java language itself that does this :(