breaking change - deprecating .pgsql packages for new projects

[joeaudette]

The following data storage packages are being deprecated in favor of a new set of packages for new projects.

Deprecated Packages

• cloudscribe.Core.Storage.EFCore.pgsql

• cloudscribe.SimpleContent.Storage.EFCore.pgsql

• cloudscribe.Logging.EFCore.pgsql

• cloudscribe.Core.IdentityServer.EFCore.pgsql

• cloudscribe.Kvp.Storage.EFCore.pgsql

    services.AddCloudscribeCoreEFStoragePostgreSql(pgConnection);
    services.AddCloudscribeLoggingEFStoragePostgreSql(pgConnection);
    services.AddCloudscribeKvpEFStoragePostgreSql(pgConnection);
    services.AddCloudscribeSimpleContentEFStoragePostgreSql(pgConnection);
  
    services.AddIdentityServerConfiguredForCloudscribe()
                            .AddCloudscribeCoreEFIdentityServerStoragePostgreSql(pgConnection)
                            .AddCloudscribeIdentityServerIntegrationMvc()
                            .AddDeveloperSigningCredential();
  

New Replacement Packages

• cloudscribe.Core.Storage.EFCore.PostgreSql

• cloudscribe.SimpleContent.Storage.EFCore.PostgreSql

• cloudscribe.Logging.EFCore.PostgreSql

• cloudscribe.Core.IdentityServer.EFCore.PostgreSql

• cloudscribe.Kvp.Storage.EFCore.PostgreSql

    services.AddCloudscribeCorePostgreSqlStorage(pgsConnection);
    services.AddCloudscribeLoggingPostgreSqlStorage(pgsConnection);
    services.AddCloudscribeKvpPostgreSqlStorage(pgsConnection);
    services.AddCloudscribeSimpleContentPostgreSqlStorage(pgsConnection);
  
    services.AddIdentityServerConfiguredForCloudscribe()
                            .AddCloudscribeCoredentityServerPostgreSqlStorage(pgsConnection)
                            .AddCloudscribeIdentityServerIntegrationMvc()
                            .AddDeveloperSigningCredential();
  

Note that the new packages are NOT compatible with the old ones, to use the new packages you have to use a new database. If you have important sites already using the .pgsql versions of the packages you should not try to change to the new packages, at least yet. cloudscribe.com is currently using the older .pgsql packages and we are going to try to write migration scripts to migrate the existing data into a new database that uses the newer .PostgreSql packages. If we succeed in that we will share the scripts here.

Benefits of the newer packages

The original .pgsql packages use mixed case table and column names. Mixed case column names work fine if you write all your queries and commands using entity framework and Linq. Entity Framework generates the sql queries for you based on your Linq queries. However if you ever want to write your own custom sql queries or functions instead of using Linq to Entities, mixed case column names pose an inconvenience because they have to be quoted. The new packages use snake case tables and columns which is more conventional for PostgreSql and it simplifies how you write queries.

Example Quoted Query for Mixed Case

SELECT 
"Id", 
"CompanyName", 
"CompanyStreetAddress", 
"CompanyStreetAddress2",
"CompanyLocality",
"CompanyRegion", 
"CompanyPostalCode",  
"CompanyCountry", 
"CompanyPhone", 
"CompanyFax", 
"CompanyPublicEmail",  
"DefaultEmailFromAddress", 
"DefaultEmailFromAlias", 
"PreferredHostName"
FROM public."cs_Site";

Writing quires as shown above is tedious, you have to get the case just right and you have to use quotes around everything.

Example Query Using Snake Case

With the new packages, snake case names are used so we always write in lower case and we don't have to quote everything.

SELECT 
id,  
company_name, 
company_street_address, 
company_street_address2, 
company_locality, 
company_region, 
company_postal_code, 
company_country, 
company_phone, 
company_fax, 
company_public_email, 
default_email_from_address, 
default_email_from_alias, 
preferred_host_name
FROM public.cs_site;

Note that this does not change how we write Linq queries against our models, Entity Framework handles the mapping of the models to the snake cased table and column names, so Linq queries are not changed at all. The main benefit of the new packages is just that snake case is more conventional for naming practices in PostgreSql and it makes it easier to write sql queries and commands.

Going forward

The latest cloudscribe project templates will create new projects using the newer .PostgreSql libraries.

If you have projects in development that have not yet shipped, please consider switching to the newer libraries using a new database.

If you already have important sites deployed using the .pgsql packages please let us know here and that will help us decide whether we need to keep maintaining those packages long term.

We would like to not have to maintain the older .pgsql packages going forward, but it is not a huge extra effort to do so, therefore if we determine that people have important sites already in production we will continue to maintain the packages as long as needed.

As mentioned above cloudscribe.com currently uses the .pgsql packages and we are planning to try to migrate our data into a new database using the snake case tables and columns. If that works as expected we will share the scripts here for anyone else who wants to migrate to the newer packages.

[joeaudette]

I've created scripts for migrating cloudscribe core, simplecontent, and kvp storage to the new snake case tables. It requires a new db created with the new packages and with migrations already executed.
You configure the variable with the connection string to the source db and you run the scripts on the new db to migrate data.

https://github.com/cloudscribe/Announcements/tree/master/Support_Files/issue_7

After migrating you should be able to remove the old packages and reference the new ones and change your connection string to the new db where you migrated.