Move to SQL server instead of db3 file? (1 Viewer)

ScoopD

Portal Member
April 24, 2007
37
4
Melbourne
Home Country
Hey all, Just wondered if anyone had broached the subject of migrating the data to a SQL database instead of the MP standard db3 file? I searched, but haven't seen any posts talking about this.

With TVServer as a central location for TV viewing (and a SQL server install required for that) and multiple MP clients streaming from that, it seems that there would be the option to put the My-TVSeries data into the SQL server to be centrally available to all clients?

Obviously this would be heaps of rework on the plugin and would probably then require a server plug-in (for all the downloading etc) and a client plug-in (to direct to the server and handle the runtime read/write of data).

Big job, I know, but a sensible approach for the future of MP?

Just chucking it out there and seeing who bites.

P.S. if you want a laugh and you are reading this around 20th August 2008, search for "Sally McLellan" getting silver in the women's 110m hurdles - the interview is just brilliant.
 

joz

Portal Pro
March 17, 2008
1,353
306
Home Country
Netherlands Netherlands
These are my thoughts exactly.
Could be I'm missing something here but the SQL server is (almost) completely not used on a hotseat installation...
The advantages of using MySQL or MSSQL is also the great tools to manage your databases. I know you could do the same with SQLLite (db3 files) but the functionality of SQLLite is nowhere close to a fully functional db as MySQL and MSSQL
 

fforde

Community Plugin Dev
June 7, 2007
2,667
1,702
44
Texas
Home Country
United States of America United States of America
I don't know why everyone around here is so hot on SQL Server. With a MP setup, the only advantage SQL Server gives you is concurrent access from multiple clients. But that could be taken care with just about the same amount of work with a client/server setup in the program itself, with only the server accessing the (SQLite) database. It's a cleaner setup and easier to maintain the integrity of the database because you only have one application connecting. Plus it puts less of a burden on the user and the user's system. SQL Server has a lot of overhead, and I can't think of a single reason to justify it in the context of MP.
 

piranha

MP Donator
  • Premium Supporter
  • September 17, 2005
    370
    10
    It's not being hot about some specific database. It's about being independent from the backend SQL engine instead being limited to db3.
    What's the purpose of reinventing the wheel and doing client/server, if you have it available as db engine?
    And as SQL server, we are not talking only about Microsoft SQL Server, but also about mySql etc.. which has a little overhead.
    Performance of proper SQL structure against lite db3 database engine is not comparable at all.
     

    fforde

    Community Plugin Dev
    June 7, 2007
    2,667
    1,702
    44
    Texas
    Home Country
    United States of America United States of America
    Well I don't want to get into a debate about it, but I still have not heard a single argument for why moving to a more robust database server is a good idea for MP. Transactional databases like SQL Server or MySQL offer many many advantages over an embedded DB like SQLite. But the only one I can think of that applies to MP is concurrent access.

    But you are wrong, the logic for this is not already in place, and whichever way you implement a multi-seat system, you are not reinventing the wheel, regardless of whether or not you use a client/server setup. How does one box know data was changed by another box? How do you prevent multiple boxes modifying similar data at the same time? How do you manage individual settings on a box by box basis? How do you handle video or music files stored on a central server? Do all the systems need to map their network drives the exact same way? Is it ok to place this requirement on the user? Will a server stream the content? These are the hard questions that have to be answered, regardless of how you implement a multi-seat system. Having all the boxes connect directly to a central database doesn't get rid of any of these problems. I am not saying a client/server setup would immediately solve these problems either, but I am saying that it would reduce the system requirements and complexity from the users point of view.

    So what advantages does using a enterprise level database solution bring us? Concurrent access can be done other ways so that is out. Sure SQL Server or MySQL is going to outperform SQLite, but the database is not the performance bottleneck for MediaPortal...
     

    piranha

    MP Donator
  • Premium Supporter
  • September 17, 2005
    370
    10
    In all above aspects I agree with you. I think I misunderstood what you meant by "client/server" architecture.

    It is clear that now all plugins, and MP itself is limited in design to single seat and single db. Just moving to SQL of course doesn't solve the problem.
    What regular users mean by "moving to SQL" is actualy developers starting thinking about centralized storage and getting plugins ready in this direction.
    Some stuff like modifying similiar data at the same time is solved by SQL implementation itself, but definitely there is some "homework" to be done by the developer.
    By saying reinventing the wheel, I meant solution where you'd have a "master instance" of the plugin, which listens to "client plugins" and co-ordinates data storage via db3 or so. This is easily solved by transactional SQL. :)
    At my work, I do development with Oracle and M$ SQL, so I guess we speak the same language. Are you asking for proposals of actual implementation (because this will be dependent on each plugin), or you want to open discussion about "core" db handling by MP?

    The only thing that MP should provide, is common data provider which is independent from the plugin. It would/could take care of local db cache if necessary. So for example, on each MP client user needs to define which type of db to use, what's the IP of the server, username and password. Then each plugin would get it's own namespace (let's say each table for you would be prepended with mp_ (for moving pictures :) ) and the actual table use/structure would depend on the developer of the plugin). Certain core tables (shared across all plugins) would help for sure. Tables like, users, settings, settings per user etc..

    I guess the most important part of the discussion is convincing developers that thinking outside single seat/user is a must. The other choice is to give them framework of db access, which will take the db development overhead away from them.
     

    fforde

    Community Plugin Dev
    June 7, 2007
    2,667
    1,702
    44
    Texas
    Home Country
    United States of America United States of America
    I think we're on the same page. Ultimately the goal should be to move to a multi-seat setup. A centralized database should only be a means to an end, not an end in and of itself.

    So back on topic, I am curious too, Inker have you given any thoughts to multi-seat support with the TV-Series plugin? I know you have export/import functionality for watched flags but any ideas further than that?
     

    Inker

    Retired Team Member
  • Premium Supporter
  • December 6, 2004
    2,055
    318
    Well sure I've thought about it, but it requires massive amounts of work.

    I'd design it as a seperate importer, which is mostly independend from MP (at maximum use some dlls maybe), that runs as a service on the server and writes entries to a db server. Clients then only retrieve info, and maybe write some user specific things (like diff. settings, or watched flags and the like to the db server). Importer and clients never talk to each other directly.

    Question is how do you handle data like banners. Store them as a blob to the db. On the server disk....if so, how does the client retrieve them..windows shares. Do you design another service that handles sending those out, or does the importer handle it. Similarly, how do you play your episodes..... Do you cache the images locally on the clients?

    How do you send data like ratings to the onlinedb. Should the clients do it directly, for each user seperatly? Should it simply write to the local db, and then the importer monitors it and sends it online?

    What about single-seat users.....do you really require them to install a db server just to use your plugin. Do you provide a fallback to sqlite for those users.

    MP2 will hopefully abstract alot of this stuff await from plugins, thus a plugin written according to design guidelines will do all of those by default. At least that's my hope.
     

    piranha

    MP Donator
  • Premium Supporter
  • September 17, 2005
    370
    10
    I already keep my db "centralized" via mapped share. Same with graphic files.. With TV-Series, I would just be happy if it would have ability to use different databases, and allow selection of current db via interface..
    eg.. tvseries-wife.db3, tvseries-mine.db3, tvseries-kids etc..
    That way everyone would have separate db, with separate settings and also separate shows (if needed). And yes, I know redundant information in the db files, but I'd have multiuser setup ready to go :) (optional password protection and I am reaching extasy :) )
     

    joz

    Portal Pro
    March 17, 2008
    1,353
    306
    Home Country
    Netherlands Netherlands
    This is becoming an insightful discussion for me :)
    Been an MP user for like a couple of months and really started joining the community actively this month. Interesting thoughts you guys have here.

    I've only really worked with full-blown databases (primarily MySQL) and don't know to much about sqllite. Should dig in to this sometime...
     

    Users who are viewing this thread


    Write your reply...
    Top Bottom