Unique ID (SQLite, but all others too !)

Databases
1

I just realized that the Unique ID is… unique but numbers can be missed !

I checked in a “production db” (SQLite), and saw some missing IDs (using specialized methods to display SQLite db contents).

So, is a Unique ID meaningful only for the just loaded Record, not for navigation (unless you enter in a “trap missing Unique ID”) ?

2

@Emile Schwarz Also keep in mind when doing operation ‘compacting database’ that Unique ID may be recalculated and start with first free one which wasn’t used as well. E.g. you have records in db 1,2,3,4,5 and then remove 3,4. then do compacting and repair, after it can come that next new id will be 3,4… You should check this since it was before but I’m not sure is it still present since didn’t play with it in meantime with new versions of DBMS to check this.

Happy NY! Cheers.

3

Hi Bogdan,

thanks for your answer and happy Nex Year too.

I implemented at Record read time the storage of the Unique ID in a Label.

Now, I use it when I want to UPDATE (modify) the current Record and that seems to works fine (after a ton of simple errors most of them due to copy/paste!).
I need to modify the Record because the application is a “meeting manager” and after the meeting, one have to enter what happens durig the meeting and recommendations for Action(s).
Date / Time may be changed too, etc.

4

Hi Emile,

You can also use non Unique ID handled by DBMS and make your own checking function to get or make unique ID where then in your db that filed will be text or number type but indexed for example.

Also regarding your app concept, you should reconsider design of data back end.

You have meeting, that meeting have their properties, and then connect other part (new table e.g. happenings on meeting) where you will hold meeting id (which is unique id of meeting from meeting table), happening unique id, data and time and other data regarding to happening on meeting session. This will allows you that one meeting in your app will have more meeting sessions and history which you can later use for analytics and make changes overview.

One tip more regarding do adding records to some DBMS.

If your using INSERT INTO SQL statement then you can apply specific unique id via SQL which should be used and that will be stored as is in DBMS if is valid (not present already in DBMS).

Keep coding, keep moving, keep xojoing :slight_smile:

Cheers.

p.s. Hope you don’t mind on my quick overview above.

5

No problem friend.

I just have to read the main comment later to really understand your suggestion(s).

BTW: this software prints a user reminder fro that window. Another trick is … there ill be another application that will deal with helped people (they will have their own “folder”). I forgot how I am supposed to send the data in the other application’s person (family) “folder”, but I will have to do so. (move data from this db into the other one as history).

6

What are you using for the “unique ID”? Sqlite provides a RowID, but you shouldn’t use that except for the purpose of updating the row within a short time of having read it.

  • Always use an AutoIncrement Primary Key. Let the database assign a unique ID.
  • Never assign your own “unique ID”, unless you use a GUID.
  • Don’t use sqlite’s RowID.
  • It is quite acceptable for there to be gaps in the unique ID sequence.
  • Never reuse an ID.
7

[quote=367028:@Tim Hare]What are you using for the “unique ID”? Sqlite provides a RowID, but you shouldn’t use that except for the purpose of updating the row within a short time of having read it.

  • Always use an AutoIncrement Primary Key. Let the database assign a unique ID.
  • Never assign your own “unique ID”, unless you use a GUID.
  • Don’t use sqlite’s RowID.
  • It is quite acceptable for there to be gaps in the unique ID sequence.
  • Never reuse an ID.[/quote]

I only use a Integer Primary Key Autoincremented when using ActiveRecord as that is what it uses for the Unique ID. I always use a UUID/GUID for my Unique ID. So when I use AR, I have two fields for my Unique ID. One that I use and one that AR uses.

I agree with all the other bullet points that @Tim Hare mentioned here.

8

Thanks.

9

We’ve been working on a new version that can use UUID’s as the primary key! Not sure when it will get released but it will be part of ARGen version 3.

10

I know that was on the list of “it will be nice to haves”. That will be a really nice improvement to it. If it was a real issue, I would have cracked open AR and updated the code myself.

Thanks!!
—sb

PS> if you need a beta tester, let me know.