TSQLRecord.ID type change suggestion

h.hasenack · 2012-10-19 09:16:41

Hi Ab

I'm writing an object that requires a (DB) link to another object and found the ID is declared directly as integer. I think it would be well-advised to generate a special type identifier for the TSQLRecord.ID, so code wont break if later on the ID is eg changed to a cardinal or Int64. (I know JSON doesn't like cardinals from the doc, but not exactly why).

It's a bit of work right now, but may help save a lot of work in the future.

type
  TSQLRecordID=integer;

TSQLRecord=class(TObject)
...
  TSQLRecordID;
...
  property ID:TSQLRecordID ...
...
end;

mpv · 2012-10-19 09:38:46

such changes require a huge refactoring of mORMot, I think. Let's wait what AB sad. As for me, the best choice is Int64 as it in mORMot now. But I have remarks for you proposition:
1) TSQLRecordID=integer; is not very good, because if we possibly move to x64 integer become int64 and recreating primary key of existing DB tables is a problem. So TSQLRecordID=int32 must be used in your case;
2) In my project's I fount what int32 is not enough in real life. Really - I have int32 primaty key overflow in some production DB, so to prevent future problems I recommend use Int64 as primary key. But this is IMHO.
About JSON: JSON doesn't like cardinals because of JavaScript internal realization (JSON is primary using in JavaScript). For example in Mozilla SpiderMonkey internal(I think in V8 to) cardinal stored as double. Using Int64 is also a VERY big problem in JavaScript - it also become double, so only Int64 with 16 digits long is usable.

h.hasenack · 2012-10-19 09:54:14

Int the sources I have, in SQLite3Commons.pas, about position 3592 it says

    property ID: integer read GetID write fID;

FAIK this is not an int64 so it needs to be refactored anyway. And if we have to, then I suggest also creating a separate type for it. It the type is still the same as the old one, as I suggested before, it wont break your existing code, though it would be wise to change your ID related variables to the same type.

In delphi x64 compiler integer is still defined as int32. Maybe not in lazarus, I dont know. I agree Int64 is a better choice for the ID anyway. or a TGUID. (Where did I read this before )

Hans

Last edited by h.hasenack (2012-10-19 09:58:03)

ab · 2012-10-19 11:56:41

In mORMot, TSQLRecord kind of properties are mapped to integer for one-to-many or one-to-one properties.
That is, it is TSQLRecord = TObject = pointer = PtrInt = NativeInt = integer in 32 bit environment.

The same for TSQLRecordReference kind of properties, which maps a cardinal.

This is clearly stated by the documentation.

SQLite3 handle Int64 for IDs.
GUID is IMHO a Microsoft policy.
IMHO integers are much easier to store and index than GUIDs. For instance, it is easy to create auto-sorted ID values when inserting records (this is what we do for mORMot in-memory tables).
And AFAIK usual practice for RESTful access (the scheme used by mORMot routing) is mostly about using integers.

Would you need more than 2,147,483,647 values in your table?

mpv · 2012-10-19 12:05:14

Hmmm. Really - TSQLRecord.ID is integer. I work with mORMot in more lower level, and in SynDB level it's int64.
About int overflow - In my project there is one ID counter for all table, ant first 3 digit of it is customer number (I need generate different ID for different customer). So yes - I need bigint.
But if I do not reserve first 3 digit int32 is not enough too - in my country live 42million people, everyone pay taxes every month, so int32 is only for 1 year of production usage of level-country tax system, for example....

About storing int - yes - it much easy - I agree with you

Last edited by mpv (2012-10-19 12:16:58)

h.hasenack · 2012-10-19 12:19:18

At the moment I dont think we need than 2G objects :\
But it's more about the fact that ID is an integer now, and might be something different in the future (Int64 like in SQLite3). So I think it would generally be a good idea to define a special type (nothing fancy) for the ID.

About GUID's : it's not really a M$ thing, it is actually known as a UUID and used on many platforms: http://en.wikipedia.org/wiki/Globally_unique_identifier though the notation with curly braces seems to be a M$ thing. I believed it to be M$ invention too until we did some research...

About the ID being an a simple integer: IMHO an ID, especialliy a generated ID should be impossible to be negative, so it should be a UINT32 or UINT64. But from what I read in the docs, this can be a bit of a problem for JSON communication.

Targeting as int32 or int64 instead of integer ensures the same type is defined independent of the platform it is used on.

If you want the ID to be interchangablke with pointers, you'd netter use NativeUInt. and again defining your

TSQLRecordID=NativeUInt

will come in handye because you can pout it in $ifdefs if it needs to be a different type (or type identifier) on a different platform.

Hans

Last edited by h.hasenack (2012-10-19 12:22:08)

ab · 2012-10-19 12:37:31

I do agree.
Int64 may be added at some point.
Since they are "fake" pointers, using an Int32 even on 64 bit platform won't be a concern.

My main point was about the fact that native IDs in SQLite3, which is our core DB, is integer (possibly negative, if forced at insertion, but not to be used according to SQLite3 recommendations).
Adding a string as index, mapping a GUID/UUID is possible, but you'll always have the RowID INTEGER field anyway. So you are adding data just for the pleasure.
In a DDD world, you don't need to know how is your key designed. You just get your entity objects from the repository using the key. And do not care about the DB internals.

This is one of the KISS design choices of mORMot, which makes the code easier to maintain, make possible some features (like TSQLRecord / TSQLRecordReference kind of properties), and avoid the need of internal lookup tables.

In the road-map, you have external database handling with a mapping policy.
With such patterns, you would be able to store your data using whatever you want, an integer, a string, a UUID... but there will be some work to manage the mapping.

I know some other ORMs allow such possibilities based on $ifdef, to allow identification by integer / string / TGUID. But you won't be able to mix types.
But this is IMHO some unneeded design change. The external database custom mapping is a better answer I suspect.
In mORMot, upper ORM layer has its policy, but lower persistence layer, with database access, has its own.
And you would be able to mix ID types, on request: new DB may have their direct integer ID, and legacy / regular DBs may have their own primary keys, potentially over several columns.
Sounds not like a limitation nor a bug, but like a feature to me.

h.hasenack · 2012-10-19 13:23:11

with the $ifdef I did not think of changing the numerical type into a UUID or string. More like supporting platforms, whe on one compiler NativeInt is defined on another some different identifier...
allowing GUID or string record ID's would seriously complicate the underlaying code, and IMHO this is not a serious requirement.

I can foresee using Int64 (or Uint64 for that matter) being used in the near future.

mpv · 2014-11-14 12:17:45

AB! My congratulation about your last commit what change ID from Int to Int64. I miss this feature long time and this is one of main reason I use my own ORM implementation instead of mORMot.

ab · 2014-11-14 14:09:56

That's it: we have introduced the new TID = type Int64 definition as our ORM primary key, so that we would be able to compute coherent monotonic system-wide IDs.

See http://synopse.info/files/html/Synopse% … ml#TITL_26
and http://blog.synopse.info/post/2014/11/1 … rimary-key

mariomoretti · 2014-11-19 08:09:50

very very tx AB !!!

mdbs99 · 2018-02-13 19:37:28

That is a old thread, but I think this is the best place to ask:
If my table has only GUID/UUID primary key, how would be mORMot behavior, if there is not ID column at the physical table?

ab · 2018-02-14 00:12:02

An integer primary key is required by the framework ORM.

mdbs99 · 2018-02-14 01:47:57

Is it possible fake this ID by overriding some method?
It's early to say but, I'm thinking if `TID` could be a `Variant` instead of `Int64`. What do you think?

Chaa · 2018-02-14 03:41:03

It'a a sqlite feature called ROWID, see https://www.sqlite.org/rowidtable.html
You can add GUID field to your table and use it as primary key in your business logic, but sqlite still use ROWID to identify records.

mdbs99 · 2018-02-14 11:36:21

Thanks. But, in my case, I'm referring about MS SQL Server.
I would like to access MS SQL directly and many tables, on a specific system, don't have ID as integer, only uniqueidentifier type as primary key.

ab · 2018-02-14 14:46:39

Then there is no problem to use the SOA of mORMot. Using SynOleDB for direct access to the MSSQL database.

But you can't use the ORM directly.

ab · 2018-02-14 14:48:24

@chaa We expect the virtual table feature of SQLite, which is NOT compatible with Without RowID tables.

mdbs99 · 2018-02-14 15:11:34

ab wrote:

Then there is no problem to use the SOA of mORMot. Using SynOleDB for direct access to the MSSQL database.
But you can't use the ORM directly.

Understood

mORMot Open Source

#1 2012-10-19 09:16:41

TSQLRecord.ID type change suggestion

#2 2012-10-19 09:38:46

Re: TSQLRecord.ID type change suggestion

#3 2012-10-19 09:54:14

Re: TSQLRecord.ID type change suggestion

#4 2012-10-19 11:56:41

Re: TSQLRecord.ID type change suggestion

#5 2012-10-19 12:05:14

Re: TSQLRecord.ID type change suggestion

#6 2012-10-19 12:19:18

Re: TSQLRecord.ID type change suggestion

#7 2012-10-19 12:37:31

Re: TSQLRecord.ID type change suggestion

#8 2012-10-19 13:23:11

Re: TSQLRecord.ID type change suggestion

#9 2014-11-14 12:17:45

Re: TSQLRecord.ID type change suggestion

#10 2014-11-14 14:09:56

Re: TSQLRecord.ID type change suggestion

#11 2014-11-19 08:09:50

Re: TSQLRecord.ID type change suggestion

#12 2018-02-13 19:37:28

Re: TSQLRecord.ID type change suggestion

#13 2018-02-14 00:12:02

Re: TSQLRecord.ID type change suggestion

#14 2018-02-14 01:47:57

Re: TSQLRecord.ID type change suggestion

#15 2018-02-14 03:41:03

Re: TSQLRecord.ID type change suggestion

#16 2018-02-14 11:36:21

Re: TSQLRecord.ID type change suggestion

#17 2018-02-14 14:46:39

Re: TSQLRecord.ID type change suggestion

#18 2018-02-14 14:48:24

Re: TSQLRecord.ID type change suggestion

#19 2018-02-14 15:11:34

Re: TSQLRecord.ID type change suggestion

Board footer