How to enumerate from a custom data feed

[mwpowellhtx]

Let's say we have a spreadsheet asset, or database, or perhaps even an external, potentially dynamic API, that delivers record oriented data i.e. IEnumerable<TSource> for integration within the CMS framework. How might we enumerate these, doing an appropriate ETL, perhaps, from the source data set to the OrchardCore/CMS framework? Ostensibly, I am assuming this would land as at least a ContentItem, or derivation thereof? And however that got organized, as a bag, list, etc, as appropriate. Assuming that would require a custom module, for starters, which knowledge of that data source. However, I am not familiar with the OrchardCore CMS vis-a-vis ContentItem hierarchy, or how to customize them, around such an objective.

[Skrypt]

You can import the spreadsheet as is in the Orchard Core database. Let's say, SpreadSheetTable. Then in the SQL Query module you can create a SQL Query that will return the data you require to use. Moreover, you can use the Schema on the SQL Query to create a GraphQL endpoint to that custom table. Of course, this is the simplest way to do it.

Though, if you want to create a ContentItem you will need to create a recipe.json file out of your SpreadSheet data. You start by creating a Content Type that will have the same column dbtypes than your spreadsheet and then you create 1 content item from the admin UI. You use the Import/Export feature in OC to export the data as a recipe.json file and then you base yourself on this JSON data to create your spreadsheet migration code to a OC recipe.

[Skrypt]

Take a look at the Workflow module source code to see some examples of custom tables usage. Of course, this uses YesSQL though. I believe that PostgreSQL can make use of JSON functions to retrieve the data directly with SQL queries. Though, Orchard Core doesn't make use of these JSON functions. We do a map/reduce design pattern with our tables. So, the Document table contains the JSON data in the Content column and our SomethingIndex tables are containing the mapped data that is required to filter on. Also, we use SomethingIndex tables for performance reasons too. I can't comment on MartenDB, I never used it.

[Skrypt]

I'm going to take some time this afternoon to read the MartenDB documentation.

[mwpowellhtx]

@Skrypt I appreciate the eyes; MartenDB aside, I need to know more about what sort of shapes we're talking about, what is the goal transformation, given say the Agency recipe? Out of the box there is the static content, right. What do we need to provide for that to be dynamic? Is it a JSON thing? A liquid thing? I'm not sure the examples are that clear on the source domain model.

[mwpowellhtx]

Considering approaches here. There may be some advantages to enumerating items dynamically during an map reduce event sequence, if possible. For instance, potentially helping with SEO and so forth.

Alternate to that, to be truly dynamic, and I mean dynamic in the run time space, if at all possible. Perhaps less helpful with the SEO, but it truly helps us by engaging visitors with content that is rendered dynamically.

Forums, blogs, etc, is one potentially application here. But also for the 'boilerplate' Agency recipe sections, where we might want to update or adjust services, portfolio, etc, in a regular, arbitrary manner.

[mwpowellhtx]

@Skrypt Need a bit of orientation here. Which moving parts do I need? Enable Queries from the admin features? For starters? Along these lines? Thank you...

[Skrypt]

https://martendb.io/introduction.html

To be honest. I'm watching this video https://sec.ch9.ms/ch9/2d29/a281311a-76bb-4573-a2a0-2dd7affc2d29/S315dotNETconf_high.mp4 about Marten and it is really similar to YesSQL. In fact, they are using the same tech for database persistence which is Dapper. The only difference is that they are using JSON functions in PostgreSQL to fetch the data whereas YesSQL doesn't. Though YesSQL has a lot of the same things that Marten has.

I have noticed that in that video he says that they didn't implement a solution with SQL Server because of the lack of these same JSON path functions but SQL Server has them since SQL Server 2016. So maybe since the video is from 2017 they didn't know at that time.

Things that I noted that YessSQL doesn't have :

Compiled Queries (faster than LINQ queries)
Patching API (uses JSON functions)
Events (As in DB events to get/set raw JSON documents)

@sebastienros I think Marten works a lot like YesSQL. The Patching API (json functions) is something I already talked about before. Maybe it would be worth starting to consider having it with Dialects for every DB Providers that allows it.

Actually I think that Marten decided to support only PostgreSQL because it was easier to only support the JSON functions from that DbProvider only whereas YesSQL tries to support every DBProviders.

[mwpowellhtx]

@Skrypt Up to y'all if/when/how you'd want to pursue Marten. As we've mentioned, I'd probably contain my domain models to modules with that as the data tier serialization for the time being.

But for purposes of this discussion, and our pursuits with OC/CMS, I'm more concerned about engaging with its domain model vis-a-vis content parts, items, types, and so forth, and how we might go about bridging that transformation. Examples based on the Agency recipe, for instance, would be helpful.

[mwpowellhtx]

I am turning a couple of choices over in my mind how to approach this. Is it worth the trouble transforming our enumerated domain model, whatever its source, to Content Items (ostensibly, presumably, ...), or do we simply present it as a custom Content Part, with its view doing the enumeration. And as for its Edit Views, ..., well we have the same block considering that as for my initial report. So we really do need to find what the heck is going on with the out of the box custom part, edit views, etc. Maybe it is a typo, I don't know. Also, not sure we want liquid in the near/medium/long range. Probably we just need/want the Razor, for a host of reasons, but that is neither here nor there

[mwpowellhtx]

@Skrypt I am wondering, possibly something along these lines for the Part HomeController? Except instead of a database connection, reaching out to Excel, or other, supporting assets.

[Skrypt]

Executing pure SQL will work this way. Though, it will not be SQL that works with any RDB as when you are using the ContentManager that passes through YesSQL. That's the only drawback.

[mwpowellhtx]

Also not sure it was mentioned here, but elsewhere, something along the lines what Lombiq is doing re: Person, perhaps. I see that controller is doing some work with Content Manager, which might possibly work, assuming we can connect properly with a workflow event, let's say.