/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.LuceneSupportPermsTest

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to you 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.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.security.AccessController;
import java.security.PrivilegedActionException;
import java.security.PrivilegedExceptionAction;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import java.util.Locale;
import junit.framework.Test;
import org.apache.derby.iapi.sql.conn.ConnectionUtil;
import org.apache.derby.optional.api.LuceneIndexDescriptor;
import org.apache.derby.optional.api.LuceneUtils;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.LocaleTestSetup;
import org.apache.derbyTesting.junit.SecurityManagerSetup;
import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.lucene.analysis.Analyzer;
import org.apache.lucene.queryparser.classic.MultiFieldQueryParser;
import org.apache.lucene.queryparser.classic.ParseException;
import org.apache.lucene.queryparser.classic.QueryParser;
import org.apache.lucene.search.Query;
import org.apache.lucene.util.Version;

/**
 * <p>
 * Test permissions on objects created by the optional Lucene support tool.
 * </p>
 */
public class LuceneSupportPermsTest extends GeneratedColumnsHelper
{
    ///////////////////////////////////////////////////////////////////////////////////
    //
    // CONSTANTS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    private static  final   String      LUCENE_VERSION_PROPERTY = "derby.tests.lucene.version";
    private static  String              LUCENE_VERSION = "LUCENE_47";

    private static  final   String      DB_NAME = "lucenesupportpermsdb";

    private static  final   String      TEST_DBO = "TEST_DBO";
    private static  final   String      RUTH = "RUTH";
    private static  final   String      ALICE = "ALICE";
    private static  final   String      FRANK = "FRANK";
    private static  final   String[]    LEGAL_USERS = { TEST_DBO, ALICE, RUTH, FRANK  };

	private static  final   String      AUTH_NO_ACCESS_NOT_OWNER    = "42507";
	private static  final   String      DBO_ONLY                                = "4251D";
    private static  final   String      FUNCTION_EXISTS                 = "X0Y68";

    private static  final   String      NOT_INDEXABLE                   = "42XBA";
    private static  final   String      NO_PRIMARY_KEY              = "42XBB";
    // LUCENE_UNSUPPORTED_TYPE = "42XBC": only raised if key type is unsupported. but all indexable types are supported.
    // LUCENE_INVALID_CHARACTER = "42XBD" is tested by LuceneSupportTest.
    private static  final   String      NONEXISTENT_INDEX           = "42XBE";
	private static  final   String      NO_DDL_PRIV                    = "42XBF";
	private static  final   String      DOUBLE_LOAD_ILLEGAL         = "42XBG";
	private static  final   String      DOUBLE_UNLOAD_ILLEGAL       = "42XBH";
	private static  final   String      BAD_DIRECTORY                      = "42XBI";
	private static  final   String      BAD_COLUMN_NAME                 = "42XBJ";
    private static  final   String      NONEXISTENT_TABLE_FUNCTION  ="42ZB4";
    private static  final   String      INCOMPATIBLE_ENCRYPTION = "42XBL";
    private static  final   String      ILLEGAL_NULL_ARG = "42XBM";
    private static  final   String      BAD_FIELD_NAME = "42XBN";
    private static  final   String      DUPLICATE_FIELD_NAME = "42XBO";
    private static  final   String      NULL_PRIMITIVE_ARG = "39004";

    private static  final   String      POLICY_FILE = "org/apache/derbyTesting/functionTests/tests/lang/luceneSupport.policy";

    private static  final   String      LOAD_TOOL = "call syscs_util.syscs_register_tool( 'luceneSupport', true )";
    private static  final   String      UNLOAD_TOOL = "call syscs_util.syscs_register_tool( 'luceneSupport', false )";
    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;

    private static  final   String      LANGUAGE = "en";
    private static  final   String      COUNTRY = "US";

    private static  final   String      DEFAULT_INDEX_DESCRIPTOR = "org.apache.derby.optional.api.LuceneUtils.defaultIndexDescriptor";
    private static  final   String      CONSTANT_QUERY_PARSER = "org.apache.derbyTesting.functionTests.tests.lang.LuceneSupportPermsTest.constantStringIndexDescriptor";
    
    ///////////////////////////////////////////////////////////////////////////////////
    //
    // STATE
    //
    ///////////////////////////////////////////////////////////////////////////////////

    static  String[]    _fieldNames;

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // CONSTRUCTOR
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /**
     * Create a new instance.
     */

    public LuceneSupportPermsTest(String name)
    {
        super(name);
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // JUnit BEHAVIOR
    //
    ///////////////////////////////////////////////////////////////////////////////////


    /**
     * Construct top level suite in this JUnit test
     */
    public static Test suite()
    {
        String      luceneVersion = getSystemProperty( LUCENE_VERSION_PROPERTY );
        if ( luceneVersion != null ) { LUCENE_VERSION = luceneVersion; }
        
        BaseTestSuite suite = (BaseTestSuite)TestConfiguration.embeddedSuite(
            LuceneSupportPermsTest.class);

        Test        secureTest = new SecurityManagerSetup( suite, POLICY_FILE );
        Test        authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication
            ( secureTest, LEGAL_USERS, "LuceneSupportPermissions" );
        Test        authorizedTest = TestConfiguration.sqlAuthorizationDecoratorSingleUse( authenticatedTest, DB_NAME, true );
        Test        localizedTest = new LocaleTestSetup( authorizedTest, new Locale( LANGUAGE, COUNTRY ) );

        return localizedTest;
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // TESTS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /**
     * <p>
     * Test baseline permissions where no grants are made.
     * </p>
     */
    public  void    test_001_basicNoGrant()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );
        Connection  aliceConnection = openUserConnection( ALICE );

        createSchema( ruthConnection, Types.INTEGER );

        // ruth does not have permission to load the tool
        expectExecutionError( ruthConnection, LACK_EXECUTE_PRIV, LOAD_TOOL );

        // but the dbo has permission
        goodStatement( dboConnection, LOAD_TOOL );

        // can't update a non-existent index
        expectExecutionError
            ( ruthConnection, NONEXISTENT_INDEX, "call LuceneSupport.updateIndex( 'ruth', 'poems', 'poemText', null )" );

        // alice does not have permission to index a table owned by ruth
        expectExecutionError( aliceConnection, LACK_COLUMN_PRIV, INDEX_POEMS );

        // but ruth can
        goodStatement( ruthConnection, INDEX_POEMS );

        // redundant index creation fails
        expectExecutionError( ruthConnection, FUNCTION_EXISTS, INDEX_POEMS );

        // can't update a non-existent index
        expectExecutionError( ruthConnection, NONEXISTENT_INDEX, "call LuceneSupport.updateIndex( 'ruth', 'poems', 'foo', null )" );
        expectExecutionError
            ( ruthConnection, NONEXISTENT_INDEX, "call LuceneSupport.updateIndex( 'ruth', 'poems', 'originalAuthor', null )" );

        // alice can't view an index created by ruth
        String  viewPoemsIndex = "select * from table ( ruth.poems__poemText( 'star', 1000, null ) ) luceneResults order by poemID";
        expectExecutionError( aliceConnection, LACK_EXECUTE_PRIV, viewPoemsIndex );

        // but ruth can
        assertResults
            (
             ruthConnection,
             viewPoemsIndex,
             new String[][]
             {
                 { "3", "3", "2", "0.22933942" },
                 { "4", "4", "3", "0.22933942" },
                 { "5", "5", "4", "0.26756266" },
             },
             false
             );

        // alice can list indexes even on tables owned by ruth
        String  listIndexes = "select schemaName, tableName, columnName from table ( LuceneSupport.listIndexes() ) listindexes";
        assertResults
            (
             aliceConnection,
             listIndexes,
             new String[][]
             {
                 { "RUTH", "POEMS", "POEMTEXT" },
             },
             false
             );

        // alice cannot update an index owned by ruth
        expectExecutionError( aliceConnection, NO_DDL_PRIV, UPDATE_POEMS_INDEX );
        
        // alice cannot drop an index owned by ruth
        expectExecutionError( aliceConnection, AUTH_NO_ACCESS_NOT_OWNER, DROP_POEMS_INDEX );

        // ruth can update the index
        goodStatement( ruthConnection, 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 );
        assertResults
            (
             ruthConnection,
             listIndexes,
             new String[][] {},
             false
             );

        // 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 );

        // but the dbo can
        goodStatement( dboConnection, UNLOAD_TOOL );

        dropSchema( ruthConnection );
    }

    /**
     * <p>
     * Test that a user can grant access to her indexes.
     * </p>
     */
    public  void    test_002_userGrant()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );
        Connection  aliceConnection = openUserConnection( ALICE );

        createSchema( ruthConnection, Types.INTEGER );

        // load the Lucene plugin
        goodStatement( dboConnection, LOAD_TOOL );

        // ruth indexes her table and grants alice privilege to run the index reading function
        goodStatement( ruthConnection, INDEX_POEMS );

        Permission[]    permissions = new Permission[]
        {
            new Permission( "execute on function poems__poemText", NO_GENERIC_PERMISSION ),
            new Permission( "select ( poemID ) on poems", NO_SELECT_OR_UPDATE_PERMISSION ),
            new Permission( "select ( versionStamp ) on poems", NO_SELECT_OR_UPDATE_PERMISSION ),
            new Permission( "select ( poemText ) on poems", NO_SELECT_OR_UPDATE_PERMISSION ),
        };
        for ( Permission permission : permissions )
        {
            grantPermission( ruthConnection, permission.text, ALICE );
        }

        // but alice still needs select privilege on the base table columns
        String  viewPoemsIndex = "select * from table ( ruth.poems__poemText( 'star', 1000, null ) ) luceneResults order by poemid";
        String[][]  viewPoemsIndexResults = new String[][]
            {
                { "3", "3", "2", "0.22933942" },
                { "4", "4", "3", "0.22933942" },
                { "5", "5", "4", "0.26756266" },
            };

        // now alice can view the index
        assertResults( aliceConnection, viewPoemsIndex, viewPoemsIndexResults, false );

        // now revoke each permission and verify that it is needed
        for ( Permission permission : permissions )
        {
            vetPermission_002( permission, ruthConnection, aliceConnection, viewPoemsIndex, viewPoemsIndexResults );
        }

        // but alice still can't drop an index owned by ruth
        expectExecutionError( aliceConnection, AUTH_NO_ACCESS_NOT_OWNER, DROP_POEMS_INDEX );

        // unload the plugin
        goodStatement( dboConnection, UNLOAD_TOOL );

        dropSchema( ruthConnection );
    }
    private void    vetPermission_002
        (
         Permission permission,
         Connection ruthConnection,
         Connection aliceConnection,
         String statement,
         String[][] expectedResults
         )
        throws Exception
    {
        revokePermission( ruthConnection, permission.text, ALICE );
        expectExecutionError( aliceConnection, permission.sqlStateWhenMissing, statement );
        grantPermission( ruthConnection, permission.text, ALICE );
        assertResults( aliceConnection, statement, expectedResults, false );
    }
    private void    grantPermission( Connection conn, String permission, String grantee )
        throws Exception
    {
        String  command = "grant " + permission + " to " + grantee;

        goodStatement( conn, command );
    }
    private void    revokePermission( Connection conn, String permission, String grantee )
        throws Exception
    {
        String  command = "revoke " + permission + " from " + grantee;
        if ( permission.startsWith( "execute" ) || permission.startsWith( "usage" ) )   { command += " restrict"; }

        goodStatement( conn, command );
    }

    /**
     * <p>
     * Test that only the DBO can (un)load the tool and the tool
     * can't be (un)loaded twice.
     * </p>
     */
    public  void    test_003_loading()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        createSchema( ruthConnection, Types.INTEGER );

        goodStatement( dboConnection, "grant execute on procedure syscs_util.syscs_register_tool to public" );

        // only the DBO can load the tool
        expectExecutionError( ruthConnection, DBO_ONLY, LOAD_TOOL );

        goodStatement( dboConnection, LOAD_TOOL );
        expectExecutionError( dboConnection, DOUBLE_LOAD_ILLEGAL, LOAD_TOOL );

        // cannot index non-existent table or column
        expectExecutionError( ruthConnection, NOT_INDEXABLE, "call LuceneSupport.createIndex( 'ruth', 'foo', 'poemText', null )" );
        expectExecutionError( ruthConnection, NOT_INDEXABLE, "call LuceneSupport.createIndex( 'ruth', 'poems', 'fooText', null )" );
        expectExecutionError( ruthConnection, NOT_INDEXABLE, "call LuceneSupport.createIndex( 'ruth', 'poems', 'versionStamp', null )" );

        // cannot drop non-existent index
        expectExecutionError( ruthConnection, NONEXISTENT_OBJECT, "call LuceneSupport.dropIndex( 'ruth', 'foo', 'poemText' )" );
        expectExecutionError( ruthConnection, NONEXISTENT_OBJECT, "call LuceneSupport.dropIndex( 'ruth', 'poems', 'versionStamp' )" );
        
        // only the DBO can unload the tool
        expectExecutionError( ruthConnection, DBO_ONLY, UNLOAD_TOOL );

        goodStatement( dboConnection, "revoke execute on procedure syscs_util.syscs_register_tool from public restrict" );

        goodStatement( dboConnection, UNLOAD_TOOL );
        expectExecutionError( dboConnection, DOUBLE_UNLOAD_ILLEGAL, UNLOAD_TOOL );

        // try loading and unloading again for good measure
        goodStatement( dboConnection, LOAD_TOOL );
        goodStatement( dboConnection, UNLOAD_TOOL );
        dropSchema( ruthConnection );
    }
    
    /**
     * <p>
     * Test all datatypes as key types.
     * </p>
     */
    public  void    test_004_datatypes()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        goodStatement( dboConnection, LOAD_TOOL );

        vet_004( ruthConnection, Types.BIGINT );
        vet_004( ruthConnection, Types.BOOLEAN );
        vet_004( ruthConnection, Types.CHAR );
        vet_004( ruthConnection, Types.BINARY );
        vet_004( ruthConnection, Types.DATE );
        vet_004( ruthConnection, Types.DECIMAL );
        vet_004( ruthConnection, Types.DOUBLE );
        vet_004( ruthConnection, Types.FLOAT );
        vet_004( ruthConnection, Types.INTEGER );
        vet_004( ruthConnection, Types.NUMERIC );
        vet_004( ruthConnection, Types.REAL );
        vet_004( ruthConnection, Types.SMALLINT );
        vet_004( ruthConnection, Types.TIME );
        vet_004( ruthConnection, Types.TIMESTAMP );
        vet_004( ruthConnection, Types.VARCHAR );
        vet_004( ruthConnection, Types.VARBINARY );

        goodStatement( dboConnection, UNLOAD_TOOL );
    }
    private void    vet_004( Connection ruthConnection, int jdbcType )
        throws Exception
    {
        createSchema( ruthConnection, jdbcType );
        goodStatement( ruthConnection, INDEX_POEMS );

        // make sure that we can de-serialize the key
        assertResults
            (
             ruthConnection,
             "select p.originalAuthor, i.score\n" +
             "from ruth.poems p, table ( ruth.poems__poemText( 'star', 1000, null ) ) i\n" +
             "where p.poemID = i.poemID and p.versionStamp = i.versionStamp\n" +
             "order by i.score desc\n",
             new String[][]
             {
                 { "Walt Whitman", "0.26756266" },
                 { "Lord Byron", "0.22933942" },
                 { "John Milton", "0.22933942" },
             },
             false
             );

        goodStatement( ruthConnection, DROP_POEMS_INDEX );
        dropSchema( ruthConnection );
    }

    /**
     * <p>
     * Test error messages when a lucene directory has been deleted.
     * </p>
     */
    public  void    test_005_deleteDirectory()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        createSchema( ruthConnection, Types.INTEGER );
        goodStatement( dboConnection, LOAD_TOOL );
        goodStatement( ruthConnection, INDEX_POEMS );

        TestConfiguration   config = getTestConfiguration();

        String  dbName = config.getDefaultDatabaseName();
        String  physicalDBName = config.getPhysicalDatabaseName( dbName );
        String  dbPath = config.getDatabasePath( physicalDBName );
        File    dbDirectory = new File( dbPath );
        File    luceneDirectory = new File( dbDirectory, "LUCENE" );
        File    ruthDirectory = new File( luceneDirectory, "RUTH" );
        File    poemsDirectory = new File( ruthDirectory, "POEMS" );
        File    poemTextIndexDirectory = new File( poemsDirectory, "POEMTEXT" );

        assertTrue( deleteFile( poemTextIndexDirectory ) );

        // but that doesn't stop you from deleting the index
        goodStatement( ruthConnection, DROP_POEMS_INDEX );
        expectCompilationError
            (
             ruthConnection, NONEXISTENT_TABLE_FUNCTION,
             "select * from table( ruth.textTable__textCol( 'one two three four five six seven eight nine ten', 100, null ) ) t"
             );

        goodStatement( dboConnection, UNLOAD_TOOL );
        dropSchema( ruthConnection );
    }
    
    /**
     * <p>
     * Test that you can change the Analyzer.
     * </p>
     */
    public  void    test_006_changeAnalyzer()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        createSchema( ruthConnection, Types.INTEGER );
        goodStatement( dboConnection, LOAD_TOOL );
        goodStatement( ruthConnection, INDEX_POEMS );

        // verify that we are the correct locale
        assertResults
            (
             ruthConnection,
             "values ( substr( getDatabaseLocale(), 1, 2 ) )",
             new String[][]
             {
                 { LANGUAGE },
             },
             false
             );
        

        String  query =
            "select p.originalAuthor, i.score\n" +
            "from ruth.poems p, table ( ruth.poems__poemText( 'star', 1000, null ) ) i\n" +
            "where p.poemID = i.poemID and p.versionStamp = i.versionStamp\n" +
            "order by i.score desc\n";

        assertResults
            (
             ruthConnection,
             query,
             new String[][]
             {
                 { "Walt Whitman", "0.26756266" },
                 { "Lord Byron", "0.22933942" },
                 { "John Milton", "0.22933942" },
             },
             false
             );

        // now switch the Analyzer and re-run the query
        goodStatement
            ( ruthConnection,
              "call LuceneSupport.updateIndex( 'ruth', 'poems', 'poemText', '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );

        assertResults
            (
             ruthConnection,
             query,
             new String[][]
             {
                 { "Walt Whitman", "0.3304931" },
                 { "John Milton", "0.2832798" },
             },
             false
             );

        //
        // Add another index and inspect the values of listIndexes()
        //
        goodStatement( ruthConnection, "call LuceneSupport.createIndex( 'ruth', 'poems', 'originalAuthor', null )" );
        assertResults
            (
             ruthConnection,
             "select schemaName, tableName, columnName, luceneVersion, analyzer, indexDescriptorMaker\n" +
             "from table( LuceneSupport.listIndexes() ) l\n" +
             "order by schemaName, tableName, columnName\n",
             new String[][]
             {
                 {
                     "RUTH", "POEMS", "ORIGINALAUTHOR", LUCENE_VERSION,
                     "org.apache.lucene.analysis.en.EnglishAnalyzer",
                     DEFAULT_INDEX_DESCRIPTOR
                 },
                 {
                     "RUTH", "POEMS", "POEMTEXT", LUCENE_VERSION,
                     "org.apache.lucene.analysis.standard.StandardAnalyzer",
                     LuceneCoarseAuthorizationTest.STANDARD_ANALYZER
                 },
             },
             false
             );

        goodStatement( ruthConnection, DROP_POEMS_INDEX );
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'poems', 'originalAuthor' )" );
        goodStatement( dboConnection, UNLOAD_TOOL );
        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.score\n" +
            "from ruth.poems p, table ( ruth.poemView__poemText( 'star', 1000, null ) ) i\n" +
            "where p.poemID = i.poemID and p.versionStamp = i.versionStamp\n" +
            "order by i.score 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, indexDescriptorMaker\n" +
            "from table( LuceneSupport.listIndexes() ) l\n" +
            "order by schemaName, tableName, columnName\n";
        assertResults
            (
             ruthConnection,
             selectIndexes,
             new String[][]
             {
                 {
                     "RUTH", "POEMS", "POEMTEXT",
                     DEFAULT_INDEX_DESCRIPTOR
                 },
                 {
                     "RUTH", "POEMVIEW", "POEMTEXT",
                     DEFAULT_INDEX_DESCRIPTOR
                 },
             },
             false
             );

        // update the view index, changing its analyzer
        goodStatement
            (
             ruthConnection,
             "call LuceneSupport.updateIndex( 'ruth', 'poemView', 'poemText', '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )"
             );
        assertResults
            (
             ruthConnection,
             selectFromViewIndex,
             new String[][]
             {
                 { "Walt Whitman", "0.3304931" },
                 { "John Milton", "0.2832798" },
             },
             false
             );
        assertResults
            (
             ruthConnection,
             selectIndexes,
             new String[][]
             {
                 {
                     "RUTH", "POEMS", "POEMTEXT",
                     DEFAULT_INDEX_DESCRIPTOR
                 },
                 {
                     "RUTH", "POEMVIEW", "POEMTEXT",
                     LuceneCoarseAuthorizationTest.STANDARD_ANALYZER
                 },
             },
             false
             );

        // drop the index on the view
        goodStatement
            (
             ruthConnection,
             "call LuceneSupport.dropIndex( 'ruth', 'poemView', 'poemText' )"
             );
        assertResults
            (
             ruthConnection,
             selectIndexes,
             new String[][]
             {
                 {
                     "RUTH", "POEMS", "POEMTEXT",
                     DEFAULT_INDEX_DESCRIPTOR
                 },
             },
             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', 1000, null ) ) i\n" +
             "order by i.score 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 );
    }

    /**
     * <p>
     * Test that you can't create an index involving a column with the same name
     * as one of the system-supplied column names (documentID and score).
     * </p>
     */
    public  void    test_008_columnNames()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        goodStatement( dboConnection, LOAD_TOOL );

        goodStatement( ruthConnection, "create table badTable1( keyCol int primary key, score clob )" );
        goodStatement( ruthConnection, "create table badTable2( keyCol int primary key, documentID clob )" );
        goodStatement( ruthConnection, "create table badTable3( score int primary key, textCol clob )" );
        goodStatement( ruthConnection, "create table badTable4( documentID int primary key, textCol clob )" );

        expectExecutionError
            (
             ruthConnection, BAD_COLUMN_NAME,
             "call LuceneSupport.createIndex( 'ruth', 'badTable1', 'score', null )"
             );
        expectExecutionError
            (
             ruthConnection, BAD_COLUMN_NAME,
             "call LuceneSupport.createIndex( 'ruth', 'badTable2', 'documentID', null )"
             );
        expectExecutionError
            (
             ruthConnection, BAD_COLUMN_NAME,
             "call LuceneSupport.createIndex( 'ruth', 'badTable3', 'textCol', null )"
             );
        expectExecutionError
            (
             ruthConnection, BAD_COLUMN_NAME,
             "call LuceneSupport.createIndex( 'ruth', 'badTable4', 'textCol', null )"
             );

        goodStatement( dboConnection, UNLOAD_TOOL );
        goodStatement( ruthConnection, "drop table badTable1" );
        goodStatement( ruthConnection, "drop table badTable2" );
        goodStatement( ruthConnection, "drop table badTable3" );
        goodStatement( ruthConnection, "drop table badTable4" );
    }
    
   /**
     * <p>
     * Test changes to the arguments to the searching table function.
     * </p>
     */
    public  void    test_009_searchArgs()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        loadTestTable( ruthConnection );
        
        goodStatement( dboConnection, LOAD_TOOL );
        goodStatement( ruthConnection, "call LuceneSupport.createIndex( 'ruth', 'textTable', 'textCol', null )" );

        // get all the matches
        assertResults
            (
             ruthConnection,
             "select * from table( ruth.textTable__textCol( 'one two three four five six seven eight nine ten', 100, null ) ) t",
             new String[][]
             {
                 { "10", "9", "2.2791052" },
                 { "9", "8", "1.6305782" },
                 { "8", "7", "1.1616905" },
                 { "7", "6", "0.97469425" },
                 { "6", "5", "0.6597747" },
                 { "5", "4", "0.49575216" },
                 { "4", "3", "0.33803377" },
                 { "3", "2", "0.17799875" },
                 { "2", "1", "0.09289266" },
                 { "1", "0", "0.035006654" },
             },
             false
             );
        
        // get an initial 3-row window of the top results
        assertResults
            (
             ruthConnection,
             "select * from table( ruth.textTable__textCol( 'one two three four five six seven eight nine ten', 3, null ) ) t",
             new String[][]
             {
                 { "10", "9", "2.2791052" },
                 { "9", "8", "1.6305782" },
                 { "8", "7", "1.1616905" },
             },
             false
             );
        
        // get the next 4-row window of results
        assertResults
            (
             ruthConnection,
             "select * from table( ruth.textTable__textCol( 'one two three four five six seven eight nine ten', 4, 1.0 ) ) t",
             new String[][]
             {
                 { "7", "6", "0.97469425" },
                 { "6", "5", "0.6597747" },
                 { "5", "4", "0.49575216" },
                 { "4", "3", "0.33803377" },
             },
             false
             );

        // get the final window of results
        assertResults
            (
             ruthConnection,
             "select * from table( ruth.textTable__textCol( 'one two three four five six seven eight nine ten', 100, 0.2 ) ) t",
             new String[][]
             {
                 { "3", "2", "0.17799875" },
                 { "2", "1", "0.09289266" },
                 { "1", "0", "0.035006654" },
             },
             false
             );
        
        // try a different query parser
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'textTable', 'textCol' )" );
        goodStatement
            (
             ruthConnection,
             "call LuceneSupport.createIndex( 'ruth', 'textTable', 'textCol', '" + CONSTANT_QUERY_PARSER + "' )"
             );
        assertResults
            (
             ruthConnection,
             "select * from table( ruth.textTable__textCol( 'one two three four five six seven eight nine ten', 100, null ) ) t",
             new String[][]
             {
                 { "1", "0", "1.597837" },
                 { "2", "1", "0.9986481" },
                 { "3", "2", "0.7989185" },
                 { "4", "3", "0.7989185" },
                 { "5", "4", "0.69905365" },
                 { "6", "5", "0.59918886" },
                 { "7", "6", "0.59918886" },
                 { "8", "7", "0.49932405" },
                 { "9", "8", "0.49932405" },
                 { "10", "9", "0.49932405" },
             },
             false
             );
        
        goodStatement( dboConnection, UNLOAD_TOOL );
        unloadTestTable( ruthConnection );
    }
    static  void    loadTestTable( Connection conn ) throws Exception
    {
        conn.prepareStatement
            (
             "create table textTable( keyCol int primary key, textCol clob )"
             ).execute();
        conn.prepareStatement
            (
             "insert into textTable values\n" +
             "( 1, 'one' ),\n" +
             "( 2, 'one two' ),\n" +
             "( 3, 'one two three' ),\n" +
             "( 4, 'one two three four' ),\n" +
             "( 5, 'one two three four five' ),\n" +
             "( 6, 'one two three four five six' ),\n" +
             "( 7, 'one two three four five six seven' ),\n" +
             "( 8, 'one two three four five six seven eight' ),\n" +
             "( 9, 'one two three four five six seven eight nine' ),\n" +
             "( 10, 'one two three four five six seven eight nine ten' ),\n" +
             "( 101, 'bricks' ),\n" +
             "( 102, 'bricks and mortar' ),\n" +
             "( 103, 'bricks and mortar, tea' ),\n" +
             "( 104, 'bricks and mortar, tea, tears' ),\n" +
             "( 105, 'bricks and mortar, tea, tears, turtle' ),\n" +
             "( 106, 'bricks and mortar, tea, tears, turtle, soup' ),\n" +
             "( 107, 'bricks and mortar, tea, tears, turtle, soup, when in the course' ),\n" +
             "( 108, 'bricks and mortar, tea, tears, turtle, soup, when in the course of human events' ),\n" +
             "( 109, 'bricks and mortar, tea, tears, turtle, soup, when in the course of human events you want' ),\n" +
             "( 110, 'bricks and mortar, tea, tears, turtle, soup, when in the course of human events you want better cell coverage' )\n"
             ).execute();
    }
    static  void    unloadTestTable( Connection conn ) throws Exception
    {
        conn.prepareStatement
            (
             "drop table textTable"
             ).execute();
    }

   /**
     * <p>
     * Test that encryption and the Lucene plugin are incompatible.
     * </p>
     */
    public  void    test_010_encryption()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        String      password = getTestConfiguration().getPassword( TEST_DBO );
        String      encryptDatabaseURL = "jdbc:derby:" + DB_NAME + ";user=" + TEST_DBO + ";password=" + password +
                ";dataEncryption=true;bootPassword=tryToGuessThis";
        String      decryptDatabaseURL = "jdbc:derby:" + DB_NAME + ";user=" + TEST_DBO + ";password=" + password +
                ";decryptDatabase=true;bootPassword=tryToGuessThis";

        goodStatement( dboConnection, LOAD_TOOL );
        getTestConfiguration().shutdownDatabase();

        // verify that you can't encrypt the database now
        try {
            DriverManager.getConnection( encryptDatabaseURL );
            fail( "Should not have been able to get a connection!" );
        }
        catch (SQLException se)
        {
            assertSQLState( INCOMPATIBLE_ENCRYPTION, se );
        }

        // now unload the tool and encrypt the database
        dboConnection = openUserConnection( TEST_DBO );
        goodStatement( dboConnection, UNLOAD_TOOL );
        getTestConfiguration().shutdownDatabase();
        dboConnection = DriverManager.getConnection( encryptDatabaseURL );

        // now you can't load the plugin
        expectExecutionError( dboConnection, INCOMPATIBLE_ENCRYPTION, LOAD_TOOL );

        // turn off encryption
        getTestConfiguration().shutdownDatabase();
        dboConnection = DriverManager.getConnection( decryptDatabaseURL );
    }
    
   /**
     * <p>
     * Test restrictions on field names.
     * </p>
     */
    public  void    test_011_fieldNames()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        loadTestTable( ruthConnection );
        goodStatement( dboConnection, LOAD_TOOL );

        String  indexTestTable =
            "call LuceneSupport.createIndex\n" +
            "(\n" +
            "    'ruth', 'textTable', 'textCol',\n" +
            "    'org.apache.derbyTesting.functionTests.tests.lang.LuceneSupportPermsTest.makeFieldNameIndexDescriptor'\n" +
            ")\n";
        
        goodStatement
            (
             ruthConnection,
             "create procedure setFieldNames( fieldName varchar( 32672 )... )\n" +
             "language java parameter style derby no sql\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.LuceneSupportPermsTest.setFieldNames'\n"
             );

        // Field and key name conflict.
        goodStatement( ruthConnection, "call setFieldNames( 'KEYCOL' )" );
        expectExecutionError( ruthConnection, BAD_FIELD_NAME, indexTestTable );

        // Duplicate field names.
        goodStatement( ruthConnection, "call setFieldNames( 'FOO', 'FOO' )" );
        expectExecutionError( ruthConnection, DUPLICATE_FIELD_NAME, indexTestTable );

        // Null field name.
        goodStatement( ruthConnection, "call setFieldNames( 'FOO', null )" );
        expectExecutionError( ruthConnection, DUPLICATE_FIELD_NAME, indexTestTable );
        goodStatement( ruthConnection, "call setFieldNames( null, 'FOO' )" );
        expectExecutionError( ruthConnection, DUPLICATE_FIELD_NAME, indexTestTable );

        goodStatement( ruthConnection, "drop procedure setFieldNames" );
        goodStatement( dboConnection, UNLOAD_TOOL );
        unloadTestTable( ruthConnection );
    }

   /**
     * <p>
     * Test that nulls are allowed in keys. See DERBY-6602.
     * </p>
     */
    public  void    test_6602()
        throws Exception
    {
        CD[]    columnDescriptors = new CD[]
        {
            new CD( "bigintCol", "bigint" ),
            new CD( "blobCol", "blob" ),
            new CD( "booleanCol", "boolean" ),
            new CD( "charCol", "char( 10 )" ),
            new CD( "clobCol", "clob" ),
            new CD( "dataCol", "date" ),
            new CD( "decimalCol", "decimal" ),
            new CD( "doubleCol", "double" ),
            new CD( "floatCol", "float" ),
            new CD( "intCol", "int" ),
            new CD( "longvarcharCol", "long varchar" ),
            new CD( "longvarcharforbitdataCol", "long varchar for bit data" ),
            new CD( "numericCol", "numeric" ),
            new CD( "realCol", "real" ),
            new CD( "smallintCol", "smallint" ),
            new CD( "timeCol", "time" ),
            new CD( "varcharCol", "varchar( 10 )" ),
            new CD( "varcharforbitdataCol", "varchar( 10 ) for bit data" ),
        };

        StringBuilder   buffer = new StringBuilder();
        buffer.append( "create table t_6602\n(\ttextcol clob" );
        for ( CD cd : columnDescriptors )
        {
            buffer.append( ",\n\t" + cd.name + " " + cd.type );
        }
        buffer.append( "\n)" );
        
        Connection  dboConnection = openUserConnection( TEST_DBO );
        
        goodStatement( dboConnection, LOAD_TOOL );
        goodStatement( dboConnection, buffer.toString() );
        goodStatement( dboConnection, "insert into t_6602( textcol ) values ( 'abc'), ( 'def')" );

        for ( CD cd : columnDescriptors )
        {
            vet6602( dboConnection, cd );
        }

        goodStatement( dboConnection, "drop table t_6602" );
        goodStatement( dboConnection, UNLOAD_TOOL );
    }
    private void    vet6602( Connection conn, CD cd )   throws Exception
    {
        PreparedStatement   ps = chattyPrepare
            (
             conn,
             "call lucenesupport.createindex\n" +
             "( 'TEST_DBO', 't_6602', 'textcol', '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "', ? )"
             );
        ps.setString( 1, cd.name );
        ps.execute();

        assertResults
            (
             conn,
             "select * from table( t_6602__textcol( 'abc or def', 3, null ) ) tc order by documentid",
             new String[][]
             {
                 { null, "0", "0.35355338" },
                 { null, "1", "0.35355338" },
             },
             false
             );

        goodStatement( conn, "call lucenesupport.dropIndex( 'TEST_DBO', 't_6602', 'textcol' )" );
    }
    
   /**
     * <p>
     * Test that nulls are not allowed as the values of certain arguments. See DERBY-6596.
     * </p>
     */
    public  void    test_6596_null_args()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        
        goodStatement( dboConnection, LOAD_TOOL );
        goodStatement( dboConnection, "create table t_6596( x int primary key, c clob )" );
        goodStatement( dboConnection, "insert into t_6596 values ( 1, 'abc' ), ( 2, 'def' )" );

        // create index errors
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.createindex( null, 't_6596', 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.createindex( 'TEST_DBO', null, 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.createindex( 'TEST_DBO', 't_6596', null,  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.createindex( 'TEST_DBO', 't_6596', 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "', null )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.createindex( 'TEST_DBO', 't_6596', 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "', 'x', null )" );

        goodStatement( dboConnection, "call lucenesupport.createindex( 'TEST_DBO', 't_6596', 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        assertResults
            (
             dboConnection,
             "select * from table( t_6596__c( 'abc or def', 3, null ) ) tc order by documentid",
             new String[][]
             {
                 { "1", "0", "0.35355338" },
                 { "2", "1", "0.35355338" },
             },
             false
             );
        goodStatement( dboConnection, "call lucenesupport.updateindex( 'TEST_DBO', 't_6596', 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        
        // update index errors
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.updateindex( null, 't_6596', 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.updateindex( 'TEST_DBO', null, 'c',  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.updateindex( 'TEST_DBO', 't_6596', null,  '" + LuceneCoarseAuthorizationTest.STANDARD_ANALYZER + "' )" );

        // query errors
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "select * from table( t_6596__c( null, 3, null ) ) tc order by documentid" );
        expectExecutionError
            ( dboConnection, NULL_PRIMITIVE_ARG,
              "select * from table( t_6596__c( 'abc or def', null, null ) ) tc order by documentid" );

        // drop index errors
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.dropindex( null, 't_6596', 'c' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.dropindex( 'TEST_DBO', null, 'c' )" );
        expectExecutionError
            ( dboConnection, ILLEGAL_NULL_ARG,
              "call lucenesupport.dropindex( 'TEST_DBO', 't_6596', null )" );

        goodStatement( dboConnection, "call lucenesupport.dropindex( 'TEST_DBO', 't_6596', 'c' )" );
        goodStatement( dboConnection, "drop table t_6596" );
        goodStatement( dboConnection, UNLOAD_TOOL );
    }
    
    /**
     * <p>
     * Test identifier casing for keys and text columns. See DERBY-6730.
     * </p>
     */
    public  void    test_6730()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );

        createSchema( ruthConnection, Types.INTEGER );
        goodStatement
            (
             ruthConnection,
             "create view v_6730_1 ( poemID, poemText ) as select poemID, poemText from ruth.poems"
             );
        goodStatement
            (
             ruthConnection,
             "create view v_6730_2 ( poemID, \"poemText\" ) as select poemID, poemText from ruth.poems"
             );
        goodStatement
            (
             ruthConnection,
             "create view v_6730_3 ( \"poemID\", poemText ) as select poemID, poemText from ruth.poems"
             );
        goodStatement
            (
             ruthConnection,
             "create view v_6730_4 ( \"poemID\", \"poemText\" ) as select poemID, poemText from ruth.poems"
             );
        goodStatement
            (
             ruthConnection,
             "create view v_6730_5 ( poemID, \"c l o b\" ) as select poemID, poemText from ruth.poems"
             );
        goodStatement
            (
             ruthConnection,
             "create view v_6730_6 ( \"k e y\", poemText ) as select poemID, poemText from ruth.poems"
             );

        goodStatement( dboConnection, LOAD_TOOL );

        // Index the views
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.createIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_1',\n" +
             "  'poemText',\n" +
             "  null,\n" +
             "  'poemID'\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.createIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_2',\n" +
             "  '\"poemText\"',\n" +
             "  null,\n" +
             "  'poemID'\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.createIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_3',\n" +
             "  'poemText',\n" +
             "  null,\n" +
             "  '\"poemID\"'\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.createIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_4',\n" +
             "  '\"poemText\"',\n" +
             "  null,\n" +
             "  '\"poemID\"'\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.createIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_5',\n" +
             "  '\"c l o b\"',\n" +
             "  null,\n" +
             "  'poemID'\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.createIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_6',\n" +
             "  'poemText',\n" +
             "  null,\n" +
             "  '\"k e y\"'\n" +
             ")\n"
             );

        // Verify the expected casing of identifiers
        vet6730( ruthConnection );

        // Update the indexes and re-verify
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.updateIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_1',\n" +
             "  'poemText',\n" +
             "  null\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.updateIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_2',\n" +
             "  '\"poemText\"',\n" +
             "  null\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.updateIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_3',\n" +
             "  'poemText',\n" +
             "  null\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.updateIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_4',\n" +
             "  '\"poemText\"',\n" +
             "  null\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.updateIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_5',\n" +
             "  '\"c l o b\"',\n" +
             "  null\n" +
             ")\n"
             );
        goodStatement
            (
             ruthConnection,
             "call luceneSupport.updateIndex\n" +
             "(\n" +
             "  'ruth',\n" +
             "  'v_6730_6',\n" +
             "  'poemText',\n" +
             "  null\n" +
             ")\n"
             );
        vet6730( ruthConnection );

        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'v_6730_1', 'poemText' )" );
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'v_6730_2', '\"poemText\"' )" );
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'v_6730_3', 'poemText' )" );
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'v_6730_4', '\"poemText\"' )" );
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'v_6730_5', '\"c l o b\"' )" );
        goodStatement( ruthConnection, "call LuceneSupport.dropIndex( 'ruth', 'v_6730_6', 'poemText' )" );
        goodStatement( dboConnection, UNLOAD_TOOL );
        goodStatement( ruthConnection, "drop view v_6730_6" );
        goodStatement( ruthConnection, "drop view v_6730_5" );
        goodStatement( ruthConnection, "drop view v_6730_4" );
        goodStatement( ruthConnection, "drop view v_6730_3" );
        goodStatement( ruthConnection, "drop view v_6730_2" );
        goodStatement( ruthConnection, "drop view v_6730_1" );
        dropSchema( ruthConnection );
    }
    private void vet6730( Connection conn ) throws Exception
    {
        // Verify the expected casing of identifiers
        String[][]  expectedResults = new String[][]
        {
            { "5", "0.26756266" },
            { "4", "0.22933942" },
            { "3", "0.22933942" },
        };
        assertResults
            (
             conn,
             "select i.poemID, i.score\n" +
             "from table ( ruth.v_6730_1__poemText( 'star', 1000, null ) ) i\n" +
             "order by i.score desc\n",
             expectedResults,
             false
             );
        assertResults
            (
             conn,
             "select i.poemID, i.score\n" +
             "from table ( ruth.\"V_6730_2__poemText\"( 'star', 1000, null ) ) i\n" +
             "order by i.score desc\n",
             expectedResults,
             false
             );
        assertResults
            (
             conn,
             "select i.\"poemID\", i.score\n" +
             "from table ( ruth.v_6730_3__poemText( 'star', 1000, null ) ) i\n" +
             "order by i.score desc\n",
             expectedResults,
             false
             );
        assertResults
            (
             conn,
             "select i.\"poemID\", i.score\n" +
             "from table ( ruth.\"V_6730_4__poemText\"( 'star', 1000, null ) ) i\n" +
             "order by i.score desc\n",
             expectedResults,
             false
             );
        assertResults
            (
             conn,
             "select i.poemID, i.score\n" +
             "from table ( ruth.\"V_6730_5__c l o b\"( 'star', 1000, null ) ) i\n" +
             "order by i.score desc\n",
            expectedResults,
             false
             );
        assertResults
            (
             conn,
             "select i.\"k e y\", i.score\n" +
             "from table ( ruth.v_6730_6__poemText( 'star', 1000, null ) ) i\n" +
             "order by i.score desc\n",
             expectedResults,
             false
             );
        
        String[][]  expectedListResults = new String[][]
        {
            { "RUTH", "V_6730_1", "POEMTEXT", "org.apache.lucene.analysis.en.EnglishAnalyzer" },
            { "RUTH", "V_6730_2", "poemText", "org.apache.lucene.analysis.en.EnglishAnalyzer" },
            { "RUTH", "V_6730_3", "POEMTEXT", "org.apache.lucene.analysis.en.EnglishAnalyzer" },
            { "RUTH", "V_6730_4", "poemText", "org.apache.lucene.analysis.en.EnglishAnalyzer" },
            { "RUTH", "V_6730_5", "c l o b", "org.apache.lucene.analysis.en.EnglishAnalyzer" },
            { "RUTH", "V_6730_6", "POEMTEXT", "org.apache.lucene.analysis.en.EnglishAnalyzer" },
        };
        assertResults
            (
             conn,
             "select schemaName, tableName, columnName, analyzer\n" +
             "from table( lucenesupport.listIndexes() ) t\n" +
             "order by schemaName, tableName, columnName\n",
             expectedListResults,
             false
             );
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // MINIONS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    private void    createSchema( Connection ruthConnection, int jdbcType )  throws Exception
    {
        createPoemsTable( ruthConnection, jdbcType );
        createLocaleFunction( ruthConnection );
    }
    private void    createPoemsTable( Connection conn, int jdbcType )
        throws Exception
    {
        goodStatement
            (
             conn,
             "create table poems\n" +
             "(\n" +
             "    poemID " + getType( jdbcType ) + ",\n" +
             "    versionStamp int not null,\n" +
             "    originalAuthor       varchar( 50 ),\n" +
             "    lastEditor           varchar( 50 ),\n" +
             "    poemText            clob,\n" +
             "    constraint poemsKey primary key( poemID, versionStamp )\n" +
             ")\n"
             );

        PreparedStatement   ps = conn.prepareStatement( "insert into poems values ( ?, ?, ?, ?, ? )" );

        int     poemID = 1;
        int     versionStamp = 1;

        setNextPoemID( ps, jdbcType, poemID++ );
        ps.setInt( 2, versionStamp++ );
        ps.setString( 3, "Geoffrey Chaucer" );
        ps.setString( 4, "Geoffrey Chaucer" );
        ps.setString( 5, "Whan that Aprill, with his shoures soote The droghte of March hath perced to the roote And bathed every veyne in swich licour, Of which vertu engendred is the flour;" );
        ps.executeUpdate();

        setNextPoemID( ps, jdbcType, poemID++ );
        ps.setInt( 2, versionStamp++ );
        ps.setString( 3, "Andrew Marvell" );
        ps.setString( 4, "Andrew Marvell" );
        ps.setString( 5, "Had we but world enough, and time, This coyness, lady, were no crime." );
        ps.executeUpdate();

        setNextPoemID( ps, jdbcType, poemID++ );
        ps.setInt( 2, versionStamp++ );
        ps.setString( 3, "John Milton" );
        ps.setString( 4, "John Milton" );
        ps.setString( 5, "From morn to noon he fell, from noon to dewy eve, a summers day, and with the setting sun dropped from the ze4ith like a falling star on Lemnos, the Aegean isle" );
        ps.executeUpdate();

        setNextPoemID( ps, jdbcType, poemID++ );
        ps.setInt( 2, versionStamp++ );
        ps.setString( 3, "Lord Byron" );
        ps.setString( 4, "Lord Byron" );
        ps.setString( 5, "The Assyrian came down like the wolf on the fold, And his cohorts were gleaming in purple and gold; And the sheen of their spears was like stars on the sea, When the blue wave rolls nightly on deep Galilee." );
        ps.executeUpdate();

        setNextPoemID( ps, jdbcType, poemID++ );
        ps.setInt( 2, versionStamp++ );
        ps.setString( 3, "Walt Whitman" );
        ps.setString( 4, "Walt Whitman" );
        ps.setString( 5, "When lilacs last in the dooryard bloomd, And the great star early droopd in the western sky in the night, I mournd, and yet shall mourn with ever-returning spring." );
        ps.executeUpdate();

        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
    {
        goodStatement
            (
             conn,
             "create function getDatabaseLocale() returns varchar( 20 )\n" +
             "language java parameter style java reads sql data\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.LuceneSupportPermsTest.getDatabaseLocale()'\n"
             );
    }
    
    private String  getType( int jdbcType ) throws Exception
    {
        switch( jdbcType )
        {
        case Types.BINARY: return "char( 100 ) for bit data";
        case Types.BOOLEAN: return "boolean";
        case Types.DECIMAL: return "decimal";
        case Types.INTEGER: return "int";
        case Types.BIGINT: return "bigint";
        case Types.SMALLINT: return "smallint";
        case Types.DOUBLE: return "double";
        case Types.FLOAT: return "real";
        case Types.NUMERIC: return "numeric";
        case Types.CHAR: return "char( 5 )";
        case Types.REAL: return "real";
        case Types.VARCHAR: return "varchar( 5 )";
        case Types.VARBINARY: return "varchar( 256 ) for bit data";
        case Types.DATE: return "date";
        case Types.TIME: return "time";
        case Types.TIMESTAMP: return "timestamp";

        default:    throw new Exception( "Unsupported datatype: " + jdbcType );
        }
    }

    private void    setNextPoemID( PreparedStatement ps, int jdbcType, int intPoemID )
        throws Exception
    {
        switch( jdbcType )
        {
        case Types.BINARY:
            ps.setBytes( 1, makeBytes( intPoemID ) );
            break;

        case Types.BOOLEAN:
            ps.setBoolean( 1, (intPoemID % 2 == 0) ? true : false );
            break;

        case Types.DECIMAL:
        case Types.NUMERIC:
            ps.setBigDecimal( 1, new BigDecimal( intPoemID ) );
            break;

        case Types.INTEGER:
            ps.setInt( 1, intPoemID );
            break;

        case Types.BIGINT:
            ps.setLong( 1, (long) intPoemID + (long) Integer.MAX_VALUE );
            break;

        case Types.SMALLINT:
            ps.setShort( 1, (short) intPoemID );
            break;

        case Types.DOUBLE:
            ps.setDouble( 1, (double) intPoemID );
            break;

        case Types.FLOAT:
        case Types.REAL:
            ps.setFloat( 1, (float) intPoemID );
            break;

        case Types.CHAR:
        case Types.VARCHAR:
            ps.setString( 1, makeStringKey( intPoemID ) );
            break;

        case Types.VARBINARY:
            ps.setBytes( 1, makeAllBytes( intPoemID ) );
            break;

        case Types.DATE:
            ps.setDate( 1, new Date( MILLIS_IN_DAY * (long)(500 + intPoemID) ) );
            break;

        case Types.TIME:
            ps.setTime( 1, new Time( MILLIS_IN_HOUR * (long)(intPoemID) ) );
            break;

        case Types.TIMESTAMP:
            ps.setTimestamp( 1, new Timestamp( MILLIS_IN_DAY * (long)(500 + intPoemID) ) );
            break;

        default:    throw new Exception( "Unsupported datatype: " + jdbcType );
        }
    }
    private String  makeStringKey( int key )
    {
        String  digit = Integer.toString( key );
        return digit + digit + digit + digit + digit;
    }

    private void    dropSchema( Connection ruthConnection )    throws Exception
    {
        goodStatement( ruthConnection, "drop table poems" );
        goodStatement( ruthConnection, "drop function getDatabaseLocale" );
    }
    
    ///////////////////////////////////////////////////////////////////////////////////
    //
    // PROCEDURES AND FUNCTIONS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /** Get the database locale */
    public  static  String  getDatabaseLocale()
        throws SQLException
    {
        return ConnectionUtil.getCurrentLCC().getDatabase().getLocale().toString();
    }
    
    public  static  String  toString( byte[] value )
    {
        if ( value == null ) { return null; }

        return Arrays.toString( value );
    }

    /** Make a byte array with all possible byte values in it */
    public  static  byte[]  makeAllBytes( int initialValue )
    {
        int     size = 2 * ( -Byte.MIN_VALUE );
        byte[]  result = new byte[ size ];
        int     value = initialValue;

        if ( value < Byte.MIN_VALUE ) { value = Byte.MIN_VALUE; }
        if ( value > Byte.MAX_VALUE ) { value = Byte.MAX_VALUE; }

        for ( int idx = 0; idx < size; idx++ )
        {
            result[ idx ] = (byte) (value++);

            if ( value > Byte.MAX_VALUE ) { value = Byte.MIN_VALUE; }
        }

        return result;
    }
    
    /** Make a byte array starting with the given byte */
    public  static  byte[]  makeBytes( int initialValue )
    {
        byte[]  result = new byte[ initialValue ];

        for ( int idx = 0; idx < initialValue; idx++ )
        {
            result[ idx ] = (byte) initialValue;
        }

        return result;
    }

    /** Alternative QueryParser maker, which forces the text to be a constant string */
    public  static  LuceneIndexDescriptor constantStringIndexDescriptor()
    {
        return new ConstantIndexDescriptor();
    }
    
    /**
     * Delete a file. If it's a directory, recursively delete all directories
     * and files underneath it first.
     */
    private boolean deleteFile( File file )
        throws IOException, PrivilegedActionException
    {
        boolean retval = true;
        
        if ( isDirectory( file ) )
        {
            for ( File child : listFiles( file ) ) { retval = retval && deleteFile( child ); }
        }

        return retval && clobberFile( file );
    }

    /** Return true if the file is a directory */
    private boolean isDirectory( final File file )
        throws IOException, PrivilegedActionException
    {
        return AccessController.doPrivileged
            (
             new PrivilegedExceptionAction<Boolean>()
             {
                public Boolean run() throws IOException
                {
                    if ( file == null ) { return false; }
                    else { return file.isDirectory(); }
                }
             }
             ).booleanValue();
    }

    /** Really delete a file */
    private boolean clobberFile( final File file )
        throws IOException, PrivilegedActionException
    {
        return AccessController.doPrivileged
            (
             new PrivilegedExceptionAction<Boolean>()
             {
                public Boolean run() throws IOException
                {
                    return file.delete();
                }
             }
             ).booleanValue();
    }

    /** List files */
    private File[]  listFiles( final File file )
        throws IOException, PrivilegedActionException
    {
        return AccessController.doPrivileged
            (
             new PrivilegedExceptionAction<File[]>()
             {
                public File[] run() throws IOException
                {
                    return file.listFiles();
                }
             }
             );
    }

    public  static  void    setFieldNames( String... fieldNames ) { _fieldNames = fieldNames; }

    public  static  LuceneIndexDescriptor   makeFieldNameIndexDescriptor() { return new FieldNameIndexDescriptor(); }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // NESTED CLASSES
    //
    ///////////////////////////////////////////////////////////////////////////////////

    public  static  class   ConstantQueryParser extends MultiFieldQueryParser
    {
        public  ConstantQueryParser
            (
             Version version,
             String[] fieldNames,
             Analyzer analyzer
             )
        {
            super( version, fieldNames, analyzer );
        }

        public Query parse( String query )  throws ParseException
        {
            return super.parse( "one" );
        }
    }

    public  static  class   CD
    {
        public  final   String  name;
        public  final   String  type;

        public  CD( String Name, String Type )
        {
            name = Name;
            type = Type;
        }

        public  String  toString() { return "[ " + name + ", " + type + " ]"; }
    }

    public  static  class   ConstantIndexDescriptor extends LuceneUtils.DefaultIndexDescriptor
    {
        public  ConstantIndexDescriptor() { super(); }
        
        public  QueryParser getQueryParser()
            throws SQLException
        {
            return new ConstantQueryParser
                (
                 LuceneUtils.currentVersion(),
                 getFieldNames(),
                 getAnalyzer()
                 );
        }
    }

    public  static  class   FieldNameIndexDescriptor extends LuceneUtils.DefaultIndexDescriptor
    {
        public  FieldNameIndexDescriptor() { super(); }
        public  String[]    getFieldNames() { return LuceneSupportPermsTest._fieldNames; }
    }
    
}