Hi Terry,

Here are some old blogposts that shows the database, but as I said they are old, so there can have been changes since.

http://blog.hendyracher.co.uk/umbraco-database-part-1/ and http://blog.hendyracher.co.uk/umbraco-database-part-2/

But I think this can gives you an idea on how the database tables are connected.

Hope this can help you.

/Dennis

Thanks very much Dennis

I think you'll find that approach rather problematic Terry - as the split you are looking for isn't as clean as you might like.  For example umbracoNode contains records for content, but also data types and document types.  And as it uses integer primary keys, you'll find clashes between what you've added as a developer in your development environment and the content users have added in production.

If you solve it I'd love to hear!  But just wanted to flag the difficulty in case you weren't aware.

Regards

Andy

Andy, 

Thanks for the heads up.  Yes I can now see the problem.

Looking at
http://blog.hendyracher.co.uk/umbraco-database-part-1/
http://blog.hendyracher.co.uk/umbraco-database-part-2/ and
http://blog.hendyracher.co.uk/wp-content/uploads/Umbraco-Schema-with-Constraints-v2.gif

There appears to be only three areas of cross over:

>  First and foremost the umbracoNode table.
> Second and Third are two constraints that span the Orange-Green divide in the schema diagram from the last link above.  It seems to me that these are less likely to be a problem because the IDs are created on the green side and just referenced from the Orange side.  I am not sure but there may be an issue if an ID on the green side is deleted on your dev machine and cascading deletes is enabled, then the related notes would be deleted on your dev machine but would remain orphaned on your production machine so you may have to write a script to clean up orphaned rows on the production machine, this would not be difficult.

But the main issue (as you say) is the umbracoNode table. And the issue here is the overwhelming likelihood that during development, “Document Type” nodes will be added to this table (with links to the “Green Side”)  and that meanwhile, on the production machine, “Content” nodes will be added to this table (with links to the “Orange Side”).  Thus, unless the Umbraco Developers got smart and allocated umbracoNode  “id” Primary Keys from different integer ranges or pools (which they may will have done – I note that some are negative and some are positive) then the likelihood exists that you would get id conflicts between your dev machine and your production machine. This would cause complexity if you tried to deploy updates to the umbracoNode table from dev to production.. 

If on the other hand IF they did allocate id’s from different pools then you should just be able to use the umbracoNode table from the dev machine to replace or insert rows in the production umbracoNode table only where  IDs are in the “Document Type”  ID Range, thus not interfering with content.

Does anyone know if Umbraco allocates umbracoNode ids from different ranges or pools depending on what type of table it is initially and primarily being linked to ?

Any other thoughts or comments welcome.

Also, the issue of wanting to deploy and update Document Type (Green Side) data from developer, test or staging servers to production without disturbing content (Orange Side) on production server must not be a new problem.  How have do most Umbraco users deal with that ?

Terry Clancy

Yes, it's a challenge for sure - one of the trickiest things about working with Umbraco in my experience.  There are a number of options you can look at but for me none of them are really ideal.

• Courier is the tool Umbraco HQ provide.  But as you've described it's a really hard problem to solve, and when we looked at it in my agency there were just enough problems to mean we didn't trust it fully and so now don't use it.  It's been a while though so don't take that that as gospel by any means, you should definately investigate it further.
• uSync is a package that serializes document type changes to XML files, so other developers and potentially other environments can pick them up and reinstate them.  It's a good soluttion to the problem, but again there are hard things solve here around renames and deletions - so whilst we use it to share between devs again I don't completely trust it as a deployment solution.  It needs a manual check at least.
• One still reasonably automated way is to create a package from your dev environment, include all the new document types, and restore it on the staging or production environment.  Updates to existing document types are more tricky here though.
• And lastly there's just - take a careful note of what you've done and do it again on staging and production!  Not very smart... but if you are careful about things and ideally release little and often, it works.

is working on a reply...