{ "info": { "author": "Scott Walton", "author_email": "scott.walton@mypebble.co.uk", "bugtrack_url": null, "classifiers": [ "Development Status :: 5 - Production/Stable", "Framework :: Django", "Framework :: Django :: 1.10", "Framework :: Django :: 1.11", "Framework :: Django :: 1.9", "Programming Language :: Python", "Programming Language :: Python :: 2.7", "Programming Language :: Python :: 3" ], "description": "# SQL Views for Postgres\n\n[](https://gitter.im/mypebble/django-pgviews?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge&utm_content=badge)\n[](https://circleci.com/gh/mypebble/django-pgviews)\n\nAdds first-class support for [PostgreSQL Views][pg-views] in the Django ORM\n\n[pg-views]: http://www.postgresql.org/docs/9.1/static/sql-createview.html\n\n\n## Installation\n\nInstall via pip:\n\n pip install django-pgviews\n\nAdd to installed applications in settings.py:\n\n```python\nINSTALLED_APPS = (\n # ...\n 'django_pgviews',\n)\n```\n\n## Examples\n\n```python\nfrom django.db import models\n\nfrom django_pgviews import view as pg\n\n\nclass Customer(models.Model):\n name = models.CharField(max_length=100)\n post_code = models.CharField(max_length=20)\n is_preferred = models.BooleanField(default=False)\n\n class Meta:\n app_label = 'myapp'\n\nclass PreferredCustomer(pg.View):\n projection = ['myapp.Customer.*',]\n dependencies = ['myapp.OtherView',]\n sql = \"\"\"SELECT * FROM myapp_customer WHERE is_preferred = TRUE;\"\"\"\n\n class Meta:\n app_label = 'myapp'\n db_table = 'myapp_preferredcustomer'\n managed = False\n```\n\n**NOTE** It is important that we include the `managed = False` in the `Meta` so\nDjango 1.7 migrations don't attempt to create DB tables for this view.\n\nThe SQL produced by this might look like:\n\n```postgresql\nCREATE VIEW myapp_preferredcustomer AS\nSELECT * FROM myapp_customer WHERE is_preferred = TRUE;\n```\n\nTo create all your views, run ``python manage.py sync_pgviews``\n\nYou can also specify field names, which will map onto fields in your View:\n\n```python\nfrom django_pgviews import view as pg\n\n\nVIEW_SQL = \"\"\"\n SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE\n\"\"\"\n\n\nclass PreferredCustomer(pg.View):\n name = models.CharField(max_length=100)\n post_code = models.CharField(max_length=20)\n\n sql = VIEW_SQL\n```\n\n## Usage\n\nTo map onto a View, simply extend `pg_views.view.View`, assign SQL to the\n`sql` argument and define a `db_table`. You must _always_ set `managed = False`\non the `Meta` class.\n\nViews can be created in a number of ways:\n\n1. Define fields to map onto the VIEW output\n2. Define a projection that describes the VIEW fields\n\n### Define Fields\n\nDefine the fields as you would with any Django Model:\n\n```python\nfrom django_pgviews import view as pg\n\n\nVIEW_SQL = \"\"\"\n SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE\n\"\"\"\n\n\nclass PreferredCustomer(pg.View):\n name = models.CharField(max_length=100)\n post_code = models.CharField(max_length=20)\n\n sql = VIEW_SQL\n\n class Meta:\n managed = False\n db_table = 'my_sql_view'\n```\n\n### Define Projection\n\n`django-pgviews` can take a projection to figure out what fields it needs to\nmap onto for a view. To use this, set the `projection` attribute:\n\n```python\nfrom django_pgviews import view as pg\n\n\nclass PreferredCustomer(pg.View):\n projection = ['myapp.Customer.*',]\n sql = \"\"\"SELECT * FROM myapp_customer WHERE is_preferred = TRUE;\"\"\"\n\n class Meta:\n db_table = 'my_sql_view'\n managed = False\n```\n\nThis will take all fields on `myapp.Customer` and apply them to\n`PreferredCustomer`\n\n## Features\n\n### Updating Views\n\nSometimes your models change and you need your Database Views to reflect the new\ndata. Updating the View logic is as simple as modifying the underlying SQL and\nrunning:\n\n```\npython manage.py sync_pgviews --force\n```\n\nThis will forcibly update any views that conflict with your new SQL.\n\n### Dependencies\n\nYou can specify other views you depend on. This ensures the other views are\ninstalled beforehand. Using dependencies also ensures that your views get\nrefreshed correctly when using `sync_pgviews --force`.\n\n**Note:** Views are synced after the Django application has migrated and adding\nmodels to the dependency list will cause syncing to fail.\n\nExample:\n\n```python\nfrom django_pgviews import view as pg\n\nclass PreferredCustomer(pg.View):\n dependencies = ['myapp.OtherView',]\n sql = \"\"\"SELECT * FROM myapp_customer WHERE is_preferred = TRUE;\"\"\"\n\n class Meta:\n app_label = 'myapp'\n db_table = 'myapp_preferredcustomer'\n managed = False\n```\n\n### Materialized Views\n\nPostgres 9.3 and up supports [materialized views](http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html)\nwhich allow you to cache the results of views, potentially allowing them\nto load faster.\n\nHowever, you do need to manually refresh the view. To do this automatically,\nyou can attach [signals](https://docs.djangoproject.com/en/1.8/ref/signals/)\nand call the refresh function.\n\nExample:\n\n```python\nfrom django_pgviews import view as pg\n\n\nVIEW_SQL = \"\"\"\n SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE\n\"\"\"\n\nclass Customer(models.Model):\n name = models.CharField(max_length=100)\n post_code = models.CharField(max_length=20)\n is_preferred = models.BooleanField(default=True)\n\n\nclass PreferredCustomer(pg.MaterializedView):\n name = models.CharField(max_length=100)\n post_code = models.CharField(max_length=20)\n\n sql = VIEW_SQL\n\n\n@receiver(post_save, sender=Customer)\ndef customer_saved(sender, action=None, instance=None, **kwargs):\n PreferredCustomer.refresh()\n```\n\nPostgres 9.4 and up allow materialized views to be refreshed concurrently, without blocking reads, as long as a\nunique index exists on the materialized view. To enable concurrent refresh, specify the name of a column that can be\nused as a unique index on the materialized view. Unique index can be defined on more than one column of a materialized \nview. Once enabled, passing `concurrently=True` to the model's refresh method will result in postgres performing the \nrefresh concurrently. (Note that the refresh method itself blocks until the refresh is complete; concurrent refresh is \nmost useful when materialized views are updated in another process or thread.)\n\nExample:\n\n```python\nfrom django_pgviews import view as pg\n\n\nVIEW_SQL = \"\"\"\n SELECT id, name, post_code FROM myapp_customer WHERE is_preferred = TRUE\n\"\"\"\n\nclass PreferredCustomer(pg.MaterializedView):\n concurrent_index = 'id, post_code'\n sql = VIEW_SQL\n\n name = models.CharField(max_length=100)\n post_code = models.CharField(max_length=20)\n\n\n@receiver(post_save, sender=Customer)\ndef customer_saved(sender, action=None, instance=None, **kwargs):\n PreferredCustomer.refresh(concurrently=True)\n```\n\n### Custom Schema\n\nYou can define any table name you wish for your views. They can even live inside your own custom\n[PostgreSQL schema](http://www.postgresql.org/docs/current/static/ddl-schemas.html).\n\n```python\nfrom django_pgviews import view as pg\n\n\nclass PreferredCustomer(pg.View):\n sql = \"\"\"SELECT * FROM myapp_customer WHERE is_preferred = TRUE;\"\"\"\n\n class Meta:\n db_table = 'my_custom_schema.preferredcustomer'\n managed = False\n```\n\n### Sync Listeners\n\ndjango-pgviews 0.5.0 adds the ability to listen to when a `post_sync` event has\noccurred.\n\n#### `view_synced`\n\nFired every time a VIEW is synchronised with the database.\n\nProvides args:\n* `sender` - View Class\n* `update` - Whether the view to be updated\n* `force` - Whether `force` was passed\n* `status` - The result of creating the view e.g. `EXISTS`, `FORCE_REQUIRED`\n* `has_changed` - Whether the view had to change\n\n#### `all_views_synced`\n\nSent after all Postgres VIEWs are synchronised.\n\nProvides args:\n* `sender` - Always `None`\n\n\n## Django Compatibility\n\n
| Django Version | \nDjango-PGView Version | \n
|---|---|
| 1.4 and down | \nUnsupported | \n
| 1.5 | \n0.0.1 | \n
| 1.6 | \n0.0.3 | \n
| 1.7 | \n0.0.4 | \n
| 1.9 | \n0.1.0 | \n
| 1.10 | \n0.2.0 | \n