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 + ")";

    }

}