Make compatible with Redshift #39

Closed
tcha-tcho opened this Issue Feb 16, 2013 · 13 comments

Projects

None yet

5 participants

@tcha-tcho

Hi,

Amazon Redshift is build on top of PostgresSQL. But some types and functions do not work. What do you think about make node-orm2 compatible with this new Database?

The limitations:
http://awsdocs.s3.amazonaws.com/redshift/latest/redshift-dg.pdf

Best regards

@dresende
Owner

It's ok to support more SQL databases. I don't know the difference between Redshift and a common PostgreSQL database server, but you can probably use the postgres driver. ORM doesn't use all possible SQL commands, just the more common.

@tcha-tcho

Hi Diogo!
At page 96 of the reference you can see unsupported features, functions and types. I'm getting problems with "RESULTING *". What do you thing of make those changes together?

Cheers

@dresende
Owner

You mean RETURNING *, it's used to get the id the the last INSERT. I just need to know how to get it in Redshift.

@dresende
Owner

I can't seem to find anything on the web related to this query and I'm not that good at postgres. Do you know a way around of knowing the last insert id?

@tcha-tcho

Can't we store this at the server? when the Postgres returns "ok" we know that it is ok for that ID? I gonna search around too.

@dresende
Owner

The problem is that ORM needs to know the insert id to store in the Instance, so you can make a few more changes and save again, or return that id to something else (like an association).

@dresende
Owner

Try the latest commit. I'm going to post an update to npm (2.0.1) since I found some postgres bugs in the process. To use Redshift driver (which is just the postgres driver with 1 tweak for now) do:

orm.connect("redshift://user:pass@host/database", function (err, db) {
    // ...
});
@dresende dresende closed this Feb 21, 2013
@tcha-tcho

Prodigious! :)

@kfuchs

I'm trying to get Doctrine2(php) to work with Redshift as well and am facing the same issue. I do not know how to generate the id. I'm hoping someone here can let me know how you guys did it :)?

@dxg
Collaborator

See the comment here: de86054 and compare the insert functions for standard postgres and redshift

@pmgtech

I think my issue is getting the NEXTVAL(). Since Redshift uses IDENTITY and not SEQUENCE. Did you guys have to figure out how to generate the next primary key?

@pmgtech

Looked at the code. So your adding a temporary table to store id's? Is that correct?

@dresende
Owner

The code should work if you already have a table but I think it need to use sequences. I don't know much about postgres so I can't help you out on this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment