Symphony.

@remie, the flags mentioned above are invented to make master/master replication possible, and simply put that's what we are trying to do here.

If there was a way to tell mysql to only replicate on demand then we could let it figure all out, it has mechanisms in place which will do pretty much exactly what the CDI does, but for everything in the database.

However, the reason this on demand sync function is not available is simple: normally you want to keep the data on both tables as up-to-date as possible, to avoid the merge conflicts mentioned above. If I were to edit an entry while somebody edits the structure, two things can happen: I save the data after the structural update is done, which will give me an error when saving, or I can hit save before the update, which will save the data, then truncate it when the structural change is processed. So far so good in theory, but when the latency isn't a few miliseconds (or seconds), but hours or days (as with a normal release cycle), it means the databases have a lot of time to diverge from each other, so there is a lot of time for actual merge conflicts to emerge.

Hope I'm not rambling too much, and you'll be able to make something of this;)

@creativedutchmen quite a good explanation in fact :) I've just tested this briefly yesterday on my local copy; two separate databases and merging on different git branches. Seems to work fine; I'll create a branch on my own fork of CDI and try explain how it all works.

As regards conflicts something that also comes to mind, which I'd like to identify... is when you save pages & link up datasources.. if you have branch on which you attach a datasource to the homepage for example... and another developer attaches another datasource (not having yours) most likely when merged in (whatever order) you'd have one of them missing as the SQL just updates the whole list if not mistaken.

Can I just check that this extension has had it's folder name changed to lower case?

Unix filesystems are case sensitive, and every time I install this extension Symphony complains that it can't find the extension.

Started some work on a branch in my fork I'll keep you updated on its status. Some simple tests today shows it works indeed; I'll have a go at it tomorrow with a colleague of mine so we have to separate databases and see how it goes.

I think I just have to cater for merging into Master mode instances, as we'll probably be both on master instances.

So I think I'm happy with the overall solution; so far. Will be installing it by default on all systems we run/have. Testing so far seems quite promising; had to make some minor alterations & add a delegate.

I'd appreciate if someone would give it a try and give some feedback on this branch.

Also if you want to use the multiple-dev feature you'd need to set up offsets in the mysql.conf, my.cnf or my.ini depending on your setup and the following lines have to be added under the [mysqld] section.

# The current machine unique offset
auto-increment-offset = 3
#The "Total offset" same for all your machines
auto-increment-increment = 10

I can't install this extension. I get this error: (Using Symphony 2.3.2)

MySQL Error (1048): Column 'version' cannot be null in query: INSERT INTO `sym_extensions` (`name`, `status`, `version`) VALUES ('CDI', 'enabled', NULL)

I have changed the folder name from CDI to cdi (extension won't appear in Symphony unless this is changed).

Anyone else using this extension? I've been using it just fine for several months, but suddenly some fields in my sections won't sync properly. Specifically, it looks like adding Entry Order fields does not sync, but adding other fields like Text Input syncs fine.

Specifically, the Entry Order field I created only gets this line in CDI

"116":[
  1371847945,
  22,
  "cdc28e9578e18456c1e1c9cd4710d299",
  "CREATE TABLE IF NOT EXISTS `sym_entries_data_151` (ntttt  `id` int(11) unsigned NOT NULL auto_increment,ntttt  `entry_id` int(11) unsigned NOT NULL,ntttt  `value` double default NULL,ntttt  PRIMARY KEY  (`id`),ntttt  KEY `entry_id` (`entry_id`),ntttt  KEY `value` (`value`)ntttt) ENGINE=MyISAM;"
],

while a random field I created gets all these

"63":[
  1371847842,
  19,
  "76f4f933df56d3aa20f14e1043710c51",
  "INSERT INTO `sym_fields` (`label`, `element_name`, `parent_section`, `location`, `required`, `type`, `show_column`, `sortorder`) VALUES ('Random', 'random', '18', 'main', 'no', 'input', 'yes', '7');"
],
"64":[
  1371847842,
  20,
  "efe0dcc71a8c94c12eb8c1e118c51737",
  "CREATE TABLE IF NOT EXISTS `sym_entries_data_150` (ntttt  `id` int(11) unsigned NOT NULL auto_increment,ntttt  `entry_id` int(11) unsigned NOT NULL,ntttt  `handle` varchar(255) default NULL,ntttt  `value` varchar(255) default NULL,ntttt  PRIMARY KEY  (`id`),ntttt  UNIQUE KEY `entry_id` (`entry_id`),ntttt  KEY `handle` (`handle`),ntttt  KEY `value` (`value`)ntttt) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"
],
"65":[
  1371847842,
  21,
  "aa14995008f5d006b929765559b170ca",
  "DELETE FROM `sym_fields_input` WHERE `field_id` = 150 LIMIT 1;"
],
"66":[
  1371847842,
  22,
  "c784aa2e0a5f771c0fe283a81d98e782",
  "INSERT INTO `sym_fields_input` (`validator`, `field_id`) VALUES (NULL, '150');"
],

@Pat: I'm using it on a daily basis, but unfortunately not in combination with the Entry Order field.

The way the CDI extension works is basically derived from the DBSync extension created by Nick Dunn. It does a regex match for certain keywords in the SQL query to decide if it should log for synchronisation. That is meant to filter out content queries.

Perhaps the Entry Order field has custom SQL queries which are not matched by the regular expressions?

@remie:

I'm experiencing some problems with this extension. For example, I added a Date field and a Reflection field to a section in my local server, but then deleted it. However, CDI did not log the deletion, and so my live server had extra fields. I had to delete them on the live server manually.

This may also be related to the fact that CDI does not catch when Entry Order fields are added. However, this is inconsistent as I clearly remember that I have created an section before with Entry Order fields and it worked fine.

I'm not sure where exactly everything started getting messed up... Could it be another extension interfering with CDI?

@remie & @pat, for the Entry Order it's quite simple... the table name is order_entries and there was a slight mistake in the regex... so it was not saving...

@gunglien: Do you know how to fix it? :)

yes you can find a pull request on the repo here; or just use my integration branch.

sorry if this is a duplicate question, but I'm still trying to fully understand the workflow with this extension, and to a a degree, nick's db sync extension.

So with any website, you have your production build and most likely your client will be working on updating entries there. Then what would the typical workflow be to make sure that any concurrent development work does not stomp on any production work?

From my understanding, this extension is a one-way workflow:

Dev -> Staging -> Production

I already started out with a base db sync from production down, so essentially I had my production data on all three of my environments. Or is it common practice to move the production db down to staging and then apply the cdi.sql changes there to make sure that nothing gets stomped on? and then do the same up to production.

Also, after any sync, do you delete the cdi.sql file, similar to deleting the db_sync.sql file for nick's extension?

Everything you said sounds right. You don't have to manually delete the cdi.sql file. You can click "clear" from the admin interface, or just keep it there. CDI logs which queries have been run in the database and skips those in the future.

so @pat, you're saying that if i change something on production, i don't need to sync it down to my local before I continue with more development work? This should always be a one-way sync?

Depends what you mean by "sync." You don't necessarily have to download your production data to local unless you want to see what your site looks like with recent data. You can have a workflow completely one way just using CDI to push database structural changes from local to production.

i think i glazed over the "just structural changes" aspect of this extension. So if my client is just updating entries, adding images, etc and not actual structural changes (ie. editing sections on production) then the dev -> production workflow should most likely be fine?

edit:
right after i posted this, I ran the cdi extension on my staging env and it seemed to worked fine, but then I realized new sections weren't picked up in my staging environment. aren't those supposed to be the structural changes?

I still had the db_sync extension enabled and was looking at the sql generated, but there seems to be now mention of sym_sections, just sym_sections_association. I know that this is not the thread to see if that is a bug, but since the cdi extension was (from what I gather) essentially born from the db_sync, I figured the same or similar logic to output the sql was used. so i guess i'm wondering - why would just the sym_sections not get updated when doing the syncs?

Theoretically, your new section should have been created once you hit the synchronize link.

In my experience, CDI has failed to record certain changes like new fields or deleted fields. Unfortunately, it doesn't seem like CDI has been actively maintained for the past 3 months. The only solution currently is the fix anything CDI missed manually through either the database or the Blueprints interface.

@Pat very strange; I haven't had much problems if any, with CDI.

I admit there were a few bugs; which when noticed I submitted fixes for. however been working with it over 3 months no major problems unless you delete the file itself by mistake.

Note to the above (I'm using a multidev version on my own branch) most common cases for things not working is that saving config sometimes might delete your CDI file with the original.