Alpha DevCon 2018
Results 1 to 18 of 18

Thread: UUID

  1. #1
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default UUID

    I recently saw in a video where someone made his SQL table Primary Keys UUID. Up until now, I never gave an auto-increment Int(10) field any second guesses. After doing some more research, it sounds like there will be some issues if multiple users try to submit new records to the same table at the same time. I then noticed that Alpha Anywhere has a field type for GUID. If I was to use this, how would I need to configure the MySQL table fields for the primary key? Varchar(40)? Would I need to write a trigger on the table to trim any dashes?

  2. #2
    Member
    Real Name
    David Hagen-Sørensen
    Join Date
    Jul 2013
    Location
    Denmark - Odense
    Posts
    66

    Default Re: UUID

    Hi Jack,

    I'm using UUID in my mySQL DB. I started out with DBF, but changed within weeks to mysql. Almost all of my tables use UUID for PK. The fields are set to varchar(40), as you also write. I dont' trim the dashes.
    I don't use triggers, since I create the uuid in AA using the api_uuidcreate() function. In my application, I sometimes need to know the uuid before I store the value, because I might be inserting in another table that is linked to the PK just created. I also have some xbasic functions writen to copy data in my tables, and here I also change the uuid in AA, because I again need to treat all the data linking to each other to make sure it's working correctly.
    Kind regards
    David H-S

    Using: Alpha Anywhere V12

  3. #3
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    1,785

    Default Re: UUID

    what was wrong with AI ? Perhaps you could consider using AI and filtering by the UUID, I read that using UUID can be 200 times more costly in terms of speed.
    http://www.percona.com/blog/2007/03/...r-not-to-uuid/

    I really do not know your situation so please just take my suggestion with a grain of salt only you know what will work best for your needs BUT keep in mind that some knuckle-head may have be to go behind you to figure out what the heck you were doing! I like the idea of NO triggers and keep it as simple as possible with as little duplication as possible.

  4. #4
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    Thank you both for responding.
    Charles, you brought up the point (and the same article) that made me decide to write this post. The reason why I want my PKs like this is to avoid issues resulting from multiple new records being created at the same time, but speed and efficiency is always a consideration. In the case of AI, the next record entered will have a PK of one more than the last record, but what happens if two records are submitted at the same time? That is what I am trying to avoid since I have a location table of addresses that are on the many side of a one-to-many with the contacts table that will get new records daily.
    Additionally, I have read that in order to apply an AI value to the record, a full conversation has to occur between the server and the client in order to issue the proper value, whereas UUIDs are random and uniquely generated on the client side. I would like input on this because I wouldn't be surprised if Alpha handles the AIs in a way that avoid this.

    David, when you use the api_uuidcreate() do you put that as default values in hidden text boxes linked to the PK? Have you noticed speed decreases in your DB since the change? I am glad I am not the only one who decided on varchar(40), but I am wondering if I shouldn't trim the dashed or change the field type to something else to make it smaller.
    Again, thank you to both of you. You represent both side of the argument in my brain ATM.

  5. #5
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: UUID

    it sounds like there will be some issues if multiple users try to submit new records to the same table at the same time.
    I don't think so. looks like you are using dbf ( alpha native tables) alpha shows a tentative number for the auto increment field ( many programs will simply say <auto>) there lies the confusion and worry.
    while this is tentative when saved alpha correctly saves first come first saved. the auto increment number is efficient in many ways and definitely if you go to web side ( I see many posts from you in the web side) and one day you might want to switch to sql, where auto increment fields are integer type by default.
    really speaking you don't have to show anyone the auto increment field. you can hide them, so only you will know not your users.
    recently there was some discussion in the desktop side regarding. this you might want to take a look. as long as numerical value is used for auto increment then there won't be a problem. should you use a character type, then a long time senior member has recommended giving a default start value as "000000001" like that, to avoid any errors in incrementing the value
    I use sql backend and integer type auto increment field for pk.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  6. #6
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    Thank you for your reply Govindan. I was already stongly encouraged to move away from DBF, so I am now using MySQL. Even my security tables are SQL.

    Yes, I have posted quite a bit in the last few weeks. Most likely they will all be considered noob questions, mainly because I am a noob. Before I changed to UUIDs, all of my PKs were auto-incremental integers.

    Do you mind posting a link to the post you were talking about?

  7. #7
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    1,785

    Default Re: UUID

    As far as I know if you are using AI in MySQL - AI cannot create a duplicate ID because it is impossible for MYSQL to process multiple record inserts and assign the same AI. They have to be done in order regardless of when they are received...
    If you look around every alleged case of duplicates involves a manually entry at one time, period. At least as far as I have found. I would not worry about two users or a hundred all using the same database even signed in with the same credentials! For that reason I would not hesitate to drop the UUID as a sole PK.
    I think you will find that you will not put your database through the kind of hell you imagine would crash one. If it does and your making money you will find it very easy to find folks smart enough to make your worries go away.

    I think the most common problem with databases are orphaned records due to bad design. If you can avoid that I think you will find the AI thing really isnt much of a concern.

  8. #8
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    Thank you again Charles. I was not sure what would happen in that case, but I did not think it would be duplicate records. I was more worries about a record not being saved. In the case of one of my tables, one record would take a user anywhere from 15 minutes to 2 hours to make, and I do not want to take the chance of that happening.

    Well I certainly want to get away from the unnecesary size of UUIDs, but before I do, could you tell me reasons why you think someone whould use UUIDs? I know that in a video uploaded to Youtube by Alpha Software, their sample app was made with UUIDs
    https://www.youtube.com/watch?v=BOxYJUoPl5c

    and the security tables that Alpha makes are even using UUIDs. I am not doubting what you say, but I am asking why someone would want to use them so that I can determine if I need them or not.

  9. #9
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: UUID

    a search via google integer vs guid as primary key gave lot of hits. one of them

    http://databases.aspfaq.com/database...imary-key.html
    hope this answers some of your questions.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  10. #10
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    Thanks for your reply Govindan. I thought you meant there was a conversation going on in these forums about it. I did not find it, but I am glad you sent me that link anyways. That definitely isn't the first product of a Google search that I have read regarding Int vs GUIDs, but I do like how it lists pros and cons. Thank you.

  11. #11
    Member
    Real Name
    David Hagen-Sørensen
    Join Date
    Jul 2013
    Location
    Denmark - Odense
    Posts
    66

    Default Re: UUID

    Quote Originally Posted by BCJack View Post
    David, when you use the api_uuidcreate() do you put that as default values in hidden text boxes linked to the PK? Have you noticed speed decreases in your DB since the change? I am glad I am not the only one who decided on varchar(40), but I am wondering if I shouldn't trim the dashed or change the field type to something else to make it smaller.
    Again, thank you to both of you. You represent both side of the argument in my brain ATM.
    Hi Jack,

    The reason for using UUID in my case is that I don't have to worry about replication between databases. I can do development on a test server, but then replicate it to the production server without worrying about same PK if used AI. Also have local hosted apps in the future where it can sync with online databases without worrying to much about PK overlap. I think this is the main reason for choosing UUID, as the others has posted UUID holds more data, so will use more data in the DB. It is also slower performing then AI.
    I have worked with some industrial SPC systems, were databases were spread in different datacenters, and here we always used UUID because of replication issues. Speed and performance were not a problem, but this was also on MS SQL servers.

    If I didn't expect to use several databases in my system, then I would stay with AI. As I also understand, AI is very easy to use in AA. But UUID takes some more programming to do in AA.

    If you choose to use UUID, in my case I usually hide the field that holds the PK(UUID). Then set the default value to =api_uuidcreate(). But also sometimes I calculate UUID in xbasic and pass it in before saving a record, or do it in onSynchronize events.
    Kind regards
    David H-S

    Using: Alpha Anywhere V12

  12. #12
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    Thank you very much David. That has helped me make up my mind. Due to the definite possibility of me testing during development and then applying those changes to the production server, I think it is best for me to stay with UUIDs, at least until I test less frequently or when speed becomes an issue. There is also the future possibility of splitting our database by regional servers, in which case the corporate users will need to have a client-side that retrieves data from multiple databases, so UUID may help there as well.

    Thank you again David.

  13. #13
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,775

    Default Re: UUID

    When I first converted to SQL I used mostly UUID's, but now the only place I still use them consistently is as the PK for my User List and as a "secondary encrypted record ID". Let me explain that second one:

    Say I have a table to hold my Products. I would use a normal autoincrement PK to link those records to other tables (better performance, more simple). But if I expected to need to refer to those Product records in a way that might expose the PK, like a parameter in a URL or a POST operation, I would instead us the "encrypted record ID". So the URL would look like this: MYPRODUCT.A5W?id=cc1e889bddc446ed8d0a984e1a704edf rather than this: MYPRODUCT.A5W?id=12345 (where 12345 is the autoincrement). The purpose for this is so some hacker does not "guess" the next autoincrement value and see something I might not want them to see. The "encrypted record ID" is just a CHAR(32) field set using remspecial(api_uuidcreate()) in the CanInsertRecord, CanUpdateRecord or AfterValidate for a UX.

    The reason I use a UUID for my User List is I match that UUID up to Alpha's Security Framework GUID for a given user. But this is becoming redundant because Alpha suggests we link our own User List to their Security Framework on UserID instead (most often an email address). But since you can't use an email address as a Primary Key/Foreign Key in any other instance*, I still have a UUID as my Primary Key for my User List.

    *Because a user's email address can change and that is forbidden as a criteria for a PK.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  14. #14
    Member
    Real Name
    David Hagen-Sørensen
    Join Date
    Jul 2013
    Location
    Denmark - Odense
    Posts
    66

    Default Re: UUID

    Hi Jack,

    Well, it sure is easy to migrate data with uuid.
    But I have also seen examples where AI and additional fields in the database can define the PK together.
    Etc. One field could be AI, and another could be a locationId with the default of some value. But then again this would require you to setup databases specific to the location.
    I like not having to use special database setups, but it comes with a cost in field size, a little performance, and some additional programming :-)
    Kind regards
    David H-S

    Using: Alpha Anywhere V12

  15. #15
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    Again, thank you to both of you. All these responses are giving me invaluable insight.
    I am guessing that the record ID would be exposed if referenced by a session variable within a page's code or something like that? It wouldn't be exposed if it was in a UX component would it?

    Thank you for sharing the remspecial() command. I was wondering about something like that.

    I am currently trying to figure out how to link the employees to the users and was thinking about what you just said.

    David, is what you are suggesting what is called a secondary key? Does the PK not still have to be unique even in that case?

  16. #16
    Member
    Real Name
    David Hagen-Sørensen
    Join Date
    Jul 2013
    Location
    Denmark - Odense
    Posts
    66

    Default Re: UUID

    In concerns to session, from my understanding they should be secure in AA no matter if they are used in a a5w page, UX or grid. A least this is how I try to keep my data safe. But you should be able to push your session data to the browser(JS) if you want.
    I'm not sure that arguments being passed around are safe, I think I've seen data in JS {dialog.object} that has argument values.

    Yes if the field is set to PK, now this system we had was in MS SQL. But here they handle the PK renaming in a Stored Procedure. Some data were using UUID, but some used AI. In the end we changed the AI default value to start from 10.000.000 from location1 and start from 20.000.000 from location2 and so on. We did this change after a year in production, and then we calculated that in about 10 years, we would run in to overlapping AI in the location. This should also solve some of the concerns with UUID in regards to space and performance.
    Kind regards
    David H-S

    Using: Alpha Anywhere V12

  17. #17
    Member BCJack's Avatar
    Real Name
    Jack
    Join Date
    Dec 2014
    Location
    OKC, OK
    Posts
    102

    Default Re: UUID

    I think in ten years I will forget anything that I did that long ago, so I am looking for a fire-and-forget structure, as much as is logically possible.

    I think I have a solution to all of this, but only if it is possible. An AI+UserPK. In other words, record 501 inputted by user 9 would be 501-9 as a PK for the record. Is something like that possible with MySQL and AA?

  18. #18
    Member
    Real Name
    David Hagen-Sørensen
    Join Date
    Jul 2013
    Location
    Denmark - Odense
    Posts
    66

    Default Re: UUID

    Hi Jack

    If you use AI, I don't think you are able to add a value before the record has been inserted. If you want to calculated your PK yourself, I think you need to define PK by maybe counting the rows in the table, and then doing your calculation but here you are not using AI function on the tables PK. But still you will to try and not make more then one record with same PK, or you will get a sql error.
    Kind regards
    David H-S

    Using: Alpha Anywhere V12

Similar Threads

  1. What is the data type: K-UUID ??
    By porjoe in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 09-05-2014, 06:54 PM
  2. Filter using uuid field
    By richardurban in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 12-15-2010, 03:07 AM
  3. Clearing UUID
    By nigelp in forum Application Server Version 10 - Web/Browser Applications
    Replies: 4
    Last Post: 11-25-2010, 10:45 AM
  4. UUID - Grid Event
    By eboughey1 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 9
    Last Post: 09-19-2009, 09:27 AM
  5. guid or uuid
    By Paul in forum Alpha Four Versions 7 and 8
    Replies: 0
    Last Post: 10-25-2002, 09:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •