Language-integrated updatable views

Relational lenses are a modern approach to the view update problem in relational databases. As introduced by Bohannon et al. [5], relational lenses allow the definition of updatable views by the composition of lenses performing individual transformations. Horn et al. [20] provided the first implementation of incremental relational lenses, which demonstrated that relational lenses can be implemented efficiently by propagating changes to the database rather than replacing the entire database state. However, neither approach proposes a concrete language design; consequently, it is unclear how to integrate lenses into a general-purpose programming language, or how to check that lenses satisfy the well-formedness conditions needed for predictable behaviour. In this paper, we propose the first full account of relational lenses in a functional programming language, by extending the Links web programming language. We provide support for higher-order predicates, and provide the first account of typechecking relational lenses which is amenable to implementation. We prove the soundness of our typing rules, and illustrate our approach by implementing a curation interface for a scientific database application.


INTRODUCTION
Relational databases are considered the de facto standard for storing data persistently, offering a ready-to-use method for storing and retrieving data efficiently in a broad range of contexts.
Programs interface with relational databases using the Structured Query Language (SQL).To query the database, the host application needs to generate an SQL query from user input, issue it to the database server, and then process the result in a way that aligns with the result of the query.
As an example, we consider a music database, originally proposed by Bohannon et al. [5] and shown in Figure 1.There are two tables: the albums table, which details the quantities of albums available, and the tracks table, which details the track name, year of release, rating, and the album on which the track is contained.Our application could generate a query by using string concatenation and then assume the result will be in a known format containing records of track names of type string and years of type int.
However, such an approach leaves many possible sources of error, most of which are related to a lack of cross-checking of the different stages of execution.The application could have bugs in query generation, which might result in incorrect queries or even security flaws.Furthermore, a generated query may not produce a result of the type that the application expects, resulting in a runtime error.The user experience of the programmer is also poor, as tooling provides little help and the programmer must write code in two different languages, while being mindful not to introduce any bugs in the application.We refer to this as an impedance mismatch between the host programming language and SQL [9].Existing work on language integrated query (LINQ) allows queries to be expressed in the host language [8,29].Rather than generating an SQL query using string manipulation, the query is written in the same syntax as the host programming language.The user need not worry about how the query is generated, and the code that performs the database query is automatically type-checked at compile time.
As an example of LINQ, consider the following function, written in the Links [8] programming language, which queries the albums table and returns all albums with a given album name: fun getAlbumsByName(albumName) { for (a <--albums) where (a.album == albumName) [a] } The corresponding SQL for getAlbumsByName("Galore") would be: SELECT * FROM albums AS a WHERE a.album == "Galore" LINQ approaches are convenient for querying databases, but still take a relatively fine-grained approach to data manipulation (updates).The programmer is required to explicitly determine which changes were made at the application level.All modifications made by the user must then be translated into equivalent insertions, updates and deletions for each table.In contrast, a typical user workflow consists of fetching a subset of the database, called a view, making changes to this view, and then propagating the changes to Defining views that can be updated directly is known as the view-update problem, a long-standing area of study in the field of databases [4].
Relational Lenses.A recent approach to the view update problem is to define views using composable relational lenses [5].Lenses are a form of bidirectional transformation [14].With relational lenses, instead of defining the view using a general SQL query, the programmer defines the view by combining individual lenses, which are known to behave in a correct manner.Bohannon et al. [5] define lenses for relational algebra operations, in particular, projections, selections and joins.Figure 2 shows the composable nature of relational lenses.
A relational lens can be considered a form of asymmetric lens, in which we have a forward (get) direction to fetch the data, and a reverse (put) direction to make updates [16].A bidirectional transformation is well-behaved if it satisfies round-tripping guarantees: Relational lenses are equipped with typing rules which ensure operations on lenses are well-behaved.The type system for relational lenses tracks the attribute types of the defined view as well as constraints, including predicates and functional dependencies, which are not easily expressible in an ML-like type system.
From theory to practice.The theory of relational lenses was developed over a decade ago by Bohannon et al. [5], but until recently there has been little work on practical implementations.Horn et al. [19] recently presented the first implementation using an incremental semantics.However Horn et al. [19] focus on performance rather than language integration, leaving two issues unresolved: • How to integrate relational lenses, which are defined as a sequential composition of primitives, into a functional language, where lenses are composed using lens subexpressions.• How to define and verify the correctness of a concrete selection predicate syntax for relational lenses.
Predicates.Some of the relational lens constructors, such as the select lens, require user supplied functions for filtering rows.Such functions, called predicates, determine whether or not an individual record should be included.Predicates are a function of type R → bool where R is the type of the input record and a return value of true indicates that the predicate holds.
Bohannon et al. [5] treat predicates as abstract (finite or infinite) sets, without giving a computational syntax.Sets allow predicates to be defined in an abstract form while still being amenable to mathematical reasoning, but such an approach does not scale to a practical implementation.In practice the user should define a predicate as a function from a record (in this case containing album and year fields) to a Boolean value: fun(x) { x.album = "Galore" && x.year == 1989 } Some of the lens typing rules require static checks on predicates.The above predicate contains only static information, and is thus a closed function which can be checked at compile-time.We call such predicates static predicates.Alas, such checks become problematic when the programmer would like to define a function which depends on information only available at runtime, such as a parameter in a web request.For example, consider the following function which adapts the getAlbumsByName function to use relational lenses.As albumsName is supplied as a parameter to the getAlbumsByNameL function, the anonymous predicate supplied to select can only be completely known at runtime.We call such predicates dynamic predicates.Dynamic predicates are not closed, which means that variables in the closure of a dynamic predicate may not be available until runtime, and may themselves refer to functions.While it is possible to statically know the type of the function, and thus rule out a class of errors, relational lenses require finer-grained checks which require a more in-depth analysis of the predicate.As an example, a select lens is only well-formed if the predicate does not rely on the output of a functional dependency.
If we required the function to be fully known at compile time, a programmer could not define predicates that depend on user input.Thus, there is a tradeoff between static correctness and programming flexibility.In our design, we can perform checks on lenses using static predicates at compile-time, and we can also support dynamic predicates by performing the same checks at runtime.
Another obstacle is the handling of functional dependencies, which are an important part of the type system for relational lenses.Functional dependencies are constraints that apply to the data, and specify which fields in a table uniquely determine other fields.
The typing rules given by Bohannon et al. [5] are important for showing soundness of relational lenses: without ensuring all the requirements are met, it is not possible to ensure the lenses are well-behaved.We take the existing work by Bohannon et al. [5] and concretise and adapt the design to allow the rules to be implemented in practice.

Contributions
The primary technical contribution of this paper is the first full design and implementation of relational lenses in a typed functional programming language, namely Links [8].This paper makes three concrete contributions: (1) A design and implementation of predicates for relational lenses, based on previous approaches to language-integrated query.We define a language of predicates, and show how terms can be normalised to a fragment both amenable to typechecking of relational lenses, and translation to SQL. (2) An implementation of the typing rules for relational lenses, adapted to the setting of a functional programming language ( §3).We prove ( §3.4) that our compositional typing rules are sound with respect to the original rules proposed by Bohannon et al. [5].Static predicates can be fully checked at compile time, whereas the same checks can be performed on dynamic predicates at runtime.(3) A curation interface for a real-world scientific database implemented as a cross-tier web application, tying together relational lenses with the Model-View-Update architecture for frontend web development ( §4).
We have packaged our implementation and example application as an artifact [18].
The remainder of the paper proceeds as follows: §2 describes the design and implementation of predicates; §3 describes the implementation of static typechecking for relational lenses; §4 describes the case study; §5 describes related work; and §6 concludes.

PREDICATES
In their original proposal for relational lenses, Bohannon et al. [5] define predicates using abstract sets.Although theoretically convenient, such a representation is not suited to implementation in a programming language.Our first task in implementing relational lenses, therefore, is to define a concrete syntax for predicates.
As we are working in the setting of a functional programming language, it is natural to treat predicates as functions from records to Boolean values.As an example, recall our earlier example of the select lens, which selects albums with a given name: select from albumsLens where fun(a) { a.album == albumName } Here, the predicate function is fun(a) {a.album == albumName}.Intuitively, this predicate includes a record a in the set of results if its album field matches albumName.
In our approach, predicates are a well-behaved subset of Links functions which take a parameter of the type of row on which the lens operates.We define a simply-typed λ-calculus for predicates, and apply the normalisation approach advocated by Cooper [7] to derive a form which is both amenable to SQL translation, and can be used when typechecking lens construction.

Static and Dynamic Predicates
Ensuring relational lenses are well-typed requires some conditions that require static knowledge of predicates.As an example, we require that the predicate of a select lens does not refer to the outputs of the functional dependencies of a table; we describe the conditions more in detail in Section 3.
Our approach distinguishes two types of predicates: static predicates, which rely on only static information; and dynamic predicates, which can refer to arbitrary free variables.Referring to our previous example, fun(a) { a.album == albumName } is a dynamic predicate, as albumName is a free variable, whereas fun(a) { a.album == "Paris"} is a static predicate.
We can check the construction of lenses with static predicates entirely statically, whereas lenses with dynamic predicates require  the same checks to be performed dynamically.Our formal results are based on static predicates, however the same results apply for dynamic predicates (which can be treated as closed at runtime).

Predicate Language
Syntax. Figure 3 shows the syntax of the predicate language.Types, ranged over by A, B, C, include function types A → B; record types ( − −− → ℓ : A) mapping labels ℓ to values of type A; and base types D, ranging over the types of Boolean values, strings, and integers.It is convenient to let R range over records whose fields are of base type.The unit type () is definable as a record with no fields.
Terms, ranged over by L, M, N , are those of the simply-typed λ-calculus extended with base types, records, conditional statements, and n-ary operators on base types ⊙ { − → M }.We assume that the set of available operators all have an SQL equivalent and assume the existence of at least the comparison operators <, >, == and Boolean negation, conjunction, and disjunction.We sometimes find it convenient to use infix notation for binary operators.
Typing.Most typing rules are standard for the simply-typed λcalculus extended with records.The only non-standard rule is T-Op,

Normal forms
O which states that the arguments to an operator must be of base type and match the type of the operator.
Normalisation.Given a functional language for predicates, we wish to show that predicates can be normalised to a fragment easily translatable to SQL and usable when typechecking lenses.Figure 4 introduces normal forms O which include variables, constants, λabstractions, records whose fields are all values, record projection from a variable, conditional expressions whose subterms are all in normal form, and operations whose arguments are all in normal form.Terms in predicate normal form, ranged over by P, are a restriction of terms in normal forms.Terms in predicate normal form have a straightforward SQL equivalent, and can be used when typechecking lenses.
Normalisation rules M ⇝ N are a subset of the rules proposed by Cooper [7]: the first four rules are standard β-reduction rules; the fifth pushes function application inside branches of a conditional; and the sixth pushes conditional expressions inside each component of a record.Normalisation rules can be applied anywhere in a term, so we do not require congruence rules.
The rewrite system is strongly normalising.
Proposition 1 (Strong normalisation).If Γ ⊢ M : A, then there are no infinite ⇝ sequences from M.
Proof.A special case of the result shown by Cooper [7].□ Static predicates refer only to constants and properties of a given record.Let ⇝ * be the transitive, reflexive closure of the normalisation relation.Given a variable with base record type R, we can show that normalisation results in a term in predicate normal form.Consequently, any static predicate written in our predicate language can be normalised to predicate normal form, allowing it to be used in typechecking of lenses and for translation into SQL.Furthermore, the normalisation procedure can be applied to any dynamic predicate at runtime in order to allow the same checks to be performed dynamically.

TYPECHECKING RELATIONAL LENSES
In this section, we show how naïve composition of lens combinators can give rise to ill-formed lenses, and show how such ill-formed lenses can be ruled out using static and dynamic checks.We adapt the rules proposed by Bohannon et al. [5] to the setting of a functional programming language.We begin by discussing functional dependencies, and then look at each lens combinator in turn.

Functional Dependencies
Functional dependencies are constraints restricting combinations of records.A functional dependency We use F and G to denote sets of functional dependencies.It is possible to derive functional dependencies from other functional dependencies.The judgement ℓ ′ can be derived from the set of functional dependencies F following Armstrong's axioms [2]; these (standard) derivation rules can be found in Appendix A. The output fields of the functional dependencies F, written outputs(F), is the set of fields constrained by F and is defined as:

Table names S,T
Types A, B :: Bohannon et al. [5] impose a special restriction on functional dependencies called tree form.Tree form requires that functional dependencies form a forest, meaning that column names can be partitioned into pairwise-disjoint sets forming a directed acyclic graph with at most one incoming edge per node.As an example, {A → B, A → C, C → D} is in tree form.It is straightforward to check whether a set of functional dependencies is in tree form using a standard graph reachability algorithm.
Sets of functional dependencies which are semantically equivalent to a set of functional dependencies in tree form are also considered to be in tree form.As an example, {A → BC, C → D} is not literally in tree form but is semantically equivalent to the previous example, so can thus considered to be in tree form.

Lens Types
Figure 5 shows the additional types and terms for tables and lens constructs.We let S,T range over table names.Type table of (S, R) is the type of a table with table name S containing records of type R. The record set type record set of R describes a set of records of type R. The type of lenses, lens of (Σ, R, λx .P, F), consists of four components: the set of underlying tables Σ; the base record type R; a restriction predicate λx .P; and a set of functional dependencies F. The base record type describes the type of rows which can be retrieved or committed to the view, and the restriction predicate describes the subset of records on which the lens operates.
In the remainder of the section, we describe each lens combinator and its typing rule in turn.

Rules
We now introduce the rules we use to typecheck relational lenses, adapted from the rules as defined by Bohannon et al. [5] to support nested composition and to make use of our concrete predicate syntax.We show a formal correspondence between our typing rules and the typing rules of Bohannon et al. [5] in §3.4.We first introduce some notation.Definition 2 (Record concatenation).
Tables.Links defines a primitive table expression table S with R which defines a handle to a table in the database.The table expression assumes that the programmer has supplied a record type which corresponds to the types in the underlying database schema.
Lens Primitives.The rule T-Lens is used to create a relational lens from a Links table.A lens primitive is assigned the default predicate constraint true.All columns referred to by a set of functional dependencies F, written names(F), should be part of the table record type R.
Γ ⊢ lens M with F : lens of ({S }, R, λx .true, F) We might then decide to further limit this view by defining a lens l 3 which only shows the tables with the album Galore.The user then notices that the rating for Lovesong is not correct, and changes it from 5 to 4. Calling put on l 3 would yield the updated view for l 2 : Since the rating of the track Lovesong is 4 and not lower than the quantity of the album Paris, the updated view for l 2 violates the predicate requirement quantity < rating.
To prevent such an invalid combination of lenses, the select lens needs to ensure that the underlying lens has no predicate constraints on any fields which may be changed by functional dependencies.The set of fields which can be changed by functional dependencies F is outputs(F).A predicate P ignores the set − → ℓ if the result of evaluating the predicate P with respect to a row in the database is not affected by changing any fields in Definition 3 (Predicate Ignores).We say P ignores − → ℓ if there exists an R such that − → ℓ is disjoint from dom(R) and x : R ⊢ P : bool.
The T-Select rule also needs to ensure that the resulting lens only accepts records that satisfy the given predicate λx .Q as well as any existing constraints λx .P that already apply to the underlying lens.The resulting lens's constraint predicate can thus be defined as λx .P ∧ Q.The full select lens typing rule can be defined as: Γ ⊢ M : lens of (Σ, R, λx .P, F) x : R ⊢ Q : bool F is in tree form P ignores outputs(F) In this case, there is no way to define a correct behaviour for put.If the user's review is deleted then the other entry by the same user would also be removed from the joined table.If the track is deleted, then the entry from the other user for the same track would also be removed.
The issue is resolved by requiring that one of the tables is completely determined by the join key.The added functional dependency restriction ensures that each entry in the resulting view is associated with exactly one entry in the left table.In this case, if the reviews table contained a single review per track, it would be possible to delete any individual record by only deleting the entry in the reviews table.In practice we need to show that we can derive the functional dependency ℓ ′ are the join columns and − → ℓ ′ is the set of columns of the right table.We can check if this functional dependency can be derived by calculating the transitive closure of ℓ ′ is a subset.Join lenses come in different variants with varying deletion behaviours: a variant that always deletes the entry from the left table, a variant that tries to delete from the right table and otherwise deletes from the left table, and a variant that deletes the entries from both tables if possible.The type checking for each variant is similar, so we only discuss the delete left lens.The rule T-Join-Left requires us to also show that P ignores outputs(F) and Q ignores outputs(G).The resulting lens should have the predicate P ∧ Q since the record constraints of both input lenses apply to the output lens.What would the new predicate constraint be?It cannot reference the field year, since it does not exist anymore.If it were rating > 4 then the last record would be a violation in the output view.If the predicate were true it would violate PutGet: Changing the rating from 5 to 3 for the track Lovesong, would cause it to no longer satisfy the parent lens' predicate since it is from year 1989 and the rating is only 3.
The underlying issue is the dependency between the dropped field year and the field rating.It is not possible to define a predicate P which specifies if any rating value is valid independently of the drop column year.Without being able to construct such a P, a lens cannot be well-typed.
Lossless Join Decomposition.The typing rule for the drop lens requires some finer-grained checks on predicates.We begin with some preliminary definitions.Definition 4 (Predicate satisfaction).We say that a record r satisfies predicate λx .P, written sat(λx .P, r ), if P[r /x] ⇓ true.
Definition 5 (Record type inhabitants).We define the inhabitants of a record type R, written inh(R), as: We define set(λx .P, R) as the equivalent set of all records of type R satisfying a predicate P. The definition of set(λx .P, R) is used to show that our implementation is sound.Definition 6 (Predicate sets).We define the set representation of predicate λx .P over R, written set(λx .P, R), as: It is often helpful to consider only a subset of fields in a record.Definition 7 (Record restriction).Given a record r = (ℓ 1 = V 1 , . . ., ℓ m = V m , . . ., ℓ n = V n ), we define the record restriction of r to ℓ 1 , . . ., ℓ m , written r [ℓ 1 , . . ., ℓ m ], as (ℓ Let Π, Π ′ range over homogeneous sets of records, such as the set representation of predicates.It is also convenient to be able to consider a set where each constituent record is restricted to a given set of fields.Definition 8 (Predicate set restriction).We define the restriction of set Π to It is also useful to be able to consider the natural join of two sets of records.Definition 9 (Set join).Suppose R = R 1 ⊕ R 2 , and suppose Π contains records of type R 1 and Π ′ contains records of type R 2 .
We define the set join of Π and Π ′ , written Π Π ′ , as: To check the safety of a drop lens, we need to show that the predicate does not impose any dependency between the value of the dropped field and any other field.We formalise this constraint by defining the notion of a lossless join decomposition (LJD).
Definition 10 (Lossless join decomposition).A lossless join decomposition of two record types R 1 and R 2 with respect to a predicate P of type x : R 1 ⊕ R 2 ⊢ P : bool, written LJD R 1 , R 2 (λx .P), means that for all r 1 , r 2 ∈ inh(R 1 ) and s 1 , s 2 ∈ inh(R 2 ), it is the case that: Showing LJD R, R ′ (λx .P) is NP-hard and could be undecidable, depending on the atomic formulae available in the predicates.Since a predicate that satisfies LJD R, R ′ (λx .P) can be rewritten as a conjunction of predicates which depend only on either R or R ′ , we can, however, define a sound but incomplete syntactic approximation LJD † R, R ′ (λx .P).

Proof. By induction on the derivation of LJD †
R, R ′ (λx .P). □ Updates to the view will use the default value V in place of the given column.Therefore, in addition to showing that the predicate does not impose any dependency between the value of the dropped field and the other fields, we must show that the default value V of the dropped column does not violate the predicate.Given the set representation of a predicate set(λx .P, R), we must show that We define a property DV R,R ′ (λx .P) r and show that it is sound with respect to the set semantics.Definition 11.Given a predicate λx .P and record types R and R ′ such that LJD R, R ′ (λx .P) and r ∈ inh(R ′ ), we write DV R,R ′ (λx .P) r when set(λx .P, R ⊕ R ′ ) is not empty and there exists an s ∈ inh(R) such that sat(λx .P, r ⊗ s).As with the definition of LJD R, R ′ (λx .P), determining if DV R, R ′ (λx .P) r holds in the general case is a difficult problem.To simplify this problem we introduce an incomplete set of inference rules to determine DV † R,R ′ (λx .P) r , which covers the same set of predicates as the LJD † R, R ′ (λx .P) rule.

Proof. By expansion of the definitions of DV
DV † -1 x : R ⊢ P : D x : R ′ ⊢ P : D sat(λx .P, r ) Lemma 4. Given a predicate λx .P such that set(λx .P, R ⊕ R ′ ) is not empty and record r such that • ⊢ r : R, it follows that DV † R, R ′ (λx .P) r implies DV R, R ′ (λx .P) r .
Proof.By induction on the derivation of DV † R, R ′ (λx .P) r .For further details see Appendix C.3.4.□ Note that the soundness proof for DV † R,R ′ (λx .P) r requires that set(λx .P, R ⊕ R ′ ) is not empty.This is problematic in theory, because it requires us to show that the predicate is satisfiable.According to Bohannon et al. [5], a drop lens on a lens with predicate that is false does not typecheck.In practice however, this lens is well behaved as it returns an empty view and only takes an empty view.The lens would therefore be useless, but not incorrect.
With the preliminaries in place, we can present the typing rule for the drop lens.The term drop ℓ ′ determined by ( − → ℓ , V ) from M constructs a lens which removes column ℓ ′ from view M, given that the functional dependencies of the view ensure that ℓ ′ is determined by the columns − → ℓ .The typing rule is as follows: The clause F ≡ G∪{ − → ℓ →ℓ ′ } checks that the functional dependencies of the underlying lens M imply that − → ℓ do indeed determine ℓ ′ ; that − → ℓ are contained in the domain of the record type R of underlying lens M; that V has the same type as the dropped field; that R and (ℓ ′ : A) define a lossless join decomposition with respect to the lens predicate; and finally that V is a suitable default value with respect to the predicate.
The resulting type lens of (Σ, R, λx .P[V /x .ℓ′ ], G) contains the updated record type without the dropped column, and the updated predicate with the default variable in place of all references to the dropped column.
Lens Get.Finally we define typing rules for making use of relational lenses.Since Links is not dependently typed, we discard the constraints which apply to the view, and specify that calling get returns a set of records which all have the type R.

T-Get
Γ ⊢ M : lens of (Σ, R, λx .P, F) Γ ⊢ get M : record set of R Lens Put.Just as with T-Get, we have no way of statically ensuring that the input satisfies P and F, so we only statically check that the updated view is a set of records matching type R, deferring the checks to ensure that the set of records satisfies F and P to runtime.
To ensure that the constraint P applies to each record r in a view, runtime checks ensure that sat(λx .P, r ).Functional dependency constraints can be checked by projecting the set of records down to each functional dependency and determining if any two records violate a functional dependency.

Correctness
Bohannon et al. [5] prove that lenses satisfying correctness conditions are well-behaved (i.e., satisfy GetPut and PutGet, and therefore safely compose).Their typing rules are not in a form amenable to implementation, since predicates are defined as abstract sets; lenses are composed using a sequential composition operator rather than allowing arbitrarily-nested lenses as one would in a functional language; and there is no distinction between a relation and a lens on a relation.Nevertheless, we must show that our typing rules also guarantee well-behavedness.Our approach is to define a type-preserving translation from our functional-style lenses into the sequentialstyle lenses defined by Bohannon et al. [5].
Figure 6 shows the grammar of sequential-style lenses.We let Π range over set-style predicates; S,T range over relation names; Σ, ∆ range over schemas (i. Figure 6 also shows the translation from functional lenses to sequential-style lenses, which involves flattening functional lenses by introducing intermediate relations with fresh table names.The translation function M = Σ/I /S states that functional lens M depends on tables Σ, translates to sequential lens I , and produces a view with name S.
As an example of a typing rule for sequential-style lenses, consider the typing rule for the select lens: The sequential lens typing judgement has the shape I ∈ Σ ⇔ ∆, meaning that I is a lens transforming the source schema Σ into the view schema ∆.In the case of the select lens, given a predicate set Π, the typing rule enforces the invariant that the source relation S has sort ( − → ℓ , Π ′ , F); that the functional dependencies F are in tree form; that Π ′ ignores the outputs of F; and assigns the view T the sort We can now state our soundness theorem, stating that once translated, lenses typeable in our system are typeable using the original rules proposed by Bohannon et al. [5], and can use the incremental semantics described by Horn et al. [19].Proof.By induction on the derivation of Γ ⊢ M : lens of (Σ, R, λx .P, F); see Appendix C. □

Typechecking Dynamic Predicates
If a dynamic predicate is used in any lens combinator, the same checks are performed, but checking of predicates must be deferred to runtime.In this case, we require the programmer to acknowledge that the lens construction may fail at run-time.We introduce a special lens, the check lens, which the user must incorporate prior to using the lens in a get or put operation.

CASE STUDY: CURATED SCIENTIFIC DATABASES
In this section, we illustrate the use of relational lenses in the setting of a larger Links application: part of the curation interface for a scientific database.Scientific databases collect information about a particular topic, and are curated by subject matter experts who manually enter and update entries.
The IUPHAR/BPS Guide to Pharmacology (GtoPdb) [22] is a curated scientific database which collects information on pharmacological targets, such as receptors and enzymes, and ligands such as pharmaceuticals which act upon targets.GtoPdb consists of a PostgreSQL database, a Java/JSP web application frontend to the database, and a Java GUI application used for data curation.
In parallel work [15], we have implemented a workalike frontend application in Links, using the Links LINQ functionality.In this section, we demonstrate how we are beginning to use relational lenses for the curation interface, and show how relational lenses are useful in tandem with the Model-View-Update (MVU) paradigm pioneered by the Elm programming language [1].

Disease Curation Interface
One section of GtoPdb collects information on diseases, such as the disease name, description, crossreferences to other databases, and relevant drugs and targets.In this section, we describe a curation interface for diseases, where all interaction with the database occurs using relational lenses.
Figure 7a shows the official Java curation interface.The main data entries edited using the curation interface are the name and description of the disease; the crossreferences for the disease which refer to external databases; and the synonyms for a disease.As an example, a synonym for "allergic rhinitis" is "hayfever".Note that this curation interface does not edit ligand or target information; curation of ligand-to-disease and target-to-disease links are handled by the ligand and target curation interfaces respectively.

Links Reimplementation
Figure 7b shows the curation interface as a Links web application.In the original implementation of Links [8], requests invoked Links as a CGI script.Modern Links web applications execute as follows: (1) A Links application is executed, which registers URLs against page generation functions, and starts the webserver (2) A request is made to a registered URL, and the server runs the corresponding page generation function (3) The page generation function may spawn server processes, make database queries, and register processes to run on the client, before returning HTML to the client (4) The client application spawns any client processes, and renders the HTML (5) Client processes can communicate with server processes over a WebSocket connection.

4.2.1
Architecture.The disease curation interface consists of a persistent server process, and a client process which is spawned by the Links MVU library.Upon page creation, the application creates lenses to the underlying tables: the lenses retrieve data from, and propagate changes to, the database.Since lenses only exist on the server and cannot be serialised to the client, we spawn a process which awaits a message from the client with the updated data.

Tables and Lenses.
We begin by defining the records we need, and handles to the underlying database and its tables.
First, we define a database handle, db, to the gtopdb database.
var db = database "gtopdb"; Next, we define type aliases for the types of records in each table.The disease curation interface uses tables describing four entity types: disease data (DiseaseData), metadata about external databases (ExternalDatabase), links from diseases to external databases (DatabaseLink), and disease synonyms (Synonym).(Note that "prefix" appears in quotes as prefix is a Links keyword).Next, we can define handles to each database table.The with clause specifies a record type denoting the column name and type of each attribute in the table, and the tablekeys clause specifies the primary keys (i.e., sets of attributes which uniquely identify a row in the database) for each table.We show only the definition of diseaseTable; the definitions for databaseTable, dbLinkTable, and synonymTable are similar.
The ID of the disease to edit (diseaseID) is provided as a GET parameter to the page, and thus we need a dynamic predicate as not all information is known statically.With the description of the entities and tables defined, we can describe the relational lenses over the tables.We work in a function scope where diseaseID has been extracted from the GET parameters.We create a lens over a table using the lens keyword, writing default when we do not need to specify functional dependencies.The dbLens lens specifies a functional dependency from database _ id to each of the other columns, as knowledge of this dependency is required when constructing a join lens.
We need not filter the databaseTable table since we wish to display all external databases.The diseaseLens, dbLinksLens, and synonymsLens lenses make use of the select lens combinator, allowing us to consider only the records relevant to the given diseaseID.Note that each entity has a disease _ id field: as a result, we can make use of Links' row typing system [21] to define a single predicate, diseaseFilter, for each select lens using row polymorphism.
The dbLinksJoinLens lens joins the external database links with the data about each external database by using the join lens combinator, stating that if a record is deleted from the view, then it should be deleted from the dbLinkTable rather than the dbLens table.Joining these two tables is only possible because database _ id uniquely determines each column of the databaseTable table; as the lens uses a dynamic predicate, this property is checked at runtime.

Model.
In implementing the case study, we make use of the Model-View-Update (MVU) paradigm, pioneered by the Elm programming language [1].MVU is similar to the Model-View-Controller design pattern in that it splits the state of the system from the rendering logic.In contrast to MVC, MVU relies on explicit message passing to update the model.The key interplay between MVU and relational lenses is that MVU allows the model to be directly modified in memory, and relational lenses allow the changes in the model to be directly propagated to the database without writing any marshalling or query construction code.The model (Model) contains all definitions retrieved from the database (DiseaseInfo), as well as the current value of the various form components for adding database links (selectedDatabaseID and accessionID) and synonyms (newSynonym).Finally, the model contains a function submitDisease which commits the information to the database.Note that the {}~> function arrow denotes a function which cannot be run on the database, and does not perform any effects.The Model type is wrapped in a Maybe constructor to handle the case where the application tries to curate a nonexistent disease.
The model is rendered to the page using a view function which takes a model and produces some HTML to display.Interaction with the page produces messages which cause changes to the model.Finally, submission of the form causes the submitDisease function to be executed, which in turn sends a Submit message to the server to propagate the changes to the database using the lenses.

Discussion
In this section, we have described part of the curation interface for a scientific database.Our application is a tierless web application with the client written using the Model-View-Update architecture.
Relational lenses allow seamless integration between all three layers of the application.Lenses with dynamic predicates allow us to retrieve the relevant data from the database; the data is used as part of a model which is changed directly as a result of interaction with the web page; and the updated data entries are committed directly to the database.At no point does a user need to write a query: every interaction with the database uses only lens primitives.
The primary limitation of the implementation at present is that it does not currently support auto-incrementing primary keys, which are commonly used in relational databases.

RELATED WORK
Edit Lenses.Edit lenses are a form of bidirectional transformation where, rather than translating directly between one data structure and another, the changes to a data structure are tracked and then translated into changes to the other data structure [17].They can be particularly useful in the case of symmetric lenses in situations where neither of the data structures contain all of the data, and thus none of the sources can be considered the 'source' [16].Changes could be described by insert, update and delete commands, and will usually result in similar insert, update or deletion commands for the other data structure.
Relational lenses are generally not considered edit lenses, as they directly translate the entire view to an updated source when performing get.Incremental Relational Lenses on the other hand take the updated view and compute a delta which is then translated into a delta to the source tables [19].
The language integration aspect of relational lenses is not dependent on the semantics used to perform relational updates.Instead it only relies on all of the relational lens typing rules in §3.3 to be satisfied; in this case, both the incremental and the non-incremental relational put semantics are guaranteed to be well-behaved.
Put-based Lenses.Bidirectional lenses are often defined in a form that corresponds to the forward (get) direction and the reverse direction.A common issue with this approach is that a get function might correspond to several well-behaved put functions, as illustrated by drop and join relational lenses.As such, defining a bidirectional transformation by only specifying the forward direction is generally not sufficient.An alternative approach recently used is to rather have the programmer instead only specify the put semantics, which then uniquely define the get semantics [13,20].
A putback approach to bidirectional transformations has been recently proposed by Asano et al. [3] for relational data.Asano et al. define a language which allows the specification of update queries, for which the forward query can automatically be derived.They support splitting views vertically for defining behaviour specific to columns and horizontally for behaviour specific to rows.For each of the different sections of the view they can then define the update behaviour, which can be simple checks or actual update semantics.
Cross-tier web programming.SMLServer [12] was among the first functional frameworks to allow interaction with a relational database.Ur/Web [6] is a cross-tier web programming language which supports a statically-typed SQL DSL, along with atomic transactions and functional combinators on results.Neither framework supports language-integrated views.
Hop.js [27] builds on the Hop programming language [26] and allows cross-tier web programming in JavaScript.Eliom [25] is a cross-tier functional programming framework building on top of the OCaml programming language.Eliom programs can explicitly assign locations to functions and variables.ScalaLoci [28] is a Scala framework for cross-tier application programming.A key concept behind ScalaLoci is that data transfer between tiers uses the reactive programming paradigm.Haste.App [11] is a Haskell EDSL allowing web applications to be written directly in Haskell.Since these are embedded DSLs or frameworks, it becomes possible to use the database functionality provided by other libraries, but are not aware of any work providing relational lenses as a library in any programming language.
Task-oriented programming (TOP) [24] is a high-level paradigm centred the idea of a task, which can be thought of as a unit of work with an observable value.TOP is implemented in the iTask system [23].An editor is a task which interacts with a user.Editlets [10] are editors with customisable user interfaces, which can allow multiple users to interact with shared data sources.Much like incremental relational lenses [19], Editlets communicate changes in the data as opposed to the entire data source, however the user must specify this behaviour manually.

CONCLUSION
Relational lenses allow updatable views of database tables.Previous work has concentrated on the semantics of relational lenses, but has not proposed a concrete language design.As a result, previous implementations imposed severe limitations on predicates, and provided limited checking of the correctness of lens composition.
In this paper, we have presented the first full integration of relational lenses in a functional programming language, by extending the Links programming language.Building on the approach of Cooper [7], we use normalisation rules to rewrite functional expressions into a form amenable to compilation to SQL and for typechecking lenses.Furthermore, we have adapted the existing typing rules for relational lenses to the setting of a functional programming language and proved that our adapted rules are sound.
Previous implementations have provided only small example applications.To demonstrate the use of relational lenses, we have implemented part of the curation interface for a scientific database as a cross-tier web application, and shown how relational lenses can be used in tandem with the Model-View-Update architecture for frontend web development.
As future work, we plan to explore integrating relational lenses with auto-incrementing table fields.

A SUPPLEMENTARY MATERIAL A.1 Functional Dependencies
Figure 8 defines the set of functional dependencies which can be derived from a set of functional dependencies F. Proof.As the rewrite rules can be applied anywhere in a term, it follows that if we cannot apply a normalisation rule to a term, then we cannot apply a normalisation rule to any of its subterms.Given that M is in normal form and has function type, there are the following possibilities:

Functional Dependencies
• M = x, which is not possible since the only variable in the typing environment is x, and R is not a function type • M = c, which is not possible since constants only have base types, not function types • M = λx .M.In this case, we could apply the first normalisation rule, which would be a contradiction.
• M = x .ℓ,which is not possible since R only contains fields with base types, not function types In this case, we could apply the fifth normalisation rule, which would be a contradiction.
• ⊙{ − → V }, which is not possible since the result of an operator must have base type.
Thus, a term x : R ⊢ M N cannot be in normal form.

Case T-Project
Assumption: By the IH, we have that M is in normal form.We now perform case analysis on M, giving us the following possibilities for terms in normal form which can have record type: • M = x: We have that x .ℓwhich is in normal form.
x : R ⊢ P assumption Proof.

Figure 3 :
Figure 3: Syntax and typing rules for predicate language

Proposition 2 (
Normal forms).If x : R ⊢ M : A and M ⇝ * N ̸ ⇝, then N is in normal form.Proof.By induction on the derivation of x : R ⊢ M : A. The details can be found in Appendix B. □ As a corollary, by considering only terms with type bool, we can show that static predicates are in predicate normal form.Corollary 3 (Predicate normal form).If x : R ⊢ M : bool and M ⇝ * N ̸ ⇝, then N is in predicate normal form.

Figure 4
also introduces a standard big-step evaluation relation M ⇓ V , which states that term M evaluates to a value V .We use the notation ⊙ { − → V } to describe the denotation of operation ⊙ applied to arguments − → V : for example, +{5, 10} = 15.The semantics enjoys a standard type soundness property.Proposition 4 (Type Soundness).If • ⊢ M : A, then there exists some V such that M ⇓ V and • ⊢ V : A.
Terms typeable by rules T-Var, T-Const, and T-Abs are already in normal form.Rules T-Record and T-Op follow directly from the induction hypothesis.The remainder of the cases follow.Case T-App Assumption: x : R ⊢ M : A → B x : R ⊢ N : A x : R ⊢ M N : B By the induction hypothesis, we have that M and N are in normal form.

V 3 :
Impossible, since it would be possible to reduce by the sixth reduction rule Case T-If Assumption: Γ ⊢ L : bool Γ ⊢ M : A Γ ⊢ N : A Γ ⊢ if L then M else N : A Immediate by the induction hypothesis on all three subterms; normalisation rules 3 and 4 serve only as an optimisation.□ C PROOFS FOR SECTION 2 C.1 Predicate Lemmas
− → ℓ ′ are uniquely determined by − → ℓ 2 Join Lens.The join lens joins two underlying views.A join lens has limitations on the functional dependencies of the underlying tables.Let us assume that there is another table reviews which contains album reviews by users.The table has the functional dependency user album -> review1.The reviews table is joined with the tracks table to produce the lens l 1 .Suppose the user tries to delete the first "90sclassics" record: 3.3.3DropLens.The drop lens allows a more fine-grained notion of relational projection, allowing us to remove a column from a view.Note that this is not to be confused with the SQL DROP statement, which deletes a table.Let us assume we define the lens l 1 as a select lens with predicate year > 1990 ∨ rating > 4.We can then define the lens l 2 as l 1 , but dropping column year determined by track to yield the table: We will need to join the ExternalDatabase and DatabaseLink tables in order to render the database name of each external database link.It is therefore useful to define a type synonym for the record type resulting from the join: typename JoinedDatabaseLink = (disease _ id: Int, database _ id: Int, placeholder: String, name: String, url: String, specialist: Bool, "prefix": String); 1 )] equals r 1 by definition of • ⊗ •.Conversely the same can be shown for r 2 .