Source code: org/hibernate/dialect/MySQLDialect.java
1 //$Id: MySQLDialect.java,v 1.28 2005/04/13 04:44:35 oneovthafew Exp $
2 package org.hibernate.dialect;
3
4 import java.sql.Types;
5
6 import org.hibernate.Hibernate;
7 import org.hibernate.cfg.Environment;
8 import org.hibernate.dialect.function.NoArgSQLFunction;
9 import org.hibernate.dialect.function.StandardSQLFunction;
10 import org.hibernate.exception.ErrorCodeConverter;
11 import org.hibernate.exception.SQLExceptionConverter;
12 import org.hibernate.exception.ViolatedConstraintNameExtracter;
13 import org.hibernate.util.StringHelper;
14
15 /**
16 * An SQL dialect for MySQL.
17 * @author Gavin King
18 */
19 public class MySQLDialect extends Dialect {
20
21 public MySQLDialect() {
22 super();
23 registerColumnType( Types.BIT, "bit" );
24 registerColumnType( Types.BIGINT, "bigint" );
25 registerColumnType( Types.SMALLINT, "smallint" );
26 registerColumnType( Types.TINYINT, "tinyint" );
27 registerColumnType( Types.INTEGER, "integer" );
28 registerColumnType( Types.CHAR, "char(1)" );
29 registerColumnType( Types.VARCHAR, "longtext" );
30 registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
31 registerColumnType( Types.VARCHAR, 65535, "text" );
32 registerColumnType( Types.VARCHAR, 255, "varchar($l)" );
33 registerColumnType( Types.FLOAT, "float" );
34 registerColumnType( Types.DOUBLE, "double precision" );
35 registerColumnType( Types.DATE, "date" );
36 registerColumnType( Types.TIME, "time" );
37 registerColumnType( Types.TIMESTAMP, "datetime" );
38 registerColumnType( Types.VARBINARY, "longblob" );
39 registerColumnType( Types.VARBINARY, 16777215, "mediumblob" );
40 registerColumnType( Types.VARBINARY, 65535, "blob" );
41 registerColumnType( Types.VARBINARY, 255, "tinyblob" );
42 registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
43 registerColumnType( Types.BLOB, "longblob" );
44 registerColumnType( Types.BLOB, 16777215, "mediumblob" );
45 registerColumnType( Types.BLOB, 65535, "blob" );
46 registerColumnType( Types.CLOB, "longtext" );
47 registerColumnType( Types.CLOB, 16777215, "mediumtext" );
48 registerColumnType( Types.CLOB, 65535, "text" );
49
50 registerFunction("ascii", new StandardSQLFunction("ascii", Hibernate.INTEGER) );
51 registerFunction("bin", new StandardSQLFunction("bin", Hibernate.STRING) );
52 registerFunction("char_length", new StandardSQLFunction("char_length", Hibernate.LONG) );
53 registerFunction("character_length", new StandardSQLFunction("character_length", Hibernate.LONG) );
54 registerFunction("lcase", new StandardSQLFunction("lcase") );
55 registerFunction("lower", new StandardSQLFunction("lower") );
56 registerFunction("length", new StandardSQLFunction("length", Hibernate.LONG) );
57 registerFunction("ltrim", new StandardSQLFunction("ltrim") );
58 registerFunction("ord", new StandardSQLFunction("ord", Hibernate.INTEGER) );
59 registerFunction("quote", new StandardSQLFunction("quote") );
60 registerFunction("reverse", new StandardSQLFunction("reverse") );
61 registerFunction("rtrim", new StandardSQLFunction("rtrim") );
62 registerFunction("soundex", new StandardSQLFunction("soundex") );
63 registerFunction("space", new StandardSQLFunction("space", Hibernate.STRING) );
64 registerFunction("ucase", new StandardSQLFunction("ucase") );
65 registerFunction("upper", new StandardSQLFunction("upper") );
66 registerFunction("unhex", new StandardSQLFunction("unhex", Hibernate.STRING) );
67
68 registerFunction("abs", new StandardSQLFunction("abs") );
69 registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) );
70
71 registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) );
72 registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) );
73 registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) );
74 registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) );
75 registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE) );
76 registerFunction("crc32", new StandardSQLFunction("crc32", Hibernate.LONG) );
77 registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) );
78 registerFunction("ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) );
79 registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE) );
80 registerFunction("log2", new StandardSQLFunction("log2", Hibernate.DOUBLE) );
81 registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE) );
82 registerFunction("pi", new NoArgSQLFunction("pi", Hibernate.DOUBLE) );
83 registerFunction("rand", new NoArgSQLFunction("rand", Hibernate.DOUBLE) );
84 registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) );
85 registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
86 registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) );
87
88 registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE) );
89 registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE) );
90
91 registerFunction("ceiling", new StandardSQLFunction("ceiling", Hibernate.INTEGER) );
92 registerFunction("ceil", new StandardSQLFunction("ceil", Hibernate.INTEGER) );
93 registerFunction("floor", new StandardSQLFunction("floor", Hibernate.INTEGER) );
94 registerFunction("round", new StandardSQLFunction("round", Hibernate.INTEGER) );
95
96 registerFunction("curdate", new NoArgSQLFunction("curdate", Hibernate.DATE) );
97 registerFunction("curtime", new NoArgSQLFunction("curtime", Hibernate.TIME) );
98 registerFunction("current_date", new NoArgSQLFunction("current_date", Hibernate.DATE, false) );
99 registerFunction("current_time", new NoArgSQLFunction("current_time", Hibernate.TIME, false) );
100 registerFunction("current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP, false) );
101 registerFunction("date", new StandardSQLFunction("date", Hibernate.DATE) );
102 registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) );
103 registerFunction("dayofmonth", new StandardSQLFunction("dayofmonth", Hibernate.INTEGER) );
104 registerFunction("dayname", new StandardSQLFunction("dayname", Hibernate.STRING) );
105 registerFunction("dayofweek", new StandardSQLFunction("dayofweek", Hibernate.INTEGER) );
106 registerFunction("dayofyear", new StandardSQLFunction("dayofyear", Hibernate.INTEGER) );
107 registerFunction("from_days", new StandardSQLFunction("from_days", Hibernate.DATE) );
108 registerFunction("from_unixtime", new StandardSQLFunction("from_unixtime", Hibernate.TIMESTAMP) );
109 registerFunction("hour", new StandardSQLFunction("hour", Hibernate.INTEGER) );
110 registerFunction("last_day", new StandardSQLFunction("last_day", Hibernate.DATE) );
111 registerFunction("localtime", new NoArgSQLFunction("localtime", Hibernate.TIMESTAMP) );
112 registerFunction("localtimestamp", new NoArgSQLFunction("localtimestamp", Hibernate.TIMESTAMP) );
113 registerFunction("microseconds", new StandardSQLFunction("microseconds", Hibernate.INTEGER) );
114 registerFunction("minute", new StandardSQLFunction("minute", Hibernate.INTEGER) );
115 registerFunction("month", new StandardSQLFunction("month", Hibernate.INTEGER) );
116 registerFunction("monthname", new StandardSQLFunction("monthname", Hibernate.STRING) );
117 registerFunction("now", new NoArgSQLFunction("now", Hibernate.TIMESTAMP) );
118 registerFunction("quarter", new StandardSQLFunction("quarter", Hibernate.INTEGER) );
119 registerFunction("second", new StandardSQLFunction("second", Hibernate.INTEGER) );
120 registerFunction("sec_to_time", new StandardSQLFunction("sec_to_time", Hibernate.TIME) );
121 registerFunction("sysdate", new NoArgSQLFunction("sysdate", Hibernate.TIMESTAMP) );
122 registerFunction("time", new StandardSQLFunction("time", Hibernate.TIME) );
123 registerFunction("timestamp", new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
124 registerFunction("time_to_sec", new StandardSQLFunction("time_to_sec", Hibernate.INTEGER) );
125 registerFunction("to_days", new StandardSQLFunction("to_days", Hibernate.LONG) );
126 registerFunction("unix_timestamp", new StandardSQLFunction("unix_timestamp", Hibernate.LONG) );
127 registerFunction("utc_date", new NoArgSQLFunction("utc_date", Hibernate.STRING) );
128 registerFunction("utc_time", new NoArgSQLFunction("utc_time", Hibernate.STRING) );
129 registerFunction("utc_timestamp", new NoArgSQLFunction("utc_timestamp", Hibernate.STRING) );
130 registerFunction("week", new StandardSQLFunction("week", Hibernate.INTEGER) );
131 registerFunction("weekday", new StandardSQLFunction("weekday", Hibernate.INTEGER) );
132 registerFunction("weekofyear", new StandardSQLFunction("weekofyear", Hibernate.INTEGER) );
133 registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER) );
134 registerFunction("yearweek", new StandardSQLFunction("yearweek", Hibernate.INTEGER) );
135
136 registerFunction("hex", new StandardSQLFunction("hex", Hibernate.STRING) );
137 registerFunction("oct", new StandardSQLFunction("oct", Hibernate.STRING) );
138
139 registerFunction("octet_length", new StandardSQLFunction("octet_length", Hibernate.LONG) );
140 registerFunction("bit_length", new StandardSQLFunction("bit_length", Hibernate.LONG) );
141
142 registerFunction("bit_count", new StandardSQLFunction("bit_count", Hibernate.LONG) );
143 registerFunction("encrypt", new StandardSQLFunction("encrypt", Hibernate.STRING) );
144 registerFunction("md5", new StandardSQLFunction("md5", Hibernate.STRING) );
145 registerFunction("sha1", new StandardSQLFunction("sha1", Hibernate.STRING) );
146 registerFunction("sha", new StandardSQLFunction("sha", Hibernate.STRING) );
147
148 registerFunction( "concat", new StandardSQLFunction( "concat", Hibernate.STRING ) );
149
150 getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2");
151 getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
152 }
153
154 public String getAddColumnString() {
155 return "add column";
156 }
157
158 public boolean qualifyIndexName() {
159 return false;
160 }
161
162 public boolean supportsIdentityColumns() {
163 return true;
164 }
165
166 public String getIdentitySelectString() {
167 return "select last_insert_id()";
168 }
169
170 public String getIdentityColumnString() {
171 return "not null auto_increment"; //starts with 1, implicitly
172 }
173
174 public String getAddForeignKeyConstraintString(
175 String constraintName,
176 String[] foreignKey,
177 String referencedTable,
178 String[] primaryKey
179 ) {
180 String cols = StringHelper.join(", ", foreignKey);
181 return new StringBuffer(30)
182 .append(" add index ")
183 .append(constraintName)
184 .append(" (")
185 .append(cols)
186 .append("), add constraint ")
187 .append(constraintName)
188 .append(" foreign key (")
189 .append(cols)
190 .append(") references ")
191 .append(referencedTable)
192 .append(" (")
193 .append( StringHelper.join(", ", primaryKey) )
194 .append(')')
195 .toString();
196 }
197
198 public boolean supportsLimit() {
199 return true;
200 }
201
202 public String getDropForeignKeyString() {
203 return " drop foreign key ";
204 }
205
206 public String getLimitString(String sql, boolean hasOffset) {
207 return new StringBuffer( sql.length()+20 )
208 .append(sql)
209 .append( hasOffset ? " limit ?, ?" : " limit ?")
210 .toString();
211 }
212
213 /*
214 * Temporary, until MySQL fix Connector/J bug
215 */
216 /*public String getLimitString(String sql, int offset, int limit) {
217 StringBuffer buf = new StringBuffer( sql.length()+20 )
218 .append(sql);
219 if (offset>0) {
220 buf.append(" limit ")
221 .append(offset)
222 .append(", ")
223 .append(limit);
224 }
225 else {
226 buf.append(" limit ")
227 .append(limit);
228 }
229 return buf.toString();
230 }*/
231
232 /*
233 * Temporary, until MySQL fix Connector/J bug
234 */
235 /*public boolean supportsVariableLimit() {
236 return false;
237 }*/
238
239 public char closeQuote() {
240 return '`';
241 }
242
243 public char openQuote() {
244 return '`';
245 }
246
247 public boolean supportsIfExistsBeforeTableName() {
248 return true;
249 }
250
251 public char getSchemaSeparator() {
252 return '_';
253 }
254
255 public String getSelectGUIDString() {
256 return "select uuid()";
257 }
258
259 public boolean supportsCascadeDelete() {
260 return false;
261 }
262
263 /**
264 * Build an instance of the SQLExceptionConverter preferred by this dialect for
265 * converting SQLExceptions into Hibernate's JDBCException hierarchy. The default
266 * Dialect implementation simply returns a converter based on X/Open SQLState codes.
267 * <p/>
268 * It is strongly recommended that specific Dialect implementations override this
269 * method, since interpretation of a SQL error is much more accurate when based on
270 * the ErrorCode rather than the SQLState. Unfortunately, the ErrorCode is a vendor-
271 * specific approach.
272 *
273 * @return The Dialect's preferred SQLExceptionConverter.
274 */
275 public SQLExceptionConverter buildSQLExceptionConverter() {
276 return new ExceptionConverter( getViolatedConstraintNameExtracter() );
277 }
278
279 private static class ExceptionConverter extends ErrorCodeConverter {
280 private int[] sqlGrammarCodes = new int[] { 1054, 1064, 1146 };
281 private int[] integrityViolationCodes = new int[] { 1062, 1216, 1217 };
282 private int[] connectionCodes = new int[] { 1049 };
283 private int[] lockAcquisitionErrorCodes = new int[] { 1099, 1100, 1150, 1165, 1192, 1205, 1206, 1207, 1213, 1223 };
284
285 public ExceptionConverter(ViolatedConstraintNameExtracter extracter) {
286 super(extracter);
287 }
288
289 protected int[] getSQLGrammarErrorCodes() {
290 return sqlGrammarCodes;
291 }
292
293 protected int[] getIntegrityViolationErrorCodes() {
294 return integrityViolationCodes;
295 }
296
297 protected int[] getConnectionErrorCodes() {
298 return connectionCodes;
299 }
300
301 protected int[] getLockAcquisitionErrorCodes() {
302 return lockAcquisitionErrorCodes;
303 }
304 }
305 }