UUID

[davidhs]

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.

[BCJack]

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?

[davidhs]

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.

[BCJack]

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?

[davidhs]

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 :-)

[Steve Wood]

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.

[BCJack]

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.

[davidhs]

Re: UUID

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.

[BCJack]

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.

[GGandhi]

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.

[BCJack]

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.

[CharlesParker]

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.

[BCJack]

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?

[GGandhi]

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.

[BCJack]

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.