The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
Of course I'll use if it makes all work, but I don't need it.
I was preparing the sample flow as @zenofmud suggested and with my big surprise it worked!
I started investigating what the reason was.
I made only very little (tiny) modifications. It ends up that the link in my static folder needs to be nominated in the sqlite's node.
To make the flow easier to be loaded as I thought that not all might use the Raspberry (user pi), I made the node use the DB in the folder in / belonging to www-data .
$ ls -l /home/pi/mynode/recipedb.sqlite
lrwxrwxrwx 1 pi pi 25 giu 12 20:53 /home/pi/mynode/recipedb.sqlite -> /www-data/recipedb.sqlite
I will use this, thanks. I was worried about this approach for ID reuse.
Say in the table there are the rows whose IDs are from 1 to 10, then the row with ID=4 is deleted. The table has rows with ID 1-3 and ID 5-10.
When an INSERT is performed the new row would have ID=4 or ID=11?
The case ID=4 means that the DB is doing the ID reuse: of course max(ID) won't work.
But -a good new sometime- my DB setup generates an unused ID, I checked doing the row drop with the CLI and the insert via RNode.
PS @Colinselect last_insert_rowid() FROM recipe; doesn't work, in my opinion it has to be called in the same DB connection; after the insertion I have a n zeros, where n is the number of rows.
I would agree with you yesterday; today I tried and it worked without reuse.
I created the table with the primary key without the AUTOINCREMENT, I had 12 records with ID 1 to 12, I deleted the one with ID 4 from the CLI. Then with the browser I did insert and the new record has -with surprise- the ID 13 and not ID 4 as I was expecting.
I don't have an explanation: I was waiting for a different result.
There may be some algorithm involved that does garbage collection of IDs. I suspect that if you keep adding and removing records that eventually it will start reusing then.
Perhaps you could just feed it direct to the display from before it is given to the database.