Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Добрый день, ребята!
Такая ситуация: У меня в БД есть диапазон адресов (Range_Start и Range_End). Теперь, хочу написать процедуру добавления в эту таблицу новый диапазонов, но сделать, так, чтобы не было пересечений между диапазонами, хранящиеся в БД.
Написал такую процедуру:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE Insert_Range
	-- Add the parameters for the stored procedure here
	@range_start_new nvarchar (15),
	@range_end_new nvarchar (15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS 
	( 
		SELECT [IP_Ranges].[Range_Start], [IP_Ranges].[Range_End] FROM [dbo].[IP_Ranges]
		WHERE 
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_Start], 2)) <> CONVERT (INT, PARSENAME (@range_start_new, 2))) AND
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_End], 2)) <> CONVERT (INT, PARSENAME (@range_end_new, 2)))
	)
			
	BEGIN
		INSERT INTO [dbo].[IP_Ranges] 
		(
			[IP_Ranges].[Range_Start],
			[IP_Ranges].[Range_End]
		)
		VALUES
		(
			@range_start_new,
			@range_end_new
		)

		RETURN 1;
	END
		
	ELSE
	BEGIN
		RETURN -1;
	END 
END
GO


Но она, почему добавляет все без исключения. даже не проверяет, есть бд у меня диапазон, с которым может быть пересечение.
Помогите пожалуйста.
23 апр 14, 11:51    [15921282]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Glory
Member

Откуда:
Сообщений: 104751
adimmat
даже не проверяет, есть бд у меня диапазон,

Как написали проверку, так она и работает

adimmat
Помогите пожалуйста.

Как вы монитроили ход выполнения своей процедуры ?
Как проверяли, что возвращает запрос в IF EXISTS ?
23 апр 14, 11:55    [15921318]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9169
Диапазоны А-Б, В-Г пересекаются, если А < Г и В < Б.
23 апр 14, 11:56    [15921323]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Glory, Ход выполнения Я не проверял, но дело в том, что у меня в БД есть такой диапазон:
195.58.53.3-195.58.53.255
Я добавляю след. диапазон: 127.58.53.0-127.58.53.255.
Сравнение должно идти по 3-му актету (т.е. 53 есть у же в бд и я добавляю 53, который процедура не должна пропускать).
23 апр 14, 11:59    [15921374]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Glory
Member

Откуда:
Сообщений: 104751
adimmat
Glory, Ход выполнения Я не проверял,

И что вы тогда хотите ?
Что вам мешает проверить, что возвращает ваш SELECT ?
23 апр 14, 12:02    [15921395]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Владислав Колосов, Простите, а разве не по 3-му актету нужно сравнивать?
Допустим, такой диапазон:
A,B,C,D-A1,B1,C1,D1 (где A,B,C,D и A1,B1,C1,D1 - актеты).
Если C!=C1, значит они не пересекаются!?
Или Я ошибаюсь?
23 апр 14, 12:03    [15921409]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3450
adimmat
у меня в БД есть такой диапазон:
195.58.53.3-195.58.53.255
Я добавляю след. диапазон: 127.58.53.0-127.58.53.255.
Сравнение должно идти по 3-му актету (т.е. 53 есть у же в бд и я добавляю 53, который процедура не должна пропускать).
Вообще-то 195/24 и 127/24 - это непересекающиеся диапазоны. Ну, если конечно мы про IPv4 говорим.

Почему сравнение должно идти по третьему октету, если первые уже разные, и с какого конца вы его считаете третьим - не очень понятно.
23 апр 14, 12:08    [15921453]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Ennor Tiegael, Честно Вам скажу, я не разбираюсь в сетевой ... !
Но "добрые люди" сказали, что главное, чтоб 3-ий актет не совпадал!
Люди, между прочим, заниматься этим непосредственно, это их хлеб, можно сказать.
А нумерация актетов идет, сверху вниз:
Parsename (Range_start, 4) - 1 актет
Parsename (Range_start, 3) - 2 актет
Parsename (Range_start, 2) - 3 актет
Parsename (Range_start, 1) - 4 актет
23 апр 14, 12:16    [15921528]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Владислав Колосов, Вы не могли бы объяснить на примере!
Спасибо!
23 апр 14, 12:18    [15921550]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Glory
Member

Откуда:
Сообщений: 104751
adimmat
Но "добрые люди" сказали, что главное, чтоб 3-ий актет не совпадал!


Ну так и начните с отладки вашего проверочного запроса
DECLARE @range_start_new nvarchar (15),	@range_end_new nvarchar (15)
SET @range_start_new = '127.58.53.0'
SET @range_end_new = '127.58.53.255'

SELECT [IP_Ranges].[Range_Start], [IP_Ranges].[Range_End] FROM [dbo].[IP_Ranges]
WHERE 
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_Start], 2)) <> CONVERT (INT, PARSENAME (@range_start_new, 2))) AND
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_End], 2)) <> CONVERT (INT, PARSENAME (@range_end_new, 2)))
23 апр 14, 12:24    [15921596]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3450
Ааа! Ну если добрые люди сказали, значит так и надо делать.
if not exists (
	select 0 from dbo.IPRanges where parsename(@range_start_new, 2) between parsename(Range_Start, 2) and parsename(Range_End, 2)
	)
	and not exists (
	select 0 from dbo.IPRanges where parsename(@range_end_new, 2) between parsename(Range_Start, 2) and parsename(Range_End, 2)
	)
	-- Insert here
Только вот "несовпадение третьего октета" и "непересечение диапазонов адресов" - это все-таки совсем не одно и то же.

Ну и идиотизм полнейший, конечно. Похоже, над вами кто-то стебется в открытую.
23 апр 14, 12:27    [15921639]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Ennor Tiegael, Почему ВЫ таж решили?
23 апр 14, 12:36    [15921730]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Glory
Member

Откуда:
Сообщений: 104751
adimmat
, Почему ВЫ таж решили?

Потому что диапазоны 195.58.53.3-195.58.53.255 и 127.58.53.0-127.58.53.255 НЕ пересекаются
23 апр 14, 12:39    [15921756]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9169
автор
A,B,C,D

Зачем Вы так делаете? Сравнивайте

A' = A*256*256*256+B*256*256+C*256+D
и т.д.

Побайтно бессмысленно сравнивать.
23 апр 14, 12:47    [15921820]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Владислав Колосов, А что нам даст метод, который предложили Вы?
Там же тоже сравнение будет идти?!
23 апр 14, 13:11    [15922076]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9169
По мне так бессмысленно разбивать IP адрес на составляющие байты, т.к. надо сравнить 4 целых числа в итоге.
23 апр 14, 14:45    [15922944]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT!  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
adimmat
есть диапазон адресов (Range_Start и Range_End)
Странно что вы выбрали этот подход.
Обычно сети делятся по подсетям ровно. Т.е. по маскам типа 127.58.53.0/24
Смешивание, это как-то ... непонятно. Если это логические единицы то на практике часто бывает когда несколько подсетей объединяются, притом не последовательно, а разрывами, т.е. между двумя по порядку померами может вклиниваться другие.
Поэтому просто объединяют набор подсетей под один ключ - группу.

adimmat
195.58.53.3 - 195.58.53.255
Вот тут как считать? Подсети такой нет. Куда делись адреса от 0 до 2х? Это либо набор покрывающих подсетей или исключающая сеть. Я уже не говорю про бродкасты.

В случае масок (без исключающих сетей) хватает как я писал в прошлой теме (15919915) - 2 колонки и 1 функцию.
Условия проверяются всегда апосля, иначе в случае параллельности процессов вы таки вставите пересекающиеся диапазоны.
Такова природа транзакционности.
На указаной мной структуре (9280409) будет так:
+ Структура и украшательства
CREATE TABLE [dbo].[SubNet] (
	[SubNet]	Binary(4)	NOT NULL
,	[Mask]		TinyInt		NOT NULL
,	CONSTRAINT [PK_SubNet] PRIMARY KEY (
		[SubNet]
	,	[Mask]
	)
,	CONSTRAINT [UQ_SubNet] UNIQUE (
		[Mask]
	,	[SubNet]
	)
)
GO
CREATE VIEW [dbo].[vwSubNet] AS
SELECT	S.*
,	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,1,1))) + '.'
+	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,2,1))) + '.'
+	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,3,1))) + '.'
+	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,4,1)))	AS SubNetIP
-- TODO: Host Address Range, Broadcast Address, Wildcard Mask etc.
FROM	dbo.SubNet	S
GO
CREATE VIEW [dbo].[SubNetMask] AS
SELECT * FROM (VALUES
 (32,Convert(Int,0xFFFFFFFF))
,(31,Convert(Int,0xFFFFFFFE))
,(30,Convert(Int,0xFFFFFFFC))
,(29,Convert(Int,0xFFFFFFF8))
,(28,Convert(Int,0xFFFFFFF0))
,(27,Convert(Int,0xFFFFFFE0))
,(26,Convert(Int,0xFFFFFFC0))
,(25,Convert(Int,0xFFFFFF80))
,(24,Convert(Int,0xFFFFFF00))
,(23,Convert(Int,0xFFFFFE00))
,(22,Convert(Int,0xFFFFFC00))
,(21,Convert(Int,0xFFFFF800))
,(20,Convert(Int,0xFFFFF000))
,(19,Convert(Int,0xFFFFE000))
,(18,Convert(Int,0xFFFFC000))
,(17,Convert(Int,0xFFFF8000))
,(16,Convert(Int,0xFFFF0000))
) M (Bits,Mask)
GO
CREATE FUNCTION [dbo].[fnIPSubNet] (
	@IP	VarChar(15)
) RETURNS TABLE AS RETURN
SELECT	Convert(Binary(4),Convert(Int,
	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,4)))
+	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,3)))
+	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,2)))
+	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,1)))
&	M.Mask))	AS SubNet
,	M.Bits		AS Mask
FROM	dbo.SubNetMask	M
GO
Триггер и процедура
CREATE TRIGGER [dbo].[trSubNetCheck] ON [dbo].[SubNet]
AFTER INSERT,UPDATE AS BEGIN
	SET NOCOUNT ON;

	IF Exists(	-- Надсети, перебором SEEK
		SELECT	*
		FROM	Inserted	I
		JOIN	dbo.SubNetMask	M ON M.Bits	< I.Mask
		JOIN	dbo.SubNet	T ON T.Mask	= M.Bits
					 AND T.SubNet	= I.SubNet & M.Mask
	) OR Exists(	-- Подсети, по диапазону
		SELECT	*
		FROM	Inserted	I
		JOIN	dbo.SubNetMask	M ON M.Bits	= I.Mask
		JOIN	dbo.SubNet	T ON T.SubNet	BETWEEN I.SubNet AND I.SubNet | ~M.Mask
					 AND T.Mask	> I.Mask
	) BEGIN
		ROLLBACK;
		RAISERROR('SubNet range intersect',16,1);
		RETURN;
	END
END
GO
CREATE PROCEDURE [dbo].[spSubNetAdd]
	@IP	VarChar(15)
,	@Mask	TinyInt
AS BEGIN
	SET NOCOUNT ON;

	INSERT	dbo.SubNet	( SubNet,  Mask)
	SELECT			S.SubNet,S.Mask
	FROM	dbo.fnIPSubNet(@IP)	S
	WHERE	S.Mask = @Mask
	IF (@@RowCount != 1)
		RAISERROR('Wrong Subnet',16,1)
END
GO
Тест:
EXEC dbo.spSubNetAdd '195.58.53.0',24
EXEC dbo.spSubNetAdd '195.58.52.0',23	-- SubNet range intersect
Поиск принадлежности IP к подсети
DECLARE	@@IP VarChar(15) = '195.58.53.3'
SELECT	TOP(1) S.*
FROM	dbo.fnIPSubNet(@@IP)	I
JOIN	dbo.vwSubNet		S ON S.SubNet	= I.SubNet
				 AND S.Mask	= I.Mask
ORDER BY I.Mask DESC
Естественно что всё доведено до предела скорости работы (разве что можно отдельный парсер IP выделить для процедуры), хоть миллионы подсетей.
Универсальный парсер IP4 подсетей сложнее будет, т.к. можно писать октеты и сокращения - там уйма приколов.
24 апр 14, 03:48    [15926464]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить