7 Databases

7.1 Database Connection

Each database use occurs within a single database session, which uses a specific database connection. Simple database programs use a single database session and a single database connection.

The more complex case of programs having multiple database connections is not covered by this tutorial, yet.

In Wafl, programs implicitly connect to a database, based on defined connection parameters. The programmer does not explicitly establish or break the connection between the program and the database.

In command-line programs, connection parameters can be defined in two ways: using command-line arguments and using configuration files. In both cases, to define a connection, it is necessary to specify the driver, database name, username, and password.

7.1.1 Command Line Database Connection Parameters

Driver is selected using the -dbdriver:<driver-name> option, where <driver-name> can be Odbc, Db2v11, Sqlite and None.

Database is selected using the -db:<db-alias> option, where <db-alias> is the name of the database that is visible to the selected driver. For Sqlib, alias is the filename of the database file. * Both the driver and the database can be specified with a single option -db:<driver-name>:<db-alias>.

Username is specified using the -user:<username> option. In some cases, and depending on the database driver and operating system, the username is not required (for Sqlib) or can be detected from the current terminal session (Db2).

Password is specified using the -pwd:<password> option. Both username and password can be specified by a single option -user:<username>:<password>.

Specifying Password

In command-line programs, it is recommended not to specify a password in the command line, especially if the commands are specified in batch files.

If the program is running in console mode and the password is not entered or is incorrect, the program will prompt the user to enter the correct password three times.

7.1.2 Configuration File Database Parameters

Some of the database connection parameters can be set using configuration files. These parameters are located in the [database] section:

• The default-driver parameter defines the default database driver that is used if no other driver is explicitly specified.
• The default-db parameter defines a database name that is used if no other database is explicitly specified.
• The default-query-type parameter defines whether queries are typed or untyped by default. It can be typed or untyped.

For security reasons, it is not possible to specify a username or a password in configuration files.

Here is an example of the [database] section in a configuration file:

[database]
default-db = Test
default-driver = Db2v11
default-query-type = untyped

For more detail please see the Configuration Files section.

7.2 Queries

7.2.1 Query Functions

The usual way to write database queries in Wafl is to define query functions. Query functions have a custom syntax, but they evaluate in the same way as any other Wafl function.

A query function definition begins with the keyword sequence [typed | untyped] [sql] query, followed by the query enclosed in curly braces. The function arguments can be used in the query using the usual host variable syntax supported by many database tools.

The keywords typed and untyped specify the function return type. If neither is specified, the default behavior depends on the implementation version and the configuration files. It is strongly recommended to always specify one of these keywords.

In both cases, the result of the query is a list of rows, but the rows can be represented by records (typed rows) or by maps of string values (untyped rows).

The sql keyword specifies that the query is written in SQL. No other query languages are supported in this version of Wafl.

For example, the following query function returns a list of books with a given title:

qBooks( title ) = untyped sql query {
    select * from books
    where book_title = :title
};

Untyped Queries

The untyped query function returns a list of string maps: List[Map[String][String]]. Each value returned by the database is converted to its string representation and returned as a string. The processing of rows is the same as the processing of the lists and string maps map types.

For example, the following code creates the report on books whose titles contain the given string, in CSV format. Each row is converted to a line of text, using a lambda function \r:..., and the rows are glued together using \n as a separator:

booksReport( titlePart ) = 
    'Title,Author,Pages\n'
    + qBooks( '%' + titlePart + '%' )
        .map(\r: r['title']) + ',' + r['author'] + ',' + r['pages'])
        .strJoin('\n');

qBooks( titleTpl ) = untyped sql query {
    select title, author, pages
    from books
    where book_title like :titleTpl
};

The type of the query function qBooks is: (String -> List[Map[String][String]]).

Untyped queries all have the same result type. If we need to write some code that handles the results of many different queries in a general way, then it can be better to use untyped queries.

Typed Queries

The typed query function returns a list of records, where the records elements correspond to the columns by both name and type. The type of the query result is checked during the type-checking of the program.

For example, the code equivalent to the previous example, but with typed queries, can be written as follows:

booksReport( title ) = 
    'Title,Author\n'
    + qBooks( title )
        .map(\r: r$title + ',' + r$author + r$pages$)
        .strJoin('\n');

qBooks( title ) = typed sql query {
    select * from books
    where book_title = :title
};

The query function type is: (String -> List[Record[author: String, pages: Integer, title:String]].

Untyped queries are always strongly type-checked in the same way as the other Wafl functions. Thus, they are better to be used when we need to be sure that the data that comes from the database is of the expected and appropriate type.

Query Arguments

Query arguments are analyzed and type-checked in the same way as the column types. If the database provides the data on the query parameters, these data is used to determine the argument types. For example, for most of the relational databases and the usual ODBC interface, the following query will have correctly determined arguments:

qBooks( title, pages, author ) = untyped sql query {
    select *
    from books
    where title = :t
       or pages = :p
       or author = :a
};

The type of this function should be determined as: (String * Integer * String -> List[Map[String][String]]).

Arguments are handled the same way for typed and untyped queries. However, some databases and database drivers may only accept string arguments, regardless of where the arguments are used in the query.

Dynamic Queries

If a query text is not known in advance when the program is developed, then database can be queried using queries defined as strings. In such cases the queries cannot include arguments - the arguments values should be embedded in the query text.

The library function dynSqlQuery gets a query text and returns a record that contains the query execution status and the resulting rows. Its type is: (String -> Record[errCode:Int, errText:String, ok:Bool, result:List[Map[String][String]]]).

• ok - a simple logical result, true if everything is correct;
• errCode - the standard SQLCODE integer value;
• errText - the textual representation of the error;
• result - result, as a list of string maps.

Lazy Semantics

All query functions use lazy reading - the result of the query is a so-called lazy list. No rows are retrieved from the database before attempting to read the corresponding element of the resulting list. Rows are read as much as are actually needed.

7.3 Transactions

7.3.1 Transactional Functions

The transactional functions are a special type of Wafl functions that have transactional semantics. A transactional function is essentially defined as a logical conjunction of a sequence of logical expressions - if all expressions evaluate the logical value true, then the transaction is committed and the result is true, otherwise, if at least one expression evaluates to false, the evaluation is canceled, the transaction is rolled back, and the result is false.

The definition of a transactional function begins with the keyword sequence [sql] transaction, followed by the sequence of action statements enclosed in curly braces and separated by semicolons. Just like with the queries, SQL is currently the only supported language.

Three kinds of action statements are supported:

• SQL statements, that evaluate to true if successfully executed and to false in the case of failure;

• regular logical Wafl expressions;

• action statements, that modify the environment and return true if successful.

SQL Statements

SQL statements are regular SQL statements with no predefined limits. However, each driver and/or database can have some constraints.

Each driver must support insert, update and delete statements. Additionally, the statements for creating and dropping SQL objects, granting and revoking privileges and other statements can be supported.

The host variables are used in SQL statements in the same way as the queries.

Logical Expressions

Logical expressions are used as a kind of guards. If a logical expression evaluates to true, everything is assumed to be correct and the transaction resumes. If it evaluates to false, transaction is cancelled and rolled back.

Action Statements

…todo…

Example

The following transactional function insert a row in a book table and updates its pages count:

tAddBook( title, author, pages ) = sql transaction {
    insert into books( title, author )
    values (:title, :author);
    
    update books
    set pages = :pages
    where title = :title
      and author = :author;
};

7.3.2 …

Tuple elements are accessed via tuple selectors. As tuple elements are not named, their position is used to select them. The positions of the tuple elements are 1-based.

Tuple selectors use the dot-syntax <tuple>.<n>, where a positive integer indicates the position of the element to be read.

{# 1, 2, 3, 4, 5 #}.f()
where{
    f(t) = {#
        t.1,
        t.1 + t.2,
        t.1 + t.2 + t.3,
        t.1 + t.2 + t.3 + t.4,
        t.1 + t.2 + t.3 + t.4 + t.5
    #};
}

{# 1, 3, 6, 10, 15 #}

The selector indices and element types are checked in the type-checking phase so that it is not possible to use an invalid selector.