Create and update programmatically database structure

i would like to create and update database structure through the update of my software to customer.

What is the be开发者_运维问答st way ? Maybe Using Sql (create table, alter table etc.) ? Thanks

---

I don't see any other way than using SQL statements such as CREATE TABLE, ALTER TABLE and DROP TABLE. You might want to put somewhere a schema version number in order to check what updates your helper script must run in order to update the database schema. This is probably easier than making SQL statements idempotent, as often you also need to do some data conversion too.

---

this is how i do that:

1. create the Sql statements change script and attach it to the upload package.
2. create operating-system oriented batch/script commands [i.e., Windows .bat, .cmd or powershell commands. linux shell commands..] to execute the update process.
3. the script should backup at least the current DB structure [prefer with data] first to avoid risk of down-time or loss of data during update process! also if the software used by multi-users you must notify the client and get approval to stop the system usage during the update process.
4. in case the software administrated by the client db access might be changed so the lazy way is to ask the client admin to perform the update or better is to create small tool/script to ask the user to enter privileged account info.
5. if you already provided the client with periodical automated backup for the db make sure that the new amendments/changes added to the script. usually you don't need if the script is well written!

hope that's help!