53622129e3595980d08582097cd8b36f2b231df2
[working/Evergreen.git] / 1.6 / development / introduction_to_sql.xml
1 <?xml version="1.0" encoding="UTF-8"?>\r
2 <chapter xml:id="intro_to_sql" xmlns="http://docbook.org/ns/docbook" version="5.0" xml:lang="EN"\r
3     xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">\r
4         <chapterinfo>\r
5                 <title>Introduction to SQL for Evergreen Administrators</title>\r
6         </chapterinfo>\r
7         <abstract id="itnroSQL_abstract">\r
8                 <simpara>This chapter was taken from Dan Scott's <emphasis>Introduction to SQL for Evergreen Administrators</emphasis>, February 2010.</simpara>\r
9         </abstract>     \r
10         <section id="intro_to_databases">\r
11                         <title>Introduction to SQL Databases</title>\r
12                 <simplesect>\r
13                         <title>Introduction</title>\r
14                         <simpara>Over time, the SQL database has become the standard method of storing,\r
15                         retrieving, and processing raw data for applications. Ranging from embedded\r
16                         databases such as SQLite and Apache Derby, to enterprise databases such as\r
17                         Oracle and IBM DB2, any SQL database offers basic advantages to application\r
18                         developers such as standard interfaces (Structured Query Language (SQL), Java\r
19                         Database Connectivity (JDBC), Open Database Connectivity (ODBC), Perl Database\r
20                         Independent Interface (DBI)), a standard conceptual model of data (tables,\r
21                         fields, relationships, constraints, etc), performance in storing and retrieving\r
22                         data, concurrent access, etc.</simpara>\r
23                         <simpara>Evergreen is built on PostgreSQL, an open source SQL database that began as\r
24                         <literal>POSTGRES</literal> at the University of California at Berkeley in 1986 as a research\r
25                         project led by Professor Michael Stonebraker. A SQL interface was added to a\r
26                         fork of the original POSTGRES Berkelely code in 1994, and in 1996 the project\r
27                         was renamed PostgreSQL.</simpara>\r
28                 </simplesect>\r
29                 <simplesect id="_tables">\r
30                         <title>Tables</title>\r
31                         <simpara>The table is the cornerstone of a SQL database. Conceptually, a database table\r
32                         is similar to a single sheet in a spreadsheet: every table has one or more\r
33                         columns, with each row in the table containing values for each column. Each\r
34                         column in a table defines an attribute corresponding to a particular data type.</simpara>\r
35                         <simpara>We&#8217;ll insert a row into a table, then display the resulting contents. Don&#8217;t\r
36                         worry if the INSERT statement is completely unfamiliar, we&#8217;ll talk more about\r
37                         the syntax of the insert statement later.</simpara>\r
38                         <formalpara><title><literal>actor.usr_note</literal> database table</title><para>\r
39                         <programlisting language="sql" linenumbering="unnumbered">evergreen=# INSERT INTO actor.usr_note (usr, creator, pub, title, value)\r
40                             VALUES (1, 1, TRUE, 'Who is this guy?', 'He''s the administrator!');\r
41 \r
42                         evergreen=# select id, usr, creator, pub, title, value from actor.usr_note;\r
43                          id | usr | creator | pub |      title       |          value\r
44                         ----+-----+---------+-----+------------------+-------------------------\r
45                           1 |   1 |       1 | t   | Who is this guy? | He's the administrator!\r
46                         (1 rows)</programlisting>\r
47                         </para></formalpara>\r
48                         <simpara>PostgreSQL supports table inheritance, which lets you define tables that\r
49                         inherit the column definitions of a given parent table. A search of the data in\r
50                         the parent table includes the data in the child tables. Evergreen uses table\r
51                         inheritance: for example, the <literal>action.circulation</literal> table is a child of the\r
52                         <literal>money.billable_xact</literal> table, and the <literal>money.*_payment</literal> tables all inherit from\r
53                         the <literal>money.payment</literal> parent table.</simpara>\r
54                 </simplesect>\r
55                 <simplesect id="_schemas">\r
56                         <title>Schemas</title>\r
57                         <simpara>PostgreSQL, like most SQL databases, supports the use of schema names to group\r
58                         collections of tables and other database objects together. You might think of\r
59                         schemas as namespaces if you&#8217;re a programmer; or you might think of the schema\r
60                         / table / column relationship like the area code / exchange / local number\r
61                         structure of a telephone number.</simpara>\r
62                         <table\r
63                         frame="all"\r
64                         rowsep="1" colsep="1"\r
65                         >\r
66                         <title>Examples: database object names</title>\r
67                         <?dbhtml table-width="80%"?>\r
68                         <?dbfo table-width="80%"?>\r
69                         <tgroup cols="4">\r
70                         <colspec colname="col_1" colwidth="85*"/>\r
71                         <colspec colname="col_2" colwidth="85*"/>\r
72                         <colspec colname="col_3" colwidth="85*"/>\r
73                         <colspec colname="col_4" colwidth="85*"/>\r
74                         <thead>\r
75                         <row>\r
76                         <entry align="left" valign="top">Full name </entry>\r
77                         <entry align="left" valign="top">Schema name </entry>\r
78                         <entry align="left" valign="top">Table name </entry>\r
79                         <entry align="left" valign="top">Field name</entry>\r
80                         </row>\r
81                         </thead>\r
82                         <tbody>\r
83                         <row>\r
84                         <entry align="left" valign="top"><simpara>actor.usr_note.title</simpara></entry>\r
85                         <entry align="left" valign="top"><simpara>actor</simpara></entry>\r
86                         <entry align="left" valign="top"><simpara>usr_note</simpara></entry>\r
87                         <entry align="left" valign="top"><simpara>title</simpara></entry>\r
88                         </row>\r
89                         <row>\r
90                         <entry align="left" valign="top"><simpara>biblio.record_entry.marc</simpara></entry>\r
91                         <entry align="left" valign="top"><simpara>biblio</simpara></entry>\r
92                         <entry align="left" valign="top"><simpara>record_entry</simpara></entry>\r
93                         <entry align="left" valign="top"><simpara>marc</simpara></entry>\r
94                         </row>\r
95                         </tbody>\r
96                         </tgroup>\r
97                         </table>\r
98                         <simpara>The default schema name in PostgreSQL is <literal>public</literal>, so if you do not specify a\r
99                         schema name when creating or accessing a database object, PostgreSQL will use\r
100                         the <literal>public</literal> schema. As a result, you might not find the object that you&#8217;re\r
101                         looking for if you don&#8217;t use the appropriate schema.</simpara>\r
102                         <formalpara><title>Example: Creating a table without a specific schema</title><para>\r
103                         <programlisting language="sql" linenumbering="unnumbered">evergreen=# CREATE TABLE foobar (foo TEXT, bar TEXT);\r
104                         CREATE TABLE\r
105                         evergreen=# \d foobar\r
106                            Table "public.foobar"\r
107                          Column | Type | Modifiers\r
108                         --------+------+-----------\r
109                          foo    | text |\r
110                          bar    | text |</programlisting>\r
111                         </para></formalpara>\r
112                         <formalpara><title>Example: Trying to access a unqualified table outside of the public schema</title><para>\r
113                         <programlisting language="sql" linenumbering="unnumbered">evergreen=# SELECT * FROM usr_note;\r
114                         ERROR:  relation "usr_note" does not exist\r
115                         LINE 1: SELECT * FROM usr_note;\r
116                                               ^</programlisting>\r
117                         </para></formalpara>\r
118                         <simpara>Evergreen uses schemas to organize all of its tables with mostly intuitive,\r
119                         if short, schema names. Here&#8217;s the current (as of 2010-01-03) list of schemas\r
120                         used by Evergreen:</simpara>\r
121                         <table\r
122                         frame="all"\r
123                         rowsep="1" colsep="1"\r
124                         >\r
125                         <title>Evergreen schema names</title>\r
126                         <?dbhtml table-width="80%"?>\r
127                         <?dbfo table-width="80%"?>\r
128                         <tgroup cols="2">\r
129                         <colspec colname="col_1" colwidth="170*"/>\r
130                         <colspec colname="col_2" colwidth="170*"/>\r
131                         <thead>\r
132                         <row>\r
133                         <entry align="left" valign="top">Schema name </entry>\r
134                         <entry align="left" valign="top">Description</entry>\r
135                         </row>\r
136                         </thead>\r
137                         <tbody>\r
138                         <row>\r
139                         <entry align="left" valign="top"><simpara><literal>acq</literal></simpara></entry>\r
140                         <entry align="left" valign="top"><simpara>Acquisitions</simpara></entry>\r
141                         </row>\r
142                         <row>\r
143                         <entry align="left" valign="top"><simpara><literal>action</literal></simpara></entry>\r
144                         <entry align="left" valign="top"><simpara>Circulation actions</simpara></entry>\r
145                         </row>\r
146                         <row>\r
147                         <entry align="left" valign="top"><simpara><literal>action_trigger</literal></simpara></entry>\r
148                         <entry align="left" valign="top"><simpara>Event mechanisms</simpara></entry>\r
149                         </row>\r
150                         <row>\r
151                         <entry align="left" valign="top"><simpara><literal>actor</literal></simpara></entry>\r
152                         <entry align="left" valign="top"><simpara>Evergreen users and organization units</simpara></entry>\r
153                         </row>\r
154                         <row>\r
155                         <entry align="left" valign="top"><simpara><literal>asset</literal></simpara></entry>\r
156                         <entry align="left" valign="top"><simpara>Call numbers and copies</simpara></entry>\r
157                         </row>\r
158                         <row>\r
159                         <entry align="left" valign="top"><simpara><literal>auditor</literal></simpara></entry>\r
160                         <entry align="left" valign="top"><simpara>Track history of changes to selected tables</simpara></entry>\r
161                         </row>\r
162                         <row>\r
163                         <entry align="left" valign="top"><simpara><literal>authority</literal></simpara></entry>\r
164                         <entry align="left" valign="top"><simpara>Authority records</simpara></entry>\r
165                         </row>\r
166                         <row>\r
167                         <entry align="left" valign="top"><simpara><literal>biblio</literal></simpara></entry>\r
168                         <entry align="left" valign="top"><simpara>Bibliographic records</simpara></entry>\r
169                         </row>\r
170                         <row>\r
171                         <entry align="left" valign="top"><simpara><literal>booking</literal></simpara></entry>\r
172                         <entry align="left" valign="top"><simpara>Resource bookings</simpara></entry>\r
173                         </row>\r
174                         <row>\r
175                         <entry align="left" valign="top"><simpara><literal>config</literal></simpara></entry>\r
176                         <entry align="left" valign="top"><simpara>Evergreen configurable options</simpara></entry>\r
177                         </row>\r
178                         <row>\r
179                         <entry align="left" valign="top"><simpara><literal>container</literal></simpara></entry>\r
180                         <entry align="left" valign="top"><simpara>Buckets for records, call numbers, copies, and users</simpara></entry>\r
181                         </row>\r
182                         <row>\r
183                         <entry align="left" valign="top"><simpara><literal>extend_reporter</literal></simpara></entry>\r
184                         <entry align="left" valign="top"><simpara>Extra views for report definitions</simpara></entry>\r
185                         </row>\r
186                         <row>\r
187                         <entry align="left" valign="top"><simpara><literal>metabib</literal></simpara></entry>\r
188                         <entry align="left" valign="top"><simpara>Metadata about bibliographic records</simpara></entry>\r
189                         </row>\r
190                         <row>\r
191                         <entry align="left" valign="top"><simpara><literal>money</literal></simpara></entry>\r
192                         <entry align="left" valign="top"><simpara>Fines and bills</simpara></entry>\r
193                         </row>\r
194                         <row>\r
195                         <entry align="left" valign="top"><simpara><literal>offline</literal></simpara></entry>\r
196                         <entry align="left" valign="top"><simpara>Offline transactions</simpara></entry>\r
197                         </row>\r
198                         <row>\r
199                         <entry align="left" valign="top"><simpara><literal>permission</literal></simpara></entry>\r
200                         <entry align="left" valign="top"><simpara>User permissions</simpara></entry>\r
201                         </row>\r
202                         <row>\r
203                         <entry align="left" valign="top"><simpara><literal>query</literal></simpara></entry>\r
204                         <entry align="left" valign="top"><simpara>Stored SQL statements</simpara></entry>\r
205                         </row>\r
206                         <row>\r
207                         <entry align="left" valign="top"><simpara><literal>reporter</literal></simpara></entry>\r
208                         <entry align="left" valign="top"><simpara>Report definitions</simpara></entry>\r
209                         </row>\r
210                         <row>\r
211                         <entry align="left" valign="top"><simpara><literal>search</literal></simpara></entry>\r
212                         <entry align="left" valign="top"><simpara>Search functions</simpara></entry>\r
213                         </row>\r
214                         <row>\r
215                         <entry align="left" valign="top"><simpara><literal>serial</literal></simpara></entry>\r
216                         <entry align="left" valign="top"><simpara>Serial MFHD records</simpara></entry>\r
217                         </row>\r
218                         <row>\r
219                         <entry align="left" valign="top"><simpara><literal>stats</literal></simpara></entry>\r
220                         <entry align="left" valign="top"><simpara>Convenient views of circulation and asset statistics</simpara></entry>\r
221                         </row>\r
222                         <row>\r
223                         <entry align="left" valign="top"><simpara><literal>vandelay</literal></simpara></entry>\r
224                         <entry align="left" valign="top"><simpara>MARC batch importer and exporter</simpara></entry>\r
225                         </row>\r
226                         </tbody>\r
227                         </tgroup>\r
228                         </table>\r
229                         <note><simpara>The term <emphasis>schema</emphasis> has two meanings in the world of SQL databases. We have\r
230                         discussed the schema as a conceptual grouping of tables and other database\r
231                         objects within a given namespace; for example, "the <emphasis role="strong">actor</emphasis> schema contains the\r
232                         tables and functions related to users and organizational units". Another common\r
233                         usage of <emphasis>schema</emphasis> is to refer to the entire data model for a given database;\r
234                         for example, "the Evergreen database schema".</simpara></note>\r
235                 </simplesect>\r
236                 <simplesect id="_columns">\r
237                         <title>Columns</title>\r
238                         <simpara>Each column definition consists of:</simpara>\r
239                         <itemizedlist>\r
240                         <listitem>\r
241                         <simpara>\r
242                         a data type\r
243                         </simpara>\r
244                         </listitem>\r
245                         <listitem>\r
246                         <simpara>\r
247                         (optionally) a default value to be used whenever a row is inserted that\r
248                              does not contain a specific value\r
249                         </simpara>\r
250                         </listitem>\r
251                         <listitem>\r
252                         <simpara>\r
253                         (optionally) one or more constraints on the values beyond data type\r
254                         </simpara>\r
255                         </listitem>\r
256                         </itemizedlist>\r
257                         <simpara>Although PostgreSQL supports dozens of data types, Evergreen makes our life\r
258                         easier by only using a handful.</simpara>\r
259                         <table\r
260                         frame="all"\r
261                         rowsep="1" colsep="1"\r
262                         >\r
263                         <title>PostgreSQL data types used by Evergreen</title>\r
264                         <?dbhtml table-width="90%"?>\r
265                         <?dbfo table-width="90%"?>\r
266                         <tgroup cols="3">\r
267                         <colspec colname="col_1" colwidth="77*"/>\r
268                         <colspec colname="col_2" colwidth="77*"/>\r
269                         <colspec colname="col_3" colwidth="230*"/>\r
270                         <thead>\r
271                         <row>\r
272                         <entry align="left" valign="top">Type name   </entry>\r
273                         <entry align="left" valign="top">Description    </entry>\r
274                         <entry align="left" valign="top">Limits</entry>\r
275                         </row>\r
276                         </thead>\r
277                         <tbody>\r
278                         <row>\r
279                         <entry align="left" valign="top"><simpara><literal>INTEGER</literal></simpara></entry>\r
280                         <entry align="left" valign="top"><simpara>Medium integer</simpara></entry>\r
281                         <entry align="left" valign="top"><simpara>-2147483648 to +2147483647</simpara></entry>\r
282                         </row>\r
283                         <row>\r
284                         <entry align="left" valign="top"><simpara><literal>BIGINT</literal></simpara></entry>\r
285                         <entry align="left" valign="top"><simpara>Large integer</simpara></entry>\r
286                         <entry align="left" valign="top"><simpara>-9223372036854775808 to 9223372036854775807</simpara></entry>\r
287                         </row>\r
288                         <row>\r
289                         <entry align="left" valign="top"><simpara><literal>SERIAL</literal></simpara></entry>\r
290                         <entry align="left" valign="top"><simpara>Sequential integer</simpara></entry>\r
291                         <entry align="left" valign="top"><simpara>1 to 2147483647</simpara></entry>\r
292                         </row>\r
293                         <row>\r
294                         <entry align="left" valign="top"><simpara><literal>BIGSERIAL</literal></simpara></entry>\r
295                         <entry align="left" valign="top"><simpara>Large sequential integer</simpara></entry>\r
296                         <entry align="left" valign="top"><simpara>1 to 9223372036854775807</simpara></entry>\r
297                         </row>\r
298                         <row>\r
299                         <entry align="left" valign="top"><simpara><literal>TEXT</literal></simpara></entry>\r
300                         <entry align="left" valign="top"><simpara>Variable length character data</simpara></entry>\r
301                         <entry align="left" valign="top"><simpara>Unlimited length</simpara></entry>\r
302                         </row>\r
303                         <row>\r
304                         <entry align="left" valign="top"><simpara><literal>BOOL</literal></simpara></entry>\r
305                         <entry align="left" valign="top"><simpara>Boolean</simpara></entry>\r
306                         <entry align="left" valign="top"><simpara>TRUE or FALSE</simpara></entry>\r
307                         </row>\r
308                         <row>\r
309                         <entry align="left" valign="top"><simpara><literal>TIMESTAMP WITH TIME ZONE</literal></simpara></entry>\r
310                         <entry align="left" valign="top"><simpara>Timestamp</simpara></entry>\r
311                         <entry align="left" valign="top"><simpara>4713 BC to 294276 AD</simpara></entry>\r
312                         </row>\r
313                         <row>\r
314                         <entry align="left" valign="top"><simpara><literal>TIME</literal></simpara></entry>\r
315                         <entry align="left" valign="top"><simpara>Time</simpara></entry>\r
316                         <entry align="left" valign="top"><simpara>Expressed in HH:MM:SS</simpara></entry>\r
317                         </row>\r
318                         <row>\r
319                         <entry align="left" valign="top"><simpara><literal>NUMERIC</literal>(precision, scale)</simpara></entry>\r
320                         <entry align="left" valign="top"><simpara>Decimal</simpara></entry>\r
321                         <entry align="left" valign="top"><simpara>Up to 1000 digits of precision. In Evergreen mostly used for money\r
322                         values, with a precision of 6 and a scale of 2 (<literal>####.##</literal>).</simpara></entry>\r
323                         </row>\r
324                         </tbody>\r
325                         </tgroup>\r
326                         </table>\r
327                         <simpara>Full details about these data types are available from the\r
328                         <ulink url="http://www.postgresql.org/docs/8.4/static/datatype.html">data types section of\r
329                         the PostgreSQL manual</ulink>.</simpara>\r
330                 </simplesect>\r
331                 <simplesect id="_constraints">\r
332                         <title>Constraints</title>\r
333                         <simplesect id="_prevent_null_values">\r
334                                 <title>Prevent NULL values</title>\r
335                                 <simpara>A column definition may include the constraint <literal>NOT NULL</literal> to prevent NULL\r
336                                 values. In PostgreSQL, a NULL value is not the equivalent of zero or false or\r
337                                 an empty string; it is an explicit non-value with special properties. We&#8217;ll\r
338                                 talk more about how to work with NULL values when we get to queries.</simpara>\r
339                         </simplesect>\r
340                         <simplesect id="_primary_key">\r
341                                 <title>Primary key</title>\r
342                                 <simpara>Every table can have at most one primary key. A primary key consists of one or\r
343                                 more columns which together uniquely identify each row in a table. If you\r
344                                 attempt to insert a row into a table that would create a duplicate or NULL\r
345                                 primary key entry, the database rejects the row and returns an error.</simpara>\r
346                                 <simpara>Natural primary keys are drawn from the intrinsic properties of the data being\r
347                                 modelled. For example, some potential natural primary keys for a table that\r
348                                 contains people would be:</simpara>\r
349                                 <table\r
350                                 frame="all"\r
351                                 rowsep="1" colsep="1"\r
352                                 >\r
353                                 <title>Example: Some potential natural primary keys for a table of people</title>\r
354                                 <?dbhtml table-width="90%"?>\r
355                                 <?dbfo table-width="90%"?>\r
356                                 <tgroup cols="3">\r
357                                 <colspec colname="col_1" colwidth="77*"/>\r
358                                 <colspec colname="col_2" colwidth="153*"/>\r
359                                 <colspec colname="col_3" colwidth="153*"/>\r
360                                 <thead>\r
361                                 <row>\r
362                                 <entry align="left" valign="top">Natural key </entry>\r
363                                 <entry align="left" valign="top">Pros </entry>\r
364                                 <entry align="left" valign="top">Cons</entry>\r
365                                 </row>\r
366                                 </thead>\r
367                                 <tbody>\r
368                                 <row>\r
369                                 <entry align="left" valign="top"><simpara>First name, last name, address</simpara></entry>\r
370                                 <entry align="left" valign="top"><simpara>No two people with the same name would ever live at the same address, right?</simpara></entry>\r
371                                 <entry align="left" valign="top"><simpara>Lots of columns force data duplication in referencing tables</simpara></entry>\r
372                                 </row>\r
373                                 <row>\r
374                                 <entry align="left" valign="top"><simpara>SSN or driver&#8217;s license</simpara></entry>\r
375                                 <entry align="left" valign="top"><simpara>These are guaranteed to be unique</simpara></entry>\r
376                                 <entry align="left" valign="top"><simpara>Lots of people don&#8217;t have an SSN or a driver&#8217;s license</simpara></entry>\r
377                                 </row>\r
378                                 </tbody>\r
379                                 </tgroup>\r
380                                 </table>\r
381                                 <simpara>To avoid problems with natural keys, many applications instead define surrogate\r
382                                 primary keys. A surrogate primary keys is a column with an autoincrementing\r
383                                 integer value added to a table definition that ensures uniqueness.</simpara>\r
384                                 <simpara>Evergreen uses surrogate keys (a column named <literal>id</literal> with a <literal>SERIAL</literal> data type)\r
385                                 for most of its tables.</simpara>\r
386                         </simplesect>\r
387                         <simplesect id="_foreign_keys">\r
388                                 <title>Foreign keys</title>\r
389                                 <simpara>Every table can contain zero or more foreign keys: one or more columns that\r
390                                 refer to the primary key of another table.</simpara>\r
391                                 <simpara>For example, let&#8217;s consider Evergreen&#8217;s modelling of the basic relationship\r
392                                 between copies, call numbers, and bibliographic records. Bibliographic records\r
393                                 contained in the <literal>biblio.record_entry</literal> table can have call numbers attached to\r
394                                 them. Call numbers are contained in the <literal>asset.call_number</literal> table, and they can\r
395                                 have copies attached to them. Copies are contained in the <literal>asset.copy</literal> table.</simpara>\r
396                                 <table\r
397                                 frame="all"\r
398                                 rowsep="1" colsep="1"\r
399                                 >\r
400                                 <title>Example: Evergreen&#8217;s copy / call number / bibliographic record relationships</title>\r
401                                 <?dbhtml table-width="100%"?>\r
402                                 <?dbfo table-width="100%"?>\r
403                                 <tgroup cols="4">\r
404                                 <colspec colname="col_1" colwidth="106*"/>\r
405                                 <colspec colname="col_2" colwidth="106*"/>\r
406                                 <colspec colname="col_3" colwidth="106*"/>\r
407                                 <colspec colname="col_4" colwidth="106*"/>\r
408                                 <thead>\r
409                                 <row>\r
410                                 <entry align="left" valign="top">Table </entry>\r
411                                 <entry align="left" valign="top">Primary key </entry>\r
412                                 <entry align="left" valign="top">Column with a foreign key </entry>\r
413                                 <entry align="left" valign="top">Points to</entry>\r
414                                 </row>\r
415                                 </thead>\r
416                                 <tbody>\r
417                                 <row>\r
418                                 <entry align="left" valign="top"><simpara>asset.copy</simpara></entry>\r
419                                 <entry align="left" valign="top"><simpara>asset.copy.id</simpara></entry>\r
420                                 <entry align="left" valign="top"><simpara>asset.copy.call_number</simpara></entry>\r
421                                 <entry align="left" valign="top"><simpara>asset.call_number.id</simpara></entry>\r
422                                 </row>\r
423                                 <row>\r
424                                 <entry align="left" valign="top"><simpara>asset.call_number</simpara></entry>\r
425                                 <entry align="left" valign="top"><simpara>asset.call_number.id</simpara></entry>\r
426                                 <entry align="left" valign="top"><simpara>asset.call_number.record</simpara></entry>\r
427                                 <entry align="left" valign="top"><simpara>biblio.record_entry.id</simpara></entry>\r
428                                 </row>\r
429                                 <row>\r
430                                 <entry align="left" valign="top"><simpara>biblio.record_entry</simpara></entry>\r
431                                 <entry align="left" valign="top"><simpara>biblio.record_entry.id</simpara></entry>\r
432                                 <entry align="left" valign="top"><simpara></simpara></entry>\r
433                                 <entry align="left" valign="top"><simpara></simpara></entry>\r
434                                 </row>\r
435                                 </tbody>\r
436                                 </tgroup>\r
437                                 </table>\r
438                         </simplesect>\r
439                         <simplesect id="_check_constraints">\r
440                                 <title>Check constraints</title>\r
441                                 <simpara>PostgreSQL enables you to define rules to ensure that the value to be inserted\r
442                                 or updated meets certain conditions. For example, you can ensure that an\r
443                                 incoming integer value is within a specific range, or that a ZIP code matches a\r
444                                 particular pattern.</simpara>\r
445                         </simplesect>\r
446                 </simplesect>\r
447                 <simplesect id="_deconstructing_a_table_definition_statement">\r
448                         <title>Deconstructing a table definition statement</title>\r
449                         <simpara>The <literal>actor.org_address</literal> table is a simple table in the Evergreen schema that\r
450                         we can use as a concrete example of many of the properties of databases that\r
451                         we have discussed so far.</simpara>\r
452                         <programlisting language="sql" linenumbering="unnumbered">CREATE TABLE actor.org_address (\r
453                           id            SERIAL  PRIMARY KEY,      <co id="sqlCO1-1"/>\r
454                           valid         BOOL    NOT NULL DEFAULT TRUE, <co id="sqlCO1-2"/>\r
455                           address_type  TEXT    NOT NULL DEFAULT 'MAILING', <co id="sqlCO1-3"/>\r
456                           org_unit      INT     NOT NULL REFERENCES actor.org_unit (id)  <co id="sqlCO1-4"/>\r
457                                                   DEFERRABLE INITIALLY DEFERRED,\r
458                           street1       TEXT    NOT NULL,\r
459                           street2       TEXT, <co id="sqlCO1-5"/>\r
460                           city          TEXT    NOT NULL,\r
461                           county        TEXT,\r
462                           state         TEXT    NOT NULL,\r
463                           country       TEXT    NOT NULL,\r
464                           post_code     TEXT    NOT NULL\r
465                         );</programlisting>\r
466                         <calloutlist>\r
467                         <callout arearefs="sqlCO1-1">\r
468                         <simpara>\r
469                         The column named <literal>id</literal> is defined with a special data type of <literal>SERIAL</literal>; if\r
470                         given no value when a row is inserted into a table, the database automatically\r
471                         generates the next sequential integer value for the column. <literal>SERIAL</literal> is a\r
472                         popular data type for a primary key because it is guaranteed to be unique - and\r
473                         indeed, the constraint for this column identifies it as the <literal>PRIMARY KEY</literal>.\r
474                         </simpara>\r
475                         </callout>\r
476                         <callout arearefs="sqlCO1-2">\r
477                         <simpara>\r
478                         The data type <literal>BOOL</literal> defines a boolean value: <literal>TRUE</literal> or <literal>FALSE</literal> are the only\r
479                         acceptable values for the column. The constraint <literal>NOT NULL</literal> instructs the\r
480                         database to prevent the column from ever containing a NULL value. The column\r
481                         property <literal>DEFAULT TRUE</literal> instructs the database to automatically set the value\r
482                         of the column to <literal>TRUE</literal> if no value is provided.\r
483                         </simpara>\r
484                         </callout>\r
485                         <callout arearefs="sqlCO1-3">\r
486                         <simpara>\r
487                         The data type <literal>TEXT</literal> defines a text column of practically unlimited length.\r
488                         As with the previous column, there is a <literal>NOT NULL</literal> constraint, and a default\r
489                         value of <literal>'MAILING'</literal> will result if no other value is supplied.\r
490                         </simpara>\r
491                         </callout>\r
492                         <callout arearefs="sqlCO1-4">\r
493                         <simpara>\r
494                         The <literal>REFERENCES actor.org_unit (id)</literal> clause indicates that this column has a\r
495                         foreign key relationship to the <literal>actor.org_unit</literal> table, and that the value of\r
496                         this column in every row in this table must have a corresponding value in the\r
497                         <literal>id</literal> column in the referenced table (<literal>actor.org_unit</literal>).\r
498                         </simpara>\r
499                         </callout>\r
500                         <callout arearefs="sqlCO1-5">\r
501                         <simpara>\r
502                         The column named <literal>street2</literal> demonstrates that not all columns have constraints\r
503                         beyond data type. In this case, the column is allowed to be NULL or to contain a\r
504                         <literal>TEXT</literal> value.\r
505                         </simpara>\r
506                         </callout>\r
507                         </calloutlist>\r
508                 </simplesect>\r
509                 <simplesect id="_displaying_a_table_definition_using_literal_psql_literal">\r
510                         <title>Displaying a table definition using <literal>psql</literal></title>\r
511                         <simpara>The <literal>psql</literal> command-line interface is the preferred method for accessing\r
512                         PostgreSQL databases. It offers features like tab-completion, readline support\r
513                         for recalling previous commands, flexible input and output formats, and\r
514                         is accessible via a standard SSH session.</simpara>\r
515                         <simpara>If you press the <literal>Tab</literal> key once after typing one or more characters of the\r
516                         database object name, <literal>psql</literal> automatically completes the name if there are no\r
517                         other matches. If there are other matches for your current input, nothing\r
518                         happens until you press the <literal>Tab</literal> key a second time, at which point <literal>psql</literal>\r
519                         displays all of the matches for your current input.</simpara>\r
520                         <simpara>To display the definition of a database object such as a table, issue the\r
521                         command <literal>\d _object-name_</literal>. For example, to display the definition of the\r
522                         actor.usr_note table:</simpara>\r
523                         <programlisting language="sh" linenumbering="unnumbered">$ psql evergreen <co id="sqlCO2-1"/>\r
524                         psql (8.4.1)\r
525                         Type "help" for help.\r
526 \r
527                         evergreen=# \d actor.usr_note  <co id="sqlCO2-2"/>\r
528                                                                 Table "actor.usr_note"\r
529                            Column    |           Type           |                          Modifiers\r
530                         -------------+--------------------------+-------------------------------------------------------------\r
531                          id          | bigint                   | not null default nextval('actor.usr_note_id_seq'::regclass)\r
532                          usr         | bigint                   | not null\r
533                          creator     | bigint                   | not null\r
534                          create_date | timestamp with time zone | default now()\r
535                          pub         | boolean                  | not null default false\r
536                          title       | text                     | not null\r
537                          value       | text                     | not null\r
538                         Indexes:\r
539                             "usr_note_pkey" PRIMARY KEY, btree (id)\r
540                             "actor_usr_note_creator_idx" btree (creator)\r
541                             "actor_usr_note_usr_idx" btree (usr)\r
542                         Foreign-key constraints:\r
543                             "usr_note_creator_fkey" FOREIGN KEY (creator) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
544                             "usr_note_usr_fkey" FOREIGN KEY (usr) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
545 \r
546                         evergreen=# \q    <co id="sqlCO2-3"/>\r
547                         $</programlisting>\r
548                         <calloutlist>\r
549                         <callout arearefs="sqlCO2-1">\r
550                         <simpara>\r
551                         This is the most basic connection to a PostgreSQL database. You can use a\r
552                         number of other flags to specify user name, hostname, port, and other options.\r
553                         </simpara>\r
554                         </callout>\r
555                         <callout arearefs="sqlCO2-2">\r
556                         <simpara>\r
557                         The <literal>\d</literal> command displays the definition of a database object.\r
558                         </simpara>\r
559                         </callout>\r
560                         <callout arearefs="sqlCO2-3">\r
561                         <simpara>\r
562                         The <literal>\q</literal> command quits the <literal>psql</literal> session and returns you to the shell prompt.\r
563                         </simpara>\r
564                         </callout>\r
565                         </calloutlist>\r
566                 </simplesect>\r
567         </section>\r
568         <section id="basic_sql_queries">\r
569                 <title>Basic SQL queries</title>\r
570                 <simplesect id="_the_select_statement">\r
571                         <title>The SELECT statement</title>\r
572                         <simpara>The SELECT statement is the basic tool for retrieving information from a\r
573                         database. The syntax for most SELECT statements is:</simpara>\r
574                         <blockquote>\r
575                         <literallayout><literal>SELECT</literal> [<emphasis>columns(s)</emphasis>]\r
576                           <literal>FROM</literal> [<emphasis>table(s)</emphasis>]\r
577                           [<literal>WHERE</literal> <emphasis>condition(s)</emphasis>]\r
578                           [<literal>GROUP BY</literal> <emphasis>columns(s)</emphasis>]\r
579                           [<literal>HAVING</literal> <emphasis>grouping-condition(s)</emphasis>]\r
580                           [<literal>ORDER BY</literal> <emphasis>column(s)</emphasis>]\r
581                           [<literal>LIMIT</literal> <emphasis>maximum-results</emphasis>]\r
582                           [<literal>OFFSET</literal> <emphasis>start-at-result-#</emphasis>]\r
583                         ;</literallayout>\r
584                         </blockquote>\r
585                         <simpara>For example, to select all of the columns for each row in the\r
586                         <literal>actor.usr_address</literal> table, issue the following query:</simpara>\r
587                         <programlisting language="sql" linenumbering="unnumbered">SELECT *\r
588                           FROM actor.usr_address\r
589                         ;</programlisting>\r
590                 </simplesect>\r
591                 <simplesect id="_selecting_particular_columns_from_a_table">\r
592                         <title>Selecting particular columns from a table</title>\r
593                         <simpara><literal>SELECT *</literal> returns all columns from all of the tables included in your query.\r
594                         However, quite often you will want to return only a subset of the possible\r
595                         columns. You can retrieve specific columns by listing the names of the columns\r
596                         you want after the <literal>SELECT</literal> keyword. Separate each column name with a comma.</simpara>\r
597                         <simpara>For example, to select just the city, county, and state from the\r
598                         actor.usr_address table, issue the following query:</simpara>\r
599                         <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
600                           FROM actor.usr_address\r
601                         ;</programlisting>\r
602                 </simplesect>\r
603                 <simplesect id="_sorting_results_with_the_order_by_clause">\r
604                         <title>Sorting results with the ORDER BY clause</title>\r
605                         <simpara>By default, a SELECT statement returns rows matching your query with no\r
606                         guarantee of any particular order in which they are returned. To force\r
607                         the rows to be returned in a particular order, use the ORDER BY clause\r
608                         to specify one or more columns to determine the sorting priority of the\r
609                         rows.</simpara>\r
610                         <simpara>For example, to sort the rows returned from your <literal>actor.usr_address</literal> query by\r
611                         city, with county and then zip code as the tie breakers, issue the\r
612                         following query:</simpara>\r
613                         <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
614                           FROM actor.usr_address\r
615                           ORDER BY city, county, post_code\r
616                         ;</programlisting>\r
617                 </simplesect>\r
618                 <simplesect id="_filtering_results_with_the_where_clause">\r
619                         <title>Filtering results with the WHERE clause</title>\r
620                         <simpara>Thus far, your results have been returning all of the rows in the table.\r
621                         Normally, however, you would want to restrict the rows that are returned to the\r
622                         subset of rows that match one or more conditions of your search. The <literal>WHERE</literal>\r
623                         clause enables you to specify a set of conditions that filter your query\r
624                         results. Each condition in the <literal>WHERE</literal> clause is an SQL expression that returns\r
625                         a boolean (true or false) value.</simpara>\r
626                         <simpara>For example, to restrict the results returned from your <literal>actor.usr_address</literal>\r
627                         query to only those rows containing a state value of <emphasis>Connecticut</emphasis>, issue the\r
628                         following query:</simpara>\r
629                         <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
630                           FROM actor.usr_address\r
631                           WHERE state = 'Connecticut'\r
632                           ORDER BY city, county, post_code\r
633                         ;</programlisting>\r
634                         <simpara>You can include more conditions in the <literal>WHERE</literal> clause with the <literal>OR</literal> and <literal>AND</literal>\r
635                         operators. For example, to further restrict the results returned from your\r
636                         <literal>actor.usr_address</literal> query to only those rows where the state column contains a\r
637                         value of <emphasis>Connecticut</emphasis> and the city column contains a value of <emphasis>Hartford</emphasis>,\r
638                         issue the following query:</simpara>\r
639                         <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
640                           FROM actor.usr_address\r
641                           WHERE state = 'Connecticut'\r
642                             AND city = 'Hartford'\r
643                           ORDER BY city, county, post_code\r
644                         ;</programlisting>\r
645                         <note><simpara>To return rows where the state is <emphasis>Connecticut</emphasis> and the city is <emphasis>Hartford</emphasis> or\r
646                         <emphasis>New Haven</emphasis>, you must use parentheses to explicitly group the city value\r
647                         conditions together, or else the database will evaluate the <literal>OR city = 'New\r
648                         Haven'</literal> clause entirely on its own and match all rows where the city column is\r
649                         <emphasis>New Haven</emphasis>, even though the state might not be <emphasis>Connecticut</emphasis>.</simpara></note>\r
650                         <formalpara><title>Trouble with OR</title><para>\r
651                         <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
652                           FROM actor.usr_address\r
653                           WHERE state = 'Connecticut'\r
654                             AND city = 'Hartford' OR city = 'New Haven'\r
655                           ORDER BY city, county, post_code\r
656                         ;\r
657 \r
658                         -- Can return unwanted rows because the OR is not grouped!</programlisting>\r
659                         </para></formalpara>\r
660                         <formalpara><title>Grouped OR&#8217;ed conditions</title><para>\r
661                         <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
662                           FROM actor.usr_address\r
663                           WHERE state = 'Connecticut'\r
664                             AND (city = 'Hartford' OR city = 'New Haven')\r
665                           ORDER BY city, county, post_code\r
666                         ;\r
667 \r
668                         -- The parentheses ensure that the OR is applied to the cities, and the\r
669                         -- state in either case must be 'Connecticut'</programlisting>\r
670                         </para></formalpara>\r
671                         <simplesect id="_comparison_operators">\r
672                                 <title>Comparison operators</title>\r
673                                 <simpara>Here is a partial list of comparison operators that are commonly used in\r
674                                 <literal>WHERE</literal> clauses:</simpara>\r
675                                 <simplesect id="_comparing_two_scalar_values">\r
676                                         <title>Comparing two scalar values</title>\r
677                                         <itemizedlist>\r
678                                         <listitem>\r
679                                         <simpara>\r
680                                         <literal>x = y</literal> (equal to)\r
681                                         </simpara>\r
682                                         </listitem>\r
683                                         <listitem>\r
684                                         <simpara>\r
685                                         <literal>x != y</literal> (not equal to)\r
686                                         </simpara>\r
687                                         </listitem>\r
688                                         <listitem>\r
689                                         <simpara>\r
690                                         <literal>x &lt; y</literal> (less than)\r
691                                         </simpara>\r
692                                         </listitem>\r
693                                         <listitem>\r
694                                         <simpara>\r
695                                         <literal>x &gt; y</literal> (greater than)\r
696                                         </simpara>\r
697                                         </listitem>\r
698                                         <listitem>\r
699                                         <simpara>\r
700                                         <literal>x LIKE y</literal> (TEXT value x matches a subset of TEXT y, where y is a string that\r
701                                         can contain <emphasis>%</emphasis> as a wildcard for 0 or more characters, and <emphasis>_</emphasis> as a wildcard\r
702                                         for a single character. For example, <literal>WHERE 'all you can eat fish and chips\r
703                                         and a big stick' LIKE '%fish%stick'</literal> would return TRUE)\r
704                                         </simpara>\r
705                                         </listitem>\r
706                                         <listitem>\r
707                                         <simpara>\r
708                                         <literal>x ILIKE y</literal> (like LIKE, but the comparison ignores upper-case / lower-case)\r
709                                         </simpara>\r
710                                         </listitem>\r
711                                         <listitem>\r
712                                         <simpara>\r
713                                         <literal>x IN y</literal> (x is in the list of values y, where y can be a list or a SELECT\r
714                                         statement that returns a list)\r
715                                         </simpara>\r
716                                         </listitem>\r
717                                         </itemizedlist>\r
718                                 </simplesect>\r
719                         </simplesect>\r
720                 </simplesect>\r
721                 <simplesect id="_null_values">\r
722                         <title>NULL values</title>\r
723                         <simpara>SQL databases have a special way of representing the value of a column that has\r
724                         no value: <literal>NULL</literal>. A <literal>NULL</literal> value is not equal to zero, and is not an empty\r
725                         string; it is equal to nothing, not even another <literal>NULL</literal>, because it has no value\r
726                         that can be compared.</simpara>\r
727                         <simpara>To return rows from a table where a given column is not <literal>NULL</literal>, use the\r
728                         <literal>IS NOT NULL</literal> comparison operator.</simpara>\r
729                         <formalpara><title>Retrieving rows where a column is not <literal>NULL</literal></title><para>\r
730                         <programlisting language="sql" linenumbering="unnumbered">SELECT id, first_given_name, family_name\r
731                           FROM actor.usr\r
732                           WHERE second_given_name IS NOT NULL\r
733                         ;</programlisting>\r
734                         </para></formalpara>\r
735                         <simpara>Similarly, to return rows from a table where a given column is <literal>NULL</literal>, use\r
736                         the <literal>IS NULL</literal> comparison operator.</simpara>\r
737                         <formalpara><title>Retrieving rows where a column is <literal>NULL</literal></title><para>\r
738                         <programlisting language="sql" linenumbering="unnumbered">SELECT id, first_given_name, second_given_name, family_name\r
739                           FROM actor.usr\r
740                           WHERE second_given_name IS NULL\r
741                         ;\r
742 \r
743                          id | first_given_name | second_given_name |  family_name\r
744                         ----+------------------+-------------------+----------------\r
745                           1 | Administrator    |                   | System Account\r
746                         (1 row)</programlisting>\r
747                         </para></formalpara>\r
748                         <simpara>Notice that the <literal>NULL</literal> value in the output is displayed as empty space,\r
749                         indistinguishable from an empty string; this is the default display method in\r
750                         <literal>psql</literal>. You can change the behaviour of <literal>psql</literal> using the <literal>pset</literal> command:</simpara>\r
751                         <formalpara><title>Changing the way <literal>NULL</literal> values are displayed in <literal>psql</literal></title><para>\r
752                         <programlisting language="sql" linenumbering="unnumbered">evergreen=# \pset null '(null)'\r
753                         Null display is '(null)'.\r
754 \r
755                         SELECT id, first_given_name, second_given_name, family_name\r
756                           FROM actor.usr\r
757                           WHERE second_given_name IS NULL\r
758                         ;\r
759 \r
760                          id | first_given_name | second_given_name |  family_name\r
761                         ----+------------------+-------------------+----------------\r
762                           1 | Administrator    | (null)            | System Account\r
763                         (1 row)</programlisting>\r
764                         </para></formalpara>\r
765                         <simpara>Database queries within programming languages such as Perl and C have\r
766                         special methods of checking for <literal>NULL</literal> values in returned results.</simpara>\r
767                 </simplesect>\r
768                 <simplesect id="_text_delimiter">\r
769                         <title>Text delimiter: '</title>\r
770                         <simpara>You might have noticed that we have been using the <literal>'</literal> character to delimit\r
771                         TEXT values and values such as dates and times that are TEXT values. Sometimes,\r
772                         however, your TEXT value itself contains a <literal>'</literal> character, such as the word\r
773                         <literal>you&#8217;re</literal>. To prevent the database from prematurely ending the TEXT value at the\r
774                         first <literal>'</literal> character and returning a syntax error, use another <literal>'</literal> character to\r
775                         escape the following <literal>'</literal> character.</simpara>\r
776                         <simpara>For example, to change the last name of a user in the <literal>actor.usr</literal> table to\r
777                         <literal>L&#8217;estat</literal>, issue the following SQL:</simpara>\r
778                         <formalpara><title>Escaping <literal>'</literal> in TEXT values</title><para>\r
779                         <programlisting language="sql" linenumbering="unnumbered">UPDATE actor.usr\r
780                           SET family_name = 'L''estat'\r
781                           WHERE profile IN (\r
782                             SELECT id\r
783                               FROM permission.grp_tree\r
784                               WHERE name = 'Vampire'\r
785                           )\r
786                                 ;</programlisting>\r
787                                 </para></formalpara>\r
788                                 <simpara>When you retrieve the row from the database, the value is displayed with just\r
789                                 a single <literal>'</literal> character:</simpara>\r
790                                 <programlisting language="sql" linenumbering="unnumbered">SELECT id, family_name\r
791                                   FROM actor.usr\r
792                                   WHERE family_name = 'L''estat'\r
793                                 ;\r
794 \r
795                                  id | family_name\r
796                                 ----+-------------\r
797                                   1 | L'estat\r
798                                 (1 row)</programlisting>\r
799                 </simplesect>\r
800                 <simplesect id="_grouping_and_eliminating_results_with_the_group_by_and_having_clauses">\r
801                         <title>Grouping and eliminating results with the GROUP BY and HAVING clauses</title>\r
802                         <simpara>The GROUP BY clause returns a unique set of results for the desired columns.\r
803                         This is most often used in conjunction with an aggregate function to present\r
804                         results for a range of values in a single query, rather than requiring you to\r
805                         issue one query per target value.</simpara>\r
806                         <formalpara><title>Returning unique results of a single column with <literal>GROUP BY</literal></title><para>\r
807                         <programlisting language="sql" linenumbering="unnumbered">SELECT grp\r
808                           FROM permission.grp_perm_map\r
809                           GROUP BY grp\r
810                           ORDER BY grp;\r
811 \r
812                          grp\r
813                         -----+\r
814                            1\r
815                            2\r
816                            3\r
817                            4\r
818                            5\r
819                            6\r
820                            7\r
821                           10\r
822                         (8 rows)</programlisting>\r
823                         </para></formalpara>\r
824                         <simpara>While <literal>GROUP BY</literal> can be useful for a single column, it is more often used\r
825                         to return the distinct results across multiple columns. For example, the\r
826                         following query shows us which groups have permissions at each depth in\r
827                         the library hierarchy:</simpara>\r
828                         <formalpara><title>Returning unique results of multiple columns with <literal>GROUP BY</literal></title><para>\r
829                         <programlisting language="sql" linenumbering="unnumbered">SELECT grp, depth\r
830                           FROM permission.grp_perm_map\r
831                           GROUP BY grp, depth\r
832                           ORDER BY depth, grp;\r
833 \r
834                          grp | depth\r
835                         -----+-------\r
836                            1 |     0\r
837                            2 |     0\r
838                            3 |     0\r
839                            4 |     0\r
840                            5 |     0\r
841                           10 |     0\r
842                            3 |     1\r
843                            4 |     1\r
844                            5 |     1\r
845                            6 |     1\r
846                            7 |     1\r
847                           10 |     1\r
848                            3 |     2\r
849                            4 |     2\r
850                           10 |     2\r
851                         (15 rows)</programlisting>\r
852                         </para></formalpara>\r
853                         <simpara>Extending this further, you can use the <literal>COUNT()</literal> aggregate function to\r
854                         also return the number of times each unique combination of <literal>grp</literal> and <literal>depth</literal>\r
855                         appears in the table. <emphasis>Yes, this is a sneak peek at the use of aggregate\r
856                         functions! Keeners.</emphasis></simpara>\r
857                         <formalpara><title>Counting unique column combinations with <literal>GROUP BY</literal></title><para>\r
858                         <programlisting language="sql" linenumbering="unnumbered">SELECT grp, depth, COUNT(grp)\r
859                           FROM permission.grp_perm_map\r
860                           GROUP BY grp, depth\r
861                           ORDER BY depth, grp;\r
862 \r
863                          grp | depth | count\r
864                         -----+-------+-------\r
865                            1 |     0 |     6\r
866                            2 |     0 |     2\r
867                            3 |     0 |    45\r
868                            4 |     0 |     3\r
869                            5 |     0 |     5\r
870                           10 |     0 |     1\r
871                            3 |     1 |     3\r
872                            4 |     1 |     4\r
873                            5 |     1 |     1\r
874                            6 |     1 |     9\r
875                            7 |     1 |     5\r
876                           10 |     1 |    10\r
877                            3 |     2 |    24\r
878                            4 |     2 |     8\r
879                           10 |     2 |     7\r
880                         (15 rows)</programlisting>\r
881                         </para></formalpara>\r
882                         <simpara>You can use the <literal>WHERE</literal> clause to restrict the returned results before grouping\r
883                         is applied to the results. The following query restricts the results to those\r
884                         rows that have a depth of 0.</simpara>\r
885                         <formalpara><title>Using the <literal>WHERE</literal> clause with <literal>GROUP BY</literal></title><para>\r
886                         <programlisting language="sql" linenumbering="unnumbered">SELECT grp, COUNT(grp)\r
887                           FROM permission.grp_perm_map\r
888                           WHERE depth = 0\r
889                           GROUP BY grp\r
890                           ORDER BY 2 DESC\r
891                         ;\r
892 \r
893                          grp | count\r
894                         -----+-------\r
895                            3 |    45\r
896                            1 |     6\r
897                            5 |     5\r
898                            4 |     3\r
899                            2 |     2\r
900                           10 |     1\r
901                         (6 rows)</programlisting>\r
902                         </para></formalpara>\r
903                         <simpara>To restrict results after grouping has been applied to the rows, use the\r
904                         <literal>HAVING</literal> clause; this is typically used to restrict results based on\r
905                         a comparison to the value returned by an aggregate function. For example,\r
906                         the following query restricts the returned rows to those that have more than\r
907                         5 occurrences of the same value for <literal>grp</literal> in the table.</simpara>\r
908                         <formalpara><title><literal>GROUP BY</literal> restricted by a <literal>HAVING</literal> clause</title><para>\r
909                         <programlisting language="sql" linenumbering="unnumbered">SELECT grp, COUNT(grp)\r
910                           FROM permission.grp_perm_map\r
911                           GROUP BY grp\r
912                           HAVING COUNT(grp) &gt; 5\r
913                         ;\r
914 \r
915                          grp | count\r
916                         -----+-------\r
917                            6 |     9\r
918                            4 |    15\r
919                            5 |     6\r
920                            1 |     6\r
921                            3 |    72\r
922                           10 |    18\r
923                         (6 rows)</programlisting>\r
924                         </para></formalpara>\r
925                 </simplesect>\r
926                 <simplesect id="_eliminating_duplicate_results_with_the_distinct_keyword">\r
927                         <title>Eliminating duplicate results with the DISTINCT keyword</title>\r
928                         <simpara><literal>GROUP BY</literal> is one way of eliminating duplicate results from the rows returned\r
929                         by your query. The purpose of the <literal>DISTINCT</literal> keyword is to remove duplicate\r
930                         rows from the results of your query. However, it works, and it is easy - so if\r
931                         you just want a quick list of the unique set of values for a column or set of\r
932                         columns, the <literal>DISTINCT</literal> keyword might be appropriate.</simpara>\r
933                         <simpara>On the other hand, if you are getting duplicate rows back when you don&#8217;t expect\r
934                         them, then applying the <literal>DISTINCT</literal> keyword might be a sign that you are\r
935                         papering over a real problem.</simpara>\r
936                         <formalpara><title>Returning unique results of multiple columns with <literal>DISTINCT</literal></title><para>\r
937                         <programlisting language="sql" linenumbering="unnumbered">SELECT DISTINCT grp, depth\r
938                           FROM permission.grp_perm_map\r
939                           ORDER BY depth, grp\r
940                         ;\r
941 \r
942                          grp | depth\r
943                         -----+-------\r
944                            1 |     0\r
945                            2 |     0\r
946                            3 |     0\r
947                            4 |     0\r
948                            5 |     0\r
949                           10 |     0\r
950                            3 |     1\r
951                            4 |     1\r
952                            5 |     1\r
953                            6 |     1\r
954                            7 |     1\r
955                           10 |     1\r
956                            3 |     2\r
957                            4 |     2\r
958                           10 |     2\r
959                         (15 rows)</programlisting>\r
960                         </para></formalpara>\r
961                 </simplesect>\r
962                 <simplesect id="_paging_through_results_with_the_limit_and_offset_clauses">\r
963                         <title>Paging through results with the LIMIT and OFFSET clauses</title>\r
964                         <simpara>The <literal>LIMIT</literal> clause restricts the total number of rows returned from your query\r
965                         and is useful if you just want to list a subset of a large number of rows. For\r
966                         example, in the following query we list the five most frequently used\r
967                         circulation modifiers:</simpara>\r
968                         <formalpara><title>Using the <literal>LIMIT</literal> clause to restrict results</title><para>\r
969                         <programlisting language="sql" linenumbering="unnumbered">SELECT circ_modifier, COUNT(circ_modifier)\r
970                           FROM asset.copy\r
971                           GROUP BY circ_modifier\r
972                           ORDER BY 2 DESC\r
973                           LIMIT 5\r
974                         ;\r
975 \r
976                          circ_modifier | count\r
977                         ---------------+--------\r
978                          CIRC          | 741995\r
979                          BOOK          | 636199\r
980                          SER           | 265906\r
981                          DOC           | 191598\r
982                          LAW MONO      | 126627\r
983                         (5 rows)</programlisting>\r
984                         </para></formalpara>\r
985                         <simpara>When you use the <literal>LIMIT</literal> clause to restrict the total number of rows returned\r
986                         by your query, you can also use the <literal>OFFSET</literal> clause to determine which subset\r
987                         of the rows will be returned. The use of the <literal>OFFSET</literal> clause assumes that\r
988                         you&#8217;ve used the <literal>ORDER BY</literal> clause to impose order on the results.</simpara>\r
989                         <simpara>In the following example, we use the <literal>OFFSET</literal> clause to get results 6 through\r
990                         10 from the same query that we prevously executed.</simpara>\r
991                         <formalpara><title>Using the <literal>OFFSET</literal> clause to return a specific subset of rows</title><para>\r
992                         <programlisting language="sql" linenumbering="unnumbered">SELECT circ_modifier, COUNT(circ_modifier)\r
993                           FROM asset.copy\r
994                           GROUP BY circ_modifier\r
995                           ORDER BY 2 DESC\r
996                           LIMIT 5\r
997                           OFFSET 5\r
998                         ;\r
999 \r
1000                          circ_modifier | count\r
1001                         ---------------+--------\r
1002                          LAW SERIAL    | 102758\r
1003                          DOCUMENTS     |  86215\r
1004                          BOOK_WEB      |  63786\r
1005                          MFORM SER     |  39917\r
1006                          REF           |  34380\r
1007                         (5 rows)</programlisting>\r
1008                         </para></formalpara>\r
1009                 </simplesect>\r
1010         </section>\r
1011         <section id="advanced_sql_queries">\r
1012                 <title>Advanced SQL queries</title>\r
1013                 <simplesect id="_transforming_column_values_with_functions">\r
1014                         <title>Transforming column values with functions</title>\r
1015                         <simpara>PostgreSQL includes many built-in functions for manipulating column data.\r
1016                         You can also create your own functions (and Evergreen does make use of\r
1017                         many custom functions). There are two types of functions used in\r
1018                         databases: scalar functions and aggregate functions.</simpara>\r
1019                         <simplesect id="_scalar_functions">\r
1020                                 <title>Scalar functions</title>\r
1021                                 <simpara>Scalar functions transform each value of the target column. If your query\r
1022                                 would return 50 values for a column in a given query, and you modify your\r
1023                                 query to apply a scalar function to the values returned for that column,\r
1024                                 it will still return 50 values. For example, the UPPER() function,\r
1025                                 used to convert text values to upper-case, modifies the results in the\r
1026                                 following set of queries:</simpara>\r
1027                                 <formalpara><title>Using the UPPER() scalar function to convert text values to upper-case</title><para>\r
1028                                 <programlisting language="sql" linenumbering="unnumbered">-- First, without the UPPER() function for comparison\r
1029                                 SELECT shortname, name\r
1030                                   FROM actor.org_unit\r
1031                                   WHERE id &lt; 4\r
1032                                 ;\r
1033 \r
1034                                  shortname |         name\r
1035                                 -----------+-----------------------\r
1036                                  CONS      | Example Consortium\r
1037                                  SYS1      | Example System 1\r
1038                                  SYS2      | Example System 2\r
1039                                 (3 rows)\r
1040 \r
1041                                 -- Now apply the UPPER() function to the name column\r
1042                                 SELECT shortname, UPPER(name)\r
1043                                   FROM actor.org_unit\r
1044                                   WHERE id &lt; 4\r
1045                                 ;\r
1046 \r
1047                                  shortname |       upper\r
1048                                 -----------+--------------------\r
1049                                  CONS      | EXAMPLE CONSORTIUM\r
1050                                  SYS1      | EXAMPLE SYSTEM 1\r
1051                                  SYS2      | EXAMPLE SYSTEM 2\r
1052                                 (3 rows)</programlisting>\r
1053                                 </para></formalpara>\r
1054                                 <simpara>There are so many scalar functions in PostgreSQL that we cannot cover them\r
1055                                 all here, but we can list some of the most commonly used functions:</simpara>\r
1056                                 <itemizedlist>\r
1057                                 <listitem>\r
1058                                 <simpara>\r
1059                                 || - concatenates two text values together\r
1060                                 </simpara>\r
1061                                 </listitem>\r
1062                                 <listitem>\r
1063                                 <simpara>\r
1064                                 COALESCE() - returns the first non-NULL value from the list of arguments\r
1065                                 </simpara>\r
1066                                 </listitem>\r
1067                                 <listitem>\r
1068                                 <simpara>\r
1069                                 LOWER() - returns a text value converted to lower-case\r
1070                                 </simpara>\r
1071                                 </listitem>\r
1072                                 <listitem>\r
1073                                 <simpara>\r
1074                                 REPLACE() - returns a text value after replacing all occurrences of a given text value with a different text value\r
1075                                 </simpara>\r
1076                                 </listitem>\r
1077                                 <listitem>\r
1078                                 <simpara>\r
1079                                 REGEXP_REPLACE() - returns a text value after being transformed by a regular expression\r
1080                                 </simpara>\r
1081                                 </listitem>\r
1082                                 <listitem>\r
1083                                 <simpara>\r
1084                                 UPPER() - returns a text value converted to upper-case\r
1085                                 </simpara>\r
1086                                 </listitem>\r
1087                                 </itemizedlist>\r
1088                                 <simpara>For a complete list of scalar functions, see\r
1089                                 <ulink url="http://www.postgresql.org/docs/8.3/interactive/functions.html">the PostgreSQL function documentation</ulink>.</simpara>\r
1090                         </simplesect>\r
1091                         <simplesect id="_aggregate_functions">\r
1092                                 <title>Aggregate functions</title>\r
1093                                 <simpara>Aggregate functions return a single value computed from the the complete set of\r
1094                                 values returned for the specified column.</simpara>\r
1095                                 <itemizedlist>\r
1096                                 <listitem>\r
1097                                 <simpara>\r
1098                                 AVG()\r
1099                                 </simpara>\r
1100                                 </listitem>\r
1101                                 <listitem>\r
1102                                 <simpara>\r
1103                                 COUNT()\r
1104                                 </simpara>\r
1105                                 </listitem>\r
1106                                 <listitem>\r
1107                                 <simpara>\r
1108                                 MAX()\r
1109                                 </simpara>\r
1110                                 </listitem>\r
1111                                 <listitem>\r
1112                                 <simpara>\r
1113                                 MIN()\r
1114                                 </simpara>\r
1115                                 </listitem>\r
1116                                 <listitem>\r
1117                                 <simpara>\r
1118                                 SUM()\r
1119                                 </simpara>\r
1120                                 </listitem>\r
1121                                 </itemizedlist>\r
1122                         </simplesect>\r
1123                 </simplesect>\r
1124                 <simplesect id="_sub_selects">\r
1125                         <title>Sub-selects</title>\r
1126                         <simpara>A sub-select is the technique of using the results of one query to feed\r
1127                         into another query. You can, for example, return a set of values from\r
1128                         one column in a SELECT statement to be used to satisfy the IN() condition\r
1129                         of another SELECT statement; or you could return the MAX() value of a\r
1130                         column in a SELECT statement to match the = condition of another SELECT\r
1131                         statement.</simpara>\r
1132                         <simpara>For example, in the following query we use a sub-select to restrict the copies\r
1133                         returned by the main SELECT statement to only those locations that have an\r
1134                         <literal>opac_visible</literal> value of <literal>TRUE</literal>:</simpara>\r
1135                         <formalpara><title>Sub-select example</title><para>\r
1136                         <programlisting language="sql" linenumbering="unnumbered">SELECT call_number\r
1137                           FROM asset.copy\r
1138                           WHERE deleted IS FALSE\r
1139                             AND location IN (\r
1140                             SELECT id\r
1141                               FROM asset.copy_location\r
1142                               WHERE opac_visible IS TRUE\r
1143                           )\r
1144                         ;</programlisting>\r
1145                         </para></formalpara>\r
1146                         <simpara>Sub-selects can be an approachable way to breaking down a problem that\r
1147                         requires matching values between different tables, and often result in\r
1148                         a clearly expressed solution to a problem. However, if you start writing\r
1149                         sub-selects within sub-selects, you should consider tackling the problem\r
1150                         with joins instead.</simpara>\r
1151                 </simplesect>\r
1152                 <simplesect id="_joins">\r
1153                         <title>Joins</title>\r
1154                         <simpara>Joins enable you to access the values from multiple tables in your query\r
1155                         results and comparison operators. For example, joins are what enable you to\r
1156                         relate a bibliographic record to a barcoded copy via the <literal>biblio.record_entry</literal>,\r
1157                         <literal>asset.call_number</literal>, and <literal>asset.copy</literal> tables. In this section, we discuss the\r
1158                         most common kind of join&#8212;the inner join&#8212;as well as the less common outer join\r
1159                         and some set operations which can compare and contrast the values returned by\r
1160                         separate queries.</simpara>\r
1161                         <simpara>When we talk about joins, we are going to talk about the left-hand table and\r
1162                         the right-hand table that participate in the join. Every join brings together\r
1163                         just two tables - but you can use an unlimited (for our purposes) number\r
1164                         of joins in a single SQL statement. Each time you use a join, you effectively\r
1165                         create a new table, so when you add a second join clause to a statement,\r
1166                         table 1 and table 2 (which were the left-hand table and the right-hand table\r
1167                         for the first join) now act as a merged left-hand table and the new table\r
1168                         in the second join clause is the right-hand table.</simpara>\r
1169                         <simpara>Clear as mud? Okay, let&#8217;s look at some examples.</simpara>\r
1170                         <simplesect id="_inner_joins">\r
1171                                 <title>Inner joins</title>\r
1172                                 <simpara>An inner join returns all of the columns from the left-hand table in the join\r
1173                                 with all of the columns from the right-hand table in the joins that match a\r
1174                                 condition in the ON clause. Typically, you use the <literal>=</literal> operator to match the\r
1175                                 foreign key of the left-hand table with the primary key of the right-hand\r
1176                                 table to follow the natural relationship between the tables.</simpara>\r
1177                                 <simpara>In the following example, we return all of columns from the <literal>actor.usr</literal> and\r
1178                                 <literal>actor.org_unit</literal> tables, joined on the relationship between the user&#8217;s home\r
1179                                 library and the library&#8217;s ID. Notice in the results that some columns, like\r
1180                                 <literal>id</literal> and <literal>mailing_address</literal>, appear twice; this is because both the <literal>actor.usr</literal>\r
1181                                 and <literal>actor.org_unit</literal> tables include columns with these names. This is also why\r
1182                                 we have to fully qualify the column names in our queries with the schema and\r
1183                                 table names.</simpara>\r
1184                                 <formalpara><title>A simple inner join</title><para>\r
1185                                 <programlisting language="sql" linenumbering="unnumbered">SELECT *\r
1186                                   FROM actor.usr\r
1187                                     INNER JOIN actor.org_unit ON actor.usr.home_ou = actor.org_unit.id\r
1188                                     WHERE actor.org_unit.shortname = 'CONS'\r
1189                                 ;\r
1190 \r
1191                                 -[ RECORD 1 ]------------------+---------------------------------\r
1192                                 id                             | 1\r
1193                                 card                           | 1\r
1194                                 profile                        | 1\r
1195                                 usrname                        | admin\r
1196                                 email                          |\r
1197                                 ...\r
1198                                 mailing_address                |\r
1199                                 billing_address                |\r
1200                                 home_ou                        | 1\r
1201                                 ...\r
1202                                 claims_never_checked_out_count | 0\r
1203                                 id                             | 1\r
1204                                 parent_ou                      |\r
1205                                 ou_type                        | 1\r
1206                                 ill_address                    | 1\r
1207                                 holds_address                  | 1\r
1208                                 mailing_address                | 1\r
1209                                 billing_address                | 1\r
1210                                 shortname                      | CONS\r
1211                                 name                           | Example Consortium\r
1212                                 email                          |\r
1213                                 phone                          |\r
1214                                 opac_visible                   | t\r
1215                                 fiscal_calendar                | 1</programlisting>\r
1216                                 </para></formalpara>\r
1217                                 <simpara>Of course, you do not have to return every column from the joined tables;\r
1218                                 you can (and should) continue to specify only the columns that you want to\r
1219                                 return. In the following example, we count the number of borrowers for\r
1220                                 every user profile in a given library by joining the <literal>permission.grp_tree</literal>\r
1221                                 table where profiles are defined against the <literal>actor.usr</literal> table, and then\r
1222                                 joining the <literal>actor.org_unit</literal> table to give us access to the user&#8217;s home\r
1223                                 library:</simpara>\r
1224                                 <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
1225                                 <programlisting language="sql" linenumbering="unnumbered">SELECT permission.grp_tree.name, actor.org_unit.name, COUNT(permission.grp_tree.name)\r
1226                                   FROM actor.usr\r
1227                                     INNER JOIN permission.grp_tree\r
1228                                       ON actor.usr.profile = permission.grp_tree.id\r
1229                                     INNER JOIN actor.org_unit\r
1230                                       ON actor.org_unit.id = actor.usr.home_ou\r
1231                                   WHERE actor.usr.deleted IS FALSE\r
1232                                   GROUP BY permission.grp_tree.name, actor.org_unit.name\r
1233                                   ORDER BY actor.org_unit.name, permission.grp_tree.name\r
1234                                 ;\r
1235 \r
1236                                  name  |        name        | count\r
1237                                 -------+--------------------+-------\r
1238                                  Users | Example Consortium |     1\r
1239                                 (1 row)</programlisting>\r
1240                                 </para></formalpara>\r
1241                         </simplesect>\r
1242                         <simplesect id="_aliases">\r
1243                                 <title>Aliases</title>\r
1244                                 <simpara>So far we have been fully-qualifying all of our table names and column names to\r
1245                                 prevent any confusion. This quickly gets tiring with lengthy qualified\r
1246                                 table names like <literal>permission.grp_tree</literal>, so the SQL syntax enables us to assign\r
1247                                 aliases to table names and column names. When you define an alias for a table\r
1248                                 name, you can access its column throughout the rest of the statement by simply\r
1249                                 appending the column name to the alias with a period; for example, if you assign\r
1250                                 the alias <literal>au</literal> to the <literal>actor.usr</literal> table, you can access the <literal>actor.usr.id</literal>\r
1251                                 column through the alias as <literal>au.id</literal>.</simpara>\r
1252                                 <simpara>The formal syntax for declaring an alias for a column is to follow the column\r
1253                                 name in the result columns clause with <literal>AS</literal> <emphasis>alias</emphasis>. To declare an alias for a table name,\r
1254                                 follow the table name in the FROM clause (including any JOIN statements) with\r
1255                                 <literal>AS</literal> <emphasis>alias</emphasis>. However, the <literal>AS</literal> keyword is optional for tables (and columns as\r
1256                                 of PostgreSQL 8.4), and in practice most SQL statements leave it out.  For\r
1257                                 example, we can write the previous INNER JOIN statement example using aliases\r
1258                                 instead of fully-qualified identifiers:</simpara>\r
1259                                 <formalpara><title>Borrower Count by Profile (using aliases)</title><para>\r
1260                                 <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
1261                                   FROM actor.usr au\r
1262                                     INNER JOIN permission.grp_tree pgt\r
1263                                       ON au.profile = pgt.id\r
1264                                     INNER JOIN actor.org_unit aou\r
1265                                       ON aou.id = au.home_ou\r
1266                                   WHERE au.deleted IS FALSE\r
1267                                   GROUP BY pgt.name, aou.name\r
1268                                   ORDER BY aou.name, pgt.name\r
1269                                 ;\r
1270 \r
1271                                  Profile |      Library       | Count\r
1272                                 ---------+--------------------+-------\r
1273                                  Users   | Example Consortium |     1\r
1274                                 (1 row)</programlisting>\r
1275                                 </para></formalpara>\r
1276                                 <simpara>A nice side effect of declaring an alias for your columns is that the alias\r
1277                                 is used as the column header in the results table. The previous version of\r
1278                                 the query, which didn&#8217;t use aliased column names, had two columns named\r
1279                                 <literal>name</literal>; this version of the query with aliases results in a clearer\r
1280                                 categorization.</simpara>\r
1281                         </simplesect>\r
1282                         <simplesect id="_outer_joins">\r
1283                                 <title>Outer joins</title>\r
1284                                 <simpara>An outer join returns all of the rows from one or both of the tables\r
1285                                 participating in the join.</simpara>\r
1286                                 <itemizedlist>\r
1287                                 <listitem>\r
1288                                 <simpara>\r
1289                                 For a LEFT OUTER JOIN, the join returns all of the rows from the left-hand\r
1290                                 table and the rows matching the join condition from the right-hand table, with\r
1291                                 NULL values for the rows with no match in the right-hand table.\r
1292                                 </simpara>\r
1293                                 </listitem>\r
1294                                 <listitem>\r
1295                                 <simpara>\r
1296                                 A RIGHT OUTER JOIN behaves in the same way as a LEFT OUTER JOIN, with the\r
1297                                 exception that all rows are returned from the right-hand table participating in\r
1298                                 the join.\r
1299                                 </simpara>\r
1300                                 </listitem>\r
1301                                 <listitem>\r
1302                                 <simpara>\r
1303                                 For a FULL OUTER JOIN, the join returns all the rows from both the left-hand\r
1304                                 and right-hand tables, with NULL values for the rows with no match in either\r
1305                                 the left-hand or right-hand table.\r
1306                                 </simpara>\r
1307                                 </listitem>\r
1308                                 </itemizedlist>\r
1309                                 <formalpara><title>Base tables for the OUTER JOIN examples</title><para>\r
1310                                 <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa;\r
1311 \r
1312                                  id | stuff\r
1313                                 ----+-------\r
1314                                   1 | one\r
1315                                   2 | two\r
1316                                   3 | three\r
1317                                   4 | four\r
1318                                   5 | five\r
1319                                 (5 rows)\r
1320 \r
1321                                 SELECT * FROM bbb;\r
1322 \r
1323                                  id | stuff |   foo\r
1324                                 ----+-------+----------\r
1325                                   1 | one   | oneone\r
1326                                   2 | two   | twotwo\r
1327                                   5 | five  | fivefive\r
1328                                   6 | six   | sixsix\r
1329                                 (4 rows)</programlisting>\r
1330                                 </para></formalpara>\r
1331                                 <formalpara><title>Example of a LEFT OUTER JOIN</title><para>\r
1332                                 <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
1333                                   LEFT OUTER JOIN bbb ON aaa.id = bbb.id\r
1334                                 ;\r
1335                                  id | stuff | id | stuff |   foo\r
1336                                 ----+-------+----+-------+----------\r
1337                                   1 | one   |  1 | one   | oneone\r
1338                                   2 | two   |  2 | two   | twotwo\r
1339                                   3 | three |    |       |\r
1340                                   4 | four  |    |       |\r
1341                                   5 | five  |  5 | five  | fivefive\r
1342                                 (5 rows)</programlisting>\r
1343                                 </para></formalpara>\r
1344                                 <formalpara><title>Example of a RIGHT OUTER JOIN</title><para>\r
1345                                 <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
1346                                   RIGHT OUTER JOIN bbb ON aaa.id = bbb.id\r
1347                                 ;\r
1348                                  id | stuff | id | stuff |   foo\r
1349                                 ----+-------+----+-------+----------\r
1350                                   1 | one   |  1 | one   | oneone\r
1351                                   2 | two   |  2 | two   | twotwo\r
1352                                   5 | five  |  5 | five  | fivefive\r
1353                                     |       |  6 | six   | sixsix\r
1354                                 (4 rows)</programlisting>\r
1355                                 </para></formalpara>\r
1356                                 <formalpara><title>Example of a FULL OUTER JOIN</title><para>\r
1357                                 <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
1358                                   FULL OUTER JOIN bbb ON aaa.id = bbb.id\r
1359                                 ;\r
1360                                  id | stuff | id | stuff |   foo\r
1361                                 ----+-------+----+-------+----------\r
1362                                   1 | one   |  1 | one   | oneone\r
1363                                   2 | two   |  2 | two   | twotwo\r
1364                                   3 | three |    |       |\r
1365                                   4 | four  |    |       |\r
1366                                   5 | five  |  5 | five  | fivefive\r
1367                                     |       |  6 | six   | sixsix\r
1368                                 (6 rows)</programlisting>\r
1369                                 </para></formalpara>\r
1370                         </simplesect>\r
1371                         <simplesect id="_self_joins">\r
1372                                 <title>Self joins</title>\r
1373                                 <simpara>It is possible to join a table to itself. You can, in fact you must, use\r
1374                                 aliases to disambiguate the references to the table.</simpara>\r
1375                         </simplesect>\r
1376                 </simplesect>\r
1377                 <simplesect id="_set_operations">\r
1378                         <title>Set operations</title>\r
1379                         <simpara>Relational databases are effectively just an efficient mechanism for\r
1380                         manipulating sets of values; they are implementations of set theory. There are\r
1381                         three operators for sets (tables) in which each set must have the same number\r
1382                         of columns with compatible data types: the union, intersection, and difference\r
1383                         operators.</simpara>\r
1384                         <formalpara><title>Base tables for the set operation examples</title><para>\r
1385                         <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa;\r
1386 \r
1387                          id | stuff\r
1388                         ----+-------\r
1389                           1 | one\r
1390                           2 | two\r
1391                           3 | three\r
1392                           4 | four\r
1393                           5 | five\r
1394                         (5 rows)\r
1395 \r
1396                         SELECT * FROM bbb;\r
1397 \r
1398                          id | stuff |   foo\r
1399                         ----+-------+----------\r
1400                           1 | one   | oneone\r
1401                           2 | two   | twotwo\r
1402                           5 | five  | fivefive\r
1403                           6 | six   | sixsix\r
1404                         (4 rows)</programlisting>\r
1405                         </para></formalpara>\r
1406                         <simplesect id="_union">\r
1407                                 <title>Union</title>\r
1408                                 <simpara>The <literal>UNION</literal> operator returns the distinct set of rows that are members of\r
1409                                 either or both of the left-hand and right-hand tables. The <literal>UNION</literal> operator\r
1410                                 does not return any duplicate rows. To return duplicate rows, use the\r
1411                                 <literal>UNION ALL</literal> operator.</simpara>\r
1412                                 <formalpara><title>Example of a UNION set operation</title><para>\r
1413                                 <programlisting language="sql" linenumbering="unnumbered">-- The parentheses are not required, but are intended to help\r
1414                                 -- illustrate the sets participating in the set operation\r
1415                                 (\r
1416                                   SELECT id, stuff\r
1417                                     FROM aaa\r
1418                                 )\r
1419                                 UNION\r
1420                                 (\r
1421                                   SELECT id, stuff\r
1422                                     FROM bbb\r
1423                                 )\r
1424                                 ORDER BY 1\r
1425                                 ;\r
1426 \r
1427                                  id | stuff\r
1428                                 ----+-------\r
1429                                   1 | one\r
1430                                   2 | two\r
1431                                   3 | three\r
1432                                   4 | four\r
1433                                   5 | five\r
1434                                   6 | six\r
1435                                 (6 rows)</programlisting>\r
1436                                 </para></formalpara>\r
1437                         </simplesect>\r
1438                         <simplesect id="_intersection">\r
1439                                 <title>Intersection</title>\r
1440                                 <simpara>The <literal>INTERSECT</literal> operator returns the distinct set of rows that are common to\r
1441                                 both the left-hand and right-hand tables. To return duplicate rows, use the\r
1442                                 <literal>INTERSECT ALL</literal> operator.</simpara>\r
1443                                 <formalpara><title>Example of an INTERSECT set operation</title><para>\r
1444                                 <programlisting language="sql" linenumbering="unnumbered">(\r
1445                                   SELECT id, stuff\r
1446                                     FROM aaa\r
1447                                 )\r
1448                                 INTERSECT\r
1449                                 (\r
1450                                   SELECT id, stuff\r
1451                                     FROM bbb\r
1452                                 )\r
1453                                 ORDER BY 1\r
1454                                 ;\r
1455 \r
1456                                  id | stuff\r
1457                                 ----+-------\r
1458                                   1 | one\r
1459                                   2 | two\r
1460                                   5 | five\r
1461                                 (3 rows)</programlisting>\r
1462                                 </para></formalpara>\r
1463                         </simplesect>\r
1464                         <simplesect id="_difference">\r
1465                                 <title>Difference</title>\r
1466                                 <simpara>The <literal>EXCEPT</literal> operator returns the rows in the left-hand table that do not\r
1467                                 exist in the right-hand table. You are effectively subtracting the common\r
1468                                 rows from the left-hand table.</simpara>\r
1469                                 <formalpara><title>Example of an EXCEPT set operation</title><para>\r
1470                                 <programlisting language="sql" linenumbering="unnumbered">(\r
1471                                   SELECT id, stuff\r
1472                                     FROM aaa\r
1473                                 )\r
1474                                 EXCEPT\r
1475                                 (\r
1476                                   SELECT id, stuff\r
1477                                     FROM bbb\r
1478                                 )\r
1479                                 ORDER BY 1\r
1480                                 ;\r
1481 \r
1482                                  id | stuff\r
1483                                 ----+-------\r
1484                                   3 | three\r
1485                                   4 | four\r
1486                                 (2 rows)\r
1487 \r
1488                                 -- Order matters: switch the left-hand and right-hand tables\r
1489                                 -- and you get a different result\r
1490                                 (\r
1491                                   SELECT id, stuff\r
1492                                     FROM bbb\r
1493                                 )\r
1494                                 EXCEPT\r
1495                                 (\r
1496                                   SELECT id, stuff\r
1497                                     FROM aaa\r
1498                                 )\r
1499                                 ORDER BY 1\r
1500                                 ;\r
1501 \r
1502                                  id | stuff\r
1503                                 ----+-------\r
1504                                   6 | six\r
1505                                 (1 row)</programlisting>\r
1506                                 </para></formalpara>\r
1507                         </simplesect>\r
1508                 </simplesect>\r
1509                 <simplesect id="_views">\r
1510                         <title>Views</title>\r
1511                         <simpara>A view is a persistent <literal>SELECT</literal> statement that acts like a read-only table.\r
1512                         To create a view, issue the <literal>CREATE VIEW</literal> statement, giving the view a name\r
1513                         and a <literal>SELECT</literal> statement on which the view is built.</simpara>\r
1514                         <simpara>The following example creates a view based on our borrower profile count:</simpara>\r
1515                         <formalpara><title>Creating a view</title><para>\r
1516                         <programlisting language="sql" linenumbering="unnumbered">CREATE VIEW actor.borrower_profile_count AS\r
1517                           SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
1518                             FROM actor.usr au\r
1519                               INNER JOIN permission.grp_tree pgt\r
1520                                 ON au.profile = pgt.id\r
1521                               INNER JOIN actor.org_unit aou\r
1522                                 ON aou.id = au.home_ou\r
1523                             WHERE au.deleted IS FALSE\r
1524                             GROUP BY pgt.name, aou.name\r
1525                             ORDER BY aou.name, pgt.name\r
1526                         ;</programlisting>\r
1527                         </para></formalpara>\r
1528                         <simpara>When you subsequently select results from the view, you can apply additional\r
1529                         <literal>WHERE</literal> clauses to filter the results, or <literal>ORDER BY</literal> clauses to change the\r
1530                         order of the returned rows. In the following examples, we issue a simple\r
1531                         <literal>SELECT *</literal> statement to show that the default results are returned in the\r
1532                         same order from the view as the equivalent SELECT statement would be returned.\r
1533                         Then we issue a <literal>SELECT</literal> statement with a <literal>WHERE</literal> clause to further filter the\r
1534                         results.</simpara>\r
1535                         <formalpara><title>Selecting results from a view</title><para>\r
1536                         <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM actor.borrower_profile_count;\r
1537 \r
1538                                   Profile           | Library                    | Count\r
1539                         ----------------------------+----------------------------+-------\r
1540                          Faculty                    | University Library         |   208\r
1541                          Graduate                   | University Library         |    16\r
1542                          Patrons                    | University Library         |    62\r
1543                         ...\r
1544 \r
1545                         -- You can still filter your results with WHERE clauses\r
1546                         SELECT *\r
1547                           FROM actor.borrower_profile_count\r
1548                           WHERE "Profile" = 'Faculty';\r
1549 \r
1550                          Profile | Library                    | Count\r
1551                         ---------+----------------------------+-------\r
1552                          Faculty | University Library         |   208\r
1553                          Faculty | College Library            |    64\r
1554                          Faculty | College Library 2          |   102\r
1555                          Faculty | University Library 2       |   776\r
1556                         (4 rows)</programlisting>\r
1557                         </para></formalpara>\r
1558                 </simplesect>\r
1559                 <simplesect id="_inheritance">\r
1560                         <title>Inheritance</title>\r
1561                         <simpara>PostgreSQL supports table inheritance: that is, a child table inherits its\r
1562                         base definition from a parent table, but can add additional columns to its\r
1563                         own definition. The data from any child tables is visible in queries against\r
1564                         the parent table.</simpara>\r
1565                         <simpara>Evergreen uses table inheritance in several areas:\r
1566                           * In the Vandelay MARC batch importer / exporter, Evergreen defines base\r
1567                         tables for generic queues and queued records for which authority record and\r
1568                         bibliographic record child tables\r
1569                           * Billable transactions are based on the <literal>money.billable_xact</literal> table;\r
1570                         child tables include <literal>action.circulation</literal> for circulation transactions\r
1571                         and <literal>money.grocery</literal> for general bills.\r
1572                           * Payments are based on the <literal>money.payment</literal> table; its child table is\r
1573                         <literal>money.bnm_payment</literal> (for brick-and-mortar payments), which in turn has child\r
1574                         tables of <literal>money.forgive_payment</literal>, <literal>money.work_payment</literal>, <literal>money.credit_payment</literal>,\r
1575                         <literal>money.goods_payment</literal>, and <literal>money.bnm_desk_payment</literal>. The\r
1576                         <literal>money.bnm_desk_payment</literal> table in turn has child tables of <literal>money.cash_payment</literal>,\r
1577                         <literal>money.check_payment</literal>, and <literal>money.credit_card_payment</literal>.\r
1578                           * Transits are based on the <literal>action.transit_copy</literal> table, which has a child\r
1579                         table of <literal>action.hold_transit_copy</literal> for transits initiated by holds.\r
1580                           * Generic acquisition line items are defined by the\r
1581                         <literal>acq.lineitem_attr_definition</literal> table, which in turn has a number of child\r
1582                         tables to define MARC attributes, generated attributes, user attributes, and\r
1583                         provider attributes.</simpara>\r
1584                 </simplesect>\r
1585         </section>\r
1586         <section id="understanding_query_performance_with_explain">\r
1587                 <title>Understanding query performance with EXPLAIN</title>\r
1588                 <simpara>Some queries run for a long, long time. This can be the result of a poorly\r
1589                 written query&#8212;a query with a join condition that joins every\r
1590                 row in the <literal>biblio.record_entry</literal> table with every row in the <literal>metabib.full_rec</literal>\r
1591                 view would consume a massive amount of memory and disk space and CPU time&#8212;or\r
1592                 a symptom of a schema that needs some additional indexes. PostgreSQL provides\r
1593                 the <literal>EXPLAIN</literal> tool to estimate how long it will take to run a given query and\r
1594                 show you the <emphasis>query plan</emphasis> (how it plans to retrieve the results from the\r
1595                 database).</simpara>\r
1596                 <simpara>To generate the query plan without actually running the statement, simply\r
1597                 prepend the <literal>EXPLAIN</literal> keyword to your query. In the following example, we\r
1598                 generate the query plan for the poorly written query that would join every\r
1599                 row in the <literal>biblio.record_entry</literal> table with every row in the <literal>metabib.full_rec</literal>\r
1600                 view:</simpara>\r
1601                 <formalpara><title>Query plan for a terrible query</title><para>\r
1602                 <programlisting language="sql" linenumbering="unnumbered">EXPLAIN SELECT *\r
1603                   FROM biblio.record_entry\r
1604                     FULL OUTER JOIN metabib.full_rec ON 1=1\r
1605                 ;\r
1606 \r
1607                                                    QUERY PLAN\r
1608                 -------------------------------------------------------------------------------//\r
1609                  Merge Full Join  (cost=0.00..4959156437783.60 rows=132415734100864 width=1379)\r
1610                    -&gt;  Seq Scan on record_entry  (cost=0.00..400634.16 rows=2013416 width=1292)\r
1611                    -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
1612                 (3 rows)</programlisting>\r
1613                 </para></formalpara>\r
1614                 <simpara>This query plan shows that the query would return 132415734100864 rows, and it\r
1615                 plans to accomplish what you asked for by sequentially scanning (<emphasis>Seq Scan</emphasis>)\r
1616                 every row in each of the tables participating in the join.</simpara>\r
1617                 <simpara>In the following example, we have realized our mistake in joining every row of\r
1618                 the left-hand table with every row in the right-hand table and take the saner\r
1619                 approach of using an <literal>INNER JOIN</literal> where the join condition is on the record ID.</simpara>\r
1620                 <formalpara><title>Query plan for a less terrible query</title><para>\r
1621                 <programlisting language="sql" linenumbering="unnumbered">EXPLAIN SELECT *\r
1622                   FROM biblio.record_entry bre\r
1623                     INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id;\r
1624                                                         QUERY PLAN\r
1625                 ----------------------------------------------------------------------------------------//\r
1626                  Hash Join  (cost=750229.86..5829273.98 rows=65766704 width=1379)\r
1627                    Hash Cond: (real_full_rec.record = bre.id)\r
1628                    -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
1629                    -&gt;  Hash  (cost=400634.16..400634.16 rows=2013416 width=1292)\r
1630                          -&gt;  Seq Scan on record_entry bre  (cost=0.00..400634.16 rows=2013416 width=1292)\r
1631                 (5 rows)</programlisting>\r
1632                 </para></formalpara>\r
1633                 <simpara>This time, we will return 65766704 rows - still way too many rows. We forgot\r
1634                 to include a <literal>WHERE</literal> clause to limit the results to something meaningful. In\r
1635                 the following example, we will limit the results to deleted records that were\r
1636                 modified in the last month.</simpara>\r
1637                 <formalpara><title>Query plan for a realistic query</title><para>\r
1638                 <programlisting language="sql" linenumbering="unnumbered">EXPLAIN SELECT *\r
1639                   FROM biblio.record_entry bre\r
1640                     INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
1641                   WHERE bre.deleted IS TRUE\r
1642                     AND DATE_TRUNC('MONTH', bre.edit_date) &gt;\r
1643                         DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
1644                 ;\r
1645 \r
1646                                                         QUERY PLAN\r
1647                 ----------------------------------------------------------------------------------------//\r
1648                  Hash Join  (cost=5058.86..2306218.81 rows=201669 width=1379)\r
1649                    Hash Cond: (real_full_rec.record = bre.id)\r
1650                    -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
1651                    -&gt;  Hash  (cost=4981.69..4981.69 rows=6174 width=1292)\r
1652                          -&gt;  Index Scan using biblio_record_entry_deleted on record_entry bre\r
1653                                  (cost=0.00..4981.69 rows=6174 width=1292)\r
1654                                Index Cond: (deleted = true)\r
1655                                Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date)\r
1656                                  &gt; date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
1657                 (7 rows)</programlisting>\r
1658                 </para></formalpara>\r
1659                 <simpara>We can see that the number of rows returned is now only 201669; that&#8217;s\r
1660                 something we can work with. Also, the overall cost of the query is 2306218,\r
1661                 compared to 4959156437783 in the original query. The <literal>Index Scan</literal> tells us\r
1662                 that the query planner will use the index that was defined on the <literal>deleted</literal>\r
1663                 column to avoid having to check every row in the <literal>biblio.record_entry</literal> table.</simpara>\r
1664                 <simpara>However, we are still running a sequential scan over the\r
1665                 <literal>metabib.real_full_rec</literal> table (the table on which the <literal>metabib.full_rec</literal>\r
1666                 view is based). Given that linking from the bibliographic records to the\r
1667                 flattened MARC subfields is a fairly common operation, we could create a\r
1668                 new index and see if that speeds up our query plan.</simpara>\r
1669                 <formalpara><title>Query plan with optimized access via a new index</title><para>\r
1670                 <programlisting language="sql" linenumbering="unnumbered">-- This index will take a long time to create on a large database\r
1671                 -- of bibliographic records\r
1672                 CREATE INDEX bib_record_idx ON metabib.real_full_rec (record);\r
1673 \r
1674                 EXPLAIN SELECT *\r
1675                   FROM biblio.record_entry bre\r
1676                     INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
1677                   WHERE bre.deleted IS TRUE\r
1678                     AND DATE_TRUNC('MONTH', bre.edit_date) &gt;\r
1679                         DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
1680                 ;\r
1681 \r
1682                                                         QUERY PLAN\r
1683                 ----------------------------------------------------------------------------------------//\r
1684                  Nested Loop  (cost=0.00..1558330.46 rows=201669 width=1379)\r
1685                    -&gt;  Index Scan using biblio_record_entry_deleted on record_entry bre\r
1686                            (cost=0.00..4981.69 rows=6174 width=1292)\r
1687                          Index Cond: (deleted = true)\r
1688                          Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date) &gt;\r
1689                            date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
1690                    -&gt;  Index Scan using bib_record_idx on real_full_rec\r
1691                           (cost=0.00..240.89 rows=850 width=87)\r
1692                          Index Cond: (real_full_rec.record = bre.id)\r
1693                 (6 rows)</programlisting>\r
1694                 </para></formalpara>\r
1695                 <simpara>We can see that the resulting number of rows is still the same (201669), but\r
1696                 the execution estimate has dropped to 1558330 because the query planner can\r
1697                 use the new index (<literal>bib_record_idx</literal>) rather than scanning the entire table.\r
1698                 Success!</simpara>\r
1699                 <note><simpara>While indexes can significantly speed up read access to tables for common\r
1700                 filtering conditions, every time a row is created or updated the corresponding\r
1701                 indexes also need to be maintained - which can decrease the performance of\r
1702                 writes to the database. Be careful to keep the balance of read performance\r
1703                 versus write performance in mind if you plan to create custom indexes in your\r
1704                 Evergreen database.</simpara></note>\r
1705         </section>\r
1706         <section id="inserting_updating_and_deleting_data">\r
1707                 <title>Inserting, updating, and deleting data</title>\r
1708                 <simplesect id="_inserting_data">\r
1709                         <title>Inserting data</title>\r
1710                         <simpara>To insert one or more rows into a table, use the INSERT statement to identify\r
1711                         the target table and list the columns in the table for which you are going to\r
1712                         provide values for each row. If you do not list one or more columns contained\r
1713                         in the table, the database will automatically supply a <literal>NULL</literal> value for those\r
1714                         columns. The values for each row follow the <literal>VALUES</literal> clause and are grouped in\r
1715                         parentheses and delimited by commas. Each row, in turn, is delimited by commas\r
1716                         (<emphasis>this multiple row syntax requires PostgreSQL 8.2 or higher</emphasis>).</simpara>\r
1717                         <simpara>For example, to insert two rows into the <literal>permission.usr_grp_map</literal> table:</simpara>\r
1718                         <formalpara><title>Inserting rows into the <literal>permission.usr_grp_map</literal> table</title><para>\r
1719                         <programlisting language="sql" linenumbering="unnumbered">INSERT INTO permission.usr_grp_map (usr, grp)\r
1720                           VALUES (2, 10), (2, 4)\r
1721                         ;</programlisting>\r
1722                         </para></formalpara>\r
1723                         <simpara>Of course, as with the rest of SQL, you can replace individual column values\r
1724                         with one or more use sub-selects:</simpara>\r
1725                         <formalpara><title>Inserting rows using sub-selects instead of integers</title><para>\r
1726                         <programlisting language="sql" linenumbering="unnumbered">INSERT INTO permission.usr_grp_map (usr, grp)\r
1727                           VALUES (\r
1728                             (SELECT id FROM actor.usr\r
1729                                WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
1730                             (SELECT id FROM permission.grp_tree\r
1731                                WHERE name = 'Local System Administrator')\r
1732                           ), (\r
1733                             (SELECT id FROM actor.usr\r
1734                                WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
1735                             (SELECT id FROM permission.grp_tree\r
1736                                WHERE name = 'Circulator')\r
1737                           )\r
1738                         ;</programlisting>\r
1739                         </para></formalpara>\r
1740                 </simplesect>\r
1741                 <simplesect id="_inserting_data_using_a_select_statement">\r
1742                         <title>Inserting data using a SELECT statement</title>\r
1743                         <simpara>Sometimes you want to insert a bulk set of data into a new table based on\r
1744                         a query result. Rather than a <literal>VALUES</literal> clause, you can use a <literal>SELECT</literal>\r
1745                         statement to insert one or more rows matching the column definitions. This\r
1746                         is a good time to point out that you can include explicit values, instead\r
1747                         of just column identifiers, in the return columns of the <literal>SELECT</literal> statement.\r
1748                         The explicit values are returned in every row of the result set.</simpara>\r
1749                         <simpara>In the following example, we insert 6 rows into the <literal>permission.usr_grp_map</literal>\r
1750                         table; each row will have a <literal>usr</literal> column value of 1, with varying values for\r
1751                         the <literal>grp</literal> column value based on the <literal>id</literal> column values returned from\r
1752                         <literal>permission.grp_tree</literal>:</simpara>\r
1753                         <formalpara><title>Inserting rows via a <literal>SELECT</literal> statement</title><para>\r
1754                         <programlisting language="sql" linenumbering="unnumbered">INSERT INTO permission.usr_grp_map (usr, grp)\r
1755                           SELECT 1, id\r
1756                             FROM permission.grp_tree\r
1757                             WHERE id &gt; 2\r
1758                         ;\r
1759 \r
1760                         INSERT 0 6</programlisting>\r
1761                         </para></formalpara>\r
1762                 </simplesect>\r
1763                 <simplesect id="_deleting_rows">\r
1764                         <title>Deleting rows</title>\r
1765                         <simpara>Deleting data from a table is normally fairly easy. To delete rows from a table,\r
1766                         issue a <literal>DELETE</literal> statement identifying the table from which you want to delete\r
1767                         rows and a <literal>WHERE</literal> clause identifying the row or rows that should be deleted.</simpara>\r
1768                         <simpara>In the following example, we delete all of the rows from the\r
1769                         <literal>permission.grp_perm_map</literal> table where the permission maps to\r
1770                         <literal>UPDATE_ORG_UNIT_CLOSING</literal> and the group is anything other than administrators:</simpara>\r
1771                         <formalpara><title>Deleting rows from a table</title><para>\r
1772                         <programlisting language="sql" linenumbering="unnumbered">DELETE FROM permission.grp_perm_map\r
1773                           WHERE grp IN (\r
1774                             SELECT id\r
1775                               FROM permission.grp_tree\r
1776                               WHERE name != 'Local System Administrator'\r
1777                           ) AND perm = (\r
1778                             SELECT id\r
1779                               FROM permission.perm_list\r
1780                                       WHERE code = 'UPDATE_ORG_UNIT_CLOSING'\r
1781                                   )\r
1782                         ;</programlisting>\r
1783                         </para></formalpara>\r
1784                         <note><simpara>There are two main reasons that a <literal>DELETE</literal> statement may not actually\r
1785                         delete rows from a table, even when the rows meet the conditional clause.</simpara></note>\r
1786                         <orderedlist numeration="arabic">\r
1787                         <listitem>\r
1788 \r
1789                         <simpara>\r
1790                         If the row contains a value that is the target of a relational constraint,\r
1791                         for example, if another table has a foreign key pointing at your target\r
1792                         table, you will be prevented from deleting a row with a value corresponding\r
1793                         to a row in the dependent table.\r
1794                         </simpara>\r
1795                         </listitem>\r
1796                         <listitem>\r
1797                         <simpara>\r
1798                         If the table has a rule that substitutes a different action for a <literal>DELETE</literal>\r
1799                         statement, the deletion will not take place. In Evergreen it is common for a\r
1800                         table to have a rule that substitutes the action of setting a <literal>deleted</literal> column\r
1801                         to <literal>TRUE</literal>. For example, if a book is discarded, deleting the row representing\r
1802                         the copy from the <literal>asset.copy</literal> table would severely affect circulation statistics,\r
1803                         bills, borrowing histories, and their corresponding tables in the database that\r
1804                         have foreign keys pointing at the <literal>asset.copy</literal> table (<literal>action.circulation</literal> and\r
1805                         <literal>money.billing</literal> and its children respectively). Instead, the <literal>deleted</literal> column\r
1806                         value is set to <literal>TRUE</literal> and Evergreen&#8217;s application logic skips over these rows\r
1807                         in most cases.\r
1808                         </simpara>\r
1809                         </listitem>\r
1810                         </orderedlist>\r
1811                 </simplesect>\r
1812                 <simplesect id="_updating_rows">\r
1813                         <title>Updating rows</title>\r
1814                         <simpara>To update rows in a table, issue an <literal>UPDATE</literal> statement identifying the table\r
1815                         you want to update, the column or columns that you want to set with their\r
1816                         respective new values, and (optionally) a <literal>WHERE</literal> clause identifying the row or\r
1817                         rows that should be updated.</simpara>\r
1818                         <simpara>Following is the syntax for the <literal>UPDATE</literal> statement:</simpara>\r
1819                         <blockquote>\r
1820                         <literallayout><literal>UPDATE</literal> [<emphasis>table-name</emphasis>]\r
1821                           <literal>SET</literal> [<emphasis>column</emphasis>] <literal>TO</literal> [<emphasis>new-value</emphasis>]\r
1822                           <literal>WHERE</literal> [<emphasis>condition</emphasis>]\r
1823                         ;</literallayout>\r
1824                         </blockquote>\r
1825                 </simplesect>\r
1826         </section>\r
1827         <section id="query_requests">\r
1828                 <title>Query requests</title>\r
1829                 <simpara>The following queries were requested by Bibliomation, but might be reusable\r
1830                 by other libraries.</simpara>\r
1831                 <simplesect id="_monthly_circulation_stats_by_collection_code_library">\r
1832                         <title>Monthly circulation stats by collection code / library</title>\r
1833                         <formalpara><title>Monthly Circulation Stats by Collection Code/Library</title><para>\r
1834                         <programlisting language="sql" linenumbering="unnumbered">SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", acl.name AS "Copy Location"\r
1835                           FROM asset.copy ac\r
1836                             INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
1837                             INNER JOIN action.circulation acirc ON acirc.target_copy = ac.id\r
1838                             INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
1839                           WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
1840                             AND acirc.desk_renewal IS FALSE\r
1841                             AND acirc.opac_renewal IS FALSE\r
1842                             AND acirc.phone_renewal IS FALSE\r
1843                           GROUP BY aou.name, acl.name\r
1844                           ORDER BY aou.name, acl.name, 1\r
1845                         ;</programlisting>\r
1846                         </para></formalpara>\r
1847                 </simplesect>\r
1848                 <simplesect id="_monthly_circulation_stats_by_borrower_stat_library">\r
1849                         <title>Monthly circulation stats by borrower stat / library</title>\r
1850                         <formalpara><title>Monthly Circulation Stats by Borrower Stat/Library</title><para>\r
1851                         <programlisting language="sql" linenumbering="unnumbered">SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", asceum.stat_cat_entry AS "Borrower Stat"\r
1852                           FROM action.circulation acirc\r
1853                             INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
1854                             INNER JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = acirc.usr\r
1855                             INNER JOIN actor.stat_cat astat ON asceum.stat_cat = astat.id\r
1856                           WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
1857                             AND astat.name = 'Preferred language'\r
1858                             AND acirc.desk_renewal IS FALSE\r
1859                             AND acirc.opac_renewal IS FALSE\r
1860                             AND acirc.phone_renewal IS FALSE\r
1861                           GROUP BY aou.name, asceum.stat_cat_entry\r
1862                           ORDER BY aou.name, asceum.stat_cat_entry, 1\r
1863                         ;</programlisting>\r
1864                         </para></formalpara>\r
1865                 </simplesect>\r
1866                 <simplesect id="_monthly_intralibrary_loan_stats_by_library">\r
1867                         <title>Monthly intralibrary loan stats by library</title>\r
1868                         <formalpara><title>Monthly Intralibrary Loan Stats by Library</title><para>\r
1869                         <programlisting language="sql" linenumbering="unnumbered">SELECT aou.name AS "Library", COUNT(acirc.id)\r
1870                           FROM action.circulation acirc\r
1871                             INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
1872                             INNER JOIN asset.copy ac ON acirc.target_copy = ac.id\r
1873                             INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
1874                           WHERE acirc.circ_lib != acn.owning_lib\r
1875                             AND DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
1876                             AND acirc.desk_renewal IS FALSE\r
1877                             AND acirc.opac_renewal IS FALSE\r
1878                             AND acirc.phone_renewal IS FALSE\r
1879                           GROUP by aou.name\r
1880                           ORDER BY aou.name, 2\r
1881                         ;</programlisting>\r
1882                         </para></formalpara>\r
1883                 </simplesect>\r
1884                 <simplesect id="_monthly_borrowers_added_by_profile_adult_child_etc_library">\r
1885                         <title>Monthly borrowers added by profile (adult, child, etc) / library</title>\r
1886                         <formalpara><title>Monthly Borrowers Added by Profile (Adult, Child, etc)/Library</title><para>\r
1887                         <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
1888                           FROM actor.usr au\r
1889                             INNER JOIN permission.grp_tree pgt\r
1890                               ON au.profile = pgt.id\r
1891                             INNER JOIN actor.org_unit aou\r
1892                               ON aou.id = au.home_ou\r
1893                           WHERE au.deleted IS FALSE\r
1894                             AND DATE_TRUNC('MONTH', au.create_date) = DATE_TRUNC('MONTH', NOW() - '3 months'::interval)\r
1895                           GROUP BY pgt.name, aou.name\r
1896                           ORDER BY aou.name, pgt.name\r
1897                         ;</programlisting>\r
1898                         </para></formalpara>\r
1899                 </simplesect>\r
1900                 <simplesect id="_borrower_count_by_profile_adult_child_etc_library">\r
1901                         <title>Borrower count by profile (adult, child, etc) / library</title>\r
1902                         <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
1903                         <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
1904                           FROM actor.usr au\r
1905                             INNER JOIN permission.grp_tree pgt\r
1906                               ON au.profile = pgt.id\r
1907                             INNER JOIN actor.org_unit aou\r
1908                               ON aou.id = au.home_ou\r
1909                           WHERE au.deleted IS FALSE\r
1910                           GROUP BY pgt.name, aou.name\r
1911                           ORDER BY aou.name, pgt.name\r
1912                         ;</programlisting>\r
1913                         </para></formalpara>\r
1914                 </simplesect>\r
1915                 <simplesect id="_monthly_items_added_by_collection_library">\r
1916                         <title>Monthly items added by collection / library</title>\r
1917                         <simpara>We define a "collection" as a shelving location in Evergreen.</simpara>\r
1918                         <formalpara><title>Monthly Items Added by Collection/Library</title><para>\r
1919                         <programlisting language="sql" linenumbering="unnumbered">SELECT aou.name AS "Library", acl.name, COUNT(ac.barcode)\r
1920                           FROM actor.org_unit aou\r
1921                             INNER JOIN asset.call_number acn ON acn.owning_lib = aou.id\r
1922                             INNER JOIN asset.copy ac ON ac.call_number = acn.id\r
1923                             INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
1924                           WHERE ac.deleted IS FALSE\r
1925                             AND acn.deleted IS FALSE\r
1926                             AND DATE_TRUNC('MONTH', ac.create_date) = DATE_TRUNC('MONTH', NOW() - '1 month'::interval)\r
1927                           GROUP BY aou.name, acl.name\r
1928                           ORDER BY aou.name, acl.name\r
1929                         ;</programlisting>\r
1930                         </para></formalpara>\r
1931                 </simplesect>\r
1932                 <simplesect id="_hold_purchase_alert_by_library">\r
1933                         <title>Hold purchase alert by library</title>\r
1934                         <simpara>in the following set of queries, we bring together the active title, volume,\r
1935                         and copy holds and display those that have more than a certain number of holds\r
1936                         per title. The goal is to UNION ALL the three queries, then group by the\r
1937                         bibliographic record ID and display the title / author information for those\r
1938                         records that have more than a given threshold of holds.</simpara>\r
1939                         <formalpara><title>Hold Purchase Alert by Library</title><para>\r
1940                         <programlisting language="sql" linenumbering="unnumbered">-- Title holds\r
1941                         SELECT all_holds.bib_id, aou.name, rmsr.title, rmsr.author, COUNT(all_holds.bib_id)\r
1942                           FROM\r
1943                             (\r
1944                               (\r
1945                                 SELECT target, request_lib\r
1946                                   FROM action.hold_request\r
1947                                   WHERE hold_type = 'T'\r
1948                                     AND fulfillment_time IS NULL\r
1949                                     AND cancel_time IS NULL\r
1950                               )\r
1951                               UNION ALL\r
1952                               -- Volume holds\r
1953                               (\r
1954                                 SELECT bre.id, request_lib\r
1955                                   FROM action.hold_request ahr\r
1956                                     INNER JOIN asset.call_number acn ON ahr.target = acn.id\r
1957                                     INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
1958                                   WHERE ahr.hold_type = 'V'\r
1959                                     AND ahr.fulfillment_time IS NULL\r
1960                                     AND ahr.cancel_time IS NULL\r
1961                               )\r
1962                               UNION ALL\r
1963                               -- Copy holds\r
1964                               (\r
1965                                 SELECT bre.id, request_lib\r
1966                                   FROM action.hold_request ahr\r
1967                                     INNER JOIN asset.copy ac ON ahr.target = ac.id\r
1968                                     INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
1969                                     INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
1970                                   WHERE ahr.hold_type = 'C'\r
1971                                     AND ahr.fulfillment_time IS NULL\r
1972                                     AND ahr.cancel_time IS NULL\r
1973                               )\r
1974                             ) AS all_holds(bib_id, request_lib)\r
1975                           INNER JOIN reporter.materialized_simple_record rmsr\r
1976                           INNER JOIN actor.org_unit aou ON aou.id = all_holds.request_lib\r
1977                             ON rmsr.id = all_holds.bib_id\r
1978                           GROUP BY all_holds.bib_id, aou.name, rmsr.id, rmsr.title, rmsr.author\r
1979                           HAVING COUNT(all_holds.bib_id) &gt; 2\r
1980                           ORDER BY aou.name\r
1981                         ;</programlisting>\r
1982                         </para></formalpara>\r
1983                 </simplesect>\r
1984                 <simplesect id="_update_borrower_records_with_a_different_home_library">\r
1985                         <title>Update borrower records with a different home library</title>\r
1986                         <simpara>In this example, the library has opened a new branch in a growing area,\r
1987                         and wants to reassign the home library for the patrons in the vicinity of\r
1988                         the new branch to the new branch. To accomplish this, we create a staging table\r
1989                         that holds a set of city names and the corresponding branch shortname for the home\r
1990                         library for each city.</simpara>\r
1991                         <simpara>Then we issue an <literal>UPDATE</literal> statement to set the home library for patrons with a\r
1992                         physical address with a city that matches the city names in our staging table.</simpara>\r
1993                         <formalpara><title>Update borrower records with a different home library</title><para>\r
1994                         <programlisting language="sql" linenumbering="unnumbered">CREATE SCHEMA staging;\r
1995                         CREATE TABLE staging.city_home_ou_map (city TEXT, ou_shortname TEXT,\r
1996                           FOREIGN KEY (ou_shortname) REFERENCES actor.org_unit (shortname));\r
1997                         INSERT INTO staging.city_home_ou_map (city, ou_shortname)\r
1998                           VALUES ('Southbury', 'BR1'), ('Middlebury', 'BR2'), ('Hartford', 'BR3');\r
1999                         BEGIN;\r
2000 \r
2001                         UPDATE actor.usr au SET home_ou = COALESCE(\r
2002                           (\r
2003                             SELECT aou.id\r
2004                               FROM actor.org_unit aou\r
2005                                 INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
2006                                 INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
2007                               WHERE au.id = aua.usr\r
2008                               GROUP BY aou.id\r
2009                           ), home_ou)\r
2010                         WHERE (\r
2011                           SELECT aou.id\r
2012                             FROM actor.org_unit aou\r
2013                               INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
2014                               INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
2015                             WHERE au.id = aua.usr\r
2016                             GROUP BY aou.id\r
2017                         ) IS NOT NULL;</programlisting>\r
2018                         </para></formalpara>\r
2019                 </simplesect>\r
2020         </section>\r
2021         \r
2022 </chapter>\r