Postgresql

mikelaitj · Post by **mikelaitj** » Fri Jan 21, 2005 3:42 pm

Does zoneminder work with Postgresql database. If not, would it be difficult to make it work with Postges?

Post by **zoneminder** » Sun Jan 23, 2005 1:09 pm

I have done a trial migration to Postgres and it seems to be mostly working. It's a bit fiddly in places and I don't know if I'll get the time to completely iron out the glitches such that I can maintain it in synchrony with the MySQL version. To some extent it depends on whether it would actually be useful to many people. I run both DBs here and kind of assume that most people probably do as well. The only really compelling case for a PG version is that you can't ship products with MySQL inside without licensing whereas you can with PostGres. I'd be interested to hear any other reasons you might have for preferring a postgres version to mysql.

Phil

mikelaitj · Post by **mikelaitj** » Fri Jan 28, 2005 6:29 pm

I tend to use postgres across the board, just in case something I am using turns out to be usefull to a customer and I want to resell a solution based in or around it.

I am not opposed to contributing to a project, but I don't see the value in adding a DB cost when something as powerful and unencumbered as postgres exists.

I know that zoneminder started out as a project, but it has great potential. Problem is you immediately add ( I'm guessing, 500 bucks) to the cost for the DB if you wanted to do something commercial with it.

As you are probably aware, postgres version 8 now runs natively in windows. I believe this will contribute to a wider adoption of this DB.

All for now
Jim

loop · Post by **loop** » Thu Feb 10, 2005 1:49 pm

Dear Phil,
we're very interested in ZM's PG support. If you have any guides, samples etc. please share them with us, we'd like to test ZM with PG.

Thank you,
DN

Post by **zoneminder** » Fri Feb 11, 2005 4:32 pm

Hi,

If you drop me an email I can go into it in a little more detail.

Phil

turtle · Post by **turtle** » Wed Dec 06, 2006 7:38 pm

would it be possible to start a wiki page on this?

Perhaps people who have tested can put notes up as well. This may speed up the possible future PG support.

Post by **cordel** » Mon Feb 12, 2007 3:32 am

Currently there is no pg in zm at all (except for whats in cvs otherwise it's not publicly available) so there is nothing yet to work on unless we can talk Phil in releasing that trunk.
My self if I should ever decide to sell versions of ZM am perfectly fine with purchasing a license from mysql.

pessimist · Post by **pessimist** » Wed Feb 14, 2007 12:28 pm

Just started looking at ZM and am very impressed so far. I would be extremely interested in a postgres port.

I made a decision some time ago to ditch mysql and concentrate on postgres. Simple reason - we produce both open source and commercial software. Use mysql and we have to decide whether we are commercial or not. Use postgres and its not an issue.

I've taken a very quick look at the sources and it looks possible to port from one to 'tother.

I'd be happy to do some work on this in the next month or two if this would not be treading on anyone's toes.

jameswilson · Post by **jameswilson** » Wed Feb 14, 2007 1:06 pm

feel free, but it isnt something i for one would want to support here as i for one know naff all anout postg. But you could always add it as a contrib with details on how to on the wiki, then those that want to use it can

James

pessimist · Post by **pessimist** » Wed Feb 14, 2007 2:42 pm

Understand your concerns about support. Suspect that when I look a little deeper into it I'll find its not as easy as it first looks

Converted some of our own stuff from Interbase/Firebird and some from MySQL to Postgres and it wasn't (too) hard so I live in hope.

If I have any success it'll go on the wiki.

Cheers,

Colin

Post by **zoneminder** » Mon Feb 19, 2007 3:59 pm

The main issues with the pG port were moving everything to lower case, which was not complicated but just tedious, adding additional tables replace enum data types, and writing custom functions to replace missing mysql functions. Also adding a db abstraction layer to the php. So it was mostly just donkey work and nothing too hard.

The worst part was doing grouping on joined selects. If you have two tables with 10 columns each you can usually group on one column in mysql. In postgres it wants you to group on pretty much every single column so there were some horrendous sql statements. For instance this one

Code: Select all

select m.id,m.name,m.type,m.function,m.width,m.height,m.run_mode,m.device,m.channel,m.format,m.host,m.port,m.path, count(if(e.archived=0,1,NULL)) as event_count, count(if(e.archived,1,NULL)) as arch_event_count, count(if((e.start_time>($db_now - INTERVAL 1 HOUR)) and e.archived = 0,1,NULL)) as hour_event_count, count(if((e.start_time>($db_now - INTERVAL 1 DAY)) and e.archived = 0,1,NULL)) as day_event_count, count(if((e.start_time>($db_now - INTERVAL 7 DAY)) and e.archived = 0,1,NULL)) as week_event_count, count(if((e.start_time>($db_now - INTERVAL 1 MONTH)) and e.archived = 0,1,NULL)) as month_event_count from monitors as m left join events as e on e.monitor_id = m.id group by m.id,m.name,m.type,m.function,m.width,m.height,m.run_mode,m.device,m.channel,m.format,m.host,m.port,m.path order by m.id

I would like to revive it though, it's just a question of time.

pessimist · Post by **pessimist** » Mon Feb 19, 2007 6:06 pm

Now thats what I call an SQL enquiry!

As I said I've only had a very quick look - I've spent more time trying to set up cameras etc. I'd noticed the enums and, as you say, they're not too much of a problem. The joins look more interesting - nothing new there then?

I'll try to get onto this in the next week or two if that's OK?

Thanks again for a great piece of software.

Cheers,

Colin

AlienTesticle · Post by **AlienTesticle** » Sun Apr 15, 2007 8:03 pm

I am also very interested in using firebird/postgresql with stored procedures, views, triggers, etc.

to bad there is no cvs/svn etc avail to see the direction your going. or one of the distributive versioning systems like Mercurial.

I am just *beginning* to look at the code to see how hard it may be to generalize db access to take advantage of features at the db level which can simplify other code.

Also would like to see a cross platform client since some people like that over the web interface.

I think zoneminder is a great project and something I have always wanted to do. So I hope I can come up with some ideas to help out with yours.

If I end up selling systems/camera setups with zoneminder. Since I am not selling the software but hardware and labor/setup. Are there any licensing concerns I should watch out for beside the mysql?

Of course any code I write for zoneminder will be available for others.

well.. enough rambling for now. back to coding.

Lee Sharp · Post by **Lee Sharp** » Tue Apr 17, 2007 2:40 pm

cordel wrote:Currently there is no pg in zm at all (except for whats in cvs otherwise it's not publicly available) so there is nothing yet to work on unless we can talk Phil in releasing that trunk.
My self if I should ever decide to sell versions of ZM am perfectly fine with purchasing a license from mysql.

I am very concerned here, as I will be selling Zoneminder. However, my understanding is that there is no legal problem here as long as all source is available. All source is available, correct?

robi · Post by **robi** » Tue Apr 17, 2007 4:03 pm

Lee Sharp wrote:selling Zoneminder

I wouldn't dare to do that. It would be respectless regarding to the developers.

ZoneMinder Forums

Postgresql

Postgresql

re postgres