Re: Data Import

[Jonathon -- Improov]

Chris,

Yeah, MyISAM was... "difficult". Other than lack of referential integrity, transaction atomicity 
had to be especially handled. I don't know where MySQL will be today if not for InnoDB. I don't 
know where they'll be in future, since InnoDB license seems to be changing soon.

In relation to your question of MySQL database being consistent or not, it is. Unless OFBiz entity 
engine doesn't create the referential keys (which it does), the MySQL-based OFBiz database is 
consistent.

The hierarchical walk you mentioned seems like too much work, perhaps even computationally 
expensive. I would've preferred a dumb dump (same structure to same structure), complete with 
referential keys. I'm sure there are very largely similar SQL-standard features between MySQL and 
PostgreSQL.

A typical application migration (turn off old app, migrate CURRENT data and whatnot, turn on new 
app) must take less than 8 hours (overnight). All migration work must be scripted to run 
automatically, completely free of human error.

Should we enhance the data import utility, perhaps? You mentioned it can only take in a few(?) 
tables/files (XML) at a time?

Jonathon

Chris Howe wrote:
Jonathon,

That's interesting.  Until you just said that, it has never even
occurred to me to trust that the data being imported was consistent. 
When I first started playing with php and mysql a few years ago, I had
only used MyISAM tables, which doesn't (or at least didn't, perhaps
still doesn't) handle the enforcement of referential integrity.  So, I
guess that's where my bias lies.  

In regards to the hierarchy, it's already specified in the entity
model.  It just needs to be walked.  In any regards, I'll be playing
with this in the near term as it has several advantages and will help
me with several other ideas that deal with the loads of XML in OFBiz.  

--- Jonathon -- Improov <[EMAIL PROTECTED]> wrote:

Chris,

Usually, in MySQL (or MSSQL or many other RDBMSes), we disable the
foreign key constraints check 
first, then dump in all the data, then re-enable the key constraints.
That way, we don't have to 
figure out the top-down hierarchy of the database structure and
insert data from the top to the 
bottom.

Does the data import tool allow the above? It's usually just a single
flag to toggle on/off 
(foreign_key_checks for MySQL).

Jonathon

Chris Howe wrote:
The current approach is this
1.Export MySQL (or origin database) to XML files
2.Import the XML files in one at a time and either succeed or fail
the
entire file.

Because of the SAX parser you're limited to in best case scenario
to
what you can fit into memory (because of the data OFBiz's data
import
logic, you're limited to one file at a time, even if two or more
will
fit in memory).  Therefore you're guessing if referential integrity
is
maintained (exists) in subsequent XML data files.  

In the case of Postgres, once OFBiz creates the database schema,
postgres handles referential integrity constraints.  Because of
this,
it's not enough just to have the entity engine ignore its error on
referential integrity with a dummy key, the dummy key actually has
to
be written to the destination database(which in my experience did
not
happen, I didn't look any deeper into this as a solution because of
the
other remaining issues).  

By reading all of the XML data files into an XML database first,
children elements can be added to each record with error
information. 
You'll also be able to trace through referential integrity to
ensure
it's maintained in subsequent records and then actually import the
data
with the PK records going in first.  Additionally, you can test an
entire data set for importation, report back all the errors and let
the
user make adjustments as is needed.  It should be very interesting
and
fairly simple to implement as the solution is more logic based
instead
of code based.

It would be great if someone could review OFBIZ-851 and maybe add
it as
a specialpurpose app so that others can play with it and contribute
ideas.

--- Jonathon -- Improov <[EMAIL PROTECTED]> wrote:

Chris,

You mean go from MySQL to Xindice to PostgreSQL? Yeah, I know,
data
migration can be a pain, even 
without data-mapping efforts to go with it (ie, same structure
migrated to same structure).