Database Synchronization

De Stoq Wiki
Ir para: navegação, pesquisa

Notes about database synchronization

We'll use XMLRPC to communicate between the office and the shop

A final solution should have password protection (on HTTP level) but the initial implementation will lack one. The shop will listen on commands and the office will connect and decide what it should do.

Protocol

  • clean() -> nothing
    • Erases and creates the basic SQL schema
  • sql_prepare() -> sid: int

' sid: synchronization id which needs to be sent to all the commands. Prepares raw sql insertions. If successfull sid will be > 0

  • sql_create(sid: int, data: str) -> nothing

' sid: sync id ' data: gzip compressed chunk of raw sql commands.

  • sql_finish(sid: int) -> nothing

' sid: sync id

Finishes sql insertion sequence

  • setidstart(id: long) -> nothing

' id: is the initial value for all table id sequences which has a policy set to SyncPolicy.BOTH.

  • diff(since: timestamp) -> data: str

' since: Fetches the differences between since and now() Returns a string with gzipped compressed raw sql commands or None if there is nothing more to update.

Policy

In a normal situation the Office is the Source and the Shop is the Target.
  • FromSource: Product, Service
  • FromTarget: Sale, Payment
  • Both: Person
  • Initial: Parameters and System specific

Synchronization domain object

class BranchSynchronization(SQLObject):
    timestamp = DateTimeCol(notNone=True)
    branch = ForeignKey('PersonAdaptToBranch')
    policy = StringCol(notNone=True)

It's only created once per branch. It contains a string which is a refrence to a policy defined in stoqlib.database.policy and a timestamp which is updated each time a synchronization is done.

SequenceIDs

To be able to synchronize two ways we need to have unique IDs for certain tables. This is done by allocating a big range (say 10 million) for each branch. It's easily done in PostgreSQL by using the setval(), for instance to set the sequence associated with Persons id column:

SELECT setval('person''id''seq', 100000000);

The next row inserted into person will have an ID with value 100000001.

Implementation

[1]: committed