Views
CFQUERY-AMAZON
The standard CFQUERY tag has been extended to support access to Amazon's SimpleDB cloud storage. By using the dbType="" attribute, the power and ease of CFQUERY can be utilised to select, delete and insert data into the SimpleDB storage.
The real power of utilising this method, is the ability to cache your queries in the standard CFML way, thus resulting in real monitory savings.
Alternatively, you can use the CFML Functions to get access to your SimpleDB storage.
please note, this implementation supports Amazon's 2009-04-15 API onwards. Old QueryWithAttributes support has been deprecated as per Amazon's request.
Contents |
About Amazon SimpleDB
The Amazon SimpleDB service is not exactly a traditional RDMS, but with careful consideration can be thought more like a spreadsheet than a traditional database. Some limitations exist with the service:
- Database like storage
- Think less SQL, more Spreadsheet
- Table/Sheet = Amazon Domain
- Limited to 100 domains @ 10GB each
- Row = Amazon Item
- Item is a unqiue identifier
- Key / Data attributes
- Limited to 256 attributes per Item
- Limited to 250 million attributes per domain
- All data stored as strings
- >, < Comparisons are lexically in nature
- All operations via HTTP / SOAP
- Complex Query Language
- Simple get/paging ( = != )
- Range based querying (> < >= <=)
- String based
- starts-with, does-not-start-with
- Grouping
- Intersection, union, not
More details here: http://aws.amazon.com/simpledb
The extensions for CFQUERY has meant that we have made some design decisions to map SimpleDB onto a SQL like interface.
Setting up an Amazon datasource
Before you can use CFQUERY with Amazon, you have to setup a datasource to the Amazon SimpleDB account. You can do this using the AmazonSimpleDB() function, passing in the necessary AWS access identifiers. Once this is setup you are free to use this symbolic name in the datasource="" attribute.
Telling CFQUERY to use Amazon
CFQUERY tag has always had the ability to be extended through the use of the dbtype="" attribute. In much the same way you switch it for QueryOfQueries support, you use this attribute for Amazon SimpleDB support.
Simply pass in: dbtype="amazon"
The other attribute added to CFQUERY to support paged queries with Amazon, is the token="#qry.token#". See the SELECT section for details on how to use this
INSERT
To insert attributes into a domain for a given attribute you can easily construct a standard SQL insert statement, including the usage of CFQUERYPARAM build up your query string.
There are only a couple of caveats to be aware of:
- the 'TABLE' in the INSERT statement is the SimpleDB domain; case specific
- there must be a column called 'ItemName' that is used to identify the 'row' to which this operation will operate
- to create the domain you must first call CreateSDBDomain() if it doesn't already exist
This method allows you to set many attributes to a given item at once in one operation. This translates to a single call to Amazon's SimpleDB service.
<cfset AmazonSimpleDB("testamz", "1WHJCG48Y89P4582", "bC6kfiW715mIenvtr537DcHndYmQwVf5l")>
<cfquery dbtype="amazon" datasource="testamz">
insert into testDomain ("ItemName","name","date","sex","age")
values ( "MyItem", "Susan", <cfqueryparam value="#now()#">, "female", 21 )
</cfquery>
Dates are stored in Amazon SimpleDB as {ts:xxx} format, allowing you to perform lexical querying on them safe they will act accordingly.
SELECT
The SELECT method lets you query your SimpleDB domain using the Amazon specific query language detailed here Amazon SimpleDB Query
Use the following format for the Select function.
select output_list from domain_name [where expression] [sort_instructions] [limit limit]
Please note, that OpenBlueDragon will not pre-process this select statement prior to handing it off to Amazon SimpleDB. Therefore, the CFQUERYPARAM is not supported within SELECT statements. This ensures we are future proof against all enhancements Amazon makes to their Query syntax. Amazon are continually added new support for functions and query expressions. OpenBlueDragon automatically supports these future upgrades from Amazon.
Usage:
<cfset AmazonSimpleDB("testamz", "1WHJCG48Y89P4582", "bC6kfiW715mIenvtr537DcHndYmQwVf5l")>
<cfquery dbtype="amazon" datasource="testamz">
select * from mydomain
</cfquery>
This will return all the attributes and items in the domain. There is 3 extra parameters passed back that you can utilise to determine how your interaction with SimpleDB performed.
- .token - if there are more results available than your query returned this is the token you use to pass back in again
- .boxusage - this is a metric of how much of Amazon's resources your query consumed. The billing is based on this figure
- .requestid - this is the unique ID that was assigned to your last interaction with Amazon
For queries that have more than results than the limit (or timeout after 5seconds) you must page to the next set of results using a special TOKEN that Amazon passes back.
<cfset AmazonSimpleDB("testamz", "1WHJCG48Y89P4582", "bC6kfiW715mIenvtr537DcHndYmQwVf5l")>
<cfquery dbtype="amazon" datasource="testamz">
select * from mydomain limit 1
</cfquery>
<!--- Get the next row, passing in the previous token values --->
<cfquery dbtype="amazon" datasource="testamz" token="#testamz.token#">
select * from mydomain limit 1
</cfquery>
DELETE
You can delete a given item, or just an attribute, using the DELETE SQL statement.
There are only a couple of caveats to be aware of:
- the 'TABLE' in the DELETE statement is the SimpleDB domain; case specific
- there must be a column called 'ItemName' that is used to identify the 'row' to which this operation will operate
- You can delete a given attribute using the optional 'ItemAttribute' column
<cfquery dbtype="amazon" datasource="testamz" name="qry"> delete from testDomain where ItemName='AlanItemName' </cfquery> <cfquery dbtype="amazon" datasource="testamz" name="qry"> delete from testDomain where ItemName='AndyItemName' and ItemAttribute='name' </cfquery>
