[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