2Pack can replicate Data with FK integrity

To resolve FK IDs in 2Pack Data transfer

Logged In: YES
user_id=867558
Originator: YES

The export/import XML process has the stub or frameset for making LookUpName for main record PK as the target DB will hit a PK constraint without it.

TODO
1) Make PK LookUpName ie. SO/C_Order_ID = 100000 > SO/DocumentNo = 5000
That shall happen during PackOut within 'create' method of DataElementHandler.java.

2) Make LookUpName obtain back ID in target DB
Also same java under 'DataRowElementHandler' methods.

There shall be same treatment of handling non-Names such as DocumentNo as the PK in Orders does not have Name/Value key.

Logged In: YES
user_id=867558
Originator: YES

After checking closely, the Update part was actually done before by others. Under public class DataElementHandler.create method (so i just add to check for DocumentNo too):
if (col_Name.equals("NAME") && rs.getObject("name") != null)
nameatts = ""+rs.getObject("name");
//red1 - capture PK other name for lookup also
if (col_Name.equals("DOCUMENTNO") && rs.getObject("DOCUMENTNO") != null)
nameatts = ""+rs.getObject("DocumentNo");
...
atts.addAttribute("","","name","CDATA",nameatts);

Then in PackIn routine (same class) i made the same check:
String d_rowname = atts.getValue("name");

// name can be null if there are keyXname attributes.
//red1 - Using DocumentNo also (done in CREATE routine at bottom)
String lookUpName = "Name";
if ("C_Order_ID".equals(atts.getValue("key1name")))
lookUpName = "DocumentNo";
if (!d_rowname.equals("")){
int id = get_ID(lookUpName, ctx, d_tablename, d_rowname);

Then a new problem came to mind:
//TODO red1 - new problem arises where child details i.e. OrderLines need to be updated, complex and so easiest may be to delete all first, then create.

Logged In: YES
user_id=867558
Originator: YES

I have a question here. Can we remove the check for Update target DB via PK ID? I think that is wrong and unsafe. Update check should be via Name, DocumentNo or other Values that are unique and not PK ID dependent.

The important preparation of 2Pack data is that their Name Values should be unique throughout.

I also trying to do a earlier Parent/Child delete before create instead of Update because again the IDs may not be used.

Logged In: YES
user_id=867558
Originator: YES

Attaching latest patch that resolves Update same record during Data PackIn. Name of patch is 2PackFKver2.patch

Also set always New instead of Update for Non LookUpName for KeyName as LookUp via PK ID is not sound.
File Added: 2PackFKver2.patch

This is the SF thread discussing the case and my progress with sample output - http://sourceforge.net/forum/forum.php?thread_id=1924178&forum_id=611158

alternative solutions for the same problem, have been used in projects. patch is created against current trunk.

Commited in Revision: 7988
http://adempiere.svn.sourceforge.net/adempiere/?rev=7988&view=rev

Still some enhancement needed for fuller working model.

File Added: DataElementHandler.java

Latest version of DataElementHandler

Committed in http://adempiere.svn.sourceforge.net/adempiere/?rev=8307&view=rev

PLS REVIEW FOR ALL DATA CHANGES CASES such as Financial Reports, PO, SO.

Heng Sin, Redhuan, I tested this with a simple data export in GardenWorld.

Test Case:

Create a packout just one line:
Type: Data
Table: C_NonBusinessDay
SQLStatement: SELECT * FROM C_NonBusinessDay WHERE AD_Client_ID=11

exported and the resulting file is this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<adempiereAD Name="holidays" Version="holidays" CompVer="all" DataBase="holidays" Description="holidays" creator="holidays" creatorcontact="holidays" createddate="2009-02-06 18:16:30" updateddate="2009-02-06 18:16:30" PackOutVer="005">
<data>
<dtable name="C_NonBusinessDay">
<drow key1name="Name" lookupkey1name="Independence Day" name="Independence Day" key2name="" lookupkey2name="">
<dcolumn name="C_NonBusinessDay_ID" class="ID" value="100"/>
<dcolumn name="AD_Org_ID" class="Table Direct" value="" sql="SELECT AD_Org_ID FROM AD_Org WHERE Value='HQ' AND AD_Org.AD_Client_ID IN ( @AD_Client_ID@ , 0 ) Order By AD_Org.AD_Client_ID Desc "/>
<dcolumn name="IsActive" class="Yes-No" value="Y"/>
<dcolumn name="Name" class="String" value="Independence Day"/>
<dcolumn name="Date1" class="Date" value="2002-07-04 00:00:00.0"/>
<dcolumn name="C_Calendar_ID" class="Table Direct" value="" sql="SELECT C_Calendar_ID FROM C_Calendar WHERE Name='GardenWorld Calendar' AND C_Calendar.AD_Client_ID IN ( @AD_Client_ID@ , 0 ) Order By C_Calendar.AD_Client_ID Desc "/>
</drow>
</dtable>
</data>
</adempiereAD>
-------------------------------------------

I have some issues with this approach:

1 - I think PackIn must be an option just for "System Administrator" - opening for "Tenant Administrator" by default open big security issues.
This could be easily fixed if PackIn process simply ask the AD_Client_ID to apply (as parameter).

Now, if [1] is fixed then please consider the next as a feature request:
2 - The approach of using @AD_Client_ID@ is good - but still incomplete. It could be extended a little more.
The problem is that calendar names are different for every company, so searching for the record "GardenWorld Calendar" will just work for GardenWorld again.

My guess to extend this would be (I know this is bigger):
- define parameters to be used in Data - in the example I would set as parameters the values for @AD_Client_ID@, @AD_Org_ID@ and @C_Calendar_ID@
We could define some attributes of the parameters as if they were records on AD_Process_Para, I mean, define a default, a reference, a dynamic validation, etc.

The parameters will be defined at the start of the PackOut.xml file.

Every time the Packout process find a column matching a parameter - then it puts as the value @Variable@ instead of the direct value.

Now, on Packin - (this is harder) we need to ask for the parameters and replace them properly on import.
The tricky part is that parameters must be asked not based in AD_Process_Para, but in the parameters section of the XML file.

Anyways - as I said - I would vote to keep this improvement as is currently - fixing the security problem - this is, just System Admin, ask AD_Client_ID as parameter of Packin.

Regards,

Carlos Ruiz

Reverted with revision 8491.
http://adempiere.svn.sourceforge.net/adempiere/?rev=8491&view=rev

Regards,

Carlos Ruiz

Heng Sin, I see you're working on 2pack enhancements with a different approach - can this be closed?

Regards,

Carlos Ruiz