Bulk upsert with Ruby on Rails

Question

I have a Rails 3 application where I need to inject an XML file provided by an external system into a Postgres database. I would like to use something like ActiveRecord-Import , but this does not seem to handle the upsert capabilities for Postgres, and some of the entries that I will swallow will already exist, but it will need to be updated.

Most of what I read recommends writing SQL on the fly, but this seems like a problem that may already have been resolved. I just can't find him.

Thanks.

Answer 1

You can upgrade MySQL and PostgreSQL using upsert .

If you are looking for raw speed, you can use nokogiri and upsert.

It may be easier to import data using data_miner , which uses nokogiri and upsert internally.

Answer 2

If you are on PostgreSQL 9.1, you should use regular table expressions for writing. Something like:

WITH updates (id) AS ( UPDATE mytable SET ..... WHERE .... RETURNING id ) INSERT INTO mytable (....) SELECT ... FROM mytemptable WHERE id NOT IN (select id from updates); 

In this case, you first process the stream in the temp table, and then try to update the entries from the seductive according to your logic and insert the rest.

Answer 3

This is a two-step thing. First you need to extract the XML file. If it is provided by the user through a form that you are lucky with, you need to extract it using the standard HTTP lib ruby ​​or otherwise some gem, for example, mechanization (which is really really wonderful).

The second thing is very simple. You read the whole XML into a string, and then you can convert it to a hash with this point in code:

 Hash.from_xml(xml_string) 

Then you can analyze and work with the data ...