Archives for Documentation

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:

1. DOSQL function in table spinoecd: TRUNCATE TABLE spinoecd;INSERT INTO spinoecd(cas_no, chemical_name) SELECT cas_no, preferredname FROM spinstof;
2. 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.

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:

1. Install WordPress on the hosting server as per their instructions – many hosting providers have a one-click installer for this.
2. Repeat this for WordPress installation number two (the /sysman site).
3.  Upload the SPIN software packets:
   1.  SPIN database programs (unpack to the website root folder)
   2.  InfoShare special WP plugin for SPIN statistics (unpack to /wp-content/plugins/)
   3.  WordPress media files (unpack to the website root folder)
   4. ‘Show database’ plugin (unpack to both sites: /wp-content/plugins/ and /sysman/wp-content/plugins/)
4. 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)
5. Create a MySQL user and edit the following files to match the user and password:
   1. /wp-config.php
   2. /sysman/wp-config.php
   3. /spinmy/class/config.php
   4. /spinmy-graphics/class/config.php
   5. /spinmyphp/class/config.php
   6. /spinmyreports/class/config.php
   7. /spinreports/class/config.php
6. Create the SPIN database and upload data by using a database copy or by using the SPIN database upload functions.
7. If running Ubuntu or another Debian-based distribution, disable AppArmor for MySQL: https://askubuntu.com/questions/1144497/how-to-disable-apparmor-for-mysql
8. 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:

1. Add or remove “-” from any field
2. Use static data and the content of a specific field to generate content in another field
3. Control the content of any field for existence in another table and generate resulting files (csv) with missing values.
4.  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.