My previous New Year posts were about drawing pictures in SQL: snowflakes, clocks, fractals and even our planet Earth as seen from above.

But contrary to the popular belief, SQL is not just about graphics processing. You can use it for all kinds of things.

Today we'll use SQL to create music.

Remember those polyphonic ringtones in early 2000's cell phones? Instead of downloading MP3's (which were a pain to download and even more pain to upload them to the phone), you could type in the notes and their values, and the phone would play them for you.

Most phones played tunes in a very straightforward way: for each note, they calculated its frequency and generated a pure sine waveform of this frequency. Polyphonic phones could play several notes at once: this would require two or more superimposed waveforms. Math behind that are very simple: channel amplitude is a sine function of time and frequency, and all the channel amplitudes are added together.

Let's do this too and play some New Year music with PostgreSQL.

### Notation

To define a note, we need to tell the system its pitch (how high or low it is) and value (how long to keep it playing). For simplicity, we assume all our notes have the same volume. We would also need a way to mark rests (pauses).

Letters `A`

to `G`

will define pitch (the way they normally do). The uppercase letters will define pitches C_{4} to B_{4} (in scientific notation), the lowercase would define pitches C_{3} to B_{3}. A sharp sign (`#`

) may follow a letter, meaning what the sharp sign normally does (increases pitch by half-tone). If we want to go beyond the two basic octaves, we would append one or more the plus signs `+`

to the note, so `D++`

would mean D_{6}, and `d++`

would mean D_{1}. The letter `p`

(in either case) would define a rest.

The pitch (or rest) may be followed by a number, which would define the note value (relative duration). 1 would stand for an eighth, 2 for a quarter etc. For simplicity, we won't use shorter values. If the number is omitted, the note would be considered an eighth.

Finally, all notes might be separated with spaces which mean nothing and are only used to improve legibility.

Here's a sample of what the score could look like:

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ) SELECT * FROM tracks

That's just two C scales one octave apart from each other, with the final notes being quarters (twice as long as the others).

### Parameters

We need to define some basic parameters of our music, namely: sample rate (for digitizing), tempo (how long does an eighth note last) and fade length. Fading in and out will make the music sound more natural.

To do this, we'll throw in another CTE:

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ) SELECT * FROM options

Here we define the basic parameters and also calculate the number of tracks (it will come in handy on later steps).

### Parsing

To parse our notation, we'll use three steps.

First, we get rid of the spaces and split the string into a table of notes (a letter with a possible sharp symbol and value). To do this, we use PostgreSQL's `REGEXP_SPLIT_TO_TABLE`

and split by empty strings or space sequences followed by a letter:

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ) SELECT * FROM notes

This gives us each note on its row, along with the track number.

Second, given a pitch, we need to define frequency.

We will use equal temperament with A440 pitch standard. This means that frequency ratio of all adjacent half-tones is 2^{1/12}, and the frequency of A_{4} is exactly 440 Hz. To know frequency of any given pitch, we would need to count how many half-tones apart from A_{4} it is, raise 2^{1/12} to this power and multiply or divide 440 by the result.

Since our notes are defined by letters and sharp signs, it would be easiest to build a lookup table with half-tone distances:

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC'), (2, 'CDEFGABC+') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ), scale (shift, note) AS ( VALUES (-9, 'C'), (-8, 'C#'), (-7, 'D'), (-6, 'D#'), (-5, 'E'), (-4, 'F'), (-3, 'F#'), (-2, 'G'), (-1, 'G#'), (0, 'A'), (1, 'A#'), (2, 'B') ) SELECT * FROM scale

Finally, we calculate physical parameters (duration and frequency) for each note. We apply a regular expression to isolate pitch, octave, sharp and value. Once we know them, we can substitute them into a formula and get the parameters:

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ), scale (shift, note) AS ( VALUES (-9, 'C'), (-8, 'C#'), (-7, 'D'), (-6, 'D#'), (-5, 'E'), (-4, 'F'), (-3, 'F#'), (-2, 'G'), (-1, 'G#'), (0, 'A'), (1, 'A#'), (2, 'B') ), params AS ( SELECT *, 440 * POWER(2, octave + shift / 12.0) AS frequency FROM ( SELECT track, number, UPPER(SUBSTR(digits[1], 1, 1)) || digits[2] AS note, CASE WHEN digits[1] ~ E'[A-G]' THEN LENGTH(digits[1]) ELSE 1 - LENGTH(digits[1]) END AS octave, COALESCE(NULLIF(digits[3], '')::INTEGER, 1) AS value FROM ( SELECT track, ROW_NUMBER() OVER (PARTITION BY track) number, REGEXP_MATCHES(note, '([A-Ga-gp]\+*)(#?)(\d*)') digits FROM notes ) q CROSS JOIN options ) q LEFT JOIN scale USING (note) ) SELECT * FROM params

This way, we have all note parameters nicely presented in a table: track, ordinal number in the track, octave; and, most important, value and frequency.

### Digitizing

Now that we have all parameters, we need to build waveforms out of them.

To do this, we would need to generate a large resultset which would contain as many records as there are samples. The number of samples would be equal to the sample rate times the length of an eighth note times the total number of the notes.

Once we have the sample resultset, we need to find the value of each sample. To do this, we join the resultset with the notes CTE we generated earlier. The join condition should be so that each note is played on its time.

However, on this stage we don't the the absolute start and end times of the notes yet, just their order and values. We would need to expand the previous CTE a little: calculate the absolute start and end times (in eighths), using a window function; and duplicate each note record so that it's repeated as many times as the note longs (again, in eighths). The latter step might seem redundant, but it improves the overall query performance.

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ), scale (shift, note) AS ( VALUES (-9, 'C'), (-8, 'C#'), (-7, 'D'), (-6, 'D#'), (-5, 'E'), (-4, 'F'), (-3, 'F#'), (-2, 'G'), (-1, 'G#'), (0, 'A'), (1, 'A#'), (2, 'B') ), params AS ( SELECT *, 440 * POWER(2, octave + shift / 12.0) AS frequency FROM ( SELECT track, number, UPPER(SUBSTR(digits[1], 1, 1)) || digits[2] AS note, CASE WHEN digits[1] ~ E'[A-G]' THEN LENGTH(digits[1]) ELSE 1 - LENGTH(digits[1]) END AS octave, COALESCE(NULLIF(digits[3], '')::INTEGER, 1) AS value FROM ( SELECT track, ROW_NUMBER() OVER (PARTITION BY track) number, REGEXP_MATCHES(note, '([A-Ga-gp]\+*)(#?)(\d*)') digits FROM notes ) q CROSS JOIN options ) q LEFT JOIN scale USING (note) ), times AS ( SELECT *, generate_series(start, start + value - 1) ts FROM ( SELECT *, SUM(value) OVER (PARTITION BY track ORDER BY number) - value AS start FROM params ) q ) SELECT * FROM times

The last note in each track is a quarter, so in our new resultset it's returned twice. We also have an additional field, `ts`

, which means "this note sounds at this beat", beats being counted from the beginning of the piece.

Now we can generate the sample recordset, calculate each sample value and return it along with each sample number. The sample value is a sum of sine functions for each note which sounds during this sample.

Sine takes values from -1 to 1, and the samples can take values from -32768 to 32767 (we use 16-bit samples). So we need to normalize the value: multiply the sum of sines, by, say, 30000 and divide by the number of tracks, so that no clipping would occur.

We also need to enable fading in and out. To do this we would make the wave amplitude gradually increase and decrease as the samples are taken close to beginning and end of the notes. We would multiply the sine by increasing or decreasing linear function of time, making sure its value does not go beyond 1.

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ), scale (shift, note) AS ( VALUES (-9, 'C'), (-8, 'C#'), (-7, 'D'), (-6, 'D#'), (-5, 'E'), (-4, 'F'), (-3, 'F#'), (-2, 'G'), (-1, 'G#'), (0, 'A'), (1, 'A#'), (2, 'B') ), params AS ( SELECT *, 440 * POWER(2, octave + shift / 12.0) AS frequency FROM ( SELECT track, number, UPPER(SUBSTR(digits[1], 1, 1)) || digits[2] AS note, CASE WHEN digits[1] ~ E'[A-G]' THEN LENGTH(digits[1]) ELSE 1 - LENGTH(digits[1]) END AS octave, COALESCE(NULLIF(digits[3], '')::INTEGER, 1) AS value FROM ( SELECT track, ROW_NUMBER() OVER (PARTITION BY track) number, REGEXP_MATCHES(note, '([A-Ga-gp]\+*)(#?)(\d*)') digits FROM notes ) q CROSS JOIN options ) q LEFT JOIN scale USING (note) ), times AS ( SELECT *, generate_series(start, start + value - 1) ts FROM ( SELECT *, SUM(value) OVER (PARTITION BY track ORDER BY number) - value AS start FROM params ) q ) SELECT tick, COALESCE((amp * 30000 / tcnt)::INTEGER, 0) sample FROM ( SELECT tick, SUM(SIN(frequency * 2 * PI() * tick / rate) * LEAST((start + value - cts) / fade, (cts - start) / fade, 1)) AS amp FROM ( SELECT *, tick / eighth / rate AS cts FROM ( SELECT *, generate_series(0, (maxts * rate * eighth)::BIGINT - 1) tick FROM ( SELECT MAX(ts) AS maxts FROM times ) q CROSS JOIN options ) q ) beat LEFT JOIN times ON ts = FLOOR(cts) AND cts >= start AND cts < start + value GROUP BY tick ) q CROSS JOIN options ORDER BY tick LIMIT 50

Those are first 50 samples of our digitized music.

Let's see how they look on a graph:

WITH tracks (track, data) AS ( VALUES (1, 'cdefgabC2'), (2, 'CDEFGABC+2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ), scale (shift, note) AS ( VALUES (-9, 'C'), (-8, 'C#'), (-7, 'D'), (-6, 'D#'), (-5, 'E'), (-4, 'F'), (-3, 'F#'), (-2, 'G'), (-1, 'G#'), (0, 'A'), (1, 'A#'), (2, 'B') ), params AS ( SELECT *, 440 * POWER(2, octave + shift / 12.0) AS frequency FROM ( SELECT track, number, UPPER(SUBSTR(digits[1], 1, 1)) || digits[2] AS note, CASE WHEN digits[1] ~ E'[A-G]' THEN LENGTH(digits[1]) ELSE 1 - LENGTH(digits[1]) END AS octave, COALESCE(NULLIF(digits[3], '')::INTEGER, 1) AS value FROM ( SELECT track, ROW_NUMBER() OVER (PARTITION BY track) number, REGEXP_MATCHES(note, '([A-Ga-gp]\+*)(#?)(\d*)') digits FROM notes ) q CROSS JOIN options ) q LEFT JOIN scale USING (note) ), times AS ( SELECT *, generate_series(start, start + value - 1) ts FROM ( SELECT *, SUM(value) OVER (PARTITION BY track ORDER BY number) - value AS start FROM params ) q ) SELECT STRING_AGG(CASE WHEN FLOOR(sample / 30000.00 * 20) = y THEN 'x' ELSE ' ' END, '' ORDER BY tick) r FROM ( SELECT tick, COALESCE((amp * 30000 / tcnt)::INTEGER, 0) sample FROM ( SELECT tick, SUM(SIN(frequency * 2 * PI() * tick / rate) * LEAST((start + value - cts) / fade, (cts - start) / fade, 1)) AS amp FROM ( SELECT *, tick / eighth / rate AS cts FROM ( SELECT *, generate_series(0, (maxts * rate * eighth)::BIGINT - 1) tick FROM ( SELECT MAX(ts) AS maxts FROM times ) q CROSS JOIN options ) q ) beat LEFT JOIN times ON ts = FLOOR(cts) AND cts >= start AND cts < start + value GROUP BY tick ) q CROSS JOIN options WHERE tick % 5 = 0 ORDER BY tick LIMIT 100 ) x CROSS JOIN ( SELECT y FROM generate_series(-20, 20) y ) y GROUP BY y ORDER BY y

[

*Note*]

**:**See original blog post for full outputThose are two superimposed sine waveforms (for С_{3} and C_{4}) which are gradually fading in as the notes start playing.

For the graph, I took 100 samples and used subsampling (only each 5th sample is shown), as the notes' frequencies are relatively low and the waves are too wide to fit on the screen.

### Playing

To play the notes, we need to save our waveforms into a file. Unfortunately, native PostgreSQL query tool, psql, is not binary friendly. To save music into a file, we'll have to employ some Unix shell magic.

We would need to join our samples into a large hex-escaped string, append the RIFF headers and use bash's `echo`

to convert from escaped string to binary.

Let's create a more complex music piece and dump it into a file.

First, let's create a file called `bells.sql`

and paste this query there:

WITH tracks (track, data) AS ( -- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 VALUES (1, 'D2 B2 A2 G2 D4 p2 D1D1 D2 B2 A2 G2 E4 p2 E2 E2 C+2 B2 A2 F#4 p2 F#2 D+2 D+2 C+2 A2 G4 p2 D2 D2 B2 A2 G2 D4 p3 D1 D2 B2 A2 G2 E4 p2 E2 E2 C+2 B2 A2 D+2 D+2 D+2 D+2 E+2 D+2 C+2 A2 G4 D+4 B2 B2 B4 B2 B2 B4 B2 D+2 G3 A1 B8 C+2 C+2 C+3 C+1 C+2 B2 B2 B1 B1 B2 A2 A2 B2 A4 D+4 B2 B2 B4 B2 B2 B4 B2 D+2 G3 A1 B8 C+2 C+2 C+3 C+1 C+2 B2 B2 B1 B1 D+2 D+2 C+2 A2 G6 p2'), (2, 'D2 D2 D2 G2 D6 D1D1 D2 D2 D2 b2 E6 p2 E2 E2 B2 C2 F#6 p2 D+2 D+2 C+2 A2 B8 D2 B2 A2 G2 D6 p2 D2 D2 A2 G2 E6 E2 E2 E2 B2 A2 D+2 D+2 D+2 D+2 E+2 D+2 C+2 A2 G4 D+4 B2 B2 B4 B2 B2 B4 B2 D+2 G3 A1 B8 C+2 C+2 C+3 C+1 C+2 B2 B2 B1 B1 B2 A2 A2 B2 A4 D+4 B2 B2 B4 B2 B2 B4 B2 D+2 G3 A1 B8 C+2 C+2 C+3 C+1 C+2 B2 B2 B1 B1 D+2 D+2 C+2 A2 G6 p2'), (3, 'p2 G2 p2 D2 p2 b2 p2 b2 p2 G2 p2 D2 p2 C2 p2 C2 p2 G2 p2 E2 p2 D2 p2 D2 p2 C+2 p2 F#2 p2 G2 p2 G2 p2 G2 p2 D2 p2 b2 p2 b2 p2 G2 p2 D2 p2 C2 p2 C2 p2 G2 p2 E2 p2 B2 p2 B2 p2 C+2 p2 F#2 p2 D2 C+4 G2 G2 G4 G2 G2 G4 G2 B2 p4 p8 G2 G2 G3 p1 G2 G2 G2 p2 G2 G2 G2 G2 F#4 C+4 G2 G2 G4 G2 G2 G4 G2 B2 p4 p8 G2 G2 G3 p1 G2 G2 G2 G1G1 C+2 C+2 F#2 F#2 D6 p2'), (4, 'p2 B2 p2 b2 p2 g2 p2 g2 p2 B2 p2 G2 p2 g2 p2 g2 p2 C+2 p2 A2 p2 C2 p2 C2 p2 A2 p2 D2 p2 D2 p2 D2 p2 D2 p2 b2 p2 g2 p2 g2 p2 B2 p2 b2 p2 g2 p2 g2 p2 C+2 p2 C2 p2 G2 p2 G2 p2 A2 p2 D2 p2 b2 A4 D2 D2 D2 D2 D2 D2 D2 G2 b3 C1 D4 G2 F2 E2 E2 E3 E1 E2 D2 D2 D1D1 C#2 C#2 C#2 C#2 C4 F#4 D2 D2 D2 D2 D2 D2 D2 G2 b3 C1 D4 G2 F2 E2 E2 E3 E1 E2 D2 D2 p2 F#2 F#2 D2 C2 b6 p2'), (5, 'p8 p8 p8 p8 p8 p2 a2 p2 a2 p2 F#2 p2 C2 p2 b2 p2 b2 p8 p8 p8 p8 p8 p2 D2 p2 D2 p2 F#2 p2 C2 p4 F#4 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8 p8'), (6, 'g4 d4 g4 e2 d2 g4 a2 b2 C4 a2 g2 C4 a4 D4 b2 a2 d4 a4 g2 f#2 e2 d2 g4 d4 g4 e2 d2 g4 a2 b2 C4 a2 g2 C4 a4 D4 d4 D4 d2 f#2 g2 e2 d4 g2 f#2 e2 d2 g2 f#2 e2 d2 g2 f#2 e2 d2 g2 g2 a2 b2 C4 b2 a2 g4 e2 d2 a4 b2 C#2 D2 d2 e2 f#2 g2 f#2 e2 d2 g2 f#2 e2 d2 g2 f#2 e2 d2 g2 g2 a2 b2 C4 b2 a2 g4 D4 d4 D4 g2 d2 g2 p2') ), options AS ( SELECT 44100 AS rate, ( SELECT COUNT(*) FROM tracks ) tcnt, 0.15 AS eighth, 0.0625 AS fade ), notes (track, note) AS ( SELECT track, REGEXP_SPLIT_TO_TABLE(data, '\s*(?=[A-Ga-gp])') FROM tracks ), scale (shift, note) AS ( VALUES (-9, 'C'), (-8, 'C#'), (-7, 'D'), (-6, 'D#'), (-5, 'E'), (-4, 'F'), (-3, 'F#'), (-2, 'G'), (-1, 'G#'), (0, 'A'), (1, 'A#'), (2, 'B') ), params AS ( SELECT *, 440 * POWER(2, octave + shift / 12.0) AS frequency FROM ( SELECT track, number, UPPER(SUBSTR(digits[1], 1, 1)) || digits[2] AS note, CASE WHEN digits[1] ~ E'[A-G]' THEN LENGTH(digits[1]) ELSE 1 - LENGTH(digits[1]) END AS octave, COALESCE(NULLIF(digits[3], '')::INTEGER, 1) AS value FROM ( SELECT track, ROW_NUMBER() OVER (PARTITION BY track) number, REGEXP_MATCHES(note, '([A-Ga-gp]\+*)(#?)(\d*)') digits FROM notes ) q CROSS JOIN options ) q LEFT JOIN scale USING (note) ), times AS ( SELECT *, generate_series(start, start + value - 1) ts FROM ( SELECT *, SUM(value) OVER (PARTITION BY track ORDER BY number) - value AS start FROM params ) q ) SELECT 'RIFF' || ( SELECT STRING_AGG('\x' || TO_HEX((v >> o) & 255), '' ORDER BY o) FROM ( SELECT len * 2 + 36 v, o FROM generate_series(0, 24, 8) o ) q ) || 'WAVEfmt ' || '\x10\x00\x00\x00' || -- subchunk1Size '\x01\x00' || -- audioFormat '\x01\x00' || -- numChannels ( SELECT STRING_AGG('\x' || TO_HEX((v >> o) & 255), '' ORDER BY o) FROM ( SELECT rate v, o FROM generate_series(0, 24, 8) o ) q ) || -- sampleRate ( SELECT STRING_AGG('\x' || TO_HEX((v >> o) & 255), '' ORDER BY o) FROM ( SELECT rate * 2 v, o FROM generate_series(0, 24, 8) o ) q ) || -- byteRate '\x02\x00' || -- blockAlign '\x10\x00' || -- bitsPerSample 'data' || ( SELECT STRING_AGG('\x' || TO_HEX((v >> o) & 255), '' ORDER BY o) FROM ( SELECT len * 2 v, o FROM generate_series(0, 24, 8) o ) q ) || d FROM ( SELECT COUNT(*) AS len, STRING_AGG('\x' || TO_HEX(sample & 255) || '\x' || TO_HEX((sample >> 8) & 255), '' ORDER BY tick) d FROM ( SELECT tick, COALESCE((amp * 30000 / tcnt)::INTEGER, 0) sample FROM ( SELECT tick, SUM(SIN(frequency * 2 * PI() * tick / rate) * LEAST((start + value - cts) / fade, (cts - start) / fade, 1)) AS amp FROM ( SELECT *, tick / eighth / rate AS cts FROM ( SELECT *, generate_series(0, (maxts * rate * eighth)::BIGINT - 1) tick FROM ( SELECT MAX(ts) AS maxts FROM times ) q CROSS JOIN options ) q ) beat LEFT JOIN times ON ts = FLOOR(cts) AND cts >= start AND cts < start + value GROUP BY tick ) q CROSS JOIN options ) q ) q CROSS JOIN options;

Then, let's run this:

echo -ne $(psql postgres -AXtf bells.sql) > bells.wav

On output, we receive a .wav file which we can play with any multimedia player:

Download bells.wav (if your browser does not play it).

**Happy New Year!**

## {{ parent.title || parent.header.title}}

## {{ parent.tldr }}

## {{ parent.linkDescription }}

{{ parent.urlSource.name }}