[Vtigercrm-developers] Help Required to support postgres in vtigerCRM 5.0
Jens Hamisch
jens at Strawberry.COM
Tue Aug 22 12:34:55 PDT 2006
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 <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
>
> *** 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: jens at Strawberry.COM
\____/ Strawberry at Strawberry.COM
-------------- next part --------------
*** vtiger_crm/include/database/Postgres8.php.rev9059 Tue Aug 22 19:58:20 2006
--- vtiger_crm/include/database/Postgres8.php Tue Aug 22 20:29:23 2006
***************
*** 124,173 ****
//return an expanded table field list
function expandRecord($table,$log)
{
$result = "";
! $log->info( "Debug: expandRecord");
! $subfields = array();
- //vtiger_products table
- if( $table == "vtiger_products" )
- $subfields = array ( "productid", "productname", "productcode", "productcategory", "manufacturer", "product_description", "qty_per_unit", "unit_price", "weight", "pack_size", "sales_start_date", "sales_end_date", "start_date", "expiry_date", "cost_factor", "commissionrate", "commissionmethod", "discontinued", "usageunit", "handler", "contactid", "currency", "reorderlevel", "website", "taxclass", "mfr_part_no", "vendor_part_no", "serialno", "qtyinstock", "productsheet", "qtyindemand", "glacct", "vendor_id", "imagename" );
-
- //vtiger_activity table
- elseif( $table == "vtiger_activity")
- $subfields = array ( "activityid", "subject", "semodule", "activitytype", "date_start", "due_date", "time_start", "time_end", "sendnotification", "duration_hours", "duration_minutes", "status", "eventstatus", "priority", "location", "notime", "visibility" );
-
- //vtiger_notes table
- elseif( $table == "vtiger_notes")
- $subfields = array ( "notesid", "contact_id", "title", "filename", "notecontent");
-
- //vtiger_faq table
- elseif( $table == "vtiger_faq")
- $subfields = array ( "id", "product_id", "question", "answer", "category", "status");
-
- //vtiger_profile2field
- elseif( $table == "vtiger_profile2field")
- $subfields = array ( "profileid", "tabid", "fieldid", "visible", "readonly");
-
- //vtiger_field
- elseif( $table == "vtiger_field")
- $subfields = array ( "tabid", "fieldid", "columnname", "tablename", "generatedtype", "uitype", "fieldname", "fieldlabel", "readonly", "presence", "selected", "maximumlength", "sequence", "block", "displaytype", "typeofdata", "quickcreate", "quickcreatesequence", "info_type");
-
- //vtiger_activity
- 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."'");
-
//construct an entity string
for( $i=0; $i<count($subfields); $i++)
{
--- 124,133 ----
//return an expanded table field list
function expandRecord($table,$log)
{
+ global $adb;
$result = "";
! $subfields = $adb->getColumnNames($table);
//construct an entity string
for( $i=0; $i<count($subfields); $i++)
{
More information about the vtigercrm-developers
mailing list