it needs to be non database specific. to be used in ofbiz respository. so using a entity and eeca would work to take a look at the bi code
========================= BJ Freeman http://bjfreeman.elance.com Strategic Power Office with Supplier Automation <http://www.businessesnetwork.com/automation/viewforum.php?f=93> Specialtymarket.com <http://www.specialtymarket.com/> Systems Integrator-- Glad to Assist Chat Y! messenger: bjfr33man Linkedin <http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro> Deyan Tsvetanov sent the following on 4/7/2010 1:41 AM: > Hi guys, > > so about the transaction log: > > I have a table called TRANSLOG with the following structure: > > > CREATE TABLE translog > ( > id bigserial NOT NULL, > "table" character varying(30) NOT NULL, > operation character varying(6) NOT NULL, > "statement" character varying NOT NULL, > created_on timestamp with time zone NOT NULL DEFAULT now(), > CONSTRAINT id_fk PRIMARY KEY (id) > ) > > Records in the TRANSLOG table are being inserted by the triggers for the > table we are "watching" for changes on INSERT, UPDATE and DELETE. > The STATEMENT column contains executable SQL statement which is > generated by the trigger function. The following screenshot shows some > data in the TRANSLOG table: > > > > So every time some database action is performed on the tables we are > "watching" we get a TRANSLOG entry. > > Each translog entry has a sequence ID. So when we want to PUSH that > transaction log to the main server we actually execute the statements ON > the main server. We execute the statements wrapped in a transaction > which gets rolled back if an error has occured. > > The translog push gets invoked by a stored procedure which gets called > by a scheduled reoccuring job in ofbiz. > For each sync SP execution we store a record in a table called > TRANSLOG_EXEC_LOG . There we keep the currently reached translog_id so > we know from where to start the next time > when the sync SP is invoked. > > So in general that's the mechanism. Pretty simple to implement, pretty > simple to troubleshoot and pretty error resistant . Here's the > screenshot of TRANSLOG_EXEC_LOG: > > > > The result message could be either OK or the DB error message - either a > constraint error or some other error. We always know which was the last > successfully executed statement ( > last_command_id ). If there is an error then we know at which statement > the error has happened - the next after the last successful one. > > That's all in general. > > -- deyan > > -----Original Message----- > From: Jacques Le Roux <[email protected]> > Reply-to: "Jacques Le Roux" <[email protected]> > To: Deyan Tsvetanov <[email protected]>, > [email protected] > Cc: Hans Bakker <[email protected]>, [email protected] > Subject: Re: ofbiz entity sync. > Date: Tue, 6 Apr 2010 12:31:49 +0200 > > > 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 . > > Jacques > > ----- Original Message ----- > From: Deyan Tsvetanov > To: Jacques Le Roux > Cc: Hans Bakker ; [email protected] > Sent: Tuesday, April 06, 2010 9:42 AM > Subject: Re: ofbiz entity sync. > > > > Hi Sirs, > > I'm sorry, i got lost for some time :) > Things happen :) > > There was a lot of discussion on the topic, but the summary: > > there are some major issues that CAN be fixed in the current > implementation: error recovery - due to interrupted connection > between the two servers, sync speed - RMI can be removed , etc. > > 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. > > For a customer I've implemented a transaction log based on > triggers and stored procedures. The transaction log, triggers > and the stored procedures however I implemented only postgresql > as that was the customer's database. It's easy to implement ms > sql or oracle version though. It works perfectly, much much much > faster than RMI, recovers if the sync process is interrupted , > etc. My goal was to implement this mechanism using entity engine > level triggers and eventually commit it, but unfortunately still > pretty busy with other things so we don't have resources that > can be dedicated to work on that task at the moment, we're > trying to work out the world financial crisis :) > > So if you find what i say reasonable you could go ahead with the > triggers and SPs. For that you need database that supports > triggers - so mysql won't work :) > > That was just the first part. > > The second part is to identify all the tables that you need to > synchronize. Some of them will be only pulled, some of them > pushed only and some of them synced in both directions. Next you > need to test, reset the database and test again and again until > you identify the correct list of the tables so your sync process > doesn't end up with FK insert / update errors. That is pretty > easy but time consuming task - it takes few days to complete :) > > So that's all I can say for now, without getting your bored with > details :) > If you have other questions - go ahead :) > > Cheers, > Deyan > > -----Original Message----- > From: Jacques Le Roux <[email protected]> > Reply-to: "Jacques Le Roux" <[email protected]> > To: Hans Bakker <[email protected]>, > [email protected], [email protected] > Subject: Re: ofbiz entity sync. > Date: Sat, 3 Apr 2010 10:04:29 +0200 > > > Hi Hans, > > I put a comment with an abstract of the situation. Please let me know > what you think to get the ball rolling > > Thanks > > Jacques > > From: "Hans Bakker" <[email protected]> > > Hi Gentleman, > > > > may i ask if there was any progress on this issue? > > > > https://issues.apache.org/jira/browse/OFBIZ-3333 > > > > I added the following comment: > > > > We have a customer using entity sync and experiencing the problems > in > > this issue. > > > > We are interested working with other parties on this subject. If > there > > is already substantial work done we are interested to compensate > > financially for it. > > > > Please let me know if there is any progress here. > > > > regards, > > Hans > > > > -- > > http://www.antwebsystems.com : > > Quality OFBiz support for competitive rates.... > > > > > > > > > >
