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 <mz4whee...@gmail.com>To: user <user@ofbiz.apache.org>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 <michael.br...@ecomify.de>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 stim...@comcast.net:> >> > > 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>
