Code

Opened 3 years ago

Closed 13 months ago

Last modified 11 months ago

#16550 closed New feature (wontfix)

Django should load custom SQL when testing

Reported by: elver.loho@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: dbrgn Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

When running "python manage.py test appname", Django fails to load the custom SQL from the appname/sql folder. I am currently forced to use the following ugly workaround:

Workaround:

def load_sql(sender, connection, *args, **kwargs):
        if connection.settings_dict["NAME"] != "test_projectname":
                return
        f = open(os.path.join(os.path.dirname(os.path.realpath(__file__)), "sql", "custom_types.sql"), "r")
        sql = f.read()
        cursor = connection.cursor()
        cursor.execute(sql)
        transaction.commit_unless_managed()

backends.signals.connection_created.connect(load_sql)

It is important to load the custom SQL, because I am using it to define composite objects in the Postgres backend and I have written Django wrappers for them, which work fine. These composite objects allow me to store information like "50 USD per ton" or "100 EUR per kg" in a single field. The SQL that's being loaded also defines a set of new operators, which allow me to sort based on this field on the server side, using the latest currency exchange rates and weight unit conversions. Previously I had to store this information in several standard fields and either write a complex query or load them all into Python and sort there.

Please fix this.

Attachments (0)

Change History (10)

comment:1 Changed 3 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

This is a documented limitation of Django's test setup. See the documentation on testing and the release notes where this change was introduced for an explanation why.

comment:2 Changed 3 years ago by elver.loho@…

  • Resolution wontfix deleted
  • Status changed from closed to reopened
  • Summary changed from Django fails to load custom SQL when running tests to Django should load custom SQL when testing
  • Type changed from Bug to New feature

In that case I request this as a feature. It is downright moronic that I have to work around Django's deliberate limitation in order to test more advanced code.

I said this before and will repeat it here: I am NOT using SQL to load data into the database before testing. I am using SQL to create custom data types in the database before I can even load my data in it. Fixtures DO NOT help me in any way when trying to solve this problem. I am not putting data into the database -- I am extending the capabilities of the database.

comment:3 Changed 3 years ago by russellm

  • Resolution set to wontfix
  • Status changed from reopened to closed

Well, calling the core devs moronic is an interesting way to get your feature implemented for you. :-)

Allow me to assure you that the limitation exists for a very good reason. If you got to the trouble of reading the links that I provided, you should even be able to work out what that reason is. I will also direct your attention to #14661.

Essentially, we are in a situation where the feature that you are requesting is not possible to implement without causing other problems. Unless you have a *specific* proposal for how to avoid those problems, this is a wontfix.

comment:4 Changed 3 years ago by elver.loho@…

  • Resolution wontfix deleted
  • Status changed from closed to reopened

Having to work around this deliberate limitation is moronic, not the core devs.

Here is my proposal for how it should be implemented:

Workable version:

class CustomModelObjects(TestCase):
        
        sql = ["custom_types.sql"]
        fixtures = ["test_data.json"]
        
        def testQuantityWeight__mul__PricePerUnit__Normal(self):
                qw = QuantityWeight(15, "t")
                ppu = PricePerUnit(PriceWithCurrency(100, "EUR"), "t")
                pwc = qw * ppu
                self.failUnlessEqual(pwc, PriceWithCurrency(1500, "EUR"))

The stuff in sql is always loaded before the test runs. Since this is a new feature, you can easily document that it CANNOT be used for loading custom data. If you know to put that line there, you've probably read the docs and already know this. Problem solved without any workarounds.

comment:5 Changed 3 years ago by russellm

  • Resolution set to wontfix
  • Status changed from reopened to closed

No, the limitation isn't moronic. As I have now said *twice*, it exists *for a very good reason*.

The solution you propose isn't viable because it introduces an inconsistency between the way tables work during a test case and the way they work in production. Essentially, you're suggesting that whenever I have a test that utilizes the table with raw SQL modifications/inserts, I need to remember that it also needs the custom_types sql to be defined. This is a pretty egregious violation of DRY.

The only real solution to this that I can see was alluded to in the discussion on #14661 -- that is, introducing a type of custom SQL that can be used for DDL modifications. However, what I *haven't* seen is an elegant way to introduce this feature.

Please don't reopen this ticket again. If you feel strongly about this idea, take it to django-developers for discussion.

comment:6 Changed 3 years ago by anonymous

If my solution isn't viable, because it introduces an inconsistency between the way tables work during a test case and the way they work in production, then that is already the case, isn't it? In production the custom SQL is loaded. In testing it is not. That's a pretty big inconsistency right there.

comment:7 Changed 21 months ago by dbrgn

  • Cc dbrgn added

I agree very much with the comment above, in that there should be a way to execute custom SQL before and after running syncdb. This should not be used to load custom data, which is truncated anyways, but to alter the schema, create new types or load extensions.

In my case, I have to load the Postgres citext extension:

    CREATE EXTENSION IF NOT EXISTS citext;

Because I have to create a database manually before running syncdb, I can run my custom SQL in my database and then use it normally with Django. But because the extensions are database specific, when running the tests (which create their own database) they fail because the extension is not present. This means that contrary to the comment by russellm, the *test database* introduces an inconsistency between the way tables work during a test case and the way they work in production.

Another widely used extension is the hstore extension.

As a workaround, I loaded the extension in the default postgres template.

    psql -d template1 -c 'CREATE EXTENSION citext;'

But that means that any new database I create will contain the citext extension, which might not be what I want.

Therefore I suggest that you add some pre syncdb and post syncdb hooks to run custom SQL, and to mention in the docs that this should *not* be used to load custom data, but to create new types and alter the schema, so that it *matches the production database*. The data is truncated anyways.

I vote for reopen.

comment:8 Changed 13 months ago by akuryan

  • Resolution wontfix deleted
  • Status changed from closed to new
  • Version changed from 1.3 to 1.4

I also vote for reopenening and also repopening the current ticket. The reason is that we're actively using triggers in our application.
Because of that we have to run custom sql after every syncdb to create triggers.

Those sql files need to be run AFTER all tables are created in DB, so they even do not belong to any module. As long as triggers can't be created using default django mechanism and can be run automatically using syncdb we strongly need to have feature described by in comment #4 for the current ticket: that is ability to specify custom sql files to load. The same way as we may specify custom fixtures.

comment:9 Changed 13 months ago by aaugustin

  • Resolution set to wontfix
  • Status changed from new to closed

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.