Upgrading OpenBravo POS software

Tuesday, 15 November 2011

An introduction to OpenBravo POS software and Unicenta OPOS

OpenBravo is a point of sales (POS) software. It can be successfully used as a restaurant software, for keeping track of tables, orders and customers. It can be handled very well with a touchscreen or a mouse.

It can be configured for for multiple devices such as magnetic stripe reader(for quick cashier/operator login), POS ticket printers, ticket display screens and cash drawers.

It prints order tickets, it generates statistical reports on sales by product, by cashier, by date range, and it manages product stock.

OpenBravo POS may be installed on several computers while sharing the same product and sales data.

OpenBravo POS system is free software, licensed under the GPL, so you may use it and modify it freely.

It is based on Java, so you can use it on any Mac, Linux and Windows PC.

Unicenta OPOS , the succesor of OpenBravo POS, is an improved version of Openbravo. It features many bugfixes and enhancements, such as a clean and more intuitive interface, the ability to print food products from orders separately as kitchen tickets(when used as restaurant POS).

Upgrading Openbravo 2.30.2 to Unicenta OPOS 2.80 beta.

Openbravo POS was installed on an Apache Derby database. I tried initially to migrate to Unicenta on Apache Derby, but because of foreign key constraints the tables needed to be copied in a specific order, otherwise the database system throwed errors about key constrains violation.
Being familiar with MySQL RDBMS, I took a change on migrating to MySQL, being that it supports ingoring key constraints while manupulating the database.

A couple of Java based useful tools for JDBC database manipulation are

SquirrelSQL: a powerful JDBC database administration GUI, which features adanced scripting, database diff visual tool, and more. I used the latest snapshot version
OpenDBCopy : A valuable tool designed for JDBC data migration. i had to configure the Mysql connector manually

I did a clean installation of Unicenta OPOS. I configured it to use a MySQL database(launch Unicenta, it will prompt you for database setup):

driver library: browse for mysql.jar in unicenta's lib folder, ord download the java MySQL connector and place it in the aforementioned lib folder
driver class: com.mysql.jdbc.Driver
driver url: jdbc:mysql://localhost:3306/unicenta

You need to create an empty MySQL database(i my case it's named 'unicenta'), Unicenta OPOS will automatically generate the schema and populate the database.

At this point we need to empty the MySQL database(by truncating most of the tables) so its data does not conflict with our Openbravo data. I used SquirrelSQL for this. (any MySQL management tool or even the console client will suit this task).

Make sure you don't empty(and don't import) the RESOURCES, APPLICATIONS tables, and the SHARED_TICKETS table. APPLICATIONS is used by Unicenta to determine if the database is already setup, and SHARED_TICKETS may cause you some trouble because of indexes.RESOURCES is substantially different on Unicenta, so you'll have to edit the resources manually from the upgraded software itself.

Okay, now fire up OpenDBCopy.

Select the plugin "Create insert sql scripts for destination db" . This is because direct copy will NOT work because of foreign key constraints. I tried to do a direct copy and let OpenDBCopy determine the right table order, but it failed to do this.

When configuring the "Create scripts..." plugin, you must select a working folder at step 1, or the execution will fail. After configuring OpenBravo database as the source and MySQL database as the destination, you will be able to generate sql scripts(a SQL file for each table).

Next, we need to disable MySQL foreign key checks before running the SQL scripts.

It is important to run

SET FOREIGN_KEY_CHECKS=0;

on MySQL database BEFORE importing, or else the import will fail.

Next you may use any tool (such as SquirrelSQL) to run the SQL scripts generated by OpenDBCopy on the destination MySQL database.

I did this by a one-liner in Linux (which concatenates the SQL files and prepends the foreign key statement )

echo SET FOREIGN_KEY_CHECKS=0; >../all.sql ; cat * >>../all.sql ; mysql -uroot -pthe_password --database unicenta  <../all.sql

Before this statement, I had to change directory in the terminal to ~/.opendbcopy/inout

Wait, this isn't over. After doing all this, I opened Unicenta OPOS. to my despair, there were no pictures on any product, and the sales screen was not working(because the floor had a picture as well). It seems that OpenDBCopy somehow messed the BLOB fields when creating the SQL scripts.

I a spark of inspiration, I opened OpenDBCopy again, and selected the "Copy data from a source into a destination database". Now the data is present both in OpenBravo and Unicenta database, but the images are messed up. But this time key checks will not fail.

You need to copy all the tables containing binary images (FLOORS, PEOPLE,PRODUCTS, CATEGORIES). OpenDbCopy will handle BLOB fields the right way(but on the other hand it does not remove foreign key checks wich are the source of our pain).

Thank you for reading. Congratulations for saving 2 days of headaches!