Manually updating the database schema

Release 1.0 - ...

The Smartsite CMS is very flexible in handling different database configurations. Depending upon your needs, you can extend the CMS database schema in several ways.

Examples of changes to the database schema:

  • Adding an extra column to an existing table.
    For example adding an extra CTSpecifXXX column to Contents. In this case, you must als add the column to table physicalFields  to make the columns visible to the CMS.
  • Adding a extra table to the database
  • Ading a trigger

There are a few considerations to take into account when making changes to database objects.

Invoke procedure pr_Rebuildviews after changing the database

The stored procedure pr_Rebuildviews refreshes all views that might be affected by database changes.

Working with schema-bound objects

The Smartsite database can include several schema-bound views. Indexed views must be schem-bound, this as a requirement imposed by SQL Server.

If you modify the Contents table using the SQL Server Management Studio, SQl Server might want to drop schema-bound views when persisting the changes.

To cope with this, Smartsite includes procedures to reconstruct the dropped views:

Reconstruct dropped views
View name SQL statement to reconstruct the view
vwFriendlyNames EXEC pr_FriendlyNames 'prep'
vwTsContent EXEC pr_TextSearch 'prep'

You can also use the stored procedure pr_DisableSchemaBinding to temporarily disable schemabinding for a specific object. Do not forget to restore schemabinding afterwards!

Controlling schemabinding
Status SQL Statement
Disable EXEC pr_DisableSchemaBinding 'object_name', 1
Enable EXEC pr_DisableSchemaBinding 'object_name', 0

Add new database objects to Smartsite.data.config

Whenever you add an object to the database (tables, views, procedures), you must also add the object to the Schema section of the Smartsite.Data.Config.
The Smartsite meta layer uses this information to track dependencies between database objects and to control internal caching.