Introduction to SQL for Evergreen administrators

This tutorial is intended primarily for people working with Evergreen who want to dig into the database that powers Evergreen, and who have little familiarity with SQL or the PostgreSQL database in particular. If you are already comfortable with SQL, this tutorial will help point you to the implementation quirks and features of PostgreSQL. If you are already familiar with both SQL and PostgreSQL, this tutorial will help you navigate the Evergreen schema.

Over time, the SQL database has become the standard method of storing, retrieving, and processing raw data for applications. Ranging from embedded databases such as SQLite and Apache Derby, to enterprise databases such as Oracle and IBM DB2, any SQL database offers basic advantages to application developers such as standard interfaces (Structured Query Language (SQL), Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), Perl Database Independent Interface (DBI)), a standard conceptual model of data (tables, fields, relationships, constraints, etc), performance in storing and retrieving data, concurrent access, etc.

Evergreen is built on PostgreSQL, an open source SQL database that began as POSTGRES at the University of California at Berkeley in 1986 as a research project led by Professor Michael Stonebraker. A SQL interface was added to a fork of the original POSTGRES Berkelely code in 1994, and in 1996 the project was renamed PostgreSQL.

The table is the cornerstone of a SQL database. Conceptually, a database table is similar to a single sheet in a spreadsheet: every table has one or more columns, with each row in the table containing values for each column. Each column in a table defines an attribute corresponding to a particular data type.

We’ll insert a row into a table, then display the resulting contents. Don’t worry if the INSERT statement is completely unfamiliar, we’ll talk more about the syntax of the insert statement later.

PostgreSQL supports table inheritance, which lets you define tables that inherit the column definitions of a given parent table. A search of the data in the parent table includes the data in the child tables. Evergreen uses table inheritance: for example, the action.circulation table is a child of the money.billable_xact table, and the money.*_payment tables all inherit from the money.payment parent table.

PostgreSQL, like most SQL databases, supports the use of schema names to group collections of tables and other database objects together. You might think of schemas as namespaces if you’re a programmer; or you might think of the schema / table / column relationship like the area code / exchange / local number structure of a telephone number.

The default schema name in PostgreSQL is public, so if you do not specify a schema name when creating or accessing a database object, PostgreSQL will use the public schema. As a result, you might not find the object that you’re looking for if you don’t use the appropriate schema.

Evergreen uses schemas to organize all of its tables with mostly intuitive, if short, schema names. Here’s the current (as of 2010-01-03) list of schemas used by Evergreen:

Each column definition consists of:

Although PostgreSQL supports dozens of data types, Evergreen makes our life easier by only using a handful.

Full details about these data types are available from the data types section of the PostgreSQL manual.

A column definition may include the constraint NOT NULL to prevent NULL values. In PostgreSQL, a NULL value is not the equivalent of zero or false or an empty string; it is an explicit non-value with special properties. We’ll talk more about how to work with NULL values when we get to queries.

Every table can have at most one primary key. A primary key consists of one or more columns which together uniquely identify each row in a table. If you attempt to insert a row into a table that would create a duplicate or NULL primary key entry, the database rejects the row and returns an error.

Natural primary keys are drawn from the intrinsic properties of the data being modelled. For example, some potential natural primary keys for a table that contains people would be:

To avoid problems with natural keys, many applications instead define surrogate primary keys. A surrogate primary keys is a column with an autoincrementing integer value added to a table definition that ensures uniqueness.

Evergreen uses surrogate keys (a column named id with a SERIAL data type) for most of its tables.

Every table can contain zero or more foreign keys: one or more columns that refer to the primary key of another table.

For example, let’s consider Evergreen’s modelling of the basic relationship between copies, call numbers, and bibliographic records. Bibliographic records contained in the biblio.record_entry table can have call numbers attached to them. Call numbers are contained in the asset.call_number table, and they can have copies attached to them. Copies are contained in the asset.copy table.

PostgreSQL enables you to define rules to ensure that the value to be inserted or updated meets certain conditions. For example, you can ensure that an incoming integer value is within a specific range, or that a ZIP code matches a particular pattern.

The actor.org_address table is a simple table in the Evergreen schema that we can use as a concrete example of many of the properties of databases that we have discussed so far.

The psql command-line interface is the preferred method for accessing PostgreSQL databases. It offers features like tab-completion, readline support for recalling previous commands, flexible input and output formats, and is accessible via a standard SSH session.

If you press the Tab key once after typing one or more characters of the database object name, psql automatically completes the name if there are no other matches. If there are other matches for your current input, nothing happens until you press the Tab key a second time, at which point psql displays all of the matches for your current input.

To display the definition of a database object such as a table, issue the command \d _object-name_. For example, to display the definition of the actor.usr_note table:

The SELECT statement is the basic tool for retrieving information from a database. The syntax for most SELECT statements is:

For example, to select all of the columns for each row in the actor.usr_address table, issue the following query:

SELECT * returns all columns from all of the tables included in your query. However, quite often you will want to return only a subset of the possible columns. You can retrieve specific columns by listing the names of the columns you want after the SELECT keyword. Separate each column name with a comma.

For example, to select just the city, county, and state from the actor.usr_address table, issue the following query:

By default, a SELECT statement returns rows matching your query with no guarantee of any particular order in which they are returned. To force the rows to be returned in a particular order, use the ORDER BY clause to specify one or more columns to determine the sorting priority of the rows.

For example, to sort the rows returned from your actor.usr_address query by city, with county and then zip code as the tie breakers, issue the following query:

Thus far, your results have been returning all of the rows in the table. Normally, however, you would want to restrict the rows that are returned to the subset of rows that match one or more conditions of your search. The WHERE clause enables you to specify a set of conditions that filter your query results. Each condition in the WHERE clause is an SQL expression that returns a boolean (true or false) value.

For example, to restrict the results returned from your actor.usr_address query to only those rows containing a state value of Connecticut, issue the following query:

You can include more conditions in the WHERE clause with the OR and AND operators. For example, to further restrict the results returned from your actor.usr_address query to only those rows where the state column contains a value of Connecticut and the city column contains a value of Hartford, issue the following query:

Here is a partial list of comparison operators that are commonly used in WHERE clauses:

SQL databases have a special way of representing the value of a column that has no value: NULL. A NULL value is not equal to zero, and is not an empty string; it is equal to nothing, not even another NULL, because it has no value that can be compared.

To return rows from a table where a given column is not NULL, use the IS NOT NULL comparison operator.

Similarly, to return rows from a table where a given column is NULL, use the IS NULL comparison operator.

Notice that the NULL value in the output is displayed as empty space, indistinguishable from an empty string; this is the default display method in psql. You can change the behaviour of psql using the pset command:

Database queries within programming languages such as Perl and C have special methods of checking for NULL values in returned results.

You might have noticed that we have been using the ' character to delimit TEXT values and values such as dates and times that are TEXT values. Sometimes, however, your TEXT value itself contains a ' character, such as the word you’re. To prevent the database from prematurely ending the TEXT value at the first ' character and returning a syntax error, use another ' character to escape the following ' character.

For example, to change the last name of a user in the actor.usr table to L’estat, issue the following SQL:

When you retrieve the row from the database, the value is displayed with just a single ' character:

The GROUP BY clause returns a unique set of results for the desired columns. This is most often used in conjunction with an aggregate function to present results for a range of values in a single query, rather than requiring you to issue one query per target value.

While GROUP BY can be useful for a single column, it is more often used to return the distinct results across multiple columns. For example, the following query shows us which groups have permissions at each depth in the library hierarchy:

Extending this further, you can use the COUNT() aggregate function to also return the number of times each unique combination of grp and depth appears in the table. Yes, this is a sneak peek at the use of aggregate functions! Keeners.

You can use the WHERE clause to restrict the returned results before grouping is applied to the results. The following query restricts the results to those rows that have a depth of 0.

To restrict results after grouping has been applied to the rows, use the HAVING clause; this is typically used to restrict results based on a comparison to the value returned by an aggregate function. For example, the following query restricts the returned rows to those that have more than 5 occurrences of the same value for grp in the table.

GROUP BY is one way of eliminating duplicate results from the rows returned by your query. The purpose of the DISTINCT keyword is to remove duplicate rows from the results of your query. However, it works, and it is easy - so if you just want a quick list of the unique set of values for a column or set of columns, the DISTINCT keyword might be appropriate.

On the other hand, if you are getting duplicate rows back when you don’t expect them, then applying the DISTINCT keyword might be a sign that you are papering over a real problem.

The LIMIT clause restricts the total number of rows returned from your query and is useful if you just want to list a subset of a large number of rows. For example, in the following query we list the five most frequently used circulation modifiers:

When you use the LIMIT clause to restrict the total number of rows returned by your query, you can also use the OFFSET clause to determine which subset of the rows will be returned. The use of the OFFSET clause assumes that you’ve used the ORDER BY clause to impose order on the results.

In the following example, we use the OFFSET clause to get results 6 through 10 from the same query that we prevously executed.

Thus far you’ve been working with a single table at a time - and you have been able accomplish a great deal. However, relational databases by their nature spread data across multiple tables, so it is important to learn how to bring that data back together in your queries. In addition, real data in the wild often requires taming by transforming it to different states so that you can, for example, compare values more efficiently, or reduce the number of results, or find the maximum value in a set of results.

PostgreSQL includes many built-in functions for manipulating column data. You can also create your own functions (and Evergreen does make use of many custom functions). There are two types of functions used in databases: scalar functions and aggregate functions.

Scalar functions transform each value of the target column. If your query would return 50 values for a column in a given query, and you modify your query to apply a scalar function to the values returned for that column, it will still return 50 values. For example, the UPPER() function, used to convert text values to upper-case, modifies the results in the following set of queries:

There are so many scalar functions in PostgreSQL that we cannot cover them all here, but we can list some of the most commonly used functions:

For a complete list of scalar functions, see the PostgreSQL function documentation.

Aggregate functions return a single value computed from the the complete set of values returned for the specified column.

A sub-select is the technique of using the results of one query to feed into another query. You can, for example, return a set of values from one column in a SELECT statement to be used to satisfy the IN() condition of another SELECT statement; or you could return the MAX() value of a column in a SELECT statement to match the = condition of another SELECT statement.

For example, in the following query we use a sub-select to restrict the copies returned by the main SELECT statement to only those locations that have an opac_visible value of TRUE:

Sub-selects can be an approachable way to breaking down a problem that requires matching values between different tables, and often result in a clearly expressed solution to a problem. However, if you start writing sub-selects within sub-selects, you should consider tackling the problem with joins instead.

Joins enable you to access the values from multiple tables in your query results and comparison operators. For example, joins are what enable you to relate a bibliographic record to a barcoded copy via the biblio.record_entry, asset.call_number, and asset.copy tables. In this section, we discuss the most common kind of join—the inner join—as well as the less common outer join and some set operations which can compare and contrast the values returned by separate queries.

When we talk about joins, we are going to talk about the left-hand table and the right-hand table that participate in the join. Every join brings together just two tables - but you can use an unlimited (for our purposes) number of joins in a single SQL statement. Each time you use a join, you effectively create a new table, so when you add a second join clause to a statement, table 1 and table 2 (which were the left-hand table and the right-hand table for the first join) now act as a merged left-hand table and the new table in the second join clause is the right-hand table.

Clear as mud? Okay, let’s look at some examples.

An inner join returns all of the columns from the left-hand table in the join with all of the columns from the right-hand table in the joins that match a condition in the ON clause. Typically, you use the = operator to match the foreign key of the left-hand table with the primary key of the right-hand table to follow the natural relationship between the tables.

In the following example, we return all of columns from the actor.usr and actor.org_unit tables, joined on the relationship between the user’s home library and the library’s ID. Notice in the results that some columns, like id and mailing_address, appear twice; this is because both the actor.usr and actor.org_unit tables include columns with these names. This is also why we have to fully qualify the column names in our queries with the schema and table names.

Of course, you do not have to return every column from the joined tables; you can (and should) continue to specify only the columns that you want to return. In the following example, we count the number of borrowers for every user profile in a given library by joining the permission.grp_tree table where profiles are defined against the actor.usr table, and then joining the actor.org_unit table to give us access to the user’s home library:

So far we have been fully-qualifying all of our table names and column names to prevent any confusion. This quickly gets tiring with lengthy qualified table names like permission.grp_tree, so the SQL syntax enables us to assign aliases to table names and column names. When you define an alias for a table name, you can access its column throughout the rest of the statement by simply appending the column name to the alias with a period; for example, if you assign the alias au to the actor.usr table, you can access the actor.usr.id column through the alias as au.id.

The formal syntax for declaring an alias for a column is to follow the column name in the result columns clause with AS alias. To declare an alias for a table name, follow the table name in the FROM clause (including any JOIN statements) with AS alias. However, the AS keyword is optional for tables (and columns as of PostgreSQL 8.4), and in practice most SQL statements leave it out. For example, we can write the previous INNER JOIN statement example using aliases instead of fully-qualified identifiers:

A nice side effect of declaring an alias for your columns is that the alias is used as the column header in the results table. The previous version of the query, which didn’t use aliased column names, had two columns named name; this version of the query with aliases results in a clearer categorization.

An outer join returns all of the rows from one or both of the tables participating in the join.

It is possible to join a table to itself. You can, in fact you must, use aliases to disambiguate the references to the table.

Relational databases are effectively just an efficient mechanism for manipulating sets of values; they are implementations of set theory. There are three operators for sets (tables) in which each set must have the same number of columns with compatible data types: the union, intersection, and difference operators.

The UNION operator returns the distinct set of rows that are members of either or both of the left-hand and right-hand tables. The UNION operator does not return any duplicate rows. To return duplicate rows, use the UNION ALL operator.

The INTERSECT operator returns the distinct set of rows that are common to both the left-hand and right-hand tables. To return duplicate rows, use the INTERSECT ALL operator.

The EXCEPT operator returns the rows in the left-hand table that do not exist in the right-hand table. You are effectively subtracting the common rows from the left-hand table.

A view is a persistent SELECT statement that acts like a read-only table. To create a view, issue the CREATE VIEW statement, giving the view a name and a SELECT statement on which the view is built.

The following example creates a view based on our borrower profile count:

When you subsequently select results from the view, you can apply additional WHERE clauses to filter the results, or ORDER BY clauses to change the order of the returned rows. In the following examples, we issue a simple SELECT * statement to show that the default results are returned in the same order from the view as the equivalent SELECT statement would be returned. Then we issue a SELECT statement with a WHERE clause to further filter the results.

PostgreSQL supports table inheritance: that is, a child table inherits its base definition from a parent table, but can add additional columns to its own definition. The data from any child tables is visible in queries against the parent table.

Evergreen uses table inheritance in several areas: * In the Vandelay MARC batch importer / exporter, Evergreen defines base tables for generic queues and queued records for which authority record and bibliographic record child tables * Billable transactions are based on the money.billable_xact table; child tables include action.circulation for circulation transactions and money.grocery for general bills. * Payments are based on the money.payment table; its child table is money.bnm_payment (for brick-and-mortar payments), which in turn has child tables of money.forgive_payment, money.work_payment, money.credit_payment, money.goods_payment, and money.bnm_desk_payment. The money.bnm_desk_payment table in turn has child tables of money.cash_payment, money.check_payment, and money.credit_card_payment. * Transits are based on the action.transit_copy table, which has a child table of action.hold_transit_copy for transits initiated by holds. * Generic acquisition line items are defined by the acq.lineitem_attr_definition table, which in turn has a number of child tables to define MARC attributes, generated attributes, user attributes, and provider attributes.

Some queries run for a long, long time. This can be the result of a poorly written query—a query with a join condition that joins every row in the biblio.record_entry table with every row in the metabib.full_rec view would consume a massive amount of memory and disk space and CPU time—or a symptom of a schema that needs some additional indexes. PostgreSQL provides the EXPLAIN tool to estimate how long it will take to run a given query and show you the query plan (how it plans to retrieve the results from the database).

To generate the query plan without actually running the statement, simply prepend the EXPLAIN keyword to your query. In the following example, we generate the query plan for the poorly written query that would join every row in the biblio.record_entry table with every row in the metabib.full_rec view:

This query plan shows that the query would return 132415734100864 rows, and it plans to accomplish what you asked for by sequentially scanning (Seq Scan) every row in each of the tables participating in the join.

In the following example, we have realized our mistake in joining every row of the left-hand table with every row in the right-hand table and take the saner approach of using an INNER JOIN where the join condition is on the record ID.

This time, we will return 65766704 rows - still way too many rows. We forgot to include a WHERE clause to limit the results to something meaningful. In the following example, we will limit the results to deleted records that were modified in the last month.

We can see that the number of rows returned is now only 201669; that’s something we can work with. Also, the overall cost of the query is 2306218, compared to 4959156437783 in the original query. The Index Scan tells us that the query planner will use the index that was defined on the deleted column to avoid having to check every row in the biblio.record_entry table.

However, we are still running a sequential scan over the metabib.real_full_rec table (the table on which the metabib.full_rec view is based). Given that linking from the bibliographic records to the flattened MARC subfields is a fairly common operation, we could create a new index and see if that speeds up our query plan.

We can see that the resulting number of rows is still the same (201669), but the execution estimate has dropped to 1558330 because the query planner can use the new index (bib_record_idx) rather than scanning the entire table. Success!

To insert one or more rows into a table, use the INSERT statement to identify the target table and list the columns in the table for which you are going to provide values for each row. If you do not list one or more columns contained in the table, the database will automatically supply a NULL value for those columns. The values for each row follow the VALUES clause and are grouped in parentheses and delimited by commas. Each row, in turn, is delimited by commas (this multiple row syntax requires PostgreSQL 8.2 or higher).

For example, to insert two rows into the permission.usr_grp_map table:

Of course, as with the rest of SQL, you can replace individual column values with one or more use sub-selects:

Sometimes you want to insert a bulk set of data into a new table based on a query result. Rather than a VALUES clause, you can use a SELECT statement to insert one or more rows matching the column definitions. This is a good time to point out that you can include explicit values, instead of just column identifiers, in the return columns of the SELECT statement. The explicit values are returned in every row of the result set.

In the following example, we insert 6 rows into the permission.usr_grp_map table; each row will have a usr column value of 1, with varying values for the grp column value based on the id column values returned from permission.grp_tree:

Deleting data from a table is normally fairly easy. To delete rows from a table, issue a DELETE statement identifying the table from which you want to delete rows and a WHERE clause identifying the row or rows that should be deleted.

In the following example, we delete all of the rows from the permission.grp_perm_map table where the permission maps to UPDATE_ORG_UNIT_CLOSING and the group is anything other than administrators:

To update rows in a table, issue an UPDATE statement identifying the table you want to update, the column or columns that you want to set with their respective new values, and (optionally) a WHERE clause identifying the row or rows that should be updated.

Following is the syntax for the UPDATE statement:

The following queries were requested by Bibliomation, but might be reusable by other libraries.

We define a "collection" as a shelving location in Evergreen.

in the following set of queries, we bring together the active title, volume, and copy holds and display those that have more than a certain number of holds per title. The goal is to UNION ALL the three queries, then group by the bibliographic record ID and display the title / author information for those records that have more than a given threshold of holds.

In this example, the library has opened a new branch in a growing area, and wants to reassign the home library for the patrons in the vicinity of the new branch to the new branch. To accomplish this, we create a staging table that holds a set of city names and the corresponding branch shortname for the home library for each city.

Then we issue an UPDATE statement to set the home library for patrons with a physical address with a city that matches the city names in our staging table.

This work is licensed under a Creative Commons Attribution-Share Alike 2.5 Canada License.