Java Code Examples for net.sqlcipher.database.SQLiteDatabase

public Set<String> getAllPhoneNumbers() {
  SQLiteDatabase db      = databaseHelper.getReadableDatabase();
  Set<String>    results = new HashSet<>();

  try (Cursor cursor = db.query(TABLE_NAME, new String[] { PHONE }, null, null, null, null, null)) {
    while (cursor != null && cursor.moveToNext()) {
      String number = cursor.getString(cursor.getColumnIndexOrThrow(PHONE));

      if (!TextUtils.isEmpty(number)) {
        results.add(number);
      }
    }
  }

  return results;
}
 

@Override
public void remove(int id) {
    SQLiteDatabase db = getDbOrThrow();

    String filename = HybridFileBackedSqlHelpers.getEntryFilename(helper, table, id);
    db.beginTransaction();
    try {
        db.delete(table, DatabaseHelper.ID_COL + "=?", new String[]{String.valueOf(id)});
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

    if (filename != null) {
        File dataFile = new File(filename);
        dataFile.delete();
    }
}
 

public long getThreadIdForMessage(long id) {
  String sql        = "SELECT " + THREAD_ID + " FROM " + TABLE_NAME + " WHERE " + ID + " = ?";
  String[] sqlArgs  = new String[] {id+""};
  SQLiteDatabase db = databaseHelper.getReadableDatabase();

  Cursor cursor = null;

  try {
    cursor = db.rawQuery(sql, sqlArgs);
    if (cursor != null && cursor.moveToFirst())
      return cursor.getLong(0);
    else
      return -1;
  } finally {
    if (cursor != null)
      cursor.close();
  }
}
 

private void deleteStickersInPack(@NonNull SQLiteDatabase db, @NonNull String packId) {
  String   selection = PACK_ID + " = ?";
  String[] args      = new String[] { packId };

  db.beginTransaction();

  try {
    try (Cursor cursor = db.query(TABLE_NAME, null, selection, args, null, null, null)) {
      while (cursor != null && cursor.moveToNext()) {
        String  filePath = cursor.getString(cursor.getColumnIndexOrThrow(FILE_PATH));
        long    rowId    = cursor.getLong(cursor.getColumnIndexOrThrow(_ID));

        deleteSticker(db, rowId, filePath);
      }
    }

    db.setTransactionSuccessful();
  } finally {
    db.endTransaction();
  }

  db.delete(TABLE_NAME, selection, args);
}
 

private String readInput(InputStream stream, AndroidTransactionParserFactory factory)
        throws InvalidStructureException, IOException, XmlPullParserException,
        UnfullfilledRequirementsException {
    initParsers(factory);
    //this is _really_ coupled, but we'll tolerate it for now because of the absurd performance gains
    SQLiteDatabase db = CommCareApplication.instance().getUserDbHandle();
    db.beginTransaction();
    try {
        parseStream(stream, factory);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

    //Return the sync token ID
    return factory.getSyncToken();
}
 

void deleteThreads(Set<Long> threadIds) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();
  String where      = "";
  Cursor cursor     = null;

  for (long threadId : threadIds) {
    where += THREAD_ID + " = '" + threadId + "' OR ";
  }

  where = where.substring(0, where.length() - 4);

  try {
    cursor = db.query(TABLE_NAME, new String[] {ID}, where, null, null, null, null);

    while (cursor != null && cursor.moveToNext()) {
      delete(cursor.getLong(0));
    }

  } finally {
    if (cursor != null)
      cursor.close();
  }
}
 

public synchronized void deleteJobs(@NonNull List<String> jobIds) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();

  db.beginTransaction();

  try {
    for (String jobId : jobIds) {
      String[] arg = new String[]{jobId};

      db.delete(Jobs.TABLE_NAME, Jobs.JOB_SPEC_ID + " = ?", arg);
      db.delete(Constraints.TABLE_NAME, Constraints.JOB_SPEC_ID + " = ?", arg);
      db.delete(Dependencies.TABLE_NAME, Dependencies.JOB_SPEC_ID + " = ?", arg);
      db.delete(Dependencies.TABLE_NAME, Dependencies.DEPENDS_ON_JOB_SPEC_ID + " = ?", arg);
    }

    db.setTransactionSuccessful();
  } finally {
    db.endTransaction();
  }
}
 

private void insertJobSpec(@NonNull SQLiteDatabase db, @NonNull JobSpec job) {
  ContentValues contentValues = new ContentValues();
  contentValues.put(Jobs.JOB_SPEC_ID, job.getId());
  contentValues.put(Jobs.FACTORY_KEY, job.getFactoryKey());
  contentValues.put(Jobs.QUEUE_KEY, job.getQueueKey());
  contentValues.put(Jobs.CREATE_TIME, job.getCreateTime());
  contentValues.put(Jobs.NEXT_RUN_ATTEMPT_TIME, job.getNextRunAttemptTime());
  contentValues.put(Jobs.RUN_ATTEMPT, job.getRunAttempt());
  contentValues.put(Jobs.MAX_ATTEMPTS, job.getMaxAttempts());
  contentValues.put(Jobs.MAX_BACKOFF, job.getMaxBackoff());
  contentValues.put(Jobs.MAX_INSTANCES, job.getMaxInstances());
  contentValues.put(Jobs.LIFESPAN, job.getLifespan());
  contentValues.put(Jobs.SERIALIZED_DATA, job.getSerializedData());
  contentValues.put(Jobs.SERIALIZED_INPUT_DATA, job.getSerializedInputData());
  contentValues.put(Jobs.IS_RUNNING, job.isRunning() ? 1 : 0);

  db.insertWithOnConflict(Jobs.TABLE_NAME, null, contentValues, SQLiteDatabase.CONFLICT_IGNORE);
}
 

@WorkerThread
public boolean isCurrentMember(@NonNull GroupId.Push groupId, @NonNull RecipientId recipientId) {
  SQLiteDatabase database = databaseHelper.getReadableDatabase();

  try (Cursor cursor = database.query(TABLE_NAME, new String[] {MEMBERS},
                                      GROUP_ID + " = ?", new String[] {groupId.toString()},
                                      null, null, null))
  {
    if (cursor.moveToNext()) {
      String serializedMembers = cursor.getString(cursor.getColumnIndexOrThrow(MEMBERS));
      return RecipientId.serializedListContains(serializedMembers, recipientId);
    } else {
      return false;
    }
  }
}
 

public SQLiteDatabase getWritableDatabase() {
    if (dbWrite == null)
        dbWrite = super.getWritableDatabase(mKey);

    /*
    if (doCleanup)
    {
        //clean up orphaned contacts
        dbWrite.execSQL("DELETE FROM " + TABLE_CONTACTS + " WHERE " + TABLE_CONTACTS + '.' + Contacts._ID +
                " IN (Select " + TABLE_CONTACTS + '.' + Contacts._ID + " from " + TABLE_CONTACTS + " LEFT JOIN " + TABLE_PROVIDERS + " ON " + Contacts.PROVIDER + "=" + TABLE_PROVIDERS + "." + Provider._ID + " WHERE " + TABLE_PROVIDERS + '.' + Provider._ID + " IS NULL)");
        
        
        dbWrite.execSQL("DELETE FROM " + TABLE_CONTACTS + " WHERE " + TABLE_CONTACTS + '.' + Contacts._ID + " NOT IN ("
                + "SELECT min(" + Contacts._ID + ") FROM " + TABLE_CONTACTS + " group by " + Contacts.USERNAME + "," + Contacts.PROVIDER + "," + Contacts.ACCOUNT                        
                + ")");
        
        
        
        doCleanup = false;
    }
    */
    
    return dbWrite;
}
 

@SuppressWarnings("ResultOfMethodCallIgnored")
public void deleteAttachmentsForMessage(long mmsId) {
  Log.d(TAG, "[deleteAttachmentsForMessage] mmsId: " + mmsId);

  SQLiteDatabase database = databaseHelper.getWritableDatabase();
  Cursor cursor           = null;

  try {
    cursor = database.query(TABLE_NAME, new String[] {DATA, THUMBNAIL, CONTENT_TYPE, ROW_ID, UNIQUE_ID}, MMS_ID + " = ?",
                            new String[] {mmsId+""}, null, null, null);

    while (cursor != null && cursor.moveToNext()) {
      deleteAttachmentOnDisk(cursor.getString(cursor.getColumnIndex(DATA)),
                             cursor.getString(cursor.getColumnIndex(THUMBNAIL)),
                             cursor.getString(cursor.getColumnIndex(CONTENT_TYPE)),
                             new AttachmentId(cursor.getLong(cursor.getColumnIndex(ROW_ID)),
                                              cursor.getLong(cursor.getColumnIndex(UNIQUE_ID))));
    }
  } finally {
    if (cursor != null)
      cursor.close();
  }

  database.delete(TABLE_NAME, MMS_ID + " = ?", new String[] {mmsId + ""});
  notifyAttachmentListeners();
}
 

protected <D extends Document<I>, I> void removeFromDocument(long messageId, String column, I object, Class<D> clazz) throws IOException {
  SQLiteDatabase database = databaseHelper.getWritableDatabase();
  database.beginTransaction();

  try {
    D           document = getDocument(database, messageId, column, clazz);
    Iterator<I> iterator = document.getList().iterator();

    while (iterator.hasNext()) {
      I item = iterator.next();

      if (item.equals(object)) {
        iterator.remove();
        break;
      }
    }

    setDocument(database, messageId, column, document);
    database.setTransactionSuccessful();
  } finally {
    database.endTransaction();
  }
}
 

public Vector<Integer> removeAll(Vector<Integer> toRemove) {
    if (toRemove.size() == 0) {
        return toRemove;
    }

    List<Pair<String, String[]>> whereParamList = TableBuilder.sqlList(toRemove);

    SQLiteDatabase db = helper.getHandle();
    db.beginTransaction();
    try {
        for (Pair<String, String[]> whereParams : whereParamList) {
            db.delete(table, DatabaseHelper.ID_COL + " IN " + whereParams.first, whereParams.second);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

    return toRemove;
}
 

public void setForcedUnread(@NonNull Collection<Long> threadIds) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();

  db.beginTransaction();
  try {
    ContentValues contentValues = new ContentValues();
    contentValues.put(READ, ReadStatus.FORCED_UNREAD.serialize());

    for (long threadId : threadIds) {
      db.update(TABLE_NAME, contentValues, ID_WHERE, new String[] { String.valueOf(threadId) });
    }

    db.setTransactionSuccessful();
  } finally {
    db.endTransaction();
  }

  notifyConversationListListeners();
}
 

public @Nullable SessionRecord load(@NonNull RecipientId recipientId, int deviceId) {
  SQLiteDatabase database = databaseHelper.getReadableDatabase();

  try (Cursor cursor = database.query(TABLE_NAME, new String[]{RECORD},
                                      RECIPIENT_ID + " = ? AND " + DEVICE + " = ?",
                                      new String[] {recipientId.serialize(), String.valueOf(deviceId)},
                                      null, null, null))
  {
    if (cursor != null && cursor.moveToFirst()) {
      try {
        return new SessionRecord(cursor.getBlob(cursor.getColumnIndexOrThrow(RECORD)));
      } catch (IOException e) {
        Log.w(TAG, e);
      }
    }
  }

  return null;
}
 

public void updateStorageKeys(@NonNull Map<RecipientId, byte[]> keys) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();
  db.beginTransaction();

  try {
    for (Map.Entry<RecipientId, byte[]> entry : keys.entrySet()) {
      ContentValues values = new ContentValues();
      values.put(STORAGE_SERVICE_ID, Base64.encodeBytes(entry.getValue()));
      db.update(TABLE_NAME, values, ID_WHERE, new String[] { entry.getKey().serialize() });
    }

    db.setTransactionSuccessful();
  } finally {
    db.endTransaction();
  }
}
 

@Override
public void onCreate(SQLiteDatabase db) {
  db.execSQL(SmsDatabase.CREATE_TABLE);
  db.execSQL(MmsDatabase.CREATE_TABLE);
  db.execSQL(AttachmentDatabase.CREATE_TABLE);
  db.execSQL(ThreadDatabase.CREATE_TABLE);
  db.execSQL(IdentityDatabase.CREATE_TABLE);
  db.execSQL(DraftDatabase.CREATE_TABLE);
  db.execSQL(PushDatabase.CREATE_TABLE);
  db.execSQL(GroupDatabase.CREATE_TABLE);
  db.execSQL(RecipientDatabase.CREATE_TABLE);
  db.execSQL(GroupReceiptDatabase.CREATE_TABLE);
  db.execSQL(OneTimePreKeyDatabase.CREATE_TABLE);
  db.execSQL(SignedPreKeyDatabase.CREATE_TABLE);
  db.execSQL(SessionDatabase.CREATE_TABLE);
  db.execSQL(StickerDatabase.CREATE_TABLE);
  db.execSQL(StorageKeyDatabase.CREATE_TABLE);
  db.execSQL(KeyValueDatabase.CREATE_TABLE);
  db.execSQL(MegaphoneDatabase.CREATE_TABLE);
  executeStatements(db, SearchDatabase.CREATE_TABLE);
  executeStatements(db, JobDatabase.CREATE_TABLE);

  executeStatements(db, RecipientDatabase.CREATE_INDEXS);
  executeStatements(db, SmsDatabase.CREATE_INDEXS);
  executeStatements(db, MmsDatabase.CREATE_INDEXS);
  executeStatements(db, AttachmentDatabase.CREATE_INDEXS);
  executeStatements(db, ThreadDatabase.CREATE_INDEXS);
  executeStatements(db, DraftDatabase.CREATE_INDEXS);
  executeStatements(db, GroupDatabase.CREATE_INDEXS);
  executeStatements(db, GroupReceiptDatabase.CREATE_INDEXES);
  executeStatements(db, StickerDatabase.CREATE_INDEXES);
  executeStatements(db, StorageKeyDatabase.CREATE_INDEXES);
}
 

private boolean upgradeEightNine(SQLiteDatabase db) {
    Log.d(TAG, "starting user fixture migration");

    FixtureSerializationMigration.stageFixtureTables(db);

    boolean didFixturesMigrate =
            FixtureSerializationMigration.migrateFixtureDbBytes(db, c, userKeyRecordId, fileMigrationKey);

    FixtureSerializationMigration.dropTempFixtureTable(db);
    return didFixturesMigrate;
}
 

private static boolean isTableExist(SQLiteDatabase db, String table) {
    Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + table + "'", null);
    if (cursor != null) {
        if (cursor.getCount() > 0) {
            cursor.close();
            return true;
        }
        cursor.close();
    }
    return false;
}
 

private void updateOrCreateGroupMembersTable(SQLiteDatabase db, String cpDbName) {
    db.execSQL("CREATE TABLE IF NOT EXISTS " + cpDbName + TABLE_GROUP_MEMBERS + " ("
                + "_id INTEGER PRIMARY KEY," + "groupId INTEGER," + "username TEXT,"
                + "nickname TEXT," + "role TEXT," + "affiliation TEXT" + ");");
    if (!columnExists(db, cpDbName + TABLE_GROUP_MEMBERS, "role")) {
        // Role not added, add that together with affiliation!
        db.execSQL("ALTER TABLE " + cpDbName + TABLE_GROUP_MEMBERS + " ADD COLUMN role TEXT");
        db.execSQL("ALTER TABLE " + cpDbName + TABLE_GROUP_MEMBERS + " ADD COLUMN affiliation TEXT");
    }
}
 

private static boolean isIdUsed(@NonNull SQLiteDatabase db, @NonNull String tableName, @NonNull String columnName, String id) {
  try (Cursor cursor = db.query(tableName, new String[] { columnName }, columnName + " = ?", new String[] { id }, null, null, null, "1")) {
    boolean used = cursor != null && cursor.moveToFirst();
    if (used) {
      Log.i(TAG, "Recipient " + id + " was used in (" + tableName + ", " + columnName + ")");
    }
    return used;
  }
}
 

public void uninstallPack(@NonNull String packId) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();

  db.beginTransaction();
  try {
    updatePackInstalled(db, packId, false, false);
    deleteStickersInPackExceptCover(db, packId);

    db.setTransactionSuccessful();
    notifyStickerPackListeners();
    notifyStickerListeners();
  } finally {
    db.endTransaction();
  }
}
 

public @NonNull RecipientSettings getRecipientSettings(@NonNull RecipientId id) {
  SQLiteDatabase database = databaseHelper.getReadableDatabase();
  String         table    = TABLE_NAME + " LEFT OUTER JOIN " + IdentityDatabase.TABLE_NAME + " ON " + TABLE_NAME + "." + ID + " = " + IdentityDatabase.TABLE_NAME + "." + IdentityDatabase.RECIPIENT_ID;
  String         query    = TABLE_NAME + "." + ID + " = ?";
  String[]       args     = new String[] { id.serialize() };

  try (Cursor cursor = database.query(table, RECIPIENT_FULL_PROJECTION, query, args, null, null, null)) {
    if (cursor != null && cursor.moveToNext()) {
      return getRecipientSettings(context, cursor);
    } else {
      throw new MissingRecipientException(id);
    }
  }
}
 

synchronized SupportSQLiteDatabase getWritableSupportDatabase(byte[] passphrase) {
	migrated = false;

	SQLiteDatabase db = super.getWritableDatabase(passphrase);

	if (migrated) {
		close();
		return getWritableSupportDatabase(passphrase);
	}

	return getWrappedDb(db);
}
 

public void markDownloadState(long messageId, long state) {
  SQLiteDatabase database     = databaseHelper.getWritableDatabase();
  ContentValues contentValues = new ContentValues();
  contentValues.put(STATUS, state);

  database.update(TABLE_NAME, contentValues, ID_WHERE, new String[] {messageId + ""});
  notifyConversationListeners(getThreadIdForMessage(messageId));
}
 

public boolean updatePerson(Integer id, String firstName, String lastName, String address, float mileage) {
    SQLiteDatabase db = this.getWritableDatabase(DB_PASSWORD);
    ContentValues contentValues = new ContentValues();
    contentValues.put("first_name", firstName);
    contentValues.put("last_name", lastName);
    contentValues.put("address", address);
    db.update("person", contentValues, "id = ? ", new String[]{Integer.toString(id)});
    db.close();
    return true;
}
 

/**
 * Creates a custom iterator for this storage which can either include or exclude the actual data.
 * Useful for getting an overview of data for querying into without wasting the bits to transfer over
 * the huge full records.
 *
 * @param includeData True to return an iterator with all records. False to return only the index.
 */
@Override
public SqlStorageIterator<T> iterate(boolean includeData) {
    SQLiteDatabase db = helper.getHandle();

    SqlStorageIterator<T> spanningIterator = getIndexSpanningIteratorOrNull(db, includeData);
    if (spanningIterator != null) {
        return spanningIterator;
    } else {
        return new SqlStorageIterator<>(getIterateCursor(db, includeData), this);
    }
}
 

private List<MarkedMessageInfo> setMessagesRead(String where, String[] arguments) {
  SQLiteDatabase          database = databaseHelper.getWritableDatabase();
  List<MarkedMessageInfo> result   = new LinkedList<>();
  Cursor                  cursor   = null;

  database.beginTransaction();

  try {
    cursor = database.query(TABLE_NAME, new String[] {ID, RECIPIENT_ID, DATE_SENT, MESSAGE_BOX, EXPIRES_IN, EXPIRE_STARTED, THREAD_ID}, where, arguments, null, null, null);

    while(cursor != null && cursor.moveToNext()) {
      if (Types.isSecureType(cursor.getLong(cursor.getColumnIndex(MESSAGE_BOX)))) {
        long           threadId       = cursor.getLong(cursor.getColumnIndex(THREAD_ID));
        RecipientId    recipientId    = RecipientId.from(cursor.getLong(cursor.getColumnIndex(RECIPIENT_ID)));
        long           dateSent       = cursor.getLong(cursor.getColumnIndex(DATE_SENT));
        long           messageId      = cursor.getLong(cursor.getColumnIndex(ID));
        long           expiresIn      = cursor.getLong(cursor.getColumnIndex(EXPIRES_IN));
        long           expireStarted  = cursor.getLong(cursor.getColumnIndex(EXPIRE_STARTED));
        SyncMessageId  syncMessageId  = new SyncMessageId(recipientId, dateSent);
        ExpirationInfo expirationInfo = new ExpirationInfo(messageId, expiresIn, expireStarted, true);

        result.add(new MarkedMessageInfo(threadId, syncMessageId, expirationInfo));
      }
    }

    ContentValues contentValues = new ContentValues();
    contentValues.put(READ, 1);

    database.update(TABLE_NAME, contentValues, where, arguments);
    database.setTransactionSuccessful();
  } finally {
    if (cursor != null) cursor.close();
    database.endTransaction();
  }

  return result;
}
 

public static void createNumbersTable(SQLiteDatabase db) {
    //Virtual Table
    String dropStatement = "DROP TABLE IF EXISTS integers;";
    db.execSQL(dropStatement);
    String createStatement = "CREATE TABLE integers (i INTEGER);";
    db.execSQL(createStatement);

    for (long i = 0; i < 10; ++i) {
        db.execSQL("INSERT INTO integers VALUES (" + i + ");");
    }
}
 

synchronized Database getWrappedDb(SQLiteDatabase db) {
	Database wrappedDb = dbRef[0];

	if (wrappedDb == null) {
		wrappedDb = new Database(db);
		dbRef[0] = wrappedDb;
	}

	return (dbRef[0]);
}