Transcript Database Publishing

Database Publishing
Jon Whitener
Web Communications Specialist
University of Detroit Mercy
How many of you…
New to Cascade Server?
Are adminstrators?
Use Cascade for multiple,
separate but related sites?
Have worked on any Web
site that pulled data
dynamically from a DB?
Have used Cascade’s
Database Publishing?
What is
Database Publishing?
Cascade Server
External
Database
Cascade Server
Site A
Site B
External
Database
Site C
Site D
Cascade Server
Site 5
Dynamic sites are…
Dynamic sites are…
Example:
UDM news articles
UDM’s sites
College of
Business
Admin.
College of
Health
Professions
www
(General site)
College of
Engineering
& Science
School of
Dentistry
College of
Liberal Arts
& Education
Alumni
The idea: share news
Enable each site to share (access / use)
the news articles of every other site
Selective inclusion
E.g.
The Business site can include news from
the Engineering site, like Ford CEO visit
The Alumni site can pull articles from any
site where alumni are mentioned
Sharing isn’t always easy
Sharing articles withinCascade was
difficult and/or inefficient
Index blocks
Highly contorted XSL
Publishing to various sites also tricky
 Publish all news pages on all sites
We did get it working …
Sharing isn’t always easy
Upgrade to version 5
broke it
Database Publishing
offers a better way
“DBP” for short
(“DaBaPu” didn’t test well)
The goals
Enable separate sites to share news
articles
Avoid duplication of content
Avoid multiple publishes for each article
creation / edit
Use a tested, familiar technique
How to do it with DBP?
In addition to their
separate target sites,
all sites publish to the
shared, external
database
Sites can now select
from all news articles
Tagging articles
Articles have custom metadata to
indicate appropriate audiences
Alumni, current students, faculty, etc.
Also, path and Site ID are informative
Live sites can select appropriate articles
using these criteria
How does it work?
Overview
Set up external database
Set up Administration assets in Cascade
Create content assets in Cascade
Create PHP Web page that can perform
live queries of external database
Set up external database
MySQL 5.0+ required
Grant access to Cascade Server
Grant access to production Web servers
May want direct access (e.g. Navicat)
Follow security practices (omitted here)
Set up external database
External database schema
is set by Hannon Hill
We’ll look at schema later
Set up Cascade
Administration assets
Transport
Target
Destination
Template
Configuration Set
Metadata Set
Data Definition
Content Type
Transport
Transport
Site Id
User
Transport
“Test
Transport”
option
appears
when a
Transport is
selected
Target
Out. File Ext.
Destination
Test Destination
Test Destination
Template
Configuration Set
We create a new Set here
One Configuration pointing to
new DBP Template
Would likely make sense to add
DBP Configuration to one of
your existing Configuration Sets
Configuration Set
Bear with me …
Data Definition
Metadata Set
Custom (“dynamic”) metadata will be
used to make the news articles easily
selectable
Here, we add “audience” information,
i.e. indicate which audiences would be
interested in the news article
Metadata Set
Metadata Set
When creating / editing a page, custom
metadata will look like:
Content Type
Site ID cascades up
Some administrative assets are specific
to a site number, i.e. the Site ID
Transport
Destination
Target
Template
Configuration  Configuration Set
Content Type
Create content assets
Create Page with new Content Type
Create content assets
Create Page with new Content Type
Now we’re getting to
the good stuff
Let’s publish a page from the general
site – Site ID 1
Peek at the DB
External database contains five tables
page
metadata
metadata_custom
file
folder
We’re concerned with the first three
Table: page
After first Page publish, we have 1 record:
id
4
account_id
1
site_id
1
cms_id
cef30e52c66d185201260e7b6b1a1634
folder_id
67597412c66d1852000369bbff883f8b
metadata_id
1736
name
www-page-1
path
www.udmercy.edu/dbp/page-1
content
<region-render><system-data-structure><headline>This is a
headline</headline><body-text><p>This is REVISED body text, in a p
element.</p><p>This is some more, plus a picture,
below:</p><p><img alt="Basketball"
src="www.udmercy.edu/dbp/basketball.jpg"/></p><p>A link to the <a
href="www.udmercy.edu/index">home page</a>.</p></bodytext></system-data-structure></region-render>
Table: page
After first Page publish, we have 1 record:
id
4
Table’s auto-increment key
account_id
1
Always 1
site_id
1
Set in Transport
cms_id
cef30e52c66d185201260e7b6… Page’s ID in Cascade
folder_id
67597412c66d1852000369bbf… Folder’s ID in Cascade
metadata_id
1736
Foreign key  metadata table
name
www-page-1
Cascade system name
path
www.udmercy.edu/dbp/page-1
Cascade path (≠ published path)
content
<region-render><system-datastructure><headline>This is a
headline</headline><bodytext><p>This is REVISED body
text, in a p element.</p> …
</body-text></system-datastructure></region-render>
XML of the page’s DEFAULT region.
(Would include any transformations
applied to the DEFAULT region.)
Table: metadata
One record, with the fields you know from
a Cascade’s Wired Metadata pane:
idhere matches
page.metadata_id
id
account_id
review_date
site_id
start_date
display_name
end_date
title
last_published_at
summary
last_published_by
teaser
created_by
keywords
created_at
description
updated_by
author
updated_at
Table: metadata_custom
Here, we have two records for the page,
because we selected two checkbox values
First record
Foreign key
 page.id
Second record
id
51
id
52
account_id
1
account_id
1
site_id
1
site_id
1
file_id
[Null]
file_id
[Null]
folder_id
[Null]
folder_id
[Null]
page_id
4
page_id
4
field
audiences
field
audiences
value
Alumni
value
Students
Time to share
Repeat steps, from Transport up, for
another site (Business) with Site ID 2
Publish a page from the Business site to
the external database
Now the DB contains data published
from two separate sites
Site ID 1: general site (www)
Site ID 2: College of Business site
Tables after Site 2 publish
(Selected fields)
page
metadata
Tables after Site 2 publish
(Selected fields)
metadata_custom
Create PHP Web page
PHP to connect to database
Create PHP Web page
The SQL we’ll use in this PHP page
Create PHP Web page
PHP that queries DB and creates link list
Note http://and .htm
The result
The result
You will need to
add another
Configuration to
actually publish
the news article to
the Web server so
the links will work
Thank you!