RM Database "auto-increment" Fields Revisited
I just had occasion to INSERT some rows into the PlaceTable in my RM7 database, going ahead to make some changes now that I want to make prior to the eventual final conversion from RM7 to RM8.
To INSERT a row, a value must be specified for every column, and as is typically the case for RM databases I used a NULL value for the PlaceID column. The INSERT properly auto-incremented an integer into the PlaceID column, and all was well.
But we recently had a discussion where it was confirmed that the RM's primary_key_ID values are defined as plain integers rather than as auto-increment columns. Under these circumstances, how can this INSERT possibly work? I suppose it doesn't matter how it works because it does work, but I'm very curious.
Also, for any table where I add rows in this manner, the newly added ID's seem to be one higher than the largest value the column has ever had rather then being one higher than the current largest value. This is another mystery that I don't understand. How does the database engine know what the next number is to assign since the column is integer rather than auto-increment?
Finally, when you add new rows to an RM table, it seems necessary immediately to run File > Database Tools > Rebuild Indexes. I don't mind doing so, but again the need to do so is a mystery to me. The PlaceID column and similar columns in all of RM's tables are indexed and that's why they need to be re-indexed after an INSERT. But my understanding of indexed columns in a relational database has always been that the indexes are maintained automatically by the database engine, transparent to any software that is using the database. And when RM itself adds a row, for example to add a new Place to the PlaceTable, there is no evidence that it has to do a complete re-index of the PlaceTable just to accommodate one new row. So it makes me wonder what my SQLite script is doing wrong.
Jerry
I just realized that things like like the PlaceID column and similar columns in other tables such as PersonID in the PersonTable and NameID in the NameTable are not indexed. That's not what indexes are about in a relational database. Rather it's other columns that are sometimes indexed, such as the Abbrev column and the Name column in the PlaceTable. But that doesn't change the main question how to do an INSERT so that a subsequent re-index is not required.
Jerry
If you have a fake collation sequence that is not exactly the same as RMNOCASE, RM may detect issues requiring it to reindex using the real RMNOCASE. Using NOCASE as the fake or as an override produces more indexing errors than UNIFUZZ.DLL as a fake does, indicating that the latter is closer to being equal to RMNOCASE but not quite there.
Tom
You explanation makes really good sense. Much thanks.
I frequently wish that RMNOCASE would just go away. One of the first things I checked after installing RM8 was whether RMNOCASE is still there or not. It is.
By the way, the SQLite project I am working on is to merge Place Details back into the main Place field. I have this fear that you have already created this query previously, but I'm already deep into doing it myself. I was doing it manually in the RM7 user interface, a little at a time, but I quickly came to realize that I would never get it done using the RM8 user interface, so I decided to hurry the project along with SQLite. And I was going to develop the script for RM8 until I realized I would have to create a Reverse column for place names in reversed order, and I didn't want to deal with that problem in SQLite. So I'm doing it RM7 and I will let RM8's import process create the Reverse column for me.
This little query is a little trickier that it first seemed. New rows have to be inserted into PlaceTable to contain the concatenated Place Details + Place. Rows in EventTable that point to Place Details have to be repointed to the new rows in PlaceTable. And finally, the Place Details rows have to be deleted from PlaceTable. This last step is trivial. The first two steps are the tricky ones.
Jerry
I'm sure Tom and several others already know this, but the following appears to be the answer to how RM's "auto-increment" works in the primary key column for each table, even if none of RM's primary keys are defined as auto-increment. If an INTEGER column is also a PRIMARY KEY then the column becomes a "rowid" column which in effect is an auto-increment column.
The remaining mystery is that the documentation speaks of the "largest key already in the table" and I suspect it must really mean the "largest key that has ever been in the table". That's because inserting a new row at the end of the table, immediately deleting the new row, and adding it again leaves a missing number in the primary key column.
2.0 The INTEGER PRIMARY KEY
One exception to the typelessness of SQLite is a column whose type is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT". A column of type INT PRIMARY KEY is typeless just like any other.) INTEGER PRIMARY KEY columns must contain a 64-bit signed integer. Any attempt to insert non-integer data will result in an error.
INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY KEY column, the column will actually be filled with an integer that is one greater than the largest key already in the table. Or if the largest key is 2147483647, then the column will be filled with a random integer. Either way, the INTEGER PRIMARY KEY column will be assigned a unique integer. You can retrieve this integer using the sqlite_last_insert_rowid() API function or using the last_insert_rowid() SQL function in a subsequent SELECT statement.
Jerry