MySQL data in OpenERP

Albert Cervera i Areny 5 dic. 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:

#!/bin/sh
createlang plperlu $1
psql -f /usr/share/dbi-link/dbi_link.sql $1
psql -f dbi-link.sql $1

The last call installs dbi-link.sql which is the file that contains information about the external (MySQL in this case), database:

UPDATE
pg_catalog.pg_settings
SET
setting =
CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
THEN setting
ELSE 'dbi_link,' || setting
END
WHERE
name = 'search_path'
;

SELECT dbi_link.make_accessor_functions(
dbi:mysql:database=external_mysql_database;host=mysql.server.com',
'username',
'password',
'---
AutoCommit: 1
RaiseError: 0
',
NULL,
NULL,
NULL,
'mysql_schema'
);

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>

class nan_mysql_storage(osv.osv):

Arriba