SqlTransGenerator { public TransMeta generateTransMeta() throws KettleException { TransMeta transMeta = new TransMeta(); StringBuilder sbsql = new StringBuilder( sql.getServiceName() ); sbsql.append( " - SQL - " ).append( sql.getSqlString() ); for ( int i = sbsql.length() - 1; i >= 0; i-- ) { if ( sbsql.charAt( i ) == '\n' || sbsql.charAt( i ) == '\r' ) { sbsql.setCharAt( i, ' ' ); } } transMeta.setName( sbsql.toString() ); xLocation = 50; StepMeta firstStep = generateInjectorStep(); transMeta.addStep( firstStep ); injectorStepName = firstStep.getName(); StepMeta lastStep = firstStep; if ( serviceLimit > 0 ) { StepMeta serviceLimitStep = generateLimitStep( "Limit input rows", 0, serviceLimit ); lastStep = addToTrans( serviceLimitStep, transMeta, lastStep ); } lastStep = addToTrans( generateConversionStep(), transMeta, lastStep ); List<SQLField> constFields = sql.getSelectFields().getConstantFields(); if ( !constFields.isEmpty() ) { StepMeta constStep = generateConstStep( constFields ); lastStep = addToTrans( constStep, transMeta, lastStep ); } List<SQLField> iifFields = sql.getSelectFields().getIifFunctionFields(); for ( SQLField iifField : iifFields ) { lastStep = generateIifStep( iifField, transMeta, lastStep ); } if ( sql.getWhereCondition() != null && !sql.getWhereCondition().isEmpty() ) { Collection<DateToStrFunction> dateToStrFunctions = sql.getWhereCondition().getDateToStrFunctions(); if ( !dateToStrFunctions.isEmpty() ) { StepMeta fieldCloneStep = generateDateToStrStep( dateToStrFunctions ); lastStep = addToTrans( fieldCloneStep, transMeta, lastStep ); } StepMeta filterStep = generateFilterStep( sql.getWhereCondition().getCondition(), false ); lastStep = addToTrans( filterStep, transMeta, lastStep ); if ( !dateToStrFunctions.isEmpty() ) { StepMeta cleanupStep = generateRemoveStep( dateToStrFunctions ); lastStep = addToTrans( cleanupStep, transMeta, lastStep ); } } List<SQLField> aggFields = sql.getSelectFields().getAggregateFields(); if ( sql.getHavingCondition() != null ) { List<SQLField> havingFields = sql.getHavingCondition().extractHavingFields( sql.getSelectFields().getFields(), aggFields, transMeta.getStepFields( lastStep ) ); aggFields.addAll( havingFields ); } List<SQLField> groupFields = sql.getGroupFields().getFields(); if ( aggFields.size() > 0 || groupFields.size() > 0 ) { StepMeta groupStep = generateGroupByStep( aggFields, groupFields, transMeta.getStepFields( lastStep ) ); lastStep = addToTrans( groupStep, transMeta, lastStep ); } if ( sql.getOrderFields() != null ) { iifFields = sql.getOrderFields().getIifFunctionFields(); for ( SQLField iifField : iifFields ) { lastStep = generateIifStep( iifField, transMeta, lastStep ); } } if ( sql.getHavingCondition() != null && !sql.getHavingCondition().isEmpty() ) { StepMeta filterStep = generateFilterStep( sql.getHavingCondition().getCondition(), true ); lastStep = addToTrans( filterStep, transMeta, lastStep ); } if ( sql.getSelectFields().isDistinct() ) { StepMeta filterStep = generateUniqueStep( transMeta.getStepFields( lastStep ) ); lastStep = addToTrans( filterStep, transMeta, lastStep ); } if ( sql.getOrderFields() != null && !sql.getOrderFields().isEmpty() ) { StepMeta sortStep = generateSortStep( transMeta.getStepFields( lastStep ) ); lastStep = addToTrans( sortStep, transMeta, lastStep ); } if ( sql.getSelectFields().getFields().size() > 0 ) { lastStep = addToTrans( generateSelectStep(), transMeta, lastStep ); } if ( sql.getLimitClause() != null ) { int limit = sql.getLimitValues().getLimit(); int offset = sql.getLimitValues().getOffset(); StepMeta limitStep = generateLimitStep( "Limit rows", offset, limit ); lastStep = addToTrans( limitStep, transMeta, lastStep ); } if ( rowLimit > 0 ) { StepMeta sampleStep = generateSampleStep(); lastStep = addToTrans( sampleStep, transMeta, lastStep ); } StepMeta resultStep = generateResultStep(); resultStepName = resultStep.getName(); lastStep = addToTrans( resultStep, transMeta, lastStep ); return transMeta; } SqlTransGenerator( SQL sql, int rowLimit );  SqlTransGenerator( SQL sql, int rowLimit, int serviceLimit ); TransMeta generateTransMeta(); SQL getSql(); String getInjectorStepName(); void setInjectorStepName( String injectorStepName ); String getResultStepName(); void setResultStepName( String resultStepName ); int getRowLimit(); int getServiceRowLimit();  }
@Test public void testGenTransUsingSqlWithAggAndAliases() throws KettleException { SQL sql = new SQL( "SELECT \"FACT\".\"mth\" AS \"COL0\",AVG(\"FACT\".\"bmi\") AS \"COL1\"," + "\"FACT\".\"gender\" AS \"COL2\" FROM \"FACT\" GROUP BY \"FACT\".\"mth\",\"FACT\".\"gender\"" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "mth" ) ); rowMeta.addValueMeta( new ValueMetaInteger( "bmi" ) ); rowMeta.addValueMeta( new ValueMetaString( "gender" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "mth", "COL1", "gender" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { "COL0", null, "COL2" } ) ); }
@Test public void testGenTransUsingSqlWithGroupByOrderByNoAlias() throws KettleException { SQL sql = new SQL( "SELECT foo, bar, baz FROM table GROUP BY foo, bar, baz ORDER BY foo" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); rowMeta.addValueMeta( new ValueMetaInteger( "bar" ) ); rowMeta.addValueMeta( new ValueMetaString( "baz" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo", "bar", "baz" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null, null, null } ) ); }
@Test public void testGenTransAllAggsWithAliases() throws KettleException { SQL sql = new SQL( "SELECT sum(foo) as c1, avg(bar) as c2, max(baz) as c3 FROM table" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); rowMeta.addValueMeta( new ValueMetaInteger( "bar" ) ); rowMeta.addValueMeta( new ValueMetaString( "baz" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "c1", "c2", "c3" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null, null, null } ) ); }
@Test public void testGenTransAllAggsNoAliases() throws KettleException { SQL sql = new SQL( "SELECT sum(foo), avg(bar), max(baz) FROM table" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); rowMeta.addValueMeta( new ValueMetaInteger( "bar" ) ); rowMeta.addValueMeta( new ValueMetaString( "baz" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo", "bar", "baz" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null, null, null } ) ); }
@Test public void testGenTransSelectStar() throws KettleException { SQL sql = new SQL( "SELECT * FROM table" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null } ) ); }
@Test public void testLimitClause() throws KettleException { SQL sql = new SQL( "SELECT * FROM table LIMIT 1" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null } ) ); assertThat( sql.getLimitClause(), notNullValue() ); }
@Test public void testFilterStep() throws KettleException { SQL sql = new SQL( "SELECT * FROM table WHERE foo > 1" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaInteger( "foo" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null } ) ); }
@Test public void testUniqueStep() throws KettleException { SQL sql = new SQL( "SELECT DISTINCT foo FROM table" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaInteger( "foo" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null } ) ); }
@Test public void testRowLimit() throws KettleException { SQL sql = new SQL( "SELECT * FROM table" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 1 ); SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta( generator.generateTransMeta() ); assertThat( selectValuesMeta.getSelectName(), equalTo( new String[] { "foo" } ) ); assertThat( selectValuesMeta.getSelectRename(), equalTo( new String[] { null } ) ); }
@Test public void testServiceLimit() throws KettleException { SQL sql = new SQL( "SELECT * FROM table" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaString( "foo" ) ); sql.parse( rowMeta ); final String genLimitStep = "Limit input rows"; SqlTransGenerator generator = new SqlTransGenerator( sql, 0, 2 ); TransMeta transMeta = generator.generateTransMeta(); SampleRowsMeta limitInput = (SampleRowsMeta) getStepByName( transMeta, genLimitStep ); assertEquals( "limit not generated", "1..2", limitInput.getLinesRange() ); generator = new SqlTransGenerator( sql, 0, -3 ); transMeta = generator.generateTransMeta(); assertTrue( "limit<=0 not ignored", Arrays.asList( transMeta.getStepNames() ).indexOf( genLimitStep ) < 0 ); }
@Test public void testGenerateDateToStrSteps() throws KettleException { SQL sql = new SQL( "SELECT * FROM table WHERE DATE_TO_STR(date, 'MM') = '01'" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaDate( "date" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); TransMeta transMeta = generator.generateTransMeta(); CalculatorMeta dateToStrStepMeta = (CalculatorMeta) getStepByName( transMeta, "DateToStr" ); SelectValuesMeta cleanUpStepMeta = (SelectValuesMeta) getStepByName( transMeta, "DateToStr - Remove temporary fields" ); FilterRowsMeta whereStepMeta = (FilterRowsMeta) getStepByName( transMeta, "Where filter" ); String temporaryField = dateToStrStepMeta.getCalculation()[0].getFieldName(); assertEquals( temporaryField, cleanUpStepMeta.getDeleteName()[0] ); assertEquals( temporaryField, whereStepMeta.getCondition().getLeftValuename() ); assertThat( dateToStrStepMeta.getCalculation()[0].getFieldA(), equalTo( "date" ) ); assertThat( dateToStrStepMeta.getCalculation()[0].getConversionMask(), equalTo( "MM" ) ); assertThat( dateToStrStepMeta.getCalculation()[0].getValueType(), is( TYPE_STRING ) ); }
@Test public void testDateToStrStepsNotGenerated() throws KettleException { SQL sql = new SQL( "SELECT * FROM table WHERE date > [2016/04/01]" ); RowMetaInterface rowMeta = new RowMeta(); rowMeta.addValueMeta( new ValueMetaDate( "date" ) ); sql.parse( rowMeta ); SqlTransGenerator generator = new SqlTransGenerator( sql, 0 ); TransMeta transMeta = generator.generateTransMeta(); assertStepNotPresent( transMeta, "DateToStr" ); assertStepNotPresent( transMeta, "DateToStr - Remove temporary fields" ); }