1

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

GermainZ
  • 1,893
  • 3
  • 15
  • 20
  • Can you try concatenating all INSERTs separated by semicolon and passing it all at once? – PinnyM Dec 27 '12 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 Dec 27 '12 at 22:50

1 Answers1

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.

Community
  • 1
  • 1
Alexander Lucas
  • 22,171
  • 3
  • 46
  • 43
  • 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. – GermainZ Dec 27 '12 at 23:45
  • 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. – Alexander Lucas Dec 28 '12 at 00:39
  • 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;"` – GermainZ Dec 28 '12 at 00:47
  • 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 ;" – RTOSkit Dec 28 '12 at 17:44