public override void MigrateUp()
{
DropProcedureIfExists("posts_GetNext");
DropProcedureIfExists("posts_GetPrevious");
ActiveTable t = OpenTable("Posts");
ActiveStoredProcedure next = new ActiveStoredProcedure(t, "posts_GetNext");
ActiveStoredProcedure prev = new ActiveStoredProcedure(t, "posts_GetPrevious");
next.AddParameter("@ID", DataType.UniqueIdentifier, null);
prev.AddParameter("@ID", DataType.UniqueIdentifier, null);
next.TextBody = prev.TextBody = @"
DECLARE @ControllerID uniqueidentifier
DECLARE @PostDate datetime
SELECT @ControllerID=ControllerID, @PostDate=PublishDate FROM Posts WHERE ID=@ID
IF(@ControllerID IS NOT NULL AND @PostDate IS NOT NULL)
";
next.TextBody += @"
SELECT TOP 1 * FROM Posts WHERE PublishDate > @PostDate AND ControllerID=@ControllerID AND Status=1 ORDER BY PublishDate
";
prev.TextBody += @"
SELECT TOP 1 * FROM Posts WHERE PublishDate < @PostDate AND ControllerID=@ControllerID AND Status=1 ORDER BY PublishDate DESC
";
next.Save();
prev.Save();
}
public override void MigrateUp()
{
ActiveTable t = OpenTable("Tags");
ActiveStoredProcedure next = new ActiveStoredProcedure(t, "tags_GetDistinctStringValues");
next.AddParameter("@Name", DataType.NVarChar(255));
next.TextBody = @"
select top 50 count(id) as NumericValue, Name, StringValue, StringValueNormalized from Tags where Name=@Name
group by name, stringvalue, stringvaluenormalized
order by count(id) desc
";
next.Save();
}