ZoneMinder Forums

_
Hi, great product. I was just wondering if there will be support for PostgreSQL? I run all Postgres here and it would be nice to keep everything consistent. Just curious. Thanks.

I have a trial version here that runs on postgres (as well as mysql). It's not been a simple migration though and it's by no means complete, plus it's a branch off an easrlier version. If I can merge it into the trunk once it's reliable I'll probably release it though.

The main gotchas I had were

1. Sometimes pgsql ignores case and other times it doesn't but as most of the tables and columns in ZM are mixed case it got very confused.
2. Complex joined queries in mysql became massive unwieldy beasts in pgsql as every column seems to want to be in a 'group by' clause. I'd be a bit more sympathetic to pgsql if it's insistence on sticking to the SQL standard was to a more recent version than SQL92.
3. Enum and set support. I had to hack this with extra tables, not elegant.
4. Poor built in function support especially for date and time calculations.
5. Finding an abstration layer that would work with c++, perl and php. In the end I wrote a simple one for c++, used DBI for perl (which was about 95% compatible) and also wrote a noddy one for php as most of the functions are pretty much the same.

I did like being able to define functions on the fly though, that was cool.

Phil,

Phil, thanks for the info. wow, i didn't know it was so involved to port the code to postgresql. i've dealt with postgres a lot and yes, when you're doing a comparison, it is case sensitive whereas mysql is case insensitive (unless dealing with binary fields). That's always been a problem with coding. Some of the work around we've done are to force lower case on everything when inserting or updating and then forcing constrains on the relevant columns.

We do appreciate your efforts though to get a postgres version out there for release. Thanks again for such a great application.

zoneminder wrote:I have a trial version here that runs on postgres (as well as mysql). It's not been a simple migration though and it's by no means complete, plus it's a branch off an easrlier version. If I can merge it into the trunk once it's reliable I'll probably release it though.

The main gotchas I had were

1. Sometimes pgsql ignores case and other times it doesn't but as most of the tables and columns in ZM are mixed case it got very confused.
2. Complex joined queries in mysql became massive unwieldy beasts in pgsql as every column seems to want to be in a 'group by' clause. I'd be a bit more sympathetic to pgsql if it's insistence on sticking to the SQL standard was to a more recent version than SQL92.
3. Enum and set support. I had to hack this with extra tables, not elegant.
4. Poor built in function support especially for date and time calculations.
5. Finding an abstration layer that would work with c++, perl and php. In the end I wrote a simple one for c++, used DBI for perl (which was about 95% compatible) and also wrote a noddy one for php as most of the functions are pretty much the same.

I did like being able to define functions on the fly though, that was cool.

Phil,