Android room composite primary key, sort order

Question

In order to migrate old db to room library, I have to make equivalent to line

primary key(id1, id2 desc)

from

create table t(
id1 integer not null, 
id2 integer not null,
.... 
primary key(id1, id2 desc)
)

I can't find how to define sorting order of composite indices or primary keys. It seems like Android room haven't particular annotations, or Annotation parameters if see androidx.room.Entity or androidx.room.Index.

Is it not supported? What are other ways to solve the problem? Create regular composite key on id1 and id2, and in all select queries set order by ?

matreshkin · Answer 1 · 2021-01-20T15:13:53.293

Unless your table is WITHOUT ROWID, the primary key in your table is just a separate unique index.

So you just have to create your own index after the table is created.

Is an index needed for a primary key in SQLite?

Look at the sqlite3 example - sqlite just creates a separate sqlite_autoindex_t_with_pk_1 for you along with manually created indices.

sqlite> create table t_with_pk(id1 integer not null, id2 integer not null, primary key(id1, id2 desc));

sqlite> create table t_no_pk(id1 integer not null, id2 integer not null);    
sqlite> create unique index t_uniq_idx on t_no_pk(id1, id2 desc);

sqlite> select * from sqlite_master;

table|t_with_pk|t_with_pk|2|CREATE TABLE t_with_pk(id1 integer not null, id2 integer not null, primary key(id1, id2 desc))
index|sqlite_autoindex_t_with_pk_1|t_with_pk|3|
table|t_no_pk|t_no_pk|4|CREATE TABLE t_no_pk(id1 integer not null, id2 integer not null)
index|t_uniq_idx|t_no_pk|5|CREATE UNIQUE INDEX t_uniq_idx on t_no_pk(id1, id2 desc)

The behavior is also identical

sqlite> explain query plan select * from t_with_pk order by id1, id2 desc;
QUERY PLAN
`--SCAN TABLE t_with_pk USING COVERING INDEX sqlite_autoindex_t_with_pk_1
sqlite> explain query plan select * from t_no_pk order by id1, id2 desc;
QUERY PLAN
`--SCAN TABLE t_no_pk USING COVERING INDEX t_uniq_idx

Note also (regarding @Developer's answer) that just inverting the order in a SELECT statements requires sqlite to create a temp index

sqlite> explain query plan select * from t_no_pk order by id1, id2;
QUERY PLAN
|--SCAN TABLE t_no_pk USING COVERING INDEX t_uniq_idx
`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

score 0 · Accepted Answer · answered Feb 26 '21 at 13:14

0

Room supports raw queries via RoomDatabase.Callback so you can override onCreate method of callback and execute raw sql of creating index.

answered Feb 26 '21 at 13:14

theroom101

599
1
9
23

score -1 · Answer 3 · answered Jan 20 '21 at 08:43

-1

You can do somehow like

SELECT select_list FROM table ORDER BY column_1 ASC, column_2 DESC;

for details https://www.sqlitetutorial.net/sqlite-order-by/

answered Jan 20 '21 at 08:43

Developer

101
1
11

sorry, I should specified that I need to do it by Android.Room first, but yeah, thanks, I thought about extends sql queries by **order by** – theroom101 Jan 20 '21 at 08:56
You can get idea from here https://stackoverflow.com/questions/55297165/room-dao-order-by-asc-or-desc-variable – Developer Jan 20 '21 at 09:01

Android room composite primary key, sort order

3 Answers3