How to install locally an extension in SQLite on a Mac?

Question

I've no real knowledge about installing an extension. I'm stuck because I cannot use SQRT in SQLite.

I'm using Laravel on a local machine and I'm working on my PHPunit Tests. In one Test I'm using a query with some heavy math functions.

I get the error:

Caused by
PDOException: SQLSTATE[HY000]: General error: 1 no such function: SQRT

I found out that SQLite has an extension called: extension-functions.c

I downloaded it, but I'm not sure if I did everything correctly (obviously not because it does not work):

1. I downloaded it and used this in my terminal

   gcc -fno-common -dynamiclib extension-functions.c -o libsqlitefunctions.dylib
   

2. It compiled this data: libsqlitefunctions.dylib
3. With php --ini if found that my php.ini is located in /usr/local/etc/php/7.1/php.ini
4. Now I looked up for sqlite3 and found this commented ;sqlite3.extension_dir =

5. I changed it to

   sqlite3.extension_dir =/usr/local/etc/php/7.1/extension/libsqlitefunctions.dylib
   

6. and created the extension folder and put the dylib file inside

7. I restarted my apache with sudo apachectl restart

8. And tried my Query and still, it fails. What did I do wrong?

For future readers

To use it in Laravel you call it like this:

$pdo = DB::connection()->getPdo();
$pdo->sqliteCreateFunction('SQRT', 'sqrt', 1);
$pdo->sqliteCreateFunction('ASIN', 'asin', 1);
$pdo->sqliteCreateFunction('SIN', 'sin', 1);
$pdo->sqliteCreateFunction('COS', 'cos', 1);
$pdo->sqliteCreateFunction('POWER', 'pow', 2);
$pdo->sqliteCreateFunction('pi', 'pi', 0);

Answer 1

It seems that you want to use this with PDO. The problem is that loading extensions only works with the SQLite3 api directly, using the SQLite3::loadExtension method.

The PDO driver does not allow the loading of extensions (see this wiki entry regarding LoadableExtensions under "Security Considerations").

So you could either swith to the SQLite3 api directly or you could use "user defined functions" with PDO. For example if you need the SQRT function:

$db = new PDO('sqlite:testdb.sqlite', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$db->sqliteCreateFunction('SQRT', 'sqrt', 1);

$stmt = $db->query("SELECT sqrt(25)");
foreach ($stmt as $row) {
    echo $row[0], PHP_EOL; // output: 5.0
}

Most of the math-functions your extension provides map 1:1 to the PHP function names (like sqrt), so it should be easy to create the functions you need.