RE: PostgreSQL compatibility fixes

This message was sent from: Development
http://forums.oscommerce.com/viewtopic.php?p=219369#219369
----------------------------------------------------------------

[quote="pokryfka"]
there're are also functional indexes, generally pgsql is great
[/quote]
I don't know what you mean here, but there are a few queries that hit the database 10's of times per page, adding indexes for these queries help quite a bit.

[quote="pokryfka"]
as for the sql fixes - they're very minor so it's not a big deal 
some incorrect queries that work on mySQL (like the one with aggregate functions)
also i was suprised that osc compares an integer with a string '2{4}3{5}'
and according to mysql it's equal 2 !!!
[/quote]
I wouldn't believe you if I hadn't seen it myself.

[quote="pokryfka"]
how do You implement mysql's last_insert_id ?
[/quote]
That's one of the few things I if...then...else-ed:
[code]
  function tep_db_insert_id() {
		global $link;
		if ( DB_TYPE == 'mysql'  ) {
			return($link->getOne( 'SELECT last_insert_id()' ) );
		} elseif ( DB_TYPE == 'pgsql'  ) {
			return $link->getOne( "SELECT currval('master_id_seq')" );
		}
  }
[/code]
In postgres all tables share the same sequence. It's 64bit so it should provide plenty of headroom.

[quote="pokryfka"]
i think osc needs abstrac db layer but it's up to the project leader when and how.
[/quote]
Sure, but a contribution won't hurt.

[quote="pokryfka"]
meanwhile it would be nice if they change some db related issues.
this would not break osc and would make the future shift easier.
[/quote]
I agree. But I understand shipping a 2.2 that works is the number one prority right now, so fixing mysql-isms probably isn't on the top of the todo list.

Cheers,
Rob