Sugar CRM Model Auditing

In our last SugarCRM article, we promised to get to creating your own SugarCRM model/bean objects. Before we can do that though, we need to cover one of the special features that are built in to all SugarCRM beans: Data Auditing.

The following code samples should be run in a SugarCRM bootstrapped environment. Just as we’ve done in previous articles, we’ll be writing code in the list controller action method, and exiting before the full page renders. While the specifics here were tested against SugarCRM Community Edition 6.5.10, the concepts should apply to all modern version of SugarCRM.

Bean Review

First, let’s write some PHP to create a new Accounts entry

#File: modules/Helloworld/controller.php
public function action_listview()
{              
    $o = BeanFactory::getBean('Accounts');                
    $o->name='Our Lovely Fake Unique Company';
    $o->save();        
    var_dump(__METHOD__);
    exit("Model Saved");
}

Run your code (in our case by calling the module’s default page at http://sugar-crm.dev/index.php?module=Helloworld), open your favorite database browser, and take a look at the accounts table. You should see your data in the database.

mysql> SELECT * 
    -> FROM accounts 
    -> WHERE name LIKE 'Our%'\G

*************************** 1. row ***************************
                         id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
                       name: Our Lovely Fake Unique Company
               date_entered: 2013-03-31 03:42:47
              date_modified: 2013-03-31 03:42:47
           modified_user_id: 85559995-ca27-d43b-ab23-51271051cee8
                 created_by: 1
                description: NULL
                    deleted: 0
           assigned_user_id: NULL
...

We can also load and edit our model with code that looks like this.

#File: modules/Helloworld/controller.php
public function action_listview()
{              
    $o = BeanFactory::getBean('Accounts')
    ->retrieve_by_string_fields(array('name'=>'Our Lovely Fake Unique Company'));                
    $o->name='Our Better Lovely Fake Unique Company';
    $o->save();        
}

If we run the above and take a look at the database table again, we’ll see that the name column has been updated

mysql> SELECT id, name 
    -> FROM accounts 
    -> WHERE name like 'Our%'\G

*************************** 1. row ***************************
  id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
name: Our Better Lovely Fake Unique Company
1 row in set (0.00 sec)

So far, pretty standard stuff, and nothing we haven’t already encountered.

Bean Audit Table

Next, lets take a look at some of the other tables in the database.

mysql> SHOW tables LIKE 'accounts%';
+--------------------------------+
| Tables_in_sugarcrm (accounts%) |
+--------------------------------+
| accounts                       |
| accounts_audit                 |
...

In addition to the accounts table, there’s also a table named accounts_audit. If you take a look at this table’s contents, you’ll see information about our original account name in the before_value_string column

mysql> SELECT * 
    -> FROM accounts_audit\G
*************************** 1. row ***************************
                 id: ac97b9db-3f45-8f58-0b2e-5157b1dbf320
          parent_id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
       date_created: 2013-03-31 03:44:53
         created_by: 1
         field_name: name
          data_type: name
before_value_string: Our Lovely Fake Unique Company
 after_value_string: Our Better Lovely Fake Unique Company
  before_value_text: NULL
   after_value_text: NULL
1 row in set (0.00 sec)

Let’s try loading the model and making another change.

#File: modules/Helloworld/controller.php
public function action_listview()
{              
    $o = BeanFactory::getBean('Accounts')
    ->retrieve_by_string_fields(array('name'=>'Our Better Lovely Fake Unique Company'));                
    $o->name='Our Best Lovely Fake Unique Company';
    $o->save();        
}

After running the above code, you’ll see we now have two rows in the accounts_audit table

mysql> SELECT * 
    -> FROM accounts_audit\G    
*************************** 1. row ***************************
                 id: 1403080d-6be2-5d76-4991-5159d39c3587
          parent_id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
       date_created: 2013-04-01 18:37:18
         created_by: 1
         field_name: name
          data_type: name
before_value_string: Our Better Lovely Fake Unique Company
 after_value_string: Our Best Lovely Fake Unique Company
  before_value_text: NULL
   after_value_text: NULL
*************************** 2. row ***************************
                 id: ac97b9db-3f45-8f58-0b2e-5157b1dbf320
          parent_id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
       date_created: 2013-03-31 03:44:53
         created_by: 1
         field_name: name
          data_type: name
before_value_string: Our Lovely Fake Unique Company
 after_value_string: Our Better Lovely Fake Unique Company
  before_value_text: NULL
   after_value_text: NULL
2 rows in set (0.12 sec)

The new row recorded our second change to the model. This is SugarCRM’s bean auditing system. Every bean in SugarCRM has the option of having an audit table. This audit table will keep track of changes to certain column values, allowing a data miner to view the history of a particular object over its lifetime.

If you search your database, you’ll see that eight of SugarCRM’s default beans have audit tables.

mysql> SHOW tables LIKE '%_audit';

+------------------------------+
| Tables_in_sugarcrm (%_audit) |
+------------------------------+
| accounts_audit               |
| bugs_audit                   |
| campaigns_audit              |
| cases_audit                  |
| contacts_audit               |
| leads_audit                  |
| opportunities_audit          |
| project_task_audit           |
+------------------------------+

Audit Fields

Of course, all bean fields aren’t created equal. Let’s try changing the value of our Account’s industry

#File: modules/Helloworld/controller.php
$o = BeanFactory::getBean('Accounts')
->retrieve_by_string_fields(array('name'=>'Our Best Lovely Fake Unique Company'));                
$o->industry    = 'WIDGET CRANKING';
$o->save();        

If you look at the database, the value has been changed

mysql> SELECT name,industry
    -> FROM accounts 
    -> WHERE industry = 'WIDGET CRANKING'\G

*************************** 1. row ***************************
    name: Our Best Lovely Fake Unique Company
industry: WIDGET CRANKING

However, the audit table contains no record of this.

mysql> SELECT * 
    -> FROM accounts_audit\G    

*************************** 1. row ***************************
                 id: 1403080d-6be2-5d76-4991-5159d39c3587
          parent_id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
       date_created: 2013-04-01 18:37:18
         created_by: 1
         field_name: name
          data_type: name
before_value_string: Our Better Lovely Fake Unique Company
 after_value_string: Our Best Lovely Fake Unique Company
  before_value_text: NULL
   after_value_text: NULL
*************************** 2. row ***************************
                 id: ac97b9db-3f45-8f58-0b2e-5157b1dbf320
          parent_id: 5a8e771c-6f41-f2af-8a9b-5157b0e0d7be
       date_created: 2013-03-31 03:44:53
         created_by: 1
         field_name: name
          data_type: name
before_value_string: Our Lovely Fake Unique Company
 after_value_string: Our Better Lovely Fake Unique Company
  before_value_text: NULL
   after_value_text: NULL

2 rows in set (0.12 sec)

That’s because bean fields, by default, are not audited. Auditing beans on a per field basis means an additional INSERT for every “auditable” field, which means making every field auditable would quickly create a performance bottleneck (one that would make Magento’s EAV system look lightweight by comparison).

You can grab a list of which fields are auditable by using the bean’s getAuditEnabledFieldDefinitions method.

$o = BeanFactory::getBean('Accounts');
$fields = $o->getAuditEnabledFieldDefinitions();
foreach($fields as $field)
{
    var_dump($field['name']);
}
var_dump(__METHOD__);
exit;

On my factory default system, this results in the following output.

string 'name' (length=4)

string 'assigned_user_id' (length=16)

string 'phone_office' (length=12)

string 'parent_id' (length=9)

As you can see, only the name, assigned_user_id, phone_office, and parent_id fields are auditable.

It’s also possible to configure an entire bean as auditable or not. Remember, while the default SugarCRM system ships with over 40 beans, we only found 8 audit tables above. That means there’s over 32 beans that are not auditable.

If you need to, you can check if a bean is auditable or not with the bean’s is_AuditEnabled method.

$o = BeanFactory::getBean('Accounts');
if($o->is_AuditEnabled())
{
    var_dump(get_class($o) . ' is being audited.');
}
else
{
    var_dump(get_class($o) . ' is not being audited.');    
}    

Wrap Up

That, in a nutshell, is auditing. Remember, a good CRM application has two purposes in life. First, it’s the operating system you use to direct your employees/sales-force in their day-to-day tasks. Second, it’s a system to review and track progress and performance. The bean auditing system serves the second goal — CRM users can do anything they like to the bean data, but you’ll never lose a key piece of information just because a user goes on a data cleanup spree.

The auditing features are intertwined deeply with the standard bean setup code and configuration. Next time we’ll look at creating our own bean object, including how to setup fields as auditable or not.