Brian Mains

Facebook Connect and Localhost

Most people may not realize, but you can use Facebook Connect with your http://localhost server.  This works great for ASP.NET and MVC (and other environments too, but I'm primarily a .NET guy so please pardon my favoritism).  You setup the localhost environment as the Facebook connect URL, as in http://localhost/<Virtual>.  I would recommend using a virtual directory, and not using the Cassini web server (the local web server which may reassing port addresses on you).

You do have to be connected to the internet for this to work, as an FYI.  Not usually a problem, but just something to know about.  THis is because Facebook is making requests to its server, and there isn't anything setup to use a local credentials store for development only.

Using FQL in SQL Server Over REST

If you haven't heard from my blog or Facebook WIKI's before, there are two ways to query data from Facebook, using the ApiClient object to query the data from the Facebook server, or by using FQL to query against their propietary database directly.  In a recent app, I found that using FQL cut in half the number of calls to the server than what I would have had to made using the REST api.  So using FQL can be a great benefit.  Picture this query:

select page_id, name, page_url, pic_small, website, location, bio, founded from page where page_id in (select page_id from page_fan where uid = '{0}')

This queries the database server looking for pages that you became a fan of, where you are a fan of that page.  There are two primary tables: page and page_fan, where page contains the page data, and the page_fan table links your user ID to the page that you are a fan of.  To do this via rest required two calls to the server, one to get the pages you are a fan of, while the other to get the details of the page.  This means you have to use callbacks within callbacks, and really creates a nested hierarchy.  This way, FQL cuts off that work.

I've had other scenario's where it was going to take many calls to have the same effect as one FQL query, where you end up making a rest call just to get the ID's of your friends, then making a call to load your friend's data, and then loading other data related to your friends.  You can see how that can be cumbersome without FQL.

Adding an Effective/End Date CodeSmith Template

I really like CodeSmith, a great tool for generating templates.  It comes with a variety of default templates, all managed in a Visual Studio-like environments, complete with intellisense that's good, but not quite as powerful as Visual Studio.  I do a lot of scripting, so I wanted to make stored procedure generation easier.  One scenario not covered is reference tables, where the reference tables happen to have two specific columns all the time:

EffectiveDate  datetime not null,
EndDate datetime null

The effective and end dates determine which entries are in process at the given time.  I don't want to be having to write this code all the time.  Instead, I added this to the default template (StoredProcedures.cst) that comes with CodeSmith.  These are the modifications I made.  First, I need to be able to use the IEnumerable<T> interface, and thus we need the mscorlib assembly.  So I added these two declarations at the top.  More on why in a little bit.

<%@ Assembly Name="mscorlib" %>
<%@ Import Namespace="System.Collections.Generic" %>

I added a new property for this method that can be used to turn it off or on.  This is a boolean, with a default to true:

<%@ Property Name="IncludeSelectByEffectiveEndDate" Type="System.Boolean" Default="True" Category="3. Procedure Types"
 Description="If true a SELECT procedure will be generated to select by effective/end dates, if the table has these fields." %>

If you are unfamiliar with this, CodeSmith uses the @Property attribute to allow a configurable setting that can be changed in the property window (shown below).  This property window makes it easy to add all sorts of configuration capabilities to your scripts.  Note the description appears within the description window.

 So now we have a property to turn our feature on and off, we need some supporting methods, such as a method to generate the proc name.  The existing template has a variety of method that do this, and working in straight copy-and-adapt mode, I added a method that follows suit as shown below.  Note: if you aren't aware, CodeSmith allows code segments within its <script runat='server"> tags, following an inline code approach that ASP.NET uses.  This makes for a great place to stick in helper methods that return text, or write a response directly to the output.

public string GetSelectByEffectiveEndDateProcedureName()
{
 return String.Format("{0}[{1}{2}ReadByEffectiveEndDate]", GetTableOwner(), ProcedurePrefix, GetEntityName(true));
}

Our first task in using this is to drop the proc, if it exists, via the GenerateDropStatement method.  This work is already done, and needs a simple call to first check if the conditions are met (the flag is set to true and we indeed have an EffectiveDate/EndDate fields because not all tables will).  Note that SourceTable is a TableSchema object that represents that individual table.

if (IncludeSelectByEffectiveEndDate &&
  this.SourceTable.Columns.Contains("EffectiveDate") &&
  this.SourceTable.Columns.Contains("EndDate"))
 {
  GenerateDropStatement(GetSelectByEffectiveEndDateProcedureName());
 }

 Next, the process for creating the proc is pretty simple too.  It starts off with a generic comment and header (as repeated from other examples.  I must do the same flag/effective/end date check, and if all is well, then we can begin by generating the header (already done via GenerateProcedureHeader).  Take a look at the body below.

<%------------------------------------------------------------------------------------------
*
* SelectByEffectiveEndDate Procedure
*
------------------------------------------------------------------------------------------%>
<%
if (IncludeSelectByEffectiveEndDate &&
 this.SourceTable.Columns.Contains("EffectiveDate") &&
 this.SourceTable.Columns.Contains("EndDate"))
{
 GenerateProcedureHeader(GetSelectByEffectiveEndDateProcedureName());
%>

CREATE PROCEDURE <%= GetSelectByEffectiveEndDateProcedureName() %>
(
 @EffectiveDate  datetime,
 @EndDate   datetime
)
AS

SET NOCOUNT ON
<% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %>

SELECT
 <% GenerateColumns(SourceTable.Columns, 1); %>
FROM
 <%= GetTableOwner() %>[<%= SourceTable.Name %>]
WHERE
 ISNULL(EffectiveDate, '1/1/1900') <= @EffectiveDate AND
 ISNULL(EndDate, '12/31/9999') >= @EndDate

<%
 GenerateProcedureFooter(GetSelectProcedureName());
 this.Progress.PerformStep();
}
%>

 In this case, I know the fields already, so I don't need to generate them dynamically since I'm assuming they exist.  The GenerateColumns methods generates all columns for select, GetTableOwner returns dbo or the schema name for whatever the case may be, and the table name is accessible via SourceTable.Name property (SourceTable, as I said before is a TableSchema object).

For more information about using CodePlex and the database schema objects, check out this article: http://aspalliance.com/1580_Creating_Code_Smith_Templates.all

MVC Controller Context - Where does it come from?

The Controller class maintains a reference to the HttpContext property, and object of HttpContextBase.  HttpContextBase is a great class because now we have an abstract way to replace default logic if needed (but generally that wouldn't be recommended).  Ideally, it's great for fake environments.  But I digress, HttpContextBase is the main object for accessing context-specific information.  But where does this actually get created?  I went digging around the API in Reflector, and found where the MVC handlers that create the request.  The MVCHttpHandler uses theMvcHandler object, along with the HttpContext.Current reference as a way to instantiate the context.  So while in ASP.NET you would directly reference HttpContext.Current to access the context, here in MVC references to HttpContextBase comes from HttpContext.Current.

If , for whatever reason, you wanted to instantiate the HttpContext object directly, you would do this via: new HttpContextWrapper(HttpContext.Current).

Facebook Location Object

If you have used the Facebook FQL syntax to query the location column of a table, or have requested the location parameter from the friends_get or other API methods, what you need to know is location is a complex object consisting of the following properties:

• city
• country
• state
• street
• zip

These properties can be directly accessed via location.zip, location.city, etc.

Facebook FQL

Facebook supports a variant of SQL called FQL, or Facebook Query Language.  If you know anything about Facebook's data storage center, Facebook has moved away from the traditional relational database and created their own custom application for storing data, which has the ability to query petaflops worth of data in seconds.  So what is FQL?  FQL looks very like SQL.  It has a select and select list, a from, a where clause, which makes it seem like its ordinary SQL.  However, it cannot use inner joins or outer joins, and its limited on the number of fields that it can query.  For more information on the language itself, please consult this: http://wiki.developers.facebook.com/index.php/FQL.  For a list of tables, see this. http://wiki.developers.facebook.com/index.php/FQL_Tables.

One way FQL can be executed is in JavaScript, through the fql_query method.  Below is a sample query where pages are queried for its page id, name, small picture, location, and website. 

var api = FB.Facebook.apiClient;
api.fql_query('SELECT page_id, name, pic_small, location, website FROM page WHERE name in \"' + name + '\"',

function (results, ex) { .. });

Note that FQL may not allow you to restrict input to the exact results you want due to limitations in FQL.  This has been posted around the internet, about having to bring back more results from the database, and limiting the results in your code.  To test out the query that you may want to setup, try it out in the Facebook Tools section.  Selecting the method to execute as fql_query allows you to test database queries against the backend to see what is allowed and what is not.

For some sample queries, check out this page: http://wiki.developers.facebook.com/index.php/Sample_FQL_Queries