Mapping Derby Sample Data Foreign Keys

[stimits]

Hi,
 
There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for demo data. I'm trying to learn to load this data manually using SQL queries, and thus making a dependency list of tables with foreign key requirements (or even references to other tables which are allowed to be null). Basically, no table referencing another table's data can be loaded without first loading that parent table. With 861 tables and some tables having more than one foreign key I wanted to automate this with a SQL query (I'm using SQuirrel-sqL to explore now). As such I'm looking at system tables describing constraints and keys.
 
Through system tables I can find a list of tables and the ID of constraints which are foreign keys. However, I seem to be losing the battle to find the name of the foreign table and foreign column name to which the constraint points (only the table with the originating constraint is easy to find and automate a listing for). I see this may be possible to find through JDBC; SQuirrel-sqL itself shows imported and exported keys with no apparent means to export the information. Can anyone tell me if it is possible to use an ordinary SQL query to find all foreign keys of a table and print the list of the child table and the parent foreign table.column? If I can do this I can get around the missing WebPOS setup functionality and edit/load SQL directly (I'm using PostegreSQL so I can't edit a Derby database directly...it needs to be copied over to PostgresSQL first in the proper order).
 
If you think I need to write a JDBC app instead to map table load order dependencies, please let me know. I don't believe there is any other way to use the WebPOS without doing it this way. I hate to think I may have to go through all of those tables and hand write a spreadsheet with the dependencies.
 
Thanks!

[taher]

Hi,

I might be wrong, but I think you're trying to do this the hard way. The
problem is really simple in that you have some missing data that is not
loading correctly. To fix this issue I would suggest the following steps:

1- Copy the XML data file contents
2- Paste it into the XML data import window (i.e.
https://localhost:8443/webtools/control/EntityImport)
3- Click Import Text
4- Observe error message (it will tell you which foreign key is violated
and how)
5- fix the data accordingly
6- Repeat 1 to 5 as needed

That would be much faster and easier than going down to the SQL level to
try to investigate. I recommend that you use the entity engine to your
advantage instead of bypassing it completely.

HTH

On Wed, Jun 7, 2017 at 1:42 AM, <[hidden email]> wrote:

> Hi,
>
> There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for
> demo data. I'm trying to learn to load this data manually using SQL
> queries, and thus making a dependency list of tables with foreign key
> requirements (or even references to other tables which are allowed to be
> null). Basically, no table referencing another table's data can be loaded
> without first loading that parent table. With 861 tables and some tables
> having more than one foreign key I wanted to automate this with a SQL query
> (I'm using SQuirrel-sqL to explore now). As such I'm looking at system
> tables describing constraints and keys.
>
> Through system tables I can find a list of tables and the ID of
> constraints which are foreign keys. However, I seem to be losing the battle
> to find the name of the foreign table and foreign column name to which the
> constraint points (only the table with the originating constraint is easy
> to find and automate a listing for). I see this may be possible to find
> through JDBC; SQuirrel-sqL itself shows imported and exported keys with no
> apparent means to export the information. Can anyone tell me if it is
> possible to use an ordinary SQL query to find all foreign keys of a table
> and print the list of the child table and the parent foreign table.column?
> If I can do this I can get around the missing WebPOS setup functionality
> and edit/load SQL directly (I'm using PostegreSQL so I can't edit a Derby
> database directly...it needs to be copied over to PostgresSQL first in the
> proper order).
>
> If you think I need to write a JDBC app instead to map table load order
> dependencies, please let me know. I don't believe there is any other way to
> use the WebPOS without doing it this way. I hate to think I may have to go
> through all of those tables and hand write a spreadsheet with the
> dependencies.
>
> Thanks!

[Paul Foxworthy]

Hi stimits,

I agree with Taher about the most productive way to solve your problem.

For an overall understanding of the data model, see
https://cwiki.apache.org/confluence/display/OFBIZ/Data+Model . There's a
mention there of the Data Model Resource Book by Len Silverston, which
inspired much of OFBiz's data model, and links to diagrams. The diagrams
are a few years old but still substantially correct and relevant.

Cheers

Paul Foxworthy

On 8 June 2017 at 06:43, Taher Alkhateeb <[hidden email]> wrote:

> Hi,
>
> I might be wrong, but I think you're trying to do this the hard way. The
> problem is really simple in that you have some missing data that is not
> loading correctly. To fix this issue I would suggest the following steps:
>
> 1- Copy the XML data file contents
> 2- Paste it into the XML data import window (i.e.
> https://localhost:8443/webtools/control/EntityImport)
> 3- Click Import Text
> 4- Observe error message (it will tell you which foreign key is violated
> and how)
> 5- fix the data accordingly
> 6- Repeat 1 to 5 as needed
>
> That would be much faster and easier than going down to the SQL level to
> try to investigate. I recommend that you use the entity engine to your
> advantage instead of bypassing it completely.
>
> HTH
>
> On Wed, Jun 7, 2017 at 1:42 AM, <[hidden email]> wrote:
>
> > Hi,
> >
> > There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for
> > demo data. I'm trying to learn to load this data manually using SQL
> > queries, and thus making a dependency list of tables with foreign key
> > requirements (or even references to other tables which are allowed to be
> > null). Basically, no table referencing another table's data can be loaded
> > without first loading that parent table. With 861 tables and some tables
> > having more than one foreign key I wanted to automate this with a SQL
> query
> > (I'm using SQuirrel-sqL to explore now). As such I'm looking at system
> > tables describing constraints and keys.
> >
> > Through system tables I can find a list of tables and the ID of
> > constraints which are foreign keys. However, I seem to be losing the
> battle
> > to find the name of the foreign table and foreign column name to which
> the
> > constraint points (only the table with the originating constraint is easy
> > to find and automate a listing for). I see this may be possible to find
> > through JDBC; SQuirrel-sqL itself shows imported and exported keys with
> no
> > apparent means to export the information. Can anyone tell me if it is
> > possible to use an ordinary SQL query to find all foreign keys of a table
> > and print the list of the child table and the parent foreign
> table.column?
> > If I can do this I can get around the missing WebPOS setup functionality
> > and edit/load SQL directly (I'm using PostegreSQL so I can't edit a Derby
> > database directly...it needs to be copied over to PostgresSQL first in
> the
> > proper order).
> >
> > If you think I need to write a JDBC app instead to map table load order
> > dependencies, please let me know. I don't believe there is any other way
> to
> > use the WebPOS without doing it this way. I hate to think I may have to
> go
> > through all of those tables and hand write a spreadsheet with the
> > dependencies.
> >
> > Thanks!
>

--
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: [phone]
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]

[stimits]

Hi,
 
I do have that book which explains concepts, but currently I am trying to make the existing system's WebPOS work...versus extending, customizing, or adding to the existing system. The book has no concept of how the OfBiz interface itself is arranged. I need a working WebPOS, which means I need to be able to enter data for POS terminals and stores, but there is no web interface for this. Thus the route of trying to edit sample data and transfer it over to my PostgreSQL database instead of Derby.
 
The OfBizDatamodelBook charts are indeed something like what I want, but I need it in a form where I can use tools to automate tasks based on query of an existing Derby database and copy to PostgreSQL. Someone made a very nice chart of account relationships, but the PDF is for human-readable form and may not reflect changes and other details of the actual database (for example the chart does not know about automatic sequences for generating various keys...a database query may be able to take this into account and simple copy of data from one database to another results in an error).
 
In terms of using the web tools with copy and paste to load data I have tried this unsuccessfully. The time it takes to make attempts is extreme and error messages do not necessarily point out what needs to be loaded for a given failure to be fixed. Here are some points about the web tool copy and paste method:
 
1. Many XML documents have a root tag of "<entity-engine-xml>". This requires editing the XML files and removing the "<entity-engine-xml>" (you can't paste "<entity-engine-xml>" within "<entity-engine-xml>"). This isn't too bad of an issue, but it slows things down (especially if it is a large file and you are skipping open/close tags via scrolling around in an editor). Sometimes java errors are so long that the pop-up showing them is not capable of displaying on a large monitor (though log files do not have this limitation).
 
2. Some tables (many) have a foreign key and produce an error if the foreign table does not already have an entry. There are also cases where a column in a foreign table is copied during insert, but the value is allowed to be null...I worry that no error would be generated if the foreign key is there but the data for the null column has not yet been populated. Those columns pulling data from another table where null is allowed may change whether the data is valid but will never produce an error (copy by value). I see a SQL query as a programmatic way to be certain columns which can be null will always be loaded from a parent table which has had the maximum opportunity to be populated before using that parent.
 
3. In some cases I am going to be copying from Derby to PostgreSQL. SQuirrel-sqL has a tool for this, but the tool does not deal well with keys which have been generated using different methods between the different databases. This is a particular case of the above mentioned need to be able to see generated values.
 
I have tried for a long time, with many hours per day, to come up with a simpler solution for getting WebPOS working (including mouse copy and paste, then command line XML file loads). Then I tried for quite some time to be able to get the Derby demo data into PostgreSQL using simpler tools and methods...once again without success. I am positive that if I have detailed access to both the demo database under Derby and to the database under PostgreSQL that I can guarantee certain qualities during copy. Once I can do that I can investigate editing the data for replacing some demo data with a real world store. I do not yet know how to create the missing interface for populating WebPOS, so I am limited to this route...once I can do this and understand it I can work on modifying or customizing OfBiz to include an interface for avoiding all of the WebPOS issues.
 
I just wish that part of the unit testing before OfBiz releases included checking if demo data can be loaded by an end user without direct database manipulation (this would of course be a monumentally difficult task to see if every bit of demo data could be hand entered...someone would have to actually enter it). The written instructions of how the unit test would be performed would serve as an incredibly valuable way of understanding how to set up a new business. Right now I'm basically reverse engineering this when all I really wanted to do was set up the existing features and get it running (modification could come later).
 
So I'm back to finding ways to map table relationships in Derby...it looks like a simple SQL query won't be possible, I'm going to have to use the JDBC interface.
 
Thanks!
 
----- Original Message -----From: Paul Foxworthy <[hidden email]>To: [hidden email]: Thu, 08 Jun 2017 07:31:52 -0000 (UTC)Subject: Re: Mapping Derby Sample Data Foreign Keys

Hi stimits,

I agree with Taher about the most productive way to solve your problem.

For an overall understanding of the data model, seehttps://cwiki.apache.org/confluence/display/OFBIZ/Data+Model . There's amention there of the Data Model Resource Book by Len Silverston, whichinspired much of OFBiz's data model, and links to diagrams. The diagramsare a few years old but still substantially correct and relevant.

Cheers

Paul Foxworthy

On 8 June 2017 at 06:43, Taher Alkhateeb <[hidden email]> wrote:

> Hi,>> I might be wrong, but I think you're trying to do this the hard way. The> problem is really simple in that you have some missing data that is not> loading correctly. To fix this issue I would suggest the following steps:>> 1- Copy the XML data file contents> 2- Paste it into the XML data import window (i.e.> https://localhost:8443/webtools/control/EntityImport)> 3- Click Import Text> 4- Observe error message (it will tell you which foreign key is violated> and how)> 5- fix the data accordingly> 6- Repeat 1 to 5 as needed>> That would be much faster and easier than going down to the SQL level to> try to investigate. I recommend that you use the entity engine to your> advantage instead of bypassing it completely.>> HTH>> On Wed, Jun 7, 2017 at 1:42 AM, <[hidden email]> wrote:>> > Hi,> >> > There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for> > demo data. I'm trying to learn to load this data manually using SQL> > queries, and thus making a dependency list of tables with foreign key> > requirements (or even references to other tables which are allowed to be> > null). Basically, no table referencing another table's data can be loaded> > without first loading that parent table. With 861 tables and some tables> > having more than one foreign key I wanted to automate this with a SQL> query> > (I'm using SQuirrel-sqL to explore now). As such I'm looking at system> > tables describing constraints and keys.> >> > Through system tables I can find a list of tables and the ID of> > constraints which are foreign keys. However, I seem to be losing the> battle> > to find the name of the foreign table and foreign column name to which> the> > constraint points (only the table with the originating constraint is easy> > to find and automate a listing for). I see this may be possible to find> > through JDBC; SQuirrel-sqL itself shows imported and exported keys with> no> > apparent means to export the information. Can anyone tell me if it is> > possible to use an ordinary SQL query to find all foreign keys of a table> > and print the list of the child table and the parent foreign> table.column?> > If I can do this I can get around the missing WebPOS setup functionality> > and edit/load SQL directly (I'm using PostegreSQL so I can't edit a Derby> > database directly...it needs to be copied over to PostgresSQL first in> the> > proper order).> >> > If you think I need to write a JDBC app instead to map table load order> > dependencies, please let me know. I don't believe there is any other way> to> > use the WebPOS without doing it this way. I hate to think I may have to> go> > through all of those tables and hand write a spreadsheet with the> > dependencies.> >> > Thanks!>



-- Coherent Software Australia Pty LtdPO Box 2773Cheltenham Vic 3192Australia

Phone: [phone]Web: http://www.coherentsoftware.com.au/Email: [hidden email]

[Michael Brohl-3]

Why don't you just configure OFBiz to use your Postgres database and load the demo data the same way you did with the embedded Derby database?

Your approach seems to be overly complicated to me (or I am missing something).

> Am 08.06.2017 um 23:08 schrieb [hidden email]:
>
> Hi,
>
> I do have that book which explains concepts, but currently I am trying to make the existing system's WebPOS work...versus extending, customizing, or adding to the existing system. The book has no concept of how the OfBiz interface itself is arranged. I need a working WebPOS, which means I need to be able to enter data for POS terminals and stores, but there is no web interface for this. Thus the route of trying to edit sample data and transfer it over to my PostgreSQL database instead of Derby.
>
> The OfBizDatamodelBook charts are indeed something like what I want, but I need it in a form where I can use tools to automate tasks based on query of an existing Derby database and copy to PostgreSQL. Someone made a very nice chart of account relationships, but the PDF is for human-readable form and may not reflect changes and other details of the actual database (for example the chart does not know about automatic sequences for generating various keys...a database query may be able to take this into account and simple copy of data from one database to another results in an error).
>
> In terms of using the web tools with copy and paste to load data I have tried this unsuccessfully. The time it takes to make attempts is extreme and error messages do not necessarily point out what needs to be loaded for a given failure to be fixed. Here are some points about the web tool copy and paste method:
>
> 1. Many XML documents have a root tag of "<entity-engine-xml>". This requires editing the XML files and removing the "<entity-engine-xml>" (you can't paste "<entity-engine-xml>" within "<entity-engine-xml>"). This isn't too bad of an issue, but it slows things down (especially if it is a large file and you are skipping open/close tags via scrolling around in an editor). Sometimes java errors are so long that the pop-up showing them is not capable of displaying on a large monitor (though log files do not have this limitation).
>
> 2. Some tables (many) have a foreign key and produce an error if the foreign table does not already have an entry. There are also cases where a column in a foreign table is copied during insert, but the value is allowed to be null...I worry that no error would be generated if the foreign key is there but the data for the null column has not yet been populated. Those columns pulling data from another table where null is allowed may change whether the data is valid but will never produce an error (copy by value). I see a SQL query as a programmatic way to be certain columns which can be null will always be loaded from a parent table which has had the maximum opportunity to be populated before using that parent.
>
> 3. In some cases I am going to be copying from Derby to PostgreSQL. SQuirrel-sqL has a tool for this, but the tool does not deal well with keys which have been generated using different methods between the different databases. This is a particular case of the above mentioned need to be able to see generated values.
>
> I have tried for a long time, with many hours per day, to come up with a simpler solution for getting WebPOS working (including mouse copy and paste, then command line XML file loads). Then I tried for quite some time to be able to get the Derby demo data into PostgreSQL using simpler tools and methods...once again without success. I am positive that if I have detailed access to both the demo database under Derby and to the database under PostgreSQL that I can guarantee certain qualities during copy. Once I can do that I can investigate editing the data for replacing some demo data with a real world store. I do not yet know how to create the missing interface for populating WebPOS, so I am limited to this route...once I can do this and understand it I can work on modifying or customizing OfBiz to include an interface for avoiding all of the WebPOS issues.
>
> I just wish that part of the unit testing before OfBiz releases included checking if demo data can be loaded by an end user without direct database manipulation (this would of course be a monumentally difficult task to see if every bit of demo data could be hand entered...someone would have to actually enter it). The written instructions of how the unit test would be performed would serve as an incredibly valuable way of understanding how to set up a new business. Right now I'm basically reverse engineering this when all I really wanted to do was set up the existing features and get it running (modification could come later).
>
> So I'm back to finding ways to map table relationships in Derby...it looks like a simple SQL query won't be possible, I'm going to have to use the JDBC interface.
>
> Thanks!
>
> ----- Original Message -----From: Paul Foxworthy <[hidden email]>To: [hidden email]: Thu, 08 Jun 2017 07:31:52 -0000 (UTC)Subject: Re: Mapping Derby Sample Data Foreign Keys
>
> Hi stimits,
>
> I agree with Taher about the most productive way to solve your problem.
>
> For an overall understanding of the data model, seehttps://cwiki.apache.org/confluence/display/OFBIZ/Data+Model . There's amention there of the Data Model Resource Book by Len Silverston, whichinspired much of OFBiz's data model, and links to diagrams. The diagramsare a few years old but still substantially correct and relevant.
>
> Cheers
>
> Paul Foxworthy
>
>> On 8 June 2017 at 06:43, Taher Alkhateeb <[hidden email]> wrote:
>>
>> Hi,>> I might be wrong, but I think you're trying to do this the hard way. The> problem is really simple in that you have some missing data that is not> loading correctly. To fix this issue I would suggest the following steps:>> 1- Copy the XML data file contents> 2- Paste it into the XML data import window (i.e.> https://localhost:8443/webtools/control/EntityImport)> 3- Click Import Text> 4- Observe error message (it will tell you which foreign key is violated> and how)> 5- fix the data accordingly> 6- Repeat 1 to 5 as needed>> That would be much faster and easier than going down to the SQL level to> try to investigate. I recommend that you use the entity engine to your> advantage instead of bypassing it completely.>> HTH>> On Wed, Jun 7, 2017 at 1:42 AM, <[hidden email]> wrote:>> > Hi,> >> > There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for> > demo data. I'm trying to learn to load this data manually using SQL> > queries, and thus making a dependency list of tables with foreign key> > requirements (or even references to other tables which are allowed to be> > null). Basically, no table referencing another table's data can be loaded> > without first loading that parent table. With 861 tables and some tables> > having more than one foreign key I wanted to automate this with a SQL> query> > (I'm using SQuirrel-sqL to explore now). As such I'm looking at system> > tables describing constraints and keys.> >> > Through system tables I can find a list of tables and the ID of> > constraints which are foreign keys. However, I seem to be losing the> battle> > to find the name of the foreign table and foreign column name to which> the> > constraint points (only the table with the originating constraint is easy> > to find and automate a listing for). I see this may be possible to find> > through JDBC; SQuirrel-sqL itself shows imported and exported keys with> no> > apparent means to export the information. Can anyone tell me if it is> > possible to use an ordinary SQL query to find all foreign keys of a table> > and print the list of the child table and the parent foreign> table.column?> > If I can do this I can get around the missing WebPOS setup functionality> > and edit/load SQL directly (I'm using PostegreSQL so I can't edit a Derby> > database directly...it needs to be copied over to PostgresSQL first in> the> > proper order).> >> > If you think I need to write a JDBC app instead to map table load order> > dependencies, please let me know. I don't believe there is any other way> to> > use the WebPOS without doing it this way. I hate to think I may have to> go> > through all of those tables and hand write a spreadsheet with the> > dependencies.> >> > Thanks!>
>
>
>
> -- Coherent Software Australia Pty LtdPO Box 2773Cheltenham Vic 3192Australia
>
> Phone: [phone]Web: http://www.coherentsoftware.com.au/Email: [hidden email]

[Mike Z]

Hey stimits.  Are you finding that the demo webpos for 16.x works for you?
The reason I ask is that I would assume a properly functioning POS would
need a lot of supporting stuff, like a chart of accounts, payment accounts,
etc.  I tried it (demo), and it is not very intuitive as is.

If you REALLY want to capture all the SQL that postgresql sees, you can
simply set "log_statement = mod', and it will log all of the SQL, but going
this route is not recommended.  Instead, you can create a very simple
hot-deploy component that can load all of the stuff you need after seed,
and seed-initial.  Here is a sample:

root@server:/opt/ofbiz/hot-deploy# find my_data/
my_data/
my_data/ofbiz-component.xml
my_data/data
my_data/data/16240_shop_StandardCostingData.xml
my_data/data/16120_shop_RelatedData.xml
my_data/data/16020_shop_CatalogData.xml
---etc...

Consists of ONE "ofbiz-component.xml" file, a directory called "data", and
the .xml files you want to load, preferably named to create order.  That's
it.

The "data" directory has the xml files to load.  I keep them named by a
consistent 5 digit number where if you list it's contents, they are always
in the exact same loading sequence.  The key item is the
ofbiz-component.xml file, which lists the items to load, which is basically
the same order as the files... Example:

<?xml version="1.0" encoding="UTF-8"?>
<ofbiz-component name="my_data"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="
http://ofbiz.apache.org/dtds/ofbiz-component.xsd">
    <!-- define resource loaders; most common is to use the component
resource loader -->
    <resource-loader name="main" type="component"/>
    <entity-resource type="data" reader-name="my_data" loader="main"
location="data/16000_shop_StoreData.xml"/>
    <entity-resource type="data" reader-name="my_data" loader="main"
location="data/16010_shop_RentalStoreData.xml"/>
    <entity-resource type="data" reader-name="my_data" loader="main"
location="data/16020_shop_CatalogData.xml"/>
     ... ETC... More files
</ofbiz-component>

Notice the line: ofbiz-component name="my_data" and "reader-name"... That
name (my_data) is the name used to load (and re-load as necessary) all of
the xml files in the "data" directory.  For instance:

./ant load-readers "-Ddata-readers=seed,seed-initial" (this is run ONCE)
...then...
./ant load-readers "-Ddata-readers=my_data" (look for ERRORS, fix .xml as
required)
./ant load-readers "-Ddata-readers=my_data" (RERUN untill all loading
errors are gone)

Hope this helps.

On Thu, Jun 8, 2017 at 2:37 PM, Michael Brohl <[hidden email]>
wrote:

> Why don't you just configure OFBiz to use your Postgres database and load
> the demo data the same way you did with the embedded Derby database?
>
> Your approach seems to be overly complicated to me (or I am missing
> something).
>
>
>
> > Am 08.06.2017 um 23:08 schrieb [hidden email]:
> >
> > Hi,
> >
> > I do have that book which explains concepts, but currently I am trying
> to make the existing system's WebPOS work...versus extending, customizing,
> or adding to the existing system. The book has no concept of how the OfBiz
> interface itself is arranged. I need a working WebPOS, which means I need
> to be able to enter data for POS terminals and stores, but there is no web
> interface for this. Thus the route of trying to edit sample data and
> transfer it over to my PostgreSQL database instead of Derby.
> >
> > The OfBizDatamodelBook charts are indeed something like what I want, but
> I need it in a form where I can use tools to automate tasks based on query
> of an existing Derby database and copy to PostgreSQL. Someone made a very
> nice chart of account relationships, but the PDF is for human-readable form
> and may not reflect changes and other details of the actual database (for
> example the chart does not know about automatic sequences for generating
> various keys...a database query may be able to take this into account and
> simple copy of data from one database to another results in an error).
> >
> > In terms of using the web tools with copy and paste to load data I have
> tried this unsuccessfully. The time it takes to make attempts is extreme
> and error messages do not necessarily point out what needs to be loaded for
> a given failure to be fixed. Here are some points about the web tool copy
> and paste method:
> >
> > 1. Many XML documents have a root tag of "<entity-engine-xml>". This
> requires editing the XML files and removing the "<entity-engine-xml>" (you
> can't paste "<entity-engine-xml>" within "<entity-engine-xml>"). This isn't
> too bad of an issue, but it slows things down (especially if it is a large
> file and you are skipping open/close tags via scrolling around in an
> editor). Sometimes java errors are so long that the pop-up showing them is
> not capable of displaying on a large monitor (though log files do not have
> this limitation).
> >
> > 2. Some tables (many) have a foreign key and produce an error if the
> foreign table does not already have an entry. There are also cases where a
> column in a foreign table is copied during insert, but the value is allowed
> to be null...I worry that no error would be generated if the foreign key is
> there but the data for the null column has not yet been populated. Those
> columns pulling data from another table where null is allowed may change
> whether the data is valid but will never produce an error (copy by value).
> I see a SQL query as a programmatic way to be certain columns which can be
> null will always be loaded from a parent table which has had the maximum
> opportunity to be populated before using that parent.
> >
> > 3. In some cases I am going to be copying from Derby to PostgreSQL.
> SQuirrel-sqL has a tool for this, but the tool does not deal well with keys
> which have been generated using different methods between the different
> databases. This is a particular case of the above mentioned need to be able
> to see generated values.
> >
> > I have tried for a long time, with many hours per day, to come up with a
> simpler solution for getting WebPOS working (including mouse copy and
> paste, then command line XML file loads). Then I tried for quite some time
> to be able to get the Derby demo data into PostgreSQL using simpler tools
> and methods...once again without success. I am positive that if I have
> detailed access to both the demo database under Derby and to the database
> under PostgreSQL that I can guarantee certain qualities during copy. Once I
> can do that I can investigate editing the data for replacing some demo data
> with a real world store. I do not yet know how to create the missing
> interface for populating WebPOS, so I am limited to this route...once I can
> do this and understand it I can work on modifying or customizing OfBiz to
> include an interface for avoiding all of the WebPOS issues.
> >
> > I just wish that part of the unit testing before OfBiz releases included
> checking if demo data can be loaded by an end user without direct database
> manipulation (this would of course be a monumentally difficult task to see
> if every bit of demo data could be hand entered...someone would have to
> actually enter it). The written instructions of how the unit test would be
> performed would serve as an incredibly valuable way of understanding how to
> set up a new business. Right now I'm basically reverse engineering this
> when all I really wanted to do was set up the existing features and get it
> running (modification could come later).
> >
> > So I'm back to finding ways to map table relationships in Derby...it
> looks like a simple SQL query won't be possible, I'm going to have to use
> the JDBC interface.
> >
> > Thanks!
> >
> > ----- Original Message -----From: Paul Foxworthy <[hidden email]>To:
> [hidden email]: Thu, 08 Jun 2017 07:31:52 -0000 (UTC)Subject:
> Re: Mapping Derby Sample Data Foreign Keys
> >
> > Hi stimits,
> >
> > I agree with Taher about the most productive way to solve your problem.
> >
> > For an overall understanding of the data model, seehttps://
> cwiki.apache.org/confluence/display/OFBIZ/Data+Model . There's amention
> there of the Data Model Resource Book by Len Silverston, whichinspired much
> of OFBiz's data model, and links to diagrams. The diagramsare a few years
> old but still substantially correct and relevant.
> >
> > Cheers
> >
> > Paul Foxworthy
> >
> >> On 8 June 2017 at 06:43, Taher Alkhateeb <[hidden email]>
> wrote:
> >>
> >> Hi,>> I might be wrong, but I think you're trying to do this the hard
> way. The> problem is really simple in that you have some missing data that
> is not> loading correctly. To fix this issue I would suggest the following
> steps:>> 1- Copy the XML data file contents> 2- Paste it into the XML data
> import window (i.e.> https://localhost:8443/webtools/control/EntityImport)>
> 3- Click Import Text> 4- Observe error message (it will tell you which
> foreign key is violated> and how)> 5- fix the data accordingly> 6- Repeat 1
> to 5 as needed>> That would be much faster and easier than going down to
> the SQL level to> try to investigate. I recommend that you use the entity
> engine to your> advantage instead of bypassing it completely.>> HTH>> On
> Wed, Jun 7, 2017 at 1:42 AM, <[hidden email]> wrote:>> > Hi,> >> >
> There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for>
> > demo data. I'm trying to learn to load this data manually using SQL> >
> queries, and thus making a dependency list of tables with foreign key> >
> requirements (or even references to other tables which are allowed to be> >
> null). Basically, no table referencing another table's data can be loaded>
> > without first loading that parent table. With 861 tables and some tables>
> > having more than one foreign key I wanted to automate this with a SQL>
> query> > (I'm using SQuirrel-sqL to explore now). As such I'm looking at
> system> > tables describing constraints and keys.> >> > Through system
> tables I can find a list of tables and the ID of> > constraints which are
> foreign keys. However, I seem to be losing the> battle> > to find the name
> of the foreign table and foreign column name to which> the> > constraint
> points (only the table with the originating constraint is easy> > to find
> and automate a listing for). I see this may be possible to find> > through
> JDBC; SQuirrel-sqL itself shows imported and exported keys with> no> >
> apparent means to export the information. Can anyone tell me if it is> >
> possible to use an ordinary SQL query to find all foreign keys of a table>
> > and print the list of the child table and the parent foreign>
> table.column?> > If I can do this I can get around the missing WebPOS setup
> functionality> > and edit/load SQL directly (I'm using PostegreSQL so I
> can't edit a Derby> > database directly...it needs to be copied over to
> PostgresSQL first in> the> > proper order).> >> > If you think I need to
> write a JDBC app instead to map table load order> > dependencies, please
> let me know. I don't believe there is any other way> to> > use the WebPOS
> without doing it this way. I hate to think I may have to> go> > through all
> of those tables and hand write a spreadsheet with the> > dependencies.> >>
> > Thanks!>
> >
> >
> >
> > -- Coherent Software Australia Pty LtdPO Box 2773Cheltenham Vic
> 3192Australia
> >
> > Phone: [phone]Web: http://www.coherentsoftware.com.au/Email:
> [hidden email]
>

[stimits]

Hi,
 
I agree that a lot is needed for WebPOS, but it is a starting point if I can get to the data and put real world data in. I only go through these routes because it seems no other method exists to do this reliably.
 
So far as just loading data from sample into PostegreSQL goes, I still need to do the same mapping of foreign keys and columns to foreign tables which are allowed to be null. Derby is just the place I started because I don't know the inner details of either Derby or PostgreSQL, but saw examples of using system tables for Derby. I'm confident with SQL, but have never delved into the metadata within a SQL server.
 
Basically I cannot trust editing data in SQL directly unless I know I am editing from top-most table downward into dependent tables in an order which guarantees I won't leave any original data which needed to be altered. The web tools do not seem to provide everything I need so far as understanding required order of edit (not just order of load...loading data in the correct order is insufficient for these purposes). Though I might investigate doing the same thing under PostgreSQL...the tools for PostgreSQL may offer a way to export what I need.
 
Understand that right now I just want to get WebPOS running with real data from a real retail store. If I can do that, then I can adjust WebPOS (which I agree is terribly nonintuitive). Getting a chart of accounts which matches or is compatible with the current POS is the first goal...I can't do that from within the existing interfaces (due to WebPOS requirements).
 
Thanks!
 
----- Original Message -----From: Mike <[hidden email]>To: user <[hidden email]>Sent: Fri, 09 Jun 2017 15:56:07 -0000 (UTC)Subject: Re: Mapping Derby Sample Data Foreign Keys

Hey stimits. Are you finding that the demo webpos for 16.x works for you?The reason I ask is that I would assume a properly functioning POS wouldneed a lot of supporting stuff, like a chart of accounts, payment accounts,etc. I tried it (demo), and it is not very intuitive as is.

If you REALLY want to capture all the SQL that postgresql sees, you cansimply set "log_statement = mod', and it will log all of the SQL, but goingthis route is not recommended. Instead, you can create a very simplehot-deploy component that can load all of the stuff you need after seed,and seed-initial. Here is a sample:

root@server:/opt/ofbiz/hot-deploy# find my_data/my_data/my_data/ofbiz-component.xmlmy_data/datamy_data/data/16240_shop_StandardCostingData.xmlmy_data/data/16120_shop_RelatedData.xmlmy_data/data/16020_shop_CatalogData.xml---etc...

Consists of ONE "ofbiz-component.xml" file, a directory called "data", andthe .xml files you want to load, preferably named to create order. That'sit.

The "data" directory has the xml files to load. I keep them named by aconsistent 5 digit number where if you list it's contents, they are alwaysin the exact same loading sequence. The key item is theofbiz-component.xml file, which lists the items to load, which is basicallythe same order as the files... Example:

<?xml version="1.0" encoding="UTF-8"?><ofbiz-component name="my_data" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://ofbiz.apache.org/dtds/ofbiz-component.xsd"> <!-- define resource loaders; most common is to use the componentresource loader --> <resource-loader name="main" type="component"/> <entity-resource type="data" reader-name="my_data" loader="main"location="data/16000_shop_StoreData.xml"/> <entity-resource type="data" reader-name="my_data" loader="main"location="data/16010_shop_RentalStoreData.xml"/> <entity-resource type="data" reader-name="my_data" loader="main"location="data/16020_shop_CatalogData.xml"/> ... ETC... More files</ofbiz-component>

Notice the line: ofbiz-component name="my_data" and "reader-name"... Thatname (my_data) is the name used to load (and re-load as necessary) all ofthe xml files in the "data" directory. For instance:

./ant load-readers "-Ddata-readers=seed,seed-initial" (this is run ONCE)...then..../ant load-readers "-Ddata-readers=my_data" (look for ERRORS, fix .xml asrequired)./ant load-readers "-Ddata-readers=my_data" (RERUN untill all loadingerrors are gone)

Hope this helps.

On Thu, Jun 8, 2017 at 2:37 PM, Michael Brohl <[hidden email]>wrote:

> Why don't you just configure OFBiz to use your Postgres database and load> the demo data the same way you did with the embedded Derby database?>> Your approach seems to be overly complicated to me (or I am missing> something).>>>> > Am 08.06.2017 um 23:08 schrieb [hidden email]:> >> > Hi,> >> > I do have that book which explains concepts, but currently I am trying> to make the existing system's WebPOS work...versus extending, customizing,> or adding to the existing system. The book has no concept of how the OfBiz> interface itself is arranged. I need a working WebPOS, which means I need> to be able to enter data for POS terminals and stores, but there is no web> interface for this. Thus the route of trying to edit sample data and> transfer it over to my PostgreSQL database instead of Derby.> >> > The OfBizDatamodelBook charts are indeed something like what I want, but> I need it in a form where I can use tools to automate tasks based on query> of an existing Derby database and copy to PostgreSQL. Someone made a very> nice chart of account relationships, but the PDF is for human-readable form> and may not reflect changes and other details of the actual database (for> example the chart does not know about automatic sequences for generating> various keys...a database query may be able to take this into account and> simple copy of data from one database to another results in an error).> >> > In terms of using the web tools with copy and paste to load data I have> tried this unsuccessfully. The time it takes to make attempts is extreme> and error messages do not necessarily point out what needs to be loaded for> a given failure to be fixed. Here are some points about the web tool copy> and paste method:> >> > 1. Many XML documents have a root tag of "<entity-engine-xml>". This> requires editing the XML files and removing the "<entity-engine-xml>" (you> can't paste "<entity-engine-xml>" within "<entity-engine-xml>"). This isn't> too bad of an issue, but it slows things down (especially if it is a large> file and you are skipping open/close tags via scrolling around in an> editor). Sometimes java errors are so long that the pop-up showing them is> not capable of displaying on a large monitor (though log files do not have> this limitation).> >> > 2. Some tables (many) have a foreign key and produce an error if the> foreign table does not already have an entry. There are also cases where a> column in a foreign table is copied during insert, but the value is allowed> to be null...I worry that no error would be generated if the foreign key is> there but the data for the null column has not yet been populated. Those> columns pulling data from another table where null is allowed may change> whether the data is valid but will never produce an error (copy by value).> I see a SQL query as a programmatic way to be certain columns which can be> null will always be loaded from a parent table which has had the maximum> opportunity to be populated before using that parent.> >> > 3. In some cases I am going to be copying from Derby to PostgreSQL.> SQuirrel-sqL has a tool for this, but the tool does not deal well with keys> which have been generated using different methods between the different> databases. This is a particular case of the above mentioned need to be able> to see generated values.> >> > I have tried for a long time, with many hours per day, to come up with a> simpler solution for getting WebPOS working (including mouse copy and> paste, then command line XML file loads). Then I tried for quite some time> to be able to get the Derby demo data into PostgreSQL using simpler tools> and methods...once again without success. I am positive that if I have> detailed access to both the demo database under Derby and to the database> under PostgreSQL that I can guarantee certain qualities during copy. Once I> can do that I can investigate editing the data for replacing some demo data> with a real world store. I do not yet know how to create the missing> interface for populating WebPOS, so I am limited to this route...once I can> do this and understand it I can work on modifying or customizing OfBiz to> include an interface for avoiding all of the WebPOS issues.> >> > I just wish that part of the unit testing before OfBiz releases included> checking if demo data can be loaded by an end user without direct database> manipulation (this would of course be a monumentally difficult task to see> if every bit of demo data could be hand entered...someone would have to> actually enter it). The written instructions of how the unit test would be> performed would serve as an incredibly valuable way of understanding how to> set up a new business. Right now I'm basically reverse engineering this> when all I really wanted to do was set up the existing features and get it> running (modification could come later).> >> > So I'm back to finding ways to map table relationships in Derby...it> looks like a simple SQL query won't be possible, I'm going to have to use> the JDBC interface.> >> > Thanks!> >> > ----- Original Message -----From: Paul Foxworthy <[hidden email]>To:> [hidden email]: Thu, 08 Jun 2017 07:31:52 -0000 (UTC)Subject:> Re: Mapping Derby Sample Data Foreign Keys> >> > Hi stimits,> >> > I agree with Taher about the most productive way to solve your problem.> >> > For an overall understanding of the data model, seehttps://> cwiki.apache.org/confluence/display/OFBIZ/Data+Model . There's amention> there of the Data Model Resource Book by Len Silverston, whichinspired much> of OFBiz's data model, and links to diagrams. The diagramsare a few years> old but still substantially correct and relevant.> >> > Cheers> >> > Paul Foxworthy> >> >> On 8 June 2017 at 06:43, Taher Alkhateeb <[hidden email]>> wrote:> >>> >> Hi,>> I might be wrong, but I think you're trying to do this the hard> way. The> problem is really simple in that you have some missing data that> is not> loading correctly. To fix this issue I would suggest the following> steps:>> 1- Copy the XML data file contents> 2- Paste it into the XML data> import window (i.e.> https://localhost:8443/webtools/control/EntityImport)>> 3- Click Import Text> 4- Observe error message (it will tell you which> foreign key is violated> and how)> 5- fix the data accordingly> 6- Repeat 1> to 5 as needed>> That would be much faster and easier than going down to> the SQL level to> try to investigate. I recommend that you use the entity> engine to your> advantage instead of bypassing it completely.>> HTH>> On> Wed, Jun 7, 2017 at 1:42 AM, <[hidden email]> wrote:>> > Hi,> >> >> There are 861 tables in the ofbiz Derby database under ofbiz-16.11.02 for>> > demo data. I'm trying to learn to load this data manually using SQL> >> queries, and thus making a dependency list of tables with foreign key> >> requirements (or even references to other tables which are allowed to be> >> null). Basically, no table referencing another table's data can be loaded>> > without first loading that parent table. With 861 tables and some tables>> > having more than one foreign key I wanted to automate this with a SQL>> query> > (I'm using SQuirrel-sqL to explore now). As such I'm looking at> system> > tables describing constraints and keys.> >> > Through system> tables I can find a list of tables and the ID of> > constraints which are> foreign keys. However, I seem to be losing the> battle> > to find the name> of the foreign table and foreign column name to which> the> > constraint> points (only the table with the originating constraint is easy> > to find> and automate a listing for). I see this may be possible to find> > through> JDBC; SQuirrel-sqL itself shows imported and exported keys with> no> >> apparent means to export the information. Can anyone tell me if it is> >> possible to use an ordinary SQL query to find all foreign keys of a table>> > and print the list of the child table and the parent foreign>> table.column?> > If I can do this I can get around the missing WebPOS setup> functionality> > and edit/load SQL directly (I'm using PostegreSQL so I> can't edit a Derby> > database directly...it needs to be copied over to> PostgresSQL first in> the> > proper order).> >> > If you think I need to> write a JDBC app instead to map table load order> > dependencies, please> let me know. I don't believe there is any other way> to> > use the WebPOS> without doing it this way. I hate to think I may have to> go> > through all> of those tables and hand write a spreadsheet with the> > dependencies.> >>> > Thanks!>> >> >> >> > -- Coherent Software Australia Pty LtdPO Box 2773Cheltenham Vic> 3192Australia> >> > Phone: [phone]Web: http://www.coherentsoftware.com.au/Email:> [hidden email]>

[G.W. Haywood-2]

Hi there,

On Sat, 10 Jun 2017, stimits.comcast.net wrote:

> So far as just loading data from sample into PostegreSQL goes, I
> still need to do the same mapping of foreign keys and columns to
> foreign tables which are allowed to be null.

If it's of any interest I might be able to help you with some Perl
scripts which I wrote to populate a Postgres (OfBiz) database from
flat product files.

--

73,
Ged.

[stimits]

Hi,
 
I will probably be interested in this. I have just switched from trying to do this under Derby to doing this under PostgreSQL after looking at pgadmin3. It looks like this tool provides more information than SQuirrel-sqL (possibly because of Derby, not necessarily because of the tool) and I'm checking what I might be able to export directly without an outside script before I try scripting this (I can see dependencies exactly like I need in this tool, I'm looking at the easiest way to export through the tool in a format I can use...it'll be a few days before I know if this will need an outside script).
 
Thanks!
 
----- Original Message -----From: G.W. Haywood <[hidden email]>To: [hidden email]: Sat, 10 Jun 2017 11:25:26 -0000 (UTC)Subject: Re: Mapping Derby Sample Data Foreign Keys

Hi there,

On Sat, 10 Jun 2017, stimits.comcast.net wrote:

> So far as just loading data from sample into PostegreSQL goes, I> still need to do the same mapping of foreign keys and columns to> foreign tables which are allowed to be null.

If it's of any interest I might be able to help you with some Perlscripts which I wrote to populate a Postgres (OfBiz) database fromflat product files.

--

73,Ged.