Original project including issue tracker found at ThreeFourSeven/Database-Gui.

This project has two parts and follows a client server model:

1. A desktop GUI application for querying, parameter selection (including SVM), machine learning model training and visualizing volumetric data taken from fMRI brain scans of over 4,000 patients (exclusive access). This application was developed over three months with three colleagues using our (Seven's) own pixel graphics engine. _The app is written in Java and works on Windows as well as Mac. The user can send SQL commands to a DB running on a university server.

2. Automation for quick oracle database server provisioning, initializing / migration, data seeding and resetting / reloading. The DB contains thousands of specific brain measurements. Some of the features available are a user login, patient file lookup, and plotting for specific measurements across a specified search domain._

Add some screenshots of the login, graphing, and search options.

• Configure environment variables if non-standard for dev/testing
• Provision local database instances by running docker-compose build
• Initialize and seed the dev/testing databases by running ???
• The app should be good from here to connect.
• Explore the docker image using docker exec -it mridb_oracledb_1 /bin/bash

These instructions will get you a copy of the project up and running on your local machine for development and testing purposes. See deployment for notes on how to deploy the project on a live system.

• Running instance of Oracle DB version 11 (Docker works well for local dev)
• Java version 8 (if running from a jar file)

• Everything under Run Requirements
• git
• Java development kit (jdk) 1.8
• Eclipse (must have the built-in support for git, otherwise use comman)
• ojdbc8.jar — the oracle database interfaces and drivers for Java
• packr-all.jar — Packr release packaging

A step by step series of examples that tell you have to get a development env running

1. Clone the project repo from GitHub

   • In Eclipse go to File > Import...
   • Click Git(folder icon) > Projects from Git then click next
   • Click Clone URI, Next then put in the github clone address (use https)
   • Continue on with default settings, whatever location you want
   • On the wizard selection window use Import existing Eclipse projects

2. Install the external ojdbc8.jar to the library.

   • From the files shared on Martin's OneDrive download any of the folders containing the file ojdbc8.jar.
   • Drag ojdbc8.jar to the lib/ folder in this project.
   • That's it!

3. Setup project environment variables. These are for information we don't want saved as part of the project history such as the database address, username and password. They'll be used by both from sqlloader and the java app. To get started, make a COPY of .env.template file to .env in your project folder. Then fill in the values on the right side. All scripts in the project can read from this file.

On mac just run ./bin/eclipse-mac.sh from the project root.

All the data used is stored with the source code under db/data/. The MRI data derives from these data sets:

• BCH - Boston Children's Hospital (ADHD)
• ABIDE (autism)

Brain MRI image data was preprocessed with 3D Slicer and FreeSurfer. All data was anonymized long before it reached our hands. We've also generated random data using mockaroo.

The schema for the database can be found under db/schema.sql. This is run sqlloader.exe.

Data loader scripts are under db/seed.sql and are just SQL files. The data itself is all under db/data/ in csv format. This is run sqlloader.exe. The resulting logs will be placed in the tmp/ directory of the project, git will ignore the contents of this folder.

Oracle sqlldr cli documentation

Also note that you'll need to define the environment variables for sqlldr to know how to connect to the host ORACLE_HOME, in our case the path to the sqlldr and slqplus executables, and TNS_ADMIN, the folder where tnsnames.ora is found. Make sure you have a tnsnames.ora file and that it contains a definition for csci275.

From Windows command prompt:

setx ORACLE_HOME "D:\example\path\instant_client"
setx TNS_ADMIN %ORACLE_HOME%

Example using environment variables to run sqlloader from project root folder:

On bash prompt (unix, mac, linux), although sqldr doesn't exist on this platform:

cd path/to/project/Database-Gui

export SQLLDR_PATH=/usr/bin/sqlloader
export DB_USERNAME=jsdb
export DB_PASSWORD=mypassword

$SQLLDR_PATH userid=$DB_USERNAME/$DB_PASSWORD control=db/seed.sql \
             log=tmp/sqlldr.log bad=tmp/sqlldr-bad.log

On Windows command prompt (use setx instead to permanently set variable):

cd path\to\project\Database-Gui

set SQLLDR_PATH="C:\\Users\Darwin\path\to\sqlldr.exe"
set DB_USERNAME=jsdb
set DB_PASSWORD=mypassword

%SQLLDR_PATH% userid=%DB_USERNAME%/%DB_PASSWORD%@csci275 control=db\seed.sql log=tmp\sqlldr.log bad=tmp\sqlldr-bad.log

Stands for object relational mapping, is the approach we take to mapping relations in our database to objects in our application. Given that this is a simple project, we will be using the popular Active Record design pattern to model this layer of the application.

From the description of an active record object in the above link:

An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.

The source code for all the classes of active record objects can be found under the src/models/ folder and each one is named with the suffix "Model".

It is worth noting that our application does not resemble a CRUD (create, read, update, delete) app. We are mostly reading from the database and do very little creating, updating and deleting of records. This is the nature of our problem as a research database for running statistics.

The testing framework used is Junit5.

The documentation is available HERE. For more instructions with eclipse see this WashingtonU page: Unit Testing in Eclipse Using JUnit.

To run all the tests in Eclipse select or highlight the test/ folder in the package explorer and then click the green Run button in the toolbar at the top of the window. To run tests for a single class or just a subfolder of the tests, just have that specific file or subfolder selected when you click the run button.

The tests are in a parallel folder to src/, the test/ folder. The classes written here mirror those written in src and each has the same name suffixed by "Test". For example, the PatientModel class would have a PatientModelTest class.

There are no end to end tests.

To create an executable jar file in eclipse go to File > Export > Java > Runnable Jar File. Then click next then finish.

To run you must have a script to load the environment then run the following:

java -jar JSDB.jar

• Oracle-to-Java Database API - ojdbc8.jar
• sqlplus and sqlldr (Oracle)
• JUnit5 testing framework/library
• Mockaroo - to generate fake data

We use SemVer for versioning. For the versions available, see the tags on this repository.

• Seven Randall - Created rendering engine and developed app GUI - ThreeFourSeven
• Mike Kennedy - Oracle SQL expert and data wrangling - mikekenn
• Darwin Groskleg - Project architect and ORM design - darwingr
• Cynthia Forgeron - Problem domain expert (brain MRI researcher) - cynthiaforgeron

See also the list of contributors who participated in this project.

This project is licensed under the MIT License - see the LICENSE file for details.

• Dr. Martin Van Bommel - How to Database (StFX - CSCI 275)
• Dr. Jacob Levman