Пример #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());
  }
Пример #2
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());
  }
Пример #3
0
 @Test
 public void dbclose() throws SQLException {
   conn.prepareStatement("select ?;").setString(1, "Hello World");
   conn.prepareStatement("select null;").close();
   conn.prepareStatement("select null;").executeQuery().close();
   conn.prepareStatement("create table t (c);").executeUpdate();
   conn.prepareStatement("select null;");
 }
Пример #4
0
  @Test
  public void timestamp() throws SQLException {
    connectWithJulianDayModeActivated();
    long now = System.currentTimeMillis();
    Timestamp d1 = new Timestamp(now);
    Date d2 = new Date(now);
    Time d3 = new Time(now);

    stat.execute("create table t (c1);");
    PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
    prep.setTimestamp(1, d1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select c1 from t;");
    assertTrue(rs.next());
    assertEquals(d1, rs.getTimestamp(1));

    rs = stat.executeQuery("select date(c1, 'localtime') from t;");
    assertTrue(rs.next());
    assertEquals(d2.toString(), rs.getString(1));

    rs = stat.executeQuery("select time(c1, 'localtime') from t;");
    assertTrue(rs.next());
    assertEquals(d3.toString(), rs.getString(1));

    rs = stat.executeQuery("select strftime('%Y-%m-%d %H:%M:%f', c1, 'localtime') from t;");
    assertTrue(rs.next());
    // assertEquals(d1.toString(), rs.getString(1)); // ms are not occurate...
  }
 @Test
 public void testCompareLongGTEDecimal() throws Exception {
   long ts = nextTimestamp();
   initTableValues(null, ts);
   String query = "SELECT l FROM LongInKeyTest where l >= 1.5";
   Properties props = new Properties();
   props.setProperty(
       PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
   Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
   try {
     PreparedStatement statement = conn.prepareStatement(query);
     ResultSet rs = statement.executeQuery();
     /*
      *  Failing because we're not converting the constant to the type of the RHS
      *  when forming the start/stop key.
      *  For this case, 1.5 -> 1L
      *  if where l < 1.5 then 1.5 -> 1L and then to 2L because it's not inclusive
      *
      */
     assertTrue(rs.next());
     assertEquals(2, rs.getLong(1));
     assertFalse(rs.next());
   } finally {
     conn.close();
   }
 }
Пример #6
0
 @edu.umd.cs.findbugs.annotations.SuppressWarnings(
     value = "RV_RETURN_VALUE_IGNORED",
     justification = "Test code.")
 private void executeQuery(Connection conn, String query) throws SQLException {
   PreparedStatement st = conn.prepareStatement(query);
   st.executeQuery();
 }
Пример #7
0
 @Test
 public void dblock() throws SQLException {
   stat.executeUpdate("create table test (c1);");
   stat.executeUpdate("insert into test values (1);");
   conn.prepareStatement("select * from test;").executeQuery().close();
   stat.executeUpdate("drop table test;");
 }
Пример #8
0
  @Test
  public void retainKeysInBatch() throws SQLException {
    stat.executeUpdate("create table test (c1, c2);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?, ?);");
    prep.setInt(1, 10);
    prep.setString(2, "ten");
    prep.addBatch();
    prep.setInt(1, 100);
    prep.setString(2, "hundred");
    prep.addBatch();
    prep.setString(2, "one hundred");
    prep.addBatch();
    prep.setInt(1, 1000);
    prep.setString(2, "thousand");
    prep.addBatch();
    prep.executeBatch();
    prep.close();

    ResultSet rs = stat.executeQuery("select * from test;");
    assertTrue(rs.next());
    assertEquals(10, rs.getInt(1));
    assertEquals("ten", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(100, rs.getInt(1));
    assertEquals("hundred", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(100, rs.getInt(1));
    assertEquals("one hundred", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(1000, rs.getInt(1));
    assertEquals("thousand", rs.getString(2));
    assertFalse(rs.next());
    rs.close();
  }
Пример #9
0
  @Test
  public void batch() throws SQLException {
    ResultSet rs;

    stat.executeUpdate("create table test (c1, c2, c3, c4);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
    for (int i = 0; i < 10; i++) {
      prep.setInt(1, Integer.MIN_VALUE + i);
      prep.setFloat(2, Float.MIN_VALUE + i);
      prep.setString(3, "Hello " + i);
      prep.setDouble(4, Double.MAX_VALUE + i);
      prep.addBatch();
    }
    assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
    assertEquals(0, prep.executeBatch().length);
    prep.close();

    rs = stat.executeQuery("select * from test;");
    for (int i = 0; i < 10; i++) {
      assertTrue(rs.next());
      assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
      assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
      assertEquals("Hello " + i, rs.getString(3));
      assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
    }
    rs.close();
    stat.executeUpdate("drop table test;");
  }
Пример #10
0
 @Test
 public void emptyRS() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select null limit 0;");
   ResultSet rs = prep.executeQuery();
   assertFalse(rs.next());
   rs.close();
   prep.close();
 }
Пример #11
0
 @Test
 public void doubleclose() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select null;");
   ResultSet rs = prep.executeQuery();
   rs.close();
   prep.close();
   prep.close();
 }
Пример #12
0
 @Test
 public void changeSchema() throws SQLException {
   stat.execute("create table t (c1);");
   PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
   conn.createStatement().execute("create table t2 (c2);");
   prep.setInt(1, 1000);
   prep.execute();
   prep.executeUpdate();
 }
Пример #13
0
  @Test
  public void update() throws SQLException {
    assertEquals(conn.prepareStatement("create table s1 (c1);").executeUpdate(), 0);
    PreparedStatement prep = conn.prepareStatement("insert into s1 values (?);");
    prep.setInt(1, 3);
    assertEquals(1, prep.executeUpdate());
    prep.setInt(1, 5);
    assertEquals(1, prep.executeUpdate());
    prep.setInt(1, 7);
    assertEquals(1, prep.executeUpdate());
    prep.close();

    // check results with normal statement
    ResultSet rs = stat.executeQuery("select sum(c1) from s1;");
    assertTrue(rs.next());
    assertEquals(15, rs.getInt(1));
    rs.close();
  }
Пример #14
0
  @Test
  public void testPrepareStatement() throws SQLException {
    PreparedStatement ps = connection.prepareStatement(SELECT);
    assertNotNull(ps);
    ps = connection.prepareStatement(INSERT, Statement.NO_GENERATED_KEYS);
    assertNotNull(ps);
    ps = connection.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS);
    assertNotNull(ps);

    // Ignored, despite this being a select
    ps = connection.prepareStatement(SELECT, Statement.NO_GENERATED_KEYS);
    assertNotNull(ps);
    connection.enableStrictMode(true);
    try {
      connection.prepareStatement(SELECT, Statement.NO_GENERATED_KEYS);
      fail("expected exception");
    } catch (IllegalArgumentException e) {
    }
  }
Пример #15
0
 private void insertRow(Connection conn, String uri, int appcpu) throws SQLException {
   PreparedStatement statement =
       conn.prepareStatement(
           "UPSERT INTO " + GROUPBYTEST_NAME + "(id, uri, appcpu) values (?,?,?)");
   statement.setString(1, "id" + id);
   statement.setString(2, uri);
   statement.setInt(3, appcpu);
   statement.executeUpdate();
   id++;
 }
Пример #16
0
 @Test
 public void setmaxrows() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select 1 union select 2;");
   prep.setMaxRows(1);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(1, rs.getInt(1));
   assertFalse(rs.next());
   prep.close();
 }
Пример #17
0
 @Test
 public void stringRS() throws SQLException {
   String name = "Gandhi";
   PreparedStatement prep = conn.prepareStatement("select ?;");
   prep.setString(1, name);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(name, rs.getString(1));
   assertFalse(rs.next());
   rs.close();
 }
  protected static void initTableValues(byte[][] splits, long ts) throws Exception {
    ensureTableCreated(getUrl(), "LongInKeyTest", splits, ts - 2);

    // Insert all rows at ts
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
    Connection conn = DriverManager.getConnection(url);
    conn.setAutoCommit(true);
    PreparedStatement stmt = conn.prepareStatement("upsert into " + "LongInKeyTest VALUES(?)");
    stmt.setLong(1, 2);
    stmt.execute();
    conn.close();
  }
Пример #19
0
  @Test
  public void utf() throws SQLException {
    ResultSet rs =
        stat.executeQuery(
            "select '"
                + utf01
                + "','"
                + utf02
                + "','"
                + utf03
                + "','"
                + utf04
                + "','"
                + utf05
                + "','"
                + utf06
                + "','"
                + utf07
                + "','"
                + utf08
                + "';");
    assertEquals(utf01, rs.getString(1));
    assertEquals(utf02, rs.getString(2));
    assertEquals(utf03, rs.getString(3));
    assertEquals(utf04, rs.getString(4));
    assertEquals(utf05, rs.getString(5));
    assertEquals(utf06, rs.getString(6));
    assertEquals(utf07, rs.getString(7));
    assertEquals(utf08, rs.getString(8));
    rs.close();

    PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;");
    prep.setString(1, utf01);
    prep.setString(2, utf02);
    prep.setString(3, utf03);
    prep.setString(4, utf04);
    prep.setString(5, utf05);
    prep.setString(6, utf06);
    prep.setString(7, utf07);
    prep.setString(8, utf08);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(utf01, rs.getString(1));
    assertEquals(utf02, rs.getString(2));
    assertEquals(utf03, rs.getString(3));
    assertEquals(utf04, rs.getString(4));
    assertEquals(utf05, rs.getString(5));
    assertEquals(utf06, rs.getString(6));
    assertEquals(utf07, rs.getString(7));
    assertEquals(utf08, rs.getString(8));
    rs.close();
  }
Пример #20
0
  public void assertObjectExistenceInSQLIteMasterTable(
      final String name,
      final String type,
      boolean exists,
      final ConnectionManager connectionManager) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = connectionManager.getConnection(null);
      statement = connection.prepareStatement("SELECT name FROM sqlite_master WHERE type=?");
      statement.setString(1, type);
      java.sql.ResultSet indices = statement.executeQuery();

      boolean found = false;
      StringBuilder objectsFound = new StringBuilder();
      String next;
      while (indices.next()) {
        next = indices.getString(1);
        objectsFound.append("'").append(next).append("' ");
        if (name.equals(next)) {
          found = true;
        }
      }

      if (exists)
        Assert.assertTrue(
            "Object '"
                + name
                + "' must exists in 'sqlite_master' but it doesn't. found: "
                + found
                + ". Objects found: "
                + objectsFound,
            found);
      else
        Assert.assertFalse(
            "Object '"
                + name
                + "' must NOT exists in 'sqlite_master' but it does. found: "
                + found
                + " Objects found: "
                + objectsFound,
            found);

    } catch (Exception e) {
      throw new IllegalStateException(
          "Unable to verify existence of the object '" + name + "' in the 'sqlite_master' table",
          e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
Пример #21
0
 @Test
 public void singleRowRS() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select ?;");
   prep.setInt(1, Integer.MAX_VALUE);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(Integer.MAX_VALUE, rs.getInt(1));
   assertEquals(Integer.toString(Integer.MAX_VALUE), rs.getString(1));
   assertEquals(new Integer(Integer.MAX_VALUE).doubleValue(), rs.getDouble(1));
   assertFalse(rs.next());
   rs.close();
   prep.close();
 }
Пример #22
0
 @Test
 public void twoRowRS() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select ? union all select ?;");
   prep.setDouble(1, Double.MAX_VALUE);
   prep.setDouble(2, Double.MIN_VALUE);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(Double.MAX_VALUE, rs.getDouble(1));
   assertTrue(rs.next());
   assertEquals(Double.MIN_VALUE, rs.getDouble(1));
   assertFalse(rs.next());
   rs.close();
 }
Пример #23
0
  @Test
  public void set() throws SQLException {
    ResultSet rs;
    PreparedStatement prep = conn.prepareStatement("select ?, ?, ?;");

    // integers
    prep.setInt(1, Integer.MIN_VALUE);
    prep.setInt(2, Integer.MAX_VALUE);
    prep.setInt(3, 0);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(Integer.MIN_VALUE, rs.getInt(1));
    assertEquals(Integer.MAX_VALUE, rs.getInt(2));
    assertEquals(0, rs.getInt(3));

    // strings
    String name = "Winston Leonard Churchill";
    String fn = name.substring(0, 7), mn = name.substring(8, 15), sn = name.substring(16, 25);
    prep.clearParameters();
    prep.setString(1, fn);
    prep.setString(2, mn);
    prep.setString(3, sn);
    prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(fn, rs.getString(1));
    assertEquals(mn, rs.getString(2));
    assertEquals(sn, rs.getString(3));

    // mixed
    prep.setString(1, name);
    prep.setString(2, null);
    prep.setLong(3, Long.MAX_VALUE);
    prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(name, rs.getString(1));
    assertNull(rs.getString(2));
    assertTrue(rs.wasNull());
    assertEquals(Long.MAX_VALUE, rs.getLong(3));

    // bytes
    prep.setBytes(1, b1);
    prep.setBytes(2, b2);
    prep.setBytes(3, b3);
    prep.executeQuery();
    assertTrue(rs.next());
    assertArrayEq(rs.getBytes(1), b1);
    assertArrayEq(rs.getBytes(2), b2);
    assertArrayEq(rs.getBytes(3), b3);
    assertFalse(rs.next());
    rs.close();
  }
Пример #24
0
  @Test
  public void date2() throws SQLException {
    Date d1 = new Date(1092941466000L);
    stat.execute("create table t (c1);");
    PreparedStatement prep =
        conn.prepareStatement("insert into t values (datetime(?/1000, 'unixepoch'));");
    prep.setDate(1, d1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select strftime('%s', c1) * 1000 from t;");
    assertTrue(rs.next());
    assertEquals(d1.getTime(), rs.getLong(1));
    assertTrue(rs.getDate(1).equals(d1));
  }
Пример #25
0
  @Test
  public void multiUpdate() throws SQLException {
    stat.executeUpdate("create table test (c1);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?);");

    for (int i = 0; i < 10; i++) {
      prep.setInt(1, i);
      prep.executeUpdate();
      prep.execute();
    }

    prep.close();
    stat.executeUpdate("drop table test;");
  }
Пример #26
0
  @Test
  public void tokens() throws SQLException {
    /* checks for a bug where a substring is read by the driver as the
     * full original string, caused by my idiocyin assuming the
     * pascal-style string was null terminated. Thanks Oliver Randschau. */
    StringTokenizer st = new StringTokenizer("one two three");
    st.nextToken();
    String substr = st.nextToken();

    PreparedStatement prep = conn.prepareStatement("select ?;");
    prep.setString(1, substr);
    ResultSet rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(substr, rs.getString(1));
  }
Пример #27
0
 @Test
 public void batchOneParam() throws SQLException {
   stat.executeUpdate("create table test (c1);");
   PreparedStatement prep = conn.prepareStatement("insert into test values (?);");
   for (int i = 0; i < 10; i++) {
     prep.setInt(1, Integer.MIN_VALUE + i);
     prep.addBatch();
   }
   assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
   prep.close();
   ResultSet rs = stat.executeQuery("select count(*) from test;");
   assertTrue(rs.next());
   assertEquals(10, rs.getInt(1));
   rs.close();
 }
Пример #28
0
  @Test
  public void insert1000() throws SQLException {
    stat.executeUpdate("create table in1000 (a);");
    PreparedStatement prep = conn.prepareStatement("insert into in1000 values (?);");
    conn.setAutoCommit(false);
    for (int i = 0; i < 1000; i++) {
      prep.setInt(1, i);
      prep.executeUpdate();
    }
    conn.commit();

    ResultSet rs = stat.executeQuery("select count(a) from in1000;");
    assertTrue(rs.next());
    assertEquals(1000, rs.getInt(1));
    rs.close();
  }
Пример #29
0
  @Test
  public void metaData() throws SQLException {
    PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as delta;");
    ResultSetMetaData meta = prep.getMetaData();
    assertEquals(3, meta.getColumnCount());
    assertEquals("col1", meta.getColumnName(1));
    assertEquals("col2", meta.getColumnName(2));
    assertEquals("delta", meta.getColumnName(3));
    /*assertEquals(Types.INTEGER, meta.getColumnType(1));
    assertEquals(Types.INTEGER, meta.getColumnType(2));
    assertEquals(Types.INTEGER, meta.getColumnType(3));*/

    meta = prep.executeQuery().getMetaData();
    assertEquals(3, meta.getColumnCount());
    prep.close();
  }
Пример #30
0
 @Test
 public void colNameAccess() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;");
   prep.setNull(1, 0);
   prep.setFloat(2, Float.MIN_VALUE);
   prep.setShort(3, Short.MIN_VALUE);
   prep.executeQuery();
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertNull(rs.getString("col1"));
   assertTrue(rs.wasNull());
   assertEquals(Float.MIN_VALUE, rs.getFloat("col2"));
   assertEquals(Short.MIN_VALUE, rs.getShort("bingo"));
   rs.close();
   prep.close();
 }