We use SugarCRM at work and I’ve complained about its not-very-RESTy REST interface. John Mertic a (the?) SugarCRM Community Manager asked me about what problems I’d had (apart from its lack of RESTfulness) and I said I’d write a blog post about it.

In our case, the REST interface is used to integrate Sugar and RT so we get a link in both interfaces to jump from opportunities to the corresponding RT ticket (and back again). This should be a fairly trivial exercise or so you would think.

The problems, as I see it are:

• Not REST-y.
• Exposes the database tables all the way through the REST interface
• Lack of useful documentation forcing the developer to cargo cult and guess
• Annoying data structures
• Forced pagination

My first gripe is the complete lack of REST in the URLs. Everything is just sent to https://sugar/service/v2/rest.php. Usually a POST, but sometimes a GET. It’s not documented what to use where.

The POST parameters we send when logging in are:

method=>"login"
input_type=>"JSON"
response_type=>"JSON"
rest_data=>json($params)

$params is a hash as follows:

user_auth => {
            user_name => $USERNAME,
            password => $PW,
            version => "1.2",
},
application => "foo",

Nothing seems to actually care about the value of application, nor about the user_auth.version value. The password is the md5 of the actual password, hex encoded. I’m not sure why it is, as this adds absolutely no security, but it is. This is also not properly documented.

This gives us a JSON object back with a somewhat haphazard selection of attributes (reformatted here for readability):

{
     "id":"<hex session id>,
     "module_name":"Users",
     "name_value_list": {
             "user_id": {
                     "name":"user_id",
                     "value":"1"
             },
             "user_name": {
                     "name":"user_name",
                     "value":"<username>"
             },
             "user_language": {
                     "name":"user_language",
                     "value":"en_us"
             },
             "user_currency_id": {
                     "name":"user_currency_id",
                 "value":"-99"
             },
             "user_currency_name": {
                     "name":"user_currency_name",
                     "value":"Euro"
             }
     }
}

What is the module_name? No real idea. In general, when you get back an id and a module_name field, it tells you that the id exists is an object that exists in the context of the given module. Not here, since the session id is not a user.

The worst here is the name_value_list concept which is used all over the REST interface. First, it’s not a list, it’s a hash. Secondly, I have no idea what would be wrong by just using keys directly in the top level object, so the object would have looked somewhat like:

{
     "id":"<hex session id>,
     "user_id": 1,
     "user_name": "<username>,
     "user_language":"en_us",
     "user_currency_id": "-99",
     "user_currency_name": "Euro"
}

Some people might argue that since you can have custom field names this can cause clashes. Except, it can’t, since they’re all suffixed with _c.

So we’re now logged in and can fetch all opportunities. This we do by posting:

method=>"get_entry_list",
input_type=>"JSON",
response_type=>"JSON",
rest_data=>to_json([
            $sid,
            $module,
            $where,
            "",
            $next,
            $fields,
            $links,
            1000
])

• $sid is our session id from the login
• $module is “Opportunities”
• $where is opportunities_cstm.rt_id_c IS NOT NULL. Yes, that’s right. An SQL fragment right there and you have to know that you’ll join the opportunities_cstm and opportunities tables because we are using a custom field. I find this completely crazy.
• $next starts out at 0 and we’re limited to 1000 entries at a time. There is, apparently, no way to say “just give me all you have”.
• $fields is an array, in our case consisting of id, name, description, rt_id_c and rt_status_c. To find out the field names, look at the database schema or poke around in the SugarCRM studio.
• $links is to link records together. I still haven’t been able to make this work properly and just do multiple queries.
• 1000 is the maximum number of records. No, you can’t say -1 and get everything.

Why this is a list rather than a hash? Again, I don’t know. A hash would make more sense to me.

The resulting JSON looks like:

{
    "result_count" : 16,
    "relationship_list" : [],
    "entry_list" : [
       {
          "name_value_list" : {
             "rt_status_c" : {
                "value" : "resolved",
                "name" : "rt_status_c"
             },
             […]
          },
          "module_name" : "Opportunities",
          "id" : "<entry_uuid>"
       },
       […]
    ],
    "next_offset" : 16
}

Now, entry_list actually is a list here, which is good and all, but there’s still the annoying name_value_list concept.

Last, we want to update the record in Sugar, to do this we do:

method=>"set_entry",
input_type=>"JSON",
response_type=>"JSON",
rest_data=>to_json([
    $sid,
    "Opportunities",
    $fields
])

$fields is not a name_value_list, but instead is:

{
    "rt_status_c" : "resolved",
    "id" : "<status text>"
}

Why this works and my attempts at using a proper name_value_list didn’t work? I have no idea.

I think that pretty much sums it up. I’m sure there are other problems in there (such as the over 100 lines of support code for the about 20 lines of actual code that does useful work), though.

Back to posts