Amazon SimpleDB

Procedure

1. Download this sample RSL file from the Resolver Exchange.

2. Save the file on your system.

3. Copy the .NET library to the same location.

4. Open the sample RSL file in Resolver One.

5. In the Settings worksheet, enter your AWS access key and secret keys. (If you want to access a domain other than the default "MyStore", you can change that here too.)

6. Let recalculation complete, then switch to the "Data" worksheet.

   You should see all of the data from your domain. The Header Rows and Columns are set up so you can use them to query the data from your pre- or post-formula user code, or from your formulae.

About the code

Here's a step-by-step explanation of the pre-formulae user code that does the work.

1. Load the .NET library we created earlier...

   import clr
   clr.AddReference("Amazon.SimpleDB")
   
   from Amazon.SimpleDB import AmazonSimpleDBClient
   from Amazon.SimpleDB.Model import GetAttributes, Query
   

2. Connect to the web service using the keys that were entered in the "Settings" worksheet (using references to named ranges)...

   accessKeyId = accessKey.Value
   secretAccessKey = secretKey.Value
   service = AmazonSimpleDBClient(accessKeyId, secretAccessKey)
   

3. Set up the "Data" worksheet, giving it an appropriate header row and column...

   sheet = workbook["Data"]
   itemHeader = "<Item>"
   rowNum = 1
   colNum = 1
   sheet.HeaderRow = sheet.Rows[rowNum]
   sheet.HeaderCol = sheet.Rows[colNum]
   sheet.HeaderRow[colNum] = itemHeader
   sheet.HeaderRow.Bold = True
   

4. Make a query to Amazon SimpleDB, asking for all items in the specified domain...

   queryAction = Query().WithDomainName(domain.Value)
   response = service.Query(queryAction)
   

5. If the request returns any items, iterate over them...

   if response.IsSetQueryResult():
       for itemName in response.QueryResult.ItemName:
   

6. Add each item to a new row, putting its name into the special "<Item>" column, then getting all of its attributes...

   rowNum += 1
   row = sheet.Rows[rowNum]
   row[itemHeader] = itemName
   domain = GetAttributes().WithDomainName("MyStore")
   getAttributesAction = domain.WithItemName(itemName)
   response = service.GetAttributes(getAttributesAction)
   

7. If the item has any attributes, iterate over the attribute/value pairs that have been returned...

   if response.IsSetGetAttributesResult():
       for attribute in response.GetAttributesResult.Attribute:
   

The next step is to put the attributes into appropriate cells in the item's row. This is a little more complicated than the code so far.

Items in Amazon SimpleDB domains can specify a number of possible values for an attribute. For example, in the "Getting started" tutorial MyStore domain, the sweater's size can be "Large", "Medium" or "Small". The solution here is to use lists. In Resolver One, a cell can hold a list of values, so if an attribute for a specific row has multiple values, we can put a list of those values into the relevant cell.

Here's how the code does this...

8. If the current item already has an attribute with the name of the current attribute, changes the cell to use a list (if required) and adds the current value to the list. Otherwise puts the current value into the cell.

   try:
       if row[attribute.Name] is Empty:
           row[attribute.Name] = attribute.Value
       else:
           if type(row[attribute.Name]) != list:
               row[attribute.Name] = [row[attribute.Name]]
           row[attribute.Name].append(attribute.Value)
   except IndexError:
       colNum += 1
       sheet.HeaderRow[colNum] = attribute.Name
       row[attribute.Name] = attribute.Value
   

(The try...except that wraps around this section creates new columns for attributes as they are encountered for the first time.)