Sqlite : Unique makes my database go crazy

Question

I'm having an issue with my app. What my app does is this : gets some data from a couple of edittexts(3 per row,created dynamically) and puts them in a database .

What i want the database to do is this : take the product name,the quantity and the price and put them in the table.The name should be UNIQUE(it will be used to power an autocomplete,it needs to be unique not to have duplicates in the AC list).The price in the database must be the last price inserted for that product(for example,if Cheese at 3$ is inserted and after that Cheese at 2.5$ in the database we will find 2.5$).The quantity has to be summed up(if i enter Cheese in quantity 3 and then again Cheese in quantity 4 in the database we will find 7).

Now,my issue : Lets say i enter this in my shopping list :

 1.     Hhhh    4   2.5
 2.     Dddd    3   1
 3.     Eeee    2   2
 4.     Aaaa    5   3.5

In my database I will find this :

4.      Aaaa    4   2.5
2.      Dddd    3   1
3.      Eeee    2   2
1.      Hhhh    5   3.5

So,the issue is that it arranges the product name column alphabetically but the other columns remain in the same order,the one i entered in the edittexts. I did some tests,if i remove the UNIQUE from the product name column,it will enter it as it should but of course,it will create duplicates,which i don't need.I don't get it,what's wrong ? why does UNIQUE trigger this ?

Here's my code :

My table creation :

public class SQLiteCountryAssistant extends SQLiteOpenHelper {
private static final String DB_NAME = "usingsqlite.db";
private static final int DB_VERSION_NUMBER = 1;
private static final String DB_TABLE_NAME = "countries";
private static final String DB_COLUMN_1_NAME = "country_name";
private static final String DB_COLUMN_2_NAME = "country_counter";
private static final String DB_COLUMN_3_NAME = "country_price";


private static final String DB_CREATE_SCRIPT = "create table "
        + DB_TABLE_NAME
        + " (_id INTEGER PRIMARY KEY,country_name text unique, country_quantity REAL DEFAULT '0',country_price REAL);) ";

private SQLiteDatabase sqliteDBInstance = null;

public SQLiteCountryAssistant(Context context) {
    super(context, DB_NAME, null, DB_VERSION_NUMBER);
}

@Override
public void onCreate(SQLiteDatabase sqliteDBInstance) {
    Log.i("onCreate", "Creating the database...");
    sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);
}

My insert method :

public void insertCountry(String countryName, String countryPrice,
        String countryQuantity) {

    sqliteDBInstance.execSQL("INSERT OR IGNORE INTO " + DB_TABLE_NAME
            + "(country_name, country_quantity, country_price) VALUES('"
            + countryName + "','0', '" + countryPrice + "')");

    sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
            + " SET country_name='" + countryName
            + "', country_quantity=country_quantity+'" + countryQuantity
            + "' WHERE country_name='" + countryName + "';");

    sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
            + " SET country_name='" + countryName + "', country_price='"
            + countryPrice + "' WHERE country_name='" + countryName + "';");
}

And this is how i call the insert method :

    for (int g = 0; g < allcant.size() - 1; g++) {
        if (prod[g] != "0.0") {
            sqlliteCountryAssistant.insertCountry(prod[g],pret[g],cant[g]);
        }

Also,please excuse my messy code,i've started learning android with no programming background like a month ago.I just got my bachelors degree in Sociology so yea,i'm an absolute beginner.If there is way to do it better then i did and i'm pretty sure there is,please,show me the way,heh.

Thanks and have a good day !

EDIT : Aaaand the whole db class :

public class SQLiteCountryAssistant extends SQLiteOpenHelper {
private static final String DB_NAME = "usingsqlite.db";
private static final int DB_VERSION_NUMBER = 1;
private static final String DB_TABLE_NAME = "countries";
private static final String DB_COLUMN_1_NAME = "country_name";
private static final String DB_COLUMN_2_NAME = "country_counter";
private static final String DB_COLUMN_3_NAME = "country_price";

private static final String DB_CREATE_SCRIPT = "create table "
        + DB_TABLE_NAME
        + " ( id INTEGER PRIMARY KEY AUTOINCREMENT,country_name text unique, country_quantity REAL DEFAULT '0',country_price REAL)";

private SQLiteDatabase sqliteDBInstance = null;

public SQLiteCountryAssistant(Context context) {
    super(context, DB_NAME, null, DB_VERSION_NUMBER);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO: Implement onUpgrade

}

@Override
public void onCreate(SQLiteDatabase sqliteDBInstance) {
    Log.i("onCreate", "Creating the database...");
    sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);
}

public void openDB() throws SQLException {
    Log.i("openDB", "Checking sqliteDBInstance...");
    if (this.sqliteDBInstance == null) {
        Log.i("openDB", "Creating sqliteDBInstance...");
        this.sqliteDBInstance = this.getWritableDatabase();

    }
}

public void closeDB() {
    if (this.sqliteDBInstance != null) {
        if (this.sqliteDBInstance.isOpen())
            this.sqliteDBInstance.close();
    }
}

public void insertCountry(String countryName, String countryPrice,
        String countryQuantity) {
    ContentValues cv = new ContentValues();
    cv.put("country_name", countryName);
    cv.put("country_price", countryPrice);
    sqliteDBInstance.insertWithOnConflict(DB_TABLE_NAME, null, cv, sqliteDBInstance.CONFLICT_IGNORE);

    // Increment the quantity field (there isn't a good way to do this with sql.update() )
    sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME + " SET country_quantity=country_quantity+? WHERE country_name=?",
      new Object[] { new Long(countryQuantity), countryName }); 








    /*sqliteDBInstance.execSQL("INSERT OR IGNORE INTO " + DB_TABLE_NAME
            + "(country_name) VALUES('" + countryName + "')");

    sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
            + " SET country_quantity=country_quantity+" + countryQuantity
            + " WHERE country_name='" + countryName + "';");

    sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
            + " SET country_price=" + countryPrice
            + " WHERE country_name='" + countryName + "';");*/
}

public boolean removeCountry(String countryName) {
    int result = this.sqliteDBInstance.delete(DB_TABLE_NAME,
            "country_name='" + countryName + "'", null);

    if (result > 0)
        return true;
    else
        return false;
}

public long updateCountry(String oldCountryName, String newCountryName) {
    ContentValues contentValues = new ContentValues();
    contentValues.put(DB_COLUMN_1_NAME, newCountryName);
    return this.sqliteDBInstance.update(DB_TABLE_NAME, contentValues,
            "country_name='" + oldCountryName + "'", null);
}

public String[] getAllCountries() {
    Cursor cursor = this.sqliteDBInstance.query(DB_TABLE_NAME,
            new String[] { DB_COLUMN_1_NAME }, null, null, null, null,
            DB_COLUMN_1_NAME + " ASC");

    if (cursor.getCount() > 0) {
        String[] str = new String[cursor.getCount()];
        // String[] strpri = new String[cursor.getCount()];
        int i = 0;

        while (cursor.moveToNext()) {
            str[i] = cursor.getString(cursor
                    .getColumnIndex(DB_COLUMN_1_NAME));
            // strpri[i] = cursor.getString(cursor
            // .getColumnIndex(DB_COLUMN_2_NAME));
            i++;
        }
        return str;
    } else {
        return new String[] {};
    }
}

}

Answer 1

A couple of things to add to @WarrenFaith's excellent suggestions. I agree that the error is probably in code you haven't shown.

1. The quotes around the increment value in the UPDATE SQL are wrong. Should be e.g. quantity=quantity+42, not quantity=quantity+'42'
2. You need to use argument escapes (question marks ?) to avoid problems including SQL insertion attacks on your app.
3. The insert logic is insanely complicated. Perhaps this is where the problem lies.

You want something like:

// Insert or ignore.
ContentValues cv = new ContentValues();
cv.put("country_name", country_name);
cv.put("country_price", country_price);
sql.insertWithOnConflict(DB_TABLE_NAME, null, cv, CONFLICT_IGNORE);

// Increment the quantity field (there isn't a good way to do this with sql.update() )
sql.execSQL("UPDATE " + DB_TABLE_NAME + " SET country_quantity=country_quantity+? WHERE country_name=?",
  new Object[] { new Long(country_quantity), country_name }); 

AND you didn't mention if the LogCat is clean. It must be showing DB errors at least regarding the quotes problem. Also suggest you make sure the table is dropped and rebuilt between debugging runs.

Answer 2

I haven't figured out the crazy order but I found two things that might even clear something up:

1. your create table sql has one closing bracket too much (remove the one after the semicolon)
2. your insert method is really messy :) I would split it into two methods.

The general approach would be to create an insertOrUpdate method that queries the database for the entry (in your case the countryName). If an entry exist, it will be updated, if not it will be inserted. As you are a beginner, this might be a good task to do that by yourself, you should get the basic code here on SO in different questions.

The final tip (you might have seen it already): Use the parameter version and/or the real update/insert methods from the database.

db.insert(TABLE_NAME, null, contentValues); // see class ContentValues for details

According to the execSQL() method, you shouldn't use that for any SELECT/INSERT/UPDATE/DELETE statement:

Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE.

Now my question which answer might help me to help you:
I would also like to know how you verified the order of your database content? Have you created a query in your android code where you query the content or have you opened the db file with a SQLite manager tool? If you query, can you include your query/display code in your question, too?