Re: Help Required to support postgres in vtigerCRM 5.0

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Help Required to support postgres in vtigerCRM 5.0

Jens Hamisch
Hi Don,
hi Richie,
hi *,

I've recently upgraded to SVN version 8840 and discovered, that one
of my postgres fixes needs to be adapted because the regarding
database table has changed.

Also I figured out 2 problems in the 8440 revision: Missing entries
in the 'blue' style sheet (though it is not used by default) and a
include_once that has been forgotten to change when the Activities.php
went to the Calendar module.

If attached my fixed to this mail.

Jens


On Tue, Aug 08, 2006 at 05:49:41AM -0700, don wrote:

>
> Hi Jens,
>
> Sorry to disturb you again.
>
> I havent yet received the following files:
>
> include/database/Postgres8.php
> include/DatabaseUtil.php
> PearDatabase.php
>
> Kindly send these files to me at the earliest.
>
> Thanks & Regards,
> Don
>
>
>
> ---- On Mon, 07 Aug 2006 Jens Hamisch <[hidden email]> wrote ----
>
> Hi Don,
>
>
> sorry I first read your mail today, so I wasn't aware of the release
> planned for this week. However I've prepared a patch and added it to
> thgis mail. During the weekend I was able to drag down and fix even the
> transcations problems, so everything now wors fine for me.
>
> The patch relates to SVN version 8640.
> It will update the following files:
>
>  adodb/drivers/adodb-postgres64.inc.php
>  adodb/adodb-datadict.inc.php
>  include/database/PearDatabase.php
>  include/freetag/freetag.class.php
>  include/utils/CommonUtils.php
>  include/utils/EditViewUtils.php
>  include/utils/SearchUtils.php
>  include/utils/UserInfoUtil.php
>  include/RelatedListView.php
>  install/1checkSystem.php
>  install/5createTables.inc.php
>  install/4createConfigFile.php
>  modules/Vendors/ListView.php
>  modules/Potentials/ListView.php
>  modules/Notes/ListView.php
>  modules/Leads/ListView.php
>  modules/Invoice/ListView.php
>  modules/Campaigns/ListView.php
>  modules/SalesOrder/ListView.php
>  modules/Products/ListView.php
>  modules/Activities/ListView.php
>  modules/Activities/Event.php
>  modules/PurchaseOrder/ListView.php
>  modules/Calendar/calendarLayout.php
>  modules/Accounts/ListView.php
>  modules/Quotes/ListView.php
>  modules/HelpDesk/ListView.php
>  modules/PriceBooks/ListView.php
>  modules/Users/User.php
>  modules/Users/DefaultDataPopulator.php
>  modules/Users/Authenticate.php
>  modules/Faq/ListView.php
>  modules/Contacts/ListView.php
>
>
>
> Besides the postgres fixes it also addresses some installation
> problems (regarding file names).
>
>
> Kind regards,
> Jens
>
> n Fri, Aug 04, 2006 at 12:38:27AM -0700, don wrote:
> > Hi Jens,
> >
> > It would be good if you can send me the postgres patch at the earliest by today. We are planning to do a release by
> > next week and by this weekend we have to freeze the developments.
> >
> > Also I looked into your earlier patches.  Can you tell me how to interpret them.
> >
> > there are lines starting with ! or + or -  What does these symbols indicate.
> >
> >
> > Thanks & Regards,
> > Don
> >
> >
> >
> > ---- On Thu, 03 Aug 2006 Jens Hamisch <[hidden email]> wrote ----
> >
> > Hi Don,
> >
> >
> > in which timeframe do you need this? Currently I have a running
> > version in which I had to disable postgres transactions. I'm in the
> > process of dragging down the transactions probs and hope I will
> > be finished on that up to this weekend ...
> >
> > Do you want to wait a few days? Otherwise I can produce a current
> > snapshot this evening.
> >
> >
> >
> > Jens
> >
> >
> > On Thu, Aug 03, 2006 at 12:14:15AM -0700, don wrote:
> > > Hi all,
> > >
> > > I am going to integrate the changes done to support postgres8 in vtigerCRM 5.0.  It will be good if you can send
> > >  me a consolidated patch that would help me in integrating most of  the changes required for postgres8 in one shot.
> > >
> > >
> > > Thanks & Regards,
> > > Don
> > >
> > >
> > >  
> >
> > --
> >
> > --------------------------------------------------------------------------------
> >      /
> >  +##+|##+   STRAWBERRY                     Jens Hamisch
> > +v#+v v##+  EDV-Systeme GmbH               Managing director
> > / v    v\v
> > | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> > |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
> >  | .     |                                 Fax:          (+49 8171) 41805-59
> >  \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
> >   \____/    [hidden email]      
>
> --
>
> --------------------------------------------------------------------------------
>      /
>  +##+|##+   STRAWBERRY                     Jens Hamisch
> +v#+v v##+  EDV-Systeme GmbH               Managing director
> / v    v\v
> | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
>  | .     |                                 Fax:          (+49 8171) 41805-59
>  \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
>   \____/    [hidden email]      
>
--

--------------------------------------------------------------------------------
     /
 +##+|##+   STRAWBERRY                     Jens Hamisch
+v#+v v##+  EDV-Systeme GmbH               Managing director
/ v    v\v
| . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
|     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
 | .     |                                 Fax:          (+49 8171) 41805-59
 \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
  \____/    [hidden email]      


_______________________________________________
Get started with creating presentations online - http://zohoshow.com?vt 

Patches_8840.tgz (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Help Required to support postgres in vtigerCRM 5.0

Jens Hamisch
Hi Don,

here's another Postgres8 patch for you.

I did what I suggested with my last patch: I changed the getUniqueID function
in PeraDatabase.ph. It now inserts the key column into the sequence name
when it is called in a postgres environment.

However during doing this I figured out, that there are some things to be
thought of:

    1.  For the following tables there is no sequence defined:

        vtiger_role
        vtiger_audit_trial
        vtiger_datashare_relatedmodules
        vtiger_relatedlists
        vtiger_inventory_tandc
        vtiger_customview
        vtiger_crmentity
        vtiger_seactivityrel
        vtiger_selectquery
        vtiger_systems
        vtiger_freetags
        vtiger_inventorytaxinfo
        vtiger_shippingtaxinfo

        means: there's no autoincrement keyword in DatabaseSchema.xml.
        For the time being, I have left over the sequence creations in
        5createtables.in.php. I was able to remove those for the rest
        of the database tables.

        I've also added a SQL script which will create those missing
        sequences in running databases.


    2.  In line 98 of CRMentity.php there is the following statement:

            $parentid = $adb->getUniqueID('vtiger_seactivityrel');

        For my understanding of the database this is wrong and should be

            $parentid = $adb->getUniqueID('vtiger_crmentity');

        The table seactivityrel is a helper table relating between an
        activity and a crmentity. So there should never be a sequence
        of this table in the crmid column (what the wrong statement does),
        but only a reference to a sequence in the crmentity table,
        shouldn't it?

        Maybe some expert may have a loo at this! I'm afraid, that the
        current coding will introduce wrong references in the database.



Kind regards,
Jens

On Thu, Aug 17, 2006 at 12:33:19AM -0700, don wrote:

> Hi Jens,
>
> I have received the files.  I will integrate this.  If anything required i will contact you.
>
> Thanks,
> Don
>
>
>
> ---- On Thu, 17 Aug 2006 Jens Hamisch <[hidden email]> wrote ----
>
> Hi Don,
>
> did you receive the files?
>
>
> -- Jens
>
> --------------------------------------------------------------------------------
>      /
>  +##+|##+   STRAWBERRY                     Jens Hamisch
> +v#+v v##+  EDV-Systeme GmbH               Managing director
> / v    v\v
> | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
>  | .     |                                 Fax:          (+49 8171) 41805-59
>  \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
>   \____/    [hidden email]      
--

--------------------------------------------------------------------------------
     /
 +##+|##+   STRAWBERRY                     Jens Hamisch
+v#+v v##+  EDV-Systeme GmbH               Managing director
/ v    v\v
| . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
|     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
 | .     |                                 Fax:          (+49 8171) 41805-59
 \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
  \____/    [hidden email]      


_______________________________________________
Get started with creating presentations online - http://zohoshow.com?vt 

9041-MainLine-1-Postgres8.patch (6K) Download Attachment
9041-MainLine-2-Database.sql (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Help Required to support postgres in vtigerCRM 5.0

Jens Hamisch
Hi Don,

I did it again ...
This time I've taken the database column lists out of
Postgres8.php and replaced this part of code by the
hgenerich function already provide by PearDatabase.php.

Jens


PS: Should Is assemble another complete postgres patch?

On Mon, Aug 21, 2006 at 01:16:07PM +0200, Jens Hamisch wrote:

> Hi Don,
>
> here's another Postgres8 patch for you.
>
> I did what I suggested with my last patch: I changed the getUniqueID function
> in PeraDatabase.ph. It now inserts the key column into the sequence name
> when it is called in a postgres environment.
>
> However during doing this I figured out, that there are some things to be
> thought of:
>
>     1.  For the following tables there is no sequence defined:
>
> vtiger_role
> vtiger_audit_trial
> vtiger_datashare_relatedmodules
> vtiger_relatedlists
> vtiger_inventory_tandc
> vtiger_customview
> vtiger_crmentity
> vtiger_seactivityrel
> vtiger_selectquery
> vtiger_systems
> vtiger_freetags
> vtiger_inventorytaxinfo
> vtiger_shippingtaxinfo
>
> means: there's no autoincrement keyword in DatabaseSchema.xml.
> For the time being, I have left over the sequence creations in
> 5createtables.in.php. I was able to remove those for the rest
> of the database tables.
>
> I've also added a SQL script which will create those missing
> sequences in running databases.
>
>
>     2.  In line 98 of CRMentity.php there is the following statement:
>
>    $parentid = $adb->getUniqueID('vtiger_seactivityrel');
>
> For my understanding of the database this is wrong and should be
>
>    $parentid = $adb->getUniqueID('vtiger_crmentity');
>
> The table seactivityrel is a helper table relating between an
> activity and a crmentity. So there should never be a sequence
> of this table in the crmid column (what the wrong statement does),
> but only a reference to a sequence in the crmentity table,
> shouldn't it?
>
> Maybe some expert may have a loo at this! I'm afraid, that the
> current coding will introduce wrong references in the database.
>
>
>
> Kind regards,
> Jens
>
> On Thu, Aug 17, 2006 at 12:33:19AM -0700, don wrote:
> > Hi Jens,
> >
> > I have received the files.  I will integrate this.  If anything required i will contact you.
> >
> > Thanks,
> > Don
> >
> >
> >
> > ---- On Thu, 17 Aug 2006 Jens Hamisch <[hidden email]> wrote ----
> >
> > Hi Don,
> >
> > did you receive the files?
> >
> >
> > -- Jens
> >
> > --------------------------------------------------------------------------------
> >      /
> >  +##+|##+   STRAWBERRY                     Jens Hamisch
> > +v#+v v##+  EDV-Systeme GmbH               Managing director
> > / v    v\v
> > | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> > |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
> >  | .     |                                 Fax:          (+49 8171) 41805-59
> >  \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
> >   \____/    [hidden email]      
>
> --
>
> --------------------------------------------------------------------------------
>      /
>  +##+|##+   STRAWBERRY                     Jens Hamisch
> +v#+v v##+  EDV-Systeme GmbH               Managing director
> / v    v\v
> | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
>  | .     |                                 Fax:          (+49 8171) 41805-59
>  \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
>   \____/    [hidden email]      
>

> *** vtiger_crm/include/database/PearDatabase.php.rev9041 Mon Aug 21 09:13:14 2006
> --- vtiger_crm/include/database/PearDatabase.php Mon Aug 21 11:48:33 2006
> ***************
> *** 928,936 ****
> --- 928,947 ----
>  
>       function getUniqueID($seqname)
>       {
> + global $log;
>   $this->checkConnection();
> + if( $this->dbType == "pgsql") {
> +    $keytab = $this->database->MetaPrimaryKeys($seqname);
> +    if( count( $keytab) > 0) {
> + $log->info("PearDatabase: Postgres getUniqueID hack: ".$seqname."_".$keytab[0]."_seq");
> + return $this->database->GenID($seqname."_".$keytab[0]."_seq",1);
> +    } else {
> + $log->info("PearDatabase: Problem: getUniqueID but no key for '$seqname'");
> +    }
> + }
>   return $this->database->GenID($seqname."_seq",1);
>       }
> +
>       function get_tables()
>       {
>   $this->checkConnection();
> *** vtiger_crm/install/5createTables.inc.php.rev9041 Mon Aug 21 10:56:14 2006
> --- vtiger_crm/install/5createTables.inc.php Mon Aug 21 12:38:10 2006
> ***************
> *** 164,232 ****
>   //   This should be a part of "createTables" however ...
>   if( $adb->dbType == "pgsql" ) {
>       $sequences = array(
> ! "vtiger_leadsource_seq",
> ! "vtiger_accounttype_seq",
> ! "vtiger_industry_seq",
> ! "vtiger_leadstatus_seq",
> ! "vtiger_rating_seq",
> ! "vtiger_licencekeystatus_seq",
> ! "vtiger_opportunity_type_seq",
> ! "vtiger_salutationtype_seq",
> ! "vtiger_sales_stage_seq",
> ! "vtiger_ticketstatus_seq",
> ! "vtiger_ticketpriorities_seq",
> ! "vtiger_ticketseverities_seq",
> ! "vtiger_ticketcategories_seq",
> ! "vtiger_duration_minutes_seq",
> ! "vtiger_eventstatus_seq",
> ! "vtiger_taskstatus_seq",
> ! "vtiger_taskpriority_seq",
> ! "vtiger_manufacturer_seq",
> ! "vtiger_productcategory_seq",
> ! "vtiger_activitytype_seq",
> ! "vtiger_currency_seq",
> ! "vtiger_faqcategories_seq",
> ! "vtiger_usageunit_seq",
> ! "vtiger_glacct_seq",
> ! "vtiger_quotestage_seq",
> ! "vtiger_quotestagehistory_seq",
> ! "vtiger_carrier_seq",
> ! "vtiger_taxclass_seq",
> ! "vtiger_recurringtype_seq",
> ! "vtiger_faqstatus_seq",
> ! "vtiger_invoicestatus_seq",
> ! "vtiger_invoicestatushistory_seq"
> ! "vtiger_postatus_seq",
> ! "vtiger_postatushistory_seq",
> ! "vtiger_sostatus_seq",
> ! "vtiger_sostatushistory_seq",
> ! "vtiger_visibility_seq",
> ! "vtiger_campaigntype_seq",
> ! "vtiger_campaignstatus_seq",
> ! "vtiger_expectedresponse_seq",
> ! "vtiger_status_seq",
> ! "vtiger_activity_view_seq",
> ! "vtiger_lead_view_seq",
> ! "vtiger_date_format_seq",
> ! "vtiger_users_seq",
> ! "vtiger_role_seq",
> ! "vtiger_profile_seq",
> ! "vtiger_field_seq",
> ! "vtiger_def_org_share_seq",
> ! "vtiger_datashare_relatedmodules_seq",
> ! "vtiger_relatedlists_seq",
> ! "vtiger_notificationscheduler_seq",
> ! "vtiger_inventorynotification_seq",
> ! "vtiger_currency_info_seq",
> ! "vtiger_emailtemplates_seq",
> ! "vtiger_inventory_tandc_seq",
> ! "vtiger_selectquery_seq",
> ! "vtiger_customview_seq",
> ! "vtiger_crmentity_seq",
> ! "vtiger_seactivityrel_seq",
> ! "vtiger_freetags_seq",
> ! "vtiger_shippingtaxinfo_seq",
> ! "vtiger_inventorytaxinfo_seq"
>   );
>  
>       foreach ($sequences as $sequence ) {
> --- 164,182 ----
>   //   This should be a part of "createTables" however ...
>   if( $adb->dbType == "pgsql" ) {
>       $sequences = array(
> ! "vtiger_role_roleid_seq",
> ! "vtiger_audit_trial_auditid_seq",
> ! "vtiger_datashare_relatedmodules_datashare_relatedmodule_id_seq",
> ! "vtiger_relatedlists_relation_id_seq",
> ! "vtiger_inventory_tandc_id_seq",
> ! "vtiger_customview_cvid_seq",
> ! "vtiger_crmentity_crmid_seq",
> ! "vtiger_seactivityrel_crmid_seq",
> ! "vtiger_selectquery_queryid_seq",
> ! "vtiger_systems_id_seq",
> ! "vtiger_freetags_id_seq",
> ! "vtiger_inventorytaxinfo_taxid_seq",
> ! "vtiger_shippingtaxinfo_taxid_seq"
>   );
>  
>       foreach ($sequences as $sequence ) {
> *** vtiger_crm/include/database/Postgres8.php.rev9019 Mon Aug 21 12:46:37 2006
> --- vtiger_crm/include/database/Postgres8.php Mon Aug 21 12:49:27 2006
> ***************
> *** 156,161 ****
> --- 156,169 ----
>       elseif( $table == "vtiger_activity")
>   $subfields = array ( "activityid", "subject", "semodule", "activitytype", "date_start", "due_date", "time_start", "sendnotification", "duration_hours", "duration_minutes", "status", "eventstatus", "priority", "location", "notime", "visibility");
>  
> +     //vtiger_inventorytaxinfo
> +     elseif( $table == "vtiger_inventorytaxinfo")
> + $subfields = array ( "taxid", "taxname", "percentage", "deleted", "taxlabel");
> +
> +     //vtiger_producttaxrel
> +     elseif( $table == "vtiger_producttaxrel")
> + $subfields = array ( "productid", "taxid", "taxpercentage");
> +
>       //fields of the requested array still undefined
>       else
>   $log->info("function expandRecord: please add structural information for table '".$table."'");
> *** vtiger_crm/include/utils/InventoryUtils.php.rev9019 Mon Aug 21 12:42:38 2006
> --- vtiger_crm/include/utils/InventoryUtils.php Mon Aug 21 12:45:52 2006
> ***************
> *** 397,408 ****
>   }
>   if($available != 'all' && $available == 'available_associated')
>   {
> ! $query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.* FROM vtiger_inventorytaxinfo left JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = $productid or vtiger_inventorytaxinfo.deleted=0 group by vtiger_inventorytaxinfo.taxid";
>   }
>   else
>   {
>   $query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.* FROM vtiger_inventorytaxinfo INNER JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = $productid $where";
>   }
>   $res = $adb->query($query);
>   for($i=0;$i<$adb->num_rows($res);$i++)
>   {
> --- 397,413 ----
>   }
>   if($available != 'all' && $available == 'available_associated')
>   {
> ! $query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.* FROM vtiger_inventorytaxinfo left JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = $productid or vtiger_inventorytaxinfo.deleted=0 GROUP BY vtiger_inventorytaxinfo.taxid";
>   }
>   else
>   {
>   $query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.* FROM vtiger_inventorytaxinfo INNER JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = $productid $where";
>   }
> +
> + //Postgres 8 fixes
> + if( $adb->dbType == "pgsql")
> +    $query = fixPostgresQuery( $query, $log, 0);
> +
>   $res = $adb->query($query);
>   for($i=0;$i<$adb->num_rows($res);$i++)
>   {

> CREATE SEQUENCE vtiger_crmentity_crmid_seq  INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_customview_cvid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_datashare_relatedmodules_datashare_relatedmodule_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_freetags_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_inventory_tandc_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_relatedlists_relation_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_role_roleid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_audit_trial_auditid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_seactivityrel_crmid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_selectquery_queryid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
> CREATE SEQUENCE vtiger_systems_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

> _______________________________________________
> Get started with creating presentations online - http://zohoshow.com?vt 


--

--------------------------------------------------------------------------------
     /
 +##+|##+   STRAWBERRY                     Jens Hamisch
+v#+v v##+  EDV-Systeme GmbH               Managing director
/ v    v\v
| . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
|     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
 | .     |                                 Fax:          (+49 8171) 41805-59
 \   .  /   Tel.: (+49 8171) 41805-0       Email:        [hidden email]
  \____/    [hidden email]      


_______________________________________________
Get started with creating presentations online - http://zohoshow.com?vt 

9059-MainLine-1-Postgres8.patch (3K) Download Attachment