コード例 #1
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));
  }
コード例 #2
0
ファイル: DBQueriesTest.java プロジェクト: kminder/cqengine
  @Test
  public void testDropIndexOnTable() throws SQLException {

    Connection connection = null;
    Statement statement = null;
    try {
      ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
      connection = spy(connectionManager.getConnection(null));
      statement = spy(connection.createStatement());
      when(connection.createStatement()).thenReturn(statement);

      DBQueries.createIndexTable(NAME, Integer.class, String.class, connection);
      DBQueries.createIndexOnTable(NAME, connection);

      assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
      assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);

      DBQueries.dropIndexOnTable(NAME, connection);
      assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
      assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", false, connectionManager);

      verify(statement, times(3)).close();
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
コード例 #3
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());
  }
コード例 #4
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());
  }
コード例 #5
0
ファイル: PrepStmtTest.java プロジェクト: gwenn/sqlitejdbc
 private void connectWithJulianDayModeActivated() throws SQLException {
   close();
   final Properties info = new Properties();
   info.setProperty("julian_day", "true");
   conn = DriverManager.getConnection("jdbc:sqlite:", info);
   stat = conn.createStatement();
 }
コード例 #6
0
ファイル: GroupByCaseTest.java プロジェクト: baeeq/phoenix
 @Test
 public void testScanUri() throws Exception {
   GroupByCaseTest gbt = new GroupByCaseTest();
   long ts = gbt.createTable();
   gbt.loadData(ts);
   Properties props = new Properties(TEST_PROPERTIES);
   props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
   Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("select uri from " + GROUPBYTEST_NAME);
   assertTrue(rs.next());
   assertEquals("Report1", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("Report2", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("Report3", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("Report4", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL1", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL2", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL3", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("SOQL4", rs.getString(1));
   assertFalse(rs.next());
   conn.close();
 }
コード例 #7
0
ファイル: PrepStmtTest.java プロジェクト: gwenn/sqlitejdbc
 @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();
 }
コード例 #8
0
  @Test
  public void testClientIds() throws SQLException {
    mockConnection.setEmptyResults();
    connection.createStatement().executeQuery(SELECT);
    int id1 = mockConnection.lastRequest.getTransactionId();

    new Connection(mockConnection, "test").createStatement().executeQuery(SELECT);
    int id2 = mockConnection.lastRequest.getTransactionId();
    assertTrue(id1 != id2);
  }
コード例 #9
0
  @Test
  public void createEncrypted() throws SQLException, IOException {
    File tmp = File.createTempFile("sqlitetest", ".db");
    tmp.deleteOnExit();
    String url = "jdbc:sqlite:" + tmp.getAbsolutePath();

    Properties props = new Properties();
    final String password = "******"dog";
    props.put("key", password);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(false);

    Statement st = conn.createStatement();
    st.executeUpdate("create table ants (col int)");
    st.executeUpdate("insert into ants values( 300 )");
    st.executeUpdate("insert into ants values( 400 )");
    st.close();
    conn.commit();
    conn.close();

    // Try reading without key.
    props.remove("key");
    conn = DriverManager.getConnection(url, props);

    try {
      st = conn.createStatement();
      ResultSet rs = st.executeQuery("select count(*) from ants");
      fail("Database not encrypted.");
    } catch (SQLException ignore) {
    }

    conn.close();
    props.put("key", password);
    conn = DriverManager.getConnection(url, props);

    st = conn.createStatement();
    ResultSet rs = st.executeQuery("select count(*) from ants");
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
    conn.close();
  }
コード例 #10
0
 @After
 public void dispose() throws SQLException, IOException, ClassNotFoundException {
   Connection connection = BddConnecteur.getConnection();
   Statement statement = connection.createStatement();
   statement.execute("DROP TABLE Creneau");
   statement.execute("DROP TABLE Atelier");
   statement.execute("DROP TABLE Labo");
   statement.execute("DROP TABLE Utilisateur");
   statement.close();
   connection.close();
   BddConnecteur.dispose();
 }
コード例 #11
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();
  }
コード例 #12
0
 @After
 public void tearDown() throws Exception {
   if (em != null) {
     em.close();
   }
   if (emFactory != null) {
     emFactory.close();
   }
   try {
     connection.createStatement().execute("SHUTDOWN");
   } catch (Exception ex) {
   }
 }
コード例 #13
0
  @Test
  public void testAjoutCreneau() throws SQLException, IOException, ClassNotFoundException {
    Connection connection = BddConnecteur.getConnection();

    BddUtilisateur.ajout("*****@*****.**", "labri");
    BddLabo.ajout(1, "Labri");
    BddAtelier.ajoutAtelier(
        1,
        "A la poursuite d'ennemis invisibles",
        "Sciences de la vie ",
        "Campus Carreire (Hôpital Pellegrin)",
        "Labo MFP",
        "",
        "",
        "",
        "");

    // Insertion
    int idAtelier = 1;
    String jour = "2015-12-23";
    String heure = "13:00";
    int capacite = 20;
    BddCreneau.ajoutCreneau(1, jour, heure, capacite);

    // Vérification
    String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(sql);

    int count = 0;
    Date jourRecup;
    Time heureRecup;
    int capaciteRecup = 0;

    while (rs.next()) {
      jourRecup = rs.getDate("jour");
      heureRecup = rs.getTime("heure");
      capaciteRecup = rs.getInt("capacite");
      count++;
    }

    assertEquals(count, 1);
    // assertEquals(jourRecup, jour);
    // assertEquals(heureRecup, heure);
    assertEquals(capaciteRecup, capacite);

    // Fermeture
    rs.close();
    statement.close();
    connection.close();
  }
コード例 #14
0
ファイル: DBQueriesTest.java プロジェクト: kminder/cqengine
  @Test
  public void testClearIndexTable() throws SQLException {
    Connection connection = null;
    Statement statement = null;
    try {
      ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
      createSchema(connectionManager);
      assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
      assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);

      connection = spy(connectionManager.getConnection(null));
      statement = spy(connection.createStatement());
      when(connection.createStatement()).thenReturn(statement);
      DBQueries.clearIndexTable(NAME, connection);

      List<DBQueries.Row<Integer, String>> expectedRows = Collections.emptyList();
      assertQueryResultSet("SELECT * FROM " + TABLE_NAME, expectedRows, connectionManager);
      verify(statement, times(1)).close();
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
コード例 #15
0
  @Test
  public void testDeadlockTransactionRollback() throws SQLException {
    connection.setAutoCommit(false);

    // Failed statement
    mockConnection.setError(Jdbc.ErrorCode.DEADLOCK.getNumber(), "deadlock");
    Statement s = connection.createStatement();
    try {
      s.executeUpdate("DELETE bad");
      fail("expected exception");
    } catch (SQLException e) {
      assertEquals(Jdbc.ErrorCode.DEADLOCK.getNumber(), e.getErrorCode());
      assertEquals("40001", e.getSQLState());
    }
    assertNull(mockConnection.lastFinish);
  }
コード例 #16
0
  @Test
  public void testSupprCreneauxByIdAtelier()
      throws SQLException, IOException, ClassNotFoundException {
    Connection connection = BddConnecteur.getConnection();

    BddUtilisateur.ajout("*****@*****.**", "labri");
    BddLabo.ajout(1, "Labri");
    BddAtelier.ajoutAtelier(
        1,
        "A la poursuite d'ennemis invisibles",
        "Sciences de la vie ",
        "Campus Carreire (Hôpital Pellegrin)",
        "Labo MFP",
        "",
        "",
        "",
        "");

    // Insertion
    int idAtelier = 1;
    String jour = "2015-12-23";
    String heure = "13:00";
    int oldCapacite = 20;
    int newCapacite = 40;
    BddCreneau.ajoutCreneau(1, jour, heure, oldCapacite);

    // Suppression
    BddCreneau.supprCreneauxByIdAtelier(1);

    // Vérification
    String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(sql);

    int count = 0;

    while (rs.next()) {
      count++;
    }

    assertEquals(count, 0);

    // Fermeture
    rs.close();
    statement.close();
    connection.close();
  }
コード例 #17
0
  @Test
  public void testFailedTransactionRollback() throws SQLException {
    connection.setAutoCommit(false);

    // Failed statement
    mockConnection.setError(42, "bad");
    Statement s = connection.createStatement();
    try {
      s.executeUpdate("DELETE bad");
      fail("expected exception");
    } catch (SQLException e) {
    }
    assertNull(mockConnection.lastFinish);

    // All future statements should also fail
    mockConnection.setUpdateCount(42);
    try {
      s.executeUpdate("DELETE good");
      fail("expected exception");
    } catch (SQLException e) {
    }

    connection.enableStrictMode(true);
    try {
      connection.commit();
      fail("expected exception");
    } catch (SQLException e) {
    }

    // rollback this transaction
    connection.rollback();
    assertNull(mockConnection.lastFinish);
    try {
      // Can't rollback: no transaction!
      connection.rollback();
      fail("expected exception");
    } catch (SQLException e) {
    }

    assertEquals(42, s.executeUpdate("DELETE good"));
  }
コード例 #18
0
ファイル: DBQueriesTest.java プロジェクト: kminder/cqengine
  void initWithTestData(final ConnectionManager connectionManager) {

    createSchema(connectionManager);

    Connection connection = null;
    Statement statement = null;
    try {
      connection = connectionManager.getConnection(null);
      statement = connection.createStatement();
      assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'abs')"), 1);
      assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'gps')"), 1);
      assertEquals(
          statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (2, 'airbags')"), 1);
      assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (3, 'abs')"), 1);
    } catch (Exception e) {
      throw new IllegalStateException("Unable to initialize test database", e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
コード例 #19
0
ファイル: DBQueriesTest.java プロジェクト: kminder/cqengine
  void createSchema(final ConnectionManager connectionManager) {
    Connection connection = null;
    Statement statement = null;

    try {
      connection = connectionManager.getConnection(null);
      statement = connection.createStatement();
      assertEquals(
          statement.executeUpdate(
              "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (objectKey INTEGER, value TEXT)"),
          0);
      assertEquals(
          statement.executeUpdate(
              "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON " + TABLE_NAME + "(value)"),
          0);
    } catch (Exception e) {
      throw new IllegalStateException("Unable to create test database schema", e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
コード例 #20
0
ファイル: DBQueriesTest.java プロジェクト: kminder/cqengine
  public void assertQueryResultSet(
      final String query,
      final List<DBQueries.Row<Integer, String>> rows,
      final ConnectionManager connectionManager)
      throws SQLException {

    Connection connection = null;
    Statement statement = null;

    try {
      connection = connectionManager.getConnection(null);
      statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery(query);
      assertResultSetOrderAgnostic(resultSet, rows);

    } catch (Exception e) {
      throw new IllegalStateException("Unable to verify resultSet", e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
コード例 #21
0
  @Test
  public void testIntegerPK() throws Exception {
    int[] testNumbers = {
      Integer.MIN_VALUE,
      Integer.MIN_VALUE + 1,
      -2,
      -1,
      0,
      1,
      2,
      Integer.MAX_VALUE - 1,
      Integer.MAX_VALUE
    };
    ensureTableCreated(getUrl(), "PKIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO PKIntValueTest VALUES(?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i = 0; i < testNumbers.length; i++) {
      stmt.setInt(1, testNumbers[i]);
      stmt.execute();
    }
    conn.commit();
    conn.close();

    String select = "SELECT COUNT(*) from PKIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());

    select = "SELECT count(*) FROM PKIntValueTest where pk >= " + Integer.MIN_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk >= "
            + Integer.MIN_VALUE
            + " GROUP BY pk ORDER BY pk ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    // NOTE: This case currently fails with an error message:
    // "Overflow trying to get next key for [-1, -1, -1, -1]"
    select = "SELECT count(*) FROM PKIntValueTest where pk <= " + Integer.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk <= "
            + Integer.MAX_VALUE
            + " GROUP BY pk ORDER BY pk DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    // NOTE: This case currently fails since it is not retrieving the negative values.
    select = "SELECT count(*) FROM PKIntValueTest where pk >= " + INTEGER_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk >= "
            + INTEGER_MIN_MINUS_ONE
            + " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    // NOTE: This test case fails because it is not retrieving positive values.
    select = "SELECT count(*) FROM PKIntValueTest where pk <= " + INTEGER_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk <= "
            + INTEGER_MAX_PLUS_ONE
            + " GROUP BY pk ORDER BY pk DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());
  }
コード例 #22
0
  @Test
  public void testBigIntKV() throws Exception {
    // NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of
    // bigint is not recognizable in the current version. As a result, we start with
    // Long.MIN_VALUE+1 as the smallest value.
    long[] testNumbers = {
      Long.MIN_VALUE + 1,
      Long.MIN_VALUE + 2,
      -2L,
      -1L,
      0L,
      1L,
      2L,
      Long.MAX_VALUE - 1,
      Long.MAX_VALUE
    };
    ensureTableCreated(getUrl(), "KVBigIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO KVBigIntValueTest VALUES(?,?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i = 0; i < testNumbers.length; i++) {
      stmt.setLong(1, i);
      stmt.setLong(2, testNumbers[i]);
      stmt.execute();
    }
    conn.commit();
    conn.close();

    String select = "SELECT COUNT(*) from KVBigIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + (Long.MIN_VALUE + 1);
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVBigIntValueTest WHERE kv >= "
            + (Long.MIN_VALUE + 1)
            + " GROUP BY kv ORDER BY kv ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + Long.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVBigIntValueTest WHERE kv <= "
            + Long.MAX_VALUE
            + " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());

    /* NOTE: This section currently fails due to the fact that we cannot parse literal values
       that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
       before enabling this section of the test.
    select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + LONG_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT kv FROM KVBigIntValueTest WHERE kv >= " + LONG_MIN_MINUS_ONE +
            " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + LONG_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT kv FROM KVBigIntValueTest WHERE kv <= " + LONG_MAX_PLUS_ONE +
            " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length-1; i >= 0; i--) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());
    */
  }
コード例 #23
0
  @Test
  public void testIntegerKV() throws Exception {
    int[] testNumbers = {
      Integer.MIN_VALUE,
      Integer.MIN_VALUE + 1,
      -2,
      -1,
      0,
      1,
      2,
      Integer.MAX_VALUE - 1,
      Integer.MAX_VALUE
    };
    ensureTableCreated(getUrl(), "KVIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO KVIntValueTest VALUES(?, ?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i = 0; i < testNumbers.length; i++) {
      stmt.setInt(1, i);
      stmt.setInt(2, testNumbers[i]);
      stmt.execute();
    }
    conn.commit();
    conn.close();

    String select = "SELECT COUNT(*) from KVIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv >= " + Integer.MIN_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv >= "
            + Integer.MIN_VALUE
            + " GROUP BY kv ORDER BY kv ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv <= " + Integer.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv <= "
            + Integer.MAX_VALUE
            + " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv >= " + INTEGER_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv >= "
            + INTEGER_MIN_MINUS_ONE
            + " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv <= " + INTEGER_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv <= "
            + INTEGER_MAX_PLUS_ONE
            + " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());
  }
コード例 #24
0
ファイル: PrepStmtTest.java プロジェクト: gwenn/sqlitejdbc
 @Before
 public void connect() throws Exception {
   conn = DriverManager.getConnection("jdbc:sqlite:");
   stat = conn.createStatement();
 }