Wiz.bin SQL importer

[wnjj]

Originally Posted by wnjj

There must be some Visual C++ dll dependencies that aren't happy on your machines. I only ran it on mine which has the compiler installed. I'll try it on a machine without one and see what it needs and/or see if I can compile it without needing the DLL's.

I recompiled with static runtime libraries. Previously you needed the VS2015 runtimes for XP installed. I updated the .zip in the first post.

[brandypuff]

Can I use this tool to somehow query the wiz.bin database? It's not clear how to do this with SQLlite as the wiz.bin tables would need to be imported into SQLlite. I am looking for a way to be able to list my recordings in a formatted output.

[wnjj]

Originally Posted by brandypuff

Can I use this tool to somehow query the wiz.bin database? It's not clear how to do this with SQLlite as the wiz.bin tables would need to be imported into SQLlite. I am looking for a way to be able to list my recordings in a formatted output.

It extracts the entire wiz.bin into a text file (specified by -data) that includes SQL commands for use in loading into a SQL DB. It's meant to do a one time dump from wiz.bin to a DB. You can always parse that text file for the information you need.

If you are looking for something that runs a little faster and very regularly (i.e. daily) for just recordings, the code could be used as a base for a utility that only pulls those and outputs them in the exact format you want.

[brandypuff]

i was able to import the data into a SQLlite database but it won't be easy to figure out how the different tables are linked. I want to do a listing like the Sage Webserver version 2.25 outputs for Sage Recordings. I need the included file sizes since i want to know what recordings are taking up all my space but this data is not in the database. I know that i can view the recording directory but would like to have a formatted output like the web browser provides. The XML button on the output creates an empty xml file, at least with this version of the WebUI. I think i am spiraling down a rat hole with this so may just give up soon.

[graywolf]

@WNJJ - happen to have a windows compiled version of this that works with "DB file version 87"?

Just ried this and getting

DB file version 87 greater than supported version 86.

[wnjj]

Originally Posted by graywolf

@WNJJ - happen to have a windows compiled version of this that works with "DB file version 87"?

Just ried this and getting

When did you last download it? The .zip in the first post was update for version 87 some time ago. Unless I messed up and put the wrong binary in? I just downloaded the .exe in the .zip and it runs on my version 87 DB.

[graywolf]

Originally Posted by wnjj

When did you last download it? The .zip in the first post was update for version 87 some time ago. Unless I messed up and put the wrong binary in? I just downloaded the .exe in the .zip and it runs on my version 87 DB.

DOH! Thanks.
That was it. Had an old copy of it.

Now to play around with it some.

[gary201]

I gave this gadget a try and thought you might be interested in this output observed.

M:\tmp>sqlite\sqlite3 wiz.db < schema.sql
M:\tmp>sqlite\sqlite3 wiz.db < data.sql
Error: near line 1255177: near "-": syntax error
Error: near line 1255179: near "-": syntax error
Error: near line 1255181: near "-": syntax error
Error: near line 1255183: near "-": syntax error
Error: near line 1255185: near "-": syntax error
Error: near line 1255187: near "-": syntax error
Error: near line 1255189: near "-": syntax error

The output continues for a screen or two. Here are lines 1255165-1255180.

UPDATE person_headshotURL SET headURL="json.schedulesdirect.org/20141201/image/v3/AllPhotos/554845/554845_v3_ba" WHERE personID=20910726;
INSERT INTO person VALUES (22080945,1,"Sherman Howard",4294883849,0,0,0,65535);
INSERT INTO person_headshotURL VALUES (22080945,"json.schedulesdirect.org/20141201/image/v9/AllPhotos/83447/83447_v9_ba");
INSERT INTO person_headshotURL VALUES (22080945,"json.schedulesdirect.org/20141201/image/v3/AllPhotos/83447/83447_v3_ba");
INSERT INTO person VALUES (22080946,1,"Ray Chang",282454,0,0,0,65535);
INSERT INTO person_headshotURL VALUES (22080946,"json.schedulesdirect.org/20141201/image/v9/AllPhotos/282454/282454_v9_ba");
INSERT INTO person_headshotURL VALUES (22080946,"json.schedulesdirect.org/20141201/image/v2/AllPhotos/282454/282454_v2_ba");
INSERT INTO person VALUES (22080947,1,"Todd Bridges",4294886017,0,0,0,65535);
INSERT INTO person_headshotURL VALUES (22080947,"json.schedulesdirect.org/20141201/image/v9/AllPhotos/81279/81279_v9_ba");
INSERT INTO person_headshotURL VALUES (22080947,"json.schedulesdirect.org/20141201/image/v3/AllPhotos/81279/81279_v3_ba");
UPDATE person SET mediamask=1,name="Rob Morgan",extID=402669,dateOfBirth=0,dateOfDeath=0,birthPlace=0,headshotImageId=65535 WHERE personID=21636930;
UPDATE person_headshotURL SET headURL="json.schedulesdirect.org/20141201/image/v9/AllPhotos/402669/402669_v9_ba" WHERE personID=21636930;
UPDATE person_headshotURL SET headURL="json.schedulesdirect.org/20141201/image/v2/AllPhotos/402669/402669_v2_ba" WHERE personID=21636930;
UPDATE person SET mediamask=1,name="Olivia Hack",extID=233083,dateOfBirth=0,dateOfDeath=0,birthPlace=0,headshotImageId=65535 WHERE personID=21050010;
UPDATE person_headshotURL SET headURL="json.schedulesdirect.org/20141201/image/v9/AllPhotos/233083/233083_v9_ba" WHERE personID=21050010;
UPDATE person_headshotURL SET headURL="json.schedulesdirect.org/20141201/image/v2/AllPhotos/233083/233083_v2_ba" WHERE personID=21050010;

[wnjj]

I don’t see any “-“ in those lines. I’m thinking the line number in the error message is off for some reason. Can you search for the “-“ or maybe for “,-“? I suspect you may have an oversized negative number in there and that’s what’s causing the issues with the sort. I don’t remember if there are usually legit negative numbers in there but if there are too many, try removing large amounts of rows from the data file until the error goes away so you know where.

I’ll bet the bad numbers are part of the “INSERT INTO show” lines.

[gary201]

Originally Posted by wnjj

I don’t see any “-“ in those lines. I’m thinking the line number in the error message is off for some reason. Can you search for the “-“ or maybe for “,-“? I suspect you may have an oversized negative number in there and that’s what’s causing the issues with the sort. I don’t remember if there are usually legit negative numbers in there but if there are too many, try removing large amounts of rows from the data file until the error goes away so you know where.

I’ll bet the bad numbers are part of the “INSERT INTO show” lines.

I use Notepad++ (great editor) to view the data.sql. I searched for ",-" and found 165 occurrences of those two characters. It appears like they are all part of a quoted string in INSERT INTO lines as you suspected.

Here is an example of one of these entries:

INSERT INTO show VALUES (21468416,4,0,20988058,"Cactus, Tomb of Christ, & Flying Solar Panel!!!","VIDEO DETAILS (Click Show More)
Yard Art GPS: 32.725843,-100.908765
White Buffalo & Arrow GPS: 32.717092, -100.918454
Tomb of Christ GPS: 33.181566, -102.321952
Metal Art GPS: 33.257137,-103.313550
My name is Eric and I travel with my cat, Jax in a 2001 Chevy Fleetwood Tioga Arrow 24D Class C RV.  We travel about 35 miles a day chasing 70 degrees year-round.  Here are some popular questions answered:

Cameras (Updated)* 
*GoPro 6 for Vlogging (2.7K Downscaled to 1080p 60fps)
*Canon 80D for Vlogging (1080p 60fps) 
*Canon 10-18mm STM 5.6 STM Lens
*Canon 50mm Pancake Lens for Bokeh Macro
*GoPro Hero 3 Silver for Driving Narration
*GoPro Hero 4 Black for Timelapses (80D for Nightlapses)
*SJ4000 for driving shots out the window.  
*Canon Vixia HF M500 with Canon WD-H43 Wide Angle Conversion lens for Zoom Video shots.  
Stabilizer:  Feiyu Tech 4GS 3 axis gimbal.
Time Lapses: Gopro Hero 4: 2 second intervals. Speed up 1200x, cropped 4K down to 1080 for panning
Night Lapses: Gopro Hero 4 Black manual settings: 800 ISO, 30 second Shutter, 3000K WB, Protune On
Slow Motion: Shot 1080p 240fps. Reduced to 8% in Post Production
Audio: Sony ICD-PX333 (Audio swapped in post production)
Audio on Canon 80D: Dual Rode Video Micro shotgun mics with dead kitten wind shields
Editing Laptop: 2015 MacBook Pro 2.8ghz i7 16GB Ram, 500 SSD
Editing Software: Adobe Premiere Pro CC
Editing Encoder: Adobe Encoder - Preset: MP4 VBR H.264 16mbps
Aerial Drone Shots: DJI Phantom 3 Standard Shot in 2.7K Downscaled to 1080p
RV MPG: 7-11mpg depending on generator use.  (7.4L 454 Chevy) 65,000 miles
Scooter: 2011 Honda PCX 125 with 4K miles. (99mpg)
Solar: 500 watts on tilting brackets on roof.  5 AGM batteries totaling 400 amp hours
Mobile Wifi: AT&T Unlimited 
Music: www.youtube.com/audiolibrary
Jax is a MaineCoon/Ragdoll Tabby mix. He weighs 23lbs. Born April 21st 2010.
RV is 24 Feet Long
Tennessee Hayride by Audionautix is licensed under a Creative Commons Attribution license (https://creativecommons.org/licenses/by/4.0/) 
Artist: http://audionautix.com/ 


Email: nwnomadicfanatic@gmail.com

Mail:
Eric Jacobs
PO Box 1463
Olympia, WA 98507",0,0,0,0,0,1511828522300,0,"TMP1516566533636",21468416,0,0,0,0,0,0,0,0);

This would be a YouTube recording that was made by the YouTube plugin for Sage.

I did, however, find other instances such as this one.

UPDATE mediaFile SET mediamask=5,createWatchCount=32710,videoDirectory="/media/nas1media/SageTV/Imports/PlayOn/Altered Carbon/Season 1",name="PlayOn/Altered Carbon/Season 1/Altered Carbon - s01e01 Out of the Past.mp4",archive=1,-=infoAiringID=21908799,host="media3",encodedBy="",mediaType=0,mediaSubtype=0,generalType=61,acquisitionTech=94,forcedComplete=1,thumbnailFile="/media/nas1media/SageTV/Imports/PlayOn/Altered Carbon/Season 1/Altered Carbon - s01e01 Out of the Past.mp4",thumbnailOffset=49119,thumbnailSize=89758,fileFormat="f=Quicktime;dur=3548790;br=3265000;MEpisodeNumber=1;MSeriesInfoID=200332331;MSeasonNumber=1;MMediaType=TV;MDirector=Miguel Sapochnik;MTitle=Altered Carbon;MMuxer=Lavf57.56.101;MExternalID=EPmt332331S01E01;MIMDBID=tt5989942;MGenre=Drama/Science-Fiction/Thriller;Mcompatible_brands=isomiso2avc1mp41;MActor=Joel Kinnaman -- Takeshi Kovacs\;Martha Higareda -- Kristin Ortega\;Ato Essandoh -- Vernon Elliot\;Dichen Lachman -- Reileen Kawahara\;James Purefoy -- Laurens Bancroft\;Will Yun Lee -- Takeshi Kovacs\;Waleed Zuaiter -- Abboud\;Trieu Tran -- Leung\;Tamara Taylor -- Oumou Prescott\;Hiro Kanagawa -- Captain Tanaka\;Hayley Law -- Lizzie Elliot\;Renée Elise Goldsberry -- Quellcrist Falconer\;Chris Conner -- Poe\;Kristin Lehman -- Miriam Bancroft;Mminor_version=512;MParentalRating=TVM;MUserRating=80;MOriginalAirDate=1517547600000;MComment=Waking up in a new body 250 years after his death, Takeshi Kovacs discovers he's been resurrected to help a titan of industry solve his own murder.;MMediaProviderDataID=332331;MMediaProviderID=tvdb;MWriter=Laeta Kalogridis;Mmajor_brand=isom;MArtist=Altered Carbon;MScrapedBy=Phoenix;MLanguage=und;MGuest=Will Yun Lee;MThumbnailOffset=1448334125;MMediaTitle=Altered Carbon;MThumbnailSize=89758;MScrapedDate=1532559914695;Mshow=Altered Carbon;MDescription=Waking up in a new body 250 years after his death, Takeshi Kovacs discovers he's been resurrected to help a titan of industry solve his own murder.;MEpisodeName=Out of the Past;MRunningTime=3300000;[bf=vid;f=H.264;br=3067000;index=0;fps=29.97;w=1280;h=720;cs=yuv420p;][bf=aud;f=AAC;br=190000;index=1;sr=44100;ch=2;lang=und;]" WHERE mediaFileID=21908524;

Note the "-=infoAiringID".

There's a bunch more like these.

UPDATE mediaFile SET mediamask=5,createWatchCount=33568,videoDirectory="/media/nas2media/SageTV/Recordings",name="",archive=0,-=infoAiringID=22022740,host="media3",encodedBy="HDHomeRun 10434ff9 Tuner 0 WUHFDT",mediaType=0,mediaSubtype=0,generalType=61,acquisitionTech=92,forcedComplete=0,thumbnailFile="",thumbnailOffset=0,thumbnailSize=0,fileFormat="f=MPEG2-PS;br=3480000;MSeriesInfoID=230963;[bf=vid;f=MPEG2-Video;index=0;main=yes;tag=e0;fps=59.94006;fpsn=60000;fpsd=1001;ar=1.777778;arn=16;ard=9;w=1280;h=720;cs=yuv420p;][bf=aud;f=AC3;br=384000;index=1;main=yes;tag=bd-80010000;sr=48000;ch=6;lang=eng;][bf=aud;f=AC3;br=96000;index=2;tag=bd-81010000;sr=48000;ch=1;lang=spa;]" WHERE mediaFileID=22078797;

[wnjj]

The "-" in the first item is just part of the text of the show description and normal.

The other ones in the INSERT statements that cause the syntax errors are a bug. There are unused boolean data fields in the tables that were only there to trip up decryption of the previously non-open file format. They aren't supposed to be added to the SQL but I missed an "if" for the INSERT statements.

I fixed that and re-uploaded a new .zip in the first post if you want to play any more with it.

[gary201]

Originally Posted by wnjj

The "-" in the first item is just part of the text of the show description and normal.

The other ones in the INSERT statements that cause the syntax errors are a bug. There are unused boolean data fields in the tables that were only there to trip up decryption of the previously non-open file format. They aren't supposed to be added to the SQL but I missed an "if" for the INSERT statements.

I fixed that and re-uploaded a new .zip in the first post if you want to play any more with it.

I will, thanks. I just though it was interesting and wondered whether this could be a contributor to that other problem w/sorting we've been discussing over here (https://forums.sagetv.com/forums/showthread.php?t=65579). I won't get to this until the weekend. I really only did this because I was curious what was in the wiz.bin.

[wnjj]

Originally Posted by gary201

I really only did this because I was curious what was in the wiz.bin.

That’s the same reason I built the tool originally.

[KeithAbbott]

I have a fairly large wiz.bin, it's 56 meg in size. I made a copy of it when Sage was shut down, and ran wiz2sql against the copy. It appears to have created a partial data.sql file (122 meg in size), here is the tail-end of the file:

UPDATE mediaFile SET mediamask=5,createWatchCount=41970,videoDirectory="/var/media/tv",name="",archive=0,infoAiringID=21084638,host="SageTV",encodedBy="DCT-HDHomeRun Prime Tuner 13256C0B-1 on 192.168.1.40:9000 WWJDT",mediaType=0,mediaSubtype=0,generalType=61,acquisitionTech=92,forcedComplete=0,thumbnailFile="",thumbnailOffset=0,thumbnailSize=0,fileFormat="f=MPEG2-TS;" WHERE mediaFileID=21157571;
UPDATE mediaFile_file SET currFile="/var/media/tv/TheLateLateShowWithJamesCorden-50CentHayleyAtwellSnowPatrol-21084638-35.ts",times1=1537598324347,times2=1537598292070 WHERE mediaFileID=21157571;
DELETE FROM mediaFile WHERE mediaFileID=21157571;
DELETE FROM mediaFile_file WHERE mediaFileID=21157571;
UPDATE mediaFile SET mediamask=5,createWatchCount=41970,videoDirectory="/var/media/tv",name="",archive=0,infoAiringID=21064001,host="SageTV",encodedBy="DCT-HDHomeRun Prime Tuner 13256C0B-0 on 192.168.1.40:9000 WDIVDT",mediaType=0,mediaSubtype=0,generalType=61,acquisitionTech=92,forcedComplete=0,thumbnailFile="",thumbnailOffset=0,thumbnailSize=0,fileFormat="f=MPEG2-PS;dur=25925;br=2451000;[bf=vid;f=MPEG2-Video;index=0;main=yes;tag=e0;fps=29.97003;fpsn=30000;fpsd=1001;ar=1.333333;arn=4;ard=3;w=720;h=480;lace=1;cs=yuv420p;][bf=aud;f=AC3;br=384000;index=1;main=yes;tag=bd-80010000;sr=48000;ch=6;][bf=aud;f=AC3;br=192000;index=2;tag=bd-81010000;sr=48000;ch=2;]" WHERE mediaFileID=21157570;
UPDATE mediaFile_file SET currFile="/var/media/tv/LateNightWithSethMeyers-KevinHartKristenBellHariNefBenSesar-21064001-0.ts",times1=1537598296550,times2=1537598352065 WHERE mediaFileID=21157570;
UPDATE airing SET mediamask=1,showID=20955701,stationID=20359,airTime=1537598296544,duration=55520,partsB=0,miscB=519,prB=5,persist=1 WHERE airingID=21064001;
DELETE FROM mediaFile WHERE mediaFileID=21148790;
DELETE FROM mediaFile_file WHERE mediaFileID=21148790;
DELETE FROM agent WHERE agentID=20686486;

I would have expected to see a commit at the very end of the file. win2sql is not returning any error messages when I run it, so I'm not sure what's going on. Also, it doesn't appear to create any sort of log file. Any suggestions?

[wnjj]

Originally Posted by KeithAbbott

I have a fairly large wiz.bin, it's 56 meg in size. I made a copy of it when Sage was shut down, and ran wiz2sql against the copy. It appears to have created a partial data.sql file (122 meg in size), here is the tail-end of the file:

UPDATE mediaFile SET mediamask=5,createWatchCount=41970,videoDirectory="/var/media/tv",name="",archive=0,infoAiringID=21084638,host="SageTV",encodedBy="DCT-HDHomeRun Prime Tuner 13256C0B-1 on 192.168.1.40:9000 WWJDT",mediaType=0,mediaSubtype=0,generalType=61,acquisitionTech=92,forcedComplete=0,thumbnailFile="",thumbnailOffset=0,thumbnailSize=0,fileFormat="f=MPEG2-TS;" WHERE mediaFileID=21157571;
UPDATE mediaFile_file SET currFile="/var/media/tv/TheLateLateShowWithJamesCorden-50CentHayleyAtwellSnowPatrol-21084638-35.ts",times1=1537598324347,times2=1537598292070 WHERE mediaFileID=21157571;
DELETE FROM mediaFile WHERE mediaFileID=21157571;
DELETE FROM mediaFile_file WHERE mediaFileID=21157571;
UPDATE mediaFile SET mediamask=5,createWatchCount=41970,videoDirectory="/var/media/tv",name="",archive=0,infoAiringID=21064001,host="SageTV",encodedBy="DCT-HDHomeRun Prime Tuner 13256C0B-0 on 192.168.1.40:9000 WDIVDT",mediaType=0,mediaSubtype=0,generalType=61,acquisitionTech=92,forcedComplete=0,thumbnailFile="",thumbnailOffset=0,thumbnailSize=0,fileFormat="f=MPEG2-PS;dur=25925;br=2451000;[bf=vid;f=MPEG2-Video;index=0;main=yes;tag=e0;fps=29.97003;fpsn=30000;fpsd=1001;ar=1.333333;arn=4;ard=3;w=720;h=480;lace=1;cs=yuv420p;][bf=aud;f=AC3;br=384000;index=1;main=yes;tag=bd-80010000;sr=48000;ch=6;][bf=aud;f=AC3;br=192000;index=2;tag=bd-81010000;sr=48000;ch=2;]" WHERE mediaFileID=21157570;
UPDATE mediaFile_file SET currFile="/var/media/tv/LateNightWithSethMeyers-KevinHartKristenBellHariNefBenSesar-21064001-0.ts",times1=1537598296550,times2=1537598352065 WHERE mediaFileID=21157570;
UPDATE airing SET mediamask=1,showID=20955701,stationID=20359,airTime=1537598296544,duration=55520,partsB=0,miscB=519,prB=5,persist=1 WHERE airingID=21064001;
DELETE FROM mediaFile WHERE mediaFileID=21148790;
DELETE FROM mediaFile_file WHERE mediaFileID=21148790;
DELETE FROM agent WHERE agentID=20686486;

I would have expected to see a commit at the very end of the file. win2sql is not returning any error messages when I run it, so I'm not sure what's going on. Also, it doesn't appear to create any sort of log file. Any suggestions?

Those final lines are the “delta transactions” that Narflex has referred to and are at the end of wiz.bin. I don’t recall if there’s anything special at the end but I don’t think so. I could probably add a “success” message but right now no news is good news.

[KeithAbbott]

I am getting syntax errors when I execute the data.sql statements. The syntax errors all point to insert statements that contain more than one row of values, for example the person_by_name table. Is there a maximum number of rows in one value clause in Sqlite? Here's a snippet of the generated SQL:

INSERT INTO person_by_name VALUES 
(1,102992),
(2,101855),
(3,89270),
(4,80178),
(5,89412),
(6,18543),
.
.
.
(108972,56386),
(108973,31022),
(108974,64913),
(108975,68703),
(108976,77817),
(108977,42283)
;

[wnjj]

Originally Posted by KeithAbbott

I am getting syntax errors when I execute the data.sql statements. The syntax errors all point to insert statements that contain more than one row of values, for example the person_by_name table. Is there a maximum number of rows in one value clause in Sqlite? Here's a snippet of the generated SQL:

INSERT INTO person_by_name VALUES 
(1,102992),
(2,101855),
(3,89270),
(4,80178),
(5,89412),
(6,18543),
.
.
.
(108972,56386),
(108973,31022),
(108974,64913),
(108975,68703),
(108976,77817),
(108977,42283)
;

I found info online that says the SQL statement size is limited to 1M so it looks like those are over that. They can easily be split in half manually if you want to prove that. When I get time I’ll add code to do that automatically.

[KeithAbbott]

Never mind, it looks like I was using an older version of Sqlite. I upgraded to Sqlite 3.25.2, and it appears to be working fine.

[stuckless]

I don't have windows, but i was wondering if someone could post the "sql" for the schema.

wiz2sql.exe wiz.bin -schema schema.sql

Just curious as to what all the sagetv objects and fields are from a database point of view.

[wnjj]

Originally Posted by stuckless

I don't have windows, but i was wondering if someone could post the "sql" for the schema.

wiz2sql.exe wiz.bin -schema schema.sql

Just curious as to what all the sagetv objects and fields are from a database point of view.

You can compile it on Linux.

Here it is (attached).