Introduction

The WebSite Director (WSD) Database Publishing feature allows WSD users to update information in any database that can be accessed by WSD. This allows site owners to add workflow to database updating for dynamic content and other types of information stored in SQL databases.

Note:The WSD Database Publishing feature does not support the retrieval of dynamic content directly from the SQL database. A directory structure on the web site (usually hidden from public view) mirrors the content stored in the database. WSD uses Page Layout Templates and data records to update database content.

This tutorial will guide you through the following steps required for database publishing using WSD:

1. Site Setup for Database Publishing
2. Converting Existing Database Content for Updating by WSD
3. Creating Page Layout Templates to Update a Database
4. Using Page Layout Templates to Update a Database
5. Publishing into a Database

1.Site Setup for Database Publishing

To implement this feature, the site owner must maintain a "flat-file data record" for EACH database record created/managed by WSD. To do this, you must first create a directory to be used for storing database information in the file system (for example, /database_info). If you have multiple database record types (for example, Employee records and Product records), create a child directory for each record type (for example, /database_info/employees and /database_info/products).

For additional security, you can password-protect the /database_info subdirectory, using the same usernames and passwords assigned to WSD users, by placing a copy of the ".htaccess" file located in the WSD install directory into the /database_info directory. If your web server does not support .htaccess files, you can set up another web server authentication mechanism for the /database_info directory.

You should clearly define and document the naming convention you use for the "flat-file data record" content associated with your database records. For example, if you are updating an employee database, your flat file records should clearly indicate exactly WHICH employee’s information is contained in the file. A sample directory listing for employee records might look like this:

/database_info/employees/emp000123.html
/database_info/employees/emp000152.html
/database_info/employees/emp000223.html
/database_info/employees/emp000231.html
/database_info/employees/emp000311.html

Warning: WSD can not automatically associate the naming convention for your files with any database keys actually contained within any given file. Your users must ensure that the relationship between the file name used to describe the database record being updated and the actual key used to update the database are correct!

Please Note: The WSD Database Publishing functionality in this (initial) implementation is more complex and difficult to implement than the true "WSD Database Management" facility we will provide in a future version of WSD! You should not attempt this without assistance if you are not experienced with SQL database definition, creation, and maintenance.

If your database is empty and all content will be updated using WSD, go directly to "Creating Page Layout Templates to Update a Database" below.

2.Converting Existing Database Content for Updating

If you have existing content in your database, you should use the following to manage that content using WSD:

1. Backup your existing database.
2. Create the Page Layout Templates that will be used to update the database (see "Creating Page Layout Templates to Update a Database" below). These templates will also be used to export existing data from the database during this conversion process.
3. If any of the template field names in the Page Layout Templates do not match the corresponding database record field names, you will need to add a 'dbfield="<database record field name>"' attribute to each template field definition that does not have matching field/record names.
4. Test the templates created in Steps 1 and 2 to verify that they have the correct field definitions to update the database table properly.
5. For each table in the database that will be updated by WSD, perform Steps A through C below.
6. Verify that the database content is correct.
7. Begin using WSD’s database updating functionality and update your database content using Page Layout Templates in WSD.
    

3.Creating Page Layout Templates to Update a Database

Database Publishing using WSD is enabled through the use of Page Layout Templates containing database update commands. These templates are used to maintain database content corresponding to records stored in the file system. The database commands in a Page Layout Template define the database and database tables into which WSD will publish data when a template is applied.

The Page Layout Templates you use to publish database content must contain SQL queries. These queries, used to Insert, Update, and Delete database records, are based on the WSD request type (ADD, MODIFY, DELETE, etc.) that you will use to publish your database content from the file system version of each database record. If you are not familiar with your file system structure of database information, carefully read "Converting Existing Database Content for Updating by WSD" above!

Note: As a template designer you do not have to know the exact structure of the database, but you must be able to construct SQL queries or ask someone who does to create the SQL queries you will be using within your Page Layout Template. The overall format of each document in the file system is not important, other than for those areas that define the template fields and field data used to update database records during publishing.

In addition to specifying database information, Page Layout Templates used for database updates also define the data entry fields used to enter the database field data on the Create/Edit Online screen. Instructions for defining these fields are contained in the Page Layout Template tutorials and the Page Layout Templates User Guide (PDF version) accessed from the Tutorial Index on any WSD screen.

Reminder: The WSD Database Publishing feature does not support retrieving dynamic content from your SQL database. A directory structure on the web site mirrors the content stored in the database. Each database record has a corresponding document in that directory structure. Each record in the document structure contains within it a database "Record Key" and one or more database content values, depending on the number of database fields to be updated when WSD publishes the content.

The database definition used in the following examples is that defined for the WSD demo site at http://demoweb.cyberteams.com, where CyberTeams provides a PHP-based dynamic content engine for simple experimentation. If you do not have access to this demo area and wish to experiment using the "sample.db_template" installed with WSD, please contact your CyberTeams' representative for a username and password.

The "sample.db_template" installed with WSD assumes the following database definition:

table articles ( 
      releasedate     char(80) binary not null primary key,
      icon            char(80) binary,
      headline        char(80) binary,
      story           blob,
      authorname      char(80) binary,
      authoremail     char(80) binary
);

To specify Database Publishing for a specific Page Layout Template, the template must include two sets of template commands, normally defined at the top of each Page Layout Template used for database updating.

The first set of commands specifies the information WSD needs to connect to the database server to publish the data into your database

1. DatabaseName <text-value> - Data Source (ODBC) or Database Name
2. DatabaseUser <database login username> - Database User Name
3. DatabasePassword <text-value> - Database User Password
4. DatabaseHost <text-value> - Database Server Hostname (MySQL or MiniSQL only)
5. DatabasePort <value> - Database Server Port or Config File (MySQL or MiniSQL only)

Note: Fields 4) and 5) apply to MySQL and MiniSQL databases only, and are not required when using ODBC

Examples (from WSD sample.dbtemplate):

1. DatabaseName wsddb
2. DatabaseUser wsduser
3. DatabasePassword wsdpassword
4. DatabaseHost wsdserver

The second set of commands specifies the SQL queries used by WSD to update the database:

1. QueryInsert <SQL INSERT query>
   - WSD will use this query when publishing content ADD and COPY requests
   
   
2. QueryUpdate <SQLUPDATE query>
   - WSD will use this query when publishing content MODIFY and MOVE requests
   
   
3. QueryRename <SQL UPDATE query>
   - WSD will use this query when publishing content RENAME requests
   
   
4. QueryDelete <SQL DELETE query>
   - WSD will use this query when publishing content DELETE requests

Syntax Note: The System Administration Tutorial links you to the tutorial on "Page Layout Templates" (listed under Template Processing). Please read this tutorial carefully. Also, review the "Page Layout Templates" User Guide, available in PDF format.

Examples (from WSD sample.db_template):

1. QueryInsert INSERT into articles values('$NewsReleaseDate$','$Icon$','$Headline$','$Story$','<$prop:authorname$>',' <$prop:authoremail$>')
2. QueryRename UPDATE articles set releasedate =’$NewsReleaseDate$’ where releasedate=‘<$prop:dbkey$>-<$prop:NewsReleaseDate$>’ (See Note 1 below.)
3. QueryUpdateUPDATEarticlesseticon='$Icon$',headline='$Headline$',story='$Story$',authorname='<$prop:authorname$>',
   authoremail='<$prop:authoremail$>' where releasedate='$NewsReleaseDate$'
4. QueryDelete DELETE from articles where releasedate='$NewsReleaseDate$'

Note 1: WSD will automatically create temporary properties containing existing values for all "dbkey" fieldname variables when creating RENAME requests. These values can be used to refer to the original field values during the update process.

Note 2: Each SQL Query must be on a single line.

Multiple queries of each type (Insert, Update, or Delete) can be specified when multiple tables need to be updated from the same Page Layout Template. The query definitions can contain three kinds of replacement variables:

1. WSD properties - Specified using the normal template tag format for WSD properties ("<$prop:property$>"). For each occurrence of one of these variables, WSD retrieves the specified property from the meta data record associated with the WSD request.
2. WSD template field value - For each occurrence, WSD retrieves the value the user enters into the specified template field when the Page Layout Template is used to create, modify, or delete database information. This type of template field is referenced in the format "<$field:field-name$>". The template field name is case-sensitive and may not contain spaces. Any type of WSD template field can be used in database queries.
3. WSD template field reference - Specified using the normal template tag format for WSD references ("<$reference $prop:reference-prop$:$prop:source-prop$>" or "<$reference {/path/to/file.html}:$field:source-field$>"). For each occurrence of a reference tag, WSD retrieves the specified property for template field value from the document specified directly or indirectly in the first part of the reference tag.

Multiple databases can be updated from a single template by specifying multiple sets of database commands and SQL queries. All database and SQL query commands are processed sequentially in the template, so any later database commands will override previous commands (and the previous database will be closed and the new one opened). If the values of one or more database commands remain the same for multiple databases, they do not need to be repeated. For example:

DatabaseName employees
DatabaseUser wsduser
DatabasePassword wsdpassword
DatabaseHost wsdserver

QueryInsert INSERT into employee_table ...
QueryUpdate UPDATE employee_table ...
QueryRename UPDATE employee_table ...
QueryDelete DELETE from employee_table ...

DatabaseName products

QueryInsert INSERT into product_info ...
QueryUpdate UPDATE product_info ...
QueryRename UPDATE product_info ...
QueryDelete DELETE from product_info ...

After issuing the SQL query commands to the database, WSD checks the results of the query to determine how many records were updated in the database. Unless told otherwise, WSD assumes that a single record will be updated for each SQL query command. If no records are updated (if the "where" clause in an UPDATE or DELETE query did not match any existing records, for example) or more than one record is updated, WSD will display an error to the user and the publishing operation will be cancelled.

If you know that a query will update more than one record, you can change the expected results by using the "QueryResults" command, in the format "QueryResults <number>". For example, if you know that an SQL query will update two records, put the command "QueryResults 2" before that query. The results of all queries following a "QueryResults" command will be checked for the specified number of updated records, until another "QueryResults" command changes that number. If a query will result in a variable number of updated records, you can turn off the expected results checking using "QueryResults *".

In addition to placing Database and Query commands directly in the Page Layout Template, you can also include such commands from a file or dynamic script or program using the "QueryHref" command, in the format "QueryHref <URL>", where <URL> is a local or remote URL. The QueryHref URL is in the same format as the URL used in the standard <$href ...> template tag and can contain either a hard-coded URL or a URL computed from the values of properties or template fields in the document being published.

The output from the page or script specified by the URL is processed for Database and Query commands and other template tags, just as if it were specified directly in the template itself. Some examples:

QueryHref /path/to/my/script.asp
QueryHref http://www.mycompany.com/path/to/web/page.html
QueryHref {/scripts/myscript.asp?Filename= $prop:filename$}