ObjectGears Help

Forum   ObjectGears (Version:

Database extension

ObjectGears system can be easily extended also on the database level. This extension is supported by several stored procedures and functions.

Name Description
Get_AuthenticateUsersRoleCode Function returns constant with name of the role for all the authenticated users.
Get_ReportRoleCode Function returns constant with name of the role for reports.
FindClassDef(@modelCode varchar(100), @classDefCode varchar(100)) Function returns ID of a class according to model code and class code.
FindColumnDbName( @classDefId int, @columnCode varchar(100)) Function returns database name of the column according to the defined class ID and column code..

Use the functions Get_AuthenticateUsersRoleCode and Get_ReportRoleCode instead of hardcoding of the name in your code to avoid future compatibility issues.

Use of function for mapping role ID (role ID can be different in each ObjectGears instance).

select r.id, r.code
from [role] r
where r.Code = dbo.Get_AuthenticateUsersRoleCode()


select r.id, r.code
from [role] r
where r.Code = dbo.Get_ReportRoleCode()


Functions FindClassDef a ndFindColumnDbName are used to get class ID and database name of columns. These names are necessary for dynamic creating SQL on the databsse level in order it can be portable to other instances.

ALTER PROCEDURE [dbo].[AddStatistics]

 declare @m_code varchar(100)
 set @m_code = 'systemutil'

 Declare @date varchar(200)
 select @date = 'DateAdd(day, ' + cast( datepart( day, getdate()) as varchar(10)) + ' - 1, DateAdd(month, ' + cast( datepart( month, getdate()) as varchar(10)) + ' - 1, DateAdd(Year, ' + cast( datepart( year, getdate()) as varchar(10)) + ' - 1900, DateAdd(Hour, ' + cast( datepart( hour, getdate()) as varchar(10)) + ', DateAdd(Minute, ' + cast( datepart( minute, getdate()) as varchar(10)) + ', 0)))))'

 declare @clName varchar(50)
 declare @col_rowsName varchar(50)
 declare @col_total_space varchar(50)
 declare @col_total_used varchar(50)
 declare @sql varchar(max)
 declare @cl_id int

 --nacist cl
 set @cl_id = dbo.FindClassDef( @m_code, 'stat_system')
 set @col_rowsName = dbo.FindColumnDbName( @cl_id, 'rows')
 set @col_total_space = dbo.FindColumnDbName( @cl_id, 'total_space_kb')
 set @col_total_used = dbo.FindColumnDbName( @cl_id, 'total_used_space_kb')
 set @clName = 'DataRow' + cast( @cl_id as varchar(10))

 --spocitat pocet zaznamu
 set @sql = 'insert into ' + @clName + '(created, CreatedDataSourceId, Creator, ShortDescription, ' + @col_rowsName + ', ' + @col_total_space + ', ' + @col_total_used + ')
     select ' + @date + ', 4, ''SYSTEM'', t.name, p.rows, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB
      from sys.tables t
       INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
       INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
       INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
       LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
      where t.type_desc = ''USER_TABLE''
       and t.name not in ( ''appversion'')
       and t.name not like ''datarow%''
       and t.name not like ''aspnet_%''
       and (t.name not like ''wp%'' or t.name = ''wpbase'')
       and s.Name = ''dbo''
      GROUP BY t.Name, s.Name, p.Rows'
 exec ( @sql)

This website is using cookies files to provide services and analyse visits. You agree with that by using this website.     Further information