1. Dedykowany widok wykorzystujący funkcje pseudolosowe.
CREATE VIEW dbo.VGenerateCrypts
AS
SELECT NEWID() AS CryptUID,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt1,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt2,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt3,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt4,
1 + CONVERT(INT, (99-1+1) * RAND()) as CryptYear,
1 + CONVERT(INT, (12-1+1) * RAND()) as CryptMonth,
1 + CONVERT(INT, (28-1+1) * RAND()) as CryptDay
Do czego jest potrzebny ten widok? To jest obejście znanego problemu braku możliwości wykorzystania funkcji pseudolosowych w funkcjach skalarnych/tabelarycznych.
Przykładowo:
CREATE FUNCTION dbo.spGenerateNumber()
RETURNS varchar(100)
AS
BEGIN
RETURN newID()
END
Próba kompilacji takiej funkcji kończy się komunikatem: Msg 443, Level 16, State 1, Procedure spGenerateNumber, Line 8 Invalid use of a side-effecting operator 'newid' within a function.
2. Dedykowane funkcje tabelaryczne
Z założenia jedynym wykorzystywanym obiektem jest powyższy widok, pozostała implementacja nie wymaga dodatkowych struktur,tabel,dedykowanych funkcji i innych obiektów.
Każda z funkcji jest budowana schematycznie przy wykorzystaniu CTE, podobnie jest zapytania w pierwszej części artykułu. Dane generowane przez alias CTE zwracają wartość w postaci surowej i jeśli trzeba ją uzupełnić o poprawną sumę kontrolną odpowiednie podmiany postaci surowej na poprawną są w instrukcji select aliasu.
Funkcje przyjmują dwa parametry wejściowe @value i @algorithm. Ta ostatnia jest do poźniejszego wykorzystania w ramach rozwoju grupy algorytmów dla takiej samej domeny. Aktualne wersje funkcji nie korzystają z wartości wejściowej, ale można będzie to zmodyfikować na następnych iteracjach w ramach rozwoju, tak by wykorzystywany kod np. w procedurach składowanych nie ulegał zmianie ze względu liczbę argumentów funkcji.
Generowanie numerów PESEL
CREATE FUNCTION dbo.spGenerateValueOfPESEL (@value varchar(11),@algorithm int=1)
RETURNS TABLE
AS
RETURN
WITH generator AS
(
SELECT
RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 99) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 12) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 28) + 1,2),2)
+RIGHT(c.Crypt1,5) -- pozostala czesc
as PESEL
from dbo.VGenerateCrypts c
)
SELECT @value as InValue -- to co wejsciu
,left(PESEL,10)+
convert(varchar(1), ((
Convert(int,substring(PESEL,1,1))*9
+Convert(int,substring(PESEL,2,1))*7
+Convert(int,substring(PESEL,3,1))*3
+Convert(int,substring(PESEL,4,1))*1
+Convert(int,substring(PESEL,5,1))*9
+Convert(int,substring(PESEL,6,1))*7
+Convert(int,substring(PESEL,7,1))*3
+Convert(int,substring(PESEL,8,1))*1
+Convert(int,substring(PESEL,9,1))*9
+Convert(int,substring(PESEL,10,1))*7
)
% 10)) AS OutValue
,@algorithm AS algorithm
FROM generator;
Generowanie EMAIL
CREATE FUNCTION [dbo].[spGenerateValueOfEMAIL] (@value varchar(100),@algorithm int=1)
RETURNS TABLE
AS
RETURN
with generator AS
(
select @value as InValue
,RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%35) + 1 ),1)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%30) + 1 ),1)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%29) + 1 ),1)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%45) + 1 ),1)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%36) + 1 ),1)
+'@'
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%33) + 1 ),1)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%15) + 1 ),1)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%8) + 1 ),1)
+'.PL'
AS OutValue
,@algorithm AS algorithm
FROM dbo.VGenerateCrypts c
)
SELECT InValue,OutValue,algorithm
FROM generator;
Generowanie dowodu osobistego
CREATE FUNCTION [dbo].[spGenerateValueOfIDCard] (@value varchar(100),@algorithm int=1)
Returns table
as
RETURN
(
with generator AS
(
select
@value as InValue,
char(right(CAST(c.Crypt1 AS INT),1)+65)
+char(right(CAST(c.Crypt2 AS INT),1)+65)
+char(right(CAST(c.Crypt3 AS INT),1)+65)
+c.Crypt4
as IDCardNumber,
@algorithm as algorithm
from dbo.VGenerateCrypts c
)
select InValue,
left(IDCardNumber,3)
+
convert(varchar(1),((7*ascii(substring(IDCardNumber,1,1))-55)+
(3*ascii(substring(IDCardNumber,2,1))-55)+
(1*ascii(substring(IDCardNumber,3,1))-55)+
7*convert(int,(substring(IDCardNumber,5,1)))+
3*convert(int,(substring(IDCardNumber,6,1)))+
1*convert(int,(substring(IDCardNumber,7,1)))+
7*convert(int,(substring(IDCardNumber,8,1)))+
3*convert(int,(substring(IDCardNumber,9,1))) ) % 10)
+
right(IDCardNumber,5) as OutValue ,algorithm
from generator
)
Generowanie rachunku bankowego
CREATE FUNCTION [dbo].[spGenerateValueOfPolishBankAccount] (@value varchar(50),@algorithm int=1)
RETURNS TABLE
as
RETURN
(
WITH generator AS
(
SELECT
@value AS InValue,
RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 99) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 99) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 98) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 97) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 96) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 95) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 94) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 93) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 92) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 91) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 90) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 89) + 1,2),2)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 88) + 1,2),2)
AS Account,
@algorithm AS algorithm
from dbo.VGenerateCrypts c
)
select InValue,
-- poprawa sumy kontrolnej
right('00'+convert(varchar(2),98-convert(numeric(30,0),right(Account,24)+'252100')%97),2)+right(Account,24) as OutValue
,algorithm
FROM generator
)
Generowanie imion
CREATE function [dbo].[spGenerateValueOfFirstName] (@value varchar(50),@algorithm int=1)
RETURNS TABLE
AS
RETURN
with generator AS
(
select @value as InValue
,RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%26) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXY',ABS(BINARY_CHECKSUM(c.CryptUID)%25) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%24) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%23) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%22) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%21) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%20) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%19) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%18) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%17) + 1 ),3)
+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%16) + 1 ),3)
as OutValue,
ABS(Checksum(c.CryptUID) % 25)+5 as Length
,@algorithm as algorithm
from dbo.VGenerateCrypts c
)
select InValue,left(OutValue,Length) as OutValue,algorithm
from generator;
Generowanie kodu pocztowego
CREATE FUNCTION dbo.spGenerateValueOfPostalCode(@value varchar(11),@algorytm int=1)
RETURNS TABLE
AS
RETURN
WITH generator AS
(
select
RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 38) + 1,2),1)+
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 36) + 1,2),1)
+'-'
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 34) + 1,2),1)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 32) + 1,2),1)
+RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 30) + 1,2),1)
as PostalCode
from dbo.VGenerateCrypts c
)
select @value as InValue,
PostalCode as OutValue,
@algorytm as algorytm
from generator;
3. Generowanie danych testowych (TODO)
use AdventureWorks2012
GO
CREATE PROCEDURE dbo.spGenerateRandomTestData
AS
BEGIN
IF OBJECT_ID('AdventureWorks2012.dbo.RandomTestData') is null
BEGIN
CREATE TABLE dbo.RandomTestData
(lp int not null Primary Key,
PESEL varchar(11) not null,
EMAIL varchar(50) null,
DowodOsobisty varchar(50) null,
NRB varchar(50) null,
IMIE varchar(50) null,
KodPocztowy varchar(6) null
)
END
truncate table dbo.RandomTestData
;WITH
Poziom0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
Poziom1 AS (SELECT 1 AS C FROM Poziom0 AS A, Poziom0 AS B),--4 rows
Poziom2 AS (SELECT 1 AS C FROM Poziom1 AS A, Poziom1 AS B),--16 rows
Poziom3 AS (SELECT 1 AS C FROM Poziom2 AS A, Poziom2 AS B),--256 rows
Poziom4 AS (SELECT 1 AS C FROM Poziom3 AS A, Poziom3 AS B),--65536 rows
Poziom5 AS (SELECT 1 AS C FROM Poziom4 AS A, Poziom4 AS B),--4294967296 rows
Dane AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS Lp,
'' AS Wartosc
FROM Poziom5)
insert into dbo.RandomTestData
(lp,PESEL,EMAIL,DowodOsobisty,NRB,IMIE,KodPocztowy)
select lp,pes.OutValue as PESEL,email.OutValue,dowod.OutValue,nrb.OutValue,name.OutValue,kod.OutValue from dane d
CROSS apply dbo.spGenerateValueOfPESEL(d.Wartosc,1) pes
CROSS apply dbo.spGenerateValueOfEMAIL(d.Wartosc,1) email
CROSS apply dbo.spGenerateValueOfIDCard(d.Wartosc,1) dowod
CROSS apply dbo.spGenerateValueOfPolishBankAccount (d.Wartosc,1) nrb
CROSS apply dbo.spGenerateValueOfFirstname(d.Wartosc,1) name
CROSS apply dbo.spGenerateValueOfPostalCode(d.Wartosc,1) kod
WHERE lp <= 1000000-- limit
RETURN (0)
END
4. Wykorzystanie funkcji tabelarycznych do zmiany wygenerowanych danych testowych (TODO)
Dodaj komentarz