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 <p...@cohsoft.com.au>To: user@ofbiz.apache.orgSent: 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 <slidingfilame...@gmail.com> 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, > <stim...@comcast.net> 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: +61 3 9585 6788Web: http://www.coherentsoftware.com.au/Email: i...@coherentsoftware.com.au
