Skip to content

Instantly share code, notes, and snippets.

@tohuuuuu

tohuuuuu/export_vsmeta_to_xml.sql

Last active February 29, 2024 17:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tohuuuuu/bc1028c56f7677d83840cc18e04836c3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tohuuuuu/bc1028c56f7677d83840cc18e04836c3 to your computer and use it in GitHub Desktop.
Download ZIP
Export result of SQL Query to XML - Synology VideoStation metadata
Raw
export_vsmeta_to_xml.sql
CREATE TEMP TABLE "videodata_tmp" AS
SELECT
v.mapper_id as id,
'home'||v.mapper_id as uniqueid,
right(v.path, position('/' in reverse(v.path)) - 1) as filename,
v.path,
g2.genre,
h.title,
h.record_time,
s.summary
FROM video_file AS v
NATURAL LEFT OUTER JOIN (
SELECT string_agg(g1.gnere, '|') as genre,
mapper_id FROM (SELECT gnere, mapper_id from gnere) as g1
GROUP BY mapper_id) as g2
LEFT JOIN home_video AS h ON h.mapper_id = v.mapper_id
LEFT OUTER JOIN summary AS s ON s.mapper_id = v.mapper_id;
\copy (SELECT table_to_xml('videodata_tmp', false, false, '')) to program 'sed -e ''s/\\n//g'' > /tmp/videodata.xml';
DROP TABLE videodata_tmp;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment