Data Structure, UITypes and key tables - radus28/Vtiger-DevOps GitHub Wiki

Database

Vtiger has an Enity-Relational database model. It is used for different purposes including below key scenarios..

  1. Store Entity records

    • vtiger_crmentity - Primary table for Entity modules, which linked it's PK into an FK of Module Tables
    • Module Table - Store primary record DATA of the module. It has module's name as suffix like vtiger_[MODULE]. Example
      • vtiger_products for Products module
      • vtiger_contactdetails for contacts.

    Check Modules' Class to find the tables of specific module. Example for Organization modules/Accounts/Accounts.php. Further check the root Entity class data/CRMEntity.php

  2. System configurations / settings

    • vtiger_settings_blocks ( Keeps top level menu of Vtiger > Settings )
    • vtiger_settings_fields (settings menu item)

  3. Module Configuration

    • vtiger_tabs - Primary Module table
    • vtiger_app2tabs - Sub table to keep parent tabs such as MARKETING, SALES, TOOL etc
    • vtiger_ws_entity - Module representation table for Web services (REST Api).
    • ... AND MORE ..

  4. Spefic configuration - Store different configuration such as SMTP, SMS Gateway, mailmanager etc..

    • vtiger_currency
    • vtiger_system - Stores SMTP Outgoing
    • vtiger_time_zone
    • vtiger_webforms - To generate web forms such as ContactUs, Subscription or Inquiry
    • vtiger_workflow_... - There are several table with this prefix to store all workflow related details
    • vtiger_eventhandler - Handles system events such as after.save, before.save, after.delete
    • .. AND MORE ..

  5. Picklist Tables - for drop down values. Example vtiger_invoicestatus stores values of Invoice>Status field

  6. History - Update history is an extension module ModTracker. Tables related to this are

    • vtiger_modtracker_basic
    • vtiger_modtracker_details

  7. Comments - for extension module "ModComments"

    • vtiger_modcomments

  8. RBAC - Role based access control

  • Users - vtiger_users
  • Roles - vtiger_roles
  • Groups - vtiger_groups
  • User - Role assignment - vtiger_user2role
  • User - Group assignment - vtiger_user2group
  1. Related list and Links
    • vtiger_relatedlist - keeps relation between modules. Example Contacts Tab under Organization detail view
    • vtiger_links - Keeps externally loaded Links.
  2. Fields - Definition of each fields by UITypes
  • vtiger_fields - Primary Field definition table
  • vtiger_ws_fieldtype - FieldType/UIType definition
  • ws_entity_referencetype - Reference Field (UIType 10,70 etc) definitions
  • vtiger_blocks - The fields are grouped into fields. Example "General Information", "Contact information", "Address Information"

Manipulating Database

The database primary class is include/database/PearDatabase.php

**Sample code ** Below code can be used anywhere inside a method of Actions / Views / Models or any other vtiger auto loaded class

global $adb;

$result = $adb->pquery("SELECT * FROM vtiger_organizationdetails", array());

$num_rows = $adb->num_rows($result);

if($num_rows) {

$resultrow = $adb->fetch_array($result);// returns first row, call it recursively to get all rows

}

Alternatively the $adb object can be obtained

$adb = PearDatabase::getInstance();

Looping to get all values

while($row = $adb->fetch_array($result)){

...

}

Refer PearDatabase.php for further details

Sequence Tables

A sequence table is used to increment every entity tables PK. These tables ends with suffix '_seq'. Example

  • vtiger_field -> vtiger_field_seq
  • vtiger_crmentity -> vtiger_crmentity_seq
  • vtiger_links -> vtiger_links_seq and more.

Before inserting a value into main table it increments the seq table using PearDatabase->getUniqueID($seqname)

Picklist tables

If a new picklist table is created,

  • it creates 2 tables with the [fieldname] like
    • vtiger_[FIELDNAME]
    • vtiger_[FIELDNAME]_seq example in Contacts module Gender (Male | Female). It creates new table vtiger_gender AND it's seq table vtiger_gender_seq
  • Adds an entry in vtiger_picklist
  • Increment the value in vtiger_picklistvalue_seq

UITypes

  • UITypes are field type definition in vtiger. Example

    • Text - UItype 1
    • date - UIType 5 or 6
    • Picklist - UIType 15 or 16

  • Refer UITypes definitions