ievv_customsql — Framework for adding custom SQL to Django apps

The intention of this module is to make it easier to add and update custom SQL in a Django app. We do this by providing a registry of all custom SQL, and a management command to add and update custom SQL and any refresh data that is maitained by triggers.

Configuration

Add the following to your INSTALLED_APPS-setting:

'ievv_opensource.ievv_customsql'

Add custom SQL to your app

Create the class containing your custom SQL

First you need to create a subclass of AbstractCustomSql.

Lets say you have a Person model with name and description. You want to maintain a fulltext search vector to efficiently search the person model. You would then create a subclass of AbstractCustomSql that looks something like this:

from ievv_opensource.ievv_customsql import customsql_registry


class PersonCustomSql(customsql_registry.AbstractCustomSql):
    def initialize(self):
        self.execute_sql("""

            -- Add search_vector column to the Person model
            ALTER TABLE myapp_person DROP COLUMN IF EXISTS search_vector;
            ALTER TABLE myapp_person ADD COLUMN search_vector tsvector;

            -- Function used to create the search_vector value both in the trigger,
            -- and in the UPDATE statement (in recreate_data()).
            CREATE OR REPLACE FUNCTION myapp_person_get_search_vector_value(param_table myapp_person)
            RETURNS tsvector AS $$
            BEGIN
                RETURN setweight(to_tsvector(param_table.name), 'A') ||
                    setweight(to_tsvector(param_table.description), 'C');
            END
            $$ LANGUAGE plpgsql;

            -- Trigger function called on insert or update to keep the search_vector column
            -- in sync.
            CREATE OR REPLACE FUNCTION myapp_person_set_search_vector() RETURNS trigger AS $$
            BEGIN
                NEW.search_vector := myapp_person_get_search_vector_value(NEW);
              return NEW;
            END
            $$ LANGUAGE plpgsql;

            DROP TRIGGER IF EXISTS myapp_person_set_search_vector_trigger ON myapp_person;
            CREATE TRIGGER myapp_person_set_search_vector_trigger BEFORE INSERT OR UPDATE
                ON myapp_person FOR EACH ROW
                EXECUTE PROCEDURE myapp_person_set_search_vector();
        """)

    def recreate_data(self):
        self.execute_sql("""
            UPDATE myapp_person SET
                search_vector = myapp_person_get_search_vector_value(myapp_person);
        """)

You can put this code anywhere in your app, but the recommended location is to put it in a file named customsql.py in the root of your app.

Add your custom SQL to the registry

Next, you need to register your PersonCustomSql class in the registry. Create an AppConfig for your app with the following code:

from django.apps import AppConfig

from ievv_opensource.ievv_customsql import customsql_registry
from myproject.myapp.customsqldemo.customsql import PersonCustomSql


class CustomSqlDemoAppConfig(AppConfig):
    name = 'myproject.myapp'
    verbose_name = "My APP"

    def ready(self):
        registry = customsql_registry.Registry.get_instance()
        registry.add('myapp', PersonCustomSql)

Using your custom SQL

During development and as part of production releases, you use the ievvtasks_customsql command to update your custom SQL. Run the following to execute both:

• initialize()
• recreate_data()

for all the custom SQL classes in the registry:

$ python manage.py ievvtasks_customsql -i -r

Since this is an ievvtasks command, you can also run it as:

$ ievv customsql -i -r

Writing tests using your custom SQL

The custom SQL is not added automatically, so you need to use it explicitly in your tests. You have three choices:

1. Call PersonCustomSql().initialize() in your setUp() method, or in your test method(s). You will probably also want to call PersonCustomSql().recreate_data() when required. This is normally the recommented method, since it provides the largest amount of control. See AbstractCustomSql.initialize() and AbstractCustomSql.recreate_data() for more info.
2. Call ievv_customsql.Registry.get_instance().run_all_in_app('myapp'). This may be useful to test views and other code that require all the custom SQL in your app. See Registry.run_all_in_app() for more info.
3. Call ievv_customsql.Registry.get_instance().run_all(). This is not recommended because it runs SQL from ALL apps in INSTALLED_APPS. See See Registry.run_all() for more info.

Example of using option (1) to create a TestCase:

class TestPersonCustomSql(test.TestCase):
    def test_add_person_and_search(self):
        PersonCustomSql().initialize()
        jack = mommy.make('myapp.Person', name='Jack The Man', description='Also called john by some.')
        mommy.make('myapp.Person', name='NoMatch Man')
        john = mommy.make('myapp.Person', name='John Peterson', description='Hello world')

        tsquery = 'john'
        queryset = Person.objects.extra(
            select={
                'rank': 'ts_rank_cd(search_vector, to_tsquery(%s))',
            },
            select_params=[tsquery],
            where=['search_vector @@ to_tsquery(%s)'],
            params=[tsquery],
            order_by=['-rank']
        )
        self.assertEqual([john, jack], list(queryset))

Demo

See ievv_opensource/demo/customsqldemo/ for a full demo of everything explained above.

API

AbstractCustomSql

class ievv_opensource.ievv_customsql.customsql_registry.AbstractCustomSql(appname=None)

Bases: object

Defines custom SQL that can be executed by the ievv_customsql framework.

You typically override initialize() and use execute_sql() to add triggers and functions, and override recreate_data() to rebuild the data maintained by the triggers, but many other use-cases are also possible.

Parameters:appname – Not required - it is added automatically by Registry, and used by __str__`() for easier debugging / prettier output.

execute_sql(sql)

Execute the provided SQL.

Parameters:sql (str) – String of SQL.

get_sql_from_file(path)

Get SQL from a file as a string.

Parameters:path (str) –

A path relative to a directory named the same as the module where this class is located without the filename extension and suffixed with _sqlcode.

So if the file with this class is in path/to/customsql.py, path will be relative to path/to/customsqlcode/.

execute_sql_from_file(path)

Execute SQL from the provided file.

Parameters:path – See get_sql_from_file().

execute_sql_from_files(paths)

Shortcut for calling execute_sql_from_file() with multiple files.

Calls execute_sql_from_file() once for each file in paths.

Parameters:paths (list) – A list of paths. See get_sql_from_file() for the format of each path.

execute_sql_from_template_file(path, context_data=None)

Execute SQL from the provided Django template file.

The SQL is retrieved from the file, then processed as a Django template with the provided context_data, and the result is executed using execute_sql().

Parameters:

• path – See get_sql_from_file().
• context_data (dict) – Template context data. Can be None.

execute_sql_from_template_files(paths, context_data=None)

Shortcut for calling execute_sql_from_template_file() with multiple files.

Calls execute_sql_from_template_file() once for each file in paths.

Parameters:

• paths (list) – A list of paths. See get_sql_from_file() for the format of each path.
• context_data (dict) – Forwarded to execute_sql_from_template_file().

initialize()

Code to initialize the custom SQL.

You should create triggers, functions, columns, indexes, etc. in this method, using execute_sql(), or using plain Django code.

Make sure to write everything in a manner that updates or creates everything in a self-contained manner. This method is called both for the first initialization, and to update code after updates/changes.

Must be overridden in subclasses.

clear()

Revert initialize() and remove any data created by the triggers.

Drop/delete triggers, functions, columns, indexes, etc. created in initialize().

recreate_data()

Recreate all data that any triggers created in initialize() would normally keep in sync automatically.

Can not be used unless initialize() has already be run (at some point). This restriction is here to make it possible to create SQL functions in initialize() that this method uses to recreate the data. Without this restriction, code-reuse between initialize() and this function would be very difficult.

run()

Run both initialize() and recreate_data().

Registry

class ievv_opensource.ievv_customsql.customsql_registry.Registry

Bases: ievv_opensource.utils.singleton.Singleton

Registry of AbstractCustomSql objects.

Examples

First, define a subclass of AbstractCustomSql.

Register the custom SQL class with the registry via an AppConfig for your Django app:

from django.apps import AppConfig
from ievv_opensource.ievv_customsql import customsql_registry
from myapp import customsql

class MyAppConfig(AppConfig):
    name = 'myapp'

    def ready(self):
        customsql_registry.Registry.get_instance().add(customsql.MyCustomSql)

See ievv_opensource/demo/customsql/apps.py for a complete demo.

add(appname, customsql_class)

Add the given customsql_class to the registry.

Parameters:

• appname – The django appname where the customsql_class belongs.
• customsql_class – A subclass of AbstractCustomSql.

iter_appnames()

Returns an iterator over all the appnames in the registry. Each item in the iterator is an appname (a string).

iter_customsql_in_app(appname)

Iterate over all AbstractCustomSql subclasses registered in the provided appname. The yielded values are objects of the classes initialized with no arguments.

run_all_in_app(appname)

Loops through all the AbstractCustomSql classes registered in the registry with the provided appname, and call AbstractCustomSql.run() for each of them.

run_all()

Loops through all the AbstractCustomSql classes in the registry, and call AbstractCustomSql.run() for each of them.

MockableRegistry

class ievv_opensource.ievv_customsql.customsql_registry.MockableRegistry

Bases: ievv_opensource.ievv_customsql.customsql_registry.Registry

A non-singleton version of Registry. For tests.

Typical usage in a test:

from ievv_opensource.ievv_customsql import customsql_registry

class MockCustomSql(customsql_registry.AbstractCustomSql):
    # ...

mockregistry = customsql_registry.MockableRegistry()
mockregistry.add(MockCustomSql())

with mock.patch('ievv_opensource.ievv_customsql.customsql_registry.Registry.get_instance',
                lambda: mockregistry):
    pass  # ... your code here ...