Gefin framework

A data-driven SQL-based web development framework.

1. Introduction

The Gefin framework is built on the idea that SQL can be used as the main development component of a web application. It translates SQL output directly into web pages using an API.

This approach addresses the problem that API (programming) languages work with very different data structures (trees) from the relational model for SQL (linked tables): the so-called Object–relational impedance mismatch problem. It achieves this without abandoning, for example, many-to-many relationships with enforced referential integrity, and ACID transactions, as would be the case with applications built on XML or NoSQL architectures.

This framework also allows the integration of tools for data management with those for data analysis: through the framework users can see the data, edit the data, analyse the data, and visualise the analysis.

In most development stacks there are three sets of technologies that can each have complex programming to generate pages: the API/webserver (e.g. PHP, ASP, etc. - the 'Controller' in the MVC model), the database/model (SQL, JSON, XML - the 'Model' in MVC) and the browser (HTML5/CSS/JS - the 'View' in MVC). Most applications focus on the first of these (e.g. most LAMP apps are written in PHP) or the third (SPAs using primarily Javascript). Others attempt to use a consistent language between the different levels (e.g. MEAN/Node.js) to simplify development.

The table model of SQL output can be interpreted in consistent ways in a web application: for searching, listing and of course linking, for example. For some kinds of applications, where tables are directly required, or where the queries correspond to coordinates on a map, there seems little point in developing a complex application for processing this information for each application/project if select statements can be written to output the information in a way understandable to the framework.

In addition, SQL can generate directly highly complex HTML output using string concatenation (CONCAT in MySQL/MariaDB, || in Oracle, etc.) and aggregated concatenation (GROUP_CONCAT, LISTAGG, STRING_AGG, etc.). Most DBMSs have a range of string transformation functions such as REGEX_REPLACE that can be used directly in SQL to manipulate data directly into something interpretable by a web browser.

The Gefin framework leverages these features to use SQL as the primary language for generating pages: the API is built on the interpretation of tables and their column labels. Once set up, all components are generated from either or both static HTML and dynamic SQL queries, managed through the interface itself, with the possibility of writing complex interactions in Javascript. There are minimal security risks where access is managed securely and the database server limits access to the server filesystem and processes, as is normally the case. This means that page-generation queries can be edited within the web application itself along with content. Where the project cannot write custom functions within the database, this framework also provides its own system for embedding function-like queries.

But the real advantage for many projects is the easy integration of the data editing system into the front-end interface, meaning that editing and publication can proceed concurrently without any additional workflows.

This framework design allows very rapid development of new and complex applications for both editing and publishing.

To use the framework, you will need a reasonable knowledge of SQL and a basic knowledge of HTML. Javascript can be used to build more complex interactive features. No knowledge of PHP is required.