dm_db_query Module

Basic SQL query builder.

The following example uses the query builder to extend an SQL query with WHERE, ORDER BY, and LIMIT parameters. An observation database observ.sqlite must be provided, from which an observation id observ_id is read.

character(len=:), allocatable :: node_id, observ_id, sensor_id, target_id
character(len=:), allocatable :: sql

integer             :: rc
type(db_type)       :: db
type(db_query_type) :: dbq
type(db_stmt_type)  :: dbs

! Query parameters.
node_id   = 'dummy-node'
sensor_id = 'dummy-sensor'
target_id = 'dummy-target'

! Open an existing observation database first.
rc = dm_db_open(db, 'observ.sqlite', read_only=.true.)
call dm_error_out(rc, fatal=.true.)

! Set SQL base query string.
call dm_db_query_set_sql(dbq,                                      &
    'SELECT observs.id FROM observs '                           // &
    'INNER JOIN nodes ON nodes.row_id = observs.node_id '       // &
    'INNER JOIN sensors ON sensors.row_id = observs.sensor_id ' // &
    'INNER JOIN targets ON targets.row_id = observs.target_id')

! Set WHERE clause of the query.
call dm_db_query_where(dbq, 'nodes.id = ?',   node_id)
call dm_db_query_where(dbq, 'sensors.id = ?', sensor_id)
call dm_db_query_where(dbq, 'targets.id = ?', target_id)

! Set ORDER BY and LIMIT of the query.
call dm_db_query_set_order(dbq, by='observs.timestamp', desc=.true.)
call dm_db_query_set_limit(dbq, 1_i8)

! Create full query string from base query.
sql = dm_db_query_build(dbq)

sql_block: block
    ! Prepare the database statement.
    rc = dm_db_prepare(db, dbs, sql)
    if (dm_is_error(rc)) exit sql_block

    ! Bind the query parameters to the statement.
    rc = dm_db_bind(dbs, dbq)
    if (dm_is_error(rc)) exit sql_block

    ! Run the statement.
    rc = dm_db_step(dbs)
    if (rc /= E_DB_ROW) exit sql_block

    ! Get next row as allocatable character string.
    rc = dm_db_row_next(dbs, observ_id)
    if (dm_is_error(rc)) exit sql_block
end block sql_block

call dm_error_out(rc, verbose=.true.)
call dm_db_query_destroy(dbq)
call dm_db_finalize(dbs)
call dm_db_close(db)

if (allocated(observ_id)) print '("observ_id: ", a)', observ_id

Make sure to not add more than DB_QUERY_NPARAMS parameters to a query or increase the constant first (32 is assumed to be sufficient for WHERE and SET parameters).


Uses

  • module~~dm_db_query~~UsesGraph module~dm_db_query dm_db_query module~dm_error dm_error module~dm_db_query->module~dm_error module~dm_kind dm_kind module~dm_db_query->module~dm_kind module~dm_error->module~dm_kind module~dm_ascii dm_ascii module~dm_error->module~dm_ascii iso_fortran_env iso_fortran_env module~dm_kind->iso_fortran_env

Used by

  • module~~dm_db_query~~UsedByGraph module~dm_db_query dm_db_query module~dm_db dm_db module~dm_db->module~dm_db_query module~dm_db_api dm_db_api module~dm_db_api->module~dm_db_query module~dm_db_api->module~dm_db module~dm_db_table dm_db_table module~dm_db_api->module~dm_db_table module~dm_db_count dm_db_count module~dm_db_api->module~dm_db_count module~dm_db_pragma dm_db_pragma module~dm_db_api->module~dm_db_pragma module~dm_db_row dm_db_row module~dm_db_api->module~dm_db_row module~dm_db_json dm_db_json module~dm_db_json->module~dm_db_query module~dm_db_json->module~dm_db module~dm_db_json->module~dm_db_count module~dm_db_json->module~dm_db_row module~dm_db_table->module~dm_db_query module~dm_db_table->module~dm_db module~dmpack dmpack module~dmpack->module~dm_db_query module~dmpack->module~dm_db module~dmpack->module~dm_db_api module~dmpack->module~dm_db_json module~dmpack->module~dm_db_table module~dmpack->module~dm_db_count module~dmpack->module~dm_db_pragma module~dmpack->module~dm_db_row module~dm_db_count->module~dm_db module~dm_db_pragma->module~dm_db module~dm_db_row->module~dm_db

Variables

Type Visibility Attributes Name Initial
integer, public, parameter :: DB_QUERY_TYPE_NONE = 0

No type (invalid).
integer, public, parameter :: DB_QUERY_TYPE_DOUBLE = 1

SQLite double precision.
integer, public, parameter :: DB_QUERY_TYPE_INT = 2

SQLite 32-bit integer.
integer, public, parameter :: DB_QUERY_TYPE_INT64 = 3

SQLite 64-bit integer.
integer, public, parameter :: DB_QUERY_TYPE_TEXT = 4

SQLite text.

Interfaces

public interface dm_db_query_update

Generic subroutine to add SET values to UPDATE query. The procedures do not validate that values have been added only once. The function is prone to SQL injections. Only pass parametrised strings!

  • private subroutine db_query_update_double(db_query, column, value, error)

    Adds double precision SET parameter to query. Returns E_LIMIT in error if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: column

    Column name.
    real(kind=r8), intent(in) :: value

    New column value.
    integer, intent(out), optional :: error

    Error code.

  • private subroutine db_query_update_int(db_query, column, value, error)

    Adds 32-bit integer SET parameter to query. Returns E_LIMIT if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: column

    Column name.
    integer(kind=i4), intent(in) :: value

    New column value.
    integer, intent(out), optional :: error

    Error code.

  • private subroutine db_query_update_int64(db_query, column, value, error)

    Adds 64-bit integer SET parameter to query. Returns E_LIMIT if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: column

    Column name.
    integer(kind=i8), intent(in) :: value

    New column value.
    integer, intent(out), optional :: error

    Error code.

  • private subroutine db_query_update_text(db_query, column, value, error)

    Adds text parameter to SET query. Returns E_LIMIT if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: column

    Column name.
    character(len=*), intent(in) :: value

    New column value.
    integer, intent(out), optional :: error

    Error code.

public interface dm_db_query_where

Generic subroutine to add WHERE values to query. The procedures do not validate that values have been added only once. The function is prone to SQL injections. Only pass parametrised strings!

  • private subroutine db_query_where_double(db_query, param, value, error)

    Adds double precision WHERE parameter to query. Returns E_LIMIT in error if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: param

    Query parameter.
    real(kind=r8), intent(in) :: value

    Query parameter value.
    integer, intent(out), optional :: error

    Error code.

  • private subroutine db_query_where_int(db_query, param, value, error)

    Adds 32-bit integer WHERE parameter to query. Returns E_LIMIT if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: param

    Query parameter.
    integer(kind=i4), intent(in) :: value

    Query parameter value.
    integer, intent(out), optional :: error

    Error code.

  • private subroutine db_query_where_int64(db_query, param, value, error)

    Adds 64-bit integer WHERE parameter to query. Returns E_LIMIT if parameter limit has been reached.

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: param

    Query parameter.
    integer(kind=i8), intent(in) :: value

    Query parameter value.
    integer, intent(out), optional :: error

    Error code.

  • private subroutine db_query_where_text(db_query, param, value, empty, error)

    Adds text parameter to WHERE query. Returns E_LIMIT if parameter limit has been reached. Empty strings and strings containing only white-space characters are ignored, unless argument empty is set to .true..

    Arguments

    Type IntentOptional Attributes Name
    type(db_query_type), intent(inout) :: db_query

    Database query type.
    character(len=*), intent(in) :: param

    Query parameter.
    character(len=*), intent(in) :: value

    Query parameter value.
    logical, intent(in), optional :: empty

    Add empty string.
    integer, intent(out), optional :: error

    Error code.

Derived Types

type, public ::  db_query_param_type

Single WHERE or SET parameter of database query.

Components

Type Visibility Attributes Name Initial
integer, public :: type = DB_QUERY_TYPE_NONE

Value type.
real(kind=r8), public :: value_double = 0.0_r8

Double value.
integer, public :: value_int = 0

Integer value.
integer(kind=i8), public :: value_int64 = 0.0_i8

64-bit integer value.
character(len=:), public, allocatable :: value_text

Text value.
character(len=:), public, allocatable :: sql

WHERE clause or column name.

type, public ::  db_query_type

Database query with SET values (for SQL UPDATE), WHERE parameters, LIMIT, and ORDER BY. Do not modify this derived type directly!

Components

Type Visibility Attributes Name Initial
character(len=:), public, allocatable :: sql

SQL base query.
character(len=:), public, allocatable :: order_by

ORDER BY clause.
logical, public :: order_desc = .false.

ASC or DESC order.
integer(kind=i8), public :: limit = 0_i8

Row limit.
integer, public :: nparams = 0

Current WHERE parameter array size.
integer, public :: nupdates = 0

Current SET parameter array size.
type(db_query_param_type), public :: params(DB_QUERY_NPARAMS)

WHERE parameter array.
type(db_query_param_type), public :: updates(DB_QUERY_NPARAMS)

SET parameter array.

Functions

public function dm_db_query_build(db_query, base) result(sql)

Returns SQL string from query. If no base SQL query base is passed, uses query attribute sql instead. If attribute sql is not allocated, the SQL base query will be empty (and only the WHERE, ORDER BY, and LIMIT parameters are returned). A passed base query will overwrite the attribute of argument db_query.

Read more…

Arguments

Type IntentOptional Attributes Name
type(db_query_type), intent(inout) :: db_query

Database query type.
character(len=*), intent(in), optional :: base

Base query string.

Return Value character(len=:), allocatable

SQL string.


Subroutines

public pure elemental subroutine dm_db_query_destroy(db_query)

Resets query.

Arguments

Type IntentOptional Attributes Name
type(db_query_type), intent(inout) :: db_query

Database query type.

public pure elemental subroutine dm_db_query_set_limit(db_query, limit)

Sets LIMIT clause of query. If argument limit is not passed, not limit is set. Passing 0 disables the LIMIT parameter.

Arguments

Type IntentOptional Attributes Name
type(db_query_type), intent(inout) :: db_query

Database query type.
integer(kind=i8), intent(in), optional :: limit

Limit value.

public pure elemental subroutine dm_db_query_set_order(db_query, by, desc)

Sets ORDER BY clause of query. Argument by must be a valid field name. The function is prone to SQL injections. Only pass parametrised strings! If desc is not passed, ascending order is used.

Arguments

Type IntentOptional Attributes Name
type(db_query_type), intent(inout) :: db_query

Database query type.
character(len=*), intent(in) :: by

Field name.
logical, intent(in), optional :: desc

Descending order.

public pure elemental subroutine dm_db_query_set_sql(db_query, base)

Sets SQL base query. The function is prone to SQL injections. Only pass parametrised strings!

Arguments

Type IntentOptional Attributes Name
type(db_query_type), intent(inout) :: db_query

Database query type.
character(len=*), intent(in) :: base

SQL base query.