Using SQL to create pages & blocks in CMS.

Dan Butcher

Vote:

Is anyone able to shed some light on the structure/ arcitecture of pages and wehre they're stored in the CMS database.

We are looking at creating a some standard pages in out testing environment for smoke testing and user training. Mostly around our custom blocks. We are looking at doing the using a sql Query so that when we pull down our latest production databases we can recreate the pages every time without having to manually build every custom block and all of its setting permutations. This will also allow us to reset them back to their defaults shoudl users play with them.

So far I've pieced together Retrieving most of the strcuture of the pages through the tblContent using the pkId & parent to drill down the page structure. Wh'at im trying to understand is how you would get what blocks were on a page and what settings are saved for the block.

Any direction would be appreciated.

Thanks

#253554

Apr 19, 2021 13:08

Sebastian Enberget

Vote:

I think it will be impossible to do this with pure sql.

My sugesstion is to make a IInitializableModule, check if you are in test. If you are in test then check if the pages/blocks allready exist through API calls. If they doesn't exist, create them programaticly with episervers APIs (IContentRepository). Then you know it will allways work as epi may change their database structure any update (even small updates)

#253556

Apr 19, 2021 13:46

Quan Mai

Vote:

What you can do is to manually create, or programmatically create pages and blocks as you'd like, and then export them as an .episerverdata file. next time you can just import that file to your new site for testing

#253557

Apr 19, 2021 13:50

Ravindra S. Rathore

Vote:

Hi Dan,

I agree with Sebbe. It is very difficult to use the SQL queries since Episerver stores the values in multiple tables so I suggest you create one schedule job that basically creates your custom blocks/pages on demand

Some helpful articles -

https://labs.tadigital.com/index.php/2017/12/07/programatically-creating-and-updating-pages-and-blocks-in-episerver/

https://world.episerver.com/forum/developer-forum/-Episerver-75-CMS/Thread-Container/2014/11/create-block-programmatically/

#253558

Apr 19, 2021 13:57

Tomas Hensrud Gulla

Vote:

Pages are stored in the database - of course it is possible to create them using SQL.

Key tables are

tblContent
tblContentLanguage
tblContentProperty

References to blocks are stored in properties, i.e. content areas or xhtml string properties.

Sebbe's point is valid, and I think that's (no doubt) a better option than going all inn on SQL. I would however be tempted to use SQL anyway. It's a bad idea - but you'll probably learn a few things along the way.

I would also recommend looking into the export/import function available from admin mode. You could probably create your smoke-test-pages once, and them import them when you pull down the latest production database. You can probably automate it too, if needed.

#253559

Apr 19, 2021 13:57

Daniel Ovaska

Vote:

Although possible to use SQL I would favor using the Episerver api-s to do it. Either export import or using the ContentRepository...

Another variant is preparing one Episerver DB with the test content you want and then take a backup and use that one.

#253561

Apr 19, 2021 14:07

Vote:

Adding to Quans comment, if you spin up an empty site for the testing you could place that exported your-export-name.episerverdata to the new sites App_Data folder and that way get the new empty site automatically initilized with that data (built-in feature in Episerver).

Can't find the documentation about the automatic site initilization feature, but it is at least mentioned also in the DXP Creating a new CMS site documentation, under the title "Deployment steps" - it has a "Note" section. So this would be usable if you setup the testing site each time and tear it down after running tests.

#253654

Apr 20, 2021 16:45