package db;

import app.SystemUtils;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class DBUtils {

	private static final String DB_NAME = "fmps.db";
	public static final String TABLE_FMPS_MAIN = "fmps_main";
	public static final String DLC = ", ";

	// Columns
	public static final String LAST_NAME = "lastname";
	public static final String FIRST_NAME = "firstname";
	public static final String MIDDLE_NAME = "middlename";
	public static final String ST1_PROGRESS = "st1_progress";
	public static final String ST2_PROGRESS = "st2_progress";
	public static final String ST3_PROGRESS = "st3_progress";
	public static final String ST1_FE = "st1_fe";
	public static final String ST1_C = "st1_c";
	public static final String ST1_MN = "st1_mn";
	public static final String ST1_P_ENV = "st1_p_env";
	public static final String ST1_F_SURF_WELD_AREA = "st1_f_surface_area";
	public static final String ST1_MP_WEIGHT_MOLTEN_METAL = "st1_mp_weight_molten_metal";
	public static final String ST1_TEMPERATURE = "st1_temperature";
	public static final String ST1_TIME = "st1_time";
	public static final String ST2_ARGON = "st2_argon";
	public static final String ST2_CO2 = "st2_co2";
	public static final String ST2_O2 = "st2_o2";
	public static final String ST2_CO = "st2_co";
	public static final String ST2_O = "st2_o";
	public static final String ST2_C = "st2_c";
	public static final String ST2_TEMPERATURE = "st2_temperature";
	public static final String ST3_CO_PARTIAL_PRESSURE = "st3_co_partial_pressure";
	public static final String ST3_O_PARTIAL_PRESSURE = "st3_o_partial_pressure";
	public static final String ST3_O_DISSOLVE = "st3_dissolve";
	public static final String ST3_ATM_PRESSURE = "st3_atm_pressure";
	public static final String ST3_TEMPERATURE = "st3_temperature";

	private static String dbStoredAbsPath;


	public DBUtils() {
		dbStoredAbsPath = SystemUtils.getUserCatalogAbsPath();
	}


	private static void initDatabase() throws Exception {
		Connection conn;
		Statement stmt;
		String dbNameAbsPath = dbStoredAbsPath + "/" + DB_NAME;
		System.out.println("db name = " + dbNameAbsPath);

		File dbFile = new File(dbNameAbsPath);
		if (dbFile.exists())
			return;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + dbStoredAbsPath + "/" + DB_NAME);
			System.out.println("Opened database successfully");
			stmt = conn.createStatement();
			String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_FMPS_MAIN + " (" +
				"id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  DEFAULT 1, " +
				LAST_NAME + " VARCHAR , " +
				FIRST_NAME + " VARCHAR , " +
				MIDDLE_NAME + " VARCHAR , " +
				ST1_PROGRESS + " INTEGER DEFAULT 0, " +
				ST2_PROGRESS + " INTEGER DEFAULT 0, " +
				ST3_PROGRESS + " INTEGER DEFAULT 0, " +
				ST1_FE + " DOUBLE DEFAULT 0, " +
				ST1_C + " DOUBLE DEFAULT 0, " +
				ST1_MN + " DOUBLE DEFAULT 0, " +
				ST1_P_ENV + " INTEGER , " +
				ST1_F_SURF_WELD_AREA + " DOUBLE , " +
				ST1_MP_WEIGHT_MOLTEN_METAL + " DOUBLE , " +
				ST1_TEMPERATURE + " INTEGER , " +
				ST1_TIME + " DOUBLE , " +
				ST2_ARGON + " DOUBLE ," +
				ST2_CO2 + " DOUBLE ," +
				ST2_O2 + " DOUBLE ," +
				ST2_CO + " DOUBLE ," +
				ST2_O + " DOUBLE ," +
				ST2_C + " DOUBLE ," +
				ST2_TEMPERATURE + " INTEGER ," +
				ST3_CO_PARTIAL_PRESSURE + " DOUBLE ," +
				ST3_O_PARTIAL_PRESSURE + " DOUBLE ," +
				ST3_O_DISSOLVE + " DOUBLE ," +
				ST3_ATM_PRESSURE + " INTEGER ," +
				ST3_TEMPERATURE + " DOUBLE" +
				");";
			stmt.execute(sql);
			System.out.println("Created new db file and executed init sql statement.");

			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			e.printStackTrace();
		}
	}
}

@@ -111,16 +111,46 @@
 anager;%0A
+import java.sql.SQLException;%0A
 import j
@@ -3847,11 +3847,552 @@
 ;%0A%09%09%7D%0A%09%7D
+%0A%0A%09/**%0A%09 * General method for execute sql statements.%0A%09 *%0A%09 * @param sql query%0A%09 */%0A%09private static void sqlStatementExecutor(String sql) %7B%0A%09%09Connection conn;%0A%09%09Statement stmt;%0A%09%09try %7B%0A%09%09%09Class.forName(%22org.sqlite.JDBC%22);%0A%09%09%09conn = DriverManager.getConnection(%22jdbc:sqlite:%22 + dbStoredAbsPath + %22/%22 + DB_NAME);%0A%09%09%09System.out.println(%22Opened database successfully%22);%0A%0A%09%09%09stmt = conn.createStatement();%0A%09%09%09stmt.execute(sql);%0A%09%09%09stmt.close();%0A%09%09%09conn.close();%0A%09%09%7D catch (ClassNotFoundException %7C SQLException e) %7B%0A%09%09%09e.printStackTrace();%0A%09%09%7D%0A%09%7D
 %0A%7D%0A

Add general sql executor method.