Existing Database Strategies

Topics: Customizing Orchard, Writing modules
Mar 26, 2013 at 9:06 PM
Hey folks,

I'm developing a collection based web site, with a blog and regular pages as well, and am attempting to do so with Orchard as the dashboard. Imagine a mini-version of AllMusic.com or Rhapsody.com, there are 3,000 albums and ~40,000 tracks. I have an existing relational database with tables relating to albums and tracks. I want to pull these tables into Orchard so that I can utilize Orchard's CMS and search capabilities. Essentially extending Orchard to include albums and tracks as well as our existing web pages and blogs. I've been working through the Advanced Orchard tutorial on Pluralsight, which models an IMDB, type site. It's going well, but I am concerned since it was developed on Orchard 1.4, that it's methods may be out of date.

So the point. Has anyone here had any experience using Orchard to manage this scale of content items? Any advice for managing a fairly complex database? Am I crazy for trying to customize Orchard this extensively? I could see myself building a separate application for the music in straight MVC but I'd want a unified search that found the Orchard items as well.

Any and all advice is welcome and thanks for taking the time to read this far.

Best regards,
Toby
Mar 26, 2013 at 10:14 PM
That amount of content is definitely doable in Orchard. The only issue is that Import/Export (if you use it) will be a bit slow. But that shouldn't be a big obstacle as you can workaround the timeouts by breaking your import files into smaller chunks.

I've also done a hybrid approach where I converted an existing app into an Orchard site. for some of the content items I had a large amount of existing MVC display code, and data access code that would have taken way too long to rewrite as Orchard content types, so I imported "shell" content items, and used those as a wrapper to expose the content from their non-Orchard db schema into various points in the Orchard framework. This allowed me to index by content (you can customize which properties get indexed, I created a custom index for those content parts, and included the external/legacy database record id for those content parts as part of the search index document so I could reference hte original data when I want to display it. I also hooked into the Content Part driver system, and pull the data from the custom location to allow some aspects of the content to be managed and displayed by Orchard. For the primary display on the front end I just created a route pointing to my existing MVC controller and the display happens entirely outside of Orchard, except Orchard provides the theming / style. I customized the Razor views to use Orchard's resource management (to include style sheets, javascripts, etc).

For the database, I initially started with two separate db's but it became really annoying to maintain as I altered code and did migrations and deployments, so I eventually reworked it so that I created all the tables and schema into my Orchard db using handwritten SQL. This let me do migrations to my legacy schema from inside my Migrations.cs (but using custom SQL, not Orchard's Schema API). I had scripts to export all my data into flat files and one of my Migrations.cs steps creates all the schema and imports the data from the flat files using SqlBulkCopy. It's very fast, takes only a few seconds to import a couple hundred megs of data.

For other Content Types I just imported them entirely as Orchard parts and handled them 100% Orchard style.

So whatever degree of conversion you are willing to do from an existing app is possible. What are your concerns with regards to your database being complex? Are you worried about trying to convert the entire thing into Orchard style? If so the most complex part would probably be managing 1:N, and N:N relations between content. So far that's been the most annoying piece to handle when translating existing apps into Orchard.
Mar 27, 2013 at 1:44 PM
Thanks Monarch, I don't have existing MVC code - our current App is all in the Web Forms model. I'm jealous of your comfort level with this stuff, I'll hopefully be getting there in the months to come. I'm going to press ahead developing this as a custom Module with the database created using Migrations. Any advice for handling the relationship between Albums and Tracks in my context would be greatly appreciated. That one-to-many relationship is really central to our whole site. Also what do you think about using a SSIS package to update Orchard by routine. Has anyone done that?

Thanks again Monarch, you're living up to your handle!

Toby
Mar 27, 2013 at 1:54 PM
Edited Mar 27, 2013 at 1:55 PM
Edited for double posting. Why CodePlex, why?
Mar 27, 2013 at 2:05 PM
You can do it. You just have to decide what makes the most sense:
  1. Build everything "the Orchard way", i.e., make content types for everything, import everything via recipes using the Import/Export feature. Model 1:N and 1:1 relationships using the methods described in the Orchard docs, as well as using the Content Picker when possible (Content Picker is very nice, and makes it easy for a non-technical person to modify the relationships through the UI).
  2. Hybrid, where you get to keep your existing db schema mostly intact, but maybe just rebuild it inside the Orchard database, and import your data there using the method of your choosing (SSIS, flat file imports through C# code, etc).
If possible go for #1 because that's just the nicer way, and you will be able to get more help from the community using that more standard approach. In my situation I had to go with a hybrid solution because of the large amount of legacy code, data, and schema I had to convert.

The legacy system actually started as a web forms app, and I did the conversion in several steps. First converted it to MVC, which was not that hard, then built an orchard site and integrated the legacy MVC stuff alongside Orchard. Orchard and the legacy MVC stuff didn't integrate much at all beyond the fact that they were in the same IIS app. I used custom routes to point to custom controllers for the legacy stuff. Then I made an Orchard theme that mimicked my existing look and feel from the legacy MVC stuff, and retrofitted the MVC app to use that theme instead of the built-in MVC _layouts.cshtml system. I later on hooked in more integration points between the two, while in parallel I added a lot of new features and imported other existing content using 100% "The Orchard Way". I just kept taking more steps to further the integration as my comfort level with Orchard grew. I learned a lot by building the newer features The Orchard Way, and then I would later go back and apply some of the techniques I learned to improve the integration with the hybrid legacy stuff.

Hope that helps. Good luck!
Apr 3, 2013 at 4:31 PM
Edited Apr 3, 2013 at 4:58 PM
One more follow-up. When you say "Build everything "the Orchard way"" do you mean leverage as much as possible the tools built into Orchard, or do you mean build a custom module and script it all out using Migrations, Drivers, Handlers etc... I've been traversing the Advanced Orchard tutorial from Pluralsight and as former WebForms guy it's really a steep learning curve.

Edit to elaborate. The relationship between doing it the easy way using the tools set up in the Dashboard and the hard way - source code in Visual Studio has been really hard for me to navigate. I'd like to use the Dashboard approach but then when something tricky comes up extend in code. The approach demo'd in the Advanced Orchard tutorial on Pluralsight is 100% code based and a huge challenge for the uninitiated.

Hope that makes sense and thanks again!
Toby
Apr 3, 2013 at 6:39 PM
Edited Apr 3, 2013 at 6:42 PM
I would recommend scripting everything out, and the reason I prefer that is it allows you to have a project that can be built via script from absolutely nothing but a source control repository and a brand new developer machine, to a 100% working web app. I agree that could be difficult to manage for someone who doesn't have the right developer skills. If you are a developer, take it as a challenge to learn to build a dev project this way, because you can take the skills with you to other projects.

So when I said the "Orchard Way" i meant using content types and content parts, and using nhibernate, and having all your schema defined through orchard API's, as opposed to the non Orchard way, which would be starting with an existing database and doing some kind of hybrid solution that pulled in other data. That doesn't preclude doing it through the dashboard -- dashboard is definitely still The Orchard Way -- but dashboard style building isn't something i would personally recommend either.

When trying to figure stuff out i do use the dashboard to build types, and then export sample content items of those types. I then might mimic the export when building my official version through Migrations.cs. It is a learning curve, but it gives you a nice ability to experiment and then blow everything away and restart from scratch if you mess up.

EDIT: You can also move back and forth between the existing content type/part definitions in the Dashboard UI and the Migrations.cs files of the various modules where those types/parts are defined. This could help you learn how to represent what you want via Migrations.cs, if you find the UI building method intuitive. It won't take that long to get the hang of it.