-----Original Message----- From: Jacques Le Roux <jacques.le.r...@les7arts.com> Reply-to: "Jacques Le Roux" <jacques.le.r...@les7arts.com> To: user@ofbiz.apache.org Subject: Re: JUNK->Re: ofbiz entity sync. Date: Tue, 6 Apr 2010 21:30:24 +0200
From: "Deyan Tsvetanov" <deyan.tsveta...@ittconsult.com> > Hi Adrian, > > yes , my current approach is database specific. It was implemented under > pressure when I found out that ofbiz sync does not work, at least in my > setup :) > > So my suggestion and idea is to implement database independent solution, > I would do it but may be in few months. > The approach suggests that we implement a triggering function in entity > engine which would allow us to do event based handling on insert / > update / delete events. So in general we say: onInsert for a specific > entity execute some java method. > > Entity Engine would either do that event handling completely in it's > java code or would generate and create real database triggers, if the db > engine supports it. I don't know if there is such a feature already, I > did a very quick search in the docs and found nothing. May be > Jacques could help here ? :) There are no such features in OFBiz. Note that you said that MySql has no triggers but since 5.0.2 there are triggers http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html Actually, most modern DBMS have triggers, the problems with them are syntax differences http://en.wikipedia.org/wiki/Database_trigger. We could try to use common denominator (if it exists, I did not check), but some are still using older DBMS without any such capabilities. [Deyan] Well yes, the triggers are database specific, but isn't what entity engine is about to provide: unified database access layer ? Triggers are also an almost universal database feature. They could be very useful, especially for extra security in ofbiz, synchronization, integration with 3rd party systems or home grown software like most of companies have. Most of the databases have it, for mysql we have to increase the minimum required version to 5. It's not a pain. At some point we might have the question: so mysql does not support things that all others do support. What do we do: do we implement the missing features in entity engine's java code ? How much effort will it take ? Why do we have to implement stuff that is already provided by many other products for free and in a good quality ? In the end of the day the goal is to provide lot of good features, erp, enterprise class features for a low cost to users, customers, companies. Providing both options - open source and commercial database engines support is required and very good, keeps TCO down. But if some database isn't improving as fast as needed than it shouldn't not be a problem to drop the support for it. Mysql is targeted to different types of customers and users who have different requirements than ours - like web developers, php, jsp, perl, whatever. So I say for now we implement the entity events / triggers in java, for the purpose of ofbiz synchronization it will be enough. We don't need native db triggers yet. > Jacques, off the topic - is there an option to call native stored > procedure in the database from entity engine ? It comes very handy > sometimes ;) No this does not exist for the same reason http://en.wikipedia.org/wiki/Stored_procedure [Deyan] Why do we need native stored procedures ? OFBiz database is complex. Its more flexible and more complex than the database of a typical web site. Customers want reports. For reports we use tools like BIRT, Pentaho, etc. For reports calculations and functions are needed, often not supported by entity engine. SUM and GROUP_BY sometimes are not enough.For reports the grouping and calculations need to happen in the database engine as much as possible. If you let the BI tools to iterate result sets in a JVM then you're in trouble with "memory issues" and limits :) SPs are also convenient when doing complex database queries from a complex search GUI screen. So here is where it comes in hand to be able to call native DB stored procedures. Entity engine would have to generate db specific code, either "call sp_select_average_sales()" or "SELECT * FROM sp_select_average_sales()". It's not hard, it's the purpose of entity engine actually. And then code the stored procedures for all engines you like to support. Jacques PS: I saw your answer to Adrian and your example. Yes this seems to be a real problem, I can't find a good solution so far :/ If we could avoid to have NTP sync the machine during transactions it should not be a problem. I found http://commons.apache.org/net/api-release/org/apache/commons/net/ntp/TimeInfo.html But there are maybe other problems you crossed during your experiment? [Deyan] dealing with timestamps is difficult. In the end of the day the question is: why do we need created_timestamp_tx and last_update_tx for all entities / db tables ? Answer: we need them in order to generate a list of entities ( database records ) to be pushed / pulled and that list must be in the correct order so we avoid FK or PK constraint issues. Well there is another way to generate that list without dealing with timestamps and time offsets at all. We can generate that list on the fly - in real time - using triggers which upon execution create an entry in a transaction log. This approach would guarantee that if a PARTY is created before a PARTY_GROUP in the real life - the same sequence of actions will be appended to the transaction log. I'll send you some examples a bit later today. -- deyan >> As an alternative, you could query the other servers for their > current time > > and generate a set of offsets to adjust the timing. > > > Deyan: Yes, but the clock of the main server also could run faster or > slower and in few weeks or months would eventually get synced by NTP. > That would also cause issues. Another problem is that you insert > entities with local timestamp and synchronize them using a remote one > for comparison. So you need to calculate time diff, too much logic i > think :) > > For the transaction log method I couldn't think of cons. I'll send you > some example stored procedures and description, that would illustrate > the approach I use best i believe :) You might find something, i'll be > happy to discuss it :) > > The goal here is to create a very reliable mechanism, that runs fast, > does not make much traffic, does not require lot of cpu and resources - > like java heap memory, recovers from interruptions reliably ( at least > logically reliably ), configures easily, troubleshoots easily ( ! ) , > implements easily ( if possible :) and works on all databases, If > possible :) > > I'll send the stuff tomorrow so we know what are we discussing :) > > Cheers, > Deyan > -----Original Message----- > From: Adrian Crum <adri...@hlmksw.com> > Reply-to: user@ofbiz.apache.org > To: user@ofbiz.apache.org > Subject: Re: JUNK->Re: ofbiz entity sync. > Date: Tue, 06 Apr 2010 07:27:50 -0700 > > > Jacques Le Roux wrote: >> Hi Deyan, >> >> Thanks for your clear explanation and suggestion. As I'm busy with another >> stuff, I have quickly put your comment as a quote in >> OFBIZ-3333 . >> There is however a major issue that CAN NOT be fixed in the current >> implementation: the list and sequence of entities to be >> synchronized gets created by entities' timestamp - date_created_tx and >> last_update_tx. It works as long as the clocks of all the >> syncing parties are in sync. You can easily achieve this by using NTP for >> example - reliable enough. But if the clock of one of >> the parties gets un-synced for just few minutes, and during those few >> minutes records get inserted or updated than you are in >> trouble. Syncing the clock back won't help you because you won't be able to >> sync the broken records due to foreign key constraint >> issues. Examples I could give but I guess you could think of such by >> yourselves :) >> >> So IMHO the best approach for synchronization is not the timestamp but the >> TRANSACTION LOG. This approach is used in all major >> databases - m$ $ql, oracle. > > The problem with that approach is that it is database-specific. As an > alternative, you could query the other servers for their current time > and generate a set of offsets to adjust the timing. > > -Adrian > > >
