1 /*
2 * Hibernate, Relational Persistence for Idiomatic Java
3 *
4 * Copyright (c) 2008, Red Hat Middleware LLC or third-party contributors as
5 * indicated by the @author tags or express copyright attribution
6 * statements applied by the authors. All third-party contributions are
7 * distributed under license by Red Hat Middleware LLC.
8 *
9 * This copyrighted material is made available to anyone wishing to use, modify,
10 * copy, or redistribute it subject to the terms and conditions of the GNU
11 * Lesser General Public License, as published by the Free Software Foundation.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
15 * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
16 * for more details.
17 *
18 * You should have received a copy of the GNU Lesser General Public License
19 * along with this distribution; if not, write to:
20 * Free Software Foundation, Inc.
21 * 51 Franklin Street, Fifth Floor
22 * Boston, MA 02110-1301 USA
23 *
24 */
25 package org.hibernate.dialect;
26
27 import java.sql.CallableStatement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.sql.Types;
31
32 import org.hibernate.Hibernate;
33 import org.hibernate.cfg.Environment;
34 import org.hibernate.dialect.function.NoArgSQLFunction;
35 import org.hibernate.dialect.function.StandardSQLFunction;
36 import org.hibernate.util.StringHelper;
37
38 /**
39 * An SQL dialect for MySQL (prior to 5.x).
40 *
41 * @author Gavin King
42 */
43 public class MySQLDialect extends Dialect {
44
45 public MySQLDialect() {
46 super();
47 registerColumnType( Types.BIT, "bit" );
48 registerColumnType( Types.BIGINT, "bigint" );
49 registerColumnType( Types.SMALLINT, "smallint" );
50 registerColumnType( Types.TINYINT, "tinyint" );
51 registerColumnType( Types.INTEGER, "integer" );
52 registerColumnType( Types.CHAR, "char(1)" );
53 registerColumnType( Types.FLOAT, "float" );
54 registerColumnType( Types.DOUBLE, "double precision" );
55 registerColumnType( Types.DATE, "date" );
56 registerColumnType( Types.TIME, "time" );
57 registerColumnType( Types.TIMESTAMP, "datetime" );
58 registerColumnType( Types.VARBINARY, "longblob" );
59 registerColumnType( Types.VARBINARY, 16777215, "mediumblob" );
60 registerColumnType( Types.VARBINARY, 65535, "blob" );
61 registerColumnType( Types.VARBINARY, 255, "tinyblob" );
62 registerColumnType( Types.NUMERIC, "decimal($p,$s)" );
63 registerColumnType( Types.BLOB, "longblob" );
64 // registerColumnType( Types.BLOB, 16777215, "mediumblob" );
65 // registerColumnType( Types.BLOB, 65535, "blob" );
66 registerColumnType( Types.CLOB, "longtext" );
67 // registerColumnType( Types.CLOB, 16777215, "mediumtext" );
68 // registerColumnType( Types.CLOB, 65535, "text" );
69 registerVarcharTypes();
70
71 registerFunction("ascii", new StandardSQLFunction("ascii", Hibernate.INTEGER) );
72 registerFunction("bin", new StandardSQLFunction("bin", Hibernate.STRING) );
73 registerFunction("char_length", new StandardSQLFunction("char_length", Hibernate.LONG) );
74 registerFunction("character_length", new StandardSQLFunction("character_length", Hibernate.LONG) );
75 registerFunction("lcase", new StandardSQLFunction("lcase") );
76 registerFunction("lower", new StandardSQLFunction("lower") );
77 registerFunction("length", new StandardSQLFunction("length", Hibernate.LONG) );
78 registerFunction("ltrim", new StandardSQLFunction("ltrim") );
79 registerFunction("ord", new StandardSQLFunction("ord", Hibernate.INTEGER) );
80 registerFunction("quote", new StandardSQLFunction("quote") );
81 registerFunction("reverse", new StandardSQLFunction("reverse") );
82 registerFunction("rtrim", new StandardSQLFunction("rtrim") );
83 registerFunction("soundex", new StandardSQLFunction("soundex") );
84 registerFunction("space", new StandardSQLFunction("space", Hibernate.STRING) );
85 registerFunction("ucase", new StandardSQLFunction("ucase") );
86 registerFunction("upper", new StandardSQLFunction("upper") );
87 registerFunction("unhex", new StandardSQLFunction("unhex", Hibernate.STRING) );
88
89 registerFunction("abs", new StandardSQLFunction("abs") );
90 registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) );
91
92 registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) );
93 registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) );
94 registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) );
95 registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) );
96 registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE) );
97 registerFunction("crc32", new StandardSQLFunction("crc32", Hibernate.LONG) );
98 registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) );
99 registerFunction("ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) );
100 registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE) );
101 registerFunction("log2", new StandardSQLFunction("log2", Hibernate.DOUBLE) );
102 registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE) );
103 registerFunction("pi", new NoArgSQLFunction("pi", Hibernate.DOUBLE) );
104 registerFunction("rand", new NoArgSQLFunction("rand", Hibernate.DOUBLE) );
105 registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) );
106 registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
107 registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) );
108
109 registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE) );
110 registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE) );
111
112 registerFunction("ceiling", new StandardSQLFunction("ceiling", Hibernate.INTEGER) );
113 registerFunction("ceil", new StandardSQLFunction("ceil", Hibernate.INTEGER) );
114 registerFunction("floor", new StandardSQLFunction("floor", Hibernate.INTEGER) );
115 registerFunction("round", new StandardSQLFunction("round", Hibernate.INTEGER) );
116
117 registerFunction("datediff", new StandardSQLFunction("datediff", Hibernate.INTEGER) );
118 registerFunction("timediff", new StandardSQLFunction("timediff", Hibernate.TIME) );
119 registerFunction("date_format", new StandardSQLFunction("date_format", Hibernate.STRING) );
120
121 registerFunction("curdate", new NoArgSQLFunction("curdate", Hibernate.DATE) );
122 registerFunction("curtime", new NoArgSQLFunction("curtime", Hibernate.TIME) );
123 registerFunction("current_date", new NoArgSQLFunction("current_date", Hibernate.DATE, false) );
124 registerFunction("current_time", new NoArgSQLFunction("current_time", Hibernate.TIME, false) );
125 registerFunction("current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP, false) );
126 registerFunction("date", new StandardSQLFunction("date", Hibernate.DATE) );
127 registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) );
128 registerFunction("dayofmonth", new StandardSQLFunction("dayofmonth", Hibernate.INTEGER) );
129 registerFunction("dayname", new StandardSQLFunction("dayname", Hibernate.STRING) );
130 registerFunction("dayofweek", new StandardSQLFunction("dayofweek", Hibernate.INTEGER) );
131 registerFunction("dayofyear", new StandardSQLFunction("dayofyear", Hibernate.INTEGER) );
132 registerFunction("from_days", new StandardSQLFunction("from_days", Hibernate.DATE) );
133 registerFunction("from_unixtime", new StandardSQLFunction("from_unixtime", Hibernate.TIMESTAMP) );
134 registerFunction("hour", new StandardSQLFunction("hour", Hibernate.INTEGER) );
135 registerFunction("last_day", new StandardSQLFunction("last_day", Hibernate.DATE) );
136 registerFunction("localtime", new NoArgSQLFunction("localtime", Hibernate.TIMESTAMP) );
137 registerFunction("localtimestamp", new NoArgSQLFunction("localtimestamp", Hibernate.TIMESTAMP) );
138 registerFunction("microseconds", new StandardSQLFunction("microseconds", Hibernate.INTEGER) );
139 registerFunction("minute", new StandardSQLFunction("minute", Hibernate.INTEGER) );
140 registerFunction("month", new StandardSQLFunction("month", Hibernate.INTEGER) );
141 registerFunction("monthname", new StandardSQLFunction("monthname", Hibernate.STRING) );
142 registerFunction("now", new NoArgSQLFunction("now", Hibernate.TIMESTAMP) );
143 registerFunction("quarter", new StandardSQLFunction("quarter", Hibernate.INTEGER) );
144 registerFunction("second", new StandardSQLFunction("second", Hibernate.INTEGER) );
145 registerFunction("sec_to_time", new StandardSQLFunction("sec_to_time", Hibernate.TIME) );
146 registerFunction("sysdate", new NoArgSQLFunction("sysdate", Hibernate.TIMESTAMP) );
147 registerFunction("time", new StandardSQLFunction("time", Hibernate.TIME) );
148 registerFunction("timestamp", new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
149 registerFunction("time_to_sec", new StandardSQLFunction("time_to_sec", Hibernate.INTEGER) );
150 registerFunction("to_days", new StandardSQLFunction("to_days", Hibernate.LONG) );
151 registerFunction("unix_timestamp", new StandardSQLFunction("unix_timestamp", Hibernate.LONG) );
152 registerFunction("utc_date", new NoArgSQLFunction("utc_date", Hibernate.STRING) );
153 registerFunction("utc_time", new NoArgSQLFunction("utc_time", Hibernate.STRING) );
154 registerFunction("utc_timestamp", new NoArgSQLFunction("utc_timestamp", Hibernate.STRING) );
155 registerFunction("week", new StandardSQLFunction("week", Hibernate.INTEGER) );
156 registerFunction("weekday", new StandardSQLFunction("weekday", Hibernate.INTEGER) );
157 registerFunction("weekofyear", new StandardSQLFunction("weekofyear", Hibernate.INTEGER) );
158 registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER) );
159 registerFunction("yearweek", new StandardSQLFunction("yearweek", Hibernate.INTEGER) );
160
161 registerFunction("hex", new StandardSQLFunction("hex", Hibernate.STRING) );
162 registerFunction("oct", new StandardSQLFunction("oct", Hibernate.STRING) );
163
164 registerFunction("octet_length", new StandardSQLFunction("octet_length", Hibernate.LONG) );
165 registerFunction("bit_length", new StandardSQLFunction("bit_length", Hibernate.LONG) );
166
167 registerFunction("bit_count", new StandardSQLFunction("bit_count", Hibernate.LONG) );
168 registerFunction("encrypt", new StandardSQLFunction("encrypt", Hibernate.STRING) );
169 registerFunction("md5", new StandardSQLFunction("md5", Hibernate.STRING) );
170 registerFunction("sha1", new StandardSQLFunction("sha1", Hibernate.STRING) );
171 registerFunction("sha", new StandardSQLFunction("sha", Hibernate.STRING) );
172
173 registerFunction( "concat", new StandardSQLFunction( "concat", Hibernate.STRING ) );
174
175 getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2");
176 getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
177 }
178
179 protected void registerVarcharTypes() {
180 registerColumnType( Types.VARCHAR, "longtext" );
181 // registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
182 // registerColumnType( Types.VARCHAR, 65535, "text" );
183 registerColumnType( Types.VARCHAR, 255, "varchar($l)" );
184 }
185
186 public String getAddColumnString() {
187 return "add column";
188 }
189
190 public boolean qualifyIndexName() {
191 return false;
192 }
193
194 public boolean supportsIdentityColumns() {
195 return true;
196 }
197
198 public String getIdentitySelectString() {
199 return "select last_insert_id()";
200 }
201
202 public String getIdentityColumnString() {
203 return "not null auto_increment"; //starts with 1, implicitly
204 }
205
206 public String getAddForeignKeyConstraintString(
207 String constraintName,
208 String[] foreignKey,
209 String referencedTable,
210 String[] primaryKey, boolean referencesPrimaryKey
211 ) {
212 String cols = StringHelper.join(", ", foreignKey);
213 return new StringBuffer(30)
214 .append(" add index ")
215 .append(constraintName)
216 .append(" (")
217 .append(cols)
218 .append("), add constraint ")
219 .append(constraintName)
220 .append(" foreign key (")
221 .append(cols)
222 .append(") references ")
223 .append(referencedTable)
224 .append(" (")
225 .append( StringHelper.join(", ", primaryKey) )
226 .append(')')
227 .toString();
228 }
229
230 public boolean supportsLimit() {
231 return true;
232 }
233
234 public String getDropForeignKeyString() {
235 return " drop foreign key ";
236 }
237
238 public String getLimitString(String sql, boolean hasOffset) {
239 return new StringBuffer( sql.length() + 20 )
240 .append( sql )
241 .append( hasOffset ? " limit ?, ?" : " limit ?" )
242 .toString();
243 }
244
245 public char closeQuote() {
246 return '`';
247 }
248
249 public char openQuote() {
250 return '`';
251 }
252
253 public boolean supportsIfExistsBeforeTableName() {
254 return true;
255 }
256
257 public String getSelectGUIDString() {
258 return "select uuid()";
259 }
260
261 public boolean supportsCascadeDelete() {
262 return false;
263 }
264
265 public String getTableComment(String comment) {
266 return " comment='" + comment + "'";
267 }
268
269 public String getColumnComment(String comment) {
270 return " comment '" + comment + "'";
271 }
272
273 public boolean supportsTemporaryTables() {
274 return true;
275 }
276
277 public String getCreateTemporaryTableString() {
278 return "create temporary table if not exists";
279 }
280
281 public String getCastTypeName(int code) {
282 if ( code==Types.INTEGER ) {
283 return "signed";
284 }
285 else if ( code==Types.VARCHAR ) {
286 return "char";
287 }
288 else if ( code==Types.VARBINARY ) {
289 return "binary";
290 }
291 else {
292 return super.getCastTypeName( code );
293 }
294 }
295
296 public boolean supportsCurrentTimestampSelection() {
297 return true;
298 }
299
300 public boolean isCurrentTimestampSelectStringCallable() {
301 return false;
302 }
303
304 public String getCurrentTimestampSelectString() {
305 return "select now()";
306 }
307
308 public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
309 return col;
310 }
311
312 public ResultSet getResultSet(CallableStatement ps) throws SQLException {
313 boolean isResultSet = ps.execute();
314 while (!isResultSet && ps.getUpdateCount() != -1) {
315 isResultSet = ps.getMoreResults();
316 }
317 return ps.getResultSet();
318 }
319
320 public boolean supportsRowValueConstructorSyntax() {
321 return true;
322 }
323
324 public Boolean performTemporaryTableDDLInIsolation() {
325 return Boolean.FALSE;
326 }
327
328
329 // Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
330
331 public boolean supportsEmptyInList() {
332 return false;
333 }
334
335 public boolean areStringComparisonsCaseInsensitive() {
336 return true;
337 }
338
339 public boolean supportsLobValueChangePropogation() {
340 // note: at least my local MySQL 5.1 install shows this not working...
341 return false;
342 }
343
344 public boolean supportsSubqueryOnMutatingTable() {
345 return false;
346 }
347 }
Save This Page