The "License" and "LicenseDownloadData" model will be deleted and replaced with LicenseModel, now LicenseModel will have AvailableActivations and TimesDownloaded
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.
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.
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.
-- 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);
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);
-- 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 = '[email protected]');
