[Vtigercrm-developers] Help Required to support postgres in vtigerCRM 5.0

Jens Hamisch jens at Strawberry.COM
Mon Aug 21 04:16:07 PDT 2006


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 <jens at Strawberry.COM> 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:        jens at Strawberry.COM
>   \____/    Strawberry at Strawberry.COM      

-- 

--------------------------------------------------------------------------------
     /
 +##+|##+   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:        jens at Strawberry.COM
  \____/    Strawberry at Strawberry.COM      

-------------- next part --------------
*** 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++)
  		{
-------------- next part --------------
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;


More information about the vtigercrm-developers mailing list