Download QIR: Query Intermediate Representation and Type System and more Lecture notes Programming Languages in PDF only on Docsity!
Thèse de doctorat
NNT : 2019SACLS
Au-delà des frontières entre langages
de programmation et bases de données
Thèse de doctorat de l'Université Paris-Saclay préparée à Université Paris-Sud
Ecole doctorale n◦580 Sciences et Technologies de l'Information et de la Communication (STIC) Spécialité de doctorat: Informatique
Thèse présentée et soutenue à Orsay, le 13/09/2019, par
Julien Lopez
Composition du Jury :
James Cheney Reader, Université d'Édimbourg Rapporteur Emmanuel Chailloux Professeur, Sorbonne Université (UMR 7606) Rapporteur Alan Schmitt Directeur de recherche, INRIA Rennes (UMR 6074) Examinateur Jérôme Siméon Chief Scientist, Clause Inc. Examinateur Sarah Cohen Boulakia Professeur, Université Paris-Sud (UMR 8623) Présidente du jury Véronique Benzaken Professeur, Université Paris-Sud (UMR 8623) Directeur de thèse Kim Nguyen Maitre de Conférences, Université Paris-Sud (UMR
Co-encadrant de thèse Giuseppe Castagna Directeur de Recherche, CNRS et Université de Paris (UMR 8243) Invité
ii
j'ai renforcé mon OCaml en enseignant notamment comment réaliser un jeu vidéo avec des match, et qui m'a appris le model checking. Merci à Guillaume Melquiond de m'avoir appris à faire du C++ correctement (sans *), et à Thibaut Balabonski grâce à qui j'ai dépoussiéré mes connaissances en compilation, tout cela me sera utile très prochainement. Merci à Jean-Christophe Filliâtre pour m'avoir partagé ses connaissances sur OCaml, et pour m'avoir prêté son exemplaire de Sixty Million Frenchmen Can't Be Wrong. Merci à Frédéric Voisin pour avoir trouvé le code d'accès à la salle de reprographie pour un thésard en panique et pour les blagues du vendredi (du lundi au vendredi) au coin café. Merci à Sylvie Boldo pour m'avoir fait con ance pour la review d'un article au JFLA, pour son aide précieuse avec Véronique sur le recrutement académique, et sur le fonctionnement de la machine magique à faire des posters. Merci à Hai et Stefania pour m'avoir montré à l'avance dans quel état je serai en dernière année, à Robin pour avoir écouté mes tirades inintéressantes sur le speedrun de Mario 64, à Albin pour le vin à l'orange, à Bruno pour la promenade en vélo, à Alexandrina pour m'avoir fait rire quand j'en avais bien besoin et pour son amitié. Ah, et merci à Mattias pour les pauses, m'avoir presque donné envie de jouer à un MOBA, et pour m'avoir appris la quantité de crème pour des pâtes à la carbonara. Merci à mes vieux amis, Pierre-Alain, Pierre-Alain, Frédéric, Romain qui m'ont soutenu malgré la distance, et Bertrand, Solenne, Thibaut et Pierre-Jean pour nos beuveries et parties de Borderlands. Un grand merci à ma famille, tout particulièrement mes parents, ma grand- mère, mon frérot, et ma petite s÷ur. Sans votre amour et votre soutien, je ne serais jamais allé aussi loin. En n, merci à toi Jenny. Merci pour ton soutien, tes corrections sur mon anglais beaucoup trop français, et pour ces merveilleuses années que nous avons passées ensemble.
iv
v
Abstract
Several classes of solutions allow programming languages to express queries: spe- ci c APIs such as JDBC, Object-Relational Mappings (ORMs) such as Hibernate, and language-integrated query frameworks such as Microsoft's LINQ. However, most of these solutions do not allow for e cient cross-databases queries, and none allow the use of complex application logic from the programming language in queries.
This thesis studies the design of a new language-integrated query framework called BOLDR that allows the evaluation in databases of queries written in general-purpose programming languages containing application logic, and tar- geting several databases following dierent data models. In this framework, ap- plication queries are translated to an intermediate representation. Then, they are typed with a type system extensible by databases in order to detect which database language each subexpression should be translated to. This type system also allows us to detect a class of errors before execution. Next, they are rewritten in order to avoid query avalanches and make the most out of database optimiza- tions. Finally, queries are sent for evaluation to the corresponding databases and the results are converted back to the application. Our experiments show that the techniques we implemented are applicable to real-world database applica- tions, successfully handling a variety of language-integrated queries with good performances.
Résumé
Plusieurs classes de solutions permettent d'exprimer des requêtes dans des langages de programmation: les interfaces spéci ques telles que JDBC, les map- pings objet-relationnel ou object-relational mapping en anglais (ORMs) comme Hibernate, et les frameworks de requêtes intégrées au langage comme le frame- work LINQ de Microsoft. Cependant, la plupart de ces solutions ne permettent
vii
pas d'écrire des requêtes visant plusieurs bases de données en même temps, et au- cune ne permet l'utilisation de logique d'application complexe dans des requêtes aux bases de données. Cette thèse présente un nouveau framework de requêtes intégrées au langage nommé BOLDR qui permet d'écrire des requêtes dans des langages de program- mation généralistes et qui contiennent de la logique d'application, et de les évaluer dans des bases de données hétérogènes. Dans ce framework, les requêtes d'une application sont traduites vers une représentation intermédiaire de requêtes. Puis, elles sont typées en utilisant un système de type extensible par les bases de don- nées pour détecter dans quel langage de données chaque sous-expression doit être traduite. Cette phase de typage permet également de détecter certaines erreurs avant l'exécution. Ensuite, les requêtes sont réécrites pour éviter le phénomène "d'avalanche de requêtes" et pour pro ter au maximum des capacités d'optimisa- tion des bases de données. En n, les requêtes sont envoyées aux bases de données ciblées pour évaluation et les résultats obtenus sont convertis dans le langage de programmation de l'application. Nos expériences montrent que les techniques implémentées dans ce framework sont applicables pour de véritables applications centrées données, et permettent de gérer e cacement un vaste champ de requêtes intégrées à des langages de programmation généralistes.
Note : A n d'en assurer une plus large diusion, et en accord avec l'école docto- rale STIC Paris-Saclay, cette thèse est rédigée en anglais. Pour un résumé étendu des travaux rédigé en français, voir l'Annexe A page 163.
viii
List of symbols
≡ Syntactical equivalence.................... 23 = Equality................................... 23 ∅ Empty set................................. 23 ⊆ Subset inclusion........................... 23 ∪, ∩, \ Union, intersection and dierence of sets... 23 dom(f ) Domain of a function f.................... 23 img(f ) Image of a function f...................... 23 i..j Set of integers {i, i + 1,... , j}.............. 23 _ Placeholder................................ 23 Γ Typing environment....................... 24 γ Evaluation environment.................... 24 σ Type substitution.......................... 24 σ 1 ◦ σ 2 Composition of type substitutions.......... 24 (EH, IH, VH, (^) →H) Host language............................. 24 (ED, VD, (^) →D) Database language......................... 25 (A, c) Inference rule.............................. 26 q QIR expression............................ 28 C(q) Children of a QIR expression.............. 32 T(q) Databases targeted by a QIR expression... 32 → Basic QIR semantics....................... 32 (H
EXP,
VALH,H^
VAL) Host language driver....................... 36 (
EXPD,
VALD,D^
VAL) Database driver............................ 36 VQIR QIR values................................ 32 Extended QIR semantics................... 37 MEM MEM database language................... 40 ↪→ Normalization relation..................... 45 M (^) D(q) Measure of a QIR expression by a database 47 M (q) Generic measure of a QIR expression....... 49 Reds (q) Possible reductions of a QIR expression.... 51 B Basic QIR type............................ 56 T QIR type.................................. 56 dom(R) Domain of a QIR record type.............. 56
xiii
q
- 1 Introduction
- 1.1 Context
- 1.2 SQL
- 1.2.1 Relational algebra
- 1.2.2 Expressing queries in SQL
- 1.3 Application programming languages
- 1.4 Sending queries from application languages
- 1.4.1 JDBC
- 1.4.2 ORMs
- 1.4.3 LINQ
- 1.4.4 Apache Calcite
- 1.4.5 Other interfaces
- 1.5 A new solution: BOLDR
- 1.5.1 Features
- 1.5.2 Detailed description
- 1.5.3 Implementation
- 1.6 Contributions
- 1.6.1 Query Intermediate Representation
- 1.6.2 QIR type system
- 1.6.3 QIR type inference
- 1.6.4 Type-oriented evaluation
- 1.6.5 Implementation and experiments
- 2 De nitions
- 2.1 Basic notations
- 2.2 Languages
- 2.3 Inference systems
- 3 Query Intermediate Representation
- 3.1 Syntax
- 3.2 Basic semantics
- 3.3 Extended semantics
- 3.4 A default database language: MEM
- 3.5 QIR normalization
- 3.5.1 Motivation
- 3.5.2 Reduction relation for the normalization
- 3.5.3 A measure for good queries
- 3.5.4 Generic measure
- 3.5.5 Heuristic-based normalization
- 4 QIR type system
- 4.1 QIR types
- 4.2 QIR type systems
- 4.3 Type safety
- 4.3.1 Progress and preservation of types
- 4.3.2 Strong normalization
- 4.4 Speci c type system for SQL
- 5 QIR type inference
- 5.1 Typing algorithms
- 5.2 Speci c typing algorithm for MEM
- 5.3 Constraint resolution
- 5.4 Speci c typing algorithm for SQL
- 6 Type-oriented evaluation
- 6.1 Translation into database languages
- 6.1.1 Speci c and generic translations
- 6.1.2 A speci c translation for SQL
- 6.2 Type-safe SQL translation
- 6.3 Extension to scalar subqueries for SQL
- 6.4 Type-oriented normalization
- 7 Implementation and experiments
- 7.1 Translation from a host language to QIR
- 7.2 Tru e
- 7.3 Implementation
- 7.3.1 QIR
- 7.3.2 Interface to FastR
- 7.3.3 Host language expressions in databases
- 7.4 Experiments
- 8 Conclusion
- 8.1 Related work
- 8.2 Conclusion
- 8.3 Future work
- Appendices
- A Résumé étendu
- A.1 Contexte
- A.2 SQL
- A.2.1 Algèbre relationnelle
- A.2.2 Exprimer des requêtes en SQL
- A.3 Langages de programmation applicatifs
- A.4 Requêtes depuis des langages d'application
- A.4.1 JDBC
- A.4.2 ORMs
- A.4.3 LINQ
- A.4.4 Apache Calcite
- A.4.5 Autres interfaces
- A.5 Une nouvelle solution : BOLDR
- A.5.1 Fonctionnalités
- A.5.2 Description détaillée
- A.5.3 Implémentation
- A.6 Contributions
- A.6.1 Représentation intermédiaire de requêtes (QIR)
- A.6.2 Système de types pour QIR
- A.6.3 Inférence de types pour QIR
- A.6.4 Évaluation orientée par les types
- A.6.5 Implémentation et expériences
- B Full proofs
- Bibliography
- Subtyping relation
- ≺ Strict subtype
- Γ `D q : T Speci c type system
- Γ ` q : T, D Generic type system
- j ⊆ j′ Specialization of a type inference judgement
- (A, c) ⊆ (A′, c′) Specialization of a type inference rule
- T Algorithmic QIR type
- C, T = T′ Set of type constraints, type constraint
- k Kind
- K, α =k T Set of kind constraints, kind constraint
- Γ `AD q : T, (C, K) Speci c typing algorithm
- Γ `A q : T, (C, K), D Generic typing algorithm
- (K, σ) Kinded substitution
- q e Generic translation
- ↪→ Type-oriented normalization ` - Translation from R to QIR R
e-commerce application
Key-value database Document database RDBMS
User session Shopping cart
User accounts Logs
Inventory Financial data
Figure 1.1 Example of an application using dierent types of databases
of the application. Figure 1.1 shows an example of such application.
This thesis is a study aiming to create a solution that allows application developers to write safe and e cient queries targeting databases without forcing them to become experts in the data models and query languages of their target databases.
In this introduction, we rst give an overview of database query languages, programming languages used in the development of applications, and the existing solutions to interface these two worlds and the problems encountered in the pro- cess. Then we describe a new solution in the form of a new language-integrated query framework called BOLDR.
1.2 SQL
SQL (Structured Query Language) is the most popular query language. It is a domain-speci c language based on relational algebra.
id name salary teamid 1 Lily Pond 5200 2 2 Daniel Rogers 4700 1 3 Olivia Sinclair 6000 1 (a) Table Employee
teamid teamname bonus 1 R&D 500 2 Sales 600 (b) Table Team
Figure 1.2 An example of data organized as tables
1.2.1 Relational algebra
Relational algebra, rst designed by Edgar F. Codd [Cod70], de nes operations on data represented as a set of n-tuples where every element of the tuple corresponds to an attribute denoted by a name. Relational databases call these constructions tables, composed of lines and columns. Figure 1.2 gives an example of tables.
Relational algebra is the basis of most database query languages [AHV95]. The most common operations of relational algebra are the projection, which re- stricts the tuples to a set of attributes; the selection (or lter), which keeps only the tuples that satisfy a condition; and the join, which returns the set of all com- binations of tuples from two tables that are equal on their common attributes. Figure 1.3 shows examples of applications of those operations on tables. Fig- ure 1.3a shows the projection of table Employee on attributes name and salary, Figure 1.3b shows the selection in the table Employee of the tuples for which the value of the attribute salary is greater than 5000, and Figure 1.3c shows the result of the join between Employee and Team.
name salary Lily Pond 5200 Daniel Rogers 4700 Olivia Sinclair 6000 (a) Projection on Employee
id name salary teamid 1 Lily Pond 5200 2 3 Olivia Sinclair 6000 1 (b) Selection on Employee
id name salary teamname bonus 1 Lily Pond 5200 Sales 600 2 Daniel Rogers 4700 Sales 600 3 Olivia Sinclair 6000 R&D 500 (c) Join between Employee and T eam
Figure 1.3 Example of applications of relational algebra
which is then bound to the name t using an alias, and this name is then used in the WHERE clause to refer to the table. The simple syntax of SQL is one of the reasons why it is so popular, and the most commonly used query language. Most databases support SQL, even those that do not have a data model directly suited for relational algebra. Therefore, SQL is an unavoidable database language to study for solutions aiming to allow programmers to send queries to databases.
1.3 Application programming languages
The majority of data-driven applications are written in imperative programming languages. Python is used in particular for Web applications and for machine learning. It is a very popular programming language because of its simple syn- tax and numerous eld-speci c libraries, such as for machine learning, general algorithms, and statistics. JavaScript is widely used for Web applications. R is a language natively designed for statistical applications and data analysis. Java is a widely used general-purpose programming language with numerous libraries for Web development, machine learning, text processing, and more. Contrary to declarative programming in languages like SQL, imperative pro- gramming involves describing step-by-step the control ow of a program, which requires programmers in these languages to describe how to get to the desired result. For instance, ltering a table in an imperative language would typically be written as such in Python:
filteredTable = [] for employee in employees: if (employee['salary'] > 5000): filteredTable.append(employee)
However, modern programming languages have made an eort to support some aspects of functional programming, making data-oriented applications less tech- nically detailed. For instance, we can write the example above in Python using list comprehensions [Kuh11]:
[employee for employee in employees if employee['salary'] > 5000] Application programs can use imperative and functional features, and usu- ally contain a mix of both. Even so, most application languages are originally imperative, and in particular are more e cient at evaluating imperative code. Additionally, most application languages (Python, R, Ruby, JavaScript,... ) are dynamically typed, meaning that the type-safety of a program is checked during its execution. For instance, a program such as
(function (x) { return x; })(2, 3)
which applies the identity function to two arguments would be recognized as an error during its execution (or should, but JavaScript just ignores the second argument in this case... ).
1.4 Sending queries from application languages
As stated earlier, most applications are written in general-purpose programming languages. These languages do not have native ways to query databases, and the vast majority of them are imperative languages, so their syntax is very dierent from those of query languages. Various solutions have been designed to enable programmers to send queries to databases from their programming languages. In this section, we take a look at some existing solutions, and discuss their pros and cons.
1.4.1 JDBC
Java Database Connectivity (JDBC) [Cor16] is an application programming in- terface (API) which provides data access from the Java programming language to data sources, including databases. Example 1.1 is an example of use of JDBC in a Java program to retrieve data from a database.
Example 1.1. final Connection conn = ... final Statement stmt = conn.createStatement(); final String query = "SELECT id, name, salary FROM employee WHERE salary > 2500"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { System.out.println(rs.getInt("ID") + " "
- rs.getString("NAME") + " " + rs.getFloat("SALARY")); }
In this example, the program queries the identi er, name, and salary of em- ployees which salary is greater than 2500 from a table employee stored in a database. In JDBC, the user must rst create a Connection object using the correct credentials to access the targeted database, then create a Statement object from the connection object to send a query. The query itself is a string in the query language of the database (SQL in the example). The results are represented
