Administrators gav240z Posted May 17, 2015 Administrators Share Posted May 17, 2015 Ok after testing this locally, I just ran this command on the server and it seems page load time has increased significantly for forum threads where multiple avatars are displayed. Wondering if anyone else has noticed increased responsiveness? Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted May 18, 2015 Administrators Share Posted May 18, 2015 1 more piece of the puzzle solved, looks like Avatars now move across more consistently, there is a few that are broken but the majority say 98% will move across. Yay! Quote Link to comment Share on other sites More sharing options...
Moderators PB260Z Posted May 18, 2015 Moderators Share Posted May 18, 2015 In fairness most of the time the site runs itself Way to modest mate Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted May 18, 2015 Administrators Share Posted May 18, 2015 Way to modest mate Thanks mate, does the site seem any quicker to you today? In terms of response and page load times? Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted May 18, 2015 Administrators Share Posted May 18, 2015 Gav I work in IT - in corporate lending so worlds apart from web but does involve some database stuff and APIs etc. Let me know if there's anything I can lend a hand with. Hi mate, I have a question for you re: SQL. I've just converted the Gallery over and it seems to be working fine, except for the fact that each album that was previously created had an owner assigned to it. Now however the owners are blank (as in not defined). The old Coppermine Gallery table (cpg132_albums) has a couple of columns: 1. title = Album name 2. owner = member id The new Invision Power Gallery table (ips_gallery_albums) has the following corresponding columns: 1. album_name = Album name 2. album_owner_id = member id Where the left hand value before the = sign is the database column name. What I need to do is some kind of lookup for the old table, where I extract the title and owner columns and then insert them into the album_name and album_owner_id columns. Although interestingly I tried do manually update the album_owner_id value via phpmyadmin UI and it didn't seem to work... I'll look into that. Quote Link to comment Share on other sites More sharing options...
Moderators PB260Z Posted May 19, 2015 Moderators Share Posted May 19, 2015 Thanks mate, does the site seem any quicker to you today? In terms of response and page load times? Been out and about today and yes the site does seem faster Using iPad on Optus Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted May 19, 2015 Administrators Share Posted May 19, 2015 Really just putting this here for my own sake, but just in case anyone else is interested. I noticed that after converting the photo albums across to the new software that the albums lost their owners, along with comments etc.. Turns out that when you 'bridge' software (in this case Coppermine gallery and SMF forum) Coppermine defers to the SMF users tables (they share the same user tables) rather than creating their own independent tables. So what's the problem? This means Coppermines own user tables are blank (no users in them) the script to convert Coppermine to Invision Power Suite's Gallery can't convert these users because they don't exist in the table. Compounding the problem is the fact that when you convert from the old forum software to the new software a members unique ID changes, so a member with ID 2 before becomes 2048 or similar. I've been doing a few join statements in SQL to figure out how I can look up a specific user by ID and name and see how their ID has changed in the old system to the new, I'm hoping to use this information to update the database with the correct owners of albums etc.. Once I figure that out, I think that's the last major hurdle. Then I can upload a test version for people to experiment with and see if any issues come up before uploading and transitioning over. This looks like something I might need. http://stackoverflow.com/questions/12248413/replacing-a-sql-column-value-based-on-a-column-another-table Quote Link to comment Share on other sites More sharing options...
andyk_79 Posted May 19, 2015 Share Posted May 19, 2015 Sorry Gav just saw this now. I might be looking a bit simplicly, but an insert/select would do the trick ? insert into ips_gallery_albums (album_name, album_owner_id) select title, owner from cpg132_albums; Edited Note your comment about the user tables - do they provide a data dictionary for the DB ? I'll google if I can find one online Edit again: Just realised this will insert when you want to update if the gallery has migrated but the owner is blank. Update script: update ips_gallery_albums set album_owner_id = (select owner from cpg132_albums where cpg132_albums.title = ips_gallery_albums.album_name) where album_owner_id is null; Hi mate, I have a question for you re: SQL. I've just converted the Gallery over and it seems to be working fine, except for the fact that each album that was previously created had an owner assigned to it. Now however the owners are blank (as in not defined). The old Coppermine Gallery table (cpg132_albums) has a couple of columns: 1. title = Album name 2. owner = member id The new Invision Power Gallery table (ips_gallery_albums) has the following corresponding columns: 1. album_name = Album name 2. album_owner_id = member id Where the left hand value before the = sign is the database column name. What I need to do is some kind of lookup for the old table, where I extract the title and owner columns and then insert them into the album_name and album_owner_id columns. Although interestingly I tried do manually update the album_owner_id value via phpmyadmin UI and it didn't seem to work... I'll look into that. Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted May 20, 2015 Administrators Share Posted May 20, 2015 Thanks mate, I actually discovered that the User IDs have changed between the old software to the new software. I ran this command. update ips_gallery_comments set comment_author_id = (select member_id from ips_members where ips_gallery_comments.comment_author_name = ips_members.name) where ips_gallery_comments.comment_author_id = '0'; Which seems to have updated the comments table to include the correct author IDs. Just got to work out how to do it for the owner IDs. Worst case scenario I'll do it in a manual fashion...there is only about 300 unique rows. Quote Link to comment Share on other sites More sharing options...
George Posted June 17, 2015 Share Posted June 17, 2015 How is this going Gav? Can anyone help you possibly if you're stuck or lack the time? Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted June 17, 2015 Administrators Share Posted June 17, 2015 I've got a friend at work who was going to help with some SQL queries to make the migration over. Just been a combination of busy, a bit sick the last week and when I have spare time working on the car for fear of it stagnating. Quote Link to comment Share on other sites More sharing options...
brent012 Posted June 17, 2015 Share Posted June 17, 2015 I'm happy to help with writing some SQL queries if you you need any help. I've got a fair bit of experience with SQL through work (software developer). Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted July 13, 2015 Administrators Share Posted July 13, 2015 Minor update on the weekend I managed to get the gallery album's associated with the correct members in the new software. A few hiccups where multiple users had given their albums the same name, about 40 or so albums with the album name "new album" for example. Those will have to be manually adjusted and assigned to the correct users later... Next steps. 1. Associate individual images with their respective owners (I'm hoping the album table and images table share a common key to make this easy). 2. Associate the comments on images with the correct user (these were lost during the migration process). 3. Once that's done, get the latest database dump from the live site, convert the latest instance over to the new software, upload the new software to a development sub-domain and get Auszcar users to test it out and see what they think... 4. Iron out any kinks and then I'll replace the live site with the new software. That will take care of 2 pieces of software becoming 1 piece. Then for the front page articles I've have to transition those over so go from 3 pieces of software down to 1. This will make updating and maintaining the site much easier. I've noticed both Classiczcars.com and Hybridz.org use the same software as we are moving to, so those who use those forums should be able to adapt pretty quickly. I do find that Classizcars.com is a bit harder to use these days. I hope to try and keep things as simple as possible. Eventually once everything is tied to 1 piece of software moving domains permanently to Auszcar.com should be easy enough..I hope. Quote Link to comment Share on other sites More sharing options...
Moderators PB260Z Posted July 13, 2015 Moderators Share Posted July 13, 2015 Hi Gav You have been busy. I for one did not realise just how much work goes on the behind the scenes to keep this forum running. Thanks for all the effort. Cheers PB Quote Link to comment Share on other sites More sharing options...
andyk_79 Posted July 13, 2015 Share Posted July 13, 2015 Maybe those of us with some tech know how can help through a "working bee" type of get together - tie it in with a drive day/bbq or something ? ditto to the appreciation for all of your efforts Gav! Quote Link to comment Share on other sites More sharing options...
d3c0y Posted July 13, 2015 Share Posted July 13, 2015 Forum LAN party! Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted July 14, 2015 Administrators Share Posted July 14, 2015 Need a little SQL help here... It should be simple but I keep getting errors...Rather than post the SQL statements I've tried and adding confusion I'll include a couple of photos of the database tables... ips_gallery_images - This table is for all the images in the photo gallery. The problem is the column image_member_id is 0 for all images. (Dangling reference?). But it does contain an image_album_id which determines the album they belong to. (foreign key). See attachment ips_gallery_images.png. Now if we look at the ips_gallery_albums table, we can see columns album_owner_id and album_id. See attachment ips_gallery_albums.png So what I need to do is: Set all the images within an album to have the same owner id as the parent album. How would you structure this SQL statement? (Provided a few may already be correct having manually set them, so a where image_member_id=0 might be a good idea here). Quote Link to comment Share on other sites More sharing options...
andyk_79 Posted July 14, 2015 Share Posted July 14, 2015 UPDATE ips_gallery_images SET image_member_id = (SELECT album_owner_id FROM ips_gallery_albums WHERE ips_gallery_albums.album_id = ips_gallery_images.image_album_id) WHERE image_member_id = 0 Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted July 14, 2015 Administrators Share Posted July 14, 2015 Brilliant that appears to have worked. I had originally tried something like this. update ips_gallery_images set ips_gallery_images.image_member_id = ips_gallery_albums.album_owner_id where ips_gallery_image.image_album_id = ips_gallery_albums.album_id; My problem is I'm not always sure how to structure a statement and when I need to use a select statement or not? Quote Link to comment Share on other sites More sharing options...
George Posted July 14, 2015 Share Posted July 14, 2015 UPDATE ips_gallery_images SET image_member_id = (SELECT album_owner_id FROM ips_gallery_albums WHERE ips_gallery_albums.album_id = ips_gallery_images.image_album_id) WHERE image_member_id = 0 I concur... Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted July 15, 2015 Administrators Share Posted July 15, 2015 Thanks George for the sanity check. Quote Link to comment Share on other sites More sharing options...
andyk_79 Posted July 15, 2015 Share Posted July 15, 2015 My problem is I'm not always sure how to structure a statement and when I need to use a select statement or not? Pretty much any time you are updating to a value that comes from a different table (or another row in the same table) you need to use a select statement. The issue is that if you simply reference the other table in your update statement you are creating a join. Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted July 15, 2015 Administrators Share Posted July 15, 2015 Thanks Andy, Just so you can see the progress. You can see most album's now have an associated owner. Also you can see that the images inside these albums are now associated with the correct owner of the album. You can see that "new album" or "240z" or albums of a very generic nature are missing references, this is because there was no other unique key (or foreign key) I could use to match the owner with an album, at least not without a lot of work. I'll manually update these later so they have the correct owners etc.. Now I have to figure out why the comments are not displaying below images. I can see the entries in the table, but there is something else preventing the comments being displayed. I think it's a count in the images table... Quote Link to comment Share on other sites More sharing options...
Administrators gav240z Posted July 18, 2015 Administrators Share Posted July 18, 2015 So I figured out why comments are not showing... Turns out that during the migration process the comment, it's date stamp, it's author and author id were all correct, but the image id referenced somehow got modified and the image id is out of whack. I've tried to see if there was any pattern to this modification but so far the numbers are all over the place. I can't work out why. I was hoping a pattern would allow me to apply 1 single statement to update them all to the correct image_id but at the moment I'm doing it manually (all 500 of them). Which is a pain in the arse no doubt but it works. Here is an attached example showing the comment appearing under the image correctly. An arduous task but I'll just do it in batches of 50 over the next few days (to save my sanity). Quote Link to comment Share on other sites More sharing options...
GongZ Posted July 18, 2015 Share Posted July 18, 2015 Its looking good Gavin. I am really looking forward to seeing it go live. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.