@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);
}
@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$
}
@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) {
}
}