Java Code Examples for com.almworks.sqlite4java.SQLiteConnection

The following examples show how to use com.almworks.sqlite4java.SQLiteConnection. 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: mrgeo   Source File: MbVectorTilesDataProvider.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 2
Source Project: arcusplatform   Source File: Extractors.java    License: Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example 3
Source Project: arcusplatform   Source 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 4
Source Project: sync-android   Source File: SQLiteWrapperUtils.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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);
    }
}
 
Example 5
Source Project: sync-android   Source File: SQLiteWrapperUtils.java    License: Apache License 2.0 6 votes vote down vote up
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);
    }
}
 
Example 6
Source Project: AttestationServer   Source 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 7
Source Project: AttestationServer   Source 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 8
Source Project: arcusplatform   Source File: TestDbService.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 9
Source Project: arcusplatform   Source File: TestDbService.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public TestPojo extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   TestPojo result = new TestPojo();
   result.key = stmt.columnString(0);
   result.value1 = stmt.columnLong(1);
   result.value2 = stmt.columnDouble(2);

   byte[] value3 = stmt.columnBlob(3);
   if (value3 == null) {
      value3 = new byte[0];
   }

   result.value3 = value3;
   return result;
}
 
Example 10
Source Project: arcusplatform   Source File: ReflexDao.java    License: Apache License 2.0 5 votes vote down vote up
@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;
}
 
Example 11
Source Project: arcusplatform   Source File: ReflexDao.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 12
Source Project: arcusplatform   Source File: Binders.java    License: Apache License 2.0 5 votes vote down vote up
@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());
}
 
Example 13
Source Project: arcusplatform   Source File: ZWDao.java    License: Apache License 2.0 5 votes vote down vote up
static boolean configTableExists(Db db) {
   String name = db.query(CHECK_CONFIG_TABLE, new DbExtractor<String>() {
      
      @Override
      public String extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
         return stmt.columnString(0);
      }
   });

   return name != null;
}
 
Example 14
Source Project: sync-android   Source File: SQLiteWrapper.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void close() {
    // it's not possible to call dispose from other threads
    // so the best we can do is call dispose on the connection
    // for the same thread as us
    SQLiteConnection conn = localConnection;
    if (conn != null && !conn.isDisposed()) {
        conn.dispose();
    }
}
 
Example 15
Source Project: arcusplatform   Source File: Db.java    License: Apache License 2.0 5 votes vote down vote up
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);
   }
}
 
Example 16
Source Project: mrgeo   Source 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 17
Source Project: arcusplatform   Source File: Db.java    License: Apache License 2.0 5 votes vote down vote up
@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);
   }
}
 
Example 18
Source Project: arcusplatform   Source File: Db.java    License: Apache License 2.0 5 votes vote down vote up
@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);
   }
}
 
Example 19
Source Project: arcusplatform   Source File: Db.java    License: Apache License 2.0 5 votes vote down vote up
@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;
}
 
Example 20
Source Project: arcusplatform   Source File: Db.java    License: Apache License 2.0 5 votes vote down vote up
@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...");
   }
}
 
Example 21
Source Project: arcusplatform   Source File: BackupUtils.java    License: Apache License 2.0 5 votes vote down vote up
@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;
}
 
Example 22
Source Project: arcusplatform   Source File: ConfigService.java    License: Apache License 2.0 5 votes vote down vote up
@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;
}
 
Example 23
Source Project: sync-android   Source File: SQLiteWrapper.java    License: Apache License 2.0 5 votes vote down vote up
public SQLiteConnection getConnection() {
    if (localConnection == null) {
        localConnection = createNewConnection();
    }

    return localConnection;
}
 
Example 24
Source Project: arcusplatform   Source File: ConfigService.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 25
Source Project: arcusplatform   Source File: BinaryLogger.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 26
Source Project: arcusplatform   Source File: SQLite3Table.java    License: Apache License 2.0 5 votes vote down vote up
@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;
}
 
Example 27
Source Project: sync-android   Source File: SQLiteWrapperUtils.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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);
    }
}
 
Example 28
Source Project: arcusplatform   Source File: SQLite3Table.java    License: Apache License 2.0 5 votes vote down vote up
@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++);
   }
}
 
Example 29
Source Project: arcusplatform   Source File: SQLite3Table.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 30
Source Project: AttestationServer   Source File: AttestationServer.java    License: MIT License 5 votes vote down vote up
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");
}