Триггеры.
Мы записали GUI (внутренне названный Красный Матрица: Перезагрузка ) для разрешения легкого создания/управления триггеров входа аудита.
Вот некоторый DDL используемого материала:
<час>CREATE TABLE [AuditLog] (
[AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,
[ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()),
[RowGUID] [uniqueidentifier] NOT NULL ,
[ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserGUID] [uniqueidentifier] NULL ,
[TagGUID] [uniqueidentifier] NULL ,
[Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
AuditLog <час> CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes
FOR INSERT
AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/*We dont' log individual field changes Old/New because the row is new.
So we only have one record - INSERTED*/
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'INSERTED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
null, --OldValue
null --NewValue
FROM Inserted i
<час> CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes
FOR UPDATE AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/* ParentNodeGUID uniqueidentifier */
IF UPDATE (ParentNodeGUID)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'ParentNodeGUID', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
d.ParentNodeGUID, --OldValue
i.ParentNodeGUID --NewValue
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL)
OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL)
OR (d.ParentNodeGUID <> i.ParentNodeGUID)
END
/* Caption varchar(255) */
IF UPDATE (Caption)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'Caption', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
d.Caption, --OldValue
i.Caption --NewValue
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL)
OR (d.Caption IS NOT NULL AND i.Caption IS NULL)
OR (d.Caption <> i.Caption)
END
...
/* ImageGUID uniqueidentifier */
IF UPDATE (ImageGUID)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'ImageGUID', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
(SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue
(SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL)
OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL)
OR (d.ImageGUID <> i.ImageGUID)
END
<час> CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes
FOR DELETE
AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/*We dont' log individual field changes Old/New because the row is new.
So we only have one record - DELETED*/
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue,NewValue)
SELECT
getdate(), --ChangeDate
d.NodeGUID, --RowGUID
'DELETED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'', --FieldName
d.ParentNodeGUID, --TagGUID
d.Caption, --Tag
null, --OldValue
null --NewValue
FROM Deleted d
<час> И для знания, какой пользователь в программном обеспечении сделал обновление, каждое соединение "регистрирует себя на SQL Server" путем вызова хранимой процедуры:
CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS
/* Saves the given UserGUID as the session's "Context Information" */
IF @UserGUID IS NULL
BEGIN
PRINT 'Emptying CONTEXT_INFO because of null @UserGUID'
DECLARE @BinVar varbinary(128)
SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) )
SET CONTEXT_INFO @BinVar
RETURN 0
END
DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes
SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16))
SET CONTEXT_INFO @UserGUIDBinary
/* To load the guid back
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
select @SavedUserGUID AS UserGUID
*/
<час> Примечания
иногда значения "OldValue" и "NewValue" записаны как подвыбор - для получения значимой строки. т.е. "
OldValue: {233-й-ad34234..} NewValue: {883-sdf34...}
менее полезно в журнале аудита, чем:
OldValue: Daimler Chrysler
NewValue: Cerberus Capital Management
Заключительное примечание : Не стесняйтесь не делать то, что мы делаем. Здорово для нас, но все остальные свободны не использовать его.
Here’s another solution:
span = 2
words = "this-is-a-string".split("-")
print ["-".join(words[i:i+span]) for i in range(0, len(words), span)]
>>> s="a-b-c-d-e-f-g-h-i-j-k-l" # use zip(*[i]*n)
>>> i=iter(s.split('-')) # for the nth case
>>> map("-".join,zip(i,i))
['a-b', 'c-d', 'e-f', 'g-h', 'i-j', 'k-l']
>>> i=iter(s.split('-'))
>>> map("-".join,zip(*[i]*3))
['a-b-c', 'd-e-f', 'g-h-i', 'j-k-l']
>>> i=iter(s.split('-'))
>>> map("-".join,zip(*[i]*4))
['a-b-c-d', 'e-f-g-h', 'i-j-k-l']
Иногда itertools.izip работает быстрее, как вы можете видеть в результатах
>>> from itertools import izip
>>> s="a-b-c-d-e-f-g-h-i-j-k-l"
>>> i=iter(s.split("-"))
>>> ["-".join(x) for x in izip(i,i)]
['a-b', 'c-d', 'e-f', 'g-h', 'i-j', 'k-l']
Вот версия, в которой вроде работает с нечетным числом частей в зависимости от того, какой результат вы хотите в этом случае. Вы могли бы предпочесть обрезать '-'
в конце последнего элемента с помощью .rstrip ('-')
, например.
>>> from itertools import izip_longest
>>> s="a-b-c-d-e-f-g-h-i-j-k-l-m"
>>> i=iter(s.split('-'))
>>> map("-".join,izip_longest(i,i,fillvalue=""))
['a-b', 'c-d', 'e-f', 'g-h', 'i-j', 'k-l', 'm-']
Вот некоторые тайминги
$ python -m timeit -s 'import re;r=re.compile("[^-]+-[^-]+");s="a-b-c-d-e-f-g-h-i-j-k-l"' 'r.findall(s)'
100000 loops, best of 3: 4.31 usec per loop
$ python -m timeit -s 'from itertools import izip;s="a-b-c-d-e-f-g-h-i-j-k-l"' 'i=iter(s.split("-"));["-".join(x) for x in izip(i,i)]'
100000 loops, best of 3: 5.41 usec per loop
$ python -m timeit -s 's="a-b-c-d-e-f-g-h-i-j-k-l"' 'i=iter(s.split("-"));["-".join(x) for x in zip(i,i)]'
100000 loops, best of 3: 7.3 usec per loop
$ python -m timeit -s 's="a-b-c-d-e-f-g-h-i-j-k-l"' 't=s.split("-");["-".join(t[i:i+2]) for i in range(0, len(t), 2)]'
100000 loops, best of 3: 7.49 usec per loop
$ python -m timeit -s 's="a-b-c-d-e-f-g-h-i-j-k-l"' '["-".join([x,y]) for x,y in zip(s.split("-")[::2], s.split("-")[1::2])]'
100000 loops, best of 3: 9.51 usec per loop
Regular expressions handle this easily:
import re
s = "aaaa-aa-bbbb-bb-c-ccccc-d-ddddd"
print re.findall("[^-]+-[^-]+", s)
Output:
['aaaa-aa', 'bbbb-bb', 'c-ccccc', 'd-ddddd']
Update for Nick D:
n = 3
print re.findall("-".join(["[^-]+"] * n), s)
Output:
['aaaa-aa-bbbb', 'bb-c-ccccc']
EDIT: The original code I posted didn't work. This version does:
I don't think you can split on every other one, but you could split on every - and join every pair.
chunks = []
content = "this-is-a-string"
split_string = content.split('-')
for i in range(0, len(split_string) - 1,2) :
if i < len(split_string) - 1:
chunks.append("-".join([split_string[i], split_string[i+1]]))
else:
chunks.append(split_string[i])
l = 'this-is-a-string'.split()
nl = []
ss = ""
c = 0
for s in l:
c += 1
if c%2 == 0:
ss = s
else:
ss = "%s-%s"%(ss,s)
nl.insert(ss)
print nl
I think several of the already given solutions are good enough, but just for fun, I did this version:
def twosplit(s,sep):
first=s.find(sep)
if first>=0:
second=s.find(sep,first+1)
if second>=0:
return [s[0:second]] + twosplit(s[second+1:],sep)
else:
return [s]
else:
return [s]
print twosplit("this-is-a-string","-")