Happy New Year: composing music in SQL
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 C4 to B4 (in scientific notation), the lowercase would define pitches C3 to B3. 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 D6, and d++ would mean D1. 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
| track | note |
|---|---|
| 1 | c |
| 1 | d |
| 1 | e |
| 1 | f |
| 1 | g |
| 1 | a |
| 1 | b |
| 1 | C2 |
| 2 | C |
| 2 | D |
| 2 | E |
| 2 | F |
| 2 | G |
| 2 | A |
| 2 | B |
| 2 | C+2 |
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 21/12, and the frequency of A4 is exactly 440 Hz. To know frequency of any given pitch, we would need to count how many half-tones apart from A4 it is, raise 21/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
| shift | note |
|---|---|
| -9 | C |
| -8 | C# |
| -7 | D |
| -6 | D# |
| -5 | E |
| -4 | F |
| -3 | F# |
| -2 | G |
| -1 | G# |
| 0 | A |
| 1 | A# |
| 2 | B |
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
| note | track | number | octave | value | shift | frequency |
|---|---|---|---|---|---|---|
| C | 1 | 1 | 0 | 1 | -9 | 261.62556530059863467840 |
| D | 1 | 2 | 0 | 1 | -7 | 293.66476791740756026480 |
| E | 1 | 3 | 0 | 1 | -5 | 329.62755691286992973600 |
| F | 1 | 4 | 0 | 1 | -4 | 349.22823143300388444720 |
| G | 1 | 5 | 0 | 1 | -2 | 391.99543598174929408560 |
| A | 1 | 6 | 0 | 1 | 0 | 440.0000000000000000 |
| B | 1 | 7 | 0 | 1 | 2 | 493.88330125612411183360 |
| C | 1 | 8 | 1 | 2 | -9 | 523.25113060119726935680 |
| C | 2 | 1 | 1 | 1 | -9 | 523.25113060119726935680 |
| D | 2 | 2 | 1 | 1 | -7 | 587.32953583481512052520 |
| E | 2 | 3 | 1 | 1 | -5 | 659.25511382573985947200 |
| F | 2 | 4 | 1 | 1 | -4 | 698.45646286600776889440 |
| G | 2 | 5 | 1 | 1 | -2 | 783.99087196349858817120 |
| A | 2 | 6 | 1 | 1 | 0 | 880.0000000000000000 |
| B | 2 | 7 | 1 | 1 | 2 | 987.76660251224822366280 |
| C | 2 | 8 | 2 | 2 | -9 | 1046.50226120239453870920 |
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
| note | track | number | octave | value | shift | frequency | start | ts |
|---|---|---|---|---|---|---|---|---|
| C | 1 | 1 | 0 | 1 | -9 | 261.62556530059863467840 | 0 | 0 |
| D | 1 | 2 | 0 | 1 | -7 | 293.66476791740756026480 | 1 | 1 |
| E | 1 | 3 | 0 | 1 | -5 | 329.62755691286992973600 | 2 | 2 |
| F | 1 | 4 | 0 | 1 | -4 | 349.22823143300388444720 | 3 | 3 |
| G | 1 | 5 | 0 | 1 | -2 | 391.99543598174929408560 | 4 | 4 |
| A | 1 | 6 | 0 | 1 | 0 | 440.0000000000000000 | 5 | 5 |
| B | 1 | 7 | 0 | 1 | 2 | 493.88330125612411183360 | 6 | 6 |
| C | 1 | 8 | 1 | 2 | -9 | 523.25113060119726935680 | 7 | 7 |
| C | 1 | 8 | 1 | 2 | -9 | 523.25113060119726935680 | 7 | 8 |
| C | 2 | 1 | 1 | 1 | -9 | 523.25113060119726935680 | 0 | 0 |
| D | 2 | 2 | 1 | 1 | -7 | 587.32953583481512052520 | 1 | 1 |
| E | 2 | 3 | 1 | 1 | -5 | 659.25511382573985947200 | 2 | 2 |
| F | 2 | 4 | 1 | 1 | -4 | 698.45646286600776889440 | 3 | 3 |
| G | 2 | 5 | 1 | 1 | -2 | 783.99087196349858817120 | 4 | 4 |
| A | 2 | 6 | 1 | 1 | 0 | 880.0000000000000000 | 5 | 5 |
| B | 2 | 7 | 1 | 1 | 2 | 987.76660251224822366280 | 6 | 6 |
| C | 2 | 8 | 2 | 2 | -9 | 1046.50226120239453870920 | 7 | 7 |
| C | 2 | 8 | 2 | 2 | -9 | 1046.50226120239453870920 | 7 | 8 |
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
| tick | sample |
|---|---|
| 0 | 0 |
| 1 | 4 |
| 2 | 16 |
| 3 | 36 |
| 4 | 64 |
| 5 | 100 |
| 6 | 142 |
| 7 | 192 |
| 8 | 248 |
| 9 | 311 |
| 10 | 378 |
| 11 | 451 |
| 12 | 528 |
| 13 | 609 |
| 14 | 692 |
| 15 | 778 |
| 16 | 866 |
| 17 | 954 |
| 18 | 1042 |
| 19 | 1130 |
| 20 | 1216 |
| 21 | 1299 |
| 22 | 1380 |
| 23 | 1457 |
| 24 | 1529 |
| 25 | 1596 |
| 26 | 1658 |
| 27 | 1713 |
| 28 | 1761 |
| 29 | 1802 |
| 30 | 1835 |
| 31 | 1859 |
| 32 | 1875 |
| 33 | 1883 |
| 34 | 1881 |
| 35 | 1870 |
| 36 | 1849 |
| 37 | 1820 |
| 38 | 1781 |
| 39 | 1734 |
| 40 | 1677 |
| 41 | 1612 |
| 42 | 1540 |
| 43 | 1459 |
| 44 | 1372 |
| 45 | 1278 |
| 46 | 1178 |
| 47 | 1073 |
| 48 | 963 |
| 49 | 850 |
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
| r |
|---|
| x |
| x x |
| x x |
| xx |
| x |
| x x x |
| x x |
| x x |
| x x |
| xx x |
| x x |
| x x xx x |
| x xx x x |
| x x x x x x |
| xxxxx x x x x x x |
| xxxxx xxx xxxxxx x x x |
| xxxx x x x x x |
| x xx x x x |
| x xx |
| x x x |
| x x |
| x x x |
| x x |
| x |
| x |
| x |
| x |
| xx |
Those are two superimposed sine waveforms (for ะก3 and C4) 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).
Previous New Year posts:
Subscribe in a reader
wow, you are simply a wizard !!
sarfraz
1 Jan 15 at 11:08
@sarfraz: thanks!
Quassnoi
2 Jan 15 at 22:20
incredible…. now i know how shazam works!!!
jajajaj
tierrarara
5 Jan 15 at 23:27
Only you could pull this off, Alex! Only you.
IanC
15 Feb 15 at 10:25
@IanC: thanks!
Quassnoi
16 Feb 15 at 14:49