Manual Reference Pages - M_sqlite (3)
NAME
M_sqlite(3fm) - [M_sqlite] Fortran interface to SQLite3 API
CONTENTS
Synopsis
Description
Data Types
Routines
Example
Limitations
Implementation Notes
Author
SYNOPSIS
Types
type(SQLITE_DATABASE) type(SQLITE_STATEMENT) type(SQLITE_COLUMN)Routines
Extensions call sqlite3_create_table(db) call sqlite3_delete_table(db) call sqlite3_column_query(column,name,type,length,function) call sqlite3_prepare_select(db,tablename,columns,stmt,extra_clause) call sqlite3_get_column(column,value)SQLite API call sqlite3_begin(db) call sqlite3_close(db) call sqlite3_column_props(column,name,type,length) call sqlite3_commit(db) call sqlite3_do(db,command) call sqlite3_finalize(stmt) call sqlite3_get_table(db,command,result,errmsg) call sqlite3_insert(db,tablename,columns) call sqlite3_next_row(stmt,columns,finished) call sqlite3_open(filename,db) call sqlite3_prepare(db,command,stmt,columns) call sqlite3_query_table(db,tablename,columns) call sqlite3_reset(stmt) call sqlite3_rollback(db) call sqlite3_set_column(column,value) call sqlite3_step(stmt,completion)
err = sqlite3_error(db) errmsg = sqlite3_errmsg(db)
Direct SQLite API (new) message = sqlite3_libversion() message = sqlite3_sourceid() ivalue = sqlite3_libversion_number()
iresult = sqlite3_threadsafe() iresult = sqlite3_initialize() iresult = sqlite3_shutdown() iresult = sqlite3_os_init() iresult = sqlite3_os_end()
DESCRIPTION
THIS IS AN EXPERIMENTAL ATTEMPT TO BEGIN A FORTRAN SQLITE3 INTERFACE.
The M_sqlite(3f) module provides a high-level means for Fortran programmers to use the SQLite3 library by Richard Hipp (http://www.sqlite.org)
The interface has been implemented in such a way that you can use a few high-level routines for common tasks, such as inserting data into a database and querying the contents. To this end the module defines a set of routines and functions as well as several derived types to hide the low-level details.
The remaining majority of the routines merely prepare SQL statements or are a simple interface to the original C functions. Therefore, when in doubt consult the SQLite documentation at http://www.sqlite.org.
In its current form, M_sqlite(3f) does not provide a full Fortran API to all the functionality offered by SQLite, but it should be quite useable.
DATA TYPES
The following derived types are defined:
The first two derived types are "opaque", that is they are used only to communicate between the application and the database library and there is information of interest to be gotten from them.
type(SQLITE_DATABASE) Variables of this type are used to hold the connection to the database or databases. They are created by the subroutine sqlite3_open(3f) The contents are valid until the database is closed (via sqlite3_close(3f)).
type(SQLITE_STATEMENT) Variables of this type hold prepared statements, the common method for database management systems to efficiently execute SQL statements. type(SQLITE_COLUMN) To provide easy communication with the database, SQLITE_COLUMN can hold values of different types. This means you can use a single routine and variable to transfer strings, integers or reals to and from the database. The third type is rather crucial to the working of the implementation: By setting the properties of an SQLITE_COLUMN variable you put data into the database or you can retrieve data from the database. See the example below for how this works.
There are a number of convenience routines that are meant to make this easier, which are marked below (with **). The remaining routines are calls to the C API for SQLite3.
ROUTINES
The M_sqlite(3fm) module currently provides the following functions:
General
call sqlite3_open(filename, db)
-- Open a database file and store the
connection for later use.
call sqlite3_close(db)
-- Close the database connection. Simply an
interface to the corresponding C function.
err = sqlite3_error(db)
-- Retrieve whether the previous command
resulted in an error or not. Returns
true if so, otherwise false.
Transactions
call sqlite3_begin(db)
-- Start a transaction.
call sqlite3_commit(db)
-- Commit the changes made since the start
of a transaction.
call sqlite3_rollback(db)
-- Undo changes made since the start of a
transaction.
Create tables
**call sqlite3_column_props(column, name, type, length)
-- Set the properties of a column
**call sqlite3_set_column(column, value)
-- Set the value of a column
call sqlite3_insert(db, tablename, columns)
-- Insert a complete new row into the table.
**call sqlite3_create_table(db)
-- Create a new table, based on the
properties of the columns.
**call sqlite3_delete_table(db)
-- Delete an existing table by name.
Query
**call sqlite3_column_query(column, name, type, length, function) -- Set properties of a column when constructing a SELECT query. The optional "function" is a string representing an SQL function like count or max. **call sqlite3_prepare_select(db,tablename,columns,stmt,extra_clause) -- Prepare a SELECT query. call sqlite3_next_row(stmt, columns, finished) -- Retrieve the next row of a SELECT query. **call sqlite3_get_column(column, value) -- Get the value of a column call sqlite3_get_table(db, command, result, errmsg) -- Get the result of a query in a single two-dimensional array call sqlite3_query_table(db, tablename, columns) -- Query the structure of the tableGeneral SQL command
call sqlite3_do(db, command) -- Run a single SQL command call sqlite3_prepare(db, command, stmt, columns) -- Prepare a general SQL statement for later actual execution. call sqlite3_step(stmt, completion) -- Run the prepared SQL statement call sqlite3_reset(stmt) -- Reset the prepared statement i so that it can be used again. call sqlite3_finalize(stmt) -- Free all resources associated with the prepared statement.Description
message=sqlite3_version() -- Get version number string message=sqlite3_sourceid() -- Get build identification string ival=sqlite3_libversion_number() -- Get library version number
EXAMPLE
To illustrate the usage of the library, here is a small example that is essentially the FLIBS example:
!-------------------------------------------------------------------- ! csvtable.f90 -- ! Program to read a simple CSV file and put it into a ! SQLite database, just to demonstrate how the Fortran ! interface works. program demo_csvtable use M_sqlite implicit none type(SQLITE_DATABASE) :: db type(SQLITE_STATEMENT) :: stmt type(SQLITE_COLUMN), pointer :: column(:) integer :: lun = 10 integer :: ierr character(len=40), dimension(4) :: name real :: salin real :: temp character(len=40) :: station character(len=40) :: date logical :: finished integer :: j character(len=40), pointer :: result(:,:) character(len=:),allocatable :: errmsg !-------------------------------------------------------------------- call sqlite3_open(’somedata.db’, db) !-------------------------------------------------------------------- ! The first part of the program simply defines the table: allocate(column(4)) call sqlite3_column_props(column(1), name(1), SQLITE_CHAR, 10) call sqlite3_column_props(column(2), name(2), SQLITE_CHAR, 10) call sqlite3_column_props(column(3), name(3), SQLITE_REAL) call sqlite3_column_props(column(4), name(4), SQLITE_REAL) call sqlite3_create_table(db, ’measurements’, column) !-------------------------------------------------------------------- ! The second part reads a data file and stores the data in a table: call csvdata() ! create CSV file for demonstration ! Open a CSV file to feed data into the database open(lun, file = ’somedata.csv’) ! To keep it simple: ! - The first line contains the names of the four columns read(lun, *) name ! - All lines after this contain the name of the station, ! the date and the two values. !-------------------------------------------------------------------- ! Insert the values into the table. ! ! Note that a transaction (via calls to sqlite3_begin and ! sqlite3_commit pair) is used, so that all the inserts can be ! done in one go. Inserting with autocommit is much slower, ! as the database file needs to be flushed every time. ! call sqlite3_begin(db) do read(lun,*,iostat=ierr) station, date, salin, temp if(ierr .ne. 0) exit call sqlite3_set_column(column(1), station) call sqlite3_set_column(column(2), date ) call sqlite3_set_column(column(3), salin ) call sqlite3_set_column(column(4), temp ) call sqlite3_insert(db, ’measurements’, column) enddo call sqlite3_commit(db) close(lun) !-------------------------------------------------------------------- ! To check that it works, retrieve the average salinity and average ! temperature per station and print them sorted by station name ! ! Retrieve the data by constructing an SQL query that will ! actually look like: ! ! SELECT station, AVG(salinity), AVG(temperature) FROM measurements ! GROUP BY station ORDER BY station; ! ! The routine sqlite3_prepare_select takes care of the actual ! construction of the above SQL query: ! ! We want a simple report, the mean of salinity and temperature ! sorted by the station ! deallocate(column)allocate(column(3)) call sqlite3_column_query & & (column(1), ’station’, SQLITE_CHAR) call sqlite3_column_query & & (column(2), name(3), SQLITE_REAL, function=’avg’) call sqlite3_column_query & & (column(3), name(4), SQLITE_REAL, function=’avg’)
call sqlite3_prepare_select(db, ’measurements’, column, stmt, & & ’group by station order by station’) !-------------------------------------------------------------------- write(*, ’(3a20)’)’Station’, ’Mean salinity’, ’Mean temperature’ do call sqlite3_next_row(stmt, column, finished) if(finished) exit call sqlite3_get_column(column(1), station) call sqlite3_get_column(column(2), salin ) call sqlite3_get_column(column(3), temp ) write(*, ’(a20,2f20.3)’) station, salin, temp enddo ! ! Get the entire table ! call sqlite3_get_table(db, "select * from measurements", result, errmsg)
if(associated(result))then write(*,*) ’Number of columns: ’, size(result,1) write(*,*) ’Number of rows: ’, size(result,2) do j = 1,size(result,2) write(*,’(10a20)’) result(:,j) enddo deallocate(result) else write(*,*) ’Error: result table not allocated’ write(*,*) ’Error: ’, trim(errmsg) endif
call sqlite3_close(db) !-------------------------------------------------------------------- contains !-------------------------------------------------------------------- subroutine csvdata() ! create fake datafile implicit none integer :: lun = 10 real, dimension(5) :: r character(len=100) :: line character(len=40) :: string character(len=20),dimension(6) :: station = & & [character(len=20) :: ’NW1’,’NW2’,’OS30_LONGER_NAME’,’DH’,’DO’,’Ah111’ ] integer :: i open(lun, file=’somedata.csv’) write(lun, ’(a)’) ’station,date,salinity,temperature’ do i = 1,100 call random_number(r) line = station(1+int(5.0*r(1))) write(string, ’(i0,a,i0,a,i0)’) & & 2005, ’-’, 1+int(12.0*r(2)), ’-’, 1+int(28.0*r(3)) line = trim(line) // ’,’ // string write(string, ’(f10.2)’) 28.0+6.0*r(4) line = trim(line) // ’,’ // adjustl(string) write(string, ’(f10.2)’) 15.0+5.0*r(5) line = trim(line) // ’,’ // adjustl(string) write(lun, ’(a)’) trim(line) enddo close(lun) end subroutine csvdata !-------------------------------------------------------------------- end program demo_csvtable !--------------------------------------------------------------------
LIMITATIONS
The module is not complete yet:
o There is no support for blobs o There is no support for NULL values or for DATE values. o The SQLite API is not completely covered, though the subset should be useful for many applications.
IMPLEMENTATION NOTES
o The library has been designed with 64-bits platforms in mind: it should run on these platforms without any difficulties.
AUTHOR
This version (20180311), by John Urban, uses the ISO_C_BINDING interface to provide portability between PEs (Programming Environments). The extensions are modeled on modules by Arjen Markus <arjenmarkus@sourceforge.net> and the associated C code is inspired by work by Al Danial (http://danial.org).
| M_sqlite (3) | March 11, 2021 |
