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

h.hasenack
Member
From: Nijmegen, Netherlands
Registered: 2012-08-01
Posts: 173
Website

TSQLRecord.ID type change suggestion

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;

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

h.hasenack
Member
From: Nijmegen, Netherlands
Registered: 2012-08-01
Posts: 173
Website

Re: TSQLRecord.ID type change suggestion

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 wink 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 tongue)

Hans

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: TSQLRecord.ID type change suggestion

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?

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: TSQLRecord.ID type change suggestion

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)

Offline

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

h.hasenack
Member
From: Nijmegen, Netherlands
Registered: 2012-08-01
Posts: 173
Website

Re: TSQLRecord.ID type change suggestion

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

h.hasenack
Member
From: Nijmegen, Netherlands
Registered: 2012-08-01
Posts: 173
Website

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: TSQLRecord.ID type change suggestion

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

Offline

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

mariomoretti
Member
From: italy
Registered: 2012-01-29
Posts: 88

Re: TSQLRecord.ID type change suggestion

very very tx AB !!! smile

Offline

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

mdbs99
Member
From: Rio de Janeiro, Brazil
Registered: 2018-01-20
Posts: 132
Website

Re: TSQLRecord.ID type change suggestion

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: TSQLRecord.ID type change suggestion

An integer primary key is required by the framework ORM.

Offline

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

mdbs99
Member
From: Rio de Janeiro, Brazil
Registered: 2018-01-20
Posts: 132
Website

Re: TSQLRecord.ID type change suggestion

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?

Offline

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

Chaa
Member
Registered: 2011-03-26
Posts: 244

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

mdbs99
Member
From: Rio de Janeiro, Brazil
Registered: 2018-01-20
Posts: 132
Website

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: TSQLRecord.ID type change suggestion

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: TSQLRecord.ID type change suggestion

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

Offline

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

mdbs99
Member
From: Rio de Janeiro, Brazil
Registered: 2018-01-20
Posts: 132
Website

Re: TSQLRecord.ID type change suggestion

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  sad

Offline

Board footer

Powered by FluxBB