setting up a two-tier acceptance/production environment

One approach is to not use the id or title fields at all but to use something that’s unique across both(or all) databases, you could use a UUID for this, this would stamp each document uniquely in the universe and you could query by this as its a string.

The problem is you have to squeeze it into an existing field of the site content table, one you don’t or never will use, that’s a varchar of at least 255 and one MODx won’t overwrite. You mention hard links above so you may not be using aliases, you may be able to hijack this field for that purpose. This will cure the ’id clashing’ problem but would still mean writing the plugins etc. I think this would work, but it needs trying.

Borrowing ideas from the NOSQL database stable here, CouchDB(good kit this) springs to mind. I may raise an RFE in JIRA to add a UUID field to content type tables, it may come in handy for future Revolution tasks.

This would help versioning also if a version number was stored along with the UUID, so a single UUID could have lots of versions(again CouchDB). These could be stored in say a temp table thne transferred to the site content table when needed, any site content table anywhere in fact.

Done http://svn.modxcms.com/jira/browse/MODX-2099

Quote from: shamblett at Jul 01, 2010, 01:06 PM

Borrowing ideas from the NOSQL database stable here, CouchDB(good kit this) springs to mind. I may raise an RFE in JIRA to add a UUID field to content type tables, it may come in handy for future Revolution tasks.

This would help versioning also if a version number was stored along with the UUID, so a single UUID could have lots of versions(again CouchDB). These could be stored in say a temp table thne transferred to the site content table when needed, any site content table anywhere in fact.

Interesting thought. If I had a vote, however, I wouldn’t go this route. Adding UUIDs to the existing tables, where they don’t really belong, seems messy. (Adding this type of functionality to core tables in future seems like a bit of a direction change for the core.) A couple of new tables could probably handle resource versioning and help with this two-tier environment. With a couple of new tables, resource versioning could be also be developed as a third-party add-on to the current Revo core.

They have to be with the resource, as part of its data set. They can’t belong anywhere else. There is no functionality to add, just columns.

Also I’m not just talking about versioning here, they could be used to sync databases from say dev to production, or to help with the particular problem in this thread. What we don’t want to do is implement one method for versioning, one for syncing etc. all of which add more and more tables still indexed by MYSQL id et al.

You could easily add a setting that said ’let the core use these’ or not, if not you are free to write your own 3PC as you say, either way if you use UUID’s they will always be unique, across databases, regardless of what generates them.

I’m sure other uses will be found when they exist.

This is really interesting, and if done properly could be something big for the whole community.

Just some things on how I’d try to do this:
- Instead of duplicating a whole database... you’d only need a duplicate table(s) to copy over the resources imo
- Whereas the published resources use the ID as unique ID, in the versioning table the UUID as suggested earlier would be unique, whereas the published resource ID links them together (multiple stored versions per resource would be made possible in this way).
- Indeed on saving the form the changed data would be saved in the versioning table as well as the published table.
- A module (or in Revo Custom Manager Page) would loop through the different versions stored, providing an overview of the different versions. Idea: set a TV with the reason for the edit to display additionally in the module (and can be used in any template as well)

Anyway. I’m think I sort of got away from the original topic, which was about draft versions being pushed to production. Just wanted to share my thoughts anyway.

You could add a UUID to each resource with today’s Revo, but I don’t think the best way would be to add it to an unused filed in the existing table. That could break things in the future. And you’re right that columns can’t be added. If someone were to add UUIDs to their resources today, they should probably do it with a new table that has a relationship (aggregated?) to the existing resource table.

With versioning, I think separate tables are usually the way to go. Versioning can take up a huge amount of space even with 10 versions of each of your resources. Why have all that data in the main resource table? I wonder if versioning should be built into the core, anyway.

I’m not saying that the UUID thing couldn’t provide some cool features, even better versioning or better ways to set up work flows like discussed in this thread.

I’ve been thinking along the same lines as Mark, but instead of a single table with UUIDs, I thought versioning should be done with a couple of tables: one that stores resource data and another that could add some special versioning features, etc.

they should probably do it with a new table that has a relationship (aggregated?)

This is the bit I don’t like, what keys would you use? id, page title, longtitle, date aggregated together? If you can do this then you have a UUID already. its just a large composite key rather than being a generated UUID.

I’d use two databases for versioning, the main site one that keeps say the last two copies of a resource, after this they are shipped across to a history database and if needed you would have a retrieve/show from history command. You now have an audit trail as well and data separation, the history database can be backed up etc. separately.

Why have all that data in the main resource table?

Not sure what you mean here, what data? The only data you would have is two extra columns, a UUID and a rev, say max 512 bytes per resource. You don’t store the revs in the site content table, for any particular resource at any time you only need to know its UUID and current rev, these are the ’aggregate keys’ you mention above and are unique everywhere. You can have as many local(or remote) tables indexed by this as you want.

An example, say you create a resource, it would be stamped with UUID1r1, ship it another database, create 3 more copies locally UUID1r2, UUID1r3 and UUID1r4 in a temp table indexed by UUID. You could now say ’I want to use revision 3’ on the remote db find the resource by UUID, check its revision, its UUID1r1, so update it to UUID1r3. There’s no doubt here that its the same resource. It doesn’t matter what its MYSQL id is. You could also do this the other way if you wish

Quote from: shamblett at Jul 01, 2010, 11:28 PM

they should probably do it with a new table that has a relationship (aggregated?)

This is the bit I don’t like, what keys would you use? id, page title, longtitle, date aggregated together? If you can do this then you have a UUID already. its just a large composite key rather than being a generated UUID.

Why can’t you simply use the existing ID as the reference....

Quote from: shamblett at Jul 01, 2010, 11:28 PM

An example, say you create a resource, it would be stamped with UUID1r1, ship it another database, create 3 more copies locally UUID1r2, UUID1r3 and UUID1r4 in a temp table indexed by UUID. You could now say ’I want to use revision 3’ on the remote db find the resource by UUID, check its revision, its UUID1r1, so update it to UUID1r3. There’s no doubt here that its the same resource. It doesn’t matter what its MYSQL id is. You could also do this the other way if you wish

and base the revision on a timestamp or another primary MySQL ID you’ll want anyway?

Why can’t you simply use the existing ID as the reference....

Because its not unique across databases.

and base the revision on a timestamp or another primary MySQL ID you’ll want anyway?

See above and why do I want a MYSQL id? MYSQL needs a MYSQL id not me. What if Revo was using SQLITE or Oracle or MSSQL which its more than capable of doing? Would we re-code it because ’keys’ are implemented differently? No, we’d just carry on using our UUID’s which are meaningful to us not the database implementation.

FWIW guys, versioning structures for future releases of Revo have already been designed and developed (and doesn’t involve UUIDs for sure). The real challenge is handling promotion; even once you have versioning, this is not simple. There are many factors to consider, like transactional data vs. content management data, development changes vs. design changes, and just the general workflow and environment being used. In my view, there should be many ways to accomplish promotion in Revo, and the primary one will involve Transport Packages.