Sqlite - Study Notes 1

 

zzh@ZZHPC:~$ sudo apt install sqlite3

 

zzh@ZZHPC:~$ 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> .load csv
Error: csv.so: cannot open shared object file: No such file or directory

 

Downloaded the source code file and extracted it to ~/Downloads/sqlite-src-3490000:

zzh@ZZHPC:~/Downloads/sqlite-src-3490000$ ./configure
......

zzh@ZZHPC:~/Downloads/sqlite-src-3490000$ make
......

zzh@ZZHPC:~/Downloads/sqlite-src-3490000$ sudo make install
/usr/bin/install -d "/usr/local/lib/pkgconfig"
/usr/bin/install -m 0644 sqlite3.pc "/usr/local/lib/pkgconfig"
/usr/bin/install libsqlite3.so "/usr/local/lib"
Setting up libsqlite3.so symlinks...
lrwxrwxrwx 1 root root      20 Feb 10 14:00 libsqlite3.so -> libsqlite3.so.3.49.0
lrwxrwxrwx 1 root root      20 Feb 10 14:00 libsqlite3.so.0 -> libsqlite3.so.3.49.0
-rwxr-xr-x 1 root root 4925880 Feb 10 14:00 libsqlite3.so.3.49.0
/usr/bin/install -m 0644 libsqlite3.a "/usr/local/lib"
/usr/bin/install -m 0644 sqlite3.h "/home/zzh/Downloads/sqlite-src-3490000/src/sqlite3ext.h" "/usr/local/include"
. ./.tclenv.sh || exit $?; \
/usr/bin/install -d "$TCLLIBDIR"; \
/usr/bin/install libtclsqlite3.so "$TCLLIBDIR"; \
/usr/bin/install -m 0644 pkgIndex.tcl "$TCLLIBDIR"
/usr/bin/install sqlite3 "/usr/local/bin"
/usr/bin/install -d "/usr/local/share/man/man1"
/usr/bin/install -m 0644 "/home/zzh/Downloads/sqlite-src-3490000/sqlite3.1" "/usr/local/share/man/man1"

 

zzh@ZZHPC:~$ which sqlite3
/usr/local/bin/sqlite3

 

The issue still existed:

zzh@ZZHPC:~$ sqlite3
SQLite version 3.49.0 2025-02-06 11:55:18
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load csv
Error: csv.so: cannot open shared object file: No such file or directory
sqlite>

 

Had to manually create the csv.so file:

zzh@ZZHPC:~/Downloads/sqlite-src-3490000/ext/misc$ lh csv.c
-rw-rw-r-- 1 zzh zzh 29K Feb  6 21:59 csv.c
zzh@ZZHPC:~/Downloads/sqlite-src-3490000/ext/misc$ gcc -fPIC -shared -o csv.so csv.c -I../../ -lsqlite3
zzh@ZZHPC:~/Downloads/sqlite-src-3490000/ext/misc$ lh csv*
-rw-rw-r-- 1 zzh zzh 29K Feb  6 21:59 csv.c
-rwxrwxr-x 1 zzh zzh 26K Feb 10 14:17 csv.so

 

Couldn't remove the sqlite3 I manually installed using 'make install':

zzh@ZZHPC:~$ sudo apt remove sqlite3
[sudo] password for zzh: 
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Package 'sqlite3' is not installed, so not removed
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

 

Couldn't uninstall from the source code:

zzh@ZZHPC:~/Downloads/sqlite-src-3490000$ sudo make uninstall
make: *** No rule to make target 'uninstall'.  Stop.

 

Manually deleted the installed files according to the log:

zzh@ZZHPC:/usr/local/lib/pkgconfig$ ll
total 4
-rw-r--r-- 1 root root 271 Feb 10 14:00 sqlite3.pc
zzh@ZZHPC:/usr/local/lib/pkgconfig$ sudo rm sqlite3.pc

 

zzh@ZZHPC:/usr/local/lib$ ll
total 13704
-rw-r--r-- 1 root root 9093678 Feb 10 14:00 libsqlite3.a
lrwxrwxrwx 1 root root      20 Feb 10 14:00 libsqlite3.so -> libsqlite3.so.3.49.0
lrwxrwxrwx 1 root root      20 Feb 10 14:00 libsqlite3.so.0 -> libsqlite3.so.3.49.0
-rwxr-xr-x 1 root root 4925880 Feb 10 14:00 libsqlite3.so.3.49.0
drwxr-xr-x 2 root root    4096 Feb 10 14:29 pkgconfig
drwxr-xr-x 3 root root    4096 Aug 15 18:43 python3.10
zzh@ZZHPC:/usr/local/lib$ sudo rm libsqlite3*
zzh@ZZHPC:/usr/local/lib$ sudo rmdir pkgconfig

 

zzh@ZZHPC:/usr/local/include$ ll
total 684
-rw-r--r-- 1 root root  38149 Feb 10 14:00 sqlite3ext.h
-rw-r--r-- 1 root root 658960 Feb 10 14:00 sqlite3.h
zzh@ZZHPC:/usr/local/include$ sudo rm sqlite3*

 

zzh@ZZHPC:~/Downloads/sqlite-src-3490000$ grep TCLLIBDIR .tclenv.sh
TCLLIBDIR=/usr/share/tcltk/tcl8.6/sqlite3

 

zzh@ZZHPC:/usr/share/tcltk/tcl8.6$ lh
total 324K
-rw-r--r-- 1 root root 4.8K Mar 25  2022 word.tcl
-rw-r--r-- 1 root root  12K Mar 25  2022 tm.tcl
-rw-r--r-- 1 root root 5.5K Mar 25  2022 tclIndex
-rw-r--r-- 1 root root 4.5K Mar 25  2022 tclAppInit.c
-rw-r--r-- 1 root root  40K Mar 25  2022 safe.tcl
-rw-r--r-- 1 root root  816 Mar 25  2022 parray.tcl
-rw-r--r-- 1 root root  23K Mar 25  2022 package.tcl
-rw-r--r-- 1 root root  25K Mar 25  2022 init.tcl
-rw-r--r-- 1 root root 7.8K Mar 25  2022 history.tcl
-rw-r--r-- 1 root root 126K Mar 25  2022 clock.tcl
-rw-r--r-- 1 root root  21K Mar 25  2022 auto.tcl
drwxr-xr-x 3 root root 4.0K Feb 23  2023 tcl8
drwxr-xr-x 2 root root 4.0K Feb 23  2023 opt0.4
drwxr-xr-x 2 root root 4.0K Feb 23  2023 msgs
drwxr-xr-x 2 root root 4.0K Feb 23  2023 http1.0
drwxr-xr-x 2 root root 4.0K Feb 23  2023 encoding
drwxr-xr-x 4 root root 4.0K Feb 10  2024 ..
drwxr-xr-x 8 root root 4.0K Feb 10 14:00 .
drwxr-xr-x 2 root root 4.0K Feb 10 14:00 sqlite3
zzh@ZZHPC:/usr/share/tcltk/tcl8.6$ sudo rm -rf sqlite3

 

zzh@ZZHPC:/usr/local/bin$ lh sqlite*
-rwxr-xr-x 1 root root 6.7M Feb 10 14:00 sqlite3
zzh@ZZHPC:/usr/local/bin$ sudo rm sqlite3

 

zzh@ZZHPC:/usr/local/share/man/man1$ lh sqlite*
-rw-r--r-- 1 root root 4.3K Feb 10 14:00 sqlite3.1
zzh@ZZHPC:/usr/local/share/man/man1$ sudo rm sqlite3.1

 

Installed sqlite3 again:

zzh@ZZHPC:~$ sudo apt install sqlite3

 

Copied the manually generated csv.so file to /usr/local/lib:

zzh@ZZHPC:~/Downloads/sqlite-src-3490000/ext/misc$ sudo cp csv.so /usr/local/lib

zzh@ZZHPC:/usr/local/lib$ ll
total 32
-rwxr-xr-x 1 root root 26072 Feb 10 14:52 csv.so
drwxr-xr-x 3 root root 4096 Aug 15 18:43 python3.10

 

zzh@ZZHPC:~$ 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> .load csv
Error: csv.so: cannot open shared object file: No such file or directory
sqlite> .load /usr/local/lib/csv.so
sqlite>

 

sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename="sample.csv", header=true);
sqlite> SELECT * FROM t1;
apples|3
oranges|5
bananas|10
sqlite> .mode box
sqlite> SELECT * FROM t1;
┌─────────┬───────┐
│  item   │ count │
├─────────┼───────┤
│ apples  │ 3     │
│ oranges │ 5     │
│ bananas │ 10    │
└─────────┴───────┘
sqlite> .shell clear

 

sqlite> SELECT * FROM generate_series(1, 20);
┌───────┐
│ value │
├───────┤
│ 1     │
│ 2     │
│ 3     │
│ 4     │
│ 5     │
│ 6     │
│ 7     │
│ 8     │
│ 9     │
│ 10    │
│ 11    │
│ 12    │
│ 13    │
│ 14    │
│ 15    │
│ 16    │
│ 17    │
│ 18    │
│ 19    │
│ 20    │
└───────┘
sqlite> SELECT * FROM generate_series(1, 20, 5);
┌───────┐
│ value │
├───────┤
│ 1     │
│ 6     │
│ 11    │
│ 16    │
└───────┘
sqlite> SELECT * FROM generate_series(0, 20, 5);
┌───────┐
│ value │
├───────┤
│ 0     │
│ 5     │
│ 10    │
│ 15    │
│ 20    │
└───────┘
sqlite>