ValidationSqliteUtil.java

package zserio.runtime.validation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

/**
 * Contains SQL utilities need for validation code generated by Zserio.
 */
public final class ValidationSqliteUtil
{
    /**
     * Describes the table column.
     */
    public static final class ColumnDescription
    {
        /**
         * Constructs table column description from all properties.
         *
         * @param name         Name of the column.
         * @param type         Column SQLite data type ("INTEGER", "REAL", "TEXT" or "BLOB").
         * @param isNotNull    true if column has "NOT NULL" constraint.
         * @param isPrimaryKey true if column is primary key.
         */
        public ColumnDescription(String name, String type, boolean isNotNull, boolean isPrimaryKey)
        {
            this.name = name;
            this.type = type;
            this.isNotNull = isNotNull;
            this.isPrimaryKey = isPrimaryKey;
        }

        /**
         * Gets the column name.
         *
         * @return Column name.
         */
        public String getName()
        {
            return name;
        }

        /**
         * Gets the column SQLite data type.
         *
         * @return Column SQLite data type ("INTEGER", "REAL", "TEXT" or "BLOB").
         */
        public String getType()
        {
            return type;
        }

        /**
         * Gets "NOT NULL" constaint flag of this column.
         *
         * @return true if column has "NOT NULL" constraint.
         */
        public boolean isNotNull()
        {
            return isNotNull;
        }

        /**
         * Gets primary key flag of this column.
         *
         * @return true if this column is primary key.
         */
        public boolean isPrimaryKey()
        {
            return isPrimaryKey;
        }

        private final String name;
        private final String type;
        private final boolean isNotNull;
        private final boolean isPrimaryKey;
    };

    /**
     * Returns a map of column names to column description for given SQLite table.
     *
     * @param connection     Database connection to use.
     * @param attachedDbName Attached database name if table is relocated in different database or null.
     * @param tableName      Name of the table to get column types of.
     *
     * @return Map of column names to column description.
     *
     * @throws SQLException Throws in case of any SQLite error.
     */
    public static Map<String, ColumnDescription> getTableSchema(
            Connection connection, String attachedDbName, String tableName) throws SQLException
    {
        Map<String, ColumnDescription> columnTypes = new HashMap<String, ColumnDescription>();

        // prepare SQL query
        final StringBuilder sqlQuery = new StringBuilder("PRAGMA ");
        if (attachedDbName != null)
        {
            sqlQuery.append(attachedDbName);
            sqlQuery.append('.');
        }
        sqlQuery.append("table_info(");
        sqlQuery.append(tableName);
        sqlQuery.append(")");

        // get table info
        try (final PreparedStatement statement = connection.prepareStatement(sqlQuery.toString());
                final ResultSet resultSet = statement.executeQuery();)
        {
            while (resultSet.next())
            {
                final String columnName = resultSet.getString(2);
                final String columnType = resultSet.getString(3);
                final boolean isNullable = resultSet.getBoolean(4);
                final int primaryKeyIndex = resultSet.getInt(6);
                columnTypes.put(columnName,
                        new ColumnDescription(columnName, columnType, isNullable, primaryKeyIndex != 0));
            }
        }

        return columnTypes;
    }

    /**
     * Checks if hidden column exits in given SQLite table.
     *
     * @param connection       Database connection to use.
     * @param attachedDbName   Attached database name if table is relocated in different database or null.
     * @param tableName        Name of the table where to check hidden column.
     * @param hiddenColumnName Name of hidden column to check.
     *
     * @return true if hidden column exists in given SQLite table.
     */
    public static boolean isHiddenColumnInTable(
            Connection connection, String attachedDbName, String tableName, String hiddenColumnName)
    {
        // prepare SQL query
        final StringBuilder sqlQuery = new StringBuilder("SELECT ");
        sqlQuery.append(hiddenColumnName);
        sqlQuery.append(" FROM ");
        if (attachedDbName != null)
        {
            sqlQuery.append(attachedDbName);
            sqlQuery.append('.');
        }
        sqlQuery.append(tableName);
        sqlQuery.append(" LIMIT 0");

        // try select to check if hidden column exists
        try (final PreparedStatement statement = connection.prepareStatement(sqlQuery.toString()))
        {
            return statement.execute();
        }
        catch (SQLException exception)
        {
            return false;
        }
    }

    /**
     * Converts SQL type returned by ResultSetMetaData.getColumnType.
     *
     * It converts only types which belongs to one of Types.INTEGER, Types.REAL, Types.VARCHAR, Types.BLOB or
     * Types.NULL to SQLite type. All others types are left unchanged (possibly unknown for SQLite).
     *
     * @param columnType SQL column type returned by ResultSetMetaData.getColumnType().
     *
     * @return SQLite column type.
     */
    public static int sqlTypeToSqliteType(int columnType)
    {
        // Zserio supports only INTEGER, REAL, VARCHAR and BLOB,
        // however we check also other cases in order to support different versions of Xerial JDBC driver
        switch (columnType)
        {
        case Types.BOOLEAN:
        case Types.TINYINT:
        case Types.SMALLINT:
        case Types.BIGINT: // returned only by older versions of Xerial
            return Types.INTEGER;
        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
            return Types.REAL;
        case Types.CHAR:
        case Types.CLOB:
            return Types.VARCHAR;
        case Types.BINARY:
            return Types.BLOB;
        default:
            return columnType;
        }
    }

    /**
     * Gets name of the given SQLite column type.
     *
     * Note that Zserio supports only INTEGER, REAL, VARCHAR ("TEXT") and BLOB.
     *
     * Latest Xerial JDBC driver uses sqlite3_column_decltype in ResultSetMetaData.getColumnTypeName
     * and thus doesn't reflect current dynamic type and therefore we cannon use it.
     *
     * @param columnType SQLite column type returned from sqlTypeToSqliteType.
     *
     * @return SQLite column type name.
     */
    public static String sqliteColumnTypeName(int columnType)
    {
        switch (columnType)
        {
        case Types.INTEGER:
            return "INTEGER";
        case Types.REAL:
            return "REAL";
        case Types.VARCHAR:
            return "TEXT";
        case Types.BLOB:
            return "BLOB";
        case Types.NULL:
            return "NULL";
        default:
            return "UNKNOWN (" + columnType + ")";
        }
    }
}