EXPLAIN EXTENDED

How to create fast database queries

Happy New Year!

with 5 comments

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).

Happy New Year!

Previous New Year posts:

Written by Quassnoi

December 31st, 2014 at 11:00 pm

Posted in PostgreSQL

5 Responses to 'Happy New Year!'

Subscribe to comments with RSS

  1. wow, you are simply a wizard !!

    sarfraz

    1 Jan 15 at 11:08

  2. @sarfraz: thanks!

    Quassnoi

    2 Jan 15 at 22:20

  3. incredible…. now i know how shazam works!!!
    jajajaj

    tierrarara

    5 Jan 15 at 23:27

  4. Only you could pull this off, Alex! Only you.

    IanC

    15 Feb 15 at 10:25

  5. @IanC: thanks!

    Quassnoi

    16 Feb 15 at 14:49

Leave a Reply