How to "upgrade" the database in real world?

Question

My company have develop a web application using php + mysql. The system can display a product's original price and discount price to the user. If you haven't logined, you get the original price, if you loginned , you get the discount price. It is pretty easy to understand.

But my company want more features in the system, it want to display different prices base on different user. For example, user A is a golden parnter, he can get 50% off. User B is a silver parnter, only have 30 % off. But this logic is not prepare in the original system, so I need to add some attribute in the database, at least a user type in this example. Is there any recommendation on how to merge current database to my new version of database. Also, all the data should preserver, and the server should works 24/7. (within stop the database)

Is it possible to do so? Also , any recommend for future maintaince advice? Thz u.

Answer 1

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Here are more concrete examples of ALTER TABLE.
http://php.about.com/od/learnmysql/p/alter_table.htm

You can add the necessary columns to your table with ALTER TABLE, then set the user type for each user with UPDATE. Then deploy the new version of your app. that uses the new column.

Answer 2

I would recommend writing a tool to run SQL queries to your databases incrementally. Much like Rails migrations.

In the system I am currently working on, we have such tool written in python, we name our scripts something like 000000_somename.sql, where the 0s is the revision number in our SCM (subversion), and the tool is run as part of development/testing and finally deploying to production.

This has the benefit of being able to go back in time in terms of database changes, much like in code (if you use a source code version control tool) too.

Answer 3

Did you use an ORM for data access layer ? I know Doctrine comes with a migration API which allow version switch up and down (in case something went wrong with new version).

Outside any framework or ORM consideration, a fast script will minimize slowdown (or downtime if process is too long).

To my opinion, I'd rather prefer a 30sec website access interruption with an information page, than getting shorter interuption time but getting visible bugs or no display at all. If interruption times matters, it's best doing this at night or when lesser traffic.

This can all be done in one script (or at least launched by one commande line), when we'd to do such scripts we include in a shell script :

• putting application in standby (temporary static page) : you can use .htaccess redirect or whatever applicable to your app/server environment.
• svn udpate (or switch) for source code and assets upgrade
• empty caches, cleaning up temp files, etc.
• rebuild generated classes (symfony specific)
• upgrade DB structure with ALTER / CREATE TABLE querys
• if needed, migrate data from old structure to new : depending on what you changed on structure, it may require fetching data before altering DB structure, or use tmp tables.
• if all went well, remove temporary page. Upgrade done
• if something went wrong display a red message to the operator so it can see what happened, try to fix it and then remove waiting page by hand.

The script should do checks at each steps and stop a first error, and it should be verbose (but concise) about what it does at all steps, thus you can fix the app faster if something has to went wrong. The best would be a recoverable script (error at step 2 - stop process - manual fix - recover at step 3), I never took the time to implement it this way.

If works pretty well but these kind of script have to be intensively tested, on an environnement as closest as possible to the production one. In general we develop such scripts locally, and test them on the same platform tha the production env (just different paths and DB)

If the waiting page is not an option, you can go whithout but you need to ensure data and users session integrity. As an example, use LOCK on tables during upgrade/data transfer and use exclusive locks on modified files (SVN does I think)

There could other better solutions, but it's basically what I use and it do the job for us. The major drawback is that kind of script had to be rewritten at each major release, this incitate me to search for other options to do this, but which one ??? I would be glad if someone here had better and simpler alternative.