Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
GUID for a primary key?
Message
From
01/08/2021 06:04:02
 
 
To
28/07/2021 23:38:25
John Ryan
Captain-Cooker Appreciation Society
Taumata Whakatangi ..., New Zealand
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
01681922
Message ID:
01681979
Views:
83
Reordered points:
>>Is there a reason why you prefer currency?

As it spans only 8 bytes, it allows more keys to fit in the bTree page leaf, making all index using steps faster.

Justin Case reminds us, if you doubt original vfp docs:
https://www.ibm.com/docs/pt-br/was-nd/8.5.5?topic=code-activex-java-primitive-data-type-conversion-values
As they have

  • BigDecimal
    another ISO-implementing Currency

  • plus homegrown types like

  • BCD in various flavors
    YodaMoney


  • Also in MS "Currency" a human readable "function" already built in, although IIRC you should verify MIN_VALUE and MAX_VALUE from backend can be display-handled in client
    REPLACE mon WITH NTOM(0)
    REPLACE mon WITH mon - NTOM(9000000000000)
    REPLACE mon WITH mon - NTOM(200000000000)
    REPLACE mon WITH mon - NTOM(20000000000)
    REPLACE mon WITH mon - NTOM(3000000000)
    REPLACE mon WITH mon - NTOM(300000000)
    *--- and so on on  on both sides
    - if only to pipe out in error handlers or similar cases, if you hide the key from typical users (I do, but often have "smartass" setting to display keys in forms as well for debugging purposes)

    >>Bigint allows values up to 2^63-1 (9,223,372,036,854,775,807)
    Yes and No. Bigint allows that many single variations. BUT: Currency "Denomination" is NOT $1, but "CentPercent",
    each next bit value having more 0.0001 more "worth". For simple addition and subtraction done in bookkeping with its fixed rules on how to round fractions occurring in real life (VAT calculation...) this is clearly more than enough. Vfp provides toe NtoM() and MtoN() functions in runtime.

    Trying for a dumb example: if you have rented a safe inside a bank vault. dimensions are part of the contract. You might even have been told you can fit in a million $ in standardized gold bar size. Now you come with a million $ worth, but in copper bars and moan that they don't fit in.

    If your room needs further heating (winter coming in your area...)
    set talk off
    set odometer to
    local lnStop, lnDone, lnStart, lnSecs
    = setup(10000000)
    lnStop = 900
    lnStop = 100000000 / reccount() * m.lnStop
    lnDone = lnStop*reccount()
    lnStart = seconds()
    for lnRun = 1 to m.lnStop
      = one_loop()
      if mod(m.lnRun, 10)=1
        ? datetime(), lnRun, loopcnt.cur_loop
      endif
    next
    lnSecs = seconds() - m.lnStart
    ? m.lnSecs, lnDone, "with ", reccount()
    browse last nowait
    select loopcnt
    browse last nowait
    
    function setup(tnRecs)
      create cursor loopcnt (autopk int autoinc, cur currency, cur_loop currency)
      create cursor chkcur (autopk int autoinc, cur currency, loop_add currency, cur_loop currency, st c(40))
      dimension p[m.tnRecs,1]
      p = ""
      append from array p fields st
      replace all cur with ntom((autopk-reccount())*0.0001)
      go bott
      replace cur_loop with ntom(0)
      return
    
    function one_loop()
      select chkcur
      go bottom
      scatter name oScat fields like cu*
      insert into loopcnt from name oScat
      local lCurAdd
      lCurAdd = ntom(reccount()*0.0001)
      select chkcur
      replace all ;
        cur with cur + 	m.lCurAdd ;
        loop_add with ntom(autopk*0.0001) + loopcnt.cur_loop ;
        in chkcur
      go bott
      replace cur_loop with cur_loop + m.lCurAdd
    >- What VFP field type, if any, actually works in a RV or CA for BIGINT PK > 2^31-1.
    You could also use blob or binary string because:
    >>>Again: As vfp NEVER does anyting but check if ==/!= and replace the value as received, why even think of FP or DP ?
    >>>It is a surrogate key. If you need something human readable, Currency is available or build Show() function translating into Hex...
    and all "mathmatics" is done server side, also with the benefit that anchor and increase

    SQL server does a better job "protecting" PK rules set in schema as vfps: " Value controlled by autoincrement Next and Step values" that are not protected in vfp...


    >>>Again: As vfp NEVER does anyting but check if ==/!= and replace the value as received, why even think of FP or DP ?
    >>>It is a surrogate key. If you need something human readable, Currency is available or build Show() function translating into Hex...
    >
    >Bigint allows values up to 2^63-1 (9,223,372,036,854,775,807)
    >
    >CREATE TABLE test (f1 Y, f2 B,f3 F(20))
    >APPEND BLANK
    >replace f1 with 9223372036854775807 &&Currency
    >*---Error: numeric overflow. Data was lost.
    >replace f2 with 9223372036854775807 &&double
    >*---no error; but field contains 9223372036854780000
    >replace f3 with 9223372036854775807 &&numeric(20)
    >*---no error; but field contains 9223372036854780000
    >
    >
    >Currency unusable; other field types also unusable because they drop digits.
    >Clearly VFP cannot handle full range for BIGINT data type.
    >
    >For this context: I agree there's a lot of deleting and inserting to get an Identity column from 2^31-1 to 2^63-1. So, trying again with 2^40-1:
    >
    >- and *any* of the field types is usable, with 1,099,511,627,775 approx 500X the INT upper limit.
    >
    >This means that for my purpose, VFP *can* handle BIGINT values for the forseeable future as Identity approaches 2B.
    >
    >If you've got the idea I need to see or munge the field- no, but if a row is edited with Identity PK>2^31-1, it needs to update the row correctly. So, what field type to use in the WHERE clause for a RV or CA? You advocate currency; I'd want to check
    >
    >- What VFP field type, if any, actually works in a RV or CA for BIGINT PK > 2^31-1.
    >- If more than one type works: I'd want to compare performance of currency versus double and even float that may need twice the space, but who cares if it's just a VFP placeholder representation for an update WHERE clause. Is it quicker for a high volume interface?
    >
    >Is there a reason why you prefer currency?
    Previous
    Next
    Reply
    Map
    View

    Click here to load this message in the networking platform