Re: [RDD] Updating scheduler codes via SQL

[Dave]

The ultimate solution was to use curl and rdxport.cgi. A simple shell 
script such as below did the trick:


curl -d 
"COMMAND=25&LOGIN_NAME=user&PASSWORD=&TICKET=&CART_NUMBER=10001&CODE=90s" 
localhost/rd-bin/rdxport.cgi
curl -d 
"COMMAND=25&LOGIN_NAME=user&PASSWORD=&TICKET=&CART_NUMBER=10003&CODE=90s" 
localhost/rd-bin/rdxport.cgi
curl -d 
"COMMAND=25&LOGIN_NAME=user&PASSWORD=&TICKET=&CART_NUMBER=10004&CODE=90s" 
localhost/rd-bin/rdxport.cgi


I modified over 1000 songs this way. I did a cart dump to csv file from 
rdlibrary and then wrote an app in object pascal (Lazarus) to parse the 
csv file, determine the scheduler code for each cart, and write each 
line to a text file to execute as a script.


On 7/14/2021 3:19 PM, Dave B wrote:

I totally forgot about the web API. A safer approach indeed!  Thanks!

On Jul 14, 2021 12:18 PM, Fred Gleason  wrote:

On Jul 14, 2021, at 05:14, Alejandro olivan Alvarez
mailto:alejandro.olivan.alva...@gmail.com>> wrote:

I have messed a little with rivendell database, quite
successfully, but only for query/read operations ...
altering/writing directly the database of such a complex
application is for the brave :-)


Just to amplify Alejandro’s point: directly modifying the
Rivendell database is NOT A SUPPORTED METHOD for making changes in
Rivendell! Specifically, the database schema CAN and DOES change
between different versions of Rivendell; meaning that the update
method that worked perfectly three months ago could end up NUKING
YOUR DATABASE next time around.

A much better and safer method for making these sorts of
site-specific bulk changes is to write a script that uses
Rivendell’s WebAPI. See:

http://opsguide.rivendellaudio.org/apis/web_api.pdf


The Web API is a documented, public and above all stable
interface; using it will allow you to write scripts that won’t
break across different versions of Rivendell.

N.B. — If you insist on writing changes directly to the DB, be
sure that you have a known good backup of it first. The sanity and
gray hair (not to mention job) that you save may be your own.

Cheers!


|-|
| Frederick F. Gleason, Jr. |             Chief Developer        
    |
|                           |             Paravel Systems        
    |
|-|
|           Beware of Programmers who carry screwdrivers.            |
|                   |
|                                             -- Leonard Brandwein   |
|-|



___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

Re: [RDD] Updating scheduler codes via SQL

[Dave B]

I totally forgot about the web API. A safer approach indeed!  Thanks!On Jul 14, 2021 12:18 PM, Fred Gleason  wrote:On Jul 14, 2021, at 05:14, Alejandro olivan Alvarez  wrote:I have messed a little with rivendell database, quite successfully, but only for query/read operations ... altering/writing directly the database of such a complex application is for the brave :-)Just to amplify Alejandro’s point: directly modifying the Rivendell database is NOT A SUPPORTED METHOD for making changes in Rivendell! Specifically, the database schema CAN and DOES change between different versions of Rivendell; meaning that the update method that worked perfectly three months ago could end up NUKING YOUR DATABASE next time around.A much better and safer method for making these sorts of site-specific bulk changes is to write a script that uses Rivendell’s WebAPI. See:	http://opsguide.rivendellaudio.org/apis/web_api.pdfThe Web API is a documented, public and above all stable interface; using it will allow you to write scripts that won’t break across different versions of Rivendell.N.B. — If you insist on writing changes directly to the DB, be sure that you have a known good backup of it first. The sanity and gray hair (not to mention job) that you save may be your own.Cheers!|-|| Frederick F. Gleason, Jr. |             Chief Developer             ||                           |             Paravel Systems             ||-||            Beware of Programmers who carry screwdrivers.            ||                                                                     ||                                              -- Leonard Brandwein   ||-|___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

Re: [RDD] Updating scheduler codes via SQL

[Alejandro olivan Alvarez]

On 7/14/21 6:18 PM, Fred Gleason wrote:
On Jul 14, 2021, at 05:14, Alejandro olivan Alvarez 
> wrote:


I have messed a little with rivendell database, quite successfully, 
but only for query/read operations ... altering/writing directly the 
database of such a complex application is for the brave :-)


Just to amplify Alejandro’s point: directly modifying the Rivendell 
database is NOT A SUPPORTED METHOD for making changes in Rivendell! 
Specifically, the database schema CAN and DOES change between 
different versions of Rivendell; meaning that the update method that 
worked perfectly three months ago could end up NUKING YOUR DATABASE 
next time around.


A much better and safer method for making these sorts of site-specific 
bulk changes is to write a script that uses Rivendell’s WebAPI. See:


http://opsguide.rivendellaudio.org/apis/web_api.pdf 



Man, I missed the API completely during all this time ... thank you for 
the remind and the pdf link!




The Web API is a documented, public and above all stable interface; 
using it will allow you to write scripts that won’t break across 
different versions of Rivendell.


N.B. — If you insist on writing changes directly to the DB, be sure 
that you have a known good backup of it first. The sanity and gray 
hair (not to mention job) that you save may be your own.


Cheers!


|-|
| Frederick F. Gleason, Jr. |             Chief Developer         |
|                           |             Paravel Systems         |
|-|
|           Beware of Programmers who carry screwdrivers.       |
|         |
|                                             -- Leonard Brandwein   |
|-|


___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

Re: [RDD] Updating scheduler codes via SQL

[Fred Gleason]

On Jul 14, 2021, at 05:14, Alejandro olivan Alvarez 
 wrote:

> I have messed a little with rivendell database, quite successfully, but only 
> for query/read operations ... altering/writing directly the database of such 
> a complex application is for the brave :-)

Just to amplify Alejandro’s point: directly modifying the Rivendell database is 
NOT A SUPPORTED METHOD for making changes in Rivendell! Specifically, the 
database schema CAN and DOES change between different versions of Rivendell; 
meaning that the update method that worked perfectly three months ago could end 
up NUKING YOUR DATABASE next time around.

A much better and safer method for making these sorts of site-specific bulk 
changes is to write a script that uses Rivendell’s WebAPI. See:

http://opsguide.rivendellaudio.org/apis/web_api.pdf

The Web API is a documented, public and above all stable interface; using it 
will allow you to write scripts that won’t break across different versions of 
Rivendell.

N.B. — If you insist on writing changes directly to the DB, be sure that you 
have a known good backup of it first. The sanity and gray hair (not to mention 
job) that you save may be your own.

Cheers!


|-|
| Frederick F. Gleason, Jr. | Chief Developer |
|   | Paravel Systems |
|-|
|Beware of Programmers who carry screwdrivers.|
| |
|  -- Leonard Brandwein   |
|-|

___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

Re: [RDD] Updating scheduler codes via SQL

[Jarret Whetstone]

When inserting via a select I don't think you need to use a subquery or
values statement..
see:
https://stackoverflow.com/questions/9422529/mysql-how-do-you-insert-into-a-table-with-a-select-subquery-returning-multiple-r

insert into CART_SCHED_CODES (CART_NUMBER, SCHED_CODE)
select NUMBER, '70s' from CART
where YEAR > str_to_date('1969', '%Y) and YEAR < str_to_date('1980', '%Y')

Of course, backup your database first :)

Jarret


On Wed, Jul 14, 2021 at 5:14 AM Alejandro olivan Alvarez <
alejandro.olivan.alva...@gmail.com> wrote:

> HI!
>
> I have messed a little with rivendell database, quite successfully, but
> only for query/read operations ... altering/writing directly the
> database of such a complex application is for the brave :-)
>
> On 7/13/21 10:14 PM, Dave B wrote:
> > I want to bulk update the scheduler codes for my songs based on the YEAR
> field, but I get an error when attempting to add songs to the
> CART_SCHED_CODES table. Sample SQL to add my 70s scheduler code to songs is:
> >
> > Insert into CART_SCHED_CODES (CART_NUMBER, SCHED_CODE) values ((select
> NUMBER from CART where YEAR > str_to_date(‘1969’, ‘%Y’) and YEAR <
> str_to_date(‘1980’, ‘%Y’)), ‘70s’);
>
> At first glance, I bet you can just use LIMIT on the subquery... and the
> specific (first) problem you encountered should be bypassed BUT... I
> don't think that being a good idea.
>
> At first glance, I see you're trying to insert TWO values into TWO
> fields (CART_NUMBER, SCHED_CODE) whereas you're subquerying for just ONE
> value (NUMBER) so I bet you'll need an additional field or the DBEngine
> may complain on that too.
>
> Then for the subquery itself, consider that If there are more than one
> result/row, you should decide which one is the right one... the DBEngine
> will never consider itself powerfull enough to impersonate you and take
> that decission by you... you have to tell it the criteria for such
> decission and the criteria must throw a single row, so you'll never
> could blame on the DBEngine for selecting the wrong one :-P
>
> Are all the subquery results equal? well... in that case the LIMIT trick
> could be enough, why not, but otherwise, you have to, at least try
> GROUPing BY and/or ORDERing BY the subquery results following your
> intended logic (and if the logic is complex maybe grouping/ordering
> wouldn't be enough... JOINining, etc... hopefully GROUing/ORDERing BY
> will be enough)
>
> My advice is that, first, you JUST work on the subquery ( just SELECT)
> playing with grouping, ordering, limiting (or whatever be necessary)
> until you get just the TWO unequivocal values you want to insert, from a
> SINGLE unequivocal result row... then paste that final query as the
> subquery of your 'parent' query and it should execute.
>
> Good luck, and best regards!
>
> >
> > This throws an error because the sub query returns more than one row.
> Any suggestions for correct SQL or a better way of doing this?
> >
> > Thanks,
> > Dave
> >
> > Sent from my iPad
> > ___
> > Rivendell-dev mailing list
> > Rivendell-dev@lists.rivendellaudio.org
> > http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
> ___
> Rivendell-dev mailing list
> Rivendell-dev@lists.rivendellaudio.org
> http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
>
___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

Re: [RDD] Updating scheduler codes via SQL

[Alejandro olivan Alvarez]

HI!

I have messed a little with rivendell database, quite successfully, but 
only for query/read operations ... altering/writing directly the 
database of such a complex application is for the brave :-)


On 7/13/21 10:14 PM, Dave B wrote:

I want to bulk update the scheduler codes for my songs based on the YEAR field, 
but I get an error when attempting to add songs to the CART_SCHED_CODES table. 
Sample SQL to add my 70s scheduler code to songs is:

Insert into CART_SCHED_CODES (CART_NUMBER, SCHED_CODE) values ((select NUMBER from 
CART where YEAR > str_to_date(‘1969’, ‘%Y’) and YEAR < str_to_date(‘1980’, 
‘%Y’)), ‘70s’);


At first glance, I bet you can just use LIMIT on the subquery... and the 
specific (first) problem you encountered should be bypassed BUT... I 
don't think that being a good idea.


At first glance, I see you're trying to insert TWO values into TWO 
fields (CART_NUMBER, SCHED_CODE) whereas you're subquerying for just ONE 
value (NUMBER) so I bet you'll need an additional field or the DBEngine 
may complain on that too.


Then for the subquery itself, consider that If there are more than one 
result/row, you should decide which one is the right one... the DBEngine 
will never consider itself powerfull enough to impersonate you and take 
that decission by you... you have to tell it the criteria for such 
decission and the criteria must throw a single row, so you'll never 
could blame on the DBEngine for selecting the wrong one :-P


Are all the subquery results equal? well... in that case the LIMIT trick 
could be enough, why not, but otherwise, you have to, at least try 
GROUPing BY and/or ORDERing BY the subquery results following your 
intended logic (and if the logic is complex maybe grouping/ordering 
wouldn't be enough... JOINining, etc... hopefully GROUing/ORDERing BY 
will be enough)


My advice is that, first, you JUST work on the subquery ( just SELECT) 
playing with grouping, ordering, limiting (or whatever be necessary) 
until you get just the TWO unequivocal values you want to insert, from a 
SINGLE unequivocal result row... then paste that final query as the 
subquery of your 'parent' query and it should execute.


Good luck, and best regards!



This throws an error because the sub query returns more than one row. Any 
suggestions for correct SQL or a better way of doing this?

Thanks,
Dave

Sent from my iPad
___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev

___
Rivendell-dev mailing list
Rivendell-dev@lists.rivendellaudio.org
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev