Chris,
> you have to be certain that you data is clean. Depending on how old your
> data is in OFBiz, this may be unlikely.
You just reminded me that my existing data can still get "unclean" simply because new OFBiz
updates changes the data structure.
Adding another item to my SOP for "Importing OFBiz updates": Check changes to entity models, and
perform any necessary data-mapping and data-migration.
> I believe, MySQL in the default installation mode treats "ofbiz" and "OFBIZ"
> the same and so found related records as expected.
If you're talking about table names, I think the default installation on Windows treats user_login
and USER_LOGIN the same. But the default on Linux is totally case-sensitive.
If you're talking about database collation, there really isn't much in the way of "default". We
set our own. In general, I always opt for _cs (case sensitive), never for _ci. The default in
OFBiz's entityengine.xml is latin1_general_cs; I've been using that for all OFBiz installations.
> Postgres in the default installation mode does not treat the two as the same
> and your application will not work as expected (I'm dealing with the fallout
> of this at the moment).
Convert all affected keys to lower case, and tell all users about the change?
Jonathon
Chris Howe wrote:
Comments inline...
--- Jonathon -- Improov <[EMAIL PROTECTED]> wrote:
Chris, David,
Wow, this is getting complicated.
2 questions.
This data import tool, can it do a data dump with foreign_key_checks
turned off? I'd like to do a
data import from XML, but not have to do the graph walk.
If by this tool, you mean the one currently in OFBiz, it does it by
using a "insert dummy key" indicator. It's only recommended if you
know your data is clean. As I've mentioned previously, I tried using
this to go from MySQL to Postgres and it did not work. I did not look
into why. There are a multitude of reasons why this may not have
worked. 95% of those reasons are on this side of the keyboard as I've
touched the data myself, so it likely isn't clean.
Anybody knows of any tools to dump MySQL data (verbatim, with only
platform-specific translations)
into a PostgreSQL?
There are quite a number of solutions. A google search should find
them. I didn't see any free choices when I looked.
I've looked into such "walk the graph/hierarchy" thing when I did my
first data migration (not
even data mapping, just dumping same structure to same structure).
After spending a week to
finally "get it!", I was slapped for wasting my time. Somebody did a
"foreign_key_checks=0",
dumped the data into database, and set the flag back to "on". I'd
agree this is a tough nut to
crack; haven't found anyone in the IT industry (other than PhDs doing
database design theses?)
who'd want to bother cracking this.
That would have the same use scenario with the insert dummy key
approach, you have to be certain that you data is clean. Depending on
how old your data is in OFBiz, this may be unlikely. For instance,
before case sensitive logins were implemented as the default the case
in which the login was typed is the case that was stored in the various
entities that have a relationship to UserLogin. I believe, MySQL in
the default installation mode treats "ofbiz" and "OFBIZ" the same and
so found related records as expected. Postgres in the default
installation mode does not treat the two as the same and your
application will not work as expected (I'm dealing with the fallout of
this at the moment).
About highly normalized data. Yes, I agree there are loops. In the
real world, a Vendor can often
be a Customer of another Vendor, who is a Customer of the first
Vendor. Ah, you get the picture
(I'm getting a headache).
Because OFBiz is designed for the most part to be a generic data model,
this case scenario doesn't create a loop for referential integrity. In
this case, there are four entities of importance; RoleType, Party,
PartyRole and PartyRelationship. RoleType and Party do not rely on the
later two, so they get entered cleanly. PartyRole only depends on
RoleType and Party, and since all of those records are already in, it
gets entered cleanly. PartyRelationship relies on PartyRole and Party
and since all of those records are already in, they get entered
cleanly.
Jonathon
