Java Code Examples for net.sqlcipher.database.SQLiteDatabase#execSQL()

/**
 * Add index on owner ID to case db
 */
private boolean upgradeSeventeenEighteen(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        db.execSQL(DbUtil.addColumnToTable(
                ACase.STORAGE_KEY,
                "owner_id",
                "TEXT"));

        SqlStorage<ACase> caseStorage = new SqlStorage<>(ACase.STORAGE_KEY, ACasePreV24Model.class,
                new ConcreteAndroidDbHelper(c, db));
        updateModels(caseStorage);

        db.execSQL(DatabaseIndexingUtils.indexOnTableCommand(
                "case_owner_id_index", "AndroidCase", "owner_id"));
        db.setTransactionSuccessful();
        return true;
    } finally {
        db.endTransaction();
    }
}
 

/**
 * Create temporary upgrade table. Used to check for new updates without
 * wiping progress from the main upgrade table
 */
private boolean upgradeFiveSix(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        TableBuilder builder = new TableBuilder(TEMP_UPGRADE_TABLE_KEY);
        builder.addData(new ResourceV13());
        db.execSQL(builder.getTableCreateString());
        String tableCmd =
                DatabaseAppOpenHelper.indexOnTableWithPGUIDCommand("temp_upgrade_index_id",
                        TEMP_UPGRADE_TABLE_KEY);
        db.execSQL(tableCmd);

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

private static boolean addTableForNewModel(SQLiteDatabase db, String storageKey,
                                           Persistable modelToAdd) {
    db.beginTransaction();
    try {
        TableBuilder builder = new TableBuilder(storageKey);
        builder.addData(modelToAdd);
        db.execSQL(builder.getTableCreateString());

        db.setTransactionSuccessful();
        return true;
    } catch (Exception e) {
        return false;
    } finally {
        db.endTransaction();
    }
}
 

private boolean upgradeTwelveThirteen(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        TableBuilder builder = new TableBuilder(AndroidLogEntry.STORAGE_KEY);
        builder.addData(new AndroidLogEntry());
        db.execSQL(builder.getTableCreateString());

        builder = new TableBuilder(ForceCloseLogEntry.STORAGE_KEY);
        builder.addData(new ForceCloseLogEntry());
        db.execSQL(builder.getTableCreateString());

        db.setTransactionSuccessful();
        return true;
    } catch (Exception e) {
        return false;
    } finally {
        db.endTransaction();
    }
}
 

public static void createIndexes(SQLiteDatabase db) {
    String recordFirstIndexId = "RECORD_NAME_ID_TARGET";
    String recordFirstIndex = COL_CASE_RECORD_ID + ", " + COL_INDEX_NAME + ", " + COL_INDEX_TARGET;
    db.execSQL(DatabaseIndexingUtils.indexOnTableCommand(recordFirstIndexId, TABLE_NAME, recordFirstIndex));

    String typeFirstIndexId = "NAME_TARGET_RECORD";
    String typeFirstIndex = COL_INDEX_NAME + ", " + COL_CASE_RECORD_ID + ", " + COL_INDEX_TARGET;
    db.execSQL(DatabaseIndexingUtils.indexOnTableCommand(typeFirstIndexId, TABLE_NAME, typeFirstIndex));
}
 

private boolean upgradeElevenTwelve(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        db.execSQL(new TableBuilder(RecoveryMeasure.class).getTableCreateString());
        db.setTransactionSuccessful();
        return true;
    } finally {
        db.endTransaction();
    }
}
 

private boolean upgradeTwentyOneTwentyTwo(SQLiteDatabase db) {
    //drop the existing table and recreate using current definition
    db.beginTransaction();
    try {
        db.execSQL("DROP TABLE IF EXISTS " + EntityStorageCache.TABLE_NAME);
        db.execSQL(EntityStorageCache.getTableDefinition());
        db.setTransactionSuccessful();
        return true;
    } finally {
        db.endTransaction();
    }
}
 

private Pair<Long, Long> updateMessageBodyAndType(long messageId, String body, long maskOff, long maskOn) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();
  db.execSQL("UPDATE " + TABLE_NAME + " SET " + BODY + " = ?, " +
             MESSAGE_BOX + " = (" + MESSAGE_BOX + " & " + (Types.TOTAL_MASK - maskOff) + " | " + maskOn + ") " +
             "WHERE " + ID + " = ?",
             new String[] {body, messageId + ""});

  long threadId = getThreadIdForMessage(messageId);

  DatabaseFactory.getThreadDatabase(context).update(threadId, true);
  notifyConversationListeners(threadId);
  notifyConversationListListeners();

  return new Pair<>(messageId, threadId);
}
 

public void incrementUnread(long threadId, int amount) {
  SQLiteDatabase db = databaseHelper.getWritableDatabase();
  db.execSQL("UPDATE " + TABLE_NAME + " SET " + READ + " = " + ReadStatus.UNREAD.serialize() + ", " +
                 UNREAD_COUNT + " = " + UNREAD_COUNT + " + ? WHERE " + ID + " = ?",
             new String[] {String.valueOf(amount),
                           String.valueOf(threadId)});
}
 

private boolean upgradeTwoThree(SQLiteDatabase db) {
    db.beginTransaction();
    try {
        TableBuilder builder = new TableBuilder("RECOVERY_RESOURCE_TABLE");
        builder.addData(new ResourceV13());
        db.execSQL(builder.getTableCreateString());
        db.setTransactionSuccessful();
        return true;
    } finally {
        db.endTransaction();
    }
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "create table " + TABLE_NAME + " ( " + BaseColumns._ID
            + " integer primary key autoincrement, " + "no integer, "
            + "name text not null);";
    db.execSQL(sql);
}
 

private static void dropAllTables(@NonNull SQLiteDatabase db) {
  try (Cursor cursor = db.rawQuery("SELECT name, type FROM sqlite_master", null)) {
    while (cursor != null && cursor.moveToNext()) {
      String name = cursor.getString(0);
      String type = cursor.getString(1);

      if ("table".equals(type) && !name.startsWith("sqlite_")) {
        db.execSQL("DROP TABLE IF EXISTS " + name);
      }
    }
  }
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(
            "create table person " +
                    "(id integer primary key, first_name text, last_name text, address text)"
    );
}
 

public static void onCreate(SQLiteDatabase database) 
{
  database.execSQL(DATABASE_CREATE);
}
 

public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) 
{
  Log.w(MessagesTable.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion  + ", which will destroy all old data");
  database.execSQL("DROP TABLE IF EXISTS " + TABLE_PUBKEYS);
  onCreate(database);
}
 

@Override
public void onCreate(SQLiteDatabase db) {

        log("DatabaseHelper.onCreate");

    db.execSQL("CREATE TABLE " + TABLE_PROVIDERS + " (" + "_id INTEGER PRIMARY KEY,"
               + "name TEXT," + // eg AIM
               "fullname TEXT," + // eg AOL Instance Messenger
               "category TEXT," + // a category used for forming intent
               "signup_url TEXT" + // web url to visit to create a new account
               ");");

    db.execSQL("CREATE TABLE " + TABLE_ACCOUNTS + " (" + "_id INTEGER PRIMARY KEY,"
               + "name TEXT," + "provider INTEGER," + "username TEXT," + "pw TEXT,"
               + "active INTEGER NOT NULL DEFAULT 0,"
               + "locked INTEGER NOT NULL DEFAULT 0,"
               + "keep_signed_in INTEGER NOT NULL DEFAULT 0,"
               + "last_login_state INTEGER NOT NULL DEFAULT 0,"
               + "UNIQUE (provider, username)" + ");");

    createContactsTables(db);
    createMessageChatTables(db, true /* create show_ts column */);

    db.execSQL("CREATE TABLE " + TABLE_AVATARS + " (" + "_id INTEGER PRIMARY KEY,"
               + "contact TEXT," + "provider_id INTEGER," + "account_id INTEGER,"
               + "hash TEXT," + "data BLOB," + // raw image data
               "UNIQUE (account_id, contact)" + ");");

    db.execSQL("CREATE TABLE " + TABLE_PROVIDER_SETTINGS + " ("
               + "_id INTEGER PRIMARY KEY," + "provider INTEGER," + "name TEXT,"
               + "value TEXT," + "UNIQUE (provider, name)" + ");");

    db.execSQL("create TABLE " + TABLE_BRANDING_RESOURCE_MAP_CACHE + " ("
               + "_id INTEGER PRIMARY KEY," + "provider_id INTEGER,"
               + "app_res_id INTEGER," + "plugin_res_id INTEGER" + ");");

    // clean up account specific data when an account is deleted.
    db.execSQL("CREATE TRIGGER account_cleanup " + "DELETE ON " + TABLE_ACCOUNTS
               + " BEGIN " + "DELETE FROM " + TABLE_AVATARS + " WHERE account_id= OLD._id;"
               + "END");

    // add a database trigger to clean up associated provider settings
    // while deleting a provider
    db.execSQL("CREATE TRIGGER provider_cleanup " + "DELETE ON " + TABLE_PROVIDERS
               + " BEGIN " + "DELETE FROM " + TABLE_PROVIDER_SETTINGS
               + " WHERE provider= OLD._id;" + "END");

    // the following are tables for mcs
    db.execSQL("create TABLE " + TABLE_OUTGOING_RMQ_MESSAGES + " ("
               + "_id INTEGER PRIMARY KEY," + "rmq_id INTEGER," + "type INTEGER,"
               + "ts INTEGER," + "data TEXT" + ");");

    db.execSQL("create TABLE " + TABLE_LAST_RMQ_ID + " (" + "_id INTEGER PRIMARY KEY,"
               + "rmq_id INTEGER" + ");");

    db.execSQL("create TABLE " + TABLE_S2D_RMQ_IDS + " (" + "_id INTEGER PRIMARY KEY,"
               + "rmq_id INTEGER" + ");");

    //DELETE FROM cache WHERE id IN (SELECT cache.id FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL);

}
 

public static void onCreate(SQLiteDatabase database) 
{
	database.execSQL(DATABASE_CREATE);
}
 

public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) 
{
	Log.w(MessagesTable.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion  + ", which will destroy all old data");
	database.execSQL("DROP TABLE IF EXISTS " + TABLE_ADDRESSES);
	onCreate(database);
}
 

public static void onCreate(SQLiteDatabase database)
{
    database.execSQL(DATABASE_CREATE);
}
 

protected static void updateIndexes(SQLiteDatabase db) {
    db.execSQL(DatabaseIndexingUtils.indexOnTableCommand("case_id_index", "AndroidCase", "case_id"));
    db.execSQL(DatabaseIndexingUtils.indexOnTableCommand("case_type_index", "AndroidCase", "case_type"));
    db.execSQL(DatabaseIndexingUtils.indexOnTableCommand("case_status_index", "AndroidCase", "case_status"));
}