Generic SQL and Clarification of behavior for Export operation for tables containing two or more multi-valued attributes

Hello been a bit , but I wanted to share some information around the MIM Generic SQL connector and Export operations.

The Generic SQL Connector technical reference found here

Download of the connector can be found here as well  fyi we just released an update today

Now the Export changes can have the following operations with exported attribute:

1. Value Add.

2. Value Delete.

Value add change has the following behavior:

1. A new row is inserted (with added value) into the table with multi-valued attributes if either of following is true:

a. table with the multi-valued attributes does not have any rows

b. multi-valued attribute column does not have any cell with the NULL value

2. Existing cell is updated if multi-valued attribute column has the cell with the NULL value

Value delete change from a multi-valued attribute has the following behavior:

1. Entire row is deleted if all other cells in this row have NULL values

2. Cell containing deleted value is set to NULL if any other cell in the same row has not NULL value.

Fig. 1 presents an example where a group has two multi-valued attributes: “owners” and “members”.

image

Fig. 1 – An example with two multi-valued attributes.

Let’s consider insert and delete operations for table from Fig 1 (all examples are against initial table look).

1. If it is necessary to insert an additional value into multi-valued attribute “members”, in this case the management agent adds a new row into the table. The new row will contain the new value in “members” field and the other fields will have NULL values.

image

Fig 1.1 – Adding an additional value into multi-valued attribute “members”.

2. If it is necessary to insert an additional value into multi-valued attribute “owners”, in this case the management agent adds value into row 3, replacing NULL value (via SQL update for row 3).

image

Fig 1.2 – Adding new value to multi-valued attribute “owners”.

3. If it is necessary to delete “Third” value from multi-valued attribute “members”, in this case management agent deletes the whole row which contains “Third” value, because all other multi-valued attributes have NULL values.

image

Fig 1.3 – Deleting “Third” value from multi-valued attribute “members”.

4. If it is necessary to delete “First” value from multi-valued attribute “members”, in this case management agent replaces deleted value with NULL in row 1 (via SQL update).

image

Fig 1.4 – Deleting “First” value from multi-valued attribute “members”

Recommendation: if database has few separate tables for each a multi-valued attribute, in this case it is recommended to unite all them into one view/table.

David Steadman has written 39 articles

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>