Going through a nopCommerce project with a 40GB database, I found the majority of the space was allotted to a very large Customer table. To fix this, I decided to delete Customer data with the following criteria:
No username, email, and password data
Not a system account
Doesn’t reference a Shipping and Billing address
Last activity recorded a month from today’s date
I considered the customers having shopping cart items as well, but found that most of the junk data had one shopping cart item – I think this comes from a robot regularly adding specific items to a cart to check price.
Here’s the SQL used:
DECLARE @customersToDelete TABLE (id int)
INSERT INTO @customersToDelete (id)
SELECT Id FROM Customer
Username IS NULL
AND Email IS NULL
AND Password is NULL
AND IsSystemAccount = 0
AND BillingAddress_Id IS NULL
AND ShippingAddress_Id IS NULL
AND LastActivityDateUtc < DATEADD(week, -1, GETDATE())
DELETE FROM ShoppingCartItem WHERE CustomerId IN (SELECT * FROM @customersToDelete)
DELETE FROM GenericAttribute WHERE KeyGroup = 'Customer' AND EntityId IN (SELECT * FROM @customersToDelete)
DELETE FROM Customer_CustomerRole_Mapping WHERE Customer_Id IN (SELECT * FROM @customersToDelete)
DELETE FROM Customer WHERE Id IN (SELECT * FROM @customersToDelete)
Once this is done, make sure to shrink the database to reclaim the space gained by clearing out the data.
Confirm by restarting the server and checking that SonarQube is running.
Setting up Reverse Proxy and HTTPS with Let’s Encrypt
Next step is setting up HTTPS, in this case we’ll use Let’s Encrypt for the certificate. To serve the correct ports, we’ll set up Apache and serve it as a proxy to the built-in Tomcat server that SonarQube provides.
When looking to configure Apache, set up the pre-reqs:
When upgrading nopCommerce, you’ll need to also get the latest versions of plugins to go alongside the nopCommerce installation. Depending on the upgrade, you may need to make some minor changes to the plugin to get it working.
In particular, 3.80 -> 4.00 marks the transition to .NET Core, meaning there are some significant changes to make.
Upgrade the .csproj File
To start, you’ll want to upgrade the .csproj file of the project. Easiest option here is to copy an existing plugin that works with nopCommerce 4.00 and modify it accordingly. You can use the Description.txt file to get the information you need.
First, create a plugin.json file.
Delete the following files:
Once you’ve done this, you’ll need to go through the file and make any kind of modifications to get a build to work successfully. If you’re working with VSCode, add the following file omnisharp.json to the plugin:
After getting the build to work successfully, the final step is running nopCommerce locally and installing/uninstalling the plugin. In addition, check the functionality to make sure everything is working.
Reminder: when installing/uninstalling plugins in nopCommerce locally, make sure to shut down and restart the application after installing applications to reflect plugin installation changes.
If you only have access to a database for nopCommerce (for example, if you’ve accidentally locked yourself out), here’s a way to create an administrator with access to the database and access to the frontend.
First, register a new user using the standard registration process. Once that’s done, run the following SQL query on the database to assign administrator rights:
INSERT INTO Customer_CustomerRole_Mapping
(SELECT Id FROM Customer
WHERE Email = 'YOUR_EMAIL'),
(SELECT Id FROM CustomerRole
WHERE SystemName = 'Administrators')