Java Code Examples for android.database.sqlite.SQLiteDatabase.execSQL()

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.d("DB_UPGRADE", "Updating " + AppChoiceTable.NAME + " table to version " +
                        newVersion + " from version " + oldVersion);
    Cursor cursor = db.query(AppChoiceTable.NAME, null, null, null, null, null, null);
    ArrayList<String> existentColumns = new ArrayList<>(Arrays.asList(cursor.getColumnNames()));
    cursor.close();
    ArrayList<String> missingColumns = AppChoiceTable.Cols.NAME_LIST;
    missingColumns.removeAll(existentColumns);

    try {
        for (String columnName : missingColumns) {
            Log.d("DB_UPGRADE", "Adding column " + columnName + " to table using: " +
                    mDbAlterCommands.get(columnName));
            db.execSQL(mDbAlterCommands.get(columnName));
        }
    } catch (Exception e) {
        Log.e("DB_UPGRADE", "Failed to upgrade DB: " + e.getMessage());
        Toast.makeText(mContext, "App update has an issue! Please send logs to developer!", Toast.LENGTH_LONG).show();
    }
}
 

public void purgeAccountAtDate(Account account, long date) {
    long nearestTransactionId = findNearestOlderTransactionId(account, date);
    if (nearestTransactionId > 0) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            Transaction newTransaction = createTransactionFromNearest(account, nearestTransactionId);
            breakSplitTransactions(account, date);
            deleteOldTransactions(account, date);
            insertWithoutUpdatingBalance(newTransaction);
            db.execSQL(INSERT_RUNNING_BALANCE, new Object[]{account.id, newTransaction.id, newTransaction.dateTime, newTransaction.fromAmount});
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }
}
 

public static void headersPruneOrphans(SQLiteDatabase db) {
    try {
        db.execSQL("DELETE FROM headers WHERE id in (SELECT headers.id FROM headers LEFT JOIN messages ON headers.message_id = messages.id WHERE messages.id IS NULL)");
    } catch (SQLiteException e) {
        Timber.e("Unable to remove extra header data from the database");
    }
}
 

private void createFeedListsTable(SQLiteDatabase db) {
    String sql = "CREATE TABLE [" + FeedListsColumns.TABLENAME + "] (\n" +
            " [" + FeedListsColumns._ID + "] INTEGER NOT NULL UNIQUE, " +
            " [" + FeedListsColumns.TITLE + "] TEXT, " +
            " [" + FeedListsColumns.NO_REPOSTS + "] BOOLEAN, " +
            " [" + FeedListsColumns.SOURCE_IDS + "] TEXT, " +
            " CONSTRAINT [] PRIMARY KEY([" + UserColumns._ID + "]) ON CONFLICT REPLACE);";
    db.execSQL(sql);
}
 

@Override
public void onRobustUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) throws SQLiteException {
		switch (oldVersion) {
		case 1:
			db.execSQL("ALTER TABLE " + TABLE_PUBKEYS
					+ " ADD COLUMN " + FIELD_PUBKEY_CONFIRMUSE + " INTEGER DEFAULT 0");
			db.execSQL("ALTER TABLE " + TABLE_PUBKEYS
					+ " ADD COLUMN " + FIELD_PUBKEY_LIFETIME + " INTEGER DEFAULT 0");
		}
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table  " +TABLE_HOMEPOST+ "( "
            +NOTIFICATION_ID+ " text  , "
            +NOTIFICATION_small_icon+ " text , "
            +NOTIFICATION_TITLE+ " text , "
            +NOTIFICATION_BODY+" text , "
            +NOTIFICATION_LARGE_ICON+" text , "
            +NOTIFICATION_BIG_PICTURE+" text , "
            +NOTIFICATION_LAUNCH_URL+" text )");
}
 

/**
 * Updates profile id of all entries and changes the default value for the column.
 */
private void migrateProfileId(SQLiteDatabase db, long newProfileId) {
    // Update existing entries.
    ContentValues values = new ContentValues();
    values.put(Favorites.PROFILE_ID, newProfileId);
    db.update(Favorites.TABLE_NAME, values, null, null);

    // Change default value of the column.
    db.execSQL("ALTER TABLE favorites RENAME TO favorites_old;");
    Favorites.addTableToDb(db, newProfileId, false);
    db.execSQL("INSERT INTO favorites SELECT * FROM favorites_old;");
    db.execSQL("DROP TABLE favorites_old;");
}
 

@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion == 1 && newVersion == 2) {
        db.execSQL("CREATE TABLE IF NOT EXISTS "
                + RESPONSE_FILENAME_TABLE_NAME + "( "
                + URL + " VARCHAR NOT NULL PRIMARY KEY, "
                + FILENAME + " VARCHAR NOT NULL)"
        );
    }
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_META + "(" + COL_TIMETABLE_ID + " TEXT PRIMARY KEY," + COL_MODIFIED_MILLIS + " INTEGER)");
    db.execSQL("CREATE TABLE " + TABLE_DAYS + "(" + COL_DAY_ID + " INTEGER," + COL_NAME + " TEXT," + COL_SHORT_NAME + " TEXT," + COL_TIMETABLE_ID + " INTEGER, PRIMARY KEY (" + COL_DAY_ID + ", " + COL_TIMETABLE_ID + "))");
    db.execSQL("CREATE TABLE " + TABLE_PERIODS + "(" + COL_PERIOD_ID + " INTEGER," + COL_START_TIME + " TEXT," + COL_END_TIME + " TEXT," + COL_TIMETABLE_ID + " INTEGER, PRIMARY KEY (" + COL_PERIOD_ID + ", " + COL_TIMETABLE_ID + "))");
    db.execSQL("CREATE TABLE " + TABLE_FORMS + "(" + COL_FORM_ID + " INTEGER," + COL_NAME + " TEXT," + COL_SHORT_NAME + " TEXT," + COL_TEACHER_ID + " INTEGER," + COL_TIMETABLE_ID + " INTEGER, PRIMARY KEY (" + COL_FORM_ID + ", " + COL_TIMETABLE_ID + "))");
    db.execSQL("CREATE TABLE " + TABLE_SUBJECTS + "(" + COL_SUBJECT_ID + " INTEGER," + COL_NAME + " TEXT," + COL_SHORT_NAME + " TEXT," + COL_TIMETABLE_ID + " INTEGER, PRIMARY KEY (" + COL_SUBJECT_ID + ", " + COL_TIMETABLE_ID + "))");
    db.execSQL("CREATE TABLE " + TABLE_CLASSROOMS + "(" + COL_CLASSROOM_ID + " INTEGER," + COL_NAME + " TEXT," + COL_SHORT_NAME + " TEXT," + COL_TIMETABLE_ID + " INTEGER, PRIMARY KEY (" + COL_CLASSROOM_ID + ", " + COL_TIMETABLE_ID + "))");
    db.execSQL("CREATE TABLE " + TABLE_TEACHERS + "(" + COL_TEACHER_ID + " INTEGER," + COL_NAME + " TEXT," + COL_SHORT_NAME + " TEXT," + COL_COLOUR + " INTEGER," + COL_TIMETABLE_ID + " INTEGER, PRIMARY KEY (" + COL_TEACHER_ID + ", " + COL_TIMETABLE_ID + "))");
    db.execSQL("CREATE TABLE " + TABLE_SCHEDULES + "(" + COL_DAY_ID + " INTEGER," + COL_PERIOD_ID + " INTEGER," + COL_CLASSROOM_ID + " INTEGER," + COL_TEACHER_ID + " INTEGER," + COL_SUBJECT_ID + " INTEGER," + COL_FORM_ID + " TEXT," + COL_TIMETABLE_ID + " INTEGER)");
}
 

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_USER);
    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_IMAGE);
    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SESSION);

    onCreate(sqLiteDatabase);
}
 

public void onCreate(SQLiteDatabase db){
    db.execSQL("CREATE TABLE "+TABLENAME+" ( "+ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+NAME+" TEXT NOT NULL,"+PATH+" TEXT NOT NULL,"+CATEGORY+" TEXT NOT NULL);");
}
 

private void addMaxAgeToRepo(SQLiteDatabase db, int oldVersion) {
    if (oldVersion >= 30 || columnExists(db, RepoTable.NAME, RepoTable.Cols.MAX_AGE)) {
        return;
    }
    db.execSQL("alter table " + RepoTable.NAME + " add column " + RepoTable.Cols.MAX_AGE + " integer not null default 0");
}
 

private static void renameOldMessagesTableAndCreateNew(SQLiteDatabase db) {
    db.execSQL("ALTER TABLE messages RENAME TO messages_old");

    db.execSQL("CREATE TABLE messages (" +
            "id INTEGER PRIMARY KEY, " +
            "deleted INTEGER default 0, " +
            "folder_id INTEGER, " +
            "uid TEXT, " +
            "subject TEXT, " +
            "date INTEGER, " +
            "flags TEXT, " +
            "sender_list TEXT, " +
            "to_list TEXT, " +
            "cc_list TEXT, " +
            "bcc_list TEXT, " +
            "reply_to_list TEXT, " +
            "attachment_count INTEGER, " +
            "internal_date INTEGER, " +
            "message_id TEXT, " +
            "preview TEXT, " +
            "mime_type TEXT, "+
            "normalized_subject_hash INTEGER, " +
            "empty INTEGER default 0, " +
            "read INTEGER default 0, " +
            "flagged INTEGER default 0, " +
            "answered INTEGER default 0, " +
            "forwarded INTEGER default 0, " +
            "message_part_id INTEGER" +
            ")");

    db.execSQL("CREATE TABLE message_parts (" +
            "id INTEGER PRIMARY KEY, " +
            "type INTEGER NOT NULL, " +
            "root INTEGER, " +
            "parent INTEGER NOT NULL, " +
            "seq INTEGER NOT NULL, " +
            "mime_type TEXT, " +
            "decoded_body_size INTEGER, " +
            "display_name TEXT, " +
            "header TEXT, " +
            "encoding TEXT, " +
            "charset TEXT, " +
            "data_location INTEGER NOT NULL, " +
            "data BLOB, " +
            "preamble TEXT, " +
            "epilogue TEXT, " +
            "boundary TEXT, " +
            "content_id TEXT, " +
            "server_extra TEXT" +
            ")");

    db.execSQL("CREATE TRIGGER set_message_part_root " +
            "AFTER INSERT ON message_parts " +
            "BEGIN " +
            "UPDATE message_parts SET root=id WHERE root IS NULL AND ROWID = NEW.ROWID; " +
            "END");
}
 

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + DatabaseContract.TABLE_TRANSACTIONS);
    onCreate(db);
}
 

/**
     * Called when the database is created for the first time. This is where the creation of
     * tables and the initial population of the tables should happen.
     *
     * @param sqLiteDatabase The database.
     */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        /*
         * This String will contain a simple SQL statement that will create a table that will
         * cache our weather data.
         */
        final String SQL_CREATE_WEATHER_TABLE =

                "CREATE TABLE " + WeatherEntry.TABLE_NAME + " (" +

                /*
                 * WeatherEntry did not explicitly declare a column called "_ID". However,
                 * WeatherEntry implements the interface, "BaseColumns", which does have a field
                 * named "_ID". We use that here to designate our table's primary key.
                 */
                WeatherEntry._ID               + " INTEGER PRIMARY KEY AUTOINCREMENT, " +

                WeatherEntry.COLUMN_DATE       + " INTEGER NOT NULL, "                 +

                WeatherEntry.COLUMN_WEATHER_ID + " INTEGER NOT NULL, "                 +

                WeatherEntry.COLUMN_MIN_TEMP   + " REAL NOT NULL, "                    +
                WeatherEntry.COLUMN_MAX_TEMP   + " REAL NOT NULL, "                    +

                WeatherEntry.COLUMN_HUMIDITY   + " REAL NOT NULL, "                    +
                WeatherEntry.COLUMN_PRESSURE   + " REAL NOT NULL, "                    +

                WeatherEntry.COLUMN_WIND_SPEED + " REAL NOT NULL, "                    +
                WeatherEntry.COLUMN_DEGREES    + " REAL NOT NULL, " +

//              COMPLETED (1) Add a UNIQUE constraint on the date column to replace on conflict
                " UNIQUE (" + WeatherEntry.COLUMN_DATE + ") ON CONFLICT REPLACE);";

        /*
         * After we've spelled out our SQLite table creation statement above, we actually execute
         * that SQL with the execSQL method of our SQLite database object.
         */
        sqLiteDatabase.execSQL(SQL_CREATE_WEATHER_TABLE);
    }
 

private void upgradeDbTo330(SQLiteDatabase db, int oldVersion) throws SQLException {
    String updSql;
    createBPartnerTable(db);

    if (!columnExists(db, TABLE_NAME_EXPENSE, COL_NAME_EXPENSE__QUANTITY)) {
        updSql = "ALTER TABLE " + TABLE_NAME_EXPENSE + " ADD " + COL_NAME_EXPENSE__QUANTITY + " NUMERIC NULL ";
        db.execSQL(updSql);
    }
    if (!columnExists(db, TABLE_NAME_EXPENSE, COL_NAME_EXPENSE__PRICE)) {
        updSql = "ALTER TABLE " + TABLE_NAME_EXPENSE + " ADD " + COL_NAME_EXPENSE__PRICE + " NUMERIC NULL ";
        db.execSQL(updSql);
    }
    if (!columnExists(db, TABLE_NAME_EXPENSE, COL_NAME_EXPENSE__PRICEENTERED)) {
        updSql = "ALTER TABLE " + TABLE_NAME_EXPENSE + " ADD " + COL_NAME_EXPENSE__PRICEENTERED + " NUMERIC NULL ";
        db.execSQL(updSql);
    }
    if (!columnExists(db, TABLE_NAME_EXPENSE, COL_NAME_EXPENSE__UOM_ID)) {
        updSql = "ALTER TABLE " + TABLE_NAME_EXPENSE + " ADD " + COL_NAME_EXPENSE__UOM_ID + " INTEGER NULL ";
        db.execSQL(updSql);
    }

    if (!columnExists(db, TABLE_NAME_EXPENSE, COL_NAME_EXPENSE__BPARTNER_ID)) {
        updSql = "ALTER TABLE " + TABLE_NAME_EXPENSE + " ADD " + COL_NAME_EXPENSE__BPARTNER_ID + " INTEGER NULL ";
        db.execSQL(updSql);
    }
    if (!columnExists(db, TABLE_NAME_EXPENSE, COL_NAME_EXPENSE__BPARTNER_LOCATION_ID)) {
        updSql = "ALTER TABLE " + TABLE_NAME_EXPENSE + " ADD " + COL_NAME_EXPENSE__BPARTNER_LOCATION_ID + " INTEGER NULL ";
        db.execSQL(updSql);
    }

    if (!columnExists(db, TABLE_NAME_REFUEL, COL_NAME_REFUEL__BPARTNER_ID)) {
        updSql = "ALTER TABLE " + TABLE_NAME_REFUEL + " ADD " + COL_NAME_REFUEL__BPARTNER_ID + " INTEGER NULL ";
        db.execSQL(updSql);
    }
    if (!columnExists(db, TABLE_NAME_REFUEL, COL_NAME_REFUEL__BPARTNER_LOCATION_ID)) {
        updSql = "ALTER TABLE " + TABLE_NAME_REFUEL + " ADD " + COL_NAME_REFUEL__BPARTNER_LOCATION_ID + " INTEGER NULL ";
        db.execSQL(updSql);
    }

    upgradeDbTo340(db, oldVersion);
}
 

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS "+NOTES_TABLE_NAME+"");
    onCreate(db);
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(SQL_CREATE_ENTRIES);
}
 

/**
 * This database is only a cache for online data, so its upgrade policy is simply to discard
 * the data and call through to onCreate to recreate the table. Note that this only fires if
 * you change the version number for your database (in our case, DATABASE_VERSION). It does NOT
 * depend on the version number for your application found in your app/build.gradle file. If
 * you want to update the schema without wiping data, commenting out the current body of this
 * method should be your top priority before modifying this method.
 *
 * @param sqLiteDatabase Database that is being upgraded
 * @param oldVersion     The old database version
 * @param newVersion     The new database version
 */
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + WeatherEntry.TABLE_NAME);
    onCreate(sqLiteDatabase);
}
 

public void onCreate(SQLiteDatabase db) {

        DatabaseObj.context = this;
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE + " (_id  integer primary key autoincrement, name TEXT, obj BLOB)");

    }