Introduction to SQL for Evergreen administrators

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 2012-01-23) 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.

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:

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.

A subquery 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 subquery to restrict the copies returned by the main SELECT statement to only those locations that have an opac_visible value of TRUE:

Subqueries 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 subqueries within subqueries, you should consider tackling the problem with joins or WITH clauses 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.

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.

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:

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.

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 subqueries:

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:

Given a large amount of data in a text file, you can use the COPY statement to quickly insert that data into a table. COPY statements are optimized for bulk loading and are faster than issuing the equivalent INSERT statements. By default, COPY expects tab-delimited files with one record per line, but you can specify different options. You can also copy data from STDIN, which can be useful for scripting data loads.

For example, given the following file of raw data in which ` → ` represents a <TAB> character, we can load some copy locations into Evergreen:

As previously noted, you can load data from STDIN. In this case, you need to identify the end of the data in the file with \. appearing on a line by itself. This delimiter enables other commands to follow it, for example for a script to populate many different tables in a database.

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:

PostgreSQL supports user-defined functions—that is, scalar or aggregate functions written in one of a number of different languages—and Evergreen relies heavily on user-defined functions to support its business logic. Accordingly, to understand how the Evergreen database schema transforms input into the results you and your users experience, you need to understand a number of different functions written in SQL, plpgsql, and plperlu.

For just a few examples:

A trigger can be added to a table to specify a function that should be invoked when that table is modified by means of an INSERT, UPDATE, or DELETE statement. The trigger can fire before or after the statement, and can fire once per modified row, or once per statement, depending on the trigger definition. A trigger can effectively prevent the statement from happening entirely, modify the data that was to have been inserted or updated, and insert data into other tables. Tables can have multiple triggers defined on them, firing matching BEFORE triggers before AFTER triggers, with triggers within those categories firing in alphabetical order.

For example, the biblio.record_entry table has a number of triggers defined on it:

Rules are conceptually similar to triggers, in that they modify the results of SELECT, INSERT, UPDATE, or DELETE statements for a given table, but their implementation is not contained in a function. Instead, the implementation is one or more SQL statements that are run in addition to, or instead of, the matching SQL statement.

For example, the following rule on biblio.record_entry prevents a DELETE statement from actually deleting the targeted row from the table, but instead sets the value of the deleted column to TRUE and removes the corresponding row from the metabib.metarecord_source_map table to effectively make the record invisible to regular users. This enables Evergreen to maintain referential integrity for any bookbags or call numbers that might reference the now-deleted record, for example.