[Phpmyadmin-devel] GSoC Synchronization Project

[Jay Jayswal]

Hello,
i am GSoC Participant. i am interested in Synchronization Project.

what i understood is we have to build PHP Scripts which connect to Remote
Machine and Synchronize data.
which means same database structure at both end and we are coping each
end's new row's to another end. Right?

i have started with my proposal. i wanted to ask what should be length of
proposal? 7 or 8 page is okay or Do i have to make it short?

Actually, i already did something like this in my past project.i can't
disclose code because i signed Non-Disclosure Agreement of that company. it
was two platform project. web-application(PHP) and Desktop application(C#
WPF). data is stored at desktop database(SQLite) locally, so user can view,
update, delete data offline. and when internet available, system will run
script to synchronize data.

I have faced many problems while solving the problem that i have listed
below. can you send me your expected solution for those problem so that i
can understand your expectations and project requirement.

Problems:

*Insert**:*

Case 1: insert at DB1

DB1

DB2

Before Synchronization

Step1: Two record with ID 1 & 2 in inserted

Current rows :1,2

Current rows : null

After Synchronization

Current rows :1,2

Two record with ID 1 & 2 in inserted

Current rows :1,2











Case 2: insert at DB2

DB1

DB2

Before Synchronization

Current rows :1,2

Step1: Two record with ID 3 & 4 in inserted

Current rows :1,2,3,4

After Synchronization

Two record with ID 3 & 4 in inserted

Current rows :1,2,3,4

Current rows :1,2,3,4










Case 3: Insert at Both DB1&DB2

DB1

DB2

Before Synchronization

Current rows :1,2,3,4

Current rows :1,2,3,4

Step 1: one new record inserted with id 5

Current rows :1,2,3,4,5(of DB1)

Current rows :1,2,3,4

Current rows :1,2,3,4,5(of DB1)

Step 2: one new record inserted with id 5

Current rows :1,2,3,4,5(of DB2)

After Synchronization





What should happen in this case? If we want to sync this we need
some differentiate to identify both DB.
In my past project I provided Device ID. Means PRIMARY KEY and DEVICE ID
both will make Composite Key which make it unique to identify.

What is your preferred solution?

















*Update**:  in this case we required updated row timestamp, I am assuming
there is update_timestamp column in each table*



Case 1:  update at DB1

DB1

DB2

Before Synchronization

Step1: updating row with id 1

Current rows :1(updated),2,3,4

Current rows :1(old),2,3,4

After Synchronization

Current rows :1(updated),2,3,4

update row with id 1

Current rows :1(updated),2,3,4









Case 2:  update at DB2

DB1

DB2

Before Synchronization

Current rows :1(updated),2(old),3,4

Step1: updating row with id 2

Current rows :1(updated),2(updated),3,4

After Synchronization

update row with id 2

Current rows :1(updated),2(updated),3,4

Current rows :1(updated),2(updated),3,4











Case 3: update at both side but different timestamp

DB1

DB2

Before Synchronization

Step1: updating row with id 2

Current rows :1,2(updated),3,4

Current rows :1,2(old),3,4

Current rows :1,2(updated),3,4

Step1: updating row with id 2

Current rows :1,2(updated),3,4

After Synchronization (both update is different and timestamp is also
different)





In this case both side update is different, what should be final record. I
think I should be last one updated. Right?

What is preferred solution?















Case 4: update at both side but same timestamp

DB1

DB2

Before Synchronization

Step1: updating row with id 2

Current rows :1,2(updated),3,4

Step1: updating row with id 2

Current rows :1,2(updated),3,4

After Synchronization (both update is different but time stamp is same)





In this case both side update is different and time stamp is same!! So here
again we need  DEVICE ID by which we can identify that both update is
different.

And what should I store finally, because which one we have to finally store
is depends on timestamp, which is SAME for BOTH DB!!! What is preferred
solution?















*Delete**:*

Case 1: deleted at DB1

DB1

DB2

Before Synchronization

Step1: row with id 3 is deleted.

Current rows :1,2,4

Current rows :1,2,3,4

After Synchronization

In this case row with id 3 will be added at DB1 because for SYSTEM it’s
newly added row at DB2.

To handle this problem in my past project I maintained Activity logs. Which
stores that row with id 3 is deleted, so when synchronization take place, I
fetch logs from activity log and first delete row with ID 3 in DB2.

What is your preferred Solution?

Current rows :1,2,3,4

Current rows :1,2,3,4



















Regards,

Jay Jayswal
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20150317/f76bc78c/attachment.html>