Automated Insertion of Exception Handling for Key and Referential Constraints

Automated Insertion of Exception Handling for Key and Referential Constraints

Kaiping Liu (School of Electrical and Electronic Engineering, Nanyang Technological University, Singapore), Hee Beng Kuan Tan (School of Electrical and Electronic Engineering, Nanyang Technological University, Singapore) and Xu Chen (Institute of Computing Technology, Chinese Academy of Sciences, Beijing, China)
Copyright: © 2013 |Pages: 19
DOI: 10.4018/jdm.2013010101
OnDemand PDF Download:
$37.50

Abstract

Key and referential constraints are two main integrity constraints in database applications. These constraints can be automatically enforced by the Database Management System with their exception—violation from these constraints—handled by programmers. This paper proposes an approach to relieve the burden of programmers from mechanical coding for handling exceptions of these constraints violation by using program transformation. We first propose an extended abstract syntax tree to include SQL query semantics. Based on it, each code pattern that requires exception handling together with the exception handling code to be inserted is represented as a transformation rule. We provide two alternatives to handle the exceptions: one is to handle the exceptions in conjunction with the built-in enforcement feature in Database Management System; the other is handling them without using the feature provided in Database Management System. Hence, two types of transformation rules are provided accordingly. 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.
Article Preview

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.

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).

Key Constraints

A key constraint dictates an attribute or a set of attributes that identifies exclusively each record in a table. A PK is a non-null-valued attribute which is unique to each individual distinct record.

Referential Constraints

A referential constraint establishes a relationship between two tables by identifying an attribute or a set of attributes in the referencing table that refers to an attribute or a set of attributes in the referenced table. An attribute defined as a Foreign Key (FK) in the referencing table should be defined as a PK in the corresponding referenced table. If a referencing row’s FK has no matching PK value in the referenced table, the referential constraint is violated.

Complete Article List

Search this Journal:
Reset
Open Access Articles
Volume 28: 4 Issues (2017): 3 Released, 1 Forthcoming
Volume 27: 4 Issues (2016)
Volume 26: 4 Issues (2015)
Volume 25: 4 Issues (2014)
Volume 24: 4 Issues (2013)
Volume 23: 4 Issues (2012)
Volume 22: 4 Issues (2011)
Volume 21: 4 Issues (2010)
Volume 20: 4 Issues (2009)
Volume 19: 4 Issues (2008)
Volume 18: 4 Issues (2007)
Volume 17: 4 Issues (2006)
Volume 16: 4 Issues (2005)
Volume 15: 4 Issues (2004)
Volume 14: 4 Issues (2003)
Volume 13: 4 Issues (2002)
Volume 12: 4 Issues (2001)
Volume 11: 4 Issues (2000)
Volume 10: 4 Issues (1999)
Volume 9: 4 Issues (1998)
Volume 8: 4 Issues (1997)
Volume 7: 4 Issues (1996)
Volume 6: 4 Issues (1995)
Volume 5: 4 Issues (1994)
Volume 4: 4 Issues (1993)
Volume 3: 4 Issues (1992)
Volume 2: 4 Issues (1991)
Volume 1: 2 Issues (1990)
View Complete Journal Contents Listing