Lib create Sql. SQL Zip Compression, RegEx và các hàm ngẫu nhiên

Mã đính kèm hiển thị thư viện nén zip SharpZipLib dưới dạng các hàm SQL thời gian chạy ngôn ngữ chung (CLR) an toàn cho luồng. Các chức năng bổ sung có trong thư viện (vì chúng đã được biên dịch vào hệ thống sản xuất của chúng tôi) được cung cấp để đánh giá Biểu thức chính quy (RegEx) trong SQL. Điều này có lẽ được giới thiệu tốt nhất mặc dù một ví dụ nhanh:

SQL

Sao chép mã

-- Zip Example --

DECLARE @input nvarchar(4000) = REPLICATE(N'HELLO 1 HELLO 2 HELLO 3 HELLO', 100)

DECLARE @zipped nvarchar(1000) = dbo.ZipString(@input)

SELECT

@zipped, -- eJztyTENACAMADArswBoIN ...

LEN(@input), -- 2900 bytes

LEN(@zipped), -- 68 bytes

100.0 - (100.0 * ((LEN(@zipped)*1.0)/(LEN(@input)*1.0))), -- 97.6% Reduction

IIF(@input = dbo.UnzipString(@zipped), 'PASS', 'FAIL') -- Pass

-- RegEx Example --

select dbo.RegexMatchCount([Phone], 'd') as [DIGIT_COUNT]

from #Users

Lý lịch

Lưu ý : Mã này chỉ có thể thực hiện được thông qua việc sử dụng SharpZipLib ), đây là một dự án tuyệt vời. Họ có lời cảm ơn sâu sắc của tôi.

Mặc dù SQL Server nguyên bản hỗ trợ lưu trữ dữ liệu dưới dạng nén ( Xem MSDN để biết chi tiết ), với thư viện này, chúng tôi có thể đạt được các mục tiêu vượt qua bất kỳ lớp ứng dụng nào. Một số ứng dụng thực tế của mã này có thể bao gồm:

Một ứng dụng N-Tiered trong đó điểm cuối của máy khách và cơ sở dữ liệu cần chia sẻ một lượng lớn dữ liệu có khả năng nén cao (như XML hoặc HTML) và SQL Server không cần phải truy cập dữ liệu đó thường xuyên. Ví dụ: cài đặt ứng dụng cụ thể của máy khách (ví dụ: cách hiển thị các thành phần giao diện người dùng) được lưu trữ dưới dạng XML.

Lớn / Đắt để tính toán các truy vấn được tính toán và lưu trữ trong một công việc hàng loạt, nhưng cần phải được gửi đến một máy khách hỗ trợ zip trong HTML / XML. Ví dụ: Báo cáo bán hàng của khách hàng sử dụng dữ liệu từ một số hệ thống từ xa, nhưng cần có sẵn cho giao diện người dùng khách hàng đang gọi. Trong trường hợp này, bạn có thể tính toán kết quả trong công việc hàng loạt FOR XML AUTO, nén kết quả tại thời điểm tính toán và lưu trữ trong một bảng được khóa bởi ID khách hàng. Khi giao diện người dùng cần dữ liệu, họ sẽ gửi một truy vấn đơn giản để lấy kết quả nén theo ID khách hàng và giải nén dữ liệu trên máy khách. Vì công việc nén và tính toán "khó" đều được thực hiện trong các lô phụ trợ, giao diện người dùng vẫn rất nhạy và tác động rất thấp đến SQL trong thời gian chạy của máy khách.

Việc tạo các tập lệnh SQL có chứa chuỗi văn bản lớn (nhiều MB). SQL Server Management Studio dường như gặp khó khăn khi tải các tập lệnh có tính chất này, do đó, nén dữ liệu đôi khi là lựa chọn khả thi duy nhất. Ví dụ: trong bài đăng khác của tôi về việc chuyển đổi .NET DataTables thành các tập lệnh SQL , sẽ là lý tưởng nhất để xuất dữ liệu tập lệnh được nén.

Ưu điểm lớn nhất là vì dữ liệu được lưu trữ và phân phối ở dạng nén, nó ít ảnh hưởng đến SQL (cả I / O Đĩa và CPU) và ít ảnh hưởng đến mạng để cung cấp dữ liệu đến máy khách. Điều này trái ngược với tính năng nén gốc SQL trong đó SQL nén khi nhận và giải nén khi gửi, mạng sau đó sẽ nén lại trong khi gửi, sau đó máy khách giải nén gói mạng khi nhận.

Sử dụng mã

Để triển khai mã, bạn có thể chạy "" được bao gồm SQLCLRCommon.publish.sqltrên cơ sở dữ liệu mục tiêu của mình hoặc biên dịch và triển khai bằng Visual Studio. Sau khi triển khai, cách sử dụng cơ bản khá đơn giản: Để nén một string giá trị, gọi dbo.ZipString, để giải nén, gọi dbo.UnzipString.

SQL

Sao chép mã

DECLARE @input nvarchar(4000) = N'HELLO HELLO HELLO HELLO HELLO HELLO HELLO HELLO HELLO HELLO'

DECLARE @zipped nvarchar(1000) = dbo.ZipString(@input)

SELECT @zipped, dbo.UnzipString(@zipped)

Có một số chức năng hữu ích khác có trong assembly:

[dbo].[CLRTestText](@text NVARCHAR (4000)) RETURNS NVARCHAR (4000)

Trả về chi tiết dữ liệu meta về văn bản đã truyền. Được sử dụng trong gỡ lỗi.

[dbo].[Random] (@min INT, @max INT, @seed INT) RETURNS INT

Trả về một int bán ngẫu nhiên giữa @min (bao gồm) và @max (độc quyền) bằng cách sử dụng hạt giống cụ thể (hoặc null đối với hạt giống dựa trên thời gian). Hữu ích khi sử dụng ROW_NUMBER() làm hạt giống để tạo ra các bộ dữ liệu ngẫu nhiên lớn.

[dbo].[RandomString] (@len INT, @includeLower BIT, @includeUpper BIT, @includeNumbers BIT, @includeExtended BIT, @seed INT) RETURNS NVARCHAR (MAX)

Trả về bán ngẫu nhiên string, hữu ích để tạo dữ liệu giả cho hệ thống thử nghiệm sớm trong quá trình phát triển khi hệ thống chưa có dữ liệu "thực", nhưng bạn cần dữ liệu có bản số gần giống với những gì bạn có thể thấy trong quá trình sản xuất.

[dbo].[RegexIsMatch] (@text NVARCHAR (4000), @pattern NVARCHAR (4000)) RETURNS BIT

Kiểm tra giá trị văn bản so với một biểu thức chính quy trả về 1 (true) nếu biểu thức khớp ít nhất một lần, nếu không thì 0 (false).

[dbo].[RegexMatch] (@text NVARCHAR (4000), @pattern NVARCHAR (4000)) RETURNS INT

Trả lại vị trí chỉ mục dựa trên số 0 đầu tiên của văn bản khớp với biểu thức chính quy đã truyền hoặc -1 khi không tìm thấy kết quả phù hợp nào.

[dbo].[RegexMatchCount] (@text NVARCHAR (4000), @pattern NVARCHAR (4000)) RETURNS INT

Trả về tổng số tất cả các kết quả phù hợp với biểu thức chính quy.

[dbo].[ToTitleCase] (@text NVARCHAR (4000)) RETURNS NVARCHAR (4000)

Định dạng a string trong Title Case, ví dụ: chuyển đổi " star wars" thành " Star Wars".

Các bài kiểm tra đơn vị C # và các tập lệnh kiểm tra đơn vị SQL (" Assembly Function Tests.sql ", " ZipTest.sql ") có các ví dụ chi tiết hơn về cách mã có thể được sử dụng, nhưng đây là những điều cơ bản:

SQL

Co lại ▲ Sao chép mã

SELECT [dbo].[Random] (1, 100, NULL) -- e.g.: 33

SELECT [dbo].[RandomString] (10, 1, 1, 1, 0, NULL) -- e.g.: 'Tj1LZPGLno'

SELECT [dbo].[RegexIsMatch] ('ABC1234', 'd{4}') -- e.g.: 1

SELECT [dbo].[RegexMatch] ('ABC1234', 'd{4}') -- e.g.: 3

SELECT [dbo].[RegexMatchCount] ('ABC1234', 'd') -- e.g.: 4

SELECT [dbo].[ToTitleCase] ('star wars') -- e.g.: 'Star Wars'

SELECT [dbo].[CLRTestText](N'Hello')

-- Example Output --

[Text] = "Hello"

[LCID] = "1033"

[IgnoreCase] = "True"

[CompareInfo.Name] = "en-US"

[CultureInfo.DisplayName] = "English (United States)"

[CultureInfo.ThreeLetterISOLanguageName] = "eng"

[TextInfo.ANSICodePage] = "1252"

Điểm quan tâm

Thử thách mã Zip

Phần thực sự thách thức duy nhất là tạo ra một luồng an toàn SharpZipLib. Ngoài hộp, SharpZipLib không được biết là an toàn theo luồng, đó là một yêu cầu đối với các hàm CLR của SQL. Để giải quyết vấn đề này, tôi đã xóa tất cả SharpZipLib mã không liên quan trực tiếp đến việc nén luồng (ví dụ: nội dung nén tệp), đó là lý do tại sao thư mục đó được đặt tên là SharpZipLibLite . Đối với các public static thuộc tính có thể ghi còn lại , chúng được chuyển sang hai lớp cá thể DeflaterHuffmanStatic và InflaterHuffmanTreeStaticsau đó cả hai đều được đóng gói dưới một lớp cá thể khác được gọi StaticClasses, lớp này được chuyển vào SharpZipLib trình xử lý luồng:

C #

Sao chép mã

StaticClasses sc = new StaticClasses();

using (var stream = new DeflaterOutputStream(memoryStream, ref sc)){…}

StaticClasses sc = new StaticClasses();

using (Stream s2 = new InflaterInputStream(new MemoryStream(byteInput), ref sc)){…}

Để biết thêm chi tiết, hãy xem lại ZipUtils.cs / ZipBytesvà UnzipBytes. Nếu bạn muốn sử dụng phiên bản mới hơn của SharpZipLib, bạn có thể làm theo cùng một mẫu này để thay đổi mã đó khi bạn thấy phù hợp nhất.

Có một số thách thức nhỏ hơn khác, chẳng hạn như làm thế nào để mã hóa tốt nhất mảng byte đã nén dưới dạng một string giá trị? Giải pháp là sử dụng System.Convert.FromBase64Stringvà ToBase64Stringmã hóa cứng System.Text.EncodingUTF-8. Để sử dụng một mã hóa khác, như ASCII, hãy thay đổi mã SERIALIZATION_ENCODING trong ZipUitls.cs , mặc dù tôi chưa kiểm tra mã bằng bất kỳ thứ gì khác ngoài UTF-8.

Một trong những thách thức vẫn còn là sử dụng hiệu quả các System.IO.Streams để nén và giải nén dữ liệu. Hiện tại, mã đang sử dụng toàn bộ Stream, chuyển đổi nó thành một string giá trị sau đó được chuyển đổi để SqlCharstrả về SQL. Đây là một phần để chúng ta có thể sử dụng cùng một hội đồng và các hàm cơ sở của nó ( ZipBytesvà UnzipBytes) cả trên máy khách hỗ trợ .NET và trên SQL Server. Nếu có cách tốt hơn để đạt được mục tiêu này với mã được tối ưu hóa hơn, vui lòng đề xuất các lựa chọn thay thế.

Thách thức ngẫu nhiên

Vấn đề Randomlà hạt giống. Nếu bạn sử dụng hạt giống mặc định (dựa trên thời gian hiện tại), phân phối ngẫu nhiên quá thấp vì rất nhiều kết quả được trả về mỗi mili giây. Tương tự như vậy, chúng tôi không thể chỉ sử dụng ROW_NUMBER() vì vì vậy chúng tôi sẽ nhận được các giá trị ngẫu nhiên giống nhau mọi lúc cho hàng 1, 2, 3, v.v., vì vậy nó không phải là ngẫu nhiên. Giải pháp "chia chênh lệch" nằm trong hàm SafeSeedcố gắng lấy giá trị đã qua và sử dụng kết hợp với thời điểm hiện tại trong năm. Những người viết mã thực sự yêu thích các thử thách toán học có thể đưa ra những ý tưởng hay hơn, vì vậy hãy thoải mái đưa ra phản hồi mang tính xây dựng.

Vấn đề khác là với RandomString. Mã hiện tại phần nào được tối ưu hóa cho các string giá trị lớn (giả sử hơn 255 ký tự) vì nó tạo ra một nhóm tất cả các giá trị ngẫu nhiên có thể có để trả về và sau đó kéo các giá trị từ nhóm. Nếu bạn đang nhận được rất nhỏ string, điều này là tương đối không hiệu quả, vì vậy hãy lưu ý.

Những thách thức của RegEx

Để tránh phải chuyển Is Case Sensitivecờ "", tôi đã cố gắng làm cho nó thông minh và phân tích cú pháp siêu dữ liệu văn bản đầu vào. Nếu đầu vào string là đối chiếu phân biệt chữ hoa chữ thường, RegEx sẽ đánh giá là phân biệt chữ hoa chữ thường, nếu không phân biệt chữ hoa chữ thường. Đây là một phần lý do tại sao tôi tiếp xúc CLRTestText() để xác định độ phân biệt chữ hoa chữ thường của văn bản string.

Công việc tương lai

Chúng tôi đã sử dụng thư viện này trong một vài năm nay và nó đã hoạt động tốt cho nhóm của chúng tôi và các dự án của chúng tôi. Tuy nhiên, trong khi tổng hợp bài viết này, tôi thấy một số cơ hội để thực hiện một số công việc mới thú vị để mở rộng điều này theo các hướng khác nhau. Đáng chú ý:

Hiển thị ZipBytesvà UnzipBytesnguyên bản là các hàm SQL để cho phép chúng được sử dụng trên dữ liệu nhị phân (varbinary).

Hiển thị bộ SharpZipLib mã đầy đủ , không chỉ là nội dung nén luồng. Tôi không chắc điều này sẽ khả thi, nhưng sẽ rất vui nếu bạn thử.

Hiển thị một RegExMatcheshàm trả về một bảng các kết quả phù hợp được tìm thấy cho một biểu thức cụ thể. Ví dụ: RegExMatches('1234', ' d {2}') sẽ trả về một bảng hai hàng với hàng một chứa " 12" và hàng hai chứa " 34". Các cột bổ sung cho vị trí phù hợp và những gì không sẽ là một phần thưởng.

Nếu bạn cảm thấy như thể bạn đã đóng góp đáng kể cho mã này, hãy gửi nó theo cách của tôi để tôi có thể đăng nó!

Lịch sử

Ngày 17 tháng 12 năm 2014 - Bản nháp công khai lần đầu

23 thg 12, 2014 - Cập nhật bài viết nhỏ, mã không thay đổi.

Giấy phép

Bài viết này, cùng với bất kỳ mã nguồn và tệp liên quan nào, được cấp phép theo Giấy phép Mở Dự án Mã (CPOL)

Chia sẻ

Thông tin về các Tác giả

Brad Joss

Nhà phát triển phần mềm (Cao cấp)

Đồng hồ đeo tay

thành viên này

Hoa Kỳ Hoa Kỳ

Không có tiểu sử được cung cấp

Nhận xét và thảo luận