Database Format

Introduction

The MeloSpy library supports storing and retrieving melody data from SQL databases. Different formats are planned, currently only SQLITE3 is supported. However, the format are only due to SQL syntax differences, the central structure is always the same (cf. Table: Overview of tables). Main table is the melody-table (Table: melody). All melodies are sequences of events and stored in a single table, where each row represents one melody event. Each event has an unique eventid and melid which is used to cross-reference across the database.

The database is designed to hold different melody types, which basically differ only in the available metadata. The type of a melody is stored in the table melody_type (Table: melody_type). Currently three types are supported: Weimar Jazz Database (WJD), EsAC and POP-type. The first types are used in the WJD and the EsAC-DB deployed with the MeloSpySuite/GUI. POP songs are implemented for future use.

WJD type melodies use the tables transcription_info (Table: transcription_info (WJD)), record_info (Table: record_info (WJD)), composition_info (Table: composition_info (WJD)) and solo_info (Table: solo_info (WJD)) for meta data. The structure of the metadata reflects the fact that a solo is part of some track, which can contain more than one solo. Tracks are contained on some record, which can contain several tracks with solos included in the WJD. Atrack and hence each solo taken from this track is based on some composition, which might be also used for other tracks, e.g., a jazz standard such as “Body and Soul”.

EsAC-type melodies use esac_info (Table: esac_info (ESAC)), which contains the metadata originally used for EsAC folk songs. POP song type melodie use (Table: popsong_info (POP)) for storing rather sparse metadata for pop songs. In future, there might be an extension or alternative solution to this. For EsAC and POP melodies, only phrase sections in table sections (Table: sections) are used.

The beats table (Table: beats (WJD)) is solely used for WJD-type melodies to store (tapped) beats along with chord, form, bass pitch and other annotations.

Description of SQLite3 database tables

Table: Overview of tables

Table name	Description
beats	Table for beat annotation of WJD melodies, referenced by melody(melid)
composition_info	Infos regarding the underlying composition of a WJD solo, referenced by melody(melid)
db_info	Information regarding the distributed database file like version information, license, etc
esac_info	EsAC infos for EsAC melodies, referenced by melody(melid)
melody	Main table for all melody events
melody_type	Indicated type of melody: WJD solos or EsAC (Folk songs using Essen Associative Code), referenced by melody(melid)
popsong_info	Pop song infos, referenced by melody(melid)
record_info	Infos regarding the specific audio recording of a WJD solo was taken from, referenced by melody(melid)
sections	All sections (phrase, chorus, form, chords, etc.), referenced by melody(melid)
solo_info	Solo infos for WJD solos, referenced by melody(melid)
track_info	Information specific to a track on a record (or CD)
transcription_info	Transcription infos for WJD solos, referenced by melody(melid)

Table: beats (WJD)

Field	Type	Description
beatid	INTEGER	Unique ID of beat event, PRIMARY KEY
melid	INTEGER	References melody(melid)
onset	REAL	Onset (in secs) of beat
bar	INTEGER	Number of bar
beat	INTEGER	Number of beat in a bar
signature	TEXT	Signature of this and all subsequent beats and bars, resp.
chord	TEXT	Accompanying chord
form	TEXT	Form part of this and all subsequent beats (e.g. A1, B2)
bass_pitch	INTEGER	Bass pitch (fractional MIDI) of the beat
chorus_id	INTEGER	Number of chorus this and all subsequent beats are belonging to

Table: composition_info (WJD)

Field	Type	Description
compid	INTEGER	Unique ID of composition_info, PRIMARY KEY
title	TEXT	Title of the composition
composer	TEXT	Composer(s) of the underlying tune
form	TEXT	Basic form the song (e.g. AABA), including labels and length (in bars)
template	TEXT	Template
tonalitytype	TEXT	Tonality type of the song. Possible values: FUNCTIONAL, BLUES, JAZZ-BLUES, MODAL, COLOR, FREE. See Tonality Type for more information.
genre	TEXT	Genre of the solo. Possible values: TRADITIONAL, BLUES, GREAT AMERICAN SONGBOOK, WORMS, ORIGINAL, RIFF. See Genre for more information.

Table: db_info

Field	Type	Description
name	TEXT	Descriptive name for the database
creator	TEXT	Person/project who created the database
major	INTEGER	Major version of database schema
minor	INTEGER	Minor version of database schema
release	TEXT	Release version of database content
created	TEXT	Date and time of the creation of the database
license	TEXT	License text
status	TEXT	Current status of the database content. (Possible values: FINAL, PREFINAL)

Table: esac_info (ESAC)

Field	Type	Description
melid	INTEGER	References melody(melid)
collection	TEXT	Folk song collection of an EsAC-type melody (orignally first line in a EsAC file)
title	TEXT	Title of an EsAC-type melody (orignal CUT-field)
esacid	TEXT	Original EsAC ID of an EsAC-type melody
key	TEXT	Original key of an EsAC-type melody (from KEY-field)
unit	TEXT	Original base rhythmical unit for of an EsAC-type melody (from KEY-field)
signature	TEXT	Original signature of an EsAC-type melody (from KEY-field)
region	TEXT	Original REG-field of an EsAC-type melody
function	TEXT	Original FCT/FKT-field of an EsAC-type melody
comment	TEXT	Original CMT-field of an EsAC-type melody
source	TEXT	Original SRC-field of an EsAC-type melody
cnr	TEXT	Original CNR-field of an EsAC-type melody
tunefamily	TEXT	Deducted tune family of an EsAC-type melody (derived from orignal EsAC ID)
text	TEXT	Original TEXT-field of an EsAC-type melody
melstring	TEXT	Original MEL-field of an EsAC-type melody
lcm_tatum	INTEGER	Least common multiple of all beat divisions

Table: melody

Field	Type	Description
eventid	INTEGER	Unique ID of a melody event, PRIMARY KEY, Event IDs must be incrementally increasing with respect to the onsets of the melody they belong to.
melid	INTEGER	Unique ID of the melody containing the event
onset	REAL	Onset (in sec) of the event
pitch	REAL	Pitch (fractional MIDI) of the event
duration	REAL	Duration (in sec) of the event
period	INTEGER	Period of the metrical context of the event
division	INTEGER	Division of current beat of the event
bar	INTEGER	Bar number of the event
beat	INTEGER	Beat number of the event
tatum	INTEGER	Tatum number of the event
subtatum	INTEGER	Subtatum number of the event
num	INTEGER	Numerator of orginal signature of the metrical context of the event
denom	INTEGER	Denominator of orginal signature of the metrical context of the event
beatprops	TEXT	Beat proportions of the metrical context of the event. Text of form '('<int>'+')+<int>')' or None.
beatdur	REAL	Duration of the current beat (in secs)
tatumprops	TEXT	Tatum proportions of the current beat. Text of form '('<real>',')+<real>')' or None.
loud_max	REAL	Maximum of loudness per tone
loud_med	REAL	Median of loudness per tone
loud_sd	REAL	Standard deviation of loundess per tone
loud_relpos	REAL	Relative position of loudness peak
loud_cent	REAL	Normalized temporal centroid of loudness per tone
loud_s2b	REAL	Signal-to-background ratio of loudness
f0_mod	TEXT	Annonated modulation per tone. One of the following values vibrato, fall-off, bend, slide, or empty string
f0_range	REAL	Modulation range in cents
f0_freq_hz	REAL	Modulation frequency in Hertz
f0_med_dev	REAL	Median deviation of the fundamental frequency from standard 12-tone equal tempered pitch (corrected for overall tuning)

Table: melody_type

Field	Type	Description
melid	INTEGER	References melody(melid)
type	TEXT	Type of melody. Valid types: ESAC, SOLO, SV.

Table: popsong_info (POP)

Field	Type	Description
melid	INTEGER	References melody(melid)
artist	TEXT	Name of the artist
title	TEXT	Title of the song
avgtempo	REAL	Average tempo (BPM) of the solo as determined by the Sonic Visualiser project file
tempoclass	TEXT	Rough classification of tempo of the solo. Possible values. SLOW, MEDIUM SLOW, MEDIUM, MEDIUM UP, UP. See Tempo Classes for more information.
signature	TEXT	Signature(s) of the solo.
key	TEXT	Key of the solo (if applicable) or tonal center. See Key for more information.
filename	TEXT	Filename of melody source
chordchanges	TEXT	Chord changes
year	INTEGER	Year
country	TEXT	Country of artist/performer
style	TEXT	Style/genre of the song

Table: record_info (WJD)

Field	Type	Description
recordid	INTEGER	Unique ID of record_info, PRIMARY KEY
artist	TEXT	Name of the artist of the record containing the track with the solo
recordtitle	TEXT	Title of the record containing the track with the solo
label	TEXT	Record label
recordbib	TEXT	Discographic entry for the record
mbzid	TEXT	MusicBrainz Identifier for the track containing the solo
releasedate	TEXT	Date of release of the record

Table: sections

Field	Type	Description
melid	INTEGER	References melody(melid)
type	TEXT	Type of section. Valid types: PHRASE, CHORD, FORM, CHORUS, KEY for WJD type melodies, PHRASE for EsAC and POP type melodies.
start	INTEGER	ID of first event in section (relative to the corresponding melody, not melody(eventid)!)
end	INTEGER	ID of last event in section (relative to the corresponding melody, not melody(eventid)!)
value	TEXT	Value of section. Valid values are : PHRASE:integer, CHORD: chord label, FORM: form label, CHORUS: integer, KEY: Key label.

Table: solo_info (WJD)

Field	Type	Description
melid	INTEGER	References melody(melid)
trackid	INTEGER	References track_info(trackid)
compid	INTEGER	References composition_info(compid)
recordid	INTEGER	References record_info(recordid)
performer	TEXT	Performer for WJD type melodies
title	TEXT	Title of tune for WJD type melodies
titleaddon	TEXT	Additional information appended to the title
solopart	INTEGER	Number of solo of corresponding track
instrument	TEXT	Instrument used in the solo
style	TEXT	Style of the solo. Possible values: TRADITIONAL, SWING, BEBOP, COOL, HARDBOP, POSTBOP, FREE, FUSION, OTHER, MIX. See Style for more information.
avgtempo	FLOAT	Average tempo (BPM) of the solo as determined by the beat track of the Sonic Visualiser project file
tempoclass	TEXT	Rough classification of tempo of the solo. Possible values. SLOW, MEDIUM SLOW, MEDIUM, MEDIUM UP, UP. See Tempo Classes for more information.
rhythmfeel	TEXT	Basic rhythmic groove of the solo. Possible values: TWOBEAT, SWING, BALLAD, LATIN, FUNK. See Rhythm Feel for more information.
key	TEXT	Key of the solo (if applicable) or tonal center. See Key for more information.
signature	TEXT	Signature(s) of the solo
chord_changes	TEXT	Chord changes of solo (as a compact string, as defined by one chorus)
chorus_count	INTEGER	Number of choruses played

Table: track_info (WJD)

Field	Type	Description
trackid	INTEGER	Unique ID of track_info, PRIMARY KEY
compid	INTEGER	References composition_info(compid)
recordid	INTEGER	References record_info(recordid)
filename_track	TEXT	Filename of the track
lineup	TEXT	Lineup
mbzid	TEXT	MusicBrainz Identifier for the track containing the solo
trackno	INTEGER	Number of the track on the record
recordingdate	TEXT	Date of recording

Table: transcription_info (WJD)

Field	Type	Description
melid	INTEGER	References melody(melid)
trackid	INTEGER	References track_info(trackid)
filename_sv	TEXT	Name of the originating Sonic Visualiser file for WJD type melodies
filename_solo	TEXT	Name of the solo cut from the original track (internal use only)
solotime	TEXT	Start/Endtime of the solo in the original track; Format mm:ss-mm:ss
solostart_sec	FLOAT	Start of the solo in seconds with nanoseconds resolution
status	TEXT	Status of the Sonic Visualiser file. Valid values: ASSIGNED, DRAFT, PREFINAL, FINAL.