Blog

Generally, all Content Management Systems are tightly integrated into the websites they control to serve one key function: publish custom content. Almost as one singular entity. From the moment you choose a CMS, you shall be forever locked down by its required platform and technology.

So in terms of the CMS world, nothing revolutionary has happened to change our perception otherwise...until now...

I have been doing some research into some content management systems that sits externally from a platform (such as a website), giving you freedom to manage the content however you like and it something that's gaining a lot of traction. I am starting to see why.  In fact, I'm in the middle of building a site using one of these "externally" managed CMS platforms.

I would say the the main market players are Contentful and Prismic. They both are very similar in the features they provide and do a great job in delivering content to a platform of your choice through simply querying their native API's to return a nice JSON feed. So from a development perspective, they're both just as easy to integrate as each another and the deciding factors on the one you choose will primarily be:

• Price
• Ease of use
• Editor features

Based on these factors alone, I found Prismic to be the ideal candidate to fulfill my clients needs and adding content was a pleasure. It probably has the nicest interface I've seen in a long time. Very quick, easy and has something Contentful didn't have: a nice WYSIWYG editor. The markdown editor alone in Contentful was a deal breaker and I feared it would add an additional learning curve for non-technical clients.

The only strange thing I noticed about Prismic was that you cannot add any form of validation or set a field to be required. Hopefully, this is something they will add to future releases. When you have other great features like an easy image upload to Amazon Cloud for resizing and cropping, having no validation isn't all that important. :-)

I am already more than halfway through my first Prismic managed website and the implementation couldn't be easier with the help of their forum and starter projects in the technology of your choice.

One of the fears I did have whilst implementing Prismic was how well will my pages load on high demand, especially when the content itself is external from the website. Would there be issues or delays in sending content to my platform? I guess this question is still yet to be answered. So far, the page speed has been better than expected (based on initial testing).

Prismic in a nutshell (stolen from their website):

prismic.io is a developer friendly, API-based approach to CMS. It features a Writing Room for content writers to author, manage and store content, and a Content Query API for developers to integrate managed content. Your content doesn't live "in a website / in websites", your project doesn't live "in a CMS"; rather, your content lives in one place and is shared across your websites, and your project lives absolutely anywhere you want.

It's a shame that I have come quite late in the game when it comes to fully utilising the benefits of stored procedures. I am more than familiar on the general uses, such as CRUD operations, passing and returning parameters. Standard stuff!

When I decided to work on a new project, I made a conscious decision to place all my database queries within stored procedures for the following reasons:

• To broaden my current knowledge.
• Remove all queries from web application level for maintainability.
• Attempt to use logic in my stored procedures to do the brunt of the processing in one database call.
• Increase speed of execution and performance for when a procedure is run multiple times.

Out all of the points listed above, my main aim was to try and minimise all my database calls and get the data in a format that can be used directly without any further manipulation at application level.

As you can see from the following stored procedure, I am carrying out checks to ensure specific data elements exist prior to carrying out further database operations. Thus, hopefully improving performance an efficiency.

CREATE PROCEDURE spLogTagActivity
(
    @ItemPrimaryHashtag AS nvarchar(100),
    @Hashtag AS nvarchar(100),
    @TagCategoryID AS int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT OFF;
    
    DECLARE @TagID AS int
    DECLARE @TagIsIgnored AS bit
    DECLARE @ItemID AS int
    DECLARE @ItemTagID AS int
        
    --Get TagID and Tag Ignore value based on the text value
    SELECT @TagID = ID, @TagIsIgnored = IsIgnored FROM Tag WHERE TextLowercase = LOWER(@Hashtag) COLLATE SQL_Latin1_General_CP1_CS_AS
    
    SET @ItemID = (SELECT ID FROM Item WHERE PrimaryHashtag = @ItemPrimaryHashtag)

    --If an Item can be found based on the Primary Hashtag, we will use the data.
    IF (@ItemID IS NOT NULL)
    BEGIN
        IF (@TagID IS NULL AND @TagIsIgnored IS NULL)    
        BEGIN
            --Only insert tags that are not the same as the Item Primary Hashtag
            IF (LOWER(@ItemPrimaryHashtag) <> LOWER(@Hashtag))
            BEGIN
                EXEC spInsertTag @TagCategoryID, @Hashtag, 0, 0, NULL, NULL, NULL, 0, @TagID OUTPUT
            END
                                        
            --Associate new tag to the Item.
            IF (@TagID IS NOT NULL)
                BEGIN
                    EXEC spInsertSingleItemTag @TagID, @ItemID, 1, @ItemTagID OUTPUT
                                                    
                    --Create a new Activity line for the Item.
                    EXEC spInsertTagTracking @ItemTagID
                END
        END    
        ELSE
        BEGIN
            --If a Tag has already been logged, we will create a new Activity line for the Item
            --only if it does not have an ignored status
            IF (@TagID IS NOT NULL AND @TagIsIgnored <> 1)    
            BEGIN
                --Associate the Tag to an Item, if it doesn't already exist.
                SET @ItemTagID = (SELECT ID FROM ItemTag WHERE TagID = @TagID AND ItemID = @ItemID)
                
                IF (@ItemTagID IS NULL)
                BEGIN
                    EXEC spInsertSingleItemTag @TagID, @ItemID, 1, @ItemTagID OUTPUT                                        
                END        
                                    
                EXEC spInsertTagTracking @ItemTagID    
            END
        END
    END
    ELSE
    BEGIN
        IF (@TagID IS NULL AND @TagIsIgnored IS NULL)    
        BEGIN    
            EXEC spInsertTag @TagCategoryID, @Hashtag, 0, 0, NULL, NULL, NULL, 0, @TagID OUTPUT    
        END
    END
END
GO

I won't go into detail on what this sample stored procedure is doing, but I am delivering different outcomes based on the conditional statements. This is something that could have been done at application level, but much nicer in one database call. In other procedures I have utilised CASE and WHILE operators.

The benefits of using stored procedures far outweigh the negatives...Negatives!? Yes negatives...Well I just have one small gripe. If you create a stored procedure that contains quite a bit of meaty logic and find that something is not working (even though syntactically correct), it's quite difficult to debug exactly what point the procedure is failing.

To end the post, here's something surprising I found: The execution time of running a simple SELECT query can be slow when used within stored procedures, even though the query itself is fast. This is something I've encountered myself when wanting to retrieve a single value from one of my tables. The only way to get around this, is by passing you query parameter to a local variable inside the stored procedure. Take a look at this answer on StackOverflow. Strange!