Java Code Examples for com.almworks.sqlite4java.SQLiteStatement#dispose()

The following examples show how to use com.almworks.sqlite4java.SQLiteStatement#dispose() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: arcusplatform   File: DbUtils.java    License: Apache License 2.0 6 votes vote down vote up
@Nullable
@Override
public O execute(SQLiteConnection conn) throws Exception {
   I val = value;
   DbBinder<I> bnd = binder;
   if (bnd == null || val == null) {
      conn.exec(sql);
      return results(conn);
   }

   SQLiteStatement stmt = conn.prepare(sql, true);
   try {
      bnd.bind(conn, stmt, val);
      while (stmt.step()) {
         row(conn, stmt);
      }

      return results(conn);
   } finally {
      stmt.dispose();
   }
}
 
Example 2
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 6 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final SQLiteStatement select = conn.prepare("DELETE from Sessions where userId = ?");
        select.bind(1, account.userId);
        select.step();
        select.dispose();
    } finally {
        conn.dispose();
    }
    clearCookie(exchange);
    exchange.sendResponseHeaders(200, -1);
}
 
Example 3
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 6 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final byte[] subscribeKey = generateRandomToken();

        final SQLiteStatement select = conn.prepare("UPDATE Accounts SET " +
                "subscribeKey = ? WHERE userId = ?");
        select.bind(1, subscribeKey);
        select.bind(2, account.userId);
        select.step();
        select.dispose();
    } finally {
        conn.dispose();
    }
    exchange.sendResponseHeaders(200, -1);
}
 
Example 4
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 5 votes vote down vote up
private static void createAccount(final String username, final String password)
        throws GeneralSecurityException, SQLiteException {
    if (username.length() > 32 || !username.matches("[a-zA-Z0-9]+")) {
        throw new GeneralSecurityException("invalid username");
    }
    validatePassword(password);

    final byte[] passwordSalt = generateRandomToken();
    final byte[] passwordHash = hash(password.getBytes(), passwordSalt);
    final byte[] subscribeKey = generateRandomToken();

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement insert = conn.prepare("INSERT INTO Accounts " +
                "(username, passwordHash, passwordSalt, subscribeKey, creationTime, verifyInterval, alertDelay) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)");
        insert.bind(1, username);
        insert.bind(2, passwordHash);
        insert.bind(3, passwordSalt);
        insert.bind(4, subscribeKey);
        insert.bind(5, System.currentTimeMillis());
        insert.bind(6, DEFAULT_VERIFY_INTERVAL);
        insert.bind(7, DEFAULT_ALERT_DELAY);
        insert.step();
        insert.dispose();
    } catch (final SQLiteException e) {
        if (e.getErrorCode() == SQLITE_CONSTRAINT_UNIQUE) {
            throw new UsernameUnavailableException();
        }
        throw e;
    } finally {
        conn.dispose();
    }
}
 
Example 5
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 5 votes vote down vote up
private static void changePassword(final long userId, final String currentPassword, final String newPassword)
        throws GeneralSecurityException, SQLiteException {
    validatePassword(currentPassword);
    validatePassword(newPassword);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        conn.exec("BEGIN TRANSACTION");

        final SQLiteStatement select = conn.prepare("SELECT passwordHash, passwordSalt " +
                "FROM Accounts WHERE userId = ?");
        select.bind(1, userId);
        select.step();
        final byte[] currentPasswordHash = select.columnBlob(0);
        final byte[] currentPasswordSalt = select.columnBlob(1);
        select.dispose();
        if (!MessageDigest.isEqual(hash(currentPassword.getBytes(), currentPasswordSalt), currentPasswordHash)) {
            throw new GeneralSecurityException("invalid password");
        }

        final byte[] newPasswordSalt = generateRandomToken();
        final byte[] newPasswordHash = hash(newPassword.getBytes(), newPasswordSalt);

        final SQLiteStatement update = conn.prepare("UPDATE Accounts " +
                "SET passwordHash = ?, passwordSalt = ? WHERE userId = ?");
        update.bind(1, newPasswordHash);
        update.bind(2, newPasswordSalt);
        update.bind(3, userId);
        update.step();
        update.dispose();

        conn.exec("COMMIT TRANSACTION");
    } finally {
        conn.dispose();
    }
}
 
Example 6
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final JsonObjectBuilder accountJson = Json.createObjectBuilder();
    accountJson.add("username", account.username);
    accountJson.add("verifyInterval", account.verifyInterval);
    accountJson.add("alertDelay", account.alertDelay);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, true);
        final SQLiteStatement select = conn.prepare("SELECT address FROM EmailAddresses " +
                "WHERE userId = ?");
        select.bind(1, account.userId);
        if (select.step()) {
            accountJson.add("email", select.columnString(0));
        }
        select.dispose();
    } finally {
        conn.dispose();
    }

    exchange.getResponseHeaders().set("Content-Type", "application/json");
    exchange.sendResponseHeaders(200, 0);
    try (final OutputStream output = exchange.getResponseBody();
            final JsonWriter writer = Json.createWriter(output)) {
        writer.write(accountJson.build());
    }
}
 
Example 7
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final String requestToken;
    final String fingerprint;
    try (final JsonReader reader = Json.createReader(exchange.getRequestBody())) {
        final JsonObject object = reader.readObject();
        requestToken = object.getString("requestToken");
        fingerprint = object.getString("fingerprint");
    } catch (final ClassCastException | JsonException | NullPointerException e) {
        e.printStackTrace();
        exchange.sendResponseHeaders(400, -1);
        return;
    }

    final Account account = verifySession(exchange, false, requestToken.getBytes(StandardCharsets.UTF_8));
    if (account == null) {
        return;
    }

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final SQLiteStatement update = conn.prepare("UPDATE Devices SET " +
                "deletionTime = ? WHERE userId = ? AND hex(fingerprint) = ?");
        update.bind(1, System.currentTimeMillis());
        update.bind(2, account.userId);
        update.bind(3, fingerprint);
        update.step();
        update.dispose();

        if (conn.getChanges() == 0) {
            exchange.sendResponseHeaders(400, -1);
            return;
        }
    } finally {
        conn.dispose();
    }
    exchange.sendResponseHeaders(200, -1);
}
 
Example 8
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final InputStream input = exchange.getRequestBody();

    final ByteArrayOutputStream sample = new ByteArrayOutputStream();
    final byte[] buffer = new byte[4096];
    for (int read = input.read(buffer); read != -1; read = input.read(buffer)) {
        sample.write(buffer, 0, read);

        if (sample.size() > 64 * 1024) {
            exchange.sendResponseHeaders(413, -1);
            return;
        }
    }

    final SQLiteConnection conn = new SQLiteConnection(SAMPLES_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement insert = conn.prepare("INSERT INTO Samples " +
               "(sample, time) VALUES (?, ?)");
        insert.bind(1, sample.toByteArray());
        insert.bind(2, System.currentTimeMillis());
        insert.step();
        insert.dispose();
    } finally {
        conn.dispose();
    }

    exchange.sendResponseHeaders(200, -1);
}
 
Example 9
Source Project: sync-android   File: SQLiteWrapperUtils.java    License: Apache License 2.0 5 votes vote down vote up
static void disposeQuietly(SQLiteStatement stmt) {
    if (stmt != null && !stmt.isDisposed()) {
        try {
            stmt.dispose();
        } catch (Throwable e) {}
    }
}
 
Example 10
Source Project: mrgeo   File: MbVectorTilesInputFormat.java    License: Apache License 2.0 5 votes vote down vote up
protected long getRecordCount(Configuration conf) throws IOException
{
  String countQuery = "SELECT COUNT(*) FROM tiles WHERE zoom_level=?";
  // Run the count query and grab the result.
  SQLiteConnection conn = null;
  try {
    conn = MbVectorTilesDataProvider.getDbConnection(dbSettings, conf);
    SQLiteStatement stmt = null;
    try {
      stmt = conn.prepare(countQuery, false);
      stmt.bind(1, zoomLevel);
      if (stmt.step()) {
        return stmt.columnLong(0);
      }
      else {
        throw new IOException("Unable to count tiles for zoom " + zoomLevel + " in " + dbSettings.getFilename());
      }
    }
    finally {
      if (stmt != null) {
        stmt.dispose();
      }
    }
  }
  catch (SQLiteException e)
  {
    String msg = "Unable to get the count of records using query: " + countQuery;
    log.error(msg, e);
    throw new IOException(msg, e);
  }
  finally {
    if (conn != null) {
      conn.dispose();
    }
  }
}
 
Example 11
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 4 votes vote down vote up
private static Session login(final String username, final String password)
        throws GeneralSecurityException, SQLiteException {
    validatePassword(password);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement select = conn.prepare("SELECT userId, passwordHash, " +
                "passwordSalt FROM Accounts WHERE username = ?");
        select.bind(1, username);
        if (!select.step()) {
            throw new UsernameUnavailableException();
        }
        final long userId = select.columnLong(0);
        final byte[] passwordHash = select.columnBlob(1);
        final byte[] passwordSalt = select.columnBlob(2);
        select.dispose();
        if (!MessageDigest.isEqual(hash(password.getBytes(), passwordSalt), passwordHash)) {
            throw new GeneralSecurityException("invalid password");
        }

        final long now = System.currentTimeMillis();
        final SQLiteStatement delete = conn.prepare("DELETE FROM Sessions WHERE expiryTime < ?");
        delete.bind(1, now);
        delete.step();
        delete.dispose();

        final byte[] cookieToken = generateRandomToken();
        final byte[] requestToken = generateRandomToken();

        final SQLiteStatement insert = conn.prepare("INSERT INTO Sessions " +
                "(userId, cookieToken, requestToken, expiryTime) VALUES (?, ?, ?, ?)");
        insert.bind(1, userId);
        insert.bind(2, cookieToken);
        insert.bind(3, requestToken);
        insert.bind(4, now + SESSION_LENGTH);
        insert.step();
        insert.dispose();

        return new Session(conn.getLastInsertId(), cookieToken, requestToken);
    } finally {
        conn.dispose();
    }
}
 
Example 12
Source Project: AttestationServer   File: AttestationServer.java    License: MIT License 4 votes vote down vote up
private static Account verifySession(final HttpExchange exchange, final boolean end, byte[] requestTokenEncoded)
        throws IOException, SQLiteException {
    final String cookie = getCookie(exchange, "__Host-session");
    if (cookie == null) {
        exchange.sendResponseHeaders(403, -1);
        return null;
    }
    final String[] session = cookie.split("\\|", 2);
    if (session.length != 2) {
        clearCookie(exchange);
        exchange.sendResponseHeaders(403, -1);
        return null;
    }
    final long sessionId = Long.parseLong(session[0]);
    final byte[] cookieToken = Base64.getDecoder().decode(session[1]);

    if (requestTokenEncoded == null) {
        requestTokenEncoded = new byte[session[1].length()];
        final DataInputStream input = new DataInputStream(exchange.getRequestBody());
        try {
            input.readFully(requestTokenEncoded);
        } catch (final EOFException e) {
            clearCookie(exchange);
            exchange.sendResponseHeaders(403, -1);
            return null;
        }
    }
    final byte[] requestToken = Base64.getDecoder().decode(requestTokenEncoded);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, !end);

        final SQLiteStatement select = conn.prepare("SELECT cookieToken, requestToken, " +
                "expiryTime, username, subscribeKey, Accounts.userId, verifyInterval, alertDelay " +
                "FROM Sessions " +
                "INNER JOIN Accounts on Accounts.userId = Sessions.userId " +
                "WHERE sessionId = ?");
        select.bind(1, sessionId);
        if (!select.step() || !MessageDigest.isEqual(cookieToken, select.columnBlob(0)) ||
                !MessageDigest.isEqual(requestToken, select.columnBlob(1))) {
            clearCookie(exchange);
            exchange.sendResponseHeaders(403, -1);
            return null;
        }

        if (select.columnLong(2) < System.currentTimeMillis()) {
            clearCookie(exchange);
            exchange.sendResponseHeaders(403, -1);
            return null;
        }

        if (end) {
            final SQLiteStatement delete = conn.prepare("DELETE FROM Sessions " +
                    "WHERE sessionId = ?");
            delete.bind(1, sessionId);
            delete.step();
            delete.dispose();
        }

        return new Account(select.columnLong(5), select.columnString(3), select.columnBlob(4),
                select.columnInt(6), select.columnInt(7));
    } finally {
        conn.dispose();
    }
}
 
Example 13
@Override
public void setup(OperatorContext context)
{
  db = new SQLiteConnection(new File("/tmp/sqlite.db"));
  java.util.logging.Logger.getLogger("com.almworks.sqlite4java").setLevel(java.util.logging.Level.SEVERE);
  SQLiteStatement st;

  try {
    db.open(true);
    // create the temporary tables here
    for (int i = 0; i < inputSchemas.size(); i++) {
      InputSchema inputSchema = inputSchemas.get(i);
      ArrayList<String> indexes = new ArrayList<String>();
      if (inputSchema == null || inputSchema.columnInfoMap.isEmpty()) {
        continue;
      }
      String columnSpec = "";
      String columnNames = "";
      String insertQuestionMarks = "";
      int j = 0;
      for (Map.Entry<String, ColumnInfo> entry : inputSchema.columnInfoMap.entrySet()) {
        if (!columnSpec.isEmpty()) {
          columnSpec += ",";
          columnNames += ",";
          insertQuestionMarks += ",";
        }
        columnSpec += entry.getKey();
        columnSpec += " ";
        columnSpec += entry.getValue().type;
        if (entry.getValue().isColumnIndex) {
          indexes.add(entry.getKey());
        }
        columnNames += entry.getKey();
        insertQuestionMarks += "?";
        entry.getValue().bindIndex = ++j;
      }
      String createTempTableStmt = "CREATE TEMP TABLE " + inputSchema.name + "(" + columnSpec + ")";
      st = db.prepare(createTempTableStmt);
      st.step();
      st.dispose();
      for (String index : indexes) {
        String createIndexStmt = "CREATE INDEX " + inputSchema.name + "_" + index + "_idx ON " + inputSchema.name + " (" + index + ")";
        st = db.prepare(createIndexStmt);
        st.step();
        st.dispose();
      }
      String insertStmt = "INSERT INTO " + inputSchema.name + " (" + columnNames + ") VALUES (" + insertQuestionMarks + ")";

      insertStatements.add(i, db.prepare(insertStmt));
      // We are calling "DELETE FROM" on the tables and because of the "truncate optimization" in sqlite, it should be fast.
      // See http://sqlite.org/lang_delete.html
      deleteStatements.add(i, db.prepare("DELETE FROM " + inputSchema.name));
    }
    beginStatement = db.prepare("BEGIN");
    commitStatement = db.prepare("COMMIT");
    execStatement = db.prepare(statement);
  } catch (SQLiteException ex) {
    throw new RuntimeException(ex);
  }
}
 
Example 14
Source Project: mrgeo   File: MbVectorTilesInputFormat.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public List<InputSplit> getSplits(JobContext context) throws IOException, InterruptedException
{
  zoomLevel = dbSettings.getZoom();
  if (zoomLevel < 0) {
    // Get the max zoom from the tile data
    SQLiteConnection conn = null;
    try {
      conn = MbVectorTilesDataProvider.getDbConnection(dbSettings,
              context.getConfiguration());
      String query = "SELECT MAX(zoom_level) FROM tiles";
      SQLiteStatement stmt = null;
      try {
        stmt = conn.prepare(query, false);
        if (stmt.step()) {
          zoomLevel = stmt.columnInt(0);
        }
        else {
          throw new IOException("Unable to get the max zoom level of " + dbSettings.getFilename());
        }
      }
      finally {
        if (stmt != null) {
          stmt.dispose();
        }
      }
    }
    catch(SQLiteException e) {
      throw new IOException("Unable to query " + dbSettings.getFilename() + " for the max zoom level", e);
    }
    finally {
      if (conn != null) {
        conn.dispose();
      }
    }
  }
  long recordCount = getRecordCount(context.getConfiguration());
  long recordsPerPartition = dbSettings.getTilesPerPartition();
  long numPartitions = recordCount / recordsPerPartition;
  if (numPartitions * recordsPerPartition < recordCount) {
    numPartitions += 1;
  }
  List<InputSplit> splits = new ArrayList<InputSplit>();
  for (int i=0; i < numPartitions; i++) {
    MbVectorTilesInputSplit split = new MbVectorTilesInputSplit(i * recordsPerPartition, recordsPerPartition, zoomLevel);
    splits.add(split);
  }
  return splits;
}