PostgreSQL Sample Database
Clone or download

README

Pagila
======

Pagila is a port of the Sakila example database originally created for MySQL by Mike Hillyer of the MySQL AB documentation team. It is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, etc...  


INSTALL NOTE
------------

The pagila-data.sql file and the pagila-insert-data.sql both contain the same
data, the former using COPY commands, the latter using INSERT commands, so you 
only need to install one of them. Both formats are provided for those who have
trouble using one version or another.


PARTITIONED TABLES
------------------

The payment table is designed as a partitioned table with a 6 month timespan for the date range. In order to take full advantage of table partitioning, you need to make sure constraint_exclusion is set to either 'partition' (the default) or 'on' in your database. You can do this by setting "constraint_exclusion = partition" in your postgresql.conf, or by issuing the command "ALTER DATABASE pagila SET constraint_exclusion = partition" (substitute pagila for your database name if installing into a database with a different name). Note, constraint_exclusion should be on by default in modern postgres releases.  


ARTICLES
--------------

The following articles make use of pagila to showcase various PostgreSQL features:

* Sample Applications - Pagila (PostgreSQL)
https://codeontime.com/learn/sample-applications/pagila-postgresql

* Showcasing REST in PostgreSQL - The PreQuel
http://www.postgresonline.com/journal/archives/32-Showcasing-REST-in-PostgreSQL-The-PreQuel.html

* PostgreSQL 8.3 Features: Enum Datatype
http://xzilla.net/blog/2007/Sep/PostgreSQL-8.3-Features-Enum-Datatype.html

* Email Validation with pl/PHP
http://xzilla.net/blog/2006/Sep/Re-inventing-Gregs-method-to-prevent-re-inventing.html

* RATIO_TO_REPORT in PostgreSQL
http://xzilla.net/blog/2006/Jul/RATIOTOREPORT-in-PostgreSQL.html

* Building Rails to Legacy Applications :: Take Control of Active Record
http://xzilla.net/blog/2006/Jun/Building-Rails-to-Legacy-Applications-Take-Control-of-Active-Record.html

* Building Rails to Legacy Applications :: Masking the Database
http://xzilla.net/blog/2006/Jun/Building-Rails-to-Legacy-Applications-Masking-the-Database.html


PORT INFO 
---------

For those interested in the original porting information, all tables, data, views, and functions have been ported, which resulted in some schema changes, including: 

* Changed char(1) true/false fields to true boolean fields
* The last_update columns were set with triggers to update them
* Added foreign keys
* Removed 'DEFAULT 0' on foreign keys since it's pointless with real FK's
* Used PostgreSQL built-in fulltext searching for fulltext indexing, removing the need for the film_text table.  
* The rewards_report function was ported to a simple SRF

The schema and data for the Sakila database were made available under the BSD license which can be found at http://www.opensource.org/licenses/bsd-license.php. 

LICENSE
-------
Pagila is made available under The PostgreSQL License (https://opensource.org/licenses/PostgreSQL)

Note: Versions of Pagila prior to and including 0.10.1 were also made available under the same BSD license as Sakila. 


VERSION HISTORY
---------------

Version 10.a 
* Rebuild payments table using native partitioning 
* Add function, rule, triggers for handling updates to partitoned payments table
* Fix numerous bugs with inserts data file   

Version 9.6.a
* Change versioning to track Postgres Major Releases
* Update articles section to current links (where available)
* Wordsmithing on README doc 

Version 0.10.1
* Add pagila-data-insert.sql file, added articles section

Version 0.10
* Support for built-in fulltext. Add enum example 

Version 0.9
* Add table partitioning example 

Version 0.8 
* First release of pagila