In this project, we will visualize the data from the different databases. For the front-end design, we use HTML, CSS, JavaScript and KnocoutJS for the interface design. And we use Java, RESTful API and Hibernate for the back-end design.

Project page: MultiDBs-Vis

Supervisors:
Al-Khateeb, Shadi (shadikhateeb)
Li, Xiaoting (lxtlouise)

Members:
Akhtar, Omar (TSyndrome)
Cao, Qilu (Luke-pitt)
Cheng, Yu (chengyu6419515)
Tong, Kefei (KieferTowne)
Xi, Peng (rogerxi)
Yang, Zhijie (Miraclekiss)
Zou, Haoda (zenithda13)

Use Cases

In our project, users should be able to create and delete canvases. After creating a canvas, they can add stories to it. Here we connect to keyword search group to provide users with keyword search function to search for their target databases. Then based on the databases or tables they add, they could select certain columns and visualize them with Google Chart.

In order to implement these functions, the work of visualization contains four parts. The first part deals with tables, which stores information about canvases, stories and charts. The second part called DAO is used for connecting databases using Hibernate. The third part is called Manager, which is used for implementing different functions using Hibernate, such as adding canvases. The last part is Java classes, which call different functions to accomplish our goals.

Requirements and Installation

To build our project, you will need to install Maven and git on Linux like server. Here are the commands that you need to type in the terminal at the very first beginning:

1. Install the essentials

First of all, you need to update the apt-get:
sudo apt-get update
sudo apt-get install git
sudo apt-get install maven
sudo apt-get install openjdk-7-jdk
sudo apt-get install nginx
sudo apt-get install docker
sudo apt-get install mysql-server

During the process of installing mysql-server, there would be a pop up window that remind you to set up the intial password. The default username is root, so just pick a password for that.

2. Set up mysql:

Type the following command to login to your mysql:
mysql -uroot -pYourpassword

Here "Yourpassword" is the password that you just set for root.
Then run the following commands:
CREATE USER 'dataverse'@'%' IDENTIFIED BY 'dataverse';
GRANT ALL PRIVILEGES ON *.* TO 'dataverse'@'%' WITH GRANT OPTION;
CREATE DATABASE visualization;
exit


3. Create folders:

Type the following commands to create the project folder:
cd /opt
sudo mkdir project
cd project


4. Clone the repositories:

Type the following commands to clone
sudo git clone https://github.com/infsci2711/MultiDBs-Vis-Server.git
sudo git clone https://github.com/Luke-pitt/ColFusionDockerUtils.git
sudo git clone https://github.com/ColFusion/ColFusionUtils.git
sudo git clone https://github.com/infsci2711/MultiDBs-Utils.git
sudo git clone https://github.com/infsci2711/MultiDBs-Vis-WebClient.git


5. Serve with nginx:

cd /usr/share/nginx
sudo rm -R html
sudo ln -sv /opt/project/MultiDBs-Vis-WebClient html


6. Import the SQL file:

Type the following to import the SQL file:
mysql -udataverse -pdataverse visualization < /opt/project/MultiDBs-Vis-Server/visualization2.sql


7. Change config.properties

Go to /opt/project/MultiDBs-Vis-Server/MultidbsVisServerAPI/src/main/resources/config.properties, and change the IP address with the new one, respectively.

8. Maven install:

Maven install each of the projects:
cd /opt/project/MultiDBs-Utils
sudo mvn install
cd /opt/project/ColFusionUtils
sudo mvn install
cd /opt/project/ColFusionDockerUtils
sudo mvn install
cd /opt/project/MultiDBs-Vis-Server
sudo mvn install


9. Change the restBaseUrl to point to the server IP

Go to /opt/project/MultiDBs-Vis-WebClient/newJs/newDashBoard.js, and change the IP address with the new one, respectively.

10. Run setup.sh:

Type the following for maven install and start the server:
cd /opt/project/MultiDBs-Vis-Server/MultidbsVisServerAPI/target/
sh setup.sh

At this time you are supposed to finish all the installation and start the server already.

11. Stop the server

If you want stop the server, run the following commands:
cd /opt/project/MultiDBs-Vis-Server/
sh stopserver.sh


Work with other MultiDBs projects

The following figure describes how our project work with other MultiDBs projects:

After creating a canvas, it will be stored in our local database. Then we will add story to the canvas, we need to connect to the keyword search, sending then the keyword of our target database, and they will return us the database id, database name, and table name. At this time, the story will also be added to our visualization local database. Then we open the story and starting creating the charts. We send the request for columns to Metastore and they will return us with column names, thus enable us to select the columns that we want to visualize. After we specify the columns, we send a SQL query in JSON format to PrestoDB, and the will send us the data, also in JSON as return. Then the chart will also be stored in our local database and shown on the page.

In our project, we use RESTful API which is identified by URIs. Here is the tutorial website: http://www.restapitutorial.com/

There are mainly three parts of the API services, which are Canvas, Story and chart, and we will describe each of them one by one.

Canvas:

Create new canvas in the database:
• Path: "localhost:7654/Visualization/canvas/new/{userId}/{name}"
• Method: GET
• Input parameter: canvas name
• Output parameter: {"cdate": "2015-04-12T17:54:46.335","mdate": "2015-04-12T17:54:46.335","name": "test1","privilege": 0,"user": {"canvasSet": [],"userId": 1,"userNames": "user1"},"vid": 10}

Find all the canvases created by the user with provided user Id:
• Path: "localhost:7654/Visualization/canvas/user/{userId: [0-9]+}"
• Method: GET
• Input parameter: userId
• Output parameter: [{"cdate":"2015-04-12T17:54:46","mdate":"2015-04-12T17:54:46","name":"test1","privilege":0,"user":{"canvasSet":[],"userId":1,"userNames":"user1"},"vid":10},{"cdate":"2015-04-12T17:56:09","mdate":"2015-04-12T17:56:09","name":"test2","privilege":0,"user":{"canvasSet":[],"userId":1,"userNames":"user1"},"vid":11}]

Delete metadata for the canvas with provided canvas Id:
• Path: "localhost:7654/Visualization/canvas/delete/{vid}"
• Method: GET
• Input parameter: vid
• Output parameter: S(Success)/F(Fail)

Story:

Add new story in the database:
• Path: "localhost:7654/Visualization/story/new/{did}/{dname}/{tname}/{vid}"
• Method: GET
• Input parameter: key word
• Output parameter: [{"canvasVM": {"cdate": "2015-04-12T17:56:09","mdate": "2015-04-12T17:56:09","name": "test2","privilege": 0,"user": {"canvasSet": [],"userId": 1,"userNames": "user1"},"vid": 11},"chartVMSet": [],"did": "1","dname": "car","tname": "test"}]

Show all the stories in a specific canvas:
• Path: "localhost:7654/Visualization/story/showAll/{vid}"
• Method: GET
• Input parameter: vid
• Output parameter: [{"canvasVM":{"cdate":"2015-04-12T17:56:09","mdate":"2015-04-12T17:56:09","name":"test2","privilege":0,"user":{"canvasSet":[],"userId":1,"userNames":"user1"},"vid":11},"chartVMSet":[],"did":"1","dname":"car","tname":"test"}]

Delete metadata for the story with provided story Id:
• Path: "localhost:7654/Visualization/story/delete/{sid}"
• Method: GET
• Input parameter: sid
• Output parameter: S(Success)/F(Fail)

Chart:

Create a new chart with provided canvas Id, chart name and chart type:
• Path: "localhost:7654/Visualization/chart/new/{sid}/{type}/{did}/{dname}/{tname}/{columns}"
• Method: GET
• Input parameter: sid, type, did, dname, tname, columns
• Output parameter: ["cid":1,"columns":"population","did":"1", "dname":"city","storyVM":{"canvasVM":{"cdate":"2015-04-12T17:56:09","mdate":"2015-04-12T17:56:09","name":"test2","privilege":0,"user":{"canvasSet":[],"userId":1,"userNames":"user1"},"vid":11},"chartVMSet":[],"did":"1","dname":"city","tname":"Pittsburgh"},"tname":"Pittsburgh","type":"pie"}]

Show all the charts in a specific story:
• Path: "localhost:7654/Visualization/chart/showAll/{sid}"
• Method: GET
• Input parameter: sid
• Output parameter: [{"cid":1,"columns":"population","did":"1", "dname":"city","storyVM":{"canvasVM":{"cdate":"2015-04-12T17:56:09","mdate":"2015-04-12T17:56:09","name":"test2","privilege":0,"user":{"canvasSet":[],"userId":1,"userNames":"user1"},"vid":11},"chartVMSet":[],"did":"1","dname":"city","tname":"Pittsburgh"},"tname":"Pittsburgh","type":"pie"}]

Delete chart with provided chart Id:
• Path: "localhost:7654/Visualization/chart/delete/{cid}"
• Method: GET
• Input parameter: cid
• Output parameter: S(Success)/F(Fail)