Moving photos between galleries

Reply

geneder replied on Fri, Aug 10 2007 1:47 PM

rated by 0 users

Ben,

Thanks for the reply.  I'll check out the database angle.  I would be nice to be able to do this from the UI.

Gene

Reply

gavin.bee replied on Sat, Dec 29 2007 11:07 AM

rated by 0 users

Any success with the Database method?  This really seems like a UI feature that should exist.

Reply

geneder replied on Thu, Jan 3 2008 4:28 PM

rated by 0 users

I was able to make the changes in the database without issues.  I agree that there should be some way of doing this from the UI.

Reply

AllCammedUp replied on Fri, Feb 1 2008 3:08 PM

rated by 0 users

geneder:

I was able to make the changes in the database without issues.  I agree that there should be some way of doing this from the UI.

Would you mind to post the steps you used to do so?  It would be a big help and save us from potentially messing up our galleries...especially if you have a working method.

Reply

geneder replied on Tue, Feb 5 2008 12:36 PM

rated by 0 users

I have found that moving a photo between galleries involves doing updates on the following database tables:

• cs_Posts
• cs_PostAttachments
• cs_Threads

I have scripted a stored procedure that will do all the necessary steps as near as I can figure.  This will move photos and any associated comments from one photo gallery to another.  You will need direct access to your CommunityServer database.  My backend is SQL Server 2005 and CS Personal/Developer version is 3.x, Edition 2.  Things may be different for earlier or later versions.  Here are the steps you need to take:

1. Open your query tool or SQL Server Managment Studion on you CommunityServer database.
2. Either query or open the cs_Sections table and find the SectionID of the gallery you want to move the photo to.  You should be able to identify the section by the Name field.
3. Either query or open the cs_Posts table and find the PostID of the photo you want to move.  You should be able to identify the photo by the subject field, PostLevel and ParentID.  PostID and ParentID should be the same.
4. Use the script at the end of this post to create the cs_Photo_Move stored procedure in your CommunityServer database.
5. In your query tool run this command line:  EXEC cs_Photo_Move <PostID>, <MoveToSectionID>  substituting the values from steps 2 and 3 in place of the angle brackets.
6. Repeat for each photo you want to move.

I have found that the moved photo may not appear to be moved immediately.  There may be some caching going on but it does move eventually.  You can check the 3 tables mentioned above to make sure that the database (SectionID) fields have been updated.  Although this seems pretty straightforward, use this method at your own risk as it is based on my limited understanding of the database schema.  It would be nice to get some comment from someone closer actual database design.

 Hope this helps.

 Here is the creation script for cs_Photo_Move:

USE [CommunityServer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[cs_Photo_Move]
(
    @PostID int,
    @MoveToSectionID int
)
AS
DECLARE @ThreadID INT
DECLARE @SectionID INT
DECLARE @ApplicationType INT
DECLARE @ApplicationPostType INT

BEGIN TRANSACTION

-- First, get information about the post that is about to be moved.
SELECT
 @ThreadID = ThreadID,
 @SectionID = SectionID,
 @ApplicationPostType = ApplicationPostType
FROM
 cs_Posts
WHERE
 PostID = @PostID

IF @@ERROR > 0
 GOTO Error

--Get the new section ApplicationType

SELECT
 @ApplicationType = ApplicationType
FROM
 cs_Sections
WHERE
 SectionID = @MoveToSectionID

IF @@ERROR > 0
 GOTO Error

--Check to see that the post is a photo and the MoveToSection is a gallery

IF (@ApplicationPostType != 64) OR (@ApplicationType != 2)
 GOTO SomethingWrong

--Update the Post to the new SectionID

UPDATE
 cs_Posts
SET
 SectionID = @MoveToSectionID
WHERE
 ThreadID = @ThreadID

IF @@ERROR > 0
 GOTO Error

--Update the post attachments for this post

UPDATE
 cs_PostAttachments
SET
 SectionID = @MoveToSectionID
WHERE
 PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID)

IF @@ERROR > 0
 GOTO Error

--Update the threads for this post

UPDATE
 cs_Threads
SET
 SectionID = @MoveToSectionID
WHERE
 ThreadID = @ThreadID

IF @@ERROR > 0
 GOTO Error

COMMIT TRANSACTION

GOTO TheEnd

SomethingWrong:

PRINT 'The PostID is not a photo or the MoveToSectionID is not a gallery'
GOTO Error

TheEnd:

RETURN 0

Error:
IF @@Trancount > 0
 ROLLBACK TRANSACTION

RETURN -1