How can I programmatically add a column to a SQLite table if ALTER TABLE is not supported?
That’s quite necessary for some firmware upgrades.
I can only think of copying all the data into a new database, but it sounds overkill and impractical.
1 Like
@Mr_John_Smith As I said, ALTER TABLE is not supported by TinyCRL. See “omitted commands & features”:
https://docs.ghielectronics.com/software/tinyclr/tutorials/sqlite-database.html#omitted-commands--features
That’s why I’m asking.
Oh, my apologies. Are you trying to make the database on the device directly? Are you able to copy the DB file to the device at deployment time?
The initial db is deployed on the device. However, once the user starts using the device, upgrades must make possible changes to the schema on the populated database already on the device.
Right, so how was the initial db deployed to the device? Remember if it’s small enough it would be possible to copy out the db to another machine, perform the modification and then download it back to the envrionment.
Deployed on MicroSD when manufacturing. But, once the card is in the device, any update must be automatic, and updates on the db should happen on the device. The PC is needed for updates of the firmware but back and forth of the database should be avoided. Even if automatic, it would imply major dev effort and other issues.
ALTER TABLE is a huge help. Not having that is a major issue.
Not sure of the needed work to for adding it to the firmware but if this is for a volume commercial application then I suggest you reach out to us to discuss options.
1 Like
It’s a volume commercial application, yes.
Thank you so much for the availability on this. I need an internal check first and, in case, I’ll contact you.
Please consider that apart from our application, alter table is a major and very useful feature in general, at least for the possibility of adding columns. I know it’s not trivial, but without that, upgrades involving the database raise issues.
1 Like
The forum is great for general questions but we do a lot of one on one with commercial customers. Either way, we are here to help.
If it can’t be done on a different machine, then you have no choice but to create the new table and copy the data from the old to the new. Seems strange since SQLLIte is open source and the is most certainly a way of performing that operation on the platform.
I am far from a SQLite expert…
You could add another table to the DB, with the additional fields, and join them by primary key. This would require changing your SQL statements and likely a lot of code, but it is a workaround of sorts…
Of course, performance might be an issue.
Given Tinyclr limitations, good design practice would be to add a few extra text fields to each table for future needs.