This question has been flagged
4 Replies
33515 Views
developmentdatabaseexternalintegrationpostgresql
Avatar
Moin

We have integrated OpenERP with an external application (Java) which has its own database (Postgres). We want to create a frontend in OpenERP which gets its data from that database and commits any changes we make in OpenERP back to that database. We don't want to import that data in OpenERP database first since there are millions of records to handle and we don't want duplication or massive performance delays. How can this be implemented?

EDIT I essentially want to intercept the data source of the list view and form view to plug my own data in. From the console logs it seems like the search_read method is responsible for acquiring data that is finally displayed, is it possible to plug my own data in that method? Thank You.

Avatar
Discard
Avatar
Mohammad Alhashash
Best Answer

If the external application uses integer primary keys, you can use PostgreSQL Foreign Data Wrapper (doc) to link the external data to the OpenERP database and create new models to access these tables.

You need to have PostgreSQL v9.1 or higher. There are FDWs for many databases and other data sources including PostgreSQL iteself, Oracle, MySQL.

Start by creating foreign tables in the OpenERP database mapped to the external tables/views. OpenERP meta data columns id, create_uid, create_date, write_uid, write_date are required, you can create these columns in a view based on other columns or as a static data and use that view as the model table.

Then, you should create OpenERP models for these tables just like normal models. Each model must have an attribute _table = '_Linked_Table_Name_' to have OpenERP using the linked table instead of creating new one.

Foreign tables are read-only. So, you have to override the create(), write() and unlink() methods to update the original table. Other OpenERP functionality like search should work directly.

If the external table does not have integer id, you may use integer hash function to map the primary key and all foreign keys in other tables to integers (should be 64bit to minimize hash collision probability).

Avatar
Discard
Moin
Author

Thanks for your answer, I looked into experimenting with FDW but I'm stuck as to how shall I go about 'installing' the pgsql_fdw library? There's no download file on the page you linked and google didn't yield any results either. What do I need to do to produce a simple example? Thank You.

Mohammad Alhashash

@Moin What is your operating system and postgresql version?

Moin
Author

@Mohammad Ali Ubuntu 12.04, postgresql 9.1 and OpenERP 6.1

Avatar
Yohanes Iwan Sugiarto
Best Answer

Maybe you can create fields of type function in an object which will act like a proxy for the other database/external application you have. Just make sure your external database/app is accessible using Python. But this approach would make the fields read only.

If you want to allow changes to be made from OpenERP: Create object with stored fields (not function fields) which mirrors the external database field.

  • Override 'read' method to populate the mirroring fields by reading values from the external database.
  • Override 'write' method to allow changes from OpenERP frontend to be made to external database.

e.g:

def read(self, cr, uid, ids, fields=None, context=None):
    #Populate local fields with values from external database
    res = super(your_proxy_object, self).read(cr, uid, select, fields=fields, context=context)
    return res

def write(self, cr, uid, ids, vals, context=None):
    #Write changes to external database
    return super(your_proxy_object, self).write(cr, uid, ids, vals, context=context)
Avatar
Discard
Moin
Author

This makes perfect sense, I knew about the write method but wasn't aware of the read method. This is exactly what I was looking for, Thank you. Could you please answer the question at http://help.openerp.com/question/1040/how-does-the-orm-read-method-work-exactly/ regarding how to use the read metho

Yohanes Iwan Sugiarto

Sure, I just posted an answer about how the read ORM method work. Just a simple example but I hope it helps.

Avatar
Daniel Reis
Best Answer

There is a simple module to access data in foreign databases from OpenERP: base_external_dbsource in branch lp:openobject-extension. I have used it toaccess data from Oracle an SQL Server databases. It should be difficult to extend it to also add write features.

You can find more about in it's manifest.

Avatar
Discard
René

Hello For testing I installed the module base_external_dbsource and created an externel data source. Connection to my external db is successful, but now I dont know how to use this connection in my custom module. Are there any informations how to use this connection and make the external data available in a module ? Can someone help? René

Avatar
René
Best Answer

Hello For testing I installed the module base_external_dbsource and created an externel data source. Connection to my external db is successful, but now I dont know how to use this connection in my custom module. Are there any informations how to use this connection and make the external data available in a module ? Can someone help? René

Avatar
Discard
Dinesh

Hello René, did you find any solution for this question?