mORMot Open Source

chapa

Member

Registered: 2012-04-30

Posts: 117

SQLite3 RowID

According SQLite3 documentation:

"... In SQLite version 3.0, the rowid is a 64-bit signed integer.
... To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative."

I noticed that the mORMot framework does not handle well cardinal TSQLRecord published properties, which I use to represent hashes.
If I used to store "real" cardinals directly using sqlite3_xx functions and try to query against TSQLRecord cardinal property which is > MaxInt, than cardinal is represented as negative int and the query does not return proper result.

Of course it can be easily work-arounded, but it will be nice if the framework handle row ids as stated in sqlite3 docs and cardinal properties right.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: SQLite3 RowID

RowID is a 32 bit integer within mORMot, to match the NativeInt = native pointer type = register type, since one-to-one and one-to-many references are defined as TSQLRecord published properties in the TSQLRecord class.
So I think we won't be able to easily change this. Do we need to?

About cardinal properties, you are right. I think there is an issue: the tkInteger kind of property is viewed as signed integer, which does not work with cardinal. There is some huge code refactoring to implement here.

As a workaround, until I fix it, you'll be able to define Int64 properties. It will handle cardinal values as expected, and won't use more space in SQlite3 file - only in memory, it will use a bit more size.
I've updated the documentation to reflect the fact that "cardinal" kind of published properties are not handled properly yet - should use Int64 instead.
In fact, Int64 is the only kind of INTEGER known by SQlite3 APIs. You should do the same.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: SQLite3 RowID

Surely, I agree fully about NativeInt and RowId. It is more common to be able to store negative ids if needed, than unsigned int32 (in case of 32 bit compiler).
Actually, already changed to use Int64. No problems so far, maybe some penalty from RTTI dealing with tkInt64 instead of tkInteger. But think it is a peace of cake in real-life.
Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: SQLite3 RowID

Yes, tkInt64 has been optimized also in mORMot, and, from the SQLite3 point of view, every INTEGER is an Int64.
So I do not expect any performance penalty about using Int64, only perhaps when searching in a huge TSQLRestServerStaticInMemory kind of table.