OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 180 | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 180 Rev 182
1 
/*
 1 
/*
2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3 
 *
3 
 *
4 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 4 
 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.
5 
 *
5 
 *
6 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 6 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7 
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 7 
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8 
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 8 
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9 
 * language governing permissions and limitations under the License.
 9 
 * language governing permissions and limitations under the License.
10 
 *
10 
 *
11 
 * When distributing the software, include this License Header Notice in each file.
 11 
 * When distributing the software, include this License Header Notice in each file.
12 
 */
 12 
 */
13
13
14 
 package org.openconcerto.sql.model;
 14 
 package org.openconcerto.sql.model;
15 
 
 15 
 
16 
import org.openconcerto.sql.model.SQLField.Properties;
 16 
import org.openconcerto.sql.model.SQLField.Properties;
17 
import org.openconcerto.sql.model.graph.Link.Rule;
 17 
import org.openconcerto.sql.model.graph.Link.Rule;
18 
import org.openconcerto.sql.model.graph.TablesMap;
 18 
import org.openconcerto.sql.model.graph.TablesMap;
19 
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
 19 
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
20 
import org.openconcerto.sql.utils.ChangeTable.DeferredClause;
 20 
import org.openconcerto.sql.utils.ChangeTable.DeferredClause;
21 
import org.openconcerto.sql.utils.SQLUtils;
 21 
import org.openconcerto.sql.utils.SQLUtils;
22 
import org.openconcerto.utils.CollectionUtils;
 22 
import org.openconcerto.utils.CollectionUtils;
23 
import org.openconcerto.utils.FileUtils;
 23 
import org.openconcerto.utils.FileUtils;
24 
import org.openconcerto.utils.ListMap;
 24 
import org.openconcerto.utils.ListMap;
25 
import org.openconcerto.utils.ProcessStreams;
 25 
import org.openconcerto.utils.ProcessStreams;
26 
import org.openconcerto.utils.RTInterruptedException;
 26 
import org.openconcerto.utils.RTInterruptedException;
27 
import org.openconcerto.utils.StringUtils;
 27 
import org.openconcerto.utils.StringUtils;
28 
import org.openconcerto.utils.Tuple2;
 28 
import org.openconcerto.utils.Tuple2;
29 
import org.openconcerto.utils.cc.ITransformer;
 29 
import org.openconcerto.utils.cc.ITransformer;
- 
 
 30 
import org.openconcerto.xml.XMLCodecUtils;
30 
 
 31 
 
- 
 
 32 
import java.beans.DefaultPersistenceDelegate;
31 
import java.io.BufferedReader;
 33 
import java.io.BufferedReader;
32 
import java.io.BufferedWriter;
 34 
import java.io.BufferedWriter;
33 
import java.io.File;
 35 
import java.io.File;
34 
import java.io.FileInputStream;
 36 
import java.io.FileInputStream;
35 
import java.io.FileOutputStream;
 37 
import java.io.FileOutputStream;
36 
import java.io.IOException;
 38 
import java.io.IOException;
37 
import java.io.InputStreamReader;
 39 
import java.io.InputStreamReader;
38 
import java.io.OutputStreamWriter;
 40 
import java.io.OutputStreamWriter;
39 
import java.math.BigDecimal;
 41 
import java.math.BigDecimal;
40 
import java.nio.charset.Charset;
 42 
import java.nio.charset.Charset;
41 
import java.sql.Blob;
 43 
import java.sql.Blob;
42 
import java.sql.Clob;
 44 
import java.sql.Clob;
43 
import java.sql.SQLException;
 45 
import java.sql.SQLException;
44 
import java.sql.Timestamp;
 46 
import java.sql.Timestamp;
45 
import java.sql.Types;
 47 
import java.sql.Types;
46 
import java.util.Arrays;
 48 
import java.util.Arrays;
47 
import java.util.BitSet;
 49 
import java.util.BitSet;
48 
import java.util.IdentityHashMap;
 50 
import java.util.IdentityHashMap;
49 
import java.util.List;
 51 
import java.util.List;
50 
import java.util.Map;
 52 
import java.util.Map;
51 
import java.util.Set;
 53 
import java.util.Set;
52 
import java.util.regex.Pattern;
 54 
import java.util.regex.Pattern;
53 
 
 55 
 
54 
class SQLSyntaxMS extends SQLSyntax {
 56 
public class SQLSyntaxMS extends SQLSyntax {
55 
 
 57 
 
56 
    static private final IdentityHashMap<String, String> DATE_SPECS;
 58 
    static private final IdentityHashMap<String, String> DATE_SPECS;
57 
 
 59 
 
58 
    static {
 60 
    static {
59 
        DATE_SPECS = new IdentityHashMap<String, String>();
 61 
        DATE_SPECS = new IdentityHashMap<String, String>();
60 
        DATE_SPECS.put(DateProp.YEAR, "yyyy");
 62 
        DATE_SPECS.put(DateProp.YEAR, "yyyy");
61 
        DATE_SPECS.put(DateProp.MONTH_NAME, "MMMM");
 63 
        DATE_SPECS.put(DateProp.MONTH_NAME, "MMMM");
62 
        DATE_SPECS.put(DateProp.MONTH_NUMBER, "MM");
 64 
        DATE_SPECS.put(DateProp.MONTH_NUMBER, "MM");
63 
        DATE_SPECS.put(DateProp.DAY_IN_MONTH, "dd");
 65 
        DATE_SPECS.put(DateProp.DAY_IN_MONTH, "dd");
64 
        DATE_SPECS.put(DateProp.DAY_NAME_IN_WEEK, "dddd");
 66 
        DATE_SPECS.put(DateProp.DAY_NAME_IN_WEEK, "dddd");
65 
        DATE_SPECS.put(DateProp.HOUR, "HH");
 67 
        DATE_SPECS.put(DateProp.HOUR, "HH");
66 
        DATE_SPECS.put(DateProp.MINUTE, "mm");
 68 
        DATE_SPECS.put(DateProp.MINUTE, "mm");
67 
        DATE_SPECS.put(DateProp.SECOND, "ss");
 69 
        DATE_SPECS.put(DateProp.SECOND, "ss");
68 
        DATE_SPECS.put(DateProp.MICROSECOND, "ffffff");
 70 
        DATE_SPECS.put(DateProp.MICROSECOND, "ffffff");
- 
 
 71 
 
- 
 
 72 
        XMLCodecUtils.register(SQLSyntaxMS.class, new DefaultPersistenceDelegate(new String[] {}));
69 
    }
 73 
    }
70 
 
 74 
 
71 
    SQLSyntaxMS() {
 75 
    public SQLSyntaxMS() {
72 
        super(SQLSystem.MSSQL, DATE_SPECS);
 76 
        super(SQLSystem.MSSQL, DATE_SPECS);
73 
        this.typeNames.addAll(Boolean.class, "bit");
 77 
        this.typeNames.addAll(Boolean.class, "bit");
74 
        // tinyint is unsigned
 78 
        // tinyint is unsigned
75 
        this.typeNames.addAll(Short.class, "smallint", "tinyint");
 79 
        this.typeNames.addAll(Short.class, "smallint", "tinyint");
76 
        this.typeNames.addAll(Integer.class, "int");
 80 
        this.typeNames.addAll(Integer.class, "int");
77 
        this.typeNames.addAll(Long.class, "bigint");
 81 
        this.typeNames.addAll(Long.class, "bigint");
78 
        this.typeNames.addAll(BigDecimal.class, "decimal", "numeric", "smallmoney", "money");
 82 
        this.typeNames.addAll(BigDecimal.class, "decimal", "numeric", "smallmoney", "money");
79 
        this.typeNames.addAll(Float.class, "real");
 83 
        this.typeNames.addAll(Float.class, "real");
80 
        this.typeNames.addAll(Double.class, "double precision", "float");
 84 
        this.typeNames.addAll(Double.class, "double precision", "float");
81 
        this.typeNames.addAll(Timestamp.class, "datetime2", "datetime", "smalldatetime");
 85 
        this.typeNames.addAll(Timestamp.class, "datetime2", "datetime", "smalldatetime");
82 
        this.typeNames.addAll(java.sql.Date.class, "date");
 86 
        this.typeNames.addAll(java.sql.Date.class, "date");
83 
        this.typeNames.addAll(java.sql.Time.class, "time");
 87 
        this.typeNames.addAll(java.sql.Time.class, "time");
84 
        this.typeNames.addAll(Blob.class, "image",
 88 
        this.typeNames.addAll(Blob.class, "image",
85 
                // byte[]
 89 
                // byte[]
86 
                "varbinary", "binary");
 90 
                "varbinary", "binary");
87 
        this.typeNames.addAll(Clob.class, "text", "ntext", "unitext");
 91 
        this.typeNames.addAll(Clob.class, "text", "ntext", "unitext");
88 
        this.typeNames.addAll(String.class, "char", "varchar", "nchar", "nvarchar", "unichar", "univarchar");
 92 
        this.typeNames.addAll(String.class, "char", "varchar", "nchar", "nvarchar", "unichar", "univarchar");
89 
    }
 93 
    }
90 
 
 94 
 
91 
    @Override
 95 
    @Override
92 
    public final String quoteString(String s) {
 96 
    public final String quoteString(String s) {
93 
        final String res = super.quoteString(s);
 97 
        final String res = super.quoteString(s);
94 
        if (s == null)
 98 
        if (s == null)
95 
            return res;
 99 
            return res;
96 
        // only use escape form if needed (=> equals with other systems most of the time)
 100 
        // only use escape form if needed (=> equals with other systems most of the time)
97 
        boolean simpleASCII = true;
 101 
        boolean simpleASCII = true;
98 
        final int l = s.length();
 102 
        final int l = s.length();
99 
        for (int i = 0; simpleASCII && i < l; i++) {
 103 
        for (int i = 0; simpleASCII && i < l; i++) {
100 
            final char c = s.charAt(i);
 104 
            final char c = s.charAt(i);
101 
            simpleASCII = c <= 0xFF;
 105 
            simpleASCII = c <= 0xFF;
102 
        }
 106 
        }
103 
        // see http://msdn.microsoft.com/fr-fr/library/ms191200(v=sql.105).aspx
 107 
        // see http://msdn.microsoft.com/fr-fr/library/ms191200(v=sql.105).aspx
104 
        return simpleASCII ? res : "N" + res;
 108 
        return simpleASCII ? res : "N" + res;
105 
    }
 109 
    }
106 
 
 110 
 
107 
    @Override
 111 
    @Override
108 
    public int getMaximumIdentifierLength() {
 112 
    public int getMaximumIdentifierLength() {
109 
        // https://msdn.microsoft.com/en-us/library/ms143432.aspx
 113 
        // https://msdn.microsoft.com/en-us/library/ms143432.aspx
110 
        return 128;
 114 
        return 128;
111 
    }
 115 
    }
112 
 
 116 
 
113 
    @Override
 117 
    @Override
114 
    public String getInitSystemRoot() {
 118 
    public String getInitSystemRoot() {
115 
        final String sql;
 119 
        final String sql;
116 
        try {
 120 
        try {
117 
            final String fileContent = FileUtils.readUTF8(SQLSyntaxPG.class.getResourceAsStream("mssql-functions.sql"));
 121 
            final String fileContent = FileUtils.readUTF8(SQLSyntaxPG.class.getResourceAsStream("mssql-functions.sql"));
118 
            sql = fileContent.replace("${rootName}", SQLBase.quoteIdentifier("dbo"));
 122 
            sql = fileContent.replace("${rootName}", SQLBase.quoteIdentifier("dbo"));
119 
        } catch (IOException e) {
 123 
        } catch (IOException e) {
120 
            throw new IllegalStateException("cannot read functions", e);
 124 
            throw new IllegalStateException("cannot read functions", e);
121 
        }
 125 
        }
122 
        return sql;
 126 
        return sql;
123 
    }
 127 
    }
124 
 
 128 
 
125 
    @Override
 129 
    @Override
126 
    public boolean isAuto(SQLField f) {
 130 
    public boolean isAuto(SQLField f) {
127 
        return f.getType().getJavaType() == Integer.class && "YES".equals(f.getMetadata("IS_AUTOINCREMENT"));
 131 
        return f.getType().getJavaType() == Integer.class && "YES".equals(f.getMetadata("IS_AUTOINCREMENT"));
128 
    }
 132 
    }
129 
 
 133 
 
130 
    @Override
 134 
    @Override
131 
    public String getAuto() {
 135 
    public String getAuto() {
132 
        return " int IDENTITY";
 136 
        return " int IDENTITY";
133 
    }
 137 
    }
134 
 
 138 
 
135 
    @Override
 139 
    @Override
136 
    public int getMaximumVarCharLength() {
 140 
    public int getMaximumVarCharLength() {
137 
        // http://msdn.microsoft.com/en-us/library/ms176089(v=sql.105).aspx
 141 
        // http://msdn.microsoft.com/en-us/library/ms176089(v=sql.105).aspx
138 
        return 8000;
 142 
        return 8000;
139 
    }
 143 
    }
140 
 
 144 
 
141 
    @Override
 145 
    @Override
142 
    public String transfDefaultJDBC2SQL(SQLField f) {
 146 
    public String transfDefaultJDBC2SQL(SQLField f) {
143 
        final Object def = f.getDefaultValue();
 147 
        final Object def = f.getDefaultValue();
144 
        if (def == null)
 148 
        if (def == null)
145 
            return null;
 149 
            return null;
146 
 
 150 
 
147 
        // remove parentheses from ((1))
 151 
        // remove parentheses from ((1))
148 
        String stringDef = def.toString();
 152 
        String stringDef = def.toString();
149 
        while (stringDef.charAt(0) == '(' && stringDef.charAt(stringDef.length() - 1) == ')')
 153 
        while (stringDef.charAt(0) == '(' && stringDef.charAt(stringDef.length() - 1) == ')')
150 
            stringDef = stringDef.substring(1, stringDef.length() - 1);
 154 
            stringDef = stringDef.substring(1, stringDef.length() - 1);
151 
 
 155 
 
152 
        if (f.getType().getJavaType() == Boolean.class) {
 156 
        if (f.getType().getJavaType() == Boolean.class) {
153 
            return stringDef.equals("'true'") ? "true" : "false";
 157 
            return stringDef.equals("'true'") ? "true" : "false";
154 
        } else {
 158 
        } else {
155 
            return stringDef;
 159 
            return stringDef;
156 
        }
 160 
        }
157 
    }
 161 
    }
158 
 
 162 
 
159 
    @Override
 163 
    @Override
160 
    protected String transfDefault(SQLField f, String castless) {
 164 
    protected String transfDefault(SQLField f, String castless) {
161 
        if (castless != null && f.getType().getJavaType() == Boolean.class) {
 165 
        if (castless != null && f.getType().getJavaType() == Boolean.class) {
162 
            // yes MS has no true/false keywords
 166 
            // yes MS has no true/false keywords
163 
            return castless.equals("TRUE") ? "'true'" : "'false'";
 167 
            return castless.equals("TRUE") ? "'true'" : "'false'";
164 
        } else
 168 
        } else
165 
            return castless;
 169 
            return castless;
166 
    }
 170 
    }
167 
 
 171 
 
168 
    @Override
 172 
    @Override
169 
    protected String getRuleSQL(final Rule r) {
 173 
    protected String getRuleSQL(final Rule r) {
170 
        // MSSQL doesn't support RESTRICT
 174 
        // MSSQL doesn't support RESTRICT
171 
        return (r.equals(Rule.RESTRICT) ? Rule.NO_ACTION : r).asString();
 175 
        return (r.equals(Rule.RESTRICT) ? Rule.NO_ACTION : r).asString();
172 
    }
 176 
    }
173 
 
 177 
 
174 
    @Override
 178 
    @Override
175 
    public String disableFKChecks(DBRoot b) {
 179 
    public String disableFKChecks(DBRoot b) {
176 
        return fkChecks(b, false);
 180 
        return fkChecks(b, false);
177 
    }
 181 
    }
178 
 
 182 
 
179 
    private String fkChecks(final DBRoot b, final boolean enable) {
 183 
    private String fkChecks(final DBRoot b, final boolean enable) {
180 
        final String s = enable ? "with check check constraint all" : "nocheck constraint all";
 184 
        final String s = enable ? "with check check constraint all" : "nocheck constraint all";
181 
        return "exec sp_MSforeachtable @command1 = 'ALTER TABLE ? " + s + "' , @whereand = " +
 185 
        return "exec sp_MSforeachtable @command1 = 'ALTER TABLE ? " + s + "' , @whereand = " +
182 
        //
 186 
        //
183 
                quoteString("and schema_id = SCHEMA_ID( " + quoteString(b.getName()) + " )");
 187 
                quoteString("and schema_id = SCHEMA_ID( " + quoteString(b.getName()) + " )");
184 
    }
 188 
    }
185 
 
 189 
 
186 
    @Override
 190 
    @Override
187 
    public String enableFKChecks(DBRoot b) {
 191 
    public String enableFKChecks(DBRoot b) {
188 
        return fkChecks(b, true);
 192 
        return fkChecks(b, true);
189 
    }
 193 
    }
190 
 
 194 
 
191 
    @Override
 195 
    @Override
192 
    public List<Map<String, Object>> getIndexInfo(SQLTable t) throws SQLException {
 196 
    public List<Map<String, Object>> getIndexInfo(SQLTable t) throws SQLException {
193 
        final String query = "SELECT NULL AS \"TABLE_CAT\", schema_name(t.schema_id) as \"TABLE_SCHEM\", t.name as \"TABLE_NAME\",\n" +
 197 
        final String query = "SELECT NULL AS \"TABLE_CAT\", schema_name(t.schema_id) as \"TABLE_SCHEM\", t.name as \"TABLE_NAME\",\n" +
194 
        //
 198 
        //
195 
                "~idx.is_unique as \"NON_UNIQUE\", NULL AS \"INDEX_QUALIFIER\", idx.name as \"INDEX_NAME\", NULL as \"TYPE\",\n" +
 199 
                "~idx.is_unique as \"NON_UNIQUE\", NULL AS \"INDEX_QUALIFIER\", idx.name as \"INDEX_NAME\", NULL as \"TYPE\",\n" +
196 
                //
 200 
                //
197 
                "indexCols.key_ordinal as \"ORDINAL_POSITION\", cols.name as \"COLUMN_NAME\",\n" +
 201 
                "indexCols.key_ordinal as \"ORDINAL_POSITION\", cols.name as \"COLUMN_NAME\",\n" +
198 
                //
 202 
                //
199 
                "case when indexCols.is_descending_key = 1 then 'D' else 'A' end as \"ASC_OR_DESC\", null as \"CARDINALITY\", null as \"PAGES\",\n" +
 203 
                "case when indexCols.is_descending_key = 1 then 'D' else 'A' end as \"ASC_OR_DESC\", null as \"CARDINALITY\", null as \"PAGES\",\n" +
200 
                //
 204 
                //
201 
                "filter_definition as \"FILTER_CONDITION\"\n" +
 205 
                "filter_definition as \"FILTER_CONDITION\"\n" +
202 
                //
 206 
                //
203 
                "  FROM [test].[sys].[objects] t\n" +
 207 
                "  FROM [test].[sys].[objects] t\n" +
204 
                //
 208 
                //
205 
                "  join [test].[sys].[indexes] idx on idx.object_id = t.object_id\n" +
 209 
                "  join [test].[sys].[indexes] idx on idx.object_id = t.object_id\n" +
206 
                //
 210 
                //
207 
                "  join [test].[sys].[index_columns] indexCols on idx.index_id = indexCols.index_id and idx.object_id = indexCols.object_id\n" +
 211 
                "  join [test].[sys].[index_columns] indexCols on idx.index_id = indexCols.index_id and idx.object_id = indexCols.object_id\n" +
208 
                //
 212 
                //
209 
                "  join [test].[sys].[columns] cols on t.object_id = cols.object_id and cols.column_id = indexCols.column_id \n" +
 213 
                "  join [test].[sys].[columns] cols on t.object_id = cols.object_id and cols.column_id = indexCols.column_id \n" +
210 
                //
 214 
                //
211 
                "  where schema_name(t.schema_id) = " + quoteString(t.getSchema().getName()) + " and t.name = " + quoteString(t.getName()) + "\n"
 215 
                "  where schema_name(t.schema_id) = " + quoteString(t.getSchema().getName()) + " and t.name = " + quoteString(t.getName()) + "\n"
212 
                //
 216 
                //
213 
                + "ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\";";
 217 
                + "ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\";";
214 
        // don't cache since we don't listen on system tables
 218 
        // don't cache since we don't listen on system tables
215 
        return (List<Map<String, Object>>) t.getDBSystemRoot().getDataSource().execute(query, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
 219 
        return (List<Map<String, Object>>) t.getDBSystemRoot().getDataSource().execute(query, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
216 
    }
 220 
    }
217 
 
 221 
 
218 
    @SuppressWarnings("unchecked")
 222 
    @SuppressWarnings("unchecked")
219 
    @Override
 223 
    @Override
220 
    public Map<String, Object> normalizeIndexInfo(final Map m) {
 224 
    public Map<String, Object> normalizeIndexInfo(final Map m) {
221 
        // genuine MS driver
 225 
        // genuine MS driver
222 
        if (getSystem().getJDBCName().equals("sqlserver"))
 226 
        if (getSystem().getJDBCName().equals("sqlserver"))
223 
            m.put("NON_UNIQUE", ((Number) m.get("NON_UNIQUE")).intValue() != 0);
 227 
            m.put("NON_UNIQUE", ((Number) m.get("NON_UNIQUE")).intValue() != 0);
224 
        return m;
 228 
        return m;
225 
    }
 229 
    }
226 
 
 230 
 
227 
    @Override
 231 
    @Override
228 
    public String getDropIndex(String name, SQLName tableName) {
 232 
    public String getDropIndex(String name, SQLName tableName) {
229 
        return "DROP INDEX " + SQLBase.quoteIdentifier(name) + " on " + tableName.quote() + ";";
 233 
        return "DROP INDEX " + SQLBase.quoteIdentifier(name) + " on " + tableName.quote() + ";";
230 
    }
 234 
    }
231 
 
 235 
 
232 
    @Override
 236 
    @Override
233 
    public boolean isUniqueException(SQLException exn) {
 237 
    public boolean isUniqueException(SQLException exn) {
234 
        return SQLUtils.findWithSQLState(exn).getErrorCode() == 2601;
 238 
        return SQLUtils.findWithSQLState(exn).getErrorCode() == 2601;
235 
    }
 239 
    }
236 
 
 240 
 
237 
    @Override
 241 
    @Override
238 
    public boolean isDeadLockException(SQLException exn) {
 242 
    public boolean isDeadLockException(SQLException exn) {
239 
        return SQLUtils.findWithSQLState(exn).getErrorCode() == 1205;
 243 
        return SQLUtils.findWithSQLState(exn).getErrorCode() == 1205;
240 
    }
 244 
    }
241 
 
 245 
 
242 
    @Override
 246 
    @Override
- 
 
 247 
    public boolean isTableNotFoundException(Exception exn) {
- 
 
 248 
        // TODO
- 
 
 249 
        throw new UnsupportedOperationException();
- 
 
 250 
    }
- 
 
 251 
 
- 
 
 252 
    @Override
- 
 
 253 
    public String getSetLockTimeoutQuery(int millis) {
- 
 
 254 
        return "SET LOCK_TIMEOUT " + millis;
- 
 
 255 
    }
- 
 
 256 
 
- 
 
 257 
    @Override
- 
 
 258 
    public String getShowLockTimeoutQuery() {
- 
 
 259 
        return "SELECT @@LOCK_TIMEOUT";
- 
 
 260 
    }
- 
 
 261 
 
- 
 
 262 
    @Override
243 
    public Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {
 263 
    public Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {
244 
        final ListMap<ClauseType, String> res = new ListMap<ClauseType, String>();
 264 
        final ListMap<ClauseType, String> res = new ListMap<ClauseType, String>();
245 
        if (toAlter.contains(Properties.TYPE) || toAlter.contains(Properties.NULLABLE)) {
 265 
        if (toAlter.contains(Properties.TYPE) || toAlter.contains(Properties.NULLABLE)) {
246 
            final String newType = toAlter.contains(Properties.TYPE) ? type : getType(f);
 266 
            final String newType = toAlter.contains(Properties.TYPE) ? type : getType(f);
247 
            final boolean newNullable = toAlter.contains(Properties.NULLABLE) ? nullable : getNullable(f);
 267 
            final boolean newNullable = toAlter.contains(Properties.NULLABLE) ? nullable : getNullable(f);
248 
            res.add(ClauseType.ALTER_COL, "ALTER COLUMN " + f.getQuotedName() + " " + getFieldDecl(newType, null, newNullable));
 268 
            res.add(ClauseType.ALTER_COL, "ALTER COLUMN " + f.getQuotedName() + " " + getFieldDecl(newType, null, newNullable));
249 
        }
 269 
        }
250 
        if (toAlter.contains(Properties.DEFAULT)) {
 270 
        if (toAlter.contains(Properties.DEFAULT)) {
251 
            final Constraint existingConstraint = f.getTable().getConstraint(ConstraintType.DEFAULT, Arrays.asList(f.getName()));
 271 
            final Constraint existingConstraint = f.getTable().getConstraint(ConstraintType.DEFAULT, Arrays.asList(f.getName()));
252 
            if (existingConstraint != null) {
 272 
            if (existingConstraint != null) {
253 
                res.add(ClauseType.DROP_CONSTRAINT, "DROP CONSTRAINT " + SQLBase.quoteIdentifier(existingConstraint.getName()));
 273 
                res.add(ClauseType.DROP_CONSTRAINT, "DROP CONSTRAINT " + SQLBase.quoteIdentifier(existingConstraint.getName()));
254 
            }
 274 
            }
255 
            if (defaultVal != null) {
 275 
            if (defaultVal != null) {
256 
                res.add(ClauseType.ADD_CONSTRAINT, "ADD DEFAULT " + defaultVal + " FOR " + f.getQuotedName());
 276 
                res.add(ClauseType.ADD_CONSTRAINT, "ADD DEFAULT " + defaultVal + " FOR " + f.getQuotedName());
257 
            }
 277 
            }
258 
        }
 278 
        }
259 
        return res;
 279 
        return res;
260 
    }
 280 
    }
261 
 
 281 
 
262 
    @Override
 282 
    @Override
263 
    public String getRenameTable(SQLName table, String newName) {
 283 
    public String getRenameTable(SQLName table, String newName) {
264 
        return "sp_rename " + SQLBase.quoteStringStd(table.quote()) + ", " + SQLBase.quoteStringStd(newName);
 284 
        return "sp_rename " + SQLBase.quoteStringStd(table.quote()) + ", " + SQLBase.quoteStringStd(newName);
265 
    }
 285 
    }
266 
 
 286 
 
267 
    @Override
 287 
    @Override
268 
    public String getDropTable(SQLName name, boolean ifExists, boolean restrict) {
 288 
    public String getDropTable(SQLName name, boolean ifExists, boolean restrict) {
269 
        // doesn't support cascade
 289 
        // doesn't support cascade
270 
        if (!restrict)
 290 
        if (!restrict)
271 
            return null;
 291 
            return null;
272 
        if (!ifExists) {
 292 
        if (!ifExists) {
273 
            return super.getDropTable(name, ifExists, restrict);
 293 
            return super.getDropTable(name, ifExists, restrict);
274 
        } else {
 294 
        } else {
275 
            final String quoted = name.quote();
 295 
            final String quoted = name.quote();
276 
            return "IF OBJECT_ID(" + SQLBase.quoteStringStd(quoted) + ", 'U') IS NOT NULL DROP TABLE " + quoted;
 296 
            return "IF OBJECT_ID(" + SQLBase.quoteStringStd(quoted) + ", 'U') IS NOT NULL DROP TABLE " + quoted;
277 
        }
 297 
        }
278 
    }
 298 
    }
279 
 
 299 
 
280 
    @Override
 300 
    @Override
281 
    public String getDropRoot(String name) {
 301 
    public String getDropRoot(String name) {
282 
        // Only works if getInitSystemRoot() was executed
 302 
        // Only works if getInitSystemRoot() was executed
283 
        // http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/
 303 
        // http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/
284 
        return "exec CleanUpSchema " + SQLBase.quoteStringStd(name) + ", 'w' ;";
 304 
        return "exec CleanUpSchema " + SQLBase.quoteStringStd(name) + ", 'w' ;";
285 
    }
 305 
    }
286 
 
 306 
 
287 
    @Override
 307 
    @Override
288 
    public String getCreateRoot(String name) {
 308 
    public String getCreateRoot(String name) {
289 
        return "CREATE SCHEMA " + SQLBase.quoteIdentifier(name) + " ;";
 309 
        return "CREATE SCHEMA " + SQLBase.quoteIdentifier(name) + " ;";
290 
    }
 310 
    }
291 
 
 311 
 
292 
    @Override
 312 
    @Override
293 
    protected Tuple2<Boolean, String> getCast() {
 313 
    protected Tuple2<Boolean, String> getCast() {
294 
        return null;
 314 
        return null;
295 
    }
 315 
    }
296 
 
 316 
 
297 
    @Override
 317 
    @Override
298 
    public void _loadData(final File f, final SQLTable t) throws IOException {
 318 
    public void _loadData(final File f, final SQLTable t) throws IOException {
299 
        final String data = FileUtils.readUTF8(f);
 319 
        final String data = FileUtils.readUTF8(f);
300 
        final File temp = File.createTempFile(FileUtils.sanitize("mssql_loadData_" + t.getName()), ".txt");
 320 
        final File temp = File.createTempFile(FileUtils.sanitize("mssql_loadData_" + t.getName()), ".txt");
301 
 
 321 
 
302 
        // no we cant't use UTF16 since Java write BE and MS ignores the BOM, always using LE.
 322 
        // no we cant't use UTF16 since Java write BE and MS ignores the BOM, always using LE.
303 
        final BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(temp), Charset.forName("x-UTF-16LE-BOM")));
 323 
        final BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(temp), Charset.forName("x-UTF-16LE-BOM")));
304 
 
 324 
 
305 
        final List<SQLField> fields = t.getOrderedFields();
 325 
        final List<SQLField> fields = t.getOrderedFields();
306 
        final int fieldsCount = fields.size();
 326 
        final int fieldsCount = fields.size();
307 
        final BitSet booleanFields = new BitSet(fieldsCount);
 327 
        final BitSet booleanFields = new BitSet(fieldsCount);
308 
        int fieldIndex = 0;
 328 
        int fieldIndex = 0;
309 
        for (final SQLField field : fields) {
 329 
        for (final SQLField field : fields) {
310 
            final int type = field.getType().getType();
 330 
            final int type = field.getType().getType();
311 
            booleanFields.set(fieldIndex++, type == Types.BOOLEAN || type == Types.BIT);
 331 
            booleanFields.set(fieldIndex++, type == Types.BOOLEAN || type == Types.BIT);
312 
        }
 332 
        }
313 
        fieldIndex = 0;
 333 
        fieldIndex = 0;
314 
 
 334 
 
315 
        try {
 335 
        try {
316 
            // skip fields names
 336 
            // skip fields names
317 
            int i = data.indexOf('\n') + 1;
 337 
            int i = data.indexOf('\n') + 1;
318 
            while (i < data.length()) {
 338 
            while (i < data.length()) {
319 
                final String twoChars = i + 2 <= data.length() ? data.substring(i, i + 2) : null;
 339 
                final String twoChars = i + 2 <= data.length() ? data.substring(i, i + 2) : null;
320 
                if ("\\N".equals(twoChars)) {
 340 
                if ("\\N".equals(twoChars)) {
321 
                    i += 2;
 341 
                    i += 2;
322 
                } else if ("\"\"".equals(twoChars)) {
 342 
                } else if ("\"\"".equals(twoChars)) {
323 
                    writer.write("\0");
 343 
                    writer.write("\0");
324 
                    i += 2;
 344 
                    i += 2;
325 
                } else {
 345 
                } else {
326 
                    final Tuple2<String, Integer> unDoubleQuote = StringUtils.unDoubleQuote(data, i);
 346 
                    final Tuple2<String, Integer> unDoubleQuote = StringUtils.unDoubleQuote(data, i);
327 
                    String unquoted = unDoubleQuote.get0();
 347 
                    String unquoted = unDoubleQuote.get0();
328 
                    if (booleanFields.get(fieldIndex)) {
 348 
                    if (booleanFields.get(fieldIndex)) {
329 
                        if (unquoted.equalsIgnoreCase("false")) {
 349 
                        if (unquoted.equalsIgnoreCase("false")) {
330 
                            unquoted = "0";
 350 
                            unquoted = "0";
331 
                        } else if (unquoted.equalsIgnoreCase("true")) {
 351 
                        } else if (unquoted.equalsIgnoreCase("true")) {
332 
                            unquoted = "1";
 352 
                            unquoted = "1";
333 
                        }
 353 
                        }
334 
                    }
 354 
                    }
335 
                    writer.write(unquoted);
 355 
                    writer.write(unquoted);
336 
                    i = unDoubleQuote.get1();
 356 
                    i = unDoubleQuote.get1();
337 
                }
 357 
                }
338 
                fieldIndex++;
 358 
                fieldIndex++;
339 
                if (i < data.length()) {
 359 
                if (i < data.length()) {
340 
                    final char c = data.charAt(i);
 360 
                    final char c = data.charAt(i);
341 
                    if (c == ',') {
 361 
                    if (c == ',') {
342 
                        writer.write(FIELD_DELIM);
 362 
                        writer.write(FIELD_DELIM);
343 
                        i++;
 363 
                        i++;
344 
                    } else if (c == '\n') {
 364 
                    } else if (c == '\n') {
345 
                        writer.write(ROW_DELIM);
 365 
                        writer.write(ROW_DELIM);
346 
                        i++;
 366 
                        i++;
347 
                        if (fieldIndex != fieldsCount)
 367 
                        if (fieldIndex != fieldsCount)
348 
                            throw new IOException("Expected " + fieldsCount + " fields but got : " + fieldIndex);
 368 
                            throw new IOException("Expected " + fieldsCount + " fields but got : " + fieldIndex);
349 
                        fieldIndex = 0;
 369 
                        fieldIndex = 0;
350 
                    } else {
 370 
                    } else {
351 
                        throw new IOException("Unexpected character after field : " + c);
 371 
                        throw new IOException("Unexpected character after field : " + c);
352 
                    }
 372 
                    }
353 
                }
 373 
                }
354 
            }
 374 
            }
355 
            if (fieldIndex != 0 && fieldIndex != fieldsCount)
 375 
            if (fieldIndex != 0 && fieldIndex != fieldsCount)
356 
                throw new IOException("Expected " + fieldsCount + " fields but got : " + fieldIndex);
 376 
                throw new IOException("Expected " + fieldsCount + " fields but got : " + fieldIndex);
357 
        } finally {
 377 
        } finally {
358 
            writer.close();
 378 
            writer.close();
359 
        }
 379 
        }
360 
 
 380 
 
361 
        execute_bcp(t, false, temp);
 381 
        execute_bcp(t, false, temp);
362 
        temp.delete();
 382 
        temp.delete();
363 
 
 383 
 
364 
        // MAYBE when on localhost, remove the bcp requirement (OTOH bcp should already be
 384 
        // MAYBE when on localhost, remove the bcp requirement (OTOH bcp should already be
365 
        // installed, just perhaps not in the path)
 385 
        // installed, just perhaps not in the path)
366 
        // checkServerLocalhost(t);
 386 
        // checkServerLocalhost(t);
367 
        // "bulk insert " + t.getSQL() + " from " + b.quoteString(temp.getAbsolutePath()) +
 387 
        // "bulk insert " + t.getSQL() + " from " + b.quoteString(temp.getAbsolutePath()) +
368 
        // " with ( DATAFILETYPE='widechar', FIELDTERMINATOR = " + b.quoteString(FIELD_DELIM)
 388 
        // " with ( DATAFILETYPE='widechar', FIELDTERMINATOR = " + b.quoteString(FIELD_DELIM)
369 
        // + ", ROWTERMINATOR= " + b.quoteString(ROW_DELIM) +
 389 
        // + ", ROWTERMINATOR= " + b.quoteString(ROW_DELIM) +
370 
        // ", FIRSTROW=1, KEEPIDENTITY, KEEPNULLS ) ;"
 390 
        // ", FIRSTROW=1, KEEPIDENTITY, KEEPNULLS ) ;"
371 
    }
 391 
    }
372 
 
 392 
 
373 
    private static final String FIELD_DELIM = "<|!!|>";
 393 
    private static final String FIELD_DELIM = "<|!!|>";
374 
    private static final String ROW_DELIM = "...#~\n~#...";
 394 
    private static final String ROW_DELIM = "...#~\n~#...";
375 
 
 395 
 
376 
    protected void execute_bcp(final SQLTable t, final boolean dump, final File f) throws IOException {
 396 
    protected void execute_bcp(final SQLTable t, final boolean dump, final File f) throws IOException {
377 
        final ProcessBuilder pb = new ProcessBuilder("bcp");
 397 
        final ProcessBuilder pb = new ProcessBuilder("bcp");
378 
        pb.command().add(t.getSQLName().quote());
 398 
        pb.command().add(t.getSQLName().quote());
379 
        pb.command().add(dump ? "out" : "in");
 399 
        pb.command().add(dump ? "out" : "in");
380 
        pb.command().add(f.getAbsolutePath());
 400 
        pb.command().add(f.getAbsolutePath());
381 
        // UTF-16LE with a BOM
 401 
        // UTF-16LE with a BOM
382 
        pb.command().add("-w");
 402 
        pb.command().add("-w");
383 
        pb.command().add("-t" + FIELD_DELIM);
 403 
        pb.command().add("-t" + FIELD_DELIM);
384 
        pb.command().add("-r" + ROW_DELIM);
 404 
        pb.command().add("-r" + ROW_DELIM);
385 
        // needed if table name is a keyword (e.g. RIGHT)
 405 
        // needed if table name is a keyword (e.g. RIGHT)
386 
        pb.command().add("-q");
 406 
        pb.command().add("-q");
387 
        pb.command().add("-S" + t.getServer().getName());
 407 
        pb.command().add("-S" + t.getServer().getName());
388 
        pb.command().add("-U" + t.getDBSystemRoot().getDataSource().getUsername());
 408 
        pb.command().add("-U" + t.getDBSystemRoot().getDataSource().getUsername());
389 
        pb.command().add("-P" + t.getDBSystemRoot().getDataSource().getPassword());
 409 
        pb.command().add("-P" + t.getDBSystemRoot().getDataSource().getPassword());
390 
        if (!dump) {
 410 
        if (!dump) {
391 
            // retain null
 411 
            // retain null
392 
            pb.command().add("-k");
 412 
            pb.command().add("-k");
393 
            // keep identity
 413 
            // keep identity
394 
            pb.command().add("-E");
 414 
            pb.command().add("-E");
395 
        }
 415 
        }
396 
 
 416 
 
397 
        final Process p = ProcessStreams.redirect(pb).start();
 417 
        final Process p = ProcessStreams.redirect(pb).start();
398 
        try {
 418 
        try {
399 
            final int returnCode = p.waitFor();
 419 
            final int returnCode = p.waitFor();
400 
            if (returnCode != 0)
 420 
            if (returnCode != 0)
401 
                throw new IOException("Did not finish correctly : " + returnCode + "\n" + pb.command());
 421 
                throw new IOException("Did not finish correctly : " + returnCode + "\n" + pb.command());
402 
        } catch (InterruptedException e) {
 422 
        } catch (InterruptedException e) {
403 
            throw new RTInterruptedException(e);
 423 
            throw new RTInterruptedException(e);
404 
        }
 424 
        }
405 
    }
 425 
    }
406 
 
 426 
 
407 
    // For bcp : http://www.microsoft.com/en-us/download/details.aspx?id=16978
 427 
    // For bcp : http://www.microsoft.com/en-us/download/details.aspx?id=16978
408 
    @Override
 428 
    @Override
409 
    protected void _storeData(final SQLTable t, final File f) throws IOException {
 429 
    protected void _storeData(final SQLTable t, final File f) throws IOException {
410 
        final File tmpFile = File.createTempFile(FileUtils.sanitize("mssql_dump_" + t.getName()), ".dat");
 430 
        final File tmpFile = File.createTempFile(FileUtils.sanitize("mssql_dump_" + t.getName()), ".dat");
411 
        execute_bcp(t, true, tmpFile);
 431 
        execute_bcp(t, true, tmpFile);
412 
        final int readerBufferSize = 32768;
 432 
        final int readerBufferSize = 32768;
413 
        final BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(tmpFile), StringUtils.UTF16), readerBufferSize);
 433 
        final BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(tmpFile), StringUtils.UTF16), readerBufferSize);
414 
        final List<SQLField> orderedFields = t.getOrderedFields();
 434 
        final List<SQLField> orderedFields = t.getOrderedFields();
415 
        final int fieldsCount = orderedFields.size();
 435 
        final int fieldsCount = orderedFields.size();
416 
        final String cols = CollectionUtils.join(orderedFields, ",", new ITransformer<SQLField, String>() {
 436 
        final String cols = CollectionUtils.join(orderedFields, ",", new ITransformer<SQLField, String>() {
417 
            @Override
 437 
            @Override
418 
            public String transformChecked(SQLField input) {
 438 
            public String transformChecked(SQLField input) {
419 
                return SQLBase.quoteIdentifier(input.getName());
 439 
                return SQLBase.quoteIdentifier(input.getName());
420 
            }
 440 
            }
421 
        });
 441 
        });
422 
        final FileOutputStream outs = new FileOutputStream(f);
 442 
        final FileOutputStream outs = new FileOutputStream(f);
423 
        BufferedWriter writer = null;
 443 
        BufferedWriter writer = null;
424 
        try {
 444 
        try {
425 
            writer = new BufferedWriter(new OutputStreamWriter(outs, StringUtils.UTF8));
 445 
            writer = new BufferedWriter(new OutputStreamWriter(outs, StringUtils.UTF8));
426 
            writer.write(cols);
 446 
            writer.write(cols);
427 
            writer.write('\n');
 447 
            writer.write('\n');
428 
            final StringBuilder sb = new StringBuilder(readerBufferSize * 2);
 448 
            final StringBuilder sb = new StringBuilder(readerBufferSize * 2);
429 
            String row = readUntil(reader, sb, ROW_DELIM);
 449 
            String row = readUntil(reader, sb, ROW_DELIM);
430 
            final Pattern fieldPattern = Pattern.compile(FIELD_DELIM, Pattern.LITERAL);
 450 
            final Pattern fieldPattern = Pattern.compile(FIELD_DELIM, Pattern.LITERAL);
431 
            while (row != null) {
 451 
            while (row != null) {
432 
                if (row.length() > 0) {
 452 
                if (row.length() > 0) {
433 
                    // -1 to have every (even empty) field
 453 
                    // -1 to have every (even empty) field
434 
                    final String[] fields = fieldPattern.split(row, -1);
 454 
                    final String[] fields = fieldPattern.split(row, -1);
435 
                    if (fields.length != fieldsCount)
 455 
                    if (fields.length != fieldsCount)
436 
                        throw new IOException("Invalid fields count, expected " + fieldsCount + " but was " + fields.length + "\n" + row);
 456 
                        throw new IOException("Invalid fields count, expected " + fieldsCount + " but was " + fields.length + "\n" + row);
437 
                    int i = 0;
 457 
                    int i = 0;
438 
                    for (final String field : fields) {
 458 
                    for (final String field : fields) {
439 
                        final String quoted;
 459 
                        final String quoted;
440 
                        if (field.length() == 0) {
 460 
                        if (field.length() == 0) {
441 
                            quoted = "\\N";
 461 
                            quoted = "\\N";
442 
                        } else if (field.equals("\0")) {
 462 
                        } else if (field.equals("\0")) {
443 
                            quoted = "\"\"";
 463 
                            quoted = "\"\"";
444 
                        } else {
 464 
                        } else {
445 
                            quoted = StringUtils.doubleQuote(field);
 465 
                            quoted = StringUtils.doubleQuote(field);
446 
                        }
 466 
                        }
447 
                        writer.write(quoted);
 467 
                        writer.write(quoted);
448 
                        if (++i < fieldsCount)
 468 
                        if (++i < fieldsCount)
449 
                            writer.write(',');
 469 
                            writer.write(',');
450 
                    }
 470 
                    }
451 
                    writer.write('\n');
 471 
                    writer.write('\n');
452 
                }
 472 
                }
453 
                row = readUntil(reader, sb, ROW_DELIM);
 473 
                row = readUntil(reader, sb, ROW_DELIM);
454 
            }
 474 
            }
455 
        } finally {
 475 
        } finally {
456 
            tmpFile.delete();
 476 
            tmpFile.delete();
457 
            if (writer != null)
 477 
            if (writer != null)
458 
                writer.close();
 478 
                writer.close();
459 
            else
 479 
            else
460 
                outs.close();
 480 
                outs.close();
461 
            reader.close();
 481 
            reader.close();
462 
        }
 482 
        }
463 
    }
 483 
    }
464 
 
 484 
 
465 
    private String readUntil(BufferedReader reader, StringBuilder sb, String rowDelim) throws IOException {
 485 
    private String readUntil(BufferedReader reader, StringBuilder sb, String rowDelim) throws IOException {
466 
        if (sb.capacity() == 0)
 486 
        if (sb.capacity() == 0)
467 
            return null;
 487 
            return null;
468 
        final int existing = sb.indexOf(rowDelim);
 488 
        final int existing = sb.indexOf(rowDelim);
469 
        if (existing >= 0) {
 489 
        if (existing >= 0) {
470 
            final String res = sb.substring(0, existing);
 490 
            final String res = sb.substring(0, existing);
471 
            sb.delete(0, existing + rowDelim.length());
 491 
            sb.delete(0, existing + rowDelim.length());
472 
            return res;
 492 
            return res;
473 
        } else {
 493 
        } else {
474 
            final char[] buffer = new char[sb.capacity() / 3];
 494 
            final char[] buffer = new char[sb.capacity() / 3];
475 
            final int readCount = reader.read(buffer);
 495 
            final int readCount = reader.read(buffer);
476 
            if (readCount <= 0) {
 496 
            if (readCount <= 0) {
477 
                final String res = sb.toString();
 497 
                final String res = sb.toString();
478 
                sb.setLength(0);
 498 
                sb.setLength(0);
479 
                sb.trimToSize();
 499 
                sb.trimToSize();
480 
                assert sb.capacity() == 0;
 500 
                assert sb.capacity() == 0;
481 
                return res;
 501 
                return res;
482 
            } else {
 502 
            } else {
483 
                sb.append(buffer, 0, readCount);
 503 
                sb.append(buffer, 0, readCount);
484 
                return readUntil(reader, sb, rowDelim);
 504 
                return readUntil(reader, sb, rowDelim);
485 
            }
 505 
            }
486 
        }
 506 
        }
487 
    }
 507 
    }
488 
 
 508 
 
489 
    @Override
 509 
    @Override
490 
    public boolean supportMultiAlterClause() {
 510 
    public boolean supportMultiAlterClause() {
491 
        // support multiple if you omit the "add" : ALTER TABLE t add f1 int, f2 bit
 511 
        // support multiple if you omit the "add" : ALTER TABLE t add f1 int, f2 bit
492 
        return false;
 512 
        return false;
493 
    }
 513 
    }
494 
 
 514 
 
495 
    @Override
 515 
    @Override
496 
    public String getNullIsDataComparison(String x, boolean eq, String y) {
 516 
    public String getNullIsDataComparison(String x, boolean eq, String y) {
497 
        final String nullSafe = x + " = " + y + " or ( " + x + " is null and " + y + " is null)";
 517 
        final String nullSafe = x + " = " + y + " or ( " + x + " is null and " + y + " is null)";
498 
        if (eq)
 518 
        if (eq)
499 
            return nullSafe;
 519 
            return nullSafe;
500 
        else
 520 
        else
501 
            return x + " <> " + y + " or (" + x + " is null and " + y + " is not null) " + " or (" + x + " is not null and " + y + " is null) ";
 521 
            return x + " <> " + y + " or (" + x + " is null and " + y + " is not null) " + " or (" + x + " is not null and " + y + " is null) ";
502 
    }
 522 
    }
503 
 
 523 
 
504 
    @Override
 524 
    @Override
505 
    public String getFunctionQuery(SQLBase b, Set<String> schemas) {
 525 
    public String getFunctionQuery(SQLBase b, Set<String> schemas) {
506 
        return "  select name, schema_name(schema_id) as \"schema\", cast(OBJECT_DEFINITION(object_id) as varchar(4096)) as \"src\"\n"
 526 
        return "  select name, schema_name(schema_id) as \"schema\", cast(OBJECT_DEFINITION(object_id) as varchar(4096)) as \"src\"\n"
507 
                //
 527 
                //
508 
                + "  FROM " + new SQLName(b.getName(), "sys", "objects") + "\n"
 528 
                + "  FROM " + new SQLName(b.getName(), "sys", "objects") + "\n"
509 
                // scalar, inline table-valued, table-valued
 529 
                // scalar, inline table-valued, table-valued
510 
                + "  where type IN ('FN', 'IF', 'TF') and SCHEMA_NAME( schema_id ) in (" + quoteStrings(schemas) + ") ";
 530 
                + "  where type IN ('FN', 'IF', 'TF') and SCHEMA_NAME( schema_id ) in (" + quoteStrings(schemas) + ") ";
511 
    }
 531 
    }
512 
 
 532 
 
513 
    @Override
 533 
    @Override
514 
    public String getTriggerQuery(SQLBase b, TablesMap tables) {
 534 
    public String getTriggerQuery(SQLBase b, TablesMap tables) {
515 
        // for some reason OBJECT_DEFINITION always returns null
 535 
        // for some reason OBJECT_DEFINITION always returns null
516 
        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"
 536 
        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"
517 
                //
 537 
                //
518 
                + "FROM " + new SQLName(b.getName(), "sys", "triggers") + " trig\n"
 538 
                + "FROM " + new SQLName(b.getName(), "sys", "triggers") + " trig\n"
519 
                //
 539 
                //
520 
                + "join " + new SQLName(b.getName(), "sys", "objects") + " tabl on trig.parent_id = tabl.object_id\n"
 540 
                + "join " + new SQLName(b.getName(), "sys", "objects") + " tabl on trig.parent_id = tabl.object_id\n"
521 
                // requested tables
 541 
                // requested tables
522 
                + getTablesMapJoin(tables, "SCHEMA_NAME( tabl.schema_id )", "tabl.name");
 542 
                + getTablesMapJoin(tables, "SCHEMA_NAME( tabl.schema_id )", "tabl.name");
523 
    }
 543 
    }
524 
 
 544 
 
525 
    @Override
 545 
    @Override
526 
    public String getDropTrigger(Trigger t) {
 546 
    public String getDropTrigger(Trigger t) {
527 
        return "DROP TRIGGER " + new SQLName(t.getTable().getSchema().getName(), t.getName()).quote();
 547 
        return "DROP TRIGGER " + new SQLName(t.getTable().getSchema().getName(), t.getName()).quote();
528 
    }
 548 
    }
529 
 
 549 
 
530 
    @Override
 550 
    @Override
531 
    public String getColumnsQuery(SQLBase b, TablesMap tables) {
 551 
    public String getColumnsQuery(SQLBase b, TablesMap tables) {
532 
        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)
 552 
        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)
533 
                + "\" , CHARACTER_SET_NAME as \"CHARACTER_SET_NAME\", COLLATION_NAME as \"COLLATION_NAME\" from INFORMATION_SCHEMA.COLUMNS\n" +
 553 
                + "\" , CHARACTER_SET_NAME as \"CHARACTER_SET_NAME\", COLLATION_NAME as \"COLLATION_NAME\" from INFORMATION_SCHEMA.COLUMNS\n" +
534 
                // requested tables
 554 
                // requested tables
535 
                getTablesMapJoin(tables, "TABLE_SCHEMA", "TABLE_NAME");
 555 
                getTablesMapJoin(tables, "TABLE_SCHEMA", "TABLE_NAME");
536 
    }
 556 
    }
537 
 
 557 
 
538 
    @Override
 558 
    @Override
539 
    public List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException {
 559 
    public List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException {
540 
        final String where = getTablesMapJoin(tables, "SCHEMA_NAME(t.schema_id)", "t.name");
 560 
        final String where = getTablesMapJoin(tables, "SCHEMA_NAME(t.schema_id)", "t.name");
541 
        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"
 561 
        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"
542 
                + "FROM sys.key_constraints k\n"
 562 
                + "FROM sys.key_constraints k\n"
543 
                //
 563 
                //
544 
                + "JOIN sys.index_columns c ON c.object_id = k.parent_object_id AND c.index_id = k.unique_index_id\n"
 564 
                + "JOIN sys.index_columns c ON c.object_id = k.parent_object_id AND c.index_id = k.unique_index_id\n"
545 
                //
 565 
                //
546 
                + "JOIN sys.tables t ON t.object_id = k.parent_object_id\n" + where + "\nUNION ALL\n"
 566 
                + "JOIN sys.tables t ON t.object_id = k.parent_object_id\n" + where + "\nUNION ALL\n"
547 
                //
 567 
                //
548 
                + "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"
 568 
                + "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"
549 
                + "FROM sys.check_constraints k\n"
 569 
                + "FROM sys.check_constraints k\n"
550 
                //
 570 
                //
551 
                + "join sys.tables t on k.parent_object_id = t.object_id\n"
 571 
                + "join sys.tables t on k.parent_object_id = t.object_id\n"
552 
                //
 572 
                //
553 
                + "left join sys.columns col on k.parent_column_id = col.column_id and col.object_id = t.object_id\n"
 573 
                + "left join sys.columns col on k.parent_column_id = col.column_id and col.object_id = t.object_id\n"
554 
                //
 574 
                //
555 
                + where + "\nUNION ALL\n"
 575 
                + where + "\nUNION ALL\n"
556 
                //
 576 
                //
557 
                + "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"
 577 
                + "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"
558 
                + "FROM sys.[default_constraints] k\n"
 578 
                + "FROM sys.[default_constraints] k\n"
559 
                //
 579 
                //
560 
                + "JOIN sys.tables t ON t.object_id = k.parent_object_id\n"
 580 
                + "JOIN sys.tables t ON t.object_id = k.parent_object_id\n"
561 
                //
 581 
                //
562 
                + "left join sys.columns col on k.parent_column_id = col.column_id and col.object_id = t.object_id\n"
 582 
                + "left join sys.columns col on k.parent_column_id = col.column_id and col.object_id = t.object_id\n"
563 
                //
 583 
                //
564 
                + where;
 584 
                + where;
565 
        // don't cache since we don't listen on system tables
 585 
        // don't cache since we don't listen on system tables
566 
        @SuppressWarnings("unchecked")
 586 
        @SuppressWarnings("unchecked")
567 
        final List<Map<String, Object>> res = (List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
 587 
        final List<Map<String, Object>> res = (List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
568 
        SQLSyntaxMySQL.mergeColumnNames(res);
 588 
        SQLSyntaxMySQL.mergeColumnNames(res);
569 
        return res;
 589 
        return res;
570 
    }
 590 
    }
571 
 
 591 
 
572 
    @Override
 592 
    @Override
573 
    public DeferredClause getSetTableComment(String comment) {
 593 
    public DeferredClause getSetTableComment(String comment) {
574 
        return null;
 594 
        return null;
575 
    }
 595 
    }
576 
 
 596 
 
577 
    @Override
 597 
    @Override
578 
    public String getConcatOp() {
 598 
    public String getConcatOp() {
579 
        return "+";
 599 
        return "+";
580 
    }
 600 
    }
581 
 
 601 
 
582 
    @Override
 602 
    @Override
583 
    public String getRegexpOp(boolean negation) {
 603 
    public String getRegexpOp(boolean negation) {
584 
        // MS needs either the CLR : http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
 604 
        // MS needs either the CLR : http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
585 
        // or http://www.codeproject.com/KB/database/xp_pcre.aspx
 605 
        // or http://www.codeproject.com/KB/database/xp_pcre.aspx
586 
        return null;
 606 
        return null;
587 
    }
 607 
    }
588 
 
 608 
 
589 
    @Override
 609 
    @Override
590 
    public String getDayOfWeek(String sqlTS) {
 610 
    public String getDayOfWeek(String sqlTS) {
591 
        return "SELECT DATEPART(dw, " + sqlTS + ")";
 611 
        return "SELECT DATEPART(dw, " + sqlTS + ")";
592 
    }
 612 
    }
593 
 
 613 
 
594 
    @Override
 614 
    @Override
595 
    public String getMonth(String sqlTS) {
 615 
    public String getMonth(String sqlTS) {
596 
        return "SELECT DATEPART(month, " + sqlTS + ")";
 616 
        return "SELECT DATEPART(month, " + sqlTS + ")";
597 
    }
 617 
    }
598 
 
 618 
 
599 
    @Override
 619 
    @Override
600 
    public String getFormatTimestamp(String sqlTS, boolean basic) {
 620 
    public String getFormatTimestamp(String sqlTS, boolean basic) {
601 
        final String extended = "CONVERT(nvarchar(30), " + sqlTS + ", 126) + '000'";
 621 
        final String extended = "CONVERT(nvarchar(30), " + sqlTS + ", 126) + '000'";
602 
        if (basic) {
 622 
        if (basic) {
603 
            return "replace( replace( " + extended + ", '-', ''), ':' , '' )";
 623 
            return "replace( replace( " + extended + ", '-', ''), ':' , '' )";
604 
        } else {
 624 
        } else {
605 
            return extended;
 625 
            return extended;
606 
        }
 626 
        }
607 
    }
 627 
    }
608 
 
 628 
 
609 
    @Override
 629 
    @Override
610 
    public String getFormatTimestamp(String sqlTS, String nativeFormat) {
 630 
    public String getFormatTimestamp(String sqlTS, String nativeFormat) {
611 
        return "FORMAT(" + sqlTS + ", " + nativeFormat + ")";
 631 
        return "FORMAT(" + sqlTS + ", " + nativeFormat + ")";
612 
    }
 632 
    }
613 
 
 633 
 
614 
    @Override
 634 
    @Override
615 
    public String quoteForTimestampFormat(String text) {
 635 
    public String quoteForTimestampFormat(String text) {
616 
        return StringUtils.doubleQuote(text);
 636 
        return StringUtils.doubleQuote(text);
617 
    }
 637 
    }
- 
 
 638 
 
- 
 
 639 
    @Override
- 
 
 640 
    public String getSessionIDExpression() {
- 
 
 641 
        return "@@SPID";
- 
 
 642 
    }
- 
 
 643 
 
- 
 
 644 
    @Override
- 
 
 645 
    public String getSessionsQuery(final DBSystemRoot sysRoot, final boolean includeSelf) {
- 
 
 646 
        final String allRows = "SELECT ps.spid as \"ID\", cmd as \"QUERY\", users.name as \"USER_NAME\" FROM sys.sysprocesses ps\n"
- 
 
 647 
                //
- 
 
 648 
                + "JOIN sys.sysdatabases db on db.dbid = ps.dbid\nJOIN sys.sysusers users on users.uid = ps.uid\\n"
- 
 
 649 
                //
- 
 
 650 
                + " WHERE db.name=" + quoteString(sysRoot.getName());
- 
 
 651 
        if (includeSelf)
- 
 
 652 
            return allRows;
- 
 
 653 
        return allRows + " and ps.spid != " + this.getSessionIDExpression();
- 
 
 654 
    }
- 
 
 655 
 
- 
 
 656 
    @Override
- 
 
 657 
    public String getVersionFunction() {
- 
 
 658 
        // if this doesn't work, try SERVERPROPERTY('ProductVersion')
- 
 
 659 
        return "@@VERSION";
- 
 
 660 
    }
618 
}
 661 
}