Retrieving random content items (rows) from a SQL database in Orchard with HQL queries

by Oliver 22. February 2014 12:37

We're adding some Premium functionality to discoverize right now, and part of that is the so-called Premium block which is a showcase of six Premium entries. Now, choosing the right entries for that block is the interesting part: as long as we don't have six Premium entries to show, we want to fill up the left over space with some random entries that haven't booked our Premium feature, yet. Get random rows from SQL database There are plenty of articles and stackoverflow discussions on the topic of how to (quickly) retrieve some random rows from a SQL database. I wanted to get something to work simply and quickly, not necessarily high performance. Incorporating any kind of hand-crafted SQL query was really the last option since it would mean to get hold of an ISessionLocator instance to get at the underlying NHibernate ISession to then create a custom SQL query and execute it. Not my favorite path, really. Luckily, the IContentManager interface contains the method HqlQuery which returns an IHqlQuery containing these interesting details: /// <summary> /// Adds a join to a specific relationship. /// </summary> /// <param name="alias">An expression pointing to the joined relationship.</param> /// <param name="order">An order expression.</param> IHqlQuery OrderBy(Action<IAliasFactory> alias, Action<IHqlSortFactory> order); …and IHqlSortFactory contains a Random() method. This finally got me going! HQL queries in Orchard HQL queries are a great feature in (N)Hibernate that allow you to write almost-SQL queries against your domain models. I won't go into further detail here, but be sure to digest that! Orchard's IContentManager interface contains the method HqlQuery() to generate a new HQL query. Unfortunately, there's almost no usage of this feature throughout the whole Orchard solution. So let me document here how I used the HqlQuery to retrieve some random entries from our DB: // retrieve count items of type "Entry" sorted randomly return contentManager.HqlQuery()     .ForType("Entry")     .OrderBy(alias => alias.ContentItem(), sort => sort.Random())     .Slice(0, count)     .Select(item => item.Id); And one more: // retrieve <count> older items filtered by some restrictions, sorted randomly return contentManager.HqlQuery()     .ForPart<PremiumPart>()     .Where(alias => alias.ContentPartRecord<PremiumPartRecord>(),            expr => expr.Eq("Active", true))     .Where(alias => alias.ContentPartRecord<PremiumPartRecord>(),            expr => expr.Lt("BookingDateTime", recentDateTime))     .OrderBy(alias => alias.ContentItem(), sort => sort.Random())     .Slice(0, count)     .Select(item => item.Id); Even with the source code at hand, thanks to Orchard's MIT license, the implementation of this API in the over 600 lines long DefaultHqlQuery is not always straight-forward to put into practice. Most of all I was missing a unit test suite that would show off some of the core features of this API and I'm honestly scratching my head of how someone could build such an API without unit tests! Random() uses newid() : monitor the query performance The above solution was easy enough to implement once I've got my head around Orchard's HQL query API. But be aware that this method uses the newid() approach (more here) and thus needs to a) generate a new id for each row in the given table and b) sort all of those ids to then retrieve the top N rows. Orchard has this detail neatly abstracted away in the ISqlStatementProvider implementation classes. Here's the relevant code from SqlServerStatementProvider (identical code is used for SqlCe): public string GetStatement(string command) {     switch (command) {         case "random":             return "newid()";     }     return null; } For completeness, here's the generated SQL from the first query above (with variable names shortened for better readability): select content.Id as col_0_0_ from Test_ContentItemVersionRecord content     inner join Test_ContentItemRecord itemRec         on content.ContentItemRecord_id = itemRec.Id     inner join Test_ContentTypeRecord typeRec         on itemRec.ContentType_id = typeRec.Id where ( typeRec.Name in ('Entry') )     and content.Published = 1 order by newid() OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY This approach works well enough on small data sets but may become a problem if your data grows. So please keep a constant eye on all your random queries' performance. Happy HQL-ing!

Productivity boost with MSBuild: use /maxcpucount

by Oliver 28. January 2014 21:24

This is embarrassing. For the n-th time during the past couple of years I've felt an unease waiting for our projects (read: solutions) to compile. I kept seeing this: This is MSBuild using 1 (!), yes, one!, of the 8 CPU cores I've sitting in my machine to get my work done. What about the other 7? Why don't you use them, MSBuild? With that single core, currently my simple local build of our project discoverize takes around 36 seconds: Tell MSBuild to use all cpu cores Well, it's as easy as adding /m or /maxcpucount to your msbuild command line build to boost your build times:   Down to 8 seconds with 3 additional characters: [space]/m. That's easily a 4.5 times improvement! Your mileage may vary Of course, every project is different, so your speed increase might be higher or a lot lower than what I've seen. But it's an easy measure to get at least some improvement in build times with very little effort. Don't trust Visual Studio on that one, though – the solution builds slowly there, still. For reference, let me tell you, that the /maxcpucount switch can actually take a parameter value like so: /maxcpucount:4. So if you lots of other stuff going on in the background or I don't know for what reason, really, you can limit the number of cpus used by MSBuild. Props to the Orchard team for a highly parallelizable build One of the specifics of the Orchard source code that's the base for discoverize is the very loose coupling between the 70+ projects in the solution. This allows MSBuild to distribute the compilation work to a high number of threads because there are almost no dependencies between the projects that MSBuild would have to respect. Great work! Happy building!

Orchard CMS - ContentPart will not update if made invisible through placement

by Oliver 17. December 2013 22:01

Today we decided that auto-updating our entries' urls when their names change is a rather good idea. Our entries are ContentItems consisting of our custom EntryPart, an AutoroutePart, and some more that are not important here. I thought it would be a matter of minutes to get this user story done. Simply set the correct Autoroute setting inside a migration step and it should work: public int UpdateFrom9() {     ContentDefinitionManager.AlterTypeDefinition(         "Entry", cfg => cfg.WithPart(             "AutoroutePart",             acfg => acfg.WithSetting("AutorouteSettings.AutomaticAdjustmentOnEdit", "true")));     return 10; } Well, it didn't. Placement affects ContentPart updates In discoverize, we offer a distinct management area (totally separated from the Admin area) where owners of entries can edit their own entry's data but not much more. The decision which url should point to their respective entry is one that we don't want them to make so we simply never rendered the AutoroutePart's edit view using the following line in our management modules placement.info file: <Place Parts_Autoroute_Edit="-" /> It turned out that this will cause Orchard to skip running through the POST related Editor() overload in the AutoroutePartDriver because in the ContentPartDriver.UpdateEditor() method there is an explicit check for the location of the currently processed part being empty: if (string.IsNullOrEmpty(location) || location == "-") {     return editor; } Because of the above check, the handling of the AutoroutePart of the currently saved entry is stopped right there and the code that is responsible for triggering the url regeneration based is never called. Updating ContentParts despite Invisible Edit View The solution is simple – thanks to Orchard's phenomenal architecture – and consists of two steps: Make the AutoroutePart's edit view visible in the placement.info: <Place Parts_Autoroute_Edit="Content:after"/> Remove all code from the AutoroutePart's edit view: With this in place, Orchard won't enter the if (location == "-") condition above but instead will execute the url regeneration we were after in the first place. Beware of Unrendered Views So, Orchard connects certain behavior to the visibility of our parts' rendered views. Not what I'd call intuitive, but at least now we know. Happy Coding!

Deleting a Table from Orchard's SQL CE Database

by Oliver 21. September 2013 11:08

In day-to-day development on discoverize, our Orchard based custom portal software, I use CompactView to look into my local instance's SQL CE database file if that's what I need to do. Dropping a Table from my Orchard.sdf Database Today, I was experimenting with some migrations code and needed to undo some table creation so that I could run an improved version on the same DB. I hoped for CompactView to get the job done, but somehow I couldn't get around how to do it from there. I tried running the following script from its SQL editor: ALTER TABLE [Discoverize_Management_UsersEntriesPartRecord] DROP CONSTRAINT [PK__Discoverize_Management_UsersEntriesPartRecord__000000000000104D]; DROP TABLE [Discoverize_Management_UsersEntriesPartRecord]; Unfortunately, CompactView told me that the table was currently in use: I was a bit baffled by this error. Somehow I had expected it to just work. SqlCeCmd to the Rescue Well, all is not lost. For some tasks I've already used the SQL Compact Command Line Tool in the past and it was waiting to be used again. This is what I first tried (without the newlines): SqlCeCmd40.exe -d "Data Source=App_Data\Sites\Default\Orchard.sdf" -q "ALTER TABLE [Discoverize_Management_UsersEntriesPartRecord] DROP CONSTRAINT [PK__Discoverize_Management_UsersEntriesPartRecord__000000000000104D]; DROP TABLE [Discoverize_Management_UsersEntriesPartRecord];" Unfortunately, this query returned an error: There was an error parsing the query. [ Token line number = 1,Token line offset = 148,Token in error = DROP ] This error referred to the DROP in the DROP TABLE statement which surprised me but a simple workaround was to just send two queries to the DB instead of a single one: SqlCeCmd40.exe -d "Data Source=..\src\Orchard.Web\App_Data\Sites\Default\Orchard.sdf" -q "ALTER TABLE [Discoverize_Management_UsersEntriesPartRecord] DROP CONSTRAINT [PK__Discoverize_Management_UsersEntriesPartRecord__000000000000104D]" (-1 rows affected) C:\Projects\discoverize\tools>SqlCeCmd40.exe -d "Data Source=..\src\Orchard.Web\App_Data\Sites\Default\Orchard.sdf" -q "DROP TABLE [Discoverize_M anagement_UsersEntriesPartRecord];" (-1 rows affected) Checking back with CompactView, I verified that the table was now successfully deleted. Do it Step by Step I'm not a guru with SQL CE databases but verifying that my syntax was correct and breaking the task into smaller pieces already got me back on track. Happy coding!

enjoyed the post?

Tags:

SQL CE | Orchard

Performance Tuning within Orchard

by Anton 17. June 2013 15:51

After hearing a little about how to go about increasing the performance of sites build upon Orchard at the Orchard Harvest, I did a quick search in the documentation. Two interesting pages came up: Optimizing Performance with a bunch of differen tips Caching with different types of cache Regarding Caching, the predominant opinion at the conference was, that one should always turn it on. We will look into SysCache, Memcache and other Modules like Cache. There was also talk about disabling dynamic compiling, but as of now we use this feature. Maybe we can circumvent our needs. One project has been demonstrated where Orchard is used as a CMS via the WebAPI of Orchard, but a “normal” MVC-Application is used to display this content. The reason for this was to achieve faster pageloads. Zoltan (as Piedone on CodePlex) has created a Combinator module to combine and minify stylesheets and javascript files. That could also reduce pageload time. We will look into these options as soon as our portal sites pick up traffic, and performance should be optimized.

Localization in Orchard

by Anton 17. June 2013 12:48

Our portal software is not yet localized. Our default is German, since our primary target users come from german-speaking countries. But as with Camping.Info our aim is to provide each portal site with translations into many languages. A year ago I already tested the possiblity of localization in Orchard – the underlying framework in our portal software. Orchard itself already provides a LocalizationPart. I tested the module Q42.Localization, which provides many useful festures, like editing the translation files from the admin panel. Other modules which we will look into: Culture Picker Culture Layer Vitus.Localization Translation Manager I think localization will be relatively easy with Orchard.

Orchard Harvest: Shapes Session

by Oliver 14. June 2013 18:09

These are session notes, so they might not adhere to the standards of a complete blog post, but there's a bunch of inspiring info that I had to get out of my head… Shapes – Overview What are they dynamic data model can be updated at runtime replaces static(ally typed) view models Creating Shapes Never instantiate Shape directly it won't trigger the related events in code, use an IShapeFactory dependency IShape Create(string shapeType) in Orchard views, use New   - New is just an IShapeFactory cast to dynamic   - New.Product -> IShapeFactory.Create("Product") Rendering Shapes use well-named file templates use shape methods any method in class inheriting from IDependency with the [Shape] attribute future: dynamic shapes to come, e.g. from DB (by Piotr Szmyd) refer to IDisplayHelperFactory and IDisplayHelper for more detailed info Advanced Uses of Shapes If you're just starting with Orchard Shapes, I recommend you to skip this section for now ;-) Creating Strongly Typed Shapes in your ContentDriver, when creating a Shape, you can do: 1: protected override DriverResult Display(EntryPart part, string displayType, dynamic shapeHelper) { 2: return ContentShape("Parts_Common_Body" /* Placement Key */, 3: () => shapeHelper.Parts_Common_Body(Html: someHtml)); 4: } becomes: 1: public class ConcreteType : Shape { 2: public string Html { get; set; } 3: } 4:  5: protected override DriverResult Display(EntryPart part, string displayType, dynamic shapeHelper) { 6: var shape = (ConcreteType) shapeHelper.Parts_Common_Body(typeof(ConcreteType)) 7: shape.Html = someHtml; 8: return ContentShape("Parts_Common_Body" /* Placement Key */, 9: () => shape); 10: } And then in your template, you can actually define your @model to be of type ConcreteType: 1: @model My.Namespace.ConcreteType 2:  3: @Display(Model.Html) Shape Events We have different extension points to hook into the shape rendering process: IShapeDisplayEvent intercept all shape Display events ShapeMetadata intercept a specific shape's Display events IShapeTableProvider configured at discovery time, for a specific type hook into events other than Display Order of invocation of the different event handlers: Displaying ShapeDisplayEvents ShapteTableProvider ShapeMetadata Display Set Metadata.ChildContent Shape is rendered if ChildContent not set (open e.g. for Caching) Wrappers are rendered in order, from Metadata.ChildContent, e.g. Document.cshtml Displayed ShapeDisplayEvents ShapteTableProvider ShapeMetadata Shape Morphing change the Metadata.Type of a Shape before rendering render the same Shape again using a different template see the MenuItem template for an example Shape Relocation The idea here is to add a Shape to a different part of the page than where your current ContentItem is rendering. In your view, just use: 1: @Display(Model.MyTitle) Now, in your placement.info file, write: 1: <Match ContentType="Page" DisplayType="Detail"> 2: <Place Parts_Title="MyTitle:5" /> 3: </Match> This will display your Title shape (possibly) a second time.

Orchard Harvest: Responsive Design

by Oliver 13. June 2013 15:30

These are just quick notes from one of the sessions at the Orchard Harvest conference currently taking place in Amsterdam. Fluid Grids bootstrap-responsive SimpleGrid 1140Grid FooTable hides columns, shows content in expandible elements Fluid Fonts FitText: fittextjs.com makes text fit into header elements SlabText adjusts text sizes to make page look beautiful Toggle navigation using Twitter bootstrap: put .nav-collapse and .collapse on the nav element Frameworks Foundation (uses SASS) INK Pure by Yahoo: new, small - 5KB

Orchard and Lucene: getting started with custom search – problems with booleans

by Oliver 12. September 2011 01:46

Update: I filed a bug in the Orchard issue tracker and it’s already been fixed. Nice work! We’ve just started to build our own customized search module. The goal is to have checkboxes for all searchable boolean properties of a given ContentPart and search by checking or unchecking them. In Orchard, one can add contents to the search index using the following code (this is an example for adding a boolean): 1: public class MyPartHandler : ContentHandler 2: { 3: public MyPartHandler(IRepository<MyPartRecord> repository) 4: { 5: Filters.Add(StorageFilter.For(repository)); 6:  7: OnIndexing<MyPart>( 8: (context, myPart) => context.DocumentIndex 9: .Add("DogsAllowed", myPart.DogsAllowed == true ? "true" : "false").Store()); 10: } 11: } Now, when a user checks the checkbox for “DogsAllowed” we want to ask the search index for all documents (Lucene term for what I would call an entity) with the DogsAllowed property set to true. The way we would do that is something along the following lines: 1: ... 2: var searchBuilder = _indexProvider().CreateSearchBuilder("Search") 3: searchBuilder.WithField("DogsAllowed", true); 4: ... The problem with this is: it gives me zero results! Of course, to get something into the index you have to go and save some document/entity for the OnIndexing handler to run, but I’d done that. Somehow I had the feeling that my search was not doing what it was supposed to… So I wanted to know more about the index Lucene had generated so far. Searching for lucene index visualizer lead me to Luke, the Lucene index toolbox which I downloaded v 0.9.9.1 of because it was built against Lucene 2.9.1 and in Orchard the Lucene.NET version is only 2.9.2. Maybe a newer version would have also worked but I got what I wanted anyways. After providing the path to the index files, Luke showed me some interesting stuff, among other things that the boolean was stored as string “True” (mind the upper case!): This behavior is actually no surprise when we look at the source code for the Add(string name, bool value) method, it just calls the Add(string name, string value) method with the bool converted to a string: 1: public IDocumentIndex Add(string name, bool value) { 2: return Add(name, value.ToString()); 3: } It’s good to know that true.ToString() == “True” btw. So, now when I search inside Luke using the query DogsAllowed:True I do get a result – but using DogsAllowed:true I don’t! (The syntax for searches is field:value.)                So now, why does my search (in Orchard) for all documents that have the DogsAllowed property set to true return no results when there obviously is a document in the index with exactly that? Well, let’s look at the implementation of .WithField(): 1: public ISearchBuilder WithField(string field, bool value) { 2: CreatePendingClause(); 3: _query = new TermQuery(new Term(field, value.ToString().ToLower())); 4: return this; 5: } When I debugged through the search I saw that all of a sudden we were searching for DogsAllowed==true instead of True which to Lucene are two completely different things (at least in this scenario). Actually, turning on logging for the Lucene.Services namespace, we get some debug logging telling us what the search is currently looking for: 1: 2011-09-10 00:44:33,026 [6] Lucene.Services.LuceneIndexProvider - Searching: DogsAllowed:true* Now I’m not surprised anymore that I’m not getting any results! This is obviously a bug in Orchard’s Lucene module, but for now I can easily get around it using simply passing the lower case strings to the Add() method myself like this: 1: OnIndexing<MyPart>( 2: (context, myPart) => context.DocumentIndex 3: .Add("DogsAllowed", myPart.DogsAllowed == true ? "true" : "false").Store()); Happy coding!

Orchard CMS: module settings not visible in Admin area? placement.info is the key!

by Oliver 9. September 2011 22:29

Today I went off to create a custom search module for our new Orchard based web application. I simply copied the module Orchard.Search, renamed all namespaces and such to Teamaton.Search, replaced some strings and prepended a prefix of “Teamaton” to a bunch of them. I wanted the new module to be able to run side-by-side with the original Orchard.Search module to make sure I had an independent implementation and there wouldn’t be any conflicts in case the Orchard.Search module is already installed somewhere. A problem I ran into quite quickly was that I wouldn’t see any of the search index’s fields on the Admin page for the new module (to the right you see the settings in the original Search module which is what I expect to see on the left as well):             Searching the discussions on Orchard’s codeplex site, I found this very helpful post on Module site settings not showing up which promised to be the key to my problem. During the initial string replacement orgy, in SearchSettingsPartDriver I had changed return ContentShape(…).OnGroup(“search”) to return ContentShape(…).OnGroup(“TeamatonSearch”). Now, this wasn’t in sync anymore with the Group Id specified inside the SearchSettingsPartHandler, but it should be (as the mentioned post suggests). So I changed that to match the Group “TeamatonSearch” like so: 1: protected override void GetItemMetadata(GetContentItemMetadataContext context) { 2: if (context.ContentItem.ContentType != "Site") { 3: return; 4: } 5: base.GetItemMetadata(context); 6: context.Metadata.EditorGroupInfo.Add(new GroupInfo(T("TeamatonSearch"))); 7: } Unfortunately, it still did not work. Frustration. Another post on the discussion list led me to believe it was a problem with my Copy’n’Paste. There, piedone states: One remarkable point is a name clashing with content parts: there can't be content parts with the same name in different modules as this leads to problems with schema creation and also when selecting them in them admin menus (because then their displayed name is also the same). This somehow made sense to me, so I went and prefixed the part type name with Teamaton. When that didn’t work I also prefixed all properties of all ContentParts and Records because I remembered that I’d encountered a similar problem before when building our Google Maps module which, contained the same Latitude and Longitude property names that another ContentPart had already defined. But still: to no avail. Desperation. In the first post mentioned above, Bertrand Le Roy also states: I have been in that exact situation. This is probably an error getting swallowed somewhere. […] In my case, it was the feature not enabling all the classes I needed, in particular the record, and the system couldn't find how to map database records. So I went to the Orchard console … feature disable Teamaton.Search … feature enable Teamaton.Search … still nothing. Next it hit me like a sledge hammer – Migrations.cs! I hadn’t taken a single look at the persistence mapping for the SettingPartRecord! Well, turns out there was something wrong in there, but only since after I prefixed both parts and properties because now they wouldn’t match the strings in Migrations.cs anymore (a good example where magic strings break stuff). Kind of helpless, I launched NHProf to look at the database calls that were being made and saw that there weren’t any for the Teamaton.Search module – but for the Orchard.Search module there were … hm. I scanned through the project files again – and stared at placement.info, another file I hadn’t touched since copying the original Search module. Bingo! This was the root of all evil or rather of the search fields not displaying. It looked like this: 1: <Placement> 2: <Place Parts_Search_SiteSettings="Content:1"/> 3: <Place Parts_Search_SearchForm="Content:1"/> 4: </Placement> The problem with it was that I had renamed all the view files and prepended a prefix to them! So the correct version should look like this: 1: <Placement> 2: <Place Parts_TeamatonSearch_SiteSettings="Content:1"/> 3: <Place Parts_TeamatonSearch_SearchForm="Content:1"/> 4: </Placement> That’s because the views are named TeamatonSearch.SiteSettings.cshtml and TeamatonSearch.SearchForm.cshtml and are both placed inside a Parts folder: And now I saw the search fields even for our own new search module. Lesson learned If you can’t see something check the  placement.info  if you (accidentally) haven’t forgot to make that something visible And if you still can’t see it – double check! Happy coding!

About Oliver

shades-of-orange.com code blog logo I build web applications using ASP.NET and have a passion for javascript. Enjoy MVC 4 and Orchard CMS, and I do TDD whenever I can. I like clean code. Love to spend time with my wife and our children. My profile on Stack Exchange, a network of free, community-driven Q&A sites

About Anton

shades-of-orange.com code blog logo I'm a software developer at teamaton. I code in C# and work with MVC, Orchard, SpecFlow, Coypu and NHibernate. I enjoy beach volleyball, board games and Coke.