Airplay from Ubuntu 14.10 to Speakers

Question

I have a table tbl1 with two columns col1 and col2 containing strings:

col1    | col2
--------+--------
bar     | foo
foo     | foobar
bar1foo | bar2foo

Corresponding SQL dump:

CREATE TABLE `tbl1` (
  `col1` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `col2` varchar(20) COLLATE latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `tbl1` (`col1`, `col2`) VALUES
('bar', 'foo'),
('foo', 'foobar'),
('bar1foo', 'bar2foo');

The strings of an entry share a common prefix in most cases. I need a query that strips those common prefixes. Expected result:

bar  | foo
     | bar
1foo | 2foo

My approach, so far:

SELECT
SUBSTR(`col1`, 1+GREATEST(LENGTH(`col1`), LENGTH(`col2`)) - CEIL(LENGTH(TRIM(TRAILING '0' FROM HEX(ABS(CONV(HEX(REVERSE(`col1`)),16,10) - CONV(HEX(REVERSE(`col2`)),16,10)))))/2)),
SUBSTR(`col2`, 1+GREATEST(LENGTH(`col1`), LENGTH(`col2`)) - CEIL(LENGTH(TRIM(TRAILING '0' FROM HEX(ABS(CONV(HEX(REVERSE(`col1`)),16,10) - CONV(HEX(REVERSE(`col2`)),16,10)))))/2))
FROM tbl1

Short explanation: The strings are reversed (REVERSE), converted into integers (HEX and CONV), subtracted from each other (- and ABS), converted into hexadecimal representation (HEX), 0's are trimmed from the end (TRIM), the length of this result is subtracted from the length of the longest string (-, LENGTH and GREATEST) and then used by SUBSTR to get the result.

Problems with my approach:

• Does not work with strings longer than 64bit.
• Does not work with strings containing multi-byte characters
• Very lengthy and ugly
• Does not have good performance.

Solution

Takkat provided the answer as a comment, so I am writing this "answer" to close out my question. Basically, AirPlay is not currently functioning with Pulseaudio without hack. BUT if DLNA is available on your speakers, which they are on any old Android phone connected to speakers, you can do this:

sudo add-apt-repository ppa:qos/pulseaudio-dlna
sudo apt-get update && sudo apt-get install pulseaudio-dlna
pulseaudio-dlna

NOTE: the last command turns it on

NOTE2: dependencies for pulseaudio-dlna are listed here

Then you can open either PulseAudioVolumeControl, or just Settings>Volume and select your device.

I'm using BubbleUPnP on my Android. Please note that you need to turn on BubbleUPnP on the Android before you start pulseaudio-dlna, else it won't be able to see the renderer.

Comments

[Takkat]

Pulseaudio only supports the old RAOP protocol (aka AirTunes). The newer RAOP2 protocol needs considerable hacks to get it working. In case your speakers also support DLNA see: askubuntu.com/questions/187086/…

[Rsync]

Takkat, that totally worked! Since I am trying to use Ubuntu to stream music to an old Android phone (which will be connected to speakers), DLNA works great.

[Strawberry]

This isn't what sql is good at. Just sayin'

[christopherlovell]

Been looking for this for hours, great job. Seems to take a long time to pick up audio changes though (~10 seconds), anyone else experiencing this?

[Marc Vanhoomissen]

Great, this works for me to stream audio to my Pionner sound sound system. Only remark: the first command sould be: sudo add-apt-repository ppa:qos/pulseaudio-dlna

[dirdi]

For this solution one needs to know the strings' length a priori. However, it works for me. Thanks!

[dirdi]

I accepted your answer since it provides a solution for strings of arbitrary length.

[crocodile2u]

Seems like, unfortunately, this is still true at least for some devices, in the latest Ubuntu 21.10. I have a SONOS Play 5 speaker. After installing paprefs & pulseaudio-module-raop, then restarting pulseaudio, I get exact same symptoms as the topic-starter. I didn't want the DLNA workaround since I am able to play my music from the iphone, just wanted to have the same ability from the laptop... Sigh...