How to set up an SQL Content Repository from Scratch

DOWNLOAD FILES - Hybrid.zip is a module implementing this example

Sometimes it's convenient to store HTML content in the file system rather than in a database. At the same time we also want to store content attributes in a database so that we can perform searching and content selection quickly.

This is done using a Hybrid SQL content repository, this article steps you through the creation of such a repository. In this case we'll create a simple repository contains HTML items articles which have a single string property author. 

From this template you will be able to create more complex repositories that meet your particular needs.

Before you Start

• Decide where your file content will reside filenames are relative to the doc root so you might want to store your content under there. I'd suggest a folder called articles directly under your document root
• Ensure that your database (probably SOLID) is running
• If you have DPS ensure that you can view Profile information in the DCC without errors

Setting up the Repository

Writing the Repository Definition File

To start with it's easiest just to copy an existing file modify it as required. The folder and content folder information is standard and doesn't need to be changed. A starter file is provided here, this file defines the folder and article items and specifies a single additional property author for the article item.

You may need to make the following modifications

• Change the pathPrefix specified for the atg.repository.FilePropertyDescriptor location property of article

Once you have the demo up and running you may want to

• Add your own properties to the article item descriptor
• Remove the author property from article
• Rename the location property

Before you edit the file further I'd suggest getting it up and running as is and then making your modifications

Notes

• The parentFolder property must have the same name for both content and folder items
• The pathPrefix specified for the atg.repository.FilePropertyDescriptor is relative to the system doc root
• The 'root' folder maps to the path specified by pathPrefix 

Configuring the Repository Component

The easiest way to configure a new repository component is to copy the existing SQLRepository component from /atg/dynamo/service/jdbc/SQLRespository to your own nucleus location. The set the defaultViewName and definitionFiles properties to match your repository.

I've done that below, so you can just save this code as HybridRespository.properties in your CONFIGPATH 

$class=atg.adapter.gsa.GSARepository
XMLToolsFactory=/atg/dynamo/service/xml/XMLToolsFactory
dataSource=/atg/dynamo/service/jdbc/JTDataSource
defaultViewName=article
definitionFiles=/db/hybrid.xml
idGenerator=/atg/dynamo/service/IdGenerator
repositoryName=HybridDemo
transactionManager=/atg/dynamo/transaction/TransactionManager

I chose to place my new repository in /db/HybridRepository, so the file is \db\HybridRepository.properties in my config directory. 

You may need to make the following modifications

• Ensure that definitionFiles is configured to point to your repository definition xml file
• Ensure that defaultViewName matches one of your item descriptor names

Once you have the demo up and running you may want to

• Change the repository name to something meaningful to you

The Database Tables

To run SQL against your repository you can use JDBC browser in the Dynamo JHTML admin UI. With your server running go to http://localhost:8830/atg/dynamo/admin/jdbcbrowser/executeQuery.jhtml. You should just be able to paste the SQL into the text box and execute and commit

The Database Tables for the Folder Hierarchy

CREATE TABLE hsqlc_folders (
        id                      INTEGER not null,
        parentFolder            INTEGER null references hsqlc_folders(id),
        name                    VARCHAR(32)     not null,
        path                    VARCHAR(512) null,
        primary key(id)
)

To keep things simple database column names match the item property names

Creating the root folder

insert into hsqlc_folders (ID, NAME) values(1,'articles')

This makes sure we have a root folder

The Database Tables for the Content Articles

CREATE TABLE hsqlc_articles (
        id                      INTEGER not null,
        name                    VARCHAR(32)     not null,
        author                  VARCHAR(32)     null,
        parentFolder            INTEGER not null references hsqlc_folders(id),
        path                    VARCHAR(512) null,
        primary key(id)
)

We have now set up the database tables and configured the repository, we are ready to start adding content. To do this via the DCC your new repository component should be added to the initialRepositories property of /atg/registry/ContentRepositories. Depending on where you configured your content repository your ContentRepositories.properties will contain a line like this...

initialRepositories+=/db/HybridRepository

Now, if you have dynamo running, stop the server. 

Start Dynamo and the DCC, log in and select Content. Your new repository should appear in the DCC. Select list items of type folder and you should see the root folder we created.

Managing Content in the Repository

Manually Adding a Content Item

Adding the Item Content to the Server 

• Copy this file (diary.jhtml) to the articles folder under your doc root
• Start Dynamo if necessary and ensure that the file is available through Dynamo by checking here
  • http://localhost:8840/articles/diary.jhtml

Adding an Item to the Database

We can manually add content records to the repository database via the DCC

• If necessary Start Dynamo and the DCC
• log in and select Content
• Select list items of type article
• The first time you do this you should see that there are no article items
• Select New Item and enter the following values (leave the optional ID field blank)
  • author : Plato
  • name: diary.jhtml
  • parentFolder : (select the root folder we created)
• Click OK

Note: that the location field of the item is calculated automatically

Testing the Repository

If you have DPS, this is probably a good time to create a ContentTargeter to test your repository. For now just create a targeter which returns all the articles in the repository to all people. In my setup that generates the file /atg/registry/RepositoryTargeters/HybridDemo/AllContent.properties which follows...

# /atg/registry/RepositoryTargeters/HybridDemo/AllContent
$class=atg.targeting.DynamicContentTargeter
$description=Shows\ all\ content\ in\ the\ Hybrid\ Demo\ repository
repository=/db/HybridRepository
repositoryViewName=article
rulesets=<ruleset>\n\ \ <accepts>\n\ \ \ \ <rule\ op\=and\ tag\="Show">\n\ \ \ \ \ \ <rule\ op\=and\ tag\="Content">\n\ \ \ \ \ \ </rule>\n\ \ \ \ </rule>\n\ \ </accepts>\n</ruleset>

Once you have the targeter you can create a simple JHTML page to display all the content items like this one. Again if you have left all the names alone you should just be able to save this file into your doc root and test the repository.

Create another targeter which only returns articles whose author is Plato. This query will be done against the information in the database, which is the whole point of this hybrid approach.

Manually Creating the Folder Hierarchy

The SQL content repository maps content into a folder hierarchy stored in the database. Each content item has a specified parent folder which must appear in the folder hierarchy. All folders except the root folder also have a specified parent folder, in this way a path to a given content item can be derived by concatenating the chain of parent folders up to the root.

The on-disk root folder of the repository is a concatenation of two things...

• <dynamo-doc-root> and pathPrefix specified in the location property

If you want to use sub-folders you should create them on the disk and in the database to match. Create folder items in the DCC using the new item button in the repository view, just as you created the article item before...

Don't forget to set the parentFolder property. After you add one or more folders you can view the repository as a folder tree. Just select View By Folder from the View menu. (You may need to disconnect and reconnect the DCC from Dynamo to see new folders in the By folder View)

Summary

We've created the basics of a Hybrid SQL Content Repository, but obviously there is more to it than this. 

Maintaining the relationships between the files and the database manually would be a tedious and error prone task, in a following article we'll look at the ContentLoader component which automates this task