Database Publishing
Previously, Cascade-managed content was published by navigating a file system and parsing XML. With the addition of database publishing in Cascade 5.0, this content, including pages, files, and folders, can be published to an external database for the purpose of performing live reads from a web application or any other type of application. This is done by setting up a database transport (new for Cascade 5.0) and a destination that uses it, and makes it possible to publish to the external database by way of that destination – the same technique you may have used when using methods of publishing such as FTP or SFTP.
REQUIREMENTS
While Cascade will continue to support multiple vendors, database publishing supports only MySQL 5.0+ for external database servers. The default character set on the external database and all its tables and columns must be set to utf8, and the collation must be utf8_unicode_ci.
TYPES OF CONTENT BEING PUBLISHED
Files - Published files include the name, location, path, and metadata of the associated asset in the CMS; the published version, however, does not include the byte content of the CMS.
Folders - Published folders include the name, location, path, and metadata of the associated asset in the CMS; they serve as containers for collections of other files, pages, and folders.
Pages - Published pages include the name, location, path, and metadata of the associated asset in the CMS; also includes the rendered content of the default region (page-specific content with any default region transformations applied). Rendered content is published for both structured data and WYSIWYG pages.
EXTERNAL DATABASE
Database publishing directs records to an external database that utilizes a set-up containing five tables: file, folder, page, metadata, and metadata_custom. The file, folder, and page tables contain records that reflect the state of Cascade-managed assets. Records that reflect Cascade Dublin and custom metadata values for those file, folder and page tables are contained in metadata and custom_metadata tables. A few notes on some of the above fields:
- account_id should always be 1.
- site_id is an arbitrary value that reflects a particular site to which the record belongs, and is set on the transport.
- folder_id corresponds to the cms_id of a folder, and not it’s id.
The default database schema for database publishing can be found here:
http://www.hannonhill.com/downloads/cascade/db-publish-schema.zip
SITE IDs
As mentioned above, a site id is an arbitrary number set on a transport to reflect the ‘site’ to which a record belongs. As you may have applications for multiple sites operating on a single external database, you may have multiple ‘sites’ in that database. The site id should be set accordingly for the various transports. When assigning them to destinations, be sure that the things you publish are making it to the remote database with the correct site id.
TROUBLESHOOTING DATABASE TRANSPORTS
If Cascade is unable to publish to your external database, make sure these guidelines are being followed:
- The server name and port are correct (usually 3306 for MySQL).
- A firewall is not blocking access on the above port.
- The host that is trying to connect has granted access to the database transport user.
- The bind-address parameter in your MySQL configuration is not set to localhost or the loopback address.
Useful information in solving these problems can be garnered from the output of a transport or destination test.
SUBTLETIES/OTHER NOTES
Until now, publishing has consisted of moving files to a local or remote file system. Database publishing is more along the lines of synchronizing some portion of the asset hierarchy within Cascade to representative records in an external database. When using database publishing, keep in mind a few of these important items :
- Records inserted during a database publish do not represent the assets in the state in which they would have been published to a file system by simply publishing them. This is particularly true if options are set on relevant targets and destinations that relate to publish-time directory and link manipulation (i.e. remove base folder path, include target path, destination path, etc.).
- Links in page content are not rewritten during database publishing, and should reflect the in-CMS path of the entities the records represent.
- After particular sequences of publishes, deletes, and unpublishes (or lack thereof), the state of the external database should be considered.
Example: Let’s say you have a folder containing two pages:
- Folder
- Page 1
- Page 2
The folder, its pages, and their associated metadata would be written to the external database if you were to publish this folder to a destination backed by a database transport. Page1 is then deleted and the folder is republished. If you examined the external database, you’d notice that page1 still has records therein. In database publishing as in normal publishing, it is assumed that you will indicate exactly which assets you want unpublished from their destination.
If the same asset is published to destinations using different database transports, this may result in several files, pages, and folders in the external database with the same cms_id. It is important that you know what criteria to include in SQL queries to ensure that you’re operating on the correct records. The cms_id, the account_id, and the site_id of the assets on which you’re operating should match those on which you intend to operate. Remember, the folder_id of records in the remote database corresponds to the cms_id of its parent folder, and not to the folder_id.
For instance, if you wanted to get the parent folder of a file named ‘koko.png’ in the external database, and you were dealing with a site with id=5 and an account with id=1, your file record would appear in the external database like this:
|
id |
account_id |
site_id |
cms_id |
folder_id |
metadata_id |
name |
path |
|
9 |
1 |
5 |
92436e0a7f00010100b2eca959237ccd |
92436dc67f00010100b2eca953a7298f |
10 |
koko.png |
site5/images/koko.pn |
And your desired folder record would appear like this:
|
id |
account_id |
site_id |
cms_id |
folder_id |
metadata_id |
name |
path |
|
3 |
1 |
5 |
92434dc67f00010100b2eca953a7298f |
9a436dc67f00010100b2eca234526666 |
10 |
images |
site5/image |
In order to select this record, the correct query would be:
SELECT * FROM folder WHERE cms_id='92436dc67f00010100b2eca953a7298f' AND site_id=5 AND account_id=1;
It is entirely possible that that there is another record corresponding to the images folder in the external database with the same cms_id, but with a different account_id, site_id, or both. If you were to omit the expressions restricting the site_id and account_id from the query above, multiple folders would be returned in the result.