Realtime Transfer of Updated and Deleted data to Cloud

This forum is for programmers who have questions about the source code.
Post Reply
SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Mon Apr 12, 2021 8:23 am

I am looking for ways to transfer data from local server to cloud realtime. I am interested in only selected few tables. I need to send created, updated and deleted records to cloud for the cloud to update the corresponding tables. This is not for backups however to build Apps on top of the transferred data for analytics and supplemental functions.

joes
Posts: 239
Joined: Tue Aug 13, 2019 12:41 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by joes » Mon Apr 12, 2021 12:05 pm

Our Implementations team may be able to provide some advice. You can contact them via email at Implementations@OpenDental.com.
Joe Sullivan
Open Dental Software
http://www.opendental.com

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by jordansparks » Mon Apr 12, 2021 3:16 pm

"for the cloud to update the corresponding tables?" That means you are talking about two way transfer. The only way to do that is to use our API.
https://www.opendental.com/manual/fhir.html
Jordan Sparks, DMD
http://www.opendental.com

SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Tue Apr 13, 2021 8:00 am

I am looking for read only from OD database to keep the cloud tables updated.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by jordansparks » Tue Apr 13, 2021 10:31 am

Oh, well that's a lot easier. You would probably install a small program on the MySQL server. It would carefully query the database at certain intervals and pass the information up to your cloud. There are lots of programs that can do this out of the box, or you could write one.
Jordan Sparks, DMD
http://www.opendental.com

SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Tue Apr 13, 2021 11:52 am

Yes, that's what I was thinking of. However few challenges

1. I can lookup using the Primary key (Patnum for patients), as they are incremented when rows are added, to identify if any new rows were added since last sync.
2. I can use UpdateDateTime Stamp of the table to see what rows were modified since the last sync.

However for deleted row I wouldn't be able to identify them.

The other issue is realtime capture of changes. I can run the checks every few minutes using the primary key and time stamp, this shouldn't put a load on the server. However it will still be near realtime not realtime.

SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Wed Apr 21, 2021 11:43 am

I am thinking of maintaining tables corresponding to each table (that we are interested in) in OD with just the Primary ID as their only column. This table will reside in the MySQL server as OD in a separate database.

every few minutes will check for updated rows, inserted rows using the lastupdatetime in the OD table to ship it to cloud. Compare the our local tables against the corresponding OD table for any deleted rows and send the deleted row info to cloud.

Is there any concerns in maintaining our own tables in the same MySQL as OD in a separate database?

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by jordansparks » Wed Apr 21, 2021 4:38 pm

It's fine to have your own database on the same server as OD. Each table is different. Rows never get deleted from the patient table, so you don't have to think about that. There's also a table called deletedobject that will get a new row inserted if an appointment is deleted.
Jordan Sparks, DMD
http://www.opendental.com

SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Thu Apr 22, 2021 6:36 am

No rows are deleted in patients table, is this true for all other tables as well? In that case I don't need to maintain a separate set of table to identify a deleted row in any table.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by jordansparks » Thu Apr 22, 2021 6:24 pm

No, that's not true for most other tables. I think it's probably true for definition and provider.
Jordan Sparks, DMD
http://www.opendental.com

SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Thu May 13, 2021 8:07 am

Change Data Stream (CDC) appears to be the buzz word in replica or propagation of changes to secondary database. Currently exploring how to implement it in OD MySQL server. Any advises based on your experience implementing such functionality in MySQL server. I believe this CDC solution relies mainly on MySQL replication services.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by jordansparks » Fri May 14, 2021 8:06 am

Yeah, it just sounds like replication. It's possible.
Jordan Sparks, DMD
http://www.opendental.com

SriramChellappan
Posts: 42
Joined: Thu Mar 11, 2021 2:45 pm

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by SriramChellappan » Sat May 15, 2021 5:15 am

I have read either in the blogs or manual cautions about replication. Certain queries or reports are recommended NOT to run in multiple of computers at the same time while replication is turned on. Is there a topic where there is a consolidated list of things to be caution about while replication is turned on?

Or replication can be configured to log only CUD operation not read operation to avoid read only queries can avoid being logged.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Realtime Transfer of Updated and Deleted data to Cloud

Post by jordansparks » Sat May 15, 2021 6:21 am

There are almost no restrictions. Just about the only problem I can think of is if you run a CREATE TABLE command for the same table on both servers. This can happen if a report needs to create a temporary table to store some data and then that table gets deleted at the end of the report. The solution is to add a random string to the end of the table name so that you won't end up with two tables of the same name. There are no other restrictions that I can think of, and the above restriction is only if you are running the report from both servers separately.
Jordan Sparks, DMD
http://www.opendental.com

Post Reply