Synchronizing client databases (1 Viewer)

Anthony Vaughan

MP Donator
  • Premium Supporter
  • June 25, 2015
    247
    36
    United Kingdom United Kingdom
    Country flag
    Hi ajs

    I am starting a new thread in reply to the following from a thread in the MP2 forum:

    <-- FanartHandler when start automatically search all new pictures and put it to db, dn have rowid in Image table, and TimeStamp (if i remember its time when picture added in db). -->

    The image database does have a time stamp column but it is only a date and is, therefore, not unique. There is also an id column, but it is of type TEXT and is also not unique. What I think would help would be a autoincrement column and I can't see one in the image table.

    Here's a bit of code that shows what I'm talking about.

    var maxID = listTo.Max(o => o.idPath);
    foreach (path item in listFrom.Skip((int)maxID))
    {
    list.Add(item);
    }

    We have two lists From and To. I find the highest id in list To then start to read the From list from the record after the highest To ID and add each item to the To list. This works because the ID is numeric and written to the table in arrival mode. This technique wouldn't be needed for a table with a few records. However, when there are thousands of records, like in the image table, this technique massively reduces resource use and the time to process the data.

    Here's what I have to do when we don't have an autoincrement ID.

    foreach (path item in listFrom)
    {
    single = listTo.Find(delegate (path d) { return (d.idPath == item.idPath); });
    if (single == null)
    {
    list.Add(item);
    }
    }

    Here, we have to read every record in the From list and check, for each From record, whether it exists in the To list, and if it doesn't exist add the From record to the To list. I hope you can appreciate how great the difference between the two options is.

    I will go ahead without the autoincrement column but if one could be added it would be great. The wonderful thing about having autoincrement fields in tables is that you don't have to reference them in your code because the database engine processes these fields automatically. However, you do have to add the field to your column definitions for the table.

    Tony
     
    Last edited:

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    247
    36
    United Kingdom United Kingdom
    Country flag
    Image table have:
    SQL:
    CONSTRAINT [pk_ID_Provider_Key] PRIMARY KEY([Id], [Provider], [Key1]) ON CONFLICT REPLACE
    Look - Rowid Tables
    I meant that the Time_Stamp column is not unique.

    I wasn't aware that the rowed table in SQLite is implicit. Thank you for pointing that out to me.

    There is one issue about rowid that concerns me. Please look at this link rowid. I think this demonstrates why an explicit INTEGER PRIMARY KEY is a better solution because I absolutely need to guarantee that the integer key does not change for file comparisons across databases to work (I need the integer key field to have the same key value for each row in each version of the each database).

    Tony
     
    Last edited:

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    247
    36
    United Kingdom United Kingdom
    Country flag
    This unique in Image table - [Id] + [Provider] + [Key1] :)
    I understand that, but a compound key is no good for what I need to do. I have to have an integer unique identifier that contains a number that is ordered in arrival sequence so I can guarantee that a record in a table in one database will have the same value as the equivalent record in another database. This allows me, as the code I showed you above demonstrates, to find the highest id in the To database and know that any id higher than that in the From database must be a new record. Your compound key cannot help me do that.

    I can live with things as they are, but we have to accept there will be a significant performance hit by doing so. Having said that, using unique identifiers that autoincrement is standard practice in the SQL database industry - as VideoDatabaseV5.db3 demonstrates.

    Tony
     

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    247
    36
    United Kingdom United Kingdom
    Country flag
    Why not? You can Join all need column to one key ... Or use Image as unique key ...
    I have given you samples of code doing it both ways and why only a unique ID will work. My method avoids having to compare every record in the source table to see whether it exists in the target table. This matters when there are thousands of records. I have developed commercial systems involving tens of millions of records and this design allows data to be accessed and retrieved in less than a second. SQLite is not an enterprise database so addressing performance, and using every trick possible, is even more important.

    I would be very happy to hear, in detail, how using a compound key could achieve what we my code does; i.e. only writing new data to the target table without having to read any records in the target table first.

    Tony
     
    Last edited:

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    12,120
    6,888
    Kyiv
    Ukraine Ukraine
    Country flag
    I have given you samples of code doing it both ways and why only a unique ID will work. My method avoids having to compare every record in the source table to see whether it exists in the target table.
    In FH Image table, Not only records are added and deleted, but also existing ones are changed, so looking at the number is not entirely correct, a comprehensive analysis is necessary here.
    I would be very happy to hear, in detail, how using a compound key could achieve what we my code does; i.e. only writing new data to the target table without having to read any records in the target table first.
    Here I will not help, mpsync analyzes the entire table as far as I remember, if I came up with how to do change analysis and two-way replication, I would already do it.
     

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    247
    36
    United Kingdom United Kingdom
    Country flag
    In FH Image table, Not only records are added and deleted, but also existing ones are changed, so looking at the number is not entirely correct, a comprehensive analysis is necessary here.

    Here I will not help, mpsync analyzes the entire table as far as I remember, if I came up with how to do change analysis and two-way replication, I would already do it.
    I thought the whole point of this conversation was that MPSync didn't work for you/the performance was poor. It certainly doesn't work on my system.

    That's why I developed an alternative application. I've been using it for over a year and it does work with four clients, and now for the music, picture fanart, folder and video databases. I am only attempting to improve performance while processing the fanart database. All of the other databases have unique ID's and I get good performance with them.

    Anyway, I know when to give up - so I'll leave it at that.

    Tony
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    12,120
    6,888
    Kyiv
    Ukraine Ukraine
    Country flag
    I thought the whole point of this conversation was that MPSync didn't work for you. It certainly doesn't work on my system.
    It works, but in one direction, i.e. from the master to NAS, and already customers are taking away from NAS to themselves. It is not very convenient, but it works.
    Ideally, you need two-way replication (no master), so that all changes on all computers are replicated among themselves (it is possible through a single repository on the NAS).
    That's why I developed an alternative application. I've been using it for over a year and it does work with four clients, and now for the music, picture fanart, folder and video databases. I am only attempting to improve performance while processing the fanart database. All of the other databases have unique ID's and I get good performance with them.
    This is understandable, but adding a key to the fanart base will not change much, because changes can be inside the record (like Last Access). As far as I remember, Last Access changes when using and updating, Time Stamp when changing. It seems to me that the time stamp is a good field for analysis, it is not unique (but as for me it is not necessary), but you can select what is needed from it.
     

    Users Who Are Viewing This Thread (Users: 0, Guests: 1)

    OP Similar threads Forum Replies Date
    C Current NUC or Intel Stick as an MP client for HD playback - minimum requirements? General 0
    CanadianEh MP TV server rebuild with Colossus 2 - Kodi clients receive PMT error when playing videos recoreded by Colossus 2 TV-Server 1
    O [Question] Advice Wanted on Developing a Client Application for a Disabled Family Member MPExtended 7
    ge2301 [WiP] MP2Web - A platform independent web client for MP2 MediaPortal 2 68
    M [solved] WTV Files does not play on client, MPEG files works well General 1
    ge2301 Android based client for MP2 MediaPortal 2 3
    T Client / Server slow channel-to-channel change (zapping) General Support 1
    horned_reaper Standby is prevented by MediaPortal client General Support 0
    P [solved] MP2 new install, 2 x hdhomerun tuners, client on same PC unable to view TV & recordings Newcomers Forum 8
    J Where the heck is the Client side setup? General 2
    T Server / client channel logo location Installation, configuration support 6
    framug [solved] Some changes/fix in MP1 configuration client and server Submit: code patches (MediaPortal/TV-Server/etc.) 14
    C Streaming to clients over Wifi. MP1 or MP2? Newcomers Forum 0
    Golf4 Client verliert immer wieder die Verbindung Allgemeines Support- und Diskussionsforum 4
    P Streaming to two clients from the same card Newcomers Forum 1
    TLD Can not watch tv from remote clients Television (MyTV frontend and TV-Server) 19
    R [further infos missing] some movies freezing evry seconds Submit: Bug Reports 9
    R [solved] MP2 Client ist extrem langsam Allgemeines Support- und Diskussionsforum 5
    I MP Server goes into suspend, because it doesn't detect the MP Client General Support 6
    A Client/Server Setup: Slow TV Startup Television (MyTV frontend and TV-Server) 0
    P MP2 Client Screen Flickering on Resume General 2
    J Server vs Client Install General 5
    Golf4 Client-Konfig clonen ? Allgemeines Support- und Diskussionsforum 2
    A Working server, no video playback on client General 5
    K MpTvClient: Client for Amazon and Android devices General 2
    Similar threads

























    Top Bottom