User Tools

Site Tools


one-to-many_many-to-many

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

one-to-many_many-to-many [2013/07/25 21:00]
127.0.0.1 external edit
one-to-many_many-to-many [2013/07/31 11:59] (current)
admin
Line 5: Line 5:
 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'':​ 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'':​
  
-<box round 740px>{{:dep-emp-many-2-many.jpg?nolink&}}</​box|Changing DEPARTMENT and EMPLOYEE tables to a many-to-many relationship.>​+<box round 728px>{{ :m2m-sql.png?​nolink ​|}}</​box|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''​. ​ 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''​. ​
Line 16: Line 16:
 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: 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:
  
-<box round 580px>{{:dep-emp-many-2-many-export.jpg?nolink&}}</​box|Console output on Export of many-to-many table>+<box round 784px>{{ :m2m-export-console.png?​nolink ​|}}</​box|Console output on Export of many-to-many table>
  
-Lure creates a new file ''​DEPARTMENT_EMPLOYEE.table''​ 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.+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'':​ The patch file contains the correct statement to drop the column from table ''​EMPLOYEE'':​
  
-<box round 820px>{{:employee-patch.jpg?nolink&}}</​box|Patch file to update table EMPLOYEE table>+<box round 784px>{{ :m2m-drop-column-patch.png?​nolink ​|}}</​box|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''​ 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:+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:
  
-<box round 850px>{{:employee-patch-updated.jpg?nolink&}}</​box|Updated patch file to populate table DEPARTMENT_EMPLOYEE correctly.>​+<box round 784px>{{ :m2m-patch-edited.png?​nolink ​|}}</​box|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: 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:
  
-<box round 590px>{{:dep-emp-many-2-many-deploy.jpg?nolink&}}</​box|Console output for Patch-Test of many-to-many table.>+<box round 784px>{{ :m2m-patch-edited-console.png?​nolink ​|}}</​box|Console output for Patch-Test of many-to-many table.>
  
 __**Notes:​**__ __**Notes:​**__
  
-  - 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.patch.r107''​ contains a statement that affects table ''​DEPARTMENT_EMPLOYEE ''​ +  - 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 ''​ 
-  - 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 ''​107''​. +  - 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''​. 
-  - 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.patch.r106''​ will suffice. +  - 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