I've been dynamically creating SQL tables using PetaPoco in Umbraco 7. Some example code to give an idea of what I've been doing:
public void OnApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
{
//Get the Umbraco Database context
var db = applicationContext.DatabaseContext.Database;
//Check if the DB table does NOT exist
if (!db.TableExist("JobAlertQueue"))
{
//Create DB table - and set overwrite to false
db.CreateTable<JobAlertQueueItem>(false);
}
}
So in this example it checks whether a table exists and, if not, creates one. This all works fine. However, since Umbraco 7.3.x it appears the CreateTable<T> extension has been deprecated in favour of DatabaseSchemaHelper.
So now I get compiler warnings like:
'Umbraco.Core.Persistence.PetaPocoExtensions.CreateTable<T>(Umbraco.Core.Persistence.Database, bool)' is obsolete: 'Use the DatabaseSchemaHelper instead'
'Umbraco.Core.Persistence.PetaPocoExtensions.TableExist(Umbraco.Core.Persistence.UmbracoDatabase, string)' is obsolete: 'Use the DatabaseSchemaHelper instead'
However I can't find any documentation on how to use the DatabaseSchemaHelper to do this. I'm not even sure how you get an instance of the class - does it follow the singleton convention like the ApplicationContext.Services or do you need to construct it? If the latter, how do you construct it - the signature is confusing:
var logger = LoggerResolver.Current.Logger;
var dbContext = ApplicationContext.Current.DatabaseContext;
var helper = new DatabaseSchemaHelper(dbContext.Database, logger, dbContext.SqlSyntax);
helper.CreateTable<JobAlertQueueItem>(false);
public void OnApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
{
//Get the Umbraco Database context
var ctx = applicationContext.DatabaseContext;
var db = new DatabaseSchemaHelper(ctx.Database, applicationContext.ProfilingLogger.Logger, ctx.SqlSyntax);
//Check if the DB table does NOT exist
if (!db.TableExist("JobAlertQueue"))
{
//Create DB table - and set overwrite to false
db.CreateTable<JobAlertQueueItem>(false);
}
}
Found this really helpful but also noticed that we don't actually need to call db.TableExist("JobAlertQueue").
Looking at the source I can see that db.CreateTable<T>(false) will make a call to db.TableExist(tableName) and use the bool passed in to determine whether or not to overwrite the table.
So we could (and probably should) simplify it to:
public void OnApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
{
//Get the Umbraco Database context
var db = applicationContext.DatabaseContext.Database;
//setting overwrite to false only creates the table if it doesn't exist
db.CreateTable<JobAlertQueueItem>(false);
}
This stops us having to hard-code the table name as it will get picked up from the type that was passed in .
Thanks for taking the time to let us know, Jason! This really simplifies it greatly. I really like that I don't need to pass the table name as a string anymore.
Maybe you could update the solution of this thread to Jasons's answer, @Dan Diplo? Don't know if that is even possible, though.
Recently there has also been added a new overload TableExist<T>()https://github.com/umbraco/Umbraco-CMS/pull/2492 , but for older versions of Umbraco you can also do something like the following.
If you create a dto class like this, you have the reference to the table name in the class and can access this in e.g. application startup event:
[TableName("jobAlertQueue")]
[PrimaryKey("Id", autoIncrement = true)]
[ExplicitColumns]
internal class JobAlertQueueDto
{
[Column("Id")]
public int Id { get; set; }
[Column("Name")]
public string Name { get; set; }
}
or something like this:
[TableName(TableName)]
[PrimaryKey(PrimaryKey, autoIncrement = true)]
public class JobAlertQueueDto
{
public const string TableName = "jobAlertQueue";
public const string PrimaryKey = "Id";
[Column(PrimaryKey)]
[PrimaryKeyColumn(AutoIncrement = true)]
public int Id { get; set; }
[Column("Name")]
public string Name { get; set; }
}
and then use it like this:
// Check if the DB table does NOT exist
if (!dbHelper.TableExist(JobAlertQueueDto.TableName))
{
// Create DB table
dbHelper.CreateTable<JobAlertQueueDto>(false);
}
Yes, that's a very good approach. Especially because it solves the all problems that will otherwise pop up down the road, when the projekt is live and needs updates/fixes.
On the other hand, sometimes checking and creating a simple table at startup is all you need. :)
How do you use DatabaseSchemaHelper to create SQL tables etc. with PetaPoco?
I've been dynamically creating SQL tables using PetaPoco in Umbraco 7. Some example code to give an idea of what I've been doing:
So in this example it checks whether a table exists and, if not, creates one. This all works fine. However, since Umbraco 7.3.x it appears the
CreateTable<T>extension has been deprecated in favour ofDatabaseSchemaHelper.So now I get compiler warnings like:
However I can't find any documentation on how to use the DatabaseSchemaHelper to do this. I'm not even sure how you get an instance of the class - does it follow the singleton convention like the
ApplicationContext.Servicesor do you need to construct it? If the latter, how do you construct it - the signature is confusing:How do I get an instance of an SqlSyntaxProvider?
Anyone know how to migrate to using DatabaseSchemaHelper?
Hi Dan,
Something like this should work
HTH,
Morten
Example refactored:
Thanks Morten - came across this one this morning and you saved me a lot of digging!
/Chris
Thanks Morten, exactly the boilerplate I was hoping to find. :)
Marc.
Thanks, Morten, that's exactly what I was looking for! #h5yr
Hi, Is DatabaseSchemaHelper still in the latest Umbraco: 7.4.3?
Jon
Hi Jon,
Yes, it is there.
Thanks
Great answer Morten, it worked for me too.
Thank you.
Comment author was deleted
Yup same here, thanks for the details :)
Found this really helpful but also noticed that we don't actually need to call
db.TableExist("JobAlertQueue").Looking at the source I can see that
db.CreateTable<T>(false)will make a call todb.TableExist(tableName)and use the bool passed in to determine whether or not to overwrite the table.So we could (and probably should) simplify it to:
This stops us having to hard-code the table name as it will get picked up from the type that was passed in .
Thanks for taking the time to let us know, Jason! This really simplifies it greatly. I really like that I don't need to pass the table name as a string anymore.
Maybe you could update the solution of this thread to Jasons's answer, @Dan Diplo? Don't know if that is even possible, though.
Recently there has also been added a new overload
TableExist<T>()https://github.com/umbraco/Umbraco-CMS/pull/2492 , but for older versions of Umbraco you can also do something like the following.If you create a dto class like this, you have the reference to the table name in the class and can access this in e.g. application startup event:
or something like this:
and then use it like this:
/Bjarne
Personally I prefer to use migrations for that :
https://cultiv.nl/blog/using-umbraco-migrations-to-deploy-changes/
Dave
Yes, that's a very good approach. Especially because it solves the all problems that will otherwise pop up down the road, when the projekt is live and needs updates/fixes.
On the other hand, sometimes checking and creating a simple table at startup is all you need. :)
But in a migration you can still do the same.
https://github.com/umbraco/Umbraco-CMS/blob/1bb593d264a085f94a3ce3bd710392b350561430/src/Umbraco.Tests/Migrations/CreateTableMigrationTests.cs#L52
Without the dependency on the database context.
Dave
is working on a reply...