Apologies in advance for the vagueness of this post ...

I have been working on linking my EDB  & DBISAM systems to the Amazon Cloud. Connecting this way is surprisingly easy using a conventional Client Server model.

However, I want to develop a brief-case model, i.e. a framework for applications where each user carries the database around on their own computer and can add data without need for a permanent connection. The framework then periodically (or when a connection is available) connects to the "main" database (probably on the cloud) and uses a replication model to update the cloud server with new stuff from the user, and update the user with new stuff.

There is a useful example of this on the EDB site, the "salesperson" example project, using EDB's excellent STORE & replication capabilities.

... I haven't used the advanced SQL / DML capabilities of EDB much, so I am very unsure how best to pursue making this work.

I want it to be reasonably invisible to the user. I am considering having a JOB, which runs every 30 min while the user is signed on, or when a user chooses to "Save", or which runs at the start & end of a users session.

1. Can EDB run a replication job, copying the contents of STORES up to a server & back from a server while the user is logged on & editing data, or is it best to do this while the user is inactive, i.e. call the user to "Save" and run the ?

2. Beyond this what happens if replication jobs for 2 users overlap? ... i.e. user 1 starts a replication read / write, and during this a second user does the same. The "salesperson" example suggests deleting the replication file from the STORE after use ... is it possible that additional changes are already logged in this STORE & might therefore be lost?

3. A tougher question: Changes in DB structure, (I realise this is a big question) if a piece of SQL is run on the server which alters the DB structure (for example adding a new TRIGGER) does this change in the DML SQL of the DB propagate through the STORE or not?

4. The "salesperson" example suggests the fairly straightforward model of incrementing ID's according to a users "seed ID" ... so that Tables in User 1's DB start from ID 1, while tables in User 2's DB start from ID 1,000,000. This seems really clean & straightforward. Is anyone using a different mechanism than this & if so why?

Thanks in advance for any responses.

Adam,

<< 1. Can EDB run a replication job, copying the contents of STORES up to a
server & back from a server while the user is logged on & editing data, or
is it best to do this while the user is inactive, i.e. call the user to
"Save" and run the ? >>

Sure, EDB uses the proper locking to ensure that the saving and loading of
updates works seamlessly.

<< 2. Beyond this what happens if replication jobs for 2 users overlap? ...
i.e. user 1 starts a replication read / write, and during this a second user
does the same. The "salesperson" example suggests deleting the replication
file from the STORE after use ... is it possible that additional changes are
already logged in this STORE & might therefore be lost? >>

You're not quite understanding how the replication works with respect to
update files.  Update files are generated by the SAVE UPDATES statement, and
are a capture of the logged updates for a set of published tables since the
last SAVE UPDATES.  The logged updates themselves are stored in the .edbpbl
file attached to each table.

Once an update file has been successfully loaded by a specific database, it
is perfectly safe to delete it.  This has no effect upon any other update
files that may have been added to the incoming store since that time, and
has no bearing on any updates currently being logged for the published
tables in the database where the updates are being loaded.  In fact, the
load operation *may* cause more updates to be logged for the published
tables, but that, again, has no bearing on the update files in the incoming
store.

<< 3. A tougher question: Changes in DB structure, (I realise this is a big
question) if a piece of SQL is run on the server which alters the DB
structure (for example adding a new TRIGGER) does this change in the DML SQL
of the DB propagate through the STORE or not? >>

No, DDL changes are not propagated at this time.

<< 4. The "salesperson" example suggests the fairly straightforward model of
incrementing ID's according to a users "seed ID" ... so that Tables in User
1's DB start from ID 1, while tables in User 2's DB start from ID 1,000,000.
This seems really clean & straightforward. Is anyone using a different
mechanism than this & if so why? >>

If you want to use IDENTITY columns, then the only way is to partition them
into logical groups by either offsetting them with a base value, or doing
like the sales quote sample application.

--
Tim Young
Elevate Software
www.elevatesoft.com

I haven't been on the newsgroups for a while and just saw this post and
thought I'd chime in with a few things.  I'm just finishing a major update
to an application that will take my customer's small application from
remote-only browsing, reporting, and editing, to local browsing and
reporting with remote editing.  It's been a long struggle and I've come to
understand why so few people attempt it: it's REALLY hard to get it right!
(Not from the database engine side, but from the application side managing
the database.)

I started out with segmented auto-inc IDs like Tim was describing.  It
would've worked except somehow, someone didn't upgrade in the right order or
there was a script that had a bug or something.  In any case, the IDs got
mixed up and I immediately had a big mess on my hands with duplicate IDs,
missing database updates and so on.  I ended up using GUIDs for ALL primary
keys--that should forever eliminate any such problems I had before.

The next update still had other problems though.  With multiple people
editing locally and the customer getting more adept at using their software
and bouncing around from record to record, we had problems with edits
getting overwritten.  I tried the idea of putting a record into a
"check-out" mode, but there were headaches involved with that and I didn't
go down that path very far.

I've settled on the idea of remote editing (insert/update/delete) and local
browsing and reporting.  If a record gets added or changed, it has to happen
on the remote server only and it will trigger an immediate SAVE UPDATES.
Then the client computer will download it and refresh the data set.  I'm not
completely sure how it's all going to turn out yet as I'm still finishing
it, but I think it will allow the customer to have immediate changes
reflected everywhere while allowing me to finally get some sleep!

For a core office group that works directly against the server with remote
salespeople only ADDING new records occasionally, it's fairly simple.  In my
customer's situation, there's only one person in the "office" with the
central server.  All other users are spread out across the US and they all
need to be able to add/edit/delete any record at any time--and of course see
each other's changes immediately with the speed of local browsing and
reporting.  That has been my nightmare.

I wish you luck--and LOTS of design and testing.


David Cornelius
Cornelius Concepts

"Adam Brett" wrote in message
news:4803B3AD-ED6E-4D7C-9A5A-8AAA14093885@news.elevatesoft.com...

Apologies in advance for the vagueness of this post ...

I have been working on linking my EDB  & DBISAM systems to the Amazon Cloud.
Connecting this way is surprisingly easy using a conventional Client Server
model.

However, I want to develop a brief-case model, i.e. a framework for
applications where each user carries the database around on their own
computer and can add data without need for a permanent connection. The
framework then periodically (or when a connection is available) connects to
the "main" database (probably on the cloud) and uses a replication model to
update the cloud server with new stuff from the user, and update the user
with new stuff.

There is a useful example of this on the EDB site, the "salesperson" example
project, using EDB's excellent STORE & replication capabilities.

... I haven't used the advanced SQL / DML capabilities of EDB much, so I am
very unsure how best to pursue making this work.

I want it to be reasonably invisible to the user. I am considering having a
JOB, which runs every 30 min while the user is signed on, or when a user
chooses to "Save", or which runs at the start & end of a users session.

1. Can EDB run a replication job, copying the contents of STORES up to a
server & back from a server while the user is logged on & editing data, or
is it best to do this while the user is inactive, i.e. call the user to
"Save" and run the ?

2. Beyond this what happens if replication jobs for 2 users overlap? ...
i.e. user 1 starts a replication read / write, and during this a second user
does the same. The "salesperson" example suggests deleting the replication
file from the STORE after use ... is it possible that additional changes are
already logged in this STORE & might therefore be lost?

3. A tougher question: Changes in DB structure, (I realise this is a big
question) if a piece of SQL is run on the server which alters the DB
structure (for example adding a new TRIGGER) does this change in the DML SQL
of the DB propagate through the STORE or not?

4. The "salesperson" example suggests the fairly straightforward model of
incrementing ID's according to a users "seed ID" ... so that Tables in User
1's DB start from ID 1, while tables in User 2's DB start from ID 1,000,000.
This seems really clean & straightforward. Is anyone using a different
mechanism than this & if so why?

Thanks in advance for any responses.

Adam,

> I have been working on linking my EDB  & DBISAM systems to the Amazon Cloud. Connecting this way is surprisingly easy using a conventional Client Server model.

That's interesting! Have you some more information on how to do this?

Regards Uli

Dear David,

Thanks for this feedback! I am still really only at the stage of implementing EDB applications that work at each of my clients, but replication / ability for staff to remote work is going to be important in the future.

I am seriously considering GUIDs, because it does basically solve the problem ... though you lose some useful things, like sequential numbers.

I can imagine a situation where a new user is taken on, installs the system messes up their primary seed ID & the whole thing explodes ... which would be a disaster! Several of my systems are only in 2 or 3 offices though, so there would not be random installation happening all the time.

The problem is requirement creep ... as soon as I give my customers replication between offices I know they will want to be able to disconnect staff members (home-workers etc.) & have them work on their applications & replicate to it ...

GUIDs help to solve this problem longer term.

Also, I have structured all my tables to try to avoid a situation where data is _ever_ overwritten.

i.e. a "Customers" object consists of a root table "Organisations" with a Customers table beside it & 1 to many relationships to other tables called things like "contacts" "phones" "addresses" etc., in that way my staff can update more volatile details without over-writing each others work.

... I am hoping that this segmentation will help to reduce the problems you talk about ... time will tell.

Yes, GUIDs will help significantly.

There are other ways to keep track of sequential numbers.  I inherited my
application from an old FoxPro for DOS program and all the contacts started
with either C or V (customer or vendor) and then had a sequential number.
At that time, the 5 users of the program just kept track among themselves,
but wanted something better when I took over the project.  I accomplished
that by have a general "system" table that kept the latest customer and
vendor numbers (separate) and any time anyone wanted to add a new contact,
it would first query that system table to find out what the next assigned
number would be.  I still use that in the application, but it's not a
primary key--the GUID is.

My first attempt segmented the IDs, and like you worried below, something
got messed up and everything exploded!  I had to quickly revert everyone
back to working directly over the internet connected to the remote database
(very slow).

I recently tried browsing from the local computer and editing only on the
server.  But I had to get changes down immediately so setup triggers to
instantly replicate for any add/edit/delete.  Of course that turned out to
be painfully slow for any simple edit.

I've come around to an older idea I had but with a small enhancement.  I am
setting up a "check out" system where each table will have two new fields: a
BOOLEAN "Locked" and a GUID "LockedBy".  Then when a user wants to edit a
record, the program will set these two values on the server and make the
changes locally.  When finished, the locally edited copy will clear this
"lock" and the replicated record will go up to the server, thus clearing the
lock for everyone else.  (Just before editing, the "Locked" value will be
checked, of course.)  This also assumes the server and all the clients are
saving updates and exchanging with the server frequently.

I should have this ready to test in a few days.


David Cornelius
Cornelius Concepts


"Adam Brett" wrote in message
news:25B64C8E-9451-45B6-900D-367C50552ED1@news.elevatesoft.com...

Dear David,

Thanks for this feedback! I am still really only at the stage of
implementing EDB applications that work at each of my clients, but
replication / ability for staff to remote work is going to be important in
the future.

I am seriously considering GUIDs, because it does basically solve the
problem ... though you lose some useful things, like sequential numbers.

I can imagine a situation where a new user is taken on, installs the system
messes up their primary seed ID & the whole thing explodes ... which would
be a disaster! Several of my systems are only in 2 or 3 offices though, so
there would not be random installation happening all the time.

The problem is requirement creep ... as soon as I give my customers
replication between offices I know they will want to be able to disconnect
staff members (home-workers etc.) & have them work on their applications &
replicate to it ...

GUIDs help to solve this problem longer term.

Also, I have structured all my tables to try to avoid a situation where data
is _ever_ overwritten.

i.e. a "Customers" object consists of a root table "Organisations" with a
Customers table beside it & 1 to many relationships to other tables called
things like "contacts" "phones" "addresses" etc., in that way my staff can
update more volatile details without over-writing each others work.

... I am hoping that this segmentation will help to reduce the problems you
talk about ... time will tell.