[xxxnelly]

Hi All,

I have decided to move my music and video library to be store away from the individual clients.

I am using the advance settings to do this.

Has anyone doe this for the EPG or other pvr databases?

Cheers,
Neil

[Johnnygo]

I'm using this:

Code:

<tvdatabase>
<type>mysql</type>
<host>192.168.0.10</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>01tv</name>
</tvdatabase>

[Jimmer]

Hi I just wanted to chip in here in case someone had my problem:

even though xbmc has the correct privileges to create databases and tables on my qnap nas - it won't get past creating the version table and then gives up. So I found a python script online that converts a sqlite3 db to a mysql one. I guess this will work for any mysql installation, not just one on a nas, so I thought I'd share:

1) save this script as sqlite2mysql.py:

Code:

import re, fileinput

def main():
  for line in fileinput.input():
    process = False
    for nope in ('BEGIN TRANSACTION','COMMIT',
                 'sqlite_sequence','CREATE UNIQUE INDEX'):
      if nope in line: break
    else:
      process = True
    if not process: continue
    m = re.search('CREATE TABLE "([a-z_]*)"(.*)', line)
    if m:
      name, sub = m.groups()
      sub = sub.replace('"','`')
      line = '''DROP TABLE IF EXISTS %(name)s;
CREATE TABLE IF NOT EXISTS %(name)s%(sub)s
'''
      line = line % dict(name=name, sub=sub)
    else:
      m = re.search('INSERT INTO "([a-z_]*)"(.*)', line)
      if m:
        line = 'INSERT INTO %s%s\n' % m.groups()
        line = line.replace('"', r'\"')
        line = line.replace('"', "'")
    line = re.sub(r"([^'])'t'(.)", "\\1THIS_IS_TRUE\\2", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "\\1THIS_IS_FALSE\\2", line)
    line = line.replace('THIS_IS_FALSE', '0')
    line = line.replace('AUTOINCREMENT', 'AUTO_INCREMENT')
    if re.search('^CREATE INDEX', line):
        line = line.replace('"','`')
    print line,

main()

Thanks to bb and Alex Martelli on Stackoverflow for this one!

2) On your xbmc box

Code:

sqlite3 .xbmc/userdata/Database/TV16.db .dump | python sqlitetomysql.py > TV16.sql

3) On your xbmc box

Code:

nano TV16.sql

and put a # infront of the

Code:

PRAGMA foreign_keys = off;

line.

4) Copy the resulting file to whatever system you use to connect to phpMyAdminOn on your NAS. Connect to phpMyAdmin, select the TV16 database that xbmc partially failed to create. Select the Version table (it will be the only one) and drop this table only (not the entire db!). You will now have a TV16 db with no tables.

5) Select the Import tab and select the TV16.sql file you produced above. Job done!

6) edit your advanced settings with the following section:

Code:

<tvdatabase>          
                <type>mysql</type>
                <host>192.168.1.7</host>
                <port>3306</port>
                <user>xbmc</user>
                <pass>xbmc</pass>
        </tvdatabase>

obviously adjusting the <host> and <pass> parameters to what you use!

7) restart xbmc so it picks up the new tvdatabase...

This is a bit of a hack. You need to be on linux (although it probably can be adapted for other platforms) and you need phpMyAdmin (although you can drop the version table and import the TV16.sql file via the commandline if you want)

[Jimmer]

Just a follow-up, on your local xbmc machine, you can do the following instead of using phpMyAdmin:

1), 2) & 3) As above

4)

Code:

mysql -u xbmc -h 192.168.1.7 -p TV16

enter your password to connect (edit the ip address to your server above)

then:

Code:

show tables;

should list something like:

Code:

mysql> show tables;
+----------------+
| Tables_in_TV16 |
+----------------+
| version        |
+----------------+
1 row in set (0.00 sec)

then type:

Code:

drop table version;

then

Code:

exit

5)

Code:

mysql -u xbmc -h 192.168.1.7 -p TV16 < TV16.sql

again put in you password, edit the above with the correct hostname/ip

6) & 7) just continue as previously stated above......

Of course the advantage of this method is that in one terminal window on the slave, you run the script to convert your existing db to mysql, edit out what you don't want from the result, drop the partially created db on the server and import the new db to the server. Should take like 2 minutes (ie quicker than firing up phpMyAdmin on a laptop and copying files, etc)

Jim

ps sorry don't know about the epg - not sure if it's been centralised yet? It's not really working properly yet anyway on the branch I'm using, so I'll wait until it's fixed up before dabbling with centralisation...

[xxxnelly]

I am getting this error after following that guide Jim

ERROR: SQL: Undefined MySQL error: Code (1093)
Query: DELETE FROM map_channelgroups_channels WHERE idChannel IN (SELECT map_channelgroups_channels.idChannel FROM map_channelgroups_channels LEFT JOIN channels on map_channelgroups_channels.idChannel = channels.idChannel WHERE channels.idChannel IS NULL)