This application claims the benefit of European Patent Application EP20382561.7 filed 25 June 2020.

The present disclosure relates to methods of compiling an SQL assertion from a Relational Database into SQL statements, so that the compiled SQL statements checks, at run-time, post-update satisfaction of the SQL assertion or, in other words, satisfaction of the SQL assertion after execution of an SQL update. The present disclosure further relates to systems and computer programs suitable for performing such methods.

BACKGROUND

SQL assertions are Boolean SQL statements over a relational database that ideally should always evaluate to true. That is, SQL assertions are supposed to evaluate to true for current state of the data in the database, and after applying any update over the data, the SQL assertion is desired to still evaluate to true. If the update leads the data into a state in which the SQL assertion is violated, the update may be rejected. In some sense, SQL assertions may be seen as a generalization of SQL constraints such as primary key constraints, check column constraints, and foreign key constraints.

Despite SQL assertions were defined more than 25 years ago in the SQL standard 92, none of the current Relational Database Management Systems (RDBMS) appears to implement them. This lack of support of SQL assertions in current RDBMS products may imply relevant drawbacks in the sense that data in a database may result in an inconsistent state if updates performed on data inadvertently violate some constraint linked to SQL assertion(s).

An object of the disclosure is to provide new methods, systems and computer programs aimed at solving at least some of the aforementioned problems.

SUMMARY

In an aspect, a method is provided for a Relational Database Management System, RDMS, to compile an SQL assertion into a set of SQL statements or views configured to check, at run-time, post-update satisfaction of the SQL assertion or, in other words, satisfaction of the SQL assertion after execution of an SQL update. The RDMS comprises SQL assertions including the SQL assertion to be checked and relational tables having a consistent pre-update state or, in other words, a state in which the SQL assertions are satisfied before execution of the SQL update. The SQL update and the SQL assertion to be checked affect or refer to one or more of the relational tables which have a pre-update content or, in other words, a content before execution of the SQL update. Herein, the term “pre-update” thus means before execution of the SQL update, and the term “post-update” means after execution of the SQL update. Pre-update content thus corresponds to data in database table(s) not including data modification(s) to be caused by SQL update. And post-update content corresponds to data in database table(s) including data modification(s) to be caused by SQL update.

The proposed “SQL compilation” method includes creating one or more triggers configured to simulate, at run-time, execution of the SQL update. This simulation includes providing one or more prospective relational tables with a differential or modified content to be caused by execution of the SQL update, while keeping unaltered the pre-update content of the affected relational tables.

The “SQL compilation” method further includes converting the SQL assertion to be checked into SQL statements or views configured to determine, at run-time, satisfaction or dissatisfaction of the SQL assertion depending on the pre-update content of the affected relational tables and the differential or modified content of the prospective relational tables.

The “SQL compilation” method still further includes creating a procedure (or sub program) configured to commit or abort, at run-time, execution of the SQL update onto the affected relational tables depending on whether the SQL assertion (to be checked) has been determined satisfied or dissatisfied, respectively. The commit of the execution of the SQL update onto the affected relational tables may be defined to transfer, at run-time, the differential or modified content (to be caused by execution of the SQL update) stored in prospective table(s) to or into affected table(s).

The suggested “SQL compilation” method permits an efficient implementation of SQL assertions that may be integrated into any RDBMS. The efficiency comes from an incremental revalidation of the SQL assertion. That is, assuming that the current data (or pre-update content) already satisfies the assertion, the proposed method checks if committing a given update will violate the assertion, or not. To do so, the method only revalidates the SQL assertion for the data affected by the update, instead of having to evaluate the assertion over the whole content of the database. In some examples, converting the SQL assertion to be checked into SQL statements or views may comprise: translating the SQL assertion to be checked into logics considering the affected relational tables; modifying the logics to further consider the prospective relational tables; and translating the modified logics back to SQL to generate the SQL statements or views. Said conversions or translations from SQL into logics and vice versa may be performed based on well-known Codd’s theorem.

In implementations of the method, translating the SQL assertion to be checked into logics may comprise translating the SQL assertion into logical rules including logic denials defining when the SQL assertion is violated, each of the logic denials having following form:

_L<-- L ₁ A ... A L _n A B ; wherein L _i=1-n are ordinary literals defining data existence or inexistence in affected tables, and B is a built-in literal defining selection of said existent or inexistent data causing violation of the SQL assertion.

According to examples, each of the ordinary literals L _i=1-n may be a basic literal or an aggregate literal or a derived literal; and translating the SQL assertion into logical rules may include, in case of derived literal, producing at least one derivation rule defining calculation of the derived literal and, in case of aggregate literal, producing at least one aggregation rule defining calculation of the aggregate literal.

In some configurations, translating the SQL assertion into the logical rules may include applying an unfolding technique to minimize number of produced (or to be produced) derivation rules.

According to some implementations, translating the SQL assertion into logical rules may include translating derived literal and corresponding derivation rule(s) defining calculation thereof into aggregate literal and corresponding aggregation rule(s) defining calculation thereof.

In examples of the method, modifying the logics to further consider the prospective relational tables may include applying a disjunctive normal form transformation so that said modifying of the logics does not produce any logical rule with OR operator.

In implementations, modifying the logics to further consider the prospective relational tables may include applying following formula to each of the logic denials:

-L←- A _i=1 .. _j-1 (old(L _i ) A new(L _j ) A A _i=1 .. _j-1 (old(L _i ) V new(L _j )) A B ; wherein function old(L) defines that L is true in the pre-update content and remains true with the differential or modified content, and function new(L) represents that the differential or modified content causes L to be true.

In case of L being a basic literal, functions new(L) and old(L) may be defined as follows: new(L) = new(T ) = ins T, which defines that existence of data T is new if data T is within differential content as to be inserted ( ins T ) by the SQL update; new(L) = new(-ir) = del_T, defining that inexistence of data T (-, T) is new if data T is within differential content as to be deleted ( del_T ) by the SQL update; old(L) = old(T ) = T A -idel_T, defining that existence of data T is old if data

T is within pre-update content (T) and is not within differential content as to be deleted (-1 del T) by the SQL update; old(L) = old(-T) = --T A --ins T, defining that inexistence of data T (-, T) is old if data T is not within pre-update content (-, T) and is not within differential content as to be inserted (-, ins_T) by the SQL update.

In case of L being an aggregate literal, functions new(L) and old(L) may be defined as follows: new(T(x,y) Aw(y,z)) ins_T (x, y 1) A del_T (x, y2) A T (x, y3) A w(y3, z) A w’(y 1, y2) A w(y3 + y 1 — y2, z) old(r(x,y) A w(y, z)) = ins_T(x,y1 ) A del_T(x,y2) A T(x,y3 ) Aw(y3, z) A w(y3 + y1 — y2, z); wherein L refers to data T(x,y ) in which x is a set of GROUP BY columns and y is aggregate value corresponding to said set of GROUP BY columns; and wherein w(y,z) is a condition involving data T(x,y ) and transformable into w'(y1,y2) and into w(y3 z) according to following table:

In some examples, producing at least one aggregation rule defining calculation of the aggregate literal may include: producing insertion aggregation rule(s) defining calculation part of the aggregate literal due to data included in differential content as to be inserted by the SQL update; and producing deletion aggregation rule(s) defining calculation part of the aggregate literal due to data included in differential content as to be deleted by the SQL update.

Aggregation rule defining calculation of aggregate literal in corresponding logic denial may have following form:

Taking the above general formulation of aggregation rule, insertion aggregation rule(s) may be produced by applying following formula to aggregation rule: and/or deletion aggregation rule(s) may be produced by applying following formula to aggregation rule:

In a further aspect, a system is provided for a Relational Database Management System, RDMS, to compile an SQL assertion into a set of SQL statements or views configured to check, at run-time, post-update satisfaction of the SQL assertion or, in other words, satisfaction of the SQL assertion after execution of an SQL update. The RDMS comprises SQL assertions including the SQL assertion to be checked and relational tables having a consistent pre-update state or, in other words, a state in which the SQL assertions are satisfied before execution of the SQL update. The SQL update and the SQL assertion to be checked affect or refer to one or more of the relational tables which have a pre-update content or, in other words, a content before execution of the SQL update.

The proposed “SQL compilation” system includes a trigger module configured to create one or more triggers adapted to simulate, at run-time, execution of the SQL update. This simulation includes providing one or more prospective relational tables with a differential or modified content to be caused by execution of the SQL update, while keeping unaltered the pre-update content of the affected relational tables.

The “SQL compilation” system further includes an SQL-view module configured to convert the SQL assertion to be checked into SQL statements or views configured to determine, at run-time, satisfaction or dissatisfaction of the SQL assertion depending on the pre-update content of the affected relational tables and the differential or modified content of the prospective relational tables.

The “SQL compilation” system still further includes a procedure module configured to create a procedure (or sub-program) adapted to commit or abort, at run-time, execution of the SQL update onto the affected relational tables depending on whether the SQL assertion (to be checked) has been determined satisfied or dissatisfied, respectively.

In a still further aspect, a computer program is provided comprising program instructions for causing a computer or computing system to perform “SQL compilation” methods such as the ones described in other parts of the disclosure. The computer program may be embodied on a storage medium and/or carried on a carrier signal.

In a still further aspect, a computing system is provided for compiling an SQL assertion into a set of SQL statements or views, the computing system comprising a memory and a processor, embodying instructions stored in the memory and executable by the processor, and the instructions comprising functionality or functionalities to execute “SQL compilation” methods, such as the ones described in other parts of the disclosure.

In some examples, a Relational Database Management System (RDMS) may be further provided including an SQL compilation system and/or an SQL compilation computer program, such as the ones described in other parts of the disclosure.

BRIEF DESCRIPTION OF THE DRAWINGS

Non-limiting examples of the disclosure will be described in the following, with reference to the appended drawings, in which:

Figure 1 is a block diagram schematically illustrating “SQL compilation” systems for compiling an SQL assertion, according to examples.

Figure 2 is a flow chart schematically illustrating “SQL compilation” methods, according to examples, which are suitable for being performed by systems according to e.g. Figure 1.

Figure 3 is a flow chart schematically illustrating examples of converting SQL assertion into SQL views suitable for SQL compilation methods such as the ones of Figure 2.

DETAILED DESCRIPTION OF EXAMPLES

Figure 1 is a block diagram schematically illustrating a (compilation) system 103 for a RDMS 100 to compile an SQL assertion into a set of SQL statements or views so that the compiled SQL statements check, at run-time, post-update satisfaction of the SQL assertion after execution of an SQL update. The RDMS 100 may comprise a repository of SQL assertions 102 including the SQL assertion to be checked, and a repository or structure of relational tables 101. The “SQL compilation” system 103 may be included in the RDMS 100 as shown in the particular example illustrated.

In the repository of relational tables 101 may coexist two types of tables: database tables 104 and prospective tables 108. The database tables 104 may be defined as tables used to store data of the database that serves corresponding applications, users, software, etc. The database tables 104 may have a consistent pre-update state, which means that the SQL assertions 102 are satisfied in said tables 104 before execution of the SQL update. The prospective tables 108 may be defined as special, customized tables aimed at storing differential or modified data to be caused by execution of the SQL update, i.e. involved in SQL update simulations.

The “SQL compilation” system 103 may be started or called by/from the RDMS, when a new SQL assertion is created, for causing the RDMS 100 to compile the new SQL assertion into several SQL statements that checks, at runtime, whether execution of an SQL update will violate the SQL assertion.

Given an SQL assertion, the SQL compilation system 103 may return a set of create triggers statements, a set of create SQL views statements, and a create procedure statement. Create triggers statements may be produced by Trigger module 103, create SQL views statements may be produced by SQL-view module 105, and create procedure statement may be produced by Procedure module 107, said modules being shown in Figure 1 as an exemplary configuration.

At run-time, the RDMS may execute all the create statements provided by the compilation system 103. The created triggers may provoke, at run-time, capturing any SQL update to be executed and providing prospective tables 108 with data to be caused by execution of the SQL update instead of updating database tables 104. In other words, execution of the SQL update is simulated by providing prospective tables 108 with differential or modified content, while affected database tables 104 are kept unaltered with pre-update content, i.e. content before execution of the SQL update. The created SQL views may, when executed at run-time, may return whether the SQL update captured or simulated in the prospective tables 108 may violate the SQL assertion in combination with pre-update content in affected database tables 104. The created procedure, when executed at run-time, may run said SQL views and, if no violation of SQL assertion is found, may apply the SQL update by “transferring” differential or modified content within prospective tables 108 into affected database tables 108. Otherwise, if violation is found, execution of the SQL may be discarded.

Thus, the RDMS may update the database tables 104 according to SQL update only if consistency of the compiled SQL assertion is found to be preserved, by invoking the created procedure when SQL update is to be executed. In other words, the created procedure permits simulating execution of the SQL update without updating database tables 104 and, if it is checked that SQL update will not violate the SQL assertion with such simulation, the SQL update may be committed. This commit may be performed by transferring differential or modified or simulated content in prospective tables 108 to corresponding database tables 104.

The SQL assertion to be compiled may define a constraint through an SQL sub-query within an SQL NOT EXISTS statement. Such an SQL sub-query may define relational conditions to determine tuple existence or inexistence within affected relational table(s) violating the constraint.

For instance, consider a database for managing the employees and departments of a company. Employees may work in some Departments and, furthermore, they may manage some of these departments with a specific additional bonus salary for being the manager. Assume it is desired to ensure that any employee managing a department with a high salary bonus (over 500$) should work in the same department. This condition may be specified by means of the following SQL assertion:

Assertion 1 :

CREATE ASSERTION ‘ImportantManagersWorklnTheirDepts’ AS CHECK( NOT EXISTS (

SELECT *

FROM Manages AS M WHERE M.bonusSalary > 500 AND NOT EXISTS(SELECT *

FROM Worksln AS W

WHERE M.emp = W.emp and M.dept = W.dept)

)

) Intuitively, this assertion 1 includes relational conditions (in SQL sub-query within outermost SQL NOT EXISTS) defining selection of all the managers that violate the constraint, that is, whose bonus salary is higher than 500$ but that do not work in the department they manage. Thus, the assertion is satisfied if none of such “problematic” managers exists. As commented before, SQL assertions may generally be written as an SQL sub-query that retrieves the tuples violating the intended constraint and placing such a sub-query inside an SQL NOT EXISTS statement.

Fundamental Logic Concepts

A term t may either a variable or a constant. An atom may be formed by a n-ary predicate p together with n terms, i.e., p(t1, . . ·; tn). If all the terms t of an atom are constants, atom mat be denominated ground atom. A literal L may be either an atom p(t1, ... , tn ) , a negated atom -p(t1, . . · tn), or a built-in literal ti ω tj , where ω is an arithmetic comparison (i.e., <,≤,=,<>).

A predicate p may be a derived predicate if boolean evaluation of atom p(t1, tn) depends on some derivation rule(s), otherwise, it may be a base predicate. A derivation rule may have following form: p(t 1, ... , tn ) ←φ (t1, ... , tn )

In the formula, p(t1, ... , tn ) is an atom called the head of the rule and φ (t1 , ... , tm) is a conjunction of literals called the body. Derivation rules may be restricted to be safe (i.e., any variable appearing in the head or in a negated or built-in literal of the body also appears in a positive literal of the body) and non-recursive. Given several derivation rules with predicate p within its head, p(t1, ... , tn ) is evaluated to true if and only if one of the bodies of such derivation rules is evaluated to true.

An aggregate predicate is a predicate defined over some predicate p that aggregates one of the terms of p with some aggregation function f. An aggregate predicate is defined by means of a rule: p(t 1, ... , tn ) ←φ (t1, ... , tn )

An atom p(t1, ... , tn,f(t )) evaluates to true if and only if f(x) is equal to aggregating all values x in <p(t1, ... , tm ) by means of f, grouped by t1, ... , tn. E.g. given the aggregate predicate sumSalaries(dept, x) defined by sumS alaries (dept, sum(sai)) Emp(e, dept, sal), sumSalaries(dept, x) evaluates to true if and only if x is equal to the sum of all salaries sal of all the employees within the same dept.

Creation of Triggers to simulate SQL update

The “SQL compilation” system 103 may further include a trigger module 106 configured to create trigger(s) configured to simulate, at run-time, execution of the SQL update. Said simulation may be based on providing prospective table(s) 108 with a differential or modified content to be caused by execution of the SQL update. Differential or modified content means data, tuple(s) to be updated (inserted, deleted, modified) by the SQL update when executed. Said differential data, tuple(s) may be provided only to prospective table(s) 108 in order to keep unaltered the pre-update content of the relational table(s) 104 affected (i.e. to be updated) by the SQL update.

Such a trigger-based simulation at run-time is aimed at delaying any commit of the SQL update until it is ensured that it will not violate constraint(s) defined by SQL assertion. Thus, the basic idea is to capture the SQL update and store it internally in prospective table(s) 108 until non-violation of the SQL assertion has been checked, instead of committing the SQL update directly.

With this purpose, for any given table T in database tables 104, two prospective tables 108 may be defined: ins_T and del_T, with same schema as table T. Table ins_T may defined to contain all the tuples to be inserted by the SQL update into T, and del_T all the tuples to be deleted by the SQL update from T. Tuple modifications, i.e. attribute modifications, may be encoded or treated as an insertion and a deletion of the same tuple with some different value. No specific prospective table for attribute modifications may thus be required.

For instance, given a database table called Manages with attributes “Emp”, “Dept”, and “Salary”, prospective tables ins_Manages and del_Manages may be defined or created with at least same three attributes as table Manages.

To populate these prospective tables, SQL Triggers may be used. That is, for each table T, three triggers may be built: one trigger for capturing insertions on T and storing them into ins_T, another trigger for capturing deletions on T and storing them into del_T, and a last trigger for capturing attribute modifications on T and storing them into ins_T and del_T.

For instance, for the table Manages, the Trigger module 106 may create following Triggers (syntax may vary depending on the RDBMS):

Trigger 1

CREATE TRIGGER ins_Manages_trigger ON Manages BEFORE INSERT AS

BEGIN INSERT INTO ins_MANAGES SELECT * FROM INSERTED END; CREATE TRIGGER del_Manages_trigger ON Manages BEFORE DELETE AS BEGIN INSERT INTO del_MANAGES SELECT * FROM DELETED END; CREATE TRIGGER update_Manages_trigger ON Manages BEFORE UPDATE AS BEGIN INSERT INTO ins_MANAGES SELECT * FROM INSERTED;

INSERT INTO del_MANAGES SELECT * FROM DELETED END;

Conversion of SQL Assertion into SQL statements

The “SQL compilation” system 103 may further comprise a SQL-view module 105 configured to convert the SQL assertion to be checked into SQL statements or views adapted to determine, at run-time, satisfaction or dissatisfaction of the SQL assertion. Said satisfaction or dissatisfaction may be determined depending on simulated post update content, i.e. pre-update content of the affected table(s) 104 and differential or modified content of the prospective table(s) 108.

An example of steps performed by this module 105 to obtain such SQL statements or views are shown in Figure 3. In essence, the module 105 first translates SQL assertion into logic based representation which may be based on logic denial(s). This logic denial(s) states when database tables 104 violates the SQL assertion. Afterwards, logic denial(s) may be simplified by removing logic derivation rule(s), if any, and converting them into logic aggregation rules. This transformation permits reducing number of cases that the method has to deal with. From there, the module 105 may convert the simplified logic rules into some new logic rules that further take into account prospective tables 108. In particular, such new logic rule(s) may define when differential or modified content stored in the prospective tables causes violation of the compiled SQL assertion along with pre-update content of the database tables 104. The module 105 may finally convert these last logic rules back into SQL, and in particular, into SQL views.

Translating SQL into Denials

The SQL assertion to be checked may be translated into logics by translating the relational conditions (defined by the SQL sub-query in the SQL assertion) into first- order logic denials.

A first-order logic denial may have n ordinary literal(s) (L _i=1-n ) and built-in literal(s) ( B ), and may be expressed as follows:

±← L ₁ A ... A L _n A B Formula 1

An ordinary literal (L _i=1-n ) may correspond to relational condition(s) defining existence or inexistence of tuple(s) within affected relational table(s). A built-in literal (B) may correspond to relational condition(s) defining a selection from among tuple(s) whose existence or inexistence is defined by ordinary literal(s). Said selection may e.g. be defined depending on an attribute of the tuple(s) and pertinent comparison operator.

Considering the above exemplary Assertion 1 , this particular SQL assertion may be translated into following first-order logic denial:

±<— Manages ( e , d, s) A s > 500 A -i WorksIn(e, d ) Denial 1

In plain terms, this first-order logic Denial 1 states that there is a violation if there is some e managing d, with a bonus salary greater than 500, when e does not work in d.

The translation from SQL assertions into logic denials, and vice versa, may be performed automatically. This is because SQL assertions are written in a similar way as logic denials. Indeed, an SQL assertion is written in the form of “CREATE ASSERTION ‘[assertion name]’ CHECK NOT EXISTS ([SQL query])”, that is, SQL assertions may describe what cannot happen in the database, in the very same manner as logic denials may do. Taking this into account, the translation of a SQL assertion into a logic denial may result into:

±← Tr Query (sqlQuery) Formula 2 Wherein TrQuery is a function that translates the sqlQuery into logics. According to Codd’s Theorem, it is already known how to translate the core of SQL queries (i.e., relational algebra expressions) into first-order logics. Moreover, this translation may be extended to translate SQL with aggregates into logics with aggregate literals. For reasons of completeness, a translation function from SQL with aggregates into logics with aggregate literals is provided in following Tables 1 - 5, in which left column represents the SQL (sub-) expression to be translated, middle column represents the logic result, and right column contains explanations/clarifications. Table 1 - Translation of SQL Query

Table 2 - Translation of SQL Body

Table 3 - Translation of FROM clause

Table 4 - Translation of WHERE Clause

From this basic translation, unfolding may be applied to reduce the number of obtained derivation rules if any. Unfolding is a well-known transformation technique which replaces a positive derived literal appearing in the body of a logic rule by its definition (i.e. the set of literals in the body of its derivation rule).

Translating Derivation Rules into Aggregation Rules

Once the SQL assertion has been translated into logic denial(s) and some logic denial includes derived literal (e.g. derived negation), said logic denial(s) with derived literal(s) may be converted into logic denial(s) with distributive aggregation. This may be implemented in following manner.

Considering a negated derived literal generally expressed as “ ^_, L(x1, ... , xn)” with a derivation rule like “L(x1, ..., xn) ← A1 A ... A Am”, corresponding new aggregate literal generally expressed as “L_agg(x1 , ... , xn, count()) ← A 1 A . . A Am” may be generated.

Intuitively, the aggregate literal “L_agg(x1, ... , xn, count())” counts how many times the rule “A1, ... Am” derives the literal “L(x1, ... , xn)”. Once such aggregate literal has been created, any occurrence of “ ^_, L(x1, ... , xn)” may be replaced by “L_agg(x1 , ... , xn, y) A y=0”. This is a sound replacement since, roughly speaking, a derived literal is false if and only if the counter of how many times it is derived as true equals 0. This replacement may be done in any rule appearing in the logics (denial or derivation rule).

Such a transformation may be applied for every negated derived literal appearing in the logics (derived from SQL assertion). This way, an equivalent logic specification without derived negation (i.e. logic specification with derived negative literals replaced by count aggregations) may be eventually obtained.

Modifying the logics (derived from SQL assertion) to further consider the prospective relational tables will be conceptually explained below with reference to previous first- order logic Denial 1.

In the following, it is shown an example involving an SQL assertion with derived negation, as given by Assertion 2:

Assertion 2:

CREATE ASSERTION ‘DepartmentsHaveOneSeniorEmployee’ AS CHECK(

NOT EXISTS (

SELECT *

FROM Department AS D WHERE NOT EXISTS(SELECT *

FROM Worksln AS WJOIN Employee AS E ON (W.emp = E.emp)

WHERE D.dept = W.dept AND E.age > 40)

)

)

This assertion 2 may be translated into following logic rules by applying translation principles described in other parts of the disclosure:

1 ← Department(d ) A - MinOneSenior(d) Rule 1

MinOneSenior(d, count(e )) ← WorksIn(e, d ) A Employee(e, age ) A age

Rule 2

> 40

Rule 1 is a denial, that is, a logic formula that states when there is a constraint violation, but Rule 2 is a derivation rule (similar to a SQL view, or SQL subquery). The denial of Rule 1 states that there is a violation if there is some department d that does not appear in the MinOneSenior view, and Rule 2 states that the MinOneSenior view contains all departments d which have at least one employee e with an age greater than 40. Any rule like the previous Rule 1 may be translated into another one (following Rule 3) using distributive aggregates, and any rule like the previous Rule 2 may be redefined accordingly (following Rule 4).

1 ← Department (d) A S enior Countered, x) A x = 0 Rule 3

S enior Countered, count (e)) ← WorksIn(e, d) A Employ ee(e, age) A age

Rule 4 > 40 Rule 3 is a denial stating a violation in case there is a department d whose counter of senior is x and x is equal to 0. Rule 4 states how this counting may be performed. That is, Rule 4 counts, for each department d, the number of employees older than 40 that work in department d. Adding Prospective Tables to Denials

Returning to Denial 1 , it may be modified to assert when some insertions/deletions (i.e. differential or modified content) may cause a constraint violation. This Denial 1 only states when current data (or pre-update content) rises a constraint violation, but the ultimate goal is to know when some insertions/deletions on current data (or pre-update content) may cause such violation.

Intuitively, a literal is true in the database tables 104 after applying the SQL update because it is either new (i.e., it was false in the database tables 104, but the SQL update causes it to be true), or because it is old (i.e., it was true in the database tables 104, and the SQL update does not cause any alteration on it). For instance, after applying the SQL update, a literal Manages(e, d, s) is true in the database tables 104 just because one of the two conditions (connected by OR operator) in the following formula is satisfied: ins_Manages(e, d, s) v ( Manages(e , d, s) A -del_Manages(e, d, s )) Formula 3 i.e., either there is an insertion of Manager, or the tuple was already true and there is no deletion of it (it is not to be deleted by the SQL update). Similarly, a literal is false in database tables after applying the SQL update because something new happened (i.e., it was true in the database tables 104, but the SQL update makes it false), or because it was false in pre-update content of database tables and nothing happened to it (i.e., the SQL update does not make it true). For instance, a literal Worksln(e, d) is false because one of the two conditions (connected by OR operator) in the following formula is satisfied: del_WorksIn(e, d ) v (- WorksIn(e, d ) A - ins_WorksIn e, d)) Formula 4 i.e., there is either a deletion of Worksln, or the tuple was not previously in the database tables and it has not been inserted (it is not to be inserted by the SQL update).

Thus, since it is wanted to check the Denial 1 according to the state of the data to be reached through the insertions/deletions (to be performed by the SQL update), the literals in the Denial 1 may be replaced with corresponding insertions/deletions that lead to its satisfaction. In the proposed example, it may suffice to replace the literal Manages(e, d, s) for the Formula 3 and -'Worksln for the Formula 4. Then, after transforming the resulting expression into disjunctive normal form, the following denials may be obtained:

1 ←- ins_Manages(e, d, s) A s > 500 A del_W orksIn(e , d) Denial 2

1 ← ins_Manages(e, d, s ^' ) A s

Denial 3

> 500 A WorksIn(e, d ) A -i ins_WorksIn(e, d )

1 ← Manages(e, d, s ) A -i del_Manages(e, d, s ) A s

Denial 4 > 500 A del_W orksIn(e , d)

1 ← Manages(e, d, s ) A -i del_Manages(e, d, s ^' ) A s

Denial 5

> 500 A -i WorksIn(e, d ) A -i ins_WorksIn(e, d )

Intuitively, Denial 2 states that there is a violation if it is inserted that e will manage d, when e has a high bonus salary and, at the same time, it is deleted that e works in d (i.e. e will not work in d anymore). Differently, Denial 3 states that there is a violation if it is inserted that e will manage d, when e has a high bonus salary, and e was not working in d and it is not inserted e to work in d. Denial 4 behaves similarly. Denial 5 states, roughly speaking, that if the constraint was already violated (i.e., there was a manager e of department d, with a high bonus salary, not working for department d) and no update is applied, then, the constraint is still violated. Since it is assumed that the state of the data prior the SQL update is consistent (i.e. database tables 104 have consistent pre-update state) or, in other words, data in database tables satisfy the SQL assertions, Denial 5 may be safely omitted.

In general, modifying the logics to further consider the prospective relational tables may include generating n - 1 rules for each of the first-order logic denials (±← L ₁ A ... A L _n A B) according to following formula:

±← Ai=1..j-1(old(L _i ) A new(L _j ) A /\ _{i=j+1 n} (pld(Li) v new(L _i )) A B Formula 5 wherein: old(L _i ) = old(T ) and new(L _j ) = new(T ) taking into account that the ordinary literal L _j , L _; refers to atom(s) T being true (or data T existing) in affected table(s), and old(L _i ) = old(-T) and new(L _j ) = new(-T) taking into account the ordinary literal L _j , L _; refers to atom(s) T being false (or data T inexisting) in affected table(s). old(T) may conceptually mean that atom(s) T (or existence of data T) is old; that is, atom(s) T (or existence of data T) is true in pre-update content of affected table(s) and the SQL update will not change said atom(s) T (or existence of data T) to false. new(T) may conceptually mean that atom(s) T (or existence of data T) is new; that is, SQL update will change atom(s) T (or existence of data T) to true in affected table(s). old(-T) may conceptually mean that false evaluation of atom(s) T (or inexistence of data T) is old; that is, atom(s) T (or existence of data T) is false in pre-update content of affected table(s) and SQL update will not change said atom(s) T (or existence of data T) to true. new(-T) may conceptually mean that false evaluation of atom(s) T (or inexistence of data T) is new; that is, SQL update will change atom(s) T (or existence of data T) to false in affected table(s). New/Old maps for base literals

New/old expressions in Formula 5 for basic (or non-aggregate) literals may be resolved according to following new/old maps: new(T ) = ins_T new(-T) = del_T

New/old maps 1 old(T) = T A —del_T old(-T) = — i T A —ins_T

Intuitively, tuple/data T is new if it is inserted, and old if it already existed before the SQL update and it is not deleted by the SQL update. Similarly, absence or inexistence of tuple/data T (written in logics as -T) is new if it is deleted, and old if it did not exist before SQL update and it is not inserted by SQL update.

In the particular example of Denial 1 (±← Manages (e, d, s) A s > 500 A

-i WorksIn(e, d)), the application of general Formula 5 to it may be summarized in following step-by-step description, taking into account that L _t = Manages (e, d, s), L ₂ = -i WorksIn(e, d ), and B = s > 500 in ±← L _t A ... A L _n A B.

Step 1: Application of general Formula 5 to Denial 1 for j = 1 may produce following denial:

±<— new(Manages (e, d, s)) A s > 500 A (old(-WorksIn(e, d)) v new(-WorksIn(e, d))) <Denial i=1>

Step 2: Application of general Formula 5 to Denial 1 for j = 2 may produce following denial:

±← old(Manages ( e , d, s)) A s > 500 A new(-WorksIn(e, d)) <Denial i=2>

Step 3: Elimination of logic operator v in <Denial j=1> may produce following denials: ±← new(Manages (e, d, s)) A s > 500 A new(-WorksIn(e, d)) <Denial i=1a>

±← new(Manages (e, d, s)) A s > 500 A old(-WorksIn(e, d)) <Denial i=1b>

Step 4: Application of New/old maps 1 to <Denial j=1a> produces Denial 2 conceptually/intuitively inferred in previous practical example regarding Denial 1:

1 ← ins_Manages(e, d, s) A s > 500 A del_W orksIn(e , d) Denial 2 Step 5: Application of New/old maps 1 to <Denial j=1b> produces Denial 3 conceptually/intuitively derived in previous practical example regarding Denial 1:

1 ← ins_Manages(e, d, s ^' ) A s

Denial 3

> 500 A WorksIn(e, d ) A -i ins_WorksIn(e, d ) Step 6: Application of New/old maps 1 to <Denial j=2> produces Denial 4 conceptually/intuitively deduced in previous practical example regarding Denial 1:

1 ← Manages(e, d, s ) A - del_Manages(e, d, s ) A s

Denial 4 > 500 A del_W orksIn(e , d)

Note that general Formula 5 is defined to avoid generation of any denial that only depends on pre-update content, i.e. with no influence of differential or modified content (to be generated by execution of the SQL update). In particular, Denial 5 conceptually/intuitively suggested in previous practical example regarding Denial 1 is not generated by application of general Formula 5. New/Old maps for aggregate literals

New/old expressions in Formula 5 for denials obtained from SQL assertion based on relational algebra with distributive aggregation (either because it contains an aggregate literal or because it is defined through derived negation which, as shown in other parts of this disclosure, may be rewritten as an aggregate literal) may follow a different pattern than those for base literals.

A distributive aggregate is an aggregate operation which may be incrementally recomputed using the aggregate value of the data to be inserted/deleted by the SQL update. For instance, COUNT(*) is a distributive aggregate. Indeed, given the counter of senior employees for each department, this aggregate may be incrementally recomputed if both the COUNT(*) of senior employees to be inserted for each department and the COUNT(*) of senior employees to be deleted for each department are known. SUM(*) is also a distributive aggregate. Distributive aggregation specific conversion will be described based on following SQL assertion.

Assertion 3: CREATE ASSERTION ‘DepartmentsHaveOneHundredSeniorEmployees’ AS CHECK(

NOT EXISTS (

SELECT * FROM Department AS D,

(SELECT W.dept, count(E.e p) as x

FROM Worksln AS W

JOIN Employee AS E ON (W.emp = E.emp)

WHERE E.age > 40 GROUP BY W.dept

) AS SenCounter

WHERE D.dept = SenCounter.dept AND SenCounter.x = 100)

)

)

This assertion 3 may be translated into following logic rules by applying translation principles described in other parts of the disclosure:

1 ← Department (d) A Senior Countered, x) A x = 100 Rule 5

S enior Countered, count (e)) ← WorksIn(e, d) A Employ ee(e, age) A age

Rule 6

> 40 Rule 5 is a denial, that is, a logic formula that states when there is constraint violation, and Rule 6 is a derivation rule (similar to a SQL view or SQL subquery). The denial of Rule 5 states that there is a violation if there is a department d whose counter of senior is x and x is equal to 100. Rule 6 states how this counting may be performed, i.e. Rule 6 counts, for each department d, the number of employees older than 40 that work in d.

Since Rule 5 is a logic denial, it may be processed by applying general Formula 5 to said Rule 5. In this case, new/old maps other than previously described new/old maps 1 may be used for aggregate literals. In the particular example of Rule 5, S enior Countered, x) is an aggregate literal. Such other new/old maps for a tuple T(x, y), where x is a set of group by columns and y the aggregate value of said set of group by columns, may be defined as follows: new(T (x, y) A w(y, z))

= ins_T (x, y 1) A del_T (x, y2) A T (x, y3) A w(y3, z)

A w’(y1, y2) A w(y3 + y1 — y2, z) New/old maps 2 old(T (x, y) A w(y, z)) = inS-T (x, y 1) A del_T (x, y2) A T (x, y3) A w(y3, z) A w(y3 + y1 — y2, z);

Wherein transformation from w(y,z) to w’(y1,y2) and to w(y3,z) may be given according t f ll i T bl 6 Application of general Formula 5 to Rule 5 may be summarized in following step-by- step description, taking into account that L _t = Department(d), L ₂ = SeniorCounter(d, x ), and B = x = 100.

Step 1 : Application of general Formula 5 to Rule 5 for j = 1 may produce following denial:

1 ← new(Department(d)) A (old(SeniorCounter(d, x) Ax = 100 )

V new(SeniorCounter(d, x ) A x = 100 )) <Rule 5 i=1>

Step 2: Application of general Formula 5 to Rule 5 for j = 2 may produce following denial:

1 ← old(pepartment(d )) A new(SenwrCounter(d, x) A x = 100 ) <Rule 5 i=2>

Step 3: Elimination of logic operator v in <Rule 5j=1> may produce following denials: 1 ← new(Department(d)) A new(SenwrCounter(d, x) A x = 100 ) <Rule 5 i=1a>

1 ← new(Department(d)) A old(SeniorCounter(d, x) A x = 100 ) <Rule 5 i=1b>

Step 4: Application to <Rule 5 j=1a> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following denial: 1 ← ins_Department(d) A Senior Countered, x)

A ins_SeniorCounter(d, x _t )

Denial 6

A del_SeniorCounter(d, x _d ) A x ¹ 100 A x _t ¹ x _d A x + X _j — x _d = 100

Step 5: Application to <Rule 5 j=1b> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following denial:

1 ← ins_Department(d)) A Senior Countered, x)

A ins_SeniorCounter(d, x _t ) A del_SeniorCounter( _< d, x _d ) Denial 7 A x = 100 A x + X _j — x _d = 100

Step 6: Application to <Rule 5 j=2> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following denial:

1 ← Department (d) A - _\ del_Department(d) A A SeniorCounter(d, x)

A ins _Senior Countered, X _j ) A del_SeniorCounter(d, x _d ) Denial 8 A x <> 100 A X _j <> x _d

In relation to Rule 6, its right part ( WorksIn(e , d) A Employee (e, age) A age > 40) may then be converted into rules taking into account differential or modified content (e.g. insertions and deletions in prospective tables) to be caused by the SQL update. This may be performed by taking into account insertions to be caused by the SQL update (data/tuples in prospective tables as to be inserted) and deletions to be caused by the SQL update (data/tuples in prospective tables as to be deleted).

With respect to insertions (to be caused by SQL update), following logics may be applied to compute aggregate value(s) for the data to be inserted.

Given any aggregation rule with n ordinary-literals and the built-in literals B: p(x, f(y)) ← L ₁ A ... A L _n A B Formula 6

Aggregation rule(s) for insertions to be caused by SQL update may be obtained according to following general formula: ins_p _j (x, f(y)) ← Ai=1..j-1(old(L _i )) A new(L _j ) A Ai=j+1„n(old(L _i ) v new(L _i )) A B Formula 7

Application of general Formula 7 to Rule 6 may be summarized in following step-by- step description, taking into account that L ₁ = WorksIn{e, d), L ₂ = Employee(e, age), and B = age > 40.

5

Step 1 : Application of general Formula 7 to Rule 6 for j = 1 may produce following rule: ins _Senior Countered, count(e))

<- new (W orksIn(e , d)) A (old(Employee(e, age )

<Rule 6i i=1> V new (Employ ee(e, age)) A age > 40

Step 2: Application of general Formula 7 to Rule 6 for j = 2 may produce following rule: ins -Senior Countered, countie)') old(WorksIn(e, d)) A new (Employ ee(e, age) A age

<Rule 6i i=2>

> 40

Step 3: Elimination of logic operator v in <Rule 61 j=1> may produce following rules: ins -Senior Counter _1a (d, count(e)) new (W orksIn(e , d)) A new (Employ ee(e, age) A age

<Rule 6i i=1a>

> 40 ins -Senior Counter _1b (d, count (e)) new(WorksIn(e, d)) A old(Employee(e, age) A age <Rule 6i i=1b>

> 40

10

Step 4: Application to <Rule 6i j=1a> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following rule: ins_SeniorCounter _1a (d, count(e)) ins_WorksIn(e, d)) A ins -Employ ee(e, age) A age

Rule 7

> 40

Said Rule 7 captures those senior people who are to be inserted as employees and workers of d at the same time.

15 Step 5: Application to <Rule 6i j=1b> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following rule: ins _SeniorCounter _1b (d, count(e )) ← ins_WorksIn(e, d)) A Employee(e, age )

Rule 8 A del_Employee(e, age ) A age > 40

Rule 8 captures those people who are to be inserted as workers of d and were already employees and are not to be deleted as such.

Step 6: Application to <Rule 6i j=2> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following rule: ins _Senior Countered, count (e)) ← WorksIn(e, d ) A -i del_WorksIn(e, d))

Rule 9

A ins_Employee(e, age ) A age > 40

Such a Rule 9 captures those people who were already workers of d and are to be inserted as employees.

Intuitively, a further rule could be defined representing those people who were already workers of d and employees and are not to be deleted, but such a rule may be ignored since it does not capture new data or differential/modified content, but just the old or pre-update content.

Regarding deletions to be caused by SQL update, following logics may be applied to compute aggregate value(s) for the data to be deleted. Given any aggregation rule with n ordinary-literals and built-in literals B, such as the one expressed through previous Formula 6, aggregation rule(s) for deletions to be caused by SQL update may be obtained according to following general formula: del_Pj(x,f(y )) ← A _i= i.. _; -1(old(Li)) A new(-L _j ) A /\i=j+1.. _n (Li) A B Formula 8

Application of general Formula 8 to Rule 6 may be summarized in following step-by- step description, taking into account that L _t = W orksIn(e , d) , L ₂ = Employee{e, age), and B = age > 40.

Step 1 : Application of general Formula 8 to Rule 6 for j = 1 may produce following rule: del-SeniorCounter1(d ,count(e)) ← new(—WorksIn(e, d)) A Employee(e, age) A age

<Rule 6d i=1> > 40

Step 2: Application of general Formula 8 to Rule 6 for j = 2 may produce following rule: del_SeniorCounter ₂ (d, count(e )) ← old(WorksIn(e, d)) A new(-Employee(e, age))

<Rule 6d i=2>

A age > 40

Step 3: Application to <Rule 6d j=1> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following rule: del-SeniorCounter1(d ,count(e)) ← del_WorksIn(e, d) A Employee(e age) A age > 40 Rule 10 Said Rule 10 captures those senior employees who are to be deleted as workers of d.

Step 4: Application to <Rule 6d j=2> of New/old maps 1 for basic literals and New/old maps 2 for aggregate literals may produce following rule: del-SeniorCounter1(d ,count(e)) ← WorksIn(e, d) A -idel_WorksIn(e, d)

Rule 11

A del_Employee(e, age) A age > 40

This Rule 11 captures those senior people who are workers of d, are not to be deleted as workers of d, and are to be deleted as employee.

Translating Denials with Prospective Tables back into SQL

Once denials dependent on prospective tables have been generated, these modified logic denials (see Denials 2 - 4, for instance) may be translated back to SQL. Since first-order logic formulas are known to be equivalent to relational-algebra, this is also a known processing as commented in other parts of the disclosure. In previous example, following three SQL views may be generated for denials 2 - 4:

View corresponding to denial 2: CREATE VIEW ‘ImportantManagersWorkslnDeptsT AS SELECT *

FROM ins_Manages AS ins JOIN del_Worksln AS del ON (ins.emp = del.emp and ins. dept = del. dept) WHERE ins. salary > 500

View corresponding to denial 3:

CREATE VIEW ‘lmportantManagersWorkslnDepts2’ AS SELECT *

FROM ins_Manages AS ins

ANTI JOIN Worksln AS current ON (ins.emp = current.emp and ins. dept = current.dept)

ANTI JOIN ins_Worksln AS insW ON (ins.emp = insW.emp and ins. dept = insW.dept)

WHERE ins. salary > 500

View corresponding to denial 4:

CREATE VIEW ‘lmportantManagersWorkslnDepts3’ AS SELECT *

FROM Manages AS current

ANTI JOIN del_Manages AS del ON (del.emp = current.emp and del. dept = current.dept)

JOIN del_Worksln AS del ON (del.emp = current.emp and del. dept = current.dept)

WHERE current.salary > 500

ANTI JOINS may be rewritten in any particular RDBMS implementation (e.g. NOT EXISTS).

Translating logic denials back into SQL statements or views is performable automatically. In fact, translating first-order logics into relational algebra expressions, which is the core of SQL, is an already known result from Codd’s Theorem. These known principles may be extended to translate first-order logics with aggregate literals into SQL with aggregates.

For the sake of self-completeness, a possible manner of performing this translation is provided in following tables 7 - 10. The left column includes the logic expression or sub-expression to be translated; the middle column shows a pseudo-code that creates corresponding SQL expression or sub-expression, and the right column contains clarifications/explanations. These tables 7 - 10 also include translation rules to be applied when SQL assertion corresponds to relational algebra with aggregates (with and without negation).

Table 7 - Translation of a denial

Table 8 - Translation of derivation rules

Table 9 - Translation of literals

Table 10 - Translation of terms

Denials 6-8 may also be translated into SQL according to these translation principles:

View corresponding to denial 6 CREATE VIEW ‘DepartmentHavelOOSeniorsT AS SELECT *

FROM ins_Department AS ins

JOIN SeniorCounter AS currentCount ON (ins. dept = currentCount.dept) LEFT JOIN ins_SeniorCounter AS insCount ON (ins. dept = insCount.dept) LEFT JOIN del_SeniorCounter AS delCount ON (ins. dept = delCount.dept)

WHERE currentCount.total <>100 AND insCount. total <> delCount. total AND currentCount. total+insCount.total-delCount.total = 100

View corresponding to denial 7 CREATE VI EW ‘DepartmentHavel 00Seniors2’ AS

SELECT *

FROM ins_Department AS ins JOIN SeniorCounter AS currentCount ON (ins. dept = currentCount.dept)

LEFT JOIN ins_SeniorCounter AS insCount ON (ins. dept = insCount.dept) LEFT JOIN del_SeniorCounter AS delCount ON (ins. dept = delCount.dept) WHERE currentCount. total = 100 AND currentCount.total+insCount.total- delCount. total = 100

View corresponding to denial 8

CREATE VIEW ‘DepartmentHave100Seniors3’ AS

SELECT *

FROM Department AS dept

JOIN SeniorCounter AS currentCount ON (dept.dept = currentCount.dept) LEFT JOIN ins_SeniorCounter AS insCount ON (dept.dept = insCount.dept) LEFT JOIN del_SeniorCounter AS delCount ON (dept.dept = delCount.dept) WHERE currentCount.total = 100 AND currentCount.total+insCount.total- delCount. total = 100

If no tuple/row is returned, at runtime, by each of the above views corresponding to denials 6 - 8, this means that SQL assertion 3 will not be violated and, therefore, SQL update may be committed. Otherwise, it means that SQL assertion 3 will be violated and, therefore, commit of the SQL update may be avoided.

Rules 7 - 9 may also be translated into SQL according to these translation principles:

View corresponding to Rule 7 (ins SeniorCounter1a)

CREATE VIEW ‘ins_SeniorCounter1a’ AS SELECT wi.dept, count(wi.emp) AS total FROM ins_Worksln AS wi

JOIN ins_Employee AS emp ON (emp.emp = wi.emp)

WHERE emp. age > 40 GROUP BY wi.dep

View corresponding to Rule 8 (ins SeniorCounteri _h )

CREATE VI EW ‘ins_SeniorCounter1 b’ AS SELECT wi.dept, count(wi.emp) AS total FROM ins_Worksln AS wi

JOIN Employee AS emp ON (emp.emp = wi.emp)

ANTI JOIN del_Employee AS del_emp ON (del_emp.emp = emp.emp AND del_emp.age = emp.age)

WHERE emp.age > 40 GROUP BY wi.dep

View corresponding to Rule 9 (ins SeniorCounter2)

CREATE VIEW ‘ins_SeniorCounter2’ AS SELECT wi.dept, count(wi.e p) AS total FROM Worksln AS wi

ANTI JOIN del_Worksln AS del_wi ON (wi.dept = del_wi.dept AND wi.emp = del_wi.emp)

JOIN ins_Employee AS emp ON (emp.emp = wi.emp)

WHERE emp.age > 40 GROUP BY wi.dep

And said three SQL views corresponding to Rules 7 - 9 may be summed at a further SQL view as the following one.

View corresponding to ins SeniorCounter (sum of Rules 7 - 9)

CREATE VIEW ‘ins_SeniorCounter’ AS

SELECT insla.dept, ins1a.total+ins1b.total+ins2.total AS TOTAL FROM ins_SeniorCounter1a AS insla

OUTER JOIN ins_SeniorCounter1b AS inslb ON (insla. dep = inslb.dep) OUTER JOIN ins_SeniorCounter2 AS ins2 ON (insla. dep = ins2.dep)

Rules 10 and 11 may also be translated into SQL according to these translation principles:

View corresponding to Rule 10 (del SeniorCounteri)

CREATE VIEW ‘del_SeniorCounterV AS SELECT wi.dept, count(wi.emp) AS total FROM del_Worksln AS wi

JOIN Employee AS emp ON (emp.emp = wi.emp)

WHERE emp.age > 40 GROUP BY wi.dep

View corresponding to Rule 11 (del SeniorCounter)

CREATE VIEW ‘del SeniorCounter2’ AS SELECT wi.dept, count(wi.emp) AS total FROM Worksln AS wi

ANTI JOIN del_Worksln AS del_wi ON (wi.emp = del_wi.emp AND wi.dep = del_wi.dep)

JOIN del_Employee AS emp ON (emp.emp = wi.emp)

WHERE e p. age > 40 GROUP BY wi.dep

And said two SQL views corresponding to Rules 10 and 11 may be summed at a further SQL view as the following one.

View corresponding to del SeniorCounter (sum of Rules 10 and 11)

CREATE VIEW ‘del_SeniorCounter’ AS SELECT deH .dept, del1.total+del2.total AS TOTAL FROM ins_SeniorCounter1 AS dell

OUTER JOIN del_SeniorCounter2 AS del2 ON (deH .dep = del2.dep)

Creation of Procedure to commit or abort SQL update

The “SQL compilation” system still further includes a Procedure module 107 configured to create a procedure adapted to commit or abort, at run-time, execution of the SQL update onto the affected relational tables depending on whether the SQL assertion (to be checked) has been determined satisfied or dissatisfied, respectively. The procedure may check the satisfaction/dissatisfaction of the update by running the previously generated SQL views, and checking whether they return an empty result, or not. Then, the SQL update may be committed by e.g. transferring the differential or modified content of prospective table(s) to corresponding affected table(s).

Implementation of modules and methods

As used herein, the term “module” may be understood to refer to software, firmware, hardware and/or various combinations thereof. It is noted that the modules are exemplary. The modules may be combined, integrated, separated, and/or duplicated to support various applications. Also, a function described herein as being performed by a particular module may be performed by one or more other modules and/or by one or more other devices instead of or in addition to the function performed by the described particular module. The modules may be implemented across multiple devices, associated or linked to corresponding “SQL compilation” systems proposed herein, and/or to other components that may be local or remote to one another. Additionally, the modules may be moved from one device and added to another device, and/or may be included in both devices, associated to corresponding “SQL compilation” systems proposed herein. Any software implementations may be tangibly embodied in one or more storage media, such as e.g. a memory device, a floppy disk, a compact disk (CD), a digital versatile disk (DVD), or other devices that may store computer code.

The “SQL compilation” systems according to present disclosure may be implemented by computing means, electronic means or a combination thereof. The computing means may be a set of instructions (e.g. a computer program) and then “SQL compilation” systems may comprise a memory and a processor, embodying said set of instructions stored in the memory and executable by the processor. These instructions may comprise functionality or functionalities to execute corresponding “SQL compilation” methods such as e.g. the ones described with reference to other figures.

In case the “SQL compilation” systems are implemented only by electronic means, a controller of the system may be, for example, a CPLD (Complex Programmable Logic Device), an FPGA (Field Programmable Gate Array) or an ASIC (Application-Specific Integrated Circuit).

In case the “SQL compilation” systems are a combination of electronic and computing means, the computing means may be a set of instructions (e.g. a computer program) and the electronic means may be any electronic circuit capable of implementing corresponding “SQL compilation” methods proposed herein, such as the ones described with reference to other figures.

The computer program(s) may be embodied on a storage medium (for example, a CD- ROM, a DVD, a USB drive, a computer memory or a read-only memory) or carried on a carrier signal (for example, on an electrical or optical carrier signal).

The computer program(s) may be in the form of source code, object code, a code intermediate source and object code such as in partially compiled form, or in any other form suitable for use in implementing “SQL compilation” methods according to present disclosure. The carrier may be any entity or device capable of carrying the computer program(s).

For example, the carrier may comprise a storage medium, such as a ROM, for example a CD ROM or a semiconductor ROM, or a magnetic recording medium, for example a hard disk. Further, the carrier may be a transmissible carrier such as an electrical or optical signal, which may be conveyed via electrical or optical cable or by radio or other means.

When the computer program(s) is/are embodied in a signal that may be conveyed directly by a cable or other device or means, the carrier may be constituted by such cable or other device or means. Alternatively, the carrier may be an integrated circuit in which the computer program(s) is/are embedded, the integrated circuit being adapted for performing, or for use in the performance of, “SQL compilation” methods proposed herein.

Figure 2 is a flow chart schematically illustrating “SQL compilation” methods performable by systems equal or similar to the ones described with reference to Figure 1. Compilation methods may be initiated (e.g. at block 200) upon detection of a starting condition such as e.g. a request for starting the method or an invocation of the method from RDMs 100. Since compilation methods according to Figure 2 are performable by compilation systems according to Figure 1 , number references from Figure 1 may be used in following description of Figure 2.

Compilation methods may further include (e.g. at block 201) creating trigger(s) configured to simulate, at run-time, execution of the SQL update by providing prospective table(s) 108 with a differential or modified content to be caused by execution of the SQL update, while keeping unaltered the pre-update content of affected tables 104. This functionality implemented at block 201 may be performed by e.g. trigger module such as the module 106 previously described with reference to Figure 1. Functional details and considerations explained about said module 106 may thus be similarly attributed to method block 201.

Compilation methods may still further include (e.g. at block 202) converting the SQL assertion to be checked into SQL view(s) configured to determine, at run-time, satisfaction or dissatisfaction of the SQL assertion depending on pre-update content of the affected table(s) 104 and differential or modified content of the prospective table(s) 108. This functionality implemented at block 202 may be performed by e.g. SQL-view module such as the module 105 previously described with reference to Figure 1. Functional details and considerations explained about said module 105 may thus be similarly attributed to method block 202.

Compilation methods may yet further include (e.g. at block 203) creating a procedure configured to commit or abort, at run-time, execution of the SQL update onto affected table(s) depending on whether the SQL assertion to be checked has been determined satisfied or dissatisfied, respectively. This functionality implemented at block 203 may be performed by e.g. Procedure module such as the module 107 previously described with reference to Figure 1. Functional details and considerations explained about said module 107 may thus be similarly attributed to method block 203.

Compilation methods may terminate (e.g. at block 204) when an ending condition is detected such as e.g. once the procedure configured to commit or abort, at run-time, execution of the SQL update has been created.

Figure 3 is a flow chart schematically illustrating examples of converting SQL assertion into SQL views suitable for SQL conversion (sub-) methods such as the ones described with reference to block 202 in Figure 2. This (sub-) methods of converting SQL assertion into SQL views may be performed by e.g. SQL-view module such as the module 105 previously described with reference to Figure 1. Functional details and considerations explained about said module 105 may thus be similarly attributed to this conversion of SQL assertion into SQL views.

Conversion (sub-) methods may be initiated (e.g. at block 300) once simulation triggers have been created at e.g. block 201 of Figure 2.

Conversion (sub-) methods may further include (e.g. at block 301) translating SQL assertion to be checked into logic rules considering only affected tables (pre-update content). Functional details and considerations explained about said translation of SQL assertion into logic rules within the context of module 105 in Figure 1 may be similarly attributed to method block 301.

Conversion (sub-) methods may further include (e.g. at block 302) verifying whether logic rules (from block 301) include some derivation rule(s) and, in this case, converting said derivation rule(s) into aggregation rule(s). Functional details and considerations explained about said conversion of derivation rules into aggregation rules within the context of module 105 may be similarly attributed to method block 302.

Conversion (sub-) methods may further include (e.g. at block 303) modifying logic rules (from block 302) to further consider prospective tables (differential or modified content). Functional details and considerations explained about said modification of logic rules within the context of module 105 may be similarly attributed to method block 303.

Conversion (sub-) methods may further include (e.g. at block 304) translating logic rules (from block 303) back to SQL to generate SQL views configured to determine, at run-time, satisfaction or unsatisfaction of the SQL assertion. Functional details and considerations explained about said translation of logic rules back to SQL within the context of module 105 may be similarly attributed to method block 304. Conversion (sub-) methods may terminate (e.g. at block 305) when SQL views to check satisfaction of SQL assertion have been created.

Although only a number of examples have been disclosed herein, other alternatives, modifications, uses and/or equivalents thereof are possible. Furthermore, all possible combinations of the described examples are also covered. Thus, the scope of the disclosure should not be limited by particular examples, but it should be determined only by a fair reading of the claims that follow.