Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

UUID

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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

    Comment


    • #3
      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.
      NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment


          • #6
            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?

            Comment


            • #7
              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.
              NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: UUID

                      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

                      Comment


                      • #12
                        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.

                        Comment


                        • #13
                          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)

                          Comment


                          • #14
                            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

                            Comment


                            • #15
                              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?

                              Comment

                              Working...
                              X