How to load extensions into SQLite?

sql sqlite aggregate aggregate-functions standard-deviation
Chucky picture Chucky · Jul 12, 2011 · Viewed 14.7k times · Source

I need a standard deviation function in SQLite. I have found one here:

http://www.sqlite.org/contrib?orderby=date

but its part of an extension file to SQLite. I've never installed one of these before and I don't know how to. I found this existing function, load_extension, at http://www.sqlite.org/lang_corefunc.html, but I don't understand what the parameters X and Y are.

Basically, I need someone to give me a step by step guide on how to install the aggregate extension file. Can anyone do this?

Answer

Benoit picture Benoit · Nov 30, 2011

SQLite extensions are libraries with dynamic linkage. You can find some examples here (This is a repository, click on “login/fill captcha” to enable hyperlinks). See for example md5.c.

  • load_extension must be enabled in SQLite (pragma IIRC)
  • it requires as first argument the path of the library
  • The second argument is the name of the entry point function (in md5.c it is sqlite3_extension_init). Its prototype must be int(sqlite3*, char **, const sqlite3_api_routines *).
  • In SQL you can try SELECT load_extension('md5.so', 'sqlite3_extension_init'); or simply SELECT load_extension('md5.so');

You can try to compile md5.c, and from the sqlite shell use .load md5.so

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More