Succesfull merging of multiple galleries

[ff]

I've successfully merged three galleries into 1.
With this post I'd like to explain what I've done.

My old setup was.
3 galleries with 2 static themes and 1 with free to choose theme
In total there were 3 accounts:
#1 in all three
#2 in two of them
#3 in one

#2 isn't used anymore, so this one could be gone.
(because it are personal albums all are admin)

I wanted to have 1 gallery with categories for the different galleries.

Changed/Used tables with records (note in my case I didn't change all of them)
_albums : aid, category
_banned : ban_id, user_id
_category : cid, owner_id
_comments : pid, msg_id, author_id
_ecards : eid
_favpics : user_id
_hit_stats : sid, pid
_pictures : pid, aid, owner_id, owner_name
_usergroups : group_id (only if you've created own groups)
_users : user_id, user_group
_votes
_vote_stats


For the changes in sql I use phpMyAdmin.

Step 1) updated all galleries to the latest version (1.4.19 in my case) and check it for errors.

Step 2) create a new gallery at the desired location with the same installation file
Step 2a) when creating the database choose a new prefix
Step 2b) check your new album

Step 3)
Check the mentioned tables and records of your old albums for the highest record numbers.
We don't want any duplicates.

Note:
_categorie
I added 10000 to all from album 1 and 20000 to all from album 2. Album 3 remained unchanged.
This almost did the trick, but i lost some categories.
All categories within the 10000-ranges became user-/personal categories.
User 1 has gallery 10001 and so on.

I corrected this part by writing down the different cid's and giving some of them a new cid following the other ones.
This works when having a few categories like I did (<50). Otherwise add as much as your highest cid.

Example:
alb 1 : highest cid = 400
alb 2 : highest cid = 134
alb 3 : highest cid = 10

alb 2 : cid = cid + 400 (gives highest cid = 534)
alb 3 : cid = cid + 534

Note 2:
For the smaller tables I just changed the id's by hand.

Note 3:
The 'last uploaded' thumbnails are sorted by pid. The highest pid is the latest upload.
I didn't know this, so the oldest images are now my 'newest'.
I'm to lazy to change them all, so I'm leaving it this way (for now;))

Step 4)
[SQL] copy the mentioned tables from database 1 in the new one
Select the table.
Click the button 'Operations'
Subject 'Copy table to (database.table):'
Select the new database and enter an original name (like cpgalb1_albums)
Select 'Structure and data'
Deselect the rest
Click on the button 'Go'

Step 5)
[SQL] select the new database and click on the 'SQL' button
Run this command to change the records as mentioned above

UPDATE table SET fieldname = fieldname + value

example:
UPDATE newalbum.cpg_pictures SET cid = cid + 400

Step 6)
Move all records into the new tables.

Click the button 'Operations'
Subject 'Copy table to (database.table):'
Select the current database and enter the correct (=new) tablename
Select 'Data only'
Deselect the rest
Click on the button 'Go'


Step 7)
Check your new gallery to see if it's still working.

Step 8)
Goto step 4 ;)

In my case after completion I had some stuff to work on.
1) Some categories were missing, see step 3
2) I created new categories for the old galleries by hand en moved the albums to them
3) I wanted to have the old galleries (now categories) to have their old theme.
I copied them to the new gallery themes-directory and checked them to be working.
Next I applied this mod http://forum.coppermine-gallery.net/index.php/topic,22781.msg279925.html "[MOD] Sub-themes".
I have some problems applying the themes from the configuration screen, so I entered the data directly into the database.
4) I forgot to have some MODs applied to the old galleries. Just apply them in the new gallery.

I hope I got everything written down, if you've got any questions just reply to this thread.

[ff]

I'd like to add this warning from GauGau

Actually merging the database records of different galleries into one falls into the category "nearly impossible", as the IDs that are suppossed to be unique will exist in both galleries. This is the case for almost all key fields in all database tables - coming up with a converter that will merge two galleries into one has not been accomplished and is very far from trivial.
Merging galleries with only one admin uploader and FTP-upload plus batch-add will be comparatively easy. Merging galleries with a lot of user interaction will be hard to impossible.
I strongly doubt that a converter or detailed documentation how to perform a merger will come from the devs (as we're busy with other things), but we would of course welcome any contribution from users.

Joachim

http://forum.coppermine-gallery.net/index.php/topic,57135.msg280038.html#msg280038