net.sqlcipher.database.SQLiteDatabase Java Examples

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

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

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

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

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

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

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

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

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

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

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

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

public int getMessageCountForThread(long threadId, long beforeTime) {
  SQLiteDatabase db = databaseHelper.getReadableDatabase();

  String[] cols  = new String[] {"COUNT(*)"};
  String   query = THREAD_ID + " = ? AND " + DATE_RECEIVED + " < ?";
  String[] args  = new String[]{String.valueOf(threadId), String.valueOf(beforeTime)};

  try (Cursor cursor = db.query(TABLE_NAME, cols, query, args, null, null, null)) {
    if (cursor != null && cursor.moveToFirst()) {
      return cursor.getInt(0);
    }
  }

  return 0;
}
 

private long getThreadId(@NonNull SQLiteDatabase db, long messageId) {
  String[] projection = new String[]{ THREAD_ID };
  String   query      = ID + " = ?";
  String[] args       = new String[]{ String.valueOf(messageId) };

  try (Cursor cursor = db.query(getTableName(), projection, query, args, null, null, null)) {
    if (cursor != null && cursor.moveToFirst()) {
      return cursor.getLong(cursor.getColumnIndexOrThrow(THREAD_ID));
    }
  }

  return -1;
}
 

private boolean upgradeTwelveThirteen(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        db.execSQL(DbUtil.addColumnToTable(
                FormDefRecord.STORAGE_KEY,
                FormDefRecord.META_RESOURCE_VERSION,
                "INTEGER"));

        SqlStorage<FormDefRecordV12> oldFormDefRecordStorage = new SqlStorage<>(
                FormDefRecord.STORAGE_KEY,
                FormDefRecordV12.class,
                new ConcreteAndroidDbHelper(context, db));

        SqlStorage<FormDefRecord> formDefRecordStorage = new SqlStorage<>(
                FormDefRecord.STORAGE_KEY,
                FormDefRecord.class,
                new ConcreteAndroidDbHelper(context, db));

        for (FormDefRecordV12 oldFormDefRecord : oldFormDefRecordStorage) {
            FormDefRecord formDefRecord = new FormDefRecord(oldFormDefRecord);
            formDefRecordStorage.update(oldFormDefRecord.getID(), formDefRecord);
        }

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

@Override
public final int delete(final Uri url, final String selection, final String[] selectionArgs) {

    int result = -1;

    if (getDBHelper() != null)
    {
        SQLiteDatabase db = getDBHelper().getWritableDatabase();

        if (db.isOpen()) //db can be closed if service sign out takes longer than app/cacheword lock
        {
            try {
                db.beginTransaction();
                result = deleteInternal(url, selection, selectionArgs);
                db.setTransactionSuccessful();
                db.endTransaction();
            }
            catch (Exception e)
            {
                //could not delete
            }

            if (result > 0) {
                getContext().getContentResolver()
                        .notifyChange(url, null /* observer */, false /* sync */);
            }
        }
    }

    return result;
}
 

@Override
public void markUnidentified(long id, boolean unidentified) {
  ContentValues contentValues = new ContentValues(1);
  contentValues.put(UNIDENTIFIED, unidentified ? 1 : 0);

  SQLiteDatabase db = databaseHelper.getWritableDatabase();
  db.update(TABLE_NAME, contentValues, ID_WHERE, new String[] {String.valueOf(id)});
}
 

final int getSecureMessageCount(long threadId) {
  SQLiteDatabase db           = databaseHelper.getReadableDatabase();
  String[]       projection   = new String[] {"COUNT(*)"};
  String         query        = getSecureMessageClause() + "AND " + MmsSmsColumns.THREAD_ID + " = ?";
  String[]       args         = new String[]{String.valueOf(threadId)};

  try (Cursor cursor = db.query(getTableName(), projection, query, args, null, null, null, null)) {
    if (cursor != null && cursor.moveToFirst()) {
      return cursor.getInt(0);
    } else {
      return 0;
    }
  }
}
 

public Long getThreadIdFor(@NonNull RecipientId recipientId) {
  SQLiteDatabase db            = databaseHelper.getReadableDatabase();
  String         where         = RECIPIENT_ID + " = ?";
  String[]       recipientsArg = new String[]{recipientId.serialize()};

  try (Cursor cursor = db.query(TABLE_NAME, new String[]{ ID }, where, recipientsArg, null, null, null)) {
    if (cursor != null && cursor.moveToFirst()) {
      return cursor.getLong(cursor.getColumnIndexOrThrow(ID));
    } else {
      return null;
    }
  }
}
 

void deleteMessagesInThreadBeforeDate(long threadId, long date) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();
  String where      = THREAD_ID + " = ? AND (CASE " + TYPE;

  for (long outgoingType : Types.OUTGOING_MESSAGE_TYPES) {
    where += " WHEN " + outgoingType + " THEN " + DATE_SENT + " < " + date;
  }

  where += (" ELSE " + DATE_RECEIVED + " < " + date + " END)");

  db.delete(TABLE_NAME, where, new String[] {threadId + ""});
}
 

protected SqlStorageIterator<T> getIndexSpanningIteratorOrNull(SQLiteDatabase db, boolean includeData) {
    //If we're just iterating over ID's, we may want to use a different, much
    //faster method depending on our stats. This method retrieves the
    //index records that _don't_ exist so we can assume the spans that
    //do.
    if (!includeData && STORAGE_OPTIMIZATIONS_ACTIVE) {

        SQLiteStatement min = db.compileStatement("SELECT MIN(" + DatabaseHelper.ID_COL + ") from " + table);

        SQLiteStatement max = db.compileStatement("SELECT MAX(" + DatabaseHelper.ID_COL + ") from " + table);

        SQLiteStatement count = db.compileStatement("SELECT COUNT(" + DatabaseHelper.ID_COL + ") from " + table);

        int minValue = (int)min.simpleQueryForLong();
        int maxValue = (int)max.simpleQueryForLong() + 1;
        int countValue = (int)count.simpleQueryForLong();

        min.close();
        max.close();
        count.close();

        double density = countValue / (maxValue - minValue * 1.0);

        //Ok, so basic metrics:
        //1) Only use a covering iterator if the number of records is > 1k
        //2) Only use a covering iterator if the number of records is less than 100k (vital, hard limit)
        //3) Only use a covering iterator if the record density is 50% or more
        if (countValue > 1000 &&
                countValue < 100000 &&
                density >= 0.5) {
            return getCoveringIndexIterator(db, minValue, maxValue, countValue);
        }
    }
    return null;
}
 

@Override
public void update(int id, Externalizable e) {
    SQLiteDatabase db = helper.getHandle();
    db.beginTransaction();
    try {
        db.update(table, helper.getContentValues(e), DatabaseHelper.ID_COL + "=?", new String[]{String.valueOf(id)});
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}
 

private void insertConstraintSpecs(@NonNull SQLiteDatabase db, @NonNull List<ConstraintSpec> constraints) {
  for (ConstraintSpec constraintSpec : constraints) {
    ContentValues contentValues = new ContentValues();
    contentValues.put(Constraints.JOB_SPEC_ID, constraintSpec.getJobSpecId());
    contentValues.put(Constraints.FACTORY_KEY, constraintSpec.getFactoryKey());
    db.insertWithOnConflict(Constraints.TABLE_NAME, null ,contentValues, SQLiteDatabase.CONFLICT_IGNORE);
  }
}
 

public @NonNull Cursor getGalleryMediaForThread(long threadId, @NonNull Sorting sorting) {
  SQLiteDatabase database = databaseHelper.getReadableDatabase();
  String         query    = sorting.applyToQuery(applyEqualityOperator(threadId, GALLERY_MEDIA_QUERY));
  String[]       args     = {threadId + ""};
  Cursor         cursor   = database.rawQuery(query, args);
  setNotifyConverationListeners(cursor, threadId);
  return cursor;
}
 

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

/**
 * Make a copy of the incoming sandbox's database and re-key it to use the new key.
 */
private static String rekeyDB(Context c, UserKeyRecord incomingSandbox, UserKeyRecord newSandbox,
                              byte[] unwrappedOldKey, byte[] unwrappedNewKey)
        throws IOException {
    File oldDb = c.getDatabasePath(DatabaseUserOpenHelper.getDbName(incomingSandbox.getUuid()));
    File newDb = c.getDatabasePath(DatabaseUserOpenHelper.getDbName(newSandbox.getUuid()));

    //TODO: Make sure old sandbox is already on newest version?
    if (newDb.exists()) {
        if (!newDb.delete()) {
            throw new IOException("Couldn't clear file location " + newDb.getAbsolutePath() + " for new sandbox database");
        }
    }

    FileUtil.copyFile(oldDb, newDb);

    Logger.log(LogTypes.TYPE_MAINTENANCE, "Created a copy of the DB for the new sandbox. Re-keying it...");

    String oldKeyEncoded = getSqlCipherEncodedKey(unwrappedOldKey);
    String newKeyEncoded = getSqlCipherEncodedKey(unwrappedNewKey);
    SQLiteDatabase rawDbHandle = SQLiteDatabase.openDatabase(newDb.getAbsolutePath(), oldKeyEncoded, null, SQLiteDatabase.OPEN_READWRITE);

    rawDbHandle.execSQL("PRAGMA key = '" + oldKeyEncoded + "';");
    rawDbHandle.execSQL("PRAGMA rekey  = '" + newKeyEncoded + "';");
    rawDbHandle.close();
    return newKeyEncoded;
}