[SOLVED] Help with SQLite3 update

[jass199012]

Hi.
Can anyone please guide me on how to upgrade SQLite3 to version 3.33 or above? Or is this even possible at all?
I am running moode 7.6 and noticed it has SQLite3 version 3.27.

I am using a database file stored in a tmpfs mount to store some variables and track changes in their values. The particular feature I am looking for is the UPDATE FROM statement, which is supported only in SQLite 3.33 onwards.
https://www.sqlite.org/lang_update.html

While in buster repository, the latest available version is 3.27, bullseye has 3.34. I tried pointing the apt sources to bullseye repository, but it didn't work. Direct installation of deb package shows missing dependencies. I didn't try too much afterwards as I feared I might break the system. Thank you.

[the_bertrum]

I don't think it is recommended to upgrade parts of a moOde system like this.

[TheOldPresbyope]

@jass199012

Looks pretty trivial to build sqlite3 from source. Here's what I did as a test.

Code:

pi@CM4:~ $ wget https://www.sqlite.org/2022/sqlite-amalgamation-3370200.zip
--2022-01-26 12:29:19--  https://www.sqlite.org/2022/sqlite-amalgamation-3370200.zip
Resolving www.sqlite.org (www.sqlite.org)... 2600:3c00::f03c:91ff:fe96:b959, 45.33.6.223
Connecting to www.sqlite.org (www.sqlite.org)|2600:3c00::f03c:91ff:fe96:b959|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2502722 (2.4M) [application/zip]
Saving to: ‘sqlite-amalgamation-3370200.zip’

sqlite-amalgamation 100%[===================>]   2.39M  2.07MB/s    in 1.2s    

2022-01-26 12:29:21 (2.07 MB/s) - ‘sqlite-amalgamation-3370200.zip’ saved [2502722/2502722]

pi@CM4:~ $ unzip sqlite-amalgamation-3370200.zip
Archive:  sqlite-amalgamation-3370200.zip
  creating: sqlite-amalgamation-3370200/
 inflating: sqlite-amalgamation-3370200/sqlite3.c  
 inflating: sqlite-amalgamation-3370200/shell.c  
 inflating: sqlite-amalgamation-3370200/sqlite3ext.h  
 inflating: sqlite-amalgamation-3370200/sqlite3.h  
pi@CM4:~ $ cd sqlite-amalgamation-3370200
pi@CM4:~/sqlite-amalgamation-3370200 $ gcc shell.c sqlite3.c -lpthread -ldl -lm -o sqlite3
pi@CM4:~/sqlite-amalgamation-3370200 $ ./sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

where the lines beginning "pi@CM4:~xxx $ " contain the commands I entered. This all too less than a minute or two on my CM4 connected wirelessly to a reasonably fast ISP.

Regards,
Kent

[jass199012]

Thanks @the_bertrum and Kent for your response.
Build from source looks straightforward indeed. Please excuse my ignorance if my question doesn't make sense. Does build from source replace the existing installation with a fresh one, or does it update the existing installation. I mean, would moodeaudio be able to acess its existing internal database files with the new build, or would it lose them all?
I appreciate the possibility of no ready answer to the question and I am willing to test it out if need be.

-Jas

[TheOldPresbyope]

Thanks @the_bertrum and Kent for your response.
Build from source looks straightforward indeed. Please excuse my ignorance if my question doesn't make sense. Does build from source replace the existing installation with a fresh one, or does it update the existing installation. I mean, would moodeaudio be able to acess its existing internal database files with the new build, or would it lose them all?
I appreciate the possibility of no ready answer to the question and I am willing to test it out if need be.

-Jas

If you look at my example output you'll see that the new sqlite3 binary is written to the directory I built in. The next step would be to copy it into the /usr/bin directory in place of the existing sqlite3 v3.27.2. To continue my example, something like:

Code:

cd /usr/bin
# save the existing binary just in case
sudo mv sqlite3 sqlite3.dist
# copy in the new binary
sudo cp /home/pi/sqlite-amalgamation-3370200/sqlite3 .
# reboot is the brute-force way to make sure the new binary is used
sudo reboot

Since the database file for moOde, including its schema, etc., is stored in /var/local/www/db/moode-sqlite3.db it's safe.

Regards,
Kent

[the_bertrum]

Since the database file for moOde, including its schema, etc., is stored in /var/local/www/db/moode-sqlite3.db it's safe.

This is the bit that had me worried, I didn't think the updated version would be separate from the moOde database.

Live and learn.

[jass199012]

I just executed all the steps Kent shared. It worked perfectly. And I am running a playlist that I created before the update. Everything is working fine. Thanks for the help.