Forum Moderators: open
There must be a way to do this, but I've yet to figure it out. Previously, during the development process, I put the current version of the project (including it's DB) up on my domain for client comments/approvals, etc.
Now, as development continues, the content of the DB will change on my local machine - especially as I'm nearing the end of the project - and I need to reflect these changes live as well. What I HAVE BEEN doing was to delete the entire DB from my domain and then create a new, fresh DB to which I would re-upload all tables and current data.
This has never seemed like a very efficient process and felt there must be a way to do this without uploading everything from scratch... isn't there? Like, shouldn't I be able to access the online DB via phpMyAdmin and then find some button that would simply upload all data from exported sql statements on my local machine? Or, when data changes and you want to reflect those changes live, do you have to destroy and re-create the entire db, tables, and content?
If there's a way to simply update a db's data (not structure) could someone please tell me how this is done?
Neophyte
For example, if I have table1 and table2 in production and on my development machine currently in sync with one another and I would like to add a column to table1 as well as add a new table3, I would create a SQL script such as (oversimplified):
ALTER TABLE table1 (
... changes to column definitions to table1 ...
);
CREATE TABLE table3 (
... new table3's column definitions ...
);
You can insert, update, delete rows from tables in these scripts as well.
I would then run this script against my testing environment DB and test my new code that runs against the new changes.
If the changes worked, great! Now I can run the script in production and I'm done.
If the changes did not work then I would make changes to the script and retest in my testing environment. It's an iterative process (as is all debugging) until I get everything working. Then the script can be run in production once everything is successfully tested.
There is one EXTREMELY important caveat here. If your 1st version of the scripted changes did not work and you are required to go through several iterations of changes in testing before getting it to work then each subsequent iteration of the script should be written in such a way that it is 're-runnable'.
For instance in the above example, I created a new table, table3. Because table3 doesn't exist in prod and I may have to run it multiple times in dev, I would write it similar to the following:
IF EXISTS ( ... check for existance of table3 in current DB - not sure of MySQL syntax ...) THEN DROP TABLE table3;
CREATE TABLE table3 (
... new table3's column definitions ...
);
This is the simplest problem to solve when it comes to making your scripts rerunnable in dev but also insuring they will still work in production when you run them the first time.
There are other approaches as well...
For example: You could backup your dev tables that are changing, try your script, test your code... If problems occur, restore the tables, modify script, try your script, test your code... and on and on until you have a script that works against the original table(s) that existed in dev and prod.
But the key is you want to end up with a script that will work in dev (even after you've gone through several iterations of dev changes) but also work in prod the first time you run it.
By scripting changes, everything you do is totally reproducable (spelling? hehe). The main thing that you then have to worry about causing your scripts not to work when you run them in prod is data. Data could be different in dev than in prod. A script may work in dev because it didn't have the same type of data in the tables as in prod. Or it may fail in dev (and would have worked in prod) because buggy code being tested there created bad data in the dev DB. This can be solved by restoring your prod DB to dev from time to time, especially when significant changes are about to be made or were recently made. Just be sure to scrub any sensitive data you have in the new dev DB when you do this. You wouldn't want peoples real SSNs, emails, credit card numbers, etc. to be made available to people with access to dev.
Hope that helps get the wheels turning...
ARRRRRRRRG Just reread your post... You're talking more about moving data from dev to prod... Depending on the amount of data, that could be done in scripts as well. Again, if you used a script to insert the data into dev then you could simply rerun those scripts to insert the data into prod.
But if it is a mass move of data, there is likely a better way. Someone else will have to help you there.
[edited by: ZydoSEO at 5:19 pm (utc) on Feb. 3, 2008]
