[mackemchris]

My head hurts after reading all that

[Ballistic]

http://pastebin.com/WEU1y30x = SERVER SETUP batch 5 user kodi script
http://pastebin.com/guTkTsQt = CLIENT SETUP batch 5 user kodi script
http://pastebin.com/sqxr4QVz = User KODI - SQL Setup txt

Hello,
I uploaded three files. these are for kodi 14.0 with the database 90 for viedeos and 48 for music
I wish you much fun with it and hope that it runs without problems

in Deutsch:

Hallo,
habe die drei Dateien hochgeladen. diese sind für kodi 14.0 mit der Datenbank 90 für Videos und 48 für Musik
Ich wünsche euch viel Spaß damit und hoffe dass es ohne Probleme läuft

For the User KODI - SQL Setup;
I needed this file but i ran into issues in Kodi14 like TVShows not loading even after the fix that was posted later.
I then found out that a line is missing.

Code:

`path`.`idParentPath` AS `idParentPath`,

This needs to be added between line 233 and 234.

[Concestly]

ok, so I read through, very easy to understand, thank you. but what I really want to know is, how do I change the settings on the XBMC (kodi) so that when I find the show I want, and it opens the next window with ALL THOSE LINKS to click, how do I change it, so it doesn’t do that? I just want to click on the show, the season, episode and go, I don’t want to sit and click each link thing until one works

[wickedsun]

For all those interested, I've made this into a fully (almost) dynamic script which will (most likely) work on schema changes. Right now it is very much beta, but I'd love to get feedback on it!

http://pastebin.com/wZ7xLe9G

[isamu.dragon]

@wickedsun, how might I implement your script?

[BigMong]

Over the last month or so I have a working setup.
I haven't had any problems with this, so I guess I will share me SQL.

Add to each "advancedsettings.xml" change the information below to your setup tho (host, port, user, pass, name)

PHP Code:

<videodatabase>
    <type>mysql</type>
    <host>192.168.0.110</host>
    <port>3306</port>
    <user>KODI</user>
    <pass>****</pass>
    <name>Kodi_Matt_Video_</name>
  </videodatabase> 


Also I have the following Databases
Kodi_Matt_Video_93
Kodi_Mace_Video_93
Kodi_Kiyana_Video_93
Kodi_Jaide_Video_93

some infomation on Database Versions
Database_versions (wiki)

Yes I have change my formate a little, its still easy to find and replace to change things.

All SQL should work from the root level of the Database

First task is to get he database ready for users
Please Note: If your Database login is different to KODI you must change it below

PHP Code:

/*----------
--- PREP ---
----------*/
  RENAME TABLE `Kodi_Matt_Video_93`.`files` to `Kodi_Matt_Video_93`.`globalfiles`;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` CHANGE playCount playCountMatt INT(11);
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` CHANGE lastPlayed lastPlayedMatt TEXT;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` ADD playCountMace INT(11) AFTER lastPlayedMatt;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` ADD lastPlayedMace TEXT AFTER playCountMace;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` ADD playCountKiyana INT(11) AFTER lastPlayedMace;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` ADD lastPlayedKiyana TEXT AFTER playCountKiyana;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` ADD playCountJaide INT(11) AFTER lastPlayedKiyana;
  ALTER TABLE `Kodi_Matt_Video_93`.`globalfiles` ADD lastPlayedJaide TEXT AFTER playCountJaide;

  CREATE VIEW `Kodi_Matt_Video_93`.`files` AS SELECT
   `Kodi_Matt_Video_93`.`globalfiles`.`idFile` AS `idFile`,
   `Kodi_Matt_Video_93`.`globalfiles`.`idPath` AS `idPath`,
   `Kodi_Matt_Video_93`.`globalfiles`.`strFilename` AS `strFilename`,
   `Kodi_Matt_Video_93`.`globalfiles`.`playCountMatt` AS `playCount`,
   `Kodi_Matt_Video_93`.`globalfiles`.`lastPlayedMatt` AS `lastPlayed`,
   `Kodi_Matt_Video_93`.`globalfiles`.`dateAdded` AS `dateAdded`
  FROM `Kodi_Matt_Video_93`.`globalfiles`;

  /* TRIGGER FIX FOR MULT USERS */
  DROP TRIGGER IF EXISTS `Kodi_Matt_Video_93`.`delete_file`;
  DELIMITER $$
  /* REPLACE ---> KODI <--- WITH YOUR MYSQL LOGIN UID FROM THE ADVANCEDSETTINGS.XML */
  CREATE DEFINER=`KODI`@`%` TRIGGER `Kodi_Matt_Video_93`.`delete_file` AFTER DELETE
  ON `Kodi_Matt_Video_93`.`globalfiles`
  FOR EACH ROW
  BEGIN
    DELETE FROM `Kodi_Matt_Video_93`.bookmark WHERE idfile = old.idfile;
    DELETE FROM `Kodi_Mace_Video_93`.bookmark WHERE idfile = old.idfile;
    DELETE FROM `Kodi_Kiyana_Video_93`.bookmark WHERE idfile = old.idfile;
    DELETE FROM `Kodi_Jaide_Video_93`.bookmark WHERE idfile = old.idfile;
    DELETE FROM settings WHERE idfile = old.idfile;
    DELETE FROM stacktimes WHERE idfile = old.idfile;
    DELETE FROM streamdetails WHERE idfile = old.idfile;
  END$$
  DELIMITER ; 


Here is the the user setup, I have only included one user, to give you the idea

PHP Code:

/*-------------
--- USER 02 ---
-------------*/
  CREATE DATABASE Kodi_Mace_Video_93 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

  CREATE VIEW `Kodi_Mace_Video_93`.`files` AS SELECT
   `Kodi_Matt_Video_93`.`globalfiles`.`idFile` AS `idFile`,
   `Kodi_Matt_Video_93`.`globalfiles`.`idPath` AS `idPath`,
   `Kodi_Matt_Video_93`.`globalfiles`.`strFilename` AS `strFilename`,
   `Kodi_Matt_Video_93`.`globalfiles`.`playCountMace` AS `playCount`,
   `Kodi_Matt_Video_93`.`globalfiles`.`lastPlayedMace` AS `lastPlayed`,
   `Kodi_Matt_Video_93`.`globalfiles`.`dateAdded` AS `dateAdded`
  FROM `Kodi_Matt_Video_93`.`globalfiles`;

  CREATE TABLE `Kodi_Mace_Video_93`.`bookmark` (
  `idBookmark` int(11) NOT NULL,
    `idFile` int(11) DEFAULT NULL,
    `timeInSeconds` double DEFAULT NULL,
    `totalTimeInSeconds` double DEFAULT NULL,
    `thumbNailImage` text,
    `player` text,
    `playerState` text,
    `type` int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
  ALTER TABLE `Kodi_Mace_Video_93`.`bookmark` ADD PRIMARY KEY (`idBookmark`), ADD KEY `ix_bookmark` (`idFile`,`type`);
  ALTER TABLE `Kodi_Mace_Video_93`.`bookmark` MODIFY `idBookmark` int(11) NOT NULL AUTO_INCREMENT;

  CREATE VIEW `Kodi_Mace_Video_93`.`actor` AS SELECT * FROM `Kodi_Matt_Video_93`.`actor`;
  CREATE VIEW `Kodi_Mace_Video_93`.`actor_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`actor_link`;
  CREATE VIEW `Kodi_Mace_Video_93`.`art` AS SELECT * FROM `Kodi_Matt_Video_93`.`art`;
  CREATE VIEW `Kodi_Mace_Video_93`.`country` AS SELECT * FROM `Kodi_Matt_Video_93`.`country`;
  CREATE VIEW `Kodi_Mace_Video_93`.`country_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`country_link`;
  CREATE VIEW `Kodi_Mace_Video_93`.`director_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`director_link`;
  CREATE VIEW `Kodi_Mace_Video_93`.`episode` AS SELECT * FROM `Kodi_Matt_Video_93`.`episode`;
  CREATE VIEW `Kodi_Mace_Video_93`.`genre` AS SELECT * FROM `Kodi_Matt_Video_93`.`genre`;
  CREATE VIEW `Kodi_Mace_Video_93`.`genre_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`genre_link`;
  CREATE VIEW `Kodi_Mace_Video_93`.`movie` AS SELECT * FROM `Kodi_Matt_Video_93`.`movie`;
  CREATE VIEW `Kodi_Mace_Video_93`.`movielinktvshow` AS SELECT * FROM `Kodi_Matt_Video_93`.`movielinktvshow`;
  CREATE VIEW `Kodi_Mace_Video_93`.`musicvideo` AS SELECT * FROM `Kodi_Matt_Video_93`.`musicvideo`;
  CREATE VIEW `Kodi_Mace_Video_93`.`path` AS SELECT * FROM `Kodi_Matt_Video_93`.`path`;
  CREATE VIEW `Kodi_Mace_Video_93`.`seasons` AS SELECT * FROM `Kodi_Matt_Video_93`.`seasons`;
  CREATE VIEW `Kodi_Mace_Video_93`.`sets` AS SELECT * FROM `Kodi_Matt_Video_93`.`sets`;
  CREATE VIEW `Kodi_Mace_Video_93`.`settings` AS SELECT * FROM `Kodi_Matt_Video_93`.`settings`;
  CREATE VIEW `Kodi_Mace_Video_93`.`stacktimes` AS SELECT * FROM `Kodi_Matt_Video_93`.`stacktimes`;
  CREATE VIEW `Kodi_Mace_Video_93`.`streamdetails` AS SELECT * FROM `Kodi_Matt_Video_93`.`streamdetails`;
  CREATE VIEW `Kodi_Mace_Video_93`.`studio` AS SELECT * FROM `Kodi_Matt_Video_93`.`studio`;
  CREATE VIEW `Kodi_Mace_Video_93`.`studio_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`studio_link`;
  CREATE VIEW `Kodi_Mace_Video_93`.`tag` AS SELECT * FROM `Kodi_Matt_Video_93`.`tag`;
  CREATE VIEW `Kodi_Mace_Video_93`.`tag_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`tag_link`;
  CREATE VIEW `Kodi_Mace_Video_93`.`tvshow` AS SELECT * FROM `Kodi_Matt_Video_93`.`tvshow`;
  CREATE VIEW `Kodi_Mace_Video_93`.`tvshowlinkpath` AS SELECT * FROM `Kodi_Matt_Video_93`.`tvshowlinkpath`;
  CREATE VIEW `Kodi_Mace_Video_93`.`version` AS SELECT * FROM `Kodi_Matt_Video_93`.`version`;
  CREATE VIEW `Kodi_Mace_Video_93`.`writer_link` AS SELECT * FROM `Kodi_Matt_Video_93`.`writer_link`;

  CREATE VIEW `Kodi_Mace_Video_93`.`episode_view` AS SELECT
    `episode`.`idEpisode` AS `idEpisode`,
    `episode`.`idFile` AS `idFile`,
    `episode`.`c00` AS `c00`,
    `episode`.`c01` AS `c01`,
    `episode`.`c02` AS `c02`,
    `episode`.`c03` AS `c03`,
    `episode`.`c04` AS `c04`,
    `episode`.`c05` AS `c05`,
    `episode`.`c06` AS `c06`,
    `episode`.`c07` AS `c07`,
    `episode`.`c08` AS `c08`,
    `episode`.`c09` AS `c09`,
    `episode`.`c10` AS `c10`,
    `episode`.`c11` AS `c11`,
    `episode`.`c12` AS `c12`,
    `episode`.`c13` AS `c13`,
    `episode`.`c14` AS `c14`,
    `episode`.`c15` AS `c15`,
    `episode`.`c16` AS `c16`,
    `episode`.`c17` AS `c17`,
    `episode`.`c18` AS `c18`,
    `episode`.`c19` AS `c19`,
    `episode`.`c20` AS `c20`,
    `episode`.`c21` AS `c21`,
    `episode`.`c22` AS `c22`,
    `episode`.`c23` AS `c23`,
    `episode`.`idShow` AS `idShow`,
    `files`.`strFilename` AS `strFileName`,
    `path`.`strPath` AS `strPath`,
    `files`.`playCount` AS `playCount`,
    `files`.`lastPlayed` AS `lastPlayed`,
    `files`.`dateAdded` AS `dateAdded`,
    `tvshow`.`c00` AS `strTitle`,
    `tvshow`.`c14` AS `studio`,
    `tvshow`.`c05` AS `premiered`,
    `tvshow`.`c13` AS `mpaa`,
    `bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
    `bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
    `seasons`.`idSeason` AS `idSeason`
  FROM (((((`Kodi_Mace_Video_93`.`episode` JOIN `Kodi_Mace_Video_93`.`files` ON(( `files`.`idfile` = `episode`.`idfile` )))
    JOIN `Kodi_Mace_Video_93`.`tvshow` ON(( `tvshow`.`idshow` = `episode`.`idshow` )))
    LEFT JOIN `Kodi_Mace_Video_93`.`seasons` ON(( ( `seasons`.`idshow` = `episode`.`idshow` ) AND ( `seasons`.`season` = `episode`.`c12` ) )))
    JOIN `Kodi_Mace_Video_93`.`path` ON(( `files`.`idpath` = `path`.`idpath` )))
    LEFT JOIN `Kodi_Mace_Video_93`.`bookmark` ON(( ( `bookmark`.`idfile` = `episode`.`idfile` ) AND ( `bookmark`.`type` = 1 ) ))
  );

  CREATE VIEW `Kodi_Mace_Video_93`.`movie_view` AS SELECT
    `movie`.`idMovie` AS `idMovie`,
    `movie`.`idFile` AS `idFile`,
    `movie`.`c00` AS `c00`,
    `movie`.`c01` AS `c01`,
    `movie`.`c02` AS `c02`,
    `movie`.`c03` AS `c03`,
    `movie`.`c04` AS `c04`,
    `movie`.`c05` AS `c05`,
    `movie`.`c06` AS `c06`,
    `movie`.`c07` AS `c07`,
    `movie`.`c08` AS `c08`,
    `movie`.`c09` AS `c09`,
    `movie`.`c10` AS `c10`,
    `movie`.`c11` AS `c11`,
    `movie`.`c12` AS `c12`,
    `movie`.`c13` AS `c13`,
    `movie`.`c14` AS `c14`,
    `movie`.`c15` AS `c15`,
    `movie`.`c16` AS `c16`,
    `movie`.`c17` AS `c17`,
    `movie`.`c18` AS `c18`,
    `movie`.`c19` AS `c19`,
    `movie`.`c20` AS `c20`,
    `movie`.`c21` AS `c21`,
    `movie`.`c22` AS `c22`,
    `movie`.`c23` AS `c23`,
    `movie`.`idSet` AS `idSet`,
    `sets`.`strSet` AS `strSet`,
    `files`.`strFilename` AS `strFileName`,
    `path`.`strPath` AS `strPath`,
    `files`.`playCount` AS `playCount`,
    `files`.`lastPlayed` AS `lastPlayed`,
    `files`.`dateAdded` AS `dateAdded`,
    `bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
    `bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
  FROM ((((`Kodi_Mace_Video_93`.`movie` LEFT JOIN `Kodi_Mace_Video_93`.`sets` ON((`sets`.`idSet` = `movie`.`idSet`)))
    JOIN `Kodi_Mace_Video_93`.`files` ON((`files`.`idFile` = `movie`.`idFile`)))
    JOIN `Kodi_Mace_Video_93`.`path` ON((`path`.`idPath` = `files`.`idPath`)))
    LEFT JOIN `Kodi_Mace_Video_93`.`bookmark` ON(((`bookmark`.`idFile` = `movie`.`idFile`) AND (`bookmark`.`type` = 1)))
  );

  CREATE VIEW `Kodi_Mace_Video_93`.`musicvideo_view` AS SELECT
    `musicvideo`.`idMVideo` AS `idMVideo`,
    `musicvideo`.`idFile` AS `idFile`,
    `musicvideo`.`c00` AS `c00`,
    `musicvideo`.`c01` AS `c01`,
    `musicvideo`.`c02` AS `c02`,
    `musicvideo`.`c03` AS `c03`,
    `musicvideo`.`c04` AS `c04`,
    `musicvideo`.`c05` AS `c05`,
    `musicvideo`.`c06` AS `c06`,
    `musicvideo`.`c07` AS `c07`,
    `musicvideo`.`c08` AS `c08`,
    `musicvideo`.`c09` AS `c09`,
    `musicvideo`.`c10` AS `c10`,
    `musicvideo`.`c11` AS `c11`,
    `musicvideo`.`c12` AS `c12`,
    `musicvideo`.`c13` AS `c13`,
    `musicvideo`.`c14` AS `c14`,
    `musicvideo`.`c15` AS `c15`,
    `musicvideo`.`c16` AS `c16`,
    `musicvideo`.`c17` AS `c17`,
    `musicvideo`.`c18` AS `c18`,
    `musicvideo`.`c19` AS `c19`,
    `musicvideo`.`c20` AS `c20`,
    `musicvideo`.`c21` AS `c21`,
    `musicvideo`.`c22` AS `c22`,
    `musicvideo`.`c23` AS `c23`,
    `files`.`strFilename` AS `strFileName`,
    `path`.`strPath` AS `strPath`,
    `files`.`playCount` AS `playCount`,
    `files`.`lastPlayed` AS `lastPlayed`,
    `files`.`dateAdded` AS `dateAdded`,
    `bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
    `bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
  FROM (((`Kodi_Mace_Video_93`.`musicvideo` JOIN `Kodi_Mace_Video_93`.`files` ON((`files`.`idFile` = `musicvideo`.`idFile`)))
    JOIN `Kodi_Mace_Video_93`.`path` ON((`path`.`idPath` = `files`.`idPath`)))
    LEFT JOIN `Kodi_Mace_Video_93`.`bookmark` ON(((`bookmark`.`idFile` = `musicvideo`.`idFile`) AND (`bookmark`.`type` = 1)))
  );

  CREATE VIEW `Kodi_Mace_Video_93`.`tvshowcounts` AS SELECT
    `tvshow`.`idshow` AS `idShow`,
    Max(`files`.`lastplayed`) AS `lastPlayed`,
    Nullif(Count(`episode`.`c12`), 0) AS `totalCount`,
    Count(`files`.`playcount`) AS `watchedcount`,
    Nullif(Count(DISTINCT `episode`.`c12`), 0) AS `totalSeasons`,
    Max(`files`.`dateadded`) AS `dateAdded`
  FROM ((`Kodi_Mace_Video_93`.`tvshow` LEFT JOIN `Kodi_Mace_Video_93`.`episode` ON(( `episode`.`idshow` = `tvshow`.`idshow` )))
    LEFT JOIN `Kodi_Mace_Video_93`.`files` ON(( `files`.`idfile` = `episode`.`idfile` ))
  )
  GROUP BY `Kodi_Mace_Video_93`.`tvshow`.`idshow`;

  CREATE VIEW `Kodi_Mace_Video_93`.`tvshow_view` AS SELECT
    `tvshow`.`idShow` AS `idShow`,
    `tvshow`.`c00` AS `c00`,
    `tvshow`.`c01` AS `c01`,
    `tvshow`.`c02` AS `c02`,
    `tvshow`.`c03` AS `c03`,
    `tvshow`.`c04` AS `c04`,
    `tvshow`.`c05` AS `c05`,
    `tvshow`.`c06` AS `c06`,
    `tvshow`.`c07` AS `c07`,
    `tvshow`.`c08` AS `c08`,
    `tvshow`.`c09` AS `c09`,
    `tvshow`.`c10` AS `c10`,
    `tvshow`.`c11` AS `c11`,
    `tvshow`.`c12` AS `c12`,
    `tvshow`.`c13` AS `c13`,
    `tvshow`.`c14` AS `c14`,
    `tvshow`.`c15` AS `c15`,
    `tvshow`.`c16` AS `c16`,
    `tvshow`.`c17` AS `c17`,
    `tvshow`.`c18` AS `c18`,
    `tvshow`.`c19` AS `c19`,
    `tvshow`.`c20` AS `c20`,
    `tvshow`.`c21` AS `c21`,
    `tvshow`.`c22` AS `c22`,
    `tvshow`.`c23` AS `c23`,
    `path`.`idParentPath` AS `idParentPath`,
    `path`.`strPath` AS `strPath`,
    `tvshowcounts`.`dateAdded` AS `dateAdded`,
    `tvshowcounts`.`lastPlayed` AS `lastPlayed`,
    `tvshowcounts`.`totalCount` AS `totalCount`,
    `tvshowcounts`.`watchedcount` AS `watchedcount`,
    `tvshowcounts`.`totalSeasons` AS `totalSeasons`
  FROM (((`Kodi_Mace_Video_93`.`tvshow` LEFT JOIN `Kodi_Mace_Video_93`.`tvshowlinkpath` ON(( `tvshowlinkpath`.`idshow` = `tvshow`.`idshow` )))
    LEFT JOIN `Kodi_Mace_Video_93`.`path` ON(( `path`.`idpath` = `tvshowlinkpath`.`idpath` )))
    JOIN `Kodi_Mace_Video_93`.`tvshowcounts` ON(( `tvshow`.`idshow` = `tvshowcounts`.`idshow` ))
  )
  GROUP BY `Kodi_Mace_Video_93`.`tvshow`.`idshow`;

  CREATE VIEW `Kodi_Mace_Video_93`.`season_view` AS SELECT
    `seasons`.`idSeason` AS `idSeason`,
    `seasons`.`idShow` AS `idShow`,
    `seasons`.`season` AS `season`,
    `tvshow_view`.`strPath` AS `strPath`,
    `tvshow_view`.`c00` AS `showTitle`,
    `tvshow_view`.`c01` AS `plot`,
    `tvshow_view`.`c05` AS `premiered`,
    `tvshow_view`.`c08` AS `genre`,
    `tvshow_view`.`c14` AS `studio`,
    `tvshow_view`.`c13` AS `mpaa`,
    Count(DISTINCT `episode_view`.`idEpisode`) AS `episodes`,
    Count(`files`.`playCount`) AS `playCount`
  FROM (((`Kodi_Mace_Video_93`.`seasons` JOIN `Kodi_Mace_Video_93`.`tvshow_view` ON((`tvshow_view`.`idShow` = `seasons`.`idShow`)))
    JOIN `Kodi_Mace_Video_93`.`episode_view` ON(((`episode_view`.`idShow` = `seasons`.`idShow`) AND (`episode_view`.`c12` = `seasons`.`season`))))
    JOIN `Kodi_Mace_Video_93`.`files` ON((`files`.`idFile` = `episode_view`.`idFile`))
  )
  GROUP BY `Kodi_Mace_Video_93`.`seasons`.`idSeason`; 


Now I know I havn't gone into much detail but SQL isn't really for the new user
I can do this and ref to each user as a, b, c or 1, 2, 3 but I never like that setup and it make it harder to find and replace.

So the find and replace vars are as followed
Main DB Referance:

• Kodi_Matt_Video_93
  
• playCountMatt
  
• lastPlayedMatt
  

Each user DB

• Kodi_Mace_Video_93
  
• playCountMace
  
• lastPlayedMace
  

• Kodi_Kiyana_Video_93
  
• playCountKiyana
  
• lastPlayedKiyana
  

• Kodi_Jaide_Video_93
  
• playCountJaide
  
• lastPlayedJaide
  

I hope this makes sense

[wickedsun]

Hey BigMong!

The perlr script I created makes it so you no longer have to modify that query every time the schema changes. Right now, my db is on revision 103, but next time I upgrade kodi, who knows, it may become 104. When I do upgrade, I'd lose my watched status for each of the slaves. My script should take care of this (eventually) and copy over the old lastPlayed and playCount of each slave and relink everything properly.

Right now the script does not keep track of slaves nor does it upgrade, but those are functionalities I'll be implementing shortly.

[BigMong]

Cool I had a little look at your script, seems nice and easy

a little tip with watch status is I like to use https://trakt.tv/ to keep a record of this data then if you need to rebuild/update the db you can just import it all again, nice and easy

I see your script is missing the Trigger "delete_file" on the main file table, it only keeps the db clean if you remove files and cleans up the slave tables
Very nice work tho

[horstepipe]

hey
will the instructions from the first post work with Jarvis?

[horstepipe]

hey BigMong I exactly followed your instructions without SQL errors.
Main-database works, but user-database stays empty.
In Kodi log I have the following error:
19:57:56 T:12264 ERROR: CVideoDatabase::GetMoviesByWhere failed

any ideas?

[apeg]

hey
will the instructions from the first post work with Jarvis?

Hey horstepipe, in short; no

The database numbers are very important when using any of the information from this forum thread. BigMong latest post will get you up to video_93, which i believe is Isengard if i'm not mistaken. If you are running Jarvis i'm guessing you're running video_99.

So you would have to figure out what what changes were made in the _99 Schema vs. the _93 and alter BigMong's latest post accordingly. Depending on your level of comfort with mySQL that could be asking a lot.

[apeg]

For all those interested, I've made this into a fully (almost) dynamic script which will (most likely) work on schema changes. Right now it is very much beta, but I'd love to get feedback on it!

http://pastebin.com/wZ7xLe9G

Interesting script, i wish it wasn't in Perl but beggars can't be choosers.

I'm guessing your claim of persistence regardless of schema updates is based on how you copy the existing table to then edit? So as long as the specific changes we're making here for multi user are not effected greatly this should work with yet unreleased DB versions? am i close on that?

I have db_99 up and running now, ill test it first chance i get and let you know.

Thanks,

[horstepipe]

thank you

If someone gets this running on a 99-database please share your sql queries.

regards

[wickedsun]

Cool I had a little look at your script, seems nice and easy

a little tip with watch status is I like to use https://trakt.tv/ to keep a record of this data then if you need to rebuild/update the db you can just import it all again, nice and easy

I see your script is missing the Trigger "delete_file" on the main file table, it only keeps the db clean if you remove files and cleans up the slave tables
Very nice work tho

The only table I create is the bookmark table. Everything else is as is from the table kodi creates. I fail to see why it wouldn't work on the main table however... the main should have the same things as the slaves... I'll have to take a deeper look, but please feel free to explain to me what I'm doing wrong and how I can make it better. Like I stated in my other post, I am far from being a good SQL guy or a good perl guy and I'd love for someone to jump on and help!

Using trakt would require me grabbing from their API, and that's definitely not in the scope of what I was trying to do. My goal is so that when a guest comes over, I can spawn a new profile and database, they have their own watched status and bookmarks. So if they return, it's still there. Trakt would require to create a new account for each guest, because trakt is definitely not "main stream", most people don't have an account.

[wickedsun]

For all those interested, I've made this into a fully (almost) dynamic script which will (most likely) work on schema changes. Right now it is very much beta, but I'd love to get feedback on it!

http://pastebin.com/wZ7xLe9G

Interesting script, i wish it wasn't in Perl but beggars can't be choosers.

I'm guessing your claim of persistence regardless of schema updates is based on how you copy the existing table to then edit? So as long as the specific changes we're making here for multi user are not effected greatly this should work with yet unreleased DB versions? am i close on that?

I have db_99 up and running now, ill test it first chance i get and let you know.

Thanks,

Sorry, perl is the only language I know well enough to remotely do anything like this. Just be happy it's not bash

For the file table, I take the one from the main db (the one create by kodi) and I read each column and I link them in the fake file (view) table. I do the same thing with every other table. The tricky part was the views created by kodi. Those have "dependencies" (some views link to other views), so there's a dependence thing going on. The only non-dynamic table right now is the slave bookmark table. Since the bookmarks aren't applied on the files table (basically the bookmark is empty and it add rows, so they all have to be completely separate per profile). I'll probably work on that to just take the table from the main table, snag the create statement and just recreate it verbatim. The main problem here is upgrades.. bookmarks may not be able to move over since if the schema changes, I can't just copy it (from old to new). It's a little trickier, especially since I have to wait for it to update to test.

I haven't done much with the script in the past few days, been busy, but I'll probably go back to it soonish. I want this to be a great script for people like me since there isn't anything dynamic that does this at the moment.