This guide aims at creating an environment for development for testing an convergence installation of OpenCart and Vtiger on MYSQL database, taking advantage of the new features of MySQL 5.1 triggers present on, in order to synchronize all data.
This technique, very popular, will be collected in our project SyncMyShop.
Our intention is to create an open source portal completely dedicated to the needs of synchronization between Business Management Software, ERP, CRM, with popular ecommerce platforms based on MYSQL, as OpenCart, Magento, Oscommerce, Spree, Wordpress etc..
And enclose what is a common need for multiple platforms in a single language.
The technique explained here is highly reproducible on other systems.
We invite you to experience and let us know your results to other situations.
Moreover, all the packages that we have created to date will be released in a complete and fully open source on http://syncmyshop.scarletkangaroo.com (in progress)
- Synchronize data of a registered user on OpenCart in Vtiger
- Synchronize data of an order in OpenCart created in Vtiger
- Synchronize data of a product in OpenCart created in Vtiger
- Create a PHP control panel to manage the options.
- Synchronize directly, without passing through webservice, or cronjob
- Making full use of the speed and power of MYSQL
- Direct Input
- No changes to the PHP code that will be "immaculate" ;-)
- MYSQL root privileges needed
- Inconsistency of some procedures that require the same operation sql, for different phases. Later we will see what we mean.
The rules of the game
First, let's briefly define the capabilities of the triggers in MySQL 5.
The triggers, can be created and executable only by root or superuser, allow you to run a script MYSQL at startup of of any event in Mysql (INSERT, UPDATE, SELECT) just as a trigger ready to shoot.
This script will contain the same data received by the original query, and more can take advantage of multiple SQL to track other data.
The use of JOIN, however, is prohibited.
The trigger can be started in two specific phases:
- Before the event (BEFORE)
- After the event (AFTER)
This allows us then to use two SQL scripts for each event.
The syntax for creating a classic trigger looks like this:
/*definire l'utente che azionerà il trigger - root, oppure un utente con privilegi SUPER*/
CREATE DEFINER = `root`@`localhost`
/*denominare il trigger, per questione di praticità il nome del trigger sarà preceduto dalla sequenza*/
/*Definisce il momento dell'operazione, e su quale tabella agire, in questo caso PRIMA di qualsiasi inserimento nella tabella oc_product*/
BEFORE INSERT ON `oc_product`
/*Definisce l'azione da eseguire, in questo caso AGGIORNA la prima riga nella tabella oc_product_trigger con i dati inseriti nella tabella oc_product*/
ROW UPDATE oc_product_trigger SET product_id = NEW.product_id,
model = NEW.model,
sku = NEW.sku,
upc = NEW.upc,
location = NEW.location,
quantity = NEW.quantity,
stock_status_id = NEW.stock_status_id,
image = NEW.image,
manufacturer_id = NEW.manufacturer_id,
shipping = NEW.shipping,
price = NEW.price,
points = NEW.points,
tax_class_id = NEW.tax_class_id,
date_available = NEW.date_available,
weight = NEW.weight,
weight_class_id = NEW.weight_class_id,
length = NEW.length,
width = NEW.width,
height = NEW.height,
length_class_id = NEW.length_class_id,
subtract = NEW.subtract,
minimum = NEW.minimum,
sort_order = NEW.sort_order,
status = NEW.status,
date_added = NEW.date_added,
date_modified = NEW.date_modified,
viewed = NEW.viewed
The limitation of one executable script to phase (which hopefully will be solved in the future as well as SQL Server and Oracle DB) and the inability to use the JOIN presents us with a scenario very problematic in our case, namely:
The creation of any instance in Vtiger takes an average of six queries, sometimes less, sometimes more.
Take for example the inputting a contact:
Cascade structure vtiger will be to update the following tables:
How to work around this limitation?
Our system provides for the creation of additional tables, consisting of a single record, which activate the UPDATE triggers on each table, this allowing you to create unlimited cascading triggers.
Begin to prepare the environment for example in the next step