Tuesday, August 21, 2012

NVelocity Template Engine

Note: The NVelocity project was abandoned in 2009, I've wanted to write this article back in November 2010, but I've found a few minutes only recently and in the hope its not a complete waste of time, here you have it with a some modifications and updates.

Velocity was one of the more advanced string templating engines around that time, it didn't take long to port it to .NET which became NVelocity. if you're porting an old application and need a transition time that all your templates will work before you can convert them to one of the more recent and advanced templating engines such as Razor, you may have found the demo program you needed.

I've tried both Castle NVelocity and Terri Liang's NVelocity port, I'm not sure which came before which, if one is an improvement of the other, but I needed something that supports recursive macros and string templates (instead of file/resource templates) which Castle didn't, so I've used the one from Terri Liang, which did.

Apache's velocity has the documentation for the syntax.

First instantiate a new engine:


NVelocity.App.VelocityEngine engine = new NVelocity.App.VelocityEngine();

//set log class type
engine.SetProperty(NVelocity.Runtime.RuntimeConstants.RUNTIME_LOG_LOGSYSTEM_CLASS, typeof(TemplateEngineLog));

//switch to local context, this way each macro/recursive execution uses its own variables.
engine.SetProperty(NVelocity.Runtime.RuntimeConstants.VM_CONTEXT_LOCALSCOPE, "true");

//allows #set to accept null values in the right hand side.
engine.SetProperty(NVelocity.Runtime.RuntimeConstants.SET_NULL_ALLOWED, "true");

//set template resource loader to strings
engine.SetProperty("resource.loader", "string");
engine.SetProperty("string.resource.loader.class", "NVelocity.Runtime.Resource.Loader.StringResourceLoader");

//initialize engine.
engine.Init();


Then assign a new logger if error reporting is desired:


TemplateEngineLog log = new TemplateEngineLog();
engine.SetProperty(NVelocity.Runtime.RuntimeConstants.RUNTIME_LOG_LOGSYSTEM, log);


Then create a context, its similar to ViewData:


//create a context
NVelocity.VelocityContext vcontext = new NVelocity.VelocityContext();

//put default values
vcontext.Put("null", null);
vcontext.Put("true", true);

//add the context values
foreach (var key in context.Keys)
    vcontext.Put(key, context[key]);


Then we have two options, either a one time execution of a template with this:


engine.Evaluate(vcontext, sw, "", template)


or multiple executions - meaning, the template will be compiled  so it will execute a lot faster:


//gets the string resource repository
var repo = NVelocity.Runtime.Resource.Loader.StringResourceLoader.GetRepository();
Guid guidcode = Guid.NewGuid();

//puts a new template inside it
repo.PutStringResource(guidcode.ToString(), template);

nvtemplate = engine.GetTemplate(guidcode.ToString(), "UTF-8");


and for the execution:


//merge uses a saved template
nvtemplate.Merge(vcontext, sw);


The demo project is located at:
https://github.com/drorgl/ForBlog/tree/master/NVelocityDemo

Monday, August 6, 2012

Faster HTTP_HOST / Host / Hostname

Just a side note, if you're developing a multiple-domains web application, it might be helpful to know that there's a faster way to get the hostname than using:


HttpContext.Current.Request.ServerVariables["HTTP_HOST"]


Try this if you've seen its a significant part of your execution:


HttpContext.Current.Request.Headers["Host"]


The reason for that is that ServerVariables is getting many more properties on initialization, including the headers.


Friday, August 3, 2012

Copy Multiple Records and Their Related Records With SQL

A nice little trick for copying multiple records and their related records with SQL.

Suppose you have a Users and Phones tables, you would like to copy users 1-3, you could go with a a cursor and copy one by one and all their phones. 

But

Its a waste of perfectly good CPU cycles.

Here's an example:


declare @fromUserId int = 1
declare @toUserId int = 3

declare @UsersOldNew table (OriginalUserId int, NewUserId int)

merge into Users u
using 
(
    select UserId, Username
    from Users
    where UserId between @fromUserId and @toUserId
) as originalUsers
    on 1=0
when not matched then
    insert (Username)
    values (originalUsers.Username + '_from_' + convert(nvarchar(10),originalUsers.UserID))
output originalUsers.UserId, inserted.UserId into @UsersOldNew;


insert into UserPhones
select NewUserId, PhoneNumber
from UserPhones
    join @UsersOldNew
        on [@UsersOldNew].OriginalUserId = UserPhones.UserID


Cool!

So how did we do it?
First, we declared a table variable to hold the old vs new identity, you can do it with any type of identity you want, int, guid, it doesn't mind.

Then we executed a merge with an always false match, this causes the merge to create a new record for every existing record (returned from originalUsers).

The 3rd part of the merge is an output clause, which writes a record of old and new identities to the temp table.

And the last part just joins on this table and insert a new phone record the old userid had but with the new userid inserted.

Simple, fast and very cool! good job Microsoft!

Thursday, August 2, 2012

Using Google Analytics Data in Your Applications

A few years ago I've been asked to make the analytics data available to our customers, give them the tool they need in order for them to optimize their websites for SEO, check how many of their efforts have been converted to deals, leads, etc'.

We had a few options (like piwik.org), but we already had a few years of data inside google analytics and we wanted to integrate the reports with our own software, making it a one shop stop for our customers.

After attempting to retrieve data on the fly with Google API (download), We've found out we might have more requests than we anticipated for more data and more detailed reports, we decided to import it into our database, create a bunch of indexed views to ease the pain of the database (and ourselves) and give the customers what they want.

And so, a program was born.

I'll describe the stages briefly to get your own data but I won't go in into our implementation for obvious business reasons (though by the time this article was written we stopped using analytics).

First, a few notes about Google Analytics API limits, 50,000 requests per project per day, 10 queries per second per IP, 10,000 requests per profile per day, 10 concurrent requests per profile.

Sound pretty high no?

But imagine the 11th person going into your reporting page getting an error, or imagine your 10,000 users got into your reporting page and someone wants the 10,001 request. From my experience, SEO people will refresh the page as soon as they can to see if anything changed in the last 3 seconds.

Of-curse we can cache the results for X amount of time, but why not store the entire database on our servers and serve it from there? 

So how do I get my program to respect google's limits?
1. For each request, retrieve the most amount of data possible by the API, it will make each request slower, but it also save me requests, making those 10,000 per profile count more.
2. Write a method that will check if I reached the 10 concurrent requests per IP and delay the next request until one of them is finished.
3. Further extent section 2 to include a check if in the past second I requested less than 10 requests, if I'm in the limit, wait a second.

If you're not implementing a multithreaded application, the 10 per second and 10 concurrent per IP are irrelevant to you.

So lets start with a limiter, I've implemented a a class which does the limiting job, its a combination of semaphore and temporal semaphore.

Limiter.cs

Then I've added the main program, first it uses AnalyticsService and set authorization (GDataCredentials).

Then we retrieve all the profiles/accounts with AccountQuery.

Then we determine the timezone that profile is using, its important if your application is serving multiple timezones so everyone will get a consistent time. Analytics stores and serves all dates and hours in the profile's timezone, I'm using PublicDomain to process TzTimeZone as its not part of the .NET framework.

After that, we're going to retrieve the records with DataQuery. Analytics uses a combination of Metrics and Dimensions to store data. Think of Dimensions as the "group by" section in a sql query and the Metrics as the select section.

You can find the reference here: https://developers.google.com/analytics/devguides/reporting/core/dimsmets

And there's a cool tool called Google Analytics Query Explorer in which you can execute queries and see the data returned immediately.

A few more thoughts which might help you implement your own tools:
1. implementation of a timeout method execution, I've noticed that from time to time some of the methods tend to freeze.
2. a retry method execution.
3. sort of sync, you should read every day the data from yesterday until today so everything will be in sync due to time zones differences.

You can find the demo project here:
https://github.com/drorgl/ForBlog/tree/master/GoogleAnalyticsData/GoogleAnalyticsDemo