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

unique key - discussion

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

    unique key - discussion

    I know Unique Keys are as basic as the Table itself, but I wanted to review my choice of setting a unique key in light of the new UUID field type. I wanted to see what you think about the four methods I describe, and get your suggestions on the best approach.

    AUTOINCREMENT
    The most common method to create a unique id is the autoincrement field rule. But I refuse to use this method for one good reason -- someone could delete the 'current highest value' making the next entry a duplicate of a previous (deleted) record. That is, 1001 may be assigned to Steve Wood, and then later on 1001 could be re-assigned to John Smith because Steve's record was deleted.

    But somewhere, maybe a sales transaction table, the value 1001 may have been assigned to a sales record. It USED to be attributed to Steve Wood, but now erroneously attributed to John Smith. The sales record is really an orphaned record, but you don't know it because a new parent record has been created. Yes, this could be avoided by perfect administration of records. But that is not always possible and accidents happen.

    UUID
    This is what I converted to recently. It works great. There is no chance that a new record will have a UUID that ever existed before. Alpha recommends it as a good candidate for a unique key. An 'orphaned' record, like in the above example is truly orphaned and you can deal with it appropriately. The only problem I have is the field is really, really BIG (32 characters). It's overkill since I only need a value that is unique for the table, not one unique in the galaxy. I wish there was a UUID_LITE() that was only unique for the table.

    In the case of something like an Invoice ID, its way too long. Could not expect someone to recite that value when calling in about their invoice. So, in that case you end up needing the UUID and still a shorter autoincrement for practical use.

    HEX(TIME)+RAND())
    Some variation of time + random number, perhaps converted to hex. For example:

    ?dec_to_hex(val(time("MMddyyyy0h0m0s3")))+floor(rand()*10000)

    = "239AF6A026D9887070"

    Shorter, still unique, partly seqential. A little easier to guess since the first 11 or so will be the same when generated during the same minute.

    Lastly, SSN, TelephoneNumber, subset of Client Name, etc.
    I won't even consider these examples for unique ids for internal use in linking parent and child.

    What do you think?
    Steve Wood
    See my profile on IADN


    #2
    Re: unique key - discussion

    I agree with all your issues. Although it wasn't for the same reasons, I recently added a "unique id" to a web app. It's really just the date and time and, in this case, I also added the first two characters of the user's last name.

    I don't see the advantage of changing the date/time with the hex() command unless there is some reason for making it less readable.

    In my case, the actual reason for this was because I wanted a record of when the order was actually placed. Since I was doing that anyway, I decided to use it as a unique identifier also. Just in case it would be possible for two users to enter a record simultaneously, I added the two characters from the last name.

    I don't really think it's possible for two users to enter a record at the same time in a web app but I'm not sure if the smallest difference would be in the 10ths of seconds or, maybe(?), under a millisecond. So, I figured adding the first two characters of the last name would make the chances of getting the same "number" almost infinitesimally small.

    FWIW, the code below is more or less what I'm using - without the two characters from the name added. The examples are from the interactive window.

    The first one is the most readable and it's what I actually use because some users actually refer to it in order to determine when the order was actually placed. (Typically when their customer says, "But I placed the order before 5:00 so it should be done the next day." Then my customer can check the actual time and can tell their customer, "No, you placed the order at 5:22 and 59.4 seconds and we close at 5:00 so your order was actually received the next business day and will be completed the day after that." When you give them the time to the nearest 1/10th of a second they usually don't argue anymore.)
    Code:
    server_time = time("0h:0m:0s.1",now())
    tm_string = cdate(date())+server_time
    ?tm_string
    = "2008100419:22:59.4"
     
    'If I want it shorter:
    ?remspecial(tm_string)
    = "200810041922594"
     
    'If I want it shorter still:
    tm_string = remspecial(right(cdate(date()),6)+server_time)
    ?tm_string
    = "0810041922594"

    Comment


      #3
      Re: unique key - discussion

      Hi Cal,

      A more direct expression is

      ?time("yyyyMMdd0h0m0s3",now())
      = "20081004233035203"

      It is also possible to encode some portions as Alpha Numeric, e.g Months 01 to 12 being A to L.

      Hi Steve,

      The Random number generator is kind of weak in A5, repeating with the same sequence each time. There may be other ways to get totally unique numbers.

      As for auto-increment, you could keep a maximum number in a table so that a smaller number can never be reused.
      Regards,

      Ira J. Perlow
      Computer Systems Design


      CSDA A5 Products
      New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
      CSDA Barcode Functions

      CSDA Code Utility
      CSDA Screen Capture


      Comment


        #4
        Re: unique key - discussion

        Unlike others, I have never had a problem with autoincrement producing the right result. However, that does not address the issue of it being the appropriate id to use. (Although I do frequently use them.)

        In a large current app I am using UUIDs internally for linking, but users get a more user friendly id that is in sequence. This gives an indication of volume to a manager that looks at the system through the day. This id uses a custom version of increment_value() and ranges from 6-8 characters.

        BTW, on a local list serve for SQL Server DBAs a recent thread discussed UUIDs as clustered indexes. The overwhelming conclusion is that they cripple performance and should never be used as a clustered index with MS SQL Server.

        Bill.

        Comment


          #5
          Re: unique key - discussion

          I first turned to UUID's because they were so handy and non-guessable for internal links between pages and for use in emails where an 'encrypted' link was required. It became easy to include a link such as: regconfirm.a5w?id=eecfde40be194db789bf568029a2e9a0 rather than having to fabricate such a link using real encryption functions or obfuscation at runtime, then decrypting when it came back. You cannot use a simple autosync value for that because the user will invariabley guess the next number.

          On the web, you cannot use encryption at runtime in a Grid calculated field, which made it more difficult.

          Bill, I'm doing now what you are, using UUID where necessary and adding a "friendly ID" also where necessary. I can't remember what company I was talking to, but my "tracking id" was some 27 digits long. I remember saying "got it" to the agent, even though I lost track around digit 17.
          Steve Wood
          See my profile on IADN

          Comment


            #6
            Re: unique key - discussion

            I'm trying to get a GUID into a UUID field via a Calculated Field and not have it recalc after the insert. Here's the calculation..

            IF(ISNULL(Acctid),API_UUIDCREATE(),Acctid)

            But I get an error message that "Expression doesn't evaluate to a K value"

            Any help appreciated.. is there a better way to initialize this type of field?

            Comment


              #7
              Re: unique key - discussion

              I always use:

              IF(ID="",REMSPECIAL(API_UUIDCREATE()),ID)

              on every table with hardly any variation. The PK is always named ID and that makes programming more simple.
              Steve Wood
              See my profile on IADN

              Comment


                #8
                Re: unique key - discussion

                I also have never had a problem with auto-increment fields. One issue with UUID, which is not an issue with Alpha Five tables but would be an issue with SQL tables, is that sometimes you, as a developer, need to examine the last record, or last 5 or 10 records, entered. With dbf tables that's easy because they're in sequential order in the file. With SQL tables you can do it if the tables have autoincrement fields, but not if they have UUID fields -- unless you know each UUID!
                Also, UUID fields will result in much larger indexes. Not just because the fields are so large, but also because the numbers are scattered all over the B-tree.

                Comment


                  #9
                  Re: unique key - discussion

                  Thanks Steve for the quick reply.. I presume you're stuffing the result into a character field of length?.. curious as to why you don't get rid of the 'remspecial' and stuff it into a UUID field? Does it cause imcompatibilities with different DBs?

                  Comment


                    #10
                    Re: unique key - discussion

                    I wish there were a 'short, sequential, UUID', like 10 characters. Like a Table-Unique value (I don't care that the value is unique across the universe).

                    The full UUID is overkill but so convenient. I don't like auto increment at all for the same reason I would not like Primary Key fields that mean something, like WOOD123; auto increment values have significance, they are in sequence. I don't like them because if I assign 10001 to employee Steve Wood, and if that employee quits, you can never delete that record because 10001 would be 'missing'. If you were to delete it, the next employee added would get ID=10001 and potentially be assigned all of Steve's orphaned records.

                    I admit I have used a time() value that was around 17 characters and had sequence.
                    Steve Wood
                    See my profile on IADN

                    Comment


                      #11
                      Re: unique key - discussion

                      Originally posted by Steve Wood View Post
                      ...auto increment values have significance, they are in sequence. I don't like them because if I assign 10001 to employee Steve Wood, and if that employee quits, you can never delete that record because 10001 would be 'missing'. If you were to delete it, the next employee added would get ID=10001 and potentially be assigned all of Steve's orphaned records.

                      ....
                      As Ira alluded to, with auto increment you can use a table to store the last number used, along with a function for auto-incrementing. I have done this with an app dating back to at least 2003, making use of a function I modified (the original came from Cal and Ira, I believe, and is probably on the Code Archive somewhere). In all these years and records there has never been an orphaned record (at least that I am aware of!).

                      Raymond Lyons

                      Comment


                        #12
                        Re: unique key - discussion

                        Originally posted by Raymond Lyons View Post
                        As Ira alluded to, with auto increment you can use a table to store the last number used, along with a function for auto-incrementing. I have done this with an app dating back to at least 2003, making use of a function I modified (the original came from Cal and Ira, I believe, and is probably on the Code Archive somewhere). In all these years and records there has never been an orphaned record (at least that I am aware of!).

                        Raymond Lyons
                        I searched the Code Archive and elsewhere but cannot find the function and method I referred to in the above. Not sure Cal still uses it or whether anyone these days would be interested, but here it is anyway. It (or an improved version) could go into the Code Archive if Cal and Ira don't object. Or maybe the entire scheme is now obsolete?

                        Raymond Lyons

                        Code:
                        FUNCTION AUTO_incr_n AS N (Next_n_tblname AS C, autoincr_fldname as C, autoincr_fld_dflt_val AS N)
                        'Description:Use field value in one record table to auto increment the value of an ID field (autoincr_fldname) for a new record.
                        
                        'Note: This entire method is something that was originated by Cal Loklin and, I think, Ira Perlow way back in the
                        'the v4 days. I (Ray Lyons) made few if any changes, possibly just to make it numeric for my purposes and change the messages.
                        'In this case the incremented value is numeric but could be easily changed to character.
                        'Need to have a 1 record, 1 field table. For example, table ="auto_incr_no" with a numeric field 
                        'named "Next_ID" with a length of, say, 10. Set a starting value of 0, 1 or whatever.
                        'For the table needing an auto-incremented ID field (e.g., "My_ID_fld"), we need a field rule.
                        'Data Entry: Simple default expression, default value = 0 and set default value at start of data entry.
                        'Set Skip =.T.
                        'Validations: Value of field must be unique.
                        'In this example, Events, Record Events, Event: CanSaveRecord = auto_incr_n("a_incr_no","MY_ID_fld",0)
                        
                        
                        'Get a pointer to the field in this local table.
                        tbl = table.current()
                        fld = tbl.field_get(autoincr_fldname)
                        
                        Restart_here:
                        
                        'Increment the field value.
                        IF fld.value_get() = autoincr_fld_dflt_val
                            'The incr_fld_value() function returns the current Character value in "Next_n_tblname" and 
                            'increments it by 1.  If it is not able to open the "Next_n_tblname" it returns the original
                            '"autoincr_fld_dflt_val" so it can be checked below.
                            newval = incr_fld_value( Next_n_tblname, autoincr_fld_dflt_val )
                            fld.value_put(newval)
                        END IF
                        
                        'Not really necessary. Just allows the function to return something meaningful.
                        Auto_incr_n = fld.value_get()
                        
                        'If increment didn't succeed, allow trying again or cancelling.
                        'This allows for the possibility that someone is working directly on the table or it has 
                        'been left 'permanently' open for some reason such as another computer crashing at the 
                        'wrong time.
                        IF fld.value_get() = autoincr_fld_dflt_val
                            msg = "The ID number cannot be incemented because the file used to "
                            msg = msg + "increment it ('"+Next_n_tblname+"') is in use. "
                            msg = msg + "Someone else could be using the table or it could be locked due to "
                            msg = msg + "a previous system crash somewhere on the network." +chr(13)+chr(13)
                            msg = msg + "If nobody else is using the file, it may be necessary for everyone to "
                            msg = msg + "shut down and re-boot. In extreme cases, it may even be necessary to "
                            msg = msg + "re-boot the server." +chr(13)+chr(13)+ "To try again up to 3 times, click 'OK'.  To "
                            msg = msg + "quit and lose this record, click 'Cancel'."
                            resp = ui_msg_box( "*** ERROR - Record NOT saved ***", msg, 16+ui_ok_cancel )
                            IF resp = ui_ok_selected
                                GOTO Restart_here
                            END IF
                            'This seems to work even *after* the message box. It didn't in version 4.
                            cancel()
                            END
                        END IF
                        
                        END FUNCTION
                        
                        FUNCTION Incr_fld_value AS N ( tablename AS C, init_val as N )
                        'Description:Returns current value in field 1 of 'tablename' and increments it by one.  Used to implement xbasic autoincrement in CanSave Record event.
                            DIM curval as N
                            
                            'If exclusive access isn't obtained, this returns the initial value so 
                            'the field value won't be changed. Add'l checks will be run in the main function above.
                            Incr_fld_value = init_val
                            
                            error_loops = 0
                            ON ERROR GOTO Not_exclusive
                            tp = table.open(tablename,file_rw_exclusive)
                        '    ON ERROR GOTO 0
                            tp.fetch_goto(1)
                            fld = tp.field_get(1)    'Get a pointer to the first field in the record.
                            curval = fld.value_get()
                            newval = increment_value(curval)
                            'Store the next value in the increment table.
                            tp.change_begin()
                                fld.value_put(newval)
                            tp.change_end()
                            tp.close()
                            Incr_fld_value = curval
                            
                            EXIT FUNCTION
                            
                        Not_exclusive:
                            'Give it 3 tries in case someone else is entering a record at exactly the same time.
                            ui_msg_box("Attempting to Save", "On this attempt (" + ltrim(str(error_loops+1))+") there was a problem that may clear up momentarily. Please click OK to try again up to 3 more times.")
                                IF error_loops < 2
                                error_loops = error_loops + 1
                                RESUME 0
                            END IF
                        END FUNCTION

                        Comment


                          #13
                          Re: unique key - discussion

                          I have no objections.

                          Comment


                            #14
                            Re: unique key - discussion

                            FYI, I have posted my version of Cal's function and auto increment method in the Code Archive. It includes the function and a sample database showing how it is used.

                            Raymond Lyons

                            Comment


                              #15
                              Re: unique key - discussion

                              Just my 2 cents

                              You want to prevent orphans to get unmeaned / unwanted new parents.

                              Why not preventing the deletion of the last inserted record?

                              If there is a request for deletion of that last inserted record (ID = max(ID))
                              a) Save the request (in a seperate file?) to handle it later (routine?).
                              b) Toggle the logical-'active'-field to .F. so it jumps out of the indexcondition (=.T.) and it will not show up.
                              c) A combination?

                              I work with a logical field 'active' and do not delete records. It also gives me a good statistical review what happens in a period.



                              Regards

                              Ton
                              Most things are simple but unfortunately only after the first time

                              Comment

                              Working...
                              X