java source code of Database

/*
 *  Kontalk Java client
 *  Copyright (C) 2016 Kontalk Devteam <[email protected]>
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.kontalk.persistence;

import java.nio.file.Path;
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.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.EnumSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.stream.Collectors;
import org.apache.commons.lang.StringUtils;
import org.kontalk.misc.JID;
import org.kontalk.misc.KonException;
import org.kontalk.model.message.KonMessage;
import org.kontalk.model.chat.Chat;
import org.kontalk.model.Contact;
import org.kontalk.model.chat.Member;
import org.kontalk.model.message.Transmission;
import org.kontalk.util.EncodingUtils;
import org.sqlite.SQLiteConfig;

/**
 * Global database for permanently storing all model information.
 * Uses the JDBC API and SQLite as DBMS.
 *
 * Database access is not concurrent safe (connection pool is needed). At least
 * writing is synchronized. Hopefully we don't see this no more:
 * "SQLException: ResultSet already requested" or "ResultSet closed"
 *
 * @author Alexander Bikadorov {@literal <[email protected]>}
 */
public final class Database {
    private static final Logger LOGGER = Logger.getLogger(Database.class.getName());

    public static final String SQL_ID = "_id INTEGER PRIMARY KEY AUTOINCREMENT, ";

    private static final String FILENAME = "kontalk_db.sqlite";
    private static final int DB_VERSION = 5;
    private static final String SQL_CREATE = "CREATE TABLE IF NOT EXISTS ";
    private static final String SV = "schema_version";
    private static final String UV = "user_version";

    private Connection mConn = null;

    public Database(Path appDir) throws KonException {
        // load the sqlite-JDBC driver using the current class loader
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            LOGGER.log(Level.SEVERE, "sqlite-JDBC driver not found", ex);
            throw new KonException(KonException.Error.DB, ex);
        }

        // create database connection
        Path path = appDir.resolve(FILENAME);
        SQLiteConfig config = new SQLiteConfig();
        config.enforceForeignKeys(true);
        try {
          mConn = DriverManager.getConnection("jdbc:sqlite:" + path.toString(), config.toProperties());
        } catch(SQLException ex) {
          // if the error message is "out of memory",
          // it probably means no database file is found
          LOGGER.log(Level.SEVERE, "can't create database connection", ex);
          throw new KonException(KonException.Error.DB, ex);
        }

        try {
            // setting to false!
            mConn.setAutoCommit(false);
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't set autocommit", ex);
        }

        boolean isNew;
        try (ResultSet rs = this.execQuery("PRAGMA "+SV)) {
            isNew = rs.getInt(SV) == 0;
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "can't get schema version", ex);
            throw new KonException(KonException.Error.DB, ex);
        }

        if (isNew) {
            LOGGER.info("new database, creating tables");
            try (Statement stat = mConn.createStatement()) {
                // set version
                mConn.createStatement().execute("PRAGMA "+UV+" = "+DB_VERSION);
                this.commit();
                this.createTable(stat, Contact.TABLE, Contact.SCHEMA);
                this.createTable(stat, Chat.TABLE, Chat.SCHEMA);
                this.createTable(stat, Member.TABLE, Member.SCHEMA);
                this.createTable(stat, KonMessage.TABLE, KonMessage.SCHEMA);
                this.createTable(stat, Transmission.TABLE, Transmission.SCHEMA);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "can't create tables", ex);
                throw new KonException(KonException.Error.DB, ex);
            }
            return;
        }

        // update if needed
        int version;
        try (ResultSet rs = this.execQuery("PRAGMA "+UV)) {
            version = rs.getInt(UV);
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't get db version", ex);
            return;
        }
        LOGGER.config("version: "+version);
        if (version < DB_VERSION) {
            try {
                this.update(version);
            } catch (SQLException ex) {
                LOGGER.log(Level.WARNING, "can't update db", ex);
            }
        }
    }

    private void createTable(Statement stat, String table, String schema) throws SQLException {
        stat.executeUpdate(SQL_CREATE + table + " " + schema);
    }

    private void update(int fromVersion) throws SQLException {
        if (fromVersion < 1) {
            mConn.createStatement().execute("ALTER TABLE "+Chat.TABLE+
                    " ADD COLUMN "+Chat.COL_VIEW_SET+" NOT NULL DEFAULT '{}'");
        }
        if (fromVersion < 2) {
            mConn.createStatement().execute("ALTER TABLE "+KonMessage.TABLE+
                    " ADD COLUMN "+KonMessage.COL_SERV_DATE+" DEFAULT NULL");
        }
        if (fromVersion < 3) {
            String messageTableTemp = KonMessage.TABLE + "_TEMP";
            this.createTable(mConn.createStatement(), messageTableTemp, KonMessage.SCHEMA);
            mConn.createStatement().execute("INSERT INTO "+messageTableTemp +
                    " SELECT _id, thread_id, xmpp_id, date, receipt_status, " +
                    "content, encryption_status, signing_status, coder_errors, " +
                    "server_error, server_date FROM "+KonMessage.TABLE);

            this.createTable(mConn.createStatement(), Transmission.TABLE, Transmission.SCHEMA);
            mConn.createStatement().execute("INSERT INTO "+Transmission.TABLE +
                    " SELECT NULL, _id, user_id, jid, NULL FROM "+KonMessage.TABLE);

            mConn.createStatement().execute("PRAGMA foreign_keys=OFF");
            mConn.createStatement().execute("DROP TABLE "+KonMessage.TABLE);
            mConn.createStatement().execute("ALTER TABLE "+messageTableTemp+
                    " RENAME TO "+KonMessage.TABLE);
            mConn.createStatement().execute("PRAGMA foreign_keys=ON");

            mConn.createStatement().execute("ALTER TABLE "+Chat.TABLE+
                    " ADD COLUMN "+Chat.COL_GD+" DEFAULT NULL");
        }
        if (fromVersion < 4) {
            mConn.createStatement().execute("ALTER TABLE "+Contact.TABLE+
                    " ADD COLUMN "+Contact.COL_AVATAR_ID+" DEFAULT NULL");
        }
        if (fromVersion < 5) {
            mConn.createStatement().execute("ALTER TABLE "+Member.TABLE+
                    " ADD COLUMN "+Member.COL_ROLE+" DEFAULT 0");
        }

        // set new version
        mConn.createStatement().execute("PRAGMA "+UV+" = "+DB_VERSION);
        this.commit();
        LOGGER.info("updated to version "+DB_VERSION);
    }

    public synchronized void close() {
        try {
            if(mConn == null || mConn.isClosed())
                return;
            // just to be sure
            mConn.commit();
            mConn.close();
        } catch(SQLException ex) {
            LOGGER.log(Level.WARNING, "can't close db", ex);
        }
    }

    /**
     * Select all rows from one table.
     * The returned ResultSet must be closed by the caller after usage!
     */
    public ResultSet execSelectAll(String table) throws SQLException {
        return this.execQuery("SELECT * FROM " + table);
    }

    /**
     * Select rows from one table that match an arbitrary 'where' clause.
     * Insecure to SQL injections, use with caution!
     * The returned ResultSet must be closed by the caller after usage!
     */
    public ResultSet execSelectWhereInsecure(String table, String where) throws SQLException {
        return this.execQuery("SELECT * FROM " + table + " WHERE " + where);
    }

    private ResultSet execQuery(String select) throws SQLException {
        try {
            PreparedStatement stat = mConn.prepareStatement(select);
            // does not work, i dont care
            //stat.closeOnCompletion();
            ResultSet resultSet = stat.executeQuery();
            return resultSet;
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't execute select: " + select, ex);
            throw ex;
        }
    }

    /**
     * Add a new model / row to database.
     * @param table table name the values are inserted into
     * @param values all objects / row fields that to insert
     * @return id value of inserted row, -1 if something went wrong
     */
    public synchronized int execInsert(String table, List<Object> values) {
        // first column is the id
        String insert = "INSERT INTO " + table + " VALUES (NULL,";

        List<String> vList = new ArrayList<>(values.size());
        while(vList.size() < values.size())
            vList.add("?");

        insert += StringUtils.join(vList, ", ") + ")";

        try (PreparedStatement stat = mConn.prepareStatement(insert,
                Statement.RETURN_GENERATED_KEYS)) {
            insertValues(stat, values);
            stat.executeUpdate();
            mConn.commit();
            ResultSet keys = stat.getGeneratedKeys();
            return keys.getInt(1);
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't execute insert: " + insert + " " + values, ex);
            return -1;
        }
    }

    /** Update values (at most one row). */
    public synchronized void execUpdate(String table, Map<String, Object> set, int id) {
        LOGGER.config("table: "+table);
        String update = "UPDATE OR FAIL " + table + " SET ";

        List<String> keyList = new ArrayList<>(set.keySet());

        List<String> vList = keyList.stream()
                .map(key -> key + " = ?")
                .collect(Collectors.toList());

        update += StringUtils.join(vList, ", ") + " WHERE _id == " + id ;
        // note: looks like driver doesn't support "LIMIT"
        //update += " LIMIT 1";

        try (PreparedStatement stat = mConn.prepareStatement(update, Statement.RETURN_GENERATED_KEYS)) {
            insertValues(stat, keyList, set);
            stat.executeUpdate();
            mConn.commit();
            stat.getGeneratedKeys();
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't execute update: " + update + " " + set, ex);
        }
    }

    /** Delete one row. Not commited! Call commit() after deletions. */
    public boolean execDelete(String table, int id) {
        LOGGER.info("deletion, table: " + table + "; id: " + id);
        try (Statement stat = mConn.createStatement()) {
            stat.executeUpdate("DELETE FROM " + table + " WHERE _id = " + id);
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't delete", ex);
            return false;
        }
        return true;
    }

    public boolean commit() {
        try {
            mConn.commit();
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't commit", ex);
            return false;
        }
        return true;
    }

    private static void insertValues(PreparedStatement stat,
            List<String> keys,
            Map<String, Object> map) throws SQLException {
        for (int i = 0; i < keys.size(); i++) {
            setValue(stat, i, map.get(keys.get(i)));
         }
    }

    private static void insertValues(PreparedStatement stat,
            List<Object> values) throws SQLException {
        for (int i = 0; i < values.size(); i++) {
            setValue(stat, i, values.get(i));
        }
    }

    private static void setValue(PreparedStatement stat, int i, Object value)
            throws SQLException {
        if (value instanceof String) {
                stat.setString(i+1, (String) value);
            } else if (value instanceof Integer) {
                stat.setInt(i+1, (int) value);
            } else if (value instanceof Date) {
                stat.setLong(i+1, ((Date) value).getTime());
            } else if (value instanceof Boolean) {
                stat.setBoolean(i+1, (boolean) value);
            } else if (value instanceof Enum) {
                stat.setInt(i+1, ((Enum) value).ordinal());
            } else if (value instanceof EnumSet) {
                stat.setInt(i+1, EncodingUtils.enumSetToInt(((EnumSet) value)));
            } else if (value instanceof Optional) {
                setValue(stat, i, ((Optional<?>) value).orElse(null));
            } else if (value instanceof JID) {
                stat.setString(i+1, ((JID) value).string());
            } else if (value == null) {
                stat.setNull(i+1, Types.NULL);
            } else {
                LOGGER.warning("unknown type: " + value);
            }
    }

    /**
     * Return the value for a specific column as string; the string is empty if
     * the value is SQL NULL.
     */
    public static String getString(ResultSet r, String columnLabel){
        String s;
        try {
            s = r.getString(columnLabel);
        } catch (SQLException ex) {
            LOGGER.log(Level.WARNING, "can't get string from db", ex);
            return "";
        }
        return StringUtils.defaultString(s);
    }

    public static String setString(String s) {
        return s.isEmpty() ? null : s;
    }
}