Hey, to make it clear this is not about how to make a sqlite file. It's more about what steps to take to make the SQL backed app future proof
To make my question simple, an example I wana use is to make a todo app. (it's not that actually) Here's what I want to do: Add new todos, Edit todos Share todos via(email , social sites) Import todos Export/backup todos
Every new added todo wil have primarykey or ID in sqlite. Resetting or deleting todos. I don't want the pkey to auto increment. If a new todo with pkey 2 is created and the deleted, then the 2 pkey will remain unused and over a long period, there may be IDs in 3 digits.
I need a way to make IDs with fixed number of digits and kinda unique to the device.
I'm confused what mechanism should I work out. Any help is appreciated.
Sqlite uses a 32 bit integer to store Pk's. That's VERY large. If you ever reach this number, sqlite automatically finds an empty (previously deleted) PK and use it.
SQLite actually uses a 64 bit signed int for row ids. So if a user were to create and delete a record every second, 24 hours a day, 365 days a year (and even 366 days a year on leap years) it would take many billions of years before SQLite would need to try to reuse old row ids.
However, if you tack AUTOINCREMENT onto the end, SQLite doesn't ever reuse old rowids. So you might want to keep that in mind; otherwise you might have some very angry users a few hundred billion years from now. They would likely change their five star reviews to 1 star reviews and demand their money back.
I am curious about your comment about wanting ids to be unique to the device. Are you planning on doing some sort of migration/syncing (that is, where you need to always remember where a certain record originated)? If so, you could just pair the rowid with the device UUID for cross-device uniqueness. If you just want to share records but don't need to forever track where they came from, then this won't matter, though.
__________________ Recall It!Tag your notes. Tag your photos. Tag your thoughts. Tag your life.
SQLite actually uses a 64 bit signed int for row ids. So if a user were to create and delete a record every second, 24 hours a day, 365 days a year (and even 366 days a year on leap years) it would take many billions of years before SQLite would need to try to reuse old row ids.
However, if you tack AUTOINCREMENT onto the end, SQLite doesn't ever reuse old rowids. So you might want to keep that in mind; otherwise you might have some very angry users a few hundred billion years from now. They would likely change their five star reviews to 1 star reviews and demand their money back.
I am curious about your comment about wanting ids to be unique to the device. Are you planning on doing some sort of migration/syncing (that is, where you need to always remember where a certain record originated)? If so, you could just pair the rowid with the device UUID for cross-device uniqueness. If you just want to share records but don't need to forever track where they came from, then this won't matter, though.
thanks for the explanation. I guess this is what im looking for.
UUID seems interesting. I want to todos to be shared across devices via sync or export via email or just share it with friends. However, im not sure if i'd want to track where it came from. Its just a question of "if i'd have a use for it some time in the future with more added features".
i know of some apps that do use this UUID.
they store it like {appname}XXXXXX-XXXX-XXXX-XXXXXXX where XX=UUID
They do have a rowid.
Sqlite design approach
Linear Mode
