Cotonti / Open Source PHP Content Management FrameworkContent Management Framework

Forums / Craftwork / Server-side / merge the data base...

12>>>

i'd like to know your opinion about this...

Dave
#1 2012-01-16 17:25
Hello, im runing a site with 0.6.20 for some time, there is alos other site with same content, users would like to see merge both sites... Both are genoa

now my question... what u guys thinking about merging data bases... i already did something like this but there is much users / topics
i manage to learn struture of the cot d.b. and can do it "manualy"... would be there a way to do it with some software? any tips, guideing?!

My site have:
~ 500 users
~ 322 topics
~ 9212 posts...

other one:
~ 6000 users
~ 300 topics
~ 3000 posts
-Dave
Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl
Trustmaster
#2 2012-01-16 19:02

I doubt there is some software that can do it out of the box, so it's gonna be some tedious hours in front of phpMyAdmin.

The most problematic subjects are gonna be permissions (sed_auth table), plugins and primary keys (IDs) everywhere. If you make it so that sed_groups tables are identical on both sites (including the IDs, including group IDs in sed_groups_users table as well), permissions gonna be easier. Then you need to change user IDs in all tables in one of the sites so that they don't overlap with the user IDs from the other site, after that you'll be able to merge users and set auto_increment counter to something bigger than maximal id you get. Then it's gonna be a similar procedure for forums: make sure they have same structure including section IDs, then shift IDs in topics and posts of one of the databases and add them to the other.

After structure and users of the both sites are merged like that and both databases contain same sets of installed plugins, sed_auth table can be merged.

I may sound quite inconsistent, but it's really a big quest to give you a definitive guide in one shot.

May the Source be with you!
Dave
#3 2012-01-16 23:02
thanks for the reply Trustm., i didnt predict that ull also explain merge with other cot tables, my plan is to merge ONLY sed_users, sed_forum_topics, sed_forum_posts my fault, now explanation:

i already did 1 merge with d.b. from the phpBB, i made few mistakes that cost me alot of time after merging :)
basicly my idea this time (with small experience) is to:

1. MOVE ALL USERS to 1 group (i.e. users.php?g=4) from the site that will be merged
2. MOVE ALL TOPICS to 1 section, like: fs_id=4 from sed_forum_sections from the site that will be merged

and thats all, after that i can tell my moderators to start moveing topics to correct sections

also i'd i.e. if the D.B. of the merged site have user_id's starting from 1 till 6000, i'd ONLY add the "prefix":
something like:
10001, 10002 till 106000 (it can be done with the Notapd++ and "regular extensions", i already used it, it helped me to fix bbcode betwen phpbb and cotonti) after that only i'd have to:
UPDATE sed_forum_topics SET ft_lastposterid='10001' WHERE ft_lastposterid='1';
UPDATE sed_forum_topics SET ft_firstposterid='10001' WHERE ft_firstposterid='1';

-Dave
Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl
Dyllon
#4 2012-01-16 23:08
This could be achieved by a php script.. Assuming you know php.
We are what we repeatedly do. Excellence then, is not an act, but a habit.
Dave
#5 2012-01-16 23:20
thats the problem that I DO NOT lol but im smart enought to use any TOOL i have / know like notepad++ (i learned regular extensions for merge with the phpBB) and i.e. i think i can easly add that "prefix" to id's useing MS Exel so it can generate me sql command to replace 1 - 6000 with 10001 - 106000

i know its noobish or lame, but i think its the quickest way FOR ME with my weak knowlage of the php

feel free to leave any comment, it might give me some ideas
Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl
Trustmaster
#6 2012-01-17 07:09

It should be relatively easy then, if you move all topics to 1 section and move all users to 1 group, you won't even need to mess with sed_auth.

May the Source be with you!
Kilandor
#7 2012-01-20 14:35

That would be an easy way to do it, but don't forget to bump up the cardinality(auto increment) value of user_id in sed_users to be higher than that of the higest user ID

Dave
#8 2012-01-23 01:56

thank you for the tips and comments, my moderators/admins cleand other site data base so there is less forum topics but i decided to merge the whole sed_users (maybe some1 in the future will buy that d.b. :P) anyway

about sed_users, on my site there is 1 - 813 id's so i decided to simply copy / paste users from the other site from 813 till 6000+, for the rest simply change ID to something that will let me easly merge it, illl figure it out :)

now i have small question, how the cotonti pic an ID for the user? does it store somewhere the last ID that was given or simply it "checks" whats the next free user_id ?

im asking cuz im afraid that some things might get F**cked

Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl
Trustmaster
#9 2012-01-23 08:05

It's in table's AUTO_INCREMENT property: You can also get it this way:

SELECT MAX(user_id) + 1 FROM sed_users;
May the Source be with you!
Dave
#10 2012-01-23 09:26

thx man :)

im almoust done, any tip how to recount i.e. user_postcount FROM sed_users ?

Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl
Trustmaster
#11 2012-01-23 09:46
UPDATE sed_users SET user_postcount = (SELECT COUNT(*) FROM sed_forum_posts WHERE fp_postrid = sed_users.user_id);
May the Source be with you!
Dave
#12 2012-01-25 16:07

i dunno how to thank u... i can say only thank you.

the merging went fine and smooth, i had some troubles with doubled users / e-mails in the d.b. but its fixed now :0

Added 1 month later:

hey guys, after merging ive noticed a "thing" idk if i can call it a bug cuz everything is wokring OK, 

so, my site have like 6500 users, gm=4 however when i go to: admin.php?m=users (the members gm=4) count only 773 or something, i went to myadmin to see the rows in the data base but it looks ok, can it be some kind of a bug? 

i dig a bit and found the reason:

http://forgotten-garage.pl/datas/users/asd_1.jpg

is it something i have to worry about ? the red circle shows the reason

Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl

Dit bericht is bewerkt door Dave (2012-02-26 13:59, 8 jaren ago)
Trustmaster
#13 2012-02-26 17:09

If the main group is selected with radio, it doesn't need a tick in the checkbox, so that's normal.

Did you merge sed_groups_users table?

May the Source be with you!
Dave
#14 2012-02-26 18:11

nope, only sed_users, _topics, _sections, as i was writing before i simply moved every user to gm=4 before merge

Your advertisement here :-)

Cotonti Genoa based site: forgotten-garage.pl
Trustmaster
#15 2012-02-26 20:17

sed_groups_users records for membership in the groups is required, even if they have only main group

May the Source be with you!

12>>>