[da-anda]

I wonder if the "server" will only require a MySQL database up and running (of which many NAS are capable of), or if it'll also need some other XBMC server software for real client/server communication etc. and a dedicated API.
I'm not sure if there could raise some issues if every XBMC client tries to play server and updates the db at the same time (maybe through automated folder rescans on startup etc.). So I think on the long run a "server version" of XBMC might be the best solution (imagine centralized skin and gui configuration for clients/accounts/usergroups etc.). Even client dependent local media shouldn't be a problem to be indexed server side, as you could use the UNC paths (\\my-client-pc\d$\local\folder\structure.mpg) and by this would even be possible to share the local media accross the other clients (assuming clients have access to that computer/share).
Once the unifiedPVR is ready, many of the users will have a dedicated TVserver with WOL anyway and some of us do already - so a XBMC server would be a great improvement I think and is the way to go in future. So maybe keep that in mind when restructuring the DB. What do you think?

[megacrypto]

The improved abstraction was pushed into mainline around r25622 and ticket #8169 has the initial patches to integrate mysql support. It is pending review for inclusion, and given the holiday season it may take a few more weeks before it gets a decent look.

All necessary information should be available in the ticket. If you're regularly compiling from the source then I would encourage you to apply the patches, test and provide feedback of your efforts.

I got the 2 patches from the ticket, but was wondering if you could provide me with how to apply them... not very experienced in patching apps

[firnsy]

I got the 2 patches from the ticket, but was wondering if you could provide me with how to apply them... not very experienced in patching apps

Whilst this is not the forum to discuss how to apply patches, you should execute the following commands in the parent directory of the xbmc source tree.

Code:

patch -p1 < /path/to/database_mysql_support_with_safe_fallback.patch

Code:

patch -p1 < /path/to/database_indexed_column_changes.patch

[oliver]

Maybe a stupid question, but what is the current status of database abstraction/support in xbmc.

I tried to install the gentoo ebuild and it wanted to get mysql in badly. I removed the mysql reference in the external libraries section of configure.in and boostraped it. it configured and is currently compiling fine.

I thought the original xbmc used sqlite only and that this whole post was about getting database abstraction. If this is the case, why does the build still force mysql support? Will xbmc even work without mysql?

[spiff]

it currently pulls mysqlclient and it's used for the mythtv support.

[oliver]

I have noticed that, but is the myth bit optional? I mean, i don run myth backends meaning that I could leave out myth support and thus mysql support. But i don't think it's optional (yet) is it?

[firnsy]

I have noticed that, but is the myth bit optional? I mean, i don run myth backends meaning that I could leave out myth support and thus mysql support. But i don't think it's optional (yet) is it?

There is no "--disable-myth" support during configuration as far as I can tell, so in short I would think no.

[megacrypto]

followed the instructions and made the following advancedsettings.xml

Code:

<advancedsettings>
<videodatabase>
  <type>mysql</type>
  <host>localhost</host>
  <port>3306</port>
  <user>root</user>
  <pass>xxxxx</pass>
  <name>mylindb</name>
</videodatabase>
</advancedsettings>

what i got that it created a new database (sqlite) in my userdata/database with the name of mylindb instead of connecting to mysql instance ?!

[firnsy]

followed the instructions and made the following advancedsettings.xml

Code:

<advancedsettings>
<videodatabase>
  <type>mysql</type>
  <host>localhost</host>
  <port>3306</port>
  <user>root</user>
  <pass>xxxxx</pass>
  <name>mylindb</name>
</videodatabase>
</advancedsettings>

what i got that it created a new database (sqlite) in my userdata/database with the name of mylindb instead of connecting to mysql instance ?!

It will currently fall back to sqlite3 if there are any issues connecting to the mysql instance identified. Can you provide the log file which should catch any such errors??

[megacrypto]

i changed the db name to another name (mylindb was actually an existing db that i had) and it worked with the new db name (myxbmc) with all the other settings in the xml file as they were.

I will see how it works and post back, but why wouldn't it use an existing db to create the tables in it (given that the db does not contain any conflicting table names)?

One quick note: Im rescanning my library right now, but so far the movies that are scanned, their info show in the main movies view, but when i hit the info button, the info screen comes out blank, with only the path showing and the poster, other than that there is nothing showing up. The data is there, since i can see it in the db tables, and also in the fanart view (such as rating, director, wirter, genre, year, etc.. )

[megacrypto]

i think this is a bug:

Code:

00:52:05 T:140411203914064 M:686641152   ERROR: SQLite: The table does not exist
                                            Query: select idActor from Actors where strActor like 'Robert Berlinger'
00:52:05 T:140411203914064 M:686641152   ERROR: AddActor (Robert Berlinger) failed

the problem here is that the select looks for Actors, whereas the table name is actors (case sensitive i guess), so this is one problem im getting.

still testing, up until now, i can not go into my TV Series (the episodes) from the library.. can not catch the error, since the log is being filled with million other things from scanning my library.

will keep posting as they come up.

[outleradam]

I just wanted to make sure that while we are creating this new database that we allow room for expansion of file types. Shows without season and episode numbers still have PLENTY of categorical data which can be used to add them to the library

Code:

mysql> use mythconverg
Database changed
mysql> select * from recorded where basename like "1012_20100102093000.mpg";
+--------+---------------------+---------------------+------------------------------+----------+-----------------------------------------------------------------+----------+--------------+----------+---------+---------+------------+-------------+----------+----------+------------+----------------+---------------------+-----------+-------+-----------------+-----------------+----------+--------+---------------+------------+-------------+-------------+-------------------------+---------------------+---------------------+-----------+---------+-----------+------------+---------+--------------+
| chanid | starttime           | endtime             | title                        | subtitle | description                                                     | category | hostname     | bookmark | editing | cutlist | autoexpire | commflagged | recgroup | recordid | seriesid   | programid      | lastmodified        | filesize  | stars | previouslyshown | originalairdate | preserve | findid | deletepending | transcoder | timestretch | recpriority | basename                | progstart           | progend             | playgroup | profile | duplicate | transcoded | watched | storagegroup |
+--------+---------------------+---------------------+------------------------------+----------+-----------------------------------------------------------------+----------+--------------+----------+---------+---------+------------+-------------+----------+----------+------------+----------------+---------------------+-----------+-------+-----------------+-----------------+----------+--------+---------------+------------+-------------+-------------+-------------------------+---------------------+---------------------+-----------+---------+-----------+------------+---------+--------------+
|   1012 | 2010-01-02 09:30:00 | 2010-01-02 10:00:00 | Teenage Mutant Ninja Turtles |          | Mutant heroes live in the sewers and train in the martial arts. | Children | adam-desktop |        0 |       0 |       0 |          1 |           1 | Default  |       64 | EP00527984 | SH005279840000 | 2010-01-02 10:15:16 | 659056340 |     0 |               1 | 2002-09-14      |        0 |      0 |             0 |          0 |           1 |          23 | 1012_20100102093000.mpg | 2010-01-02 09:30:00 | 2010-01-02 10:00:00 | Default   | Default |         1 |          0 |       0 | Default      |
+--------+---------------------+---------------------+------------------------------+----------+-----------------------------------------------------------------+----------+--------------+----------+---------+---------+------------+-------------+----------+----------+------------+----------------+---------------------+-----------+-------+-----------------+-----------------+----------+--------+---------------+------------+-------------+-------------+-------------------------+---------------------+---------------------+-----------+---------+-----------+------------+---------+--------------+
1 row in set (0.00 sec)

[firnsy]

i think this is a bug:

Code:

00:52:05 T:140411203914064 M:686641152   ERROR: SQLite: The table does not exist
                                            Query: select idActor from Actors where strActor like 'Robert Berlinger'
00:52:05 T:140411203914064 M:686641152   ERROR: AddActor (Robert Berlinger) failed

the problem here is that the select looks for Actors, whereas the table name is actors (case sensitive i guess), so this is one problem im getting.

still testing, up until now, i can not go into my TV Series (the episodes) from the library.. can not catch the error, since the log is being filled with million other things from scanning my library.

will keep posting as they come up.

I have a working patch for this and a few other issues that I will add to the ticket shortly. Apparently SQlite3 is a lot more relaxed with case sensitivities than one would have thought. Stay tuned...

[megacrypto]

this is the error from the log when i try to go into a tv series episodes:

Code:

15:17:05 T:139991329155136 M:569094144   DEBUG: GetStackedTvShowList query: select idShow from tvshow where c00 like (select c00 from tvshow where idShow=16) order by idShow
15:17:05 T:139991329155136 M:569094144   DEBUG: GetSeasonsNav query: select episode.c12,path.strPath,tvshow.c00,tvshow.c08,count(1),count(files.playCount) from episode join tvshow on tvshow.idshow=tvshowlinkepisode.idshow join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode join files on files.idFile=episode.idFile  join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow join path on path.idPath = tvshowlinkpath.idPath where tvshow.idShow = 16  group by episode.c12
15:17:05 T:139991329155136 M:569094144   ERROR: SQLite: Undefined MySQL error: Code (1054)
                                            Query: select episode.c12,path.strPath,tvshow.c00,tvshow.c08,count(1),count(files.playCount) from episode join tvshow on tvshow.idshow=tvshowlinkepisode.idshow join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode join files on files.idFile=episode.idFile  join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow join path on path.idPath = tvshowlinkpath.idPath where tvshow.idShow = 16  group by episode.c12
15:17:05 T:139991329155136 M:569094144   ERROR: GetSeasonsNav failed
15:17:05 T:139991329155136 M:569094144   ERROR: GetDirectory - Error getting videodb://2/2/16/
15:17:05 T:139991329155136 M:569094144   ERROR: CGUIMediaWindow::GetDirectory(videodb://2/2/16/) failed

i check the last query in the MySQL browser:

Code:

select episode.c12,path.strPath,tvshow.c00,tvshow.c08,count(1),count(files.playCount)
from episode join tvshow on tvshow.idshow=tvshowlinkepisode.idshow
join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode
join files on files.idFile=episode.idFile
join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow
join path on path.idPath = tvshowlinkpath.idPath
where tvshow.idShow = 16  group by episode.c12

and that's the error i got:

Code:

Unknown column 'tvshowlinkepisode.idshow' in 'on clause'

again case sensitivity issue "idshow" in the query vs. "idShow" in the table tvshowlinkepisode

[spiff]

this is precisely what's adressed in the diff firnsy posted in the very post above yours.