Jump to content
PeterAllen

MAKE A DONATION to maintain this site - See the 'Home' page for details

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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!

46072_3b25ae971355b269637a49ef853081c445b1cf76.png

Share this post


Link to post
Share on other sites

In fairness most of the time the site runs itself

 

Way to modest mate  :)

Share this post


Link to post
Share on other sites

Way to modest mate  :)

 

Thanks mate, does the site seem any quicker to you today? In terms of response and page load times?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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).

 

 

 

 

47560_02c9598c992f74a4b2109c9eaa4c4d541c403e40.png

47562_48015fd442c137aa7d78d90b95898fb145974759.png

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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...  ::)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

47566_2042c45b906ba137359108b1907d9f39be1e344b.png

47568_e3c28d6b8364f157285a458c981ce280ede43b6d.png

Share this post


Link to post
Share on other sites

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).

47586_40aa3e1ef958d23bda469e99ab3717bae0bfbb3a.jpg

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×