SQLite

SQLite is a public-domain relational database management system (RDMS) that can be embedded into applications. The library and does not require a separate server process, as the storage is entirely file-based. Most of the query features of the SQL-92 standard are supported. Multiple threads or processes may access a single database concurrently.

The FLIBS modules provide Fortran 2003 interface bindings to the SQLite 3 API. The SQLite library can either be linked statically or dynamically.

Installation

At first, install SQLite 3. On FreeBSD, just install the package databases/sqlite3:

# pkg install databases/sqlite3

Then, download the fsqlite interface bindings for Fortran 2003 that are part of the FLIBS library. Compile the module with make:

$ cd flibs-0.9/flibs/src/sqlite/
$ make FC=gfortran10 INC_SQLITE3=/usr/local/include/

On Linux, the INC_SQLITE3 parameter can be omitted. The compilation outputs the static library libfsqlite.a, and the module files sqlite.mod and sqlite_types.mod for linking.

Example

The example is taken from the FLIBS source code. A new table inside the SQLite database sqlite.db will be created. Keys and values are then printed to standard output.

! example.f90
program example
    use, intrinsic :: iso_fortran_env, only: stderr => error_unit
    use :: sqlite
    use :: sqlite_types
    implicit none
    character(len=*), parameter  :: DB_FILE_NAME = 'sqlite.db'
    integer                      :: i
    logical                      :: is_finished
    type(sqlite_column)          :: columns(2)
    type(sqlite_column), pointer :: column_names(:)
    type(sqlite_database)        :: db
    type(sqlite_statement)       :: statement

    ! Open SQLite database file.
    call sqlite3_open(DB_FILE_NAME, db)

    if (sqlite3_error(db)) then
        write (stderr, '(2a)') 'Error: ', sqlite3_errmsg(db)
    else
        call sqlite3_do(db, 'create table example (i int)')
    endif

    ! Create SQL table.
    call sqlite3_column_props(columns(1), 'key',    SQLITE_INT)
    call sqlite3_column_props(columns(2), 'string', SQLITE_CHAR, 20)
    call sqlite3_create_table(db, 'key_value', columns, 'key')

    ! Output table structure.
    nullify (column_names)
    call sqlite3_query_table(db, 'key_value', column_names)

    print '(a)', 'columns:'

    do i = 1, size(column_names)
        print '(i0, ", ", a, ", ", a)', &
            i, trim(column_names(i)%name), trim(column_names(i)%type)
    end do

    ! Add first value.
    call sqlite3_set_column(column_names(1), 1)
    call sqlite3_set_column(column_names(2), 'my string')
    call sqlite3_insert(db, 'key_value', column_names)

    ! Add second value.
    call sqlite3_set_column(column_names(1), 2)
    call sqlite3_set_column(column_names(2), 'my second string')
    call sqlite3_insert(db, 'key_value', column_names)

    call sqlite3_do(db, 'commit')

    ! Select key, values from table.
    call sqlite3_prepare_select(db, 'key_value', column_names, statement)
    is_finished = .false.
    print '(/, a)', 'sqlite3_next_row:'

    do
        call sqlite3_next_row(statement, column_names, is_finished)
        if (is_finished) exit

        print '(a, ": ", i0)',   trim(column_names(1)%name), column_names(1)%int_value
        print '(a, ": ", a, /)', trim(column_names(2)%name), trim(column_names(2)%char_value)
    end do

    call sqlite3_close(db)
end program example

Compile, link, and run the Fortran program with:

$ gfortran10 -o example example.f90 libfsqlite.a /usr/local/lib/libsqlite3.a -lpthread
$ ./example
columns:
1, key, INT
2, string, CHAR(20)

sqlite3_next_row:
key: 1
string: my string

key: 2
string: my second string

The SQLite library libsqlite3.a is compiled statically into the exectuable. Instead, we may link the shared library by adding -lsqlite3 to LDLIBS:

$ gfortran10 -o example example.f90 libfsqlite.a -lsqlite3 -lpthread

The SQLite shared library libsqlite3.so must be present on the system to run the executable.

References