Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2017-07-12 12:51:31 Link
VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow
connection to the remote database and as such, queries involving large,
sometimes-changing lookup data is slow.
Solution: pull data locally for quick lookup. This works if I can do
some sort of Sync operation to make sure the local copy matches the
remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as
well. My thought was to use those 2 fields for the sync. If it exists
in both and the remote timestamp is newer, update the local record. If
it exists in the remote but not locally, add it local. If it exists in
the local but not in the remote, delete it from local. Any new adds or
updates to existing records will then be done to the remote database and
then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia,
--Mike
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/5d4db6dfbe4432930fc849ab30cd4c61@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Stephen Russell
Posted: 2017-07-12 13:05:30 Link
How do you modify the timestamp in local mode, or what constitutes a change
that you would change the local and not update the backend instead?
On Wed, Jul 12, 2017 at 12:51 PM, <
mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
> VFP9SP2 app, MySQL(MariaDB) secure database on web
>
> Scenario: customer uses my software but has an extremely slow connection
> to the remote database and as such, queries involving large,
> sometimes-changing lookup data is slow.
> Solution: pull data locally for quick lookup. This works if I can do
> some sort of Sync operation to make sure the local copy matches the remote
> copy.
>
> The Primary Keys are varchar-40 GUID, and there's a timestamp field as
> well. My thought was to use those 2 fields for the sync. If it exists in
> both and the remote timestamp is newer, update the local record. If it
> exists in the remote but not locally, add it local. If it exists in the
> local but not in the remote, delete it from local. Any new adds or updates
> to existing records will then be done to the remote database and then
> echoed locally.
>
> I was thinking I'd setup MariaDB locally for this local database.
>
> Comments on that plan of attack?
>
> tia,
> --Mike
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYL5zEbbHYWg_gdU9TWAjvhjZ=xTKmrHz6T+RRF5+v6zrw@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Kevin J Cully
Posted: 2017-07-12 13:05:54 Link
"It depends." - Ted Roche
Why reinvent the wheel?
https://mariadb.com/kb/en/mariadb/setting-up-replication/
What is an acceptable delay in the replication process? And what is the size of the data and how much data is changing daily? Hourly?
-Kevin
-----Original Message-----
From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com
Sent: Wednesday, July 12, 2017 1:52 PM
To: ProFox
Subject: Syncing data locally from a remote database
VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow connection to the remote database and as such, queries involving large, sometimes-changing lookup data is slow.
Solution: pull data locally for quick lookup. This works if I can do some sort of Sync operation to make sure the local copy matches the remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as well. My thought was to use those 2 fields for the sync. If it exists in both and the remote timestamp is newer, update the local record. If it exists in the remote but not locally, add it local. If it exists in the local but not in the remote, delete it from local. Any new adds or updates to existing records will then be done to the remote database and then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia,
--Mike
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/F82DB4DE49440845A14B540F6C2DF6A92C6E21@BLUFITSPMBS01.cherokeega.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2017-07-12 13:11:31 Link
On 2017-07-12 14:05, Stephen Russell wrote:
> How do you modify the timestamp in local mode, or what constitutes a
> change
> that you would change the local and not update the backend instead?
You wouldn't change the local only; all changes would be done on the
remote first and then copied locally. That's what I meant when I said
"Any new adds or updates to existing records will then be done to the
remote database and then echoed locally."
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/b2bca7540730866c56f9023c5cd96538@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2017-07-12 13:12:52 Link
On 2017-07-12 14:05, Kevin J Cully wrote:
> Why reinvent the wheel?
> https://mariadb.com/kb/en/mariadb/setting-up-replication/
>
> What is an acceptable delay in the replication process? And what is
> the size of the data and how much data is changing daily? Hourly?
>
> -Kevin
I only wanted to really do this as an added option/feature, not the
defacto standard. I want to limit my setup time with the new clients if
possible. Hence, this "roll your own" concept.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/3c581481dfa535e9d62a1b5e68699d7f@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: Stephen Russell
Posted: 2017-07-12 13:30:02 Link
Why put up the data off site then now that you know it is a kluge?
Ever considered this if you have to have two?
http://www.techrepublic.com/article/how-to-set-up-database-replication-with-mariadb/
I would only have one single database server for the client myself and if
they have such poor Internet then I'd do it local. All that extra
spaghetti to keep it local and sync it sounds like a wart that will only
grow over time.
On Wed, Jul 12, 2017 at 1:11 PM, <
mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
> On 2017-07-12 14:05, Stephen Russell wrote:
>
>> How do you modify the timestamp in local mode, or what constitutes a
>> change
>> that you would change the local and not update the backend instead?
>>
>
>
> You wouldn't change the local only; all changes would be done on the
> remote first and then copied locally. That's what I meant when I said "Any
> new adds or updates to existing records will then be done to the remote
> database and then echoed locally."
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYK9aKzopAEbfrnMwLduQWEBJhtwGXoCXk_G4F1MgCX=gQ@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2017-07-12 14:50:01 Link
On 2017-07-12 14:30, Stephen Russell wrote:
> Why put up the data off site then now that you know it is a kluge?
>
> Ever considered this if you have to have two?
> http://www.techrepublic.com/article/how-to-set-up-database-replication-with-mariadb/
>
>
> I would only have one single database server for the client myself and
> if
> they have such poor Internet then I'd do it local. All that extra
> spaghetti to keep it local and sync it sounds like a wart that will
> only
> grow over time.
Thanks for the link, Steve! As for why not doing it local? These folks
will be accessing the app and their data from various locations, so
hosting it on a database they can all share is key. I'm guessing I
could setup the database locally on each, and setup the replication on
each to communicate to the common web database? This system uses GUIDs
as the PKs for nearly all tables.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/f45acb1266862246549727bf70f4a4f2@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: José Enrique Llopis
Posted: 2017-07-12 15:40:39 Link
Another possibility: do not synchronize anything, access remotely with
Remote Desktop
Jose Enrique Llopis
-----Mensaje original-----
De: ProFox [mailto:profox-bounces@leafe.com] En nombre de
mbsoftwaresolutions@mbsoftwaresolutions.com
Enviado el: miércoles, 12 de julio de 2017 19:52
Para: ProFox
Asunto: Syncing data locally from a remote database
VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow
connection to the remote database and as such, queries involving large,
sometimes-changing lookup data is slow.
Solution: pull data locally for quick lookup. This works if I can do
some sort of Sync operation to make sure the local copy matches the
remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as
well. My thought was to use those 2 fields for the sync. If it exists
in both and the remote timestamp is newer, update the local record. If
it exists in the remote but not locally, add it local. If it exists in
the local but not in the remote, delete it from local. Any new adds or
updates to existing records will then be done to the remote database and
then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia,
--Mike
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/93B7291099EE4202A1B21F5CF52DD74D@LENOVO1
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2017-07-12 15:53:55 Link
On 2017-07-12 16:40, José Enrique Llopis wrote:
> Another possibility: do not synchronize anything, access remotely with
> Remote Desktop
>
> Jose Enrique Llopis
Thanks, Jose...but not how I choose to sell this to clients. That
doesn't feel right from a turnkey software perspective.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/e93f4db76c10104998d3a8ad5dc9cb4c@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: mbsoftwaresolutions@mbsoftwaresolutions.com
Posted: 2017-07-12 15:58:44 Link
On 2017-07-12 14:30, Stephen Russell wrote:
> Why put up the data off site then now that you know it is a kluge?
>
> Ever considered this if you have to have two?
> http://www.techrepublic.com/article/how-to-set-up-database-replication-with-mariadb/
>
>
> I would only have one single database server for the client myself and
> if
> they have such poor Internet then I'd do it local. All that extra
> spaghetti to keep it local and sync it sounds like a wart that will
> only
> grow over time.
From the article:
"The easiest means of database backup: You'd be hard pressed to find an
easier method of getting a real-time database backup than replication.
And with MariaDB, setting up replication is incredibly simple. Give this
a try, and you can rest easier knowing you always have a backup database
to use."
This works IF it means the Father and Son can each work on their local
database (in their own homes/offices) and then have those changes echoed
to the WEB database. (Now you see the challenge???) This is NOT for a
simple 1-user-in-1-location scenario.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/2940450e612e0825c7dd9d0a98ed02dc@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.