Alternative to overwriting the DB every patch

[Kyle (OP)]

I'm sure you're quite aware of this approach and it's just a matter of getting around to it (... and of course, every db change is the Last^TM change that ever needs to be done...) but I saw someone mention they spent an hour working on medals only to have their work overwritten, and I just wanted to share how I've been avoiding overwriting the database with schema changes in my game.

var latest_db_version = 20200329
var db_version = db.fetch_assoc("select Use1024 from App limit 1")[0]['Use1024']

if db_version < latest_db_version:
   
   if db_version < 20200204:
      db.execute("""create table Impacts (
         ImpactID      INTEGER PRIMARY KEY AUTOINCREMENT,
         GameID        INTEGER,
         SystemID      INTEGER,
         ContactType   INTEGER,
         NumImpacts    INTEGER,
         Strength      INTEGER,
         Xcor          REAL,
         Ycor          REAL)""")
      db.execute("alter table Population add column ThermalSig integer")
      db.execute("alter table Population add column EMSig integer")
      db.execute("update Population set ThermalSig = 0, EMSig = 0")

   if db_version < 20200209:
      # ... And so on. Lots more of these blocks

   db.execute("update App set Use1024 = " + str(latest_db_version))

(I repurposed the App.Use1024 field since I need to be compatible with a raw import of Stevefire.mdb)

Edit: I'm realizing this suggestion is more geared toward whenever new content gets added... it's not a great way for dealing with bugs that are cropping up that are entangled in existing data.  Kind of wish this would stop getting bumped.

[Lightning]

While I would love to see something like this implemented, it would require tracking changes as Steve makes them to implement. And if you have already run into some errors that the patch is supposed to fix, there may be corruption in the database that get transferred to the upgraded one. That would/could make bug troubleshooting more difficult and something else Steve may not feel like spending time doing.

On a related side note, given the 'are you on the current db' or 'db/.exe didn't overwrite' comments I've seen in the bugs threads, maybe putting a DB version field in that the .exe checks on startup with a 'incompatible db version error' that prevents the .exe from using an incorrect db would be useful.

[Erik L]

It would also force Steve to ask if it's a vanilla db

[MasonMac]

Nuke the db if you must, glorious Arstotzka cannot be erased from history!