MySQL/SQLite Table Alter

Discussion in 'Plugin Development' started by THEK, Jan 7, 2013.

Thread Status:
Not open for further replies.
  1. Offline

    THEK

    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.
     
  2. Offline

    fireblast709

    So what is it exactly what you want?
     
  3. Offline

    THEK

    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.
     
  4. Offline

    skore87

    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.
     
  5. Offline

    fireblast709

    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
     
  6. Offline

    THEK

    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.
     
    Last edited by a moderator: Jul 8, 2016
  7. Offline

    fireblast709

    Why do you even want/need a dynamic structure?
     
  8. Offline

    skore87

    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.
     
  9. Offline

    PatPeter

    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.
     
Thread Status:
Not open for further replies.

Share This Page