Licensing database changes

Here is the documentation related to deprecate StudioDB to use the Generic Infrastructure DB to storage the licenses

Diagrams

The following models need changes in order to met all the licensing requirements that have been postponed due to the necessity of a new database.

The "License" and "LicenseDownloadData" model will be deleted and replaced with LicenseModel, now LicenseModel will have AvailableActivations and TimesDownloaded

Some important points that must be considered:

Validaciones si el usuario tiene o no internet.
En la validación online de la licencia, devolver license Model o licenseResponseDTO. Esto es para poderle hacer update a la licencia local
Comunicarle a Snow que va a cambiar la implementación de usar todas las lineas en todas las PCs
Preguntar a Snow acerca de Start Day, si lo quieren incluir en el fomr de creación de licencia
Agregar tabla de Usuario y de AllowedFeatures
Guardar fingerprint in DB para la validación
Posiblidad de futuros test de retrocompatibilidad
Validar createdBycomputer con VBUC
Considerar LicenseType como Tag?
Revisar el momento en el que se setea LicenseAssemblyVersion ( probablemente es en saveLicenseModel).
LicenseAssemblyVersion no debe ir en la base de datos

SQL Scripts

Create new tables in a Database with the Assessment DB Schema

Databases in which this can be run

AssessmentDB
AssessmentDB-test

Never run this script in another database besides those two. It will erase the PRODUCT table, which already exists in StudioDB, StudioDB-staging and others.

Functionality

Creates the following tables, with their indices, foreign keys and constraints:

LicenseProduct
LicenseFeature
License
Customer
Feature
Product

Script

Here we have the SQL Code for this script and also the .sql file. If you update any of them, please update the other so they are both equal at all times.

4KB

ApplyLicensingChanges.sql

-- Drop Old Schema
DROP TABLE IF EXISTS LicenseProduct;
DROP TABLE IF EXISTS LicenseFeature;
DROP TABLE IF EXISTS License;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Feature;
DROP TABLE IF EXISTS Product;

-- Create New Tables
	-- Customer
	CREATE TABLE [dbo].[Customer](
		[Id] [bigint] IDENTITY(1,1) NOT NULL,
		[Company] [varchar](2000) NOT NULL,
		[Email] [varchar](1000) NOT NULL,
		[Name] [varchar](2000) NOT NULL,
		[IpAddress] [varchar](500) NOT NULL,
	CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- Feature
	CREATE TABLE [dbo].[Feature](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Name] [varchar](1000) NOT NULL,
	CONSTRAINT [PK_Feature] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- License Feature
	CREATE TABLE [dbo].[LicenseFeature](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[LicenseId] [bigint] NOT NULL,
	[FeatureId] [int] NOT NULL,
	CONSTRAINT [PK_LicenseFeature] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- License
	CREATE TABLE [dbo].[License](
		[Id] [bigint] IDENTITY(1,1) NOT NULL,
		[CustomerId] [bigint] NOT NULL,
		[EndDate] [datetime] NOT NULL,
		[CreatedBy] [varchar](500) NOT NULL,
		[DateIssued] [datetime] NOT NULL,
		[StartDate] [datetime] NOT NULL,
		[AccumulatedUnits] [bigint] NOT NULL,
		[UnitsLimit] [bigint] NOT NULL,
		[GUID] [varchar](500) NOT NULL,
		[TimesDownloaded] [int] NOT NULL,
		[AvailableActivations] [int] NOT NULL,
		[ExecutionMode] [varchar](500) NOT NULL,
		[UnitsType] [varchar](1000) NOT NULL,
		[UnitsPerSolution] [bigint] NOT NULL,
		[FingerprintId] [int] NULL,
		[NotifyDays] [int] NOT NULL,
		[CreatedAtComputer] [varchar](1000) NOT NULL,
		[LicenseType] [varchar](1000) NOT NULL,
		[LastExecutionDate] [datetime] NULL,
	CONSTRAINT [PK_License] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- License Product
	CREATE TABLE [dbo].[LicenseProduct](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[LicenseId] [bigint] NOT NULL,
	[ProductId] [int] NOT NULL,
	CONSTRAINT [PK_LicenseProduct] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- Product
	CREATE TABLE [dbo].[Product](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](1000) NOT NULL,
	[FriendlyName] [varchar](2000) NOT NUll,
	CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

-- Foreign Keys
ALTER TABLE [dbo].[License]  WITH CHECK ADD  CONSTRAINT [FK_License_Customer] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_Customer]
GO
ALTER TABLE [dbo].[License]  WITH CHECK ADD  CONSTRAINT [FK_License_Fingerprint] FOREIGN KEY([FingerprintId])
REFERENCES [dbo].[Fingerprint] ([Id])
GO
ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_Fingerprint]
GO
ALTER TABLE [dbo].[LicenseFeature]  WITH CHECK ADD  CONSTRAINT [FK_LicenseFeature_Feature] FOREIGN KEY([FeatureId])
REFERENCES [dbo].[Feature] ([Id])
GO
ALTER TABLE [dbo].[LicenseFeature] CHECK CONSTRAINT [FK_LicenseFeature_Feature]
GO
ALTER TABLE [dbo].[LicenseFeature]  WITH CHECK ADD  CONSTRAINT [FK_LicenseFeature_License] FOREIGN KEY([LicenseId])
REFERENCES [dbo].[License] ([Id])
GO
ALTER TABLE [dbo].[LicenseFeature] CHECK CONSTRAINT [FK_LicenseFeature_License]
GO
ALTER TABLE [dbo].[LicenseProduct]  WITH CHECK ADD  CONSTRAINT [FK_LicenseProduct_License] FOREIGN KEY([LicenseId])
REFERENCES [dbo].[License] ([Id])
GO
ALTER TABLE [dbo].[LicenseProduct] CHECK CONSTRAINT [FK_LicenseProduct_License]
GO
ALTER TABLE [dbo].[LicenseProduct]  WITH CHECK ADD  CONSTRAINT [FK_LicenseProduct_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[LicenseProduct] CHECK CONSTRAINT [FK_LicenseProduct_Product]

-- Indexes
CREATE UNIQUE INDEX IX_LicenseGuid ON License (GUID);

CREATE NONCLUSTERED INDEX IX_CustomerEmail ON Customer (Email);

Create new tables in a Database with the Studio DB Schema

Databases in which this can be run

StudioDB
StudioDB-dev
StudioDB-staging

Functionality

Moves the data from the StudioLicense table into the following tables :

LicenseProduct
LicenseFeature
License
Customer
Feature
NewProduct (equivalent to Product in the AssessmentDB Schema)

Script

Here we have the SQL Code for this script and also the .sql file. If you update any of them, please update the other so they are both equal at all times.

4KB

ApplyLicensingChanges-StudioDB.sql

USE [studiodb-staging];
-- Drop Old Schema
DROP TABLE IF EXISTS LicenseProduct;
DROP TABLE IF EXISTS LicenseFeature;
DROP TABLE IF EXISTS License;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Feature;
DROP TABLE IF EXISTS NewProduct;

-- Create New Tables
	-- Customer
	CREATE TABLE [dbo].[Customer](
		[Id] [bigint] IDENTITY(1,1) NOT NULL,
		[Company] [varchar](2000) NOT NULL,
		[Email] [varchar](1000) NOT NULL,
		[Name] [varchar](2000) NOT NULL,
		[IpAddress] [varchar](500) NOT NULL,
	CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- Feature
	CREATE TABLE [dbo].[Feature](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Name] [varchar](1000) NOT NULL,
	CONSTRAINT [PK_Feature] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- License Feature
	CREATE TABLE [dbo].[LicenseFeature](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[LicenseId] [bigint] NOT NULL,
	[FeatureId] [int] NOT NULL,
	CONSTRAINT [PK_LicenseFeature] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- License
	CREATE TABLE [dbo].[License](
		[Id] [bigint] IDENTITY(1,1) NOT NULL,
		[CustomerId] [bigint] NOT NULL,
		[EndDate] [datetime] NOT NULL,
		[CreatedBy] [varchar](500) NOT NULL,
		[DateIssued] [datetime] NOT NULL,
		[StartDate] [datetime] NOT NULL,
		[AccumulatedUnits] [bigint] NOT NULL,
		[UnitsLimit] [bigint] NOT NULL,
		[GUID] [varchar](500) NOT NULL,
		[TimesDownloaded] [int] NOT NULL,
		[AvailableActivations] [int] NOT NULL,
		[ExecutionMode] [varchar](500) NOT NULL,
		[UnitsType] [varchar](1000) NOT NULL,
		[UnitsPerSolution] [bigint] NOT NULL,
		[FingerprintId] [int] NULL,
		[NotifyDays] [int] NOT NULL,
		[CreatedAtComputer] [varchar](1000) NOT NULL,
		[LicenseType] [varchar](1000) NOT NULL,
		[LastExecutionDate] [datetime] NULL,
	CONSTRAINT [PK_License] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- License Product
	CREATE TABLE [dbo].[LicenseProduct](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[LicenseId] [bigint] NOT NULL,
	[ProductId] [int] NOT NULL,
	CONSTRAINT [PK_LicenseProduct] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	-- Product
	CREATE TABLE [dbo].[NewProduct](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](1000) NOT NULL,
	[FriendlyName] [varchar](2000) NOT NULL,
	CONSTRAINT [PK_NewProduct] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

-- Foreign Keys
ALTER TABLE [dbo].[License]  WITH CHECK ADD  CONSTRAINT [FK_License_Customer] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_Customer]
GO
ALTER TABLE [dbo].[LicenseFeature]  WITH CHECK ADD  CONSTRAINT [FK_LicenseFeature_Feature] FOREIGN KEY([FeatureId])
REFERENCES [dbo].[Feature] ([Id])
GO
ALTER TABLE [dbo].[LicenseFeature] CHECK CONSTRAINT [FK_LicenseFeature_Feature]
GO
ALTER TABLE [dbo].[LicenseFeature]  WITH CHECK ADD  CONSTRAINT [FK_LicenseFeature_License] FOREIGN KEY([LicenseId])
REFERENCES [dbo].[License] ([Id])
GO
ALTER TABLE [dbo].[LicenseFeature] CHECK CONSTRAINT [FK_LicenseFeature_License]
GO
ALTER TABLE [dbo].[LicenseProduct]  WITH CHECK ADD  CONSTRAINT [FK_LicenseProduct_License] FOREIGN KEY([LicenseId])
REFERENCES [dbo].[License] ([Id])
GO
ALTER TABLE [dbo].[LicenseProduct] CHECK CONSTRAINT [FK_LicenseProduct_License]
GO
ALTER TABLE [dbo].[LicenseProduct]  WITH CHECK ADD  CONSTRAINT [FK_LicenseProduct_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[NewProduct] ([Id])
GO
ALTER TABLE [dbo].[LicenseProduct] CHECK CONSTRAINT [FK_LicenseProduct_Product]

-- Indexes
CREATE UNIQUE INDEX IX_LicenseGuid ON License (GUID);

CREATE NONCLUSTERED INDEX IX_CustomerEmail ON Customer (Email);

Move Licensing Data from Old Tables to New Tables

Databases in which this can be run

StudioDB
StudioDB-dev
StudioDB-staging

Functionality

Moves the data from the StudioLicense table into the following tables:

LicenseProduct
License
Customer
NewProduct (equivalent to Product in the AssessmentDB Schema)

Script

Here we have the SQL Code for this script and also the .sql file. If you update any of them, please update the other so they are both equal at all times.

4KB

MoveLicensesToNewTables.sql

-- Delete Customers
DELETE FROM LicenseProduct;
DELETE FROM NewProduct;
DELETE FROM License;
DELETE FROM Customer;

-- Move to Customer
Insert into dbo.Customer (Company,Email,[Name], IpAddress)
SELECT Company, Email, OwnerName, ''
FROM (
	-- CASE #1: Non-Empty Email
	(SELECT OwnerCompany AS Company, OwnerEmail AS Email, OwnerName
	FROM StudioLicense
	WHERE OwnerEmail <> ''
	GROUP BY OwnerCompany, OwnerEmail, OwnerName)
	UNION
	-- CASE #2: Email in Company, Empty Email
	(SELECT OwnerCompany AS Company, OwnerCompany AS Email, OwnerName
	FROM StudioLicense
	WHERE OwnerCompany LIKE '%@%'
	AND OwnerEmail = ''
	GROUP BY OwnerCompany, OwnerName)
	UNION
	-- CASE #3: Email not in Company, Empty Email
	(SELECT OwnerCompany AS Company, '' AS Email, OwnerName
	FROM StudioLicense
	WHERE NOT (OwnerCompany LIKE '%@%')
	AND OwnerEmail = ''
	GROUP BY OwnerCompany, OwnerName)
) AS AllCustomers
GROUP BY Company, Email, OwnerName;

-- Clean Company Name
UPDATE Customer
SET Company = 'Mobilize.Net'
WHERE Company IN 
(
	'Mobilzie',
	'Mobilize.net',
	'Mobilize Net',
	'Mobilize'
)
OR Company LIKE '%@mobilize.net';

-- Insert Products
DELETE FROM NewProduct;

INSERT INTO NewProduct([Name], [FriendlyName])
SELECT DISTINCT Product, Product
FROM StudioLicense;

-- Move to License
DECLARE @currentId BIGINT;
DECLARE @maxId BIGINT;

SELECT 
	@currentId = MIN(Id),
	@maxId = MAX(Id)
FROM StudioLicense;

PRINT @currentId;
PRINT CONCAT('START FROM ', CAST(@currentId AS VARCHAR(MAX)));
PRINT CONCAT('END AT ', CAST(@maxId AS VARCHAR(MAX)));

WHILE @currentId <= @maxId
BEGIN
	IF @currentId IN (SELECT Id FROM StudioLicense)
	BEGIN 
		PRINT @currentId;
		
		DECLARE @currentCustomerId BIGINT;

		SELECT @currentCustomerId = Customer.Id
		FROM Customer
		WHERE Customer.Email IN (
			SELECT StudioLicense.OwnerEmail
			FROM StudioLicense
			WHERE StudioLicense.Id = @currentId
		);

		IF @currentCustomerId IS NULL
		BEGIN
			SELECT @currentCustomerId = Customer.Id
			FROM Customer
			WHERE Customer.Email IN (
				SELECT StudioLicense.OwnerCompany
				FROM StudioLicense
				WHERE StudioLicense.Id = @currentId
			);
		END

		
		IF @currentCustomerId IS NULL
		BEGIN
			SELECT @currentCustomerId = Customer.Id
			FROM Customer
			WHERE Customer.Company IN (
				SELECT StudioLicense.OwnerCompany
				FROM StudioLicense
				WHERE StudioLicense.Id = @currentId
			);
		END

		Insert into dbo.License ( 
			[CustomerId] ,
			[EndDate] ,
			[CreatedBy] ,
			[DateIssued] ,
			[StartDate] ,
			[AccumulatedUnits] ,
			[UnitsLimit] ,
			[GUID] ,
			[TimesDownloaded] ,
			[AvailableActivations] ,
			[ExecutionMode] ,
			[UnitsType] ,
			[UnitsPerSolution] ,
			[FingerprintId],
			[NotifyDays],
			[CreatedAtComputer],
			[LicenseType],
			[LastExecutionDate]
		)
		select @currentCustomerId, ExpirationDate, IssuedByUsername,  CreationDate, CreationDate, LinesOfCode,LinesOfCode, dbo.StudioLicense.Guid,
		TimesDownloaded,AvailableActivations,ExecutionMode,'Lines of code',0, null,0,'','', CreationDate
		from dbo.StudioLicense
		WHERE StudioLicense.Id = @currentId; 

		DECLARE @currentLicenseNewId BIGINT;
		SELECT @currentLicenseNewId = SCOPE_IDENTITY();

		INSERT INTO LicenseProduct(LicenseId, ProductId)
		SELECT @currentLicenseNewId, NewProduct.Id
		FROM NewProduct
		INNER JOIN StudioLicense ON NewProduct.[Name] = StudioLicense.[Product]
		WHERE StudioLicense.Id = @currentId;
	END

	SET @currentId = @currentId + 1;
END

-- Validation
SELECT
	StudioLicense.OwnerCompany AS OldCompany, Customer.Company AS NewCompany,
	StudioLicense.OwnerEmail AS OldEmail, Customer.Email AS NewEmail,
	Studiolicense.OwnerName AS OldName, Customer.Name AS NewName
FROM License
INNER JOIN LicenseProduct ON LicenseProduct.LicenseId = License.Id
INNER JOIN NewProduct ON LicenseProduct.ProductId = NewProduct.Id
INNER JOIN Customer ON Customer.Id = License.CustomerId
INNER JOIN StudioLicense ON License.Guid = StudioLicense.Guid
WHERE (StudioLicense.Product <> NewProduct.[Name]
	OR StudioLicense.OwnerCompany <> Customer.Company
	OR StudioLicense.OwnerEmail <> Customer.Email)
	AND NOT (OwnerEmail LIKE '%@mobilize.net' AND Customer.Company = 'Mobilize.Net')
	AND NOT (OwnerEmail = 'aespinoza@mobilize.net');

PreviousHow to Use the Web API for Licensing NextFree licenses definition

Last updated 1 year ago