I recently talked about using SimpleDB to save or update a record. Today, we look at how to query against records in SimpleDB while using the Amazon SimpleDB C# Library. Each record in SimpleDB has an ItemName (unique, primary key) and a set of attributes (name-value pairs). Upon insert or update, all fields are indexed for easy querying. You can access the data using the Query API or Select API. Since I am already familiar with SQL, I picked the Select API as it closely resembles the standard SQL SELECT statement.
Recall that SimpleDB stores data in domains. Any query goes against the stored domain. For example, I know that the MembershipUsers in the domain for my sample ASP.NET MembershipProvider all have a unique email address in a field named email. I also know that the email only exists on one record collection, so I shouldn’t be getting back any other record types. The actual lookup code is pretty simple:
let NormalizeUsername(username:string) =
username.Replace("’", "”")
let LookupUser username =
PhotoWebInit.InitializeAWS
let normalizedName = NormalizeUsername(username)
let simpleDb = PhotoWebInit.SimpleDBClient
let selectStmt = new Model.SelectRequest()
selectStmt.SelectExpression <-
"select * from " + PhotoWebInit.domainName +
" where email=’" + normalizedName + "’"
let result = simpleDb.Select(selectStmt)
let temp = new AwsMembershipUser()
(temp.LoadFromSelect result)
NormalizeUserName takes care of embedded tick marks (and may be open to other attacks). Recall that this is NOT SQL, so a DELETE or UPDATE or DROP won’t do much of anything other than fail.
The values come back as attributes and get parsed with the following function (lines are numbered to workaround line wrapping):
1 member this.LoadFromSelect (data: Model.SelectResponse) =
2 let hasSelectResult = data.SelectResult.Item.Count > 0
3 let hasAttributes = hasSelectResult && data.SelectResult.Item.[0].Attribute.Count > 0
4 if (hasAttributes) then
5 let attributeCollection = data.SelectResult.Item.[0].Attribute
6 let providerName = PhotoWebInit.DefaultMembershipProvider
7 let name = (this.SelectAttribute attributeCollection "email")
8 let providerUserKey = (this.SelectAttribute attributeCollection "email")
9 let email = (this.SelectAttribute attributeCollection "email")
10 let passwordQuestion = (this.SelectAttribute attributeCollection "passwordQuestion")
11 let isApproved = (PhotoWebInit.ParseBool (this.SelectAttribute attributeCollection "isApproved") false)
12 let isLockedOut = (PhotoWebInit.ParseBool (this.SelectAttribute attributeCollection "isLockedOut") true)
13 let creationDate = (PhotoWebInit.ParseDateTime (this.SelectAttribute attributeCollection "creationDate") DateTime.MaxValue)
14 let lastLoginDate = (PhotoWebInit.ParseDateTime (this.SelectAttribute attributeCollection "lastLoginDate") DateTime.MaxValue)
15 let lastActivityDate = (PhotoWebInit.ParseDateTime (this.SelectAttribute attributeCollection "lastActivityDate") DateTime.MaxValue)
16 let lastPasswordChangedDate = (PhotoWebInit.ParseDateTime (this.SelectAttribute attributeCollection "lastPasswordChangedDate") DateTime.MaxValue)
17 let lastLockoutDate = (PhotoWebInit.ParseDateTime (this.SelectAttribute attributeCollection "lastLockoutDate") DateTime.MaxValue)
18 let passwordAnswer = (this.SelectAttribute attributeCollection "passwordAnswer")
19 let password = (this.SelectAttribute attributeCollection "password")

60; 20 (new AwsMembershipUser(providerName, name, providerUserKey, email,
21 passwordQuestion, System.String.Empty, isApproved, isLockedOut, creationDate, lastLoginDate,
22 lastActivityDate, lastPasswordChangedDate, lastLockoutDate, passwordAnswer, password))
23 else
24 (new AwsMembershipUser())
Finally, the helper functions that parse a date or boolean are:
let ParseBool value (defaultValue : bool) =
let mutable retval = defaultValue
let success = bool.TryParse(value, ref retval)
retval
let ParseDateTime value (defaultValue : DateTime) =
let mutable retval = defaultValue
let success = DateTime.TryParse(value, ref retval)
retval
(Is it obvious yet that I’m still an F# neophyte? Yes, I’m now grabbing the old F# books and reading them so that I develop some sense of style because the above is suboptimal.)
The Select API supports the standard equality operators:
- >
- <
- <=
- >=
- =
- !=
not makes an appearance to balance out like and is null (not like, is not null). You can also do range checking via the between operator, value checking against a set via in, and operations against multi-valued attributes using every(). A great set of examples is up on Amazon.
Karmic Code Monkey 