OECD table delivery functions
(This version of the documentation is for the MySQL database version – but generates links for the filemaker pro server 11 version. Description should be updated when mysql database goes public)
The procedure for delivery of datalink table to OECD consists of two functions:
- 1: Generating a new table of datalinks
- 2: Downloading the table as a csv file
The downloaded table is then mailed to OECD using you own normal mailsystem.
Re. 1 Generating new table:
This is implemented in the database controls as tablecontrol group 16 “Generate OECD datalinks table”. (“Tablecontrol” is what the user can choose from in the operations dropdown menu). The database operations uses an extra database table, spinoecd, that is there for this purpose only. The extra table is used to be able to add two columns with static information as required in the OECD delivery description: Database=SPIN and Operation=ADD. These two columns are defined in the table definition with initial values making the database itself doing the proper initialization.
This tablecontrol has two database operations, each defined in the tabletreat table:
- DOSQL function in table spinoecd: TRUNCATE TABLE spinoecd;INSERT INTO spinoecd(cas_no, chemical_name) SELECT cas_no, preferredname FROM spinstof;
- WRAPPER function in table spinoecd: {“sourcefield”:”cas-no”,”targetfield”:”smileshtml”,”pretext”:”<a href=http://www.spin2000.net/spinmyphp/?pid=”,”subtext”:”</a>”}
Re. 2 Downloding datalinks table
This is implemented using the general table download function with the wordpress shortcode for table download:
is_downtable table=”spinoecd” sql=”SELECT db as `DATABASE`, cas_no as CAS_NO, other_no as OTHER_NO, no_type as NO_TYPE, chemical_name as CHEMICAL_NAME, name_type as NAME_TYPE, synonyms as SYNONYMS, synonym_name_type as SYNONYM_NAME_TYPE, smiles as SMILES, doc_title as DOC_TITLE, url as URL, operation as OPERATION from spinoecd” text=”SpinOECD”
General overview
The new SPIN system is developed in such a way, that it should be possible to install it on a variety of hosts. There’s more information on this in the article on hosting.
The system itself is utilizing well known and widespread technologies to enhance the possibilities for installation and maintenance: The General Content and the SPIN Guide is deployed as a WordPress site and the same goes for this system management information site, i.e. the total system uses two installations of WordPress. These might be on the same physical server or at the same hosting provider or they might be on different hosts. The database control functions, the database userinterface and the database graphical interface are programmed as separate systems in PHP. All of these systems should of course be accessing the same database, but the database can be a Microsoft SQLserver or a MySQL database, just by changing the database configuration parameters.
Hosting
The SPIN system can be hosted on an internal server, on a VPS (Virtual Private Server) at some server hosting facility or even at a shared hosting facility like common web-hosting providers. Or a combination of this. The host can be running Windows or Linux and it must provide a web-server cappable of using PHP and a MS SQLserver og MySQL database.
The development version is running on an internal Windows VM (Virtual Machine) installed on Microsoft Hyper-V, and thus the full server with all programs, configurations and data can be moved to another Hyper-V server or to Microsoft Azure VPS with very little effort: Just by copying the .vhdx file and starting it on another hyper-v server.
But the system as such is even more flexible and portable than this: The only requirement for installation is a webserver and either a MS SQLserver og MySQL database server. This means that SPIN can be installed on any web hosting server, that allows for a database of this size. Services like this is available at a very low yearly subscription rate. Some of the cheaper solutions should not be expected to be able to support all functions)
For evaluation (until end of this development fase) try the following domain names:
www.spin2000.eu (present site, a VPS server at DigitalOcean in Amsterdam)
www.spin2000.dk (development site in a VirtualMachine at InfoShare, using MSSQL database, hosting hardware of low to medium capacity)
www.spin2020.com (cheap shared web hosting at one.com – allows for the user interface to work, but limitations at the hosting site makes the database operations and control non-working – however data could be updated by hosting site backend (phpmyadmin) database access)
Summary of deployment scenarios:
- Dedicated hardware with Windows operating system
- Dedicated hardware with Linux operating system
- Virtual Machine running Linux or Windows on internal hypervisor (Windows or VMWare)
- Virtual Private Server (Linux or Windows) eunning on a hosting center offering full server hosting
- Web-host (with MySQL or MS SQLserver) running in a shared environment at a web hotel.
Using Word for Guide maintenance
The SPIN Guide consists of WordPress posts. These Posts can be very easily published just by using Microsoft Word and the build in function for publishing directely to a WordPress site. The setup for this is a one time only activity (download guide here) and it requires a username and password to the system. Following the setup you can publish articles to any of the defined categories in the Guide:
- User manual
- General info
- Codes
- Tutorials
- Links
- Contact
The guide is organized with a drop down menu for each of these categories and the different articles are shown in the menu by their title in alphabetical order. Hence each title should normally start with a number for easier management of the article order inside the menu.
Updating with WordPress backend
The general content of the SPIN site is managed by the CMS WordPress which allows you to publish information in the form of Pages and Posts. Using the WordPress backend you can add and change content of any page or post. Access to the backend requires a username and password and you find the login form by adding /wp-admin to the the SPIN URL address.
WordPress is a very widely used CMS for web publishing and you can find numerous guides on how to use the backend on the web, including Youtube videos. You can download the SPIN guide to the WordPress backend here.
Updating with Microsoft Word
The general content of the SPIN site is managed by the CMS WordPress which allows you to publish information in the form of Pages and Posts. Your Posts (but not Pages) can be very easily published just by using Microsoft Word and the build in function for publishing directely to a WordPress site. The setup for this is a one time only activity (download guide here) and it requires a username and password to the system. Following the setup you can publish articles to any of the defined categories on the site. For now only the category News is part of the general content, but more could be added.
New Graphics – MSSQL
The new graphical datadisplay utilizing MS SQLserver database can be tested in a stand-alone version here: http://spin2000.dk/spin-graphics/.
The stand-alone version includes a header section for entering the casnumber that you want to use for showing use data. This header section will not be shown, when the display is addressed with the casnumber in the URL.
Installation from scratch
As installation from scratch isn’t expected to take place very often, it isn’t made as a single installation script. But it is rather simple: Alle you need is a webserver including a database server, ftp access to this server and the software files in the SPIN installation packet here:
- Install WordPress on the hosting server as per their instructions – many hosting providers have a one-click installer for this.
- Repeat this for WordPress installation number two (the /sysman site).
- Upload the SPIN software packets:
- SPIN database programs (unpack to the website root folder)
- InfoShare special WP plugin for SPIN statistics (unpack to /wp-content/plugins/)
- WordPress media files (unpack to the website root folder)
- ‘Show database’ plugin (unpack to both sites: /wp-content/plugins/ and /sysman/wp-content/plugins/)
- Clone the existing WordPress sites to the new location (either by downloading a copy or by using a backup copy. WordPress cloning programs will copy all templates and data and general plugins)
- Create a MySQL user and edit the following files to match the user and password:
- /wp-config.php
- /sysman/wp-config.php
- /spinmy/class/config.php
- /spinmy-graphics/class/config.php
- /spinmyphp/class/config.php
- /spinmyreports/class/config.php
- /spinreports/class/config.php
- Create the SPIN database and upload data by using a database copy or by using the SPIN database upload functions.
- If running Ubuntu or another Debian-based distribution, disable AppArmor for MySQL: https://askubuntu.com/questions/1144497/how-to-disable-apparmor-for-mysql
- You’re good to go.
Managing database controls
The data manipulation functions at spin2000.net/spinmy/ in the SPIN data control panel are made by generic programming and the specifications are held in database tables and thus makes it possible to add or change the functions when need arises.
Four general functions are available:
- Add or remove “-” from any field
- Use static data and the content of a specific field to generate content in another field
- Control the content of any field for existence in another table and generate resulting files (csv) with missing values.
- Execute some sql select statement and download the found set: This is used for generating the tables of data delivery data outside +/- 20 pct of the average data for all years.
The controls are listed in the database table tablecontrol and the corresponding specifications are found in the table tabletreat.
See the full documentation on each function in separate articles.
Yearly common data adjustments
After the delivery of data from the participating countries there must be done some common housekeeping to get the last data in place. This is done at the database data control panel
at http://spin2000.net/spinmy/main.php
This control panel is password protected. Use admin / admin to login and the choose the menu item Controls in the top line.
You can find all the database controls that you need in the list of controls. Most of these are straight forward and the more special controls are further described in articles on this site.
Yearly national data delivery
The yearly update from each national participant is mainly done by simply uploading some csv files with the new usedata. The upload is initiated from the upload operations panel
at http://spin2000.net/spinmy/main.php
This control panel is password protected. Contact admin(at)infoshare(dot)dk to get login information, if you cannot find them. The database operations panel gives you some definition options for your upload: You can choose any of the SPIN database tables for data maintenance – choose from the dropdown list in the field “Table”. Next choose the type of operation, you want to perform – for yearly delivery you would have a csv file with new data for the year/country combintion and you will chose Add:
- Replace: Replaces the entire database table with the content uploaded by you.
- Add: Add the linies in your csv file to the content of the database table
- Update: Updates the existing data by adding your uploaded content to the existing records.
- Restore: If for some reason you want to reverse to the data as they were before your upload.
The fast track to do an yearly update is to verify, that the column headings in your csv file is spelled the same way as it is shown on the screen when you select the table – including upper/lowercase – and the put a tick mark in the “Enable custom fields”. Remember this site is using English and expecting the decimalpoint to me a . (full stop) not a comma.
Generally you have two options for defining the contents of your csv file to the upload system: You can select the fields you wish to upload from the list of fields in the box Field Name – your csv file must contain the selected fields in the same order as shown in the box. Or you can “Enable custom fields” by the tickmark: In this case the upload system will take the first line of your csv file and interpret the content here as the names of fields. You must use same spelling as shown in the box “Field name”.
The File input button “Gennemse..” (Text is dependent on the language of your computer) is for finding the csv file on your computer.
Csv files can have slightly different syntax: If the separator between your field values is anything else than ; (semicolon) – typical , (comma) – you can enter this in the Separator field and if your text values are enclosed with a special character, typical ” (double quotation marks), you can enter this in the field Enclose value. Decimal point is always assumed to be . (full stop).
Finally, when you have everything in place, you click on submit. For large datafiles the upload can take some time. Your upload will start by making a backup copy of the database table for later restore if necessary.
If your yearly update also include an update of the substance names, you can do this exactly the same way. But, if for some reason your csv file contains both new names and existing names, this procedure will fail. You have to do it slightly different: Upload your names csv file to the table temp_spinname using the Replace function and then change to Database Controls and activate the controlfunction Copy values from temp_spinname to spinname replacing duplicates. This will both add new names of the same nametype and update the spelling of existing names.
Adding new substances: If you would like to be able to find new substances right away, you should also add the new substances to the spinstof table. You can do this by uploading a csv file with the new substances, but a faster shortcut to this is to choose the database control Add new spinstof from spinname.
And the you are all done. (Except for the yearly special updates, that isn’t done by each country).
Updating Preferred Name
Newly added substances will lack a preferred name. The preferred name should be found in the general list of names and added to the substance description. The preferred name will be the first found name in this type-priority: CAS, COM, PRD, PRN, FIN
The function for updating preferred names is part of the database operations her spin2000.eu/spinmy/main.php
Updating code tables
Every codetable in the SPIN system can be updated with new values by anyone with the username / password protected access.
The update is initiated from the datacontrol panel at http://spin2000.eu/spinmy/main.php
Find missing CAS-names
Newly added substances will lack a name of type CAS. These added substances should be found and the casnumber list should be used by the casname partner to produce a list of supplemental casnames.
The database can tell us which substances need a casname in two ways: Either the simple way by finding all substances that doesn’t have a preferred name of type CAS: Download that list here.
Or somewhat more complicated by finding all substances in use-total and comparing this list with the list of cas-names in the names table. Download that list here.
This file can be sent to one or more partners for delivering ekstra names of CAS type and then the extra names can be imported in the database operations at spin2000.net/spinmy/main.php
Checking for Orphan Codes
If your uploaded data uses codes, that are not found in the codetables, you need to update the code tables. You have a database control, that can help to identify this problem: By running the “Finding orphans” database control, you get a csv file containing the records, that uses a code (or a PID), that’s not found in the code table (or the substance registry). Database control functions are here: spin2000.eu/spinmy/main.php
Updating number syntax
The SPIN substances are registered with CAS numbers, EC numbers and Index numbers. Each of these numbers are registered in the database with and without the format enhancing “-“. If the upload only contained the number in one of these formats, you have three database controls for calculating the other version. Each databse control will add any missing version of the number from the other, i.e. if the CAS number was uploaded in the format 50-00-0 and the format (PID) 50000 is missing, then this control will calculate and register it. And vice versa.
If you need to do a similar change in an Excel spreadsheet, you can remove any “-” by selecting the column and using “replace” to substitute “-” with “” (nothing).
If you need to add “-” to a PID in cell A2 in order to show the PID formatted as a cas-number you can use this formula:
=VENSTRE(A2;LÆNGDE(A2)-3)&”-“&MIDT(A2;LÆNGDE(A2)-2;2)&”-“&HØJRE(A2;1) (In a danish version of excel)
=left(A2;length(A2)-3)&”-“&middle(A2;length(A2)-2;2)&”-“&right(A2;1) (english version of Excel)
Link table for OECD
Yearly updates should be finalized with generating and delivery of a link table to OECD.
The last generated link table can be downloaded here:
SpinOECD
Procedure:
- Make sure all new substances are added to SPIN
- Update the OECD link table using the two database controls at http://spin2000.eu/spinmy (1: Update OECD table with new substances, 2: Generate link definitions for all substances)
- Download the csv table from the link above.
The downloaded csv table should be sent to: Valerie Frison with email: Valerie.FRISON@oecd.org
OECD has a general delivery document which includes a description of the SPIN database. It might be relevant to consider if earlier sent description needs to be updated. The OECD delivery document template can be found here: datatemplate-20160422
