Handover Notes for the Suspension of Development, April 2019

1. Issues
2. Martin
   1. Status of Recent Work
      1. Completed, but not in master
   2. Things I wanted to get done
      1. Making the Installer Handle Updates
      2. Local Caching of Base Maps
      3. Better Database Schema Management
      4. Improving Database Access
      5. The Roadmap
   3. Contacting Me
3. Peter
   1. Current State of the Development
   2. RIF Test system
   3. Database Improvements needed before the Data Loader is built
   4. Front End
   5. Contacting Me

Issues

There are 53 open issues at 17/4/2019. Some of them are mentioned below, but all are things that we could do with fixing, building, or improving.

• Issue #157 Study area database INSERT is slow;
• Issue #156 The tilemaker needs python 2.7 and the installer build needs python 3;
• Issue #155 Statistics Service does not support TLS connections from the rifServices middleware;
• Issue #154 Cannot resize map table (study/comparison area) grids;
• Issue #151 Internet Explorer (IE) caching;
• Issue #150 RIF40_NUM_DENOM view performance;
• Issue #147 Database schema management: consider moving to Liquibase;
• Issue #146 Installer: do updates;
• Issue #145 Installer: consider running the SQL scripts directly;
• Issue #144 Data Extract ZIP file 7: create rerun scripts for Unix-based platforms;
• Issue #137 RIF WAR updates not applied because your browser cache needs to be emptied;
• Issue #134 D3 graphs; todo;
• Issue #133 DM. Data viewer and Mapping tabs. Can't select individual study areas within the overall study area;
• Issue #130 Risk analysis fault/issues (branch: risk-analysis-fixes-2);
• Issue #128 SQL Server SAHSU Database not linked to geography;
• Issue #126 Support linking RIF DB tables to Oracle master DB;
• Issue #125 Add support for region-specific details around point sources;
• Issue #123 Improve search in taxonomies on Investigation Parameters screen;
• Issue #121 Add Prior sensistivity analysis;
• Issue #120 Add model diagnostic tools (in the viewer and exportable as output);
• Issue #117 Add JavaDoc to the documentation ;
• Issue #115 Improve the installation process;
• Issue #113 Refresh logs you off;
• Issue #110 Start RIF Services automatically on Tomcat start;
• Issue #96 Data Extract ZIP file 6: additional items;
• Issue #95 Data Extract ZIP file 5: changing resolution;
• Issue #94 Data Extract ZIP file 4: allow use of more fields;
• Issue #93 Data Extract ZIP file 3: printing;
• Issue #92 Data Extract ZIP file 2: US maps;
• Issue #91 Tilemaker updates;
• Issue #90 Database script updates;
• Issue #89 Local caching of base maps;
• Issue #86 Improve logging;
• Issue #85 Information Governance tool;
• Issue #84 Data loader tool;
• Issue #83 Support of statistical packages;
• Issue #82 Build and support new interfaces;
• Issue #81 Improve the audit trail ;
• Issue #80 Optimise performance on very large datasets;
• Issue #78 Risk Analysis selection at high resolution (e.g. MSOA) does not perform acceptably;
• Issue #77 Error messages with new default basemaps functionality when there is no Internet;
• Issue #76 Study reset sometimes does not reset the stats selection;
• Issue #67 Print state support;
• Issue #66 GeoJSON mouse over support with shapefile shapes;
• Issue #63 Re-factor of Leaflet mapping code;
• Issue #62 studyType mismatch for map: viewermap; study ID: …;
• Issue #61 Add support for risk analysis in map export;
• Issue #57 Front end mapping synchronisation fails sometimes when you change the study ;
• Issue #56 Error loading study from database via middleware generated file;;
• Issue #53 Database access: remove completely from R;
• Issue #46 The Exception handling seems designed to obfuscate rather than reveal;
• Issue #45 Consider changing the database handling such that we get a datasource via JNDI;
• Issue #42 We can include webapp dependencies in Maven;
• Issue #20 Parameter validity should be checked in the front end, or at least reported back to it;

Martin

Status of Recent Work

Everything is up to date. The installer has been tested on Windows and Mac with both databases and works reliably. Multiple covariates work.

Completed, but not in master

There is a branch called installer_list_amend_scripts_in_single_place which is not merged into master. It improves the installer, in that the current version in master has the various alter scripts listed in two places. The new branch will find any such scripts and run them as required, with no need to list them specifically.

We did not have time to test the changes as fully as we would like, so we have left the branch unmerged.

Things I wanted to get done

Making the Installer Handle Updates

The installer creates a brand-new database. It would be good to make it also handle upgrades from one version to the next. See issue #146.

Local Caching of Base Maps

See issue #89. Support for using maps when no internet connection is available.

Better Database Schema Management

See issue #147. At present we maintain two sets of scripts for creating and updating the database schemas – one for each platform. We’d like to move to using a single set of scripts to the extent that is possible, and Liquibase is a useful tool to help with that.

Improving Database Access

I’d like to restructure the code so that the database access was all in classes that are intended for that. At present things are a bit of a mess, with SQL spread through various parts of the Java code.

Ideally, I think, I would move the whole database access to a separate microservice. This isn’t strictly necessary, and it may be splitting things up further than is strictly needed, but it would be a good way to enforce the separation of concerns.

The Roadmap

Not forgetting everything on the RIF Roadmap.

Contacting Me

If you need me to look at anything you can email me at martin@devilgate.org. I’m on Twitter at @devilgate. Also LinkedIn, Facebook.

Peter

For really fine detail about everything that happens, see the older Building and Installing the RIF section.

Current State of the Development

• Risk analysis specific D3 graphs. Done;
• Multiple (used in statistical calculations) and additional (for use outside of the RIF) covariate support. One primary covariate, multiple additional covariates, No support for multiple covariates in the calculation or results (this reduce resource risk). Multiple additional covariates available in the extract. Done;
• Pooled or individual analysis for multiple risk analysis points/shapes (e.g COMARE postcodes). Currently the RIF analyses there in N bands with all the sites as on. It is proposed to extend the RIF to support:
  • Individual site analysis;
  • Pooled analysis (1 or more groups of sites). Groups would be defined from a categorisation variable in the shapefile. Would require changes to:
    • Shapefile load screen and controller;
    • JSON study definition format;
    • Study extract and result tables;
    • R risk analysis code. Front end complete with database changes (11/4/2019). Middleware and R support TODO;

  Pooled or individual analysis for multiple risk analysis points/shapes (e.g COMARE postcodes) also fixes sizing problems study/comparison area map table (to the left of the map). It cannot be re-sized because of a bug in UI-grid this is issue #154

• Oracle interconnect. Support linking RIF DB tables to Oracle master DB; done; documented in Remote Data links.

RIF Test system

• The Statistics Service does not support TLS connections from the rifServices middleware issue #155
• All scripts used on the private network are in C:\RIFDEMO on the RIF server and G:\RIF\WSRIF on the network;
• The RIF40_STARTUP stored procedure had to be edited slightly to run on the private network;
• Two years of HES data have been added (April 2016 to April 2018) using a materialized view (V_HES_201617_APR2019) of a Postgres foreign data wrapper to an Oracle database. Running s HES cancer mapping study at MSOA level for England (the equivalent of a Health Altas map) resulted in the R error: Error: memory exhausted (limit reached?);
• The Population model was extended from 2015 to 2018 using the 2014 population;
• Multiple covariates were enabled;
• Tests showed that the RIF does not check that the numerator and denominator are valid for the study period. No denominator will result in an extract error;
• The Heathrow data was loaded into Oracle as:
  • HEATHROW_COA2011;
  • RIF_TOTAL_CVD;
  • RIF_TOTAL_CHD;
  • RIF_TOTAL_STROKE; It has not been loaded into the RIF.
• The RIF test system is Postgres only because of Issue #128 SQL Server SAHSU Database not linked to geography. SQL Server SAHSU Database not linked to geography. This is a column length issue (i.e. there is a spurious space or two). Postgres is fully functional [PH];

Database Improvements needed before the Data Loader is built

The current scripts for creating a RIF database rely on a core set of scripts, and up to 14 alter scripts. The alter script in particular cause dependency issues when they modify trigger SQL. This needs to be resolved so that the RIF can manage its own data structures and to remove dependency issue using Git version control and dynamic triggers:

• Convert the RIF to use Liquibase to manage the existing database and the data loading. This will require a Java console program to be run as the RIF40 schema owner and version checks between the Front end, middleware and Liquibase change set;
• Move the existing state machine PL/pgSQL and T-SQL code that extracts a study into Java;
• Make the trigger code column name dynamic (requires hstore for Postgres, SQL Server can do it natively)

This is estimated at about 3 months work.

Front End

The use of Angular.js is discussed in RIF Front End.

Contacting Me

If you need me to look at anything you can email me at: phambly@fastmail.co.uk.