syncronizing local and sever databases 1

[huntert]

I have a few questions about the better way to get records from a VFP database made while roaming copied to the VFP database on a Server when back in the office.

I have a field labeled roam to flag all records in each table of the database made while roaming. Some of these records contain MEMO fields.
-one options is to copy to an array and append from the array for each table. I believe this will not work for memo fields though
- second option is to copy from one DB to the other with 'Copy to' command
- i could also scan each table and append records to the server db

any suggestion on the best way to proceed particularily if the server DB is being used by others. Some of the tables have 10,000 records while others much less. I also have increamental fields for the record id and these would have to change when appending to the server DB

Thanks in advance for your help and time. hunter

 

[Mike Lewis]

Hi Huntert,

one options is to copy to an array and append from the array for each table. I believe this will not work for memo fields though

You could use SCATTER and GATHER with the keyword MEMO. That will work with memo fields.

If the records on the roaming PCs are all new records -- in other words, your roaming users need to insert records into the server database, rather than update existing records -- then why not simply do an APPEND FROM. You would SELECT the server table, and then append from the roaming table.

Doing that would lock the table header (for the server table) while the append is in progress, so you wouldn't be able to let more than one user do an append at the same time. But it won't stop other users accessing the table or even update or delete existing records.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[kosta]

my solutions ( didn't really tested )
i am using GUID for this. GUID guaranted from Microsoft ( you'll see at FAQ's section about this technic )

here is my sample run and test it !

HTH

Create Cursor Server (myname c(20),myguid c(38),LaptopId c(20),rectime T,upload L)
Index On Alltrim(LaptopId)+Dtoc(rectime) Tag laprec
Create Cursor Laptop (myname c(20),myguid c(38),LaptopId c(20),rectime T,upload L)
Index On Alltrim(LaptopId)+Dtoc(rectime) Tag laprec

Select Server
Insert Into Server (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa1',guid(),lapid(),Datetime(),.t.)

Insert Into Server (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa2',guid(),lapid(),Datetime(),.t.)

Insert Into Server (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa3',guid(),lapid(),Datetime(),.t.)

Brow
*-----------------
Select Laptop
Insert Into Laptop (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa1',guid(),lapid(),Datetime(),.f.)

Insert Into Laptop (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa2',guid(),lapid(),Datetime(),.t.)

Insert Into Laptop (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa3',guid(),lapid(),Datetime(),.f.)

Insert Into Laptop (myname,myguid,LaptopId,rectime,upload) ;
VALUES ;
('aa4',guid(),lapid(),Datetime(),.f.)

Browse

Select Laptop
Locate For myname='aa1'
Replace myname With 'AA1' In Laptop
Replace rectime With Datetime()
Brow
*-------------------

*way1
SELECT * from laptop WHERE laptop.myguid NOT in ;
(select myguid FROM server) AND laptop.upload=.f. INTO CURSOR CrsDiff
SELECT server
APPEND FROM DBF('crsDiff')
REPLACE server.upload WITH .t. all IN server
brow
SELECT laptop
REPLACE laptop.upload WITH .t. all IN laptop
BROWSE

*way2
*SELECT server
*SCAN FOR !server.myguid=laptop.myguid AND laptop.upload=.f.
*APPEND FROM laptop FOR !server.myguid=laptop.myguid AND laptop.upload=.f.
*endscan

* way3
* select LapTop
*SCAN
*SELECT LapTop
*lcLapTopID=ALLTRIM(LapTopID)
*lnRecordID=DTOC(RecTime)
*lcmyguid=myguid

*SELECT Server &&has LapID+RecID index built
*SEEK(m.lcLapTopID+m.lnRecordID)
*IF .NOT. FOUND("Server")
*APPEND FROM LapTop for LapTopID+RecTime=lcLapTopID+lnRecordID
*ENDIF
*ENDSCAN


*--------------------------------
Function guid()
Local pGUID,rGUID
Declare Integer UuidCreate In 'RPCRT4.dll' String @pguid
Declare Integer StringFromGUID2 In 'Ole32.dll' ;
string rguid, String @lpsz, Integer cchMax

pGUID=Replicate(Chr(0),16)
rGUID=Replicate(Chr(0),80)

Return Iif(Inlist(UuidCreate(@pGUID),0,1824),;
Iif(StringFromGUID2(pGUID,@rGUID,40) # 0, ;
Strconv(Left(rGUID,76),6), ""),"")
Endfunc

Function lapid
LcLapId=Left(Sys(0), Rat("#", Sys(0))-2)
Return LcLapId
Endfunc



Soykan OEZCELIK

 

[Mike Lewis]

Kosta,

Your code looks good. But if I can make one small comment: Guids are guaranteed to be unique, but they are 16 bytes long, which is awfully long for a primary key. A Smallint with an Identity setting is only 2 bytes, and an Int is only 4 bytes.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kosta]

Thanks for the commnet,
Yes but experts on UT suggest for it c(38) pkey for guid thats way i preffered this.

Soykan OEZCELIK

 

[Olaf Doschke]

Hi kosta,

Yes, the WIN API returns a character GUID as 38 char long string. You could shorten that to 32 bytes, because it contains '{','}' and 4x '-', so only 32 chars are relevant. And those are 0-9,A-F, so it's hexadecimal, which could be converted to half the length (2 hexadecimals make 1 byte).

It costs time to compute this compact GUID and it may be harder to import/export this, and so it may be better to stay with the "uncomrpessed" string.

GUID will of course make it easy to identify records and mix new records from different local databases, that's perfectly okay. If you want shorter IDs, you have to search for or invent some way for generating other Global Unique IDentifiers, or you will run into the problem of cascading updates of primary and corresponding foreign IDs, which isn't easy.

There still is a problem with conflicting changes of data (record was differently edited in database A and B, maybe even one time modified, one time deleted). Those problems are tough even with GUID, similar to TABLEUPDATE() conflicts in multiuser environments, but the problem arises not when editing/inserting data, but when synchronizing data with the server database. You may apply the same strategy of "last change wins" to solve those conflicts, but it's less transparent to the users, why and when such conflicts arise.

It's preferable to have a certain responsibility for some portion of the database, like customers a-f,g-l etc. or "projects" or whatever domain of data, then changes in the same domain on different local (partial) databases are more seldom.

Bye, Olaf.

 

[kosta]

i you were me,
how would be the unique key via guid (more short) and how would be the synch.. codes for this goals ( between different tables )

Soykan OEZCELIK

 

[Mike Lewis]

Kista,

but experts on UT suggest for it c(38) pkey for guid thats way i preffered this.

Well, it's a matter of opinion. My view is that, since the Guid is being used a key, it will frequently be involved in comparison and lookup operations. Therefore, for performance reasons, it should be a short as possible. Guids are 16 bytes (as Olaf explained, they are not C(38)), which in my opinion is too long.

A 32-bit integer would be better, performance-wise, although I accept that there might be other reasons for choosing a Guid.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kosta]

Yes , i think guid seems no headache about this solutions..

Soykan OEZCELIK