Project Results

For those who don't want to read this stuff, at the end of this post are some vids and pics.

The project results are the following modules:

Tryton Synchronize Module

The module provides synching capabilities in both, in and out direction.
The algorithm behind is quite simple: For each synch the system stores the latest time stamp. This time stamp is used to determine changed records on the remote system. The foreign ids are stored in a translation table where they can be looked up for example when existing records changed.
The fields of the models can be set up to the users needs like default values to reduce network traffic or if the field should be synched at all. The fields also have a match_field flag which is used if it is desired, that the system should try to find records by matching the fields content. All fields with match flag will be compared to determine the appropriate records.
This is useful with standard models like uom or uom.category. With this models maybe the name field is enough to determine the right record, if no matching record found it will be created. So standard records / uom wont be created.

For looking up translations the id of the source record,
the model and the server is used as primary key. This enables using the same translations for multiple synchs with different configurations of the synch-model.
For example the product.category model:
Its in a tree structure realized with a parent field referencing itself.
For synching this model at the first time all fields except the parent field can be pulled. This pulling will create all translations needed for the next synch. Because both synching configurations use the same server and model, the same translations are used and all parent fields are now translatable.

The communication with extern systems are handled through a simple interface which can be implemented quite easy for other systems like other ERP systems or databases. (see synchronize_mysql)

The direction of synchronizing can be changed if the both connection instances are exchanged with each other.

For providing ready to use configurations the existing trytond xml system was not flexible enough to create the needed records. Because of this a simple xml import method was added to the synchronize module. This import method can be used by other modules to provide configurations (see synchronize_product)

Available at:
http://hg.holtzberg.de/synchronize


The Synchronize Product Module
The module provides a ready to use synchronize configuration for synching products.

Available at:
http://hg.holtzberg.de/synchronize_product

The Synchronize MySQL Module
The module provides a sample implementation of the connection interface to synch with a MySQL database.
For each MySQL server record, translations for the tryton fields have to be added. Translation means:

• Tryton model name to the corresponding SQL statement / table name
• Tryton field name to SQL field name / SQL

Available at:
http://hg.holtzberg.de/synchronize_mysql


The POS Price Label Module
Provides reports for printing barcode labels from PDF. Products have to be added to Printjobs which can be printed out.

Available at:
http://hg.holtzberg.de/pos_price_label

The POS Cash Module
The module provides simple data structures which should be easy to synch. In this structures are the cashing transactions stored.
The idea is to implement all the logic of the POS-System in a trytond module.
All manipulations are made through RPC methods which a client can easily call without thinking about logic. For this methods some wizards are provided to enable testing on the standard Tryton GTK client.

The module also handles the hardware like printer and customer display with ESC/POS commands. (see ESC/POS module)

Available at:
http://hg.holtzberg.de/pos_cash

The POS Client
For simplicity a separate client was written with the new PySide Qt binding. The client runs full screen and calls the RPC methods mentioned before.
The client can easily be styled with Qt style sheets. This is useful if a small display is used or in other environments.

Available at:
http://hg.holtzberg.de/pos_client

The Python ESC/POS Module
For the communications with the hardware the python-escpos module (http://code.google.com/p/python-escpos/) is used and was extended with some display functionality. Also some not working bar code types have been added to the module.

Available at:
http://hg.holtzberg.de/escpos

Pics and Vids
Setting up the POS
POS in action
Final receipt

Finally
I had fun with working on this project and want to thank my mentor Udo Spallek for supporting me very well. Also thanks to all the others on tryton IRC channel. Also thanks to Google for providing such a cool stipend.

ESC/POS Display support added

The python-escpos lib from http://code.google.com/p/python-escpos/ has been extended by ESC/POS display support  and some other stuff.

Here are some pictures of how the hardware output looks like:

First product scanned
Second product scanned
Total amount
Drawback
Receipt

Thanks to the MEGA Bike corporation  which allowed me to publish the current development status of the receipts with their logo.

pos_cash module added

The whole logic on the POS terminals should be handled by this new module. It's based on a simple data structure to keep things simple.
In the configuration form can be set up the sequence used for enumerating the receipts, the company (used for the company data on the receipt like address etc.), the logo which will be printed and the printer port the receipt printer is connected to.

All operations like adding  or removing products will be added into pos_cash.sale.lines.
Every pos_cash.sale.line appears on the receipt with a tax code. At the end of the receipt a table of taxes is printed. With this table customers can translate the tax codes from the sale.lines to the real tax.
The receipt_code + company.id is printed at the end of the receipt as a code128 type B bar code.

After 50 meters of receipt testing, the receipts look quite nice ;)

The module is available at:
http://hg.holtzberg.de/pos_cash

The next things will be:

• Adding reversal functionality 
• Adding workflow to pos_cash.sale
• Adding customer display support
• Writing a kiosk client

Modules made working with Tryton tip version

Several changes made that the modules work with the newest Tryton development version.
The automatic creation of the dependencies when a model is added to the Synchronize module has been removed. With this the synchronization tree is more slim when default values are used furthermore models can easier added via XML.

All code review issues have been applied to the code.

A new module for synching with MySQL databases is available. It provides easy creation of mapping tables. Each Tryton model that should be synched can be adapted to data structures on a MySQL server with SQL-statements. The connection can easy be replaced by other DB-2.0 API compliant connections.
The synchronize_mysql module is available at:

http://hg.holtzberg.de/synchronize_mysql

Code cleaned up and committed for reviewing

Before the codereview the code has been cleaned up tested.

• Classnames have been changed according to the _name of the models.
• Classmembers renamed according to the projects specifications
• Some methods changed to private
• Changed comments
• Whitespace removed

We will see what the review brings...

Design restructured for easier use with 3rd party systems

The generation of the domain for searching for changes has moved to the connection classes. With this its easy possible to replace the standard domain scheme with an specialized format such as simple WHERE statements in strings. Thats usefull when implementing a connection class for a 3rd party system maybe DBMSs.

A simple example for implementing a connection to a MySQL database is available at: http://hg.holtzberg.de/synchronize_koleso

Its quite easy to implement synching capabilities  with WHERE statements as domains and a mapper dict for field transformation.

When a default value is defined on a relation field, the recursive dependent model is not synched. To get the MySQL example work with an own database, some fields have to be set to default to prevent synching the recursive dependent models:

• product.template.category => set to the id of the local desired product.category record.
• product.template.cost_price_method => fixed
• product.template.default_uom => 1 (should be the index of Unit by default)
• product.template.type => stockable

With the default values set, the only models for which data will be fetched through the MySQL-Connection object will be product.product and product.template.

Synchronize Product Module added

A new module for setting up the product models including the dependencies  for synching is now available at http://hg.holtzberg.de/synchronize_product

After installing the module it is needed to add a remote server in the admin menu. That server has to be set in the root entry of the added product models.

Push support added

The communication with the local and remote systems is now handled through connection objects that implement a common interface. This connection interface can be implemented also for other systems for which synching is desired.

The two connections to synch between are changed if synching in the other direction is wanted. The synchjob needs its own user to distinguish between real local changes and changes that result of a previous pull operation. The create_many method for speed improvement is deferred for now because of simplicity.

The user interface for configuration has also been changed to a more easy one.

The next thing is to create a module that adds the product model as a synching model. With this reviewers only have to install two modules and add a remote server before testing a synchronization.

Code cleaned and sqlite support added

Determining ids from a sequence with postgresql is not a problem but sqlite doesnt use such sequence objects, but for adding data into the database it is required to have the ids before insert. In sqlite the sequences are handled in a special system table sqlite_sequence which stores the last used value of each sequence. By manipulating this table it is possible to get unique sequence ids before inserting the records.

Another issue is that sqlite handles sorting of null fields the other way around than postgresql and does not support ORDER BY xxx DESC NULLS FIRST. Thats the reason why at the moment it isn't possible to synch with a sqlite based Tryton-server. (The slave can be sqlite based)

Updating of existing records is now possible. Furthermore a short screen cast is available on youtube to demonstrate the generic way of synching with Tryton servers. (http://www.youtube.com/watch?v=iAGP5fpdOAM)

Handling of function fields and default values is not implemented yet. Also MySQL has not been tested yet but there will also be the sequence issue.

For updating records prepared statements are not used but can be implemented if performance increase is needed. By now the write_many method only wraps the normal write method.

Design restructured

The synchable class has been removed, the translation between local an foreign ids is now available in a translation model. With this the module can easily installed without editing the ModelStorage.

The organization of the models to synch has also been changed. When a model is added into "synchronize.model" the model will be searched recursively for dependencies. All dependencies are added as children to the proper parent. For each model the fields that are required are automatically added, additional fields can be added manually. With this unneeded fields wont be fetched to save traffic.

The next steps will be:

• Add handling for function fields to create_many method
• Add updating for records (not much work)
• Thinking about synching models with other systems for migration. (Thats not part of the GSoC but I really would like to have this functionality)
• Testing if the system works correctly

The repository is now available under http://hg.holtzberg.de/synchronize

Speed improvement

After profiling the syching procedure I found out that the bottleneck was not the foreign_id to local id relation but the ModelSql.create method which only allows to create single datasets. I added a create_many method to the Synchable class to create datasets with prepared statements which improved the speed very well. The RAM consumption is now even less than before.

Now I  can synch with another Tryton server over the Internet 2400 product.product datasets with all needed dependencies in kind of exactly one minute.

Now the bottleneck has moved to the fetching of records from the master server, but there are lots of possibilities for further optimizations.

The next days I will clean up the code and push it to a public repository for reviewing.

First try of synching with remote Trytond servers

I implemented a synching system for tryton models for testing.
The communication is based on the normal NET-RPC which is also used by the GTK-client. The advantage over a separate solution is that a "master" server needs not to be modified before another server can synch with it.

My first tests have shown that this way of synching is possible.

The next steps will be:

• Improve synching speed with some kind of caching
• Preventing creation of system records with foreign ids like the uom model
• Possibility of excluding fields from synching