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.utils.SQL_URL;
import org.openconcerto.utils.EnumOrderedSet;
import org.openconcerto.utils.Tuple2;
import org.openconcerto.utils.Tuple2.List2;
import org.openconcerto.utils.cc.ITransformer;

import java.sql.Statement;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

import org.h2.engine.ConnectionInfo;
import org.h2.engine.Constants;
import org.h2.util.StringUtils;

/**
 * A RDBMS like PostgreSQL or MySQL.
 * 
 * @author Sylvain
 */
public enum SQLSystem {

    /**
     * The PostgreSQL database. The required version is 8.2 since "drop schema if exists" and
     * "insert returning" are needed.
     * 
     * @see <a href="http://www.postgresql.org/">PostgreSQL site</a>
     */
    POSTGRESQL("PostgreSQL") {
        @Override
        void removeRootsToIgnore(Set<String> s) {
            super.removeRootsToIgnore(s);
            final Iterator<String> iter = s.iterator();
            while (iter.hasNext()) {
                final String r = iter.next();
                if (r.startsWith("pg_"))
                    iter.remove();
            }
        }

        @Override
        public boolean isClearingPathSupported() {
            return true;
        }

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

        @Override
        public boolean isIndexFilterConditionSupported() {
            return true;
        }

        @Override
        public boolean isSequencesSupported() {
            return true;
        }
    },

    /**
     * The MySQL database. Necessary server configuration :
     * <dl>
     * <dt>sql_mode = 'ANSI'</dt>
     * <dd>to allow standard SQL (syntax like " and || ; real as float)</dd>
     * <dt>lower_case_table_names = 0</dt>
     * <dd>to allow tables with mixed case</dd>
     * </dl>
     * 
     * @see <a href="http://www.mysql.com/">MySQL site</a>
     */
    MYSQL("MySQL") {
        @Override
        EnumSet<HierarchyLevel> createLevels() {
            // mysql has no schema
            return EnumSet.complementOf(EnumSet.of(HierarchyLevel.SQLSCHEMA));
        }

        @Override
        void removeRootsToIgnore(Set<String> s) {
            super.removeRootsToIgnore(s);
            s.remove("mysql");
            s.remove("performance_schema");
            // before 5.5.8
            s.remove("PERFORMANCE_SCHEMA");
        }

        @Override
        public boolean isInterBaseSupported() {
            // since jdbc://127.0.0.1/Ideation_2007 can reach jdbc://127.0.0.1/Gestion
            return true;
        }

        @Override
        public boolean isDBPathEmpty() {
            // since ds is now on SystemRoot ie jdbc://127.0.0.1/
            return true;
        }

        @Override
        public boolean isFractionalSecondsSupported() {
            // see http://forge.mysql.com/worklog/task.php?id=946
            return false;
        }

        @Override
        public boolean isTablesCommentSupported() {
            // comments are supported in MySQL but JDBC doesn't return them
            // (for now it uses "show tables" although they are in information_schema."TABLES")
            return false;
        }
    },

    /**
     * The H2 database.
     * 
     * @see <a href="http://www.h2database.com/">H2 site</a>
     */
    H2("H2") {

        private static final String TCP_PREFIX = "tcp://";
        private static final String ARBITRARY_BASE_NAME = "foo";

        ITransformer<String, String> getURLTransf(final SQLServer s) {
            if (s.getSQLSystem() != this)
                throw new IllegalArgumentException(s + " is not " + this);

            return new ITransformer<String, String>() {
                @Override
                public String transformChecked(String base) {
                    // by default h2 convert database name to upper case (we used to work around it
                    // with SQLSystem.getMDName() but in r2251 an equalsIgnoreCase() was replaced by
                    // equals()) see http://code.google.com/p/h2database/issues/detail?id=204
                    return s.getName() + base + ";DATABASE_TO_UPPER=false";
                }
            };
        }

        @Override
        public boolean isClearingPathSupported() {
            // TODO see if SCHEMA_SEARCH_PATH can be passed an empty list
            // (in addition to merge with SCHEMA)
            return false;
        }

        @Override
        public boolean isMultipleResultSetsSupported() {
            // https://groups.google.com/d/msg/h2-database/Is91FqarxDw/5x-xW3_IPwUJ
            return false;
        }

        @Override
        public boolean isSequencesSupported() {
            return true;
        }

        @Override
        public String getServerName(final String host) {
            return TCP_PREFIX + host + "/";
        }

        @Override
        public List2<String> getHostnameAndPath(final String server) {
            // append base name to server name to get a valid value
            final ConnectionInfo info = new ConnectionInfo(server + ARBITRARY_BASE_NAME);
            final String name = info.getName();
            final String hostName;
            final int pathIndex;
            if (info.isRemote()) {
                // tcp:// or ssl://server/path
                assert name.startsWith("//");
                final int slashIndex = name.indexOf('/', 2);
                hostName = name.substring(2, slashIndex);
                pathIndex = slashIndex + 1;
            } else {
                // mem: or file:/data/sample or ~/test
                hostName = null;
                pathIndex = 0;
            }

            return new List2<>(hostName, name.substring(pathIndex, name.length() - ARBITRARY_BASE_NAME.length()));
        }

        @Override
        public boolean isPermanent(final String server) {
            return !server.startsWith(H2_IN_MEMORY);
        }

        @Override
        public Map<String, String> getConnectionInfo(final String url) {
            final Tuple2<String, Map<String, String>> settings = readSettingsFromURL(url);
            final Map<String, String> res = new HashMap<String, String>();
            // TODO other settings are ignored
            res.put("root", settings.get1().get(StringUtils.toUpperEnglish("SCHEMA")));
            res.put("table", settings.get1().get(StringUtils.toUpperEnglish("TABLE")));
            res.put("login", settings.get1().get(StringUtils.toUpperEnglish("USER")));
            res.put("pass", settings.get1().get(StringUtils.toUpperEnglish("PASSWORD")));

            // remove mem:, tcp:, etc
            final String name = settings.get0();
            final int prefix = name.indexOf(':');
            final int lastSlash = name.lastIndexOf('/');
            final String sysRoot = lastSlash < 0 ? name.substring(prefix + 1) : name.substring(lastSlash + 1);
            res.put("systemRoot", sysRoot);
            res.put("name", name.substring(0, name.length() - sysRoot.length()));

            return res;
        }

        // pasted from org.h2.engine.ConnectionInfo
        private Tuple2<String, Map<String, String>> readSettingsFromURL(final String origURL) throws IllegalArgumentException {
            String url = origURL;
            final Map<String, String> prop = new HashMap<String, String>();
            final int idx = url.indexOf(';');
            if (idx >= 0) {
                String settings = url.substring(idx + 1);
                url = url.substring(0, idx);
                String[] list = StringUtils.arraySplit(settings, ';', false);
                for (String setting : list) {
                    int equal = setting.indexOf('=');
                    if (equal < 0) {
                        throw new IllegalArgumentException("format error, missing =" + url);
                    }
                    String value = setting.substring(equal + 1);
                    String key = setting.substring(0, equal);
                    key = StringUtils.toUpperEnglish(key);

                    final String old = prop.get(key);
                    if (old != null && !old.equals(value)) {
                        throw new IllegalArgumentException("DUPLICATE_PROPERTY " + key + " in " + url);
                    }
                    prop.put(key, value);
                }
            }
            return Tuple2.create(url.substring(Constants.START_URL.length()), prop);
        }

    },
    MSSQL("Microsoft SQL Server") {
        @Override
        public String getJDBCName() {
            return "sqlserver";
        }

        @Override
        ITransformer<String, String> getURLTransf(final SQLServer s) {
            return new ITransformer<String, String>() {
                @Override
                public String transformChecked(String base) {
                    return "//" + s.getName() + ";databaseName=" + base;
                }
            };
        }

        @Override
        void removeRootsToIgnore(Set<String> s) {
            super.removeRootsToIgnore(s);
            final Iterator<String> iter = s.iterator();
            while (iter.hasNext()) {
                final String r = iter.next();
                if (r.startsWith("db_") || r.equals("sys"))
                    iter.remove();
            }
        }

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

        @Override
        public boolean isIndexFilterConditionSupported() {
            return true;
        }

        @Override
        public boolean isTablesCommentSupported() {
            // comments are not directly supported in MS, see sp_addextendedproperty
            // 'MS_Description' :
            // http://stackoverflow.com/questions/378700/is-it-possible-to-add-a-description-comment-to-a-table-in-microsoft-sql-2000
            return false;
        }
    },
    DERBY("Apache Derby");

    public static SQLSystem get(String name) {
        final String normalized = name.toUpperCase();
        try {
            return SQLSystem.valueOf(normalized);
        } catch (IllegalArgumentException e) {
            // synonyms
            if (normalized.equals("PSQL"))
                return POSTGRESQL;
            else
                throw e;
        }
    }

    public static final String H2_IN_MEMORY = "mem:";

    private final String label;
    private final EnumOrderedSet<HierarchyLevel> levels;

    private SQLSystem(final String label) {
        this.label = label;
        this.levels = new EnumOrderedSet<HierarchyLevel>(this.createLevels());
    }

    /**
     * The string to use in jdbc urls.
     * 
     * @return the jdbc string for this.
     */
    public String getJDBCName() {
        return this.name().toLowerCase();
    }

    public final String getLabel() {
        return this.label;
    }

    EnumSet<HierarchyLevel> createLevels() {
        return EnumSet.allOf(HierarchyLevel.class);
    }

    public final EnumOrderedSet<HierarchyLevel> getLevels() {
        return this.levels;
    }

    /**
     * The number of levels between the parameters.
     * 
     * @param clazz1 the start structure item class, e.g. {@link SQLTable}.
     * @param clazz2 the destination structure item class, e.g. {@link SQLSchema} or {@link DBRoot}.
     * @return the distance between parameters, e.g. -1.
     */
    public final int getHops(Class<? extends DBStructureItem<?>> clazz1, Class<? extends DBStructureItem<?>> clazz2) {
        final EnumOrderedSet<HierarchyLevel> levels;
        if (DBStructureItemDB.class.isAssignableFrom(clazz1) || DBStructureItemDB.class.isAssignableFrom(clazz2))
            levels = this.getLevels();
        else
            levels = HierarchyLevel.getAll();
        return levels.getHops(this.getLevel(clazz1), this.getLevel(clazz2));
    }

    /**
     * The level of the root for this system, ie the level above {@link HierarchyLevel#SQLTABLE}.
     * 
     * @return level of the root.
     */
    public final HierarchyLevel getDBRootLevel() {
        return this.getLevels().getPrevious(HierarchyLevel.SQLTABLE);
    }

    public final HierarchyLevel getDBLevel(Class<? extends DBStructureItemDB> clazz) {
        if (clazz.equals(DBRoot.class))
            return this.getDBRootLevel();
        else if (clazz.equals(DBSystemRoot.class))
            return this.getLevels().getPrevious(this.getDBRootLevel());
        else
            throw new IllegalArgumentException(clazz + " should be either DBRoot or DBSystemRoot");
    }

    public final HierarchyLevel getLevel(Class<? extends DBStructureItem<?>> clazz) {
        if (DBStructureItemDB.class.isAssignableFrom(clazz))
            return this.getDBLevel(clazz.asSubclass(DBStructureItemDB.class));
        else
            return HierarchyLevel.get(clazz.asSubclass(DBStructureItemJDBC.class));
    }

    /**
     * Remove from <code>s</code> the database private roots, eg "information_schema".
     * 
     * @param s a set of roots names, that will be modified.
     */
    void removeRootsToIgnore(Set<String> s) {
        s.remove("information_schema");
        s.remove("INFORMATION_SCHEMA");
    }

    // result must be thread-safe
    ITransformer<String, String> getURLTransf(final SQLServer s) {
        if (s.getSQLSystem() != this)
            throw new IllegalArgumentException(s + " is not " + this);

        return new ITransformer<String, String>() {
            @Override
            public String transformChecked(String base) {
                return "//" + s.getName() + "/" + base;
            }
        };
    }

    /**
     * Return the server name for the passed host.
     * 
     * @param host an ip address or dns name, eg "foo".
     * @return the name of the server, eg "tcp://foo/".
     */
    public String getServerName(String host) {
        return host;
    }

    /**
     * The host name for the passed server.
     * 
     * @param server the name of an {@link SQLServer}, e.g. tcp://127.0.0.1/dir/.
     * @return its host and its path, both can be <code>null</code> (but not at the same time), e.g.
     *         [127.0.0.1, dir].
     */
    public List2<String> getHostnameAndPath(String server) {
        Objects.requireNonNull(server, "Null server");
        return new List2<>(server, null);
    }

    /**
     * Whether the passed server runs inside the VM.
     * 
     * @param server a server, e.g. {@link SQLServer#getName()}.
     * @return <code>true</code> if <code>server</code> runs inside the VM.
     */
    public final boolean isEmbedded(final String server) {
        return getHostnameAndPath(server).get0() == null;
    }

    /**
     * Whether the passed server survives when all connections to it are closed.
     * 
     * @param server a server, e.g. {@link SQLServer#getName()}.
     * @return <code>true</code> if <code>server</code> survives when all connections to it are
     *         closed.
     */
    public boolean isPermanent(String server) {
        return true;
    }

    /**
     * Parse <code>url</code> to find info needed by {@link SQL_URL}.
     * 
     * @param url a jdbc url, eg
     *        "jdbc:h2:mem:Controle;USER=maillard;PASSWORD=pass;SCHEMA=Ideation_2007;TABLE=TENSION".
     * @return a map containing login, pass, server name, and systemRoot, root, table.
     */
    public Map<String, String> getConnectionInfo(final String url) {
        throw new UnsupportedOperationException();
    }

    public final boolean isNoDefaultSchemaSupported() {
        return this.isClearingPathSupported() || this.isDBPathEmpty();
    }

    /**
     * Whether this can clear the path of an existing connection.
     * 
     * @return <code>true</code> if this can.
     */
    public boolean isClearingPathSupported() {
        return false;
    }

    /**
     * Whether a connection has an empty path by default, eg MySQL when connecting to 127.0.0.1.
     * 
     * @return <code>true</code> if it does.
     */
    public boolean isDBPathEmpty() {
        return false;
    }

    /**
     * Whether a table in one base can reference a table in another one.
     * 
     * @return <code>true</code> if eg base1.schema1.RENDEZVOUS can point to base2.schema1.CLIENT.
     */
    public boolean isInterBaseSupported() {
        return false;
    }

    /**
     * Whether this system automatically creates an index for each foreign key constraint.
     * 
     * @return <code>true</code> for this implementation.
     */
    public boolean autoCreatesFKIndex() {
        return true;
    }

    public boolean isIndexFilterConditionSupported() {
        return false;
    }

    public boolean isFractionalSecondsSupported() {
        return true;
    }

    public boolean isTablesCommentSupported() {
        return true;
    }

    /**
     * Whether more than one result set can be retrieved.
     * 
     * @return <code>true</code> if {@link Statement#getMoreResults()} is functional.
     */
    public boolean isMultipleResultSetsSupported() {
        return true;
    }

    public boolean isSequencesSupported() {
        return false;
    }

    public String getMDName(String name) {
        return name;
    }

    /**
     * The default syntax for this system. NOTE : when needing a syntax for a system currently
     * accessible, {@link DBSystemRoot#getSyntax()} should be used so that server options can be
     * read. Otherwise constructors of {@link SQLSyntax} subclasses should be used to specify
     * options.
     * 
     * @return the syntax for this system, or <code>null</code> if none exists.
     */
    public final SQLSyntax getSyntax() {
        try {
            return SQLSyntax.get(this);
        } catch (IllegalArgumentException e) {
            return null;
        }
    }
}