Java Code Examples for android.arch.persistence.db.SupportSQLiteDatabase#execSQL()

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("CREATE TABLE FirewallWhitelistedPackage " +
            "(id INTEGER PRIMARY KEY, " +
            "packageName TEXT NOT NULL, " +
            "policyPackageId TEXT DEFAULT 'default-policy', " +
            "FOREIGN KEY (policyPackageId) REFERENCES PolicyPackage(id))");
    database.execSQL("CREATE UNIQUE INDEX firewall_whitelisted_package_policy_package_idx " +
            "ON FirewallWhitelistedPackage (packageName, policyPackageId)");
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {

    database.execSQL("CREATE TABLE PolicyPackage " +
            "(id TEXT PRIMARY KEY, " +
            "name TEXT NOT NULL, " +
            "createdAt INTEGER, " +
            "updatedAt INTEGER) ");

    Date currentDate = new Date();
    ContentValues contentValues = new ContentValues();
    contentValues.put("id", "default-policy");
    contentValues.put("name", "Default Policy");
    contentValues.put("createdAt", currentDate.getTime());
    contentValues.put("updatedAt", currentDate.getTime());
    database.insert("PolicyPackage", SQLiteDatabase.CONFLICT_REPLACE, contentValues);

    database.execSQL("CREATE TABLE AppPermission " +
            "(id INTEGER PRIMARY KEY, " +
            "packageName TEXT NOT NULL, " +
            "permissionName TEXT NOT NULL, " +
            "permissionStatus INTEGER DEFAULT 0, " +
            "policyPackageId TEXT DEFAULT 'default-policy', " +
            "FOREIGN KEY (policyPackageId) REFERENCES PolicyPackage(id))");
    database.execSQL("CREATE UNIQUE INDEX app_permission_policy_package_idx " +
            "ON AppPermission (packageName, permissionName, policyPackageId)");

    database.execSQL("CREATE TABLE DisabledPackage " +
            "(id INTEGER PRIMARY KEY, " +
            "packageName TEXT NOT NULL, " +
            "policyPackageId TEXT DEFAULT 'default-policy', " +
            "FOREIGN KEY (policyPackageId) REFERENCES PolicyPackage(id))");
    database.execSQL("CREATE UNIQUE INDEX disabled_package_policy_package_idx " +
            "ON DisabledPackage (packageName, policyPackageId)");
}
 

@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
    //scheme change
    database.execSQL("ALTER TABLE `users` RENAME TO `users_old`");
    database.execSQL(
        "CREATE TABLE IF NOT EXISTS `users` (`_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `username` TEXT, `password` TEXT, `nickname` TEXT, `status` TEXT, `status_message` TEXT, `avatar` TEXT)");
    database.execSQL(
        "INSERT INTO `users` (`username`, `password`, `nickname`,`status`,`status_message`,`avatar`) SELECT `username`, `password`, `nickname`,`status`,`status_message`,`avatar` FROM users_old");

    database.execSQL("DROP TABLE IF EXISTS `users_old`");
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE PolicyPackage ADD COLUMN numberOfDisabledPackages INTEGER DEFAULT 0");
    database.execSQL("ALTER TABLE PolicyPackage ADD COLUMN numberOfHosts INTEGER DEFAULT 0");
    database.execSQL("ALTER TABLE PolicyPackage ADD COLUMN numberOfUserBlockedDomains INTEGER DEFAULT 0");
    database.execSQL("ALTER TABLE PolicyPackage ADD COLUMN numberOfUserWhitelistedDomains INTEGER DEFAULT 0");
    database.execSQL("ALTER TABLE PolicyPackage ADD COLUMN numberOfChangedPermissions INTEGER DEFAULT 0");
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE product "
            + " ADD COLUMN price INTEGER");

    // enable flag to force update products
    App.get().setForceUpdate(true);
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("CREATE TABLE FirewallWhitelistedPackage " +
            "(id INTEGER PRIMARY KEY, " +
            "packageName TEXT NOT NULL, " +
            "policyPackageId TEXT DEFAULT 'default-policy', " +
            "FOREIGN KEY (policyPackageId) REFERENCES PolicyPackage(id))");
    database.execSQL("CREATE UNIQUE INDEX firewall_whitelisted_package_policy_package_idx " +
            "ON FirewallWhitelistedPackage (packageName, policyPackageId)");
}
 

@Override public void onCreate(@NonNull SupportSQLiteDatabase db) {
  db.execSQL("PRAGMA foreign_keys=ON");

  db.execSQL(CREATE_EMPLOYEE);
  aliceId = db.insert(TABLE_EMPLOYEE, CONFLICT_FAIL, employee("alice", "Alice Allison"));
  bobId = db.insert(TABLE_EMPLOYEE, CONFLICT_FAIL, employee("bob", "Bob Bobberson"));
  eveId = db.insert(TABLE_EMPLOYEE, CONFLICT_FAIL, employee("eve", "Eve Evenson"));

  db.execSQL(CREATE_MANAGER);
  db.insert(TABLE_MANAGER, CONFLICT_FAIL, manager(eveId, aliceId));
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {

    database.execSQL("CREATE TABLE PolicyPackage " +
            "(id TEXT PRIMARY KEY, " +
            "name TEXT NOT NULL, " +
            "createdAt INTEGER, " +
            "updatedAt INTEGER) ");

    Date currentDate = new Date();
    ContentValues contentValues = new ContentValues();
    contentValues.put("id", "default-policy");
    contentValues.put("name", "Default Policy");
    contentValues.put("createdAt", currentDate.getTime());
    contentValues.put("updatedAt", currentDate.getTime());
    database.insert("PolicyPackage", SQLiteDatabase.CONFLICT_REPLACE, contentValues);

    database.execSQL("CREATE TABLE AppPermission " +
            "(id INTEGER PRIMARY KEY, " +
            "packageName TEXT NOT NULL, " +
            "permissionName TEXT NOT NULL, " +
            "permissionStatus INTEGER DEFAULT 0, " +
            "policyPackageId TEXT DEFAULT 'default-policy', " +
            "FOREIGN KEY (policyPackageId) REFERENCES PolicyPackage(id))");
    database.execSQL("CREATE UNIQUE INDEX app_permission_policy_package_idx " +
            "ON AppPermission (packageName, permissionName, policyPackageId)");

    database.execSQL("CREATE TABLE DisabledPackage " +
            "(id INTEGER PRIMARY KEY, " +
            "packageName TEXT NOT NULL, " +
            "policyPackageId TEXT DEFAULT 'default-policy', " +
            "FOREIGN KEY (policyPackageId) REFERENCES PolicyPackage(id))");
    database.execSQL("CREATE UNIQUE INDEX disabled_package_policy_package_idx " +
            "ON DisabledPackage (packageName, policyPackageId)");
}
 

@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("CREATE TABLE contents (_id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT);");
    database.execSQL("INSERT INTO contents (content) SELECT text FROM Histories");
    database.execSQL("DROP TABLE Histories;");
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE PolicyPackage ADD COLUMN description TEXT");
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE BlockUrlProviders ADD COLUMN policyPackageId TEXT DEFAULT 'default-policy' REFERENCES PolicyPackage(id)");
    database.execSQL("ALTER TABLE UserBlockUrl ADD COLUMN policyPackageId TEXT DEFAULT 'default-policy' REFERENCES PolicyPackage(id)");
    database.execSQL("ALTER TABLE WhiteUrl ADD COLUMN policyPackageId TEXT DEFAULT 'default-policy' REFERENCES PolicyPackage(id)");
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    // Add column to the table
    database.execSQL("ALTER TABLE city ADD city_favourite INTEGER DEFAULT 0 NOT NULL");
}
 

@Override
public void migrate(SupportSQLiteDatabase supportSQLiteDatabase) {
    supportSQLiteDatabase.execSQL("ALTER TABLE AppInfo ADD COLUMN adhellWhitelisted INTEGER DEFAULT 0");
}
 

@Override public void onCreate(SupportSQLiteDatabase db) {
  db.execSQL(CREATE_LIST);
  db.execSQL(CREATE_ITEM);
  db.execSQL(CREATE_ITEM_LIST_ID_INDEX);

  long groceryListId = db.insert(TodoList.TABLE, CONFLICT_FAIL, new TodoList.Builder()
      .name("Grocery List")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(groceryListId)
      .description("Beer")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(groceryListId)
      .description("Point Break on DVD")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(groceryListId)
      .description("Bad Boys 2 on DVD")
      .build());

  long holidayPresentsListId = db.insert(TodoList.TABLE, CONFLICT_FAIL, new TodoList.Builder()
      .name("Holiday Presents")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(holidayPresentsListId)
      .description("Pogo Stick for Jake W.")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(holidayPresentsListId)
      .description("Jack-in-the-box for Alec S.")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(holidayPresentsListId)
      .description("Pogs for Matt P.")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(holidayPresentsListId)
      .description("Cola for Jesse W.")
      .build());

  long workListId = db.insert(TodoList.TABLE, CONFLICT_FAIL, new TodoList.Builder()
      .name("Work Items")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(workListId)
      .description("Finish SqlBrite library")
      .complete(true)
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(workListId)
      .description("Finish SqlBrite sample app")
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder()
      .listId(workListId)
      .description("Publish SqlBrite to GitHub")
      .build());

  long birthdayPresentsListId = db.insert(TodoList.TABLE, CONFLICT_FAIL, new TodoList.Builder()
      .name("Birthday Presents")
      .archived(true)
      .build());
  db.insert(TodoItem.TABLE, CONFLICT_FAIL, new TodoItem.Builder().listId(birthdayPresentsListId)
      .description("New car")
      .complete(true)
      .build());
}
 

@Override
public void migrate(SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE BlockUrlProviders ADD COLUMN policyPackageId TEXT DEFAULT 'default-policy' REFERENCES PolicyPackage(id)");
    database.execSQL("ALTER TABLE UserBlockUrl ADD COLUMN policyPackageId TEXT DEFAULT 'default-policy' REFERENCES PolicyPackage(id)");
    database.execSQL("ALTER TABLE WhiteUrl ADD COLUMN policyPackageId TEXT DEFAULT 'default-policy' REFERENCES PolicyPackage(id)");
}
 

@Override
        public void migrate(SupportSQLiteDatabase database) {

            // Create the new table
            database.execSQL(
                    "CREATE TABLE admin_new (uid INTEGER NOT NULL, first_name TEXT, last_name TEXT, email TEXT,username TEXT, password TEXT, PRIMARY KEY(uid))");
// Copy the data
            database.execSQL(
                    "INSERT INTO admin_new (uid, first_name, last_name, email, password) SELECT uid, first_name, last_name, email, password FROM Administrator");
// Remove the old table
            database.execSQL("DROP TABLE Administrator");
// Change the table name to the correct one
            database.execSQL("ALTER TABLE admin_new RENAME TO Administrator");
            // Since we didn't alter the table, there's nothing else to do here.

            // Create the new table
            database.execSQL(
                    "CREATE TABLE Category_new (cId INTEGER NOT NULL, category_name TEXT, is_active INTEGER NOT NULL, is_include_in_drawer_menu INTEGER NOT NULL, category_icon INTEGER NOT NULL, level INTEGER NOT NULL, parent_id INTEGER NOT NULL, path TEXT, PRIMARY KEY(cId))");
            database.execSQL(
                    "INSERT INTO Category_new (cId, category_name, is_active, is_include_in_drawer_menu, category_icon, level, parent_id, path) SELECT cId, category_name, is_active, is_include_in_drawer_menu, category_icon, level, parent_id, path FROM Category");
            database.execSQL("DROP TABLE Category");
            database.execSQL("ALTER TABLE Category_new RENAME TO Category");

            // Create the new table
            database.execSQL(
                    "CREATE TABLE Product_new (pId INTEGER NOT NULL, product_name TEXT, product_s_deccription TEXT, sku TEXT, is_enabled INTEGER NOT NULL, price TEXT, special_price TEXT, is_taxable_goods_applied INTEGER NOT NULL" +
                            ",track_inventory INTEGER NOT NULL, quantity TEXT, stock_availability INTEGER NOT NULL, image TEXT, weight TEXT, productCategories TEXT,formatted_price TEXT, formatted_special_price TEXT" +
                            ",barCode TEXT, product_tax TEXT, options TEXT, discount REAL NOT NULL DEFAULT 0, formatted_discount TEXT, PRIMARY KEY(pId))");
            database.execSQL(
                    "INSERT INTO Product_new (pId, product_name, product_s_deccription, sku, is_enabled, price, special_price, is_taxable_goods_applied, track_inventory, quantity, stock_availability, image, weight, productCategories" +
                            ", formatted_price, formatted_special_price, barCode, product_tax, options )" +
                            " SELECT pId, product_name, product_s_deccription, sku, is_enabled, price, special_price, is_taxable_goods_applied, track_inventory" +
                            ", quantity, stock_availability, image, weight,productCategories, formatted_price, formatted_special_price, barCode, product_tax, options " +
                            " FROM Product");
            database.execSQL("DROP TABLE Product");
            database.execSQL("ALTER TABLE Product_new RENAME TO Product");

            //create customer table
            database.execSQL(
                    "CREATE TABLE Customer_new (customerId INTEGER NOT NULL, customer_first_name TEXT, customer_last_name TEXT, email TEXT, contact_number TEXT" +
                            ", address_line TEXT,city TEXT,postal_code TEXT,state TEXT,country TEXT, PRIMARY KEY(customerId))");
            database.execSQL(
                    "INSERT INTO Customer_new (customerId, customer_first_name, customer_last_name, email, contact_number, address_line, city, postal_code,state" +
                            ", country) SELECT customerId, customer_first_name, customer_last_name, email, contact_number, address_line, city, postal_code,state" +
                            ", country FROM Customer");
            database.execSQL("DROP TABLE Customer");
            database.execSQL("ALTER TABLE Customer_new RENAME TO Customer");

            //create OrderEntity table
            database.execSQL(
                    "CREATE TABLE OrderEntity_new (orderId INTEGER NOT NULL, time TEXT, date TEXT, cart_data TEXT, qty TEXT" +
                            ", cash_data TEXT,is_synced TEXT,is_return INTEGER NOT NULL, refunded_order_id TEXT, PRIMARY KEY(orderId))");
            database.execSQL(
                    "INSERT INTO OrderEntity_new (orderId, time, date, cart_data, qty, cash_data, is_synced, is_return,refunded_order_id) " +
                            "SELECT orderId, time, date, cart_data, qty, cash_data, is_synced, is_return,refunded_order_id FROM OrderEntity");
            database.execSQL("DROP TABLE OrderEntity");
            database.execSQL("ALTER TABLE OrderEntity_new RENAME TO OrderEntity");

            //create HoldCart table
            database.execSQL(
                    "CREATE TABLE HoldCart_new (holdCartId INTEGER NOT NULL, time TEXT, date TEXT, cart_data TEXT, qty TEXT" +
                            ", is_synced TEXT, PRIMARY KEY(holdCartId))");
            database.execSQL(
                    "INSERT INTO HoldCart_new (holdCartId, time, date, cart_data, qty, is_synced) " +
                            "SELECT holdCartId, time, date, cart_data, qty, is_synced FROM HoldCart");
            database.execSQL("DROP TABLE HoldCart");
            database.execSQL("ALTER TABLE HoldCart_new RENAME TO HoldCart");

            //create CashDrawerModel table
//            database.execSQL(
//                    "CREATE TABLE CashDrawerModel_new (date INTEGER NOT NULL, cash_drawer_items TEXT, opening_balance TEXT, formatted_opening_balance TEXT" +
//                            ", closing_balance TEXT, formatted_closing_balance TEXT, net_revenue TEXT, formatted_net_revenue TEXT, " +
//                            ", in_amount TEXT, formatted_in_amount TEXT, out_amount TEXT, formatted_out_amount TEXT, is_synced TEXT, PRIMARY KEY(date))");
//            database.execSQL(
//                    "INSERT INTO CashDrawerModel_new (date, cash_drawer_items, opening_balance, formatted_opening_balance, closing_balance, formatted_closing_balance, net_revenue, formatted_net_revenue, in_amount, formatted_in_amount, out_amount, formatted_out_amount, is_synced) " +
//                            "SELECT date, cash_drawer_items, opening_balance, formatted_opening_balance, closing_balance, formatted_closing_balance, net_revenue, formatted_net_revenue, in_amount, formatted_in_amount, out_amount, formatted_out_amount, is_synced FROM CashDrawerModel");
//            database.execSQL("DROP TABLE CashDrawerModel");
//            database.execSQL("ALTER TABLE CashDrawerModel_new RENAME TO CashDrawerModel");

            //create HoldCart table
//            database.execSQL(
//                    "CREATE TABLE Option_new (optionId INTEGER NOT NULL, option_name TEXT, option_type TEXT, option_values TEXT, sort_order TEXT" +
//                            ", PRIMARY KEY(optionId))");
//            database.execSQL(
//                    "INSERT INTO Option_new (optionId, option_name, option_type, option_values, sort_order) " +
//                            "SELECT optionId, option_name, option_type, option_values, sort_order FROM Option");
//            database.execSQL("DROP TABLE Option");
//            database.execSQL("ALTER TABLE Option_new RENAME TO HoldCart");
//
//            //create Tax table
//            database.execSQL(
//                    "CREATE TABLE Tax_new (taxId INTEGER NOT NULL, tax_name TEXT, is_enabled TEXT, type TEXT, tax_rate TEXT" +
//                            ", PRIMARY KEY(taxId))");
//            database.execSQL(
//                    "INSERT INTO Tax_new (taxId, tax_name, is_enabled, type, tax_rate) " +
//                            "SELECT taxId, tax_name, is_enabled, type, tax_rate FROM Tax");
//            database.execSQL("DROP TABLE Tax");
//            database.execSQL("ALTER TABLE Tax_new RENAME TO Tax");
        }
 

@Override
public void migrate(SupportSQLiteDatabase supportSQLiteDatabase) {
    supportSQLiteDatabase.execSQL("ALTER TABLE AppInfo ADD COLUMN disabled INTEGER DEFAULT 0");
}
 

@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE `users` ADD `login_time` INTEGER NOT NULL default 0");
}
 

@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
    //add column(friend_contacts) on TABLE 'friend_contacts'
    database.execSQL("ALTER TABLE `friend_contacts` ADD `has_offline_bot` INTEGER NOT NULL default 0");
}
 

@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
    //TODO
    database.execSQL("DROP TABLE IF EXISTS `group_peers`");
}