Auto Sequence Table in SQLite

  1. 4 months ago

    Dave S

    Nov 9 San Diego, California USA

    Is there a way to create a Sequence Table.... one that contains a single value, the is incremented every time it is read..

    I want to apply a sequential number to each record that I insert.... but it will be across a dozen tables.... so the value must be "global"

    • Insert a record into table "A"... seq is 1
    • Insert a record into table "B" ... seq is 2
    • Insert another into "A" .. seq is 3
    • etc.

    So I guess at this point my solution is a table with a single integer value
    and a function that reads the current value, updates the record

    SQL="SELECT nextVal from mySEQ"
    nextVal=rs.field("nextVal").integervalue
    SQL="UPDATE mySEQ Set nextVal="+str(nextVal+1)
    return nextVal

    PseudoCode of course.

  2. Karen A

    Nov 9 Pre-Release Testers

    I am not an SQLite expert to say the least and there may be an easier way...

    But I suspect it could be done with triggers on each table that needs to share the sequence and keep the sequence counter is some table...

    The trigger could read, increment and save the new sequence value

    - karen

  3. Bob K

    Nov 9 Pre-Release Testers, Xojo Pro Kansas City

    That's extremely odd way of doing things. Is there something you're trying to accomplish?

  4. Dave S

    Nov 9 San Diego, California USA

    Odd? Major Database engine (Oracle for example) have "CREATE SEQUENCE" for just this purpose

    The idea is that each of those dozen tables has a specific purpose (they each describe a different type of data)...
    and some of those tables hold data where each record relates to a different table

    For example Table D.... one record might relate to a record in Table A, while another relates to a record in table B
    there for the "key" cannot be an AutoIncrement PK in A or B since that would overlap.... so the PK values in A and B must be unique across both tables, so "Where A.key = D.key" never conflicts with "Where B.key = D.key"

    An example.... I used to work in Healthcare.... and we had (for sake of discussion) one table that was HOSPITALS, another that was DOCTORS and yet another that was CLINICS... each of these had a unique record structure..... but they all had some things in common... so those were in single tables regardless of provider type. That is a super simplified example (in reality it was much more involved). My project consists of hundreds of records... not millions

  5. Jean-Yves P

    Nov 9 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    I would use a master table containing all the common records of the 12 tables, and the key in autoincrement PK
    then related links to the 12 tables with only specifics fields in them.

  6. Dave S

    Nov 9 San Diego, California USA
    Edited 4 months ago

    @Jean-YvesPochez I would use a master table containing all the common records of the 12 tables, and the key in autoincrement PK
    then related links to the 12 tables with only specifics fields in them.

    The relational data structure is as clean as it can be already.... The main purpose of this value is to maintain a grandparent-parent-child structure between them..

    Think of an Xojo program .

    • Windows relate to Controls which have Attributes
    • WIndows also have Events
    • Windows also have Constants, Variables, Methods and Notes
    • but then you have Modules
    • Modules have the same Constants, Varaibles, Methods and Notes

    You can have a dozen "WIndows" and a dozen "Modules" all with a dozen Methods..... but the structure of a Window is too far removed from a "Module" to keep them in the same table.... but a "Module" isn't as long as you keep track of its "parent"

    so a "Window" record could say... ."SELECT * FROM MODULES WHERE PARENT=myID"

    Best example I could think of, and hope I explained it to show what effect I am going for

  7. Jason P

    Nov 9 Xojo Inc Texas

    @Dave S Is there a way to create a Sequence Table.... one that contains a single value, the is incremented every time it is read..

    I want to apply a sequential number to each record that I insert.... but it will be across a dozen tables.... so the value must be "global"

    • Insert a record into table "A"... seq is 1
    • Insert a record into table "B" ... seq is 2
    • Insert another into "A" .. seq is 3
    • etc.

    No but you could create one. The issue then is that this relationship is by convention from the API's you use to read & write data and not enforced by declaration in the DB model. But it's the only mechanism that exists AFAIK.

  8. Dave S

    Nov 9 Answer San Diego, California USA
    Edited 4 months ago

    So I guess at this point my solution is a table with a single integer value
    and a function that reads the current value, updates the record

    SQL="SELECT nextVal from mySEQ"
    nextVal=rs.field("nextVal").integervalue
    SQL="UPDATE mySEQ Set nextVal="+str(nextVal+1)
    return nextVal

    PseudoCode of course.

  9. Paul L

    Nov 9 Xojo Inc http://docs.xojo.com

    Maybe you could have a simple SQLite table that uses AutoIncrement, INSERT a row and then use SQLiteDatabase.LastRowID to get the value for use in your other tables. Could be faster than doing both a SELECT and INSERT.

  10. Jason P

    Nov 9 Xojo Inc Texas

    @Dave S So I guess at this point my solution is a table with a single integer value
    and a function that reads the current value, updates the record

    SQL="SELECT nextVal from mySEQ"
    nextVal=rs.field("nextVal").integervalue
    SQL="UPDATE mySEQ Set nextVal="+str(nextVal+1)
    return nextVal
    PseudoCode of course.

    Maybe this:

    begin transaction
    SQL="UPDATE mySEQ Set nextVal=nextVal+1"
    SQL="SELECT nextVal from mySEQ"
    nextVal=rs.field("nextVal").integervalue
    commit transaction
    return nextVal

    and this way it should be possible to use in multiple simulataneous db connections and never get overlapping results (ie from multiple threads in a xojo program) or from multiple instances of the same progrram. It looks odd but you want the transaction to block *all* other accesses while you grab the next value.

  11. Dave S

    Nov 9 San Diego, California USA

    Hmmm..... good idea...
    I was going to go with what I had mentioned... but instead of making a new table, just add a dummy row to SQLITE_SEQUENCE
    but your idea might be "safer"

    only thing.... doing a INSERT row makes that table keep growing for no valid reason

  12. Paul L

    Nov 9 Xojo Inc http://docs.xojo.com

    Yeah, the growing table might become a bigger problem as the row count gets large. Even if you remove rows from it, you'd have to Truncate the DB to reduce its space and that can be expensive.

  13. James D

    Nov 9 Pre-Release Testers Europe (Switzerland)

    @Dave S Odd? Major Database engine (Oracle for example) have "CREATE SEQUENCE" for just this purpose

    The idea is that each of those dozen tables has a specific purpose (they each describe a different type of data)...
    and some of those tables hold data where each record relates to a different table

    For example Table D.... one record might relate to a record in Table A, while another relates to a record in table B
    there for the "key" cannot be an AutoIncrement PK in A or B since that would overlap.... so the PK values in A and B must be unique across both tables, so "Where A.key = D.key" never conflicts with "Where B.key = D.key"

    An example.... I used to work in Healthcare.... and we had (for sake of discussion) one table that was HOSPITALS, another that was DOCTORS and yet another that was CLINICS... each of these had a unique record structure..... but they all had some things in common... so those were in single tables regardless of provider type. That is a super simplified example (in reality it was much more involved). My project consists of hundreds of records... not millions

    Both of your examples can be solved using composite keys. This is a very common requirement in asset management where you have commonality between various types of financial instruments and even subtypes.

or Sign Up to reply!