PDA

View Full Version : Nutcracker: How to export data and save your changes between local installs



smeighan
11-13-2012, 03:19 AM
For the more advanced users:

if you know how to get to myphpadmin and click on the SQL tab.

Here is a quick way to save your data and reload it after a local install step #7 reinitializes the database.

i will make this into a command and a click of the button. I am in an all day class at Oracle university this week.

So

1) Export all tables that have any data related to your user name.
In the sql window , cut and paste this export. Edit the username to be your user name. The exported files will be in
c:\xampp\htdocs\nutcracker\sql. If you are using wamp then you need to edit these to be
c:\wamp\www\nutcracker\sql



SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/effects_user_hdr.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from effects_user_hdr
where username ='f';

SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/effects_user_dtl.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from effects_user_dtl
where username ='f';

SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/effects_user_segment.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from effects_user_segment
where username ='f';



SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/project.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from project
where username ='f';

SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/project_dtl.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from project_dtl
where project_id in (select project_id from project
where username='f');

SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/song.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from song
where song_id in (select song_id from project
where username='f');

SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/song_dtl.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from song_dtl
where song_id in (select song_id from project
where username='f');

SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/members.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from members
where username ='f';


SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/models_strands.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from models_strands
where username ='f';


SELECT *
INTO OUTFILE 'c:/xampp/htdocs/nutcracker/sql/models_strand_segments.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from models_strand_segments
where username ='f';



What this did was export all tables that have anything related to your user name.

2) Next, run step (7), and install anew database.

3) Now we need to remove all of your username from this newly installed database

delete from effects_user_hdr
where username ='f';

delete from effects_user_dtl
where username ='f';

delete from effects_user_segment
where username ='f';

delete from project
where username ='f';

delete from project_dtl
where project_id in (select project_id from project
where username='f');



delete from members
where username ='f';


delete from models_strands
where username ='f';


delete from models_strand_segments
where username ='f';



4) The last step is to reload the exported data back in

LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/effects_user_hdr.csv'
INTO TABLE nutcracker.effects_user_hdr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';


LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/effects_user_dtl.csv'
INTO TABLE nutcracker.effects_user_dtl
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/effects_user_segment.csv'
INTO TABLE nutcracker.effects_user_segment
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';



LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/members.csv'
INTO TABLE nutcracker.members
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';


LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/models_strands.csv'
INTO TABLE nutcracker.models_strands
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';


LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/models_strand_segments.csv'
INTO TABLE nutcracker.models_strand_segments
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';


LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/project.csv'
INTO TABLE nutcracker.project
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE 'C:/xampp/htdocs/nutcracker/sql/project_dtl.csv'
INTO TABLE nutcracker.project_dtl
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';



If you want to repeat these steps , you must manually delete the csv files from your sql directory. mysql doesnot allow over writing them

I know it looks like alot of steps, i will wrap it and make it work better for you.

thanks