janus's Journal http://use.perl.org/~janus/journal/ janus's use Perl Journal en-us use Perl; is Copyright 1998-2006, Chris Nandor. Stories, comments, journals, and other submissions posted on use Perl; are Copyright their respective owners. 2012-02-09T01:13:11+00:00 pudge pudge@perl.org Technology hourly 1 1970-01-01T00:00+00:00 janus's Journal http://use.perl.org/images/topics/useperl.gif http://use.perl.org/~janus/journal/ PostgreSQL rules and rocks http://use.perl.org/~janus/journal/40300?from=rss <p>... because one can build amazing database APIs with it by using views to their full extent:<br><code><br>BEGIN;</code></p><p><code>CREATE TABLE moo (<br> &nbsp; &nbsp; &nbsp; &nbsp; id uuid NOT NULL PRIMARY KEY,<br> &nbsp; &nbsp; &nbsp; &nbsp; relation regclass NOT NULL<br>);</code></p><p><code>CREATE FUNCTION moo(regclass, uuid) RETURNS uuid LANGUAGE sql AS $$<br>INSERT INTO moo (id, relation) VALUES ($2, $1) RETURNING id;<br>$$;</code></p><p><code>CREATE TABLE foo (<br> &nbsp; &nbsp; &nbsp; &nbsp; id uuid NOT NULL DEFAULT uuid_generate_v1() PRIMARY KEY REFERENCES moo,<br> &nbsp; &nbsp; &nbsp; &nbsp; name text NOT NULL UNIQUE<br>);</code></p><p><code>CREATE VIEW foos AS SELECT id, name FROM foo;</code></p><p><code>ALTER VIEW foos ALTER COLUMN id SET DEFAULT uuid_generate_v1();</code></p><p><code>CREATE RULE "_INSERT" AS ON INSERT TO foos DO INSTEAD<br> &nbsp; &nbsp; &nbsp; &nbsp; INSERT INTO foo (id, name) SELECT moo('foo'::regclass, NEW.id), NEW.name RETURNING *;</code></p><p><code>INSERT INTO foos (id, name) VALUES ('17ee2d5a-4164-11df-be2f-0019dbf67458', 'a') RETURNING *;<br>INSERT INTO foos (id, name) VALUES ('1cf57cb8-4164-11df-9159-0019dbf67458', 'b') RETURNING *;<br>INSERT INTO foos (name) VALUES ('c') RETURNING *;</code></p><p><code>COMMIT;<br></code><nobr> <wbr></nobr>... results in:</p><p><code><br>BEGIN<br>psql:rules-and-rocks.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "moo_pkey" for table "moo"<br>CREATE TABLE<br>CREATE FUNCTION<br>psql:rules-and-rocks.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"<br>psql:rules-and-rocks.sql:16: NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_name_key" for table "foo"<br>CREATE TABLE<br>CREATE VIEW<br>ALTER VIEW<br>CREATE RULE<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id | name<br>--------------------------------------+------<br> &nbsp; 17ee2d5a-4164-11df-be2f-0019dbf67458 | a<br>(1 row)</code></p><p><code>INSERT 0 1<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id | name<br>--------------------------------------+------<br> &nbsp; 1cf57cb8-4164-11df-9159-0019dbf67458 | b<br>(1 row)</code></p><p><code>INSERT 0 1<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id | name<br>--------------------------------------+------<br> &nbsp; e087171c-4174-11df-856c-0019dbf67458 | c<br>(1 row)</code></p><p><code>INSERT 0 1<br>COMMIT<br></code></p><p>But take care with rules... is what i've been told nearly agressively... because they're dangerous and come with pitfalls.<br>More gently said: RTFM! Twice!<nobr> <wbr></nobr>;-)</p> janus 2010-04-06T12:15:53+00:00 journal Hint: Formatting SQL in Perl code http://use.perl.org/~janus/journal/40134?from=rss <p>Usually i'm telling people NOT to put their SQL into the code, pretty<br>much the same as with templates.</p><p>As that needs code to get the SQL from the outside it isn't always<br>what you want. And with SQL there's more than just ``put different<br>languages into different files'', it's also about formating.</p><p>To achieve that i was using heredocs for a while with different<br>delimiters until i realized that a semicolon is the best option i<br>can think of:<br><code><br>$sql = &lt;&lt;';';<br>SELECT<br> &nbsp; &nbsp; &nbsp; &nbsp; *<br>FROM foo<br>WHERE<br> &nbsp; &nbsp; &nbsp; id = $1<br>;<br></code><nobr> <wbr></nobr>... which is also pretty nice for copy&amp;waste and has no quoting issues either.</p> janus 2010-01-27T12:09:42+00:00 journal pseudo-referential integrity with ENUMs and ARRAYs http://use.perl.org/~janus/journal/38570?from=rss <p>After all the whining from users of the most popular open source<br>database over the years, here's some cheering about the most advanced<br>open source database: pseudo-referential integrity with ENUMs and ARRAYs</p><p>Have you ever been in the situation where you needed an array or didn't<br>want to introduce an extra mapping table for just a few well-defined<br>values without introducing inconcistencies?</p><p>Since PostgreSQL got ENUM support it's pretty easy to solve:</p><p>test=# CREATE TYPE foo AS ENUM ('a', 'b', 'c');<br>CREATE TYPE<br>test=# CREATE TABLE bar (<br>test(# id INTEGER PRIMARY KEY,<br>test(# foo foo[] NOT NULL DEFAULT '{a}'<br>test(# );<br>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"<br>CREATE TABLE<br>test=#</p><p>Now try to insert unkown values:</p><p>test=# INSERT INTO bar (id, foo) VALUES (1, '{x}');<br>ERROR: invalid input value for enum foo: "x"<br>test=#</p><p>Works as expected (the common feeling when i'm working with this database btw).</p><p>It surely accepts valid values:</p><p>test=# INSERT INTO bar (id, foo) VALUES (1, '{b}');<br>INSERT 0 1<br>test=# INSERT INTO bar (id, foo) VALUES (2, '{b,c,a}');<br>INSERT 0 1<br>test=# INSERT INTO bar (id, foo) VALUES (3, '{a,b,c,a,a}');<br>INSERT 0 1<br>test=#</p><p>Let's try the default value:</p><p>test=# INSERT INTO bar (id) VALUES (4);<br>INSERT 0 1<br>test=#</p><p>And look at the table contents:</p><p>test=# SELECT * FROM bar;<br> &nbsp; id | foo<br>----+-------------<br> &nbsp; &nbsp; 1 | {b}<br> &nbsp; &nbsp; 2 | {b,c,a}<br> &nbsp; &nbsp; 3 | {a,b,c,a,a}<br> &nbsp; &nbsp; 4 | {a}<br>(4 rows)</p><p>test=#</p><p>And of course we can only UPDATE to defined ENUM values:</p><p>test=# UPDATE bar SET foo = '{x}' WHERE id = 4;<br>ERROR: invalid input value for enum foo: "x"<br>test=# UPDATE bar SET foo = '{b}' WHERE id = 4;<br>UPDATE 1<br>test=#</p><p>So much about whining... NOT!<nobr> <wbr></nobr>;-)</p><p>EDIT/P.S.: lbr pointed that extending the ENUM wouldn't be that easy.<br>First, i don't think you should use this approach if you're expecting the<br>ENUM values to change.<br>Second, it can be done like this:</p><p>test=# CREATE TYPE foo2 AS ENUM ('a', 'b', 'c', 'd');<br>CREATE TYPE<br>test=# ALTER TABLE bar ALTER COLUMN foo DROP DEFAULT,<br>test-# ALTER COLUMN foo TYPE foo2[] USING string_to_array(array_to_string(foo, ','), ',')::foo2[],<br>test-# ALTER COLUMN foo SET DEFAULT '{a}';<br>ALTER TABLE<br>test=#</p><p>Thanks to lbr for pointing it out and making me happy again by proving<br>that PostgreSQL works and works and works.... and works<nobr> <wbr></nobr>:-)</p> janus 2009-02-28T17:56:22+00:00 journal No more "closing dbh with active statement handles" http://use.perl.org/~janus/journal/37743?from=rss <p>Short story: i fixed DBD::SQLite's annoying warning, diff at<br>http://rt.cpan.org/Ticket/Display.html?id=40383</p><p>Long story: today a friend proposed a patch to silence those warnings in<br>another module and i was finally annoyed enough to investigate.<br>First i've read the various reports about this, their analysis and<br>workarounds, then i glanced at the sqlite documentation and found<br>something promising.</p><p>I tried adding a fix in a quick and dirty style until i noticed sqlite<br>required an update first, as the used function was only available since<br>sqlite version 3.6.0.</p><p>After updating sqlite the fix still didn't work and i saw ``assignment<br>makes pointer from integer without a cast'' when compiling.<br>It took a few minutes until i realized that DBD::SQLite picks up the<br>header files of the bundled sqlite instead of the system-wide installed<br>one and thus the compiler had no idea about the new function i was using.</p><p>The first step to a solution was to zap the local sqlite headers and<br>then i had it working.<br>But that was still not very nice as it only helps me but i wanted to get<br>a result i could push upstream.<br>So the next logical thing was updating the bundled sqlite before i had a<br>sufficient patch to send to the DBD::SQLite authors.</p><p>A happy end to the story of this bug should be possible with the creation<br>of a proper ticket on RT.</p><p>Now we ``only'' have to wait for a new release of DBD::SQLite.</p><p>At least i did more than just moaning<nobr> <wbr></nobr>:-)</p> janus 2008-10-26T00:23:58+00:00 journal Perl/OpenBSD programming and porting blog: mod_perl-2.0.4 http://use.perl.org/~janus/journal/36319?from=rss <p>With a bit of help[0] i was able to update OpenBSD's mod_perl2<br>(ports/www/ap2-mod_perl) to version 2.0.4.</p><p>Now everything seems to work fine - at least there<br>are no tests failing anymore.</p><p>[0] http://marc.info/?l=apache-modperl&amp;m=120976149917390&amp;w=2</p><p>P.S.: This is actually a journal and NOT a blog about programming<br>and porting of Perl on OpenBSD.<br>Just to make sure no-one gets the impression i'd prefer the stupid,<br>trendy, pseudo-intelligent version of this<nobr> <wbr></nobr>... uhm... ``word''.</p> janus 2008-05-03T08:54:21+00:00 journal Privacy sucks? No, mozilla does! http://use.perl.org/~janus/journal/36009?from=rss <p>I'm admittedly still using firefuck^H^H^Hox.<br>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.<br>No! In order to download the plugin you need to register... &#167;Q$&amp;{/&#167;%?$%!!</p><p>I can hardly think of any reason why the mozilla people scare away their users...<br>expect for money.</p> janus 2008-03-31T07:59:28+00:00 journal use CPAN; no GOOGLE; http://use.perl.org/~janus/journal/34588?from=rss <p>Today i just found the most annoying ticket on rt.cpan.org after i filed a new bug yesterday:<br>http://rt.cpan.org/Public/Bug/Display.html?id=26344</p><p>If i take this bug as what it is - a bug - can someone fix and close it please?<br>It's even a critical one and i'm all for closing it as soon as possible!</p> janus 2007-10-02T08:14:03+00:00 journal Catalyst now in OpenBSDs CVS tree http://use.perl.org/~janus/journal/31542?from=rss After the <a href="http://use.perl.org/~janus/journal/31496">release of my catalyst ports</a> i created for OpenBSD, Marc Espie started the import into the OpenBSD tree.<br> Besides what i've done, there we're <a href="http://undeadly.org/cgi?action=article&amp;sid=20061102150554&amp;pid=8">similar efforts by Sean Comeau</a> which Marc took care of too. The adjustments made were mostly to tests, dependencies, descriptions and/or comments.<br> Marc also added other new catalyst ports.<br> <br> Two important of them: <ul> <li>devel/catalyst - complete installation with everything to write serious applications</li><li>devel/catalyst-tutorial - all whats necessary to walk through the tutorial</li></ul><p> Looks like OpenBSD is now ready to rock out of the box in one more area<nobr> <wbr></nobr>:-)<br> You might want to take a <a href="http://ports.openbsd.nu/search.php?stype=folder&amp;so=catalyst">look at all catalyst ports</a>.<br> Just grab a recent snapshot and get your feet wet right now.<br> <br> Have fun,<br> Simon</p> janus 2006-11-08T09:09:17+00:00 bsd Catalyst ported to OpenBSD http://use.perl.org/~janus/journal/31496?from=rss <p>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.</p><p> A write up with links and further information is available <a href="http://undeadly.org/cgi?action=article&amp;sid=20061102150554"> as an article</a> on undeadly.org. </p><p> 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. </p><p> As always i'd like to listen to feedback, so feel free to comment. </p> janus 2006-11-03T12:22:02+00:00 bsd