Connecting read-only Databases in a Multisite Scenario

[rrichter]

In FirstSpirit there are several possibilities to transfer content from one or more master projects to different so called target projects. This could be country websites that use central content from a corporate website or subsidiary companies of a corporation that want to reuse content published in the corporate intranet.

In most cases this will be done using the FirstSpirit module CorporateContent which provides the necessary functions for target projects to subscribe to global content created and updated in a master project. During the publicatishing process changes of global content packages done in the master project will be rolled out to the subscribed target projects. In the target projects changed objects will be released and published to the live site.

This process covers most of the scenarios in which content is reused, but there are other combinations in which the changes of global content will directly affect objects in the target projects and where the editors want to work with recent references of the objects and not copies.

This is when you have to use the FirstSpirit feature of connecting “read-only” databases. One of the standard examples is a global product database - using FirstSpirit datasources – managed in a master project (e.g. a corporate website of an international corporation).

The goal is to make it possible for every country website to create their very own product overviews with products that might only be available in the concerning region.In our example we assume that the product datasource in the master project is based on a database table named “Products”. This table is located in a database schema based on a database layer named “MASTER_DB”.

In the section below you will find a step-by-step instruction to make this datasource available „read-only“ for the target project(s).

Step 1: Creating a „read-only“ copy of the database layer

The first step is to create a „read-only“ copy of the existing database layer “MASTER_DB”.

You open the „ServerManager“ and navigate to „Server > Properties > databases“. You will now see a list of all database layers connected with different projects.

By creating a copy of the layer "MASTER_DB" with the exact same settings and the suffix _ro ("MASTER_DB_ro") we will be able to distinguish between the original and the "read only"-copy.

Step 2: Making the layer available for the target project

Now we have to open the project properties of the target project and navigate to the Databases section.

The checkboxes „Selected“ and „No schema sync“ have to be activated for the „read-only“-layer “MASTER_DB_ro” and confirmed with the OK-button.

Step 3: Saving the schema information

Now we have to save the schema information of the master database schema. To do this we have to open the master project using the “SiteArchitect”. Select the database schema (in the section “Templates > Database Schemata”) that contains the table “Products” and switch into edit mode.

We now save the XML-schema information into a local txt-File by navigating to “Right-mouse-click > Edit externally > Database-Schema”.

The dialog has to be closed via „Discard local copy“. After that we have to leave the edit-mode (CTRL+Shift+E) to avoid further changes.

Step 4: Creating a read-only database schema in the target project

As a base for the datasource in the target project the read-only database schema has to be created.

Using the „SiteArchitect“ we open the target project and create a new database schema (Templates > Database-Schemata) via „New > Create new schema“.

The schema has to be based on the read-only database layer “MASTER_DB_ro” and should have a name showing that this is a schema used for read-only purposes.

Using „Edit external > Database schema“ we now save the content of the txt-File (see Step 3) into the Database schema. In this case the dialog has to be left via „Save local copy and close“ while the database schema has to be saved as well.

Step 5: Activating write protection for the target project

This last step shows how to activate the write protection for the database layer connected to the target project. That avoids changes to the schema or content of the database via the target project.

You can do this in the „Databases“ section of the target project properties in the “ServerManager”. You only have to activate the checkbox „Read-only“ at the corresponding database layer (“MASTER_DB_ro”).

After confirming with „OK“ the product data of the master project is available read-only in the target project.

Now it is possible to create table templates and queries based on the read-only database schema of the target project.

Finally the product data can be visualized and generated for each target project using datasources and product overview pages. They can also be filtered by availability in specific countries if needed.

IMPORTANT: At this point one could assume that it is possible to omit step 5 and manage the global product database from different projects. This is not officially supported by FirstSpirit. A FirstSpirit database can only be managed by one project. All other projects have to be connected “read-only”.

Changes of the database schema in the master project

If there are changes of the database schema in the master project, we have to transfer the changes to the target projects.

We have to roll back the changes made in step 5 to save the XML-schema information following step 2 and to change the existing schema in the target project via „Edit externally > Database schema“ (step 4). After that we have to re-activate the write protection for the database layer in the target project(s) (step 5).