[mythtv] Renewing PostgreSQL support

[Michael T. Dean]

On 06/21/2010 08:05 PM, Tom Dexter wrote:
> On Mon, Jun 21, 2010 at 2:10 PM, Michael T. Dean wrote:
>    
>> On 06/21/2010 02:04 PM, Richard Morton wrote:
>>      
>>> I would expect that mSQL or SQLlite is sufficient for Myth; epecially
>>> if the clients could be re-engineered to proxy requests to the db via
>>> the backends.
>>>        
>> The (Big Ugly) Scheduler Query is too much for SQLite.  We've done tests
>> before and it just couldn't hack it.  It may be possible to make it work,
>> but we have enough MySQLisms in the code that it's a lot easier just to go
>> to an embedded MySQL--and doing so accomplishes some of our long-term goals.
>>   Specifically, it would prevent anyone other than the master backend from
>> accessing the database (since embedded mysql is not a client/server
>> architecture and allows only single-process access to the DB)--meaning all
>> clients would have access to all data via the protocol and the master
>> backend would be able to ensure data integrity without worrying about
>> clients breaking the data.
>>
>> I hope to start work on embedding MySQL after 0.24 is released.
>>      
> Wow...as someone who understands MySQL well and *really* likes having
> access to the database, I have to tell you I really don't like that
> one at all.  As a matter of fact, having access to that data is in
> fact one of the (many) things I prefer about MythTV to proprietary
> DVRs.
>    

Well, that's where you come in.  Any data you need made available needs 
to be made available through some non-direct-access protocol--so that 
the backend can prevent any data integrity issues.  So, just code up 
patches to make available whatever data you need.  (That said, the only 
real difference you're likely to notice is that the mysql command-line 
app will no longer be used to access the data.  You'll still have full 
access to all your data--you'll just access it in a way that doesn't 
involve SQL.)

I recognize that many MythTV users are SQL experts, and--therefore--they 
can modify data in the database directly without corrupting that data 
(regardless of the fact that the data integrity rules are not expressed 
in the database and all data checks are done in application code in 
MythTV).  That said, some users aren't "SQL experts" (and some people 
who understand databases also understand that knowing SQL doesn't mean 
you understand the data requirements and constraints).

And, even if we were to completely document all the data requirements 
and we were able to teach every user all the SQL they needed to safely 
touch the database and we were actually able to get users to read all 
this wonderful documentation, we still have a lot of external, 3rd-party 
client applications that are directly hitting the database, corrupting 
data, and making the fallout MythTV's problem.  So, we need to protect 
the most important part of MythTV--the MythTV data--not from users, but 
from clients that users choose to run that don't keep up with changes to 
the MythTV database schema and data requirements.

> What happens in that situation with stuff like optimizing, database
> backups etc if the backend process is the only thing that has access
> to the database.  I don't know if anyone actually does this, but it
> would also make having the DB on a separate server impossible, which
> I'd think is doable now.
>    

No, the DB is just wherever you run the MythTV data server program.  
Initially (at least) that program will be the master mythbackend (i.e. 
the only program/process that does the MythTV scheduling--and, 
therefore, needs to run the BUSQ).  Likely the mythbackend program will 
be a mythbackend that doesn't require capture cards (as the capture 
portion of mythbackend will likely be split out from the 
scheduler/housekeeper portion).  It's actually adding far more 
capability than we currently have in terms of what can run where.

> Seriously...I really appreciate all the work done by all the devs and
> don't want to sound negative, but between this, the removal of
> external player support and mandatory storage groups in mythvideo, I'm
> not sure I can remember the last time I heard about an upcoming MythTV
> change that I actually liked...it's really really starting to bring me
> down in a big way.
>    

As was already mentioned on the -users list, a handful of lines of 
Python code will allow you to set up your own means of streaming the 
files from the backend so they can be played on the frontend using any 
external player (and the mythfs.py FUSE file system ( 
http://www.mythtv.org/wiki/Mythfs.py ) may actually provide support for 
it, already, but I don't know the current status of it and MythVideo).

Besides, what's really been said is that MythTV is moving away from the 
requirement for having local files on all systems, which will impact 
usability of external players.  It is not yet decided that external 
player support will be removed (only that it will change because it's a 
mess now and requires significant effort to maintain--since we basically 
have to write and maintain the code for each new MythVideo feature added 
two times:  once for Internal and once for external players).

Mike