ID Field Auto-increment

dieforwhat · 2012-06-17 22:08:00

Hi everyone.I've newly downloaded and begun to use the framework.My problem is when a table is auto created ,sometimes ID field is autoincremented and sometimes not(but always PK).I can't find any difference between my tables.They are just regular tables with different fields and no property as tsqlrecordmany(just string and integer fields).Is this a bug or am I missing a critical point.(I've also searched the forum but not encountered with this topic.)Any help will be appreciated.

Last edited by dieforwhat (2012-06-17 22:09:02)

ab · 2012-06-18 06:26:52

This is how the SQLite3 engine works.
See http://www.sqlite.org/autoinc.html:

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

Therefore it is not a bug, but by SQLite3 design.

So in order to ensure that all RowIDs are unique in the time being, the RowID field definition should be explicitly marked as INTEGER PRIMARY KEY AUTOINCREMENT, if I understand well this page. It will create a sequence, so it will use a bit more data and CPU than the default behavior based on MAX() over an index.

What is your exact concern with this RowID reuse pattern?

ab · 2012-06-18 06:33:39

I've just checked out the SQLite3Commons.pas code.

In fact, the ID/RowID field is created in TSQLRecord.GetSQLCreate as

      result := 'CREATE TABLE '+SQLTableName+
        '(ID INTEGER PRIMARY KEY AUTOINCREMENT, ';
      // we always add an ID field which is an INTEGER PRIMARY KEY
      // column, as it is always created (as hidden ROWID) by the SQLite3 engine

Therefore you should not encounter your problem here.

I think you are not using the ORM, but only the low-level SQLite3 layer.
You have the default SQLite3 behavior, which does make sense.
If you want unique IDs, use the ORM part of mORMot, or specify INTEGER PRIMARY KEY AUTOINCREMENT explicitly in your table creation.

dieforwhat · 2012-06-18 07:09:34

Thanks for immidate answers.I'm neither using low level sql apis nor creating tables my self.I've just create a descendant of tsqlrecord and add it to my model.Table is automatically created but id field is not autoincremented.This is the image from sqliteadministrator.This is strange as I've 2 more tables but neither of them have this issue.With looking this picture, should we expect ID fileds have NOT NULL AUTOINCREMENTED ...constraints?By the way I've catch this by when I try to add new record to that table it is not working,new record never get added and maybe expecting some ID value given by me.(I know making id field autoinc is more easy than writing this post but I want it from framework since database file will be created in customer pc.Even I tried to make it myself but then I have the error 'SQL Error: no such collation sequence: SYSTEMNOCASE'.As far as I know this is becasue sqliteadminstrator (from sqliteadmin.orbmu2k.de) is not aware of framework created collation sequence and all my queries from sqliteadminstrator end up with this error but this is another issue anyway.)

Last edited by dieforwhat (2012-06-18 07:28:55)

ab · 2012-06-18 08:03:59

If I use SQLiteSpy - from http://www.yunqa.de/delphi/doku.php/pro … espy/index - you can see the CREATE statement of each table.
For instance, it should be as such for a table used for regression tests, as auto-created by mORMot:

CREATE TABLE ASource(ID INTEGER PRIMARY KEY AUTOINCREMENT, SignatureTime INTEGER, Signature TEXT COLLATE SYSTEMNOCASE);

Since there is the "ID INTEGER PRIMARY KEY AUTOINCREMENT" field definition, the ID should be unique.
It is not a constraint, it is the column creation type which uses a constraint. So I guess you should not see it in this window of SQLiteAdministrator.

About error when record should be added, please provide additional information. I was not able to find out what is wrong here.
Without no code to reproduce, it's hard to tell.

About the collations, you are right, mORMot uses SYSTEMCASE / SYSTEMNOCASE collations, which are not restricted to ASCII 7 bit char comparison, which is the default for SQLite3, but not enough for true Unicode TEXT process (e.g. indexes may not be sorting as expected).
So it does make sense that you can't have full access to mORMot SQLite3 tables directly.

dieforwhat · 2012-06-18 08:26:41

. I've downloaded sqlspy and see that you're absolutely right.( not about sqliteadministrator since it shows if ID filed autoincremented fieldcontrains column show that info).I've end up with that this is a bug in sqliteadministrator not mORMot and record saving error is about my coding (in fact mistyping).Thanks for your help and kindly replies and sorry about wasting your time.

ab · 2012-06-18 11:26:53

Thanks for your feedback!

No time waste, just making thinks clearer could help anybody in the forum.

mORMot Open Source

#1 2012-06-17 22:08:00

ID Field Auto-increment

#2 2012-06-18 06:26:52

Re: ID Field Auto-increment

#3 2012-06-18 06:33:39

Re: ID Field Auto-increment

#4 2012-06-18 07:09:34

Re: ID Field Auto-increment

#5 2012-06-18 08:03:59

Re: ID Field Auto-increment

#6 2012-06-18 08:26:41

Re: ID Field Auto-increment

#7 2012-06-18 11:26:53

Re: ID Field Auto-increment

Board footer