Querying SDS - Sorting

This is the fifth part of a series of posts about SQL Data Services (SDS). Last time I showed the basics of how to query SDS, now I will show some more query options. Let’s start with the basic query from last time that returns all the entities in a container

from e in entities select e

By default the entities will be sorted by the Id property. We can sort by other properties like this

from e in entities orderby e["DateDue"] select e

Note that just like in the Where clause flexible properties in the orderby clause use the syntax like e[“DateDue”] but metadata properties use the e.Verison syntax. By default the sort is done in ascending order. You can sort descending like this:

from e in entities orderby e["DateDue"] descending select e

You can also sort by multiple properties

from e in entities orderby e["Completed"],e["DateDue"] select e

As I have mentioned before SDS entities are schema-less so there could be entities in a container that don’t have one of the properties you are sorting on. In these cases the entities will still appear in the output. I have not seen any official documentation that explains how these are handled but it appear they are treated as having a null value and are sorted to the top of the list.

Querying SDS

In my last post I showed how to put data into an SDS database, now we will look at how to get it out. As I mentioned at the end of the last post every entity in SDS has a unique address that can be used to retrieve it directly. For example if I put in this address, https://testsds.data.database.windows.net/v1/Tasks/T1000, and click Get it will retrieve the entity we created last time:

<Entity xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Id>T1000</s:Id><br /> <s:Version>50976472</s:Version><br /> <Message xsi:type="x:string">Finish blog entry on SDS</Message><br /> <DateAdded xsi:type="x:dateTime">2009-02-01T00:00:00</DateAdded><br /> <DateDue xsi:type="x:dateTime">2009-02-07T00:00:00</DateDue><br /> <Completed xsi:type="x:boolean">false</Completed><br /></Entity><br />

This is good for retrieving a single entity, but if we want to find a group of entities based on some parameters we need to use queries. First let’s create two more entities so we have something to query. Change the address so it points back at the Tasks container, for example https://testsds.data.database.windows.net/v1/Tasks/. Enter each of these entities and click Post.

<Entity xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Id>T1001</s:Id><br /> <Message xsi:type="x:string">Take out the trash</Message><br /> <DateAdded xsi:type="x:dateTime">2009-02-02T00:00:00</DateAdded><br /> <DateDue xsi:type="x:dateTime">2009-02-08T00:00:00</DateDue><br /> <Completed xsi:type="x:boolean">true</Completed><br /></Entity><br /><br /><Entity xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Id>T1002</s:Id><br /> <Message xsi:type="x:string">Buy Valentines day gift</Message><br /> <DateAdded xsi:type="x:dateTime">2009-02-03T00:00:00</DateAdded><br /> <DateDue xsi:type="x:dateTime">2009-02-13T00:00:00</DateDue><br /> <Completed xsi:type="x:boolean">false</Completed><br /></Entity><br />

After creating the entities change the address back to the container once again.

Let’s start with the simplest query, enter this in the query box and click Query:

from e in entities select e

This will return an EntitySet with all three entities in our Tasks container. This query has no conditions so it will return all the entities in a container.

Earlier I showed you how to retrieve a single entity by doing a Get on its address, but you can also retrieve and entity using a query like this:

from e in entities where e.Id=="T1001" select e

Here we have added one condition, e.Id==”T1001” which simply means to retrieve all entities where the Id property is T1001. Since each Id is unique this will return a single entity.

What if we wanted to query for all tasks that have not been completed:

from e in entities where e["Completed"]==false select e

You will notice a difference in the syntax for this condition. Instead of e.Completed, we used e[“Completed”] instead. The e.Id syntax we used last time is only used for metadata properties. When you query flexible properties you have to use the e[“Completed”] syntax.

What if we made a mistake in the query and did this instead:

from e in entities where e["Complete"]==false select e

In a traditional database this would throw and exception since the field Complete doesn’t exist in the database, but since SDS is schema less this will not throw an error, it just won’t return any entities.

We are not limited to just specifying one condition, here we query for all incomplete tasks that are due after 2/12/2009:

from e in entities where e["Completed"]==false && e["DateDue"] >DateTime("2009-02-12") select e

We use the logical operator “&&” to specify one condition and another. Also notice that when we compare to the literal date value we must use the DateTime(“”) function, if you just compared to “2009-02-12” it would not work.

That’s the basics of querying SDS. I will talk about some other query topics in my next post.

Working with SDS

In my last two posts I showed you how to get setup to use SQL Data Services, and I described the SDS data model. Now it’s time to start actually working with SDS by using the SSDS Explorer tool.

SDS can be accessed in two ways, using a REST based protocol, or using SOAP. The SDS Explorer tool uses the REST method. At the top of the screen you will see the address (URI) that the requests will be sent to, and the buttons at the bottom represent the various actions that can be executed.

The first step will be to create an authority to work in. If you click the authority button a template for a new authority will be inserted into the text editor. It will look like this:

<s:Authority xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"><br /> <s:Id></s:Id><br /></s:Authority><br />

To create the authority you will first need to put an ID for the authority in the <s:ID> tag. A couple notes on creating the ID. First it must be globally unique over the entire SDS system, meaning that no two people can create the same authority ID. You may want to prefix your authority ID with the application name you created when you signed up for SDS. The ID also must only contain lower case letters, numbers and dashes. Finally, once you create an authority you currently cannot delete it, I assume this will change before the final release of SDS. Once you have entered an ID click the Post button. If everything is working ok you should get a green check mark next to the action buttons and you should receive no errors.

Once you have created the authority the address will automatically change to contain your authority ID. For example if you authority is called ‘testsds’ you will see this:

https://testsds.data.database.windows.net/v1/

If you now click Get you will see some information about the authority. Towards the top you will see the ID you just created in the <s:ID> tag. Below this you will see various statistics about the authority which we won’t get into here.

Now that we have setup an authority, we can create a container inside of it. Click on the Container button to get a template for adding a container. Just like with Authority you need to give the Container an ID. Container IDs can contain both upper and lower case and they ARE case sensitive so ‘Tasks’ would be a different container from ‘tasks’. Enter the ID within the <s:Id> tags and press the Post button to create the container. Here is the code for creating a container called ‘Tasks’:

<s:Container xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"><br /> <s:Id>Tasks</s:Id><br /></s:Container><br />

Once again the Address will change to include the container ID, it will look something like this:

https://testsds.data.database.windows.net/v1/Tasks

Let’s take the “Tasks” off the end of the address so we can go back to the authority level and then click Query, this will query for the contents of the authority. The result will look something like this:

<s:EntitySet xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Container><br /> <s:Id>Tasks</s:Id><br /> <s:Version>50890052</s:Version><br /> </s:Container><br /></s:EntitySet><br />

Here you can see all the containers inside the authority. In this case there is only the Tasks container we just created. Change the address back to https://testsds.data.database.windows.net/v1/Tasks so we are working with the Tasks container once again.

We have created an authority and container, now we can add an entity to the container. Let’s create a simple entity to hold a task like you would have in a to-do list application Here is the code for creating the entity

<Entity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"><br /> <s:Id>T1000</s:Id><br /> <Message xsi:type="x:string">Finish blog entry on SDS</Message><br /> <DateAdded xsi:type="x:dateTime">2/1/2009</DateAdded><br /> <DateDue xsi:type="x:dateTime">2/7/2009</DateDue><br /> <Completed xsi:type="x:boolean">false</Completed><br /></Entity><br />

The first thing we had to fill out in the entity template is the ID, remember that the ID must be unique within the container. We then have four flexible properties, one string property called Message, two datetime properties called DateAdded and DateDue and finally a Boolean property called Completed. You can see that in each property we specify the name of the property using the tag name, and also the data type in the xsi:type attribute. Within the tags we put the actual data. Once you have entered this click Post to add it to the database.

As always, the address will change again to include in the ID of the entity. You will notice that everything in the database, authorities, containers and entities have their own unique addresses. So if we use this address, https://testsds.data.database.windows.net/v1/Tasks/T1000, and then click Get, we will retrieve entity T1000 from the container Tasks in authority testsds.

That covers the basics of how to get data into SDS, next time I will talk about how to query the data.