[Tolriq]

Before thinking so big you should check a little more on how XBMC works

Swig is already used And having a class does not abstract the queries and object.
And due to some legacy part in Xbmc database schema without some major changes it would be near impossible.

You should see the GSOC proposal about database rewrites that would be the first step before layer abstraction.

[phate89]

The future upnp database changes are based on the current database layout? Because if you start with this work it's a good start to rewrite the database shema but it will impact the future pump changes..

[m.savazzi]

sounds like ODBC (windows and Linux) are the way to go?
http://dtemplatelib.sourceforge.net/

http://www.easysoft.com/developer/interf...linux.html
http://support.apple.com/kb/DL895

they are widely available for windows and support all possible DB + exist for all other platforms.

This could be very good:
http://dtemplatelib.sourceforge.net/FAQ.htm

Which compilers are supported?
The DTL has been tested on these compilers:
Microsoft Visual C++ 6.0, service pack 5, using Windows NT 2000
Microsoft Visual C++ 7, using Windows NT 5
Microsoft Visual C++ 2005 Express, under Windows NT 2000
Borland 5.5, 6.0 under Windows NT 2000
GNU C++ 3.2 using Red Hat Linux 7 with unixODBC 2.0.8
GNU C++ 3.3 using Cygwin and Microsoft ODBC DLLs
GNU C++ 2.95.4 under FreeBSD with STLPort (as reported by R. Kotha).
Sun Solaris Workshop 6, Update 1 using the Merant 3.7 ODBC drivers (we are not currently testing this compiler)
HP with aCC A.03.35 using the Merant 3.7 ODBC drivers (we are not currently testing this compiler)


Which databases are supported?
We have followed the ODBC 3.0 standard and done nothing database specific. So any database that has on ODBC 3.0, Level 1 compliant driver should work. The databases we regularly test against are:
Oracle 8, 9
SQL Server 2000
Access 2000
MySQL
We have also had reports that others have successfully used DTL against Sybase, Postgres and DB2.
(Please note, as reported by Ben Martin, that if you are using Postrgres versus unixODBC then you should use the official Postgres driver from http://gborg.postgresql.org/project/psql...isplay.php since the one that comes with unixODBC does not work with DTL).

[m.savazzi]

Before thinking so big you should check a little more on how XBMC works

I like to think big to achieve even bigger results

but you're totally right, that's why I was asking some help to understand the xbmc "tricks"

Swig is already used And having a class does not abstract the queries and object.

Correct, but some objects and queries should already be there?!? aren't they a starting point?

And due to some legacy part in Xbmc database schema without some major changes it would be near impossible.

You should see the GSOC proposal about database rewrites that would be the first step before layer abstraction.

What is the GSOC? can you help me more here?

I was thinking a first step is to change the way we interact with the DB then change/improve the DB

If you change the whole DB structure AND the libraries... that's quite a complex one

Anyway what do you think of ODBC? they are natively available on Windows, and good, stable, libraries exist for unix/mac

They will provide complete DB independecy, type independency and query independency opening up to very interesting scenarios.

M

---

The future upnp database changes are based on the current database layout? Because if you start with this work it's a good start to rewrite the database shema but it will impact the future pump changes..

which are the uPNP requirement on the DB schema?

usually to design a good db you need to know which data to put in and how to get it out

Do we have a dump/track of all the queries? per each db?

Also using ODBC we can have a single DB for all the libraries ...

---

some examples
http://www.jose.it-berater.org/smfforum/...pic=4930.0
http://www.jose.it-berater.org/smfforum/...pic=3254.0

[Montellese]

ODBC doesn't have anything to do with single or multiple databases. Trying to integrate Doctrine, a PHP library through swig into XBMC to improve database speed is insane. Swig tends to make things slower and PHP is an interpreted language. Putting that together certainly won't make things faster.

I'm sorry but it sounds a bit to me like you just through out some things you come across without really knowing what they are or mean. If you want to know how XBMC uses the database you can easily look into the code. The base stuff is in xbmc/dbwrappers and the specific databases are in xbmc/video and xbmc/music.

What most people are missing is that SQLite (or something else that can be used without additional installation) will always be the main "backend" used by XBMC simply because the bigger part of our user base doesn't even know what MySQL is let alone do they have the skills/knowledge to install it. So concentrating on MySQL, PostgreSQL or whatever will never happen because it doesn't fit the average joe user. While using ODBC or whatever RDBMS wrapper there is makes using different RDBMS' easier it also introduces new problems. Look at all the SQL queries we write for SQLite and then realize that they don't work with MySQL because it supports a different subset of SQL. Adding another RDBMS into the mix will make things even worse.

IMO the best solution for people with multiple xbmc installations is a combination of a lot of clients with local databases that can sync with a (headless) xbmc instance which is responsible for getting the files etc. The advantage of this approach it can also work if you just have two clients without a dedicated server (which you will always need with MySQL). If every client can be a media provider and a media consumer (or only one of the two) you can have a very flexible setup that fits with almost every need. I agree that UPnP isn't the ideal solution to achieve this but it is the easiest way into such a design right now. Once the basic concept for the system has been setup and tested there can be another mechanism for synchronisation.

[Tolriq]

The problem about media provider via UPnP is that it's lan only and users does ask for out of lan streaming, this is the only things that make them look at Plesk.
And bouncing from NAS to master device that then serves the media via UPNP to client Xbmc that then stream the file to end device is not efficient at all

The major advantage of Mysql is that 100% of common nas providers like synology or qnap does provides easy mysql install / administration.

If headless Xbmc is made available on such platform it can be a solution for the mass.

I do not known the kind of stats you have on Xbmc users, but Yatse have 100 000 daily users with anonymous stats activated.
70% are now on Rpi and 21% on Windows the rest is mac atv android ... (One year ago it was completely different with Windows first)

On last month on 9M media file played 4M where either addons or file mode and half of file mode on remote devices.
The rest is database media or pictures or pvr.

Users needs changes and good knowledge of their usage is important for future I suppose.

[popcornmix]

I do not known the kind of stats you have on Xbmc users, but Yatse have 100 000 daily users with anonymous stats activated.
70% are now on Rpi and 21% on Windows the rest is mac atv android ... (One year ago it was completely different with Windows first)

Wow! That's a very interesting statistic...

[phate89]

IMO the best solution for people with multiple xbmc installations is a combination of a lot of clients with local databases that can sync with a (headless) xbmc instance which is responsible for getting the files etc. The advantage of this approach it can also work if you just have two clients without a dedicated server (which you will always need with MySQL). If every client can be a media provider and a media consumer (or only one of the two) you can have a very flexible setup that fits with almost every need. I agree that UPnP isn't the ideal solution to achieve this but it is the easiest way into such a design right now. Once the basic concept for the system has been setup and tested there can be another mechanism for synchronisation.

Yes but a lot of people have files in a dedicated nas that can't execute xbmc..
Now in this case with upnp data will go from the network to the device that indexed it and from that device to the player so it will need more network bandwith.
Is there a way around this problem with upnp?
Excluding this problem your approach is probably the best...
I'm so curious to see the progress of this new feature..

[m.savazzi]

Sorry we are confusing layers:
1) ODBC abstracts ANY database including sqllite, MySQL etc... they are quick, easy to implement and a single query will run on all.
2) headless XBMC will require a spare xbmc machine, a database server is NOT a spare machine, is used for another 100000000000 things. Also MySQL is available on all NAS I know that are USUALLY the core repository for XBMC content libraries.
3) a good use of correct SQL will make the library fly having the advantage not only of better data but of using a secondary system just to manage the data and sending over the network only relevant information.
4) on very small cpu implementation like raspberry and other the extra cpu required for SQLite and bad data structure is a huge waste of time and impacts the overall user experience, the content will NOT be on those devices in any case so you have a NAS somewhere.

I think your solution is more complex and cumbersome than making a good DB library able to separate the presentation layer (XBMC) from the data layer (DB) also note that if the db is bad/cumbersome your solution of a headless XBMC will not generate a good result.
If is bad on a single pc just for itself how can you think that it will be efficient to work with multiple PC? In the end you are proposing to do a "XBMC
RDBMS" an XBMC database server with SQLite files on the backend and custom protocols between client and server...

In other words you are trying to build in XBMC what is already available, super efficient and field proven with millions of installations on the standard RDBMS.

You'll have to face thread concurrency, protocol concurrency, statefull transaction, concurrent updates, etc... and it will be an awful mess...
while we can get all of that for free from any RDBMS, including free ones.

Then once you have a RDBMS in place you can do a headless XBMC just for the scraping, scripts, etc... that will work very nicely!

Note that configuring MySQL on nas is as easy as a single click!
ODBC sources can be easily included in Setup scripts on every platform you use... they are "legacy" in the sense that has been around for at leasy 15 years... there should be a reason for that

[phate89]

Sorry we are confusing layers:
1) ODBC abstracts ANY database including sqllite, MySQL etc... they are quick, easy to implement and a single query will run on all.
2) headless XBMC will require a spare xbmc machine, a database server is NOT a spare machine, is used for another 100000000000 things. Also MySQL is available on all NAS I know that are USUALLY the core repository for XBMC content libraries.
3) a good use of correct SQL will make the library fly having the advantage not only of better data but of using a secondary system just to manage the data and sending over the network only relevant information.
4) on very small cpu implementation like raspberry and other the extra cpu required for SQLite and bad data structure is a huge waste of time and impacts the overall user experience, the content will NOT be on those devices in any case so you have a NAS somewhere.

I think your solution is more complex and cumbersome than making a good DB library able to separate the presentation layer (XBMC) from the data layer (DB) also note that if the db is bad/cumbersome your solution of a headless XBMC will not generate a good result.
If is bad on a single pc just for itself how can you think that it will be efficient to work with multiple PC? In the end you are proposing to do a "XBMC
RDBMS" an XBMC database server with SQLite files on the backend and custom protocols between client and server...

In other words you are trying to build in XBMC what is already available, super efficient and field proven with millions of installations on the standard RDBMS.

You'll have to face thread concurrency, protocol concurrency, statefull transaction, concurrent updates, etc... and it will be an awful mess...
while we can get all of that for free from any RDBMS, including free ones.

Then once you have a RDBMS in place you can do a headless XBMC just for the scraping, scripts, etc... that will work very nicely!

Note that configuring MySQL on nas is as easy as a single click!
ODBC sources can be easily included in Setup scripts on every platform you use... they are "legacy" in the sense that has been around for at leasy 15 years... there should be a reason for that

The need to rewrite the database schema is not primarily to make it faster but to make it more flexible. Right now there are strict categories (movies,tv shows, seasons, episodes, songs, albums, artists etc etc) and have various limits.
Many times in the past years someone proposed new ideas:
http://forum.xbmc.org/showthread.php?tid=55794
http://wiki.xbmc.org/index.php?title=Dat...Schema_4.0
http://forum.xbmc.org/showthread.php?tid=73831
(I think the databsse schema 4.0 explain well what they're after)

The latest attempt was made with 2013 gsoc (google summer of code):
http://forum.xbmc.org/showthread.php?tid=165929
But (i think) none of the past attempts succeeded.
It's not an easy task and there are tons of things to consider.

Btw talking about performance with low power device like raspberry pi odbc makes everything a lot slower..

[da-anda]

to all those MySQL lovers: one reason why we also think it's not THE solution for us is because it's no solution everybody could deal with. While entering the MySQL credentials is the least complex part, for a MySQL setup you also have to configure path substitution correctly etc - and this is nothing we want people have to do. It has to be as easy as install -> enable library sharing -> connect -> be done.

@m.savazzi keep in mind that even if you abstract the executing DB layer, you have to deal with differences among the SQL implementations (different ways to e.g. define data types of DB columns, certain SQL functions won't work accross all systems, JOINs, ...) So you'll have to parse and rewrite hand written queries for certain implementations, which will again end up in being a hackish solution.
Going the whole 9 yards and implementing a QOM would sort of work around it in a way that you create the DB requests with an abstract object oriented language and the DB connectors have to translate these to native SQL. It also makes DB requests way more readable + we could safeguard python addons by only allowing DB access via this QOM.

Pseudo code example based QOM I'm working with day by day:

Code:

class VideoRepository {
  // find by director object
  public function findByDirector($director) {
    // this will build a basic QOM query object, preconfigured for the video database
    $query = $this->createBaseQuery();
    return $query->matching(
       $query->equals('director', $director)
    )->execute();
  }

  public function findByActor($actor) {
    $query = $this->createBaseQuery();
   return $query->matching(
      $query->contains('actors', $actor)
   )->execute();
  }

  public function findByActorName($someString) {
    $query = $this->createBaseQuery();
    return $query->matching(
      // the dot syntax is used to access the properties of nested objects
      $query->like('actors.name', $someString)
    )->execute();
  }
}

These are ofc only basic examples and ofc not every single query used needs it's own wrapping function - one could use "demand" objects holding object related search criteria (or a smartplaylist parser).
More complex queries could look like:

Code:

$query = $this->createBaseQuery();
$query->matching(
  $query->logicalAnd(
    $query->contains('tags', $someTagObject),
    $query->logicalNot(
      $query->contains('tags', $someExcludeTag)
    ),
    $query->in('genre', $collectionOfGenres),
    $query->greaterThan('actors.income', $anAwefullLot)
  )
);
$query->setOrderings(array('rating' => 'ASC'));
return $query->execute();

In the background, the parser would automatically create joins to the tags, genre and actors tables. This ofc requres the parser to know which object property belongs to which database table + the type of relation (1:1, n:m, ...)

[Milhouse]

While entering the MySQL credentials is the least complex part, for a MySQL setup you also have to configure path substitution correctly etc - and this is nothing we want people have to do.

Path substitution is not required for MySQL. At most the user only has to configure the MySQL server ip address, port, username and password.

It has to be as easy as install -> enable library sharing -> connect -> be done.

I'd have thought it should be possible to enable MySQL in the same way the webserver is enabled in Settings: ip address, port, username, password. The last three settings can all have sensible defaults.

[Tolriq]

For nas usually no need for path substitution

But as said by most, the advantage of mysql is that it can run directly on nas, eliminating the need for a main host. Since an rpi as a main host will never have the necessary power.
If headless Xbmc have enough dependencies removed to be able to run on nas like syno qnap and such this is a viable solution as long.

The only drawback right now is that UPnP was chosen as the sync mechanism and it has major flaws and won't be network friendly.

Having a movie on a nas to go from NAS to host A then to host B on slow network, or wifi is not good.
Then about transcoding, who will do it A or B ? If host A is down or crashed B cannot access media on nas that is up, meaning users haves to worry about 2 devices being up and running.
Explain to my wife that if you can't access movie you need to check if nas have the light on, then if b works, but for B you also need to have a screen or something to see the status of Xbmc.

Encrypted source sharing, and http serving of local media, with syncing protocol cannot be done with UPnP, and if going on the full UPnP road it will be a headache to get the correct road latter.

[phate89]

to all those MySQL lovers: one reason why we also think it's not THE solution for us is because it's no solution everybody could deal with. While entering the MySQL credentials is the least complex part, for a MySQL setup you also have to configure path substitution correctly etc - and this is nothing we want people have to do. It has to be as easy as install -> enable library sharing -> connect -> be done.

I agree with you. it's a lot simpler the solution the xbmc have in mind (when we will see more of it? i only saw a video of it).
But that solution creates a problem with some of the solution current user has.Some people have their files in a nas with mysql support. In this case you will need twice the network bandwidth to make this work and with wifi it's a problem...

While entering the MySQL credentials is the least complex part, for a MySQL setup you also have to configure path substitution correctly etc - and this is nothing we want people have to do.

Path substitution is not required for MySQL. At most the user only has to configure the MySQL server ip address, port, username and password.

It has to be as easy as install -> enable library sharing -> connect -> be done.

I'd have thought it should be possible to enable MySQL in the same way the webserver is enabled in Settings: ip address, port, username, password. The last three settings can all have sensible defaults.

I use mysql and i think it's great but the basic user doesn't know anything about network sharing and stuff like that. You don't need path substitution usually but most people adds to the library the local path not the network path and have their contents in the local hdd not in the nas..
It's not as easy as insert ip address-port-user-pass. People needs to know something they don't

[da-anda]

wasn't path substitution required to also share the thumbnails/artwork when using MySQL?

@Tolriq
Thanks for your input and concerns. We for now use UPNP, but we also have JsonRPC at hand which we could use to sync stuff. Also we're not bound to the UPNP standard, so we could send extra information via UPNP for direct access to certain media if required. So it's not necessarily network overhead. A benefit of using UPNP might be that you could easily integrate any UPNP source into your XBMC library. And be asured that we aim for a performant solution.