Thread

I’m starting work on a site that is already in EE (currently running EE 1.5.2). I’d like to set up a development subdomain to work on the updated version of the site (dev.site.com).

I don’t want to work with the existing database at all because I need to update the site to 1.6.4 and add a lot of extensions and modules, alter the weblogs with new fields, etc, in short - a LOT of changes - and I don’t want to interrupt the client’s current usage of the site. Plus, the client is big enough that there is some level of bureaucracy involved, so I can’t just go nuts on the live site (like I’d really like to do…)

My initial thought was to, at a certain date known to everyone involved, completely dupe the existing site into the dev subdomain, and dupe the DB as well. At that point, I can do whatever I need to without worrying about messing with the original site or original DB.

The problem with that method is that the client adds 1-5 articles a day, and they receive comments on articles as well. At the day/time I duplicate the DB to the time we launch, every article and comment that is added will have to be duplicated to the development database, which is an extra pain. Development of the updated site will take about 2 months, so we’ll have two months of manual duplication involved. Instead of doing this manually, it would be superb if, maybe once a day, anything that was added to the main production DB was cloned over to the dev DB that I’ll be working on.

As Brandon Kelly states in this post regarding his company’s build of Navigant Consulting:

...we’ve got one shell script that dupes the DB (except for a couple tables), doing a URL search & replace in the process, and another one that runs rsync on several of the directories. It’s worked great so far.

Has anyone else done something similar? Anyone got a shell script they’d like to share so I can see what tables were ignored, and how the URL search replace works?

The issue I see is that I’ll be altering the structure of the development database and a perfect clone from a production 1.5.2 DB to a dev 1.6.4 DB won’t work. I could update their live site to 1.6.4 so the tables structures are more or less the same, but I think some modules that I have to install (like Solspace Tag) add columns to existing EE tables.

I’m looking for any advice or experience here, as I’m not totally sold on the duplication method I’m contemplating. I have yet to fully start the development, and don’t want to jump in unless I’m confident in my decision on how to proceed. Am I making this more difficult than I need to?

Thanks for any input!

EDIT/ADD: Here are some related posts.

1. database sync troubles
A post from Nov 2007 that outlines syncing troubles with EE pretty well, that was never answered.

2. set up development environment in a subdomain
Describing how a dev/production site running different versions can share the same database, but different tables.

That raises an interesting question: if the dev/production sites were using the same DB, but diff tables (with a diff prefix), couldn’t an extension be written that submits to BOTH sets of tables when a comment or new entry is made? Has anyone done anything like that?

I should have named this thread “free MacBook Pros” or “free doughnuts”, because traditionally threads that have anything to do with duping databases or development setups get a lot of crickets!

I may look into the “submit into two tables in the database” when submitting an entry thing.

I feel your pain about the cricket posts, i have a few myself! I wish I could help you too—this is something I’m fairly interested in learning the best way to approach as well. My guess would be to automate a cron for exporting the day’s posts and updating the dev db, remapping the table names (as long they don’t keep changing), but it’s a very naive response as I really haven’t tried to flesh it out yet. Best of luck!

I don’t see the problem of copying the existing database (once) and using that while developing for the next two months or so. When you feel like you’re finished get a fresh copy and apply all changes to that. If all goes to plan, like it will 😉, then apply changes to the live database.

hmm… I think this is difficult situation and you are not exaggerating the difficulty.

I am not a DB guru but I can tell you that no matter what solution you choose it is always easiest to work with two apples instead of an apple and an onion. So having said that I would update their current site to 1.6.x. I think that will make things much easier.

I am not familiar with MSM but would making their current site part of an MSM cluster allow you to share data and develop using a different address?

Combing through the DB after 2 months of entries would not be easy but it doable. The autoincrement and dependency between entry and comments is pretty straight forward. So doing it this way(again with both DBs at the same version) would be a little less risky.

Another option would be to hire a couple of high school students to port all of the content manually 😉

Ryan,

I think you need to figure out the exact table structure changes upfront a little better—remember that you don’t have to deal with the whole EE database. If you’re just dealing with entries and comments, you’re essentially dealing with:

exp_weblog_titles
exp_weblog_data
exp_relationships (if they have related entries)
exp_comments
Edit: and potentially:
exp_category_posts if they categorize entries
exp_members if comments are left by members

If you don’t need version history on entries, don’t bother with exp_entry_versioning—and this also assumes they won’t be adding custom fields, changing categories, etc…during this process (in which case you’d need to sync that data also). I could have missed a vital one above but I don’t believe so. A good check—export your database. Then add one entry, and one comment, and export again. Then run a diff—you should only see changes to the above tables (or a limited set if no related entry fields, categories, or other are involved).

I would upgrade the live site to 1.6.4. Then, look in the different modules and extensions and see what *exactly* they add to the database. I’ve never personally used an addon that modified those very core tables (that I know of!) but that doesn’t mean they don’t exist. If those four tables aren’t modified, then you should be able to export them directly from your 1.6.4 live installation to your dev one.

One potential issue is if an extension adds rows to its own table whenever a new entry is saved, even if data for that particular extension isn’t entered. That shouldn’t be the case though—I know at least lg_better_meta doesn’t do it—but it’s important to check.

Depending on how big the tables are, perhaps a script to export just those tables every night and then import them into the dev DB? If they’re small enough, you can backup the dev DB, then drop the tables and import from scratch so you don’t have to deal with syncing only changes. This is similar to what Brandon says, but you may not need to do a URL search/replace if they’re not directly linking to the live site URL—or if you didn’t mind some URLs like that (although that’s the great thing about using {homepage}).

Does any of that advice help at all? Should I elaborate more on anything? Am I way off-base?

Edit: edited to add two tables to the list above that may come into play depending on the site. Again, submitted an entry and a comment, then running a diff on the database export, is a good way to figure this out.

Sincerely,
Matt

Thanks for all this feedback. A lot of good ideas. Here’s a question on a slightly different tack: would you duplicate the database so that you had tow completely separate databases, OR would you possibly duplicate the tables within the same database, using a different table prefix for the new tables? I ask because I’m sure it would be possible in some way, if the tables are in the same database, to write an extension that would submit new articles to both sets of tables.

Fortunately, the client doesn’t see a big problem duplicating content for 1-2 months, so this whole discussion might be a moot point, but I think these discussions are good to have because EE is so database-driven, people are bound to come upon these situations every so often.

This may be another approach, but could work. I’ve only done it once, and the site I did it for wasn’t adding lots of new fields or forms. But I just swapped tables. In our situation, there was no new content in the development database. There were five new fields, and almost everything else was modules, plugins, and templates. So we just DELETED the templates tables and replaced with the development templates table. I manually added the new fields, since it was just five. But assuming no new content is added to any new fields, it shouldn’t be problematic to have a script create the new fields for you, so they are awaiting use.

The tables you don’t want to touch are primarily weblog_data and weblog_titles. Maybe category tables, if modifications have been made there. But it seems to me, almost all other tables can be replaced with the production tables. And even if you added fields to weblog_data in production, you could just use a SQL script to adjust that table, since there shouldn’t be any new data. Of course if there is new data in weblog-data table for development as well as the live table (i.e. relations, etc.) then this wouldn’t work.