MINGW-w64 toolchain and SQLITE

The gcc compiler included in the MINGW-w64 toolchain can be used for creating loadable SQLITE extensions. Let's take this extension as an example, it can be compiled on Windows as follows:

   gcc -g -fPIC -shared csv.c -o csv.lib

With this extension loaded, a csv file can be imported into the database as a Virtual Table, i.e. a read-only table where its values are updated simultaneously with the csv file, as shown in the video below.

It's also possible to create a command line utility (query_sqlite.c) in C that can extract data from a database, I have compiled a simple program that executes an arbitrary sql query and outputs the data to the stdout in form of a comma separated values list with:

   gcc -Wall -static query_sqlite.c -o query_sqlite.exe -lsqlite3 

This can be combined with a successive program that reads from the stdin. I can expand this gsl example to progressively update the statistical accumulator with the stdin incoming data.

    #include <stdio.h>
    #include <stdlib.h>
    #include <gsl/gsl_rstat.h>

    int
    main(void)
    {
      double x;
    ...  
      /* add data to rstat accumulator from stdin */

     while (fscanf (stdin, "%lg", &x) == 1)
        {
           gsl_rstat_add(x, rstat_p);
        }

      ...
      

The above code can be compiled with:

   gcc -Wall -static gsl_stat.c -o gsl_stat.exe -lm -lgsl

Below, the results for my previous dataset.

This approach is particularly memory-efficient since it doesn't need to store a copy of the data in memory.