Author Topic: SQL statement for MC Plugin  (Read 2718 times)

0 Members and 1 Guest are viewing this topic.

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
SQL statement for MC Plugin
« on: April 12, 2009, 06:33:48 pm »
I'm taking this conversation public, where maybe someone will have mercy on us... :-\

Code: [Select]
SELECT PEOPLE."name" FROM PEOPLE INNER JOIN CAREER ON (CAREER."pid" = PEOPLE."pid")
WHERE ("ctype" = Y) AND ("mid" = X)

I can add CAREER."role" to get the role as well, but I don't see how to number the rows (i.e., so the end result can be something like 1. [actor] - [role]). We need to do this because MC can't handle ordered lists.

Code: [Select]
Select
  PEOPLE."name",
  CAREER."role"
From
  PEOPLE Inner Join
  CAREER On (CAREER."pid" = PEOPLE."pid")
Where
  CAREER."ctype" = 0 And
  CAREER."mid" = 111

There is no easy way of numbering result rows in Firebird.
I do not really understand:
-first one column only needed to be retrieved, now 3 columns are needed???
-normally there is no need of numbering rows as this task is usually accomplished by the database client library and/or programmatically while analyzing results

Sorry—I'd didn't mean to confuse the matter with my very first attempt at a SQL statement. And this is for raldo's plugin, so I don't know for sure exactly what is required. But to accommodate credits in regular alphabetically-sorted list fields in MC's flat database, I believe we need to import two fields. Using Actors as an example, that would be [name] in Actors and "1. [actor] - [role]" in Actor Credits. Then Actors can be used to search and filter movies by actor, and Actor Credits can be used to properly display credits in a movie information context. I realized my query would produce three columns when one is required. I assumed Raldo would know how to concatenate them.

Judging from a screenshot raldo provided, it appears he is using SQL statements directly in a field mapping. That implies to me there is no opportunity to do anything programmatically. In researching this, I see there is a technique for ranking results within a query, but we don't seem to have anything to rank. Can the ordering of the credits somehow be included in the query, or not—because it's just an index and not a field?

Is there a way to concatenate fields in a query, or is that also something normally done programmatically?
In Firebird SQL, concatenation is denoted by a "||", so now we have...

Code: [Select]
Select
  PEOPLE."name" || ' - ' || CAREER."role"
From
  PEOPLE Inner Join
  CAREER On (CAREER."pid" = PEOPLE."pid")
Where
  CAREER."ctype" = 0 And
  CAREER."mid" = X
« Last Edit: April 12, 2009, 08:04:26 pm by rick.ca »

Offline raldo

  • Power User
  • ****
  • Posts: 140
    • View Profile
Re: SQL statement for MC Plugin
« Reply #1 on: April 17, 2009, 08:04:07 am »
Did you notice that as long as you don't edit the listed fields in JRiver MC, the ordering stays as the original ordering?

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: SQL statement for MC Plugin
« Reply #2 on: April 17, 2009, 08:28:41 am »
No—but I suppose that's not surprising. So when the credits order is lost, it will be the user's fault. That'll go over well. Are you mentioning this because you haven't figured out how to number the results? If so, then this would be when the non-programmer (me) says, "What could possibly be so difficult about numbering the results?!" ;D

Offline raldo

  • Power User
  • ****
  • Posts: 140
    • View Profile
Re: SQL statement for MC Plugin
« Reply #3 on: April 17, 2009, 11:34:07 pm »
No—but I suppose that's not surprising. So when the credits order is lost, it will be the user's fault. That'll go over well. Are you mentioning this because you haven't figured out how to number the results? If so, then this would be when the non-programmer (me) says, "What could possibly be so difficult about numbering the results?!" ;D
Smartass :) There will be numbering.

By the way, doing some progress on the plugin. The issue I'm struggling with now is "how do you communicate between MC and PvdImport which album do you want to updated".

Mind you, several users have requested "access" to menus and right click context menus from plugins. Here's how I'll do it: I'll make a simple .exe file which passes the selected file along to the plugin. "Interprocess communication". You'll add a reference to this application using "Send to external", with [name] as an argument. As I wrote previously in the MC forums, It's cumbersome and will need instructions.

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: SQL statement for MC Plugin
« Reply #4 on: April 18, 2009, 12:00:46 am »
Quote
By the way, doing some progress on the plugin. The issue I'm struggling with now is "how do you communicate between MC and PvdImport which album do you want to updated".

It hadn't occurred to me that might be an issue. Why wouldn't one want all records updated?

Quote
Mind you, several users have requested "access" to menus and right click context menus from plugins.

For what purpose?

Quote
As I wrote previously in the MC forums, It's cumbersome and will need instructions.

Is this being discussed somewhere in the MC Forums that I'm unaware of? Maybe that's why I'm lost. :-\

BTW, nostra hasn't yet responded to the issues I described Strange event with "Release" field, but I'm a little bit concerned about the possibility queries to the PVD database somehow changes the database. What do you think?