Orchard Database Schema information

Topics: Administration, Core, Customizing Orchard, General
Mar 24, 2013 at 5:28 PM
Hi All,

There are two ways to create pages in any CMS system. Either you use UI and create pages 1 by 1 which is very time consuming. Another is you create pages directly at database level in bulk using some file as source or by creating some script. Is any there any way we can do such thing in Orchard.
To fully understand a system you need to know not only the front end but also the DB schema of the system too. Where I can find a Orchard DB schema details.

Thanks.
Coordinator
Mar 25, 2013 at 12:27 AM
You should look at the import feature, and at the content manager. Tables in the DB are not the level of abstraction where you want to act.
Mar 25, 2013 at 3:28 PM
Hi Bertrand,
That sound great but what if someone wants to create bulk pages? Is there any way to to achieve this in Orchard.
Mar 25, 2013 at 6:02 PM
You can insert the rows directly, but a single content item is not stored in one table. Many parts contribute their own tables.
If you know upfront what the type of content is to be inserted, you can account for that.
For the default Page content type, you will have to insert data into the following tables:
  • Orchard_Framework_ContentItemRecord
  • Orchard_Framework_ContentItemVersionRecord
  • Common_BodyPartRecord
  • Common_CommonPartRecord
  • Common_CommonPartVersionRecord
  • Orchard_Alias_AliasRecord
  • Orchard_Autoroute_AutoroutePartRecord
  • Title_TitlePartRecord
And I'm probably forgetting a few.

I think writing code that leverages the Content Manager API is recommended.
Coordinator
Mar 26, 2013 at 6:16 AM
I don't understand what you mean by "bulk creation" that is not covered by the import feature or by using the content manager. Tables in the DB are not the level of abstraction where you want to act.
Apr 3, 2013 at 3:14 PM
Hi Bertrand,
Bulk creation means creating a lot of pages using some sort of script either from UI or directly in database. If have a list of data for say 500 pages in a excel it will be too much time consuming to create those 500 pages using dashboard menu.
Apr 3, 2013 at 3:23 PM
@The_False_Rock, you can "bulk create" by using Orchard's import/export feature. Create an xml file with the content, and import it from the Import/Export area of the Dashboard, or using the orchard.exe command line tool with the "/recipe execute" option.

You don't need to understand the database schema to do this, as Bertrand stated. You can find out the schema that your xml needs to be formed in by creating some sample content in the UI by hand, and exporting it (again, using Import/Export). That export file has the same schema as the files that you import. You can test this by modifying some of the values in the export, and then importing the modified copy. After the import your content will reflect the changes you made in the xml.

Lots of people do bulk imports like this. For example I imported content from an existing SQL Server database by writing SQL queries that output xml in the schema that matches what Orchard expected for my Content Types. I think you can do something similar with Excel, or you can write a script to do it for you.
Apr 3, 2013 at 5:19 PM
TheMonach, I've been doing exactly what you say here for a similar scenario where I have been exporting from one instance of orchard, then attempting to import into another fresh instance. It mostly works really well, however, I have had issues around the Queries and Projection Widgets. First run through it fails to add, then if I just run a Query export / import followed by Projection / Widget layer export / import it seems to populate correctly. Debugging is fun round this area.

Have you seen any similar issues as I don't want to burn too much time if this is simply the workaround.
Apr 3, 2013 at 6:48 PM
I have had issues with import/export in the past but they were all fixed because I either patched or reported them.

I haven't had the specific issues you mention because I don't use Projections or Queries. If there are problems with import/export of those features you should report them. Exporting queries/projections from one instance and importing them to another is a use case that should work out of the box. In my experience when you report those they get fixed pretty quickly.
Apr 4, 2013 at 2:09 PM
Edited Apr 4, 2013 at 2:10 PM
Cheers Monarch. I've noticed an issue with the ordering of the xml in the recipe. For what I am doing it doesn't like the Projection xml element being before the Query xml element in the recipe file. As a workaround I found placing the Projection after the Query solved the issue (the projection depends upon the query, but this shouldn't be the case in xml). I'm still investigating whether this is a bug as the vanilla projector & import / export code seems to work fine. I've added custom layouts so could be to blame. If not I'll raise a bug.
Apr 4, 2013 at 4:27 PM
Hi,
Will the import export functionality will work if I am using a module which is not using NHiberate for database access instead its using Entity Framework. It has its own tables too.
Apr 4, 2013 at 4:54 PM
Yes, that could work if you have a content part that acts as a wrapper around that data.

Roughly what I've done in this scenario, is i had a bunch of entities in an existing SQL database that I wanted to hook into my Orchard site. I created a content type, and then a content part. The content part has an integer property that references the primary key from the external SQL db. In the content part driver, to display the data I use the primary key property to do a database lookup for the additional data from the external db. In the Content Part Driver I also override the Importing() and Exporting() methods to take proper actions. THe Importing() method gives you access to the recipe xml's, and this is where you could write custom code to grab data from the xml and save it to your Entity Framework stuff. Then do the reverse for Exporting(), that is, grab data from Entity Framework and push it to the xml.

It's a lot easier than it sounds. Just first write a simple content part, and implment the display, editor, importing, and exporting methods in the Driver class. That will give you a basic understanding of how the content parts work. Then it'll be pretty obvious where you can add your integrations with external data.