Example of adding new DB table and indexes to existing database
Added by Mark Travis 6 months ago
I need to update a current Sqlite database that already has data in it with a new table, add a foreign key to an existing table to the new table, and anything else that might be required to make the new database work.
I've only got examples of Wt::Dbo::Session code that create a DB from scratch if it doesn't already exist. But I've not found much that reconfigures an existing DB using the Wt frameworks.
What's the best way to add a new table and foreign key to an existing table for the new table so that I can return to using the proven Wt::Dbo interface without breaking anything?
Replies (3)
RE: Example of adding new DB table and indexes to existing database - Added by Mark Travis 6 months ago
Oh... I hope not....
https://redmine.emweb.be/boards/1/topics/17356
Based on the reply to that thread, it seems that I would have to create a new DB, then load the old DB to transfer the data from the old tables to the new tables, then delete the old DB. Is that the only way to do it?
RE: Example of adding new DB table and indexes to existing database - Added by Stefan Bn 6 months ago
Hi Mark,
I'm using SQLite as well and I do DB updates/upgrades on existing databes often. (Just a little context: My Wt plattform supports multiple domain specific projects. I have every project in a separate SQLite DB to separate users/configurations/data between projects. I attach the particular project DB to my main DB per WApplication instance depending on what project URL has been called. So I always had issues with keeping all the project DBs in sync or up to date with my Wt developtments.)
In my main DB I created a version flag for every project DB. If my Wt-software version get's newer than the particular DB version, I run an upgrade process from version to version (e.g. upgradeDatabaseToVersion14()).
In the upgrade-methods I mostly run just plain SQL commands with whatever SQLite offers using Wt's Dbo::Session feature:
Wt::Dbo::Session::execute(const std::string &sql)
followed by
Wt::Dbo::Transaction::commit()
I wrote a helper class, that does the most often used tasks, e.g. adding/inserting/removing tables, rows/columns, renaming tables/columns etc.
Best,
Stefan
RE: Example of adding new DB table and indexes to existing database - Added by Mark Travis 6 months ago
Thanks!
I also found this thread: https://redmine.emweb.be/boards/1/topics/10032
So, between what you outline above and what was in that thread, I see where to go next.