Efficient way to insert rows in database from the android shell?

Question

I'm looking for a way to insert ~200 rows in a database efficiently, using the android shell exclusively.

The relevant part of the script I'm using is:

while read line
do
    uid=`echo $line " awk '{print($2)}'`
    pkg=`echo $line " awk '{print($1)}'`
    /system/xbin/sqlite3 /mnt/sdcard/apps.db "INSERT INTO app_list values($uid, '$pkg', 0, 0, 0, 0, 0, 0)
done < /data/system/packages.list

The goal of this script is to generate a database containing a list of all installed apps (UID/Package name). The other columns are to be used later.

For ~200 apps, this script takes a few minutes: every sqlite command takes ~2 seconds to execute. This should be greatly improved by using transactions, but I see no clear way to do that.

Any help would be greatly appreciated,

Thanks.

EDIT:

In response to post #1:
Changed:
qry=$qry ($uid, '$pkg', 0, 0, 0, 0, 0, 0), to qry=$qry" ($uid, '$pkg', 0, 0, 0, 0, 0, 0),"
and /system/xbin/sqlite3 /mnt/sdcard/apps.db $qry to /system/xbin/sqlite3 /mnt/sdcard/apps.db "$qry;"
Echoing the last command shows:
/system/xbin/sqlite3 /mnt/sdcard/apps.db INSERT INTO app_list VALUES (10028, 'com.google', 0, 0, 0, 0, 0, 0), (10048, 'com.google.something', 0, 0, 0, 0, 0, 0), ... (10062, 'com.google.somethingelse', 0, 0, 0, 0, 0, 0);
Which seems correct, but outputs the following error when executed:
SQL error: near ",": syntax error

Can you try concatenating all INSERTs separated by semicolon and passing it all at once? — PinnyM
– PinnyM, Commented Dec 27, 2012 at 22:09
you could run your input file thru an awk script to create a batch of insert statments (if a tmp file), then redirect that tmpfile into sqllite3 (with any required extra stuff at the top and bottom). Good luck. — shellter
– shellter, Commented Dec 27, 2012 at 22:50

Community · Accepted Answer · 2017-05-23 11:56:41Z

2

Most of that 2-seconds-per-row is probably being spent on starting up a new instance of sqlite each time the command runs and exiting after, so you can get a lot of speedup by just adding all the rows at once.

You can insert multiple rows in a single SQL query, so it'd probably be better to just concatonate an enormous string with all the insert values. The bash script would look something like this.

qry=""
while read line
do
    uid=`echo $line " awk '{print($2)}'`
    pkg=`echo $line " awk '{print($1)}'`
    qry="$qry INSERT INTO app_list VALUES ($uid, '$pkg', 0, 0, 0, 0, 0, 0);"

done < /data/system/packages.list

/system/xbin/sqlite3 /mnt/sdcard/apps.db "$qry;"

EDIT:

To Clarify, this script does not use the "INSERT INTO TBL VALUES (row 1 vals), (row 2 vals), (row 3 vals);" syntax, because it's not supported by SQLite.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Dec 27, 2012 at 23:08

Alexander Lucas

22.4k3 gold badges49 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

GermainZ Over a year ago

Thanks for the answer. I slightly modified it and tried it out, but it's giving me a syntax error even tho everything seems correct. Please see the edit for the details.

Alexander Lucas Over a year ago

Okay- So after researching a bit apparently Sqlite doesn't support the syntax for multiple values in one insert-statement. I've revised the sample script to just generate a bunch of insert statements. It should still save plenty of execution time, though, as sqlite only needs to be opened once.

GermainZ Over a year ago

Alright, it executes correctly now. The execution time is pretty much the same (to be fair, I think it went down from ~3-4m to ~1.5m, but that's only an impression judging from the screen timeout and my memory). However, I can now easily add BEGIN; to the start and COMMIT; to the end of the command, and that makes things much faster (~5s total, the actual sqlite command is instantaneous): /system/xbin/sqlite3 /mnt/sdcard/apps.db "BEGIN; $qry; COMMIT;"

RTOSkit Over a year ago

Using atomic commit sqlite, speed is similar to working with a RAM database , but you should consider that if one stop "atomic commit", lose all sql sentence executed between "BEGIN;" and "COMMIT ;"

Collectives™ on Stack Overflow

Efficient way to insert rows in database from the android shell?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related