Can an Orchard Migration Add a Database View?

[fuzl-llc]

I have a very complex query that requires directly using SQL in my Orchard site which is stored in MariaDB. I have a MariaDB database view I want to use but don't see any "Create" methods on SchemaBuilder which seems like it would be the place to find such funtionality (since it has CreateTable, CreateForeignKey, CreateMapIndexTable, etc). I want to manage view creation/updating like all other database stuff, through migrations... but can't seem to find a way.

[fuzl-llc]

So, I have a solution I'm posting in case it might be helpful for someone else... not sure if this is the right way or if it might be weird... First I tried working with the connection on the SchemaBuilder I'm using in other places in my migration class, assuming I could plug into whatever transaction was going on there:

var command = SchemaBuilder.Connection.CreateCommand();
command.CommandText = "CREATE VIEW nodes AS  select * from siteNodePartIndex;";
command.ExecuteNonQuery();

Unfortunately that gives and error I couldn't solve: The transaction associated with this command is not the connection's active transaction; see https://fl.vu/mysql-trans So, then I noticed there is an IDbConnectionAccessor type which you can inject into your migration class using the standard construction injection pattern. Then it seems pretty straightforward:

using var connection = _dbConnectionAccessor.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "create view theViewName as select * from theTable;";
command.ExecuteNonQuery();
connection.Close();

The above works for me so that is what I'm doing for now but please let me know if anyone has any better, more proper "Orchard" ways. Thanks!

[hishamco]

I might prefer extending ISchemaBuilder in YesSQL

[Piedone]

Going directly to IDbConnectionAccessor is something I'd do too.

[fuzl-llc]

Thanks @hishamco and @Piedone. It seems like maybe there are a couple reasonable options then. I'll just mark my solution as the answer for now since I've got some confirmation that just accessing IDbConnectionAccessor is ok and it is something that can easily be done right now for those that need this functionality... but in the long run, it does seem like it would be nicer if ISchemaBuilder was extended since (1) that would be the easiest way for people to find this functionality given its similarity to, say, ISchemaBuilder.CreateTable... and (2) they wouldn't have to really write any new code other that just using that "built in" feature. I'm not super familiar with YesSql or how it gets updated but perhaps @sebastienros might have some input on whether it should go in there?