Monday, November 26, 2012

High performance cachable websites web 2.0 in YiiFramework, mustache.js and icanhaz.js

Preface: 

To be able to cache webpage content as much as possible then it is important to delay merging data with the HTML markup until the last possible moment (which is in the browser). The moment you merge data with HTML then the page is always specific to the context in which it was retrieved. If this context happens to be a user context (which is almost always the case), then you cannot use this cached page for another user, which is bad for cacheability. 

Achieving this "late-merging" of data and markup can be achieved by building the skeleton of the site in very few html pages. Most normal sites could potentially be built using the following skeleton pages:

  • frontpage/landingpage 
  • login 
  • content 
Of course the more the pages differ from each other the more of these skeleton pages you will have. The point is to isolate the common things between the pages and boil it down to the minimum amount of pages. 

These pages (one for each distinct different buildup of the page) will contain a link to a javascript file that contains all the clientside templates/markup needed to render data. These clientside templates, can be built in one of the many templating js engines out there, for instance:

(LinkedIn's review of quite a few of these template engines The client-side templating throwdown: mustache, handlebars, dust.js, and more). 


This way we can cache the clientside needed markup used to display lists and forms (since it is a static javascript file) using nginx or other caching servers. The skeleton pages can also be cached since they contain no data. The only thing remaining is CSS, javascript, images, videos and data. Everything apart from data can also be cached using caching servers. Data we will retrieve using AJAX requests as json and then rendering it clientside. 

YiiFramework: 

I am new to the Yii framework, but needed to build a web 2.0 site that should support caching of as much of the content as possible to avoid overloading the webserver re-sending the same markup over and over again just with different data embedded. As far as I could see there was not really any built-in support in Yii to do this (apart from doing it all manually). The exising Mustache extention seemed to be related to server side templating rather than clientside templating. For this reason I created my own extension. 

The goals were: 
  1. Creating views in Yii clientside should be as similar as possible as creating server side views 
  2. Templates should be accessible on the clientside without a lot of plumbing code 
  3. Rendering templates should be clean. 

In the following I will try to argue for my decisions on how to solve the above.

- Creating views in Yii clientside should be as similar as possible as creating server side views
&
-Templates should be accessible on the clientside without a lot of plumbing code

I wanted to be able to put my clientside views in what folder I though made the most sense, and since the clientside templates very much are related to the controller actions that are delivering the data (just like server side views) I wanted to be able to put them for instance in the view folders.
I wanted it to be easy to edit the templates in an IDE and one template should be self contained and not mixed up with the other templates

The templates should not incur a significant serverside overhead (currently I am still working on a better solution than the one I have found - mentioned at the end of the posting).

To achieve the above I decided that in the IDE the client side templates should be seperate files, and to be able to distinguish them from the server side templates then needed another name that supported the js templating engine that I choose (mustache.js), so the file extension ended up being .tpl (since Smarty templates are supported in my IDE).

So the challenge is how to convert disparate files on the filesystem into a single js file that is read and initialized by the browser automatically.

I need go through the filesystem recursively and look for files of a certain kind (*.mustache.tpl), index them by they location in the filesystem and their filename. Push them info a javascript array and add the code needed to initialize the templates when the browser loads the template javascript  file.

All the above is solved in the following Yii component, which also adds the needed javascripts for the template rendering clientside.


<?php
/**
 * ClientsideViews class file.
 * @author Kenneth Thorman (kenneth.thorman@appinux.com)
 * @license http://www.opensource.org/licenses/bsd-license.php New BSD License
 */

/**
 * ClientsideViews application component.
 * Used for registering ClientsideViews core functionality.
 */
class ClientsideViews extends CApplicationComponent {

    /**
     * @var boolean whether to register jQuery and the ClientsideViews JavaScript.
     */
    public $enableJS = true;

    protected $_assetsUrl;
    protected $_assetsPath;

    /**
     * Initializes the component.
     */
    public function init( ) {
        if( !Yii::getPathOfAlias( 'clientsideviews' ) )
            Yii::setPathOfAlias( 'clientsideviews', realpath( dirname( __FILE__ ).'/..' ) );
        
        $generatedTemplateFile = Yii::getPathOfAlias( 'clientsideviews.assets.javascripts' ) . DIRECTORY_SEPARATOR . 'mustache.tpl.js';
        if (!is_file($generatedTemplateFile)) {
            $this->refreshMustacheTemplates();            
        }
        
        if( $this->enableJS ) {
            $this->registerJs( );
        }
    }

    /**
     * Registers the core JavaScript plugins.
     * @since 0.9.8
     */
    public function registerJs( ) {
  
  Yii::app( )->clientScript->registerCoreScript( 'jquery' );
        $this->registerScriptFile( 'ICanHaz.min.js' );
        $this->registerScriptFile( 'mustache.tpl.js' );
    }

    /**
     * Registers a JavaScript file in the assets folder.
     * @param string $fileName the file name.
     * @param integer $position the position of the JavaScript file.
     */
    public function registerScriptFile( $fileName, $position = CClientScript::POS_END ) {
        Yii::app( )->clientScript->registerScriptFile( $this->getAssetsUrl( ).DIRECTORY_SEPARATOR.$fileName, $position );
    }

    /**
     * Returns the URL to the published assets folder.
     * @return string the URL
     */
    protected function getAssetsUrl( ) {
        if( $this->_assetsUrl == null ) {
            $assetsPath = Yii::getPathOfAlias( 'clientsideviews.assets.javascripts' );
            $this->_assetsUrl = Yii::app( )->assetManager->publish( $assetsPath, false, -1, YII_DEBUG );
        }
        return $this->_assetsUrl;
    }
 
    public function refreshMustacheTemplates()
    {
        /* 
        Find all files recursivly in the basepath/protected named mustache.tpl
        Foreach files add to js array with a name based on the directory path and filename without 
        mustache.tpl
        
        */
        $basePath = Yii::app()->basePath;
        $templates = array();
        $options=  array('fileTypes'=>array('tpl'));
        $templateFiles = CFileHelper::findFiles(realpath(Yii::app()->basePath),$options);
        foreach($templateFiles as $file){
            // stupid additional check due to the findFiles function cannot handle . seperated filenames
            if (strpos($file,'mustache') !== false) {
                $templateId = str_replace(array($basePath,DIRECTORY_SEPARATOR,'mustache.tpl','.'),array('','_','',''),$file);
                array_push($templates, array(
                    'name' => $templateId,
                    'template' => $this->stripEndLine($this->readTemplate($file)))
                );
            }
        }

        $templatesJs = "$.each(".json_encode($templates).", function (index, template) {ich.addTemplate(template.name, template.template);});";
        $this->writeTemplateFile(Yii::getPathOfAlias( 'clientsideviews.assets.javascripts' ), $templatesJs);        
 }
    
    private function writeTemplateFile($path,$fileContents)
    {
        $my_file = $path. DIRECTORY_SEPARATOR . 'mustache.tpl.js';
        $handle = fopen($my_file, 'w') or die('Cannot open file:  '.$my_file);
        fwrite($handle, $fileContents);
        fclose($handle);
    }
        
    private function readTemplate($file)
    {

        $handle = fopen($file, 'r');
        $data = fread($handle,filesize($file));
        fclose($handle);
        return $data;
    }
    
    private function stripEndLine($template)
    {
        $output = str_replace(array("\r\n", "\r"), "\n", $template);
        $lines = explode("\n", $output);
        $new_lines = array();

        foreach ($lines as $i => $line) {
            if(!empty($line))
                $new_lines[] = trim($line);
        }
        return implode($new_lines);        
    }
 
}

By initilizing the ICanHaz javascript Mustache template wrapper in the mustache.tpl.js file then the templates are ready for use in the browser without manually having to register the templates.


$.each(
[
{"name":"_views_meeting_meetingList",
"template":"<table class='responsive'><th>Meeting ID<\/th><th>Meeting Name<\/th><th>Create Time<\/th><th>Running<\/th>{{#meetings}}<tr><td>{{meetingID}}<\/td><td>{{meetingName}}<\/td><td>{{createTime}}<\/td><td>{{running}}<\/td><\/tr>{{\/meetings}}<\/table>"}
], 
function (index, template) {ich.addTemplate(template.name, template.template);}
);


3. Rendering templates should be clean.

What now remains is the actual code that is rendering data from a controller call with the clientside template.

The controller that is returning some data in json format

public function actionMeetingList()
 {
        ...

        //show all meetings
        $meetings=$bbb->getMeetings();
        echo json_encode($meetings) ;
 }


And finally the clientside code responsible for the merging of data and the clientside templates.


<div id="meeting_list"></div>


<script type="text/javascript">
    $(document).ready(function () {
        /* 
        Getting data and rendering in template
        */
        $.getJSON('/meeting/meetinglist', function (meetings) {
            renderedTemplate = ich._views_meeting_meetingList({'meetings': meetings});
            $('#user_list').append(renderedTemplate);
        });
    });
</script>

A final note, currently to avoid a full traversal of the filesystem on every request, I have added a check in the function init() in the ClientsideViews Yii component. This checks if the file exists on the file system. This is a tradeoff between my limited knowledge of Yii and how to better implement this in the framework, avoiding traversing the filesystem (very slow) for each request and manually being able to trigger a refresh by deleting the file located under /assets/javascripts/mustache.tpl.js. I am sure someone with more knowledge of the framework know the right way to hook this up to automate the refreshing when the files change. This however works for my purpose. I have created a github repository that is available at Yii ClientsideViews extension. I have attempted to create a new extension on the YiiFramework website, but was not able to since apparently I am "too new" on the site.

Wednesday, July 04, 2012

Pentaho CE ReportViewer Localization is not localizing

When creating reports and publishing them under Pentaho it would be nice if you could localize the web interface just like for dashboards etc...

This showed out not to be as simple as expected.

I spent some time searching Google and then I posted the following forum post at How to translate the remaining labels on the ReportViewer. I was pretty busy for a few days and then I went back and searched Google some more and it seemed like I missed a pretty obvious link Report Viewer Localization. After following this only just doing the steps on the pentaho-reporting-engine-classic-core-platform-plugin-4.1.0-stable.jar file some of the labels were translated.

All was however not well since there was quite a few labels and strings that could be localized still. Searching inside all the jar files for specific strings led me to belive that I needed to translate some messages.properties files in the pentaho-reporting-engine-classic-core-3.8.3-GA.jar file as well.

This task was a bit larger than the previous jar file so I was pretty satisfied when it was time to upload the file to the server and test it. My woes were not over yet though, and nothing I did seemed to work. I contacted some knowledgeable Pentaho developers at Sigma Infosolutions and asked them if they could have a look at the problem and it showed out that a small code change was required in the Pentaho code to make the localization work.

Index: Messages.java
===================================================================
--- Messages.java (revision 662)
+++ Messages.java (working copy)
@@ -1,36 +1,38 @@
-// Decompiled by DJ v3.12.12.96 Copyright 2011 Atanas Neshkov  Date: 7/2/2012 5:25:55 PM
-// Home Page: http://members.fortunecity.com/neshkov/dj.html  http://www.neshkov.com/dj.html - Check often for new version!
-// Decompiler options: packimports(3) 
-// Source File Name:   Messages.java
-
-package org.pentaho.reporting.engine.classic.core.parameters;
-
-import java.util.Locale;
-
-import org.pentaho.reporting.libraries.base.util.ObjectUtilities;
-import org.pentaho.reporting.libraries.base.util.ResourceBundleSupport;
-
-public class Messages extends ResourceBundleSupport
-{
-  private static Messages instance;
-
-  public static Messages getInstance()
-  {
-    // its ok that this one is not synchronized. I dont care whether we have multiple instances of this
-    // beast sitting around, as this is a singleton for convinience reasons.
-    if (instance == null)
-    {
-      instance = new Messages();
-    }
-    return instance;
-  }
-
-  /**
-   * Creates a new instance.
-   */
-  private Messages()
-  {
-    super(Locale.getDefault(), "org.pentaho.reporting.engine.classic.core.parameters.messages",
-        ObjectUtilities.getClassLoader(Messages.class));
-  }
-}
+// Decompiled by DJ v3.12.12.96 Copyright 2011 Atanas Neshkov  Date: 7/2/2012 5:25:55 PM
+// Home Page: http://members.fortunecity.com/neshkov/dj.html  http://www.neshkov.com/dj.html - Check often for new version!
+// Decompiler options: packimports(3) 
+// Source File Name:   Messages.java
+
+package org.pentaho.reporting.engine.classic.core.parameters;
+
+import org.pentaho.platform.util.messages.LocaleHelper;
+
+import org.pentaho.reporting.libraries.base.util.ObjectUtilities;
+import org.pentaho.reporting.libraries.base.util.ResourceBundleSupport;
+
+public class Messages extends ResourceBundleSupport
+{
+  private static Messages instance;
+
+  public static Messages getInstance()
+  {
+    // its ok that this one is not synchronized. I dont care whether we have multiple instances of this
+    // beast sitting around, as this is a singleton for convinience reasons.
+ /*Changed the singleton Instance to the non-singleton Instance by removing the if(instance=null) condition */    
+ //if (instance == null)
+    //{
+      instance = new Messages();
+    //}
+    return instance;
+  }
+
+  /**
+   * Creates a new instance.
+   */
+  private Messages()
+  {
+ /*Changed the Locale.getDefault() to LocaleHelper.getLocale() in order to localize the parameters according to the selected locale*/
+    super(LocaleHelper.getLocale(), "org.pentaho.reporting.engine.classic.core.parameters.messages",
+        ObjectUtilities.getClassLoader(Messages.class));
+  }
+}

Thursday, June 14, 2012

Upgrading Redmine from 1.2.2 to 2.0.2

I needed to upgrade a Redmine installation that was pretty old to the newest release 2.0.2. I was following this document on how to upgrade Redmine http://www.redmine.org/projects/redmine/wiki/RedmineUpgrade. When I got to Step 4 - Update the database

I received the following error

root@xxx #rake db:migrate RAILS_ENV=production --trace
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
rake aborted!
no such file to load -- dispatcher
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:251:in `require'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:251:in `block in require'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:236:in `load_dependency'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:251:in `require'
/vol/www/sites/redmine2.appinux.com/plugins/redmine_scm/init.rb:2:in `<top (required)>'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:251:in `require'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:251:in `block in require'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:236:in `load_dependency'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:251:in `require'
/vol/www/sites/redmine2.appinux.com/lib/redmine/plugin.rb:129:in `block in load'
/vol/www/sites/redmine2.appinux.com/lib/redmine/plugin.rb:120:in `each'
/vol/www/sites/redmine2.appinux.com/lib/redmine/plugin.rb:120:in `load'
/vol/www/sites/redmine2.appinux.com/config/initializers/30-redmine.rb:13:in `<top (required)>'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:245:in `load'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:245:in `block in load'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:236:in `load_dependency'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.2.5/lib/active_support/dependencies.rb:245:in `load'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/engine.rb:588:in `block (2 levels) in <class:Engine>'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/engine.rb:587:in `each'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/engine.rb:587:in `block in <class:Engine>'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/initializable.rb:30:in `instance_exec'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/initializable.rb:30:in `run'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/initializable.rb:55:in `block in run_initializers'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/initializable.rb:54:in `each'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/initializable.rb:54:in `run_initializers'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/application.rb:136:in `initialize!'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/railtie/configurable.rb:30:in `method_missing'
/vol/www/sites/redmine2.appinux.com/config/environment.rb:14:in `<top (required)>'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/application.rb:103:in `require'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/application.rb:103:in `require_environment!'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/railties-3.2.5/lib/rails/application.rb:292:in `block (2 levels) in initialize_tasks'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `call'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `block in execute'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `each'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `execute'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/usr/local/rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:176:in `block in invoke_prerequisites'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:174:in `each'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:174:in `invoke_prerequisites'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:157:in `block in invoke_with_call_chain'
/usr/local/rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/task.rb:144:in `invoke'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:116:in `invoke_task'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block (2 levels) in top_level'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `each'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block in top_level'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:88:in `top_level'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:66:in `block in run'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/lib/rake/application.rb:63:in `run'
/usr/local/rvm/gems/ruby-1.9.2-p180/gems/rake-0.9.2.2/bin/rake:33:in `<top (required)>'
/usr/local/rvm/gems/ruby-1.9.2-p180/bin/rake:19:in `load'
/usr/local/rvm/gems/ruby-1.9.2-p180/bin/rake:19:in `<main>'
Tasks: TOP => db:migrate => environment 
This was solved by removing the redmine_scm plugin that I copied to the plugin folder in Step 3 - Perform the upgrade - Option 1 - Downloaded release (tar.gz or zip file)

Monday, June 04, 2012

SugarCrm 6.3.1: Enhancing Importer - allow importing of related module data

Often when importing data into SugarCrm the built-in importer at a module level is working great. However if you have a need to import data that is actually modeled in SugarCrm as being 2 modules (tables) with a many-many relationship in between then the built-in importer comes up short.

The full patch can be found at the bottom of this posting. This is a non upgradesafe change. Apply at your own risk.

Let us assume that we have the following entity relational model in SugarCrm that has been configured in SugarCrm Studio or in SugarCrm Module Builder.


Module Builder

The Sugar Module Builder enables users to build custom modules from scratch or combine existing or custom objects into a brand new CRM module. Developers can leverage existing Sugar Objects such as Contacts, Accounts, Documents, Cases, and Opportunities to build a new module or create their own custom objects from scratch to form a new module. Users can build an unlimited number of custom modules, which interoperate seamlessly with Reporting, Workflow, and Sugar Studio tools. Upgrades for custom modules are fully supported. Building new modules allows developers to extend Sugar beyond the typical CRM functions and optimize Sugar for any xRM (any Relationship Management) function.

Positive Impacts

  • Create custom modules to track information critical to your business
  • Use pre-defined objects or create custom objects for the new module
  • Share or charge for custom objects on SugarForge and Sugar Exchange.

Sugar Studio

Sugar Studio is the starting place for an administrator to configure the way information is presented in Sugar. Administrators can use Sugar Studio to create and add custom fields, hide fields that are not relevant, and use the extensive customization capabilities of Sugar Logic to create calculated, dependent, and related fields. Sugar Studio is a very simple but powerful WYSIWYG interface that administrators and developers use to configure Sugar to complement a company’s existing business processes.

Positive Impacts

  •  Rearrange the order of fields according to your company’s requirements
  •  Hide fields that are not relevant to your business process
  •  Create and add new custom fields
  •  Calculate variable values based on the value of other fields like opportunity amount or expected close date
  •  Present fields only when necessary using dependent fields


If importing has been enabled for the Skill and the Language module when these modules were created then we can import Skill or Languages as standalone data. The Contact module is a built-in SugarCrm module and it has importing enabled but can also only import contact standalone data. 




There are a few challenges importing related data into SugarCrm:
  1. We need to handle relationships and splitting data into the correct modules.
  2. We need to handle the fact that the id column might not contain the actual id of the data but that another column might do instead.

Without the 2 challenges solved we will not be able to import the below data the way I would prefer.


What we really want in the database after importing the above data is.
  • 2 contacts: John and Jane
  • 3 skills: English literature, European history and PHP
  • 4 languages: English, German, Danish and Swedish 
  • as well as the many-many relations needed so we can recreate the data pictured above

The 2 above mentioned challenges have been solved the following ways:

#1: In the importer step 3 we have added all the "main module's" (Contacts in this case) related modules' fields in the module field dropdown. This way you can map any data  you want into a related module's field.

#2: A primary key field selector has been added on Step 2 in the import if you have selected "Create new records and update existing records" on Step 1 in the importer.




Lets look at the proposed solution.

Step 1: the importer has not been modified visually and looks as below.

(Please note the support for saving an import configuration is working with the new importer functionality).



Step 2: We have added a new field that allows you to select the field that is to be used as "id". It defaults to the standard importer behavior which is using the id column.



Step 3: We have added a marker for the field used as primary id ( purple box below ). Please note the text in the dropdown marked with red boxes. The field selected in the dropdown is prefixex with a module name (<modulename>.<fieldname>) this indicates that it is a field from a related module you want to map to.

As you can see from the screenshot below, the module field dropdown now contains all fields from the main module (Contacts) (main module fields are not prefixed with the module name), as well as the related modules fields (skill_Skill, and lang_Language, these fields are prefixed with the module name).



Note: Do not map any value to a related module's "ID" field. This is handled behind the scenes.


Step 4: No visual changes has happened here



There are quite a few code changes to add this new functionality into the importer and you can see the Subversion patch file below.
Index: include/database/DBHelper.php
===================================================================
--- include/database/DBHelper.php (revision 73)
+++ include/database/DBHelper.php (working copy)
@@ -1185,8 +1185,8 @@
                          $before_value=(float)$bean->fetched_row[$field];
                          $after_value=(float)$bean->$field;
                      } else {
-                         $before_value=$bean->fetched_row[$field];
-                         $after_value=$bean->$field;
+                         $before_value=$bean->fetched_row[$field];   
+                         $after_value=@$bean->$field;
                    }
 
      //Because of bug #25078(sqlserver haven't 'date' type, trim extra "00:00:00" when insert into *_cstm table). so when we read the audit datetime field from sqlserver, we have to replace the extra "00:00:00" again.
Index: modules/Import/Importer.php
===================================================================
--- modules/Import/Importer.php (revision 73)
+++ modules/Import/Importer.php (working copy)
@@ -59,6 +59,7 @@
      */
     private $importColumns;
 
+ private $importRelatedColumns;
     /**
      * @var importSource
      */
@@ -104,9 +105,11 @@
         //Get the default user currency
         $this->defaultUserCurrency = new Currency();
         $this->defaultUserCurrency->retrieve('-99');
-
+        
         //Get our import column definitions
         $this->importColumns = $this->getImportColumns();
+  $this->importRelatedColumns = $this->getRelatedImportColumns();
+
         $this->isUpdateOnly = ( isset($_REQUEST['import_type']) && $_REQUEST['import_type'] == 'update' );
     }
 
@@ -132,7 +135,7 @@
     protected function importRow($row)
     {
         global $sugar_config, $mod_strings;
-
+  global $beanList,$beanFiles;
         $focus = clone $this->bean;
         $focus->unPopulateDefaultValues();
         $focus->save_from_post = false;
@@ -140,6 +143,11 @@
         $this->ifs->createdBeans = array();
         $this->importSource->resetRowErrorCounter();
         $do_save = true;
+        
+        $primary_key = (isset($_REQUEST['primary_key']) && !empty($_REQUEST['primary_key'])) ? $_REQUEST['primary_key'] : false;
+        if(!$primary_key){
+            $primary_key = 'id';
+        }
 
         for ( $fieldNum = 0; $fieldNum < $_REQUEST['columncount']; $fieldNum++ )
         {
@@ -322,46 +330,58 @@
                 return;
             }
         }
-
+        
+        $focus_array = array();
+        $newRecord_array = array();
+        
         // if the id was specified
         $newRecord = true;
-        if ( !empty($focus->id) )
+        if ( !empty($focus->$primary_key) )
         {
-            $focus->id = $this->_convertId($focus->id);
-
+            if($primary_key=='id')
+                $focus->id = $this->_convertId($focus->$primary_key);
             // check if it already exists
-            $query = "SELECT * FROM {$focus->table_name} WHERE id='".$focus->db->quote($focus->id)."'";
+            $join = '';
+            $_def = $focus->getFieldDefinition($primary_key);
+            if($_def['source'] == 'custom_fields') $join = "JOIN {$focus->table_name}_cstm ON id_c = id";
+            $query = "SELECT * FROM {$focus->table_name} $join WHERE $primary_key='".$focus->db->quote($focus->$primary_key)."'";
             $result = $focus->db->query($query)
             or sugar_die("Error selecting sugarbean: ");
 
-            $dbrow = $focus->db->fetchByAssoc($result);
+            while($dbrow = $focus->db->fetchByAssoc($result)){
 
-            if (isset ($dbrow['id']) && $dbrow['id'] != -1)
+                if (isset ($dbrow[$primary_key]) && $dbrow[$primary_key] != -1)
             {
-                // if it exists but was deleted, just remove it
-                if (isset ($dbrow['deleted']) && $dbrow['deleted'] == 1 && $this->isUpdateOnly ==false)
+                    $focus->id = $dbrow['id'];
+                    // if it exists but was deleted, just remove it                                         //skiping deleted rows
+                    if (isset ($dbrow['deleted']) && $dbrow['deleted'] == 1 && $this->isUpdateOnly == false /*&& $primary_key=='id'*/)
                 {
                     $this->removeDeletedBean($focus);
                     $focus->new_with_id = true;
                 }
                 else
                 {
+                        //skiping deleted rows
+                        if(isset ($dbrow['deleted']) && $dbrow['deleted'] == 1 ) continue;
                     if( ! $this->isUpdateOnly )
                     {
-                        $this->importSource->writeError($mod_strings['LBL_ID_EXISTS_ALREADY'],'ID',$focus->id);
+                            $this->importSource->writeError($mod_strings['LBL_ID_EXISTS_ALREADY'],$primary_key,$focus->$primary_key);
                         $this->_undoCreatedBeans($this->ifs->createdBeans);
                         return;
                     }
-
+                        
                     $clonedBean = $this->cloneExistingBean($focus);
                     if($clonedBean === FALSE)
                     {
-                        $this->importSource->writeError($mod_strings['LBL_RECORD_CANNOT_BE_UPDATED'],'ID',$focus->id);
+                            $this->importSource->writeError($mod_strings['LBL_RECORD_CANNOT_BE_UPDATED'],$primary_key,$focus->$primary_key);
                         $this->_undoCreatedBeans($this->ifs->createdBeans);
                         return;
                     }
                     else
                     {
+                            $focus_array[] = clone $clonedBean;
+                            $newRecord_array[] = FALSE;
+                            
                         $focus = $clonedBean;
                         $newRecord = FALSE;
                     }
@@ -369,19 +389,100 @@
             }
             else
             {
+                    if($focus->id){
                 $focus->new_with_id = true;
+                    }else{
+                        $focus->new_with_id = false;
+                        $focus_array[] = $this->cloneExistingBean($focus);
+                        $newRecord_array[] = true;
             }
         }
-
+            }
+        }
+        if(count($focus_array)==0){
+            $focus_array[] = $focus;
+            $newRecord_array[] = $newRecord;
+        }
+        for($focus_i=0,$focus_count=count($focus_array);$focus_i<$focus_count;$focus_i++){
+            $focus = $focus_array[$focus_i];
+            $newRecord = $newRecord_array[$focus_i];
         if ($do_save)
         {
             $this->saveImportBean($focus, $newRecord);
+       $mainModuleId = $focus->id;
+       //Save records for related module
+       foreach($this->importRelatedColumns as $k=>$v)
+       {
+         $exploedFields = explode("$",$v);
+         $relatedModule[] =  $exploedFields[0];
+         $relatedModuleField[$exploedFields[0]][] = $exploedFields[1];
+       }
+                if(isset($relatedModule) && is_array($relatedModule))
+       $relatedModules = array_unique($relatedModule);
+                if(isset($relatedModule) && is_array($relatedModule))
+                    foreach($relatedModuleField as $modName => $fldsArr)
+        {
+         $haveValue = 0;
+         $relatedModuleFile = $beanList[$modName];
+         include_once($beanFiles[$relatedModuleFile]);
+         $relatedModuleObj = new $relatedModuleFile();
+         $whereArr = array();
+                        $is_custom = false;
+         foreach($fldsArr as $k=>$fldName)
+         {
+          $fld = $modName."$".$fldName;
+          $fieldNo = array_search($fld,$this->importRelatedColumns);
+          if($row[$fieldNo]!="")
+          {
+           $haveValue++;
+           $relatedModuleObj->$fldName = $row[$fieldNo];
+           $whereArr[] = $fldName ."='". $row[$fieldNo]."'";
+                                if(@$relatedModuleObj->field_defs[$fldName]['source'] == 'custom_fields')  $is_custom = true;
+          }
+         }
+         if($haveValue)
+         {
+          $where = "";
+          $where = implode(" AND ",$whereArr);
+          $sql = "select id from ".$relatedModuleObj->table_name;
+          if($is_custom) $sql .= ' inner join '.$relatedModuleObj->table_name.'_cstm on id=id_c';
+                            $sql .= " where ".$where." and deleted=0";
+                            $result2 = $relatedModuleObj->db->query($sql);
+          $rowQuery = $relatedModuleObj->db->fetchByAssoc($result2);
+          if(count($rowQuery)==0 || empty($rowQuery))
+          {
+           $this->saveImportBean($relatedModuleObj, $newRecord); 
+           $relatedId = $relatedModuleObj->id;
+          }
+          else
+          {
+           $relatedId = $rowQuery['id'];
+          }
+          $rel_table = strtolower($relatedModuleObj->table_name);
+                            $foc_table = strtolower($focus->table_name);
+                            if(strlen($rel_table)>16) $rel_table = substr($rel_table,0,11).substr($rel_table,-5);
+                            if(strlen($foc_table)>16) $foc_table = substr($foc_table,0,11).substr($foc_table,-5);
+                            $rel_field = $foc_table.'_'.$rel_table;
+                            if(!$focus->load_relationship($rel_field)){       
+                                $rel_field = $rel_table.'_'.$foc_table;
+                                if(!$focus->load_relationship($rel_field)){
+                                    $GLOBALS['log']->error("SugarBean.load_relationships, relationship not found.");
+                                    $rel_field = false;
+                                }
+                            }
+                            if($rel_field){
+                                $focus->$rel_field->add($relatedId,array());
+                            }
+                             $focus->save();
+         }
+        }
+                   
             // Update the created/updated counter
             $this->importSource->markRowAsImported($newRecord);
         }
         else
             $this->_undoCreatedBeans($this->ifs->createdBeans);
-
+        }
         unset($defaultRowValue);
 
     }
@@ -544,11 +645,15 @@
         
         $firstrow    = unserialize(base64_decode($_REQUEST['firstrow']));
         $mappingValsArr = $this->importColumns;
-        $mapping_file = new ImportMap();
+        $mappingRelatedArr = $this->importRelatedColumns;
+        $mappArr = $mappingRelatedArr+$mappingValsArr;
+        $mapping_file = new ImportMap();     
+        
+        
         if ( isset($_REQUEST['has_header']) && $_REQUEST['has_header'] == 'on')
         {
             $header_to_field = array ();
-            foreach ($this->importColumns as $pos => $field_name)
+            foreach ($mappArr as $pos => $field_name)
             {
                 if (isset($firstrow[$pos]) && isset($field_name))
                 {
@@ -557,6 +662,8 @@
             }
 
             $mappingValsArr = $header_to_field;
+        }      else{
+         $mappingValsArr =    $mappArr;
         }
         //get array of values to save for duplicate and locale settings
         $advMapping = $this->retrieveAdvancedMapping();
@@ -679,7 +786,26 @@
 
         return $importColumns;
     }
+ protected function getRelatedImportColumns()
+    {
+        $importRelatedColumns = array();
+        foreach ($_REQUEST as $name => $value)
+        {
+            // only look for var names that start with "fieldNum"
+            if (strncasecmp($name, "colnum_", 7) != 0)
+                continue;
 
+            // pull out the column position for this field name
+            $pos = substr($name, 7);
+
+            if ( strpos($value,"$") )
+            {
+                // now mark that we've seen this field
+                $importRelatedColumns[$pos] = $value;
+            }
+        }
+        return $importRelatedColumns;
+    }
     protected function getFieldSanitizer()
     {
         $ifs = new ImportFieldSanitize();
Index: modules/Import/tpls/confirm.tpl
===================================================================
--- modules/Import/tpls/confirm.tpl (revision 73)
+++ modules/Import/tpls/confirm.tpl (working copy)
@@ -170,8 +170,30 @@
         </tr>
         <tr>
             <td colspan="2"><div class="hr" style="margin-top: 0px;"></div></td>
+        </tr>    
+        {if $TYPE != 'import'}
+        <tr>
+            <td colspan="2"><h3>Select primaty field&nbsp;{sugar_help text="It will update the row with the field equal to the value of the csv row"}</h3></td>
         </tr>
         <tr>
+            <td colspan="2">
+                <select name="primary_key">
+                    {foreach from=$SAMPLE_ROWS item=row name=row}
+                        {foreach from=$row item=value}
+                            {if $smarty.foreach.row.first}
+                                {if $HAS_HEADER}
+                                    <option value="{$value}"{if $value|lower == 'id'} selected='selected' {/if}>{$value}</option>
+                                {/if}
+                            {/if}
+                        {/foreach}
+                    {/foreach}
+                </select>
+            </td>
+        </tr>
+        {else}
+            <input name="primary_key" value="" type="hidden" />
+        {/if}                                                                                                 
+        <tr>
             <td colspan="2"><h3>{$MOD.LBL_THIRD_PARTY_CSV_SOURCES}&nbsp;{sugar_help text=$MOD.LBL_THIRD_PARTY_CSV_SOURCES_HELP}</h3></td>
         </tr>
         <tr>
Index: modules/Import/tpls/dupcheck.tpl
===================================================================
--- modules/Import/tpls/dupcheck.tpl (revision 73)
+++ modules/Import/tpls/dupcheck.tpl (working copy)
@@ -84,6 +84,7 @@
 <input type="hidden" id="enabled_dupes" name="enabled_dupes" value="">
 <input type="hidden" id="disabled_dupes" name="disabled_dupes" value="">
 <input type="hidden" id="current_step" name="current_step" value="{$CURRENT_STEP}">
+<input type="hidden" name="primary_key" value="{$primary_field}">
 
    <div class="hr"></div>
     <div>
Index: modules/Import/tpls/step3.tpl
===================================================================
--- modules/Import/tpls/step3.tpl (revision 73)
+++ modules/Import/tpls/step3.tpl (working copy)
@@ -118,10 +118,34 @@
 {/if}
 <tr>
     {if $HAS_HEADER == 'on'}
-    <td id="row_{$smarty.foreach.rows.index}_header">{$item.cell1}</td>
+    <td id="row_{$smarty.foreach.rows.index}_header">
+        {if $primary_field==$item.cell1}
+            <b>{$item.cell1}
+            <small>*Primary Field</small>
+            </b>
+            <input type="hidden" name="primary_key" value="{$primary_field}" id="primary_key" />
+            <script>
+                {literal}
+                (function(obj, evType, fn){ 
+                    if (obj.addEventListener){ 
+                       obj.addEventListener(evType, fn, false); 
+                       return true; 
+                     } else if (obj.attachEvent){ 
+                        var r = obj.attachEvent("on"+evType, fn); 
+                        return r; 
+                     } else { 
+                        return false; 
+                     } 
+                })(window,'load',function(){document.getElementById('primary_key').value=document.getElementById('primary_key_list').value})
+                {/literal}
+            </script>
+        {else}
+            {$item.cell1}
     {/if}
+    </td>
+    {/if}
     <td valign="top" align="left" id="row_{$smarty.foreach.rows.index}_col_0">
-        <select class='fixedwidth' name="colnum_{$smarty.foreach.rows.index}">
+        <select class='fixedwidth' name="colnum_{$smarty.foreach.rows.index}"{if $primary_field==$item.cell1} id="primary_key_list" onblur="document.getElementById('primary_key').value = this.value" {/if}>
             <option value="-1">{$MOD.LBL_DONT_MAP}</option>
             {$item.field_choices}
         </select>
Index: modules/Import/views/view.confirm.php
===================================================================
--- modules/Import/views/view.confirm.php (revision 73)
+++ modules/Import/views/view.confirm.php (working copy)
@@ -63,7 +63,7 @@
         global $sugar_config, $locale;
         
         $this->ss->assign("IMPORT_MODULE", $_REQUEST['import_module']);
-        $this->ss->assign("TYPE",( !empty($_REQUEST['type']) ? $_REQUEST['type'] : "import" ));
+        $this->ss->assign("TYPE",( !empty($_REQUEST['type']) ? $_REQUEST['type'] : ( !empty($_REQUEST['import_type'])?$_REQUEST['import_type'] : "import") ));
         $this->ss->assign("SOURCE_ID", $_REQUEST['source_id']);
 
         $this->instruction = 'LBL_SELECT_PROPERTY_INSTRUCTION';
Index: modules/Import/views/view.dupcheck.php
===================================================================
--- modules/Import/views/view.dupcheck.php (revision 73)
+++ modules/Import/views/view.dupcheck.php (working copy)
@@ -73,6 +73,7 @@
         $this->ss->assign("IMPORT_MODULE", $_REQUEST['import_module']);
         $this->ss->assign("CURRENT_STEP", $this->currentStep);
         $this->ss->assign("JAVASCRIPT", $this->_getJS());
+        $this->ss->assign("primary_field", @$_REQUEST['primary_key'] ? $_REQUEST['primary_key'] : 'id' );
 
         $content = $this->ss->fetch('modules/Import/tpls/dupcheck.tpl');
         $this->ss->assign("CONTENT", $content);
Index: modules/Import/views/view.last.php
===================================================================
--- modules/Import/views/view.last.php (revision 73)
+++ modules/Import/views/view.last.php (working copy)
@@ -109,6 +109,8 @@
         $this->ss->assign("errorrecordsFile",ImportCacheFiles::getErrorRecordsWithoutErrorFileName());
         $this->ss->assign("dupeFile",ImportCacheFiles::getDuplicateFileName());
         
+        $this->ss->assign("primary_field", @$_REQUEST['primary_key'] ? $_REQUEST['primary_key'] : 'id' );
+        
         if ( $this->bean->object_name == "Prospect" )
         {
          $this->ss->assign("PROSPECTLISTBUTTON", $this->_addToProspectListButton());
Index: modules/Import/views/view.step3.php
===================================================================
--- modules/Import/views/view.step3.php (revision 73)
+++ modules/Import/views/view.step3.php (working copy)
@@ -63,7 +63,7 @@
   public function display()
     {
         global $mod_strings, $app_strings, $current_user, $sugar_config, $app_list_strings, $locale;
-        
+        global $beanList,$beanFiles;
         $this->ss->assign("IMPORT_MODULE", $_REQUEST['import_module']);
         $has_header = ( isset( $_REQUEST['has_header']) ? 1 : 0 );
         $sugar_config['import_max_records_per_file'] = ( empty($sugar_config['import_max_records_per_file']) ? 1000 : $sugar_config['import_max_records_per_file'] );
@@ -200,7 +200,91 @@
         if (!empty($importColumns)) {
             $column_sel_from_req = true;
         }
-
+        foreach($this->bean->field_defs as $field)
+        {
+            if(0 == strcmp($field['type'],'link') && (!empty($field['module']) || !empty($field['relationship'])))
+            {
+                if(!empty($field['module']))
+                {
+                    $related_module = $field['module'];
+                }
+                elseif(!empty($field['relationship']))
+                {
+                    require_once("data/Relationships/RelationshipFactory.php");
+                    $test = SugarRelationshipFactory::getInstance()->getRelationship($field['relationship']);
+                    if($test->def['relationships'][$field['relationship']]['lhs_module'] == $this->bean->module_dir)
+                    {
+                        $relatedMods[] = $test->def['relationships'][$field['relationship']]['rhs_module'];
+                    }
+                    else
+                    {
+                        $relatedMods[] = $test->def['relationships'][$field['relationship']]['lhs_module'];
+                    }
+                }
+            }
+        }
+        //add extra related fields
+        foreach($relatedMods as $key=>$relatedModule)
+        {
+                if(array_key_exists($relatedModule,$beanList))
+                {
+                $relatedModuleFile = $beanList[$relatedModule];
+                include_once($beanFiles[$relatedModuleFile]);
+                $relatedModuleObj = new $relatedModuleFile();
+                $fieldsRel  = $relatedModuleObj->get_importable_fields();
+                $relModuleStrings = return_module_language($current_language, $relatedModuleObj->module_dir);
+                foreach ( $fieldsRel as $fieldname => $properties ) {
+                    if($properties['relationship'])
+                    continue;
+                    // get field name
+                    if (!empty($relModuleStrings['LBL_EXPORT_'.strtoupper($fieldname)]) )
+                    {
+                         $displayname = str_replace(":","", $relModuleStrings['LBL_EXPORT_'.strtoupper($fieldname)] );
+                    }
+                    else if (!empty ($properties['vname']))
+                    {
+                        $displayname = str_replace(":","",translate($properties['vname'] ,$relatedModuleObj->module_dir));
+                    }
+                    else
+                        $displayname = str_replace(":","",translate($properties['name'] ,$relatedModuleObj->module_dir));
+                    
+                    $selected = '';   
+                    if ($column_sel_from_req && isset($importColumns[$field_count])) {
+                        if ($fieldname == $importColumns[$field_count]) {
+                            //$selected = ' selected="selected" ';
+                            $defaultField = $fieldname;
+                            $mappedFields[] = $fieldname;
+                        }
+                    } else {
+                        if ( !empty($defaultValue) && !in_array($fieldname,$mappedFields)
+                                                        && !in_array($fieldname,$ignored_fields) )
+                        {
+                            if ( strtolower($fieldname) == strtolower($defaultValue)
+                                || strtolower($fieldname) == str_replace(" ","_",strtolower($defaultValue))
+                                || strtolower($displayname) == strtolower($defaultValue)
+                                || strtolower($displayname) == str_replace(" ","_",strtolower($defaultValue)) )
+                            {
+                                //$selected = ' selected="selected" ';
+                                $defaultField = $fieldname;
+                                $mappedFields[] = $fieldname;
+                            }
+                        }
+                    }  
+                    // get field type information
+                    $fieldtype = '';
+                    if ( isset($properties['type'])
+                            && isset($mod_strings['LBL_IMPORT_FIELDDEF_' . strtoupper($properties['type'])]) )
+                        $fieldtype = ' [' . $mod_strings['LBL_IMPORT_FIELDDEF_' . strtoupper($properties['type'])] . '] ';
+                    if ( isset($properties['comment']) )
+                        $fieldtype .= ' - ' . $properties['comment'];
+                        
+                    $optionsRelated[$relatedModuleObj->module_dir.'$'.$fieldname] = '<option value="'.$relatedModuleObj->module_dir.'$'.$fieldname.'" title="'. $displayname . htmlentities($fieldtype) . '"'
+                        . $selected . $req_class . '>'. $relatedModuleObj->module_dir.".". $displayname . '</option>\n';
+                }
+                
+                }
+        }
+        
         for($field_count = 0; $field_count < $ret_field_count; $field_count++) {
             // See if we have any field map matches
             $defaultValue = "";
@@ -225,7 +309,17 @@
             $defaultField = '';
             global $current_language;
       $moduleStrings = return_module_language($current_language, $this->bean->module_dir);
+            
+            $related_options_clone = $optionsRelated;
 
+            if(
+                isset($firstrow_name) &&
+                isset($field_map[$firstrow_name]) &&
+                isset($related_options_clone[$field_map[$firstrow_name]])
+            ){
+                $related_options_clone[$field_map[$firstrow_name]] = str_replace('<option ','<option selected="selected" ',$related_options_clone[$field_map[$firstrow_name]]);
+            }
+            
             foreach ( $fields as $fieldname => $properties ) {
                 // get field name
                 if (!empty($moduleStrings['LBL_EXPORT_'.strtoupper($fieldname)]) )
@@ -278,7 +372,6 @@
                 $options[$displayname.$fieldname] = '<option value="'.$fieldname.'" title="'. $displayname . htmlentities($fieldtype) . '"'
                     . $selected . $req_class . '>' . $displayname . $req_mark . '</option>\n';
             }
-
             // get default field value
             $defaultFieldHTML = '';
             if ( !empty($defaultField) ) {
@@ -306,6 +399,11 @@
             $cellOneData = isset($rows[0][$field_count]) ? $rows[0][$field_count] : '';
             $cellTwoData = isset($rows[1][$field_count]) ? $rows[1][$field_count] : '';
             $cellThreeData = isset($rows[2][$field_count]) ? $rows[2][$field_count] : '';
+            
+            if(count($optionsRelated)>0 && @$_REQUEST['primary_key'] != $firstrow_name)
+            {
+                $options = array_merge($options, $related_options_clone);
+            }
             $columns[] = array(
                 'field_choices' => implode('',$options),
                 'default_field' => $defaultFieldHTML,
@@ -382,6 +480,7 @@
 
         $this->ss->assign("COLUMNCOUNT",$ret_field_count);
         $this->ss->assign("rows",$columns);
+        $this->ss->assign("primary_field", @$_REQUEST['primary_key'] ? $_REQUEST['primary_key'] : 'id' );
 
         $this->ss->assign('datetimeformat', $GLOBALS['timedate']->get_cal_date_time_format());
 
@@ -768,4 +867,4 @@
 
 EOJAVASCRIPT;
     }
-}
+}
\ No newline at end of file

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;
  }
 }
}