CREATE PROCEDURE dbo.tng_fix_classes
AS
declare @class_name varchar(50)
declare @tab_name varchar(52)
declare @i int
declare @rowcount int
declare @super_class_name varchar(50)
declare @prop_cnt int
declare @prop_name varchar(50)
declare @datatype varchar(20)
declare @length int
declare @is_key tinyint
declare @required tinyint
declare @readonly tinyint
declare @default varchar(255)
declare @storage varchar(32)
declare @storage_field varchar(30)
declare @property_group varchar(10)
declare @method_name varchar(30)
DECLARE @ret_string Nvarchar (255)
declare @class_id int
declare @storage_tmp varchar(255)
declare @length_char varchar(10)
select @i = 0
select @class_name = 'TNGRoot'
select name,super_class_name,@i as level into #classes1 from tng_class where super_class_name = @class_name
select @rowcount = @@rowcount
while (@rowcount > 0)
begin
select @i = @i+1
insert into #classes1
select distinct name,super_class_name,@i from tng_class where super_class_name in (select distinct name from #classes1 where level = @i-1)
select @rowcount = @@rowcount
print @rowcount
end
select * into #properties from tng_property_definition where class_id in (select class_id from tng_class a,#classes1 b where a.name = b.name)
declare class_cursor cursor for
select name,super_class_name from #classes1
where level > 1 order by level,name
OPEN class_cursor
FETCH NEXT FROM class_cursor
INTO @class_name,@super_class_name
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Class being Processed is ' + @class_name
declare prop_cursor cursor for
select a.name from tng_property_definition a,tng_class b where a.class_id = b.class_id and b.name = @super_class_name
OPEN prop_cursor
FETCH NEXT FROM prop_cursor
INTO @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
select @prop_cnt =count(*) from tng_property_definition a,tng_class b where a.class_id = b.class_id and a.name = @prop_name and b.name = @class_name
if @prop_cnt =0
BEGIN
select @datatype = a.datatype,@length = a.length,@is_key = a.is_key,@required = a.required,@readonly = a.readonly,@default = a.default_value,@storage = a.instance_storage,@storage_field = a.storage_field,@property_group = a.property_group,@method_name = a.method_name
from tng_property_definition a,tng_class b where a.class_id = b.class_id and b.name = @super_class_name and a.name = @prop_name
print 'insert property ' + @prop_name + ' in class ' + @class_name
select @class_id = class_id from tng_class where name = @class_name
if @storage <> 'tng_managedobject'
begin
select @storage_tmp = 'wt'+@class_name
if exists (select * from dbo.sysobjects where id = object_id(N'' + @storage_tmp + '') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
select @storage = @storage_tmp
PRINT '@storage ' + @storage
PRINT '@storage_tmp ' + @storage_tmp
end
Begin tran t1
insert into dbo.tng_property_definition ( class_id, name, datatype, length, is_key, required, default_value,
instance_storage, storage_field, property_group, method_name, readonly, override)
values ( @class_id, @prop_name, @datatype, @length, @is_key, @required, @default,
@storage, @storage_field, @property_group, @method_name, @readonly, 1)
if @@error = 0
commit tran t1
if @storage <> 'tng_managedobject'
Begin
if (@datatype = 'CHAR' OR @datatype = 'VARCHAR' OR @datatype = 'BINARY')
begin
select @length_char = cast(@length AS varchar(10))
EXEC master..xp_sprintf @ret_string OUTPUT, 'Alter table dbo.%s ADD %s %s(%s ) NULL',@storage,@storage_field,@datatype,@length_char
PRINT @ret_string
end
else
begin
EXEC master..xp_sprintf @ret_string OUTPUT, 'Alter table dbo.%s ADD %s %s NULL',@storage,@storage_field,@datatype
PRINT @ret_string
end
EXECUTE sp_executesql @ret_string
end
END
select @default = a.default_value, @storage = a.instance_storage,@storage_field = a.storage_field ,@datatype = upper(a.datatype) from #properties a,tng_class b where a.class_id = b.class_id and a.name = @prop_name and b.name = @class_name
if @default is NOT NULL
BEGIN
if (@datatype = 'CHAR' OR @datatype = 'VARCHAR' OR @datatype = 'DATETIME' OR @datatype = 'DATE')
begin
if(@storage = 'tng_managedobject')
begin
EXEC master..xp_sprintf @ret_string OUTPUT, 'UPDATE dbo.tng_managedobject set %s = ''%s'' where %s IS NULL and class_name = ''%s''',@storage_field,@default,@storage_field,@class_name
PRINT @ret_string
end
else
begin
EXEC master..xp_sprintf @ret_string OUTPUT, 'UPDATE dbo.%s set %s = ''%s'' where %s IS NULL',@storage,@storage_field,@default,@storage_field
PRINT @ret_string
end
end
if (@datatype = 'INT' OR @datatype = 'TINYINT' OR @datatype = 'SMALLINT' OR @datatype = 'BINARY')
begin
if(@storage = 'tng_managedobject')
begin
EXEC master..xp_sprintf @ret_string OUTPUT, 'UPDATE dbo.tng_managedobject set %s = %s where %s IS NULL and class_name = ''%s''',@storage_field,@default,@storage_field,@class_name
PRINT @ret_string
end
else
begin
EXEC master..xp_sprintf @ret_string OUTPUT, 'UPDATE dbo.%s set %s = %s where %s IS NULL',@storage,@storage_field,@default,@storage_field
PRINT @ret_string
end
end
EXECUTE sp_executesql @ret_string
END
FETCH NEXT FROM prop_cursor
INTO @prop_name
END
CLOSE prop_cursor
DEALLOCATE prop_cursor
FETCH NEXT FROM class_cursor
INTO @class_name,@super_class_name
END
CLOSE class_cursor
DEALLOCATE class_cursor
GO
GRANT EXECUTE ON [dbo].[tng_fix_classes] TO [uniadmin]
GRANT EXECUTE ON [dbo].[tng_fix_classes] TO [uniuser]
GRANT EXECUTE ON [dbo].[tng_fix_classes] TO [wvadmin]
GRANT EXECUTE ON [dbo].[tng_fix_classes] TO [wvuser]
GO
[dbo].[tng_fix_classes]