Dépôt officiel du code source de l'ERP OpenConcerto

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.sql.model;

import org.openconcerto.sql.model.SQLField.Properties;
import org.openconcerto.sql.model.graph.TablesMap;
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
import org.openconcerto.sql.utils.SQLUtils;
import org.openconcerto.utils.CollectionUtils;
import org.openconcerto.utils.ListMap;
import org.openconcerto.utils.Tuple2;
import org.openconcerto.utils.cc.ITransformer;
import org.openconcerto.utils.cc.Transformer;
import org.openconcerto.xml.XMLCodecUtils;

import java.beans.DefaultPersistenceDelegate;
import java.io.File;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.IdentityHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.h2.constant.ErrorCode;

public class SQLSyntaxH2 extends SQLSyntax {

    static protected final IdentityHashMap<String, String> DATE_SPECS;

    static {
        DATE_SPECS = new IdentityHashMap<String, String>(DateProp.JAVA_DATE_SPECS_PURE);
        DATE_SPECS.put(DateProp.MICROSECOND, "SSS000");

        XMLCodecUtils.register(SQLSyntaxH2.class, new DefaultPersistenceDelegate(new String[] {}));
    }

    final static SQLSyntax create(DBSystemRoot sysRoot) {
        boolean standardArray = true;
        if (sysRoot != null) {
            try {
                standardArray = Arrays.equals(new String[] { "foo" }, (Object[]) sysRoot.getDataSource().executeScalar("SELECT ARRAY['foo']"));
            } catch (RuntimeException e) {
                if (SQLUtils.findWithSQLState(e).getErrorCode() == ErrorCode.COLUMN_NOT_FOUND_1)
                    standardArray = false;
                else
                    throw e;
            }
        }
        return new SQLSyntaxH2(standardArray);
    }

    private final boolean standardArray;

    public SQLSyntaxH2(final boolean standardArray) {
        super(SQLSystem.H2, DATE_SPECS);
        this.standardArray = standardArray;
        this.typeNames.addAll(Boolean.class, "boolean", "bool", "bit");
        this.typeNames.addAll(Integer.class, "integer", "int", "int4", "mediumint");
        this.typeNames.addAll(Byte.class, "tinyint");
        this.typeNames.addAll(Short.class, "smallint", "int2");
        this.typeNames.addAll(Long.class, "bigint", "int8");
        this.typeNames.addAll(BigDecimal.class, "decimal", "numeric", "number");
        this.typeNames.addAll(Float.class, "real");
        this.typeNames.addAll(Double.class, "double precision", "float", "float4", "float8");
        this.typeNames.addAll(Timestamp.class, "timestamp", "smalldatetime", "datetime");
        this.typeNames.addAll(java.sql.Date.class, "date");
        this.typeNames.addAll(java.sql.Time.class, "time");
        this.typeNames.addAll(Blob.class, "blob", "tinyblob", "mediumblob", "longblob", "image",
                // byte[]
                "bytea", "raw", "varbinary", "longvarbinary", "binary");
        this.typeNames.addAll(Clob.class, "clob", "text", "tinytext", "mediumtext", "longtext");
        this.typeNames.addAll(String.class, "varchar", "longvarchar", "char", "character", "CHARACTER VARYING");
    }

    @Override
    public int getMaximumIdentifierLength() {
        // http://www.h2database.com/html/advanced.html#limits_limitations
        return Short.MAX_VALUE;
    }

    @Override
    public String getIDType() {
        return " int";
    }

    @Override
    public int getMaximumVarCharLength() {
        // http://www.h2database.com/html/datatypes.html#varchar_type
        return Integer.MAX_VALUE;
    }

    @Override
    public boolean isAuto(SQLField f) {
        if (f.getDefaultValue() == null)
            return false;

        final String def = f.getDefaultValue().toUpperCase();
        // we used to use IDENTITY which translate to long
        return (f.getType().getJavaType() == Integer.class || f.getType().getJavaType() == Long.class) && def.contains("NEXT VALUE") && def.contains("SYSTEM_SEQUENCE");
    }

    @Override
    public String getAuto() {
        // IDENTITY means long
        return " SERIAL";
    }

    @Override
    public String disableFKChecks(DBRoot b) {
        return "SET REFERENTIAL_INTEGRITY FALSE ;";
    }

    @Override
    public String enableFKChecks(DBRoot b) {
        return "SET REFERENTIAL_INTEGRITY TRUE ;";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> normalizeIndexInfo(final Map m) {
        // NON_UNIQUE is a boolean, COLUMN_NAME has a non-quoted name
        return m;
    }

    @Override
    public String getDropIndex(String name, SQLName tableName) {
        return "DROP INDEX IF EXISTS " + SQLBase.quoteIdentifier(name) + ";";
    }

    protected String setNullable(SQLField f, boolean b) {
        return "ALTER COLUMN " + f.getQuotedName() + " SET " + (b ? "" : "NOT") + " NULL";
    }

    @Override
    public Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {
        final List<String> res = new ArrayList<String>();
        if (toAlter.contains(Properties.TYPE)) {
            // MAYBE implement AlterTableAlterColumn.CHANGE_ONLY_TYPE
            final String newDef = toAlter.contains(Properties.DEFAULT) ? defaultVal : getDefault(f, type);
            final boolean newNullable = toAlter.contains(Properties.NULLABLE) ? nullable : getNullable(f);
            final SQLName seqName = f.getOwnedSequence();
            // sequence is used for the default so if default change, remove it (same behaviour than
            // H2)
            final String seqSQL = seqName == null || toAlter.contains(Properties.DEFAULT) ? "" : " SEQUENCE " + seqName.quote();
            res.add("ALTER COLUMN " + f.getQuotedName() + " " + getFieldDecl(type, newDef, newNullable) + seqSQL);
        } else {
            if (toAlter.contains(Properties.DEFAULT))
                res.add(this.setDefault(f, defaultVal));
        }
        // Contrary to the documentation "alter column type" doesn't change the nullable
        // e.g. ALTER COLUMN "VARCHAR" varchar(150) DEFAULT 'testAllProps' NULL
        if (toAlter.contains(Properties.NULLABLE))
            res.add(this.setNullable(f, nullable));
        return ListMap.singleton(ClauseType.ALTER_COL, res);
    }

    @Override
    public String getDropRoot(String name) {
        return "DROP SCHEMA IF EXISTS " + SQLBase.quoteIdentifier(name) + " ;";
    }

    @Override
    public String getCreateRoot(String name) {
        return "CREATE SCHEMA " + SQLBase.quoteIdentifier(name) + " ;";
    }

    @Override
    public String transfDefaultJDBC2SQL(SQLField f) {
        String res = f.getDefaultValue();
        if (res != null && f.getType().getJavaType() == String.class && res.trim().toUpperCase().startsWith("STRINGDECODE")) {
            // MAYBE create an attribute with a mem h2 db, instead of using db of f
            res = (String) f.getTable().getBase().getDataSource().executeScalar("CALL " + res);
            // this will be given to other db system, so don't use base specific quoting
            res = SQLBase.quoteStringStd(res);
        }
        return res;
    }

    @Override
    protected Tuple2<Boolean, String> getCast() {
        return Tuple2.create(true, " ");
    }

    @Override
    public void _loadData(final File f, final SQLTable t) {
        checkServerLocalhost(t);
        final String quotedPath = quoteString(f.getAbsolutePath());
        t.getDBSystemRoot().getDataSource().execute("insert into " + t.getSQLName().quote() + " select * from CSVREAD(" + quotedPath + ", NULL, 'UTF8', ',', '\"', '\\', '\\N') ;");
    }

    @Override
    protected void _storeData(final SQLTable t, final File f) {
        checkServerLocalhost(t);
        final String quotedPath = quoteString(f.getAbsolutePath());
        final String quotedSel = quoteString(SQLSyntaxPG.selectAll(t).asString());
        t.getBase().getDataSource().execute("CALL CSVWRITE(" + quotedPath + ", " + quotedSel + ", 'UTF8', ',', '\"', '\\', '\\N', '\n');");
    }

    @Override
    public String getCreateSynonym(SQLTable t, SQLName newName) {
        return null;
    }

    @Override
    public boolean supportMultiAlterClause() {
        return false;
    }

    @Override
    public String getDayOfWeek(String sqlTS) {
        return "DAY_OF_WEEK(" + sqlTS + ")";
    }

    @Override
    public String getFormatTimestamp(String sqlTS, boolean basic) {
        return this.getFormatTimestamp(sqlTS, SQLBase.quoteStringStd(basic ? TS_BASIC_JAVA_FORMAT : TS_EXTENDED_JAVA_FORMAT));
    }

    @Override
    public final String getFormatTimestamp(String sqlTS, String format) {
        return "FORMATDATETIME(" + sqlTS + ", " + format + ")";
    }

    @Override
    public String quoteForTimestampFormat(String text) {
        return SQLBase.quoteStringStd(text);
    }

    // (SELECT "C1" as "num", "C2" as "name" FROM VALUES(1, 'Hello'), (2, 'World')) AS V;
    @Override
    public String getConstantTable(List<List<String>> rows, String alias, List<String> columnsAlias) {
        // TODO submit a bug report to ask for V("num", "name") notation
        final StringBuilder sb = new StringBuilder();
        sb.append("( SELECT ");
        final int colCount = columnsAlias.size();
        for (int i = 0; i < colCount; i++) {
            sb.append(SQLBase.quoteIdentifier("C" + (i + 1)));
            sb.append(" as ");
            sb.append(SQLBase.quoteIdentifier(columnsAlias.get(i)));
            sb.append(", ");
        }
        // remove last ", "
        sb.setLength(sb.length() - 2);
        sb.append(" FROM ");
        sb.append(this.getValues(rows, colCount));
        sb.append(" ) AS ");
        sb.append(SQLBase.quoteIdentifier(alias));
        return sb.toString();
    }

    @Override
    public String getFunctionQuery(SQLBase b, Set<String> schemas) {
        // src can be null since H2 supports alias to Java static functions
        // perhaps join on FUNCTION_COLUMNS to find out parameters' types
        final String src = "coalesce(\"SOURCE\", \"JAVA_CLASS\" || '.' || \"JAVA_METHOD\" ||' parameter(s): ' || \"COLUMN_COUNT\")";
        return "SELECT ALIAS_SCHEMA as \"schema\", ALIAS_NAME as \"name\", " + src + " as \"src\" FROM \"INFORMATION_SCHEMA\".FUNCTION_ALIASES where ALIAS_CATALOG=" + this.quoteString(b.getMDName())
                + " and ALIAS_SCHEMA in (" + quoteStrings(schemas) + ")";
    }

    @Override
    public String getTriggerQuery(SQLBase b, TablesMap tables) {
        return "SELECT \"TRIGGER_NAME\", \"TABLE_SCHEMA\", \"TABLE_NAME\", \"JAVA_CLASS\" as \"ACTION\", \"SQL\" from INFORMATION_SCHEMA.TRIGGERS " + getTablesMapJoin(tables) + " where "
                + getInfoSchemaWhere(b);
    }

    private String getTablesMapJoin(final TablesMap tables) {
        return getTablesMapJoin(tables, SQLBase.quoteIdentifier("TABLE_SCHEMA"), SQLBase.quoteIdentifier("TABLE_NAME"));
    }

    private final String getInfoSchemaWhere(SQLBase b) {
        return "\"TABLE_CATALOG\" = " + this.quoteString(b.getMDName());
    }

    @Override
    public String getColumnsQuery(SQLBase b, TablesMap tables) {
        return "SELECT \"" + INFO_SCHEMA_NAMES_KEYS.get(0) + "\", \"" + INFO_SCHEMA_NAMES_KEYS.get(1) + "\", \"" + INFO_SCHEMA_NAMES_KEYS.get(2)
                + "\" , \"CHARACTER_SET_NAME\", \"COLLATION_NAME\", \"SEQUENCE_NAME\" from INFORMATION_SCHEMA.\"COLUMNS\" " + getTablesMapJoin(tables) + " where " + getInfoSchemaWhere(b);
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException {
        final String sel = "SELECT \"TABLE_SCHEMA\", \"TABLE_NAME\", \"CONSTRAINT_NAME\", \n"
                //
                + "case \"CONSTRAINT_TYPE\"  when 'REFERENTIAL' then 'FOREIGN KEY' else \"CONSTRAINT_TYPE\" end as \"CONSTRAINT_TYPE\", \"COLUMN_LIST\", \"CHECK_EXPRESSION\" AS \"DEFINITION\"\n"
                //
                + "FROM INFORMATION_SCHEMA.CONSTRAINTS " + getTablesMapJoin(tables)
                // where
                + " where " + getInfoSchemaWhere(b);
        // don't cache since we don't listen on system tables
        final List<Map<String, Object>> res = (List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
        for (final Map<String, Object> m : res) {
            // FIXME change h2 to use ValueArray in MetaTable to handle names with ','
            // new ArrayList otherwise can't be encoded to XML
            m.put("COLUMN_NAMES", new ArrayList<String>(SQLRow.toList((String) m.remove("COLUMN_LIST"))));
        }
        return res;
    }

    @Override
    public String getDropTrigger(Trigger t) {
        return "DROP TRIGGER " + new SQLName(t.getTable().getSchema().getName(), t.getName()).quote();
    }

    @Override
    public String getUpdate(SQLTable t, List<String> tables, Map<String, String> setPart) throws UnsupportedOperationException {
        if (tables.size() > 0)
            throw new UnsupportedOperationException();
        return super.getUpdate(t, tables, setPart);
    }

    @Override
    public boolean isDeadLockException(SQLException exn) {
        final SQLException stateExn = SQLUtils.findWithSQLState(exn);
        // in H2 deadlock is only detected at the table level (e.g DDL)
        // in MVCC, if two transactions modify the same row the second one will repeatedly throw
        // CONCURRENT_UPDATE_1 until LockTimeout
        // otherwise, the second one will timeout while waiting for the table lock
        return stateExn.getErrorCode() == ErrorCode.DEADLOCK_1 || stateExn.getErrorCode() == ErrorCode.LOCK_TIMEOUT_1;
    }

    @Override
    public boolean isTableNotFoundException(Exception exn) {
        final SQLException stateExn = SQLUtils.findWithSQLState(exn);
        return stateExn.getErrorCode() == ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1 || stateExn.getErrorCode() == ErrorCode.SCHEMA_NOT_FOUND_1;
    }

    @Override
    public String getSessionIDExpression() {
        return "SESSION_ID()";
    }

    @Override
    public String getSessionsQuery(final DBSystemRoot sysRoot, final boolean includeSelf) {
        final String allRows = "SELECT \"ID\", \"STATEMENT\" as \"QUERY\", \"USER_NAME\" FROM INFORMATION_SCHEMA.SESSIONS";
        if (includeSelf)
            return allRows;
        return allRows + " WHERE \"ID\" != " + this.getSessionIDExpression();
    }

    @Override
    public String getVersionFunction() {
        return "H2VERSION()";
    }

    @Override
    public String getAllowConnectionsQuery(String sysRootName, final boolean allow) {
        return "SET EXCLUSIVE " + (allow ? "0" : "1");
    }

    @Override
    public boolean isConnectionDisallowedException(SQLException exn) {
        return exn.getErrorCode() == ErrorCode.DATABASE_IS_IN_EXCLUSIVE_MODE;
    }

    @Override
    public String getSQLArray(final List<String> sqlExpressions, final String type) {
        // H2 cannot specify type in SQL, so cast each item
        final ITransformer<? super String, ?> tf = type == null ? Transformer.nopTransformer() : (s) -> {
            return this.cast(s, type);
        };
        final String items = CollectionUtils.join(sqlExpressions, ", ", tf);
        if (this.standardArray) {
            return "ARRAY[" + items + ']';
        } else {
            // For the comma, see http://h2database.com/html/grammar.html#array
            return '(' + items + (sqlExpressions.size() == 1 ? ",)" : ")");
        }
    }

    @Override
    public String getSQLArrayContains(String arrayExpression, String itemExpression) {
        return "ARRAY_CONTAINS(" + arrayExpression + ", " + itemExpression + ")";
    }

    @Override
    public String getSQLArrayLength(final String arrayExpression) {
        return "ARRAY_LENGTH(" + arrayExpression + ")";
    }

    @Override
    public String getSQLArrayConcat(final String arrayExpression, final String array2Expression) {
        return "array_cat(" + arrayExpression + ", " + array2Expression + ")";
    }

    @Override
    public String getSQLArrayAppend(final String arrayExpression, final String itemExpression) {
        return "array_append(" + arrayExpression + ", " + itemExpression + ")";
    }

    @Override
    public String getSQLArraySlice(final String arrayExpression, final String index1Expression, final String index2Expression) {
        return "ARRAY_SLICE(" + arrayExpression + ", " + index1Expression + ", " + index2Expression + ")";
    }
}