Examples with QueryNode org.teiid.query.mapping.relational.QueryNode used on opensource projects

Example 36 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class RealMetadataFactory method exampleBitwise.

public static TransformationMetadata exampleBitwise() {
    MetadataStore store = new MetadataStore();
    // $NON-NLS-1$
    Schema phys = createPhysicalModel("phys", store);
    // $NON-NLS-1$
    Table t = createPhysicalGroup("t", phys);
    createElements(t, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "ID", "Name", "source_bits" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$
    Schema virt = createVirtualModel("virt", store);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    ColumnSet<Procedure> rs = createResultSet("rs", new String[] { "ID", "Name", "source_bits" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    QueryNode qn = new QueryNode(// $NON-NLS-1$
    "CREATE VIRTUAL PROCEDURE " + // $NON-NLS-1$
    "BEGIN " + // $NON-NLS-1$
    "        DECLARE integer VARIABLES.BITS;" + // $NON-NLS-1$
    "        create local temporary table #temp (id integer, name string, bits integer);" + // $NON-NLS-1$
    "        LOOP ON (SELECT DISTINCT phys.t.ID, phys.t.Name FROM phys.t) AS idCursor" + // $NON-NLS-1$
    "        BEGIN" + // $NON-NLS-1$
    "                VARIABLES.BITS = 0;" + // $NON-NLS-1$
    "                LOOP ON (SELECT phys.t.source_bits FROM phys.t WHERE phys.t.ID = idCursor.id) AS bitsCursor" + // $NON-NLS-1$
    "                BEGIN" + // $NON-NLS-1$
    "                        VARIABLES.BITS = bitor(VARIABLES.BITS, bitsCursor.source_bits);" + // $NON-NLS-1$
    "                END" + // $NON-NLS-1$
    "                SELECT idCursor.id, idCursor.name, VARIABLES.BITS INTO #temp;" + // $NON-NLS-1$
    "        END" + // $NON-NLS-1$
    "        SELECT ID, Name, #temp.BITS AS source_bits FROM #temp;" + // $NON-NLS-1$
    "END");
    // $NON-NLS-1$
    Procedure proc = createVirtualProcedure("agg", virt, null, qn);
    proc.setResultSet(rs);
    return createTransformationMetadata(store, "bitwise");
}

Example 37 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class RealMetadataFactory method exampleUpdateProc.

public static TransformationMetadata exampleUpdateProc(Table.TriggerEvent event, String procedure) {
    MetadataStore metadataStore = new MetadataStore();
    // Create models
    // $NON-NLS-1$
    Schema pm1 = createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$
    Schema pm2 = createPhysicalModel("pm2", metadataStore);
    // $NON-NLS-1$
    Schema vm1 = createVirtualModel("vm1", metadataStore);
    // Create physical groups
    // $NON-NLS-1$
    Table pm1g1 = createPhysicalGroup("g1", pm1);
    // $NON-NLS-1$
    Table pm1g2 = createPhysicalGroup("g2", pm1);
    // $NON-NLS-1$
    Table pm2g1 = createPhysicalGroup("g1", pm2);
    // $NON-NLS-1$
    Table pm2g2 = createPhysicalGroup("g2", pm2);
    // Create physical group elements
    createElements(pm1g1, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    createElements(pm1g2, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    createElements(pm2g1, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    createElements(pm2g2, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    // Create virtual groups
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vm1g1n1 = new QueryNode("SELECT * FROM pm1.g1");
    // $NON-NLS-1$
    Table vm1g1 = createUpdatableVirtualGroup("g1", vm1, vm1g1n1);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vm1g2n1 = new QueryNode("SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3 FROM pm1.g2");
    // $NON-NLS-1$
    Table vm1g2 = createUpdatableVirtualGroup("g2", vm1, vm1g2n1);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vm1g3n1 = new QueryNode("SELECT CONCAT(e1, 'm') as x, (e2 +1) as y, 1 as e3, e4*50 as e4 FROM pm1.g1");
    // $NON-NLS-1$
    Table vm1g3 = createUpdatableVirtualGroup("g3", vm1, vm1g3n1);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vm1g4n1 = new QueryNode("SELECT * FROM pm1.g1");
    // $NON-NLS-1$
    Table vm1g4 = createUpdatableVirtualGroup("g4", vm1, vm1g4n1);
    // Create virtual elements
    createElementsWithDefaults(vm1g1, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE }, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "xyz", "123", "true", "123.456" });
    createElementsWithDefaults(vm1g2, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "e1", "e2", "e3" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN }, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "abc", "456", "false" });
    createElementsWithDefaults(vm1g3, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "x", "y", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.DOUBLE }, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "mno", "789", "true", "789.012" });
    createElementsWithDefaults(vm1g4, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE }, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "xyz", "123", "true", "123.456" });
    setInsteadOfTriggerDefinition(vm1g1, event, procedure);
    setInsteadOfTriggerDefinition(vm1g2, event, procedure);
    setInsteadOfTriggerDefinition(vm1g3, event, procedure);
    setInsteadOfTriggerDefinition(vm1g4, event, procedure);
    // Create the facade from the store
    return createTransformationMetadata(metadataStore, "proc");
}

Example 38 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class RealMetadataFactory method exampleMaterializedView.

/**
 * Metadata for Materialized Views
 * @return
 * @since 4.2
 */
public static TransformationMetadata exampleMaterializedView() {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema virtModel = createVirtualModel("MatView", metadataStore);
    // $NON-NLS-1$
    Schema physModel = createPhysicalModel("MatTable", metadataStore);
    // $NON-NLS-1$
    Schema physModel_virtSrc = createPhysicalModel("MatSrc", metadataStore);
    // $NON-NLS-1$
    Table physTable = createPhysicalGroup("info", physModel);
    createElements(physTable, // $NON-NLS-1$
    new String[] { "e1", "e2", "e3", "value" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table physGroup = createPhysicalGroup("MatTable", physModel);
    createElements(physGroup, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table physGroupStage = createPhysicalGroup("MatStage", physModel);
    createElements(physGroupStage, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table physGroup1 = createPhysicalGroup("MatTable1", physModel);
    createElements(physGroup1, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table physGroupStage1 = createPhysicalGroup("MatStage1", physModel);
    createElements(physGroupStage1, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table physGroup_virtSrc = createPhysicalGroup("MatSrc", physModel_virtSrc);
    createElements(physGroup_virtSrc, // $NON-NLS-1$
    new String[] { "x" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table status = createPhysicalGroup("Status", physModel_virtSrc);
    createElements(status, // $NON-NLS-1$
    new String[] { "VDBName", "VDBVersion", "SchemaName", "Name", "TargetSchemaName", "TargetName", "Valid", "LoadState", "Cardinality", "OnErrorAction", "Updated" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.TIMESTAMP });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode virtTrans = new QueryNode("SELECT x as e1 FROM MatSrc.MatSrc");
    // $NON-NLS-1$
    Table virtGroup = createVirtualGroup("MatView", virtModel, virtTrans);
    createElements(virtGroup, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    virtGroup.setMaterialized(true);
    virtGroup.setMaterializedTable(physGroup);
    virtGroup.setMaterializedStageTable(physGroupStage);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode virtTransManaged = new QueryNode("SELECT x as e1 FROM MatSrc.MatSrc");
    // $NON-NLS-1$
    Table virtGroupManaged = createVirtualGroup("ManagedMatView", virtModel, virtTransManaged);
    createElements(virtGroupManaged, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    virtGroupManaged.setMaterialized(true);
    virtGroupManaged.setMaterializedTable(physGroup);
    virtGroupManaged.setMaterializedStageTable(physGroupStage);
    virtGroupManaged.setProperty(MaterializationMetadataRepository.ALLOW_MATVIEW_MANAGEMENT, "true");
    virtGroupManaged.setProperty(MaterializationMetadataRepository.MATVIEW_STATUS_TABLE, "MatSrc.Status");
    virtGroupManaged.setProperty(MaterializationMetadataRepository.MATVIEW_SHARE_SCOPE, "FULL");
    virtGroupManaged.setProperty(MaterializationMetadataRepository.MATVIEW_OWNER_VDB_NAME, "X");
    virtGroupManaged.setProperty(MaterializationMetadataRepository.MATVIEW_OWNER_VDB_VERSION, "1");
    // add one virtual group that uses the materialized group in transformation with NOCACHE option
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans = new QueryNode("SELECT e1 FROM MatView.MatView option NOCACHE");
    // $NON-NLS-1$
    Table vGroup = createVirtualGroup("VGroup", virtModel, vTrans);
    createElements(vGroup, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode virtTrans1 = new QueryNode("SELECT e1 FROM MatView.MatView where e1 = 1");
    // $NON-NLS-1$
    Table virtGroup1 = createVirtualGroup("MatView1", virtModel, virtTrans1);
    createElements(virtGroup1, // $NON-NLS-1$
    new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    virtGroup1.setMaterializedTable(physGroup1);
    virtGroup1.setMaterializedStageTable(physGroupStage1);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans2 = new QueryNode("SELECT x FROM matsrc");
    // $NON-NLS-1$
    Table vGroup2 = createVirtualGroup("VGroup2", virtModel, vTrans2);
    vGroup2.setMaterialized(true);
    createElements(vGroup2, // $NON-NLS-1$
    new String[] { "x" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans2a = new QueryNode("SELECT x FROM matsrc");
    // $NON-NLS-1$
    Table vGroup2a = createVirtualGroup("VGroup2a", virtModel, vTrans2a);
    KeyRecord fbi = new KeyRecord(KeyRecord.Type.Index);
    Column c = new Column();
    c.setParent(fbi);
    c.setName("upper(x)");
    c.setNameInSource("upper(x)");
    fbi.addColumn(c);
    vGroup2a.getFunctionBasedIndexes().add(fbi);
    vGroup2.setMaterialized(true);
    createElements(vGroup2a, // $NON-NLS-1$
    new String[] { "x" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // covering index
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans3 = new QueryNode("SELECT x, 'z' || substring(x, 2) as y FROM matsrc");
    // $NON-NLS-1$
    Table vGroup3 = createVirtualGroup("VGroup3", virtModel, vTrans3);
    vGroup3.setMaterialized(true);
    List<Column> vElements3 = createElements(vGroup3, // $NON-NLS-1$
    new String[] { "x", "y" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    createKey(KeyRecord.Type.Primary, "pk", vGroup3, vElements3.subList(0, 1));
    createKey(KeyRecord.Type.Index, "idx", vGroup3, vElements3.subList(1, 2));
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans4 = new QueryNode("/*+ cache(ttl:10000) */ SELECT x FROM matsrc");
    // $NON-NLS-1$
    Table vGroup4 = createVirtualGroup("VGroup4", virtModel, vTrans4);
    // $NON-NLS-1$
    vGroup4.setProperty(MaterializationMetadataRepository.MATVIEW_TTL, "100");
    vGroup4.setMaterialized(true);
    createElements(vGroup4, // $NON-NLS-1$
    new String[] { "x" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // non-covering index
    QueryNode vTrans5 = new QueryNode("SELECT x, 'z' || substring(x, 2) as y, 1 as z FROM matsrc " + "union all SELECT ifnull(x, ' ') || 'b', 'x' || substring(x, 2) as y, 1 as z FROM matsrc " + "union all SELECT ifnull(x, ' ') || 'c', 'y' || substring(x, 2) as y, 1 as z FROM matsrc " + "union all SELECT ifnull(x, ' ') || 'd', 'w' || substring(x, 2) as y, 1 as z FROM matsrc");
    // $NON-NLS-1$
    Table vGroup5 = createVirtualGroup("VGroup5", virtModel, vTrans5);
    vGroup5.setMaterialized(true);
    List<Column> vElements5 = createElements(vGroup5, // $NON-NLS-1$
    new String[] { "x", "y", "z" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    createKey(KeyRecord.Type.Primary, "pk", vGroup5, vElements5.subList(0, 1));
    createKey(KeyRecord.Type.Index, "idx", vGroup5, vElements5.subList(1, 2));
    // no pk
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans6 = new QueryNode("SELECT x, 'z' || substring(x, 2) as y FROM matsrc");
    // $NON-NLS-1$
    Table vGroup6 = createVirtualGroup("VGroup6", virtModel, vTrans6);
    vGroup6.setMaterialized(true);
    List<Column> vElements6 = createElements(vGroup6, // $NON-NLS-1$
    new String[] { "x", "y" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    createKey(KeyRecord.Type.Index, "idx", vGroup6, vElements6.subList(1, 2));
    // non-covering index
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vTrans7 = new QueryNode("SELECT '1', 'z' || substring(x, 2) as y, 1 as z FROM matsrc");
    // $NON-NLS-1$
    Table vGroup7 = createVirtualGroup("VGroup7", virtModel, vTrans7);
    vGroup7.setMaterialized(true);
    List<Column> vElements7 = createElements(vGroup7, // $NON-NLS-1$
    new String[] { "x", "y", "z" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    createKey(KeyRecord.Type.Primary, "pk", vGroup7, vElements7.subList(1, 2));
    // $NON-NLS-1$
    Schema sp = createVirtualModel("sp", metadataStore);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> rs = createResultSet("sp1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    ProcedureParameter param = createParameter("param1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    param.setNullType(NullType.Nullable);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode sp1qn = new QueryNode("/*+ cache */ CREATE VIRTUAL PROCEDURE BEGIN SELECT x as StringKey from matsrc where x = param1; END");
    // $NON-NLS-1$
    Procedure vsp5 = createVirtualProcedure("sp1", sp, Arrays.asList(param), sp1qn);
    vsp5.setResultSet(rs);
    return createTransformationMetadata(metadataStore, "");
}

Example 39 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class RealMetadataFactory method exampleBQTStore.

public static MetadataStore exampleBQTStore() {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema bqt1 = createPhysicalModel("BQT1", metadataStore);
    // $NON-NLS-1$
    Schema bqt2 = createPhysicalModel("BQT2", metadataStore);
    // $NON-NLS-1$
    Schema bqt3 = createPhysicalModel("BQT3", metadataStore);
    // $NON-NLS-1$
    Schema lob = createPhysicalModel("LOB", metadataStore);
    // $NON-NLS-1$
    Schema vqt = createVirtualModel("VQT", metadataStore);
    // $NON-NLS-1$
    Schema bvqt = createVirtualModel("BQT_V", metadataStore);
    // $NON-NLS-1$
    Schema bvqt2 = createVirtualModel("BQT2_V", metadataStore);
    Schema gis = createPhysicalModel("GIS", metadataStore);
    Table colaMarkets = createPhysicalGroup("COLA_MARKETS", gis);
    createElement("MKT_ID", colaMarkets, "integer");
    createElement("NAME", colaMarkets, "string");
    createElement("SHAPE", colaMarkets, "geometry");
    // Create physical groups
    // $NON-NLS-1$
    Table bqt1SmallA = createPhysicalGroup("SmallA", bqt1);
    // $NON-NLS-1$
    Table bqt1SmallB = createPhysicalGroup("SmallB", bqt1);
    // $NON-NLS-1$
    Table bqt1MediumA = createPhysicalGroup("MediumA", bqt1);
    // $NON-NLS-1$
    Table bqt1MediumB = createPhysicalGroup("MediumB", bqt1);
    // $NON-NLS-1$
    Table bqt2SmallA = createPhysicalGroup("SmallA", bqt2);
    // $NON-NLS-1$
    Table bqt2SmallB = createPhysicalGroup("SmallB", bqt2);
    // $NON-NLS-1$
    Table bqt2MediumA = createPhysicalGroup("MediumA", bqt2);
    // $NON-NLS-1$
    Table bqt2MediumB = createPhysicalGroup("MediumB", bqt2);
    // $NON-NLS-1$
    Table bqt3SmallA = createPhysicalGroup("SmallA", bqt3);
    // $NON-NLS-1$
    Table bqt3SmallB = createPhysicalGroup("SmallB", bqt3);
    // $NON-NLS-1$
    Table bqt3MediumA = createPhysicalGroup("MediumA", bqt3);
    // $NON-NLS-1$
    Table bqt3MediumB = createPhysicalGroup("MediumB", bqt3);
    // $NON-NLS-1$
    Table lobTable = createPhysicalGroup("LobTbl", lob);
    // $NON-NLS-1$
    Table library = createPhysicalGroup("LOB_TESTING_ONE", lob);
    // $NON-NLS-1$
    Table bin = createPhysicalGroup("binary_test", lob);
    // add direct query procedure
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    ColumnSet<Procedure> nativeProcResults = createResultSet("bqt1.nativers", new String[] { "tuple" }, new String[] { DataTypeManager.DefaultDataTypes.OBJECT });
    // $NON-NLS-1$
    ProcedureParameter nativeparam = createParameter("param", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    // $NON-NLS-1$
    ProcedureParameter vardic = createParameter("varag", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.OBJECT);
    vardic.setVarArg(true);
    // $NON-NLS-1$ //$NON-NLS-2$
    Procedure nativeProc = createStoredProcedure("native", bqt1, Arrays.asList(nativeparam, vardic));
    nativeProc.setResultSet(nativeProcResults);
    createElements(// $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    library, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "CLOB_COLUMN", "BLOB_COLUMN", "KEY_EMULATOR" }, new String[] { DataTypeManager.DefaultDataTypes.CLOB, DataTypeManager.DefaultDataTypes.BLOB, DataTypeManager.DefaultDataTypes.INTEGER });
    createElements(// $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    bin, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "BIN_COL" }, new String[] { DataTypeManager.DefaultDataTypes.VARBINARY });
    // Create virtual groups
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn1 = new QueryNode("SELECT * FROM BQT1.SmallA");
    // $NON-NLS-1$
    Table vqtg1 = createUpdatableVirtualGroup("SmallA", vqt, vqtn1);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2 = new QueryNode("SELECT Concat(stringKey, stringNum) as a12345 FROM BQT1.SmallA");
    // $NON-NLS-1$
    Table vqtg2 = createUpdatableVirtualGroup("SmallB", vqt, vqtn2);
    // Case 2589
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589 = new QueryNode("SELECT * FROM BQT1.SmallA WHERE StringNum = '10'");
    // $NON-NLS-1$
    Table vqtg2589 = createVirtualGroup("SmallA_2589", vqt, vqtn2589);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589a = new QueryNode("SELECT BQT1.SmallA.* FROM BQT1.SmallA INNER JOIN BQT1.SmallB ON SmallA.IntKey = SmallB.IntKey WHERE SmallA.StringNum = '10'");
    // $NON-NLS-1$
    Table vqtg2589a = createVirtualGroup("SmallA_2589a", vqt, vqtn2589a);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589b = new QueryNode("SELECT BQT1.SmallA.* FROM BQT1.SmallA INNER JOIN BQT1.SmallB ON SmallA.StringKey = SmallB.StringKey WHERE SmallA.StringNum = '10'");
    // $NON-NLS-1$
    Table vqtg2589b = createVirtualGroup("SmallA_2589b", vqt, vqtn2589b);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589c = new QueryNode("SELECT BQT1.SmallA.* FROM BQT1.SmallA INNER JOIN BQT1.SmallB ON SmallA.StringKey = SmallB.StringKey WHERE concat(SmallA.StringNum, SmallB.StringNum) = '1010'");
    // $NON-NLS-1$
    Table vqtg2589c = createVirtualGroup("SmallA_2589c", vqt, vqtn2589c);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589d = new QueryNode("SELECT BQT1.SmallA.* FROM BQT1.SmallA INNER JOIN BQT1.SmallB ON SmallA.StringKey = SmallB.StringKey WHERE SmallA.StringNum = '10' AND SmallA.IntNum = 10");
    // $NON-NLS-1$
    Table vqtg2589d = createVirtualGroup("SmallA_2589d", vqt, vqtn2589d);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589f = new QueryNode("SELECT * FROM VQT.SmallA_2589");
    // $NON-NLS-1$
    Table vqtg2589f = createVirtualGroup("SmallA_2589f", vqt, vqtn2589f);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589g = new QueryNode("SELECT * FROM SmallA_2589b");
    // $NON-NLS-1$
    Table vqtg2589g = createVirtualGroup("SmallA_2589g", vqt, vqtn2589g);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589h = new QueryNode("SELECT VQT.SmallA_2589.* FROM VQT.SmallA_2589 INNER JOIN BQT1.SmallB ON VQT.SmallA_2589.StringKey = SmallB.StringKey");
    // $NON-NLS-1$
    Table vqtg2589h = createVirtualGroup("SmallA_2589h", vqt, vqtn2589h);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn2589i = new QueryNode("SELECT BQT1.SmallA.* FROM BQT1.SmallA INNER JOIN BQT1.SmallB ON SmallA.StringKey = SmallB.StringKey WHERE SmallA.StringNum = '10' AND SmallB.StringNum = '10'");
    // $NON-NLS-1$
    Table vqtg2589i = createVirtualGroup("SmallA_2589i", vqt, vqtn2589i);
    // defect 15355
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn15355 = new QueryNode("SELECT convert(IntKey, string) as StringKey, BigIntegerValue FROM BQT1.SmallA UNION SELECT StringKey, (SELECT BigIntegerValue FROM BQT3.SmallA WHERE BQT3.SmallA.BigIntegerValue = BQT2.SmallA.StringNum) FROM BQT2.SmallA");
    // $NON-NLS-1$
    Table vqtg15355 = createVirtualGroup("Defect15355", vqt, vqtn15355);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn15355a = new QueryNode("SELECT StringKey, StringNum, BigIntegerValue FROM BQT1.SmallA UNION SELECT StringKey, StringNum, (SELECT BigIntegerValue FROM BQT3.SmallA WHERE BQT3.SmallA.BigIntegerValue = BQT2.SmallA.StringNum) FROM BQT2.SmallA");
    // $NON-NLS-1$
    Table vqtg15355a = createVirtualGroup("Defect15355a", vqt, vqtn15355a);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vqtn15355b = new QueryNode("SELECT convert(IntKey, string) as IntKey, BigIntegerValue FROM BQT1.SmallA UNION SELECT StringKey, (SELECT BigIntegerValue FROM BQT3.SmallA WHERE BQT3.SmallA.BigIntegerValue = BQT2.SmallA.StringNum) FROM BQT2.SmallA");
    // $NON-NLS-1$
    Table vqtg15355b = createVirtualGroup("Defect15355b", vqt, vqtn15355b);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode bvqtn1 = new QueryNode("SELECT a.* FROM BQT1.SMALLA AS a WHERE a.INTNUM = (SELECT MIN(b.INTNUM) FROM BQT1.SMALLA AS b WHERE b.INTKEY = a.IntKey ) OPTION MAKEDEP a");
    // $NON-NLS-1$
    Table bvqtg1 = createUpdatableVirtualGroup("BQT_V", bvqt, bvqtn1);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode bvqt2n1 = new QueryNode("SELECT BQT2.SmallA.* FROM BQT2.SmallA, BQT_V.BQT_V WHERE BQT2.SmallA.IntKey = BQT_V.BQT_V.IntKey");
    // $NON-NLS-1$
    Table bvqt2g1 = createUpdatableVirtualGroup("BQT2_V", bvqt2, bvqt2n1);
    // Create physical elements
    String[] elemNames = new String[] { // $NON-NLS-1$ //$NON-NLS-2$
    "IntKey", // $NON-NLS-1$ //$NON-NLS-2$
    "StringKey", // $NON-NLS-1$ //$NON-NLS-2$
    "IntNum", // $NON-NLS-1$ //$NON-NLS-2$
    "StringNum", // $NON-NLS-1$ //$NON-NLS-2$
    "FloatNum", // $NON-NLS-1$ //$NON-NLS-2$
    "LongNum", // $NON-NLS-1$ //$NON-NLS-2$
    "DoubleNum", // $NON-NLS-1$ //$NON-NLS-2$
    "ByteNum", // $NON-NLS-1$ //$NON-NLS-2$
    "DateValue", // $NON-NLS-1$ //$NON-NLS-2$
    "TimeValue", // $NON-NLS-1$ //$NON-NLS-2$
    "TimestampValue", // $NON-NLS-1$ //$NON-NLS-2$
    "BooleanValue", // $NON-NLS-1$ //$NON-NLS-2$
    "CharValue", // $NON-NLS-1$ //$NON-NLS-2$
    "ShortValue", // $NON-NLS-1$ //$NON-NLS-2$
    "BigIntegerValue", // $NON-NLS-1$ //$NON-NLS-2$
    "BigDecimalValue", // $NON-NLS-1$
    "ObjectValue" };
    String[] elemTypes = new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.FLOAT, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.DOUBLE, DataTypeManager.DefaultDataTypes.BYTE, DataTypeManager.DefaultDataTypes.DATE, DataTypeManager.DefaultDataTypes.TIME, DataTypeManager.DefaultDataTypes.TIMESTAMP, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.CHAR, DataTypeManager.DefaultDataTypes.SHORT, DataTypeManager.DefaultDataTypes.BIG_INTEGER, DataTypeManager.DefaultDataTypes.BIG_DECIMAL, DataTypeManager.DefaultDataTypes.OBJECT };
    List<Column> bqt1SmallAe = createElements(bqt1SmallA, elemNames, elemTypes);
    // $NON-NLS-1$
    bqt1SmallAe.get(1).setNativeType("char");
    createElements(bqt1SmallB, elemNames, elemTypes);
    createElements(bqt1MediumA, elemNames, elemTypes);
    createElements(bqt1MediumB, elemNames, elemTypes);
    createElements(bqt2SmallA, elemNames, elemTypes);
    createElements(bqt2SmallB, elemNames, elemTypes);
    createElements(bqt2MediumA, elemNames, elemTypes);
    createElements(bqt2MediumB, elemNames, elemTypes);
    createElements(bqt3SmallA, elemNames, elemTypes);
    createElements(bqt3SmallB, elemNames, elemTypes);
    createElements(bqt3MediumA, elemNames, elemTypes);
    createElements(bqt3MediumB, elemNames, elemTypes);
    // $NON-NLS-1$
    createElements(lobTable, new String[] { "ClobValue" }, new String[] { DataTypeManager.DefaultDataTypes.CLOB });
    // Create virtual elements
    createElements(vqtg1, elemNames, elemTypes);
    // $NON-NLS-1$
    createElements(vqtg2, new String[] { "a12345" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    createElements(vqtg15355, new String[] { "StringKey", "BigIntegerValue" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.BIG_INTEGER });
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    createElements(vqtg15355a, new String[] { "StringKey", "StringNum", "BigIntegerValue" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.BIG_INTEGER });
    // $NON-NLS-1$ //$NON-NLS-2$
    createElements(vqtg15355b, new String[] { "IntKey", "BigIntegerValue" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.BIG_INTEGER });
    createElements(vqtg2589, elemNames, elemTypes);
    createElements(vqtg2589a, elemNames, elemTypes);
    createElements(vqtg2589b, elemNames, elemTypes);
    createElements(vqtg2589c, elemNames, elemTypes);
    createElements(vqtg2589d, elemNames, elemTypes);
    createElements(vqtg2589f, elemNames, elemTypes);
    createElements(vqtg2589g, elemNames, elemTypes);
    createElements(vqtg2589h, elemNames, elemTypes);
    createElements(vqtg2589i, elemNames, elemTypes);
    createElements(bvqtg1, elemNames, elemTypes);
    createElements(bvqt2g1, elemNames, elemTypes);
    // $NON-NLS-1$
    ProcedureParameter rsp1 = createParameter("ret", ParameterInfo.RETURN_VALUE, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$
    ProcedureParameter rsp2 = createParameter("inkey", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    createVirtualProcedure("v_spTest9", bvqt, Arrays.asList(rsp1, rsp2), new QueryNode("ret = call pm4.spTest9(inkey);"));
    // Add stored procedure
    // $NON-NLS-1$
    Schema pm1 = createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter rs1p1 = createParameter("intkey", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    ColumnSet<Procedure> rs1 = createResultSet("rs1", new String[] { "IntKey", "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    Procedure spTest5 = createStoredProcedure("spTest5", pm1, Arrays.asList(rs1p1));
    spTest5.setResultSet(rs1);
    // $NON-NLS-1$
    Schema pm2 = createPhysicalModel("pm2", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter rs2p1 = createParameter("inkey", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$
    ProcedureParameter rs2p2 = createParameter("outkey", ParameterInfo.OUT, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    ColumnSet<Procedure> rs2 = createResultSet("rs2", new String[] { "IntKey", "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    Procedure spTest8 = createStoredProcedure("spTest8", pm2, Arrays.asList(rs2p1, rs2p2));
    spTest8.setResultSet(rs2);
    // $NON-NLS-1$
    ProcedureParameter rs2p2a = createParameter("outkey", ParameterInfo.OUT, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    ColumnSet<Procedure> rs2a = createResultSet("rs2", new String[] { "IntKey", "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    Procedure spTest8a = createStoredProcedure("spTest8a", pm2, Arrays.asList(rs2p2a));
    spTest8a.setResultSet(rs2a);
    // $NON-NLS-1$
    Schema pm4 = createPhysicalModel("pm4", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter rs4p1 = createParameter("ret", ParameterInfo.RETURN_VALUE, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$
    ProcedureParameter rs4p2 = createParameter("inkey", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    createStoredProcedure("spTest9", pm4, Arrays.asList(rs4p1, rs4p2));
    // $NON-NLS-1$
    Schema pm3 = createPhysicalModel("pm3", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter rs3p1 = createParameter("inkey", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$
    ProcedureParameter rs3p2 = createParameter("outkey", ParameterInfo.INOUT, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    ColumnSet<Procedure> rs3 = createResultSet("rs3", new String[] { "IntKey", "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    Procedure spTest11 = createStoredProcedure("spTest11", pm3, Arrays.asList(rs3p1, rs3p2));
    spTest11.setResultSet(rs3);
    // add virtual stored procedures
    // $NON-NLS-1$
    Schema mmspTest1 = createVirtualModel("mmspTest1", metadataStore);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs1 = createResultSet("mmspTest1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; LOOP ON (SELECT intkey FROM bqt1.smallA) AS intKeyCursor BEGIN x= intKeyCursor.intkey - 1; END SELECT stringkey FROM bqt1.smalla where intkey=x; END");
    // $NON-NLS-1$
    Procedure vsp1 = createVirtualProcedure("MMSP1", mmspTest1, null, vspqn1);
    vsp1.setResultSet(vsprs1);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs2 = createResultSet("mmspTest1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn2 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; LOOP ON (SELECT intkey FROM bqt1.smallA) AS intKeyCursor1 BEGIN LOOP ON (SELECT intkey FROM bqt1.smallB) AS intKeyCursor2 BEGIN x= intKeyCursor1.intkey - intKeyCursor2.intkey; END END SELECT stringkey FROM bqt1.smalla where intkey=x; END");
    // $NON-NLS-1$
    Procedure vsp2 = createVirtualProcedure("MMSP2", mmspTest1, null, vspqn2);
    vsp2.setResultSet(vsprs2);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs3 = createResultSet("mmspTest1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn3 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; LOOP ON (SELECT intkey FROM bqt1.smallA) AS intKeyCursor BEGIN x= intKeyCursor.intkey - 1; if(x = 25) BEGIN BREAK; END ELSE BEGIN CONTINUE; END END SELECT stringkey FROM bqt1.smalla where intkey=x; END");
    // $NON-NLS-1$
    Procedure vsp3 = createVirtualProcedure("MMSP3", mmspTest1, null, vspqn3);
    vsp3.setResultSet(vsprs3);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs4 = createResultSet("mmspTest1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn4 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; x=0; WHILE(x < 50) BEGIN x= x + 1; if(x = 25) BEGIN BREAK; END ELSE BEGIN CONTINUE; END END SELECT stringkey FROM bqt1.smalla where intkey=x; END");
    // $NON-NLS-1$
    Procedure vsp4 = createVirtualProcedure("MMSP4", mmspTest1, null, vspqn4);
    vsp4.setResultSet(vsprs4);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs5 = createResultSet("mmspTest1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    ProcedureParameter vsp5p1 = createParameter("param1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn5 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT 0; END");
    // $NON-NLS-1$
    Procedure vsp5 = createVirtualProcedure("MMSP5", mmspTest1, Arrays.asList(vsp5p1), vspqn5);
    vsp5.setResultSet(vsprs5);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs6 = createResultSet("mmspTest1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    ProcedureParameter vsp6p1 = createParameter("p1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn6 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT p1 as StringKey; END");
    // $NON-NLS-1$
    Procedure vsp6 = createVirtualProcedure("MMSP6", mmspTest1, Arrays.asList(vsp6p1), vspqn6);
    vsp6.setResultSet(vsprs6);
    createStoredProcedure("spRetOut", pm4, Arrays.asList(createParameter("ret", ParameterInfo.RETURN_VALUE, DataTypeManager.DefaultDataTypes.INTEGER), // $NON-NLS-1$ //$NON-NLS-2$
    createParameter("x", ParameterInfo.OUT, DataTypeManager.DefaultDataTypes.INTEGER)));
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs7 = createResultSet("TEIIDSP7.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    ProcedureParameter vsp7p1 = createParameter("p1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn7 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN declare integer x; x = exec spTest9(p1); declare integer y; exec spTest11(inkey=>x, outkey=>y) without return; select convert(x, string) || y; END");
    // $NON-NLS-1$
    Procedure vsp7 = createVirtualProcedure("TEIIDSP7", mmspTest1, Arrays.asList(vsp7p1), vspqn7);
    vsp7.setResultSet(vsprs7);
    // $NON-NLS-1$
    ProcedureParameter vsp8p1 = createParameter("r", ParameterInfo.RETURN_VALUE, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$
    ProcedureParameter vsp8p2 = createParameter("p1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn8 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN r = p1; END");
    // $NON-NLS-1$
    createVirtualProcedure("TEIIDSP8", mmspTest1, Arrays.asList(vsp8p1, vsp8p2), vspqn8);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> vsprs9 = createResultSet("TEIIDSP9.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    ProcedureParameter vsp9p1 = createParameter("r", ParameterInfo.RETURN_VALUE, DataTypeManager.DefaultDataTypes.INTEGER);
    vsp9p1.setNullType(NullType.No_Nulls);
    // $NON-NLS-1$
    ProcedureParameter vsp9p2 = createParameter("p1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$
    ProcedureParameter vsp9p3 = createParameter("p2", ParameterInfo.OUT, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vspqn9 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN if (p1 = 1) begin\n r = 1; end\n p2 = 10; select 'hello'; END");
    // $NON-NLS-1$
    Procedure vsp9 = createVirtualProcedure("TEIIDSP9", mmspTest1, Arrays.asList(vsp9p1, vsp9p2, vsp9p3), vspqn9);
    vsp9.setResultSet(vsprs9);
    // $NON-NLS-1$ //$NON-NLS-2$
    createStoredProcedure("sp_noreturn", pm4, Collections.EMPTY_LIST);
    // this is for the source added function
    bqt1.addFunction(new // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    FunctionMethod(// $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    "reverse", // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    "reverse", // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    "misc", // $NON-NLS-1$ //$NON-NLS-2$
    new FunctionParameter[] { new FunctionParameter("columnName", DataTypeManager.DefaultDataTypes.STRING, "") }, // $NON-NLS-1$ //$NON-NLS-2$
    new FunctionParameter("result", DataTypeManager.DefaultDataTypes.STRING, "")));
    return metadataStore;
}