I've just discovered the possibility to use an virtual table for searching thru database content. I have however been sitting with this for two days now, and I think I'm getting stupider for every hour that pass. Can someone please provide me with an simple example of how I can do this?
This is where Im at: I got an Database class:
public class DBHelper {
private static final String TAG = DBHelper.class.getSimpleName();
//Database config
private static int DATABASE_VERSION = 2;
public static String DATABASE_NAME = "shoppingdatabase.db";
//Item table config
private static final String ITEM_TABLE_NAME = "item_table";
private static final String ITEM_TABLE_COLUMN_ID = "_id";
private static final String ITEM_TABLE_COLUMN_ITEM_NAME = "item_name";
private static final String ITEM_TABLE_COLUMN_ITEM_SIZE = "item_size";
private static final String ITEM_TABLE_COLUMN_ITEM_PRICE = "item_price";
private static final String ITEM_TABLE_COLUMN_ITEM_BRAND = "item_brand";
private static final String ITEM_TABLE_COLUMN_ITEM_CATAGORY = "item_catagory";
//Viritual table config
public static final String KEY_ROWID = "rowid";
public static final String KEY_ITEM = "name";
public static final String KEY_SIZE = "size";
public static final String KEY_PRICE = "price";
public static final String KEY_BRAND = "brand";
public static final String KEY_CATAGORY = "catagory";
public static final String KEY_SEARCH = "searchData";
private static final String FTS_VIRTUAL_TABLE = "ItemInfo";
private DatabaseHelper dbHelper;
private SQLiteDatabase db;
public DBHelper(Context aContext){
dbHelper = new DatabaseHelper(aContext);
db = dbHelper.getWritableDatabase();
}
//I use this method in my AddItem class for the user to add an item to the database
public void addItem(String iName, String iSize, String iPrice, String iBrand, String iCatagory){
ContentValues cv = new ContentValues();
cv.put(ITEM_TABLE_COLUMN_ITEM_NAME, iName);
cv.put(ITEM_TABLE_COLUMN_ITEM_SIZE, iSize);
cv.put(ITEM_TABLE_COLUMN_ITEM_PRICE, iPrice);
cv.put(ITEM_TABLE_COLUMN_ITEM_BRAND, iBrand);
cv.put(ITEM_TABLE_COLUMN_ITEM_CATAGORY, iCatagory);
db.insert(ITEM_TABLE_NAME, null, cv);
}
public void populateVT() {
}
private class DatabaseHelper extends SQLiteOpenHelper{
public DatabaseHelper(Context aContext){
super(aContext, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqliteDB){
String buildSQL = "CREATE TABLE " + ITEM_TABLE_NAME + "( "
+ ITEM_TABLE_COLUMN_ID + " INTEGER PRIMARY KEY, "
+ ITEM_TABLE_COLUMN_ITEM_NAME + " TEXT, "
+ ITEM_TABLE_COLUMN_ITEM_SIZE + " TEXT, "
+ ITEM_TABLE_COLUMN_ITEM_PRICE + " TEXT, "
+ ITEM_TABLE_COLUMN_ITEM_BRAND + " TEXT, "
+ ITEM_TABLE_COLUMN_ITEM_CATAGORY + " TEXT )";
String buildSQL2 = "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3(" +
KEY_ITEM + "," +
KEY_SIZE + "," +
KEY_PRICE + "," +
KEY_BRAND + "," +
KEY_CATAGORY + "," +
KEY_SEARCH + "," +
" UNIQUE (" + KEY_ITEM + "));";
Log.d(TAG, "onCreate SQL: " + buildSQL);
Log.d(TAG, "onCreate SQL: " + buildSQL2);
sqliteDB.execSQL(buildSQL);
sqliteDB.execSQL(buildSQL2);
}
@Override
public void onUpgrade(SQLiteDatabase sqliteDB, int oldVersion, int newVersion){
String buildSQL = "DROP TABLE IF EXISTS " + ITEM_TABLE_NAME;
String buildSQL2 = "DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE;
Log.d(TAG, "onUpgrade SQL: " + buildSQL);
Log.d(TAG, "onUpgrade SQL: " + buildSQL2);
sqliteDB.execSQL(buildSQL);
sqliteDB.execSQL(buildSQL2);
onCreate(sqliteDB);
}
}
}
How can I use populateVT() to populate the virtual table with the contents from ITEM_TABLE_NAME? and where should I call it so that it updates every time the user adds something to the database?
This is how I solved my problem.
In my database class I added these methods:
public long createItem(String name, String size, String price, String brand) {
ContentValues initialValues = new ContentValues();
String searchValue = name + " " +
size + " " +
price + " " +
brand;
initialValues.put(KEY_ITEM, name);
initialValues.put(KEY_SIZE, size);
initialValues.put(KEY_PRICE, price);
initialValues.put(KEY_BRAND, brand);
initialValues.put(KEY_CATAGORY, catagory);
initialValues.put(KEY_SEARCH, searchValue);
return db.insert(FTS_VIRTUAL_TABLE, null, initialValues);
}
public Cursor listAll(){
String buildSQL = "SELECT * FROM " + ITEM_TABLE_NAME;
Log.d(TAG, "listAll SQL: " + buildSQL);
return db.rawQuery(buildSQL, null);
}
public boolean deleteAllItems() {
int doneDelete = 0;
doneDelete = db.delete(FTS_VIRTUAL_TABLE, null , null);
Log.w(TAG, Integer.toString(doneDelete));
return doneDelete > 0;
}
And in my MainActivity I used an Cursor to iterate thru my original table and fill my virtual table with the content:
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
setTitle("");
dbHelper = new DBHelper(this);
dbHelper.deleteAllCustomers();
fillVT();
}
public void fillVT(){
String a, b, x, y, z;
Cursor c = dbHelper.listAll();
if(c.moveToFirst()){
do{
a = c.getString(1);
b = c.getString(2);
x = c.getString(3);
y = c.getString(4);
z = c.getString(5);
dbHelper.createCustomer(a, b, x, y, z);
}while(c.moveToNext());
}
}
@Override
protected void onActivityResult(int reqCode, int resCode, Intent data){
super.onActivityResult(reqCode, resCode, data);
if(reqCode == ENTER_DATA_REQUEST_CODE && resCode == RESULT_OK){
dbHelper.addItem(data.getExtras().getString("tag_item_item_name"),
data.getExtras().getString("tag_item_item_size"),
data.getExtras().getString("tag_item_item_price"),
data.getExtras().getString("tag_item_item_brand"),
data.getExtras().getString("tag_item_item_catagory"));
dbHelper.deleteAllCustomers();
fillVT();
cursorAdapter.changeCursor(dbHelper.listAll());
}
}
I put the dbHelper.deleteAllCustomers() and fillVT() in the onActivityResult() so that it would update the virtual table every time a new item is added