SQLite Extensions

[alnutile]

Hello, trying to test out SQLite Vector Search extension.

Just want to make sure I have the php.ini updates in the right place.

I used this post https://github.com/orgs/NativePHP/discussions/171 to see about how to alter the php.ini

I added this to app/Providers/NativeAppServiceProvider.php

    public function phpIni(): array
    {
        return [
            'sqlite3.extension_dir' => base_path("system/mac"),
            'extension_vector0'=>'vector0.dylib',
            'extension_vss0'=>'vss0.dylib',
        ];
    }

and on boot I see this in the logs:

Starting PHP server... /Users/alfrednutile/Code/native_laralama/vendor/nativephp/electron/resources/js/resources/php/php artisan serve /Users/alfrednutile/Code/native_laralama {
  'sqlite3.extension_dir': '/Users/alfrednutile/Code/native_laralama/system/mac',
  extension_vector0: 'vector0.dylib',
  extension_vss0: 'vss0.dylib'
}
Making sure app folders are available

But the query on the home page (the default one) just never loads so I assume it is just not making it past something here.

Route::get('/', function () {
    Route::get('/dashboard', function () {
        try {
            $result = DB::select("SELECT vss_version() as version");

            // Output the result to check the version
            dump($result);
        } catch (Exception $e) {
            Log::error("Sqlite", [
                $e->getMessage()
            ]);
        }
    return view('welcome');
});

White screen and nothing in the logs.

The post I am following is https://resonance.distantmagic.com/docs/features/vector-store/sqlite-vss/

[alnutile]

btw did a tinker and as expected

[simonhamp]

This is a really interesting use-case!

I'm not sure if it's possible (or even whether it should be possible at this stage) to load extra extensions into these static PHP binaries like this, but happy to explore.

You could try it directly against the binary rather than via the Electron app, as this may make your debugging and tinkering a little easier:

/Users/alfrednutile/Code/native_laralama/vendor/nativephp/electron/resources/js/resources/php/php -d
  sqlite3.extension_dir='/Users/alfrednutile/Code/native_laralama/system/mac' -i

This should set the needed ini setting (sqlite3.extension_dir - it looks like those other 2 options are for a separate config.ini file and may not be needed at all?) and output PHP info in the CLI.

I'm not sure what PHP info is expected to show when this extension is loaded, but I guess it will be somewhere around the SQLite section if it does load.

Also I noted - according to their docs - that you are required to use a special SQLiteVSSConnectionBuilder class to make the connection. Maybe using Laravel's DB facade and Eloquent won't work, at least not without deeper integration 🤔

[alnutile]

Tried a few things and we let you know if anything works. Good lead though thanks!

SQLiteVSSConnectionBuilder that is part of their framework I will dig into that as well.