PINES Documentation

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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.

Many tables are related to other tables, and these related tables are linked.
To create a report, we need to join the tables together, so you can see the relationship that exists between the data.

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

Below is an example diagram showing how different tables can be linked through shared fields.

{{:reports:egreports:linked_table.png?600|}}


===== SQL Queries =====

Structure Query Language, or 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 updating the data held in the database - for example, change all patrons with home library A to home library B. 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 general terms of Evergreen’s report template interface:

  * //SELECT// → “Display Fields”
  * //FROM// → “Source”
  * //WHERE// → “Filters”
  * //HAVING// → More filters

To show specific examples from a template, the Display Fields (SELECT) are represented by the Report Columns. The column names show what you want to see.

{{:reports:egreports:select_report_columns.png?400|}}

The Source (FROM) in this case is 'Classic Item List.' The source used will be listed in the Report Description.

The Filters (WHERE) are Edit Date, Owning Library, and that the item is deleted is true.

{{:reports:egreports:where_filters.png?400|}}

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).

//Source: {{ :reports:egreports:advanced_reporting_guide.doc |Advanced Reporting Guide}}//