com.zimbra.cs.db.SQLite.java Source code

Java tutorial

Introduction

Here is the source code for com.zimbra.cs.db.SQLite.java

Source

/*
 * ***** BEGIN LICENSE BLOCK *****
 * Zimbra Collaboration Suite Server
 * Copyright (C) 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016 Synacor, Inc.
 *
 * 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,
 * version 2 of the License.
 *
 * 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 <https://www.gnu.org/licenses/>.
 * ***** END LICENSE BLOCK *****
 */
package com.zimbra.cs.db;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.ReentrantLock;

import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.Options;
import org.apache.commons.dbcp.DelegatingConnection;
import org.apache.commons.pool.impl.GenericObjectPool;

import com.google.common.base.Joiner;
import com.google.common.base.Strings;
import com.zimbra.common.localconfig.LC;
import com.zimbra.common.service.ServiceException;
import com.zimbra.common.util.ZimbraLog;
import com.zimbra.cs.db.DbPool.DbConnection;
import com.zimbra.cs.db.DbPool.PoolConfig;

public final class SQLite extends Db {

    private static final String PRAGMA_JOURNAL_MODE_DEFAULT = "DELETE";
    private static final String PRAGMA_SYNCHRONOUS_DEFAULT = "FULL";

    private Map<Db.Error, String> mErrorCodes;
    private String cacheSize;
    private String journalMode;
    private String pageSize;
    private String syncMode;

    SQLite() {
        mErrorCodes = new HashMap<Db.Error, String>(6);
        mErrorCodes.put(Db.Error.DUPLICATE_ROW, "not unique");
        mErrorCodes.put(Db.Error.NO_SUCH_TABLE, "no such table");
        mErrorCodes.put(Db.Error.FOREIGN_KEY_CHILD_EXISTS, "foreign key");
        mErrorCodes.put(Db.Error.FOREIGN_KEY_NO_PARENT, "foreign key");
        mErrorCodes.put(Db.Error.TOO_MANY_SQL_PARAMS, "too many SQL variables");
        mErrorCodes.put(Db.Error.BUSY, "SQLITE_BUSY");
        mErrorCodes.put(Db.Error.LOCKED, "database is locked");
        mErrorCodes.put(Db.Error.CANTOPEN, "SQLITE_CANTOPEN");
    }

    @Override
    boolean supportsCapability(Db.Capability capability) {
        switch (capability) {
        case AVOID_OR_IN_WHERE_CLAUSE:
            return false;
        case BITWISE_OPERATIONS:
            return true;
        case BOOLEAN_DATATYPE:
            return false;
        case CASE_SENSITIVE_COMPARISON:
            return true;
        case CAST_AS_BIGINT:
            return false;
        case CLOB_COMPARISON:
            return true;
        case DISABLE_CONSTRAINT_CHECK:
            return false;
        case FILE_PER_DATABASE:
            return true;
        case FORCE_INDEX_EVEN_IF_NO_SORT:
            return false;
        case LIMIT_CLAUSE:
            return true;
        case MULTITABLE_UPDATE:
            return false;
        case NON_BMP_CHARACTERS:
            return true;
        case ON_DUPLICATE_KEY:
            return false;
        case ON_UPDATE_CASCADE:
            return true;
        case READ_COMMITTED_ISOLATION:
            return false;
        case REPLACE_INTO:
            return true;
        case REQUEST_UTF8_UNICODE_COLLATION:
            return false;
        case ROW_LEVEL_LOCKING:
            return false;
        case UNIQUE_NAME_INDEX:
            return false;
        case SQL_PARAM_LIMIT:
            return true;
        case DUMPSTER_TABLES:
            return false;
        }
        return false;
    }

    @Override
    boolean compareError(SQLException e, Error error) {
        // XXX: the SQLite JDBC driver doesn't yet expose SQLite error codes, which sucks
        String code = mErrorCodes.get(error);
        return code != null && e.getMessage() != null && e.getMessage().contains(code);
    }

    @Override
    String forceIndexClause(String index) {
        // don't think we can direct the sqlite optimizer...
        return "";
    }

    @Override
    String getIFNULLClause(String expr1, String expr2) {
        return "IFNULL(" + expr1 + ", " + expr2 + ")";
    }

    @Override
    public String bitAND(String expr1, String expr2) {
        return expr1 + " & " + expr2;
    }

    @Override
    public String bitANDNOT(String expr1, String expr2) {
        return expr1 + " & ~" + expr2;
    }

    @Override
    PoolConfig getPoolConfig() {
        return new SQLiteConfig();
    }

    @Override
    void startup(org.apache.commons.dbcp.PoolingDataSource pool, int poolSize) throws SQLException {
        cacheSize = LC.sqlite_cache_size.value();
        if (cacheSize.equals("0"))
            cacheSize = null;
        journalMode = LC.sqlite_journal_mode.value();
        pageSize = LC.sqlite_page_size.value();
        if (pageSize.equals("0"))
            pageSize = null;
        syncMode = LC.sqlite_sync_mode.value();
        ZimbraLog.dbconn.info("sqlite driver running with " + (cacheSize == null ? "default" : cacheSize)
                + " cache cache, " + (pageSize == null ? "default" : pageSize) + " page size, " + journalMode
                + " journal mode, " + syncMode + " sync mode");
        super.startup(pool, poolSize);
    }

    @Override
    void postCreate(Connection conn) throws SQLException {
        try {
            conn.setAutoCommit(true);
            pragmas(conn, null);
        } finally {
            conn.setAutoCommit(false);
        }
    }

    private void pragma(Connection conn, String dbname, String key, String value) throws SQLException {
        PreparedStatement stmt = null;

        try {
            String prefix = dbname == null || dbname.equals("zimbra") ? "" : dbname + ".";
            (stmt = conn.prepareStatement("PRAGMA " + prefix + key + (value == null ? "" : " = " + value)))
                    .execute();
        } finally {
            DbPool.quietCloseStatement(stmt);
        }
    }

    void pragmas(Connection conn, String dbname) throws SQLException {
        /*
         * auto_vacuum causes databases to be locked permanently
         * pragma(conn, dbname, "auto_vacuum", "2");
         */
        pragma(conn, dbname, "foreign_keys", "ON");
        if (journalMode != null && !journalMode.equalsIgnoreCase(PRAGMA_JOURNAL_MODE_DEFAULT))
            pragma(conn, dbname, "journal_mode", journalMode);
        if (syncMode != null && !syncMode.equalsIgnoreCase(PRAGMA_SYNCHRONOUS_DEFAULT))
            pragma(conn, dbname, "synchronous", syncMode);
        if (cacheSize != null)
            pragma(conn, dbname, "cache_size", cacheSize);
        if (pageSize != null)
            pragma(conn, dbname, "page_size", pageSize);
    }

    private static final int DEFAULT_CONNECTION_POOL_SIZE = 6;

    private static final int MAX_ATTACHED_DATABASES = readConfigInt("sqlite_max_attached_databases",
            "max # of attached databases", 7);

    private static final HashMap<Connection, LinkedHashMap<String, String>> sAttachedDatabases = new HashMap<Connection, LinkedHashMap<String, String>>(
            DEFAULT_CONNECTION_POOL_SIZE);

    private LinkedHashMap<String, String> getAttachedDatabases(DbConnection conn) {
        return sAttachedDatabases.get(getInnermostConnection(conn.getConnection()));
    }

    private Connection getInnermostConnection(Connection conn) {
        Connection retVal = null;
        if (conn instanceof DebugConnection)
            retVal = ((DebugConnection) conn).getConnection();
        if (conn instanceof DelegatingConnection)
            retVal = ((DelegatingConnection) conn).getInnermostDelegate();
        return retVal == null ? conn : retVal;
    }

    @Override
    public void optimize(DbConnection conn, String dbname, int level) throws ServiceException {
        try {
            boolean autocommit = conn.getConnection().getAutoCommit();
            PreparedStatement stmt = null;

            try {
                if (!autocommit)
                    conn.getConnection().setAutoCommit(true);
                if (dbname == null)
                    dbname = "zimbra";
                registerDatabaseInterest(conn, dbname);
                if (level > 0 && dbname.endsWith("zimbra")) {
                    if (level == 2)
                        (stmt = conn.prepareStatement("VACUUM")).execute();
                    else
                        pragma(conn.getConnection(), dbname, "incremental_vacuum", null);
                }
                (stmt = conn.prepareStatement("ANALYZE " + dbname)).execute();
                ZimbraLog.dbconn.debug("sqlite " + (level > 0 ? "vacuum" : "analyze") + ' ' + dbname);
            } finally {
                if (!autocommit) {
                    try {
                        conn.getConnection().setAutoCommit(autocommit);
                    } catch (SQLException sqle) {
                        ZimbraLog.dbconn.warn(
                                "failed to reset autocommit to false. probably caused by prior errors %s", dbname);
                        DbPool.quietClose(conn);
                        throw ServiceException.FAILURE("failed to reset autocommit to false", sqle);
                    }
                }

                DbPool.quietCloseStatement(stmt);
            }
        } catch (Exception e) {
            throw ServiceException.FAILURE("sqlite " + (level > 0 ? "vacuum" : "analyze") + ' ' + dbname + " error",
                    e);
        }
    }

    @Override
    public void registerDatabaseInterest(DbConnection conn, String dbname) throws SQLException, ServiceException {
        LinkedHashMap<String, String> attachedDBs = getAttachedDatabases(conn);
        if (attachedDBs != null && attachedDBs.containsKey(dbname))
            return;

        // if we're using more databases than we're allowed to, detach the least recently used
        if (attachedDBs != null && attachedDBs.size() >= MAX_ATTACHED_DATABASES) {
            for (Iterator<String> it = attachedDBs.keySet().iterator(); attachedDBs.size() >= MAX_ATTACHED_DATABASES
                    && it.hasNext();) {
                String name = it.next();

                if (!name.equals("zimbra") && detachDatabase(conn, name))
                    it.remove();
            }
        }
        attachDatabase(conn, dbname);
    }

    void attachDatabase(DbConnection conn, String dbname) throws SQLException, ServiceException {
        PreparedStatement stmt = null;
        boolean autocommit = true;
        try {
            autocommit = conn.getConnection().getAutoCommit();
            if (!autocommit)
                conn.getConnection().setAutoCommit(true);

            (stmt = conn.prepareStatement("ATTACH DATABASE \"" + getDatabaseFilename(dbname) + "\" AS " + dbname))
                    .execute();
            pragmas(conn.getConnection(), dbname);
        } catch (SQLException e) {
            ZimbraLog.dbconn.error("database " + dbname + " attach failed", e);
            if (!"database is already attached".equals(e.getMessage()))
                throw e;
        } finally {
            if (!autocommit) {
                try {
                    conn.getConnection().setAutoCommit(autocommit);
                } catch (SQLException sqle) {
                    ZimbraLog.dbconn
                            .warn("failed to reset autocommit to false. probably caused by prior errors " + dbname);
                    DbPool.quietClose(conn);
                    throw ServiceException.FAILURE("failed to reset autocommit to false", sqle);
                }
            }
            DbPool.quietCloseStatement(stmt);
        }

        LinkedHashMap<String, String> attachedDBs = getAttachedDatabases(conn);
        if (attachedDBs != null) {
            attachedDBs.put(dbname, null);
        } else {
            attachedDBs = new LinkedHashMap<String, String>(MAX_ATTACHED_DATABASES * 3 / 2, (float) 0.75, true);
            attachedDBs.put(dbname, null);
            sAttachedDatabases.put(getInnermostConnection(conn.getConnection()), attachedDBs);
        }
    }

    private boolean detachDatabase(DbConnection conn, String dbname) throws ServiceException {
        PreparedStatement stmt = null;
        boolean autocommit = true;
        try {
            autocommit = conn.getConnection().getAutoCommit();
            if (!autocommit) {
                conn.getConnection().setAutoCommit(true);
            }
            (stmt = conn.prepareStatement("DETACH DATABASE " + dbname)).execute();
            return true;
        } catch (SQLException e) {
            if (!deleted.containsKey(dbname)) {
                ZimbraLog.dbconn.warn("database overflow autoclose failed for DB " + dbname, e);
                return false;
            } else {
                return true;
            }
        } finally {
            if (!autocommit) {
                try {
                    conn.getConnection().setAutoCommit(autocommit);
                } catch (SQLException sqle) {
                    ZimbraLog.dbconn.warn("failed to reset autocommit to false. probably caused by prior errors %s",
                            dbname);
                    DbPool.quietClose(conn);
                    throw ServiceException.FAILURE("failed to reset autocommit to false", sqle);
                }
            }
            DbPool.quietCloseStatement(stmt);
        }
    }

    private void releaseMboxDbLock(Integer mboxId) {
        if (mboxId != null) {
            ReentrantLock lock = null;
            lock = lockMap.get(mboxId);
            if (lock != null && lock.isHeldByCurrentThread()) {
                lock.unlock();
                ZimbraLog.dbconn.trace("unlocked mbox %d", mboxId);
            }
        }
    }

    @Override
    void preClose(DbConnection conn) {
        releaseMboxDbLock(conn.mboxId);
    }

    private static ConcurrentMap<Integer, ReentrantLock> lockMap = new ConcurrentHashMap<Integer, ReentrantLock>();

    private boolean checkLockMap(int mboxId) {
        for (Entry<Integer, ReentrantLock> entry : lockMap.entrySet()) {
            if (entry.getKey().intValue() != mboxId && entry.getValue().isHeldByCurrentThread()) {
                ZimbraLog.dbconn.debug("already holding db lock for mbox %d", entry.getKey());
                if (entry.getKey().intValue() != -1) {
                    return false;
                }
            }
        }
        return true;
    }

    @Override
    void preOpen(Integer mboxId) {
        ZimbraLog.dbconn.trace("trying to lock mbox %d", mboxId);
        assert (checkLockMap(mboxId));
        ReentrantLock lock = lockMap.get(mboxId);
        if (lock == null) {
            lock = new ReentrantLock();
            ReentrantLock added = lockMap.putIfAbsent(mboxId, lock);
            if (added != null) {
                lock = added;
            }
        }
        boolean locked = false;
        long timeoutSecs = 180;
        //lock with timeout in case external call sites cause a deadlock
        //(e.g. one site locks some object before opening connection; another incorrectly locks same object after opening connection)
        //in case of timeout we'll fall through and let sqlite_busy retry handler sort it out
        try {
            locked = lock.tryLock(timeoutSecs, TimeUnit.SECONDS);
        } catch (InterruptedException e) {
        }
        if (!locked) {
            ZimbraLog.dbconn.warn("Unable to get db lock for mbox %d", mboxId);
        } else {
            ZimbraLog.dbconn.trace("locked mbox %d", mboxId);
        }
    }

    @Override
    void abortOpen(Integer mboxId) {
        releaseMboxDbLock(mboxId);
    }

    @Override
    public boolean databaseExists(DbConnection conn, String dbname) throws ServiceException {
        if (!new File(getDatabaseFilename(dbname)).exists())
            return false;

        // since it's so easy to end up with an empty SQLite database, make
        // sure that at least one table exists
        PreparedStatement stmt = null;
        ResultSet rs = null;
        boolean autocommit = true;
        try {
            autocommit = conn.getConnection().getAutoCommit();
            if (!autocommit)
                conn.getConnection().setAutoCommit(true);

            registerDatabaseInterest(conn, dbname);
            stmt = conn.prepareStatement("SELECT COUNT(*) FROM " + (dbname.equals("zimbra") ? "" : dbname + ".")
                    + "sqlite_master WHERE type='table'");
            rs = stmt.executeQuery();
            boolean complete = rs.next() ? (rs.getInt(1) >= 1) : false;
            return complete;
        } catch (SQLException e) {
            throw ServiceException.FAILURE("sqlite error", e);
        } finally {
            if (!autocommit) {
                try {
                    conn.getConnection().setAutoCommit(autocommit);
                } catch (SQLException sqle) {
                    ZimbraLog.dbconn.warn("failed to reset autocommit to false. probably caused by prior errors %s",
                            dbname);
                    DbPool.quietClose(conn);
                    throw ServiceException.FAILURE("failed to reset autocommit to false", sqle);
                }
            }
            DbPool.closeResults(rs);
            DbPool.closeStatement(stmt);
        }
    }

    private ConcurrentMap<String, Boolean> deleted = new ConcurrentHashMap<String, Boolean>();

    @Override
    void deleteDatabaseFile(DbConnection conn, String dbname) {
        assert (dbname != null && !dbname.trim().equals(""));
        try {
            detachDatabase(conn, dbname);
        } catch (ServiceException se) {
            ZimbraLog.dbconn.warn("failed to detach while deleting");
        }
        deleted.put(dbname, true);
        ZimbraLog.dbconn.info("deleting database file for DB '" + dbname + "'");
        new File(getDatabaseFilename(dbname)).delete();
        new File(getDatabaseFilename(dbname) + "-journal").delete();
    }

    public String getDatabaseFilename(String dbname) {
        return LC.zimbra_home.value() + File.separator + "sqlite" + File.separator + dbname + ".db";
    }

    final class SQLiteConfig extends DbPool.PoolConfig {
        SQLiteConfig() {
            mDriverClassName = "org.sqlite.JDBC";
            mPoolSize = DEFAULT_CONNECTION_POOL_SIZE;
            mRootUrl = null;
            mConnectionUrl = "jdbc:sqlite:" + getDatabaseFilename("zimbra");
            mLoggerUrl = null;
            mSupportsStatsCallback = false;
            mDatabaseProperties = getSQLiteProperties();
            whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW; //we use a small pool. we can easily starve when any code requires more than one connection to complete a single operation

            // override pool size if specified in prefs
            mPoolSize = readConfigInt("sqlite_pool_size", "connection pool size", DEFAULT_CONNECTION_POOL_SIZE);
        }

        private Properties getSQLiteProperties() {
            Properties props = new Properties();
            if (LC.sqlite_shared_cache_enabled.booleanValue())
                props.setProperty("shared_cache", "true");
            return props;
        }
    }

    static int readConfigInt(final String keyname, final String description, final int defaultvalue) {
        int value = defaultvalue;
        try {
            String configvalue = LC.get(keyname);
            if (configvalue != null && !configvalue.trim().equals(""))
                value = Math.max(1, Integer.parseInt(configvalue));
        } catch (NumberFormatException nfe) {
            ZimbraLog.dbconn.warn("exception parsing '" + keyname + "' config; defaulting limit to " + defaultvalue,
                    nfe);
        }
        ZimbraLog.dbconn.info("setting " + description + " to " + value);
        return value;
    }

    @Override
    public void flushToDisk() {
        // not really implemented
    }

    @Override
    public String toString() {
        return "SQLite";
    }

    @Override
    protected int getInClauseBatchSize() {
        return 200;
    }

    @Override
    public void checkParamLimit(int numParams) throws ServiceException {
        if (numParams > getParamLimit()) {
            throw ServiceException.FAILURE("SQLite parameter limit will be exceeded",
                    new SQLException(mErrorCodes.get(Db.Error.TOO_MANY_SQL_PARAMS)));
        }
    }

    @Override
    public int getParamLimit() {
        return 999; //SQLite's SQLITE_MAX_VARIABLE_NUMBER default value
    }

    public static void main(String args[]) {
        // command line argument parsing
        Options options = new Options();
        CommandLine cl = Versions.parseCmdlineArgs(args, options);

        String outputDir = cl.getOptionValue("o");
        File outFile = new File(outputDir, "versions-init.sql");
        outFile.delete();

        try {
            String redoVer = com.zimbra.cs.redolog.Version.latest().toString();
            String outStr = "-- AUTO-GENERATED .SQL FILE - Generated by the SQLite versions tool\n"
                    + "INSERT INTO config(name, value, description) VALUES\n" + "\t('db.version', '"
                    + Versions.DB_VERSION + "', 'db schema version');\n"
                    + "INSERT INTO config(name, value, description) VALUES\n" + "\t('index.version', '"
                    + Versions.INDEX_VERSION + "', 'index version');\n"
                    + "INSERT INTO config(name, value, description) VALUES\n" + "\t('redolog.version', '" + redoVer
                    + "', 'redolog version');\n";

            Writer output = new BufferedWriter(new FileWriter(outFile));
            output.write(outStr);
            output.close();
        } catch (IOException e) {
            System.out.println("ERROR - caught exception at\n");
            e.printStackTrace();
            System.exit(-1);
        }
    }

    @Override
    public String concat(String... fieldsToConcat) {
        Joiner joiner = Joiner.on(" || ").skipNulls();
        return joiner.join(fieldsToConcat);
    }

    @Override
    public String sign(String field) {
        return "CASE WHEN(" + field + ")>0 THEN '1' WHEN(" + field + ")<0 THEN '-1' ELSE '0' END";
    }

    @Override
    public String lpad(String field, int padSize, String padString) {
        return "SUBSTR('" + Strings.repeat(padString, padSize) + "' || " + field + ", -" + padSize + ", " + padSize
                + ")";
    }

    @Override
    public String limit(int offset, int limit) {
        return "LIMIT " + offset + "," + limit;
    }
}