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();
        }
Ejemplo n.º 3
0
        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();
        }