<commit_before> /**********************************************************************
 **                                                                   **
 **               This code belongs to the KETTLE project.            **
 **                                                                   **
 ** Kettle, from version 2.2 on, is released into the public domain   **
 ** under the Lesser GNU Public License (LGPL).                       **
 **                                                                   **
 ** For more details, please read the document LICENSE.txt, included  **
 ** in this project                                                   **
 **                                                                   **
 ** http://www.kettle.be                                              **
 ** <EMAIL>                                                    **
 **                                                                   **
 **********************************************************************/
 
package be.ibridge.kettle.trans.step.databaselookup;

import java.util.Enumeration;
import java.util.Hashtable;

import be.ibridge.kettle.core.Const;
import be.ibridge.kettle.core.Row;
import be.ibridge.kettle.core.TimedRow;
import be.ibridge.kettle.core.database.Database;
import be.ibridge.kettle.core.exception.KettleException;
import be.ibridge.kettle.core.exception.KettleStepException;
import be.ibridge.kettle.core.value.Value;
import be.ibridge.kettle.trans.Trans;
import be.ibridge.kettle.trans.TransMeta;
import be.ibridge.kettle.trans.step.BaseStep;
import be.ibridge.kettle.trans.step.StepDataInterface;
import be.ibridge.kettle.trans.step.StepInterface;
import be.ibridge.kettle.trans.step.StepMeta;
import be.ibridge.kettle.trans.step.StepMetaInterface;

/**
 * Looks up values in a database using keys from input streams.
 * 
 * @author Matt
 * @since 26-apr-2003
 */
public class DatabaseLookup extends BaseStep implements StepInterface
{
	private DatabaseLookupMeta meta;
	private DatabaseLookupData data;

	public DatabaseLookup(StepMeta stepMeta, StepDataInterface stepDataInterface, int copyNr, TransMeta transMeta, Trans trans)
	{
		super(stepMeta, stepDataInterface, copyNr, transMeta, trans);
	}

	/**
	 * Performs the lookup based on the meta-data and the input row.
	 * @param row The row to use as lookup data and the row to add the returned lookup fields to
	 * @return true if we can pass the rows to the next steps, false if we can't.
	 * @throws KettleException In case something goes wrong.
	 */
	private synchronized boolean lookupValues(Row row) throws KettleException
	{
		Row lu;
		Row add;
		boolean cache_now=false;		

		if (first)
		{
            first=false;

			if (meta.isCached())
			{
				if (meta.getCacheSize()>0)
				{
					data.look=new Hashtable((int)(meta.getCacheSize()*1.5));
				}
				else
				{
					data.look=new Hashtable();
				}
			}

			data.db.setLookup(meta.getSchemaName(), meta.getTablename(), meta.getTableKeyField(), meta.getKeyCondition(), meta.getReturnValueField(), meta.getReturnValueNewName(), meta.getOrderByClause(), meta.isFailingOnMultipleResults());

			// lookup the values!
			if (log.isDetailed()) logDetailed(Messages.getString("DatabaseLookup.Log.CheckingRow")+row.toString()); //$NON-NLS-1$
			data.keynrs = new int[meta.getStreamKeyField1().length];
			data.keynrs2= new int[meta.getStreamKeyField1().length];

			for (int i=0;i<meta.getStreamKeyField1().length;i++)
			{
				data.keynrs[i]=row.searchValueIndex(meta.getStreamKeyField1()[i]);
				if (data.keynrs[i]<0 &&  // couldn't find field!
                    !"IS NULL".equalsIgnoreCase(meta.getKeyCondition()[i]) &&   // No field needed! //$NON-NLS-1$
				    !"IS NOT NULL".equalsIgnoreCase(meta.getKeyCondition()[i])  // No field needed! //$NON-NLS-1$
                   )
				{
					throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0001.FieldRequired1.Exception")+meta.getStreamKeyField1()[i]+Messages.getString("DatabaseLookup.ERROR0001.FieldRequired2.Exception")); //$NON-NLS-1$ //$NON-NLS-2$
				}
				data.keynrs2[i]=row.searchValueIndex(meta.getStreamKeyField2()[i]);
				if (data.keynrs2[i]<0 &&  // couldn't find field!
				    "BETWEEN".equalsIgnoreCase(meta.getKeyCondition()[i])   // 2 fields needed! //$NON-NLS-1$
				   )
				{
					throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0001.FieldRequired3.Exception")+meta.getStreamKeyField2()[i]+Messages.getString("DatabaseLookup.ERROR0001.FieldRequired4.Exception")); //$NON-NLS-1$ //$NON-NLS-2$
				}
				if (log.isDebug()) logDebug(Messages.getString("DatabaseLookup.Log.FieldHasIndex1")+meta.getStreamKeyField1()[i]+Messages.getString("DatabaseLookup.Log.FieldHasIndex2")+data.keynrs[i]); //$NON-NLS-1$ //$NON-NLS-2$
			}

			data.nullif = new Value[meta.getReturnValueField().length];

			for (int i=0;i<meta.getReturnValueField().length;i++)
			{
				data.nullif[i] = new Value(meta.getReturnValueNewName()[i], meta.getReturnValueDefaultType()[i]);
				if (!Const.isEmpty(meta.getReturnValueDefault()[i]))
				{
                    data.nullif[i].setValue(meta.getReturnValueDefault()[i]);
					data.nullif[i].setType(meta.getReturnValueDefaultType()[i]);
				}
				else
				{
					data.nullif[i].setType(meta.getReturnValueDefaultType()[i]);
					data.nullif[i].setNull();
				}
			}

			// Determine the types...
			data.keytypes = new int[meta.getTableKeyField().length];
            String schemaTable = meta.getDatabaseMeta().getQuotedSchemaTableCombination(meta.getSchemaName(), meta.getTablename());
			Row fields = data.db.getTableFields(schemaTable);
			if (fields!=null)
			{
				// Fill in the types...
				for (int i=0;i<meta.getTableKeyField().length;i++)
				{
					Value key = fields.searchValue(meta.getTableKeyField()[i]);
					if (key!=null)
					{
						data.keytypes[i] = key.getType();
					}
					else
					{
						throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0001.FieldRequired5.Exception")+meta.getTableKeyField()[i]+Messages.getString("DatabaseLookup.ERROR0001.FieldRequired6.Exception")); //$NON-NLS-1$ //$NON-NLS-2$
					}
				}
			}
			else
			{
				throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0002.UnableToDetermineFieldsOfTable")+schemaTable+"]"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		}

		lu = new Row();
		for (int i=0;i<meta.getStreamKeyField1().length;i++)
		{
			if (data.keynrs[i]>=0)
			{
				Value value = row.getValue(data.keynrs[i]);
				// Try to convert type if needed in a clone, we don't want to
				// change the type in the original row
				Value clonedValue = value.Clone();

				if (clonedValue.getType()!=data.keytypes[i]) clonedValue.setType(data.keytypes[i]);
				lu.addValue( clonedValue );
			}
			if (data.keynrs2[i]>=0)
			{
				Value value = row.getValue(data.keynrs2[i]);

				// Try to convert type if needed in a clone, we don't want to
				// change the type in the original row
				Value clonedValue = value.Clone();

				if (clonedValue.getType()!=data.keytypes[i]) clonedValue.setType(data.keytypes[i]);
				lu.addValue( clonedValue );
			}
		}

		// First, check if we looked up before
		if (meta.isCached())
        {
            TimedRow timedRow = (TimedRow) data.look.get(lu);
            if (timedRow==null)
            {
                add=null;
            }
            else
            {
                add=timedRow.getRow();
            }
        }
		else add=null; 

		if (add==null)
		{
			if (log.isRowLevel()) logRowlevel(Messages.getString("DatabaseLookup.Log.AddedValuesToLookupRow1")+meta.getStreamKeyField1().length+Messages.getString("DatabaseLookup.Log.AddedValuesToLookupRow2")+lu); //$NON-NLS-1$ //$NON-NLS-2$

			data.db.setValuesLookup(lu);
			add=data.db.getLookup(meta.isFailingOnMultipleResults());
			cache_now=true;
		}

		if (add==null) // nothing was found, unknown code: add default values
		{
			if (meta.isEatingRowOnLookupFailure())
			{
				return false;
			}
			if (log.isRowLevel()) logRowlevel(Messages.getString("DatabaseLookup.Log.NoResultsFoundAfterLookup")); //$NON-NLS-1$
			add=new Row();
			for (int i=0;i<meta.getReturnValueField().length;i++)
			{
				if (data.nullif[i]!=null)
				{
					add.addValue(new Value(data.nullif[i]));
				}
				else
				{
					Value v = new Value(meta.getReturnValueNewName()[i], meta.getReturnValueDefaultType()[i]);
					v.setNull();
					add.addValue(v);			
				}
			}
		}
        else
        {
        	if (log.isRowLevel()) logRowlevel(Messages.getString("DatabaseLookup.Log.FoundResultsAfterLookup")+add); //$NON-NLS-1$

        	int types[] = meta.getReturnValueDefaultType();<commit_msg>More output if lookup failes<commit_after> /**********************************************************************
 **                                                                   **
 **               This code belongs to the KETTLE project.            **
 **                                                                   **
 ** Kettle, from version 2.2 on, is released into the public domain   **
 ** under the Lesser GNU Public License (LGPL).                       **
 **                                                                   **
 ** For more details, please read the document LICENSE.txt, included  **
 ** in this project                                                   **
 **                                                                   **
 ** http://www.kettle.be                                              **
 ** <EMAIL>                                                    **
 **                                                                   **
 **********************************************************************/
 
package be.ibridge.kettle.trans.step.databaselookup;

import java.util.Enumeration;
import java.util.Hashtable;

import be.ibridge.kettle.core.Const;
import be.ibridge.kettle.core.Row;
import be.ibridge.kettle.core.TimedRow;
import be.ibridge.kettle.core.database.Database;
import be.ibridge.kettle.core.exception.KettleDatabaseException;
import be.ibridge.kettle.core.exception.KettleException;
import be.ibridge.kettle.core.exception.KettleStepException;
import be.ibridge.kettle.core.value.Value;
import be.ibridge.kettle.trans.Trans;
import be.ibridge.kettle.trans.TransMeta;
import be.ibridge.kettle.trans.step.BaseStep;
import be.ibridge.kettle.trans.step.StepDataInterface;
import be.ibridge.kettle.trans.step.StepInterface;
import be.ibridge.kettle.trans.step.StepMeta;
import be.ibridge.kettle.trans.step.StepMetaInterface;

/**
 * Looks up values in a database using keys from input streams.
 * 
 * @author Matt
 * @since 26-apr-2003
 */
public class DatabaseLookup extends BaseStep implements StepInterface
{
	private DatabaseLookupMeta meta;
	private DatabaseLookupData data;

	public DatabaseLookup(StepMeta stepMeta, StepDataInterface stepDataInterface, int copyNr, TransMeta transMeta, Trans trans)
	{
		super(stepMeta, stepDataInterface, copyNr, transMeta, trans);
	}

	/**
	 * Performs the lookup based on the meta-data and the input row.
	 * @param row The row to use as lookup data and the row to add the returned lookup fields to
	 * @return true if we can pass the rows to the next steps, false if we can't.
	 * @throws KettleException In case something goes wrong.
	 */
	private synchronized boolean lookupValues(Row row) throws KettleException
	{
		Row lu;
		Row add;
		boolean cache_now=false;		

		if (first)
		{
            first=false;

			if (meta.isCached())
			{
				if (meta.getCacheSize()>0)
				{
					data.look=new Hashtable((int)(meta.getCacheSize()*1.5));
				}
				else
				{
					data.look=new Hashtable();
				}
			}

			data.db.setLookup(meta.getSchemaName(), meta.getTablename(), meta.getTableKeyField(), meta.getKeyCondition(), meta.getReturnValueField(), meta.getReturnValueNewName(), meta.getOrderByClause(), meta.isFailingOnMultipleResults());

			// lookup the values!
			if (log.isDetailed()) logDetailed(Messages.getString("DatabaseLookup.Log.CheckingRow")+row.toString()); //$NON-NLS-1$
			data.keynrs = new int[meta.getStreamKeyField1().length];
			data.keynrs2= new int[meta.getStreamKeyField1().length];

			for (int i=0;i<meta.getStreamKeyField1().length;i++)
			{
				data.keynrs[i]=row.searchValueIndex(meta.getStreamKeyField1()[i]);
				if (data.keynrs[i]<0 &&  // couldn't find field!
                    !"IS NULL".equalsIgnoreCase(meta.getKeyCondition()[i]) &&   // No field needed! //$NON-NLS-1$
				    !"IS NOT NULL".equalsIgnoreCase(meta.getKeyCondition()[i])  // No field needed! //$NON-NLS-1$
                   )
				{
					throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0001.FieldRequired1.Exception")+meta.getStreamKeyField1()[i]+Messages.getString("DatabaseLookup.ERROR0001.FieldRequired2.Exception")); //$NON-NLS-1$ //$NON-NLS-2$
				}
				data.keynrs2[i]=row.searchValueIndex(meta.getStreamKeyField2()[i]);
				if (data.keynrs2[i]<0 &&  // couldn't find field!
				    "BETWEEN".equalsIgnoreCase(meta.getKeyCondition()[i])   // 2 fields needed! //$NON-NLS-1$
				   )
				{
					throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0001.FieldRequired3.Exception")+meta.getStreamKeyField2()[i]+Messages.getString("DatabaseLookup.ERROR0001.FieldRequired4.Exception")); //$NON-NLS-1$ //$NON-NLS-2$
				}
				if (log.isDebug()) logDebug(Messages.getString("DatabaseLookup.Log.FieldHasIndex1")+meta.getStreamKeyField1()[i]+Messages.getString("DatabaseLookup.Log.FieldHasIndex2")+data.keynrs[i]); //$NON-NLS-1$ //$NON-NLS-2$
			}

			data.nullif = new Value[meta.getReturnValueField().length];

			for (int i=0;i<meta.getReturnValueField().length;i++)
			{
				data.nullif[i] = new Value(meta.getReturnValueNewName()[i], meta.getReturnValueDefaultType()[i]);
				if (!Const.isEmpty(meta.getReturnValueDefault()[i]))
				{
                    data.nullif[i].setValue(meta.getReturnValueDefault()[i]);
					data.nullif[i].setType(meta.getReturnValueDefaultType()[i]);
				}
				else
				{
					data.nullif[i].setType(meta.getReturnValueDefaultType()[i]);
					data.nullif[i].setNull();
				}
			}

			// Determine the types...
			data.keytypes = new int[meta.getTableKeyField().length];
            String schemaTable = meta.getDatabaseMeta().getQuotedSchemaTableCombination(meta.getSchemaName(), meta.getTablename());
			Row fields = data.db.getTableFields(schemaTable);
			if (fields!=null)
			{
				// Fill in the types...
				for (int i=0;i<meta.getTableKeyField().length;i++)
				{
					Value key = fields.searchValue(meta.getTableKeyField()[i]);
					if (key!=null)
					{
						data.keytypes[i] = key.getType();
					}
					else
					{
						throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0001.FieldRequired5.Exception")+meta.getTableKeyField()[i]+Messages.getString("DatabaseLookup.ERROR0001.FieldRequired6.Exception")); //$NON-NLS-1$ //$NON-NLS-2$
					}
				}
			}
			else
			{
				throw new KettleStepException(Messages.getString("DatabaseLookup.ERROR0002.UnableToDetermineFieldsOfTable")+schemaTable+"]"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		}

		lu = new Row();
		for (int i=0;i<meta.getStreamKeyField1().length;i++)
		{
			if (data.keynrs[i]>=0)
			{
				Value value = row.getValue(data.keynrs[i]);
				// Try to convert type if needed in a clone, we don't want to
				// change the type in the original row
				Value clonedValue = value.Clone();

				if (clonedValue.getType()!=data.keytypes[i]) clonedValue.setType(data.keytypes[i]);
				lu.addValue( clonedValue );
			}
			if (data.keynrs2[i]>=0)
			{
				Value value = row.getValue(data.keynrs2[i]);

				// Try to convert type if needed in a clone, we don't want to
				// change the type in the original row
				Value clonedValue = value.Clone();

				if (clonedValue.getType()!=data.keytypes[i]) clonedValue.setType(data.keytypes[i]);
				lu.addValue( clonedValue );
			}
		}

		// First, check if we looked up before
		if (meta.isCached())
        {
            TimedRow timedRow = (TimedRow) data.look.get(lu);
            if (timedRow==null)
            {
                add=null;
            }
            else
            {
                add=timedRow.getRow();
            }
        }
		else add=null; 

		if (add==null)
		{
			if (log.isRowLevel()) logRowlevel(Messages.getString("DatabaseLookup.Log.AddedValuesToLookupRow1")+meta.getStreamKeyField1().length+Messages.getString("DatabaseLookup.Log.AddedValuesToLookupRow2")+lu); //$NON-NLS-1$ //$NON-NLS-2$

			data.db.setValuesLookup(lu);
			try {
				add = data.db.getLookup(meta.isFailingOnMultipleResults());
			} catch (KettleDatabaseException e) {
				logError("Database lookup failed for values: " + lu);
				throw e;
			}
			cache_now=true;
		}

		if (add==null) // nothing was found, unknown code: add default values
		{
			if (meta.isEatingRowOnLookupFailure())
			{
				return false;
			}
			if (log.isRowLevel()) logRowlevel(Messages.getString("DatabaseLookup.Log.NoResultsFoundAfterLookup")); //$NON-NLS-1$
			add=new Row();
			for (int i=0;i<meta.getReturnValueField().length;i++)
			{
				if (data.nullif[i]!=null)
				{
					add.addValue(new Value(data.nullif[i]));
				}
				else
				{
					Value v = new Value(meta.getReturnValueNewName()[i], meta.getReturnValueDefaultType()[i]);
					v.setNull();
					add.addValue(v);			
				}
			}
		}
        else
        {
        	if (log.isRowLevel()) logRowlevel(Messages.getString("DatabaseLookup.Log.FoundResultsAfterLookup")+add); //$NON-NLS-1$

        	int types[] = meta.getReturnValueDefaultType();
{'fraction_non_alphanumeric': 0.15248732121488404, 'fraction_numerical': 0.010484927916120577, 'mean_word_length': 2.7725709372312983, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 42, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3633794', 'n_tokens_mistral': 6214, 'n_tokens_neox': 5327, 'n_words': 954}