Thread

Hi, I was wondering if anyone had any advice on my current EE database versioning routine, I’m worried I might end up causing myself a lot of headaches with this so I thought I’d better share and get some feedback.

In order to keep my local DB and remote DB as close to the same as possible (at least with the important stuff), I’ve had to hack the mysql driver in CI specifically the _execute method on line 178 I’ve add $this->_log_query_vcs($sql); The idea is that I log any important changes to the DB as they happen local and remote and diff the file, then import the changes.

Here’s my _log_query_vcs method:

/**
 * Hack to log queries to file fo version control
 *
 * @access private
 * @param string an SQL query
 * @return void
 */
 function _log_query_vcs($sql)
 {
  $test = trim($sql);
  if (
   !preg_match('/^SELECT/', $test) && 
   !preg_match('/^SHOW/', $test) &&
   !preg_match('/^UPDATE `exp_sessions`/', $test) && 
   !preg_match('/^INSERT INTO exp_security_hashes/', $test) &&
   !preg_match('/^DELETE FROM exp_security_hashes/', $test) && 
   !preg_match('/^DELETE FROM `exp_sessions`/', $test) && 
   !preg_match('/^INSERT INTO `exp_search_log`/', $test) &&
   !preg_match('/^INSERT INTO `exp_search`/', $test)
  ) 
  {
   file_put_contents('db.log', "$test;\n", FILE_APPEND);
  }
 }

I’m checking the query for a few keywords and excluding them from being logged if they match. My worry here is that my log file would be absolutely huge after a few weeks.

Is this approach madness? With a few more checks, could it be workable?

Hi there. While I’m unable to help directly, the latest episode of the EE Podcast was all about version control and may trigger something for you?

Cool, I’ll check it out, thanks.

In many cases the simplest thing is to export & import the database between instances.

If your config files are set up the right way, you can just dump and blast from your local instance to the remote, or vice/versa. The config file setup is explained nicely at http://net.tutsplus.com/tutorials/php/apply-the-dry-principle-to-build-websites-with-expressionengine-2/.

If your config files are set up the right way, you can just dump and blast from your local instance to the remote, or vice/versa.

I’ve got a great config file setup, and a pretty robust git strategy for deploying from develop, staging and release.

My problem lies in channel data. When adding stuff to the site locally, there are additions to channel data. On the live site, there are user contributions that go into channel data. I need to get my changes up there without disturbing the user contributions.

How do you reconcile the two?

Any ideas on this?  I’m running into the same issues as cbad

What’s the point you want to do that?

There is no “version control” on database. Data is added at every second. Even if you have versions for database scheme, you can’t have whole data’s versions.

“You can not run into same river twice”.

For you specific case, I think there is no point to take care of session table and hash table even for database backup. The two tables can be emptied without affecting site running. In fact I use cron to empty them everyday.

Here’s a scenario.  We’d like to have an N-tier setup.  With an open development server, a limited staging server and a production server that’s locked from people making live changes.

We don’t have the most HTML-savvy folks updating content (channels) and sometimes while they make changes on the production server, they mess up, and doh, those errors are out there immediately.  I’m looking for a way for users to make changes on the dev servers and be able to play around there, then be able to “push” the content when it’s ready to go.

Now with one user, it’s manageable, copy the entire database.. But when you add multiple users updating multiple things, you don’t want to push EVERYBODY’S changes at once (some people may have changed things on a dev server, but it’s not finalized), only those that a user marks as ready.

I’m actually in the process of developing such a plugin form EE.  (Right now it will only operate on channels)

I see your points. It is a little pain to push such “setting changes” to living server.

A common case is, to make some changes on site, you need to both alter the template and CP settings.

My solution is to record them in plain text, and realize them manually on living sever after satisfaction in staging server. The plain text files have version control, together with templates. You can also mark them as “done” after manually changes.

That is probably the only way to do. There are too much fields involved in CP setting, not only EE itself, but tons of third party modules. I don’t it’s viable to make such a plugin.

Besides, it’s against the principle to push database upwards. Data can only go downwards. Otherwise it’s dangerous.

Summer Student:  Again, my case is probably the simplest case available our site is pretty simple, but vast.  We have most things setup as basic channels so database-wise we’re only working with the handful of exp_channel* tables. When this “channel pusher” is the ONLY process modifying the production database, it’s easy to keep things in sync.  Templates [saved as files] are already controlled by myself with a svn version control push process.  Again, we’re trying to make this as easy on the end-users (think administrative assistants) that are updating simple channels. 

I’m not going to argue for-against principals of data-flow, the simple fact is that we need a dev and staging environments where end-users have a sandbox to make their changes, and a non-invasive migration process that our QA people can use to migrate these changes in a controlled fashion to the production side.

Too many people have messed up live content (typos, etc) and I’ve had to then spend half of my day fixing their problems.  My job is to actually develop useful applications, and new functionality for our sites, not to fix simple content problems.

If the concern is for content only, that’s simple. Content should be taken care of by editor, not developer. Just make their content pending before an editor’s approval. That’s done. There should be nothing about database.