Proper (Source) Control of Media/DB file?

Topics: Customizing Orchard, General, Writing modules
Dec 3, 2011 at 1:54 PM

First, let me start off with the fact that I have very little experience with SQL Compact databases but a lot of experience with built from scratch full SQL DB solutions.

In a typical enterprise environment, I typically source control the schema, not the data itself.  Then I (ideally) have a data loading technique for the small amount of static data, configurable options etc.  For the majority of the work I've done, this data is fairly limited and doesn't impact the application look/feel that much.

With Orchard DEV, I find myself in an interesting place.  So much of the application is driven off the database and the media folder that if I want to develop a specific site and build/add modules as I see fit and I'm struggling abit on the proper process for doing this when the development is shared with 3 other remote people.

It's likely I'll have one person working on the customized theme, another 1-2 working on modules and 1-2 on content population. I suspect the modules will require database modifications/additions and the themes will need to be made to purposefully support some of the customized modules.  And of course, the site layout/content will be populated as we go. 

If it matters we are using TFS as the source control.

I realize this problem has little to do with Orchard itself and more general questions regarding proper source control and development styles for CMS based solutions but I'm still very interested in how others have tackled this with Orchard.

Dec 3, 2011 at 11:32 PM

Orchard has lots of built-in features that make it easy to work in this sort of environment. Modules have a system for Migrations, so you can define db changes at each version of the module. You can keep a live version of the site content, and use import/export to transfer that content to dev instances. Orchard is very testable to boot.

Coordinator
Dec 4, 2011 at 1:40 AM
Edited Dec 4, 2011 at 1:41 AM

This is quite timely. Pete is right to point out that import/export is a great help in keeping those things under source control, but it's still not necessarily optimal as not everything is exported. I would see it more as a good way to take content between instances, but it misses a number of qualities that a true backup has. On the other hand, more traditional backup solutions have the problem that they don't typically diff and merge so they are awkward to keep under source control. It so happens that I have built a small tool to tackle that particular problem, that I will release in the next few days once I've completed it.

For media, diffs and merges are not that important usually because it's quite rare that there is concurrency of work on them. Putting them under source control seems sensible, except when the sheer volume of them makes it impractical (I encountered that problem recently with a site that had 2GB of media, so we had to move that stuff to an FTP to make it available to all devs: still central, but no history).

Watch my blog, more news soon http://weblogs.asp.net/bleroy

Coordinator
Dec 5, 2011 at 1:37 AM

Here it is... Source-controlled Database Backups http://weblogs.asp.net/bleroy/archive/2011/12/04/source-controlled-database-backups.aspx

Dec 5, 2011 at 4:26 PM

That's awesome - I'll definitely be using it in production. It'd be nice to version the schema also; I wonder if there are any tools around to write out CREATE TABLE scripts like SMS does?

Coordinator
Dec 5, 2011 at 9:06 PM

I'll look at the schema next, yes. For the moment, I do it from SQL Management Studio.

Jan 16, 2012 at 5:08 AM

The schema issue is one I am currently working on a solution for.
Some modules I am building actually require that the schema be managed by VS database projects.
What I am thinking is that the migration.cs file in the module could just pull a V1 or V2 file from a SqlScripts folder packaged with the module. Then, instead of the typical NHibernate calls for table creation we could run our scripts (after some find/replace magic on the .sql file).
The main requirement is that the database schema is not managed with the orchard module because the project is intended to be consumed from different CMS packages. Also, the services should be testable before the module exists so we need to create the database sooner, rather than later.

Has anyone attempted this before? Thoughts on the process? Pitfalls?

Thanks,
Will

Jan 16, 2012 at 12:26 PM

Orchard tests its own modules before the DB exists, it does this by mocking the DB (remember you're testing your service, not the DB).

You could probably write a migration system to pull in SQL scripts; there's already a SchemaBuilder.ExecuteSQL command. How will you handle a multi-tenancy scenario where you could have sets of tables with different prefixes?

Jan 16, 2012 at 4:10 PM

That's what the "find/replace magic" is supposed to accomplish. Basically I would need to get the current database and server name from Orchard and replace tag values in the sql script so that it is executed in the correct context.

I'll have to test to make sure that multi-tenancy doesn't screw this up, or at least see if I have to encapsulate that to make it work correctly.

Care to suggest how one would get the current connection string and tenant name, in Orchard?

Thanks,
Will

Jan 16, 2012 at 4:14 PM

Well, SchemaBuilder.ExecuteSQL(...) will execute on the current DB and connection so you shouldn't need that. You can get the Table Prefix (and probably connection string if you really needed it) from site settings, and whilst you can inject that in Migrations I've found that problematic recently with background migrations, although I think there's a work item to solve that. 

Jan 16, 2012 at 4:16 PM

Correction: you can inject ShellSettings to get the information.

Jan 16, 2012 at 4:30 PM

Thanks Pete,

If I get time to work on it tonight I will report back with my findings.

Will

Jan 17, 2012 at 2:05 AM

Well, this isn't going to work as planned - or at least the adding the table prefix is not exactly trivial.
So, the question now is, how would one package a module, with included services, that are developed against a specific database schema?

If I let Orchard manage the schema, in the migrations.cs, file then I'll need a way to properly update the WCF system to expect the table prefix.
Either way I approach the issue I find myself at the same wall...

The WCF services create the system and the Orchard module is really just the UI for the system - the system can not be tied to Orchard (no hard dependencies to anything in Orchard).

I'm still trying to figure out a way around the .sql script but adding the table prefix isn't going to be easy - considering the system will also have to expect the addition of table prefixes.

Thanks,
Will

Jul 13, 2012 at 2:37 PM

I am going to drag this one back from the grave since I have spent some time working with it lately and wanted to run this by you all.

Orchard will not always be the Client (because some clients are not going to be UI at all), here is what I need.
1) Database schema is managed by a Visual Studio Database Project
2) Orchard migration will have access to SQL script generated during a build-event (or some process) from the Database project - to Orchard this is nothing more than a "resource"
3) Actual system is using Entity Framework for data access (but abstracted away so that NHibernate, or other can be used as well)
4) For the purposes of Orchard module, migration.cs will be responsible for running appropriate SQL script instead of the "normal" way of updating database
     Then, services needed by the module will be configured - both automatically and some requiring user input via Orchard admin panel.
5) Multi tenancy is currently addressed by two things
     A) BrandonHaynes.ModelAdapter for EF which allows you to change schema, prefix, suffix on the fly - so, as long as I can get this info from Orchard (and I can) then this works nicely.
     B) Substitution on the SQL script before it is executed. Something like
         UPDATE TABLE [SCHEMA].[{TENANT}_{PREFIX}TABLE_NAME{SUFFIX}] SET ColumnA = 'ORCHARD FTW'
         We use what is available in Orchard to get the values for TENANT and PREFIX before running the script.

This has the very nice benefit that I can run the SQL Script to test the actual SYSTEM without having to rely on the Orchard module. So, I can write unit tests AND system integration tests with ease.
This allows the core system to be disconnected from Orchard so I can provide a vanilla MVC package, ASP.Net package, other-less-nice CMS package. :)


There is definitely a bit of complexity under the covers. But, if I do my design job correctly, and configure good build and package scripts, consumers will not know/care.
Thoughts? Potential Pitfalls?
Thanks,
Will