1. Database extension

    Article: AN0001766Updated: 31.07.2018

    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 code of the role for all the authenticated users.
    Get_ReportRoleCode Function returns constant with code of the role for reports.
    Get_AllUsersRoleCode Function returns constant with code of the role for all users.
    Get_NoAuthenticateUsersRoleCode FunkcFunction returns constant with code of the role for all unauthenticated users.
    FindClassDef(@modelCode varchar(100), @classDefCode varchar(100)) Function returns ID of a class according to the 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..
    FindColumnId(@classDefId int, @columnCode varchar(100)) Function returns column id according to the input class ID and column code.

    Use the functions Get_...RoleCode 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 and FindColumnDbName are used to get class ID and database name of columns. These names are necessary for dynamic creating SQL on the database 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)

    You can create a View or  a Stored procedure also in case when finding information by means of ObjectGears scripts would be labour intensive, demanding on computing or lead to a forced use of the functionality. You can build ObjectGears Query over the View or Stored procedure and then filter, sort and use the query where you need.