0

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?

The Dude
  • 1,088
  • 7
  • 16
  • 30
  • hey look i think its not that easy to update data from one table to another. i can give you hint how to do it. check weather your virtual table needs to be updated. and if yes than select that only data in cursor from main table and insert it using for loop. you should define 1 extra column for checking table if its need to be update. – Aiyaz Parmar Nov 01 '14 at 12:57
  • Is there any other solution than updating it, so that the new data will be included when searching thru the virtual table. How can I populate the VT with the content from the original table? – The Dude Nov 01 '14 at 13:01
  • i think you should add one extra column in both table say "version" and first time you copy data to VT your VT data will contain version=1 now when you want to update your main table than increment value of new data to version:2 now check from VT table that is it having version=2 if no than insert all the data of version=2 to VT. – Aiyaz Parmar Nov 01 '14 at 13:04
  • But how can I insert all the data? SQL statment? thats my main problem. you got an example? – The Dude Nov 01 '14 at 13:07
  • you can't do it with one sql statement.. you will need for loop for that. if there's available way in SQL than i don't know about it. but as per my information you should use for loop that will fire insert query one by one. – Aiyaz Parmar Nov 01 '14 at 13:09

1 Answers1

1

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

The Dude
  • 1,088
  • 7
  • 16
  • 30