Postgres support

[volkanunsal]

The SQL file Polo generates doesn't work with Postgres on my setup. I noticed there has been some discussion of support for Postgres already, but only partial support has been implemented. Can anyone fill in the the status of the support for Postgres?

[nettofarah]

hello, @volkanunsal.
Polo should work fine with Postgres, except for a few advanced features that are MySQL only.

Can you be a little more specific? Maybe post some stack traces?

[volkanunsal]

The override and ignore directives don't work on postgres. That syntax is
mysql specific apparently.
On Mar 22, 2016 5:40 PM, "Netto Farah" [email protected] wrote:

hello, @volkanunsal https://github.com/volkanunsal.
Polo should work fine with Postgres, except for a few advanced features
that are MySQL only.

Can you be a little more specific and post some stack traces to this issue?

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub
#36 (comment)

[nettofarah]

That's correct, @volkanunsal.
We mention it in the docs: https://github.com/IFTTT/polo#advanced-usage

Note: This feature is currently only supported for MySQL databases. (PRs for other databases are welcome!)

[nettofarah]

I have looked into working on Posgres support for these features, but I couldn't find any PostgreSQL function that would allow us to build them. I was really bummed when I learned Postgres didn't support these features :(

[volkanunsal]

For the ignore directive, this seems to work:

INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

[volkanunsal]

My only trouble is I haven't been able to figure out how to get Arel to generate INSERT INTO ... SELECT statement.

[nettofarah]

This is awesome!
Wanna take a stab and start a PR?

I can help!

[volkanunsal]

Another trouble I encountered was the backticks around table and column names. I had to gsub them to make Postgres happy.

[nettofarah]

Interesting... My guess is that we'll probably need to implement separate drivers for different database adapters, as these differences start to become more evident.

[volkanunsal]

This is a crude version of the code I got to work today using regex substitution. It's really ugly though...

def ignore_transform(inserts)
  inserts.map do |insert|
    table_name = insert.match(/INSERT INTO `(.+?)` /)[1]
    id = insert.match(/VALUES \((\d+),/)[1]
    insert = insert.gsub('`', '')
    insert = insert.gsub(/VALUES \((.+)\)$/m, 'SELECT \\1')
    insert << " WHERE NOT EXISTS (SELECT 1 FROM #{table_name} WHERE id=#{id});"
  end
end

[nettofarah]

This is great progress!
Do you want to start a PR so we can discuss the code a bit more?

[volkanunsal]

I'm working on a PR. Another thing I wanted to note is that the SQL translator doesn't work for some native Postgres columns, such as JSON. This column is serialized in YAML format by ActiveRecord, which is not acceptable as a JSON column input to Postgres.

[dmnelson]

Hi @volkanunsal, are you working on master? I've submitted a PR in the past (#26) that should fix the issues with Postgres (besides on_duplicate and other stuff).

I've been using Polo with Postgres, I have hstore columns on my schema and it has been working like a charm. I assume json would be the same.

[volkanunsal]

@dmnelson I just saw that. This may address the issue of JSON columns, but it complicates things a bit since the regex matcher above doesn't work now. Do you have any ideas how to get Arel to spit out something we can use to ignore duplicate rows?

[volkanunsal]

I think I solved my own problem. This updated snippet works. We have to change the signature of the method a little bit to receive the records as well.

def ignore_transform(inserts, records)
  insert_and_record = inserts.zip(records)
  insert_and_record.map do |insert, record|
    table_name = record.class.arel_table.name
    id = record[:id]
    insert = insert.gsub(/VALUES \((.+)\)$/m, 'SELECT \\1')
    insert << " WHERE NOT EXISTS (SELECT 1 FROM #{table_name} WHERE id=#{id});"
  end
end

[volkanunsal]

Submitted a PR.