Clubbing multiple sqlite statements in a single query

Question

I have a following table:

folderId	folderPath
1	"C:\Users\obama"
2	"C:\Users\biden"

Here, folderId is an autoincrement key. Now, insertion into the table has following steps: a)check if given folderPath exists in the table or not. b)If given folderPath exists then return folderId of that path. c)If folderPath doesn't exist then insert that folderPath into the table and return its folderId(last inserted rowId)

I want to add above steps in a single SQlite query for better performance. I am new to sqlite so any help would be greatly appreciated. Thanks

I tried to create a single sqlite query for the steps mentioned above. My expectation is to have a single query for better performance.

Answer 1

In short you cannot accomplish all the above in a single query. SQLite can only process a single statement at a time. You will always require 2 statements:-

One to insert or not and another to query the table to obtain the id (or vice-versa i.e. to query and then insert or not).

• the former would require that the folderpath has a unique index (the latter would probably be more efficient with an index on the folderpath).

However, the SQLiteDatabase's insert method does, under the cover, perform both the insert and obtains the rowid or an alias thereof (which the folderId column is according to your question in that the id is generated).

Furthermore, the SQLiteDatabase's insert method uses INSERT OR IGNORE ..... Thus not failing if there is a UNIQUE constrain conflict.

For these reasons it is probably easiest to utilise the SQliteDatabase's convenience insert method along with having a UNIQUE index on the folderPath column.

As for performance you should look into wrapping the inserts, if doing more than one at a time, within a single transaction.

The following is a working demonstration based upon the information available in your question:-

DBOpenHelper (of particular note is the insertOrNotFolderReturningFolderId method)

class DBOpenHelper extends SQLiteOpenHelper {

    /* DATABASE VALUES (constants)*/
    public static final String DATABASE_NAME = "the_database.db";
    public static final int DATABASE_VERSION = 1;
    /* FOLDER TABLKE VALUES (constants) */
    public static final String FOLDER_TABLE_NAME = "folder";
    public static final String FOLDER_TABLE_COLUMN_FOLDERID = "folderId";
    public static final String FOLDER_TABLE_COLUMN_FOLDERPATH = "folderPath";
    /*
        The Table create SQL noting the UNIQUE index on the folderPath column
     */
    private static final String FOLDER_TABLE_CREATE_SQL =
            "CREATE TABLE IF NOT EXISTS " + FOLDER_TABLE_NAME + "(" +
                    FOLDER_TABLE_COLUMN_FOLDERID + " INTEGER PRIMARY KEY " +
                    "," + FOLDER_TABLE_COLUMN_FOLDERPATH + " TEXT UNIQUE " +
                    ")";
    private volatile static DBOpenHelper instance;

    public static DBOpenHelper getInstance(Context context) {
        if (instance==null) {
            instance = new DBOpenHelper(context);
        }
        return instance;
    }
    private DBOpenHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(FOLDER_TABLE_CREATE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public long insertOrNotFolderReturningFolderId(String folderPath) {
        long rv;
        ContentValues cv = new ContentValues();
        cv.put(FOLDER_TABLE_COLUMN_FOLDERPATH,folderPath);
        SQLiteDatabase db = this.getWritableDatabase();
        rv = db.insert(FOLDER_TABLE_NAME,null,cv);
        if (rv < 0) {
            /* use the DatabaseUtils longForQuery to get the id (no need to manage a Cursor)*/
            rv = DatabaseUtils.longForQuery(
                    db,
                    "SELECT " + FOLDER_TABLE_COLUMN_FOLDERID +
                            " FROM " + FOLDER_TABLE_NAME +
                            " WHERE " + FOLDER_TABLE_COLUMN_FOLDERPATH + "=?",
                    new String[]{folderPath}
            );
        }
        return rv;
    }
}

To actually demonstrate then the following activity code:-

public class MainActivity extends AppCompatActivity {
    DBOpenHelper helper;
    
    /* folders to test (with duplicates on purpose) */
    String[] testlist = new String[]{
            "C:\\Users\\obama",
            "C:\\Users\\biden",
            "C:\\Users\\fred",
            "C:\\Users\\obama",
            "C:\\Users\\biden",
            "C:\\Users\\fred",
            "C:\\Users\\obama"
    };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        helper = DBOpenHelper.getInstance(this);
        SQLiteDatabase db = helper.getWritableDatabase();
        db.beginTransaction();
        /* insert rows as per the testlist */
        for (String folderPath: testlist) {
            long folderId = helper.insertOrNotFolderReturningFolderId(folderPath);
            Log.d("DBINFO","Folder " + folderPath + " has an ID of " + folderId);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        /* Extract ALL rows and write to the log */
        Cursor csr = helper.getWritableDatabase().query(DBOpenHelper.FOLDER_TABLE_NAME,null,null,null,null,null,null);
        int folderIdIx = csr.getColumnIndex(DBOpenHelper.FOLDER_TABLE_COLUMN_FOLDERID);
        int folderPathIx = csr.getColumnIndex(DBOpenHelper.FOLDER_TABLE_COLUMN_FOLDERPATH);
        while (csr.moveToNext()) {
            Log.d("DBINFO","FolderID=" + csr.getLong(folderIdIx) + "FolderPath=" + csr.getString(folderPathIx));
        }
        csr.close();
    }
}

Results output to the log:-

2023-11-01 07:38:22.749 D/DBINFO: Folder C:\Users\obama has an ID of 1
2023-11-01 07:38:22.749 D/DBINFO: Folder C:\Users\biden has an ID of 2
2023-11-01 07:38:22.750 D/DBINFO: Folder C:\Users\fred has an ID of 3
2023-11-01 07:38:22.753 D/DBINFO: Folder C:\Users\obama has an ID of 1
2023-11-01 07:38:22.755 D/DBINFO: Folder C:\Users\biden has an ID of 2
2023-11-01 07:38:22.764 D/DBINFO: Folder C:\Users\fred has an ID of 3
2023-11-01 07:38:22.769 D/DBINFO: Folder C:\Users\obama has an ID of 1


2023-11-01 07:38:22.773 D/DBINFO: FolderID=1FolderPath=C:\Users\obama
2023-11-01 07:38:22.773 D/DBINFO: FolderID=2FolderPath=C:\Users\biden
2023-11-01 07:38:22.773 D/DBINFO: FolderID=3FolderPath=C:\Users\fred

i.e. only the 3 rows exists (last section) out of the 7 attempted inserts from the testlist but the respective folderid has been returned (first section)