SageTV Community  

Go Back   SageTV Community > SageTV Development and Customizations > SageTV Github Development

Notices

SageTV Github Development Discussion related to SageTV Open Source Development. Use this forum for development topics about the Open Source versions of SageTV, hosted on Github.

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 09-27-2015, 06:51 PM
wnjj wnjj is offline
Sage Icon
 
Join Date: Jan 2009
Posts: 1,052
Wiz.bin SQL importer

I wrote a standalone C++ program that reads in a wiz.bin file and generates database table schema (CREATE TABLE) and data import (INSERT) SQL commands. Currently SQLite and Postgres formats are supported with the only differences being a few data types and syntax. I recommend SQLite since it's very lightweight and requires no setup.

It's not necessarily complete but seemed to run fine on my version7 (wiz.bin ver 75) and version9 (wiz.bin ver 85 & ver 86). I have not tested any other versions so there could be errors in the format definitions for those which can be fixed easily.

The code is 4 .cpp and 3 .h files and compiles as a Windows console app or Linux.

I'm not sure what explicit purpose this program serves yet but it gave me an excuse to learn the insides of wiz.bin as well as confidently and portably save my TV show history. It could potentially be modified to write wiz.bin files which would allow for SQL data edits (repairs) before exporting back to wiz.bin.

I've included the Windows .exe inside the .zip. Compile on Linux with:
Code:
g++ -o wiz2sql Wiz2SQL.cpp Wizard.cpp Tables.cpp TableField.cpp
Run the program without any arguments for a usage description. An example run with arguments looks like this:

Code:
wiz2sql.exe wiz.bin -schema schema.sql -data data.sql
To import into SQLite:
Code:
sqlite3 wiz.db < schema.sql
sqlite3 wiz.db < data.sql
Play with it, ignore it, critique it, modify it, etc. One thing it lacks right now is support for UTF strings. It works fine for single-byte character sets but will fall apart for 2-byte and more chars. The function that reads strings from wiz.bin is Wizard::readUTF(). Refer to the readUTF function in Sage's FastRandomFile.java for the extra code needed for I18N support. I haven't yet figured out what kind of string format is needed in C++ to handle those. Java apparently uses 1 or 2-byte chars as needed. I also had to filter binary (non-printable and non-whitespace) characters from string fields because those are not handled in database text columns. I had some data like that in some photo files containing embedded binary metadata. That data isn't important anyway and displays as gibberish in SageTV when I press info on those photos.

The tables are built to follow the classes used in SageTV for the various DB objects (show, person, etc). In wiz.bin, related tables were simply serialized inline within the individual records. For these, Wiz2SQL creates related tables like "show_person" which represents "person" data related to a "show". For those tables, the primary ID column is the original table wiz ID (e.g. showID).

In later versions of wiz.bin, there are also indexes which provide a list of ID's, sorted by something specific. Those are built as tables with "_by_" in the name like "titles_by_name" with a 'pos' (position) column and an ID column.

Also, feel free to post questions about the wiz.bin format so Jeff can correct my answers.

EDIT: Modified ZIP attachment for Wizard version 87 - NOTE: Not tested.

EDIT (09/27/2018): Fixed bug with UPDATE that created a bogus "-" column title.
Attached Files
File Type: zip Wiz2SQL.zip (152.0 KB, 80 views)

Last edited by wnjj; 09-27-2018 at 11:38 PM.
Reply With Quote
  #2  
Old 09-27-2015, 08:04 PM
PLUCKYHD PLUCKYHD is offline
SageTVaholic
 
Join Date: Dec 2007
Posts: 6,257
Thanks for sharing. I am not sure of the usage case yet either but I can see it coming in handy. I definitely will play with it. Thanks again
Reply With Quote
  #3  
Old 09-27-2015, 08:17 PM
MrD MrD is offline
Sage Aficionado
 
Join Date: Feb 2005
Location: Washington DC
Posts: 387
Quote:
Originally Posted by wnjj View Post

I'm not sure what explicit purpose this program serves yet but it gave me an excuse to learn the insides of wiz.bin as well as confidently and portably save my TV show history. It could potentially be modified to write wiz.bin files which would allow for SQL data edits (repairs) before exporting back to wiz.bin.
This
__________________
[size=1]-MrD
=============
Linux Server 7.1.9 (1)HD300 (1) HD200 (1) HD100 (2) PC Clients
Intel Xeon L? 32Gb
CetonTV cable card /FIOS
Reply With Quote
  #4  
Old 09-28-2015, 04:25 AM
scoful's Avatar
scoful scoful is offline
Sage Aficionado
 
Join Date: Jun 2006
Location: Lawrence, Kansas
Posts: 371
Potential Future Move for Open Source to replace WIZ.BIN

It would make sense to me that WIZ.BIN be replaced with a SQL database if there's an advantage in speed and/or resilience and standardization for other things to connect to it.
I'd guess that SageTV already kind of behaves like a SQL database engine - I'm presuming clients and extenders don't directly look at WIZ.BIN and rely on the SageTV service / server app to hand data to them like an SQL server does, so there may not be any performance improvement.
__________________
SageTV 9 / 3 SageTV Clients / Ceton InfiniTV 6 / ComSkip
Reply With Quote
  #5  
Old 09-28-2015, 05:49 AM
KeithAbbott KeithAbbott is offline
Sage Icon
 
Join Date: Oct 2009
Location: Southeastern Michigan
Posts: 1,026
My biggest wiz.bin wishlist item would be some sort of compaction utility, which would leave the wiz.bin file in a state that optimizes efficiency within SageTV, along with hopefully reducing the likelihood of the wiz.bin getting "corrupted".
__________________
Server: MSI Z270 SLI Plus ATX Motherboard, Intel i3-7100T CPU, 16GB Memory, unRAID 6.3.2, SageTV & OpenDCT Dockers
Tuners: 2 x SiliconDust HDHomeRun Prime Cable TV Tuners, 2 x Hauppauge WinTV-HVR-2250 Dual TV Tuner Boards
Clients: Multiple HD300 Extenders
Miscellaneous: Multiple Sony RM-VLZ620 Universal Remote Controls
Reply With Quote
  #6  
Old 09-28-2015, 06:10 AM
stuckless's Avatar
stuckless stuckless is offline
SageTVaholic
 
Join Date: Oct 2007
Location: London, Ontario, Canada
Posts: 9,541
@wnjj - you should create a github project for this. People could then fork it, extend it, contribute back, etc.
Reply With Quote
  #7  
Old 09-28-2015, 08:54 AM
stanger89's Avatar
stanger89 stanger89 is offline
SageTVaholic
 
Join Date: May 2003
Location: Marion, IA
Posts: 15,186
Quote:
Originally Posted by KeithAbbott View Post
My biggest wiz.bin wishlist item would be some sort of compaction utility, which would leave the wiz.bin file in a state that optimizes efficiency within SageTV, along with hopefully reducing the likelihood of the wiz.bin getting "corrupted".
What makes you think that it doesn't already do that? I'm not saying it does, but I've got ~10 years of history in mine and it's not like it keeps growing, I think the size has been pretty stable for years.
Reply With Quote
  #8  
Old 09-28-2015, 10:36 AM
Tiki's Avatar
Tiki Tiki is offline
Sage Icon
 
Join Date: Feb 2005
Location: Southwest Florida, USA
Posts: 1,806
Quote:
Originally Posted by PLUCKYHD View Post
Thanks for sharing. I am not sure of the usage case yet either but I can see it coming in handy. I definitely will play with it. Thanks again
I wonder if this could easily be extended to allow you to merge two Wiz.Bin files?

Another idea would be if you could use this to roll-back from a newer wiz.bin format to an older format.
__________________
Server: Athlon II X4 630 CPU, Gigabyte MA770T-UD3P (AM3) Motherboard, VisionTek Radeon HD4670 PCIe w/ Component output to 50" CRT Rear-Projection HDTV (Mitsubishi), Win7-64Bit (Home Premium), 4GB RAM
Capture Devices (8 tuners): Colossus (x1), HD-PVR (x1), HDHR Prime (x2)
,USBUIRT (multi-zone)
Source:
Comcast Cable
Primary Client: Server Other Clients: (1) HD200, (1) HD300
Retired Equipment: MediaMVP, PVR150 (x2), PVR150MCE,
HDHR, HVR-2250
Reply With Quote
  #9  
Old 09-28-2015, 11:05 AM
wnjj wnjj is offline
Sage Icon
 
Join Date: Jan 2009
Posts: 1,052
Quote:
Originally Posted by Tiki View Post
Another idea would be if you could use this to roll-back from a newer wiz.bin format to an older format.

I was thinking of that too. A V9 to V7 might be nice for those who didn't back things up. I'll have to look at what data may be changed or lost going from V7 to V9 but I think it would work ok.
Reply With Quote
  #10  
Old 09-28-2015, 11:32 AM
PLUCKYHD PLUCKYHD is offline
SageTVaholic
 
Join Date: Dec 2007
Posts: 6,257
More thinking it would also be good for those of us () that prefer SQL filtering/querying as compared to sage's API. I know personally I can perform more dynamic and powerful searches using SQL.
Reply With Quote
  #11  
Old 09-28-2015, 11:39 AM
wnjj wnjj is offline
Sage Icon
 
Join Date: Jan 2009
Posts: 1,052
Quote:
Originally Posted by stanger89 View Post
What makes you think that it doesn't already do that? I'm not saying it does, but I've got ~10 years of history in mine and it's not like it keeps growing, I think the size has been pretty stable for years.
Mine is about 40MB after 6.5 years. There is no compression in wiz.bin. When I looked into it, a decent part of it is all of my photos and the large amount of ASCII "info" pulled from the JPG. Since nothing is compressed or shared, there are literally tens of thousands of strings like "Shutter Speed", flash settings, etc. The thing is, at 40MB I don't really notice a performance issue so maybe if it ain't broke...

The other kind of wasteful thing I noticed is that the FULL path to every mediafile is stored (actually 2x for photos). I have something like 3MB in just the paths of my photos without their name.
Reply With Quote
  #12  
Old 09-28-2015, 11:52 AM
KeithAbbott KeithAbbott is offline
Sage Icon
 
Join Date: Oct 2009
Location: Southeastern Michigan
Posts: 1,026
Quote:
Originally Posted by wnjj View Post
Mine is about 40MB after 6.5 years. There is no compression in wiz.bin. When I looked into it, a decent part of it is all of my photos and the large amount of ASCII "info" pulled from the JPG. Since nothing is compressed or shared, there are literally tens of thousands of strings like "Shutter Speed", flash settings, etc. The thing is, at 40MB I don't really notice a performance issue so maybe if it ain't broke...

The other kind of wasteful thing I noticed is that the FULL path to every mediafile is stored (actually 2x for photos). I have something like 3MB in just the paths of my photos without their name.
I seem to remember a forum post some time back saying that the SageTV server start up time increases as the wiz.bin grows larger. Maybe the file contents are loaded into an array in memory on startup?
__________________
Server: MSI Z270 SLI Plus ATX Motherboard, Intel i3-7100T CPU, 16GB Memory, unRAID 6.3.2, SageTV & OpenDCT Dockers
Tuners: 2 x SiliconDust HDHomeRun Prime Cable TV Tuners, 2 x Hauppauge WinTV-HVR-2250 Dual TV Tuner Boards
Clients: Multiple HD300 Extenders
Miscellaneous: Multiple Sony RM-VLZ620 Universal Remote Controls
Reply With Quote
  #13  
Old 09-28-2015, 12:36 PM
stanger89's Avatar
stanger89 stanger89 is offline
SageTVaholic
 
Join Date: May 2003
Location: Marion, IA
Posts: 15,186
If you've got a lot of history, the profiler (which does Intelligent Recording scheduling) can take a long time to start up, but I don't think it's in any way tied to the "physical" size of the database. Beside the delayed_carny_init or whatever property gets Sage started in seconds anyway (takes a while for IRs to show up though).
Reply With Quote
  #14  
Old 09-28-2015, 12:44 PM
Monedeath Monedeath is offline
Sage Expert
 
Join Date: Sep 2009
Location: Idaho
Posts: 508
Quote:
Originally Posted by scoful View Post
It would make sense to me that WIZ.BIN be replaced with a SQL database if there's an advantage in speed and/or resilience and standardization for other things to connect to it.
I'd guess that SageTV already kind of behaves like a SQL database engine - I'm presuming clients and extenders don't directly look at WIZ.BIN and rely on the SageTV service / server app to hand data to them like an SQL server does, so there may not be any performance improvement.
Your flipside is this:

Quote:
Originally Posted by Narflex View Post
It's "Jeff's Custom Object Database" format.

I wanted to have a true Object database where everything in it corresponded to an actual Java object that I could just use normally and not have to worry about making actual database queries throughout the source code. It also allowed me to write logic which keeps it synchronized between all the clients & server simultaneously without having to do server RPCs for every database query on the client (although there's likely replication logic in SQLite/MySQL that could do the same thing).

There's a huge amount of work to replacing it at this point...all the core SageTV code has been written expecting that all of the database items are actually just in memory Java objects that can be treated that way. Theoretically it could all have been done with Java object serialization...but that doesn't have the client/server sync, and 'delta transactions' that are part of what SageTV has.

Last edited by Monedeath; 09-28-2015 at 12:52 PM. Reason: Fixed formatting
Reply With Quote
  #15  
Old 09-28-2015, 12:46 PM
wnjj wnjj is offline
Sage Icon
 
Join Date: Jan 2009
Posts: 1,052
Quote:
Originally Posted by KeithAbbott View Post
I seem to remember a forum post some time back saying that the SageTV server start up time increases as the wiz.bin grows larger. Maybe the file contents are loaded into an array in memory on startup?
Yes, the DB is loaded into a big array of tables in Wizard.java.
Reply With Quote
  #16  
Old 09-28-2015, 12:50 PM
Narflex's Avatar
Narflex Narflex is offline
Sage
 
Join Date: Feb 2003
Location: Redondo Beach, CA
Posts: 6,301
Quote:
Originally Posted by scoful View Post
It would make sense to me that WIZ.BIN be replaced with a SQL database if there's an advantage in speed and/or resilience and standardization for other things to connect to it.
I'd guess that SageTV already kind of behaves like a SQL database engine - I'm presuming clients and extenders don't directly look at WIZ.BIN and rely on the SageTV service / server app to hand data to them like an SQL server does, so there may not be any performance improvement.
The prior post pasted my explanation about this before...nowhere near an easy project to replace the DB. And the clients do all have copies of the DB in RAM which are synced to the server in realtime. Extenders don't have copies in RAM (they are the 'thin clients').

Quote:
Originally Posted by KeithAbbott View Post
My biggest wiz.bin wishlist item would be some sort of compaction utility, which would leave the wiz.bin file in a state that optimizes efficiency within SageTV, along with hopefully reducing the likelihood of the wiz.bin getting "corrupted".
It already does this...there's Wizard.maintenance() which basically does garbage collection on the database daily.

Quote:
Originally Posted by wnjj View Post
Mine is about 40MB after 6.5 years. There is no compression in wiz.bin. When I looked into it, a decent part of it is all of my photos and the large amount of ASCII "info" pulled from the JPG. Since nothing is compressed or shared, there are literally tens of thousands of strings like "Shutter Speed", flash settings, etc. The thing is, at 40MB I don't really notice a performance issue so maybe if it ain't broke...

The other kind of wasteful thing I noticed is that the FULL path to every mediafile is stored (actually 2x for photos). I have something like 3MB in just the paths of my photos without their name.
There is a 'compactDB' mode in SageTV which is what we used on embedded devices (and on Fiber). This reduces storage of various things...specifically what you mention; but unless you really need that extra 50MB of memory back; it's not worth switching over to it.

Quote:
Originally Posted by KeithAbbott View Post
I seem to remember a forum post some time back saying that the SageTV server start up time increases as the wiz.bin grows larger. Maybe the file contents are loaded into an array in memory on startup?
Yes, it does grow over time...just not that much. And it is all loaded into various arrays in memory...the whole DB lives in RAM and then gets saved out to disk (and it appends it with transactional data between maintenance/compression cycles).
__________________
Jeffrey Kardatzke
Google
Founder of SageTV
Reply With Quote
  #17  
Old 09-28-2015, 01:06 PM
KeithAbbott KeithAbbott is offline
Sage Icon
 
Join Date: Oct 2009
Location: Southeastern Michigan
Posts: 1,026
Quote:
Originally Posted by Narflex View Post
It already does this...there's Wizard.maintenance() which basically does garbage collection on the database daily.
Fantastic. I have officially dropped wiz.bin compaction off my wish list!
__________________
Server: MSI Z270 SLI Plus ATX Motherboard, Intel i3-7100T CPU, 16GB Memory, unRAID 6.3.2, SageTV & OpenDCT Dockers
Tuners: 2 x SiliconDust HDHomeRun Prime Cable TV Tuners, 2 x Hauppauge WinTV-HVR-2250 Dual TV Tuner Boards
Clients: Multiple HD300 Extenders
Miscellaneous: Multiple Sony RM-VLZ620 Universal Remote Controls
Reply With Quote
  #18  
Old 03-03-2016, 04:53 PM
jusjoken jusjoken is offline
Sage Icon
 
Join Date: Dec 2005
Location: Minneapolis MN
Posts: 2,169
I just want to say THANKS for this tool...wow. This makes chasing/debuging issues a breeze as I can clearly see (offline of course) what is going on in the database.

Great tool!

Thanks
k
Reply With Quote
  #19  
Old 03-25-2016, 07:56 AM
brandypuff brandypuff is offline
Sage Aficionado
 
Join Date: Mar 2008
Location: Berlin, MA
Posts: 339
Won't run for me

I get a unable to start correctly exception. Tried on a few different computers.
__________________
- James M -
Server: Asus P5B Deluxe, 8GB, Core2 E6600 @2.4GHz, Windows 10 X64 Pro
Storage Internal: (1) 250GB, (2) 2TB, (1) 4TB
Storage External: Sans Digital 4-Bay eSATA Port Multiplier RAID Enclosure with (4) 4TB Drives
Capture Devices: HDHomerunXTEND, HDHomerunPrime
Clients: (1) STX-HD100 HD Media Extender, (3) HD200 HD Theatre, (1) HD300
Reply With Quote
  #20  
Old 03-25-2016, 11:42 AM
wnjj wnjj is offline
Sage Icon
 
Join Date: Jan 2009
Posts: 1,052
Quote:
Originally Posted by brandypuff View Post
I get a unable to start correctly exception. Tried on a few different computers.
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.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Plugin: DVB-S Importer routerunner Customization Announcements 18 04-11-2019 12:04 PM
Plugin: DVB4Sage EPG Importer Jabroni SageTV v7 Customizations 9 09-14-2014 03:26 AM
XMLTV importer Vaughan SageTV Australia/New Zealand 2 09-22-2010 06:22 AM
er... how do i run the xmltv importer sfag SageTV United Kingdom 4 01-20-2009 07:14 AM
So what importer are most using? wilsonj SageTV Australia/New Zealand 3 02-15-2007 05:34 AM


All times are GMT -6. The time now is 03:27 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Copyright 2003-2005 SageTV, LLC. All rights reserved.