External sources: SQL and CSV Code Sources

This guide explains:

  • How to use a Code Source in Silva
  • How to add, customize and use a SQL Source in Silva
  • How to add, use and update a CSV Source in Silva

Overview 

Silva has an External Sources extension that provides the ability to insert data from non-Silva sources to Silva Documents. These non-Silva, or "external" sources, can be a relational database (e.g. addresses, price-listings, course info, etc.) or the outcome of executing a Python script (which could e.g. make an RSS feed available for Silva Documents).

Since an External Source can potentially be resource intensive or expose a vulnerability, only users with ZMI (Zope Management Interface) access can create External Sources. The exception to this rule is a CSV Source because its functionality is limited. An Author can create a CSV source in the SMI as an asset.

When using External Sources it is the Manager’s responsibility to make sure no vulnerabilities are exposed to the Authors or the public website.

An External Source added to a Silva site may offer the Author a set of parameters to configure the source within the document.

Using a Code Source

Code Source icon

The Code Source element is a feature for including dynamically generated HTML into a document. It can be a page template or Python script. Silva comes with a number of Code Sources pre-installed. See the Code Sources service doc for more information.

Additional Code Sources can created by a Manager. Here is a list of the steps to take:

  1. Go to the Zope (ZMI) management screen and navigate to the desired Silva root, or the level in the site below which you wish to use it.
  2. Choose Silva Code Source from the add select list.
  3. Fill in id, title, and a script id, then click the Add and Edit button.
  4. Click the Contents tab and add a Python Script or Zope Page Template, giving it the same name as the script id; write your code.
  5. Save the changes.
  6. Test the Code Source via the View tab.

Now the Code Source can be inserted into a document.

If the Code Source needs to have options, for instance to show a title or not, a form can be created that lists the parameters and allows the Author to make a choice. The Edit tab of the Code Source contains a link called edit parameters. With this link a Formulator form can be created and modified. This form displays in the external source toolbox in the document editor.

Of course, one needs to know how to manage forms with Formulator – this is out of scope for this guide, but there is a HowTo available on zope.org.

Using an SQL source

SQL Source icon

The SQL Source enables the results of an SQL query to be presented in a table. Because of the security issues mentioned above, only Zope managers are allowed to create them. Once they exist, Authors can insert an SQL Source into their documents.

Prerequisites

  1. A Zope adapter for a SQL database must be installed.
  2. At least one SQL connection object must be visible/reachable from the Silva root.
  3. The Manager creating the SQL Source has to program the SQL.

Adding an SQL Source

Go to the ZMI and navigate to the Silva root.

Add SQL Source in ZMI select

Fig. 1. ZMI contents area where you can add a Silva SQL Source.

Select Silva SQL Source from the drop-down menu (Fig. 1.).
You will be taken to the Add SQLSource screen (Fig. 2.).

Add SQL Source screen

Fig. 2. Add SQLSource form.

Fill in the id and title.
Press the Add and Edit button.

If the displayed SQL connection isn’t the one you want, choose a different one.
Check if the data encoding is the same as your database delivers .
Enter the sql statement you want to be executed.
Save the changes.
Test the query via the Test tab.

If everything went well, the SQL Source can now be used in documents.
Click the Add an External Source icon in the editor toolbar which will bring you to the  the Add an External Source form
Select the SQL source.
Click the OK button.
Save the document.
Preview the content.

Customizing the SQL Source

In the form that appears the Author can adjust the appearance of the table to be displayed and can add a caption if needed.

The Edit tab of the SQL Source in the ZMI contains a link called edit parameters. With this link a Formulator form can be created and changed, which displays when the SQL Source is referenced from a document. Of course, one needs to know how to manage forms with Formulator – this is out of scope for this guide, but there is a HowTo available on zope.org.

Using a CSV Source

CSV Source icon

CSV Source displays data that is uploaded in a format called "comma separated values". CSV is a plain text format where each line contains a row of a table; the columns within a row are delimited by specific characters, usually commas. The data is presented in a table following the tabular structure.

Something important to note with the CSV Sources is the encoding of the data that will be uploaded. Another difference is that the parameter form displayed when the CSV Source is referenced is not changeable.

Example: When you save your spreadsheet as .csv, Excel uses one of the windows encodings. For most European countries this will be "cp1252". Of course you have to know the name of the encoding before you upload the data.

In the edit view of the CSV Source you can change the encoding after the data was uploaded. If you experience strange visual effects, try another encoding.

In the edit view you also can upload another file, which replaces the actual data, or apply manual changes to the data.

Customizing a CSV Source

CSV Sources behave like normal assets, so you can specify some parameters that are attached to the document. For CSV Sources these parameters consist of the table style to use and the number of rows to show if the table is long and needs batching.