コード例 #1
0
                @Override
                protected void starting(Description description) {
                  try {
                    spliceClassWatcher.setAutoCommit(true);
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) values 1,2,3,4,5,6,7,8,9,10", spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher, spliceTableWatcher));

                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) values 1,2,3,4,5,6,7,8,9,10", spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));
                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) select i from %s",
                            spliceTableWatcher2, spliceTableWatcher2));

                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (i) values 1,2,3,4,5,6,7,8,9,10", spliceTableWatcher3));

                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (r_regionkey, r_name) values "
                                + "(0, 'AFRICA'), (1, 'AMERICA'), (2, 'ASIA'), (3, 'EUROPE'), (4, 'MIDDLE EAST'), "
                                + "(5, 'AMERICA'), (6, 'AMERICA'), (7, 'AMERICA'), (8, 'AMERICA'), (9, 'AMERICA')",
                            spliceTableRegion));

                    spliceClassWatcher.executeUpdate(
                        format(
                            "insert into %s (n_nationkey, n_name, n_regionkey) values "
                                + "(0, 'ALGERIA', 0), "
                                + "(1, 'ARGENTINA', 1), "
                                + "(2, 'BRAZIL', 1), "
                                + "(4, 'EGYPT', 4), "
                                + "(5, 'ETHIOPIA', 0), "
                                + "(6, 'FRANCE', 3)",
                            spliceTableNation));

                    spliceClassWatcher.execute(
                        format(
                            "call syscs_util.COLLECT_SCHEMA_STATISTICS('%s',false)", CLASS_NAME));

                  } catch (Exception e) {
                    throw new RuntimeException(e);
                  } finally {
                    spliceClassWatcher.closeAll();
                  }
                }
コード例 #2
0
  @SuppressWarnings("unchecked")
  @Test
  public void createTableWithViewJoins() throws Exception {
    // DB-4170: create table with data didn't work with more than one join (the view defn is
    // executed)
    String nameTable = "names";
    String nameTableRef = spliceSchemaWatcher.schemaName + "." + nameTable;
    String nameTableDef = "(id int, fname varchar(10), lname varchar(10))";
    new TableDAO(methodWatcher.getOrCreateConnection())
        .drop(spliceSchemaWatcher.schemaName, nameTable);

    new TableCreator(methodWatcher.getOrCreateConnection())
        .withCreate(format("create table %s %s", nameTableRef, nameTableDef))
        .withInsert(format("insert into %s values (?,?,?)", nameTableRef))
        .withRows(
            rows(
                row(20, "Joe", "Blow"),
                row(70, "Fred", "Ziffle"),
                row(60, "Floyd", "Jones"),
                row(40, "Janice", "Jones")))
        .create();

    String empTable = "emptab";
    String empTableRef = spliceSchemaWatcher.schemaName + "." + empTable;
    String empTableDef = "(empnum int, dept int, salary int)";
    new TableDAO(methodWatcher.getOrCreateConnection())
        .drop(spliceSchemaWatcher.schemaName, empTable);

    new TableCreator(methodWatcher.getOrCreateConnection())
        .withCreate(format("create table %s %s", empTableRef, empTableDef))
        .withInsert(format("insert into %s values (?,?,?)", empTableRef))
        .withRows(rows(row(20, 1, 75000), row(70, 3, 76000), row(60, 2, 78000), row(40, 2, 52000)))
        .create();

    String ssnTable = "ssn";
    String ssnTableRef = spliceSchemaWatcher.schemaName + "." + ssnTable;
    String ssnTableDef = "(id int, ssn int)";
    new TableDAO(methodWatcher.getOrCreateConnection())
        .drop(spliceSchemaWatcher.schemaName, ssnTable);

    new TableCreator(methodWatcher.getOrCreateConnection())
        .withCreate(format("create table %s %s", ssnTableRef, ssnTableDef))
        .withInsert(format("insert into %s values (?,?)", ssnTableRef))
        .withRows(rows(row(20, 11199222), row(70, 33366777), row(60, 88844777), row(40, 22200555)))
        .create();

    String viewName = "empsal";
    String viewRef = spliceSchemaWatcher.schemaName + "." + viewName;
    String viewDef =
        format(
            "create view %s as select distinct "
                + "A.ID, A.LNAME, A.FNAME, "
                + "B.DEPT, B.SALARY, "
                + "C.SSN "
                + "FROM %s A "
                + "LEFT OUTER JOIN %s B ON A.ID = B.EMPNUM "
                + "LEFT OUTER JOIN %s C ON A.ID = C.ID ",
            viewRef, nameTableRef, empTableRef, ssnTableRef);

    methodWatcher.execute(viewDef);

    String depsalTable = "depsal";
    String depsalTableRef = spliceSchemaWatcher.schemaName + "." + depsalTable;
    String depsalTableDef =
        format(
            "create table %s as " + "select dept, salary, ssn from %s with data",
            depsalTableRef, viewRef);
    new TableDAO(methodWatcher.getOrCreateConnection())
        .drop(spliceSchemaWatcher.schemaName, depsalTable);

    methodWatcher.executeUpdate(depsalTableDef);
    String sqlText = format("select * from %s order by dept, salary", depsalTableRef);
    ResultSet rs = methodWatcher.executeQuery(sqlText);

    String expected =
        "DEPT |SALARY |   SSN   |\n"
            + "------------------------\n"
            + "  1  | 75000 |11199222 |\n"
            + "  2  | 52000 |22200555 |\n"
            + "  2  | 78000 |88844777 |\n"
            + "  3  | 76000 |33366777 |";
    assertEquals(
        "\n" + sqlText + "\n",
        expected,
        TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs));
  }