Solved Custom building a port (maybe more than one)

[Keve]

For one of my projects I need Perl to access data from a database. SQLite3 would be perfectly suitable for my needs, except for the fact that by default SQLite does not know my national characters. For example, a

SELECT UPPER(person_name) FROM people;

turns all ASCII characters of the name to uppercase, but leaves all my national characters as they were, because SQLite does not know the uppercase counterpart of those letters.
A little research revealed that the required functionality can be compiled into SQLite3 at build time, using ICU. So this is what I did:
cd /usr/ports/database/sqlite3
make config
This brought up a nice curses-based window with some 20 build options, "Use ICU for Unicode support" being unchecked by default. I checked it, and left everything else on their default values. Then ...
make
... and everything went just fine. Then ...
make install
... which failed because I already had SQLite3 in the system. A sentence printed out by the failed make install suggested that I do a "make deinstall" first. So I attempted ...
pkg remove sqlite3
... but that listed two screenful of other ports depending on sqlite3 destined for automatic removal, that I answered NO to leave everything as is.
However, I copied out the sqlite3 executable from the build directory work/stage/usr/local/bin/sqlite3 together with the sqlite libs work/stage/usr/local/lib/* to /root/sqlite3icu/. Now if I use that custom built sqlite3 executable, the SELECT UPPER(person_name) example shown above works perfectly fine and prints all letters (including my accented national ones) as proper uppercase.
Now I just need to get Perl to use my custom built sqlite instead of the one in the base system.

I figured that if I can similarly custom-build the Perl database-driver for SQLite then I will be all set. So ...
cd /usr/ports/database/p5-DBD-SQLite3
make config
... which offered one single configuration option to use the sqlite found in the base system (this was the default) or use the one in the ports database/sqlite3. I removed the checkmark, indicating my desire to build using the code in ports. Then make followed by make install, all completed well.
Unfortunately, when I use a perl script to connect to my SQLite3 database file and print the results of the SELECT UPPER( ) example above, it leaves the national characters as lowercase, proving that Perl uses the ASCII-ONLY sqlite present in the base system instead of my Unicode capable ICU build.

I would appreciate your thoughts on ...

• What to do in order to get Perl to use my ICU-extended SQLite3.
• or How to replace the sqlite in the base system with my custom-built one without removing or rebuilding the existing packages depending on SQLIte.
• Am I on the right track believing that the way to get Perl to work with the ICU-ready custom-built SQLite is through the p5-dbd-sqlite3 driver?

 

[Keve]

It may be more of a quick and dirty solution rather than a nice solution done in the proper ways, but this one got me where I needed to be and allows me to move on. Seemingly without breaking anything on the system and causing no trouble to existing or future builds. So for the benefit of others, here is what I did.

I renamed /usr/local/lib/libsqlite3.so.0.8.6 to libsqlite3.so.0.8.6.DIST and copied my ICU-built libsqlite3.so.0.8.6 into its place.
At that point, my Perl scripts started fetching proper UPPERCASE national characters from my SQLite database.
I similarly backed up /usr/local/bin/sqlite3 and manually replaced it with the corresponding executable from my own build. So that using the sqlite3 CLI from anywhere in the system now runs an interface which handles accented national characters properly. It did not seem necessary -as at that point both CLI and Perl access to SQLite was fully national-uppercase capable- but for completeness I also replaced /usr/local/lib/libsqlite3.a with the one from my build.

On a side-note: I find it very interesting that the SQLite developers claim the reason ICU (and thus, proper support for non-ascii characters) is not built into the default setup is because this functionality would double the size of SQLite.

-rw-r--r--  1 root  wheel  2331538 Dec 14 15:06 /usr/local/lib/libsqlite3.a
-rw-r--r--  1 root  wheel  2283248 Aug 22 03:10 /usr/local/lib/libsqlite3.a.DIST
-rwxr-xr-x  1 root  wheel  1704488 Dec 14 14:15 /usr/local/lib/libsqlite3.so.0.8.6
-rwxr-xr-x  1 root  wheel  1673712 Aug 22 03:10 /usr/local/lib/libsqlite3.so.0.8.6.DIST
-rwxr-xr-x  1 root  wheel  1868936 Dec 14 14:21 /usr/local/bin/sqlite3
-rwxr-xr-x  1 root  wheel  1813712 Aug 22 03:10 /usr/local/bin/sqlite3.DIST

I only see a very insignificant increase in the size of the related files.

Question: What would be the proper way to replace the sqlite3 files in the base system with the ICU-enabled ones I built from ports? I mean, obviously, instead of manually replacing those 3 files mentioned above. Because make install did not work. Should I have just forced " make install", which would have given me the same result I achieved by the manual copying?