MusicXML Tasks: Procedure Code

Procedure Code

Functions

char_to_boolean

drop function if exists char_to_boolean;

delimiter //
create function char_to_boolean (
    boolean_value char(1)
)
returns boolean
deterministic
begin
    return boolean_value is not null and boolean_value = 'Y';
end //
delimiter ;

is_tied_note

drop function if exists is_tied_note;

delimiter //
create function is_tied_note (
    v_note_id int
)
returns boolean
not deterministic
begin
    declare is_end_of_data boolean default false;
    declare v_type varchar(255);
    declare is_tied boolean default false;

    declare c_note_ties cursor for
        select type from tie where note_id = v_note_id;

    declare continue handler for not found set is_end_of_data = true;

    open c_note_ties;
    ties_loop: loop
        fetch c_note_ties into v_type;
        if is_end_of_data then leave ties_loop; end if;
        if v_type = 'stop' or v_type = 'continue' then
            set is_tied = true;
            leave ties_loop;
        end if;
    end loop;
    close c_note_ties;

    return is_tied;
end //
delimiter ;

pitch_label

drop function if exists pitch_label;

delimiter //
create function pitch_label (
    v_pitch_class int
)
returns varchar(255)
deterministic
begin
    case v_pitch_class
        when 0 then
            return 'C';
        when 1 then
            return 'C-sharp/D-flat';
        when 2 then
            return 'D';
        when 3 then
            return 'D-sharp/E-flat';
        when 4 then
            return 'E';
        when 5 then
            return 'F';
        when 6 then
            return 'F-sharp/G-flat';
        when 7 then
            return 'G';
        when 8 then
            return 'G-sharp/A-flat';
        when 9 then
            return 'A';
        when 10 then
            return 'A-sharp/B-flat';
        when 11 then
            return 'B';
        else
            return null;
    end case;
end //
delimiter ;

pitch_number

drop function if exists pitch_number;

delimiter //
create function pitch_number (
	v_step varchar(255),
	v_pitch_alter int,
	v_transpose int
)
returns int
deterministic
begin
	declare v_pitch_number int default 0;
	declare v_mod_number int default 12;

	if v_step is null then return null; end if;

	case v_step
		when 'A' then
			set v_pitch_number = 9;
		when 'B' then
			set v_pitch_number = 11;
		when 'C' then
			set v_pitch_number = 0;
		when 'D' then
			set v_pitch_number = 2;
		when 'E' then
			set v_pitch_number = 4;
		when 'F' then
			set v_pitch_number = 5;
		when 'G' then
			set v_pitch_number = 7;
		else
			return null;
	end case;
	if v_pitch_alter is not null then set v_pitch_number = v_pitch_number + v_pitch_alter; end if;
	if v_transpose is not null then set v_pitch_number = v_pitch_number + v_transpose; end if;
	return (mod(v_pitch_number + v_mod_number, v_mod_number));
end //
delimiter ;

The score view

score_view

create or replace view score_view as
select
       s.id as score_id,
       p.id as part_id,
       m.measure_number,
       md.id as music_data_id, md.music_data_type, md.voice, md.duration, md.grace_id, char_to_boolean(md.chord) as chord,
       nt.note_type_type, nt.step, nt.pitch_alter, nt.octave,
       ti.chromatic
from
     score s, part p, measure m, music_data md
left join note_type nt on nt.id = md.note_type_id
left join transpose t on t.attributes_id = md.id
left join transpose_info ti on ti.id = t.transpose_info_id
where
      s.id = p.score_id
      and p.id = m.part_id and m.id = md.measure_id
order by p.ordering, m.ordering, md.ordering;

Procedure score_report

score_report

drop procedure if exists score_report;

delimiter //
create procedure score_report
(in v_procedure_name varchar(255), in v_score_id int)
proc: begin
    declare is_end_of_data boolean default false;
    declare is_report_run boolean default false;
    declare v_score_name varchar(255);
    declare v_current_transposition int;
    declare v_current_part_id int default 0;
    declare v_part_id int default 0;
    declare v_current_measure_number varchar(255) default '';
    declare v_measure_number varchar(255);
    declare v_music_data_id int;
    declare v_music_data_type varchar(255);
    declare v_voice varchar(255);
    declare v_current_measure_duration int default 0;
    declare v_previous_duration int default 0;
    declare v_duration int;
    declare is_new_measure boolean;
    declare v_octave int;
    declare v_previous_octave int;
    declare v_grace_id int;
    declare is_chord boolean;
    declare v_note_type_type varchar(255);
    declare v_chromatic int;
    declare v_step varchar(255);
    declare v_previous_step varchar(255);
    declare v_pitch_alter int;
    declare v_previous_pitch_alter int;
    declare is_measures_handled boolean;
    declare is_first_part boolean;
    declare v_report_measure_id int;
    declare v_ordering int;

    declare c_score_items cursor for
        select part_id, measure_number, music_data_id, music_data_type, voice, duration, grace_id, chord, note_type_type, step, pitch_alter, octave, chromatic
        from score_view
        where score_id = v_score_id;

    declare continue handler for not found set is_end_of_data = true;

    declare exit handler for sqlexception
        begin
            get diagnostics condition 1 @error_message = message_text;
            select @error_message as error_message;
            rollback to report_run;
        end;

    create table if not exists report_run (
        score_id int,
        procedure_name varchar(255),
        unique (score_id, procedure_name)
    );

    create table if not exists report_measure (
        id int,
        score_id int,
        measure_number varchar(255),
        ordering int,
        unique (score_id, measure_number)
    );

    create temporary table if not exists report_current_music_data (
        measure_number varchar(255),
        music_data_type varchar(31),
        transposition int,
        note_type_type varchar(31),
        tied boolean,
        chord boolean,
        octave int,
        step varchar(255),
        pitch_alter int,
        previous_octave int,
        previous_step varchar(255),
        previous_pitch_alter int,
        new_measure boolean,
        grace_id int
    );

    create temporary table if not exists report_previous_voice_note (
        voice varchar(255),
        octave int,
        step varchar(255),
        pitch_alter int
    );

    if not exists(select id from score where id = v_score_id) then
        set @message_text = concat('Score ID ', v_score_id, ' not found');
        signal sqlstate '45000' set message_text = @message_text;
    end if;

    if exists(select score_id from report_run where score_id = v_score_id and procedure_name = v_procedure_name) then
        set @message_text = concat('Report ', v_procedure_name, ' already run for score ', v_score_id);
        signal sqlstate '45000' set message_text = @message_text;
    end if;

    select score_name from score where id = v_score_id into v_score_name;

    select concat('Running report ', v_procedure_name, ' for score id ', v_score_id, ', score name ', v_score_name) as message;

    start transaction;
    savepoint report_run;

    set @s_procedure_statement = concat('call ', v_procedure_name, '(', v_score_id, ')');
    prepare s_procedure_statement from @s_procedure_statement;

    set is_measures_handled = exists(select score_id from report_measure where score_id = v_score_id);

    open c_score_items;
    score_items_loop: loop
        fetch c_score_items into
            v_part_id, v_measure_number, v_music_data_id, v_music_data_type,
            v_voice, v_duration, v_grace_id, is_chord, v_note_type_type,
            v_step, v_pitch_alter, v_octave, v_chromatic;
        if is_end_of_data then leave score_items_loop; end if;
        if v_part_id != v_current_part_id then
            if v_current_part_id = 0 then
                set is_first_part = true;
            else set is_first_part = false;
            end if;
            set v_current_transposition = null;
            set v_current_part_id = v_part_id;
            delete from report_previous_voice_note;
        end if;
        if v_measure_number != v_current_measure_number then
            if is_first_part and not is_measures_handled then
                select coalesce(max(id) + 1, 1) from report_measure into v_report_measure_id;
                select coalesce(max(ordering) + 1, 1) from report_measure where score_id = v_score_id into v_ordering;
                insert into report_measure (id, score_id, measure_number, ordering) values (v_report_measure_id, v_score_id, v_measure_number, v_ordering);
            end if;
            set v_current_measure_number = v_measure_number;
            set v_current_measure_duration = 0;
        end if;

        -- process the music data type
        case v_music_data_type
            when 'attributes' then
                if v_chromatic is not null then set v_current_transposition = v_chromatic; end if;
            when 'backup' then
                set v_current_measure_duration = v_current_measure_duration - v_duration;
            when 'forward' then
                set v_current_measure_duration = v_current_measure_duration + v_duration;
            when 'note' then
                if v_note_type_type = 'rest' then
                    delete from report_previous_voice_note where voice = v_voice;
                elseif v_note_type_type = 'pitch' then
                    if not is_chord then
                        if exists (select voice from report_previous_voice_note where voice = v_voice) then
                            select octave, step, pitch_alter into v_previous_octave, v_previous_step, v_previous_pitch_alter
                            from report_previous_voice_note where voice = v_voice;
                            update report_previous_voice_note set octave = v_octave, step = v_step, pitch_alter = v_pitch_alter where voice = v_voice;
                        else
                            set v_previous_octave = null;
                            set v_previous_step = null;
                            set v_previous_pitch_alter = null;
                            insert into report_previous_voice_note (voice, octave, step, pitch_alter) values (v_voice, v_octave, v_step, v_pitch_alter);
                        end if;
                    end if;
                end if;
                if is_chord then set v_current_measure_duration = v_current_measure_duration - v_previous_duration; end if;
                if v_current_measure_duration = 0 then
                    set is_new_measure = true;
                else
                    set is_new_measure = false;
                end if;
                set v_current_measure_duration = v_current_measure_duration + v_duration;
                set v_previous_duration = v_duration;
            else begin end;
            end case;

        -- replace current state used by reports procedures
        delete from report_current_music_data;
        insert into report_current_music_data
            (measure_number, music_data_type, transposition, note_type_type, tied, chord,
             octave, step, pitch_alter, previous_octave, previous_step, previous_pitch_alter, new_measure, grace_id)
            values
            (v_measure_number, v_music_data_type, v_current_transposition, v_note_type_type, is_tied_note(v_music_data_id), is_chord,
             v_octave, v_step, v_pitch_alter, v_previous_octave, v_previous_step, v_previous_pitch_alter, is_new_measure, v_grace_id);

        -- run a report
        execute s_procedure_statement;
        set is_report_run = true;

    end loop;
    close c_score_items;
    deallocate prepare s_procedure_statement;

    if is_report_run then insert into report_run (score_id, procedure_name) values (v_score_id, v_procedure_name); end if;
    drop temporary table if exists report_previous_voice_note;
    drop temporary table if exists report_current_music_data;

    commit;

    select 'Report run complete' as message;
end //
delimiter ;

Individual score reports

The score report procedure and its reports data table are grouped together.

pitch_count_report and table report_pitch_counts

create table if not exists report_pitch_counts (
    score_id int,
    pitch int,
    pitch_count int
);

drop procedure if exists pitch_count_report;

delimiter //
create procedure pitch_count_report
	(in v_score_id int)
proc: begin
	declare v_pitch_count int;
	declare v_pitch_number int;
	declare v_music_data_type varchar(255);
	declare v_step varchar(255);
	declare v_pitch_alter int;
	declare v_transposition int;
	declare is_tied boolean;

	select music_data_type, step, pitch_alter, transposition, tied from report_current_music_data into v_music_data_type, v_step, v_pitch_alter, v_transposition, is_tied;

	if v_music_data_type != 'note' then leave proc; end if;
	if is_tied then leave proc; end if;

    set v_pitch_number = pitch_number(v_step, v_pitch_alter, v_transposition);
    if v_pitch_number is null then leave proc; end if;

    select pitch_count into v_pitch_count from report_pitch_counts where score_id = v_score_id and pitch = v_pitch_number;
    if v_pitch_count is null then
        insert into report_pitch_counts (score_id, pitch, pitch_count) values (v_score_id, v_pitch_number, 1);
    else
        set v_pitch_count = v_pitch_count + 1;
        update report_pitch_counts set pitch_count = v_pitch_count where score_id = v_score_id and pitch = v_pitch_number;
    end if;
end //
delimiter ;

interval_count_report and table report_interval_counts

create table if not exists report_interval_counts (
    score_id int,
    pitch_interval int,
    interval_count int
);

drop procedure if exists interval_count_report;

delimiter //
create procedure interval_count_report
(in v_score_id int)
proc: begin
    declare v_interval_count int;
    declare v_interval_number int;
    declare v_music_data_type varchar(255);
    declare v_note_type_type varchar(255);
    declare is_tied boolean;
    declare is_chord boolean;
    declare v_step varchar(255);
    declare v_previous_step varchar(255);
    declare v_pitch_alter int;
    declare v_previous_pitch_alter int;
    declare v_octave int;
    declare v_previous_octave int;

    select music_data_type, note_type_type, tied, chord, step, previous_step, pitch_alter, previous_pitch_alter, octave, previous_octave from report_current_music_data
    into v_music_data_type, v_note_type_type, is_tied, is_chord, v_step, v_previous_step, v_pitch_alter, v_previous_pitch_alter, v_octave, v_previous_octave;

    if v_music_data_type != 'note' and v_note_type_type != 'pitch' then leave proc; end if;
    if is_tied then leave proc; end if;
    if is_chord then leave proc; end if;

    if v_step is null or v_previous_step is null then leave proc; end if;

    set v_interval_number = pitch_number(v_step, v_pitch_alter, null) - pitch_number(v_previous_step, v_previous_pitch_alter, null);
    if v_octave is not null and v_previous_octave is not null then
        set v_interval_number = v_interval_number + ((v_octave * 12) - (v_previous_octave * 12));
    end if;
    select interval_count into v_interval_count from report_interval_counts where score_id = v_score_id and pitch_interval = v_interval_number;
    if v_interval_count is null then
        insert into report_interval_counts (score_id, pitch_interval, interval_count) values (v_score_id, v_interval_number, 1);
    else
        set v_interval_count = v_interval_count + 1;
        update report_interval_counts set interval_count = v_interval_count where score_id = v_score_id and pitch_interval = v_interval_number;
    end if;
end //
delimiter ;

measure_notes_report and table report_measure_notes

create table if not exists report_measure_notes (
    report_measure_id int,
    step varchar(255),
    pitch_alter int,
    transpose int
);

drop procedure if exists measure_notes_report;

delimiter //
create procedure measure_notes_report
	(in v_score_id int)
proc: begin
    declare v_music_data_type varchar(255);
    declare v_grace_id int;
    declare is_new_measure boolean;
    declare v_report_measure_id int;
    declare v_measure_number varchar(255);
	declare v_step varchar(255);
	declare v_pitch_alter int;
	declare v_current_transposition int;

    select music_data_type, grace_id, new_measure, measure_number, step, pitch_alter, transposition from report_current_music_data
    into v_music_data_type, v_grace_id, is_new_measure, v_measure_number, v_step, v_pitch_alter, v_current_transposition;

    if v_music_data_type != 'note' or v_grace_id is not null or not is_new_measure then leave proc; end if;

    select id from report_measure where score_id = v_score_id and measure_number = v_measure_number into v_report_measure_id;
    if v_report_measure_id is null then
        select concat('report_measure record not found for measure number ', v_measure_number) as message;
        leave proc;
    end if;

    if pitch_number(v_step, v_pitch_alter, v_current_transposition) is not null then
        insert into report_measure_notes (report_measure_id, step, pitch_alter, transpose)
        values (v_report_measure_id, v_step, v_pitch_alter, v_current_transposition);
    end if;
end //
delimiter ;