Ejemplo n.º 1
0
  @Test
  public void testMultipleCommands2() throws Exception {
    String ddl =
        "             CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), from_user varchar(25), to_user varchar(25), \n"
            + "                 profile_image_url varchar(25), source varchar(25), text varchar(140)) AS \n"
            + "                select tweet.* from \n"
            + "	                (call twitter.invokeHTTP(action => 'GET', endpoint =>querystring('',query as \"q\"))) w, \n"
            + "	                XMLTABLE('results' passing JSONTOXML('myxml', w.result) columns \n"
            + "	                created_on string PATH 'created_at', \n"
            + "	                from_user string PATH 'from_user',\n"
            + "	                to_user string PATH 'to_user',	\n"
            + "	                profile_image_url string PATH 'profile_image_url',	\n"
            + "	                source string PATH 'source',	\n"
            + "	                text string PATH 'text') tweet;"
            + "                CREATE VIEW Tweet AS select * FROM twitterview.getTweets;";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();
    Table table = tableMap.get("Tweet");
    assertNotNull(table);

    Map<String, Procedure> procedureMap = s.getProcedures();
    Procedure p = procedureMap.get("getTweets");
    assertNotNull(p);
  }
Ejemplo n.º 2
0
  @Test
  public void testOptionsKey() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1( e1 integer, e2 varchar, e3 date, UNIQUE (e1) OPTIONS (CUSTOM_PROP 'VALUE'))";
    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");
    KeyRecord record = table.getAllKeys().iterator().next();
    assertEquals("VALUE", record.getProperty("CUSTOM_PROP", false));
  }
Ejemplo n.º 3
0
  @Test
  public void testMultiKeyPK() throws Exception {
    String ddl = "CREATE FOREIGN TABLE G1( e1 integer, e2 varchar, e3 date, PRIMARY KEY (e1, e2))";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");

    assertEquals(table.getColumns().subList(0, 2), table.getPrimaryKey().getColumns());
  }
Ejemplo n.º 4
0
  @Test
  public void testFBI() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1(e1 integer, e2 varchar, CONSTRAINT fbi INDEX (UPPER(e2)))";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    Table table = tableMap.get("G1");

    assertEquals(1, table.getFunctionBasedIndexes().size());
  }
Ejemplo n.º 5
0
  @Test
  public void testView() throws Exception {
    String ddl =
        "CREATE View G1( e1 integer, e2 varchar) OPTIONS (CARDINALITY 12) AS select e1, e2 from foo.bar";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    Table table = tableMap.get("G1");

    assertEquals("SELECT e1, e2 FROM foo.bar", table.getSelectTransformation());
    assertEquals(12, table.getCardinality());
  }
Ejemplo n.º 6
0
  @Test
  public void testAlterTableDropOptions() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1( e1 integer, e2 varchar, e3 date) OPTIONS(CARDINALITY 12, FOO 'BAR');"
            + "ALTER FOREIGN TABLE G1 OPTIONS(DROP CARDINALITY);"
            + "ALTER FOREIGN TABLE G1 OPTIONS(DROP FOO);";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");
    assertEquals(-1, table.getCardinality());
    assertNull(table.getProperty("FOO", false));
  }
Ejemplo n.º 7
0
  @Test
  public void testUDT() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1( e1 integer, e2 varchar OPTIONS (UDT 'NMTOKENS(12,13,14)'))";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");

    assertEquals("NMTOKENS", table.getColumns().get(1).getDatatype().getName());
    assertEquals(12, table.getColumns().get(1).getLength());
    assertEquals(13, table.getColumns().get(1).getPrecision());
    assertEquals(14, table.getColumns().get(1).getScale());
  }
Ejemplo n.º 8
0
  @Test
  public void testMultipleCommands() throws Exception {
    String ddl =
        "CREATE VIEW V1 AS SELECT * FROM PM1.G1 "
            + "CREATE PROCEDURE FOO(P1 integer) RETURNS (e1 integer, e2 varchar) AS SELECT * FROM PM1.G1;";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();
    Table table = tableMap.get("V1");
    assertNotNull(table);
    assertEquals("SELECT * FROM PM1.G1", table.getSelectTransformation());

    Map<String, Procedure> procedureMap = s.getProcedures();
    Procedure p = procedureMap.get("FOO");
    assertNotNull(p);
    assertEquals("SELECT * FROM PM1.G1;", p.getQueryPlan());
  }
Ejemplo n.º 9
0
  @Test
  public void testConstraints2() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1( e1 integer, e2 varchar, e3 date, "
            + "ACCESSPATTERN(e1), UNIQUE(e1), ACCESSPATTERN(e2, e3))";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");

    assertEquals(table.getColumns().subList(0, 1), table.getUniqueKeys().get(0).getColumns());
    assertEquals(2, table.getAccessPatterns().size());
    assertEquals(table.getColumns().subList(0, 1), table.getAccessPatterns().get(0).getColumns());
    assertEquals(table.getColumns().subList(1, 3), table.getAccessPatterns().get(1).getColumns());
  }
Ejemplo n.º 10
0
  @Test
  public void testAlterTableRemoveColumnOptions() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1( e1 integer OPTIONS (NULL_VALUE_COUNT 12, FOO 'BAR'), e2 varchar, e3 date);"
            + "ALTER FOREIGN TABLE G1 ALTER COLUMN e1 OPTIONS(DROP NULL_VALUE_COUNT);"
            + "ALTER FOREIGN TABLE G1 ALTER COLUMN e1 OPTIONS(DROP FOO);"
            + "ALTER FOREIGN TABLE G1 ALTER COLUMN e1 OPTIONS( ADD x 'y');";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");
    Column c = table.getColumnByName("e1");
    assertNotNull(c);

    assertNull(c.getProperty("FOO", false));
    assertEquals(-1, c.getNullValues());
    assertEquals("y", c.getProperty("x", false));
  }
Ejemplo n.º 11
0
  @Test
  public void testAlterTableAddColumnOptions() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1( e1 integer, e2 varchar, e3 date);"
            + "ALTER FOREIGN TABLE G1 OPTIONS(ADD CARDINALITY 12);"
            + "ALTER FOREIGN TABLE G1 ALTER COLUMN e1 OPTIONS(ADD NULL_VALUE_COUNT 12);"
            + "ALTER FOREIGN TABLE G1 ALTER COLUMN e1 OPTIONS(ADD FOO 'BAR');";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");
    assertEquals(12, table.getCardinality());
    Column c = table.getColumnByName("e1");
    assertNotNull(c);

    assertEquals("BAR", c.getProperty("FOO", false));
    assertEquals(12, c.getNullValues());
  }
Ejemplo n.º 12
0
  @Test
  public void testInsteadOfTrigger() throws Exception {
    String ddl =
        "CREATE VIEW G1( e1 integer, e2 varchar) AS select * from foo;"
            + "CREATE TRIGGER ON G1 INSTEAD OF INSERT AS "
            + "FOR EACH ROW \n"
            + "BEGIN ATOMIC \n"
            + "insert into g1 (e1, e2) values (1, 'trig');\n"
            + "END;"
            + "CREATE View G2( e1 integer, e2 varchar) AS select * from foo;";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    assertTrue("Table not found", tableMap.containsKey("G2"));
    assertEquals(
        "FOR EACH ROW\nBEGIN ATOMIC\nINSERT INTO g1 (e1, e2) VALUES (1, 'trig');\nEND",
        s.getTable("G1").getInsertPlan());
  }
Ejemplo n.º 13
0
  @Test
  public void testFK() throws Exception {
    String ddl =
        "CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 varchar, PRIMARY KEY(g1e1, g1e2));\n"
            + "CREATE FOREIGN TABLE G2( g2e1 integer, g2e2 varchar, "
            + "FOREIGN KEY (g2e1, g2e2) REFERENCES G1 (g1e1, g1e2) options (\"teiid_rel:allow-join\" true))";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();
    assertEquals(2, tableMap.size());

    assertTrue("Table not found", tableMap.containsKey("G1"));
    assertTrue("Table not found", tableMap.containsKey("G2"));

    Table table = tableMap.get("G2");
    ForeignKey fk = table.getForeignKeys().get(0);
    assertEquals(Boolean.TRUE.toString(), fk.getProperty(ForeignKey.ALLOW_JOIN, false));
    assertEquals(fk.getColumns(), table.getColumns());
    assertEquals("G1", fk.getReferenceTableName());
  }
Ejemplo n.º 14
0
  @Test
  public void testForeignTable() throws Exception {

    String ddl =
        "CREATE FOREIGN TABLE G1(\n"
            + "e1 integer primary key,\n"
            + "e2 varchar(10) unique,\n"
            + "e3 date not null unique,\n"
            + "e4 decimal(12,3) default 12.2 options (searchable 'unsearchable'),\n"
            + "e5 integer auto_increment INDEX OPTIONS (UUID 'uuid', NAMEINSOURCE 'nis', SELECTABLE 'NO'),\n"
            + "e6 varchar index default 'hello')\n"
            + "OPTIONS (CARDINALITY 12, UUID 'uuid2',  UPDATABLE 'true', FOO 'BAR', ANNOTATION 'Test Table')";

    Schema s = helpParse(ddl, "model").getSchema();
    Map<String, Table> tableMap = s.getTables();

    assertTrue("Table not found", tableMap.containsKey("G1"));
    Table table = tableMap.get("G1");
    assertTrue(table.isPhysical());
    assertFalse(table.isVirtual());
    assertFalse(table.isSystem());
    assertFalse(table.isMaterialized());
    assertFalse(table.isDeletePlanEnabled());
    assertEquals("uuid2", table.getUUID());
    assertEquals(12, table.getCardinality());
    assertTrue(table.supportsUpdate());
    assertEquals("BAR", table.getProperties().get("FOO"));
    assertEquals("Test Table", table.getAnnotation());

    assertEquals(6, table.getColumns().size());

    List<Column> columns = table.getColumns();
    Column e1 = columns.get(0);
    Column e2 = columns.get(1);
    Column e3 = columns.get(2);
    Column e4 = columns.get(3);
    Column e5 = columns.get(4);
    Column e6 = columns.get(5);

    assertEquals("e1", e1.getName());
    assertEquals("int", e1.getDatatype().getName());
    assertEquals("primary key not same", e1, table.getPrimaryKey().getColumns().get(0));

    assertEquals("e2", e2.getName());
    assertEquals("string", e2.getDatatype().getName());
    assertEquals("unique", e2, table.getUniqueKeys().get(0).getColumns().get(0));
    assertEquals(NullType.Nullable, e2.getNullType());
    assertEquals(10, e2.getLength());
    assertEquals(0, e2.getPrecision());

    assertEquals("e3", e3.getName());
    assertEquals("date", e3.getDatatype().getName());
    assertEquals("unique", e3, table.getUniqueKeys().get(1).getColumns().get(0));
    assertEquals(NullType.No_Nulls, e3.getNullType());

    assertEquals("e4", e4.getName());
    assertEquals("bigdecimal", e4.getDatatype().getName());
    assertEquals(false, e4.isAutoIncremented());
    assertEquals(12, e4.getPrecision());
    assertEquals(3, e4.getScale());
    assertEquals(SearchType.Unsearchable, e4.getSearchType());
    assertEquals("12.2", e4.getDefaultValue());

    assertEquals("e5", e5.getName());
    assertEquals("int", e5.getDatatype().getName());
    assertEquals(true, e5.isAutoIncremented());
    assertEquals("uuid", e5.getUUID());
    assertEquals("nis", e5.getNameInSource());
    assertEquals(false, e5.isSelectable());
    assertEquals("index", e5, table.getIndexes().get(0).getColumns().get(0));

    assertEquals("e6", e6.getName());
    assertEquals("string", e6.getDatatype().getName());
    assertEquals("index", e6, table.getIndexes().get(1).getColumns().get(0));
    assertEquals("hello", e6.getDefaultValue());
  }