Opened 13 years ago

Closed 12 years ago

Last modified 11 years 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: Danilo Bargen 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.

Change History (10)

comment:1 by Russell Keith-Magee, 13 years ago

Resolution: wontfix
Status: newclosed

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 by elver.loho@…, 13 years ago

Resolution: wontfix
Status: closedreopened
Summary: Django fails to load custom SQL when running testsDjango should load custom SQL when testing
Type: BugNew 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 by Russell Keith-Magee, 13 years ago

Resolution: wontfix
Status: reopenedclosed

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 by elver.loho@…, 13 years ago

Resolution: wontfix
Status: closedreopened

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 by Russell Keith-Magee, 13 years ago

Resolution: wontfix
Status: reopenedclosed

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 by anonymous, 13 years ago

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 by Danilo Bargen, 12 years ago

Cc: Danilo Bargen 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 by akuryan, 12 years ago

Resolution: wontfix
Status: closednew
Version: 1.31.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 by Aymeric Augustin, 12 years ago

Resolution: wontfix
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top