SQL to analyze scripting usage

The attached "SQLToAnalyzeLuaScripts" script at the bottom of this article will help you gather the number of lines of Lua script in a given configuration DB. This can be taken a step further to get stats on where scripts (e.g. OnFocus vs. OnChange) are being deployed as well. In the future, other intelligence can be added to calculate scripting size.

The script below does not count comment only lines or empty lines. One of the options in the script disregards Lua-specific directives such as "do" and "end". These will usually be braces in JavaScript. 

Disclaimer - This was only tested in 7.3 in a few databases, therefore we recommend you do not run this directly on your production server but instead on a clone of it. 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

--Arun Nagarajan

--Verivo Software

--Original version 4/10/12

--Goal is to collect basic stats on scripting usage

BEGIN TRAN

DECLARE @Type varchar(50),@ScriptID varchar(50), @ScriptText varchar(max), @ScriptLine varchar(max)

DECLARE @StartPos int, @Length int, @AppID int

DECLARE @Delimeter char(1)

SET @Delimeter = char(10)

DECLARE @tblScripts TABLE(AppID int,Type varchar(50),ScriptID varchar(50), ScriptLine varchar(max))

DECLARE temp_cursor CURSOR FOR

    select AppID,'Event',EventScriptID,Script from tmpcfg_EventScript union all select AppID,'Global',GlobalScriptID,Script from tmpcfg_GlobalScript

OPEN temp_cursor

FETCH NEXT FROM temp_cursor INTO @AppID,@Type,@ScriptID, @ScriptText

WHILE @@FETCH_STATUS = 0 BEGIN

    WHILE LEN(@ScriptText) > 0

      BEGIN

        SET @StartPos = CHARINDEX(@Delimeter, @ScriptText)

        IF @StartPos < 0 SET @StartPos = 0

        SET @Length = LEN(@ScriptText) - @StartPos - 1

        IF @Length < 0 SET @Length = 0

        IF @StartPos > 0

          BEGIN

            SET @ScriptLine = SUBSTRING(@ScriptText, 1, @StartPos - 1)

            SET @ScriptText = SUBSTRING(@ScriptText, @StartPos + 1, LEN(@ScriptText) - @StartPos)

          END

        ELSE

          BEGIN

            SET @ScriptLine = @ScriptText

            SET @ScriptText = ''

          END

        SELECT @ScriptLine = LTRIM(RTRIM(REPLACE(REPLACE(@ScriptLine, CHAR(13), ''), CHAR(10), '')))

         

        IF LEN(@ScriptLine) > 0 AND @ScriptLine NOT LIKE '--%' --AND @ScriptLine NOT IN ('do','then','else','end')

            BEGIN

                INSERT @tblScripts (AppID,Type,ScriptID,ScriptLine) VALUES(@AppID,@Type,@ScriptID,@ScriptLine)

            END

    END

FETCH NEXT FROM temp_cursor INTO @AppID,@Type,@ScriptID, @ScriptText

END

CLOSE temp_cursor

DEALLOCATE temp_cursor

--SELECT * FROM @tblScripts

--NULL implies shared global scripts

select AppID,count(*) as NumLinesOfScript from @tblScripts group by AppID

select AppID,Type,count(*) as NumLinesOfScript

from @tblScripts

group by AppID,Type

select AppID,'ControlOnFocus' as Type,count(OnFocusScriptID) as NumOfControls ,sum(NumLinesOfScript) as NumLinesOfScript from

(select c.AppID,OnFocusScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_Control c

left outer join @tblScripts t on c.OnFocusScriptID = t.ScriptID and c.AppID = t.AppID

where OnFocusScriptID is not null

group by c.AppID,OnFocusScriptID) tab

group by AppID

select AppID,'ControlOnUnFocus' as Type,count(OnUnFocusScriptID) as NumOfControls ,sum(NumLinesOfScript) as NumLinesOfScript from

(select c.AppID,OnUnFocusScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_Control c

left outer join @tblScripts t on c.OnUnFocusScriptID = t.ScriptID and c.AppID = t.AppID

where OnUnFocusScriptID is not null

group by c.AppID,OnUnFocusScriptID) tab

group by AppID

select AppID,'ControlOnChange' as Type,count(OnChangeScriptID) as NumOfControls ,sum(NumLinesOfScript) as NumLinesOfScript from

(select c.AppID,OnChangeScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_Control c

left outer join @tblScripts t on c.OnChangeScriptID = t.ScriptID and c.AppID = t.AppID

where OnChangeScriptID is not null

group by c.AppID,OnChangeScriptID) tab

group by AppID

 

select AppID,'ViewOnLoad' as Type,count(OnLoadScriptID) as NumOfViews ,sum(NumLinesOfScript) as NumLinesOfScript from

(select v.AppID,OnLoadScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_View v

left outer join @tblScripts t on v.OnLoadScriptID = t.ScriptID and v.AppID = t.AppID

where OnLoadScriptID is not null

group by v.AppID,OnLoadScriptID) tab

group by AppID

select AppID,'ViewOnSave' as Type,count(OnSaveScriptID) as NumOfViews ,sum(NumLinesOfScript) as NumLinesOfScript from

(select v.AppID,OnSaveScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_View v

left outer join @tblScripts t on v.OnSaveScriptID = t.ScriptID and v.AppID = t.AppID

where OnSaveScriptID is not null

group by v.AppID,OnSaveScriptID) tab

group by AppID

select AppID,'ViewOnRowWillAdd' as Type,count(OnRowWillAddScriptID) as NumOfViews ,sum(NumLinesOfScript) as NumLinesOfScript from

(select v.AppID,OnRowWillAddScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_View v

left outer join @tblScripts t on v.OnRowWillAddScriptID = t.ScriptID and v.AppID = t.AppID

where OnRowWillAddScriptID is not null

group by v.AppID,OnRowWillAddScriptID) tab

group by AppID

 

select AppID,'ViewOnRowWasAdded' as Type,count(OnRowWasAddedScriptID) as NumOfViews ,sum(NumLinesOfScript) as NumLinesOfScript from

(select v.AppID,OnRowWasAddedScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_View v

left outer join @tblScripts t on v.OnRowWasAddedScriptID = t.ScriptID and v.AppID = t.AppID

where OnRowWasAddedScriptID is not null

group by v.AppID,OnRowWasAddedScriptID) tab

group by AppID

 

select AppID,'ViewOnRowWillDelete' as Type,count(OnRowWillDeleteScriptID) as NumOfViews ,sum(NumLinesOfScript) as NumLinesOfScript from

(select v.AppID,OnRowWillDeleteScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_View v

left outer join @tblScripts t on v.OnRowWillDeleteScriptID = t.ScriptID and v.AppID = t.AppID

where OnRowWillDeleteScriptID is not null

group by v.AppID,OnRowWillDeleteScriptID) tab

group by AppID

 

select AppID,'ViewOnRowWasDeleted' as Type,count(OnRowWasDeletedScriptID) as NumOfViews ,sum(NumLinesOfScript) as NumLinesOfScript from

(select v.AppID,OnRowWasDeletedScriptID,count(ScriptLine) as NumLinesOfScript

from tmpcfg_View v

left outer join @tblScripts t on v.OnRowWasDeletedScriptID = t.ScriptID and v.AppID = t.AppID

where OnRowWasDeletedScriptID is not null

group by v.AppID,OnRowWasDeletedScriptID) tab

group by AppID

 

ROLLBACK

 

 

In this example there are no list builder scripting. 

7-24-2012_2-32-11_PM.png

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk