Sql Content Repository

SqlContentRepository.cs - for v1.0.1.0 and above only

This is a full-featured, ANSI SQL content repository. This is useful in a load balanced or webfarm environment.

    <configSections>
        <section name="phuncms" type="Phun.Configuration.PhunCmsConfigurationSection, Phun" 
                 requirePermission="false" />
    </configSections>
    <phuncms ...>
        <contentMap>
            <add route="CmsContent" repositoryType="sql" 
                 repositorySource="DefaultConnection" repositoryTable="CmsContent"
                 repositoryCache="App_Data\CmsContent" />
        </contentMap>
    </phuncms>

• repositorySource: required, Your connection name configured in connectionString section.
• repositoryTable: required, The name of the table for use in storing your cms content.
• repositoryCache: optional, this is the path to cache cmscontent on your local folder. This also support absolute, relative, and window azure local storage.

SQL Table Structure

Let @repositoryTable="CmsContent", if the table does not exist, the repository will automatically create two tables: CmsContent and CmsContentData.

• CmsContent Table: store file metadata in simple structure to provide maximum query performance. The DataIdString column reference the IdString of CmsContentData.
• CmsContentData Table: store the actual file content as image/bytes.

DataIdString - this string was designed as a GUID for easy data manipulation and system migration. This was originally a GUID/uniqueidentifier datatype but then converted to NVARCHAR for ANSI SQL support.

Caching strategy

SQL is the source of truth for the cms content. Whenever a cms content is requested, the repository will read the metadata from SQL and compare with local file date to determine if the file should be cache.

File history/version controlled

Since the data is stored in on a separate table and all updates are just inserts to CmsContentData, all content are automatically tracked. In future release of PhunCms, we may even provide interface to view, compare, or even rollback from content history.

Side Note

Another advantage of storing content meta and data in separate table is to better support database server ability to for file storage. MS SQL Server example: FileStream or FileTable in Delani.

You can override PhunCms.Data.IDataRepository to provide your own data storage strategy. Then setup WebApi DependencyResolver to resolve the implementation for this interface.

Content history are stored permanently. This means that, if you are worry about your SQL Server disk space, then you can setup something to purge CmsContentData table. It's up to this job to determine how much historical data to store.

-- Job to delete all

DELETE d FROM CmsContentData d WHERE NOT EXISTS (SELECT top 1 1 FROM CmsContent c WHERE c.DataIdString = d.IdString)

-- Job to delete any historical data less than 30 days

DELETE d FROM CmsContentData d WHERE d.CreateDate < (getdate()-30) AND NOT EXISTS (SELECT top 1 * FROM CmsContent c WHERE c.DataIdString = d.IdString)