User Tools

Site Tools


playlists_sql_examples

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
playlists_sql_examples [2018/07/27 22:23]
A User Not Logged in
playlists_sql_examples [2020/05/11 21:20] (current)
pinupadmin
Line 2: Line 2:
  
 Active SQL Playlists are a very flexible way of managing your playlists. They are Playlists that will automatically execute/update each time you select them giving you ability to filter and grab tables dynamically. Active SQL Playlists are a very flexible way of managing your playlists. They are Playlists that will automatically execute/update each time you select them giving you ability to filter and grab tables dynamically.
 +
 +Sample Video by user showing example config: [[https://youtu.be/-ZwC32AzgIM|https://youtu.be/-ZwC32AzgIM]]
  
 Here are some sample of Playlists and the SQL you can set to use them: Here are some sample of Playlists and the SQL you can set to use them:
 +
 +some threads:
 +
 +[[https://vpinball.com/forums/topic/sql-playlists/|https://vpinball.com/forums/topic/sql-playlists/]]
 +
 +[[https://vpinball.com/forums/topic/sql-helper-for-pinballx-users-going-to-pinup-menu/|https://vpinball.com/forums/topic/sql-helper-for-pinballx-users-going-to-pinup-menu/]]
 +
 +==== How to make a 'mature' sql playlist and hide them from others ====
 +
 +You want on each of these 'mature' games, the first field in game manager is “status'. set to mature/hidden.
 +
 +Note: if you are using any active sql playlist make sure you filter out and use visible=1 so it doesn't pickup any mature games…..
 +
 +Here are the some columns for Games Table.<code>
 +    GameID          INTEGER
 +    EMUID           INTEGER,
 +    GameName        VARCHAR (200)
 +    GameFileName    VARCHAR (250)
 +    GameDisplay     VARCHAR (200)
 +    UseEmuDefaults  INTEGER,
 +    Visible         INTEGER       DEFAULT (1),
 +    Notes           TEXT,
 +    DateAdded       DATETIME,
 +    GameYear        INTEGER,
 +    ROM             VARCHAR (100),
 +    Manufact        VARCHAR (200),
 +    NumPlayers      INTEGER,
 +    ResolutionX     INTEGER,
 +    ResolutionY     INTEGER,
 +    OutputScreen    INTEGER,
 +    ThemeColor      INTEGER,
 +    GameType        VARCHAR (50),
 +    TAGS            VARCHAR (200),
 +    Category        VARCHAR (200),
 +    Author          VARCHAR (200),
 +    LaunchCustomVar VARCHAR (200),
 +    GKeepDisplays   VARCHAR (50),
 +    GameTheme       VARCHAR (100),
 +    GameRating      INTEGER,
 +    Special         TEXT,
 +    sysVolume       INTEGER,
 +    DOFStuff        VARCHAR (250),
 +    MediaSearch     VARCHAR (100),
 +    AudioChannels   VARCHAR (50),
 +    CUSTOM2         VARCHAR (100),
 +    CUSTOM3         VARCHAR (100),
 +    GAMEVER         VARCHAR (100),
 +    ALTEXE          VARCHAR (250),
 +    IPDBNum         VARCHAR (100),
 +    DateUpdated     DATETIME,
 +    DateFileUpdated DATETIME,
 +    AutoRecFlag     INTEGER        DEFAULT 0,
 +    AltRunMode      VARCHAR (250),
 +    WebLinkURL      VARCHAR (1000),
 +    DesignedBy      VARCHAR (200)
 +</code>
  
 - Based on GameType, list all DMD (and color DMD) games: - Based on GameType, list all DMD (and color DMD) games:
  
-SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID  WHERE  (GameType= "DMDc" or GameType= "DMD" ORDER BY GameDisplay+<code> 
 +SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID WHERE (GameType= "DMDc" or GameType= "DMD") ORDER BY GameDisplay 
 +</code>
  
 - Using the TAG "ignore" remove certain games from any list: - Using the TAG "ignore" remove certain games from any list:
  
-SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID  WHERE  (GameType= "DMDc" or GameType= "DMD"  and tags not like "%ignore%" ORDER BY GameDisplay+<code> 
 +SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID WHERE (GameType= "DMDc" or GameType= "DMD") and tags not like "%ignore%" ORDER BY GameDisplay 
 +</code>
  
-​​​​​​​- Playlist for Pinsound tables (using field LaunchCustomVar)+- Playlist for Pinsound tables (using field LaunchCustomVar)
  
-SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID  WHERE ( LaunchCustomVar LIKE "%pinsound%"  or  LaunchCustomVar LIKE "%altsound%") and TAGS NOT LIKE "%ignore%"  ORDER BY "GameDisplay"+<code> 
 +SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID WHERE ( LaunchCustomVar LIKE "%pinsound%" or LaunchCustomVar LIKE "%altsound%") and TAGS NOT LIKE "%ignore%" ORDER BY "GameDisplay" 
 +</code>
  
 - Playlist for adult games: - Playlist for adult games:
  
-select * from games JOIN Emulators on Games.EmuID = emulators.EmuID  where TAGS LIKE "%adult%" and TAGS NOT LIKE "%ignore%"   ORDER BY "GameDisplay"+<code> 
 +select * from games JOIN Emulators on Games.EmuID = emulators.EmuID where TAGS LIKE "%adult%" and TAGS NOT LIKE "%ignore%" ORDER BY "GameDisplay" 
 +</code>
  
  
playlists_sql_examples.1532744592.txt.gz · Last modified: 2018/07/27 22:23 by A User Not Logged in