SQLite: how to to use `WITH` clause for recursive queries in Android using Room
I'm trying to implement a recursive query using the `WITH` clause in SQLite for my Android app that uses Room as the ORM. Specifically, I want to retrieve all child categories in a hierarchical category structure. My SQLite version is 3.31.1, and I'm using Room version 2.4.0. Here's my table structure: ```sql CREATE TABLE categories ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES categories(id) ); ``` I have added some sample categories: ```sql INSERT INTO categories (name, parent_id) VALUES ('Electronics', NULL), ('Computers', 1), ('Laptops', 2), ('Desktops', 2), ('Smartphones', 1); ``` I want to execute a recursive query that fetches all the child categories of 'Electronics'. My Room DAO looks like this: ```kotlin @Dao interface CategoryDao { @Query("WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id FROM categories WHERE name = :categoryName UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree") fun getAllSubCategories(categoryName: String): List<Category>; } ``` However, when I run this query, I get the following behavior: **"SQLite database locked"**. I've ensured that I'm not using transactions that could lock the database. I even tried running it directly in a SQLite browser, and it works as expected. Could this be related to how Room handles database connections or threading? I've tried running the query on a background thread using RxJava, but the same behavior continues. Any insights on resolving this scenario would be greatly appreciated!