Batch editing an SQLite db

Rolf Nielsen rmg1970swe at gmail.com
Sat Feb 14 02:11:51 UTC 2015


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

On 2015-02-14 02:33, Polytropon wrote:
> On Fri, 13 Feb 2015 21:49:53 +0100, Rolf Nielsen wrote:
>> I recently changed from one ROM to another on my Android phone.
>> The old one and the new one use different mount points for the
>> external SD card, and because of this the music playlists I've
>> painstakingly created are utterly useless. The player refuses to
>> even open them, because the files in them are non-existent. These
>> are SQLite database files, and I've been able to open them with
>> the SQLiteEditor app for Android, but I've not found an option to
>> replace all occurances of a string with something else. I have
>> eight playlists with some 500 songs each, so editing them
>> manually would be tedious. Is there something in the ports tree
>> that can do this? SQL isn't a language I speak fluently, so I'd
>> need some pointers as well. Basically I need to substitute
>> "sdcard1" for "extSdcard".
> 
> Do you have r/w access to the database file and a SQLite 
> installation you can use? Then you can use its replace() function
> with an UPDATE command:
> 
> UPDATE <tableneme> SET <entry> = replace(<entry>, 'sdcard1',
> 'extSdcard') WHERE <entry> LIKE '%sdcard1%';
> 
> Here <tablename> is the name of the table which contains the
> playlist entries, and <entry> is the "column name" under which
> playlist entriy paths are stored.
> 
> "Sadly", I don't have to mess with SQL regularly anymore. So not
> tested. :-)
> 
> 

I have root access to my phone, so r/w access isn't going to be a
problem. I also have SQLite installed (as a dependency of some other
port(s)) on my home computer. I will try this during the weekend. If
I'm not mistaken, you and I are in the same TZ, which means we're both
up so late it's actually early... Suffice it to say, I'm not up for
the task right now. I'll report back once I've tried it.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBCAAGBQJU3q7XAAoJEPJMW41Co4JgsncP/3x+qxqvC2WxAJccvep51TFz
gbMFIpej4aYkte1u9outePPP5QATmyEu8bpkxP1H37gcuqyPBw5eT2ZLoQmF8QER
8Ghxwh4mV2+wvyW3XmaxGxIC01wOcZfHmPRFfGv43laIjzMaV50EBmN4Wuy+HOPT
k/AVgwAjMRFhqJV7sncwLgRkIHxQ9J6Hlw+sPscEMPYQHt+A07THCwoNgBQuC529
CLK/qCHi4KVnEy/NWS4ucGphCoIL4wjmdYhDQEipMRxJtby+GnIeE5P4YjJ8J/+F
sgvLrzYNmFuwJ7Coi5ixLxtfBoVjKu1ym/9GjHWAUNfv+wkHgwAvMD4yA6gNaPWv
dVkIrETawSgPX2RSzbz36vinMpPVULoR8AmRtb8tNR8OdRYXT2eZteQ7xUIpBQJC
ET7QrFFDOvLi8ORlqWlJLjEjV4ziQoJEzq4T9f0CLmzez+1uK3qKC0vKIpHQ9nf2
D7iFHjMmtOIN72o+aytg2ZLU8twcTupbI4OBSdEXft5Nzx/gSu9IddiVs1E/Q7Bp
ID0Etoy8ITwE8iiFWj6cmoUmeZx/rMjmtE5kbaauGVaeKl8r6srnrqcJlIXn3Pq5
43RC5PrlJi0SXALVT4DVOc0yZGDSyOP9kF89UyOMlZjhzAxN3jW7LNKHdR1FA4yE
ewNYyOZjgwsgzJIGw08O
=aRxG
-----END PGP SIGNATURE-----


More information about the freebsd-questions mailing list