db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject 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
Date Wed, 02 Apr 2014 02:01:37 GMT
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
     {



Mime
View raw message