The motivation for this came from a project where an ETL service was responsible for handling synchronization of data (such as departments, customers and contracts) across several databases and applications.
The ETL tool didn't have a simple way to make XML-RPC calls, and writing directly to OpenERP's tables is not a good solution.
This was solved using a relatively simple "hybrid" solution. The PushSQL module provides a database table for external apps to write data to imported to OpenERP, using plain SQL INSERT statements. A scheduled job regularly looks for new rows in this table and processes them, making the intended changes to OpenERP data.
The table , import_pushsql, has two mandatory columns: model and data. The first is the target model, such as res.partner. The second contains the data to import, using the format for CSV files, but using TAB instead of comma as the column separator.
For example, using PSQL:
INSERT INTO import_pushsql (model, data) VALUES ('res.partner', E'id\tname\tref\nres_partner_XXX\tDemo Customer\tXXX')
Or, if using SQL Server:
INSERT INTO import_pushsql (model, data) VALUES ('res.partner',
'id' +char(9)+ 'name' +char(9)+ 'ref' +char(10)+ 'res_partner_XXX' +char(9)+ 'Demo Customer' +char(9)+ 'XXX'
)
The scheduled action uses this to run a standard import_data ORM method, writes the result into the log column, and changes the state to 'done' if successful, or 'cancel' if an error was found.
Compared to the "pull" approach used by the import_odbc module, this "push" approach can provide a few advantages:
- No need to install other database clients and middleware in OpenERP's server.
- It uses less resources on OpenERP server, because there is no need to connect to other servers.
- Event driven data sync, where an action in another system immediately triggers a write on OpenERP that can be processed within a few minutes.
Overall, the PushSQL module can provide a very simple and straighforward way for business apps to safely send data to OpenERP, in scenarios where using the API is not an option or would be too cumbersome.
The import_pushsql can be downloaded either from OpenERP Apps or from Launchpad using: bzr branch lp:reis-openerp-addons
Looks interesting, thanks, will try it out.
ReplyDeleteThanks. Please do comment your experience.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteAlthough it shouldn't be hard to do it, I'm not planning to because I'm using the Kettle OpenERP steps instead. But if you need that contact me through https://launchpad.net/~dreis-pt/+contactuser.
ReplyDeleteThanks Daniel. I installed it on V7 and it works just fine.
ReplyDeleteIt is a good piece of work.Thanks for sharing this.
ReplyDeleteOpenERP in Kerala
Very intresting to read your article.
ReplyDeleteweb designing in thrissur