+++ /dev/null
-<?xml version="1.0" encoding="UTF-8"?>\r
-<chapter xml:id="intro_to_sql" xmlns="http://docbook.org/ns/docbook" version="5.0" xml:lang="EN"\r
- xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">\r
- <chapterinfo>\r
- <title>Introduction to SQL for Evergreen Administrators</title>\r
- </chapterinfo>\r
- <abstract id="itnroSQL_abstract">\r
- <simpara>This chapter was taken from Dan Scott's <emphasis>Introduction to SQL for Evergreen Administrators</emphasis>, February 2010.</simpara>\r
- </abstract> \r
- <section id="intro_to_databases">\r
- <title>Introduction to SQL Databases</title>\r
- <indexterm><primary>sql</primary></indexterm>\r
- <simplesect>\r
- <title>Introduction</title>\r
- <simpara>Over time, the SQL database has become the standard method of storing,\r
- retrieving, and processing raw data for applications. Ranging from embedded\r
- databases such as SQLite and Apache Derby, to enterprise databases such as\r
- Oracle and IBM DB2, any SQL database offers basic advantages to application\r
- developers such as standard interfaces (Structured Query Language (SQL), Java\r
- Database Connectivity (JDBC), Open Database Connectivity (ODBC), Perl Database\r
- Independent Interface (DBI)), a standard conceptual model of data (tables,\r
- fields, relationships, constraints, etc), performance in storing and retrieving\r
- data, concurrent access, etc.</simpara>\r
- <simpara>Evergreen is built on PostgreSQL, an open source SQL database that began as\r
- <literal>POSTGRES</literal> at the University of California at Berkeley in 1986 as a research\r
- project led by Professor Michael Stonebraker. A SQL interface was added to a\r
- fork of the original POSTGRES Berkelely code in 1994, and in 1996 the project\r
- was renamed PostgreSQL.</simpara>\r
- </simplesect>\r
- <simplesect id="_tables">\r
- <title>Tables</title>\r
- <indexterm><primary>sql</primary><secondary>tables</secondary></indexterm>\r
- <simpara>The table is the cornerstone of a SQL database. Conceptually, a database table\r
- is similar to a single sheet in a spreadsheet: every table has one or more\r
- columns, with each row in the table containing values for each column. Each\r
- column in a table defines an attribute corresponding to a particular data type.</simpara>\r
- <simpara>We’ll insert a row into a table, then display the resulting contents. Don’t\r
- worry if the INSERT statement is completely unfamiliar, we’ll talk more about\r
- the syntax of the insert statement later.</simpara>\r
- <formalpara><title><literal>actor.usr_note</literal> database table</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-evergreen=# INSERT INTO actor.usr_note (usr, creator, pub, title, value)\r
- VALUES (1, 1, TRUE, 'Who is this guy?', 'He''s the administrator!');\r
-\r
-evergreen=# select id, usr, creator, pub, title, value from actor.usr_note;\r
- id | usr | creator | pub | title | value\r
-----+-----+---------+-----+------------------+-------------------------\r
- 1 | 1 | 1 | t | Who is this guy? | He's the administrator!\r
-(1 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>PostgreSQL supports table inheritance, which lets you define tables that\r
- inherit the column definitions of a given parent table. A search of the data in\r
- the parent table includes the data in the child tables. Evergreen uses table\r
- inheritance: for example, the <literal>action.circulation</literal> table is a child of the\r
- <literal>money.billable_xact</literal> table, and the <literal>money.*_payment</literal> tables all inherit from\r
- the <literal>money.payment</literal> parent table.</simpara>\r
- </simplesect>\r
- <simplesect id="_schemas">\r
- <title>Schemas</title>\r
- <simpara>PostgreSQL, like most SQL databases, supports the use of schema names to group\r
- collections of tables and other database objects together. You might think of\r
- schemas as namespaces if you’re a programmer; or you might think of the schema\r
- / table / column relationship like the area code / exchange / local number\r
- structure of a telephone number.</simpara>\r
- <table\r
- frame="all"\r
- rowsep="1" colsep="1"\r
- >\r
- <title>Examples: database object names</title>\r
- <?dbhtml table-width="100%"?>\r
- <?dbfo table-width="100%"?>\r
- <tgroup cols="4">\r
- <colspec colname="col_1" colwidth="2.0*"/>\r
- <colspec colname="col_2" colwidth="1.0*"/>\r
- <colspec colname="col_3" colwidth="1.0*"/>\r
- <colspec colname="col_4" colwidth="1.0*"/>\r
- <thead>\r
- <row>\r
- <entry align="left" valign="top">Full name </entry>\r
- <entry align="left" valign="top">Schema name </entry>\r
- <entry align="left" valign="top">Table name </entry>\r
- <entry align="left" valign="top">Field name</entry>\r
- </row>\r
- </thead>\r
- <tbody>\r
- <row>\r
- <entry align="left" valign="top"><simpara>actor.usr_note.title</simpara></entry>\r
- <entry align="left" valign="top"><simpara>actor</simpara></entry>\r
- <entry align="left" valign="top"><simpara>usr_note</simpara></entry>\r
- <entry align="left" valign="top"><simpara>title</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara>biblio.record_entry.marc</simpara></entry>\r
- <entry align="left" valign="top"><simpara>biblio</simpara></entry>\r
- <entry align="left" valign="top"><simpara>record_entry</simpara></entry>\r
- <entry align="left" valign="top"><simpara>marc</simpara></entry>\r
- </row>\r
- </tbody>\r
- </tgroup>\r
- </table>\r
- <simpara>The default schema name in PostgreSQL is <literal>public</literal>, so if you do not specify a\r
- schema name when creating or accessing a database object, PostgreSQL will use\r
- the <literal>public</literal> schema. As a result, you might not find the object that you’re\r
- looking for if you don’t use the appropriate schema.</simpara>\r
- <formalpara><title>Example: Creating a table without a specific schema</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-evergreen=# CREATE TABLE foobar (foo TEXT, bar TEXT);\r
-CREATE TABLE\r
-evergreen=# \d foobar\r
- Table "public.foobar"\r
- Column | Type | Modifiers\r
---------+------+-----------\r
- foo | text |\r
- bar | text |\r
-</programlisting>\r
- </para></formalpara>\r
- <formalpara><title>Example: Trying to access a unqualified table outside of the public schema</title><para>\r
- <programlisting language="sql" linenumbering="unnumbered">evergreen=# SELECT * FROM usr_note;\r
- ERROR: relation "usr_note" does not exist\r
- LINE 1: SELECT * FROM usr_note;\r
- ^</programlisting>\r
- </para></formalpara>\r
- <simpara>Evergreen uses schemas to organize all of its tables with mostly intuitive,\r
- if short, schema names. Here’s the current (as of 2010-01-03) list of schemas\r
- used by Evergreen:</simpara>\r
- <table\r
- frame="all"\r
- rowsep="1" colsep="1"\r
- >\r
- <title>Evergreen schema names</title>\r
- <?dbhtml table-width="80%"?>\r
- <?dbfo table-width="80%"?>\r
- <tgroup cols="2">\r
- <colspec colname="col_1" colwidth="1.0*"/>\r
- <colspec colname="col_2" colwidth="1.0*"/>\r
- <thead>\r
- <row>\r
- <entry align="left" valign="top">Schema name </entry>\r
- <entry align="left" valign="top">Description</entry>\r
- </row>\r
- </thead>\r
- <tbody>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>acq</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Acquisitions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>action</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Circulation actions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>action_trigger</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Event mechanisms</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>actor</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Evergreen users and organization units</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>asset</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Call numbers and copies</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>auditor</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Track history of changes to selected tables</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>authority</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Authority records</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>biblio</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Bibliographic records</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>booking</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Resource bookings</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>config</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Evergreen configurable options</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>container</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Buckets for records, call numbers, copies, and users</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>extend_reporter</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Extra views for report definitions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>metabib</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Metadata about bibliographic records</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>money</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Fines and bills</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>offline</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Offline transactions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>permission</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>User permissions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>query</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Stored SQL statements</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>reporter</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Report definitions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>search</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Search functions</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>serial</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Serial MFHD records</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>stats</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Convenient views of circulation and asset statistics</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>vandelay</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>MARC batch importer and exporter</simpara></entry>\r
- </row>\r
- </tbody>\r
- </tgroup>\r
- </table>\r
- <note><simpara>The term <emphasis>schema</emphasis> has two meanings in the world of SQL databases. We have\r
- discussed the schema as a conceptual grouping of tables and other database\r
- objects within a given namespace; for example, "the <emphasis role="strong">actor</emphasis> schema contains the\r
- tables and functions related to users and organizational units". Another common\r
- usage of <emphasis>schema</emphasis> is to refer to the entire data model for a given database;\r
- for example, "the Evergreen database schema".</simpara></note>\r
- </simplesect>\r
- <simplesect id="_columns">\r
- <title>Columns</title>\r
- <simpara>Each column definition consists of:</simpara>\r
- <itemizedlist>\r
- <listitem>\r
- <simpara>\r
- a data type\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- (optionally) a default value to be used whenever a row is inserted that\r
- does not contain a specific value\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- (optionally) one or more constraints on the values beyond data type\r
- </simpara>\r
- </listitem>\r
- </itemizedlist>\r
- <simpara>Although PostgreSQL supports dozens of data types, Evergreen makes our life\r
- easier by only using a handful.</simpara>\r
- <table\r
- frame="all"\r
- rowsep="1" colsep="1"\r
- >\r
- <title>PostgreSQL data types used by Evergreen</title>\r
- <?dbhtml table-width="90%"?>\r
- <?dbfo table-width="90%"?>\r
- <tgroup cols="3">\r
- <colspec colname="col_1" colwidth="1.0*"/>\r
- <colspec colname="col_2" colwidth="1.0*"/>\r
- <colspec colname="col_3" colwidth="2.5*"/>\r
- <thead>\r
- <row>\r
- <entry align="left" valign="top">Type name </entry>\r
- <entry align="left" valign="top">Description </entry>\r
- <entry align="left" valign="top">Limits</entry>\r
- </row>\r
- </thead>\r
- <tbody>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>INTEGER</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Medium integer</simpara></entry>\r
- <entry align="left" valign="top"><simpara>-2147483648 to +2147483647</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>BIGINT</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Large integer</simpara></entry>\r
- <entry align="left" valign="top"><simpara>-9223372036854775808 to 9223372036854775807</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>SERIAL</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Sequential integer</simpara></entry>\r
- <entry align="left" valign="top"><simpara>1 to 2147483647</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>BIGSERIAL</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Large sequential integer</simpara></entry>\r
- <entry align="left" valign="top"><simpara>1 to 9223372036854775807</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>TEXT</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Variable length character data</simpara></entry>\r
- <entry align="left" valign="top"><simpara>Unlimited length</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>BOOL</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Boolean</simpara></entry>\r
- <entry align="left" valign="top"><simpara>TRUE or FALSE</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>TIMESTAMP WITH TIME ZONE</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Timestamp</simpara></entry>\r
- <entry align="left" valign="top"><simpara>4713 BC to 294276 AD</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>TIME</literal></simpara></entry>\r
- <entry align="left" valign="top"><simpara>Time</simpara></entry>\r
- <entry align="left" valign="top"><simpara>Expressed in HH:MM:SS</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara><literal>NUMERIC</literal>(precision, scale)</simpara></entry>\r
- <entry align="left" valign="top"><simpara>Decimal</simpara></entry>\r
- <entry align="left" valign="top"><simpara>Up to 1000 digits of precision. In Evergreen mostly used for money\r
- values, with a precision of 6 and a scale of 2 (<literal>####.##</literal>).</simpara></entry>\r
- </row>\r
- </tbody>\r
- </tgroup>\r
- </table>\r
- <simpara>Full details about these data types are available from the\r
- <ulink url="http://www.postgresql.org/docs/8.4/static/datatype.html">data types section of\r
- the PostgreSQL manual</ulink>.</simpara>\r
- </simplesect>\r
- <simplesect id="_constraints">\r
- <title>Constraints</title>\r
- <simplesect id="_prevent_null_values">\r
- <title>Prevent NULL values</title>\r
- <simpara>A column definition may include the constraint <literal>NOT NULL</literal> to prevent NULL\r
- values. In PostgreSQL, a NULL value is not the equivalent of zero or false or\r
- an empty string; it is an explicit non-value with special properties. We’ll\r
- talk more about how to work with NULL values when we get to queries.</simpara>\r
- </simplesect>\r
- <simplesect id="_primary_key">\r
- <title>Primary key</title>\r
- <simpara>Every table can have at most one primary key. A primary key consists of one or\r
- more columns which together uniquely identify each row in a table. If you\r
- attempt to insert a row into a table that would create a duplicate or NULL\r
- primary key entry, the database rejects the row and returns an error.</simpara>\r
- <simpara>Natural primary keys are drawn from the intrinsic properties of the data being\r
- modelled. For example, some potential natural primary keys for a table that\r
- contains people would be:</simpara>\r
- <table\r
- frame="all"\r
- rowsep="1" colsep="1"\r
- >\r
- <title>Example: Some potential natural primary keys for a table of people</title>\r
- <?dbhtml table-width="90%"?>\r
- <?dbfo table-width="90%"?>\r
- <tgroup cols="3">\r
- <colspec colname="col_1" colwidth="1.0*"/>\r
- <colspec colname="col_2" colwidth="2.0*"/>\r
- <colspec colname="col_3" colwidth="2.0*"/>\r
- <thead>\r
- <row>\r
- <entry align="left" valign="top">Natural key </entry>\r
- <entry align="left" valign="top">Pros </entry>\r
- <entry align="left" valign="top">Cons</entry>\r
- </row>\r
- </thead>\r
- <tbody>\r
- <row>\r
- <entry align="left" valign="top"><simpara>First name, last name, address</simpara></entry>\r
- <entry align="left" valign="top"><simpara>No two people with the same name would ever live at the same address, right?</simpara></entry>\r
- <entry align="left" valign="top"><simpara>Lots of columns force data duplication in referencing tables</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara>SSN or driver’s license</simpara></entry>\r
- <entry align="left" valign="top"><simpara>These are guaranteed to be unique</simpara></entry>\r
- <entry align="left" valign="top"><simpara>Lots of people don’t have an SSN or a driver’s license</simpara></entry>\r
- </row>\r
- </tbody>\r
- </tgroup>\r
- </table>\r
- <simpara>To avoid problems with natural keys, many applications instead define surrogate\r
- primary keys. A surrogate primary keys is a column with an autoincrementing\r
- integer value added to a table definition that ensures uniqueness.</simpara>\r
- <simpara>Evergreen uses surrogate keys (a column named <literal>id</literal> with a <literal>SERIAL</literal> data type)\r
- for most of its tables.</simpara>\r
- </simplesect>\r
- <simplesect id="_foreign_keys">\r
- <title>Foreign keys</title>\r
- <simpara>Every table can contain zero or more foreign keys: one or more columns that\r
- refer to the primary key of another table.</simpara>\r
- <simpara>For example, let’s consider Evergreen’s modelling of the basic relationship\r
- between copies, call numbers, and bibliographic records. Bibliographic records\r
- contained in the <literal>biblio.record_entry</literal> table can have call numbers attached to\r
- them. Call numbers are contained in the <literal>asset.call_number</literal> table, and they can\r
- have copies attached to them. Copies are contained in the <literal>asset.copy</literal> table.</simpara>\r
- <table\r
- frame="all"\r
- rowsep="1" colsep="1"\r
- >\r
- <title>Example: Evergreen’s copy / call number / bibliographic record relationships</title>\r
- <?dbhtml table-width="100%"?>\r
- <?dbfo table-width="100%"?>\r
- <tgroup cols="4">\r
- <colspec colname="col_1" colwidth="1.0*"/>\r
- <colspec colname="col_2" colwidth="1.0*"/>\r
- <colspec colname="col_3" colwidth="1.0*"/>\r
- <colspec colname="col_4" colwidth="1.0*"/>\r
- <thead>\r
- <row>\r
- <entry align="left" valign="top">Table </entry>\r
- <entry align="left" valign="top">Primary key </entry>\r
- <entry align="left" valign="top">Column with a foreign key </entry>\r
- <entry align="left" valign="top">Points to</entry>\r
- </row>\r
- </thead>\r
- <tbody>\r
- <row>\r
- <entry align="left" valign="top"><simpara>asset.copy</simpara></entry>\r
- <entry align="left" valign="top"><simpara>asset.copy.id</simpara></entry>\r
- <entry align="left" valign="top"><simpara>asset.copy.call_number</simpara></entry>\r
- <entry align="left" valign="top"><simpara>asset.call_number.id</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara>asset.call_number</simpara></entry>\r
- <entry align="left" valign="top"><simpara>asset.call_number.id</simpara></entry>\r
- <entry align="left" valign="top"><simpara>asset.call_number.record</simpara></entry>\r
- <entry align="left" valign="top"><simpara>biblio.record_entry.id</simpara></entry>\r
- </row>\r
- <row>\r
- <entry align="left" valign="top"><simpara>biblio.record_entry</simpara></entry>\r
- <entry align="left" valign="top"><simpara>biblio.record_entry.id</simpara></entry>\r
- <entry align="left" valign="top"><simpara></simpara></entry>\r
- <entry align="left" valign="top"><simpara></simpara></entry>\r
- </row>\r
- </tbody>\r
- </tgroup>\r
- </table>\r
- </simplesect>\r
- <simplesect id="_check_constraints">\r
- <title>Check constraints</title>\r
- <simpara>PostgreSQL enables you to define rules to ensure that the value to be inserted\r
- or updated meets certain conditions. For example, you can ensure that an\r
- incoming integer value is within a specific range, or that a ZIP code matches a\r
- particular pattern.</simpara>\r
- </simplesect>\r
- </simplesect>\r
- <simplesect id="_deconstructing_a_table_definition_statement">\r
- <title>Deconstructing a table definition statement</title>\r
- <simpara>The <literal>actor.org_address</literal> table is a simple table in the Evergreen schema that\r
- we can use as a concrete example of many of the properties of databases that\r
- we have discussed so far.</simpara>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-CREATE TABLE actor.org_address (\r
- id SERIAL PRIMARY KEY, <co id="sqlCO1-1"/>\r
- valid BOOL NOT NULL DEFAULT TRUE, <co id="sqlCO1-2"/>\r
- address_type TEXT NOT NULL DEFAULT 'MAILING', <co id="sqlCO1-3"/>\r
- org_unit INT NOT NULL REFERENCES actor.org_unit (id) <co id="sqlCO1-4"/>\r
- DEFERRABLE INITIALLY DEFERRED,\r
- street1 TEXT NOT NULL,\r
- street2 TEXT, <co id="sqlCO1-5"/>\r
- city TEXT NOT NULL,\r
- county TEXT,\r
- state TEXT NOT NULL,\r
- country TEXT NOT NULL,\r
- post_code TEXT NOT NULL\r
-);\r
-</programlisting>\r
- <calloutlist>\r
- <callout arearefs="sqlCO1-1">\r
- <simpara>\r
- The column named <literal>id</literal> is defined with a special data type of <literal>SERIAL</literal>; if\r
- given no value when a row is inserted into a table, the database automatically\r
- generates the next sequential integer value for the column. <literal>SERIAL</literal> is a\r
- popular data type for a primary key because it is guaranteed to be unique - and\r
- indeed, the constraint for this column identifies it as the <literal>PRIMARY KEY</literal>.\r
- </simpara>\r
- </callout>\r
- <callout arearefs="sqlCO1-2">\r
- <simpara>\r
- The data type <literal>BOOL</literal> defines a boolean value: <literal>TRUE</literal> or <literal>FALSE</literal> are the only\r
- acceptable values for the column. The constraint <literal>NOT NULL</literal> instructs the\r
- database to prevent the column from ever containing a NULL value. The column\r
- property <literal>DEFAULT TRUE</literal> instructs the database to automatically set the value\r
- of the column to <literal>TRUE</literal> if no value is provided.\r
- </simpara>\r
- </callout>\r
- <callout arearefs="sqlCO1-3">\r
- <simpara>\r
- The data type <literal>TEXT</literal> defines a text column of practically unlimited length.\r
- As with the previous column, there is a <literal>NOT NULL</literal> constraint, and a default\r
- value of <literal>'MAILING'</literal> will result if no other value is supplied.\r
- </simpara>\r
- </callout>\r
- <callout arearefs="sqlCO1-4">\r
- <simpara>\r
- The <literal>REFERENCES actor.org_unit (id)</literal> clause indicates that this column has a\r
- foreign key relationship to the <literal>actor.org_unit</literal> table, and that the value of\r
- this column in every row in this table must have a corresponding value in the\r
- <literal>id</literal> column in the referenced table (<literal>actor.org_unit</literal>).\r
- </simpara>\r
- </callout>\r
- <callout arearefs="sqlCO1-5">\r
- <simpara>\r
- The column named <literal>street2</literal> demonstrates that not all columns have constraints\r
- beyond data type. In this case, the column is allowed to be NULL or to contain a\r
- <literal>TEXT</literal> value.\r
- </simpara>\r
- </callout>\r
- </calloutlist>\r
- </simplesect>\r
- <simplesect id="_displaying_a_table_definition_using_literal_psql_literal">\r
- <title>Displaying a table definition using <literal>psql</literal></title>\r
- <simpara>The <literal>psql</literal> command-line interface is the preferred method for accessing\r
- PostgreSQL databases. It offers features like tab-completion, readline support\r
- for recalling previous commands, flexible input and output formats, and\r
- is accessible via a standard SSH session.</simpara>\r
- <simpara>If you press the <literal>Tab</literal> key once after typing one or more characters of the\r
- database object name, <literal>psql</literal> automatically completes the name if there are no\r
- other matches. If there are other matches for your current input, nothing\r
- happens until you press the <literal>Tab</literal> key a second time, at which point <literal>psql</literal>\r
- displays all of the matches for your current input.</simpara>\r
- <simpara>To display the definition of a database object such as a table, issue the\r
- command <literal>\d _object-name_</literal>. For example, to display the definition of the\r
- actor.usr_note table:</simpara>\r
-<programlisting language="sh" linenumbering="unnumbered">\r
-$ psql evergreen <co id="sqlCO2-1"/>\r
-psql (8.4.1)\r
-Type "help" for help.\r
-\r
-evergreen=# \d actor.usr_note <co id="sqlCO2-2"/>\r
- Table "actor.usr_note"\r
- Column | Type | Modifiers\r
--------------+--------------------------+-------------------------------------------------------------\r
- id | bigint | not null default nextval('actor.usr_note_id_seq'::regclass)\r
- usr | bigint | not null\r
- creator | bigint | not null\r
- create_date | timestamp with time zone | default now()\r
- pub | boolean | not null default false\r
- title | text | not null\r
- value | text | not null\r
-Indexes:\r
- "usr_note_pkey" PRIMARY KEY, btree (id)\r
- "actor_usr_note_creator_idx" btree (creator)\r
- "actor_usr_note_usr_idx" btree (usr)\r
-Foreign-key constraints:\r
- "usr_note_creator_fkey" FOREIGN KEY (creator) REFERENCES actor.usr(id) ON ...\r
- "usr_note_usr_fkey" FOREIGN KEY (usr) REFERENCES actor.usr(id) ON DELETE ....\r
-\r
-evergreen=# \q <co id="sqlCO2-3"/>\r
-$\r
-</programlisting>\r
- <calloutlist>\r
- <callout arearefs="sqlCO2-1">\r
- <simpara>\r
- This is the most basic connection to a PostgreSQL database. You can use a\r
- number of other flags to specify user name, hostname, port, and other options.\r
- </simpara>\r
- </callout>\r
- <callout arearefs="sqlCO2-2">\r
- <simpara>\r
- The <literal>\d</literal> command displays the definition of a database object.\r
- </simpara>\r
- </callout>\r
- <callout arearefs="sqlCO2-3">\r
- <simpara>\r
- The <literal>\q</literal> command quits the <literal>psql</literal> session and returns you to the shell prompt.\r
- </simpara>\r
- </callout>\r
- </calloutlist>\r
- </simplesect>\r
- </section>\r
- <section id="basic_sql_queries">\r
- <title>Basic SQL queries</title>\r
- <simplesect id="_the_select_statement">\r
- <title>The SELECT statement</title>\r
- <simpara>The SELECT statement is the basic tool for retrieving information from a\r
- database. The syntax for most SELECT statements is:</simpara>\r
- <blockquote>\r
- <literallayout><literal>SELECT</literal> [<emphasis>columns(s)</emphasis>]\r
- <literal>FROM</literal> [<emphasis>table(s)</emphasis>]\r
- [<literal>WHERE</literal> <emphasis>condition(s)</emphasis>]\r
- [<literal>GROUP BY</literal> <emphasis>columns(s)</emphasis>]\r
- [<literal>HAVING</literal> <emphasis>grouping-condition(s)</emphasis>]\r
- [<literal>ORDER BY</literal> <emphasis>column(s)</emphasis>]\r
- [<literal>LIMIT</literal> <emphasis>maximum-results</emphasis>]\r
- [<literal>OFFSET</literal> <emphasis>start-at-result-#</emphasis>]\r
- ;</literallayout>\r
- </blockquote>\r
- <simpara>For example, to select all of the columns for each row in the\r
- <literal>actor.usr_address</literal> table, issue the following query:</simpara>\r
- <programlisting language="sql" linenumbering="unnumbered">SELECT *\r
- FROM actor.usr_address\r
- ;</programlisting>\r
- </simplesect>\r
- <simplesect id="_selecting_particular_columns_from_a_table">\r
- <title>Selecting particular columns from a table</title>\r
- <simpara><literal>SELECT *</literal> returns all columns from all of the tables included in your query.\r
- However, quite often you will want to return only a subset of the possible\r
- columns. You can retrieve specific columns by listing the names of the columns\r
- you want after the <literal>SELECT</literal> keyword. Separate each column name with a comma.</simpara>\r
- <simpara>For example, to select just the city, county, and state from the\r
- actor.usr_address table, issue the following query:</simpara>\r
- <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
- FROM actor.usr_address\r
- ;</programlisting>\r
- </simplesect>\r
- <simplesect id="_sorting_results_with_the_order_by_clause">\r
- <title>Sorting results with the ORDER BY clause</title>\r
- <simpara>By default, a SELECT statement returns rows matching your query with no\r
- guarantee of any particular order in which they are returned. To force\r
- the rows to be returned in a particular order, use the ORDER BY clause\r
- to specify one or more columns to determine the sorting priority of the\r
- rows.</simpara>\r
- <simpara>For example, to sort the rows returned from your <literal>actor.usr_address</literal> query by\r
- city, with county and then zip code as the tie breakers, issue the\r
- following query:</simpara>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT city, county, state\r
- FROM actor.usr_address\r
- ORDER BY city, county, post_code\r
-;\r
-</programlisting>\r
- </simplesect>\r
- <simplesect id="_filtering_results_with_the_where_clause">\r
- <title>Filtering results with the WHERE clause</title>\r
- <simpara>Thus far, your results have been returning all of the rows in the table.\r
- Normally, however, you would want to restrict the rows that are returned to the\r
- subset of rows that match one or more conditions of your search. The <literal>WHERE</literal>\r
- clause enables you to specify a set of conditions that filter your query\r
- results. Each condition in the <literal>WHERE</literal> clause is an SQL expression that returns\r
- a boolean (true or false) value.</simpara>\r
- <simpara>For example, to restrict the results returned from your <literal>actor.usr_address</literal>\r
- query to only those rows containing a state value of <emphasis>Connecticut</emphasis>, issue the\r
- following query:</simpara>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT city, county, state\r
- FROM actor.usr_address\r
- WHERE state = 'Connecticut'\r
- ORDER BY city, county, post_code\r
-;\r
-</programlisting>\r
- <simpara>You can include more conditions in the <literal>WHERE</literal> clause with the <literal>OR</literal> and <literal>AND</literal>\r
- operators. For example, to further restrict the results returned from your\r
- <literal>actor.usr_address</literal> query to only those rows where the state column contains a\r
- value of <emphasis>Connecticut</emphasis> and the city column contains a value of <emphasis>Hartford</emphasis>,\r
- issue the following query:</simpara>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT city, county, state\r
- FROM actor.usr_address\r
- WHERE state = 'Connecticut'\r
- AND city = 'Hartford'\r
- ORDER BY city, county, post_code\r
-;\r
-</programlisting>\r
- <note><simpara>To return rows where the state is <emphasis>Connecticut</emphasis> and the city is <emphasis>Hartford</emphasis> or\r
- <emphasis>New Haven</emphasis>, you must use parentheses to explicitly group the city value\r
- conditions together, or else the database will evaluate the <literal>OR city = 'New\r
- Haven'</literal> clause entirely on its own and match all rows where the city column is\r
- <emphasis>New Haven</emphasis>, even though the state might not be <emphasis>Connecticut</emphasis>.</simpara></note>\r
- <formalpara><title>Trouble with OR</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT city, county, state\r
- FROM actor.usr_address\r
- WHERE state = 'Connecticut'\r
- AND city = 'Hartford' OR city = 'New Haven'\r
- ORDER BY city, county, post_code\r
-;\r
-\r
--- Can return unwanted rows because the OR is not grouped!\r
-</programlisting>\r
- </para></formalpara>\r
- <formalpara><title>Grouped OR’ed conditions</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT city, county, state\r
- FROM actor.usr_address\r
- WHERE state = 'Connecticut'\r
- AND (city = 'Hartford' OR city = 'New Haven')\r
- ORDER BY city, county, post_code\r
-;\r
-\r
--- The parentheses ensure that the OR is applied to the cities, and the\r
--- state in either case must be 'Connecticut'\r
-</programlisting>\r
- </para></formalpara>\r
- <simplesect id="_comparison_operators">\r
- <title>Comparison operators</title>\r
- <simpara>Here is a partial list of comparison operators that are commonly used in\r
- <literal>WHERE</literal> clauses:</simpara>\r
- <simplesect id="_comparing_two_scalar_values">\r
- <title>Comparing two scalar values</title>\r
- <itemizedlist>\r
- <listitem>\r
- <simpara>\r
- <literal>x = y</literal> (equal to)\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- <literal>x != y</literal> (not equal to)\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- <literal>x < y</literal> (less than)\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- <literal>x > y</literal> (greater than)\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- <literal>x LIKE y</literal> (TEXT value x matches a subset of TEXT y, where y is a string that\r
- can contain <emphasis>%</emphasis> as a wildcard for 0 or more characters, and <emphasis>_</emphasis> as a wildcard\r
- for a single character. For example, <literal>WHERE 'all you can eat fish and chips\r
- and a big stick' LIKE '%fish%stick'</literal> would return TRUE)\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- <literal>x ILIKE y</literal> (like LIKE, but the comparison ignores upper-case / lower-case)\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- <literal>x IN y</literal> (x is in the list of values y, where y can be a list or a SELECT\r
- statement that returns a list)\r
- </simpara>\r
- </listitem>\r
- </itemizedlist>\r
- </simplesect>\r
- </simplesect>\r
- </simplesect>\r
- <simplesect id="_null_values">\r
- <title>NULL values</title>\r
- <simpara>SQL databases have a special way of representing the value of a column that has\r
- no value: <literal>NULL</literal>. A <literal>NULL</literal> value is not equal to zero, and is not an empty\r
- string; it is equal to nothing, not even another <literal>NULL</literal>, because it has no value\r
- that can be compared.</simpara>\r
- <simpara>To return rows from a table where a given column is not <literal>NULL</literal>, use the\r
- <literal>IS NOT NULL</literal> comparison operator.</simpara>\r
- <formalpara><title>Retrieving rows where a column is not <literal>NULL</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT id, first_given_name, family_name\r
- FROM actor.usr\r
- WHERE second_given_name IS NOT NULL\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>Similarly, to return rows from a table where a given column is <literal>NULL</literal>, use\r
- the <literal>IS NULL</literal> comparison operator.</simpara>\r
- <formalpara><title>Retrieving rows where a column is <literal>NULL</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT id, first_given_name, second_given_name, family_name\r
- FROM actor.usr\r
- WHERE second_given_name IS NULL\r
-;\r
-\r
- id | first_given_name | second_given_name | family_name\r
-----+------------------+-------------------+----------------\r
- 1 | Administrator | | System Account\r
-(1 row)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>Notice that the <literal>NULL</literal> value in the output is displayed as empty space,\r
- indistinguishable from an empty string; this is the default display method in\r
- <literal>psql</literal>. You can change the behaviour of <literal>psql</literal> using the <literal>pset</literal> command:</simpara>\r
- <formalpara><title>Changing the way <literal>NULL</literal> values are displayed in <literal>psql</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-evergreen=# \pset null '(null)'\r
-Null display is '(null)'.\r
-\r
-SELECT id, first_given_name, second_given_name, family_name\r
- FROM actor.usr\r
- WHERE second_given_name IS NULL\r
-;\r
-\r
- id | first_given_name | second_given_name | family_name\r
-----+------------------+-------------------+----------------\r
- 1 | Administrator | (null) | System Account\r
-(1 row)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>Database queries within programming languages such as Perl and C have\r
- special methods of checking for <literal>NULL</literal> values in returned results.</simpara>\r
- </simplesect>\r
- <simplesect id="_text_delimiter">\r
- <title>Text delimiter: '</title>\r
- <simpara>You might have noticed that we have been using the <literal>'</literal> character to delimit\r
- TEXT values and values such as dates and times that are TEXT values. Sometimes,\r
- however, your TEXT value itself contains a <literal>'</literal> character, such as the word\r
- <literal>you’re</literal>. To prevent the database from prematurely ending the TEXT value at the\r
- first <literal>'</literal> character and returning a syntax error, use another <literal>'</literal> character to\r
- escape the following <literal>'</literal> character.</simpara>\r
- <simpara>For example, to change the last name of a user in the <literal>actor.usr</literal> table to\r
- <literal>L’estat</literal>, issue the following SQL:</simpara>\r
- <formalpara><title>Escaping <literal>'</literal> in TEXT values</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-UPDATE actor.usr\r
- SET family_name = 'L''estat'\r
- WHERE profile IN (\r
- SELECT id\r
- FROM permission.grp_tree\r
- WHERE name = 'Vampire'\r
- )\r
- ;</programlisting>\r
- </para></formalpara>\r
- <simpara>When you retrieve the row from the database, the value is displayed with just\r
- a single <literal>'</literal> character:</simpara>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT id, family_name\r
- FROM actor.usr\r
- WHERE family_name = 'L''estat'\r
-;\r
-\r
- id | family_name\r
-----+-------------\r
- 1 | L'estat\r
-(1 row)\r
-</programlisting>\r
- </simplesect>\r
- <simplesect id="_grouping_and_eliminating_results_with_the_group_by_and_having_clauses">\r
- <title>Grouping and eliminating results with the GROUP BY and HAVING clauses</title>\r
- <simpara>The GROUP BY clause returns a unique set of results for the desired columns.\r
- This is most often used in conjunction with an aggregate function to present\r
- results for a range of values in a single query, rather than requiring you to\r
- issue one query per target value.</simpara>\r
- <formalpara><title>Returning unique results of a single column with <literal>GROUP BY</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT grp\r
- FROM permission.grp_perm_map\r
- GROUP BY grp\r
- ORDER BY grp;\r
-\r
- grp\r
------+\r
- 1\r
- 2\r
- 3\r
- 4\r
- 5\r
- 6\r
- 7\r
- 10\r
-(8 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>While <literal>GROUP BY</literal> can be useful for a single column, it is more often used\r
- to return the distinct results across multiple columns. For example, the\r
- following query shows us which groups have permissions at each depth in\r
- the library hierarchy:</simpara>\r
- <formalpara><title>Returning unique results of multiple columns with <literal>GROUP BY</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT grp, depth\r
- FROM permission.grp_perm_map\r
- GROUP BY grp, depth\r
- ORDER BY depth, grp;\r
-\r
- grp | depth\r
------+-------\r
- 1 | 0\r
- 2 | 0\r
- 3 | 0\r
- 4 | 0\r
- 5 | 0\r
- 10 | 0\r
- 3 | 1\r
- 4 | 1\r
- 5 | 1\r
- 6 | 1\r
- 7 | 1\r
- 10 | 1\r
- 3 | 2\r
- 4 | 2\r
- 10 | 2\r
-(15 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>Extending this further, you can use the <literal>COUNT()</literal> aggregate function to\r
- also return the number of times each unique combination of <literal>grp</literal> and <literal>depth</literal>\r
- appears in the table. <emphasis>Yes, this is a sneak peek at the use of aggregate\r
- functions! Keeners.</emphasis></simpara>\r
- <formalpara><title>Counting unique column combinations with <literal>GROUP BY</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT grp, depth, COUNT(grp)\r
- FROM permission.grp_perm_map\r
- GROUP BY grp, depth\r
- ORDER BY depth, grp;\r
-\r
- grp | depth | count\r
------+-------+-------\r
- 1 | 0 | 6\r
- 2 | 0 | 2\r
- 3 | 0 | 45\r
- 4 | 0 | 3\r
- 5 | 0 | 5\r
- 10 | 0 | 1\r
- 3 | 1 | 3\r
- 4 | 1 | 4\r
- 5 | 1 | 1\r
- 6 | 1 | 9\r
- 7 | 1 | 5\r
- 10 | 1 | 10\r
- 3 | 2 | 24\r
- 4 | 2 | 8\r
- 10 | 2 | 7\r
-(15 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>You can use the <literal>WHERE</literal> clause to restrict the returned results before grouping\r
- is applied to the results. The following query restricts the results to those\r
- rows that have a depth of 0.</simpara>\r
- <formalpara><title>Using the <literal>WHERE</literal> clause with <literal>GROUP BY</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT grp, COUNT(grp)\r
- FROM permission.grp_perm_map\r
- WHERE depth = 0\r
- GROUP BY grp\r
- ORDER BY 2 DESC\r
-;\r
-\r
- grp | count\r
------+-------\r
- 3 | 45\r
- 1 | 6\r
- 5 | 5\r
- 4 | 3\r
- 2 | 2\r
- 10 | 1\r
-(6 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>To restrict results after grouping has been applied to the rows, use the\r
- <literal>HAVING</literal> clause; this is typically used to restrict results based on\r
- a comparison to the value returned by an aggregate function. For example,\r
- the following query restricts the returned rows to those that have more than\r
- 5 occurrences of the same value for <literal>grp</literal> in the table.</simpara>\r
- <formalpara><title><literal>GROUP BY</literal> restricted by a <literal>HAVING</literal> clause</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT grp, COUNT(grp)\r
- FROM permission.grp_perm_map\r
- GROUP BY grp\r
- HAVING COUNT(grp) > 5\r
-;\r
-\r
- grp | count\r
------+-------\r
- 6 | 9\r
- 4 | 15\r
- 5 | 6\r
- 1 | 6\r
- 3 | 72\r
- 10 | 18\r
-(6 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_eliminating_duplicate_results_with_the_distinct_keyword">\r
- <title>Eliminating duplicate results with the DISTINCT keyword</title>\r
- <simpara><literal>GROUP BY</literal> is one way of eliminating duplicate results from the rows returned\r
- by your query. The purpose of the <literal>DISTINCT</literal> keyword is to remove duplicate\r
- rows from the results of your query. However, it works, and it is easy - so if\r
- you just want a quick list of the unique set of values for a column or set of\r
- columns, the <literal>DISTINCT</literal> keyword might be appropriate.</simpara>\r
- <simpara>On the other hand, if you are getting duplicate rows back when you don’t expect\r
- them, then applying the <literal>DISTINCT</literal> keyword might be a sign that you are\r
- papering over a real problem.</simpara>\r
- <formalpara><title>Returning unique results of multiple columns with <literal>DISTINCT</literal></title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT DISTINCT grp, depth\r
- FROM permission.grp_perm_map\r
- ORDER BY depth, grp\r
-;\r
-\r
- grp | depth\r
------+-------\r
- 1 | 0\r
- 2 | 0\r
- 3 | 0\r
- 4 | 0\r
- 5 | 0\r
- 10 | 0\r
- 3 | 1\r
- 4 | 1\r
- 5 | 1\r
- 6 | 1\r
- 7 | 1\r
- 10 | 1\r
- 3 | 2\r
- 4 | 2\r
- 10 | 2\r
-(15 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_paging_through_results_with_the_limit_and_offset_clauses">\r
- <title>Paging through results with the LIMIT and OFFSET clauses</title>\r
- <simpara>The <literal>LIMIT</literal> clause restricts the total number of rows returned from your query\r
- and is useful if you just want to list a subset of a large number of rows. For\r
- example, in the following query we list the five most frequently used\r
- circulation modifiers:</simpara>\r
- <formalpara><title>Using the <literal>LIMIT</literal> clause to restrict results</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT circ_modifier, COUNT(circ_modifier)\r
- FROM asset.copy\r
- GROUP BY circ_modifier\r
- ORDER BY 2 DESC\r
- LIMIT 5\r
-;\r
-\r
- circ_modifier | count\r
----------------+--------\r
- CIRC | 741995\r
- BOOK | 636199\r
- SER | 265906\r
- DOC | 191598\r
- LAW MONO | 126627\r
-(5 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>When you use the <literal>LIMIT</literal> clause to restrict the total number of rows returned\r
- by your query, you can also use the <literal>OFFSET</literal> clause to determine which subset\r
- of the rows will be returned. The use of the <literal>OFFSET</literal> clause assumes that\r
- you’ve used the <literal>ORDER BY</literal> clause to impose order on the results.</simpara>\r
- <simpara>In the following example, we use the <literal>OFFSET</literal> clause to get results 6 through\r
- 10 from the same query that we prevously executed.</simpara>\r
- <formalpara><title>Using the <literal>OFFSET</literal> clause to return a specific subset of rows</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT circ_modifier, COUNT(circ_modifier)\r
- FROM asset.copy\r
- GROUP BY circ_modifier\r
- ORDER BY 2 DESC\r
- LIMIT 5\r
- OFFSET 5\r
-;\r
-\r
- circ_modifier | count\r
----------------+--------\r
- LAW SERIAL | 102758\r
- DOCUMENTS | 86215\r
- BOOK_WEB | 63786\r
- MFORM SER | 39917\r
- REF | 34380\r
-(5 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- </section>\r
- <section id="advanced_sql_queries">\r
- <title>Advanced SQL queries</title>\r
- <simplesect id="_transforming_column_values_with_functions">\r
- <title>Transforming column values with functions</title>\r
- <simpara>PostgreSQL includes many built-in functions for manipulating column data.\r
- You can also create your own functions (and Evergreen does make use of\r
- many custom functions). There are two types of functions used in\r
- databases: scalar functions and aggregate functions.</simpara>\r
- <simplesect id="_scalar_functions">\r
- <title>Scalar functions</title>\r
- <simpara>Scalar functions transform each value of the target column. If your query\r
- would return 50 values for a column in a given query, and you modify your\r
- query to apply a scalar function to the values returned for that column,\r
- it will still return 50 values. For example, the UPPER() function,\r
- used to convert text values to upper-case, modifies the results in the\r
- following set of queries:</simpara>\r
- <formalpara><title>Using the UPPER() scalar function to convert text values to upper-case</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
--- First, without the UPPER() function for comparison\r
-SELECT shortname, name\r
- FROM actor.org_unit\r
- WHERE id < 4\r
-;\r
-\r
- shortname | name\r
------------+-----------------------\r
- CONS | Example Consortium\r
- SYS1 | Example System 1\r
- SYS2 | Example System 2\r
-(3 rows)\r
-\r
--- Now apply the UPPER() function to the name column\r
-SELECT shortname, UPPER(name)\r
- FROM actor.org_unit\r
- WHERE id < 4\r
-;\r
-\r
- shortname | upper\r
------------+--------------------\r
- CONS | EXAMPLE CONSORTIUM\r
- SYS1 | EXAMPLE SYSTEM 1\r
- SYS2 | EXAMPLE SYSTEM 2\r
-(3 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>There are so many scalar functions in PostgreSQL that we cannot cover them\r
- all here, but we can list some of the most commonly used functions:</simpara>\r
- <itemizedlist>\r
- <listitem>\r
- <simpara>\r
- || - concatenates two text values together\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- COALESCE() - returns the first non-NULL value from the list of arguments\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- LOWER() - returns a text value converted to lower-case\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- REPLACE() - returns a text value after replacing all occurrences of a given text value with a different text value\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- REGEXP_REPLACE() - returns a text value after being transformed by a regular expression\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- UPPER() - returns a text value converted to upper-case\r
- </simpara>\r
- </listitem>\r
- </itemizedlist>\r
- <simpara>For a complete list of scalar functions, see\r
- <ulink url="http://www.postgresql.org/docs/8.3/interactive/functions.html">the PostgreSQL function documentation</ulink>.</simpara>\r
- </simplesect>\r
- <simplesect id="_aggregate_functions">\r
- <title>Aggregate functions</title>\r
- <simpara>Aggregate functions return a single value computed from the the complete set of\r
- values returned for the specified column.</simpara>\r
- <itemizedlist>\r
- <listitem>\r
- <simpara>\r
- AVG()\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- COUNT()\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- MAX()\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- MIN()\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- SUM()\r
- </simpara>\r
- </listitem>\r
- </itemizedlist>\r
- </simplesect>\r
- </simplesect>\r
- <simplesect id="_sub_selects">\r
- <title>Sub-selects</title>\r
- <simpara>A sub-select is the technique of using the results of one query to feed\r
- into another query. You can, for example, return a set of values from\r
- one column in a SELECT statement to be used to satisfy the IN() condition\r
- of another SELECT statement; or you could return the MAX() value of a\r
- column in a SELECT statement to match the = condition of another SELECT\r
- statement.</simpara>\r
- <simpara>For example, in the following query we use a sub-select to restrict the copies\r
- returned by the main SELECT statement to only those locations that have an\r
- <literal>opac_visible</literal> value of <literal>TRUE</literal>:</simpara>\r
- <formalpara><title>Sub-select example</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT call_number\r
- FROM asset.copy\r
- WHERE deleted IS FALSE\r
- AND location IN (\r
- SELECT id\r
- FROM asset.copy_location\r
- WHERE opac_visible IS TRUE\r
- )\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>Sub-selects can be an approachable way to breaking down a problem that\r
- requires matching values between different tables, and often result in\r
- a clearly expressed solution to a problem. However, if you start writing\r
- sub-selects within sub-selects, you should consider tackling the problem\r
- with joins instead.</simpara>\r
- </simplesect>\r
- <simplesect id="_joins">\r
- <title>Joins</title>\r
- <simpara>Joins enable you to access the values from multiple tables in your query\r
- results and comparison operators. For example, joins are what enable you to\r
- relate a bibliographic record to a barcoded copy via the <literal>biblio.record_entry</literal>,\r
- <literal>asset.call_number</literal>, and <literal>asset.copy</literal> tables. In this section, we discuss the\r
- most common kind of join—the inner join—as well as the less common outer join\r
- and some set operations which can compare and contrast the values returned by\r
- separate queries.</simpara>\r
- <simpara>When we talk about joins, we are going to talk about the left-hand table and\r
- the right-hand table that participate in the join. Every join brings together\r
- just two tables - but you can use an unlimited (for our purposes) number\r
- of joins in a single SQL statement. Each time you use a join, you effectively\r
- create a new table, so when you add a second join clause to a statement,\r
- table 1 and table 2 (which were the left-hand table and the right-hand table\r
- for the first join) now act as a merged left-hand table and the new table\r
- in the second join clause is the right-hand table.</simpara>\r
- <simpara>Clear as mud? Okay, let’s look at some examples.</simpara>\r
- <simplesect id="_inner_joins">\r
- <title>Inner joins</title>\r
- <simpara>An inner join returns all of the columns from the left-hand table in the join\r
- with all of the columns from the right-hand table in the joins that match a\r
- condition in the ON clause. Typically, you use the <literal>=</literal> operator to match the\r
- foreign key of the left-hand table with the primary key of the right-hand\r
- table to follow the natural relationship between the tables.</simpara>\r
- <simpara>In the following example, we return all of columns from the <literal>actor.usr</literal> and\r
- <literal>actor.org_unit</literal> tables, joined on the relationship between the user’s home\r
- library and the library’s ID. Notice in the results that some columns, like\r
- <literal>id</literal> and <literal>mailing_address</literal>, appear twice; this is because both the <literal>actor.usr</literal>\r
- and <literal>actor.org_unit</literal> tables include columns with these names. This is also why\r
- we have to fully qualify the column names in our queries with the schema and\r
- table names.</simpara>\r
- <formalpara><title>A simple inner join</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT *\r
- FROM actor.usr\r
- INNER JOIN actor.org_unit ON actor.usr.home_ou = actor.org_unit.id\r
- WHERE actor.org_unit.shortname = 'CONS'\r
-;\r
-\r
--[ RECORD 1 ]------------------+---------------------------------\r
-id | 1\r
-card | 1\r
-profile | 1\r
-usrname | admin\r
-email |\r
-...\r
-mailing_address |\r
-billing_address |\r
-home_ou | 1\r
-...\r
-claims_never_checked_out_count | 0\r
-id | 1\r
-parent_ou |\r
-ou_type | 1\r
-ill_address | 1\r
-holds_address | 1\r
-mailing_address | 1\r
-billing_address | 1\r
-shortname | CONS\r
-name | Example Consortium\r
-email |\r
-phone |\r
-opac_visible | t\r
-fiscal_calendar | 1\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>Of course, you do not have to return every column from the joined tables;\r
- you can (and should) continue to specify only the columns that you want to\r
- return. In the following example, we count the number of borrowers for\r
- every user profile in a given library by joining the <literal>permission.grp_tree</literal>\r
- table where profiles are defined against the <literal>actor.usr</literal> table, and then\r
- joining the <literal>actor.org_unit</literal> table to give us access to the user’s home\r
- library:</simpara>\r
- <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT permission.grp_tree.name, actor.org_unit.name, COUNT(permission.grp_tree.name)\r
- FROM actor.usr\r
- INNER JOIN permission.grp_tree\r
- ON actor.usr.profile = permission.grp_tree.id\r
- INNER JOIN actor.org_unit\r
- ON actor.org_unit.id = actor.usr.home_ou\r
- WHERE actor.usr.deleted IS FALSE\r
- GROUP BY permission.grp_tree.name, actor.org_unit.name\r
- ORDER BY actor.org_unit.name, permission.grp_tree.name\r
-;\r
-\r
- name | name | count\r
--------+--------------------+-------\r
- Users | Example Consortium | 1\r
-(1 row)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_aliases">\r
- <title>Aliases</title>\r
- <simpara>So far we have been fully-qualifying all of our table names and column names to\r
- prevent any confusion. This quickly gets tiring with lengthy qualified\r
- table names like <literal>permission.grp_tree</literal>, so the SQL syntax enables us to assign\r
- aliases to table names and column names. When you define an alias for a table\r
- name, you can access its column throughout the rest of the statement by simply\r
- appending the column name to the alias with a period; for example, if you assign\r
- the alias <literal>au</literal> to the <literal>actor.usr</literal> table, you can access the <literal>actor.usr.id</literal>\r
- column through the alias as <literal>au.id</literal>.</simpara>\r
- <simpara>The formal syntax for declaring an alias for a column is to follow the column\r
- name in the result columns clause with <literal>AS</literal> <emphasis>alias</emphasis>. To declare an alias for a table name,\r
- follow the table name in the FROM clause (including any JOIN statements) with\r
- <literal>AS</literal> <emphasis>alias</emphasis>. However, the <literal>AS</literal> keyword is optional for tables (and columns as\r
- of PostgreSQL 8.4), and in practice most SQL statements leave it out. For\r
- example, we can write the previous INNER JOIN statement example using aliases\r
- instead of fully-qualified identifiers:</simpara>\r
- <formalpara><title>Borrower Count by Profile (using aliases)</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
- FROM actor.usr au\r
- INNER JOIN permission.grp_tree pgt\r
- ON au.profile = pgt.id\r
- INNER JOIN actor.org_unit aou\r
- ON aou.id = au.home_ou\r
- WHERE au.deleted IS FALSE\r
- GROUP BY pgt.name, aou.name\r
- ORDER BY aou.name, pgt.name\r
-;\r
-\r
- Profile | Library | Count\r
----------+--------------------+-------\r
- Users | Example Consortium | 1\r
-(1 row)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>A nice side effect of declaring an alias for your columns is that the alias\r
- is used as the column header in the results table. The previous version of\r
- the query, which didn’t use aliased column names, had two columns named\r
- <literal>name</literal>; this version of the query with aliases results in a clearer\r
- categorization.</simpara>\r
- </simplesect>\r
- <simplesect id="_outer_joins">\r
- <title>Outer joins</title>\r
- <simpara>An outer join returns all of the rows from one or both of the tables\r
- participating in the join.</simpara>\r
- <itemizedlist>\r
- <listitem>\r
- <simpara>\r
- For a LEFT OUTER JOIN, the join returns all of the rows from the left-hand\r
- table and the rows matching the join condition from the right-hand table, with\r
- NULL values for the rows with no match in the right-hand table.\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- A RIGHT OUTER JOIN behaves in the same way as a LEFT OUTER JOIN, with the\r
- exception that all rows are returned from the right-hand table participating in\r
- the join.\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- For a FULL OUTER JOIN, the join returns all the rows from both the left-hand\r
- and right-hand tables, with NULL values for the rows with no match in either\r
- the left-hand or right-hand table.\r
- </simpara>\r
- </listitem>\r
- </itemizedlist>\r
- <formalpara><title>Base tables for the OUTER JOIN examples</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT * FROM aaa;\r
-\r
- id | stuff\r
-----+-------\r
- 1 | one\r
- 2 | two\r
- 3 | three\r
- 4 | four\r
- 5 | five\r
-(5 rows)\r
-\r
-SELECT * FROM bbb;\r
-\r
- id | stuff | foo\r
-----+-------+----------\r
- 1 | one | oneone\r
- 2 | two | twotwo\r
- 5 | five | fivefive\r
- 6 | six | sixsix\r
-(4 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <formalpara><title>Example of a LEFT OUTER JOIN</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT * FROM aaa\r
- LEFT OUTER JOIN bbb ON aaa.id = bbb.id\r
-;\r
- id | stuff | id | stuff | foo\r
-----+-------+----+-------+----------\r
- 1 | one | 1 | one | oneone\r
- 2 | two | 2 | two | twotwo\r
- 3 | three | | |\r
- 4 | four | | |\r
- 5 | five | 5 | five | fivefive\r
-(5 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <formalpara><title>Example of a RIGHT OUTER JOIN</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT * FROM aaa\r
- RIGHT OUTER JOIN bbb ON aaa.id = bbb.id\r
-;\r
- id | stuff | id | stuff | foo\r
-----+-------+----+-------+----------\r
- 1 | one | 1 | one | oneone\r
- 2 | two | 2 | two | twotwo\r
- 5 | five | 5 | five | fivefive\r
- | | 6 | six | sixsix\r
-(4 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <formalpara><title>Example of a FULL OUTER JOIN</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT * FROM aaa\r
- FULL OUTER JOIN bbb ON aaa.id = bbb.id\r
-;\r
- id | stuff | id | stuff | foo\r
-----+-------+----+-------+----------\r
- 1 | one | 1 | one | oneone\r
- 2 | two | 2 | two | twotwo\r
- 3 | three | | |\r
- 4 | four | | |\r
- 5 | five | 5 | five | fivefive\r
- | | 6 | six | sixsix\r
-(6 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_self_joins">\r
- <title>Self joins</title>\r
- <simpara>It is possible to join a table to itself. You can, in fact you must, use\r
- aliases to disambiguate the references to the table.</simpara>\r
- </simplesect>\r
- </simplesect>\r
- <simplesect id="_set_operations">\r
- <title>Set operations</title>\r
- <simpara>Relational databases are effectively just an efficient mechanism for\r
- manipulating sets of values; they are implementations of set theory. There are\r
- three operators for sets (tables) in which each set must have the same number\r
- of columns with compatible data types: the union, intersection, and difference\r
- operators.</simpara>\r
- <formalpara><title>Base tables for the set operation examples</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT * FROM aaa;\r
-\r
- id | stuff\r
- ----+-------\r
- 1 | one\r
- 2 | two\r
- 3 | three\r
- 4 | four\r
- 5 | five\r
- (5 rows)\r
-\r
-SELECT * FROM bbb;\r
-\r
- id | stuff | foo\r
- ----+-------+----------\r
- 1 | one | oneone\r
- 2 | two | twotwo\r
- 5 | five | fivefive\r
- 6 | six | sixsix\r
-(4 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simplesect id="_union">\r
- <title>Union</title>\r
- <simpara>The <literal>UNION</literal> operator returns the distinct set of rows that are members of\r
- either or both of the left-hand and right-hand tables. The <literal>UNION</literal> operator\r
- does not return any duplicate rows. To return duplicate rows, use the\r
- <literal>UNION ALL</literal> operator.</simpara>\r
- <formalpara><title>Example of a UNION set operation</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
--- The parentheses are not required, but are intended to help\r
--- illustrate the sets participating in the set operation\r
-(\r
- SELECT id, stuff\r
- FROM aaa\r
-)\r
-UNION\r
-(\r
- SELECT id, stuff\r
- FROM bbb\r
-)\r
-ORDER BY 1\r
-;\r
-\r
- id | stuff\r
-----+-------\r
- 1 | one\r
- 2 | two\r
- 3 | three\r
- 4 | four\r
- 5 | five\r
- 6 | six\r
-(6 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_intersection">\r
- <title>Intersection</title>\r
- <simpara>The <literal>INTERSECT</literal> operator returns the distinct set of rows that are common to\r
- both the left-hand and right-hand tables. To return duplicate rows, use the\r
- <literal>INTERSECT ALL</literal> operator.</simpara>\r
- <formalpara><title>Example of an INTERSECT set operation</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-(\r
- SELECT id, stuff\r
- FROM aaa\r
-)\r
-INTERSECT\r
-(\r
- SELECT id, stuff\r
- FROM bbb\r
-)\r
-ORDER BY 1\r
-;\r
-\r
- id | stuff\r
-----+-------\r
- 1 | one\r
- 2 | two\r
- 5 | five\r
-(3 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_difference">\r
- <title>Difference</title>\r
- <simpara>The <literal>EXCEPT</literal> operator returns the rows in the left-hand table that do not\r
- exist in the right-hand table. You are effectively subtracting the common\r
- rows from the left-hand table.</simpara>\r
- <formalpara><title>Example of an EXCEPT set operation</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-(\r
- SELECT id, stuff\r
- FROM aaa\r
-)\r
-EXCEPT\r
-(\r
- SELECT id, stuff\r
- FROM bbb\r
-)\r
-ORDER BY 1\r
-;\r
-\r
- id | stuff\r
-----+-------\r
- 3 | three\r
- 4 | four\r
-(2 rows)\r
-\r
--- Order matters: switch the left-hand and right-hand tables\r
--- and you get a different result\r
-(\r
- SELECT id, stuff\r
- FROM bbb\r
-)\r
-EXCEPT\r
-(\r
- SELECT id, stuff\r
- FROM aaa\r
-)\r
-ORDER BY 1\r
-;\r
-\r
- id | stuff\r
-----+-------\r
- 6 | six\r
-(1 row)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- </simplesect>\r
- <simplesect id="_views">\r
- <title>Views</title>\r
- <simpara>A view is a persistent <literal>SELECT</literal> statement that acts like a read-only table.\r
- To create a view, issue the <literal>CREATE VIEW</literal> statement, giving the view a name\r
- and a <literal>SELECT</literal> statement on which the view is built.</simpara>\r
- <simpara>The following example creates a view based on our borrower profile count:</simpara>\r
- <formalpara><title>Creating a view</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-CREATE VIEW actor.borrower_profile_count AS\r
- SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
- FROM actor.usr au\r
- INNER JOIN permission.grp_tree pgt\r
- ON au.profile = pgt.id\r
- INNER JOIN actor.org_unit aou\r
- ON aou.id = au.home_ou\r
- WHERE au.deleted IS FALSE\r
- GROUP BY pgt.name, aou.name\r
- ORDER BY aou.name, pgt.name\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>When you subsequently select results from the view, you can apply additional\r
- <literal>WHERE</literal> clauses to filter the results, or <literal>ORDER BY</literal> clauses to change the\r
- order of the returned rows. In the following examples, we issue a simple\r
- <literal>SELECT *</literal> statement to show that the default results are returned in the\r
- same order from the view as the equivalent SELECT statement would be returned.\r
- Then we issue a <literal>SELECT</literal> statement with a <literal>WHERE</literal> clause to further filter the\r
- results.</simpara>\r
- <formalpara><title>Selecting results from a view</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT * FROM actor.borrower_profile_count;\r
-\r
- Profile | Library | Count\r
-----------------------------+----------------------------+-------\r
- Faculty | University Library | 208\r
- Graduate | University Library | 16\r
- Patrons | University Library | 62\r
-...\r
-\r
--- You can still filter your results with WHERE clauses\r
-SELECT *\r
- FROM actor.borrower_profile_count\r
- WHERE "Profile" = 'Faculty';\r
-\r
- Profile | Library | Count\r
----------+----------------------------+-------\r
- Faculty | University Library | 208\r
- Faculty | College Library | 64\r
- Faculty | College Library 2 | 102\r
- Faculty | University Library 2 | 776\r
-(4 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_inheritance">\r
- <title>Inheritance</title>\r
- <simpara>PostgreSQL supports table inheritance: that is, a child table inherits its\r
- base definition from a parent table, but can add additional columns to its\r
- own definition. The data from any child tables is visible in queries against\r
- the parent table.</simpara>\r
- <simpara>Evergreen uses table inheritance in several areas:\r
- * In the Vandelay MARC batch importer / exporter, Evergreen defines base\r
- tables for generic queues and queued records for which authority record and\r
- bibliographic record child tables\r
- * Billable transactions are based on the <literal>money.billable_xact</literal> table;\r
- child tables include <literal>action.circulation</literal> for circulation transactions\r
- and <literal>money.grocery</literal> for general bills.\r
- * Payments are based on the <literal>money.payment</literal> table; its child table is\r
- <literal>money.bnm_payment</literal> (for brick-and-mortar payments), which in turn has child\r
- tables of <literal>money.forgive_payment</literal>, <literal>money.work_payment</literal>, <literal>money.credit_payment</literal>,\r
- <literal>money.goods_payment</literal>, and <literal>money.bnm_desk_payment</literal>. The\r
- <literal>money.bnm_desk_payment</literal> table in turn has child tables of <literal>money.cash_payment</literal>,\r
- <literal>money.check_payment</literal>, and <literal>money.credit_card_payment</literal>.\r
- * Transits are based on the <literal>action.transit_copy</literal> table, which has a child\r
- table of <literal>action.hold_transit_copy</literal> for transits initiated by holds.\r
- * Generic acquisition line items are defined by the\r
- <literal>acq.lineitem_attr_definition</literal> table, which in turn has a number of child\r
- tables to define MARC attributes, generated attributes, user attributes, and\r
- provider attributes.</simpara>\r
- </simplesect>\r
- </section>\r
- <section id="understanding_query_performance_with_explain">\r
- <title>Understanding query performance with EXPLAIN</title>\r
- <simpara>Some queries run for a long, long time. This can be the result of a poorly\r
- written query—a query with a join condition that joins every\r
- row in the <literal>biblio.record_entry</literal> table with every row in the <literal>metabib.full_rec</literal>\r
- view would consume a massive amount of memory and disk space and CPU time—or\r
- a symptom of a schema that needs some additional indexes. PostgreSQL provides\r
- the <literal>EXPLAIN</literal> tool to estimate how long it will take to run a given query and\r
- show you the <emphasis>query plan</emphasis> (how it plans to retrieve the results from the\r
- database).</simpara>\r
- <simpara>To generate the query plan without actually running the statement, simply\r
- prepend the <literal>EXPLAIN</literal> keyword to your query. In the following example, we\r
- generate the query plan for the poorly written query that would join every\r
- row in the <literal>biblio.record_entry</literal> table with every row in the <literal>metabib.full_rec</literal>\r
- view:</simpara>\r
- <formalpara><title>Query plan for a terrible query</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-EXPLAIN SELECT *\r
- FROM biblio.record_entry\r
- FULL OUTER JOIN metabib.full_rec ON 1=1\r
-;\r
-\r
- QUERY PLAN\r
--------------------------------------------------------------------------------//\r
- Merge Full Join (cost=0.00..4959156437783.60 rows=132415734100864 width=1379)\r
- -> Seq Scan on record_entry (cost=0.00..400634.16 rows=2013416 width=1292)\r
- -> Seq Scan on real_full_rec (cost=0.00..1640972.04 rows=65766704 width=87)\r
-(3 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>This query plan shows that the query would return 132415734100864 rows, and it\r
- plans to accomplish what you asked for by sequentially scanning (<emphasis>Seq Scan</emphasis>)\r
- every row in each of the tables participating in the join.</simpara>\r
- <simpara>In the following example, we have realized our mistake in joining every row of\r
- the left-hand table with every row in the right-hand table and take the saner\r
- approach of using an <literal>INNER JOIN</literal> where the join condition is on the record ID.</simpara>\r
- <formalpara><title>Query plan for a less terrible query</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-EXPLAIN SELECT *\r
- FROM biblio.record_entry bre\r
- INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id;\r
- QUERY PLAN\r
-----------------------------------------------------------------------------------------//\r
- Hash Join (cost=750229.86..5829273.98 rows=65766704 width=1379)\r
- Hash Cond: (real_full_rec.record = bre.id)\r
- -> Seq Scan on real_full_rec (cost=0.00..1640972.04 rows=65766704 width=87)\r
- -> Hash (cost=400634.16..400634.16 rows=2013416 width=1292)\r
- -> Seq Scan on record_entry bre (cost=0.00..400634.16 rows=2013416 width=1292)\r
-(5 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>This time, we will return 65766704 rows - still way too many rows. We forgot\r
- to include a <literal>WHERE</literal> clause to limit the results to something meaningful. In\r
- the following example, we will limit the results to deleted records that were\r
- modified in the last month.</simpara>\r
- <formalpara><title>Query plan for a realistic query</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-EXPLAIN SELECT *\r
- FROM biblio.record_entry bre\r
- INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
- WHERE bre.deleted IS TRUE\r
- AND DATE_TRUNC('MONTH', bre.edit_date) >\r
- DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
-;\r
-\r
- QUERY PLAN\r
-----------------------------------------------------------------------------------------//\r
- Hash Join (cost=5058.86..2306218.81 rows=201669 width=1379)\r
- Hash Cond: (real_full_rec.record = bre.id)\r
- -> Seq Scan on real_full_rec (cost=0.00..1640972.04 rows=65766704 width=87)\r
- -> Hash (cost=4981.69..4981.69 rows=6174 width=1292)\r
- -> Index Scan using biblio_record_entry_deleted on record_entry bre\r
- (cost=0.00..4981.69 rows=6174 width=1292)\r
- Index Cond: (deleted = true)\r
- Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date)\r
- > date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
-(7 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>We can see that the number of rows returned is now only 201669; that’s\r
- something we can work with. Also, the overall cost of the query is 2306218,\r
- compared to 4959156437783 in the original query. The <literal>Index Scan</literal> tells us\r
- that the query planner will use the index that was defined on the <literal>deleted</literal>\r
- column to avoid having to check every row in the <literal>biblio.record_entry</literal> table.</simpara>\r
- <simpara>However, we are still running a sequential scan over the\r
- <literal>metabib.real_full_rec</literal> table (the table on which the <literal>metabib.full_rec</literal>\r
- view is based). Given that linking from the bibliographic records to the\r
- flattened MARC subfields is a fairly common operation, we could create a\r
- new index and see if that speeds up our query plan.</simpara>\r
- <formalpara><title>Query plan with optimized access via a new index</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
--- This index will take a long time to create on a large database\r
--- of bibliographic records\r
-CREATE INDEX bib_record_idx ON metabib.real_full_rec (record);\r
-\r
-EXPLAIN SELECT *\r
- FROM biblio.record_entry bre\r
- INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
- WHERE bre.deleted IS TRUE\r
- AND DATE_TRUNC('MONTH', bre.edit_date) >\r
- DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
-;\r
-\r
- QUERY PLAN\r
-----------------------------------------------------------------------------------------//\r
- Nested Loop (cost=0.00..1558330.46 rows=201669 width=1379)\r
- -> Index Scan using biblio_record_entry_deleted on record_entry bre\r
- (cost=0.00..4981.69 rows=6174 width=1292)\r
- Index Cond: (deleted = true)\r
- Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date) >\r
- date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
- -> Index Scan using bib_record_idx on real_full_rec\r
- (cost=0.00..240.89 rows=850 width=87)\r
- Index Cond: (real_full_rec.record = bre.id)\r
-(6 rows)\r
-</programlisting>\r
- </para></formalpara>\r
- <simpara>We can see that the resulting number of rows is still the same (201669), but\r
- the execution estimate has dropped to 1558330 because the query planner can\r
- use the new index (<literal>bib_record_idx</literal>) rather than scanning the entire table.\r
- Success!</simpara>\r
- <note><simpara>While indexes can significantly speed up read access to tables for common\r
- filtering conditions, every time a row is created or updated the corresponding\r
- indexes also need to be maintained - which can decrease the performance of\r
- writes to the database. Be careful to keep the balance of read performance\r
- versus write performance in mind if you plan to create custom indexes in your\r
- Evergreen database.</simpara></note>\r
- </section>\r
- <section id="inserting_updating_and_deleting_data">\r
- <title>Inserting, updating, and deleting data</title>\r
- <simplesect id="_inserting_data">\r
- <title>Inserting data</title>\r
- <simpara>To insert one or more rows into a table, use the INSERT statement to identify\r
- the target table and list the columns in the table for which you are going to\r
- provide values for each row. If you do not list one or more columns contained\r
- in the table, the database will automatically supply a <literal>NULL</literal> value for those\r
- columns. The values for each row follow the <literal>VALUES</literal> clause and are grouped in\r
- parentheses and delimited by commas. Each row, in turn, is delimited by commas\r
- (<emphasis>this multiple row syntax requires PostgreSQL 8.2 or higher</emphasis>).</simpara>\r
- <simpara>For example, to insert two rows into the <literal>permission.usr_grp_map</literal> table:</simpara>\r
- <formalpara><title>Inserting rows into the <literal>permission.usr_grp_map</literal> table</title><para>\r
- <programlisting language="sql" linenumbering="unnumbered">INSERT INTO permission.usr_grp_map (usr, grp)\r
- VALUES (2, 10), (2, 4)\r
- ;</programlisting>\r
- </para></formalpara>\r
- <simpara>Of course, as with the rest of SQL, you can replace individual column values\r
- with one or more use sub-selects:</simpara>\r
- <formalpara><title>Inserting rows using sub-selects instead of integers</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-INSERT INTO permission.usr_grp_map (usr, grp)\r
- VALUES (\r
- (SELECT id FROM actor.usr\r
- WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
- (SELECT id FROM permission.grp_tree\r
- WHERE name = 'Local System Administrator')\r
- ), (\r
- (SELECT id FROM actor.usr\r
- WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
- (SELECT id FROM permission.grp_tree\r
- WHERE name = 'Circulator')\r
- )\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_inserting_data_using_a_select_statement">\r
- <title>Inserting data using a SELECT statement</title>\r
- <simpara>Sometimes you want to insert a bulk set of data into a new table based on\r
- a query result. Rather than a <literal>VALUES</literal> clause, you can use a <literal>SELECT</literal>\r
- statement to insert one or more rows matching the column definitions. This\r
- is a good time to point out that you can include explicit values, instead\r
- of just column identifiers, in the return columns of the <literal>SELECT</literal> statement.\r
- The explicit values are returned in every row of the result set.</simpara>\r
- <simpara>In the following example, we insert 6 rows into the <literal>permission.usr_grp_map</literal>\r
- table; each row will have a <literal>usr</literal> column value of 1, with varying values for\r
- the <literal>grp</literal> column value based on the <literal>id</literal> column values returned from\r
- <literal>permission.grp_tree</literal>:</simpara>\r
- <formalpara><title>Inserting rows via a <literal>SELECT</literal> statement</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-INSERT INTO permission.usr_grp_map (usr, grp)\r
- SELECT 1, id\r
- FROM permission.grp_tree\r
- WHERE id > 2\r
-;\r
-\r
-INSERT 0 6\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_deleting_rows">\r
- <title>Deleting rows</title>\r
- <simpara>Deleting data from a table is normally fairly easy. To delete rows from a table,\r
- issue a <literal>DELETE</literal> statement identifying the table from which you want to delete\r
- rows and a <literal>WHERE</literal> clause identifying the row or rows that should be deleted.</simpara>\r
- <simpara>In the following example, we delete all of the rows from the\r
- <literal>permission.grp_perm_map</literal> table where the permission maps to\r
- <literal>UPDATE_ORG_UNIT_CLOSING</literal> and the group is anything other than administrators:</simpara>\r
- <formalpara><title>Deleting rows from a table</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-DELETE FROM permission.grp_perm_map\r
- WHERE grp IN (\r
- SELECT id\r
- FROM permission.grp_tree\r
- WHERE name != 'Local System Administrator'\r
- ) AND perm = (\r
- SELECT id\r
- FROM permission.perm_list\r
- WHERE code = 'UPDATE_ORG_UNIT_CLOSING'\r
- )\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- <note><simpara>There are two main reasons that a <literal>DELETE</literal> statement may not actually\r
- delete rows from a table, even when the rows meet the conditional clause.</simpara></note>\r
- <orderedlist numeration="arabic">\r
- <listitem>\r
- <simpara>\r
- If the row contains a value that is the target of a relational constraint,\r
- for example, if another table has a foreign key pointing at your target\r
- table, you will be prevented from deleting a row with a value corresponding\r
- to a row in the dependent table.\r
- </simpara>\r
- </listitem>\r
- <listitem>\r
- <simpara>\r
- If the table has a rule that substitutes a different action for a <literal>DELETE</literal>\r
- statement, the deletion will not take place. In Evergreen it is common for a\r
- table to have a rule that substitutes the action of setting a <literal>deleted</literal> column\r
- to <literal>TRUE</literal>. For example, if a book is discarded, deleting the row representing\r
- the copy from the <literal>asset.copy</literal> table would severely affect circulation statistics,\r
- bills, borrowing histories, and their corresponding tables in the database that\r
- have foreign keys pointing at the <literal>asset.copy</literal> table (<literal>action.circulation</literal> and\r
- <literal>money.billing</literal> and its children respectively). Instead, the <literal>deleted</literal> column\r
- value is set to <literal>TRUE</literal> and Evergreen’s application logic skips over these rows\r
- in most cases.\r
- </simpara>\r
- </listitem>\r
- </orderedlist>\r
- </simplesect>\r
- <simplesect id="_updating_rows">\r
- <title>Updating rows</title>\r
- <simpara>To update rows in a table, issue an <literal>UPDATE</literal> statement identifying the table\r
- you want to update, the column or columns that you want to set with their\r
- respective new values, and (optionally) a <literal>WHERE</literal> clause identifying the row or\r
- rows that should be updated.</simpara>\r
- <simpara>Following is the syntax for the <literal>UPDATE</literal> statement:</simpara>\r
- <blockquote>\r
- <literallayout><literal>UPDATE</literal> [<emphasis>table-name</emphasis>]\r
- <literal>SET</literal> [<emphasis>column</emphasis>] <literal>TO</literal> [<emphasis>new-value</emphasis>]\r
- <literal>WHERE</literal> [<emphasis>condition</emphasis>]\r
- ;</literallayout>\r
- </blockquote>\r
- </simplesect>\r
- </section>\r
- <section id="query_requests">\r
- <title>Query requests</title>\r
- <simpara>The following queries were requested by Bibliomation, but might be reusable\r
- by other libraries.</simpara>\r
- <simplesect id="_monthly_circulation_stats_by_collection_code_library">\r
- <title>Monthly circulation stats by collection code / library</title>\r
- <formalpara><title>Monthly Circulation Stats by Collection Code/Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", acl.name AS "Copy Location"\r
- FROM asset.copy ac\r
- INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
- INNER JOIN action.circulation acirc ON acirc.target_copy = ac.id\r
- INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
- WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
- AND acirc.desk_renewal IS FALSE\r
- AND acirc.opac_renewal IS FALSE\r
- AND acirc.phone_renewal IS FALSE\r
- GROUP BY aou.name, acl.name\r
- ORDER BY aou.name, acl.name, 1\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_monthly_circulation_stats_by_borrower_stat_library">\r
- <title>Monthly circulation stats by borrower stat / library</title>\r
- <formalpara><title>Monthly Circulation Stats by Borrower Stat/Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", asceum.stat_cat_entry AS "Borrower Stat"\r
- FROM action.circulation acirc\r
- INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
- INNER JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = acirc.usr\r
- INNER JOIN actor.stat_cat astat ON asceum.stat_cat = astat.id\r
- WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
- AND astat.name = 'Preferred language'\r
- AND acirc.desk_renewal IS FALSE\r
- AND acirc.opac_renewal IS FALSE\r
- AND acirc.phone_renewal IS FALSE\r
- GROUP BY aou.name, asceum.stat_cat_entry\r
- ORDER BY aou.name, asceum.stat_cat_entry, 1\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_monthly_intralibrary_loan_stats_by_library">\r
- <title>Monthly intralibrary loan stats by library</title>\r
- <formalpara><title>Monthly Intralibrary Loan Stats by Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT aou.name AS "Library", COUNT(acirc.id)\r
- FROM action.circulation acirc\r
- INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
- INNER JOIN asset.copy ac ON acirc.target_copy = ac.id\r
- INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
- WHERE acirc.circ_lib != acn.owning_lib\r
- AND DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
- AND acirc.desk_renewal IS FALSE\r
- AND acirc.opac_renewal IS FALSE\r
- AND acirc.phone_renewal IS FALSE\r
- GROUP by aou.name\r
- ORDER BY aou.name, 2\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_monthly_borrowers_added_by_profile_adult_child_etc_library">\r
- <title>Monthly borrowers added by profile (adult, child, etc) / library</title>\r
- <formalpara><title>Monthly Borrowers Added by Profile (Adult, Child, etc)/Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
- FROM actor.usr au\r
- INNER JOIN permission.grp_tree pgt\r
- ON au.profile = pgt.id\r
- INNER JOIN actor.org_unit aou\r
- ON aou.id = au.home_ou\r
- WHERE au.deleted IS FALSE\r
- AND DATE_TRUNC('MONTH', au.create_date) = DATE_TRUNC('MONTH', NOW() - '3 months'::interval)\r
- GROUP BY pgt.name, aou.name\r
- ORDER BY aou.name, pgt.name\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_borrower_count_by_profile_adult_child_etc_library">\r
- <title>Borrower count by profile (adult, child, etc) / library</title>\r
- <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
- FROM actor.usr au\r
- INNER JOIN permission.grp_tree pgt\r
- ON au.profile = pgt.id\r
- INNER JOIN actor.org_unit aou\r
- ON aou.id = au.home_ou\r
- WHERE au.deleted IS FALSE\r
- GROUP BY pgt.name, aou.name\r
- ORDER BY aou.name, pgt.name\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_monthly_items_added_by_collection_library">\r
- <title>Monthly items added by collection / library</title>\r
- <simpara>We define a <quote>collection</quote> as a shelving location in Evergreen.</simpara>\r
- <formalpara><title>Monthly Items Added by Collection/Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-SELECT aou.name AS "Library", acl.name, COUNT(ac.barcode)\r
- FROM actor.org_unit aou\r
- INNER JOIN asset.call_number acn ON acn.owning_lib = aou.id\r
- INNER JOIN asset.copy ac ON ac.call_number = acn.id\r
- INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
- WHERE ac.deleted IS FALSE\r
- AND acn.deleted IS FALSE\r
- AND DATE_TRUNC('MONTH', ac.create_date) = DATE_TRUNC('MONTH', NOW() - '1 month'::interval)\r
- GROUP BY aou.name, acl.name\r
- ORDER BY aou.name, acl.name\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_hold_purchase_alert_by_library">\r
- <title>Hold purchase alert by library</title>\r
- <simpara>in the following set of queries, we bring together the active title, volume,\r
- and copy holds and display those that have more than a certain number of holds\r
- per title. The goal is to UNION ALL the three queries, then group by the\r
- bibliographic record ID and display the title / author information for those\r
- records that have more than a given threshold of holds.</simpara>\r
- <formalpara><title>Hold Purchase Alert by Library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
--- Title holds\r
-SELECT all_holds.bib_id, aou.name, rmsr.title, rmsr.author, COUNT(all_holds.bib_id)\r
- FROM\r
- (\r
- (\r
- SELECT target, request_lib\r
- FROM action.hold_request\r
- WHERE hold_type = 'T'\r
- AND fulfillment_time IS NULL\r
- AND cancel_time IS NULL\r
- )\r
- UNION ALL\r
- -- Volume holds\r
- (\r
- SELECT bre.id, request_lib\r
- FROM action.hold_request ahr\r
- INNER JOIN asset.call_number acn ON ahr.target = acn.id\r
- INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
- WHERE ahr.hold_type = 'V'\r
- AND ahr.fulfillment_time IS NULL\r
- AND ahr.cancel_time IS NULL\r
- )\r
- UNION ALL\r
- -- Copy holds\r
- (\r
- SELECT bre.id, request_lib\r
- FROM action.hold_request ahr\r
- INNER JOIN asset.copy ac ON ahr.target = ac.id\r
- INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
- INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
- WHERE ahr.hold_type = 'C'\r
- AND ahr.fulfillment_time IS NULL\r
- AND ahr.cancel_time IS NULL\r
- )\r
- ) AS all_holds(bib_id, request_lib)\r
- INNER JOIN reporter.materialized_simple_record rmsr\r
- INNER JOIN actor.org_unit aou ON aou.id = all_holds.request_lib\r
- ON rmsr.id = all_holds.bib_id\r
- GROUP BY all_holds.bib_id, aou.name, rmsr.id, rmsr.title, rmsr.author\r
- HAVING COUNT(all_holds.bib_id) > 2\r
- ORDER BY aou.name\r
-;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- <simplesect id="_update_borrower_records_with_a_different_home_library">\r
- <title>Update borrower records with a different home library</title>\r
- <simpara>In this example, the library has opened a new branch in a growing area,\r
- and wants to reassign the home library for the patrons in the vicinity of\r
- the new branch to the new branch. To accomplish this, we create a staging table\r
- that holds a set of city names and the corresponding branch shortname for the home\r
- library for each city.</simpara>\r
- <simpara>Then we issue an <literal>UPDATE</literal> statement to set the home library for patrons with a\r
- physical address with a city that matches the city names in our staging table.</simpara>\r
- <formalpara><title>Update borrower records with a different home library</title><para>\r
-<programlisting language="sql" linenumbering="unnumbered">\r
-CREATE SCHEMA staging;\r
-CREATE TABLE staging.city_home_ou_map (city TEXT, ou_shortname TEXT,\r
- FOREIGN KEY (ou_shortname) REFERENCES actor.org_unit (shortname));\r
-INSERT INTO staging.city_home_ou_map (city, ou_shortname)\r
- VALUES ('Southbury', 'BR1'), ('Middlebury', 'BR2'), ('Hartford', 'BR3');\r
-BEGIN;\r
-\r
-UPDATE actor.usr au SET home_ou = COALESCE(\r
- (\r
- SELECT aou.id\r
- FROM actor.org_unit aou\r
- INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
- INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
- WHERE au.id = aua.usr\r
- GROUP BY aou.id\r
- ), home_ou)\r
-WHERE (\r
- SELECT aou.id\r
- FROM actor.org_unit aou\r
- INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
- INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
- WHERE au.id = aua.usr\r
- GROUP BY aou.id\r
-) IS NOT NULL;\r
-</programlisting>\r
- </para></formalpara>\r
- </simplesect>\r
- </section>\r
- \r
-</chapter>\r
projects / Evergreen-DocBook.git / blobdiff