How to "update" the database in the real world?

Question

My company has developed a web application using php + mysql. The system can display the original price of the product and the discount price for the user. If you are not logged in, you will receive the initial price; if you are logged in, you will receive a discount. This is pretty easy to understand.

But my company wants more features in the system, it wants to display different prices on different users. For example, user A is a gold partner, he can get 50%. User B is a silver player with only 30%. But this logic is not prepared in the source system, so I need to add some attribute to the database, at least the user type in this example. Are there any recommendations for merging the current database with my new version of the database. In addition, all data must be stored, and the server must be running 24/7. (within database stop)

Can this be done? Also, any recommendation for further advice? Thz u.

Answer 1

Do you use ORM for data access layer? I know that Doctrine comes with a transition API that allows you to switch versions up and down (in case something went wrong with the new version).

Outside of any ORM structure or consideration, a fast script minimizes slowdown (or downtime if the process is too long).

In my opinion, I would rather interrupt access to the website for 30 seconds with an information page than getting a shorter interception time, but getting visible errors or no display at all. If interruption time matters, it is best to do this at night or with less traffic.

This can be done in one script (or, at least, launched by one command line), when we will do such scripts, we include a script in the shell:

• placing the application in standby mode (temporary static page): you can use the .htaccess redirection or whatever applies to your application / server environment.
• svn udpate (or switch) for updating source code and assets
• empty caches, clearing temporary files, etc.
• rebuild generated classes (symfony specific)
• update database structure with ALTER / CREATE TABLE queries
• if necessary, transfer data from the old structure to the new one: depending on what you changed in structure, you may need to select data before changing the database structure or using tmp tables.
• If everything went well, delete the temporary page. Update completed
• If something went wrong, display a red message to the operator so that he can see what happened, try to fix it, and then delete the wait page manually.

The script should run checks at every step and stop the first error, and it should be detailed (but concise) about what it does at all stages, so you can quickly fix the application if something goes wrong. The best restored script would be (error in step 2 - stopping the process - manual correction - recovery in step 3), I still did not find the time to implement it this way.

If it works very well, but this type of script should be intensively tested, as close as possible to the production environment. In general, we develop such scripts locally and test them on the same platform as in production env (only different paths and DBs)

If the wait page is not an option, you can go without it, but you need to ensure data and user integrity. As an example, use LOCK for tables during data update / transfer and use exclusive locks for modified files (I think SVN)

There may be other better solutions, but basically this is what I use and it does the work for us. The main drawback is that the script type had to be rewritten in each major release, which prompts me to look for other options for this, but which one? I would be glad if someone had a better and simpler alternative.

Answer 2

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

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

You can add the necessary columns to the table using ALTER TABLE, and then set the user type for each user using UPDATE. Then deploy the new version of your application. which uses a new column.

Answer 3

I would recommend writing a tool to gradually run SQL queries against your databases. Very similar to Rails migration.

In the system I'm currently working on, we have such a tool written in python, we call our scripts something like 000000_somename.sql, where 0s is the revision number in our SCM (subversion), and the tool runs as part of the development / testing and finally deployment for production.

This makes it possible to return in time in terms of database changes, as in the code (if you use the source code version control tool).