com.almworks.sqlite4java.SQLiteConnection Java Examples

/**
 * 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
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);
}
 

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

protected static boolean canOpen(Configuration conf,
        String input,
        ProviderProperties providerProperties) throws IOException
{
  MbVectorTilesSettings dbSettings = parseResourceName(input, conf, providerProperties);
  SQLiteConnection conn = null;
  try {
    conn = getDbConnection(dbSettings, conf);
    return true;
  }
  catch(IOException e) {
    log.info("Unable to open MB vector tiles database: " + dbSettings.getFilename(), e);
  }
  finally {
    if (conn != null) {
      conn.dispose();
    }
  }
  return false;
}
 

public static SQLiteCursor buildSQLiteCursor(SQLiteConnection conn, String sql, Object[] bindArgs)
        throws SQLiteException {
    SQLiteStatement stmt = null;
    try {
        stmt = bindArguments(conn.prepare(sql), bindArgs);
        List<String> columnNames = null;
        List<Tuple> resultSet = new ArrayList<Tuple>();
        while (!stmt.hasStepped() || stmt.hasRow()) {
            if (!stmt.step()) {
                break;
            }
            if (columnNames == null) {
                columnNames = getColumnNames(stmt);
            }

            Tuple t = getDataRow(stmt);
            logger.finest("Tuple: "+ t.toString());
            resultSet.add(t);
        }
        return new SQLiteCursor(columnNames, resultSet);
    } finally {
        SQLiteWrapperUtils.disposeQuietly(stmt);
    }
}
 

@Override
public ZWNode extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   int node_id = stmt.columnInt(NODE_ID_COL);
   ZWNodeBuilder builder = ZWNode.builder(node_id);
   ZWNode node = builder.setBasicType(stmt.columnInt(NODE_BASIC_TYPE_COL))
         .setGenericType(stmt.columnInt(NODE_GENERIC_TYPE_COL))
         .setSpecificType(stmt.columnInt(NODE_SPECIFIC_TYPE_COL))
         .setManufacturerId(stmt.columnInt(NODE_MANUFACTURER_ID_COL))
         .setProductTypeId(stmt.columnInt(NODE_PRODUCT_TYPE_COL))
         .setProductId(stmt.columnInt(NODE_PRODUCT_ID))
         .setHomeId(ZWServices.INSTANCE.getNetwork().getHomeId())
         .addCmdClasses(ByteUtils.string2bytes(stmt.columnString(NODE_CMD_CLASSES)))
         .setOnline(stmt.columnInt(NODE_ONLINE))
         .setOfflineTimeout(stmt.columnInt(NODE_OFFLINE_TIMEOUT))
         .build();
   return node;
}
 

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

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

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

@Override
public List<?> extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   int count = stmt.columnCount();
   List<Object> results = new ArrayList<>(count);
   for (int i = 0; i < count; ++i) {
      results.add(stmt.columnValue(i));
   }

   return results;
}
 

@Override
public void row(SQLiteConnection conn, SQLiteStatement stmt) {
   try {
      O result = extractor.extract(conn, stmt);
      if (result != null) {
         results.add(result);
      }
   } catch (Exception ex) {
      log.debug("sql extractor failed: {}", ex.getMessage(), ex);
   }
}
 

@Override
public void row(SQLiteConnection conn, SQLiteStatement stmt) {
   if (result != null) {
      return;
   }

   try {
      result = extractor.extract(conn, stmt);
   } catch (Exception ex) {
      log.debug("sql extractor failed: {}", ex.getMessage(), ex);
   }
}
 

public SQLiteConnection getConnection() {
    if (localConnection == null) {
        localConnection = createNewConnection();
    }

    return localConnection;
}
 

private SQLiteConnection createConnection() {
   try {
      SQLiteConnection db = new SQLiteConnection(dbFile);
      db.openV2(
         SQLiteConstants.SQLITE_OPEN_READWRITE |
         SQLiteConstants.SQLITE_OPEN_CREATE |
         SQLiteConstants.SQLITE_OPEN_FULLMUTEX |
         SQLiteConstants.SQLITE_OPEN_PRIVATECACHE
      );
      return db;
   } catch (SQLiteException ex) {
      throw new DbException("could not connect to db", ex);
   }
}
 

@SuppressWarnings("null")
private static boolean configTableExists(Db db) {
   String name = db.query(CHECK_CONFIG_TABLE, new DbExtractor<String>() {
      @Override
      @Nullable
      public String extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
         return stmt.columnString(0);
      }
   });
   return name != null;
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, ZWNode node) throws Exception {
   int idx = 1;
   stmt.bind(idx++, node.getNodeId());
   stmt.bind(idx++, node.getBasicDeviceType());
   stmt.bind(idx++, node.getGenericDeviceType());
   stmt.bind(idx++, node.getSpecificDeviceType());
   stmt.bind(idx++, node.getManufacturerId());
   stmt.bind(idx++, node.getProductTypeId());
   stmt.bind(idx++, node.getProductId());
   stmt.bind(idx++, ByteUtils.byteArray2SpacedString(node.getCmdClassBytes()));
   stmt.bind(idx++, node.isOnline() ? 1 : 0);
   stmt.bind(idx++, node.getOfflineTimeout());
}
 

@Override
public AddressKeyValue extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   String addr = stmt.columnString(0);
   String key = stmt.columnString(1);
   String val = stmt.columnString(2);
   return new AddressKeyValue(addr,key,val);
}
 

@SuppressWarnings("null")
public static boolean configTableExists(Db db, String tableName) {
   String name = db.query(
         "SELECT name FROM sqlite_master WHERE type='table' AND name='" + tableName + "'",
      new DbExtractor<String>() {
         @Override
         @Nullable
         public String extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
            return stmt.columnString(0);
         }
      }
   );

   return name != null;
}
 

@SuppressWarnings("null")
private static boolean configTableExists(Db db) {
   String name = db.query(CHECK_CONFIG_TABLE, new DbExtractor<String>() {
      @Override
      @Nullable
      public String extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
         return stmt.columnString(0);
      }
   });
   return name != null;
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, TestPojo value) throws Exception {
   stmt.bind(1, value.key);
   stmt.bind(2, value.value1);
   stmt.bind(3, value.value2);
   stmt.bind(4, value.value3);
}
 

@Override
public KeyValuePair extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   String key = stmt.columnString(0);
   String val = stmt.columnString(1);
   Long lastUpdateTime = stmt.columnLong(2);
   Long lastReportTime = stmt.columnLong(3);

   ValueWithTime<String> vwt = new ValueWithTime(val, lastUpdateTime, lastReportTime);
   return new KeyValuePair(key,vwt);
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, BinaryLogDbData value) throws Exception {
   stmt.bind(1, value.key);
   stmt.bind(2, value.timestamp);
   stmt.bind(3, value.direction);
   stmt.bind(4, value.data);
}
 

@Override
@Nullable
public T extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   int index = 0;

   T tbr = type.newInstance();
   for (Field f : fields.keySet()) {
      f.set(tbr, SQLite3StatementBindInvoker.bind(stmt, f.getType(), index++));
   }

   return tbr;
}
 

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

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, T value) throws Exception {
   int index = 1;
   for (Map.Entry<Field,SQLField> entry : fields.entrySet()) {
      if (!entry.getValue().isPrimaryKey()) continue;

      Field f = entry.getKey();
      DbUtils.bind(stmt, f.get(value), index++);
   }
}
 

@Override
public SQLite3TableInfo extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   int cid = stmt.columnInt(0);
   String name = stmt.columnString(1);
   String type = stmt.columnString(2);
   String notnull = stmt.columnString(3);
   String defaultValue = stmt.columnString(4);
   int pk = stmt.columnInt(5);
   return new SQLite3TableInfo(cid,name,type,notnull,defaultValue,pk);
}
 

static void open(final SQLiteConnection conn, final boolean readOnly) throws SQLiteException {
    if (readOnly) {
        conn.openReadonly();
    } else {
        conn.open();
    }
    conn.setBusyTimeout(BUSY_TIMEOUT);
    conn.exec("PRAGMA foreign_keys=ON");
    conn.exec("PRAGMA journal_mode=WAL");
}
 

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