Synchronizing client databases (1 Viewer)

Anthony Vaughan

MP Donator
  • Premium Supporter
  • June 25, 2015
    278
    45
    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
    278
    45
    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
    278
    45
    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
    278
    45
    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
    13,450
    7,973
    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
    278
    45
    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
    13,450
    7,973
    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

    Similar threads
    OP Title Forum Replies Date
    Requiem 1.27.0 1.27 Client crash from SplashScreen 1.24-1.27 Pre Release 4
    TLD 1.27 Pre Clients crash 1.24-1.27 Pre Release 84
    Scheibes MP2 - V2.3 Live TV is not possible if two clients are showing HD channels (Astra HD+) General 21
    S MP2 - V2.3 MediaPortal 2.3 Client stürzt ab - freetype6.dll nicht gefunden General 1
    Scheibes MP2 - V2.3 2. Client stops video on 1. Client Submit: Bug Reports 10
    toricred MP2 - V2.3 Client Dying When Changing Channels ATSC Submit: Bug Reports 1
    toricred MP2 - V2.3 MP2 2.3 Client won't start General 7
    Grisu002 MP2 - V2.3 MP2 2.3 Client startet nicht - bleibt beim Laden hängen Allgemeines Support- und Diskussionsforum 64
    R Firewall on server blocking client General 10
    P [solved] Blocking TV recordings on client? General 10
    -Merkur- MP2 Client startet nicht, "libXBMC_addon.dll" fehlt Allgemeines Support- und Diskussionsforum 1
    C Current NUC or Intel Stick as an MP client for HD playback - minimum requirements? General 6
    CanadianEh MP TV server rebuild with Colossus 2 - Kodi clients receive PMT error when playing videos recoreded by Colossus 2 TV-Server 2
    O [Question] Advice Wanted on Developing a Client Application for a Disabled Family Member MPExtended 7
    ge2301 [Postponed] MP2Web - A platform independent web client for MP2 MediaPortal 2 72
    M [solved] WTV Files does not play on client, MPEG files works well General 1
    ge2301 [Pending] 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

    Similar threads

    Top Bottom