This weekend I set about installing the OpenGuides software and setting up PostgreSQL, ready to install all the data and software for the Birmingham OpenGuide site on my home box. Birmingham.pm have negotiated a new server to host our site on, and to maximise it's usage we want to put the OpenGuide site back up. Steve was trying to do this, but has been beset with hardware problems for the last year.
So OpenGuides installs fine, PostgreSQL appears to be up and running, and I have all the scripts, data and images from the site Steve was working on.
I then spend half a day trying to figure out why I can't just load the database dump that Steve gave me into PostgreSQL. It took a while of searching and trial and error, but I finally did it. The problem was I was trying to be a little secure over what users were created and the db owner has to be superuser, ie they can create users. That little snippet isn't clear in the docs, and I was trying to figure out how to set a user as a superuser. Any road up, users are now set up, and db is loaded without errors.
Next I fired up the CGI script. Unfortunately Steve was working with an older copy of OpenGuides, and there are quite a few changes in the last year. No big deal, but it mean installing a few extra plugins. That's when I discovered the flaw in PostgreSQL.
Well I'm assuming its PostgreSQL, but it's definitely a bug.
I started to install 'CGI::Wiki::Locator::UK', and make test failed. Digging deeper, the problem is this. Two entries from the metadata table:
node,metadata_type,metdata_value '21','os_y',1000 '21 clone','os_y',1000
You would expect the following:
SELECT node FROM metadata WHERE metadata_type = 'os_y' AND metadata_value >= 995 AND metadata_value <= 1005
to return both nodes, '21' and '21 clone'. It doesn't it returns nothing. Remove the '>= 995' line and it works. So according to PostgreSQL '1000 >= 995' is false.
I installed the plugin anyway, and started up the script again. I then hit another PostgreSQL problem. I get a FAILED:IDENT error. However, this time I am supecting that the DSN setup in OpenGuides may be at fault, as the connect line is:
DBI->connect('dbname=openguides','username',....);
Shouldn't the DSN be 'dbi:Pg:dname=openguides'? It was gone 11pm last night when I got that far, so plan to have another crack tonight. But installing software shouldn't be this cumbersome.
Re:what does the schema look like?
barbie on 2005-08-15T15:41:58
No it's numeric. The 1000 <= 1005 line works fine.Re:what does the schema look like?
Wallie on 2005-08-15T17:05:42
When doing a string compare 1000 <= 1005 would be true but 1000 >= 995 would be false (because 1 is less than 9). So if you're saying it's numeric because '1000 <= 1005 works fine' then you might want to take a look at the table definition. If not then it's a really strange problem, and I would like to know what caused it... if you ever find out.Re:what does the schema look like?
barbie on 2005-08-15T17:54:03
Sorry I see the confusion. I have assumed the field to be numeric, as that appears to be how it was set up by the tests in CGI::Wiki::Plugin::Locator::UK. Although having said that, maybe you're right, perhaps it did create a string field and not a numeric one. That would seem to make sense.