Solved: Help with SQLite3 update - Printable Version

Moode Forum

[SOLVED] Help with SQLite3 update - Printable Version

+- Moode Forum (https://moodeaudio.org/forum)
+-- Forum: Community (https://moodeaudio.org/forum/forumdisplay.php?fid=18)
+--- Forum: General Discussion (https://moodeaudio.org/forum/forumdisplay.php?fid=21)
+--- Thread: [SOLVED] Help with SQLite3 update (/showthread.php?tid=4690)

Help with SQLite3 update - jass199012 - 01-26-2022

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.

RE: Help with SQLite3 update - the_bertrum - 01-26-2022

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

RE: Help with SQLite3 update - TheOldPresbyope - 01-26-2022

@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

RE: Help with SQLite3 update - jass199012 - 01-26-2022

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

RE: Help with SQLite3 update - TheOldPresbyope - 01-26-2022

(01-26-2022, 06:54 PM)jass199012 Wrote: 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

RE: Help with SQLite3 update - the_bertrum - 01-27-2022

(01-26-2022, 08:10 PM)TheOldPresbyope Wrote: 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.

RE: Help with SQLite3 update - jass199012 - 01-27-2022

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.