Thanks Florian, Is this info somewhere on the wiki so we can further reference to it in an easy way ?
2013/5/3 Florian Henry <[email protected]> > Hello All, > > I would like to precise some things about PgSQL. There is a free > module on DoliStore to easy to migrate from MySQL to PgSQL. This module is > build for Dolibarr and do all check that need to be done speciccly for > Dolibarr and PgSQL. > Module : > http://www.dolistore.com/lang-fr/outils-systemes/195-Migrate-to-PostgreSQL.html > DoliForge Project : https://doliforge.org/projects/MigrationToPG/ > > There is a non solve issue between PgSQL and MySQL, the "update from" > request. > Syntax work with MySQL is : > UPDATE table_taget as target, table_source as source SET > fieldtarget=source.fieldsource > WHERE source.rowid=target.rowid; > > syntax work on pgSQL: > UPDATE table_taget as target SET fieldtarget=source.fieldsource > FROM table_source as source WHERE source.rowid=target.rowid; > > There is no native SQL request "update from" in dolibarr core. But in your > module you should do : > > if ($this->db->type=='pgsql') { > $sql="UPDATE table_taget as target SET fieldtarget=source.fieldsource > FROM table_source as source WHERE source.rowid=target.rowid"; > } else { > $sql= "UPDATE table_taget as target, table_source as source SET > fieldtarget=source.fieldsource > WHERE source.rowid=target.rowid"; > } > > Kind regards. > > > > Florian [email protected]+33 6 03 76 48 > 07http://www.open-concept.pro > Twitter : @_Open_Concept_ > > Le 25/04/2013 22:47, Laurent Destailleur (eldy) a écrit : > > Le 25/04/2013 17:21, Marcos García a écrit : > > I think it would be nice that we use Dolibarr with Doctrine or any ORM so > that way we won't have compatibility problems with different database > drivers... > > > > What do you think? > > > Dolibarr already has its own abstract database driver. > And this abstract layer is really better than doctrine and any other orm. > We already test all of them and they were not enough powerfull to match > dolibarr quality, so we develop ours. > For exemple, > - Abstraction for error report differs according to dabase. System works > for all database but they return error code of database and not value > differs. > - Doctrime, like other ORM does not support imbricated level of > transaction > - Doctrine like other CRM does not allow to make a difference between ddl > and dml. And this is required to be able to manage upgrade process. > - Doctrine like other CRM support select, update, delete and insert but > few of them support instruction to manage foreign key creation, or support > is not working correctly. > > The problem of Florian is not a problem of ORM. It is just that request > are not correctly written by developer. So we have to fix this into core > code. > We recommand to develop using Postgresql, as when it works with postgres, > it works with mysql but not the oposite. > > > Regards, > > > > *Marcos García* > > [email protected] > > > > 2013/4/25 Florian Henry <[email protected]> > >> Dear developpers, >> >> I'm currently experiement lot's of problem with PostgreSQL only >> due to non SQL respect standard. >> >> As reminder with an agregate SQL function you must put in GROUP >> BY all field that are not in the agreate >> >> For example : SELECT t.name,t.id, SUM(t.cost) FROM llx_table >> GROUP BY t.id >> That's WRONG ! MySQL let it go, but PgSQL no ! >> >> Correct version is SELECT* t.name,t.id*, SUM(t.cost) FROM >> llx_table GROUP BY *t.name,t.id >> >> * This kind of problem cannot be detected by UnitTest because >> most of the time the are on list, not in object method, but in list SQL. >> >> Other common problem is into insert or update method >> If your field is an integer, you must have this to build the >> request : >> $sql.= " ".(! >> empty($this->fk_fourn)?"'".$this->fk_fourn."'":"null"); >> and NOT >> $sql.= " '".(! >> empty($this->fk_fourn)?$this->fk_fourn:"null")."'"; >> >> Try to sent update or insert request UPDATE llx_table SET >> fk_fourn='' WHERE ... in PgSQL it will say '' is not a valid inter, and is >> right. >> Once again MySQL let it go but insert 0 instead of NULL.... >> >> >> Regards >> >> -- >> Florian [email protected]+33 6 03 76 48 >> 07http://www.open-concept.pro >> Twitter : @_Open_Concept_ >> >> >> _______________________________________________ >> Dolibarr-dev mailing list >> [email protected] >> https://lists.nongnu.org/mailman/listinfo/dolibarr-dev >> >> > > > _______________________________________________ > Dolibarr-dev mailing > [email protected]https://lists.nongnu.org/mailman/listinfo/dolibarr-dev > > > > -- > Eldy (Laurent Destailleur). > > EMail: [email protected] > Web: http://www.destailleur.fr > > Dolibarr (Project leader): http://www.dolibarr.org > To make a donation for Dolibarr project via Paypal: [email protected] > AWStats (Author) : http://awstats.sourceforge.net > To make a donation for AWStats project via Paypal: [email protected] > AWBot (Author) : http://awbot.sourceforge.net > CVSChangeLogBuilder (Author) : http://cvschangelogb.sourceforge.net > > > > _______________________________________________ > Dolibarr-dev mailing > [email protected]https://lists.nongnu.org/mailman/listinfo/dolibarr-dev > > > > _______________________________________________ > Dolibarr-dev mailing list > [email protected] > https://lists.nongnu.org/mailman/listinfo/dolibarr-dev > > -- *Raphaël Doursenaud* 05 35 53 97 13 - 06 68 48 20 10 [email protected] -- <http://gpcsolutions.fr> http://gpcsolutions.fr Technopole Hélioparc 2 avenue du Président Pierre Angot 64053 PAU CEDEX 9 SARL GPC.solutions au capital de 7 500 € - R.C.S. PAU 528 995 921
_______________________________________________ Dolibarr-dev mailing list [email protected] https://lists.nongnu.org/mailman/listinfo/dolibarr-dev
