use Perl

... because one can build amazing database APIs with it by using views to their full extent:

BEGIN;

CREATE TABLE moo (
        id uuid NOT NULL PRIMARY KEY,
        relation regclass NOT NULL
);

CREATE FUNCTION moo(regclass, uuid) RETURNS uuid LANGUAGE sql AS $$
INSERT INTO moo (id, relation) VALUES ($2, $1) RETURNING id;
$$;

CREATE TABLE foo (
        id uuid NOT NULL DEFAULT uuid_generate_v1() PRIMARY KEY REFERENCES moo,
        name text NOT NULL UNIQUE
);

CREATE VIEW foos AS SELECT id, name FROM foo;

ALTER VIEW foos ALTER COLUMN id SET DEFAULT uuid_generate_v1();

CREATE RULE "_INSERT" AS ON INSERT TO foos DO INSTEAD
        INSERT INTO foo (id, name) SELECT moo('foo'::regclass, NEW.id), NEW.name RETURNING *;

INSERT INTO foos (id, name) VALUES ('17ee2d5a-4164-11df-be2f-0019dbf67458', 'a') RETURNING *;
INSERT INTO foos (id, name) VALUES ('1cf57cb8-4164-11df-9159-0019dbf67458', 'b') RETURNING *;
INSERT INTO foos (name) VALUES ('c') RETURNING *;

COMMIT;
... results in:


BEGIN
psql:rules-and-rocks.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "moo_pkey" for table "moo"
CREATE TABLE
CREATE FUNCTION
psql:rules-and-rocks.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
psql:rules-and-rocks.sql:16: NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_name_key" for table "foo"
CREATE TABLE
CREATE VIEW
ALTER VIEW
CREATE RULE
                                    id | name
--------------------------------------+------
  17ee2d5a-4164-11df-be2f-0019dbf67458 | a
(1 row)

INSERT 0 1
                                    id | name
--------------------------------------+------
  1cf57cb8-4164-11df-9159-0019dbf67458 | b
(1 row)

INSERT 0 1
                                    id | name
--------------------------------------+------
  e087171c-4174-11df-856c-0019dbf67458 | c
(1 row)

INSERT 0 1
COMMIT


But take care with rules... is what i've been told nearly agressively... because they're dangerous and come with pitfalls.
More gently said: RTFM! Twice! ;-)

Usually i'm telling people NOT to put their SQL into the code, pretty
much the same as with templates.

As that needs code to get the SQL from the outside it isn't always
what you want. And with SQL there's more than just ``put different
languages into different files'', it's also about formating.

To achieve that i was using heredocs for a while with different
delimiters until i realized that a semicolon is the best option i
can think of:

$sql = <<';';
SELECT
        *
FROM foo
WHERE
      id = $1
;
... which is also pretty nice for copy&waste and has no quoting issues either.

After all the whining from users of the most popular open source
database over the years, here's some cheering about the most advanced
open source database: pseudo-referential integrity with ENUMs and ARRAYs

Have you ever been in the situation where you needed an array or didn't
want to introduce an extra mapping table for just a few well-defined
values without introducing inconcistencies?

Since PostgreSQL got ENUM support it's pretty easy to solve:

test=# CREATE TYPE foo AS ENUM ('a', 'b', 'c');
CREATE TYPE
test=# CREATE TABLE bar (
test(# id INTEGER PRIMARY KEY,
test(# foo foo[] NOT NULL DEFAULT '{a}'
test(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"
CREATE TABLE
test=#

Now try to insert unkown values:

test=# INSERT INTO bar (id, foo) VALUES (1, '{x}');
ERROR: invalid input value for enum foo: "x"
test=#

Works as expected (the common feeling when i'm working with this database btw).

It surely accepts valid values:

test=# INSERT INTO bar (id, foo) VALUES (1, '{b}');
INSERT 0 1
test=# INSERT INTO bar (id, foo) VALUES (2, '{b,c,a}');
INSERT 0 1
test=# INSERT INTO bar (id, foo) VALUES (3, '{a,b,c,a,a}');
INSERT 0 1
test=#

Let's try the default value:

test=# INSERT INTO bar (id) VALUES (4);
INSERT 0 1
test=#

And look at the table contents:

test=# SELECT * FROM bar;
  id | foo
----+-------------
    1 | {b}
    2 | {b,c,a}
    3 | {a,b,c,a,a}
    4 | {a}
(4 rows)

test=#

And of course we can only UPDATE to defined ENUM values:

test=# UPDATE bar SET foo = '{x}' WHERE id = 4;
ERROR: invalid input value for enum foo: "x"
test=# UPDATE bar SET foo = '{b}' WHERE id = 4;
UPDATE 1
test=#

So much about whining... NOT! ;-)

EDIT/P.S.: lbr pointed that extending the ENUM wouldn't be that easy.
First, i don't think you should use this approach if you're expecting the
ENUM values to change.
Second, it can be done like this:

test=# CREATE TYPE foo2 AS ENUM ('a', 'b', 'c', 'd');
CREATE TYPE
test=# ALTER TABLE bar ALTER COLUMN foo DROP DEFAULT,
test-# ALTER COLUMN foo TYPE foo2[] USING string_to_array(array_to_string(foo, ','), ',')::foo2[],
test-# ALTER COLUMN foo SET DEFAULT '{a}';
ALTER TABLE
test=#

Thanks to lbr for pointing it out and making me happy again by proving
that PostgreSQL works and works and works.... and works :-)

Short story: i fixed DBD::SQLite's annoying warning, diff at
http://rt.cpan.org/Ticket/Display.html?id=40383

Long story: today a friend proposed a patch to silence those warnings in
another module and i was finally annoyed enough to investigate.
First i've read the various reports about this, their analysis and
workarounds, then i glanced at the sqlite documentation and found
something promising.

I tried adding a fix in a quick and dirty style until i noticed sqlite
required an update first, as the used function was only available since
sqlite version 3.6.0.

After updating sqlite the fix still didn't work and i saw ``assignment
makes pointer from integer without a cast'' when compiling.
It took a few minutes until i realized that DBD::SQLite picks up the
header files of the bundled sqlite instead of the system-wide installed
one and thus the compiler had no idea about the new function i was using.

The first step to a solution was to zap the local sqlite headers and
then i had it working.
But that was still not very nice as it only helps me but i wanted to get
a result i could push upstream.
So the next logical thing was updating the bundled sqlite before i had a
sufficient patch to send to the DBD::SQLite authors.

A happy end to the story of this bug should be possible with the creation
of a proper ticket on RT.

Now we ``only'' have to wait for a new release of DBD::SQLite.

At least i did more than just moaning :-)

With a bit of help[0] i was able to update OpenBSD's mod_perl2
(ports/www/ap2-mod_perl) to version 2.0.4.

Now everything seems to work fine - at least there
are no tests failing anymore.

[0] http://marc.info/?l=apache-modperl&m=120976149917390&w=2

P.S.: This is actually a journal and NOT a blog about programming
and porting of Perl on OpenBSD.
Just to make sure no-one gets the impression i'd prefer the stupid,
trendy, pseudo-intelligent version of this ... uhm... ``word''.

I'm admittedly still using firefuck^H^H^Hox.
Once you decide to search for extensions... you might be able to find some, but don't expect you'll be able to just install them.
No! In order to download the plugin you need to register... §Q$&{/§%?$%!!

I can hardly think of any reason why the mozilla people scare away their users...
expect for money.

Today i just found the most annoying ticket on rt.cpan.org after i filed a new bug yesterday:
http://rt.cpan.org/Public/Bug/Display.html?id=26344

If i take this bug as what it is - a bug - can someone fix and close it please?
It's even a critical one and i'm all for closing it as soon as possible!

• devel/catalyst - complete installation with everything to write serious applications
• devel/catalyst-tutorial - all whats necessary to walk through the tutorial

Looks like OpenBSD is now ready to rock out of the box in one more area :-)
You might want to take a look at all catalyst ports.
Just grab a recent snapshot and get your feet wet right now.

Have fun,
Simon

Over the last days i've done something i wanted to do for a long time: share and give access to the Catalyst ports i wrote.

A write up with links and further information is available as an article on undeadly.org.

I'm basically trying to generate more interest in Catalyst, polish and let review to finally push it into OpenBSDs CVS tree for maximum usability.

As always i'd like to listen to feedback, so feel free to comment.