English > Development

SQL statement for MC Plugin

(1/1)

rick.ca:
I'm taking this conversation public, where maybe someone will have mercy on us... :-\


--- Quote from: nostra on April 12, 2009, 11:53:38 am ---
--- Quote from: rick.ca on April 12, 2009, 04:21:10 am ---
--- Quote from: nostra on April 12, 2009, 02:44:10 am ---
--- Code: ---SELECT PEOPLE."name" FROM PEOPLE INNER JOIN CAREER ON (CAREER."pid" = PEOPLE."pid")
WHERE ("ctype" = Y) AND ("mid" = X)
--- End code ---

--- End quote ---

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
  PEOPLE."name",
  CAREER."role"
From
  PEOPLE Inner Join
  CAREER On (CAREER."pid" = PEOPLE."pid")
Where
  CAREER."ctype" = 0 And
  CAREER."mid" = 111
--- End code ---

--- End quote ---

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

--- End quote ---

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
  PEOPLE."name" || ' - ' || CAREER."role"
From
  PEOPLE Inner Join
  CAREER On (CAREER."pid" = PEOPLE."pid")
Where
  CAREER."ctype" = 0 And
  CAREER."mid" = X
--- End code ---

raldo:
Did you notice that as long as you don't edit the listed fields in JRiver MC, the ordering stays as the original ordering?

rick.ca:
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

raldo:

--- Quote from: rick.ca 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

--- End quote ---
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.

rick.ca:

--- 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".
--- End quote ---

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.
--- End quote ---

For what purpose?


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

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?

Navigation

[0] Message Index