User Tools

Site Tools


one-to-many_many-to-many

One-To-Many => Many-To-Many

The following example shows how to use Lure when changing the relationship between two tables from a one-to-many relationship to a many-to-many relationship.

Consider the two tables DEPARTMENT and EMPLOYEE where one DEPARTMENT row is related to many EMPLOYEE rows. The table EMPLOYEE has a foreign key column with name DEPT_FK:

Changing DEPARTMENT and EMPLOYEE tables to a many-to-many relationship.

The many-to-many-relationship will be represented by a new table with name DEPARTMENT_EMPLOYEE. This table is created and initialized from the data representing the existing relationship, i.e. the two columns ID and DEPT_FK in table EMPLOYEE.

Changing the relationship requires two DDL statements:

  1. Creating the new table DEPARTMENT_EMPLOYEE and populating it with the existing relationships.
  2. Dropping the foreign key column from the “many” table (i.e. EMPLOYEE).

These two statements are shown in the figure above in the right hand panel. Once these statements have been executed you can use Lure to Export the changes to source files:

Console output on Export of many-to-many table

Lure creates a new file DEPARTMENT_EMPLOYEE.table.lsql for the new table that was created. Lure updates the file EMPLOYEE.table according to the dropped column and lastly creates a patch file to drop the column.

The patch file contains the correct statement to drop the column from table EMPLOYEE:

Patch file to update table EMPLOYEE table

However, there is problem with the code as exported in this way. During Deploy Lure will use the statement in file DEPARTMENT_EMPLOYEE.table.lsql to create the new many-to-many table but this will not correctly populate the table with the existing relationships data. This situation can be corrected by adding the ”CREATE TABLE AS SELECT” statement to the generated patch file as follows:

Updated patch file to populate table DEPARTMENT_EMPLOYEE correctly.

By adding the ”CREATE TABLE” statement to the patch file before the ”DROP COLUMN” statement we ensure that the relationship information is populated in the new table before this information is removed from the existing EMPLOYEE table. Now test the patch file by running Lure Patch Test which yields the following output:

Console output for Patch-Test of many-to-many table.

Notes:

  1. This example illustrates a case where it is necessary to have DDL statements that affect a table other than the table matching the name of the patch file. Specifically the patch file EMPLOYEE.table.lsql.r69.lpatch-pending contains a statement that affects table DEPARTMENT_EMPLOYEE
  2. Lure only installs the patch file if all referenced tables (in this case tables DEPARTMENT_EMPLOYEE and EMPLOYEE) source code match the corresponding source as stored in the version control system for the patch version number, in this case revision 69.
  3. An alternative way to ensure that table DEPARTMENT_EMPLOYEE is created and populated before column DEPT_FK is dropped, is to put the “CREATE TABLE AS SELECT” statement in the patch file for table DEPARTMENT_EMPLOYEE (see CREATE TABLE AS SELECT...) but to make sure that the revision for this patch file is less than the revision of the EMPLOYEE patch file. Specifically in this case a patch file name of DEPARTMENT_EMPLOYEE.table.lsql.r68.lpatch will suffice.
one-to-many_many-to-many.txt · Last modified: 2013/07/31 11:59 by admin

Page Tools