Creating a database based on API 37signals

One of our services builds daily reports by analyzing data from Highrise .

Data from Highrise can be obtained in several ways:

• Use CSV export
• Use API

CSV was not even considered by us and we tried to find a convenient method for daily updating our database through the API.

Come up with the following:

• Get the XML feed and parse it.
• We consider all the immediate child fields as fields in the table.
• We consider all nested elements as related tables.

Sample XML response (https://xxx.highrisehq.com/deals.xml):







The arrows indicate the elements that we consider nested and, accordingly, bring the data into related tables.

')

As we did from XML MySQL tables.

• If there is an id field in the data, we consider it to be the primary integer key.
• For all other XML fields, we consider the type attribute (excluding the array type) as the type
• For nested elements, we check whether they have an id attribute and if they have (we have a linked table), then add the <field of nested table> -id field to our table and create a nested table
• All fields in the table are divided into 3 types: integer, datetime and varchar / text. We have TEXT only if the field name has one of the specific values: 'value', 'description', 'text', 'body', 'message', 'data', 'blob', 'background'

An example of what we get at the output:







Now you can do anything with this data, draw graphs, count amounts, pull out other interesting information.



Most importantly, the resulting code works with all 37signals services and can synchronize any of their services.

Basecamp synchronization example

<?php require 'SyncService.class.php'; $sync = new SyncService( array( 'db' => array( 'host' => 'localhost', 'user' => 'root', 'password' => '123', 'schema' => 'basecamp' ), 'service' => array( 'name' => 'basecamp', 'url' => 'https://xxx.basecamphq.com', 'token' => 'abcdef1234567890abcdef1234567890', 'streams' => array( '/todo_lists.xml' => 500, '/people.xml' => 500, '/projects.xml' => 500, '/account.xml' => 0, ) ), ) ); $sync->doSyncing(); 

All library code along with an example on a githab.