Differences

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

--- playlists_sql_examples [2023/03/29 16:53] – created A User Not Logged in
+++ playlists_sql_examples [2024/05/26 22:41] (current) – [How to make a 'mature' sql playlist and hide them from others] scampa123
@@ Line 1: / Line 1: @@
+====== PlayLists Active SQL Examples ======
+One of the best features of PinUP Popper is the use of active sql playlists. These are different than regular playlists where you add/remove games manually to those. With Acitive SQL Playlists, they will query the sql database in realtime so that the list is populated based on data from game manager. That way you don't need to maintain the playlist. For example, An Active Playlist could list all games from 1990-2000….if you add a new table/game to your system and set its date field, the list will automatically update while you're in the frontend.
+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:
+some threads:
+[[https://web.archive.org/web/20201029143253/https://vpinball.com/forums/topic/sql-playlists/|https://web.archive.org/web/20201029143253/https://vpinball.com/forums/topic/sql-playlists/]]
+If you are power user you can download a free sql util like : [[http://sqlitestudio.pl|http://sqlitestudio.pl]] You can interactive and test sql easier with this db browser.
+//note: do not change the modify the database structure or datatypes. If you need to add your own data into the sql, then add a new table and join.//
+==== 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>
+- Playlist that will show you the last 10 games you've updated in Popper (via drag/drop update method)
+<file>
+Select * from games where visible=1 order by datefileupdated DESC LIMIT 10
+</file>
+-List of all games that you haven't played in last 30 days
+<code>
+SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 where lastplayed <= datetime("now","-30 day") order by lastplayed desc LIMIT 50
+</code>
+- Based on GameType, list all DMD (and color DMD) games:
+<code>
+SELECT * FROM Games WHERE (GameType= "DMDc" or GameType= "DMD") ORDER BY GameDisplay
+</code>
+- Using the TAG "ignore" remove certain games from any list:
+<code>
+SELECT * FROM Games WHERE (GameType= "DMDc" or GameType= "DMD") and tags not like "%ignore%" ORDER BY GameDisplay
+</code>
+- Playlist for Pinsound tables (using field LaunchCustomVar)
+<code>
+SELECT * FROM Games WHERE ( LaunchCustomVar LIKE "%pinsound%" or LaunchCustomVar LIKE "%altsound%") and TAGS NOT LIKE "%ignore%" ORDER BY "GameDisplay"
+</code>
+- Playlist for adult games:
+<code>
+select * from games JOIN Emulators on Games.EmuID = emulators.EmuID where TAGS LIKE "%adult%" and TAGS NOT LIKE "%ignore%" ORDER BY "GameDisplay"
+</code>
+- Playlist for matching-named game files (VPW Example since all their file names contain VPW)
+<code>
+select * from Games where EMUID=1 AND visible=1 AND Games.GameFileName LIKE "%VPW%" Order BY GameDisplay
+</code>