ofbiz-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Deyan Tsvetanov <deyan.tsveta...@ittconsult.com>
Subject Re: JUNK->Re: ofbiz entity sync.
Date Wed, 07 Apr 2010 07:04:10 GMT
-----Original Message-----
From: Jacques Le Roux <jacques.le.roux@les7arts.com>
Reply-to: "Jacques Le Roux" <jacques.le.roux@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.tsvetanov@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 
Actually, most modern DBMS have triggers, the problems with them are syntax differences 
We could try to use common denominator (if it exists, I did not check), but some are still
using older DBMS without any such 

[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

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.

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
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 <adrianc@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

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message