---

SQLite

SQLite is a public domain relational database that can be easily embedded into applications. Unlike database management systems, SQLite does not require a separate server process, as the database 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. Additionally, Write-Ahead Logging (WAL) will increase the performance for concurrent readers significantly if activated.

Several libraries are available that provide access to SQLite from Fortran:

FLIBS

Fortran 90 modules that include non-standard wrapper routines around SQLite.

fortran-sqlite3

Modern interface bindings to SQLite 3 in pure Fortran 2018.

libGPF

General Purpose Fortran collection, includes SQLite 3 bindings.

sqliteff

SQLite for Fortran 2003, a thin C wrapper around the SQLite library.

fortran-sqlite3

At first, install SQLite 3 with development headers. On FreeBSD, simply add the package databases/sqlite3:

# pkg install databases/sqlite3

The package installs the SQLite 3 libraries libsqlite3.a and libsqlite3.so, as well as the command-line REPL utility sqlite3. On Linux, additional development headers are required.

Clone the fortran-sqlite3 repository, and build the static library libfortran-sqlite3.a; either with make:

$ git clone https://github.com/interkosmos/fortran-sqlite3
$ cd fortran-sqlite3/
$ make

Or, by running fpm:

$ fpm build --profile release

Finally, link your application against libfortran-sqlite3.a and -lsqlite3 (or libsqlite3.a) to access SQLite from Fortran.

Example

The example implements an abstraction layer around SQLite to store students, courses, and course attendance in a database. The database access is covered by functions in module db. The functions are called by the accompanying example program that opens the SQLite database example.sqlite, and creates the tables courses, students, students_courses of the following SQL schema:

CREATE TABLE courses (
    id          INTEGER PRIMARY KEY,
    name        TEXT UNIQUE
);
CREATE TABLE students (
    id          INTEGER PRIMARY KEY,
    name        TEXT UNIQUE
);
CREATE TABLE students_courses (
    id          INTEGER PRIMARY KEY,
    student_id  INTEGER NOT NULL,
    course_id   INTEGER NOT NULL,
    FOREIGN KEY(student_id) REFERENCES students(id),
    FOREIGN KEY(course_id)  REFERENCES courses(id)
);

The derived type db_type stores the C pointer to the current SQLite 3 instance, and is passed to all public functions of the module. The error handling has been simplified to reduce the complexity of the example.

! db.f90
module db
    !! Database abstraction layer.
    use, intrinsic :: iso_c_binding
    use :: sqlite3
    implicit none
    private

    integer, parameter, public :: DB_OK = SQLITE_OK

    type, public :: db_type
        type(c_ptr) :: ptr = c_null_ptr
    end type db_type

    public :: db_add_course
    public :: db_add_student
    public :: db_add_student_to_course
    public :: db_close
    public :: db_create
    public :: db_open
    public :: db_print_ncourses_per_student

    private :: db_error
    private :: db_exec
contains
    integer function db_add_course(db, name) result(rc)
        !! Adds course to database.
        type(db_type),    intent(inout) :: db
        character(len=*), intent(in)    :: name
        type(c_ptr)                     :: stmt

        ! Insert values through prepared statement.
        rc = sqlite3_prepare_v2(db%ptr, "INSERT INTO courses(name) VALUES (?)", stmt)
        call db_error(rc, 'sqlite3_prepare_v2()')

        ! Bind values to prepared statement.
        rc = sqlite3_bind_text(stmt, 1, name)
        call db_error(rc, 'sqlite3_bind_text()')

        ! Insert bound value into database.
        rc = sqlite3_step(stmt)
        call db_error(rc, 'sqlite3_step()')

        ! Clean-up prepared statement.
        rc = sqlite3_finalize(stmt)
        call db_error(rc, 'sqlite3_finalize()')
    end function db_add_course

    integer function db_add_student(db, name) result(rc)
        !! Adds student to database.
        type(db_type),    intent(inout) :: db
        character(len=*), intent(in)    :: name
        type(c_ptr)                     :: stmt

        ! Insert values through prepared statement.
        rc = sqlite3_prepare_v2(db%ptr, "INSERT INTO students(name) VALUES (?)", stmt)
        call db_error(rc, 'sqlite3_prepare_v2()')

        ! Bind values to prepared statement.
        rc = sqlite3_bind_text(stmt, 1, name)
        call db_error(rc, 'sqlite3_bind_text()')

        ! Insert bound value into database.
        rc = sqlite3_step(stmt)
        call db_error(rc, 'sqlite3_step()')

        ! Clean-up prepared statement.
        rc = sqlite3_finalize(stmt)
        call db_error(rc, 'sqlite3_finalize()')
    end function db_add_student

    integer function db_add_student_to_course(db, student_name, course_name) result(rc)
        !! Adds student to course.
        type(db_type),    intent(inout) :: db
        character(len=*), intent(in)    :: student_name
        character(len=*), intent(in)    :: course_name
        type(c_ptr)                     :: stmt

        ! Insert values through prepared statement.
        rc = sqlite3_prepare_v2(db%ptr, &
                "INSERT INTO students_courses(student_id, course_id) VALUES (" // &
                "(SELECT id FROM students WHERE name = ?), " // &
                "(SELECT id FROM courses WHERE name = ?))", stmt)
        call db_error(rc, 'sqlite3_prepare_v2()')

        ! Bind values to prepared statement.
        rc = sqlite3_bind_text(stmt, 1, student_name)
        call db_error(rc, 'sqlite3_bind_text()')
        rc = sqlite3_bind_text(stmt, 2, course_name)
        call db_error(rc, 'sqlite3_bind_text()')

        ! Insert bound value into database.
        rc = sqlite3_step(stmt)
        call db_error(rc, 'sqlite3_step()')

        ! Clean-up prepared statement.
        rc = sqlite3_finalize(stmt)
        call db_error(rc, 'sqlite3_finalize()')
    end function db_add_student_to_course

    integer function db_close(db) result(rc)
        !! Closes database.
        type(db_type), intent(inout) :: db

        rc = sqlite3_close(db%ptr)
        call db_error(rc, 'sqlite3_close()')
    end function db_close

    integer function db_create(db) result(rc)
        !! Creates database tables.
        type(db_type), intent(inout) :: db

        ! Create table "courses".
        rc = db_exec(db, "CREATE TABLE courses(" // &
                         "id INTEGER PRIMARY KEY, " // &
                         "name TEXT UNIQUE)")
        if (rc /= SQLITE_OK) return

        ! Create table "students".
        rc = db_exec(db, "CREATE TABLE students(" // &
                         "id INTEGER PRIMARY KEY, " // &
                         "name TEXT UNIQUE)")
        if (rc /= SQLITE_OK) return

        ! Create table "students_courses".
        rc = db_exec(db, "CREATE TABLE students_courses(" // &
                         "id INTEGER PRIMARY KEY, " // &
                         "student_id INTEGER NOT NULL, " // &
                         "course_id INTEGER NOT NULL, " // &
                         "FOREIGN KEY(student_id) REFERENCES students(id), " // &
                         "FOREIGN KEY(course_id) REFERENCES courses(id))")
        if (rc /= SQLITE_OK) return
    end function db_create

    integer function db_exec(db, query) result(rc)
        !! Executes SQLite query.
        type(db_type),    intent(inout) :: db
        character(len=*), intent(in)    :: query
        character(len=:), allocatable   :: err_msg

        rc = sqlite3_exec(db%ptr, query, c_null_ptr, c_null_ptr, err_msg)
        call db_error(rc, 'sqlite3_exec()', err_msg)
    end function db_exec

    integer function db_open(db, path) result(rc)
        !! Opens database.
        type(db_type),    intent(inout) :: db
        character(len=*), intent(in)    :: path

        rc = sqlite3_open(path, db%ptr)
        call db_error(rc, 'sqlite3_open()')
    end function db_open

    integer function db_print_ncourses_per_student(db) result(rc)
        !! Prints number of courses per student to standard output.
        type(db_type), intent(inout) :: db

        type(c_ptr)      :: stmt
        character(len=8) :: student
        integer          :: ncourses

        rc = sqlite3_prepare_v2(db%ptr, &
                "SELECT students.name, COUNT(students_courses.course_id) " // &
                "FROM students " // &
                "LEFT JOIN students_courses " // &
                "ON students.id = students_courses.student_id " // &
                "GROUP BY students.id, students.name " // &
                "ORDER BY students.name ASC", stmt)
        call db_error(rc, 'sqlite3_prepare_v2()')

        print '("Student  | #Courses")'
        print '(19("-"))'

        step_loop: do
            rc = sqlite3_step(stmt)

            select case (rc)
                case (SQLITE_ROW)
                    student  = sqlite3_column_text(stmt, 0)
                    ncourses = sqlite3_column_int(stmt, 1)
                    print '(a, " | ", i8)', student, ncourses

                case (SQLITE_DONE)
                    exit step_loop

                case default
                    call db_error(rc, 'sqlite3_step()')
                    exit step_loop
            end select
        end do step_loop

        rc = sqlite3_finalize(stmt)
        call db_error(rc, 'sqlite3_finalize()')
    end function db_print_ncourses_per_student

    subroutine db_error(code, proc, err_msg)
        !! Prints error message.
        integer,          intent(in)           :: code
        character(len=*), intent(in), optional :: proc
        character(len=*), intent(in), optional :: err_msg

        if (code == SQLITE_OK .or. code == SQLITE_DONE) return

        if (present(proc) .and. present(err_msg)) then
            print '(a, ": ", a, " (", i0, ")")', proc, err_msg, code
            return
        end if

        if (present(proc)) then
            print '(a, ": ", i0)', proc, code
            return
        end if

        print '("unknown error: ", i0)', code
    end subroutine db_error
end module db

The program in example.f90 imports module db and invokes the provided database abstraction functions to open a connection and to create the tables. Students, courses, and their relations are only added if the tables do not exist already. A real-world application would probably implement more sophisticated error handling:

! example.f90
program main
    use :: db
    implicit none
    character(len=*), parameter :: DB_FILE  = 'example.sqlite'

    type(db_type) :: db ! Database handle.
    integer       :: rc ! Return code.

    ! Open database.
    if (db_open(db, DB_FILE) /= DB_OK) stop

    ! Create tables.
    if (db_create(db) == DB_OK) then
        ! Add courses to database.
        rc = db_add_course(db, 'Fortran 1')
        rc = db_add_course(db, 'Fortran 2')
        rc = db_add_course(db, 'Fortran 3')

        ! Add students to database.
        rc = db_add_student(db, 'Alice')
        rc = db_add_student(db, 'Bob')

        ! Add students to courses.
        rc = db_add_student_to_course(db, 'Alice', 'Fortran 1')
        rc = db_add_student_to_course(db, 'Alice', 'Fortran 2')
        rc = db_add_student_to_course(db, 'Bob',   'Fortran 3')
    end if

    rc = db_print_ncourses_per_student(db)
    rc = db_close(db)
end program main

Compile the db module, then build and link the example program:

$ gfortran13 -c db.f90
$ gfortran13 -o example example.f90 db.o libfortran-sqlite3.a -lsqlite3

The application outputs the students and the number of courses they attend:

$ ./example
Student  | #Courses
-------------------
Alice    |        2
Bob      |        1

References

• SQLite: Official website

---

< nginx

[Index]

Lua >