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”
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
That’s extremely odd way of doing things. Is there something you’re trying to accomplish?
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
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.
[quote=413546:@Jean-Yves Pochez]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.[/quote]
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 .
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
[quote=413537:@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”
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.
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 nextValPseudoCode of course.
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.
[quote=413551:@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.[/quote]
Maybe this:
begin transaction
SQL="UPDATE mySEQ Set nextVal=nextVal+1"
SQL="SELECT nextVal from mySEQ"
nextVal=rs.field("nextVal").integervalue
commit transaction
return nextValand 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.
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
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.
[quote=413545:@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[/quote]
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.