db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject 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...
Date Thu, 17 May 2007 23:19:03 GMT
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



Mime
View raw message