Database Maintanence
Added by Joseph Toppi about 9 years ago
As projects evolve it is common for objects to gain or lose members and therefor common for DB tables to gain or lose columns. The same can be said for tables/classes and custom queries with indexes. I see that the Dbo::Session object has the ability to execute arbitrary SQL. For simplicity I am going to focus only classes and columns. I have seen that others advise creating alter table statements for updates this.
Is this currently the best way to keep the DB in sync with the C object layout? If it is the best way currently how do people manage database migrations?
I would guess different teams do different things. I can think of only two real strategies for minimal downtime.
The first way could be to run alter statement more or less manually. Run statements that add columns ahead of new executable installation and alters that remove them after. You would want to rig it to do this during low traffic periods I suppose. This seems exposed to error and seems against the current trend of progress of automated deployment.
The second way I can see it happening involves more legwork in C. Every time a change would be made atransitional version of the involved classes could be made. These could avoid column use until alter statements have finished and remove columns safely when not in use. I am not sure how this would work with the persist(Action&) method on each persisted object. It seems unclear when this is used and called. Despite this unknown, it seems likely this strategy is preferable in terms of reliability and automatability. I say this because, one could test (unit, functional, performance, etc...) their migrations to remove human error and Continuous Integration tools are already pretty good at installing single files.
I am curious on everyone's thoughts because there have to be a variety of strategies out there that I am simply not aware of.
Replies (3)
RE: Database Maintanence - Added by Zalu Hobgobicus about 9 years ago
Background: I'm a game developer with some years of reluctant SQL non-game work.
My thought, having worked with SQL databases, is to use a non-relational (no-SQL) databse (Couchbase Server) to avoid that and other SQL qualities that don't really match what I want to do with my database.
On my SQL projects, I generally do dev and test on a dev/test server, and then add the new stuff to new columns on the database, and then push the new client/server code.
I try to make the system backwards-compatible whenever possible, which sometimes results in some legacy columns etc until they're completely phased out. The C object doesn't need to stay completely in sync, if the old code can still make do with the old columns. Leave the old DB columns and custom queries in until no software is left that uses them, and all the relevant data has been migrated out of them to wherever it lives now.
I much prefer working with a no-SQL database, because it puts the data complexity in my court in C, and doesn't require me to also deal with SQL etc., which isn't my preferred way to do most things, anyway.
RE: Database Maintanence - Added by Koen Deforche about 9 years ago
We typically have a migrate script that accompanies git commits which require database schema changes. This script is then run by the sys-admin as part of a deployment.
RE: Database Maintanence - Added by Joseph Toppi about 9 years ago
@Zalu Hobgobicus
I like your idea of ignoring the extra columns unless they are an issue, then dealing with them cleanly at the right time.
I appreciate the suggestion of a using a key/values store, but I think an SQL database is close to ideal for this project. I am not creating anything of preposterous scale, I doubt I will get zillions of users. My data maps cleanly onto a relation table mapping. Wt has already done most the heavy lifting with their ORM tool. Ubuntu has an easy to install Postgres package in the default repository. The enforced consistency on the data as it reduces the kinds of checks I need to do this project.
because it puts the data complexity in my court in C
This is exactly what I am trying to avoid. As much as I like C I do not like or want complexity, I will let the database handle data the same way I have chosen to let Wt handle widgets and websockets. I understand that good data structures inform the decision making processes for good algorithms. In most web applications all this means is choosing which data members to move from the DB to the browser by way of programmed object instances and then doing it the other way around. As boring as that sounds it describes my current project pretty well.
@Koen Deforche
This is brain dead simple, why didn't I think of this. Unless absolute minimization of downtime is the goal then this is obviously a workable solution. It is even testable if DB migrations are automatable.