Automated Insertion of Exception Handling for Key and Referential Constraints

Introduction

Relational database is commonly used among database applications. Key and referential constraints (KRC) are two main integrity constraints enforced in relational databases. These constraints play a key role in ensuring accuracy and consistency of data, so their enforcement during a database updating is of great importance.

Most database management systems (DBMS) can automatically enforce KRC by defining them in the database schema, upon which DBMS will reject any updating to a database that will lead to constraint violation. However, exception handling to handle such rejections still requires coding by programmers. Following is an example which executes an SQL query but without the provision of exception handling in Box 1.

$username = $_GET[‘username’];
$query = “UPDATE users SET username=$username”;
mysql_query($query);

In this example, the attribute “username” is a primary key (PK). The SQL is executed without any exception handling code following. This may cause key constraint violation. In order to make the program robust and less error-prone, programmers should provide adequate exception handling of KRC violations.

Generally, there are two levels to enforce the KRC in a database application: DBMS level and application level. In the classical DBMS, only a limited range of constraints can be enforced, and referential constraints enforcement is often neglected in favor of better performance. Empirical studies indicated that DBMS-level integrity constraint enforcement is rare in actual practice (Blaha, 2004). At the application level, programmers may perform KRC by detecting any KRC violation before executing the corresponding SQL operations, though we discovered that programmers seldom perform application level KRC enforcement.

This paper proposes a novel approach to automatically generate and insert exception handling code of KRC violation. In our approach, we first propose an extended abstract syntax tree (AST) to include SQL query semantics based on traditional AST. Each code pattern that requires exception handling together with the code to be inserted as precondition or post-condition is represented as a transformation rule. Two alternatives are provided for handling the possible exceptions associated with KRC. The first handles the exceptions in conjunction with the built-in enforcement in DBMS, while the second handles the exceptions in program without using the automated enforcement feature provided in DBMS. A tool GEHPHP (Generation of Exception Handling for PHP Systems) has been developed to implement the proposed approach. Experiments have also been conducted to evaluate the applicable of the approach.

The paper is organized as follows. The next section provides the background knowledge. The section after presents the proposed approach. The section next describes the prototype system as well as the evaluation of the approach. The section after examines related work and the final section concludes.

Background

Integrity constraints play an important role in database applications. Enforcement of these constraints will reject inconsistent or unauthorized data from entering a database. KRC are the most common integrity constraints which can be enforced automatically by a DBMS once specified in database schema (Silberschatz, Korth, & Sudarshan, 2002).