The SchemaBuilder, the service you use in migrations to modify the database schema of your module is a convenient tool. However, you can't use it directly to modify the schema of tables defined by other modules (this is commonly the case when wanting to add indices to other modules' tables) because SchemaBuilder automatically appends you module's name to the manipulated table's name... But this is very simple to overcome!

You can instantiate SchemaBuilder yourself (you just need a data migration interpreter instance) and parameterize it appropriately, like following:

    public class LocalizationMigrations : DataMigrationImpl
    {
        private readonly IDataMigrationInterpreter _dataMigrationInterpreter;


        public LocalizationMigrations(IDataMigrationInterpreter dataMigrationInterpreter)
        {
            _dataMigrationInterpreter = dataMigrationInterpreter;
        }
        
		
        public int Create()
        {
            var builder = new SchemaBuilder(_dataMigrationInterpreter, "Orchard_Localization_");
            builder.AlterTable("LocalizationPartRecord", table =>
                table.CreateIndex("Localization", "MasterContentItemId", "CultureId"));

            return 1;
        }
    }

Here we have a migration class for adding an index to LocalizationPartRecord, but this migration is in our own custom module, not in Orchard.Localization. We instantiate SchemaBuilder by hand, together with defining "Orchard_Localization_" as the table prefix (since this is what the original table has).

The above code is in a migration class for the sake of simplicity: this way it will behave like any other custom migration and we could make use of the versioning feature of migrations too. However this code could be anywhere else too.

Happy migrations!

No Comments

• Bertrand said 9 years ago Reply

  I have sincerely no idea why you'd want to do that.

• Benedek Farkas said 9 years ago Reply

  It's simple: we needed to work with localized content items (and retrieve the items' master content item) and indexing the LocalizationPartRecord table obviously makes it faster (after a certain amount of records).

• Piotr Szmyd said 9 years ago Reply

  Then submit an issue and provide a core fix:)

• Piedone said 9 years ago Reply

  We've talked about core indices before and came to the conclusion that barely any is needed OOTB because how indices should be build depends on the usage: and this can vary greatly.
  
  The same is true for this index: it suits our specific needs, where we sometimes also query on LocalizedPartRecord directly (we don't just use LocalizationService) and there are a huge number of rows in that table.
  
  But here the point is that this way you can add your indices to other modules' tables, also that of built-in ones easily.

• Robert said 9 years ago Reply

  Not to be naive, but this allows for inter-module communication. E.g., if I have a Matter module with all Matter related data and FK's to other tables, and then have a Document module, it can hook into Matter (i.e., PK/FK). No?

• Benedek Farkas said 9 years ago Reply

  Robert: yes, this allows you to interact with other modules' DB schema, although for certain operations you may have to write HQL.

  • Robert said 9 years ago Reply

    Thanks for confirming that. Are there any Orchard examples (modules, etc.) that I can look at to get ideas of how to use HQL in those instances? Again, thank you.

    • Piedone said 9 years ago Reply

      For adding FKs you don't need (and I think you can't use, since it's not a DDL) HQL. I don't think there's a method to add FKs in SchemaBuilder but you can always use ExecuteSql() as the last resort.