[RDD] SQL script

John Hodgson mail at johnhodgson.co.uk
Fri May 20 03:57:20 EDT 2016


Hi David, 

This looks to be just what I need! I am not great with the SQL side of
things so you have saved me from a big headache.

Many thanks
John

-----Original Message-----
From: David Klann [mailto:dklann at linux.com] 
Sent: 19 May 2016 16:00
To: John Hodgson; rivendell-dev at lists.rivendellaudio.org
Subject: Re: [RDD] SQL script

Hi John,

Here's what I've used in the past to generate a CSV file from the CART table
(adapted with your column names). From a command line on a system with
access to your Rivendell database:

mysql -u rduser -pletmein -e "select \
a.NUMBER, a.TITLE, a.ARTIST, u.TALK_END_POINT/1000, u.LENGTH/1000 \ from
CART as a \ left join CUTS as u on (a.NUMBER = u.CART_NUMBER) \ where
a.GROUP='MUSIC' \ into outfile '/tmp/cart-$(date +%F).csv' \ fields
terminated by ',' \ enclosed by '\"' \ lines terminated by '\n'"

I'm assuming that the column "TALK_END_POINT" is what you're using to
indicate the length of the intro. This in turn assumes that
"TALK_START_POINT" is set to the beginning of the cut. Your mileage may
vary...

The length columns are stored as number of milliseconds, so I divide by
1000 to save the values as seconds. Exercise for the reader to convert that
to minutes and seconds...

The "outfile" uses the current date (in YYYY-MM-DD format) as part of the
file name. You'll need to make sure that file name does NOT exist before
running the command otherwise mysql will complain.

Hope this helps!

  ~David Klann


On 05/19/2016 09:10 AM, John Hodgson wrote:
> Hi everyone,
> 
> I'm wondering if you can help me?
> 
> I am trying to export our audio library into a CSV file...  The info I 
> need
> is:
> 
> CART NUMBER, FILENAME, TITLE, ARTIST, INTRO LENGTH, CUT LENGTH
> 
> The issue I have with the rdlibrary report I can produce is that I can 
> get all I need apart from the INTRO LENGTH I can make the file name up 
> from the cut number and cart number that I get out.
> 
> If anyone has an SQL script I can run or something similar I would be 
> very grateful.
> 
> Thanks in advance.
> 
> John
> 
> _______________________________________________
> Rivendell-dev mailing list
> Rivendell-dev at lists.rivendellaudio.org
> http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
> 




More information about the Rivendell-dev mailing list