Roland Balk - SQLento: Database programming made easier

author:Roland Balk
title:SQLento: Database programming made easier
keywords:Relational Databases, Transactions, Programming Languages
topics:Languages, Algorithms and Data Structures
committee:L. Wevers MSc (1st supervisor)
prof.dr. M. Huisman
Maurice van Keulen
graduation date:12 January 2016

FMT Master Project

Abstract

Relational databases take care of many problems, like storing digital information on a physical medium and providing search algorithms. However, programming relational databases can be improved a lot. One of the most discussed problems is the linguistic mismatch between the general purpose programming language (GPPL) and the relational database programming language, also known as the impedance mismatch. Since there exist a mismatch, we have explored concerns of programming relational databases from the GPPL side. For example, programming languages like Java, PHP and C# provide solutions to write SQL code in strings. Writing database code in strings increases the vulnerability for SQL injections. These concerns may not look crucial to solve and may already have a solution. But we don't expect that all found concerns have been solved by one solution. Therefore we propose a new database programming language, called SQLento, to solve these interfacing concerns.

These concerns have not been unnoticed and solutions have been proposed and developed to solve them. Ten different solutions have been summarized and compared to gain insights in the used concepts. For example, Microsoft LINQ embeds the declarative database language within the GPPL to prevent type and security concerns. But embedding the language results in a dependency with the general purpose programming language and its expressiveness. After comparing each solution we conclude that none of the solutions solve all concerns. Combining multiple solution does solve most of the concerns, but also increases the development complexity. We also realized that not all concerns can be solved by providing a new database programming language for relational databases. Thus we decided to design an architecture as well.

We propose an architectural solution to place the new database language, called SQLento, between the GPPL and the DBMS. We separate SQLento from the GPPL by providing a web service interface for the GPPL to execute database operations. Separating SQLento from the GPPL has two advantages: the architecture can optimize the database code and it makes the architecture more portable because it is not bound to any GPPL. The executable database code and the communication interfaces are produced by the compiler, which uses SQLento as input. GPPLs use the generated (web service) interface to call procedures and functions written in SQLento. When the architecture receives those calls, the corresponding database code is passed on towards the DBMS. Results of the DBMS are passed back to the GPPL and can be mapped to a nested data structure to prevent data model mismatches.

SQLento is a procedural database programming language and allows to build simple programs. Two new features have been introduced to make database programming easier: relationship declarations and nested queries. Relationships between tables can be declared once as a subfield of a table, instead of rewriting them in queries and statements. Relationships can be used within queries to reason forwards and backwards between tables, as long as the query starts with the table the relationship is defined on. This dependency introduces limitations since relationships are bound to a table. For example, if a relationship is declared on the "users" table towards the "addresses" table, then it is not possible to get from the "addresses" table back to "users" table by using the same relationship. A new unique relationship should be declared or the query has to start from the table the relationship is declared on. This constraint does not exist when using SQL and should be solved to prevent limitations in expressiveness.