Mysql profiles and watched status

  Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
mcfang Offline
Junior Member
Posts: 34
Joined: Mar 2011
Reputation: 0
Post: #91
No problems with updates because the secondary user's database has views that directly reference the master database so that all updates are actioned on the master database anyway. The only actual data stored in secondary user's database is the bookmarks table.

When you set the advancedsettings to use MySQL then Kodi will attempt to create the database and tables for that user when you login.

I'm sure it's possible to migrate your existing database to the new MySQL database but I think its easier to export it to NFO files before switching and then re-scraping. Also there is an option in advancedsettings to include your watched status when you export/import.
find quote
gaxander Offline
Junior Member
Posts: 5
Joined: Jul 2013
Reputation: 0
Post: #92
(2015-08-24 07:34)mcfang Wrote:  I'm still using the design from timmyj9's post and it's been very easy to upgrade for each successive version of XBMC since Frodo.

This is the design updated to Kodi v15: http://pastebin.com/rtaLhFBj

Simply it adds a new playCount and lastWatched fields to the master files table for secondary user and creates a new database with views to the master database for everything except for the bookmarks table.

Thanks for the hard work!
It works for me
find quote
bmac88 Offline
Junior Member
Posts: 24
Joined: Mar 2013
Reputation: 0
Post: #93
(2015-08-24 07:34)mcfang Wrote:  I'm still using the design from timmyj9's post and it's been very easy to upgrade for each successive version of XBMC since Frodo.

This is the design updated to Kodi v15: http://pastebin.com/rtaLhFBj

Simply it adds a new playCount and lastWatched fields to the master files table for secondary user and creates a new database with views to the master database for everything except for the bookmarks table.

Before I get ahead of myself, if I wanted to create User2Videos and User3Videos databases, it seems like I would just "find and replace" User1Videos93 with User2Videos93 (and so on) and run certain parts of the script again. If I wanted to achieve, for example, 3 users User1 User2 and User3, what sections of the script would I run and in what order?

It seems certain that I would modify and run these two sections of the script for as many users as I wanted:
/* create slave database and the only slave table required */
/* from here to the end of the paste is creating views in slave database */

Would I run these two sections (modified for each username) after running the entire query all in order, or would I paste copies with User2Videos93 and User3Videos93 into the script so that it all happened at once? I am not very experienced with SQL but I am using HeidiSQL and I've played with it only to the extent of XBMC/Kodi databases for this purpose. I appreciate your patience.
(This post was last modified: 2016-01-02 07:09 by bmac88.)
find quote
wickedsun Offline
Junior Member
Posts: 14
Joined: Jul 2010
Reputation: 1
Post: #94
For those interested, I'm in the process of writing a perl script that will do this automatically and dynamically, even if the schema changes, based on timmy9's solution. This is interesting for people, like me, who recompile from the git repo. The script will also allow for creating new sets of profiles (clients).

I'll post it here once I'm done.
find quote
gaxander Offline
Junior Member
Posts: 5
Joined: Jul 2013
Reputation: 0
Post: #95
(2016-01-15 21:42)wickedsun Wrote:  For those interested, I'm in the process of writing a perl script that will do this automatically and dynamically, even if the schema changes, based on timmy9's solution. This is interesting for people, like me, who recompile from the git repo. The script will also allow for creating new sets of profiles (clients).

I'll post it here once I'm done.

it would be great
find quote
bmac88 Offline
Junior Member
Posts: 24
Joined: Mar 2013
Reputation: 0
Post: #96
(2016-01-15 21:45)gaxander Wrote:  
(2016-01-15 21:42)wickedsun Wrote:  For those interested, I'm in the process of writing a perl script that will do this automatically and dynamically, even if the schema changes, based on timmy9's solution. This is interesting for people, like me, who recompile from the git repo. The script will also allow for creating new sets of profiles (clients).

I'll post it here once I'm done.

it would be great

I second that... that would be awesome. I'm putting off the upgrade from 14.2 right now because I haven't wanted to tackle the backing up of three profiles and figuring this out.
(This post was last modified: 2016-01-16 07:56 by bmac88.)
find quote
wickedsun Offline
Junior Member
Posts: 14
Joined: Jul 2010
Reputation: 1
Post: #97
The only problem right now is recreating the views found in the databas, they are a pain in the ass, I have to figure out how to extract what I need from the 'create view. Since I'm doing this as dynamic as possible, I need to take it from the existing tables... which isn't super clean.

I'm definitely not a great mysql guy, so if anyone has a clean solution to get the views and write them dynamically, that'd be great.

The rest is all done (video only right now).
(This post was last modified: 2016-01-16 09:53 by wickedsun.)
find quote
wickedsun Offline
Junior Member
Posts: 14
Joined: Jul 2010
Reputation: 1
Post: #98
Well, good news. The script is pretty much done (videos only for now). I have to finalize, test and make it work with upgrading the DB and adding new clients (right now this is all pretty static).

So the way it works, for those interested, is that it looks at all the tables in the main database and then replicates everything with a view instead of a real table. it adds 2 columns to the main `files` table and moves it to globalfiles. It then creates a view for `files` in the main database. I wasn't sure if that was needed, but whatever, I followed the sql from timmyj9's post.

After this, it cycles through all the tables and creates the same ones on the client db. It reads all the columns from each table and links them in the client db.

The views are a pain in the ass, but I've managed to make them dynamic as well. I read the SHOW CREATE from the views and modify only what I need, pretty much like timmyj9's except if the views changes, if there are new views added in the future, this will also take care of them. I went into this with the thought that whenever the revision of the db increases its because there was a change to the schema.

So, the path to upgrade will be: compile new source, start kodi on main db, run the script.

The last step needed in the script is what happens when the db upgrades. I need to read the old column from the previous db, copy it over to the new one. This shouldn't take me that lon as it is far simpler than what I've done so far.

Here's the work so far:
http://pastebin.com/wZ7xLe9G

This should work in its current form, but if someone wants to chime in and try to make it better, please do!

I plan on adding command line to configure the top part of the script.
find quote
oxivanisher Offline
Junior Member
Posts: 1
Joined: Apr 2012
Reputation: 0
Post: #99
Any news on this front? I am also very interested in the possibility to use the same media info for two users watching different shows in parallel with mysql.
find quote
gaxander Offline
Junior Member
Posts: 5
Joined: Jul 2013
Reputation: 0
Post: #100
Update to mysql version: 5.7.11-0ubuntu5 broke tvshow_view

Code:
#1055 - Expression #26 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'path.idParentPath' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I patch the problem with

Code:
msql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

credits: https://stackoverflow.com/a/36033983/6177986
find quote
Post Reply