I'm currently updating a plugin that uses the SQLibrary by PatPeter. I need to update the layout of the tables without losing any data. How do I check if all the columns are there? Should I have the column layout stored in a HashMap or something and check against that? I thought it would be easy by only checking if 1 column is missing, but what if I add another column in the future and someone updates from 2 versions ago and is missing both columns. Plus perhaps I'll add new tables and need to check they've got those too.
I'd like to know the best way to check that the current database layout matches the updated layout. If it doesn't simply add the columns that are missing.
Take a look at this. A command you can call is "PRAGMA table_info(table_name)". I haven't tried it myself but I would suggest you look up the documentation on it.
select everything from the table ("SELECT * FROM table"), and loop over it. Check the values (update if neccesary) and remove from your runtime storage. INSERT all the storage that was not removed DELETE what was in the table but not in the storage
Nope, layout/structure, not data. skore87 Just looking at it now, thanks EDIT by Moderator: merged posts, please use the edit button instead of double posting.
Probably upgrading from an old version that used a different structure where he would want to alter the table instead of migrate it to a new database.
I do a CREATE TABLE IF NOT EXISTS and catch an SQLException. If the table exists as you specified, it does nothing. If the table has a different structure, however, it will throw an exception. This is also a good idea. That would be O(n) and very taxing on the server, but as stated it's structure and not data.