SQL Playlists

[Iain]

Sorry for the spam, I tried submitting this a few hours ago and just realised that I think it somehow submitted twice? and neither have appeared so maybe its been spam filtered out?

SQL in playlists is an awesome feature of Pinup Popper. I’m a programmer by day but I’ve never actually been able to mess around with SQL so I’d love to know if there are any cool tricks and ideas that some users out there know with SQL?

I’ve got the absolute basic knowledge of it in practice, and I’ve got a few playlists I’ve setup and I’ll share the SQL here for anyone interested.

I honestly recommend using SQL over making your own playlists, even some more specific playlists because you’ll *never* have to maintain lists if you do it right.

So, first a tiny bit of basic SQL knowledge (this is a very very very basic overview and skips *a lot* of stuff)….

All SQL is in the form of something like….

SELECT blah
FROM blah
WHERE blah
AND blah
OR blah
ORDER BY blah

Now. Most of these are optional, all you *actually* need is SELECT and FROM.

In terms of Pinup Popper, you’re almost always going to start with….

“SELECT * FROM Games”

SELECT * basically means, “Select everything” because the * is the wildcard so will get ALL games.

FROM Games means you’re picking items from the “Games” database, this is the main database of games inside Pinup Popper.

The rest are all optional.

WHERE is followed by some equality check. Maybe WHERE Theme=’something’ or WHERE Rating>3 where Theme and Rating are values set when you add games in the Game Manager.
Now, this is where its a little fiddly, but the input values in the GameSetupForm don’t match exactly the name of the value in the database.

Currently the easiest way I found to get the names of the values I need was to go the Popper Setup menu and click DBEditor. Don’t worry, you won’t be editing anything.

In this the basic “Select * from Games” will be shown, so just hit Execute and Open Results, this will show you the *raw* table data and you can see the name of the columns,these are the fields you can use in your SQL.

AND and OR basically do what they say, they let you combine other equality checks. So maybe you want all themes of 3 rating at least, so you’d do “SELECT * FROM Games WHERE GameTheme=”blah” AND GameRating>=3″

ORDER BY is how you want the list ordered after being built. If you don’t have ORDER BY, it will return the results in the order they are in the database, which is probably the order you added them in! Most of the time you want them in alphabetical order, so “ORDER BY GameDisplay” is what you want (note. NOT GameName, as that the name of the vpx file itself, not the display name you see in the menu).

So a simple, get all games that have rating at least 3 in alphabetical order might be….

“SELECT * FROM Games WHERE GameRating>=3 ORDER BY GameDisplay”.

And if I wanted them in *reverse* order for no apparent reason, pop a “DESC” on the end.

“SELECT * FROM Games WHERE GameRating>=3 ORDER BY GameDisplay DESC”.

Now, tbh, this is the limits of my SQL knowledge as I haven’t even looked much at SQL for nearly 10 years. So I’m really interested to see if there are any cool tricks or ideas of there for more advanced SQL searching. For now though, these are the playlists I’ve got in my setup….

TLDR; Here’s my playlists…

Visual Pinball X – Displays all games that are VPX tables…
SELECT * FROM Games WHERE EMUID= ‘1’ ORDER BY GameDisplay;

(This highlights the issue between database and how you input values, the emulator field is EMUID and they all have ids, so VPX is 1)

 

Pinball FX Tables – Displays all PBFX2 and PBFX3 tables (I have both installed as PBFX2 still has 4 tables not in PBFX3)
SELECT * FROM Games WHERE EMUID= ‘2’ Or EMUID=’3′ ORDER BY GameDisplay;

(Note – this uses OR and not AND, it might seem intuitive to put AND as you want FX2 AND FX3, but no table is *both*, they are one or the other, so what you actually are asking for is FX2 OR FX3)

MARVEL – These are all Marvel tables. I set the Theme in the game manager to be “Marvel”.
SELECT * FROM Games WHERE GameTheme=’Marvel’ ORDER BY GameDisplay;

STAR WARS – These are all Star Wars tables. I set the Theme in the game manager to be “Star Wars”
SELECT * FROM Games WHERE GameTheme=’Star Wars’ ORDER BY GameDisplay;

 

 

Stern Pinball – This is an odd one. Basically I wanted a “modern stern” playlist, but I have all modern and old stern tables as the same manufacturer. I could have used a Theme tbh but instead I’ve been more hacky and just use the date 1999 as the point Modern Stern tables ‘started’
SELECT * FROM Games WHERE Tags = ‘Stern’ AND GameYear >= ‘1999’ ORDER BY GameDisplay;

(Now we DO use the AND as we want both things to be true, Stern AND from 1999 onwards. Note also, Manufacturer in the game manager is saved to the Tags field…this threw me at first!)

 

I then have a “Decades” playlist which contains a playlist for each “Decade”. So each one is like this…

2000s+ – SELECT * FROM Games WHERE GameYear >= 2000 ORDER BY GameDisplay;
1990s – SELECT * FROM Games WHERE GameYear >= 1990 AND GameYear < 2000 ORDER BY GameDisplay;
1980s – SELECT * FROM Games WHERE GameYear >= 1980 AND GameYear < 1990 ORDER BY GameDisplay;
1970s – SELECT * FROM Games WHERE GameYear >= 1970 AND GameYear < 1980 ORDER BY GameDisplay;
1960s – SELECT * FROM Games WHERE GameYear < 1970 ORDER BY GameDisplay;

Yes – 2000s is actually everything after 2000 and 1960s is everything before 1970, but its good enough for me :P

You could also actually order them chronologically instead, so have something like “ORDER BY GameYear” on the end instead, and can have it go new to old with “DESC” on the end of that.

 

Thats all I’ve got for now, I don’t have ratings set for games but if I did I’d go with something like a “top 25” playlist or similar with

SELECT * FROM Games ORDER BY GameRating DESC LIMIT (25)

This introduces a new thing, LIMIT which basically limits the result to the amount in the brackets.

So, now if I were to add a new modern Stern Star Wars table (ha), it will automagically get added to the following playlists without me having to do anything (except fill out the details correctly when adding)…

Visual Pinball X
Star Wars
2000s+
Stern Pinball

Done.

You need to login in order to like this post: click here

12 users thanked author for this post.

Lion570, handyturnips, Grimstead, Tom, adrian kuney, KrakenByte, jbg4208, WallClock, TerryRed, exodus_cl, matd, NailBuster

[Iain]

I’m not sure what flavour of SQL is actually being used, but I tried to do the following and it kept failing and I *think* its legit SQL…

I tried to make a playlist that was 5 random tables, kinda like a “Random Table of the Day” style playlist as (I assume) the playlists are evaluated when the Pinup Popper loads up?

I tried,

SELECT * FROM Games ORDER BY NEWID() LIMIT(5);

Both with and without the LIMIT(5) so I’m assuming it doesn’t recognise the NEWID().

 

Basically, its trying to just randomly order the games then pick the first 5.

A mroe efficient way as well would be to do…

SELECT TOP 5 FROM Games …

But I also think TOP 5 isn’t recognised (https://www.w3schools.com/sql/sql_top.asp) so not sure what version of SQL is being used…

EDIT –  Actuallly, just googled and LIMIT might be the same as TOP so no more or less efficient….

 

You need to login in order to like this post: click here

1 user thanked author for this post.

adrian kuney

[NailBuster]

Great Post!  Good info for starters…

The sql db/engine is SQLite 3.0  so you’ll need to use that when googling.

also… suggestion with TAGS since you can have many tags in the field you’d want to do something:

where  TAGS like ‘%stern%’

that way it just finds stern anywhere in TAGS, also  using like is case insensitive…but of course slower than equals  ,  but i don’t think you need to worry about performance unless you’re getting into HUGE dbs.

funny… the forum software was not allowing me to post some sqls for security reasons… thinks its an sql injection :)

 

 

You need to login in order to like this post: click here

[NailBuster]

oh, and your random idea would be:

select * from games order by random() limit 10

You need to login in order to like this post: click here

[Iain]

Ah cheers gotcha, did wonder about the TAGS field and thanks for the “Random” tip. Once I get the chance to test it all i’ll post updated SQL here for people. Not at my cabinet for a bit.

You need to login in order to like this post: click here

[Iain]

Cool, cheers to NailBuster fixing up the mistakes, here are the updated SQL playlists including a working “Random 5 tables of the day” style playlist….

Visual Pinball X :
SELECT * FROM Games WHERE EMUID= ‘1’ ORDER BY GameDisplay;

Pinball FX2 + FX3 :
SELECT * FROM Games WHERE EMUID= ‘2’ OR EMUID=’3′ ORDER BY GameDisplay;

Star Wars (Theme Set):
SELECT * FROM Games WHERE GameTheme LIKE ‘star wars’ ORDER BY GameDisplay;

Marvel (Theme Set):
SELECT * FROM Games WHERE GameTheme LIKE ‘marvel’ ORDER BY GameDisplay;

Modern Stern :
SELECT * FROM Games WHERE Tags LIKE ‘%stern%’ AND GameYear >= ‘1999’ ORDER BY GameDisplay;

Random 5 Games :
SELECT * FROM Games ORDER BY RANDOM() LIMIT(5);

Table of the Day :
SELECT * FROM Games ORDER BY RANDOM() LIMIT(1);

 

Decades 2000s+ :
SELECT * FROM Games WHERE GameYear >= 2000 ORDER BY GameDisplay;

Decades 1990 :
SELECT * FROM Games WHERE GameYear >= 1990 AND GameYear < 2000 ORDER BY GameDisplay;

Decades 1980 :
SELECT * FROM Games WHERE GameYear >= 1980 AND GameYear < 1990 ORDER BY GameDisplay;

Decades 1970 :
SELECT * FROM Games WHERE GameYear >= 1970 AND GameYear < 1980 ORDER BY GameDisplay;

Decades 1960 and Earlier :
SELECT * FROM Games WHERE GameYear < 1970 ORDER BY GameDisplay;

You need to login in order to like this post: click here

1 user thanked author for this post.

adrian kuney

[DJRobX]

Iain wrote:

SELECT * basically means, “Select everything” because the * is the wildcard so will get ALL games.

Just for clarification – * here means “Select all columns from games”.    You get “all games” because you have no WHERE clause.

Select GameName from games would give you just the GameNames with no other information.   For the purposes of dynamic playlists, *  is generally going to be easiest, since Popper can ignore anything it doesn’t want.

 

You need to login in order to like this post: click here

[Mark70]

Very interesting stuff!

You need to login in order to like this post: click here

[Iain]

Turns out, the Playlist SQL is executed when you pick a playlist, not just once when the menu boots up. So the “Random” playlists will give you different games everytime you view them!

 

So on my cab I removed the “5 random games” one, and changes the “Table of the day” to just “Random” and it basically picks a random game for you to play.

You need to login in order to like this post: click here

[Iain]

I posted this in the FB group but realised some people here won’t see that.

I originally had no tables in my Home playlist, only other playlists (I prefer everything “sorted”). This meant I didn’t get the Global Favourites (or Recently Played and Most Played) options in the home screen….these only appear if you have actual tables in your home playlist.

I didn’t really want to add tables in just to see the favourites list, but also I didn’t want to manually maintain my *own* favourites in the homelist. So instead, SQL to the rescue!

PinupPopper showing its awesome flexibility meant I set the Home Active SQL to the following….

SELECT *

FROM Games t1

LEFT JOIN Playlistdetails t2 ON t2.GameID = t1.GameID

WHERE t2.isFav = 2

ORDER BY GameDisplay”

What this now means is, the tables in my home playlist are the tables stored in the “Global Favourites”! So now, my launch list are all my playlists and all my favourites.

You need to login in order to like this post: click here

[coreduo0099]

Hi everyone,

I’m experimenting in moving from PinballX and spent all Saturday on my setup.

I have all of my VPX, VP9, VP9 PM, VP9 PROC, FX2, FX3 all working as desired with all my media linked to my hyperspin/pinX collection via symbolic link.

A few challenges I’m having:

A) None of the metadata I had in PinballX is in Pinup.  I’ve use some graphical sql front ends before to do interactive queries, then start to apply various metadata/key value pairs to the data but don’t see a good way to do filters/bulk selects/bulk updates in the pinup popper setup.  What do folks here recommend if one is not a sql cli jockey?

(In the mean time my lists are static.  If I could bulk select and add an ignore and adult tags to the tables not in my playlists, that would then enable me to build sql lists and filter out those tags, but I don’t want manually go through appx 1500+ tables one by one in the UI.)

B) I have lots of tables in my tables directories I don’t want/use in playlists.  It seems all the samples here Assume one would want every table in a playlist.  Sometimes I keep multiple versions to test new before I delete old, some have different authors, etc, but I only one in my playlists.  So, I’m trying to figure out how to build a playlist of playlists.  I would love to be able to understand the schema.  I found a db emulators and one playlists in addition to games but could not make it fly.  I can’t figure out join logic between games and playlists.  Essentially I want an All games playlist that is the aggregate of my VPX, VP9, VP9 PM, VP9 PROC, FX2, FX3 playlists.

Something like where (playlistid=25 or playlistid=26 or playlistid=28……)

Any tips/suggestions for a pinup newbie would be very appreciated.  Thanks!

You need to login in order to like this post: click here

1 user thanked author for this post.

adrian kuney

[portyman]

I was wondering how to create a playlist by letter. I can’t seem to figure out the sql code. Thanks in advance.

You need to login in order to like this post: click here

[portyman]

select * from games where GameName like ‘a%’

 

 

Figured it out late last night

You need to login in order to like this post: click here

[m0riarty]

some of these don’t seem to be working

SELECT * FROM Games WHERE Manufacturer LIKE ‘%williams% ORDER BY GameDisplay;

Getting an SQL error

 

even if i use exactly what is suggest above:

SELECT * FROM Games WHERE Tags LIKE ‘%stern%’ AND GameYear >= ‘1999’ ORDER BY GameDisplay;

i get the same error.   what i’m i doing wrong.

 

You need to login in order to like this post: click here

[LynnInDenver]

Take out the percent signs. And the field name is not Manufacturer, but Manufact.

SELECT * FROM Games WHERE Manufact = 'Stern' ORDER BY GameDisplay

Creator of the first PinupPlayer PostDMD mods for VPX - PostDMD for Masters of the Universe VPX and Jaws VPX.
Head Proprietor of Pisces Pinball, a VPX table developer.
Lead Technician of MC Chase Amusements, a private arcade in our home basement.

You need to login in order to like this post: click here

[m0riarty]

hmm doesn’t give me an error, but doesn’t give me games.

 

EDIT… sorry its case sensitive. got it to work.

You need to login in order to like this post: click here

[m0riarty]

Ok, knew nothing about SQL but with your guys help, here are my lists you can use.

ALPHABETICAL

tables starting with A-C displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘A’ and GameDisplay< ‘D’ ORDER BY GameDisplay;

tables starting with D-F displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘D’ and GameDisplay< ‘G’ ORDER BY GameDisplay;

tables starting with G-I displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘G’ and GameDisplay< ‘J’ ORDER BY GameDisplay;

tables starting with J-L displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘J’ and GameDisplay< ‘M’ ORDER BY GameDisplay;

tables starting with M-O displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘M’ and GameDisplay< ‘P’ ORDER BY GameDisplay;

tables starting with P-R displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘P’ and GameDisplay< ‘S’ ORDER BY GameDisplay;

tables starting with S-T displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘S’ and GameDisplay< ‘U’ ORDER BY GameDisplay;

tables starting with U-W displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘U’ and GameDisplay< ‘X’ ORDER BY GameDisplay;

tables starting with X-Z displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘X’ and GameDisplay<= ‘Z’ ORDER BY GameDisplay;

tables starting with # displayed in alphabetical order:

SELECT * FROM Games WHERE GameDisplay>= ‘1’ and GameDisplay< ’10’ ORDER BY GameDisplay;

 

DECADES:

I also have the decades in separate playlists, in order of creation date.  I’ll give an example of one.  you can figure out the dates you want to bundle together.

1990s

SELECT * FROM Games WHERE GameYear >= 1990 AND GameYear < 2000 ORDER BY GameYear ASC;

 

MANUFACTURES

Same as others described above but I wanted to see the games in order of which they were build of course replace name of manufacturer). so…

SELECT * FROM Games WHERE Manufact = ‘Stern’ ORDER BY GameYear ASC;

 

 

 

You need to login in order to like this post: click here

[Kellogs]

Just try an error, but could not make it work for the letter “C”

SELECT * FROM Games WHERE GameDisplay like ‘a%’ or GameDisplay like ‘b%’;

 

You need to login in order to like this post: click here

[Westworld]

To find old, forgotten games:

Less Played (lowest number of played)

select * from Games where GameID in (select GameID from GamesStats order by NumberPlays asc limit 25)

 

Not recently played (oldest not played games by date)

select * from Games where GameID in (select GameID from GamesStats order by LastPlayed asc limit 25)

 

These playlist includes 25 games, modify if you want more or less.

You need to login in order to like this post: click here

[NailBuster]

always good practice to add to where clause ” and visible=1″

visible=2 are adult/hidden status and visible=0 are hidden.  (visible is set in games manager on table).

You need to login in order to like this post: click here

[Author]

Posts