Albert Cervera i Areny Dec 5, 2009
One of our customers has bandwidth and storage consumption information of it's customers in a MySQL database. The information is only needed when creating invoices but we wanted it to be browsable in OpenERP, so we went the dbi-linkway. dbi-link is a module for PostgreSQL developed in plperl , which makes it very easy to query/update/remove information from other data sources from within PostgreSQL.
The first thing we did was create a small script which installs dbi-link on a given database:
The last call installs dbi-link.sql which is the file that contains information about the external (MySQL in this case), database:
This creates a new schema in PostgresSQL called mysql_schema which already has all the tables in the mysql database. In our case we can simply:
SELECT * FROM mysql_schema.storage;
Now we need to create the models in OpenERP to view that information. Unfortunately dbi-link has one drawback and it's that all fields are imported as type TEXT in PostgreSQL. That means we'll have to create a new view in the model that will ensure data types are the correct ones:<br>