@Test
  public void testDeleteComplex() throws Exception {
    String userSql = "delete from vm1.gx where e2 < 10"; // $NON-NLS-1$
    String viewSql = "select g2.* from pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1";

    HardcodedDataManager dm = new HardcodedDataManager();
    dm.addData(
        "SELECT g_1.e2 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_1.e2 < 10)",
        new List[] {Arrays.asList(2)});
    dm.addData("DELETE FROM pm1.g2 WHERE pm1.g2.e2 = 2", new List[] {Arrays.asList(1)});

    helpTest(
        userSql,
        viewSql,
        "CREATE VIRTUAL PROCEDURE\nBEGIN ATOMIC\nDECLARE integer VARIABLES.ROWS_UPDATED = 0;\nLOOP ON (SELECT pm1.g2.e2 AS s_0 FROM pm1.g1 INNER JOIN pm1.g2 ON g1.e1 = g2.e1 WHERE pm1.g2.e2 < 10) AS X\nBEGIN\nDELETE FROM pm1.g2 WHERE pm1.g2.e2 = X.s_0;\nVARIABLES.ROWS_UPDATED = (VARIABLES.ROWS_UPDATED + 1);\nEND\nSELECT VARIABLES.ROWS_UPDATED;\nEND",
        dm);
  }
  @Test
  public void testUpdateComplex() throws Exception {
    String userSql = "update vm1.gx set e1 = e2 where e3 is null"; // $NON-NLS-1$
    String viewSql = "select g2.* from pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1";

    HardcodedDataManager dm = new HardcodedDataManager();
    dm.addData(
        "SELECT convert(g_1.e2, string), g_1.e2 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_1.e3 IS NULL)",
        new List[] {Arrays.asList("1", 1)});
    dm.addData(
        "UPDATE pm1.g2 SET e1 = convert(pm1.g2.e2, string) WHERE pm1.g2.e2 = 1",
        new List[] {Arrays.asList(1)});

    helpTest(
        userSql,
        viewSql,
        "CREATE VIRTUAL PROCEDURE\nBEGIN ATOMIC\nDECLARE integer VARIABLES.ROWS_UPDATED = 0;\nLOOP ON (SELECT convert(pm1.g2.e2, string) AS s_0, pm1.g2.e2 AS s_1 FROM pm1.g1 INNER JOIN pm1.g2 ON g1.e1 = g2.e1 WHERE pm1.g2.e3 IS NULL) AS X\nBEGIN\nUPDATE pm1.g2 SET e1 = convert(pm1.g2.e2, string) WHERE pm1.g2.e2 = X.s_1;\nVARIABLES.ROWS_UPDATED = (VARIABLES.ROWS_UPDATED + 1);\nEND\nSELECT VARIABLES.ROWS_UPDATED;\nEND",
        dm);
  }
Esempio n. 3
0
  @Test
  public void testInherentUpdateUsingTemp() throws Exception {
    TransformationMetadata metadata =
        RealMetadataFactory.fromDDL(
            "create foreign table g1 (e1 string primary key, e2 integer, e3 boolean, e4 double, FOREIGN KEY (e1) REFERENCES G2 (e1)) options (updatable true);"
                + " create foreign table g2 (e1 string primary key, e2 integer, e3 boolean, e4 double) options (updatable true);"
                + " create view v options (updatable true) as select g1.e2 from g1 inner join g2 on g1.e1 = g2.e1;",
            "x",
            "pm1");
    HardcodedDataManager hdm = new HardcodedDataManager(metadata);
    setUp(metadata, hdm);

    BufferManager bm = BufferManagerFactory.getStandaloneBufferManager();
    SessionAwareCache<CachedResults> cache =
        new SessionAwareCache<CachedResults>(
            "resultset", DefaultCacheFactory.INSTANCE, SessionAwareCache.Type.RESULTSET, 0);
    cache.setTupleBufferCache(bm);
    dataManager = new TempTableDataManager(hdm, bm, cache);

    execute(
        "create temporary table x (e1 string, e2 integer, e3 string, primary key (e1))",
        new List[] {Arrays.asList(0)}); // $NON-NLS-1$

    execute("insert into x values ('a', 1, 'b')", new List[] {Arrays.asList(1)});

    TempMetadataID id = this.tempStore.getMetadataStore().getData().get("x");

    // ensure that we're using the actual metadata
    assertNotNull(id);
    assertNotNull(this.metadata.getPrimaryKey(id));

    hdm.addData(
        "SELECT g_0.e1 FROM g1 AS g_0, g2 AS g_1 WHERE g_0.e1 = g_1.e1 AND g_0.e2 = 1",
        new List[] {Arrays.asList("a")});
    hdm.addData("DELETE FROM g1 WHERE g1.e1 = 'a'", new List[] {Arrays.asList(1)});

    execute(
        "delete from v where e2 = (select max(e2) from x as z where e3 = z.e3)",
        new List[] {Arrays.asList(1)},
        TestOptimizer.getGenericFinder()); // $NON-NLS-1$
  }
Esempio n. 4
0
  @Test
  public void testForeignTemp() throws Exception {
    HardcodedDataManager hdm = new HardcodedDataManager(metadata);
    BufferManager bm = BufferManagerFactory.getStandaloneBufferManager();
    SessionAwareCache<CachedResults> cache =
        new SessionAwareCache<CachedResults>(
            "resultset", DefaultCacheFactory.INSTANCE, SessionAwareCache.Type.RESULTSET, 0);
    cache.setTupleBufferCache(bm);
    dataManager = new TempTableDataManager(hdm, bm, cache);

    execute(
        "create foreign temporary table x (e1 string options (nameinsource 'a'), e2 integer, e3 string, primary key (e1)) options (cardinality 1000, updatable true) on pm1",
        new List[] {Arrays.asList(0)}); // $NON-NLS-1$

    TempMetadataID id = this.tempStore.getMetadataStore().getData().get("x");

    // ensure that we're using the actual metadata
    assertNotNull(id);
    assertNotNull(this.metadata.getPrimaryKey(id));
    assertEquals(1000, this.metadata.getCardinality(id), 0);
    assertEquals("pm1", this.metadata.getName(this.metadata.getModelID(id)));

    hdm.addData("SELECT x.a, x.e2, x.e3 FROM x", new List[] {Arrays.asList(1, 2, "3")});
    execute("select * from x", new List[] {Arrays.asList(1, 2, "3")}); // $NON-NLS-1$

    hdm.addData(
        "SELECT g_0.e2 AS c_0, g_0.e3 AS c_1, g_0.a AS c_2 FROM x AS g_0 ORDER BY c_1, c_0",
        new List[] {Arrays.asList(2, "3", "1")});
    hdm.addData("SELECT g_0.e3, g_0.e2 FROM x AS g_0", new List[] {Arrays.asList("3", 2)});
    hdm.addData("DELETE FROM x WHERE x.a = '1'", new List[] {Arrays.asList(1)});

    // ensure compensation behaves as if physical - not temp
    execute(
        "delete from x where e2 = (select max(e2) from x as z where e3 = x.e3)",
        new List[] {Arrays.asList(1)},
        TestOptimizer.getGenericFinder()); // $NON-NLS-1$

    hdm.addData(
        "SELECT g_0.e1 FROM g1 AS g_0, x AS g_1 WHERE g_1.a = g_0.e1",
        new List[] {Arrays.asList(1)});

    // ensure pushdown support
    execute(
        "select g1.e1 from pm1.g1 g1, x where x.e1 = g1.e1",
        new List[] {Arrays.asList(1)},
        TestOptimizer.getGenericFinder()); // $NON-NLS-1$

    try {
      execute(
          "create local temporary table x (e1 string)",
          new List[] {Arrays.asList(0)}); // $NON-NLS-1$
      fail();
    } catch (QueryResolverException e) {

    }

    // ensure that drop works
    execute("drop table x", new List[] {Arrays.asList(0)});

    try {
      execute("drop table x", new List[] {Arrays.asList(0)});
      fail();
    } catch (QueryResolverException e) {

    }
  }