https://s.lapas.info

Sukurta naudojant elementarų PHP, JS, na ir Bootstrap5, kad bjauriai neatrodytų (nors pernelyg daug į stilių neinvestavau).

Aš neturiu obuolių;
Aš turiu vieną obuolį;
Aš turiu 3 obuolius;
Aš turiu 11 obuolių;
Aš turiu 21 obuolį;
Aš turiu 20 obuolių;

Tokios problemos seniai išspręstos daugelyje programavimo aplinkų, tačiau tai, kaip tai padaryta Codeigniter’yje, nebuvo aiškiai dokumentuota.

Taigi, tokiu atveju, LT kalbos faile vietoje įprastos eilutės įterpiama:

    // 'apples' => 'Aš turiu {1} obuolių/-us/-į', // čia nekažkas
    'apples' => 'Aš {apples, plural, // čia gerai!
        =0 {neturiu obuolių}
        =1 {turiu vieną obuolį}
        one {turiu # obuolį}
        few {turiu # obuolius}
        other {turiu # obuolių}
    }',

O View faile taip:

<?= lang('Fruit.apples', ['apples' => $appleNo]) ?>

It is extracted from this Youtube video:

{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Launch built-in web server",
            "type": "php",
            "request": "launch",
            "runtimeArgs": [
                "spark",
                "serve",
                "-dxdebug.mode=debug",
                "-dxdebug.start_with_request=yes",
                "-S",
                "localhost:8080"
            ],
            "env": {
                "XDEBUG_MODE": "debug",
                "XDEBUG_SESSION": "permsExample",
            },
            //"externalConsole": true,
            "program": "",
            "cwd": "${workspaceRoot}",
            "port": 9003,
            "serverReadyAction": {
                "pattern": "Development server \\(http://localhost:[0-9]+)\\) started",
                "uriFormat": "http://localhost:%s",
                "action": "openExternally"
            }
        }
    ]
}

Here is how we achieved the desired change basically without refactoring of the codebase.

First of all, we simply followed the Queue documentation to the letter in installing the package in our Codeigniter app and creating the Email queue files. You can read about that step here.

Now we had to decide where to tweak the code to send the failed emails to queue for sending retry later. There were a lot of code blocks in our code that looked like this:

$email = service('email');
$email->setFrom('noreply@lsu.lt', 'LSU informacinė sistema');
$email->setTo($user->email);
$email->setSubject($messageSubject);
$email->setMessage($messageBody);
$success = $email->send(false);

So the obvious choice at first seemed to add a code block that would check if email was successfully sent and if not – send it’s data to the queue:

if (! $success) {
    $emailData = [
        'recipients' => $user->email,
        'subject'    => $messageSubject,
        'body'       => $messageBody,
        'fromEmail'  => 'noreply@lsu.lt',
        'fromName'   => 'LSU informacinė sistema',
    ];
    service('queue')->push('emails', 'email', $emailData);
}

It would work fine, but this code had to be added in many different places.

So we decided to extend the Codeigniter 4 Email library instead. We added file app/Libraries/Email.php:

<?php

namespace App\Libraries;

use CodeIgniter\Email\Email as BaseEmail;
use ErrorException;

class Email extends BaseEmail {
    /**
     * Store the raw subject.
     *
     * @var string
     */
    protected $rawSubject = '';

    /**
     * Raw debug messages
     *
     * @var list<string>
     */
    private array $debugMessageRaw = [];

    /**
     * Indicates whether the email is being executed by the queue.
     *
     * @var bool
     */
    protected $isQueueExecution = false;

    /**
     * Indicates whether the email is being moved to the queue instead of sending.
     *
     * @var bool
     */
    protected $toQueue = false;

    /**
     * Spool mail to the mail server
     *
     * @return bool
     */
    protected function spoolEmail() {
        $this->unwrapSpecials();
        $protocol = $this->getProtocol();
        $method = 'sendWith' . ucfirst($protocol);

        if($this->toQueue){
            $this->addToQueue();
            return true;
        }

        try {
            $success = $this->{$method}();
        } catch (ErrorException $e) {
            $success = false;
            log_message('error', 'Email: ' . $method . ' throwed ' . $e);
        }

        if (! $success) {
            $message = lang('Email.sendFailure' . ($protocol === 'mail' ? 'PHPMail' : ucfirst($protocol)));

            log_message('error', 'Email: ' . $message);
            log_message('error', $this->printDebuggerRaw());

            $this->setErrorMessage($message);

            // Avoid recursive queueing
            if (! $this->isQueueExecution) {
                $this->addToQueue();
            }

            return false;
        }

        $this->setErrorMessage(lang('Email.sent', [$protocol]));

        return true;
    }

    /**
     * Returns raw debug messages
     * copy of the original method, which is set to private unfortunately
     */
    private function printDebuggerRaw(): string {
        return implode("\n", $this->debugMessageRaw);
    }

    /**
     * Set the queue execution flag.
     */
    public function setQueueExecution(bool $isQueue): void {
        $this->isQueueExecution = $isQueue;
    }

    /**
     * Set toQueue flag.
     */
    public function setToQueue(bool $toQueue): void {
        $this->toQueue = $toQueue;
    }

    /**
     * Add the failed email to the queue.
     */
    protected function addToQueue(): void {
        $queueData = [
            'recipients' => $this->recipients,
            'cc' => $this->CCArray,
            'bcc' => $this->BCCArray,
            // $this->subject results in empty string, had to make an owerride
            'subject' => $this->getRawSubject(),
            'body' => $this->body,
            'headers' => $this->headers,
            'fromEmail' => $this->fromEmail,
            'fromName' => $this->fromName,
            // 'attachments' => $this->attachments, // needs more work
        ];

        // Use queue library to add the data
        service('queue')->push('emails', 'email', $queueData);
    }

    /**
     * Override setSubject to store the raw subject.
     *
     * @param string $subject
     *
     * @return $this
     */
    public function setSubject($subject) {
        $this->rawSubject = $subject; // Store the raw subject

        return parent::setSubject($subject); // Call the parent method
    }

    /**
     * Get the raw subject.
     */
    public function getRawSubject(): string {
        return $this->rawSubject;
    }
}

The main change here is this: the extended class contains an overridden method spoolEmail(), which is responsible for sending emails; here, when an email fails to send, it automatically executes a new method addToQueue(),which sends the data of email to the queue for retrying later. (Some other changes needed to be added as well, they are explained in the comments of the code).

After this, we also added this code:

public static function email($config = null, bool $getShared = true)
{
    if ($getShared) {
        return static::getSharedInstance('email', $config);
    }

    if (empty($config) || (! is_array($config) && ! $config instanceof EmailConfig)) {
        $config = config(EmailConfig::class);
    }

    return new Email($config);
}

to app/Config/Services.php file, so that the Codeigniter’s Email service would use our extended class in place of the default Codeigniter’s Email class.

And that was it. Now, without any other changes to the old code, once an email fails to send, it is transferred to the emails queue and retries are made to send it later.

As you can see, if email is sent from the queue and fails, it is NOT readded to the queue, the code block

if (! $this->isQueueExecution) {
    $this->addToQueue();
}

takes care of that.

And if we have a cycle where a lot of emails need to be sent and we do not want the user to wait for those emails to send, we can call the method setToQueue() with parameter ‘true’ before the $email->send():

$email->setToQueue(true);
$email->send(false);

And, instead of sending the email immediately, it will be transferred to the queue to be dealt with later. And the user will be returned the page faster (as database operations are much faster than sending emails).

Here is how, with very little change in the code, we solved the problem of failed emails; and added a lot of scalability to our app.

Image credit goes to ChatGPT

So here they are, my two packages: https://packagist.org/users/dgvirtual/

I have maintained a few packages installable via PHP’s Composer for a while, but only as private packages, so I had to add additional instructions on how to install them via composer (setting up a private repository for the package and stuff).

But now my packages can be installed with a one-liner.

In this post I will describe how you can get in trouble by generating the QR codes on their website.

First let’s define the terms: QR code and „dynamic” link.

A QR code (Quick Response code) is a type of two-dimensional barcode that stores information as a pattern of black and white squares, which can be quickly scanned by a device to access data, such as URLs, text, or contact details. It is easy to generate QR codes using free tools, even within browsers (Edge and Chrome have it built in, just right-click on the page).

Often QR codes are used with shortened URL‘s, that is, short links that, on trying to access it, redirect the browser to another (longer) link. Shorter links allow QR codes to be smaller and quicker to read for our devices, and in addition, some services allow you to make such links dynamic, that is, you can change the page URL the short link targets without changing the short URL itself.

Cool, ha?

Those two services (URL shortening and QR generation) are often integrated and, if you only need a few such links/QR codes, they are available for free. E.g., bitly.com allows you to create two 10 short links and 2 QR codes for such links per month for free. That is enough for most casual users.

But you have to be careful with some providers. If the provider allows you to generate a dynamic link and later demands money for using it, you may be in trouble. One such manipulative provider is QRFY.com.

Suppose you need a single QR code for an event, like wedding or conference. You intend to put it on the participants cards and conference/event materials, so people can easily find updates and news. You google for QR code generator online and find one, looking very professional, at https://qrfy.com

So, you enter the link, website generates the QR code:

If you scan the code, you will see it resolves to Website URL you have entered, so, great! So, you press download button…

After pushing „Download QR“ you find that there is no direct download, registration is required:

And if you go back and try to download it via the browser right-click action „Save image“, you find it is also disabled. You can actually do a screenshot and be done with it. But that is a workaround that may not come to your mind at once. OK, so lets register, that could not hurt, right?

So you register successfully:

On pushing Accept (what are you accepting, exactly?), they suggest downloading the QR code yoy previously generated, some nice format choices there!

So you push „Download“ and here is the JPEG image that you get:

It might look about the same, but it is not the same as the initial QR code. Here is a comparison, and what it resolves to. Inestead of a QR code that resolves directly to your website/page you get a code that resolves to THEIR website’s redirect link to your website, which is in their control, not yours:

Initial QR code: Resolves to: https://lapas.info

„Same“ QR code after registration: Resolves to: https://qrfy.io/8JxBhAMXnS

Notice that NOWHERE on the way to download of the QR code you were:

• a) introduced/required to opt-in to the site service conditions,
• b) warned that your QR code is changed into a redirect link controlled by QRFY company, and of course I was not;
• c) warned that (surprise surprise!) the link encoded into the QR code will expire in a week

Not only were you not warned explicitly, the website implies that the QR codes will work all the time in the title page, in the Basic Information section:

So, the idea is that the QR codes generated over the trial period are „yours forever” and therefore should work as expected (on scan should lead to your website). But after the trial period you can only create codes on their website if you choose a plan and pay. The reality is that, though the pictures of QR codes cannot be taken away after you download them, they stop doing what they are supposed to do when the trial period expires.

Again, impression is given that the codes you generate during the trial period will work indefinitely, and only later you will have to pay to generate new ones.

So here they say, again, the the QR codes do not expire, UNLESS you create a „dynamic code”. You did not opt for that, but they replaced covertly the static code with dynamic. Again, you were not warned that the website intentionally changes the QR codes to dynamic ones, and the passage above says explicitly they do not do that.

So, you have created a QR code, there is a promise that it will not expire, and you continue with production of the materials (sending wedding invitations, printing conference materials with the QR code, etc).

And then in 7 days the QR code stops working: on scan they open the QRFY website instead of yours, which says the link is disabled „for some reason”, and suggests you log in to reenable it.

You login to their website and find that to reenable the redirection link, that is encoded in your QR code, you need to choose one of the paid subscription plans, the cheapest of which is 145.16€ for three months, paid at once.

The materials, invitations etc that have already been printed and distributed, and if they contain a QR code that does not work, they promise you a public relations disaster. You are time-pressed, so you weight the options and choose the lesser evil, choose that „cheapest” plan and pay those 145.16€. Ah, and when choosing that subscription you have to agree to all the long list of conditions, one of which is „no refund”.

What a fine business model in a EU country Spain! Consumer protection institutions should take not of this one.

If you try to find out who they are, the most you can get on their website is their legal address. No people are willing to associate their name with this schema.

Tad vasarą pasidariau tokį projektėlį – patogesnę juridinių asmenų duomenų paiešką. Panaudojau viešai prieinamus juridinių asmenų duomenis (jie atnaujinami kas mėnesį) ir taip pat sukūriau scenarijus, kurie tuos duomenis papildo panaudojant tą pačią Registrų centro duomenų paiešką (mat viešai pateikiamuose duomenyse nėra individualių įmonių ir komanditinių bendrovių pavadinimų).

Technologinis sprendimas buvo paprastas:

• SQlite3 duomenų bazė
• su PHP kurtas API backend’as
• Alpine.js frontend’as

Paiešką galima atlikti pagal įregistravimo/išregistravimo datas, teisinį statusą, teisinę formą, pavadinimą, adresą – tad galite ir savo poreikius, ir smalsumą patenkinti:

• Kiek įmonių įregistruota jūsų name?
• Ar name, gatvėje, miestelyje yra religinių bendruomenių?
• Ar Jūsų planuojamame įsigyti būste nėra įregistruotų įmonių?
• Kiek mažųjų bendrijų yra jūsų miestelyje? O kiek – Lietuvoje?

Įprastai sistemas kuriu su Codeigniter 4 PHP karkasu, bet šį kartą nusprendžiau pamėginti apsieiti be karkaso – tiesiog „vanilla PHP“, ir be jokių išorinių bibliotekų. Nedidelis projektėlis, turėtų būti nesudėtinga, ar ne? Gan greitai paaiškėjo, kad nieko panašaus. Būčiau tikrai daug laiko sutaupęs, jei nebūtų reikėję išradinėti visų tų dviračių pakeliui Na bet buvo įdomu šį tą naujo išmokti.

Kodėl SQLite3, o ne MySQL? Nes norėjosi išmėginti šios duomenų bazės galimybes su dideliais duomenų kiekiais (juridinių asmenų Lietuvoje, įskaitant išregistruotus, – per pusę milijono). Buvo iššūkių su duomenų rikiavimu (SQLite3 „out of the box“ palaiko tik ASCII rikiavimą, taigi visos „lietuviškos“ raidės atsidurdavo rikiavimo gale). Taip pat ir greičio klausimas yra: su MySQL būtų tikrai greičiau, bent jau kol SQLite3 tinkamai neoptimizuota lietuviškiems simboliams (ko dar iki galo nepavyko padaryti).

Taigi, štai projekto svetainė: https://pr.lapas.info/jar/

Be to, sistemos kodą galite rasti Github’e (https://github.com/dgvirtual/jar-search-api); sistema turi diegimo vediklį, tad galėsite nesunkiai ir ant savo serverio šį projektėlį pasileisti.

Už paveikslėlį ačiū ChatGPT (visgi smagu, kad DI dar nesugeba kurti kažko, kas prilygtų dizainerių darbui)

And then one of the hosting providers dropped SFTP for FTP over TLS – which KDE ecosystem does not support… I had to switch to Filezilla (which does not have sync in their free ftp client) or look for other alternatives.

So I found lftp, Unix command-line ftp client, which supports multiple protocols, including FTP over TLS, and, with some help of Gemini and ChatGPT wrote a script to automate deployment.

Here is how it works:

$ ./deploy.sh app
Syncing app folder...
Synchronizing directory: '/home/me/Programavimas/myproject/app' to '/myproject/app'
source: Aplankas
Removing old file `Config/Filters.php'               
Transferring file `Config/Filters.php'
Removing old file `Controllers/Auth.php'                               
Transferring file `Controllers/Auth.php'
Removing old file `Controllers/Texts.php'       
Transferring file `Controllers/Texts.php'
Transferring file `Filters/IsLoggedIn.php'                           
Successfully synchronized '/home/me/Programavimas/myapp/app' to '/myapp/app'

Actually, the AI was great with Bash part, but not very helpful with lftp itself, giving me only non-working code, so I had to Google and read in the man page about it.

So here I share the working script, maybe it will benefit someone. [Update: the script has been turned into a package installable via composer, see https://github.com/dgvirtual/deploy-via-ftp/ There you will find an updated version of the script].

First, you will want a config file for FTP credentials, which are better kept outside of your Git repository. Also, to make the script itself more universal, the other options particular to the project should be kept in it. I named it .ftp_config :

# .ftp_config
USERNAME="myname"
PASSWORD="mySecretPass"
PROTOCOL="ftps"  # or "ftp"
PORT=21
SERVER="ftp.example.com"

# Remote directories
REMOTE_APP="/myproject/app"
REMOTE_VENDOR="/myproject/vendor"
REMOTE_PUBLIC_HTML="/public_html"

# options for excluding files/directories from sync; things that rarely
# change, should not be in production, or that should be updated manually
APP_EXCLUDE_GLOBS=""
VENDOR_EXCLUDE_GLOBS=""
PUBLIC_EXCLUDE_GLOBS="--exclude-glob='uploads/*' --exclude-glob='index.php'"

# Local directories
LOCAL_APP="app"
LOCAL_VENDOR="vendor"
LOCAL_PUBLIC_HTML="public"

Be sure to use SERVER address which which matches the server certificate (as many providers do not care to configure server to use your site sertificate). So, in my case, I could not use the real address of my website, instead using the generic address of the server.

And here is my deploy.sh script, which can be run in command line by specifying the directory to be deployed (in my case: public, app or vendor, or any combination of those), like this:

./deploy.sh app vendor

#!/bin/bash

# retrieve all variables for server, protocol and directories
source ./.ftp_config

# Base directory (where the script is executed)
BASE_DIR="$(pwd)"

# Function to synchronize a directory using lftp
sync_dir() {
  local local_dir="$1"
  local remote_dir="$2"
  local excludes="$3"  # Additional exclude patterns

  # Construct the full local directory path
  local full_local_path="$BASE_DIR/$local_dir"

  # Check if directory exists locally
  if [[ ! -d "$full_local_path" ]]; then
    echo "Error: Local directory '$full_local_path' does not exist."
    return 1
  fi

  # Debugging output to verify directory paths
  echo "Synchronizing directory: '$full_local_path' to '$remote_dir'"

  # Construct lftp command based on protocol
  if [[ "$PROTOCOL" == "ftps" ]]; then
    lftp -f "
    set ftp:ssl-force true
    set ftp:ssl-protect-data true
    open $SERVER
    user $USERNAME $PASSWORD
    lcd $BASE_DIR
    mirror -c --continue --reverse --delete --verbose $excludes $local_dir $remote_dir
    bye
    "
  else
    lftp -f "
    open $SERVER
    user $USERNAME $PASSWORD
    lcd $BASE_DIR
    mirror -c --continue --reverse --delete --verbose $excludes $local_dir $remote_dir
    bye
    "
  fi

  # Check if lftp command succeeded
  if [[ $? -eq 0 ]]; then
    echo "Successfully synchronized '$full_local_path' to '$remote_dir'"
  else
    echo "Error: Synchronization of '$full_local_path' failed."
    exit 1
  fi
}

# Function to upload a single file using curl
upload_single_file() {
  local local_file="$1"
  local remote_file="$2"

  # Construct the full local file path
  local full_local_path="$BASE_DIR/$local_file"

  # Check if file exists locally
  if [[ ! -f "$full_local_path" ]]; then
    echo "Error: Local file '$full_local_path' does not exist."
    return 1
  fi

  # Determine the URL scheme and port based on protocol
  if [[ "$PROTOCOL" == "ftps" ]]; then
    CURL_OPTS="--ftp-ssl-reqd --ftp-create-dirs --ftp-pasv"
  else
    CURL_OPTS="--ftp-pasv"
  fi

  # Upload the file using curl
  curl $CURL_OPTS --ftp-create-dirs -T "$full_local_path" --user "$USERNAME:$PASSWORD" ftp://$SERVER:$PORT$remote_file

  # Check if curl command succeeded
  if [[ $? -eq 0 ]]; then
    echo "Successfully uploaded '$full_local_path' to '$remote_file'"
  else
    echo "Error: Upload of '$full_local_path' failed."
    exit 1
  fi
}

# Check for arguments and call the appropriate function
if [[ $# -eq 0 ]]; then
  echo "Usage: $0 (app|vendor|public|onefile) [relative_local_path] [/absolute_remote_path]"
  echo "  app: Synchronize app directory only."
  echo "  vendor: Synchronize vendor directory only."
  echo "  public: Synchronize public_html directory only."
  echo "  onefile: Upload a single file using curl."
  echo "  for example:"
  echo "  ./deploy.sh onefile app/Controllers/Test.php /project/app/Controllers/Test.php"
  exit 1
fi

# Check the first argument to decide which function to call
case $1 in
  app)
    echo 'Syncing app folder...'
    sync_dir "$LOCAL_APP" "$REMOTE_APP" "$APP_EXCLUDE_GLOBS"
    ;;
  vendor)
    echo 'Remove vendor dev dependencies...'
    composer install --no-dev
    echo 'Syncing vendor folder...'
    sync_dir "$LOCAL_VENDOR" "$REMOTE_VENDOR"  "$VENDOR_EXCLUDE_GLOBS"
    echo 'Restore vendor dev dependencies...'
    composer install
    ;;
  public)
    echo 'Syncing public folder...'
    sync_dir "$LOCAL_PUBLIC_HTML" "$REMOTE_PUBLIC_HTML" "$PUBLIC_EXCLUDE_GLOBS"
    ;;
  onefile)
    if [[ $# -ne 3 ]]; then
      echo "Usage: $0 onefile [local_path] [remote_path]"
      exit 1
    fi
    echo 'Uploading single file...'
    upload_single_file "$2" "$3"
    ;;
  *)
    echo "Invalid argument: '$1'"
    exit 1
    ;;
esac

exit 0

As you see, there is an option „onefile” with additional argumens, that runs a curl command (instead of lftp). It is handy if you only need to update one file; or, as in my case, sometimes lftp gets stuck on a file for a long time failing to sync (in my case there are two such files in the project). It is some bug in lftp, which is no longer actively maintained. So, in case I change such file and it gets stuck, I apply the „onefile” command as a workaround:

./deploy.sh onefile app/Controllers/Test.php /project/app/Controllers/Test.php

If anyone finds this useful, or as some improvements to suggest – please tell me in the comments.

One thing that troubled me, however, was lack of proper text ordering in Lithuanian language, as well as lack of case-insensitive search for UTF8 characters.

Here is how SQLite3 would order Lithuanian letters:

$ 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> CREATE TABLE test (name TEXT COLLATE NOCASE);
sqlite> INSERT INTO test (name) VALUES ('ž'), ('Ž'), ('e'), ('q'), ('į'), ('i'), ('y'), ('ė'), ('ę'), ('c'), ('z');
sqlite> SELECT name FROM test ORDER BY name;
c
e
i
q
y
z
ė
ę
į
Ž
ž
sqlite>

All specific Lithuanian letters would be moved to the end by the available collations.

At first I tried to solve the ordering issue by a workaround (retrieve database results into a PHP array and then reorder it; or order the results on the frontend using, for example, Datatables). That was not very scalable (it meant loading full tables into PHP’s memory for sorting!) and it meant extra programming. So, not good enough.

Later I found out that PHP’s SQlite3 extension can pass the ordering or lowercasing of text to a PHP function, which removed the need for extra programming to order (or search) properly. I described how to do that in this forum post (for both Vanilla PHP and Codeigniter 4). Searches, however, became so much slower. Not a big problem on a small app, mostly unnoticeable, but once I tried it on a database with 0.5 mln records searches began to take up to 5 seconds.

Finally today I managed to compile the ICU extension for SQLite3, which reduced the database operation time by half in comparison to the operation with PHP functions, since SQLite3 no longer needs to employ external PHP functions for ordering, lowercasing and searching. That is perhaps the best that can be done in trying to optimize SQLite3 for web projects on my part.

Let me share it.

So, my development computer has a Kubuntu 22.04 OS.

First I had to get compile dependencies as well as the development packages for SQlite3 and the ICU libraries:

$ sudo apt build-dep sqlite3
$ sudo apt install libicu-dev libsqlite3-dev

Then I got the source code of SQlite3:

$ mkdir /tmp/SQLITE
$ cd /tmp/SQLITE
$ apt source sqlite3

Then got into the source, the ICU extension folder:

$ cd sqlite3-3.37.2/ext/icu

Within that directory there were three files:

$ ls
icu.c README.txt  sqliteicu.h

So I read the `README.txt` file first, where I found the compilation instructions:

$ gcc -fPIC -shared icu.c `pkg-config --libs \  
     --cflags icu-uc icu-io` -o libSqliteIcu.so

After running the command the `libSqliteIcu.so` extension was compiled. I tried to use it in SQLite3 cli client:

$ 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 ./libSqliteIcu.so
Error: ./libSqliteIcu.so: undefined symbol: sqlite3_sqliteicu_init

So, something did not quite work, some undefined symbol `sqlite3_sqliteicu_init`, whatever it is…

It sent me googling, and I found explanation that suggested I needed to open the file `icu.c` and replace every instance of string `sqlite3_icu_init` with `sqlite3_sqliteicu_init`.

So I replaced `sqlite3_icu_init` with `sqlite3_sqliteicu_init` (found one string only) and rerun the compile command. And voila, it worked! Here is the test:

$ 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 ./libSqliteIcu.so
sqlite> SELECT icu_load_collation('lt', 'lithuanian');

sqlite> CREATE TABLE test (name TEXT COLLATE lithuanian)
   ...> ;
sqlite> INSERT INTO test (name) VALUES ('ž'), ('Ž'), ('e'), ('q'), ('į'), ('i'), ('y'), ('ė'), ('ę'), ('c'), ('z');
sqlite> SELECT name FROM test ORDER BY name;
c
e
ę
ė
i
į
y
q
z
ž
Ž

So, now the ordering was good.

To use the extension with Apache’s php I needed to put the extension into some common folder, so I created it:

$ mkdir -p /usr/lib/sqlite3/ext/
$ cp libSqliteIcu.so /usr/lib/sqlite3/ext/

Then I pointed php.ini file located at `/etc/php/php8.1/apache/php.ini`, found relevant SQLite3 section and pointed the variable `sqlite3.extension_dir` to the directory:

[sqlite3]
# Directory pointing to SQLite3 extensions
sqlite3.extension_dir = /usr/lib/sqlite3/ext/

The extension has to be loaded in PHP scripts after the initiation of database. Below is a full example:

<?php

$db = new SQLite3(':memory:');
$db->loadExtension('libSqliteIcu.so');
$db->exec("SELECT icu_load_collation('lt', 'lithuanian')");

$db->exec('CREATE TABLE test (name TEXT COLLATE lithuanian)');

$db->exec("INSERT INTO test (name) VALUES ('ž'), ('Ž'), ('e'), ('q'), ('į'), ('i'), ('y'), ('ė'), ('ę'), ('c'), ('z')");

$result = $db->query('SELECT name FROM test ORDER BY name');

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . "\n";
}