SQL Anywhere Mobile - a few questions

Author

a few questions

Michele Phoenix

2005-06-06, 7:24 am

Hi everyone,
I'm experimenting with Mobilink for the development of a future project.
I was thinking about using shadow tables to handle updates and deletes in
the download events,
but I have a few questions about this solution:

We have the following table in the consolidated DB:

create table test (code PK, description)

Now we have another 2 tables:

create table test_update (code FK, update_time)
and
create table test_delete (code FK, delete_time)

"test_update" is populated with same rows as "test", and is updated by
triggers upon insert and update from "test".

"test_delete" is populated by trigger upon delete from "test".

The "test" table is added to a publication with a subscription for the user
"remote_user_1".

The download cursor will join "test" with "test_update" and download to
remote DB rows that were updated after last synchronization for
"remote_user_1".

The download delete cursor will delete all rows in the remote DB which PK is
indicated inside "test_delete" with a date further than last synchronization
for "remote_user_1".

Now suppose the following behaviour inside the consolidated DB:

One row is inserted inside "test" and after few minutes deleted, the PK of
this row is now both in "test_update" and "test_delete". "remote_user_1"
starts synchronization but since the download delete cursor is triggered
before download cursor I think the remote DB will get the download commands
in the wrong order, I mean first try to delete the row that doesn't exist
and then insert it.
What do you think about this?

Another question:
The "test_delete" table will always grow up every time a row is deleted from
"test". If I wish to clean "test_delete" when all the remote users have
received delete notifications, how should I do?
I was thinking about scripting the "end_synchronization" table event in this
way:
- read from systable the table id of the current table
- read from sysarticle the id of the publication for the given table id
- read from syspublication the publication name for the given publication id
- read from ml_subscription the MIN "last_download_time" between all the
remote users
- delete from "test_delete" all entries before this date

I think this should work but seems a little crazy, maybe there's a better
way (I hope so).
Any suggestion?

Thanks in advance.

--
Michele Mazzucco
michele.mazzucco@wuerth-phoenix.com

Würth Phoenix S.r.l.
www.wuerth-phoenix.com

Breck Carter [TeamSybase]

2005-06-06, 7:24 am

Shadow tables are usually used for downloading deletes only.
Downloaded inserts and updates are usually controlled by an
update_time column in the base table rather than a shadow table; a
shadow update table seems to be unnecessary.

Having said that, if you delete a row on the consolidated database,
you should delete the corresponding row in the shadow update table at
the same time as inserting the row in the shadow delete table. You
should not download an insert for a row that no longer exists.
Downloading a delete for a row that no longer exists is a minor waste
of performance but AFAIK it doesn't raise an error.

You should also have some code that eventually deletes the rows in the
shadow delete table; e.g., when the delete_time is earlier than the
earliest last_download time for all remotes.

FWIW these topics are discussed further in my book.

Breck

On 6 Jun 2005 03:03:00 -0700, "Michele Phoenix"
<michele.mazzucco@wuerth-phoenix.com> wrote:

>Hi everyone,
>I'm experimenting with Mobilink for the development of a future project.
>I was thinking about using shadow tables to handle updates and deletes in
>the download events,
>but I have a few questions about this solution:
>
>We have the following table in the consolidated DB:
>
>create table test (code PK, description)
>
>Now we have another 2 tables:
>
>create table test_update (code FK, update_time)
>and
>create table test_delete (code FK, delete_time)
>
>"test_update" is populated with same rows as "test", and is updated by
>triggers upon insert and update from "test".
>
>"test_delete" is populated by trigger upon delete from "test".
>
>The "test" table is added to a publication with a subscription for the user
>"remote_user_1".
>
>The download cursor will join "test" with "test_update" and download to
>remote DB rows that were updated after last synchronization for
>"remote_user_1".
>
>The download delete cursor will delete all rows in the remote DB which PK is
>indicated inside "test_delete" with a date further than last synchronization
>for "remote_user_1".
>
>Now suppose the following behaviour inside the consolidated DB:
>
>One row is inserted inside "test" and after few minutes deleted, the PK of
>this row is now both in "test_update" and "test_delete". "remote_user_1"
>starts synchronization but since the download delete cursor is triggered
>before download cursor I think the remote DB will get the download commands
>in the wrong order, I mean first try to delete the row that doesn't exist
>and then insert it.
>What do you think about this?
>
>Another question:
>The "test_delete" table will always grow up every time a row is deleted from
>"test". If I wish to clean "test_delete" when all the remote users have
>received delete notifications, how should I do?
>I was thinking about scripting the "end_synchronization" table event in this
>way:
>- read from systable the table id of the current table
>- read from sysarticle the id of the publication for the given table id
>- read from syspublication the publication name for the given publication id
>- read from ml_subscription the MIN "last_download_time" between all the
>remote users
>- delete from "test_delete" all entries before this date
>
>I think this should work but seems a little crazy, maybe there's a better
>way (I hope so).
>Any suggestion?
>
>
>Thanks in advance.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Michele Phoenix

2005-06-06, 7:24 am

that's clear.
thank you very much

Sponsored Links