/**********************************************************************
Copyright (c) 2002 Mike Martin (TJDO) and others. All rights reserved.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
Contributors:
2003 Andy Jefferson - coding standards
...
**********************************************************************/
package org.jpox.store.rdbms.adapter;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import org.jpox.store.mapped.DatastoreContainerObject;
import org.jpox.store.mapped.DatastoreIdentifier;
import org.jpox.store.mapped.expression.BooleanExpression;
import org.jpox.store.mapped.expression.LogicSetExpression;
import org.jpox.store.mapped.expression.NumericExpression;
import org.jpox.store.mapped.expression.QueryExpression;
import org.jpox.store.mapped.expression.ScalarExpression;
import org.jpox.store.mapped.expression.SqlTemporalExpression;
import org.jpox.store.mapped.expression.StringExpression;
import org.jpox.store.mapped.expression.TableExprAsJoins;
import org.jpox.store.rdbms.columninfo.ColumnInfo;
import org.jpox.store.rdbms.columninfo.MSSQLServerColumnInfo;
import org.jpox.store.rdbms.key.ForeignKey;
import org.jpox.store.rdbms.table.Table;
import org.jpox.store.rdbms.typeinfo.MSSQLTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
/**
* Provides methods for adapting SQL language elements to the Microsoft SQL
* Server database.
*
* @see DatabaseAdapter
* @version $Revision: 1.40 $
*/
public class MSSQLServerAdapter extends DatabaseAdapter
{
/**
* Microsoft SQL Server 2000 uses reserved keywords for defining,
* manipulating, and accessing databases. Reserved keywords are part of the
* grammar of the Transact-SQL language used by SQL Server to parse and
* understand Transact-SQL statements and batches. Although it is
* syntactically possible to use SQL Server reserved keywords as identifiers
* and object names in Transact-SQL scripts, this can be done only using
* delimited identifiers.
*/
private static final String MSSQL_RESERVED_WORDS =
"ADD,ALL,ALTER,AND,ANY,AS," +
"ASC,AUTHORIZATION,BACKUP,BEGIN,BETWEEN,BREAK," +
"BROWSE,BULK,BY,CASCADE,CASE,CHECK," +
"CHECKPOINT,CLOSE,CLUSTERED,COALESCE,COLLATE,COLUMN," +
"COMMIT,COMPUTE,CONSTRAINT,CONTAINS,CONTAINSTABLE,CONTINUE," +
"CONVERT,CREATE,CROSS,CURRENT,CURRENT_DATE,CURRENT_TIME," +
"CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DBCC,DEALLOCATE,DECLARE," +
"DEFAULT,DELETE,DENY,DESC,DISK,DISTINCT," +
"DISTRIBUTED,DOUBLE,DROP,DUMMY,DUMP,ELSE," +
"END,ERRLVL,ESCAPE,EXCEPT,EXEC,EXECUTE," +
"EXISTS,EXIT,FETCH,FILE,FILLFACTOR,FOR," +
"FOREIGN,FREETEXT,FREETEXTTABLE,FROM,FULL,FUNCTION," +
"GOTO,GRANT,GROUP,HAVING,HOLDLOCK,IDENTITY," +
"IDENTITY_INSERT,IDENTITYCOL,IF,IN,INDEX,INNER," +
"INSERT,INTERSECT,INTO,IS,JOIN,KEY," +
"KILL,LEFT,LIKE,LINENO,LOAD,NATIONAL," +
"NOCHECK,NONCLUSTERED,NOT,NULL,NULLIF,OF," +
"OFF,OFFSETS,ON,OPEN,OPENDATASOURCE,OPENQUERY," +
"OPENROWSET,OPENXML,OPTION,OR,ORDER,OUTER," +
"OVER,PERCENT,PLAN,PRECISION,PRIMARY,PRINT," +
"PROC,PROCEDURE,PUBLIC,RAISERROR,READ,READTEXT," +
"RECONFIGURE,REFERENCES,REPLICATION,RESTORE,RESTRICT,RETURN," +
"REVOKE,RIGHT,ROLLBACK,ROWCOUNT,ROWGUIDCOL,RULE," +
"SAVE,SCHEMA,SELECT,SESSION_USER,SET,SETUSER," +
"SHUTDOWN,SOME,STATISTICS,SYSTEM_USER,TABLE,TEXTSIZE," +
"THEN,TO,TOP,TRAN,DATABASE,TRANSACTION,TRIGGER," +
"TRUNCATE,TSEQUAL,UNION,UNIQUE,UPDATE,UPDATETEXT," +
"USE,USER,VALUES,VARYING,VIEW,WAITFOR," +
"WHEN,WHERE,WHILE,WITH,WRITETEXT";
/**
* Constructs a Microsoft SQL Server adapter based on the given JDBC
* metadata.
* @param metadata the database metadata.
*/
public MSSQLServerAdapter(DatabaseMetaData metadata)
{
super(metadata);
reservedKeywords.addAll(parseKeywordList(MSSQL_RESERVED_WORDS));
// Add on any missing JDBC types
TypeInfo ti = new MSSQLTypeInfo("UNIQUEIDENTIFIER",
(short)Types.CHAR,
36,
"'",
"'",
"",
1,
false,
(short)2,
false,
false,
false,
"UNIQUEIDENTIFIER",
(short)0,
(short)0,
10);
ti.allowsPrecisionSpec = false;
addTypeInfo((short)MSSQLTypeInfo.UNIQUEIDENTIFIER, ti, true);
ti = new MSSQLTypeInfo("IMAGE",
(short)Types.BLOB,
2147483647,
null,
null,
null,
1,
false,
(short)1,
false,
false,
false,
"BLOB",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.BLOB, ti, true);
ti = new MSSQLTypeInfo("TEXT",
(short)Types.CLOB,
2147483647,
null,
null,
null,
1,
true,
(short)1,
false,
false,
false,
"TEXT",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.CLOB, ti, true);
ti = new MSSQLTypeInfo("float", //double precision
(short)Types.DOUBLE,
53,
null,
null,
null,
1,
false,
(short)2,
false,
false,
false,
null,
(short)0,
(short)0,
2);
addTypeInfo((short)Types.DOUBLE, ti, true);
ti = new MSSQLTypeInfo("IMAGE",
(short)Types.LONGVARBINARY,
2147483647,
null,
null,
null,
1,
false,
(short)1,
false,
false,
false,
"LONGVARBINARY",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.LONGVARBINARY, ti, true);
// Update any defaults
// MSSQL 2005 sometimes puts "uniqueidentifier" before "char" in the list
setDefaultTypeInfoForJDBCType(Types.CHAR, "char");
}
public String getVendorID()
{
return "sqlserver";
}
/**
* Accessor for the catalog name.
* @param conn The Connection to use
* @return The catalog name used by this connection
* @throws SQLException
*/
public String getCatalogName(Connection conn)
throws SQLException
{
String catalog = conn.getCatalog();
// the ProbeTable approach returns empty string instead of null here,
// so do the same
return catalog != null ? catalog : "";
}
public String getSchemaName(Connection conn) throws SQLException
{
/*
* As of version 7 there was no equivalent to the concept of "schema"
* in SQL Server. For DatabaseMetaData functions that include
* SCHEMA_NAME drivers usually return the user name that owns the table.
*
* So the default ProbeTable method for determining the current schema
* just ends up returning the current user. If we then use that name in
* performing metadata queries our results may get filtered down to just
* objects owned by that user. So instead we report the schema name as
* null which should cause those queries to return everything.
*
* DO not use the user name here, as in MSSQL, you are able to use
* an user name and access any schema
*/
/*
* use an empty string, otherwise fullyqualified object name is
* invalid
* In MSSQL, fully qualified must include the object owner, or an empty owner
* <catalog>.<schema>.<object> e.g. mycatalog..mytable
*/
return "";
}
/**
* The function to creates a unique value of type uniqueidentifier.
* @return The function. e.g. "SELECT NEWID()"
**/
public String getSelectNewUUIDStmt()
{
return "SELECT NEWID()";
}
/**
* The function to creates a unique value of type uniqueidentifier.
* @return The function. e.g. "NEWID()"
**/
public String getNewUUIDFunction()
{
return "NEWID()";
}
public boolean supportsBooleanComparison()
{
return false;
}
public boolean supportsDeferredConstraints()
{
return false;
}
/**
* Whether this datastore supports the specified foreign key update action
* @param action The update action
* @return Whether it is supported
*/
public boolean supportsForeignKeyUpdateAction(ForeignKey.FKAction action)
{
if (action == ForeignKey.CASCADE_ACTION)
{
return true;
}
return false;
}
/**
* Whether this datastore supports the specified foreign key delete action
* @param action The delete action
* @return Whether it is supported
*/
public boolean supportsForeignKeyDeleteAction(ForeignKey.FKAction action)
{
if (action == ForeignKey.CASCADE_ACTION)
{
return true;
}
return false;
}
/**
* Whether the datastore will support setting the query fetch size to the supplied value.
* @param size The value to set to
* @return Whether it is supported.
*/
public boolean supportsQueryFetchSize(int size)
{
if (size < 1)
{
// MSSQL doesnt support setting to 0
return false;
}
else
{
return true;
}
}
public ColumnInfo newColumnInfo(ResultSet rs)
{
return new MSSQLServerColumnInfo(rs);
}
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
public TypeInfo newTypeInfo(ResultSet rs)
{
TypeInfo ti = new MSSQLTypeInfo(rs);
/*
* Discard the tinyint type because it doesn't support negative values.
*/
if (ti.typeName.toLowerCase().startsWith("tinyint"))
{
return null;
}
/*
* unique identifiers does not allow any precision
*/
if (ti.typeName.equalsIgnoreCase("uniqueidentifier"))
{
ti.allowsPrecisionSpec = false;
}
return ti;
}
public String getDropTableStatement(DatastoreContainerObject table)
{
return "DROP TABLE " + table.toString();
}
public NumericExpression lengthMethod(StringExpression str)
{
ArrayList args = new ArrayList();
args.add(str);
return new NumericExpression("LEN", args);
}
public StringExpression substringMethod(StringExpression str,
NumericExpression begin)
{
ArrayList args = new ArrayList();
args.add(str);
args.add(begin.add(getMapping(BigInteger.class, str).newLiteral(str.getQueryExpression(), BigInteger.ONE)));
args.add(lengthMethod(str).sub(begin));
return new StringExpression("SUBSTRING", args);
}
public StringExpression substringMethod(StringExpression str,
NumericExpression begin,
NumericExpression end)
{
ArrayList args = new ArrayList();
args.add(str);
args.add(begin.add(getMapping(BigInteger.class, str).newLiteral(str.getQueryExpression(), BigInteger.ONE)));
args.add(end.sub(begin));
return new StringExpression("SUBSTRING", args);
}
/**
* Returns the appropriate SQL expression for the java query "trim" method.
* It should return something like:
* <pre>LTRIM(RTRIM(str))</pre>
* @param str The first argument to the trim() method.
* @param leading Whether to trim leading spaces
* @param trailing Whether to trim trailing spaces
* @return The text of the SQL expression.
*/
public StringExpression trimMethod(StringExpression str, boolean leading, boolean trailing)
{
ArrayList args = new ArrayList();
args.add(str);
if (leading && trailing)
{
StringExpression strExpr = new StringExpression("RTRIM", args);
args.clear();
args.add(strExpr);
return new StringExpression("LTRIM", args);
}
else if (leading)
{
return new StringExpression("LTRIM", args);
}
else if (trailing)
{
return new StringExpression("RTRIM", args);
}
return str;
}
/**
* Method to handle the starts with operation.
* @param source The expression with the searched string
* @param str The expression for the search string
* @return The expression.
**/
public BooleanExpression startsWithMethod(ScalarExpression source, ScalarExpression str)
{
ScalarExpression integerLiteral = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
args.add(str);
args.add(source);
return new BooleanExpression(new StringExpression("CHARINDEX", args),ScalarExpression.OP_EQ,integerLiteral);
}
/**
* Method to handle the indexOf operation.
* @param source The expression with the searched string
* @param str The expression for the search string
* @param from The from position (or null if not specified)
* @return The expression.
**/
public NumericExpression indexOfMethod(ScalarExpression source, ScalarExpression str, NumericExpression from)
{
ScalarExpression integerLiteral = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
args.add(str);
args.add(source);
if (from != null)
{
// Add 1 to the passed in value so that it is of origin 1 to be compatible with CHARINDEX
args.add(new NumericExpression(from, ScalarExpression.OP_ADD, integerLiteral));
}
NumericExpression locateExpr = new NumericExpression("CHARINDEX", args);
// Subtract 1 from the result of CHARINDEX to be consistent with Java strings
// TODO Would be nice to put this in parentheses
return new NumericExpression(locateExpr, ScalarExpression.OP_SUB, integerLiteral);
}
/**
* Whether we support auto-increment fields.
* @return whether we support auto-increment fields.
**/
public boolean supportsIdentityFields()
{
return true;
}
/**
* Accessor for the auto-increment sql statement for this datastore.
* @param table Name of the table that the autoincrement is for
* @param columnName Name of the column that the autoincrement is for
* @return The statement for getting the latest auto-increment key
**/
public String getAutoIncrementStmt(Table table, String columnName)
{
return "SELECT @@IDENTITY";
}
/**
* Accessor for the auto-increment keyword for generating DDLs (CREATE TABLEs...).
* @return The keyword for a column using auto-increment
**/
public String getAutoIncrementKeyword()
{
return "IDENTITY";
}
/**
* Verifies if the given <code>columnDef</code> is auto incremented by the datastore.
* @param columnDef the datastore type name
* @return true when the <code>columnDef</code> has values auto incremented by the datastore
**/
public boolean isIdentityFieldDataType(String columnDef)
{
if (columnDef == null)
{
return false;
}
else if (columnDef.equalsIgnoreCase("uniqueidentifier"))
{
return true;
}
return false;
}
/**
* Method to return the INSERT statement to use when inserting into a table that has no
* columns specified. This is the case when we have a single column in the table and that column
* is autoincrement/identity (and so is assigned automatically in the datastore).
* @param table The table
* @return The statement for the INSERT
*/
public String getInsertStatementForNoColumns(Table table)
{
return "INSERT INTO " + table.toString() + " DEFAULT VALUES";
}
/**
* An operator in a string expression that concatenates two or more
* character or binary strings, columns, or a combination of strings and
* column names into one expression (a string operator).
*
* @return the operator SQL String
*/
public String getOperatorConcat()
{
return "+";
}
public String getSelectWithLockOption()
{
return "(UPDLOCK, ROWLOCK)";
}
/**
* Determines whether the {@link #getSelectWithLockOption()} is to be placed right
* before the FROM clause, or at the end of the statement
* @return true if option has to be placed right after the from clause
*/
public boolean getPlaceWithOptionAfterFromClause()
{
return true;
}
/**
* Determines whether lock option has to be placed within JOIN clauses as well.
* @return true if option has to be placed right after the join clause
*/
public boolean getPlaceWithOptionWithinJoinClauses()
{
return true;
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getHour()
* method. It should return something like:
* <pre>HOUR(time)</pre>
* @param time The time for the getHour() method.
* @return The text of the SQL expression.
*/
public NumericExpression getHourMethod(SqlTemporalExpression time)
{
ArrayList args = new ArrayList();
args.add("hh");
args.add(time);
return new NumericExpression("DATEPART", args);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getMinute()
* method. It should return something like:
* <pre>MINUTE(time)</pre>
* @param time The time for the getMinute() method.
* @return The text of the SQL expression.
*/
public NumericExpression getMinuteMethod(SqlTemporalExpression time)
{
ArrayList args = new ArrayList();
args.add("mi");
args.add(time);
return new NumericExpression("DATEPART", args);
}
/**
* Returns the appropriate SQL expression for the JDOQL Time.getSecond()
* method. It should return something like:
* <pre>SECOND(time)</pre>
* @param time The time for the getSecond() method.
* @return The text of the SQL expression.
*/
public NumericExpression getSecondMethod(SqlTemporalExpression time)
{
ArrayList args = new ArrayList();
args.add("ss");
args.add(time);
return new NumericExpression("DATEPART", args);
}
}