You can do this with triggers and ignore the RowID entirely. So what you want is to be able to define a column that "acts like" it were the INTEGER PRIMARY KEY, or RowID, but is not. In a RowID table any column that is a PRIMARY KEY but not an INTEGER PRIMARY KEY (of which there can only be one, since it is the RowID) is merely a UNIQUE constraint and it is not constrained NOT NULL unless you specify that constraint.Īha! I understand your conundrum now after looking at the sessions module more closely. You can have the RowID be a "named column" (after which it behaves like a named column with a meaningful value that can be used in FOREIGN KEY constraints and is preserved when the table is vacuumed and is returned by SELECT * just like other non-magical/non-hidden/non-invisible columns). If you attempt to store a NULL value in that column it will be autoassigned the next available integer value. INTEGER PRIMARY KEY (the rowid) is also never NULL. The actual primary key of a RowID table is the RowID which may be either magical (not named and therefore cannot be used in FOREIGN KEY constraints and subject to "not being preserved" if you VACUUM the table or dump/load or "invisible" if you SELECT * from the table). PRIMARY KEY is just an alternate spelling for UNIQUE EXCEPT that in a RowID table the specific declaration INTEGER PRIMARY KEY spelled EXACTLY like that means that you have "named" the RowID column and it is now a "named column" and behaves like any other named column EXCEPT that it is the automatically assigned RowID (cannot be null). You MUST have your ROWID made explicit as an INTEGER PRIMARY KEY so a vacuum won't destroy numbering, but since the whole purpose of this was for FOREIGN KEYS that is basically assumed. Now, you should have the case that ROWIDs and the text key are consistent, so tracking changes in one will track changes in the other. Then do a join to find records with the same text key but different ROWID, and renumber the ROWID in your local database to match. Assuming the changeset set comes in as an SQLite database, first, do a join to find records with the same ROWID but different text keys, and renumber those rows to some value unused in either the local database or the changeset. If this is the case, my concept of a prefilter would be then when you bring the changeset in, you first fix your database to correct for ROWID issues with your text key. I am not an expert in the session extension either (I haven't used it at all), and I am a bit surprised that it can't handle the case of the unique identification of a data row being a unique text field instead of the somewhat arbitrary ROWID primary key. TEXT, PRIMARY KEY(translatableId, language)) TranslatableId INTEGER NOT NULL REFERENCES translatables(id) ON DELETEĬASCADE, language TEXT NOT NULL CHECK (language != ''), translation I am not an expert in Sqlite, so maybe somebody knows a better way.ĬREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT)ĬREATE TABLE IF NOT EXISTS translations(id INTEGER NOT NULL UNIQUE, Like you see in this examples I have an extra unique index which could be an alias to ROWID but this is impossible because I use already an other
So I think what I really need is an alias of ROWID which is not a PRIMARY KEY.
Then I cannot use ROWID for it because it is unstable and does not work together with the FOREIGN KEYS. Third I need an integer id for efficiency in the child table because the PRIMARY KEY in the parent table is large. Second I need the PRIMARY KEY of a text field for the sessions.
Sqlite rowid update#
First I need the ROWID for the update hook.
0 kommentar(er)
