<repo_name>Caisorda/ADVANDB-MC02<file_sep>/Converted-Queries.sql
/*#1: basic; Total volume of fish caught*/
select total_aquani_volume as volume
from cube
where hpq_hh_id is null and hpq_aquani_id is null and hpq_aquaequip_id is null;

/*#1CONVERTED*/
SELECT SUM(aquani_vol)
FROM hpq_aquani a
INNER JOIN hpq_hh h
ON (h.`main.id` = a.`main.id`)
WHERE aquanitype_o is not null;


/*#2: How much fish was caught in '24'*/
select total_aquani_volume as volume, hpq_hh.mun as municipality
from cube
inner join hpq_hh on (cube.hpq_hh_id = hpq_hh.id) 
where hpq_aquani_id is null and hpq_aquaequip_id is null and hpq_hh.mun = 24
group by hpq_hh.mun;

/*#2 CONVERTED*/
SELECT aquani_vol, h.mun
FROM hpq_aquani a
INNER JOIN hpq_hh h
ON (h.`main.id` = a.`main.id`)
WHERE aquanitype_o is not null and h.mun = 24
GROUP BY h.mun;


/*#3: How much carp has been caught with bagnets*/
select total_aquani_volume as volume, hpq_aquani.aquani_type as aquani_type, hpq_aquaequip.equip_type as equipment_type
from cube
inner join hpq_aquani on (cube.hpq_aquani_id = hpq_aquani.id) 
inner join hpq_aquaequip on (cube.hpq_aquaequip_id = hpq_aquaequip.id) 
where hpq_hh_id is null and hpq_aquani.id = 5 and hpq_aquaequip.id = 3
group by hpq_aquani.aquani_type, hpq_aquaequip.equip_type;

/*#3 CONVERTED*/
SELECT aquanitype, aquaequiptype, SUM(aquani_vol)
FROM hpq_aquani as a
INNER JOIN hpq_aquaequip e ON (e.`main.id` = a.`main.id`)
WHERE aquanitype = 5 and aquaequiptype = 3 and aquaequiptype_o is not null;


/*#4: How much fish was caught with fish nets in each municipality*/
select total_aquani_volume as volume, hpq_aquaequip.equip_type as equipment_type, hpq_hh.mun as municipality
from cube
inner join hpq_aquaequip on (cube.hpq_aquaequip_id = hpq_aquaequip.id) 
inner join hpq_hh on (cube.hpq_hh_id = hpq_hh.id) 
where hpq_aquani_id is null and hpq_aquaequip.id = 1
group by hpq_aquaequip.equip_type, hpq_hh.mun;

/* #4 CONVERTED*/
SELECT mun, aquaequiptype, SUM(aquani_vol)
FROM hpq_aquaequip as e
INNER JOIN hpq_hh as h ON (e.`main.id` = h.`main.id`)
INNER JOIN hpq_aquani as a ON (e.`main.id` = a.`main.id`)
WHERE e.aquaequiptype = 1 and a.aquanitype is not null
GROUP BY h.mun;


/*#5: How much catfish was caught in '5' with hooks and line*/
select total_aquani_volume as volume, hpq_hh.mun as municipality, hpq_aquani.aquani_type as aquani_type, hpq_aquaequip.equip_type as equipment_type
from cube
inner join hpq_hh on (cube.hpq_hh_id = hpq_hh.id) 
inner join hpq_aquani on (cube.hpq_aquani_id = hpq_aquani.id) 
inner join hpq_aquaequip on (cube.hpq_aquaequip_id = hpq_aquaequip.id) 
where hpq_aquani.id = 3 and hpq_aquaequip.id = 6 and hpq_hh.mun = 5
group by hpq_hh.mun, hpq_aquani.aquani_type, hpq_aquaequip.equip_type;

/*#5 CONVERTED*/
SELECT mun, aquanitype, aquaequiptype, aquani_vol
FROM hpq_aquaequip as e
INNER JOIN hpq_aquani as a ON (e.`main.id` = a.`main.id`)
INNER JOIN hpq_hh as h ON (e.`main.id` = h.`main.id`)
WHERE a.aquanitype = 3 and e.aquaequiptype = 6 and h.mun = 5
GROUP BY h.mun, a.aquanitype, e.aquaequiptype
<file_sep>/Cube-table.sql
create table Cube as

select hpq_hh_id, hpq_aquani_id, hpq_aquaequip_id, sum(aquani_vol) AS total_aquani_volume
from fact_table
group by hpq_hh_id, hpq_aquani_id, hpq_aquaequip_id with rollup

union

select hpq_hh_id, hpq_aquani_id, hpq_aquaequip_id, sum(aquani_vol) AS total_aquani_volume
from fact_table
group by  hpq_aquani_id, hpq_aquaequip_id, hpq_hh_id with rollup

union

select hpq_hh_id, hpq_aquani_id, hpq_aquaequip_id, sum(aquani_vol) AS total_aquani_volume
from fact_table
group by  hpq_aquaequip_id, hpq_hh_id, hpq_aquani_id with rollup<file_sep>/Database/ETL query.sql
/* CREATE FACT TABLE in db_hpq schema*/
USE db_hpq;
CREATE TABLE IF NOT EXISTS fact_table AS
SELECT hh.id AS hpq_hh_id, a.aquanitype AS hpq_aquani_id, IFNULL(aq.aquaequiptype,9) AS hpq_aquaequip_id, a.aquani_vol
FROM (
	SELECT hpq_hh_id, aquanitype, sum(aquani_vol) as aquani_vol
    FROM db_hpq.hpq_aquani
    where aquanitype is not null
    group by hpq_hh_id
) as a
INNER JOIN db_hpq.hpq_hh hh
ON(a.hpq_hh_id = hh.id)
LEFT JOIN db_hpq.hpq_aquaequip aq
ON(a.hpq_hh_id = aq.hpq_hh_id)

/* CREATE STAR SCHEMA */
CREATE SCHEMA IF NOT EXISTS star_schema;
USE star_schema;

/* COPY fact_table */
CREATE TABLE IF NOT EXISTS fact_table AS
SELECT *
FROM db_hpq.fact_table;

/* TRANSFORM DIMENTION TABLES */
CREATE TABLE IF NOT EXISTS hpq_hh AS
SELECT hh.id, hh.brgy, hh.mun
FROM db_hpq.hpq_hh hh
INNER JOIN db_hpq.hpq_aquani a
ON(a.hpq_hh_id = hh.id)
WHERE a.aquanitype IS NOT NULL
GROUP BY a.hpq_hh_id

CREATE TABLE IF NOT EXISTS hpq_aquani AS
SELECT distinct a.aquanitype as id, (CASE WHEN a.aquanitype=1 THEN 'TILAPIA'
                                             WHEN a.aquanitype=2 THEN 'MILKFISH'
                                             WHEN a.aquanitype=3 THEN 'CATFISH'
                                             WHEN a.aquanitype=4 THEN 'MUDFISH'
											 WHEN a.aquanitype=5 THEN 'CARP'
                                             WHEN a.aquanitype=6 THEN 'OTHER'
       					                END) aquani_type
FROM db_hpq.hpq_aquani a;

CREATE TABLE IF NOT EXISTS hpq_aquaequip AS
SELECT distinct a.aquaequiptype as id, (CASE WHEN a.aquaequiptype=1 THEN 'FISH NET'
                                             WHEN a.aquaequiptype=2 THEN 'ELECTRICITY'
                                             WHEN a.aquaequiptype=3 THEN 'BAGNETS'
                                             WHEN a.aquaequiptype=4 THEN 'GRILLNETS'
											 WHEN a.aquaequiptype=5 THEN 'TRAPS'
                                             WHEN a.aquaequiptype=6 THEN 'HOOKS AND LINE'
                                             WHEN a.aquaequiptype=7 THEN 'SIFT NET'
                                             WHEN a.aquaequiptype=8 THEN 'OTHER'
											 WHEN a.aquaequiptype=9 THEN 'NONE'
       					                END) equip_type
FROM db_hpq.hpq_aquaequip a;<file_sep>/src/view/DefaultDAO.java
package view;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DefaultDAO {

	public ResultSet executeQuery(String query){
		ResultSet rs = null;
		try{
			PreparedStatement pstmt;
			try(Connection conn = DBConnector.getConnection()){
				pstmt = conn.prepareStatement(query);

//				long start = System.currentTimeMillis();
	            rs = pstmt.executeQuery();
//	            long end = System.currentTimeMillis();
//				System.out.println(1.0*(end - start)/1000);
//				System.out.println();
			}
			pstmt.close();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
//		System.out.println(town.size());
		return rs;
	}
	
}
<file_sep>/MCO2_ETL.sql
/* CREATE FACT TABLE in db_hpq schema*/
USE db_hpq;
CREATE TABLE IF NOT EXISTS fact_table AS
SELECT hh.id AS hpq_hh_id, a.aquanitype AS hpq_aquani_id, aq.aquaequiptype AS hpq_aquaequip_id, a.aquani_vol
FROM hpq_hh hh, hpq_aquani a, hpq_aquaequip aq
WHERE hh.id = a.hpq_hh_id AND hh.id = aq.hpq_hh_id;

/* CREATE STAR SCHEMA */
CREATE SCHEMA IF NOT EXISTS star_schema;
USE star_schema;

/* COPY fact_table */
CREATE TABLE IF NOT EXISTS fact_table AS
SELECT *
FROM db_hpq.fact_table;

/* TRANSFORM DIMENTION TABLES */
CREATE TABLE IF NOT EXISTS hpq_hh AS
SELECT hh.id, hh.brgy, hh.mun
FROM db_hpq.hpq_hh hh;

CREATE TABLE IF NOT EXISTS hpq_aquani AS
SELECT distinct a.aquanitype as id, (CASE WHEN a.aquanitype=1 THEN 'TILAPIA'
                                             WHEN a.aquanitype=2 THEN 'MILKFISH'
                                             WHEN a.aquanitype=3 THEN 'CATFISH'
                                             WHEN a.aquanitype=4 THEN 'MUDFISH'
											 WHEN a.aquanitype=5 THEN 'CARP'
                                             WHEN a.aquanitype=6 THEN 'OTHER'
       					                END) aquani_type
FROM db_hpq.hpq_aquani a;

CREATE TABLE IF NOT EXISTS hpq_aquaequip AS
SELECT distinct a.aquaequiptype as id, (CASE WHEN a.aquaequiptype=1 THEN 'FISH NET'
                                             WHEN a.aquaequiptype=2 THEN 'ELECTRICITY'
                                             WHEN a.aquaequiptype=3 THEN 'BAGNETS'
                                             WHEN a.aquaequiptype=4 THEN 'GRILLNETS'
											 WHEN a.aquaequiptype=5 THEN 'TRAPS'
                                             WHEN a.aquaequiptype=6 THEN 'HOOKS AND LINE'
                                             WHEN a.aquaequiptype=7 THEN 'SIFT NET'
                                             WHEN a.aquaequiptype=8 THEN 'OTHER'
       					                END) equip_type
FROM db_hpq.hpq_aquaequip a;<file_sep>/src/view/QueryBuilder.java
package view;

import java.util.ArrayList;
import java.util.HashMap;

public class QueryBuilder {
	private String mainTable;
	private ArrayList<String> grouping, from, select;
	private HashMap<String, String> where;
	
	public QueryBuilder(){
		this.select = new ArrayList();
		this.from = new ArrayList();
		this.where = new HashMap();
		this.grouping = new ArrayList();
	}
	
	public String build(){
		String query = "";
		for(int i=0; i<select.size(); i++){
			if(i == 0){
				query = query + "select " + select.get(i);
			}else query = query + ", " + select.get(i);
		}
		for(int i=0; i<from.size(); i++){
			if(i == 0){
				query = query + "\nfrom " + from.get(i);
				this.mainTable = from.get(i);
			}else query = query + "\ninner join " + from.get(i) 
							+ " on (" + mainTable + "." + from.get(i) + "_id = " + from.get(i) +".id) ";
		}
		for(String key: where.keySet()){
			if(query.contains("where")){
				query = query + " and " + where.get(key);
			}else{
				query = query + "\nwhere " + where.get(key);
			}
		}
		for(int i=0; i<grouping.size(); i++){
			if(i==0){
				query = query + "\ngroup by " + grouping.get(i);
			}else{
				query = query + ", " + grouping.get(i);
			}
		}
		return query;
	}
	
	public QueryBuilder addSelect(String variable){
		if(!this.select.contains(variable))
			this.select.add(variable);
		return this;
	}
	
	public QueryBuilder removeSelect(String variable){
		if(this.select.contains(variable))
			this.select.remove(variable);
		return this;
	}
	
	public QueryBuilder addFrom(String table){
		if(!this.from.contains(table))
			this.from.add(table);
		return this;
	}
	
	public QueryBuilder removeFrom(String table){
		if(this.from.contains(table))
			this.from.remove(table);
		return this;
	}
	
	public QueryBuilder addCondition(String field, String value){
		String condition = field;
//		
		if(value.equals("null")){
			condition = condition + " is null";
		}else{
			condition = condition + " = " + value;
		}
		
		if(!where.containsKey(field))
			where.put(field, condition);
		else where.replace(field, condition);
		return this;
	}
	
	public QueryBuilder removeCondition(String field){
		if(where.containsKey(field))
			where.remove(field);
		return this;
	}
	
	public QueryBuilder addGrouping(String field){
		if(!this.grouping.contains(field))
			this.grouping.add(field);
		return this;
	}
	
	public QueryBuilder removeGrouping(String field){
		if(this.grouping.contains(field))
			this.grouping.remove(field);
		for(String group: this.grouping){
			System.out.println(group + " " + field);
		}
		return this;
	}
}
<file_sep>/src/view/ResultFrame.java
package view;

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.GroupLayout;
import javax.swing.GroupLayout.Alignment;
import javax.swing.JTextField;
import javax.swing.JLabel;

public class ResultFrame extends JFrame {

	private JPanel contentPane;
	private JTable table;
	private DefaultTableModel model;
	private JScrollPane scrollPane;
	private JTextField timeTextField;
	/**
	 * Launch the application.
	 */
//	public static void main(String[] args) {
//		EventQueue.invokeLater(new Runnable() {
//			public void run() {
//				try {
//					ResultFrame frame = new ResultFrame("", new ArrayList());
//					frame.setVisible(true);
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//			}
//		});
//	}

	/**
	 * Create the frame.
	 */
	public ResultFrame(String query, ArrayList<String> labels) {
		try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(ResultFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(ResultFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(ResultFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(ResultFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
		
		
		setTitle("Query Result");
//		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 350, 340);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		
		scrollPane = new JScrollPane();
		
		table = new JTable();
		this.model = new DefaultTableModel() {

			public boolean isFocusable(int rowIndex, int mColIndex) {
				return false;
			}

			public boolean isCellSelectable(int rowIndex, int mColIndex) {
				return false;
			}
		};
		this.model.setColumnIdentifiers(labels.toArray());
        this.table.setModel(model);
		scrollPane.setViewportView(table);
		
		timeTextField = new JTextField();
		timeTextField.setColumns(10);
		
		JLabel lblExecutionTime = new JLabel("Execution Time");
		GroupLayout gl_contentPane = new GroupLayout(contentPane);
		gl_contentPane.setHorizontalGroup(
			gl_contentPane.createParallelGroup(Alignment.LEADING)
				.addGroup(gl_contentPane.createSequentialGroup()
					.addGroup(gl_contentPane.createParallelGroup(Alignment.LEADING)
						.addGroup(gl_contentPane.createSequentialGroup()
							.addComponent(lblExecutionTime)
							.addGap(18)
							.addComponent(timeTextField, GroupLayout.PREFERRED_SIZE, 156, GroupLayout.PREFERRED_SIZE))
						.addComponent(scrollPane, GroupLayout.PREFERRED_SIZE, 324, GroupLayout.PREFERRED_SIZE))
					.addContainerGap(GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
		);
		gl_contentPane.setVerticalGroup(
			gl_contentPane.createParallelGroup(Alignment.LEADING)
				.addGroup(gl_contentPane.createSequentialGroup()
					.addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
						.addComponent(timeTextField, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
						.addComponent(lblExecutionTime))
					.addGap(5)
					.addComponent(scrollPane, GroupLayout.DEFAULT_SIZE, 266, Short.MAX_VALUE))
		);
		contentPane.setLayout(gl_contentPane);
		uploadResults(query,labels);
	}

	public void uploadResults(String query, ArrayList<String> labels){
		try {
			PreparedStatement pstmt;
			try(Connection conn = DBConnector.getConnection()){
				pstmt = conn.prepareStatement(query);

				long start = System.currentTimeMillis();
	            ResultSet result = pstmt.executeQuery();
	            long end = System.currentTimeMillis();
				String time = "" + (1.0*(end - start)/1000);
				timeTextField.setText(time + "s");
//				System.out.println();
				while (result.next()) {
					model.setRowCount(model.getRowCount() + 1);
					for (int i = 0; i < labels.size(); i++){
						model.setValueAt(result.getObject(labels.get(i)), model.getRowCount() - 1, i);
					}
				}
			}
			pstmt.close();
			scrollPane.repaint();
			scrollPane.revalidate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
