DBC.java

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;

import org.json.JSONArray;
import org.json.JSONException;

/**
 *
 * @author sqlitetutorial.net
 */
public class DBC {

	private static Connection connect() {
		// SQLite connection string
		String url = "jdbc:sqlite:../DBPissirDomain.db";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static Dominio getDom(String dm) throws SQLException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Domini WHERE Domain = ?");
			prepared.setString(1, dm);
			ResultSet result = prepared.executeQuery();

			// String sql= "SELECT * FROM Domini WHERE Domain = ?";

			// ResultSet result =statement.executeQuery(sql);
			// Dominio res=new Dominio();

			if (result.next()) {
				String dom = result.getString("Domain");
				String desc = result.getString("Desc");

				// String users=result.getString("Users");
				// ArrayList<String> users = new
				// ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
				// ArrayList<String> reso = new
				// ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
				// String reso=result.getString("Resources");

				Integer state = result.getInt("State");
				// ArrayList<String> serv = new
				// ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
				// String serv=result.getString("Services");
				Dominio d = new Dominio(dom, desc, state);// users, reso,, serv
				// res.add(d);
				return d;
			}
			// return null;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;

	}

	public static void delDom(String dm) throws SQLException {

		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("DELETE FROM Domini WHERE Domain = ?");
			prepared.setString(1, dm);
			prepared.executeUpdate();

			prepared = conn.prepareStatement("DELETE FROM Usa WHERE Domain = ?");
			prepared.setString(1, dm);
			prepared.executeUpdate();

			prepared = conn.prepareStatement("DELETE FROM Amministra WHERE Domain = ?");
			prepared.setString(1, dm);
			prepared.executeUpdate();

			prepared = conn.prepareStatement("DELETE FROM Servizi WHERE Domain = ?");
			prepared.setString(1, dm);
			prepared.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		// return false;

	}

	public static void insertDom(String j) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Domini (Domain, State) VALUES (?1,?2)");
			prepared.setString(1, j);
			prepared.setInt(2, 0);
			prepared.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

	}

	public static ArrayList<String> getDomainsAdmin(String user) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Amministra WHERE User = ?");
			prepared.setString(1, user);
			ResultSet result = prepared.executeQuery();

			ArrayList<String> res = new ArrayList<String>();
			// JSONArray jsnarr=new JSONArray();
			while (result.next()) {
				String dom = result.getString("Domain");
				// String desc= result.getString("Desc");
				// Integer state = result.getInt("State");
				//
				// JSONObject j=new JSONObject("{"+dom+":"+state+"}");
				// jsnarr.put(j);
				//
				// Dominio d=new Dominio(dom,desc,state);
				res.add(dom);

			}
			// return jsnarr;
			return res;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;

	}

	public static ArrayList<String> getDomainsUser(String user) throws SQLException, JSONException {

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Usa WHERE User = ?");
			prepared.setString(1, user);
			ResultSet result = prepared.executeQuery();

			ArrayList<String> res = new ArrayList<String>();
			while (result.next()) {
				String dom = result.getString("Domain");
				res.add(dom);

			}
			// return jsnarr;
			return res;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;

	}

	public static Dominio getDomain(String domain) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Domini WHERE Domain = ?");
			prepared.setString(1, domain);
			ResultSet result = prepared.executeQuery();

			// ArrayList<String> res=new ArrayList<String>();
			// JSONArray jsnarr=new JSONArray();
			while (result.next()) {
				String dom = result.getString("Domain");
				String desc = result.getString("Desc");
				Integer state = result.getInt("State");
				//
				// JSONObject j=new JSONObject("{"+dom+":"+state+"}");
				// jsnarr.put(j);
				//
				Dominio d = new Dominio(dom, desc, state);
				// res.add(dom);
				return d;
			}
			// return jsnarr;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;

	}

	public static void fillModules(String a, String b, String c) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn
					.prepareStatement("INSERT INTO Moduli (Module, Requirements, Path) VALUES (?,?,?);");

			prepared.setString(1, a);
			prepared.setString(2, b);
			prepared.setString(3, c);
			prepared.executeUpdate();

		} catch (SQLException e) {
			if (!e.getMessage().contains("SQLITE_CONSTRAINT_PRIMARYKEY")) {
				e.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		// return null;

	}

	public static ArrayList<String> getModules() throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Moduli");
			// prepared.setString(1, user);
			ResultSet result = prepared.executeQuery();

			ArrayList<String> res = new ArrayList<String>();
			while (result.next()) {
				String mod = result.getString("Module");
				// String req= result.getString("Requirements");

				// Integer state = result.getInt("State");
				//
				// JSONObject j=new JSONObject("{"+dom+":"+state+"}");
				// jsnarr.put(j);
				//
				// Dominio d=new Dominio(dom,desc,state);
				res.add(mod);

			}
			// return jsnarr;
			return res;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;

	}

	public static void insertAmministra(String user, String dom) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Amministra (User, Domain) VALUES (?1,?2)");
			prepared.setString(1, user);
			prepared.setString(2, dom);
			// prepared.setString(6, j.getString("services").toString().trim());
			prepared.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void insertUsa(String user, String dom) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Usa (User, Domain) VALUES (?1,?2)");
			prepared.setString(1, user);
			prepared.setString(2, dom);
			// prepared.setString(6, j.getString("services").toString().trim());
			prepared.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void insertService(String domain, String host, String module) throws JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn
					.prepareStatement("INSERT INTO Servizi (Domain, Resource, Module) VALUES (?1,?2,?3)");

			prepared.setString(1, domain);
			prepared.setString(2, host);
			prepared.setString(3, module);

			prepared.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		// return null;

	}

	/**
	 * finds the host from the database and calls insertService(domain,host,service)
	 * to insert the data into the database
	 */
	public static ArrayList<String> insertService(String domain, String module) throws JSONException {
		// find the host from the database
		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement first = conn.prepareStatement("SELECT Requirements FROM Moduli WHERE Module = ?1");
			first.setString(1, module);

			ResultSet res = first.executeQuery();

			ArrayList<String> requirements = new ArrayList<String>();
			while (res.next()) {
				String[] requirementsArr = res.getString("Requirements").split(",");// also works if there is only one
																					// host

				// add the requirementsArr to requirements
				requirements.addAll(Arrays.asList(requirementsArr));
			}

			ArrayList<String> hosts = new ArrayList<String>();
			for (String reqs : requirements) {
				PreparedStatement second = conn.prepareStatement("SELECT Host FROM Risorse WHERE Platform = ?1");// and
																													// Domain
																													// =
																													// ?2");
				second.setString(1, reqs);
				// second.setString(2, domain);

				ResultSet res2 = second.executeQuery();
				while (res2.next()) {
					hosts.add(res2.getString("Host"));
				}
			}
			conn.close();
			ArrayList<String> result = new ArrayList<String>();
			for (String host : hosts) {
				insertService(domain, host, module);
				result.add(module + "---" + host);
			}
			return result;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;
	}

	public static JSONArray getServicesInUseByDomain(String domain) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT Module FROM Servizi WHERE Domain = ?");
			prepared.setString(1, domain);
			ResultSet result = prepared.executeQuery();

			// ArrayList<String> res=new ArrayList<String>();
			// JSONArray jsnarr=new JSONArray();
			ArrayList<String> arr = new ArrayList<String>();// serve per il contains
			JSONArray res = new JSONArray();
			while (result.next()) {
				// JSONObject j= result;
				String module = result.getString("Module");
				if (!arr.contains(module)) {

					arr.add(module);
					res.put(module);
				}
				// String dom = result.getString("Domain");
				// String desc= result.getString("Desc");
				// Integer state = result.getInt("State");
				//
				// JSONObject j=new JSONObject("{"+dom+":"+state+"}");
				// jsnarr.put(j);
				//
				// Dominio d=new Dominio(dom,desc,state);
				// res.add(dom);

			}

			return res;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;

	}

	public static JSONArray getAllServices() {

		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			Statement statement = conn.createStatement();
			ResultSet result = statement.executeQuery("SELECT Module FROM Moduli");

			// ArrayList<String> res=new ArrayList<String>();
			// JSONArray jsnarr=new JSONArray();
			ArrayList<String> arr = new ArrayList<String>();
			JSONArray res = new JSONArray();

			while (result.next()) {
				// JSONObject j= result;
				String resString = result.getString("Module");
				if (!arr.contains(resString)) {
					arr.add(resString);
					res.put(resString);
				}

				// String dom = result.getString("Domain");
				// String desc= result.getString("Desc");
				// Integer state = result.getInt("State");
				//
				// JSONObject j=new JSONObject("{"+dom+":"+state+"}");
				// jsnarr.put(j);
				//
				// Dominio d=new Dominio(dom,desc,state);
				// res.add(dom);

			}

			return res;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;
	}

	public static void setStatoDomain(String domain, int stato) {
		Connection conn = connect();
		if(conn == null)
			return;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("UPDATE Domini SET State = ? WHERE Domain = ?");
			prepared.setInt(1, stato);
			prepared.setString(2, domain);

			prepared.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	public static String getURI(String module) {
		Connection conn = connect();
		if(conn == null)
			return null;
		try {
			// // db parameters
			// String url = "jdbc:sqlite:../DBPissirDomain.db";
			// // create a connection to the database
			// conn = DriverManager.getConnection(url);

			PreparedStatement prepared = conn.prepareStatement("SELECT Path FROM Moduli WHERE Module = ?");
			prepared.setString(1, module);
			ResultSet result = prepared.executeQuery();

			if (result.next()) {
				return "https://gitlab.di.unipmn.it/alfredo/iotlabgw.edu-al.unipmn.it/-/raw/main/" + result.getString("Path");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return null;
	}
}