» Cascade Conference, “Database Publishing” 9/28/2009
Jon Whitener, Web Communications Specialist, University of Detroit Mercy. “Database Publishing.”
How many of you…
- New to Cascade Server?
- Are adminitrators?
- Use Cascade for mulitple, separate but related sites?
- Have worked on any Web site that pulled data dynamically
from a DB? - Have used Cascade’s Database Publishing?
Most users are administrators and/or technical users. A few using database publishing already.
This will be an introductory session to spark interest in what database publishing can do.
What is Database Publishing?
Usally CS publishes content to a web server. With database publishing Cascade can also publish key elements (not all) to MySQL. Then you can have any of your websites do live queries/reads of that database on the fly.
Dynamic sites are everywhere.
Example: UDM news articles
UDM has multiple sites—www (main) and a number of colleges and schools each with their own site, and certain departments or areas that are large enough to warrant a site of their own.
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 inclue 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 within Cascade was difficul 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…
- Upgrade to v.5 broke it
Database publishing offers a better way
- “DBP” for short
The goals
- Enable separate sites to share news articles
- Avoid duplication of content
- Avoid multiple publishes for each article creations/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 - Now any site can do a live read on the database
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)
- External database schema is set by Hannon Hill
- …
Set up Cascade Administration assets
- Transport
- Target
- Destination
- Template
- Configuration Set
- Metadata Set
- Data Definition
- Content Type
This is what you have to do anytime you want to set up something new in Cascade.
Transport
[screenshot of Transports, database transports]
- Give it a name
- Site ID
- A number you pick/set
- Put in the information to connect to the external database server
- And the username Cascade will use to access and update the database
- needs to be a user in the database
Click “Test Transport” to check that it works.
Target
- select the transport you created
Destination
- Create and test
Template
- Create a super simple HTML template
Configuation 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
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 are relevant to a news article.
- When creating/editing a page, custom metadata will appear as (in this example) a set of checkboxes
Content Type
- Ties the configuration set, metadata set, and data definition together.
Site ID cascades up
- Transport
- Destination
- Target
- Template
- Configuration—> Configuration Set
- Content Type
Site will be indicated by the Cascade asset path. // Maybe Site ID not needed later?
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
(list of fields)
- id
- account_id
- site_id – which site it is
- cms_id
- folder_id
- metadata_id – relates to metadata table
- name
- path
- content
1 record published
Table: metadata
Same as default metadata set
- id
- account_id
- site_id
- display_name
- title
- summary
- teaser
- keywords
- description
- author
- review_date
- start_date
- end_date
- last_published_at
- etc.
1 record published
metadata_custom
2 records published
- id
- account_id
- site_id
- file_id
- folder_id
- page_id
- field
- value
Time to share
- Repeat steps from Transport up, for another site (Business) with Site ID 2
…
Tables after Site 2 Publish (selected fields)
[screenshot, page, two records]
[screenshot, metadata_custom, 5 records]
Create PHP web page
- PHP to connect to database
- The SQL we’ll use in this PHP page:
SELECT page.path, metadata.title
FROM page
INNER JOIN metadata
ON page.metadata_id = metadata.id
WHERE page.id IN
( SELECT page_id
FROM metadata_custom
WHERE field = ‘audiences’ AND value = ‘Alumni’ )
- PHP that queries DB and creates link list
// uses path in link—path in Cascade is domain
echo “” . $info[‘title’] . “”;
Now anywhere on any of the sites we can include the news.