Print
Upgrading Sitecore OMS from version 1.0.1 rev.090821 to 1.1.0 rev.091012.
Prerequisites
- Sitecore OMS 1.0.1 rev. 090821
If you are using an earlier version of the OMS, then you must update to OMS 1.0.1 rev.090821 before installing this update. - Sitecore CMS 6.2.0 rev.091012
If your Sitecore solution is based on an earlier version, then you must update to Sitecore CMS 6.2.0 rev.091012 before installing this update.
This update requires the following files:
- ShorterNames.sql script (ZIP archive, 1 Kb)
Follow the steps below to upgrade an existing Sitecore OMS 1.0.1 installation to Sitecore OMS 1.1.0 rev.091012:
- Backup the Analytics database.
- Execute the ShorterNames.sql script on the Analytics database to rename tables/columns/indexes (see the release notes for more detailed information).
- Apply the changes mentioned on the Analytics.config Changes in rev.091012 compared to 090821 page.
- If you have any custom code that queries the Analytics database using LINQ, then you must rewrite this code, see the Rewriting Custom Queries article for details.
20.1. Rewriting Custom Queries to the Analytics Database
In Sitecore OMS 1.0.0 and Sitecore OMS 1.0.1, you could query data in the Analytics database using the provided LINQ to SQL classes. In Sitecore OMS 1.1.0, the AnalyticsDataContext and related LINQ to SQL classes have been removed.
If you have any custom queries that use LINQ, you must rewrite these queries. You have two options for rewriting your queries:
- Implement a custom LINQ to SQL layer. This is appropriate if the queries are only going to be used with SQL Server.
- Change the queries to use "pseudo SQL" syntax.
20.1.1. Rewriting custom queries to the Analytics database to use a custom LINQ layer
Using a custom LINQ layer for querying the Analytics database is a quick and easy way to rewrite existing queries that used the AnalyticsDataContext class. Instead of using the AnalyticsDataContext class in your code, you should simply generate the LINQ to SQL data model yourself based on the Analytics database schema. This will create a LINQ to SQL data model that you can make your queries work against.
Notice that the database schema in Sitecore OMS 1.1 was changed to use shorter names for some of the tables and fields, as described in the release notes. You should update your queries accordingly.
20.1.2. Rewriting custom queries to the Analytics database to use "pseudo SQL" syntax
API methods for executing queries
The Analytics API provide the following methods which are used for executing queries that are using "pseudo SQL" syntax:
- AnalyticsManager.ReadOne<T> - read one instance of type T
- AnalyticsManager.ReadMany<T> - read many instances of type T
- AnalyticsManager.Execute – execute query and return number of rows affected
Syntax for "pseudo SQL"
The main difference between normal SQL expressions and "pseudo SQL" expressions is that you must put in special symbols around all table and field names which Sitecore then uses to insert appropriate quotes and brackets into the SQL:
- The symbols "{0}" and "{1}" must be used around all table and field names.
- Example: "select {0}Campaign{1}.{0}CampaignId{1} from {0}Campaign{1}"
- The symbols "{2}" and "{3}" must be used around parameters.
- Example: "... where {0}CampaignId{1} = {2}campaignId{3}"
- The symbol "{4}" inserts a quote and "{5}" inserts a wildcard
- Example: "... where {0}Path{1} LIKE {4}/sitecore/{5}{4}"
- The symbol "{6}" inserts an empty string literal
- Example: "... where {0}Value{1} != {6}"
Differences in SQL syntax among database vendors
Notice that some SQL expressions, such as returning the top x rows from a query, require different syntax for the different data providers and therefore are not easily supported.
Example of using "pseudo SQL"
/// Gets the campaign by id.
/// </summary>
/// <param name="campaignId">The campaign id.</param>
/// <returns></returns>
[CanBeNull]
public virtual Campaign GetCampaignById(Guid campaignId)
{
const string GetCampaignByIdSelect = @"
select
{0}Campaign{1}.{0}CampaignId{1},
{0}Campaign{1}.{0}Type{1},
{0}Campaign{1}.{0}Title{1},
{0}Campaign{1}.{0}Description{1},
{0}Campaign{1}.{0}Cost{1},
{0}Campaign{1}.{0}StartDate{1},
{0}Campaign{1}.{0}EndDate{1},
{0}Campaign{1}.{0}Data{1},
{0}Campaign{1}.{0}IsActive{1}
from {0}Campaign{1}
where {0}CampaignId{1} = {2}campaignId{3}";
return this.ReadOne<Campaign>(GetCampaignByIdSelect, ReadCampaign, "campaignId", campaignId);
}
/// <summary>
/// Gets the campaigns.
/// </summary>
/// <returns></returns>
[NotNull]
public virtual List<Campaign> GetCampaigns()
{
const string GetCampaignsSelect =
@"select
{0}Campaign{1}.{0}CampaignId{1},
{0}Campaign{1}.{0}Type{1},
{0}Campaign{1}.{0}Title{1},
{0}Campaign{1}.{0}Description{1},
{0}Campaign{1}.{0}Cost{1},
{0}Campaign{1}.{0}StartDate{1},
{0}Campaign{1}.{0}EndDate{1},
{0}Campaign{1}.{0}Data{1},
{0}Campaign{1}.{0}IsActive{1}
from {0}Campaign{1}";
return this.ReadMany<Campaign>(GetCampaignsSelect, ReadCampaign);
}
[NotNull]
private Campaign ReadCampaign([NotNull] DataProviderReader reader)
{
Assert.ArgumentNotNull(reader, "reader");
return new Campaign
{
CampaignId = GetGuid(0, reader),
Type = GetString(1, reader),
Title = GetString(2, reader),
Description = GetString(3, reader),
Cost = GetDouble(4, reader),
StartDate = GetDateTime(5, reader),
EndDate = GetDateTime(6, reader),
Data = GetString(7, reader),
IsActive = GetInt(8, reader),
};
}
Rewriting custom filters to use "pseudo SQL" syntax
When you write custom filters for the OMS, the ApplyFilter method must provide SQL which is properly formatted for a particular database vendor when calling SqlCommand.AddWhereClause.For this purpose, you can use the Sitecore "pseudo SQL" syntax which is described in this article.
The recommended approach to creating filters this way is to format the SQL using AnalyticsManager.FormatCommandText.
An example of an OMS filtering class which uses "pseudo SQL":
public class WhereBrowser : FilterBase
{
...
public override void ApplyFilter([NotNull] SqlCommand sqlCommand)
{
Assert.ArgumentNotNull(sqlCommand, "sqlCommand");
var commandText = AnalyticsManager.FormatCommandText("{0}Session{1}.{0}BrowserId{1} = '") + EscapeQuote(this.browserId) + "'";
sqlCommand.AddWhereClause("Session", commandText, this.Except);
}
...
}