# Migrating DB data


- truncate - ensuring on correct DB!
- in the new DB, enter the table, enter SQL tab, run the commands below


# Mountains

INSERT INTO `mountains`(id, title, ukHillsDbName, ukHillsDbLongitude, ukHillsDbLatitude, ukHillsDbSection, MountainCategoryID, grid, urlName, ukHillsDbMap50k, ukHillsDbMap25k, height) SELECT ID, ukHillsDbName, ukHillsDbName, ukHillsDbLongitude, ukHillsDbLatitude, ukHillsDbSection, MountainCategoryID, grid, urlName, ukHillsDbMap50k, ukHillsDbMap25k, height FROM jonshutt_stri261.Mountain



# logs


## copying just my logs for testing

INSERT INTO `logs`(id, user_id, mountain_id, description, date_climbed, done) SELECT ID, MemberID, MountainID, Description, DateClimbed, Done FROM jonshutt_stri261.Log 


WHERE MemberID = 1;


# routes

 - disable foriegn key checks when inserting if doing before copying users

## main routes data

INSERT INTO `routes`(id, description, distance, latlon, gridref, elevations, distances, public, points, title, user_id) SELECT ID, Description, Distance, latlon, gridref, elevations, distances, public,points,  Title, OwnerID  FROM jonshutt_stri261.Route

## join table

INSERT INTO `route_mountain`(id, route_id, mountain_id) SELECT ID, RouteID, MountainID FROM jonshutt_stri261.Route_Mountain



# USERS 



- untested - needs the 'replace' on the passwords 
need to test on old passwords that don't have the same encripts
SELECT *, Replace(Password, '$2a$', '$2y$') AS Password FROM `Member` WHERE ID 1;

<!-- where  PasswordEncryption = blowfish - 3000 users -->


INSERT INTO `users`(id, email, password, subscription_id, firstName, lastName) SELECT ID, Email, Replace(Password, '$2a$', '$2y$') AS Password, SubscriptionID, FirstName, Surname FROM jonshutt_stri261.Member WHERE `Email` IS NOT NULL

## photos


1 =- copy data

INSERT INTO `photos` (Description, mountain_id , user_id, PhotoID ) SELECT Description, MountainID,  1 as MemberId, PhotoID  FROM jonshutt_stri261.MountainPhoto  Where jonshutt_stri261.MountainPhoto.MountainID > 0

2 - then update the url with the url from the other 'File' table

<!-- UPDATE  `photos`
SET `URL` = ( 
    SELECT FileFilename 
    FROM jonshutt_stri261.File AS table2
    WHERE table2.ID = PhotoID
) -->

UPDATE  `photos`
SET `URL` = ( 
    SELECT Filename 
    FROM jonshutt_stri261.File AS table2
    WHERE table2.ID = PhotoID
)


UPDATE  `photos`
SET `URL` = ( 
    SELECT FileFilename 
    FROM jonshutt_stri261.File AS table2
    WHERE table2.ID = PhotoID
)
WHERE ok = ''

// first set of photos woked


UPDATE photos SET ok = 'yes' WHERE ID IN (9,15,446,445,650,651,471,576,693,695,643,682,681,434,438,652,405,404,653,654,744,660,433,503,710,712,711,714,713,716,715,717,718,720,719,721,722,723,477,476,571,673,674,468,469,581,559,592,593,594,743,418,494,590,694,472,575,377,671,378,672,754,644,417,421,496,610,609,608,489,638,447,473,663,665,666,664,679,680,470,573,464,428,683,429,739,686,685,688,687,689,755,391,390,392,393,478,395,394,396,416,426,668,667,669,363,364,365,366,495,407,409,670,595,419,596,597,757,756,474,21,6,475,23,369,370,427,486,511,487,628,629,630,631,632,633,634,635,636,637,655,701,702,734,735,736,737,738,376,591,684,691,692,577,420,490,528,646,657,508,435,761,509,510,520,521,522,523,524,436,501,500,502,750,534,533,535,740,578,698,525,658,527,526,656,410,759,760,448,450,449,358,552,553,488,677,512,513,514,699,515,516,430,431,432,600,599,601,639,640,641,642,540,541,574,627,379,7,554,444,380,381,382,383,385,384,386,387,440,388,767,439,700,622,620,621,371,647,752,362,788,491,764,765,766,536,424,537,648,649,696,779,786,787,783,579,697,504,372,24,8,25,26,27,29,28,360,397,399,398,401,403,400,411,412,413,753,414,479,480,481,612,614,613,615,703,704,705,706,707,708,709,749,607,572,437,498,499,784,570,425,538,539,742,584,741,776,777,778,359,678,789,605,415,690,606,423,785,782,422,457,458,580,598,529,589,588,530,532,531,587,406,461,460,586,780,517,781,519,518,583,745,645,746,747,748,751,585,582,441,368,455,456,483,484,497,485,793,762,763,11,30,31,33,32,34,771,773,772,774,506,505,507,775,728,611,731,730,729,733,732,661,662,616,617,618,619,676);



- not copied filename, and put all owners as me - could update that later