Speak/say selecteed movie name

[Auri]

Sure, XBMC has it's own DB obviously and we have the ability to scan it into payloadXML files and then request stuff by name.  But maybe we don't always want to specify a movie by name.

Currently when we use the double feature command we are passing the names of the two movies we want to watch and then the CE plugin is creating its own SQL query to find and queue those movies.

I'm suggesting that we leave it more open and flexible so that we can send the actual SQL request that we want to use.  This way we could ask it to Queue up 4 movies from 1972, or 3 movies from the "Family" genre, etc.

It makes more sense to use free SQL because then we can go crazy making any types of requests we want, and we won't need to bother Giftie every time we want to create something new.

If you don't know how to use SQL that's no problem because we can simply share our commands the way we normally do and eventually you'll get the hang of making your own.

If you look at how music is requested with mediaMonkey actions in VC this is exactly how it is done.  We construct sql queries and then mediaMonkey loads the matching songs.

I see where you are going now, ok that makes sence.

And I know how to use SQL, I'm a system admin ... I maintain MS SQL servers on a daily basis

[jitterjames]

I'm sure you'll come in handy then...

 

[Auri]

I'm sure you'll come in handy then...

DOH !! I said to much already
I already have problems leave "my work" @ work, don't make me bring it to my leisure time also :p

I'll help where I can though 

[giftie]

As long as you SQL queries would would return movie titles it should be simple to add. 
For instance:

"SELECT movieview.c00 FROM movieview JOIN genrelinkmovie ON genrelinkmovie.idMovie=movieview.idMovie JOIN genre ON genrelinkmovie.idGenre=genre.idGenre WHERE strGenre="Action" ORDER BY RANDOM() LIMIT 10"

returns 10 random movie titles that match the Genre of Action.

I know this works with SQLite(XBMC's native Database), a MySQL query probably would be quite similar.   
I'm glad I went with putting 'command' before the movietitle option...  Some how I knew more would come up... 
I don't see a problem putting 'command<li>sqlquery' in... 



30mins since this posted I already have working code(well it works with the query above) 

Supply me with some queries to test...

[jitterjames]

Cool Giftie,

I'm excited to try this out when I get up tomorrow.  I just got home from a night out and it's time for bed.

[jitterjames]

Hi Giftie.  I am experimenting with this right now and so far so good.  Just one question.  Would it not be safer if our sql queries generated movie ids instead of titles?  Or would that be a lot of work to modify the plugin?

[jitterjames]

It's good to have a reference for how to do a bunch of joins in XBMC, but I find your sql a bit long.  I prefer to use this:

RunScript(script.cinema.experience,command<li>sqlquery<li>SELECT movieview.c00 FROM movieview WHERE c14 LIKE '%%comedy%%' ORDER BY RANDOM() LIMIT 5)

It's important to note that we need to use %% instead of % in our like.  I don't know why that is.  I just tried it because I felt like I had seen it done that way before and it worked.

Once we get the CE script nailed down for this, I think I'll add a new command XBMC.CE.SQL and then we will only need to enter the SQL portion, to make it easier to edit.  So the param would be:

Code: [Select]

SELECT movieview.c00 FROM movieview WHERE c14 LIKE '%%comedy%%' ORDER BY RANDOM() LIMIT 5
in Vox if we wanted to use a payload for Genre then it would be

Code: [Select]

SELECT movieview.c00 FROM movieview WHERE c14 LIKE '%%{1}%%' ORDER BY RANDOM() LIMIT 5
if you wanted to see movies from France you could do:

Code: [Select]

SELECT movieview.c00 FROM movieview WHERE c21 LIKE '%%France%%' ORDER BY RANDOM() LIMIT 5
or movies by  director 'Frank Capra'

Code: [Select]

SELECT movieview.c00 FROM movieview WHERE c15 LIKE 'Frank Capra' ORDER BY RANDOM() LIMIT 5
you may notice that I used %% for Genre and not for Director.  The reason is that the movieview table in xbmc stores multiple genres in a single text field like this: Crime / Drama / Action, but does not seem to do this with Director.  Better not to use the %% wildcards if we don't need it because if we had a director whos name was a subset of another directors name then we would have problems.  We still use LIKE instead of equal because it ignores case.

[jitterjames]

I would like to see someone come up with a join to let us queue random movies by Actor name... 

[Auri]

I would like to see someone come up with a join to let us queue random movies by Actor name...  

I have not yet seen the XBMC DB at all, but as you random c14, c21, etc ... you can't random c## the actors are in ?

Like

Code: [Select]

SELECT actors FROM movieview ORDER BY RANDOM() LIMIT 5 this is just out of my head with no idea how the XBMC db is made out.

If you need advance SQL help let me know, but your question should be done easily I reckon.

I'll have a look at the XBMC Db structure this weekend so I don't need to do stupid guesses like the above and can give actual examples.

[giftie]

I would like to see someone come up with a join to let us queue random movies by Actor name... 

In my long winded, round about way, here you go...
SELECT movieview.c00 FROM movieview JOIN actorlinkmovie ON actorlinkmovie.idMovie=movieview.idMovie JOIN actors ON actorlinkmovie.idActor=actors.idActor WHERE strActor="Adam Sandler" ORDER BY RANDOM() LIMIT 10


For those who haven't figured out the movieviewView:

c00 = Movie Title
c01 = Movie Plot
c02 = Plot Outline
c03 = Tag Line
c04 = # of Voters(IMDb)
c05 = Vote Rating(IMDb)
c06 = Writer
c07 = Year
c08 = thumbnail url
c09 = IMDb #
c10 = ? not sure ? -
c11 = Runtime
c12 = MPAA Rating
c13 = IMDb Top 250 placement
c14 = Genre
c15 = Director
c16 = Original Title - I think
c17 = ? Not Sure ? - Empty in my Db
c18 = Studio

[jitterjames]

Thanks Giftie,

I think in this case, that's about as short as you can make it!  Haven't tested it but it looks good.
So are you going to keep it like this and publish it, or do you want to use movie ids instead of titles?

Auri: the actors are in a separate table from the movieview, and the link between actors and movies is in yet another table, hence the joins.  When you have time check out google for "xbmc database" or better yet, grab an sqLite viewer and check out the actual data in your library.  Also note that we need to return movies as the end result, not actors.

[giftie]

Hi Giftie.  I am experimenting with this right now and so far so good.  Just one question.  Would it not be safer if our sql queries generated movie ids instead of titles?  Or would that be a lot of work to modify the plugin?

Not a lot of work.  Though I can't see it being any safer.  I would need to add another SQL query internally to change idMovie to Movie Title...  - I see in my own Db, I have two movies that don't seem to have Movie Titles(I know the script will skip these by default) 

quick link to XBMC's wiki page - http://wiki.xbmc.org/index.php?title=The_XBMC_Database#view

[jitterjames]

Maybe it wouldn't be any better then.  I'll leave it to your judgement.

[Wanilton]

James,
Now script is done with SQL command, in this new Cinema Experience Addon for XBMC - version 1.0.31

- Enhancement - Added sqlquery command
- use:
RunScript(script.cinema.experience,command<li>sqlq uery<li> .. your query ..)

test your queries out before trying them with the script(SQLiteSpy is a great tool for XBMC's default database)

example:
HTTP API -> RunScript(script.cinema.experience,command<li>sqlq uery<li>SELECT movieview.c00 FROM movieview WHERE c14 LIKE '%%action%%' ORDER BY RANDOM() LIMIT 10)
Skin -> RunScript(script.cinema.experience,command;sqlquer y;SELECT movieview.c00 FROM movieview WHERE c14 LIKE '%%action%%' ORDER BY RANDOM() LIMIT 10)

A couple of SQL Queries:

5 Random Movies from the Genre Comedy
SELECT movieview.c00 FROM movieview WHERE c14 LIKE '%%comedy%%' ORDER BY RANDOM() LIMIT 5
5 Random Movies Based on actor Adam Sandler:
SELECT movieview.c00 FROM movieview JOIN actorlinkmovie ON actorlinkmovie.idMovie=movieview.idMovie JOIN actors ON actorlinkmovie.idActor=actors.idActor WHERE strActor="Adam Sandler" ORDER BY RANDOM() LIMIT 5

As you can, make some commands are built to be able to enjoy these new opportunities via voxcommando.

Thanks giftie and James, this is much more powerfull now.

[jitterjames]

Thanks for the heads up W.  After a quick test of version 31 everything seems to be working well.

I will post an xml group with a few different commands when I get a chance.  This leads me to my next possible challenge: wildcards events.

In other words, being able to handle an event like XBMC.CElaunch.2, or XBMC.CElaunch.3

of course we can currently drag both events into a command, but it would be nicer if we could use XBMC.CElaunch.*