The stored procedure code referenced in this section is available in a zip file here. The zip file includes scripts for both linux and Windows to load the procedures in the database. There are separate directories in the file for procedure code used by PostgreSQL, MySQL, and Oracle.

Both the stored procedure code and the load script require:

• A MusicXML database schema loaded with score data
• The database command-line client in the path of the command line running the script (mysql, psql, or sqlplus)

The script, when executed, will prompt the user for the database username, password, and the database name where the MusicXML data is stored.

View score_view references music data records in a score, along with the record's measure and pitch information.

The score_report procedure iterates over the music data records in the score view, and for each iteration calculates the current state of the score, stores the current state in temporary tables, and calls an individual report procedure.

score_report also creates join database records for each measure in a score, and a finishing record indicating that the report has been run so that there aren't duplicate report runs.

Stored convenience functions included are:

• char_to_boolean: translates boolean character values ('Y', 'N', null) to a boolean datatype
• is_tied_note: returns a boolean value for whether there is a tie to a given note
• pitch_label: returns a pitch label for a pitch class
• pitch_number: returns a pitch class number for pitch name, alter, and transpose values

Individual report procedures are called by procedure score_report once every music data iteration. The report procedure queries the current score state in the temporary tables stored by the score report procedure, makes its own calculations and stores the results in its own dedicated reports table. The data in the reports table is then used by the BI software to create a visual display.

The individual reports here are:

• pitch_count_report: counts the number of each pitch class in a score
• interval_count_report: counts the number of each interval in consecutive notes
• measure_notes_report: keeps track of the pitch classes that occur at the beginning of each measure

A score report is executed by calling procecure score_report.

Calling score_report populates the reports table for that report.

Call score_report at the mysql prompt using the call command, the report name and the Score ID of a score that's in the MusicXML database. The Score ID of a score is found in table SCORE, field ID.

Example: call score_report('pitch_count_report', 5);