Java Code Examples for com.almworks.sqlite4java.SQLiteConnection#prepare()

@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();
   }
}
 

@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);
}
 

@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);
}
 

/**
 * Utility method to run the query on the db and return the value in the
 * first column of the first row.
 */
public static Long longForQuery(SQLiteConnection conn, String query, Object[] bindArgs)
        throws SQLiteException {
    SQLiteStatement stmt = null;
    try {
        stmt = conn.prepare(query);
        if (bindArgs != null && bindArgs.length > 0) {
            stmt = SQLiteWrapperUtils.bindArguments(stmt, bindArgs);
        }
        if (stmt.step()) {
            return stmt.columnLong(0);
        } else {
            throw new IllegalStateException("query failed to return any result: " + query);
        }
    } finally {
        SQLiteWrapperUtils.disposeQuietly(stmt);
    }
}
 

@Override
protected void execute(SQLiteConnection conn) throws Exception {
   log.trace("sqlite write ahead log checkpointer started...");
   thread = Thread.currentThread();
   try {
      checkpoint = conn.prepare("PRAGMA wal_checkpoint(TRUNCATE)", false);
      while (!shutdown.get()) {
         long now = System.nanoTime();
         long elapsed = now - lastCheckPointTime;
         if (elapsed > walModeCheckpointNs) {
            lastCheckPointTime = now;
            checkpoint(conn);
         }

         ThreadUtils.sleep(5, TimeUnit.SECONDS);
      }
   } catch (InterruptedException ex) {
      log.warn("sqlite checkpointer interrupted, shutting down...");
   } finally {
      log.warn("sqlite checkpointer shutting down...");
      thread = null;

      if (checkpoint != null) {
         checkpoint.dispose();
      }

      log.warn("killing sqlite wal checkpointer...");
   }
}
 

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();
    }
}
 

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();
    }
}
 

@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());
    }
}
 

@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);
}
 

@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);
}
 

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();
    }
  }
}
 

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();
    }
}
 

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();
    }
}
 

@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);
  }
}
 

@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;
}