Method for facilitating development and testing of relational database application software
View Patent ↗A method for allowing individual user entities access to tables in a database, such as a DB2 database, in an isolated manner, that is, without interfering with each other. For each original database table, DDL statements are employed to create a new table. Within the new table are defined the same columns that are defined in the original database table, as well as an additional column that includes a User Identification. A view is created having the same name and column definitions as the original database table, where the view selects only the rows in the new table in which the User Identification matches a particular user entity. For each particular user entity, access is allowed to the view for executing DML statements of the application program without modification of the DML statements of the application program other than for purposes of developing and testing functionality of the application program.
1. A method for facilitating the development and testing, by a plurality of individual user entities, of a relational database application program which includes a plurality of Data Manipulation Language statements, which runs under a database management system and which accesses original database tables organized as rows of defined columns, said method comprising, for each original database table:
employing Data Definition Language statements at least a first time
to create a corresponding new table within which are defined the same columns that are defined in the original database table, and which includes an additional column that is defined to include a User Identification, and
to define a view created having the same name and column definitions as the corresponding original database table, where the view Selects only the rows in the new table in which the User Identification matches a particular user entity; and
for each particular user entity, allowing access to the view created by a match of the User Identification with the particular user entity for executing Data Manipulation Language statements of the application program, including statements to insert rows, without modification of the statements of the application program other than for purposes of developing and testing functionality of the application program.
2. The method of claim 1 , which further comprises, when development and testing employing Data Manipulation Language statements of the application program have reached a desired stage of completion, for each original database table, removing the Data Definition Language statements which created the corresponding new table and defined the view having the same name and column definitions as the corresponding original database table, such that the application program can access all rows of the original database table without modification of the Data Manipulation Language statements of the application program.
3. The method of claim 1 , which further comprises, when development and testing employing Data Manipulation Language statements of the application program have reached a desired stage of completion, for each original database table, modifying the Data Definition Language statements which created the corresponding new table and defined the view having the same name and column definitions as the corresponding original database table by removing all reference to the User Identification such that access to the view is not limited to rows in the new Table where the User Identification matches a particular user entity, and such that the application program can access through the view all rows of the original database table without modification of the Data Manipulation Language statements of the application program.
4. The method of claim 1 , wherein:
the database management system creates an authorization identification for each user entity logging on; wherein
during the step of employing Data Definition Language statements at least a first time to create a corresponding new table, the additional column is defined as NOT NULL and to contain the authorization identification as a default value; and wherein
the database management system, when a statement to INSERT a row accesses a view derived from a table, stores defined default values in any columns of the row which are present in the table from which the view is derived but which are missing from the view.