You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ab...@apache.org on 2007/05/18 01:19:03 UTC
svn commit: r539164 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/reference/
testing/org/apache/derbyTesting/functionTest...
Author: abrown
Date: Thu May 17 16:19:02 2007
New Revision: 539164
URL: http://svn.apache.org/viewvc?view=rev&rev=539164
Log:
DERBY-2605: Patch to add compile-time checking of columns created from a
"CREATE TABLE AS ... WITH NO DATA" statement, to ensure that the resultant
table only includes columns with types that a user can create him/herself.
In particular this blocks indirect creation of BOOLEAN columns, Object
columns, and DECIMAL columns with precision greater than 31.
Contributed by: James F. Adams (derby@xemaps.com)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java?view=diff&rev=539164&r1=539163&r2=539164
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java Thu May 17 16:19:02 2007
@@ -1332,5 +1332,73 @@
* @return the formatID of this class
*/
public int getTypeFormatId() { return StoredFormatIds.DATA_TYPE_SERVICES_IMPL_V01_ID; }
+
+ /**
+ * Check to make sure that this type id is something a user can create
+ * him/herself directly through an SQL CREATE TABLE statement.
+ *
+ * This method is used for CREATE TABLE AS ... WITH [NO] DATA binding
+ * because it's possible for the query to return types which are not
+ * actually creatable for a user. DERBY-2605.
+ *
+ * Three examples are:
+ *
+ * BOOLEAN: A user can select boolean columns from system tables, but
+ * s/he is not allowed to create such a column him/herself.
+ *
+ * JAVA_OBJECT: A user can select columns of various java object types
+ * from system tables, but s/he is not allowed to create such a column
+ * him/herself.
+ *
+ * DECIMAL: A user can specify a VALUES clause with a constant that
+ * has a precision of greater than 31. Derby can apparently handle
+ * such a value internally, but the user is not supposed to be able
+ * create such a column him/herself.
+ *
+ * @return True if the type associated with this DTD can be created via
+ * the CREATE TABLE syntax; false otherwise.
+ */
+ public boolean isUserCreatableType() throws StandardException
+ {
+ switch (typeId.getJDBCTypeId())
+ {
+ case Types.BOOLEAN:
+ case Types.JAVA_OBJECT:
+ return false;
+ case Types.DECIMAL:
+ return
+ (getPrecision() <= typeId.getMaximumPrecision()) &&
+ (getScale() <= typeId.getMaximumScale()) &&
+ (getMaximumWidth() <= typeId.getMaximumMaximumWidth());
+ default: break;
+ }
+ return true;
+ }
+
+ /**
+ * Return the SQL type name and, if applicable, scale/precision/length
+ * for this DataTypeDescriptor. Note that we want the values from *this*
+ * object specifically, not the max values defined on this.typeId.
+ */
+ public String getFullSQLTypeName()
+ {
+ StringBuffer sbuf = new StringBuffer(typeId.getSQLTypeName());
+ if (typeId.isDecimalTypeId() || typeId.isNumericTypeId())
+ {
+ sbuf.append("(");
+ sbuf.append(getPrecision());
+ sbuf.append(", ");
+ sbuf.append(getScale());
+ sbuf.append(")");
+ }
+ else if (typeId.variableLength())
+ {
+ sbuf.append("(");
+ sbuf.append(getMaximumWidth());
+ sbuf.append(")");
+ }
+
+ return sbuf.toString();
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java?view=diff&rev=539164&r1=539163&r2=539164
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java Thu May 17 16:19:02 2007
@@ -297,8 +297,8 @@
{
ResultColumn rc = (ResultColumn) qeRCL.elementAt(index);
if (rc.isGenerated())
- {
- continue;
+ {
+ continue;
}
/* Raise error if column name is system generated. */
if (rc.isNameGenerated())
@@ -306,6 +306,16 @@
throw StandardException.newException(
SQLState.LANG_TABLE_REQUIRES_COLUMN_NAMES);
}
+
+ DataTypeDescriptor dtd = rc.getExpressionType();
+ if ((dtd != null) && !dtd.isUserCreatableType())
+ {
+ throw StandardException.newException(
+ SQLState.LANG_INVALID_COLUMN_TYPE_CREATE_TABLE,
+ dtd.getFullSQLTypeName(),
+ rc.getName());
+ }
+
ColumnDefinitionNode column = new ColumnDefinitionNode();
column.init(rc.getName(), null, rc.getType(), null);
tableElementList.addTableElement(column);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?view=diff&rev=539164&r1=539163&r2=539164
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Thu May 17 16:19:02 2007
@@ -1650,6 +1650,13 @@
</msg>
<msg>
+ <name>42X71</name>
+ <text>Invalid data type '{0}' for column '{1}'.</text>
+ <arg>datatypeName</arg>
+ <arg>columnName</arg>
+ </msg>
+
+ <msg>
<name>42X72</name>
<text>No static field '{0}' was found belonging to class '{1}'. The field might exist, but it is not public and/or static, or the class does not exist or the class is not public. </text>
<arg>fieldName</arg>
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=539164&r1=539163&r2=539164
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Thu May 17 16:19:02 2007
@@ -845,6 +845,7 @@
String LANG_NO_FIELD_FOUND = "42X68";
String LANG_PRIMITIVE_REFERENCING_EXPRESSION = "42X69";
String LANG_TABLE_DEFINITION_R_C_L_MISMATCH = "42X70";
+ String LANG_INVALID_COLUMN_TYPE_CREATE_TABLE = "42X71";
String LANG_NO_STATIC_FIELD_FOUND = "42X72";
String LANG_AMBIGUOUS_METHOD_INVOCATION = "42X73";
String LANG_INVALID_CALL_STATEMENT = "42X74";
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java?view=diff&rev=539164&r1=539163&r2=539164
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java Thu May 17 16:19:02 2007
@@ -233,6 +233,24 @@
assertStatementError("0A000", stmt,
"create table t3 as select * from t1 with data");
}
+
+ /**
+ * Test error for creating table where the data type is invalid.
+ */
+ public void testInvalidDataType() throws Exception
+ {
+ // BOOLEAN
+ assertStatementError("42X71", stmt,
+ "create table t as select systemalias from sys.sysaliases with no data");
+
+ // USER (Java Object)
+ assertStatementError("42X71", stmt,
+ "create table t as select aliasinfo from sys.sysaliases with no data");
+
+ // DECIMAL(44,0)
+ assertStatementError("42X71", stmt,
+ "create table t(x) as values 12345678901234567890123456789012345678901234 with no data");
+ }
private void positiveTest(String sql, String [] columnNames,
String [] nullability, String [] types) throws Exception