rake db:schema:dump only creates create_table in schema.rb

[donhill]

In my database I login with a user that has no tables. The tables are in the dbo schema. When I run rake db:schema:dump the schema.rb gets create but no column definitions.

I tried putting ActiveRecord::Base.table_name_prefix = 'dbo.' in my Rakefile but I still get the same results. I hacked the file SchemaStatements#column_definitions by hardcoding

AND columns.TABLE_SCHEMA = 'dbo' ORDER BY columns.ordinal_position

this fixed the issue.

Is there something I am missing to configure the adapter to use schema dbo to use in the rake tasks?

[heberuriegas]

+1, Any news?

[metaskills]

The dbo. prefix is not needed since SQL Server's authentication and permission level should identify the current users as the owner and hence schema reflection should just work. That said, we have made some signification changes in the 4.2 version of the adapter and I would be interested in knowing if this is still an issue for. If so, please open up a new GH issue with details on how to replicate. Note, our test system creates a rails user which is the dbo and everything works great. PPS, our running unit tests doc mentions too that we assign sysadmin role for this user too. Maybe something to investigate.

[mshappe]

This never appears to have been reopened, but am seeing the same thing -- I'm trying to pull the schema from an existing database. Some tables have complete information; many just have create_table and nothing more!

[mshappe]

(Using v4.2.4 of the gem).

[mshappe]

One issue of note for my situation -- the same database has two different schemata -- dbo. and ns.

[mshappe]

The workaround above (hardwiring a namespace) basically works -- I can do two runs -- one for dbo and one for ns, and just move schema.rb aside so it doesn't get overwritten. This is, however, not ideal :-) As it happens, I'm mainly using this gem to work with legacy data and move it out into a different database, but if I were trying to actually adapt Rails to really use this legacy database, this behaviour would be a show-stopper.

[mshappe]

Actually, it all works perfectly if I simply tell it to not constrain by TABLE_SCHEMA at all.

In column_definitions, near the end of the big query, simply remove

              AND columns.TABLE_SCHEMA = #{identifier.schema.blank? ? 'schema_name()' : '@1'}

entirely.

[sjmadsen]

@metaskills I don't know how to reproduce the issue, but I created a new Rails 4.2.4 app yesterday and am pointing it at a legacy database with a complex schema. schema.rb identifies all of the tables and indexes, but none of the columns. ActiveRecord itself doesn't identify the columns for an individual model unless I prefix the table name with dbo.. Using table_name_prefix in an initializer has no effect.

[metaskills]

Thanks everyone for the comments and feedback. We can not do the change above because it would break the adapter where it does two things. First, we use a per model schema prefix (if given) or the current schema_name() in our schema info reflection.

Should the adapter fix this? Shouldn't SQL Server user/roles be the solution? For example, I created a rails-test SQL Server user. I then added that user to our test DB activerecord_unittest.

Since the rails user that created the tables is the database owner, I was executing this new login with the default schema of dbo to be able to see them when I logged in. This was not the case. Once I clicked into "Memberships" and check "db_owner", I could log in as this user and see all the existing dbo tables.

To make sure this worked, I even ran the entire test suite successfully as this user. Is there some reason this is not the best way to do this?

[cmendla]

I have a problem with the way this works. We have a case where we will be using one sql database for a number of rails apps. I am setting them up with an SQL user for the app and a corresponding schema.

The tables would look like

app1.name
app1.ckeditor
app2.item
app2.qty
app2.ckeditor

etc.

When I tried to dump and load a schema, all of the non dbo tables just had the 'create table' with no columns. I have application rb set to, for example, config.active_record.table_name_prefix = 'app1.'

I'm not sure but I think that the problem with the above solution will be situations where you have multiple rails apps using the same database with schema prefixes to organize/separate things.

[metaskills]

@cmendla-cct I hear what you are saying and I can certainly see the use case. I think the above screenshots outline a default Rails convention and happy path for most users. I also think that the adapter has gone a long long way in supporting multiple schemas. We have a fair amount of logic built up over years and quite a few tests too.

Basically, if this is something that is a pain for you... AND, you think you can make a change to the adapter that allows the existing tests to pass as well as meet your use case, I am 100% in favor of reviewing that code and helping you get it merged. That said, I ask that you consider clever workarounds that may just end up being documentation. Just jamming here... and please do investigate since I have to put my time to Rails 5 compatibility.

• Can you use the configure connection per user to set some sort of current schema that helps existing column/schema reflection?
• Can you configure the user/roles/memberships in some what that helps with this?

Marking this ticket as "Show me what you got!" to see if anyone got swifty.

[mshappe]

Far as I can tell, this is still pretty much broken -- this adapter is not usable with a database with multiple schemata. In the past I worked around it by making a version of the database that flattened all tables out to be in a single schema, but that won't be feasible on the project I'm starting on. The database must retain its existing structure to continue to be usable by legacy code.

Basically, if nobody's interested in fixing this issue, I'm going to have to abandon rails for this project and see if I can find another environment that will do the right thing. That's not your problem, of course, but that's sort of where I'm at.

[metaskills]

Why don't you use something like the SecondBase gem for your needs. This feels like a problem that can be worked around in many ways outside of Rails. Like giving a users cross DB perms, etc. I'm still working hard on the Rails v5 adapter, but if there is something pragmatic that we can do, I am all ears.

[mshappe]

This is a single database with tables that have two different prefixes. That is, in a single database, I have:

dbo.Foo
dbo.Bar
app.Baz
app.Qux

[metaskills]

My point still stands and it depends on what you mean by "still pretty much broken". For example if it was schema dumping for development and test, then SecondBase could help. All in all, my orig comments still stand. #317 (comment)

[mshappe]

Maybe I'm missing something, but I don't see how treating a single database as if it were two databases is going to do anything useful, here. I apologize if I'm missing the obvious.

[metaskills]

@mshappe May I recommend you iterate exactly what your issue is? It is hard to help without knowing. The comments by @cmendla-cct above were related to schema dumping and hence why I mentioned mentally treating another schema as another DB.

[mshappe]

Mine also deal with schema dumping. Also with operating in general, but for now, schema dumping, since without that, nothing else will really work.

Just for fun, I installed secondbase just now and set up a second user in the SQLserver with a different default schema for the same database, and tried setting that up. db:schema:dump still yields full structure for one of the two; the other one still just has the table creation statement.

[mshappe]

In fact, there is no evidence that db:schema:dump works with secondbase at all.

[metaskills]

So I think most of the issue is around schema dumping and if that is the case then this is a development/test issue, not a production issue. It is worth a mention that when your schema is complex, you are technically supposed to switch to the :sql schema format. Other DBs have great dump tools that make this switch seamless and Rails just sends this file to whatever native binaries are in place. But because we are on SQL Server and there are no cross-platform native dumping tools and dumping to files with full perms is a problem outside of Rails (what the DB should do) then we have to call it an SQL Server issue.

Microsoft has promised that schema dumping should be in the Linux versions of the new Docker images and my latest update seemed to indicate that this was the case. If so... this is good for us in general who like SQL Server but only helps those out on the latest DB. So this is not really a Rails issue, we lack support for legacy schema dumping binaries from the DB to use. I hacked this up back in the day for myself using some nasty cigwin stuff to make development/test easier. But nothing stopped us using Rails in production with different users, schemas, etc going all the way back to Rails 1.2.3. Does that make sense?

[mshappe]

It does, and I have no problem with using the SQL format (since this is a fresh Rails project aiming to replace legacy .NET code without changing databases), if that will actually solve the problem, which it sounds like it might not? You're not really clear about that.

Since without development, there never will be production, saying that this is purely a development problem makes no sense to me.

[metaskills]

I'm signing off for the Holidays. If your new to Rails then I apologize if some of this stuff is going over head. The summary is that legacy DBs are a pain because Microsoft has no schema dumper. But you can ignore that in dev now by simply just getting a local copy of your production DBs schema via other means. Have a good holiday. I'll be on again week after next.

[mshappe]

I am not new to rails, and the only part of this that's "going over my head" is why this gem does not support multiple table prefixes in a single database when that's clearly a feature SQL Server allows.

[donovan-duplessis]

Any further updates to this issue?

I am in agreement with @mshappe and @cmendla. I am also not new to Rails and this is something MSSQL allows. At the moment our Rails application is using an MSSQL schema within a database that has multiple schemas (used by other pieces of software). Not every client you develop for will give you your own database, the way you want it, and we would need to fit into to their plans.

So currently it is a problem because the Rails schema file is storing other tables that exist in a completely different MSSQL schema to ours, as if it were in our schema. I just find it strange that we can set table_name_prefix to the MSSQL schema we use, yet the Rails schema still reflects changes from completely different MSSQL schemas.

Nothing is going over anyones head here who knows MSSQL and its capabilities and it has nothing to do with ones experience in Rails. We have manual workarounds for this at the moment which is not great.

Thanks for the work on this gem, we appreciate it!

[metaskills]

Hey @donovan-duplessis thanks for joining. What tool to you use to dump the SQL structure for dev/test?