Swapping a DataBase ?

knickerlas

Hi
I want to be able to insert and modify data on a database that is used to hold site content.

The thing is that I would like to work on a temporary version of the DB and then 'swap' it for the live one when I'm happy with it.

I'm using MySQL, can someone tell me where I should be looking regarding functions/formulas for this?

Many thanks

Nick

duncan_glover

Nick,

Ever looked at PHPMyAdmin?? Its a web-based GUI interface perfect for creating and manipuating SQL databases. Might be useful if you want to copy certain parts of the database when you make your temporary version??

Duncan...

[deleted]

So where is the problem?
just create a copy of your database and call it 'test' or whatever.
In your script you should have only one place where the databasename is used, so switching should be a doddle.

knickerlas

Okay, I presume there is a 'copy' command I can use. Is it as easy to 'replace' the live DB with the 'test' DB?

Cheers

Nick

[deleted]

You don't replace anything.
You create a copy of the database, and give that a name like 'test'.
So you have a 'live' copy and a 'test' copy.
Then you tell your scripts wether they should use the 'live' database or the 'test' database.

knickerlas

Great, that sounds sensible, are there functions to transfer data from the 'test' table to the 'live' one? I'm working on an admin for the sites content.

Thanks again

Nick

knickerlas

Hi Duncan,
I've not looked at PHPMyAdmin although I've heard of it. I'll give it a look tonight.

Thanks

Nick

[deleted]

Nope, you'll have to write queries to do that.
Be carefull that you can't simply copy all the data for a record when you use auto_increment fields, or you'll get problems with the UNIQUE constraints.

knickerlas

Thanks, I'll work on it!

Nick

Anon

A better answer would be to put a field in your database that stores the "state" of a record. If the state is "live" then it shows up. If the state is "build" then it only shows up on the build side so to speak. Then, all you need to do is change the state of the record to put it online.

knickerlas

Ripper!
&nbsp That would definately do it. I't will mean a little re-working but I can see how that will work nicely.

Thanks very much

Nick

[deleted]

Eek! 'worst-practice alert!' 🙂

And this from 'the' Scott Marlowe. tsk tsk.

Never ever work on 'live' data, not even with test/live flags.

When you work on live data, sooner or later you will click 'delete' where you really wanted to click 'cancel' and you will destroy your database.

If you use a test database, you can let your cat walk over your keyboard and the worst thing that happens is that you have to re-enter the records that you dind't backup/replicate to the live database.

knickerlas

Crikey, tricky isn't it?

What is the best way for me to make my data 'live'? In my admin program you can add, edit and delete whatever you like for as long as you like.
When I have a whole new 'test' database how could I best make that the 'live' table?

Is there a painless answer or will I have to copy each set of records (3 tables) and add them to the 'live' DB?

Again, Thanks everyone!

Nick

Anon

Bullshit. What I'm talking about isn't a bad practice at all.

Every db entry marked live shows up on port 80, build on port 8000, and test, let's say, on port 8001.

Users edit on port 8000, submit it for approval, approval authority checks it on 8001, and then can push it to port 80.

It's a system that works and has worked fine for many an installation. I'm just not sure you understoof what I was saying.

Anon

Of course, it's up to your php code to check who has permission to do what...

knickerlas

Okay,
is it difficult to do as you've said?
What are the basic steps I would have to take, if you can point me in the right direction I'll look up the specifics.

Nick

[deleted]

That's assuming you have working scripts that don't make mistakes. Then it could work, all though you still can't edit records once they have been put live, which is a major nuissance.

This scheme would definately not work if you are developing scripts and you want to test them on the database.

knickerlas

Yes. I can see that, so what do you think would be the best solution?

Nick

kparker

In your regular php scripts:

  include('login.inc');

In 'login.inc':

  <?php
  include('mode.inc'); // defines $dbname, $dbuser, $dbpass
  if (! ($dbid = mysql_connect($dbname, $dbuser, $dbpass)))
    header('Location: /bad_db.php');
  ?>

Now, in your real database directory, e.g.:

  /www/ht_docs/live

you have a copy of 'mode.inc' that points to the live database, whereas in

  /www/ht_docs/test

you have one that logs into the test database. When you want to promote "test" to "live", you simply copy /www/ht_docs/live/mode.inc to a safe place, copy all of ./test to ./live, and then put back the original 'mode.inc'. Bingo: all your new code is now running against the live database. Plus, this method integrates will with CVS (you can have scripts do the promotion, etc), and it can be extended to further levels--e.g. each developer can have their own sandbox directory (with a corresponding copy of the database) and then 'test' is a more formal "validation" copy run by your testing department.

The key is to centralize that decision about which database to connect to inside one tiny include-file that contains nothing else.

knickerlas

Thanks Kirk,
that looks pretty straight forward and would definately solve the problem.
The only thing I'm not clear on is the transfer from test to live, if I do it as you've suggested surely it will against the live db but the new data is in the test db?

Perhaps I just don't get it (wouldn't be the first time!)

Thanks very much

Nick