Watch Our YouTube Channel
It is currently Wed Nov 22, 2017 6:18 pm
 

All times are UTC



Post new topic Reply to topic  [ 1 post ] 
Author Message
PostPosted: Sat Nov 20, 2010 11:32 am 
Offline
User avatar

Joined: Tue Mar 02, 2010 10:49 am
Posts: 171
Location: India/Tamilnadu/Tirunelveli
Recently we released timesheetv3.1 product. http://qualitypointtech.net/online_timesheet/index.php

We already put down version 3.0 in our server. We want to keep this new version in same folder in our server. For version 3.1 we added some more new features (adding estimated time,rateperhour , refer this link http://qualitypoint.blogspot.com/2010/11/released-version-31-of-our-time-sheet.html for new version updating).

For that we changed database design (alter table structure). We wanted to import this new database structure with old one.

But in server the old database (version 3.0) had some records in all table. And also we wanted to integrate our currently entering 'timesheet' database records with this new database.

For example,

Currently entering timesheet database name – ‘timesheet’
Timesheet v3.0 database name – ‘timesheetv3-0’

The above two database table structure is somewhat different from each other. We wanted to integrate these above database with one – the new one database structure is some what different from above two databases.

We could not want to integrate this two db manually, because both table had thousands of records. So we planned to write a script for integrating this.

Below are the script steps which were used in migrating one db data with another one.

Include “config.php” file – db name =’timesheet’
1. Import ‘timesheet’ db and alter all table structure which is almost matched with this new version (version 3.1) db table structure
2. Within this db we migrating one table data with another. Here the two tables ‘employee_details’,’ employee_involved_project’.

Using select query, selecting needed data from employee_details table and update this detail with employee_involved_project

3. Next two tables ‘project_details’ and ‘employee_involved_project’

Using select query, selecting needed data from project_details table and update this detail with employee_involved_project

4. next two tables ‘employee_details’ and ‘timesheet_status’

Using select query, selecting needed data employee_details table and update this detail with timesheet_status

5. next two table ‘project_details’ and ‘timesheet_status’
Using select query, selecting needed data project_details table and update this detail with timesheet_status

Next move ‘timesheetv3-0’ db data into ‘timesheet’

Include “config.php”; db name=’timesheetv3-0’

1. link two table ‘timesheetv3-0.login’ and ‘timesheet.login’

Select needed data from timesheetv3-0 db ‘login’ table and insert this data into timesheet db ‘login’ table.

2. Participate four tables ‘timesheetv3-0.employee_details’,’timesheet. employee_details’,’timesheetv3-0.employee_involved_project’ and ‘timesheetv3-0. timesheet_status’

Select needed data from timesheetv3-0 db ‘employee_details’ table and insert into ‘timesheet’ db ‘employee_details’ table. At the time of insert get last insert id using mysql_insert_id() function and update this last insert id into timesheetv3-0 db ‘employee_involved_project’ and ‘timesheet_status’ table.

Previously we did this using one update query for above functionalities

update employee_involved_project set employee_id=$lastinsertid where employee_id=$id

update timesheet_status set employee_id =$lastinsertid where employee_id=$id

But this query is getting overlapped some time according to data. so it caused the employee id wrongly assigned to some project. So we created two more fields (new_empid,new_projid) in ’timesheetv3-0.employee_involved_project’ and ‘timesheetv3-0. timesheet_status’ tables.

After that we changed query, like this

update employee_involved_project set new_empid=$lastinsertid where employee_id=$id

update timesheet_status set new_empid=$lastinsertid where employee_id=$id

3. participate four tables - ‘timesheetv3-0. project_details’,’timesheet. project_details’,’timesheetv3-0.employee_involved_project’ and ‘timesheetv3-0. timesheet_status’

Migrating data like above.

4. participated two tables – ‘timesheetv3-0.employee_involved_project’ and ‘timesheet. employee_involved_project’

Select new_empid,new_projid from timesheet3-0 db employee_involved_project table and insert into timesheet db employee_involved_project table.

6. participated two tables – ‘timesheetv3-0. timesheet_status’ and ‘timesheet. timesheet_status’

Select new_empid,new_projid from timesheet3-0 db timesheet_status table and insert into timesheet db timesheet_status table.

_________________
Anbarasan k


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron