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("Posts");
ActiveStoredProcedure next = new ActiveStoredProcedure(t, "posts_GetNextWithoutTags");
next.TextBody = @"
SELECT TOP 1 * FROM Posts WHERE
PostDate < getDate() AND
ID NOT IN (SELECT ActiveObjectID FROM Tags WHERE Name='Tag' AND Type='Mubble.Models.Post')
ORDER BY PostDate DESC
";
next.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();
}
public override void MigrateUp()
{
ActiveTable t = OpenTable("Controllers");
ActiveStoredProcedure next = new ActiveStoredProcedure(t, "controllers_GetNextWithoutTags");
next.TextBody = @"
SELECT TOP 1 * FROM Controllers WHERE
ActiveObjectType = 'Mubble.Models.Controllers.Article, Mubble.Models' AND
PublishDate < getDate() AND
ID NOT IN (SELECT ActiveObjectID FROM Tags WHERE Name='Tag' AND Type='Mubble.Models.Controller')
ORDER BY PublishDate DESC
";
next.Save();
}
