way to truncate tables after tests

[gavinking]

I think we should add a schema export mode, create-truncate, or whatever, that deletes all the data without actually dropping the tables. This would be very helpful for testing on databases where DDL execution is slow.

(Of course there's other ways we could provide this functionality, but I'm not a fan of having a truncate() method on Session.)

[sebersole]

The one thing I do worry about is sequences. Many databases do offer a means to reset them, but I doubt all do. I guess worst case we just drop/create the sequences.

Also, the general interpretation for "create" in Hibernate is really "drop and create". "create-drop" simply adds a drop at SF close also. I only point this out because it is a deviation from the normal for that term and we need to be explicit about that - because "drop-create-truncate" is obviously pointless.

[gavinking]

And actually we don't even want to run the create statements every time I guess. Not sure what to do about that.

[sebersole]

Check for the existence ahead of time seems the only answer. So create-truncate becomes "create the schema, if it does not exist, at boot and truncate data after".

Though another take... I can only see this as useful for tests; so maybe just handle this as a test-hook? That's essentially what I had planned for ORM tests.

[gavinking]

Actually, do you folks think that a reason to not do this is that somebody will someday leave this setting turned on when deploying to production, and Hibernate's database user will have the permissions to do the delete from statements whereas it would not have had the permissions to execute DDL?

[gavinking]

You can look into our code base for implementations of the org.hibernate.testing.cleaner.DatabaseCleaner interface

Database cleaner looks interesting, but I'm not sure how I feel about the fact that it seems to trunc all tables in the schema, not just the ones mapped by Hibernate. Dunno. Perhaps that's fine.

[beikov]

but I'm not sure how I feel about the fact that it seems to trunc all tables in the schema, not just the ones mapped by Hibernate

Yeah for our tests this was fine so far.

If we want to expose this to users, we should move that to a dedicated module e.g. hibernate-test-tools? On PostgreSQL there is a truncate command which will defer FK checks to the end of the statement.

[gavinking]

Well, what I’m thinking now is to just give each dialect a DatabaseCleaner and then let you get it from the SF.

[beikov]

That sounds like a good idea.

[gavinking]

see #5561

[gavinking]

OK, so this has grown into a whole SchemaManager API which I think looks kinda fine, and could grow over time.

What's there right now is still a little unfinished, lacking:

1. logging of the executed SQL (we need that!)
2. some refactoring of code duplication inherited from the original code in hibernate-testing
3. it doesn't properly support quoted identifiers
4. investigation into the purpose of some code which queried sys.Tables in the SQL server code, which I had to remove because that table was not accessible in the CI @beikov
5. maybe some handling of table_type column in information_schema.tables
6. support for Derby and Sybase (not a critical priority)

[gavinking]

Currently it's in a new package.

SchemaManagementTool is an internal thing that we run from a gradle task, or automatically when you set a system property. It's not intended to be invoked programmatically.

This is an API you can call directly in your program (usually, in a test).

[gavinking]

Also, it can do some things SchemaManagementTool can't do: truncate tables, for example.

[beikov]

AFAICT the usage of sys.Tables is only to be able to distinguish system tables from user tables i.e. the queries always use the predicate is_ms_shipped = 0.

[gavinking]

But is there some other way to do that here? A list of system schemas, for example?

[beikov]

That I don't know. I just wrote that stuff once so that it works and erased my memories afterwards 😆

[gavinking]

After all that, I ended up going back to my original idea of making this a part of the schema export framework, but only available via the new API.

And I ended up throwing away the code of DatabaseCleaner, and implementing something much simpler and more robust from scratch.

But I'm very happy with it now.