User Tools

Site Tools


reports:egreports:intro_db

Introduction to Databases

This section introduces the basic structures of databases; an understanding of the Evergreen database is very valuable when creating templates.

Fields and Tables

The Evergreen database is organized as a group of tables that are related to one another. A table describes a real-world concept or idea and stores data related to that concept. The table definition includes named fields, which are slots that contain specific pieces of data. Tables store data in rows; each row contains fields describing a single instance of the concept it describes. Tables can also be associated with other tables that define related concepts using special fields that contain links to other tables.

A good metaphor for a database table is an Excel worksheet. Each column in the worksheet holds a specific kind of data, which is defined in the column header, with the groups of data presented in rows. The definition of a table’s fields can be thought of as the column headers, and each object in the table is like a specific row in the worksheet.

What the Excel spreadsheet metaphor doesn’t describe is the relationship that exists between tables. Many tables are related to other tables, and these related tables are linked.

For example, the Evergreen database has a table called asset.copy. This structure contains information that describes a specific copy (such as Creation Date, Copy Alert, etc.). It also contains links to other tables that contain related information that isn't necessarily copy-specific (such as Call Number, Owning Library, etc.).

SQL Queries

SQL is the language used to communicate with the Evergreen database. A single “chunk” of SQL that is used to communicate with a database is known as a statement. A statement can be a command or a query. A command is exactly what it sounds like: it tells the database to do something, such as add a table or field to the database structure or update the data held in the database. Commands are not used in Evergreen reporting; reports passively collect existing information but do not perform any ILS operations. A query, on the other hand, requests data from the database. The reporting module creates SQL queries to retrieve that information for the database user to look at (not change or delete). These queries are created by the Template and Report components, and their results are delivered through the Output component.

SQL queries typically follow this form:

  • SELECT (what you want to see)
  • FROM (your chosen data source(s))
  • [JOIN (one or more sources) on (foreign key relationships)]
  • WHERE (these filter conditions are met)
  • [HAVING (these filter conditions) - aggregates only]

In terms of Evergreen’s report template interface:

  • SELECT → “Display Fields”
  • FROM table [JOIN table(s)] → “Source”
  • WHERE → “Filters”
  • HAVING → More filters

Note that the WHERE (“Filters”) is very important. There is no situation where you would want to see every item in PINES. Filters are necessary to limit the query and get more focused results. The HAVING clause adds a further condition to the data provided as a result of the Filter, and controls how the data is grouped in, and appears in, the output (for example, Count).

The table part of the query specifies which table the query is directed toward. For each row in the table for which the conditional statements are true, the specified output fields are included in the output. The output fields must be fields defined in the table or the query will result in an error.

Source: Advanced Reporting Guide

reports/egreports/intro_db.txt · Last modified: 2022/09/16 11:55 by smorrison