JSON API in progress

As requested Engage are setting up a JSON API now! This API will use a query language called EQL (Engage Query Language), similar to the syntax of MySQL. Before its out we'd like to get feedback on how you think our specification is coming along!

Our query language at the moment is parsed by regex, and therefore it is important that it is structured right. To help with other websites intergrating we have decided to release the regex for you to view, with a few examples to get started. As this has not been fully tested and is a work in progress the regex expression could change.

PULL [\w]+ (WHERE (`\w+?`(=|<|>|<=|>=)'\w+?'.?[^`\w]?)+)?(FIELDS (`\w+?`.?)+)?

UPDATE 7/7/09: PULL (?[\w]+)\s?(WHERE (?(`\w+`\s?(\=|\<|\>|\<\=|\>\=|LIKE|!=)\s?'.+?'(,\s|,))+)\s?)?(?FIELDS (`\w+?`,?\s?)+)?

This syntax should allow the programmer to search with multiple criteria as well as selecting the particular fields they wish to view. Either of these can be ignored to the extent that we can pull with whichever part of the syntax we wish to use. Some examples of tested expressions include:

PULL EVENTS WHERE `field`='this', `that`<'them' FIELDS `field`,`that`,`something`;

PULL EVENTS WHERE `this`='that';

PULL EVENTS FIELDS `field`,`that`,`something`;

PULL ACTIVITIES WHERE `a`='b',`c`<='d' FIELDS `e`,`f`,`g`;

When executing this query Engage then sends back a JSON object. Although XML is nice, I always find it irritating to parce and as a fan of Open Source Software, and I do all my programming in PHP I know that php has the lovely function json_decode() perfect for dealing with JSON objects. Before some picky techie geek comes to me and says "But JSON stands for JAVASCRIPT object notation", I do know, and I really don't care!!!!! If you wanted you could use it with Javascript and AJAX - and maybe at some stage I'll create a library for that too.

Anyway, the object. It's created to allow you to view some of the work that Engage is doing. When EQL comes out we want to do lots of testing to ensure that the queries are being processed as quickly as possible, and therefore we'll be adding an 'info' section to our JSON object, however even after testing this probably will not be removed. Plans for the final JSON object will be something looking a bit like:

JSON = {
   'info': {
      'query': "PULL EVENTS WHERE `field`='this', `that`<'them' FIELDS `field`,`that`,`something`",
      'timestamp': Now(),
      'execTime': Now()-Now(),
      'clientId': "MyClient",
      'results': 4
   },
   'data': {
      0: {
         'field':"this",
         'that':"them",
         'something':"1"
      }
      1: {
         'field':"this",
         'that':"them",
         'something':"5"
      }
      2: {
         'field':"this",
         'that':"them",
         'something':"99999"
      }
      3: {
         'field':"this",
         'that':"them",
         'something':"111"
      }
   }
   'error': {
      'status': "0",
      'message': "Success"
   }
}

All fields should be able to be searched:

• id (int)
• name (string)
• FacilityId (int)
• club (bool)
• StartAge (int)
• EndAge (int)
• leaderId (int)
• keywords (string)
• desc (string)

The next fields can be used for selecting data using the WHEN but NOT selecting output fields with the FIELDS clause

However the next fields can be selected for output using "FIELDS `courseTimes`"

• startTime (int - unix timestamp)
• endTime (int - unix timestamp)
• startDate (int - unix timestamp)
• endDate (int - unix timestamp)
• repetition (int - defined variable set)
• repetitionInfo (string - used as array)

These can be selected for output using "FIELDS `Facility`"

• district (string)
• ContactTel (string)
• ContactWeb (string)
• ContactEmail (string)
• pcode (string)
• address1 (string)
• address2 (string)
• address3 (string)
• address4 (string)
• address5 (string)
• address6 (string)