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 rh...@apache.org on 2014/04/02 04:01:37 UTC
svn commit: r1583855 - in /db/derby/code/trunk/java:
engine/org/apache/derby/vti/VTITemplate.java
optional/org/apache/derby/optional/lucene/LuceneSupport.java
testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportPermsTest.java
Author: rhillegas
Date: Wed Apr 2 02:01:36 2014
New Revision: 1583855
URL: http://svn.apache.org/r1583855
Log:
DERBY-590: Add ability to create a Lucene index on an arbitrary view; commit derby-590-13-aa-indexViews.diff.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/vti/VTITemplate.java
db/derby/code/trunk/java/optional/org/apache/derby/optional/lucene/LuceneSupport.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportPermsTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/vti/VTITemplate.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/vti/VTITemplate.java?rev=1583855&r1=1583854&r2=1583855&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/vti/VTITemplate.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/vti/VTITemplate.java Wed Apr 2 02:01:36 2014
@@ -387,6 +387,7 @@ public abstract class VTITemplate impl
this.ordinalPosition = ordinalPosition;
}
+ /** Sort on ordinalPosition */
public int compareTo( ColumnDescriptor that ) { return this.ordinalPosition - that.ordinalPosition; }
public boolean equals( Object other )
{
@@ -395,6 +396,7 @@ public abstract class VTITemplate impl
else { return (compareTo( (ColumnDescriptor) other ) == 0); }
}
public int hashCode() { return columnName.hashCode(); }
+ public String toString() { return columnName; }
}
}
Modified: db/derby/code/trunk/java/optional/org/apache/derby/optional/lucene/LuceneSupport.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/optional/org/apache/derby/optional/lucene/LuceneSupport.java?rev=1583855&r1=1583854&r2=1583855&view=diff
==============================================================================
--- db/derby/code/trunk/java/optional/org/apache/derby/optional/lucene/LuceneSupport.java (original)
+++ db/derby/code/trunk/java/optional/org/apache/derby/optional/lucene/LuceneSupport.java Wed Apr 2 02:01:36 2014
@@ -170,8 +170,9 @@ public class LuceneSupport implements Op
createProcedure.append(" (schemaname varchar( 128 ),");
createProcedure.append("tablename varchar( 128 ),");
createProcedure.append("textcolumn varchar( 128 ),");
- createProcedure.append("analyzerMaker varchar( 32672 ))");
- createProcedure.append("parameter style java modifies sql data language java external name ");
+ createProcedure.append("analyzerMaker varchar( 32672 ),");
+ createProcedure.append("keyColumns varchar( 32672 )...)");
+ createProcedure.append("parameter style derby modifies sql data language java external name ");
createProcedure.append("'" + getClass().getName() + ".createIndex'");
executeDDL( conn, createProcedure.toString() );
@@ -359,13 +360,21 @@ public class LuceneSupport implements Op
* Create a Lucene index on the specified column.
*
* @param schema The schema of the column to index
- * @param table The table of the column to index
+ * @param table The table or view containing the indexable column
* @param textcol The column to create the Lucene index on
* @param analyzerMaker name of static method which instantiates an Analyzer. may be null.
+ * @param keyColumns names of key columns if we're indexing a column in a view
* @throws SQLException
* @throws IOException
*/
- public static void createIndex( String schema, String table, String textcol, String analyzerMaker )
+ public static void createIndex
+ (
+ String schema,
+ String table,
+ String textcol,
+ String analyzerMaker,
+ String... keyColumns
+ )
throws SQLException, IOException, PrivilegedActionException,
ClassNotFoundException, IllegalAccessException, InvocationTargetException, NoSuchMethodException
{
@@ -375,7 +384,7 @@ public class LuceneSupport implements Op
// First make sure that the text column exists and is a String type
vetTextColumn( dbmd, schema, table, textcol );
- createOrRecreateIndex( conn, schema, table, textcol, analyzerMaker, true );
+ createOrRecreateIndex( conn, schema, table, textcol, analyzerMaker, true, keyColumns );
}
/**
@@ -396,12 +405,24 @@ public class LuceneSupport implements Op
String table,
String textcol,
String analyzerMaker,
- boolean create
+ boolean create,
+ String... keyColumns
)
throws SQLException, IOException, PrivilegedActionException,
ClassNotFoundException, IllegalAccessException, InvocationTargetException, NoSuchMethodException
{
- VTITemplate.ColumnDescriptor[] primaryKeys = getPrimaryKeys( conn, schema, table );
+ VTITemplate.ColumnDescriptor[] primaryKeys = new VTITemplate.ColumnDescriptor[ 0 ];
+
+ // can't override keys when the index is updated
+ if ( !create )
+ { primaryKeys = getKeys( conn, schema, table, textcol ); }
+ // use the supplied keys if possible
+ else if ( (keyColumns != null) && (keyColumns.length > 0) )
+ { primaryKeys = getKeys( conn, schema, table, keyColumns ); }
+ else
+ { primaryKeys = getPrimaryKeys( conn, schema, table ); }
+
+ // can't create an index without specifying keys for joining it back to Derby data
if ( primaryKeys.length == 0 )
{
throw newSQLException( SQLState.LUCENE_NO_PRIMARY_KEY );
@@ -447,7 +468,7 @@ public class LuceneSupport implements Op
for ( VTITemplate.ColumnDescriptor keyDesc : primaryKeys )
{
- String keyName = keyDesc.columnName;
+ String keyName = derbyIdentifier( keyDesc.columnName );
if ( keyCount > 0 ) { query.append( ", " ); }
query.append( keyName );
@@ -1333,6 +1354,115 @@ public class LuceneSupport implements Op
return result;
}
+ /**
+ * Return the key columns for an existing LuceneQueryVTI table function.
+ */
+ private static VTITemplate.ColumnDescriptor[] getKeys
+ (
+ Connection conn,
+ String schema,
+ String table,
+ String textcol
+ )
+ throws SQLException
+ {
+ schema = derbyIdentifier( schema );
+ String functionName = makeUnqualifiedTableFunctionName( table, textcol );
+ ArrayList<VTITemplate.ColumnDescriptor> keyArray = new ArrayList<VTITemplate.ColumnDescriptor>();
+
+ ResultSet rs = conn.getMetaData().getFunctionColumns( null, schema, functionName, "%" );
+ try {
+ while ( rs.next() )
+ {
+ if ( rs.getInt( "COLUMN_TYPE" ) == DatabaseMetaData.functionColumnResult )
+ {
+ VTITemplate.ColumnDescriptor keyDescriptor = new VTITemplate.ColumnDescriptor
+ (
+ rs.getString( "COLUMN_NAME" ),
+ rs.getInt( "DATA_TYPE" ),
+ rs.getInt( "PRECISION" ),
+ rs.getInt( "SCALE" ),
+ rs.getString( "TYPE_NAME" ),
+ rs.getInt( "ORDINAL_POSITION" )
+ );
+ keyArray.add( keyDescriptor );
+ }
+ }
+ }
+ finally
+ {
+ rs.close();
+ }
+
+ VTITemplate.ColumnDescriptor[] temp = new VTITemplate.ColumnDescriptor[ keyArray.size() ];
+ keyArray.toArray( temp );
+ Arrays.sort( temp );
+
+ // remove the last two columns, which are not keys. they are the DOCUMENT_ID and RANK columns.
+ int count = temp.length - 2;
+ VTITemplate.ColumnDescriptor[] result = new VTITemplate.ColumnDescriptor[ count ];
+ for ( int i = 0; i < count; i++ ) { result[ i ] = temp[ i ]; }
+
+ return result;
+ }
+
+ /**
+ * Return column information for a proposed set of keys.
+ */
+ private static VTITemplate.ColumnDescriptor[] getKeys
+ (
+ Connection conn,
+ String schema,
+ String table,
+ String... keyColumns
+ )
+ throws SQLException
+ {
+ String qualifiedName = makeTableName( schema, table );
+ StringBuilder buffer = new StringBuilder();
+
+ buffer.append( "select " );
+ int counter = 0;
+ for ( String key : keyColumns )
+ {
+ if ( counter > 0 ) { buffer.append( ", " ); }
+ counter++;
+ buffer.append( derbyIdentifier( key ) );
+ }
+ buffer.append( "\nfrom " + qualifiedName );
+ buffer.append( "\nwhere 1=2" );
+
+ ArrayList<VTITemplate.ColumnDescriptor> keyArray = new ArrayList<VTITemplate.ColumnDescriptor>();
+
+ ResultSet rs = conn.prepareStatement( buffer.toString() ).executeQuery();
+ ResultSetMetaData rsmd = rs.getMetaData();
+ try {
+ for ( int keyPosition = 1; keyPosition <= rsmd.getColumnCount(); keyPosition++ )
+ {
+ VTITemplate.ColumnDescriptor keyDescriptor = new VTITemplate.ColumnDescriptor
+ (
+ rsmd.getColumnName( keyPosition ),
+ rsmd.getColumnType( keyPosition ),
+ rsmd.getPrecision( keyPosition ),
+ rsmd.getScale( keyPosition ),
+ rsmd.getColumnTypeName( keyPosition ),
+ keyPosition
+ );
+ keyArray.add( keyDescriptor );
+ }
+ }
+ finally
+ {
+ rs.close();
+ }
+
+ VTITemplate.ColumnDescriptor[] result = new VTITemplate.ColumnDescriptor[ keyArray.size() ];
+ keyArray.toArray( result );
+
+ return result;
+ }
+
+
/////////////////////////////////////////////////////////////////////
//
// FILE MANAGEMENT
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportPermsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportPermsTest.java?rev=1583855&r1=1583854&r2=1583855&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportPermsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportPermsTest.java Wed Apr 2 02:01:36 2014
@@ -91,6 +91,7 @@ public class LuceneSupportPermsTest exte
private static final String INDEX_POEMS = "call LuceneSupport.createIndex( 'ruth', 'poems', 'poemText', null )";
private static final String UPDATE_POEMS_INDEX = "call LuceneSupport.updateIndex( 'ruth', 'poems', 'poemText', null )";
private static final String DROP_POEMS_INDEX = "call LuceneSupport.dropIndex( 'ruth', 'poems', 'poemText' )";
+ private static final String DROP_PRIMARY_KEY = "alter table poems drop constraint poemsKey";
private static final long MILLIS_IN_HOUR = 1000L * 60L * 60L;
private static final long MILLIS_IN_DAY = MILLIS_IN_HOUR * 24L;
@@ -227,9 +228,14 @@ public class LuceneSupportPermsTest exte
// ruth can update the index
goodStatement( ruthConnection, UPDATE_POEMS_INDEX );
- // dropping the key prevents you from re-indexing
- goodStatement( ruthConnection, "alter table poems drop constraint poemsKey" );
- expectExecutionError( ruthConnection, NO_PRIMARY_KEY, UPDATE_POEMS_INDEX );
+ // dropping the key does NOT prevent you from re-indexing
+ goodStatement( ruthConnection, DROP_PRIMARY_KEY );
+ goodStatement( ruthConnection, UPDATE_POEMS_INDEX );
+
+ // but dropping a key column DOES prevent you from re-indexing and from selecting
+ goodStatement( ruthConnection, "alter table poems drop column versionStamp" );
+ expectExecutionError( ruthConnection, COLUMN_OUT_OF_SCOPE, UPDATE_POEMS_INDEX );
+ expectExecutionError( ruthConnection, COLUMN_OUT_OF_SCOPE, viewPoemsIndex );
// ruth can drop the index
goodStatement( ruthConnection, DROP_POEMS_INDEX );
@@ -244,6 +250,12 @@ public class LuceneSupportPermsTest exte
// redundant drop fails, however
expectExecutionError( ruthConnection, NONEXISTENT_OBJECT, DROP_POEMS_INDEX );
+ // verify that a primary key is necessary in order to index a table
+ dropSchema( ruthConnection );
+ createSchema( ruthConnection, Types.INTEGER );
+ goodStatement( ruthConnection, DROP_PRIMARY_KEY );
+ expectExecutionError( ruthConnection, NO_PRIMARY_KEY, INDEX_POEMS );
+
// ruth cannot unload the tool
expectExecutionError( ruthConnection, LACK_EXECUTE_PRIV, UNLOAD_TOOL );
@@ -578,6 +590,171 @@ public class LuceneSupportPermsTest exte
dropSchema( ruthConnection );
}
+ /**
+ * <p>
+ * Test that you can index views and index tables with alternative column lists.
+ * </p>
+ */
+ public void test_007_indexViews()
+ throws Exception
+ {
+ Connection dboConnection = openUserConnection( TEST_DBO );
+ Connection ruthConnection = openUserConnection( RUTH );
+
+ createSchema( ruthConnection, Types.INTEGER );
+ createPoemView( ruthConnection );
+ goodStatement( dboConnection, LOAD_TOOL );
+ goodStatement( ruthConnection, INDEX_POEMS );
+
+ // must supply some key columns if you're going to index a view
+ expectExecutionError
+ (
+ ruthConnection,
+ NO_PRIMARY_KEY,
+ "call LuceneSupport.createIndex( 'ruth', 'poemView', 'poemText', null )"
+ );
+
+ // now index the view
+ goodStatement
+ (
+ ruthConnection,
+ "call LuceneSupport.createIndex( 'ruth', 'poemView', 'poemText', null, 'poemID', 'versionStamp' )"
+ );
+
+ // can't create a second index by the same name
+ expectExecutionError
+ (
+ ruthConnection,
+ FUNCTION_EXISTS,
+ "call LuceneSupport.createIndex( 'ruth', 'poemView', 'poemText', null, 'poemID' )"
+ );
+
+ // vet index contents
+ String selectFromViewIndex =
+ "select p.originalAuthor, i.rank\n" +
+ "from ruth.poems p, table ( ruth.poemView__poemText( 'star', 0 ) ) i\n" +
+ "where p.poemID = i.poemID and p.versionStamp = i.versionStamp\n" +
+ "order by i.rank desc\n";
+ assertResults
+ (
+ ruthConnection,
+ selectFromViewIndex,
+ new String[][]
+ {
+ { "Walt Whitman", "0.26756266" },
+ { "Lord Byron", "0.22933942" },
+ { "John Milton", "0.22933942" },
+ },
+ false
+ );
+
+ // vet index list
+ String selectIndexes =
+ "select schemaName, tableName, columnName, analyzerMaker\n" +
+ "from table( LuceneSupport.listIndexes() ) l\n" +
+ "order by schemaName, tableName, columnName\n";
+ assertResults
+ (
+ ruthConnection,
+ selectIndexes,
+ new String[][]
+ {
+ {
+ "RUTH", "POEMS", "POEMTEXT",
+ "org.apache.derby.optional.api.LuceneUtils.defaultAnalyzer",
+ },
+ {
+ "RUTH", "POEMVIEW", "POEMTEXT",
+ "org.apache.derby.optional.api.LuceneUtils.defaultAnalyzer",
+ },
+ },
+ false
+ );
+
+ // update the view index, changing its analyzer
+ goodStatement
+ (
+ ruthConnection,
+ "call LuceneSupport.updateIndex( 'ruth', 'poemView', 'poemText', 'org.apache.derby.optional.api.LuceneUtils.standardAnalyzer' )"
+ );
+ assertResults
+ (
+ ruthConnection,
+ selectFromViewIndex,
+ new String[][]
+ {
+ { "Walt Whitman", "0.3304931" },
+ { "John Milton", "0.2832798" },
+ },
+ false
+ );
+ assertResults
+ (
+ ruthConnection,
+ selectIndexes,
+ new String[][]
+ {
+ {
+ "RUTH", "POEMS", "POEMTEXT",
+ "org.apache.derby.optional.api.LuceneUtils.defaultAnalyzer",
+ },
+ {
+ "RUTH", "POEMVIEW", "POEMTEXT",
+ "org.apache.derby.optional.api.LuceneUtils.standardAnalyzer",
+ },
+ },
+ false
+ );
+
+ // drop the index on the view
+ goodStatement
+ (
+ ruthConnection,
+ "call LuceneSupport.dropIndex( 'ruth', 'poemView', 'poemText' )"
+ );
+ assertResults
+ (
+ ruthConnection,
+ selectIndexes,
+ new String[][]
+ {
+ {
+ "RUTH", "POEMS", "POEMTEXT",
+ "org.apache.derby.optional.api.LuceneUtils.defaultAnalyzer",
+ },
+ },
+ false
+ );
+
+ // now drop the index on the table and create one with just one key column
+ goodStatement( ruthConnection, DROP_POEMS_INDEX );
+ goodStatement
+ (
+ ruthConnection,
+ "call LuceneSupport.createIndex( 'ruth', 'poems', 'poemText', null, 'poemID' )"
+ );
+ assertResults
+ (
+ ruthConnection,
+ "select *\n" +
+ "from table ( ruth.poems__poemText( 'star', 0 ) ) i\n" +
+ "order by i.rank desc\n",
+ new String[][]
+ {
+ { "5", "4", "0.26756266" },
+ { "4", "3", "0.22933942" },
+ { "3", "2", "0.22933942" },
+ },
+ false
+ );
+
+ goodStatement( ruthConnection, DROP_POEMS_INDEX );
+ goodStatement( dboConnection, UNLOAD_TOOL );
+ goodStatement( ruthConnection, "drop view poemView" );
+ dropSchema( ruthConnection );
+ }
+
+
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS
@@ -649,6 +826,16 @@ public class LuceneSupportPermsTest exte
ps.close();
}
+ private void createPoemView( Connection conn )
+ throws Exception
+ {
+ goodStatement
+ (
+ conn,
+ "create view poemView as select poemID, versionStamp, poemText from poems"
+ );
+ }
+
private void createLocaleFunction( Connection conn )
throws Exception
{