BVegter

Not much. In fact, SQL is a powerful language for gaining insights from your data. The concept of a view serves as an efficient, dynamic mechanism to define how insights are derived without the need for copying or moving data. However, there are some issues with SQL. While it is based on Relational Algebra, its implementation is not always sound. By adopting a different perspective on SQL, it seamlessly intertwines with Relational Algebra, allowing you to write classic SQL while simultaneously benefiting from the simplicity and insight of Relational Algebra. This article provides a concise technical overview of how this is implemented in Colbert’s SQL, an integral component of this Relational Database Management System (RDBMS).

In a previous article titled 'Executing Formulas Stored in Database Fields with (Almost) Standard SQL,' we introduced the concept of storing formulas in database fields. This article delves into the underlying concept that makes it a secure approach.

Thank you for your interest!

We'll make sure to keep you in the loop about any new articles we post on our website about Colbert if you subscribe on our mail-form at https://www.colbert.nl/mail-form/

With our approach in Colbert, you can provide your table that holds a formula field with a corresponding tag field and apply formulas to your data using a WHERE clause on the tag field. This allows you to safely test different scenarios.

Furthermore, in Colbert, you have the capability to inquire about the parameters used in each formula. For example, you can test the appearance of a specific parameter by querying:

SELECT * FROM Employees WHERE params bonus contains 'Birthdate'

Apparently, people are shocked by the title 'Execute formulas stored in database fields', which they associate with bad experiences involving scripting, SQL generation, stored procedures, SQL injections, and other vulnerabilities. However, the point is that the approach presented here is intended to address these vulnerabilities and provide a robust, secure environment.

We are accustomed to applying formulas to entire columns using calculated fields in SQL and have no problem with this; in fact, we gratefully use this option. The approach presented here offers the same safe opportunity but now at a record level. Therefore, there is no scripting, SQL generation, stored procedures, or SQL injection involved. Just as you can safely define a calculated field, you similarly define an expression that returns a value without any possible side effects.

Furthermore, it is not strange to include a formula in a database field. In databases, we register attributes of objects, and sometimes that attribute is an expression, such as the agreed bonus with your employer. Formula fields allow such an attribute to be registered and calculated within SQL. This provides us with a much more flexible, safer, and transparent method than writing a separate program outside the database.