Auto GUID for Entity Primary key Id
3338
Views
33
Comments
On our RadarOn our Radar
Backend
Just like "Is Auto Number" option

Have an "Is Auto UniqueIdentifier" option to automatically generate GUID for entity primary key id.

2016-11-21 23-23-05
Gonçalo Borrêga
Can you describe the underlying problem(s) you want to resolve with this solution/capability?
I know some, but would like to understand the real problem
Thanks
Current solution:
  • Created an action <Entity>_Create that takes input parameter <Entity>Record 
  • Created an extension with an action "NewGuid" to  generate a guid value 
  • Assigned primary key value with guid value
Needing to do this for every entity that uses text.guid for the primary key is time consuming.  It would be good to have a Guid function built into the agile platform, and also auto guid option.


2016-11-21 23-23-05
Gonçalo Borrêga
What I was trying to understand is under which scenarios would you like to use Guid primary keys for?
Integration motives? Easier data synchronization between servers? Something else?
[I replied with a long explaination describing our use case, but the session timed out]


In short...we are using GUID for our cloud based application, to solve two problems

-Get around the int limitation (we host many companies data and customers data under a single cloud platform)
-Cover future requirement: able to generate guid offline, for POS desktop app and merge data with cloud platform.



When an entity primary key identifier is set to "text" by default scaffolding (intelliwrap) is to set the primary key to use GUID.

Who manually enters primary key identifiers anyways? 


Merged from '[ServiceStudio] Entity identifiers set with "Text" datatype, should use GUID identifier by default scaffolding' (idea created on 2015-01-14 22:35:45 by Robert Chanphakeo), on 2016-03-14 17:21:16 by Goncalo Borrega
Note: When a primary key/identifier is set to varchar/text, and you use "scaffolding", scaffolding creates a input textfield widget, and assumes someone will be entering a primary key manually, but in most cases everyone that sets the primary key/identifier of an entity to "text" they want to use GUID rather than enter the primary key/identifier manually!



Merged from '[ServiceStudio] Entity identifiers set with "Text" datatype, should use GUID identifier by default scaffolding' (idea created on 2015-01-14 22:35:45 by Robert Chanphakeo), on 2016-03-14 17:21:16 by Goncalo Borrega
Outsystems needs native support for the uniqueidentifier data type.

We should be able to create attributes with uniqueidentifier data type, and have a system function to create new GUID's.

https://msdn.microsoft.com/en-us/library/ms187942.aspx

Merged from 'Native support for UNIQUEIDENTIFIER data type' (idea created on 2012-05-24 14:40:50 by Carlos Henriques), on 2016-03-14 17:21:34 by Goncalo Borrega
Soes Oracle have a similar data type?

Merged from 'Native support for UNIQUEIDENTIFIER data type' (idea created on 2012-05-24 14:40:50 by Carlos Henriques), on 2016-03-14 17:21:34 by Goncalo Borrega
No. But it can be done with a RAW column and the SYS_GUID function:

https://docs.oracle.com/cd/B14117_01/server.101/b10759/functions153.htm




Merged from 'Native support for UNIQUEIDENTIFIER data type' (idea created on 2012-05-24 14:40:50 by Carlos Henriques), on 2016-03-14 17:21:34 by Goncalo Borrega
See GUID discussion here https://www.outsystems.com/forums/discussion/13832/entity-with-guid-primary-key-identifier/

Merged from 'Native support for UNIQUEIDENTIFIER data type' (idea created on 2012-05-24 14:40:50 by Carlos Henriques), on 2016-03-14 17:21:34 by Goncalo Borrega
Rather than using auto inc ID's, provide an option to auto gen a GUID.  This makes moving data from enviroment databases possible.

Merged from 'Support for autogenerated GUID's' (idea created on 2016-03-11 17:37:45 by Warren Strong), on 2016-03-14 17:22:01 by Goncalo Borrega
Should be merged with:

https://www.outsystems.com/ideas/1830/servicestudio-entity-identifiers-set-with-text-datatype-should-use-guid-iden

https://www.outsystems.com/ideas/935/auto-guid-for-entity-primary-key-id

https://www.outsystems.com/ideas/1137/native-support-for-uniqueidentifier-data-type

J.Ja

Merged from 'Support for autogenerated GUID's' (idea created on 2016-03-11 17:37:45 by Warren Strong), on 2016-03-14 17:22:01 by Goncalo Borrega

I really like the platform you have built. Very easy to use. From a security perspective, I would add the option to use GUIDs as record identifiers. When analyzing network traffic (parameters in URLs or post data), sequential IDs are easily identified and can be guessed.  



Merged from 'Support GUIDs as entity references instead of Long Integers' (idea created on 2017-03-03 23:32:09 by Iko Knyphausen), on 2017-03-07 08:57:18 by André Vieira

Duplicate of:

https://www.outsystems.com/ideas/935/auto-guid-for-entity-primary-key-id

J.Ja



Merged from 'Support GUIDs as entity references instead of Long Integers' (idea created on 2017-03-03 23:32:09 by Iko Knyphausen), on 2017-03-07 08:57:18 by André Vieira
2016-04-21 20-09-55
J.
 
MVP

Would still like to see it implemented.


Any updates on this @Gonçalo Borrêga?

I vote this.

2016-11-21 23-23-05
Gonçalo Borrêga
Merged this idea with 'Native support for UNIQUEIDENTIFIER data type' (created on 2012-05-24 14:40:50 by Carlos Henriques)

I really would like to have this feature in outsystems, not only for more easy data migration between platforms but also for some kind of extra security layer where users can't not that easy change anymore the url parameters that are currently populated with the generated long integers. 

Is there any chance that OutSystems 11 will get support for a proper way of storing a GUID?


Currently I'd have to store it in a CHAR(32) ; using in the database of at least 2 bytes per character, eating 512 bit's into the database storage and index while Microsoft has the proper hexadecimal storage in the GUID datatype in the database (and index) of 128 bit's.

https://www.sqlshack.com/understanding-the-guid-data-type-in-sql-server/


Some advantages and reasons we need this (and if possible in the proper way)

  • The GUID Data type takes up only 1/4th in storage, index and memory.
  • A GUID can be provided by external databases and gracefully merged / combined into a central database
  • A GUID enables customers / suppliers to communicate through their own references
  • This eliminates difficult structures when managing masterdata
  • Propagating structures on the change of shared records can be made more generic
    (You could have one big list of the latest version of a GUID and know whether you should refresh it on a remote location improving caching methods)


Merged this idea with 'GenerateGUID() as default value for identifiers' (created on 05 May 2020 11:19:52 by Beatriz Silva)

It would be useful to allow use the function GenerateGUID() as default value for entity identifiers. When thinking about data security, it's common nowadays in OutSystems projects the long integer auto number type being replaced by a GUID and sometimes additional logic needs to be implemented just for this purpose.




This comment was:
- originally posted on idea 'GenerateGUID() as default value for identifiers' (created on 05 May 2020 by Beatriz Silva)
- merged to idea 'Auto GUID for Entity Primary key Id' on 08 May 2020 00:47:11 by Justin James

I loved! Currently it does not accept Functions (User or Built-in ones), only CurrDate() and CurrDateTime() as non-literal values. Your idea could help speed-up development time even more!



This comment was:
- originally posted on idea 'GenerateGUID() as default value for identifiers' (created on 05 May 2020 by Beatriz Silva)
- merged to idea 'Auto GUID for Entity Primary key Id' on 08 May 2020 00:47:12 by Justin James

Good idea, Beatriz!



This comment was:
- originally posted on idea 'GenerateGUID() as default value for identifiers' (created on 05 May 2020 by Beatriz Silva)
- merged to idea 'Auto GUID for Entity Primary key Id' on 08 May 2020 00:47:12 by Justin James

Great idea, it could be so much useful...



This comment was:
- originally posted on idea 'GenerateGUID() as default value for identifiers' (created on 05 May 2020 by Beatriz Silva)
- merged to idea 'Auto GUID for Entity Primary key Id' on 08 May 2020 00:47:12 by Justin James

If this idea is implemented, then there needs to be a way to specify the GUID when bootstrapping data. At the moment you can't bootstrap Id fields if they are autonumber, which is a pain if you have other entities that use the Id as a foreign key.

The manual insert statements work in this but agreed this should be “easy”

Also in lifetime you should be able to manage any static table GUIDs globally imho.

And just let me stress again that native GUID support (so the real database level GUID type too) is very, very important for adoption of event driven tech, workflow and global database scenario’s.

Just a nudge to emphasise again how important GUIDs are for the future of the platform, as well as proper Graph Database support and GraphQL support for both Cloud strategy, performance at scale and integration flexibility.

Also less strong typed structures are limiting the new generation developers to spread their wings with the platform.

Any updates with this? 
The Idea by Beatriz Silva is nice. So you could define your own ID Format, or just use the common uid.

Nudge; a native GUID datatype should have already been implemented in the platform long time ago.

Main reason would be INDEX SIZE and SPEED of the database system.
The query optimizer of MSSQL won't go well on a CHAR(36) which is a GUID including HYPHENS.
In a current database system as VARCHAR that will take up 16 bit's per Character, being 16 x 36 = 576 bit's.

As a native type this would take up 16 Bytes, 128 Bits (over 4 times less), making the search result quicker as well as JOINS over this type.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/df3caee5-3751-469b-a943-f0c8cb501f75/unique-identifier-vs-varchar36-for-storing-guids?forum=transactsql

From a security perspective using a GUID as Identifier and in query parameters makes it almost impossible to do brute force attack to collect all data.

@Marc Wetters that's why I have switched 100% to them when I moved from Traditional Web to Reactive. Traditional Web made it very easy to prevent certain attacks around the attacker changing IDs. Reactive makes it very easy to accidentally let anyone overwrite any record they want.

J.Ja