Search for domain can return wrong results.

[lcx]

The by_domain scope uses a like %domain%

scope :by_domain, lambda { |domain| where("domains like ?", "%#{domain}%") }

This can cause problems if you have for example 3 stores with these domains:

munich.foo.com de.foo.com ch.foo.com

When search for %ch.foo.com% the first result will be munich.foo.com which is not what is expected.

changing it to domin% also won't solve the issue as this will break things when using multiple domains per store.

[radar]

We will have a better solution for this when we release our new extension which will be using PostgreSQL schemas so that magical queries like this aren't required.

[lcx]

oh, so this was no joke about dropping MySQL support? Noooooo..... :)

[radar]

Attempting to do multi-tenanted stuff within MySQL is insanity. Scoping queries with WHERE clauses is the typical way, or disconnecting and reconnecting to databases is the other. They are both poor solutions, because you forget to scope and you're screwed.

PostgreSQL on the other hand has DB schemas which makes this incredibly easy. Setting the search_path for a query enables PostgreSQL to only search the right schemas for the data. Having a piece of Rack middleware which detects the incoming domain request and switches the search_path is exceptionally easy to do and because it's only one SQL query to do the switching for the entire connection (vs disconnecting + reconnecting), it's lightning fast.

PostgreSQL is the only production-ready open-source database in my mind*. SQLite3 is a toy (like Lego), MySQL is only a little better than that, MongoDB... don't even get me started. PostgreSQL trumps them all. You will be assimilated. Resistance is futile.

* Of course Oracle is as-good (so I hear), but you pay through the nose, ear and thumb for it.

[lcx]

got to the schema part in your book. You have a point there and I do also have some PostgreSQL installed but I just don't feel comfortable due to the fact that I just lack experience with it. But I might have to get used to it can't hurt :) Regarding SQLite and Lego, c'mon, don't insult lego!

Regarding Oracle, oh please don't get me started! just deleted a huge oracle rant

[rharriso]

I have a patch (with tests) that matches entire lines. Its gross, but it works. I tried using regex. But as you might imagine that doesn't work well across database types.

I'm all about Postgres, so the move is fine with me. My motivation is that we usually run our dev env on SQLite3.

--Edit-- Don't use the patch, I totally forgot that there were two different line ending characters.

[beefsack]

Hi @radar, would it be possible to get an update on this issue and any plan for the PostgreSQL schemas move? Will there be a migration path from existing installations to PostgreSQL schemas?

Cheers :smile:

[radar]

@beefsack I've only recently discovered that PostgreSQL has issues backing up databases with a large number of tables in it, and therefore the schemas approach is probably not the best approach if you want a lot of schemas ( > 50).

We still need to work out a good way of doing this, and unfortunately all I can come up with right now is forking Spree and putting explicit scoping in the controller.