SQL for Cord-Cutters
Created:
This post is about using JMP’s SQL capabilities, mostly, but also touches XML, Linux, MythTV.
SQL Query Builder doc: https://www.jmp.com/support/help/14/query-builder.shtml#
MythTV https://www.mythtv.org/ is an open source program (TANSTAAFL) that can record over-the-air TV broadcasts using a tuner card in a PC (and a bunch of other stuff too). It is a great way to learn about Linux and MySQL/MariaDB and avoid watching TV. The implementation uses two computers, a backend (or several) that does the recording and a frontend (or several) that does the watching. An SQL database keeps track of what to record and what has been recorded.
https://code.mythtv.org/trac/ticket/12974#no1 describes a reason why there are lots of really old, really big files on the MythTV backend disk that are not visible in the MythTV frontend. It says there is a MythTV bug around the deletepending database field that prevents the file from being deleted if MythTV crashes between the time the field is set and the file is deleted.
For example, using the Linux ls command to list some files, here’s a 3GB example (it is December 2019; only about two weeks of files fit on the 3T disk; these files should be long gone.)
-rw-r--r-- 1 mythtv mythtv 3.1G Mar 21 2018 1051_20180321073000.ts
Or, after doing a database import,
The database import seems useful; it puts columns (or fields) next to each other that have related values and combines information from several tables in the database. Here are the steps to do it.
Locate the database credentials.
On a Linux system running the MythTV frontend, there will be a file ~/.mythtv/config.xml .
<Configuration>
<LocalHostName>my-unique-identifier-goes-here</LocalHostName>
<Database>
<PingHost>1</PingHost>
<Host>closet.local</Host>
<UserName>mythtv</UserName>
<Password>this is the pw</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>
<WakeOnLAN>
<Enabled>0</Enabled>
<SQLReconnectWaitTime>0</SQLReconnectWaitTime>
<SQLConnectRetry>5</SQLConnectRetry>
<Command>echo 'WOLsqlServerCommand not set'</Command>
</WakeOnLAN>
<UPnP>
<UDN>
<MediaRenderer>52d20477-fda1-4e42-9edd-5992bd0ca45f</MediaRenderer>
</UDN>
</UPnP>
</Configuration>
Copy the file somewhere your copy of JMP can read it, and, with JMP 15, open it like this:
// grab user name and password from the XML config file
dtConfig = Open( "F:\mythconfig.xml", invisible, XML Wizard( 0, guess( "huge" ) ) );
which will make a hidden table like this
Closet.local is the name of the backend host, mythtv is the SQL user name that has access to the mythconverg database, and the 3306 is the default port, so it won’t be needed.
Create a DSN using the windows ODBC Data Source Administrator.
You probably need the 64 bit drivers, and you might need to download them. https://dev.mysql.com/downloads/connector/odbc/ or maybe https://downloads.mariadb.org/connector-odbc/+releases/ . I’m pretty sure I’m actually running MariaDB on my backend server and using the MySQL connector on the frontend. https://www.google.com/search?q=mysql+vs+mariadb
Press configure and add a few fields; I think these can all be overridden via a connection string later. My VirtualBox and network and server are not happy with closet.local for the server name so the dotted IP gets used. You can fill in the user and password and test the connection and see the database names that are available. The JSL below is going to override those.
Discover the table names in the MythTV databases
Thanks to an example from @briancorcoran for pointing the way with the next bit of JSL.
// DSN=Mythtv comes from the windows ODBC Data Source Administrator applet
dt = Open Database(
"DSN=Mythtv;UID=" || dtConfig:UserName[1] || ";PWD=" || dtConfig:Password[1],
"SHOW tables from " || dtConfig:DatabaseName[1] || ";",
"tables in " || dtConfig:DatabaseName[1]
);
That JSL is using the dtConfig JMP data table opened from the XML config file, above, to get the actual password and other information to override the DSN (which is also named mythtv, unrelated to the user name.) Brian’s example used SHOW DATABASES. This JSL uses SHOW TABLES FROM mythconverg to get the 80+ tables from the mythtv database named mythconverg.
(SQL can have multiple databases, and a database can have multiple tables. Databases do not interact with each other, but tables within a database often have keys in common that allow joining two tables. So, a database for pets, a database for recipes, and a database for MythTV. Within the MythTV database, tables that describe what programs to record and tables that describe what programs have been recorded and a lot of other tables...)
start puzzling out the shape of the database
If you do this wrong AND write to the database, you WILL corrupt the database. You could go read the source code for MythTV and maybe even find a document explaining how the tables work, or you can ask the exciting question: Will I still be able to watch recorded TV tomorrow? What follows is the exciting way.
Look through the database’s table names in the data table above and see if there are interesting ones. There are, but it isn’t yet clear what they represent.
Here’s an easy way to peek at the tables and their columns and a bit of their data.
File→Database→OpenTable
There are 50 columns in this table, but the one that leaps out is description:
Which is a description of what to record (or maybe what not to record…I wrote that rule.) This is not the table I need.
What about the table named recorded?
That looks like a table of recorded programs. This will be a starting point.
Join some tables to get everything needed in one place
File→Database→Query Builder, pick the recorded table...
When you press NEXT you’ll get this amazing @Eric_Hill intermediate window:
This window is for exploring tables. Don’t click Build Query yet, that takes you to a window where you’ll select columns. Before that, use this window to join in other tables.
(You can come back to this window later, but you should be able to find everything you’ll need later on your first visit...maybe.)
Here’s what I wound up using (almost, see below for one more change):
I used recordedfile (selected above) to get the filesize the database remembers. I think this database is not normalized because the basename appears in recorded and recordedfile...https://en.wikipedia.org/wiki/Database_normalization
(The buttons at the bottom are different because I used the go-back-to-select-more-tables red triangle button in Build Query to get this picture...pressing OK will return to the Build Query window.)
There is a lot of power here: you can control the join with the blue-circle button, you can see a snapshot of any table in the column, and you can preview the result of the join. And all of the tables are listed! This is where the puzzle gets solved: what tables are needed and how are they hooked together. Pick the Table Snapshot tab, select a table in the left column, and use the down-arrow to view each table. A lot of the MythTV tables are empty, which means I can ignore them.
Select the columns to keep
Time to click the Build Query button. This window lets you choose the columns you want and try the query:
That double-X button deletes all the column selections; you probably want to click the single-X to delete just the selected column. Move the rows describing the columns up and down to order the columns, and select a column to presort the data.
pick Run Query, study the table, re-think…
from the dialog above, the red triangle next to Tables will take you back to the add-another-table dialog (the amazing one). I needed another table to really understand what was going on.
The recgroups table has the Deleted Group indicator. Here’s a bit of the result (similar to the original table at the top.) All of these basenames are in the default group, not the deleted group.
So much lost space!
Here’s the JSL/SQL from the source script in the table. Change the <<Run to <<Modify to open in the Builder dialog for more editing!
New SQL Query(
Connection( "ODBC:DSN=mythtv;SERVER=192.168.0.39;UID=mythtv;DATABASE=mythconverg;PORT=3306;PWD=%_PWD_%;"
),
QueryName( "recorded" ),
Select(
Column( "callsign", "t3" ),
Column( "channum", "t3" ),
Column( "starttime", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ),
Column( "endtime", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ),
Column( "title", "t1" ),
Column( "description", "t1" ),
Column( "autoexpire", "t1" ),
Column( "preserve", "t1" ),
Column( "deletepending", "t1" ),
Column( "recgroup", "t4" ),
Column( "basename", "t2" ),
Column( "filesize", "t2" ),
Column( "width", "t2" ),
Column( "height", "t2" ),
Column( "fps", "t2" ),
Column( "videoprop", "t5" ),
Column( "subtitletypes", "t5" )
),
From(
Table( "recorded", Alias( "t1" ) ),
Table( "recgroups", Alias( "t4" ),
Join( Type( Left Outer ),
EQ( Column( "recgroupid", "t1" ), Column( "recgroupid", "t4" ) )
)
),
Table( "recordedprogram", Alias( "t5" ),
Join(
Type( Left Outer ),
EQ( Column( "chanid", "t1" ), Column( "chanid", "t5" ) ) &
EQ(
Column("starttime","t1",Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ),
Column("starttime","t5",Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) )
)
)
),
Table( "recordedfile", Alias( "t2" ),
Join( Type( Left Outer ),
EQ( Column( "recordedid", "t1" ), Column( "recordedid", "t2" ) )
)
),
Table( "channel", Alias( "t3" ),
Join( Type( Left Outer ),
EQ( Column( "chanid", "t1" ), Column( "chanid", "t3" ) )
)
)
),
Order By( Column( "starttime", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ),
Order( "Ascending" ) )
)
) << Run;
So I need to do a small test: for a single stuck file, touch the database to turn autoexpire back on and deletepending off. And maybe stop and restart the backend. Then watch and see if the file gets deleted, sometime in the next few hours or days...that part of MythTV’s behavior, just-in-time deleting, is also confusing.
Hmmm. Better go slow. The total filesize in the database is twice as big as the disk. Lets delete this one: 1171_20180321043700.ts
First, make a query to find it, using a filter. I'm going this route because JMP can do most of the work for me in a GUI and I'm not entirely comfortable writing the SQL without some help. I'll modify the SQL after JMP does the hard part for me.
Nice! The filter added the WHERE clause to the SQL and exactly one row was found. I think it is time to copy the SQL from the Query Builder dialog; need to add the mythconverg. before each table name:
// grab user name and password from the XML config file
dtConfig = Open( "F:\mythconfig.xml", invisible, XML Wizard( 0, guess( "huge" ) ) );
//https://community.jmp.com/t5/Discussions/How-do-I-get-the-list-of-the-databases-available-on-a-designated/m-p/10589
// dtConfig:UserName,Password,DatabaseName
//
// DSN=Mythtv comes from the windows ODBC Data Source Administrator applet
dbc=CreateDatabaseConnection("DSN=Mythtv;UID=" || dtConfig:UserName[1] || ";PWD=" || dtConfig:Password[1]);
rc=executesql(dbc,"\[
SELECT t3.callsign, t3.channum, t1.starttime, t1.endtime,
t1.title, t1.description, t1.autoexpire, t1.preserve,
t1.deletepending, t4.recgroup, t2.basename, t2.filesize,
t2.width, t2.height, t2.fps, t5.videoprop,
t5.subtitletypes
FROM mythconverg.recorded t1
LEFT OUTER JOIN mythconverg.recgroups t4
ON ( t1.recgroupid = t4.recgroupid )
LEFT OUTER JOIN mythconverg.recordedprogram t5
ON ( ( t1.chanid = t5.chanid ) AND ( t1.starttime = t5.starttime ) )
LEFT OUTER JOIN mythconverg.recordedfile t2
ON ( t1.recordedid = t2.recordedid )
LEFT OUTER JOIN mythconverg.channel t3
ON ( t1.chanid = t3.chanid )
WHERE ( ( ( t2.basename="1171_20180321043700.ts" ) ) )
ORDER BY t1.starttime ASC;
]\","one row");
Close Database Connection(dbc);
close(dtConfig,nosave);
show(nrows(rc)==1); // better only be one row
WARNING, DANGEROUS CODE FOLLOWS, YOU MIGHT WIPE OUT YOUR DATABASE WITH UPDATE AND SET.
and replace SELECT...FROM with UPDATE and add a SET clause and remove ORDER BY:
// grab user name and password from the XML config file
dtConfig = Open( "F:\mythconfig.xml", invisible, XML Wizard( 0, guess( "huge" ) ) );
dbc=CreateDatabaseConnection("DSN=Mythtv;UID=" || dtConfig:UserName[1] || ";PWD=" || dtConfig:Password[1]);
rc=executesql(dbc,"\[
UPDATE mythconverg.recorded t1
LEFT OUTER JOIN mythconverg.recgroups t4
ON ( t1.recgroupid = t4.recgroupid )
LEFT OUTER JOIN mythconverg.recordedprogram t5
ON ( ( t1.chanid = t5.chanid ) AND ( t1.starttime = t5.starttime ) )
LEFT OUTER JOIN mythconverg.recordedfile t2
ON ( t1.recordedid = t2.recordedid )
LEFT OUTER JOIN mythconverg.channel t3
ON ( t1.chanid = t3.chanid )
SET t1.autoexpire=1, t1.deletepending=0
WHERE ( ( ( t2.basename="1171_20180321043700.ts" ) ) );
]\","update");
Close Database Connection(dbc);
close(dtConfig,nosave);
fetching the table again shows the update:
I’m not sure how to get the status of the update; the rc was a missing value and no table was created, which makes sense. But it worked…
Several minutes later, the file is still present. I’m going to restart the backend. Restarted. The file does show up in the frontend now.
I’ll sit back and see if it goes away on disk in a few days. Then I’ll work on the rest of the ones like this, and see why the database seems to think there are 6T on a 3T disk...
A few hours later: ls: cannot access '1171_20180321043700.ts': No such file or directory
As noted way above (normalized comment), the filename appears in more than one table. My plan for cleaning the 6T down to a more realistic 3T assumes there are entries in the recorded table that refer to files that are no longer on disk. Rather than try to delete records from both (and maybe more?) tables, I think I’ll create a dummy file if there isn’t one and let the existing code deal with it in the usual way. Maybe a good first step would be to just open both the recorded and recordedfile database tables into JMP data tables and identify where there are files missing on disk and inconsistent between the tables.
File→Database→OpenTable will do it. Delete all the columns except basename and recordedid, sort by recordedid, compare datatables. Three diffs to click on. Easy.
Curious: 1171_20180317233200.ts is in recorded, but not recordedfile
and exists as a 0-byte file -rw-rw-r-- 1 mythtv mythtv 0 Apr 4 2018 1171_20180317233200.ts
And there are two entries with an all blank name in recordedfile that have no matching recordedid in recorded. Probably don’t want to clean those up, no telling what other tables might refer to them.
The file deleted above, 1171_20180321043700.ts, does not appear in either table. Yay!
I’ll use recorded as truth since it does not have the blank records. I’ll use a column formula with fileexists() function to help see what is going on.
There is some possibility I’ll get a lot of disk space back, but there may be a lot of zero or near-zero length files in that cluster I want to delete. Time for a break, shouldn’t start this at 9PM.
To answer the “how much?” question, use filesize() rather than fileexists(). A lot of the files exist, but are zero bytes, so there is only about 0.3T (10% !) of recoverable space.
Phase 1: add dummy files for the non-existing files so MythTV can clean them up later. The reason the records remain in the database when MythTV can’t find the files is because MythTV assumes the files might be on an unavailable disk, and they would become orphaned if the database entry was removed.
// actualsize is this formula:
// File Size( "f:/closet/" || :basename )
For Each Row(
If( :basename != "" & Is Missing( actualsize ),
If( !File Exists( "f:/closet/" || :basename ),
Show( :basename );
Save Text File( "f:/closet/" || :basename, "hi there" );// use “touch” on Linux
, // else
Throw( "oops" )
)
)
);
Write( "\!ndone" );
Phase 2: fetch a fresh copy of the database into a datatable. It looks good. Time to re-work the SQL to see if we can select just the records to be updated:
// grab user name and password from the XML config file
dtConfig = Open( "F:\mythconfig.xml", invisible, XML Wizard( 0, guess( "huge" ) ) );
dbc=CreateDatabaseConnection("DSN=Mythtv;UID=" || dtConfig:UserName[1] || ";PWD=" || dtConfig:Password[1]);
rc=executesql(dbc,"\[
SELECT t3.callsign, t3.channum, t1.starttime, t1.endtime,
t1.title, t1.description, t1.autoexpire, t1.preserve,
t1.deletepending, t4.recgroup, t2.basename, t2.filesize,
t2.width, t2.height, t2.fps, t5.videoprop,
t5.subtitletypes
FROM mythconverg.recorded t1
LEFT OUTER JOIN mythconverg.recgroups t4
ON ( t1.recgroupid = t4.recgroupid )
LEFT OUTER JOIN mythconverg.recordedprogram t5
ON ( ( t1.chanid = t5.chanid ) AND ( t1.starttime = t5.starttime ) )
LEFT OUTER JOIN mythconverg.recordedfile t2
ON ( t1.recordedid = t2.recordedid )
LEFT OUTER JOIN mythconverg.channel t3
ON ( t1.chanid = t3.chanid )
WHERE ( ( ( t1.deletepending=1 ) ) )
ORDER BY t1.starttime ASC;
]\","one row");
Close Database Connection(dbc);
close(dtConfig,nosave);
show(nrows(rc)==1);
That selects about half the records, all pretty old, that are hung with deletepending. Time to get bold…
Phase 3: do it.
WARNING, DANGEROUS CODE FOLLOWS, YOU MIGHT WIPE OUT YOUR DATABASE WITH UPDATE AND SET.
// grab user name and password from the XML config file
dtConfig = Open( "F:\mythconfig.xml", invisible, XML Wizard( 0, guess( "huge" ) ) );
dbc=CreateDatabaseConnection("DSN=Mythtv;UID=" || dtConfig:UserName[1] || ";PWD=" || dtConfig:Password[1]);
rc=executesql(dbc,"\[
UPDATE mythconverg.recorded t1
LEFT OUTER JOIN mythconverg.recgroups t4
ON ( t1.recgroupid = t4.recgroupid )
LEFT OUTER JOIN mythconverg.recordedprogram t5
ON ( ( t1.chanid = t5.chanid ) AND ( t1.starttime = t5.starttime ) )
LEFT OUTER JOIN mythconverg.recordedfile t2
ON ( t1.recordedid = t2.recordedid )
LEFT OUTER JOIN mythconverg.channel t3
ON ( t1.chanid = t3.chanid )
SET t1.autoexpire=1, t1.deletepending=0
WHERE ( ( ( t1.deletepending=1 ) ) );
]\","update");
Close Database Connection(dbc);
close(dtConfig,nosave);
The deletependings are no longer pending, and the autoexpire is back on. Time to wait and see.
The next morning: I think it is working; should have made better notes. Here’s the oldest files still hanging around before the morning news shows get recorded (6:50AM Monday):
-rw-r--r-- 1 mythtv mythtv 1.9G Mar 22 2018 1111_20180322080000.ts
-rw-r--r-- 1 mythtv mythtv 3.2G Mar 22 2018 1051_20180322080000.ts
-rw-rw-rw- 1 mythtv mythtv 1.1M Mar 22 2018 1111_20180322043700.ts.png
-rw-r--r-- 1 mythtv mythtv 11G Mar 22 2018 1171_20180322060700.ts
-rw-r--r-- 1 mythtv mythtv 5.6G Mar 22 2018 1111_20180322063000.ts
-rw-rw-rw- 1 mythtv mythtv 1.4M Mar 22 2018 1051_20180322033500.ts.png
-rw-r--r-- 1 mythtv mythtv 3.2G Mar 22 2018 1051_20180322070000.ts
-rw-r--r-- 1 mythtv mythtv 5.7G Mar 22 2018 1051_20180322060700.ts
-rw-r--r-- 1 mythtv mythtv 3.2G Mar 22 2018 1051_20180322053700.ts
-rw-r--r-- 1 mythtv mythtv 1.6G Mar 22 2018 1111_20180322053700.ts
-rw-r--r-- 1 mythtv mythtv 6.4G Mar 22 2018 1051_20180322043700.ts
-rw-r--r-- 1 mythtv mythtv 5.4G Mar 22 2018 1171_20180322043700.ts
-rw-rw-rw- 1 mythtv mythtv 1.4M Mar 22 2018 1171_20180322030000.ts.png
-rw-rw-rw- 1 mythtv mythtv 3.4M Mar 22 2018 1051_20180322030100.ts.png
-rw-r--r-- 1 mythtv mythtv 2.1G Mar 22 2018 1111_20180322043700.ts
-rw-rw-rw- 1 mythtv mythtv 511K Mar 22 2018 1111_20180322030000.ts.png
-rw-r--r-- 1 mythtv mythtv 6.6G Mar 22 2018 1051_20180322033500.ts
-rw-r--r-- 1 mythtv mythtv 3.8G Mar 22 2018 1111_20180322033500.ts
-rw-r--r-- 1 mythtv mythtv 3.3G Mar 21 2018 1171_20180322030000.ts
-rw-r--r-- 1 mythtv mythtv 3.7G Mar 21 2018 1051_20180322030100.ts
-rw-r--r-- 1 mythtv mythtv 2.4G Mar 21 2018 1111_20180322030000.ts
-rw-r--r-- 1 mythtv mythtv 3.3G Mar 21 2018 1051_20180321233000.ts
-rw-rw-rw- 1 mythtv mythtv 725K Mar 21 2018 1111_20180321200000.ts.png
-rw-rw-rw- 1 mythtv mythtv 2.6M Mar 21 2018 1051_20180321200000.ts.png
-rw-r--r-- 1 mythtv mythtv 2.9G Mar 21 2018 1051_20180321200000.ts
-rw-r--r-- 1 mythtv mythtv 1.8G Mar 21 2018 1111_20180321200000.ts
-rw-rw-rw- 1 mythtv mythtv 897K Mar 21 2018 1111_20180321163000.ts.png
-rw-r--r-- 1 mythtv mythtv 3.2G Mar 21 2018 1051_20180321163000.ts
-rw-r--r-- 1 mythtv mythtv 2.0G Mar 21 2018 1111_20180321163000.ts
-rw-r--r-- 1 mythtv mythtv 2.0G Mar 21 2018 1111_20180321160000.ts
-rw-r--r-- 1 mythtv mythtv 102M Mar 21 2018 1171_20180321160000.ts
-rw-r--r-- 1 mythtv mythtv 26G Mar 21 2018 1051_20180321110000.ts
-rw-r--r-- 1 mythtv mythtv 8.1G Mar 21 2018 1111_20180321110000.ts
-rw-r--r-- 1 mythtv mythtv 3.3G Mar 21 2018 1171_20180321100000.ts
-rw-r--r-- 1 mythtv mythtv 6.2G Mar 21 2018 1051_20180321100000.ts
-rw-r--r-- 1 mythtv mythtv 3.9G Mar 21 2018 1111_20180321100000.ts
-rw-rw-rw- 1 mythtv mythtv 743K Mar 20 2018 1281_20180321010000.ts.png
-rw-rw-rw- 1 mythtv mythtv 867K Mar 20 2018 1281_20180321002000.ts.png
drwxr-xr-x 12 root root 4.0K Mar 20 2018 ..
drwx------ 2 root root 16K Feb 28 2018 lost+found
Those three programs used about 40GB last Friday. At 9:30, the last 13.4G in the recorded table have been deleted. I’m not sure where the other 15G came from. Maybe the delete queue already had some files. Maybe these were added in oldest-first order, after some others...I try to not record daytime TV, so it may be tonight before I see more files being deleted. (Yes, it's happening!) I hope the png (giant) thumbnails take care of themselves too, too early to tell.
What I learned about JMP and SQL: there is a really simple way to load an SQL table into a JMP data table if no joining is needed (File→Database→Open Table.) There is a really nice power tool for reading tables that must be joined (File→Database→Query Builder.) Both of those also generate a JSL script in the table that can be reused. And, if you need to update the database you’ll need to use the ExecueSQL() function, but you can get the Query Builder to make the SQL that is almost what you need, then change it do do an UPDATE/SET.
I’ve done some of the SQL work outside of JMP in the past. I found it really nice to use JSL to do some non-SQL work (creating dummy files, making graphs of disk usage) that helped me understand the database better.
https://code.mythtv.org/cgit/mythtv/tree/mythtv/programs/mythbackend/autoexpire.cpp starting point for MythTV source code related to cleaning up old files
https://en.wikipedia.org/wiki/Cord-cutting
Looks like everything is back to normal. This final graph shows how different stations have optimized their available bandwidth. The bandwidth can be divided up into one or more channels; more channels reduces means less data to make a high quality image. One of the local stations also chose to go with 720 60FPS rather than 1080 30FPS which means it isn't an apples-to-apples comparison, maybe. Each of the high-lighted channels probably has one or more other channels from the same station it is sharing bandwidth with. The grayed stations at the bottom may be running multiple old movies at the same time, or shopping channels. They were removed from the legend.
Comments