Tuesday, January 24, 2012

Backing up SugarCrm and MySql

When running SugarCrm in a production environment, you need to do backup. There are many opinions about how to do backup:
Only wimps use tape backup: real men just upload their important stuff on ftp, and let the rest of the world mirror it ;)

(Torvalds, Linus (1996-07-20). Post. linux.dev.kernel newsgroup. Google Groups. Retrieved on 2006-08-28.)
However the above might not be what your boss want, so we need to schedule backup.

Backing up SugarCrm is basically 2 separate operations.
  1. zipping  up your working site directory structure
  2. dumping the associated SugarCrm database
On a Linux system #1 can be done with the tar command (or any other file packer of preference).
tar -czf SITE_DIRECTORY.tar.gz SITE_DIRECTORY

For #2 it depends on the database that you are using. In the remaining part of this posting I will be using MySQL and InnoDB as storage engine.

There are numerous scopes for backing up a database:

Normally I prefer the full backup option combined with a block level backup plan for incremental backup.

Simple full backup can be controlled using AutoMySQLBackup. There are however additional parameters that I like to add to the mysqldump command used inside the script. If you are using any kinds of functions/stored procedures in the database, then you want to add --triggers --routines to the mysqldump command. I also like to use the --single-transaction option on InnoDB storage engine (this is also mentioned here as an upcoming feature for AutoMySQLBackup).
The above mentioned options/parameters will change the lines in the script from 

mysqldump --user=XXX -- password=YYY ... 

to

mysqldump --user=XXX -- password=YYY --triggers --routines --single-transaction ...


For incremental backup please see the ibbackup command or the mysqlbackup command.

The majority of our servers are running on Amazon EC2. We use a combination of running full backups nightly as well as Amazon Elastic Block Store (EBS) (block level backup) snapshots through the day. This requires a slightly special setup with regards to your filesystem of choice. You can read more at Creating Consistent EBS Snapshots with MySQL and XFS on EC2 or if you prefer CentOS then you can have a look at my blog posting Setting up Centos 5.6 with PHP 5.3 on Amazon EC2 with ec2-consistent-snapshot.

Friday, January 13, 2012

Insert, retrieve and delete a record using SugarCrm webservices from C#/WCF

SugarCrm consists of of a core framework, MVC modules and UI widgets called dashlets, as well as a lot of other functionality. Some modules like the built in CRM modules are handcrafted, to contain very specific business logic, and other are generated using a built in tool called Module Builder. 

On top of all the modules generated through module builder, SugarCrm supplies a set of generic web services, that can be used to manipulate records in those modules, provided that you have a valid username and password. This allows for integration from other systems.

I will show how you can insert, retrieve and delete a record using C# and WCF.

First a simple supporting class for throwing exceptions
using System;

namespace SugarCrmWebserviceTests
{
 public class SugarCrmException : Exception
 {
  public SugarCrmException(string errorNumber, string description) : base (description)
  {
   ErrorNumber = errorNumber;
  }

  public string ErrorNumber { get; private set; }
 }
}

Then the full integration test with supporting helper private methods
  1. insert of a record with known values
  2. retrieve the just inserted record and compare with the known values
  3. mark the record deleted (this is also an example of how to do an record update)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.ServiceModel;
using System.Text;
using NUnit.Framework;
using SugarCrmWebserviceTests.SugarCrm;

namespace SugarCrmWebserviceTests
{
 public class WebserviceTests
 {
  // The internal sugarcrm name for the module (normally the same as the underlying table)
  private const string ModuleName = "Incident";
  
  // The key matches the module field name (database table column name) 
  // and the value is the value to be inserted/update into this field
  // id is a special field:
  // if id exists in the database then you will update that row
  // if you leave it empty (string.Empty) then you will insert a new row
  private readonly Dictionary<string,string> _incident = new Dictionary<string, string>
       {
        {"id", string.Empty},
        {"name", "Clark Kent"},
        {"description", "Something is rotten in the state of Denmark"},
        {"status", "Open"},
        {"category", "Question"}
       };

  private const string ServiceUrl = "http://somesugarcrminstall.somwhere.com/soap.php";
  private const string SugarCrmUsername = "YOUR_SUGAR_USERNAME";
  private const string SugarCrmPassword = "YOUR_SUGAR_PASSWORD";

  private readonly sugarsoapPortType _sugarServiceClient;
  private string SessionId { get; set; }
  private string RecordId { get; set; }

  public WebserviceTests()
  {
   _sugarServiceClient = ChannelFactory<sugarsoapPortType>.CreateChannel(new BasicHttpBinding(),
                      new EndpointAddress(ServiceUrl));
  }

  [TestFixtureSetUp]
  public void BeforeAnyTestsHaveRun()
  {
   // The session is valid until either you are logged out or after 30 minutes of inactivity
   Login();
  }

  [TestFixtureTearDown]
  public void AfterAllTestsHaveRun()
  {
   Logout();
  }

  [Test]
  public void InsertReadDelete()
  {
   InsertRecordInSugarCrmModule();
   RetrieveInsertedRecordFromSugarCrmModule();
   DeleteEntry();
  }

  private void InsertRecordInSugarCrmModule()
  {
   // arrange

   // act
   var result = _sugarServiceClient.set_entry(SessionId, ModuleName, _incident.Select(nameValue => new name_value { name = nameValue.Key, value = nameValue.Value }).ToArray());
   CheckResultForError(result);
   RecordId = result.id; 

   // assert
   Assert.AreEqual("0", result.error.number);
  }
  
  private void RetrieveInsertedRecordFromSugarCrmModule()
  {
   // arrange
   var fieldsToRetrieve = new[] { "id", "name", "description", "status", "category" };

   // act
   var result = _sugarServiceClient.get_entry(SessionId, ModuleName, RecordId, fieldsToRetrieve);
   CheckResultForError(result);

   // assert
   Assert.AreEqual(_incident["name"], GetValueFromNameValueList("name", result.entry_list[0].name_value_list));
   Assert.AreEqual(_incident["description"], GetValueFromNameValueList("description", result.entry_list[0].name_value_list));
   Assert.AreEqual(_incident["status"], GetValueFromNameValueList("status", result.entry_list[0].name_value_list));
   Assert.AreEqual(_incident["category"], GetValueFromNameValueList("category", result.entry_list[0].name_value_list));
  }
  
  private void DeleteEntry()
  {
   // arrange
   var deletedIncident = new Dictionary<string, string>
       {
        {"id", RecordId},
        {"deleted", "1"},
       };   

   // act
   var result = _sugarServiceClient.set_entry(SessionId, ModuleName, deletedIncident.Select(nameValue => new name_value { name = nameValue.Key, value = nameValue.Value }).ToArray());
   CheckResultForError(result);

   // assert
  }

  private void Login()
  {
   var sugarUserAuthentication = new user_auth { user_name = SugarCrmUsername, password = Md5Encrypt(SugarCrmPassword) };
   var sugarAuthenticatedUser = _sugarServiceClient.login(sugarUserAuthentication, "Some Application Name");
   SessionId = sugarAuthenticatedUser.id;
  }

  private void Logout()
  {
   //logout
   _sugarServiceClient.logout(SessionId);
  }

  private static string GetValueFromNameValueList(string key, IEnumerable<name_value> nameValues)
  {
   return nameValues.Where(nv => nv.name == key).ToArray()[0].value;
  }

  /// <summary>
  /// You can only call this method with objects that contain SugarCrm.error_value classes in the root 
  /// and the property accessor is called error. There is no compile time checking for this, if you pass
  /// an object that does not contain this then you will get a runtime error
  /// </summary>
  /// <param name="result">object contain SugarCrm.error_value class in the root and the property accessor is called error</param>
  private static void CheckResultForError(dynamic result)
  {
   if (result.error.number != "0" && result.error.description != "No Error")
   {
    throw new SugarCrmException(result.error.number, result.error.description);
   }
  }

  private static string Md5Encrypt(string valueString)
  {
   var ret = String.Empty;
   var md5Hasher = new MD5CryptoServiceProvider();
   var data = Encoding.ASCII.GetBytes(valueString);
   data = md5Hasher.ComputeHash(data);
   for (int i = 0; i < data.Length; i++)
   {
    ret += data[i].ToString("x2").ToLower();
   }
   return ret;
  }
 }
}