Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

Help with re-index my SQLite DB

Discussion in 'Scripting' started by pKallv, Feb 9, 2019.

  1. pKallv

    pKallv

    Joined:
    Mar 2, 2014
    Posts:
    1,177
    This is my first time with SQLite and I am trying to reindex my SQLite DB, after deleted record, but always get the max nr of records in each 'id' row.

    Skärmavbild 2019-02-09 kl. 18.46.02.png

    id' row should be 1, 2, 3 etc. but is now 2135 in all records.

    Skärmavbild 2019-02-09 kl. 18.50.50.png

    When I have deleted a record I want to reindex the whole table so 'id' starts with 1 and ends with max records number.

    I have been trying all kinds of things, googled a lot, but failed. Here is the code I use, last tests:

    Code (CSharp):
    1. void ReIndexDB ()
    2.     {
    3.         List<string> importedFiles = new List<string>();
    4.         int recNr = 1;
    5.         int id;
    6.  
    7.         OpenDB();
    8.         dbcmd = dbconn.CreateCommand();
    9.         sqlQuery = "SELECT * from questions";
    10.         dbcmd.CommandText = sqlQuery;
    11.  
    12.         reader = dbcmd.ExecuteReader();
    13.  
    14.         while (reader.Read())
    15.         {
    16.             SqliteCommand command = new SqliteCommand();
    17.             dbcmd2 = dbconn.CreateCommand();
    18.  
    19.             sqlQuery = "UPDATE questions SET id = '" + recNr + "'";
    20.             recNr++;
    21.  
    22.             dbcmd2.CommandText = sqlQuery;                                              // sets dbcmd.CommandText to be equal to the insert statement created above
    23.             dbcmd2.ExecuteNonQuery();
    24.         }
    25.  
    26.         txt_nrOfQuestions.text = recNr.ToString() + " questions in DB";
    27.  
    28.         reader.Close();
    29.         dbconn.Close();
    30.         reader = null;
    31.         dbcmd.Dispose();
    32.         dbcmd2.Dispose();
    33.         dbcmd = null;
    34.         dbconn = null;
    35.  
    36.         img_ConfirmationDelete1.rectTransform.anchoredPosition = new Vector2(630f, -15f);
    37.         UIAccess(true);
    38.  
    39.     }
    40.  
     
  2. Kurt-Dekker

    Kurt-Dekker

    Joined:
    Mar 16, 2013
    Posts:
    38,520
    I think line 19 in your script is operating on the entire questions table... you want it to instead operate on one line at a time with a different number each time.

    I'm not a SQL guy but I'm pretty sure that's your problem. You need to use a WHERE clause too, or else somehow specify to SQL that you are only wanting to operate on one row, not the entire 'questions' table.

    Remember also that SQL is engineered to operate on your data as a complete set. If I recall, you may not be able to iterate the items linearly until you provide some sort of ordering key. Without the ordering key, the database treats it as an unordered collection of rows and (I think) won't let you get at it as an array.
     
    pKallv likes this.
  3. The most important questions are:
    - why do you need monotonic increasing ids? is it really needed and important?
    - is it okay to remap the lines? (because if it is, you really don't want to update your entire database, you just take the very last row and update its ID to the one you just deleted)

    In general it's a really bad idea reusing deleted IDs, you will mix up sooner or later and will count on the fact that you have certain data on an ID (this is IDs for) and then you will stab yourself in the ... back with storing completely other thing with the same ID.
     
  4. Antypodish

    Antypodish

    Joined:
    Apr 29, 2014
    Posts:
    10,754
    If you reindex with one reference that may be relatively easy. But once you expand your DB adding more references etc, you will get in to trouble, as @Lurking-Ninja mentioned.

    You would need to be extremely careful with removing table rows, with references. You must propagate through all references, to mark up as not used. And then, you may want leave them for historical purposes.
    Then adding new reference to same ID. And at sudden, you have two references to same index, where should be only one. Just an example.

    It is very easy to forget and generate unexpected results, when something was depending on other record and not been properly cleaner, removed.
    And even more with shifting references.

    So unless you are certain, and really-really know what you doing, you would be batter not touching ids.
    In best case, just remove unused rows.

    The safest approach would be, to regenerate the DB, then you will know, you have all in order.
     
  5. pKallv

    pKallv

    Joined:
    Mar 2, 2014
    Posts:
    1,177
    Hmmm when you point this out I easily see the problem, I thought that it iterated the reader list and that I could change each record.
     
    Kurt-Dekker likes this.