I’m currently updating an application which only supports MariaDB as a database in order to also support other database types, such as SQLite. This involves going through each MariaDB method and translating the queries to work with SQLite. I’m not really able to change the structure of the tables within the database too much, since the rest of the code-base is already largely dependent on the specific layout in place.
The database holds information about various different GUIs. The specific tables I’m having problems with both support pagination. Each element within the these tables stores a GuiID, a page number, and a location within the page, which together act as a composite key.
| gui_id | page | position | contents |
|---|---|---|---|
| 1 | 0 | 2 | Hello! |
| 1 | 1 | 0 | Text |
| 1 | 2 | 1 | More Text |
| 1 | 3 | 1 | More More Text |
The above is a simplified example of what this could look like for one specific gui. There can also be multiple items per page.
The issue arises when trying to implement the insertPage() method and deletePage() method. Here is what the MariaDB implementation looks like for the insert page method:
UPDATE contents SET page = page + 1 WHERE gui_id=? AND page >= ? ORDER BY page DESC
The reverse is also true for the delete page method.
Here, the query needs to use the ORDER BY clause to ensure that the page number is updated from highest to lowest rather than lowest to highest to ensure that there’s never a duplicate key error. If it were to increment page numbers the other way around, using the above table as an example, the 3rd row down would conflict with the 4th row down when updating. The same principle applies for the delete page method.
Unfortunately, it doesn’t seem that SQLite supports the ORDER BY for use with UPDATE. I’d rather not remove the composite key constraints here, as I’d like to maintain as much parity with the MariaDB implementation as possible.
Two possibly solutions I can think of would be to:
A – Select each row, sort in-code, and then update each row manually within a while loop. Definitely a bad idea, and would be extremely inefficient for larger guis.
B – Create a temporary table to make the changes in and merge it back with the original one. This seems slightly overcomplicated, and I feel like there’s probably a simpler solution.
If anyone has any advice, it would be much appreciated.