The use_sql feature is broken

[NielsKSchjoedt]

Hi,

I think the use_sql feature is not at all ready for production yet. I have an apartment.rb that looks like this:

Apartment.configure do |config|
  config.excluded_models = ["User", "Payment::Order", "PaymentNotification", "Payment::OrderDiscount", "ModelText"]
  config.use_schemas = true
  config.use_sql = true

  config.persistent_schemas = ['extensions']

  config.prepend_environment = false
  config.append_environment = false

  config.default_schema = "da"

  config.tenant_names = CountryManager.country_codes.map(&:to_s)
end

Now, I can see that when running Apartment::Tenant.create('testcountry') then pg_dump will output something that looks like this:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: da; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA da;

(...)

This of cause results in an error, because the default schema da already exists! From the code in: https://github.com/influitive/apartment/blob/development/lib/apartment/adapters/postgresql_adapter.rb#L165 there is nothing in the PostgresqlSchemaFromSqlAdapter that will remove the CREATE SCHEMA da; line.

On top of this, there is an issue with handling foreign keys as mentioned here: #128 The problem is, that if you have FK constraints across schemas (which often is the case if you have excluded_models), the default_schema name which those excluded models decides in, are not appended to FK statements.

Lastly the the test database will newer be able to be created in rails 4.1. The reason is, that rake db:test:prepare does not exist anymore. Instead the database is setup from calling ActiveRecord::Migration.maintain_test_schema! inside spec_helper.rb. Now this of cause doesn't work, because the structure.sql that rails creates when using: active_record.schema_format = :sql does not contain extensions such as postgis, when the schema_search_path is set (see here: rails/rails#17261). An alternative could of cause be to just call RAILS_ENV='test' rake db:create and then inside the spec_helper.rb call Apartment::Tenant.create("test_tenant") as suggest by the apartment wiki: https://github.com/influitive/apartment/wiki/Testing-Your-Application this however doesn't either work, since there at this point is no default_schema to dump from.

The solution I ended up with for now, was to monkey patch rails, so that it dumps the entire development schema including the extensions. The patch can be found here: rails/rails#17261

[bradrobertson]

I really can't comment as I didn't write any of this. @vdragsic Have you been using this feature?

[NielsKSchjoedt]

I have made a PR that address the two mentioned issues: #182

[jonsgreen]

@NielsKSchjoedt we have been using the use_sql feature in production but do not have a default_schema or cross schema associations but we are on Rails 4.1. I appreciate your work on this and hope it will be accepted.
I have found that I can get extensions across schemas by using a shared schema or like so:

ActiveRecord::Base.connection.execute("CREATE EXTENSION hstore with schema pg_catalog;")

I am not sure if that will be any help to you.

[troya2]

It's been a long time - when/will this pull requests ever be accepted?

In the mean time, I added this to apartment.rb

require 'apartment/adapters/postgresql_adapter'
Apartment::Adapters::PostgresqlSchemaFromSqlAdapter::PSQL_DUMP_BLACKLISTED_STATEMENTS << /CREATE SCHEMA public/i

The problem this solves is that pg_dump is including the CREATE SCHEMA public, but the public schema already exists.

If you're using a different name for the default schema, change public to that name.