QSqlQuery & SQLite rowid Question
-
Is it possible to access the default rowid primary key column with QSqlQuery? Or do I have to use a manually set up auto-increment id column?
Thanks!@Crag_Hack said in QSqlQuery & SQLite rowid Question:
Will the rowid column default to being column 0? Can I access it that way? Or via the default name rowid through some other way?
The column numbers depend on your SELECT query.
- If you call
SELECT rowid, timestamp FROM table;then rowid is column 0 and timestamp is column 1 - If you call
SELECT timestamp, rowid FROM table;then rowid is column 1 and timestamp is column 0
Or do I have to use a manually set up auto-increment id column?
Note that AUTOINCREMENT is a special property that prevents deleted IDs from being re-used by a later update. By default, ROWID does not have the AUTOINCREMENT property.
If you want an auto-incrementing ID, set up an INTEGER PRIMARY KEY AUTOINCREMENT column manually. It then becomes your ROWID.
See https://www.sqlite.org/autoinc.html
Is it possible to access the default rowid primary key column with QSqlQuery?
Read through the question, answers and comments at https://stackoverflow.com/questions/8246649/why-cant-you-use-sqlite-rowid-as-a-primary-key
- If you call
-
SQLite docu says
You can access the ROWID of an SQLite table using one of the special column names ROWID, ROWID, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.
-
But the QtSqlQuery doc says:
For the sake of efficiency, there are no functions to access a field by name (unless you use prepared queries with names, as explained below). To convert a field name into an index, use record().indexOf(), for example
Will the rowid column default to being column 0? Can I access it that way? Or via the default name rowid through some other way?
-
But the QtSqlQuery doc says:
For the sake of efficiency, there are no functions to access a field by name (unless you use prepared queries with names, as explained below). To convert a field name into an index, use record().indexOf(), for example
Will the rowid column default to being column 0? Can I access it that way? Or via the default name rowid through some other way?
@Crag_Hack
The field names of a record are determined by your select statement.
You need to select rowid to make it in the result, by doing that you should already know its index. -
Is it possible to access the default rowid primary key column with QSqlQuery? Or do I have to use a manually set up auto-increment id column?
Thanks!@Crag_Hack said in QSqlQuery & SQLite rowid Question:
Will the rowid column default to being column 0? Can I access it that way? Or via the default name rowid through some other way?
The column numbers depend on your SELECT query.
- If you call
SELECT rowid, timestamp FROM table;then rowid is column 0 and timestamp is column 1 - If you call
SELECT timestamp, rowid FROM table;then rowid is column 1 and timestamp is column 0
Or do I have to use a manually set up auto-increment id column?
Note that AUTOINCREMENT is a special property that prevents deleted IDs from being re-used by a later update. By default, ROWID does not have the AUTOINCREMENT property.
If you want an auto-incrementing ID, set up an INTEGER PRIMARY KEY AUTOINCREMENT column manually. It then becomes your ROWID.
See https://www.sqlite.org/autoinc.html
Is it possible to access the default rowid primary key column with QSqlQuery?
Read through the question, answers and comments at https://stackoverflow.com/questions/8246649/why-cant-you-use-sqlite-rowid-as-a-primary-key
- If you call
-
Can you still reduce a database SELECT by rowid to a binary search without autoincrement? If you delete some rows in the middle does the lack of an autoincrement column mean that some rowids could be reused and the rowid column is no longer increasing and therefore SQLite cannot do a binary search?
-
Rowid can be considered increasing. There are only very rare situation where it is not.
Docu says:If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.
-
Can you still reduce a database SELECT by rowid to a binary search without autoincrement? If you delete some rows in the middle does the lack of an autoincrement column mean that some rowids could be reused and the rowid column is no longer increasing and therefore SQLite cannot do a binary search?
@Crag_Hack said in QSqlQuery & SQLite rowid Question:
Can you still reduce a database SELECT by rowid to a binary search without autoincrement? If you delete some rows in the middle does the lack of an autoincrement column mean that some rowids could be reused and the rowid column is no longer increasing and therefore SQLite cannot do a binary search?
I don't know the details of the inner workings of SQLite, but I believe it uses binary tree search all the time, regardless of whether AUTOINCREMENT is involved or not.
At this point, I suggest you go ahead and write a simple implementation that works. Don't worry about performance for now... even if you accidentally write suboptimal code it will probably still perform far better than the QDataStream approach!
