/*
 * The MIT License (MIT)
 *
 * Copyright (c) 2015 Norwegian Defence Research Establishment / NTNU
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */

package no.ntnu.okse.db;

/**
 * Created by Aleksander Skraastad (myth) on 2/25/15.
 * <p/>
 * okse is licenced under the MIT licence.
 */

import java.sql.*;

public class DB {
    private static Connection con = null;

    /**
     * Connecting to database okse.db
     */
    public static void conDB(){
        try {
            Class.forName("org.sqlite.JDBC");
            con = DriverManager.getConnection("jdbc:sqlite:okse.db");
            System.out.println("conDB: Opened database successfully");
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
    }

    /**
     * Closeing database connection
     */
    public static void closeDB(){
        try {
            con.close();
            System.out.println("closeDB: Closed database successfully");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Initiate database, creates tables and default admin user
     */
    public static void initDB() {
        Statement stmt = null;
        String sql = null;
        conDB();
        try {
            stmt = con.createStatement();

            sql = "CREATE TABLE users" +
                    "(username VARCHAR(50) PRIMARY KEY NOT NULL," +
                    " password VARCHAR(50) NOT NULL ," +
                    " enable INT NOT NULL ," +
                    " description VARCHAR(50))";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE authorities" +
                    "(username VARCHAR(50) NOT NULL," +
                    " authority VARCHAR(50) NOT NULL ," +
                    " constraint fk_authorities_users foreign key(username) references users(username))";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE presistance" +
                    "(topic CHAR(100), " +
                    " message CHAR(200) ," +
                    " protocol CHAR(50))";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE presets" +
                    "(topic CHAR(100), " +
                    " dialect CHAR(50))";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE stats" +
                    "(stat CHAR(100), " +
                    " counter INT)";
            stmt.executeUpdate(sql);

            System.out.println("initDB: Tables created successfully");

            sql = "INSERT INTO users (username,password,enable,description) " +
                    "VALUES ('admin','password',1,'Administrator')";
            stmt.executeUpdate(sql);

            sql = "INSERT INTO authorities (username,authority) " +
                    "VALUES ('admin','ROLE_ADMIN')";
            stmt.executeUpdate(sql);

            System.out.println("initDB: User created successfully");

            stmt.close();
            con.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
    }

    /**
     * Run SQL queries
     * @param sql
     * @return
     * @throws SQLException
     */
    public static ResultSet sqlQuery(String sql) throws SQLException {
        conDB();
        Statement sta = con.createStatement();
        return sta.executeQuery(sql);
    }

    /**
     * INSERT INTO table (fields) VALUES (values)
     * @param table
     * @param fields
     * @param values
     */
    public static void insert(String table, String fields, String values) {
        Statement stmt = null;
        String query = "INSERT INTO " + table + " (" + fields + ") " +
                "VALUES (" + values + ")";
        conDB();
        System.out.println(query);
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * SELECT * FROM table
     * @param table
     * @return
     */
    public static ResultSet selectAll(String table) {
        Statement stmt = null;
        String query = "SELECT * FROM " + table;
        conDB();
        try {
            stmt = con.createStatement();
            return stmt.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * SELECT * FROM tavle WHERE colum = value
     * @param table
     * @param colum
     * @param value
     * @return
     */
    public static ResultSet select(String table, String colum, String value) {
        Statement stmt = null;
        conDB();
        try {
            stmt = con.createStatement();
            return stmt.executeQuery( "SELECT * FROM " + table + " where " + colum + "='" + value + "'");
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        }
        System.out.println("select: Operation done successfully");
        return null;
    }
}

@@ -2534,16 +2534,17 @@
 %22 enable
+d
  INT NOT
@@ -3620,16 +3620,17 @@
 d,enable
+d
 ,descrip
@@ -4200,19 +4200,21 @@
  @param 
-sql
+query
 %0A     * 
@@ -4301,19 +4301,21 @@
 (String 
-sql
+query
 ) throws
@@ -4386,32 +4386,67 @@
 ateStatement();%0A
+        System.out.println(query);%0A
         return s
@@ -4461,19 +4461,21 @@
 teQuery(
-sql
+query
 );%0A    %7D
@@ -5278,32 +5278,67 @@
        conDB();%0A
+        System.out.println(query);%0A
         try %7B%0A  
@@ -5776,38 +5776,163 @@
 = null;%0A        
-conDB(
+String query = %22SELECT * FROM %22 + table + %22 where %22 + colum + %22='%22 + value + %22'%22;%0A        conDB();%0A        System.out.println(query
 );%0A        try %7B
@@ -6015,74 +6015,13 @@
 ery(
- %22SELECT * FROM %22 + table + %22 where %22 + colum + %22='%22 + value + %22'%22
+query
 );%0A 

Fix typo