Java DatabaseWrapper.execSQL方法代码示例

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
@Override
public void migrate(DatabaseWrapper database) {
    //The column name can't be renamed in sqlite. It is needed create a temporal table with the new column name.
    ModelAdapter myAdapter = FlowManager.getModelAdapter(Survey.class);

    //Create temporal table
    String sql=myAdapter.getCreationQuery();
    Log.d("DBMIGRATION", "old table " + sql);
    sql=sql.replace("Survey", "Survey_temp");
    Log.d("DBMIGRATION", "create temp table " + sql);
    database.execSQL(sql);

    //Insert the data in temporal table
    String sqlCopy="INSERT INTO Survey_temp(id_survey, id_tab_group, id_org_unit, id_user, creationDate, completionDate, uploadedDate, scheduledDate, status, eventuid) SELECT id_survey, id_tab_group, id_org_unit, id_user, creationDate, completionDate, eventDate, scheduledDate, status, eventuid FROM Survey";
    database.execSQL(sqlCopy);

    //Replace old table by new table with the new column name.
    database.execSQL("DROP TABLE IF EXISTS Survey");
    database.execSQL("ALTER TABLE Survey_temp RENAME TO Survey");
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
private void migrateSurveyTable(DatabaseWrapper database) {
    ModelAdapter myAdapter = FlowManager.getModelAdapter(Survey.class);

    //Create temporal table
    String sql=myAdapter.getCreationQuery();
    Log.d("DBMIGRATION", "old table " + sql);
    sql=sql.replace("Survey", "Survey_temp");
    Log.d("DBMIGRATION", "create temp table " + sql);
    database.execSQL(sql);

    //Insert the data in temporal table
    String sqlCopy="INSERT INTO Survey_temp(id_survey, id_program, id_org_unit, id_user, creation_date, completion_date, upload_date, scheduled_date, status, eventuid) SELECT id_survey, id_tab_group, id_org_unit, id_user, creationDate, completionDate, uploadedDate, scheduledDate, status, eventuid FROM Survey";
    database.execSQL(sqlCopy);

    //Replace old table by new table with the new column name.
    database.execSQL("DROP TABLE IF EXISTS Survey");
    database.execSQL("ALTER TABLE Survey_temp RENAME TO Survey");
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
@Override
public void migrate(DatabaseWrapper database) {
    String sqlOptionTemp =
            "CREATE TABLE OptionTemp(id_option INTEGER PRIMARY KEY, code TEXT, name TEXT, "
                    + "factor REAL, id_answer_fk INTEGER, id_option_attribute_fk INTEGER)";
    database.execSQL(sqlOptionTemp);

    String sqlCopyOption =
            "INSERT INTO OptionTemp(id_option, code, name, factor, id_answer_fk, "
                    + "id_option_attribute_fk) "
                    + "SELECT id_option, name, code, factor, id_answer_fk, "
                    + "id_option_attribute_fk "
                    + "FROM Option;";
    database.execSQL(sqlCopyOption);

    String sqlDeleteOption = "DROP TABLE Option";
    database.execSQL(sqlDeleteOption);

    String sqlRenameOptionTemp = "ALTER TABLE OptionTemp RENAME TO Option";
    database.execSQL(sqlRenameOptionTemp);
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
private void addProgramToSurvey(DatabaseWrapper database) {
    try {
        //Is possible in some devices between versions the column id_program not exist and it will make a sqliteexception
        addColumn(database, Survey.class, Survey_Table.id_program_fk.getDefinition(), "integer");
        database.execSQL("update survey set id_program = (select id_program from tabgroup where id_tab_group=survey.id_tab_group)");
    } catch (SQLiteException e){
        e.printStackTrace();
        //In the last migration the survey.id_tab_group was renamed to survey.id_program, but here is the fixed value.
        database.execSQL("update survey set id_program = (select id_program from tabgroup where id_tab_group=survey.id_program)");
    }
    //move id_program into survey

}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
public static void recreateTables(DatabaseWrapper database,Class[] tables){
    for(int i=0;i<tables.length;i++){
        ModelAdapter myAdapter = FlowManager.getModelAdapter(tables[i]);
        database.execSQL(DROP_TABLE_IF_EXISTS + myAdapter.getTableName());
        database.execSQL(myAdapter.getCreationQuery());
    }
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
private void migrateServerMetadataTable(DatabaseWrapper database) {

        //Insert the data in new table
        String sqlCopy="INSERT INTO ServerMetadata(id_control_dataelement, name, code, uid, value_type) SELECT id_control_dataelement, name, code, uid, valueType FROM ControlDataelement";
        database.execSQL(sqlCopy);

        //Replace old table by new table with the new column name.
        database.execSQL("DROP TABLE IF EXISTS ControlDataelement");
    }
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
public void enable(@NonNull DatabaseWrapper databaseWrapper) {
    if (table == null) {
        throw new IllegalStateException("Please call on() to set a table to use this index on.");
    } else if (columns == null || columns.isEmpty()) {
        throw new IllegalStateException("There should be at least one column in this index");
    }
    databaseWrapper.execSQL(getQuery());
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
/**
 * Drops an active INDEX by specifying the databaseWrapper and indexName
 *
 * @param databaseWrapper The manually specified wrapper.
 * @param indexName       The name of the index.
 */
public static void dropIndex(@NonNull DatabaseWrapper databaseWrapper,
                             @NonNull String indexName) {
    QueryBuilder queryBuilder = new QueryBuilder("DROP INDEX IF EXISTS ")
            .append(QueryBuilder.quoteIfNeeded(indexName));
    databaseWrapper.execSQL(queryBuilder.getQuery());
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
private void addProgramStageToProgram(DatabaseWrapper database) {
    addColumn(database, Program.class, Program_Table.stage_uid.getDefinition(), "string");
    //move programStage uid into program
    database.execSQL("update program set stage_uid = (select uid from tabgroup where id_program=program.id_program)");
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
private void addProgramToTab(DatabaseWrapper database) {
    addColumn(database, Tab.class, Tab_Table.id_program_fk.getDefinition(), "integer");
    //move id_program into tab
    database.execSQL("update tab set id_program = (select id_program from tabgroup where id_tab_group=tab.id_tab_group)");
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
private void removeTabGroups(DatabaseWrapper database) {
    database.execSQL("DROP TABLE IF EXISTS tabgroup");
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
public static void addColumn(DatabaseWrapper database, Class model, String columnName, String type){
    ModelAdapter myAdapter = FlowManager.getModelAdapter(model);
    database.execSQL(String.format(ALTER_TABLE_ADD_COLUMN, myAdapter.getTableName(), columnName, type));

}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
public static void updateColumn(DatabaseWrapper database, Class model, String columnName,String value){
    ModelAdapter myAdapter = FlowManager.getModelAdapter(model);
    Log.d(TAG, String.format(UPDATE_COLUMN, myAdapter.getTableName(), columnName, value));
    database.execSQL(String.format(UPDATE_COLUMN, myAdapter.getTableName(),columnName,value) );
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
@Override
public void migrate(DatabaseWrapper database) {

    String sqlCopy = "INSERT INTO Partner (id_partner, uid_partner, name)"
            + " SELECT id_organisation, uid_organisation, name"
            + " FROM Organisation;";
    database.execSQL(sqlCopy);
    String sqlDelete = "DROP TABLE IF EXISTS Organisation;";
    database.execSQL(sqlDelete);

    String sqlUserTemp =
            "CREATE TABLE UserTemp(id_user INTEGER PRIMARY KEY, uid_user TEXT, name TEXT, "
                    + "partner_fk INTEGER, supervisor_fk INTEGER)";
    database.execSQL(sqlUserTemp);
    String sqlCopyUser =
            "INSERT INTO UserTemp (id_user, uid_user, name, partner_fk, supervisor_fk)"
                    + " SELECT id_user, uid_user, name ,organisation_fk ,supervisor_fk"
                    + " FROM User;";
    database.execSQL(sqlCopyUser);
    String sqlDeleteUser = "DROP TABLE IF EXISTS User;";
    database.execSQL(sqlDeleteUser);
    String renameUser = "ALTER TABLE UserTemp RENAME TO User";
    database.execSQL(renameUser);

    String sqlTreatmentTemp =
            "CREATE TABLE TreatmentTemp(id_treatment INTEGER PRIMARY KEY, "
                    + "id_partner_fk INTEGER, diagnosis INTEGER, message INTEGER, type "
                    + "INTEGER)";
    database.execSQL(sqlTreatmentTemp);
    String sqlCopyTreatment =
            "INSERT INTO TreatmentTemp (id_treatment, id_partner_fk, diagnosis, message, type)"
                    + " SELECT id_treatment, id_organisation_fk, diagnosis, message, type"
                    + " FROM Treatment;";
    database.execSQL(sqlCopyTreatment);
    String sqlDeleteTreatment = "DROP TABLE IF EXISTS Treatment;";
    database.execSQL(sqlDeleteTreatment);
    String renameTreatment = "ALTER TABLE TreatmentTemp RENAME TO Treatment";
    database.execSQL(renameTreatment);

    FileCsvs fileCsvs = new FileCsvs();
    try {
        fileCsvs.copyCsvFile("Organisations.csv", PopulateDB.PARTNER_CSV);
    } catch (IOException e) {
       new MigrateMigrationException(e);
    }
}
 

import com.raizlabs.android.dbflow.structure.database.DatabaseWrapper; //导入方法依赖的package包/类
public static void addColumn(DatabaseWrapper database, Class model, String columnName,
        String type) {
    ModelAdapter myAdapter = FlowManager.getModelAdapter(model);
    database.execSQL(
            String.format(ALTER_TABLE_ADD_COLUMN, myAdapter.getTableName(), columnName, type));
}