jinjazz 日志测试 sql版本

[zsforever]

不知道有多少人看到 csdn : jinjazz 写的 日志心得，我近期发现后研究整理了一个 纯sql版的测试版本，大家有兴趣的来, 调用 exec dbo.p_getLog TableName 查看最近的10条log（只是测试用）,有什么提高和指正也请赐教,下面直接贴代码了
注意测试的时候找 数据类型在sp定义范围内的

create function dbo.f_splitBinary(@s varbinary(8000))
returns @t table(id int identity(1,1),Value binary(1))
as
begin
declare @i int,@im int
select @i=1,@im=datalength(@s)
while @i<=@im
begin
  insert into @t select substring(@s,@i,1) 
  set @i=@i+1
end
return
end

create function dbo.f_reverseBinary(@s varbinary(128))
returns varbinary(128)
as
begin
declare @r varbinary(128)
set @r=0x
select @r=@r+Value from dbo.f_splitBinary(@s) a order by id desc
return @r
end

create proc p_getLog(@TableName sysname,@c int=10)
as
set nocount on
declare @s varbinary(8000),@str varchar(max),@lb int,@le int,@operation varchar(128)
declare @i int,@lib int,@lie int,@ib int,@ie int,@lenVar int,@f bit,@cv int,@columnname sysname,@length int,@columntype varchar(32)
select b.name,b.length,c.name typename,b.colid,
case when c.name not like '%var%' and c.name not in ('xml','text','image') then 1 else 2 end p,row_number() over(partition by 
case when c.name not like '%var%' and c.name not in ('xml','text','image') then 1 else 2 end order by colid) pid
into #t
from sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertype
where a.name=@TableName order by b.colid
SELECT top(@c) Operation,[RowLog Contents 0],id=identity(int,1,1) into #t1
from::fn_dblog (null, null) 
where AllocUnitName like [email=]'dbo.'+@TableName+'%'and[/email]
Operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS') order by [Current LSN] desc
select @lb=min(id),@le=max(id) from #t1
while @lb<=@le
begin
select @operation=Operation,@s=[RowLog Contents 0] from #t1 where id=@lb
select @i=5,@str='',@f=0,@cv=1
select @lib=min(pid),@lie=max(pid) from #t where p=1 
while @lib<=@lie
begin
  select @columnname=name,@length=length,@columntype=typename from #t where p=1 and pid=@lib
  if @columntype='char'
  select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@length
  else if @columntype='datetime'
  select @str=@str+@columnname+'='+convert(varchar,dateadd(second,convert(int,dbo.f_reverseBinary(substring(@s,@i,4)))/300
    ,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+4,4))),'1900-01-01')),120)+',',@i=@i+8
  else if @columntype='int'
  select @str=@str+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+',',@i=@i+4
  else if @columntype='bit'
  begin
  select @str=@str+@columnname+'='+rtrim(convert(bit,substring(@s,@i,1)))+',',@i=@i+1
  if @f=0 set @f=1 else set @f=0
  end
  set @lib=@lib+1
end
if @f=0 set @i=@i+3 else set @i=@i+2
set @lenVar=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
set @i=@i+2
set @ib=@i + @lenVar*2
set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
select @lib=min(pid),@lie=max(pid) from #t where p=2 
while @lib<=@lie
begin
  select @columnname=name,@length=length,@columntype=typename from #t where p=2 and pid=@lib
  if @columntype='varchar'
  begin
  select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@ib,@ie-@ib+1))+','
  select @ib=@ie+1,@i=@i+2
  set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
  end
  set @lib=@lib+1
end
set @str=left(@str,len(@str)-1)
print @operation+':'+@str
set @lb=@lb+1
end
drop table #t,#t1 

[Zoezs]

好贴，顶下

[Zoezs]

SF

[ws_hgo]

等着
剪剪

[ks_reny]

帮顶

[nzperfect]

日志截断就无实际意义了

[jinjazz]

谢谢lz对我的关注，lz此帖指的是如何把日志中的二进制数据转为实际类型的对应值吧

[jinjazz]

我在sql2005测试了int字段的insert，没有问题，我把去年代码翻一下，看有没有可以补充的。

[zsforever]

做了点小的改进
1、考虑字段数超过8个的问题
2、null value的读取

ALTER proc [dbo].[p_getLog](@TableName sysname,@c int=10)
as
set nocount on
declare @s varbinary(8000),@str varchar(8000),@lb int,@le int,@operation varchar(128)
declare @i int,@lib int,@lie int,@ib int,@ie int,@lenVar int,@columnname sysname,@length int,@columntype varchar(32)
declare @TUVLength int,@vc int,@tc int

select b.name,b.length,c.name typename,b.colid,
case when c.name not like '%var%' and c.name not in ('xml','text','image') then 1 else 2 end p,row_number() over(partition by
case when c.name not like '%var%' and c.name not in ('xml','text','image') then 1 else 2 end order by colid) pid
into #t
from sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertype
where a.name=@TableName order by b.colid

SELECT top(@c) Operation,[RowLog Contents 0],id=identity(int,1,1) into #t1
from::fn_dblog (null, null)
where AllocUnitName like 'dbo.'+@TableName+'%'and
Operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS') order by [Current LSN] desc

select @TUVLength=sum(length) from #t where p=1
select @tc=count(*) from #t

select @lb=min(id),@le=max(id) from #t1
while @lb<=@le
begin
select @operation=Operation,@s=[RowLog Contents 0] from #t1 where id=@lb
select @i=5,@str='',@vc=0
select @lib=min(pid),@lie=max(pid) from #t where p=1
while @lib<=@lie
begin
select @columnname=name,@length=length,@columntype=typename from #t where p=1 and pid=@lib
-- print rtrim(@i)+'->'+rtrim(@length)
if dbo.f_reverseBinary(substring(@s,4+@TUVLength+2+1,1+((@tc-1)/8))) & power(2,@vc) <> 0
select @str=@str+@columnname+'=NULL,',@i=@i+@length
else if @columntype='char'
select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@length
else if @columntype='nchar'
select @str=@str+@columnname+'='+convert(nvarchar(256),substring(@s,@i,@length))+',',@i=@i+@length
else if @columntype='datetime'
select @str=@str+@columnname+'='+convert(varchar,dateadd(second,convert(int,dbo.f_reverseBinary(substring(@s,@i,4)))/300
,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+4,4))),'1900-01-01')),120)+',',@i=@i+8
else if @columntype='smalldatetime'
select @str=@str+@columnname+'='+convert(varchar,dateadd(minute,convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))/60
,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+2,2))),'1900-01-01')),120)+',',@i=@i+4
else if @columntype='int'
select @str=@str+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+',',@i=@i+4
-- else if @columntype='bit'
-- begin
-- select @str=@str+@columnname+'='+rtrim(convert(bit,substring(@s,@i,1)))+',',@i=@i+1
-- end
set @vc=@vc+1
set @lib=@lib+1
end
set @i=@i+3+((@tc-1)/8)
set @lenVar=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
set @i=@i+2
set @ib=@i + @lenVar*2
set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
select @lib=min(pid),@lie=max(pid) from #t where p=2
while @lib<=@lie
begin
-- print rtrim(@ib)+'->'+rtrim(@ie)
select @columnname=name,@length=length,@columntype=typename from #t where p=2 and pid=@lib
if dbo.f_reverseBinary(substring(@s,4+@TUVLength+2+1,1+((@tc-1)/8))) & power(2,@vc) <> 0
begin
select @str=@str+@columnname+'=NULL,'
select @ib=@ie+1,@i=@i+2
set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
end
else if @columntype='varchar'
begin
select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@ib,@ie-@ib+1))+','
select @ib=@ie+1,@i=@i+2
set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
end
else if @columntype='nvarchar'
begin
select @str=@str+@columnname+'='+convert(nvarchar(256),substring(@s,@ib,@ie-@ib+1))+','
select @ib=@ie+1,@i=@i+2
set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
end
set @vc=@vc+1
set @lib=@lib+1
end
set @str=left(@str,len(@str)-1)
print @operation+':'+@str
set @lb=@lb+1
end
drop table #t,#t1

测试
create table abc(c1 int primary key,c2 datetime,c3 smalldatetime,c4 char(3),c5 varchar,c6 varchar(10),c7 varchar(3),c8 nvarchar,c9 varchar)
insert into abc
select 1,getdate(),null,'123',0,'324afa445','a',null,1
insert into abc
select 2,getdate(),getdate(),'456',null,'32432asdf','b',0,1
insert into abc
select 3,getdate(),getdate(),'789',0,'anzi3223','c',1,1
delete from abc

exec dbo.p_getLog abc

bit类型的测试一直有问题，不知道哪错了，至此不再研究了，用第三方工具很实用

[gxg353]

UP

[playwarcraft]

这么有专研精神，顶下。。。

[jinjazz]

这是我的代码片段，定长的

switch (col_datatype.ToLower())
                {
                    case "char":
                    case "nchar":
                        //读取定长字符串，需要根据表结构指定长度
                        dRow[col_name] = System.Text.Encoding.Default.GetString(data, index, col_length);
                        index += col_length;
                        break;
                    case "datetime":
                        //读取datetime字段，sql为8字节保存
                        System.DateTime date = new DateTime(1900, 1, 1);
                        //前四位1/300秒保存
                        int second = BitConverter.ToInt32(data, index);
                        date = date.AddSeconds(second / 300);
                        index += 4;
                        //后四位1900-1-1的天数
                        int days = BitConverter.ToInt32(data, index);
                        date = date.AddDays(days);
                        index += 4;
                        dRow[col_name] = date;
                        break;
                    case "smalldatetime":
                        //读取smalldatetime字段，sql为4字节保存
                        System.DateTime date1 = new DateTime(1900, 1, 1);
                        //前2位
                        short mm = BitConverter.ToInt16(data, index);
                        date1 = date1.AddMinutes(mm);
                        index += 2;
                        //后2位
                        short dd = BitConverter.ToInt16(data, index);
                        date1 = date1.AddDays(dd);
                        index += 2;
                        dRow[col_name] = date1;
                        break;
                    case "int":
                        //读取int字段,为4个字节保存
                        dRow[col_name] = BitConverter.ToInt32(data, index);
                        index += col_length;
                        break;
                    case "smallint":
                        //读取smallint字段,为2个字节保存
                        dRow[col_name] = BitConverter.ToInt16(data, index);
                        index += col_length;
                        break;
                    case "tinyint":
                        //读取tinyint字段,为1个字节保存
                        dRow[col_name] = data[index];
                        index += col_length;
                        break;
                    case "bigint":
                    case "timestamp":
                        //读取bigint字段,为8个字节保存
                        dRow[col_name] = BitConverter.ToInt64(data, index);
                        index += col_length;
                        break;
                    case "bit":
                        //读取bit字段,为1个字节保存
                        dRow[col_name] = BitConverter.ToBoolean(data, index);
                        index += col_length;
                        break;
                    case "decimal":
                    case "numeric":
                        //读取decimal字段,为?个字节保存
                        //dRow[colName] = BitConverter.ToDouble(data, index);
                        byte[] bDecimal = new byte[col_length];
                        bDecimal[0] = 0;
                        bDecimal[1] = col_xprec;
                        bDecimal[2] = col_xscale;
                        bDecimal[3] = 0;
                        Array.Copy(data, index, bDecimal, 4, col_length - 4);
                        dRow[col_name] = getDecimal(bDecimal, col_xprec, col_xscale);
                        index += col_length;
                        break;
                    case "float":
                        //读取float字段,为4&8个字节保存
                        dRow[col_name] = col_length == 4 ? BitConverter.ToSingle(data, index) : BitConverter.ToDouble(data, index);
                        index += col_length;
                        break;
                    case "money":
                        //读取money字段,为8个字节保存
                        dRow[col_name] = BitConverter.ToDouble(data, index);
                        index += col_length;
                        break;
                    case "smallmoney":
                        //读取smallmoney字段,为4个字节保存
                        dRow[col_name] = BitConverter.ToSingle(data, index);
                        index += col_length;
                        break;
                    case "real":
                        //读取real字段,为4个字节保存
                        dRow[col_name] = BitConverter.ToSingle(data, index);
                        index += col_length;
                        break;
                    case "uniqueidentifier":
                        //读取uniqueidentifier字段,为16个字节保存
                        byte[] bGuid = new byte[col_length];
                        Array.Copy(data, index, bGuid, 0, col_length);
                        dRow[col_name] = new Guid(bGuid);
                        index += col_length;
                        break;
                    default:
                        //忽略不定长字段和其他不支持以及不愿意考虑的字段
                        break;
                }

不定长的

switch (col_datatype.ToLower())
                    {
                        case "varchar":
                            dRow[col_name] = System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex);
                            break;
                        case "nvarchar":
                            dRow[col_name] = System.Text.Encoding.Unicode.GetString(data, startIndex, endIndex - startIndex);
                            break;


                    }

因为当时只是摸索着写了测试代码和测试工具，后来发现有问题就放弃了，代码也没有整理过，比较乱，所以一直没有公开。

[ai_li7758521]

学习

[jdbcodbc]

学习了

[让你望见影子的墙]

关注

[zsforever]

多谢剪刀大侠对数据类型定义的代码 ：-） 等有时间测试下更多数据类型
虽然没啥实用价值，但只是有兴趣罢了，顺便说下sp中bit数据的定义有问题，参考剪刀的， char, varchar,int,datetime四种类型测试没发现问题

[-狙击手-]

dbcc log