Java Code Examples for org.pentaho.metadata.model.LogicalColumn#setProperty()
The following examples show how to use
org.pentaho.metadata.model.LogicalColumn#setProperty() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: SpiderWebTestModel.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 5 votes |
|
private LogicalTable createLogicalTable( String tblId, LogicalModel model, Category mainCat ) throws Exception {
LogicalTable rtn = new LogicalTable();
rtn.setId( "bt_" + tblId );
rtn.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt_" + tblId ); //$NON-NLS-1$
createBusinessKeyColumn( tblId, "keya", rtn, mainCat ); // 0
createBusinessKeyColumn( tblId, "keyb", rtn, mainCat ); // 1
createBusinessKeyColumn( tblId, "keyc", rtn, mainCat ); // 2
createBusinessKeyColumn( tblId, "keyd", rtn, mainCat ); // 3
createBusinessKeyColumn( tblId, "keye", rtn, mainCat ); // 4
createBusinessKeyColumn( tblId, "keyf", rtn, mainCat ); // 5
createBusinessKeyColumn( tblId, "keyg", rtn, mainCat ); // 6
createBusinessKeyColumn( tblId, "keyh", rtn, mainCat ); // 7
createBusinessKeyColumn( tblId, "keyi", rtn, mainCat ); // 8
createBusinessKeyColumn( tblId, "keyj", rtn, mainCat ); // 9
createBusinessKeyColumn( tblId, "keyk", rtn, mainCat ); // 10
createBusinessKeyColumn( tblId, "keyl", rtn, mainCat ); // 11
LogicalColumn bcs1 = new LogicalColumn();
bcs1.setId( "bcs_" + tblId );
bcs1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc_" + tblId ); //$NON-NLS-1$
bcs1.setProperty( IPhysicalColumn.AGGREGATIONTYPE_PROPERTY, AggregationType.SUM );
bcs1.setLogicalTable( rtn );
rtn.addLogicalColumn( bcs1 );
mainCat.addLogicalColumn( bcs1 );
model.addLogicalTable( rtn );
return rtn;
}
Example 2
| Source File: StarModelDialog.java From pentaho-kettle with Apache License 2.0 | 5 votes |
|
protected void addDimensionKeys() {
// Import the technical keys from all the dimensions in the fact table...
//
List<LogicalColumn> keyColumns = new ArrayList<LogicalColumn>();
List<LogicalTable> dimensionTables = ConceptUtil.findLogicalTables(logicalModel, TableType.DIMENSION);
for (LogicalTable dimensionTable : dimensionTables) {
// Find the technical or smart key
//
keyColumns.addAll( ConceptUtil.findLogicalColumns(dimensionTable, AttributeType.SMART_TECHNICAL_KEY) );
keyColumns.addAll( ConceptUtil.findLogicalColumns(dimensionTable, AttributeType.TECHNICAL_KEY) );
}
for (LogicalColumn keyColumn : keyColumns) {
LogicalColumn column = new LogicalColumn();
String dimensionName = ConceptUtil.getName(keyColumn.getLogicalTable(), locale);
column.setName(new LocalizedString(locale, dimensionName+" TK"));
column.setDescription(new LocalizedString(locale, ConceptUtil.getDescription(keyColumn, locale)));
column.setProperty(DefaultIDs.LOGICAL_COLUMN_PHYSICAL_COLUMN_NAME, dimensionName.toLowerCase().replace(' ', '_')+"_tk");
column.setProperty(DefaultIDs.LOGICAL_COLUMN_ATTRIBUTE_TYPE, AttributeType.TECHNICAL_KEY.name());
column.setDataType(keyColumn.getDataType());
column.setProperty(DefaultIDs.LOGICAL_COLUMN_DIMENSION_NAME, dimensionName);
column.setProperty(DefaultIDs.LOGICAL_COLUMN_LENGTH, ConceptUtil.getString(keyColumn, DefaultIDs.LOGICAL_COLUMN_LENGTH));
column.setProperty(DefaultIDs.LOGICAL_COLUMN_LENGTH, ConceptUtil.getString(keyColumn, DefaultIDs.LOGICAL_COLUMN_LENGTH));
column.setProperty(DefaultIDs.LOGICAL_COLUMN_CONVERSION_REMARKS, "Key to dimension '"+dimensionName+"'");
addLogicalColumnToFactAttributesList(column);
}
wFactAttributes.removeEmptyRows();
wFactAttributes.setRowNums();
wFactAttributes.optWidth(true);
getRelationshipsFromFact();
}
Example 3
| Source File: SpiderWebTestModel.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 5 votes |
|
private LogicalColumn createBusinessKeyColumn( String tblId, String columnId, LogicalTable tbl, Category cat )
throws Exception {
LogicalColumn rtn = new LogicalColumn();
rtn.setId( "bc_" + columnId + "_" + tblId );
rtn.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc_" + columnId + "_" + tblId ); //$NON-NLS-1$
rtn.setLogicalTable( tbl );
tbl.addLogicalColumn( rtn );
cat.addLogicalColumn( rtn );
return rtn;
}
Example 4
| Source File: DimensionTableDialog.java From pentaho-kettle with Apache License 2.0 | 5 votes |
|
private void addAttribute(String name, String description, String phName, AttributeType attributeType, DataType dataType, int length, int precision, String comment) {
LogicalColumn column = new LogicalColumn();
column.setLogicalTable(logicalTable);
column.setName(new LocalizedString(locale, name));
column.setDescription(new LocalizedString(locale, description));
column.setDataType(dataType);
column.setProperty(DefaultIDs.LOGICAL_COLUMN_ATTRIBUTE_TYPE, attributeType.name());
column.setProperty(DefaultIDs.LOGICAL_COLUMN_PHYSICAL_COLUMN_NAME, phName);
if (length>=0) column.setProperty(DefaultIDs.LOGICAL_COLUMN_LENGTH, Integer.toString(length));
if (precision>=0) column.setProperty(DefaultIDs.LOGICAL_COLUMN_PRECISION, Integer.toString(precision));
column.setProperty(DefaultIDs.LOGICAL_COLUMN_CONVERSION_REMARKS, comment);
addLogicalColumnToAttributesList(column);
}
Example 5
| Source File: SQLJoinIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 5 votes |
|
private LogicalTable getDummySingleColumnTable( String identifier ) {
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt" + identifier );
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt" + identifier );
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc" + identifier );
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc" + identifier );
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.COLUMN_NAME );
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
return bt1;
}
Example 6
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 2a: Two Joined Tables are outer joined to two other tables
*/
@Test
public void testOuterJoinScenario2a() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalTable bt4 = new LogicalTable();
bt4.setId( "bt4" ); //$NON-NLS-1$
bt4.setProperty( SqlPhysicalTable.TARGET_TABLE, "t4" ); //$NON-NLS-1$
final LogicalColumn bc4 = new LogicalColumn();
bc4.setId( "bc4" ); //$NON-NLS-1$
bc4.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc4.setLogicalTable( bt4 );
bt4.addLogicalColumn( bc4 );
mainCat.addLogicalColumn( bc4 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._1_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._0_N );
rl2.setFromTable( bt2 );
rl2.setFromColumn( bc2 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
final LogicalRelationship rl3 = new LogicalRelationship();
rl3.setRelationshipType( RelationshipType._1_N );
rl3.setFromTable( bt3 );
rl3.setFromColumn( bc3 );
rl3.setToTable( bt4 );
rl3.setToColumn( bc4 );
model.getLogicalRelationships().add( rl3 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc4, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0,bt2.k AS COL1,bt3.k AS COL2,bt4.k AS COL3 FROM t1 bt1 JOIN(t4 bt4 JOIN(t2 bt2 LEFT OUTER JOIN t3 bt3 ON(bt2.k = bt3.k))ON(bt3.k = bt4.k))ON(bt1.k = bt2.k)",
query.getQuery() ); //$NON-NLS-1$
}
Example 7
| Source File: MondrianModelExporterTest.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
@Test
public void testLevelProperties() throws Exception {
List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
OlapDimension dimension = new OlapDimension();
dimension.setName( "Dim1" );
List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
OlapHierarchy hierarchy = new OlapHierarchy();
hierarchy.setName( "Hier1" );
List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
OlapHierarchyLevel level = new OlapHierarchyLevel();
level.setName( "Lvl1" );
level.getAnnotations().add( new OlapAnnotation( "GeoRole", "city" ) );
List<LogicalColumn> cols = new ArrayList<LogicalColumn>();
LogicalColumn lcLat = new LogicalColumn();
lcLat.setName( new LocalizedString( "en_US", "latitude" ) );
lcLat.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "Latitude" );
lcLat.setDataType( DataType.NUMERIC );
LogicalColumn lcLon = new LogicalColumn();
lcLon.setName( new LocalizedString( "en_US", "longitude" ) );
lcLon.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "Longitude" );
lcLon.setDataType( DataType.NUMERIC );
cols.add( lcLat );
cols.add( lcLon );
level.setLogicalColumns( cols );
hierarchyLevels.add( level );
hierarchy.setHierarchyLevels( hierarchyLevels );
hierarchies.add( hierarchy );
dimension.setHierarchies( hierarchies );
dimensions.add( dimension );
List<OlapCube> cubes = new ArrayList<OlapCube>();
OlapCube cube = new OlapCube();
cube.setName( "Cube1" );
cubes.add( cube );
List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
OlapMeasure measure = new OlapMeasure();
measure.setName( "Meas1" );
measures.add( measure );
cube.setOlapMeasures( measures );
List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();
OlapDimensionUsage dimUsage = new OlapDimensionUsage();
dimensionUsages.add( dimUsage );
cube.setOlapDimensionUsages( dimensionUsages );
dimUsage.setName( "Dim1" );
dimUsage.setOlapDimension( dimension );
LogicalModel businessModel = TestHelper.buildDefaultModel();
LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
hierarchy.setLogicalTable( logicalTable );
List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
// level.setLogicalColumns(new ArrayList<LogicalColumn>());
logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, "select * from customer" );
LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
m.setAggregationType( AggregationType.SUM );
measure.setLogicalColumn( m );
businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE,
TargetTableType.INLINE_SQL );
cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );
businessModel.setProperty( "olap_dimensions", dimensions );
businessModel.setProperty( "olap_cubes", cubes );
businessModel.setName( new LocalizedString( "en_US", "model" ) );
MondrianModelExporter exporter = new MondrianModelExporter( businessModel, "en_US" );
String data = exporter.createMondrianModelXML();
TestHelper.assertEqualsIgnoreWhitespaces( "<Schema name=\"model\">\n" + " <Dimension name=\"Dim1\">\n"
+ " <Hierarchy name=\"Hier1\" hasAll=\"false\">\n" + " <View alias=\"FACT\">\n"
+ " <SQL dialect=\"generic\">\n" + " <![CDATA[select * from customer]]>\n" + " </SQL>\n"
+ " </View>\n" + " <Level name=\"Lvl1\" uniqueMembers=\"false\" column=\"pc1\" type=\"Numeric\">\n"
+ " <Annotations>\n" + " <Annotation name=\"GeoRole\">city</Annotation>\n"
+ " </Annotations>\n" + " <Property name=\"latitude\" column=\"Latitude\" type=\"Numeric\"/>\n"
+ " <Property name=\"longitude\" column=\"Longitude\" type=\"Numeric\"/>\n" + " </Level>\n"
+ " </Hierarchy>\n" + " </Dimension>\n" + " <Cube name=\"Cube1\">\n" + " <View alias=\"FACT\">\n"
+ " <SQL dialect=\"generic\">\n" + " <![CDATA[select * from customer]]>\n" + " </SQL>\n"
+ " </View>\n" + " <DimensionUsage name=\"Dim1\" source=\"Dim1\" foreignKey=\"pc2\"/>\n"
+ " <Measure name=\"bc1\" column=\"pc1\" aggregator=\"sum\"/>\n" + " </Cube>\n"
+ "</Schema>", data );
}
Example 8
| Source File: DimensionTableDialog.java From pentaho-kettle with Apache License 2.0 | 4 votes |
|
private void ok()
{
if (Utils.isEmpty(wTableName.getText())) {
MessageBox box = new MessageBox(shell, SWT.ICON_ERROR | SWT.OK);
box.setText(BaseMessages.getString(PKG, "DimensionTableDialog.ErrorDimensionHasNoName.Title"));
box.setMessage(BaseMessages.getString(PKG, "DimensionTableDialog.ErrorDimensionHasNoName.Message"));
box.open();
return;
}
logicalTable.setName( new LocalizedString(locale, wTableName.getText()) );
logicalTable.setDescription( new LocalizedString(locale, wTableDescription.getText()) );
logicalTable.setProperty(DefaultPropertyID.TABLE_TYPE.getId(), TableType.DIMENSION);
logicalTable.setProperty(DefaultIDs.LOGICAL_TABLE_PHYSICAL_TABLE_NAME, wPhysicalName.getText());
logicalTable.setProperty(DefaultIDs.LOGICAL_TABLE_DIMENSION_TYPE, wDimensionType.getText());
// name, description, field type, physical column name, data type, length, precision,
// source db, source table, source column, conversion remarks
//
logicalTable.getLogicalColumns().clear();
int nr = wAttributes.nrNonEmpty();
for (int i=0;i<nr;i++) {
TableItem item = wAttributes.getNonEmpty(i);
LogicalColumn logicalColumn = new LogicalColumn();
logicalColumn.setId(UUID.randomUUID().toString());
int col=1;
logicalColumn.setName(new LocalizedString(locale, item.getText(col++)));
logicalColumn.setDescription(new LocalizedString(locale, item.getText(col++)));
String fieldTypeString = item.getText(col++);
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_ATTRIBUTE_TYPE, AttributeType.getAttributeType(fieldTypeString).name());
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_PHYSICAL_COLUMN_NAME, item.getText(col++));
String dataTypeString = item.getText(col++);
logicalColumn.setDataType(Utils.isEmpty(dataTypeString) ? null : DataType.valueOf(dataTypeString));
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_LENGTH, item.getText(col++));
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_PRECISION, item.getText(col++));
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_SOURCE_DB, item.getText(col++));
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_SOURCE_TABLE, item.getText(col++));
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_SOURCE_COLUMN, item.getText(col++));
logicalColumn.setProperty(DefaultIDs.LOGICAL_COLUMN_CONVERSION_REMARKS, item.getText(col++));
logicalColumn.setLogicalTable(logicalTable);
logicalTable.getLogicalColumns().add(logicalColumn);
}
dispose();
}
Example 9
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 6: 4 tables outer joined
*
* NOTE: This does not work on MYSQL, because FULL OUTER JOIN is not supported.
*/
@Test
public void testOuterJoinScenario6() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalTable bt4 = new LogicalTable();
bt4.setId( "bt4" ); //$NON-NLS-1$
bt4.setProperty( SqlPhysicalTable.TARGET_TABLE, "t4" ); //$NON-NLS-1$
final LogicalColumn bc4 = new LogicalColumn();
bc4.setId( "bc4" ); //$NON-NLS-1$
bc4.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc4.setLogicalTable( bt4 );
bt4.addLogicalColumn( bc4 );
mainCat.addLogicalColumn( bc4 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_0 );
rl1.setJoinOrderKey( "A" );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._0_0 );
rl2.setJoinOrderKey( "B" );
rl2.setFromTable( bt2 );
rl2.setFromColumn( bc2 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
final LogicalRelationship rl3 = new LogicalRelationship();
rl3.setRelationshipType( RelationshipType._0_N );
rl3.setJoinOrderKey( "A" );
rl3.setFromTable( bt2 );
rl3.setFromColumn( bc2 );
rl3.setToTable( bt4 );
rl3.setToColumn( bc4 );
model.getLogicalRelationships().add( rl3 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc4, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 ,bt4.k AS COL3 FROM t3 bt3 FULL OUTER JOIN ( t1 bt1 FULL OUTER JOIN ( t2 bt2 LEFT OUTER JOIN t4 bt4 ON ( bt2.k = bt4.k ) ) ON ( bt1.k = bt2.k ) ) ON ( bt2.k = bt3.k )",
query.getQuery() ); //$NON-NLS-1$
}
Example 10
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 5: Two outer joins in the opposite direction
*/
@Test
public void testOuterJoinScenario5b() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._N_0 );
rl1.setJoinOrderKey( "B" );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._N_0 );
rl2.setJoinOrderKey( "A" );
rl2.setFromTable( bt1 );
rl2.setFromColumn( bc1 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t2 bt2 LEFT OUTER JOIN ( t1 bt1 RIGHT OUTER JOIN t3 bt3 ON ( bt1.k = bt3.k ) ) ON ( bt1.k = bt2.k )",
query.getQuery() ); //$NON-NLS-1$
}
Example 11
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 5: Two outer joins in the opposite direction
*/
@Test
public void testOuterJoinScenario5a() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._N_0 );
rl1.setJoinOrderKey( "A" );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._N_0 );
rl2.setJoinOrderKey( "B" );
rl2.setFromTable( bt1 );
rl2.setFromColumn( bc1 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t3 bt3 LEFT OUTER JOIN ( t1 bt1 RIGHT OUTER JOIN t2 bt2 ON ( bt1.k = bt2.k ) ) ON ( bt1.k = bt3.k )",
query.getQuery() ); //$NON-NLS-1$
}
Example 12
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 4: Two outer joins on a single table
*/
@Test
public void testOuterJoinScenario4() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._0_N );
rl2.setFromTable( bt1 );
rl2.setFromColumn( bc1 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t2 bt2 RIGHT OUTER JOIN ( t1 bt1 LEFT OUTER JOIN t3 bt3 ON ( bt1.k = bt3.k ) ) ON ( bt1.k = bt2.k )",
query.getQuery() ); //$NON-NLS-1$
}
Example 13
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 3: Three Tables are outer joined
*/
@Test
public void testOuterJoinScenario3() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._0_N );
rl2.setFromTable( bt2 );
rl2.setFromColumn( bc2 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t1 bt1 LEFT OUTER JOIN ( t2 bt2 LEFT OUTER JOIN t3 bt3 ON ( bt2.k = bt3.k ) ) ON ( bt1.k = bt2.k )",
query.getQuery() ); //$NON-NLS-1$
}
Example 14
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 2: Two Joined Tables are outer joined to a single table
*/
@Test
public void testOuterJoinScenario2() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalTable bt3 = new LogicalTable();
bt3.setId( "bt3" ); //$NON-NLS-1$
bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt3" ); //$NON-NLS-1$
final LogicalColumn bc3 = new LogicalColumn();
bc3.setId( "bc3" ); //$NON-NLS-1$
bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc3" ); //$NON-NLS-1$
bc3.setLogicalTable( bt3 );
bt3.addLogicalColumn( bc3 );
mainCat.addLogicalColumn( bc3 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
final LogicalRelationship rl2 = new LogicalRelationship();
rl2.setRelationshipType( RelationshipType._1_N );
rl2.setFromTable( bt2 );
rl2.setFromColumn( bc2 );
rl2.setToTable( bt3 );
rl2.setToColumn( bc3 );
model.getLogicalRelationships().add( rl2 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.pc1 AS COL0,bt2.pc2 AS COL1,bt3.pc3 AS COL2 FROM pt3 bt3 JOIN(pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON(bt1.pc1 = bt2.pc2))ON(bt2.pc2 = bt3.pc3)",
query.getQuery() ); //$NON-NLS-1$
}
Example 15
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 1c: Two Tables are outer joined with an aggregate constraint
*/
@Test
public void testOuterJoinScenario1c() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
bc2.setAggregationType( AggregationType.SUM );
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getConstraints().add( new Constraint( CombinationType.AND, "[cat_01.bc2] > 1" ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT bt1.pc1 AS COL0 ,SUM(bt2.pc2) AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 ) GROUP BY bt1.pc1 HAVING ( SUM(bt2.pc2) > 1 )",
query.getQuery() ); //$NON-NLS-1$
}
Example 16
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 1b: Two Tables are outer joined with an aggregate
*/
@Test
public void testOuterJoinScenario1b() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
bc2.setAggregationType( AggregationType.SUM );
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT bt1.pc1 AS COL0 ,SUM(bt2.pc2) AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 ) GROUP BY bt1.pc1",
query.getQuery() ); //$NON-NLS-1$
}
Example 17
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 1: Two Tables are outer joined
*/
@Test
public void testOuterJoinScenario1() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.pc1 AS COL0 ,bt2.pc2 AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 )",
query.getQuery() ); //$NON-NLS-1$
}
Example 18
| Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario 1: Two Tables are outer joined
*/
@Test
public void testOuterJoinScenario1WithAddlAlias() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bt1.addLogicalColumn( bc1 );
bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
model.getLogicalTables().add( bt1 );
mainCat.addLogicalColumn( bc1 );
final LogicalTable bt2 = new LogicalTable();
bt2.setId( "bt2" ); //$NON-NLS-1$
bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
bc2.setLogicalTable( bt2 );
bt2.addLogicalColumn( bc2 );
model.getLogicalTables().add( bt2 );
mainCat.addLogicalColumn( bc2 );
final LogicalRelationship rl1 = new LogicalRelationship();
rl1.setRelationshipType( RelationshipType._0_N );
rl1.setFromTable( bt1 );
rl1.setFromColumn( bc1 );
rl1.setToTable( bt2 );
rl1.setToColumn( bc2 );
model.getLogicalRelationships().add( rl1 );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
myTest.getSelections().add( new AliasedSelection( null, bc2, null, "alias" ) );
myTest.getConstraints().add( new Constraint( CombinationType.AND, "[cat_01.bc2] = 1" ) );
myTest.getConstraints().add( new Constraint( CombinationType.AND, "[alias.bc2] = 2" ) );
MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper
.assertEqualsIgnoreWhitespaces(
"SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt2_alias.k AS COL2 FROM t2 bt2 RIGHT OUTER JOIN ( t1 bt1 LEFT OUTER JOIN t2 bt2_alias ON ( bt1.k = bt2_alias.k AND ( bt2_alias.k = 2 ) ) ) ON ( bt1.k = bt2.k AND ( bt2.k = 1 ) )",
query.getQuery() ); //$NON-NLS-1$
}
Example 19
| Source File: AggregationScenariosIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario: we want to make a sum of a ratio<br>
* The aggregation on the ratio is obviously "SUM".<br>
* However, the aggregation on the used columns is none.<br>
* <br>
* This is a simple one-table example.<br>
*
*/
@Test
public void testSumOfRatioGroupBy() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
// dimension column d1
//
final LogicalColumn d1 = new LogicalColumn();
d1.setId( "d1" ); //$NON-NLS-1$
d1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "d" ); //$NON-NLS-1$
d1.setLogicalTable( bt1 );
d1.setAggregationType( AggregationType.NONE );
d1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.DIMENSION );
bt1.addLogicalColumn( d1 );
mainCat.addLogicalColumn( d1 );
// Sum column bc1
//
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "a" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bc1.setAggregationType( AggregationType.NONE );
bc1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );
bt1.addLogicalColumn( bc1 );
mainCat.addLogicalColumn( bc1 );
// Sum column bc2
//
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "b" ); //$NON-NLS-1$
bc2.setLogicalTable( bt1 );
bc2.setAggregationType( AggregationType.NONE );
bc2.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );
bt1.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
// A calculated column: ratio
//
final LogicalColumn ratio = new LogicalColumn();
ratio.setId( "ratio" ); //$NON-NLS-1$
ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "SUM( [bt1.bc1] / [bt1.bc2] )" ); //$NON-NLS-1$
ratio.setLogicalTable( bt1 );
ratio.setAggregationType( AggregationType.SUM );
ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
ratio.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );
bt1.addLogicalColumn( ratio );
mainCat.addLogicalColumn( ratio );
DatabaseMeta databaseMeta = TestHelper.createOracleDatabaseMeta();
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new Selection( null, d1, null ) );
myTest.getSelections().add( new Selection( null, ratio, null ) );
MappedQuery query = new SqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper.assertEqualsIgnoreWhitespaces(
"SELECT bt1.d AS COL0 , SUM( bt1.a / bt1.b ) AS COL1 FROM t1 bt1 GROUP BY bt1.d", //$NON-NLS-1$
query.getQuery() );
}
Example 20
| Source File: AggregationScenariosIT.java From pentaho-metadata with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Scenario: we have 2 sums and we want to calculate a ratio.<br>
* The aggregation on the ratio is obviously "none".<br>
* However, the generator has to keep in mind that it still needs to generate a group by.<br>
* <br>
* This is a simple one-table example.<br>
*
*/
@Test
public void testRatioOfSumsGroupBy() throws Exception {
final LogicalModel model = new LogicalModel();
model.setId( "model_01" );
Category mainCat = new Category();
mainCat.setId( "cat_01" );
model.getCategories().add( mainCat );
final LogicalTable bt1 = new LogicalTable();
bt1.setId( "bt1" ); //$NON-NLS-1$
bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$
// dimension column d1
//
final LogicalColumn d1 = new LogicalColumn();
d1.setId( "d1" ); //$NON-NLS-1$
d1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "d" ); //$NON-NLS-1$
d1.setLogicalTable( bt1 );
d1.setAggregationType( AggregationType.NONE );
d1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.DIMENSION );
bt1.addLogicalColumn( d1 );
mainCat.addLogicalColumn( d1 );
// Sum column bc1
//
final LogicalColumn bc1 = new LogicalColumn();
bc1.setId( "bc1" ); //$NON-NLS-1$
bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "a" ); //$NON-NLS-1$
bc1.setLogicalTable( bt1 );
bc1.setAggregationType( AggregationType.SUM );
bc1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );
bt1.addLogicalColumn( bc1 );
mainCat.addLogicalColumn( bc1 );
// Sum column bc2
//
final LogicalColumn bc2 = new LogicalColumn();
bc2.setId( "bc2" ); //$NON-NLS-1$
bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "b" ); //$NON-NLS-1$
bc2.setLogicalTable( bt1 );
bc2.setAggregationType( AggregationType.SUM );
bc2.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );
bt1.addLogicalColumn( bc2 );
mainCat.addLogicalColumn( bc2 );
// A calculated column: ratio
//
final LogicalColumn ratio = new LogicalColumn();
ratio.setId( "ratio" ); //$NON-NLS-1$
ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "[bt1.bc1] / [bt1.bc2]" ); //$NON-NLS-1$
ratio.setLogicalTable( bt1 );
ratio.setAggregationType( AggregationType.NONE );
ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
ratio.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );
bt1.addLogicalColumn( ratio );
mainCat.addLogicalColumn( ratio );
DatabaseMeta databaseMeta = TestHelper.createOracleDatabaseMeta();
Query myTest = new Query( null, model ); //$NON-NLS-1$
myTest.getSelections().add( new Selection( null, d1, null ) );
myTest.getSelections().add( new Selection( null, ratio, null ) );
MappedQuery query = new SqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
TestHelper.assertEqualsIgnoreWhitespaces(
"SELECT bt1.d AS COL0 , SUM(bt1.a) / SUM(bt1.b) AS COL1 FROM t1 bt1 GROUP BY bt1.d", //$NON-NLS-1$
query.getQuery() );
}
