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.Link.Rule;

import org.openconcerto.sql.model.graph.TablesMap;

import org.openconcerto.sql.utils.ChangeTable.ClauseType;

import org.openconcerto.sql.utils.ChangeTable.DeferredClause;

import org.openconcerto.sql.utils.SQLUtils;

import org.openconcerto.utils.CollectionUtils;

import org.openconcerto.utils.FileUtils;

import org.openconcerto.utils.ListMap;

import org.openconcerto.utils.ProcessStreams;

import org.openconcerto.utils.RTInterruptedException;

import org.openconcerto.utils.StringUtils;

import org.openconcerto.utils.Tuple2;

import org.openconcerto.utils.cc.ITransformer;

import org.openconcerto.xml.XMLCodecUtils;

import java.beans.DefaultPersistenceDelegate;

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStreamReader;

import java.io.OutputStreamWriter;

import java.math.BigDecimal;

import java.nio.charset.Charset;

import java.sql.Blob;

import java.sql.Clob;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.sql.Types;

import java.util.Arrays;

import java.util.BitSet;

import java.util.IdentityHashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.regex.Pattern;

public class SQLSyntaxMS extends SQLSyntax {

    static private final IdentityHashMap<String, String> DATE_SPECS;

    static {

        DATE_SPECS = new IdentityHashMap<String, String>();

        DATE_SPECS.put(DateProp.YEAR, "yyyy");

        DATE_SPECS.put(DateProp.MONTH_NAME, "MMMM");

        DATE_SPECS.put(DateProp.MONTH_NUMBER, "MM");

        DATE_SPECS.put(DateProp.DAY_IN_MONTH, "dd");

        DATE_SPECS.put(DateProp.DAY_NAME_IN_WEEK, "dddd");

        DATE_SPECS.put(DateProp.HOUR, "HH");

        DATE_SPECS.put(DateProp.MINUTE, "mm");

        DATE_SPECS.put(DateProp.SECOND, "ss");

        DATE_SPECS.put(DateProp.MICROSECOND, "ffffff");

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

    }

    public SQLSyntaxMS() {

        super(SQLSystem.MSSQL, DATE_SPECS);

        this.typeNames.addAll(Boolean.class, "bit");

        // tinyint is unsigned

        this.typeNames.addAll(Short.class, "smallint", "tinyint");

        this.typeNames.addAll(Integer.class, "int");

        this.typeNames.addAll(Long.class, "bigint");

        this.typeNames.addAll(BigDecimal.class, "decimal", "numeric", "smallmoney", "money");

        this.typeNames.addAll(Float.class, "real");

        this.typeNames.addAll(Double.class, "double precision", "float");

        this.typeNames.addAll(Timestamp.class, "datetime2", "datetime", "smalldatetime");

        this.typeNames.addAll(java.sql.Date.class, "date");

        this.typeNames.addAll(java.sql.Time.class, "time");

        this.typeNames.addAll(Blob.class, "image",

                // byte[]

                "varbinary", "binary");

        this.typeNames.addAll(Clob.class, "text", "ntext", "unitext");

        this.typeNames.addAll(String.class, "char", "varchar", "nchar", "nvarchar", "unichar", "univarchar");

    }

    @Override

    public final String quoteString(String s) {

        final String res = super.quoteString(s);

        if (s == null)

            return res;

        // only use escape form if needed (=> equals with other systems most of the time)

        boolean simpleASCII = true;

        final int l = s.length();

        for (int i = 0; simpleASCII && i < l; i++) {

            final char c = s.charAt(i);

            simpleASCII = c <= 0xFF;

        }

        // see http://msdn.microsoft.com/fr-fr/library/ms191200(v=sql.105).aspx

        return simpleASCII ? res : "N" + res;

    }

    @Override

    public int getMaximumIdentifierLength() {

        // https://msdn.microsoft.com/en-us/library/ms143432.aspx

        return 128;

    }

    @Override

    public String getInitSystemRoot() {

        final String sql;

        try {

            final String fileContent = FileUtils.readUTF8(SQLSyntaxPG.class.getResourceAsStream("mssql-functions.sql"));

            sql = fileContent.replace("${rootName}", SQLBase.quoteIdentifier("dbo"));

        } catch (IOException e) {

            throw new IllegalStateException("cannot read functions", e);

        }

        return sql;

    }

    @Override

    public boolean isAuto(SQLField f) {

        return f.getType().getJavaType() == Integer.class && "YES".equals(f.getMetadata("IS_AUTOINCREMENT"));

    }

    @Override

    public String getAuto() {

        return " int IDENTITY";

    }

    @Override

    public int getMaximumVarCharLength() {

        // http://msdn.microsoft.com/en-us/library/ms176089(v=sql.105).aspx

        return 8000;

    }

    @Override

    public String transfDefaultJDBC2SQL(SQLField f) {

        final Object def = f.getDefaultValue();

        if (def == null)

            return null;

        // remove parentheses from ((1))

        String stringDef = def.toString();

        while (stringDef.charAt(0) == '(' && stringDef.charAt(stringDef.length() - 1) == ')')

            stringDef = stringDef.substring(1, stringDef.length() - 1);

        if (f.getType().getJavaType() == Boolean.class) {

            return stringDef.equals("'true'") ? "true" : "false";

        } else {

            return stringDef;

        }

    }

    @Override

    protected String transfDefault(SQLField f, String castless) {

        if (castless != null && f.getType().getJavaType() == Boolean.class) {

            // yes MS has no true/false keywords

            return castless.equals("TRUE") ? "'true'" : "'false'";

        } else

            return castless;

    }

    @Override

    protected String getRuleSQL(final Rule r) {

        // MSSQL doesn't support RESTRICT

        return (r.equals(Rule.RESTRICT) ? Rule.NO_ACTION : r).asString();

    }

    @Override

    public String disableFKChecks(DBRoot b) {

        return fkChecks(b, false);

    }

    private String fkChecks(final DBRoot b, final boolean enable) {

        final String s = enable ? "with check check constraint all" : "nocheck constraint all";

        return "exec sp_MSforeachtable @command1 = 'ALTER TABLE ? " + s + "' , @whereand = " +

        //

                quoteString("and schema_id = SCHEMA_ID( " + quoteString(b.getName()) + " )");

    }

    @Override

    public String enableFKChecks(DBRoot b) {

        return fkChecks(b, true);

    }

    @Override

    public List<Map<String, Object>> getIndexInfo(SQLTable t) throws SQLException {

        final String query = "SELECT NULL AS \"TABLE_CAT\", schema_name(t.schema_id) as \"TABLE_SCHEM\", t.name as \"TABLE_NAME\",\n" +

        //

                "~idx.is_unique as \"NON_UNIQUE\", NULL AS \"INDEX_QUALIFIER\", idx.name as \"INDEX_NAME\", NULL as \"TYPE\",\n" +

                //

                "indexCols.key_ordinal as \"ORDINAL_POSITION\", cols.name as \"COLUMN_NAME\",\n" +

                //

                "case when indexCols.is_descending_key = 1 then 'D' else 'A' end as \"ASC_OR_DESC\", null as \"CARDINALITY\", null as \"PAGES\",\n" +

                //

                "filter_definition as \"FILTER_CONDITION\"\n" +

                //

                "  FROM [test].[sys].[objects] t\n" +

                //

                "  join [test].[sys].[indexes] idx on idx.object_id = t.object_id\n" +

                //

                "  join [test].[sys].[index_columns] indexCols on idx.index_id = indexCols.index_id and idx.object_id = indexCols.object_id\n" +

                //

                "  join [test].[sys].[columns] cols on t.object_id = cols.object_id and cols.column_id = indexCols.column_id \n" +

                //

                "  where schema_name(t.schema_id) = " + quoteString(t.getSchema().getName()) + " and t.name = " + quoteString(t.getName()) + "\n"

                //

                + "ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\";";

        // don't cache since we don't listen on system tables

        return (List<Map<String, Object>>) t.getDBSystemRoot().getDataSource().execute(query, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));

    }

    @SuppressWarnings("unchecked")

    @Override

    public Map<String, Object> normalizeIndexInfo(final Map m) {

        // genuine MS driver

        if (getSystem().getJDBCName().equals("sqlserver"))

            m.put("NON_UNIQUE", ((Number) m.get("NON_UNIQUE")).intValue() != 0);

        return m;

    }

    @Override

    public String getDropIndex(String name, SQLName tableName) {

        return "DROP INDEX " + SQLBase.quoteIdentifier(name) + " on " + tableName.quote() + ";";

    }

    @Override

    public boolean isUniqueException(SQLException exn) {

        return SQLUtils.findWithSQLState(exn).getErrorCode() == 2601;

    }

    @Override

    public boolean isDeadLockException(SQLException exn) {

        return SQLUtils.findWithSQLState(exn).getErrorCode() == 1205;

    }

    @Override

    public boolean isTableNotFoundException(Exception exn) {

        // TODO

        throw new UnsupportedOperationException();

    }

    @Override

    public String getSetLockTimeoutQuery(int millis) {

        return "SET LOCK_TIMEOUT " + millis;

    }

    @Override

    public String getShowLockTimeoutQuery() {

        return "SELECT @@LOCK_TIMEOUT";

    }

    @Override

    public Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {

        final ListMap<ClauseType, String> res = new ListMap<ClauseType, String>();

        if (toAlter.contains(Properties.TYPE) || toAlter.contains(Properties.NULLABLE)) {

            final String newType = toAlter.contains(Properties.TYPE) ? type : getType(f);

            final boolean newNullable = toAlter.contains(Properties.NULLABLE) ? nullable : getNullable(f);

            res.add(ClauseType.ALTER_COL, "ALTER COLUMN " + f.getQuotedName() + " " + getFieldDecl(newType, null, newNullable));

        }

        if (toAlter.contains(Properties.DEFAULT)) {

            final Constraint existingConstraint = f.getTable().getConstraint(ConstraintType.DEFAULT, Arrays.asList(f.getName()));

            if (existingConstraint != null) {

                res.add(ClauseType.DROP_CONSTRAINT, "DROP CONSTRAINT " + SQLBase.quoteIdentifier(existingConstraint.getName()));

            }

            if (defaultVal != null) {

                res.add(ClauseType.ADD_CONSTRAINT, "ADD DEFAULT " + defaultVal + " FOR " + f.getQuotedName());

            }

        }

        return res;

    }

    @Override

    public String getRenameTable(SQLName table, String newName) {

        return "sp_rename " + SQLBase.quoteStringStd(table.quote()) + ", " + SQLBase.quoteStringStd(newName);

    }

    @Override

    public String getDropTable(SQLName name, boolean ifExists, boolean restrict) {

        // doesn't support cascade

        if (!restrict)

            return null;

        if (!ifExists) {

            return super.getDropTable(name, ifExists, restrict);

        } else {

            final String quoted = name.quote();

            return "IF OBJECT_ID(" + SQLBase.quoteStringStd(quoted) + ", 'U') IS NOT NULL DROP TABLE " + quoted;

        }

    }

    @Override

    public String getDropRoot(String name) {

        // Only works if getInitSystemRoot() was executed

        // http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/

        return "exec CleanUpSchema " + SQLBase.quoteStringStd(name) + ", 'w' ;";

    }

    @Override

    public String getCreateRoot(String name) {

        return "CREATE SCHEMA " + SQLBase.quoteIdentifier(name) + " ;";

    }

    @Override

    protected Tuple2<Boolean, String> getCast() {

        return null;

    }

    @Override

    public void _loadData(final File f, final SQLTable t) throws IOException {

        final String data = FileUtils.readUTF8(f);

        final File temp = File.createTempFile(FileUtils.sanitize("mssql_loadData_" + t.getName()), ".txt");

        // no we cant't use UTF16 since Java write BE and MS ignores the BOM, always using LE.

        final BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(temp), Charset.forName("x-UTF-16LE-BOM")));

        final List<SQLField> fields = t.getOrderedFields();

        final int fieldsCount = fields.size();

        final BitSet booleanFields = new BitSet(fieldsCount);

        int fieldIndex = 0;

        for (final SQLField field : fields) {

            final int type = field.getType().getType();

            booleanFields.set(fieldIndex++, type == Types.BOOLEAN || type == Types.BIT);

        }

        fieldIndex = 0;

        try {

            // skip fields names

            int i = data.indexOf('\n') + 1;

            while (i < data.length()) {

                final String twoChars = i + 2 <= data.length() ? data.substring(i, i + 2) : null;

                if ("\\N".equals(twoChars)) {

                    i += 2;

                } else if ("\"\"".equals(twoChars)) {

                    writer.write("\0");

                    i += 2;

                } else {

                    final Tuple2<String, Integer> unDoubleQuote = StringUtils.unDoubleQuote(data, i);

                    String unquoted = unDoubleQuote.get0();

                    if (booleanFields.get(fieldIndex)) {

                        if (unquoted.equalsIgnoreCase("false")) {

                            unquoted = "0";

                        } else if (unquoted.equalsIgnoreCase("true")) {

                            unquoted = "1";

                        }

                    }

                    writer.write(unquoted);

                    i = unDoubleQuote.get1();

                }

                fieldIndex++;

                if (i < data.length()) {

                    final char c = data.charAt(i);

                    if (c == ',') {

                        writer.write(FIELD_DELIM);

                        i++;

                    } else if (c == '\n') {

                        writer.write(ROW_DELIM);

                        i++;

                        if (fieldIndex != fieldsCount)

                            throw new IOException("Expected " + fieldsCount + " fields but got : " + fieldIndex);

                        fieldIndex = 0;

                    } else {

                        throw new IOException("Unexpected character after field : " + c);

                    }

                }

            }

            if (fieldIndex != 0 && fieldIndex != fieldsCount)

                throw new IOException("Expected " + fieldsCount + " fields but got : " + fieldIndex);

        } finally {

            writer.close();

        }

        execute_bcp(t, false, temp);

        temp.delete();

        // MAYBE when on localhost, remove the bcp requirement (OTOH bcp should already be

        // installed, just perhaps not in the path)

        // checkServerLocalhost(t);

        // "bulk insert " + t.getSQL() + " from " + b.quoteString(temp.getAbsolutePath()) +

        // " with ( DATAFILETYPE='widechar', FIELDTERMINATOR = " + b.quoteString(FIELD_DELIM)

        // + ", ROWTERMINATOR= " + b.quoteString(ROW_DELIM) +

        // ", FIRSTROW=1, KEEPIDENTITY, KEEPNULLS ) ;"

    }

    private static final String FIELD_DELIM = "<|!!|>";

    private static final String ROW_DELIM = "...#~\n~#...";

    protected void execute_bcp(final SQLTable t, final boolean dump, final File f) throws IOException {

        final ProcessBuilder pb = new ProcessBuilder("bcp");

        pb.command().add(t.getSQLName().quote());

        pb.command().add(dump ? "out" : "in");

        pb.command().add(f.getAbsolutePath());

        // UTF-16LE with a BOM

        pb.command().add("-w");

        pb.command().add("-t" + FIELD_DELIM);

        pb.command().add("-r" + ROW_DELIM);

        // needed if table name is a keyword (e.g. RIGHT)

        pb.command().add("-q");

        pb.command().add("-S" + t.getServer().getName());

        pb.command().add("-U" + t.getDBSystemRoot().getDataSource().getUsername());

        pb.command().add("-P" + t.getDBSystemRoot().getDataSource().getPassword());

        if (!dump) {

            // retain null

            pb.command().add("-k");

            // keep identity

            pb.command().add("-E");

        }

        final Process p = ProcessStreams.redirect(pb).start();

        try {

            final int returnCode = p.waitFor();

            if (returnCode != 0)

                throw new IOException("Did not finish correctly : " + returnCode + "\n" + pb.command());

        } catch (InterruptedException e) {

            throw new RTInterruptedException(e);

        }

    }

    // For bcp : http://www.microsoft.com/en-us/download/details.aspx?id=16978

    @Override

    protected void _storeData(final SQLTable t, final File f) throws IOException {

        final File tmpFile = File.createTempFile(FileUtils.sanitize("mssql_dump_" + t.getName()), ".dat");

        execute_bcp(t, true, tmpFile);

        final int readerBufferSize = 32768;

        final BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(tmpFile), StringUtils.UTF16), readerBufferSize);

        final List<SQLField> orderedFields = t.getOrderedFields();

        final int fieldsCount = orderedFields.size();

        final String cols = CollectionUtils.join(orderedFields, ",", new ITransformer<SQLField, String>() {

            @Override

            public String transformChecked(SQLField input) {

                return SQLBase.quoteIdentifier(input.getName());

            }

        });

        final FileOutputStream outs = new FileOutputStream(f);

        BufferedWriter writer = null;

        try {

            writer = new BufferedWriter(new OutputStreamWriter(outs, StringUtils.UTF8));

            writer.write(cols);

            writer.write('\n');

            final StringBuilder sb = new StringBuilder(readerBufferSize * 2);

            String row = readUntil(reader, sb, ROW_DELIM);

            final Pattern fieldPattern = Pattern.compile(FIELD_DELIM, Pattern.LITERAL);

            while (row != null) {

                if (row.length() > 0) {

                    // -1 to have every (even empty) field

                    final String[] fields = fieldPattern.split(row, -1);

                    if (fields.length != fieldsCount)

                        throw new IOException("Invalid fields count, expected " + fieldsCount + " but was " + fields.length + "\n" + row);

                    int i = 0;

                    for (final String field : fields) {

                        final String quoted;

                        if (field.length() == 0) {

                            quoted = "\\N";

                        } else if (field.equals("\0")) {

                            quoted = "\"\"";

                        } else {

                            quoted = StringUtils.doubleQuote(field);

                        }

                        writer.write(quoted);

                        if (++i < fieldsCount)

                            writer.write(',');

                    }

                    writer.write('\n');

                }

                row = readUntil(reader, sb, ROW_DELIM);

            }

        } finally {

            tmpFile.delete();

            if (writer != null)

                writer.close();

            else

                outs.close();

            reader.close();

        }

    }

    private String readUntil(BufferedReader reader, StringBuilder sb, String rowDelim) throws IOException {

        if (sb.capacity() == 0)

            return null;

        final int existing = sb.indexOf(rowDelim);

        if (existing >= 0) {

            final String res = sb.substring(0, existing);

            sb.delete(0, existing + rowDelim.length());

            return res;

        } else {

            final char[] buffer = new char[sb.capacity() / 3];

            final int readCount = reader.read(buffer);

            if (readCount <= 0) {

                final String res = sb.toString();

                sb.setLength(0);

                sb.trimToSize();

                assert sb.capacity() == 0;

                return res;

            } else {

                sb.append(buffer, 0, readCount);

                return readUntil(reader, sb, rowDelim);

            }

        }

    }

    @Override

    public boolean supportMultiAlterClause() {

        // support multiple if you omit the "add" : ALTER TABLE t add f1 int, f2 bit

        return false;

    }

    @Override

    public String getNullIsDataComparison(String x, boolean eq, String y) {

        final String nullSafe = x + " = " + y + " or ( " + x + " is null and " + y + " is null)";

        if (eq)

            return nullSafe;

        else

            return x + " <> " + y + " or (" + x + " is null and " + y + " is not null) " + " or (" + x + " is not null and " + y + " is null) ";

    }

    @Override

    public String getFunctionQuery(SQLBase b, Set<String> schemas) {

        return "  select name, schema_name(schema_id) as \"schema\", cast(OBJECT_DEFINITION(object_id) as varchar(4096)) as \"src\"\n"

                //

                + "  FROM " + new SQLName(b.getName(), "sys", "objects") + "\n"

                // scalar, inline table-valued, table-valued

                + "  where type IN ('FN', 'IF', 'TF') and SCHEMA_NAME( schema_id ) in (" + quoteStrings(schemas) + ") ";

    }

    @Override

    public String getTriggerQuery(SQLBase b, TablesMap tables) {

        // for some reason OBJECT_DEFINITION always returns null

        return "SELECT  trig.name as \"TRIGGER_NAME\", SCHEMA_NAME( tabl.schema_id ) as \"TABLE_SCHEMA\", tabl.name as \"TABLE_NAME\",  null as \"ACTION\", cast(OBJECT_DEFINITION(trig.object_id) as varchar(4096)) as \"SQL\"\n"

                //

                + "FROM " + new SQLName(b.getName(), "sys", "triggers") + " trig\n"

                //

                + "join " + new SQLName(b.getName(), "sys", "objects") + " tabl on trig.parent_id = tabl.object_id\n"

                // requested tables

                + getTablesMapJoin(tables, "SCHEMA_NAME( tabl.schema_id )", "tabl.name");

    }

    @Override

    public String getDropTrigger(Trigger t) {

        return "DROP TRIGGER " + new SQLName(t.getTable().getSchema().getName(), t.getName()).quote();

    }

    @Override

    public String getColumnsQuery(SQLBase b, TablesMap tables) {

        return "SELECT TABLE_SCHEMA as \"" + INFO_SCHEMA_NAMES_KEYS.get(0) + "\", TABLE_NAME as \"" + INFO_SCHEMA_NAMES_KEYS.get(1) + "\", COLUMN_NAME as \"" + INFO_SCHEMA_NAMES_KEYS.get(2)

                + "\" , CHARACTER_SET_NAME as \"CHARACTER_SET_NAME\", COLLATION_NAME as \"COLLATION_NAME\" from INFORMATION_SCHEMA.COLUMNS\n" +

                // requested tables

                getTablesMapJoin(tables, "TABLE_SCHEMA", "TABLE_NAME");

    }

    @Override

    public List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException {

        final String where = getTablesMapJoin(tables, "SCHEMA_NAME(t.schema_id)", "t.name");

        final String sel = "SELECT SCHEMA_NAME(t.schema_id) AS \"TABLE_SCHEMA\", t.name AS \"TABLE_NAME\", k.name AS \"CONSTRAINT_NAME\", case k.type when 'UQ' then 'UNIQUE' when 'PK' then 'PRIMARY KEY' end as \"CONSTRAINT_TYPE\", col_name(c.object_id, c.column_id) AS \"COLUMN_NAME\", c.key_ordinal AS \"ORDINAL_POSITION\", null AS [DEFINITION]\n"

                + "FROM sys.key_constraints k\n"

                //

                + "JOIN sys.index_columns c ON c.object_id = k.parent_object_id AND c.index_id = k.unique_index_id\n"

                //

                + "JOIN sys.tables t ON t.object_id = k.parent_object_id\n" + where + "\nUNION ALL\n"

                //

                + "SELECT SCHEMA_NAME(t.schema_id) AS \"TABLE_SCHEMA\", t.name AS \"TABLE_NAME\", k.name AS \"CONSTRAINT_NAME\", 'CHECK' as \"CONSTRAINT_TYPE\", col.name AS \"COLUMN_NAME\", 1 AS \"ORDINAL_POSITION\", k.[definition] AS [DEFINITION]\n"

                + "FROM sys.check_constraints k\n"

                //

                + "join sys.tables t on k.parent_object_id = t.object_id\n"

                //

                + "left join sys.columns col on k.parent_column_id = col.column_id and col.object_id = t.object_id\n"

                //

                + where + "\nUNION ALL\n"

                //

                + "SELECT SCHEMA_NAME(t.schema_id) AS [TABLE_SCHEMA], t.name AS [TABLE_NAME], k.name AS [CONSTRAINT_NAME], 'DEFAULT' as [CONSTRAINT_TYPE], col.name AS [COLUMN_NAME], 1 AS [ORDINAL_POSITION], k.[definition] AS [DEFINITION]\n"

                + "FROM sys.[default_constraints] k\n"

                //

                + "JOIN sys.tables t ON t.object_id = k.parent_object_id\n"

                //

                + "left join sys.columns col on k.parent_column_id = col.column_id and col.object_id = t.object_id\n"

                //

                + where;

        // don't cache since we don't listen on system tables

        @SuppressWarnings("unchecked")

        final List<Map<String, Object>> res = (List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));

        SQLSyntaxMySQL.mergeColumnNames(res);

        return res;

    }

    @Override

    public DeferredClause getSetTableComment(String comment) {

        return null;

    }

    @Override

    public String getConcatOp() {

        return "+";

    }

    @Override

    public String getRegexpOp(boolean negation) {

        // MS needs either the CLR : http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

        // or http://www.codeproject.com/KB/database/xp_pcre.aspx

        return null;

    }

    @Override

    public String getDayOfWeek(String sqlTS) {

        return "SELECT DATEPART(dw, " + sqlTS + ")";

    }

    @Override

    public String getMonth(String sqlTS) {

        return "SELECT DATEPART(month, " + sqlTS + ")";

    }

    @Override

    public String getFormatTimestamp(String sqlTS, boolean basic) {

        final String extended = "CONVERT(nvarchar(30), " + sqlTS + ", 126) + '000'";

        if (basic) {

            return "replace( replace( " + extended + ", '-', ''), ':' , '' )";

        } else {

            return extended;

        }

    }

    @Override

    public String getFormatTimestamp(String sqlTS, String nativeFormat) {

        return "FORMAT(" + sqlTS + ", " + nativeFormat + ")";

    }

    @Override

    public String quoteForTimestampFormat(String text) {

        return StringUtils.doubleQuote(text);

    }

    @Override

    public String getSessionIDExpression() {

        return "@@SPID";

    }

    @Override

    public String getSessionsQuery(final DBSystemRoot sysRoot, final boolean includeSelf) {

        final String allRows = "SELECT ps.spid as \"ID\", cmd as \"QUERY\", users.name as \"USER_NAME\" FROM sys.sysprocesses ps\n"

                //

                + "JOIN sys.sysdatabases db on db.dbid = ps.dbid\nJOIN sys.sysusers users on users.uid = ps.uid\\n"

                //

                + " WHERE db.name=" + quoteString(sysRoot.getName());

        if (includeSelf)

            return allRows;

        return allRows + " and ps.spid != " + this.getSessionIDExpression();

    }

    @Override

    public String getVersionFunction() {

        // if this doesn't work, try SERVERPROPERTY('ProductVersion')

        return "@@VERSION";

    }

}