Example #1
0
  @Test
  public void testUpsertDateValues() throws Exception {
    long ts = nextTimestamp();
    Date now = new Date(System.currentTimeMillis());
    ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
    Properties props = new Properties();
    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String dateString = "1999-01-01 02:00:00";
    PreparedStatement upsertStmt =
        conn.prepareStatement(
            "upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    upsertStmt =
        conn.prepareStatement(
            "upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();

    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String select = "SELECT date,current_date() FROM ptsdb";
    ResultSet rs = conn.createStatement().executeQuery(select);
    Date then = new Date(System.currentTimeMillis());
    assertTrue(rs.next());
    Date date = DateUtil.parseDate(dateString);
    assertEquals(date, rs.getDate(1));
    assertTrue(rs.next());
    assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
    assertFalse(rs.next());
  }
Example #2
0
  @Test
  public void testUpsertValuesWithDate() throws Exception {
    long ts = nextTimestamp();
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement()
        .execute("create table UpsertDateTest (k VARCHAR not null primary key,date DATE)");
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
    conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement()
        .execute("upsert into UpsertDateTest values ('a',to_date('2013-06-08 00:00:00'))");
    conn.commit();
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs =
        conn.createStatement().executeQuery("select k,to_char(date) from UpsertDateTest");
    assertTrue(rs.next());
    assertEquals("a", rs.getString(1));
    assertEquals("2013-06-08 00:00:00", rs.getString(2));
  }
Example #3
0
  @Test
  public void testUpsertValuesWithExpression() throws Exception {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
    Properties props = new Properties();
    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
    PreparedStatement upsertStmt = conn.prepareStatement(upsert);
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
    upsertStmt = conn.prepareStatement(upsert);
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();

    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String select = "SELECT i FROM IntKeyTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(-1, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(3, rs.getInt(1));
    assertFalse(rs.next());
  }
Example #4
0
  @Test
  public void testUpsertVarCharWithMaxLength() throws Exception {
    long ts = nextTimestamp();
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement()
        .execute(
            "create table phoenix_uuid_mac (mac_md5 VARCHAR not null primary key,raw_mac VARCHAR)");
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
    conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement().execute("upsert into phoenix_uuid_mac values ('00000000591','a')");
    conn.createStatement().execute("upsert into phoenix_uuid_mac values ('000000005919','b')");
    conn.commit();
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs = conn.createStatement().executeQuery("select max(mac_md5) from phoenix_uuid_mac");
    assertTrue(rs.next());
    assertEquals("000000005919", rs.getString(1));
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 15));
    conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement()
        .execute(
            "upsert into phoenix_uuid_mac values ('000000005919adfasfasfsafdasdfasfdasdfdasfdsafaxxf1','b')");
    conn.commit();
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
    conn = DriverManager.getConnection(getUrl(), props);
    rs = conn.createStatement().executeQuery("select max(mac_md5) from phoenix_uuid_mac");
    assertTrue(rs.next());
    assertEquals("000000005919adfasfasfsafdasdfasfdasdfdasfdsafaxxf1", rs.getString(1));
    conn.close();
  }