Oke I build a even better database system with levenshtein function.
And if the function does not find it a spotify api search takes place. The result is stored for offline use.
After a podcast is ended the music plays again. (but that is part of my grand yet to document and in progress snapcast system)
the only thing of the change is that the python script does not run on HA so i have a ssh connection to a linux server. I use a unique ssh key for the command.
added php scripts to get a latest podcast is also a remote system.
alias: speaker slaapkamer voice
description: ""
trigger:
- platform: conversation
command:
- speaker[s] [s]la[a]pkamer speel[t] {artist}
- Spiekerslaapkamer speel[t] {artist}
- Speaker's loudcomers speel[t] {artist}
- Spiekenslapkamer speel[t] {artist}
- Speaker's slaapkamer speel[t] {artist}
- Speaker slotkamer speel[t] {artist}
- Speaker's Laapkamer speel[t] {artist}
- Spieken slapkamer speel[t] {artist}
- Spiekers slaapkamer speel[t] {artist}
- " Speaker's Laadkamer speel[t] {artist}"
- " Speaker's Laadkamer speel[t] {artist}"
- Speaker slaaptamer speel[t] {artist}
- Spieken [s]la[a]pkamer speel[t] {artist}
- Spieke[r][s] Slauwkamer speel[t] {artist}
condition: []
action:
- service: input_text.set_value
metadata: {}
data:
value: "{{trigger.slots.artist }}"
target:
entity_id: input_text.mpd_playlist
- service: shell_command.mpd_playlist
metadata: {}
data: {}
response_variable: result
- choose:
- conditions:
- condition: template
value_template: "{{ (result.stdout|from_json).sort== \"playlist\" }}"
sequence:
- service: media_player.play_media
metadata: {}
data:
media_content_type: "{{(result.stdout|from_json).type }}"
media_content_id: "{{ (result.stdout|from_json).source }} "
target:
entity_id: media_player.bedroom_speaker
- service: media_player.select_source
metadata: {}
data:
source: mpd_bedroom
target:
entity_id: media_player.vision_snapcast_client
- service: media_player.volume_mute
target:
entity_id: media_player.speaker_slaapkamer
data:
is_volume_muted: false
- conditions:
- condition: template
value_template: "{{ (result.stdout|from_json).sort == \"podcast\" }}"
sequence:
- service: input_text.set_value
metadata: {}
data:
value: "{{ state_attr('media_player.vision_snapcast_client','source') }}"
target:
entity_id: input_text.pod_bedroom
- service: media_player.play_media
metadata: {}
data:
media_content_id: "{{ (result.stdout|from_json).source }}"
media_content_type: "{{(result.stdout|from_json).type }}"
target:
entity_id: media_player.bedroom_speaker_pod
- service: media_player.select_source
metadata: {}
data:
source: mpd_bedroom_pod
target:
entity_id: media_player.vision_snapcast_client
- service: media_player.volume_mute
target:
entity_id: media_player.speaker_slaapkamer
data:
is_volume_muted: false
mode: single
configuration.yaml
shell_command:
mpd_playlist: /config/bin/mpd_search.sh '{{ states('input_text.mpd_playlist') }}'
/config/bin/mpd_search.sh
#!/bin/bash
echo "$@" | ssh -i /config/ssh/id_rsa_mpdir user@linux -T
ON the linux box
.ssh/authorized_keys (use ssh-copy-id)
command="./bin/mpd_search.py \"$(cat)\"",restrict ssh-ed25519 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA root@123456-ssh
daft@doorman:
#!/usr/bin/python3
mport MySQLdb
import json
import sys
import base64
import requests
# Replace with your own Spotify API credentials
client_id = ''
client_secret = ''
def search_artist(query):
access_token = get_access_token(client_id, client_secret)
if access_token:
search_url = 'https://api.spotify.com/v1/search'
params = {
'q': query,
'type': 'artist'
}
headers = {
'Authorization': 'Bearer ' + access_token
}
response = requests.get(search_url, params=params, headers=headers)
data = response.json()
if 'artists' in data and 'items' in data['artists'] and data['artists']['items']:
return data['artists']['items'][0]
return None
def get_access_token(client_id, client_secret):
token_url = 'https://accounts.spotify.com/api/token'
headers = {
'Authorization': 'Basic ' + base64.b64encode((client_id + ':' + client_secret).encode()).decode()
}
payload = {
'grant_type': 'client_credentials'
}
response = requests.post(token_url, headers=headers, data=payload)
data = response.json()
return data.get('access_token')
def lookup_artist(search):
conn = MySQLdb.connect(
host="localhost",
user="karin",
password="BTy!KagQKwc2Orek",
database="ha_music"
)
# Create a cursor
artist = search_artist(search)
cursor = conn.cursor()
query = "SELECT `p`.`name`, `p`.`playlist`, `t`.`type`, `p`.`id` \
FROM playlists AS p \
JOIN `ha_music`.`type` AS t ON p.type = t.id \
JOIN `playlist_weblookup` AS w ON p.id = w.playlist_id \
WHERE `w`.`web_lookup` = %s"
cursor.execute(query, (str(artist['name']),))
result = cursor.fetchone()
if result:
query_add = "INSERT INTO playlist_alias (`playlist_id`, `alias`) VALUES (%s,%s)"
cursor.execute(query_add, (result[3],str(search),))
conn.commit()
cursor.close()
conn.close()
return json.dumps({"sort": "playlist", "source": result[1], "type": result[2]})
else:
return False
def find_playlist(artist_name):
# Connect to MySQL database
conn = MySQLdb.connect(
host="localhost",
user="karin",
password="BTy!KagQKwc2Orek",
database="ha_music"
)
# Create a cursor
cursor = conn.cursor()
# Query to find the playlist for the given artist name
# playlist_query = (
# "SELECT * FROM `playlist_view` WHERE `name` LIKE %s LIMIT 1"
# )
playlist_query = (
"SELECT * , levenshtein( %s,`name`) as levenshtein FROM `playlist_view` WHERE levenshtein( %s,`name`) BETWEEN 0 AND 1 ORDER BY levenshtein LIMIT 1"
)
cursor.execute(playlist_query, (artist_name,artist_name,))
# cursor.execute(playlist_query, (artist_name ,artist_name',))
playlist_result = cursor.fetchone()
# If playlist is found, return JSON
if playlist_result:
cursor.close()
conn.close()
return json.dumps({"sort": "playlist", "source": playlist_result[1], "type": playlist_result[2]})
# Query to find the podcast for the given artist name
podcast_query = (
"SELECT *, levenshtein( %s,`name`) as levenshtein FROM `podcast_view` WHERE levenshtein( %s,`name`) BETWEEN 0 AND 1 ORDER BY levenshtein LIMIT 1"
)
# podcast_query = (
# "SELECT * FROM `podcast_view` WHERE `name` LIKE %s LIMIT 1"
# )
cursor.execute(podcast_query, (artist_name,artist_name,))
podcast_result = cursor.fetchone()
# Close cursor and connection
cursor.close()
conn.close()
# If podcast is found, return JSON
if podcast_result:
return json.dumps({"sort": "podcast", "source": podcast_result[1], "type": podcast_result[2]})
lookup = lookup_artist(artist_name)
if lookup:
return lookup
else:
return None
if __name__ == "__main__":
# if len(sys.argv) != 2:
# print("Usage: {} <artist_name>".format(sys.argv[0]))
# sys.exit(1)
artist_name = ' '.join(sys.argv[1:])
if not artist_name:
print("Usage: {} <artist_name>".format(sys.argv[0]))
sys.exit(1)
json_result = find_playlist(artist_name)
if json_result:
print(json_result)
else:
print("Playlist or podcast not found for artist:", artist_name)
SQL
-- phpMyAdmin SQL Dump
-- version 5.2.1deb1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Gegenereerd op: 19 feb 2024 om 15:26
-- Serverversie: 10.11.6-MariaDB-0+deb12u1
-- PHP-versie: 8.2.7
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `ha_music`
--
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `playlists`
--
CREATE TABLE `playlists` (
`id` int(10) UNSIGNED NOT NULL,
`name` varchar(50) NOT NULL,
`playlist` varchar(100) NOT NULL,
`type` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `playlist_alias`
--
CREATE TABLE `playlist_alias` (
`id` int(10) UNSIGNED NOT NULL,
`playlist_id` int(11) NOT NULL,
`alias` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `podcasts`
--
CREATE TABLE `podcasts` (
`id` int(10) UNSIGNED NOT NULL,
`name` varchar(100) NOT NULL,
`url` varchar(300) NOT NULL,
`type` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `podcast_alias`
--
CREATE TABLE `podcast_alias` (
`id` int(10) UNSIGNED NOT NULL,
`podcast_id` int(11) UNSIGNED NOT NULL,
`alias` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `type`
--
CREATE TABLE `type` (
`id` int(10) UNSIGNED NOT NULL,
`type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Gegevens worden geƫxporteerd voor tabel `type`
--
INSERT INTO `type` (`id`, `type`) VALUES
(1, 'music'),
(2, 'playlist');
-- --------------------------------------------------------
CREATE VIEW `playlist_view` AS SELECT `p`.`name` AS `name`, `p`.`playlist` AS `playlist`, `t`.`type` AS `type` FROM (`playlists` `p` join `type` `t` on(`p`.`type` = `t`.`id`))union select `a`.`alias` AS `name`,`p`.`playlist` AS `playlist`,`t`.`type` AS `type` from ((`playlist_alias` `a` join `playlists` `p` on(`p`.`id` = `a`.`playlist_id`)) join `type` `t` on(`p`.`type` = `t`.`id`)) ;
CREATE VIEW `podcast_view` AS SELECT `p`.`name` AS `name`, `p`.`url` AS `url`, `t`.`type` AS `type` FROM (`podcasts` `p` join `type` `t` on(`p`.`type` = `t`.`id`))union select `a`.`alias` AS `name`,`p`.`url` AS `url`,`t`.`type` AS `type` from ((`podcast_alias` `a` join `podcasts` `p` on(`p`.`id` = `a`.`podcast_id`)) join `type` `t` on(`p`.`type` = `t`.`id`)) ;
--
-- Indexen voor geƫxporteerde tabellen
--
--
-- Indexen voor tabel `playlists`
--
ALTER TABLE `playlists`
ADD PRIMARY KEY (`id`);
--
-- Indexen voor tabel `playlist_alias`
--
ALTER TABLE `playlist_alias`
ADD PRIMARY KEY (`id`);
--
-- Indexen voor tabel `podcasts`
--
ALTER TABLE `podcasts`
ADD PRIMARY KEY (`id`);
--
-- Indexen voor tabel `podcast_alias`
--
ALTER TABLE `podcast_alias`
ADD PRIMARY KEY (`id`);
--
-- Indexen voor tabel `type`
--
ALTER TABLE `type`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT voor geƫxporteerde tabellen
--
--
-- AUTO_INCREMENT voor een tabel `playlists`
--
ALTER TABLE `playlists`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,;
--
-- AUTO_INCREMENT voor een tabel `playlist_alias`
--
ALTER TABLE `playlist_alias`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT voor een tabel `podcasts`
--
ALTER TABLE `podcasts`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT voor een tabel `podcast_alias`
--
ALTER TABLE `podcast_alias`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT voor een tabel `type`
--
ALTER TABLE `type`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
levenshtein function
DELIMITER $
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
-- max strlen=255
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1;
END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END$
DELIMITER ;
php code to get the lastest episode of a podcast
GNU nano 7.2 latenightlinux.php
<?php
$xmlurl = "https://latenightlinux.com/feed/mp3";
$xml = simplexml_load_file($xmlurl);
if (@$_GET['info']) {
$title = $xml->channel->item[0]->title;
$timestamp = $xml->channel->item[0]->pubDate;
$date = DateTime::createFromFormat('D, d M Y H:i:s O', $timestamp);
// Get the current date and time
$now = new DateTime();
// Calculate the difference in days
$interval = $now->diff($date);
$daysAgo = $interval->format('%a');
echo "$title $daysAgo dagen geleden";
die();
}
$url = $xml->channel->item[0]->enclosure['url'][0];
header("Location: $url");
?>
v0.0001 post
I saw a great example to use voice assistant with music assistant with AI support. But i want to keep everything local so i made my own with out AI.
It uses a chatgtp generated bash script that greps the list of playlists (artists in my system) from a txt file and gives back the best result.
so that you can give a partial name or a name with typos.
#!/bin/bash
# Check if the argument is provided
if [ $# -eq 0 ]; then
echo "Usage: $0 <search_query>"
exit 1
fi
# Read the list of artists from the file
artists_file="/config/artists.txt"
# Function to find matching artist names
find_artist() {
local query="$1"
# Convert query to lowercase for case-insensitive matching
query_lower=$(echo "$query" | tr '[:upper:]' '[:lower:]')
# Iterate over the list of artists
while IFS= read -r artist; do
# Convert artist name to lowercase for case-insensitive matching
artist_lower=$(echo "$artist" | tr '[:upper:]' '[:lower:]')
# Check if the query is a substring of the artist name
if [[ "$artist_lower" == *"$query_lower"* ]]; then
echo "$artist"
return 0
fi
done < "$artists_file"
# If no match found, return non-zero exit status
return 1
}
# Call the function with the provided argument
if find_artist "$1"; then
exit 0
else
echo "No matching artist found."
exit 1
fi
alias: speaker slaapkamer voice
description: ""
trigger:
- platform: conversation
command:
- speaker slaapkamer speel {artist}
- Spiekerslaapkamer speel {artist}
- Speaker slaapkamers speel {artist}
- Speaker's loudcomers speel {artist}
- Spiekenslapkamer speel {artist}
- Speaker's slaapkamer speel {artist}
- Speaker slotkamer speel {artist}
- Speaker's Laapkamer speel {artist}
- Spieken slapkamer speel {artist}
- Speaker slaapkamer speelt {artist}
condition: []
action:
- service: input_text.set_value
metadata: {}
data:
value: "{{trigger.slots.artist }}"
target:
entity_id: input_text.mpd_playlist
- service: shell_command.mpd_playlist
metadata: {}
data: {}
response_variable: result
- service: media_player.play_media
metadata: {}
data:
media_content_type: playlist
media_content_id: mpddir_{{result.stdout}}
target:
entity_id: media_player.bedroom_speaker
- service: media_player.select_source
metadata: {}
data:
source: mpd_bedroom
target:
entity_id: media_player.vision_snapcast_client
- service: media_player.volume_mute
target:
entity_id: media_player.speaker_slaapkamer
data:
is_volume_muted: false
mode: single
