Synchronizing client databases (1 Viewer)

Anthony Vaughan

MP Donator
  • Premium Supporter
  • June 25, 2015
    566
    292
    Home Country
    United Kingdom United Kingdom
    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
    566
    292
    Home Country
    United Kingdom United Kingdom
    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
    566
    292
    Home Country
    United Kingdom United Kingdom
    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
    566
    292
    Home Country
    United Kingdom United Kingdom
    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
    15,492
    10,371
    Kyiv
    Home Country
    Ukraine Ukraine
    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
    566
    292
    Home Country
    United Kingdom United Kingdom
    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
    15,492
    10,371
    Kyiv
    Home Country
    Ukraine Ukraine
    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

    Top Bottom