Preface
Kognitio Console Scripting and Macros
Version 7.9.3
March 2013
Public
Notices
This document contains proprietary information that should not be reproduced in whole or in part, nor released to third parties nor used for purposes other than those for which it has been expressly provided without the prior written agreement of Kognitio Limited.
Kognitio Limited tries to ensure that the information in this document is correct and fairly stated, but does not accept liability for any error or omission.
Kognitio Console Macro Guide, October 2012
Kognitio Technology Centre
© Kognitio Limited, 2002-2012
3A Waterside Park, Cookham Road
BRACKNELL, Berks, RG12 1RB
United Kingdom
Public
Console Macro Guide vii
About this Manual
This manual is part of a series that describes how WX2 can enhance the productivity of your interactive database applications.
The manual assumes that the reader is familiar with relational concepts and SQL.
The Kognitio Console Macro system is built on XML and Lua (http://www.lua.org/), it assumes that the reader is familiar with these.
Kognitio Scripts (‘Kog’ Scripts) are an extension of Lua 5.2 which make it easier to use for the purpose of SQL access of a database.
Console Macro Guide vii
Contents
About this Manual iii
Contents v
1 Kog Scripting xv
1.1 Getting Started xv
1.2 Naked SQL xvi
Global Variables xvii
SQL statements returning a table xix
Using $ substitution within SQL statements xx
1.3 KogScript command line tool xxi
1.4 Using require in Kog scripts xxii
Package Path xxiii
LUA_PATH xxiv
Standard Library Location xxiv
1.5 What Happens “under the hood” xxv
1.6 Other Kognitio Extensions xxvi
Shell xxvi
Table __toString() xxvi
Mixed Lua and Kog scripts xxvi
Lua Lanes xxvi
1.7 Compatibility with SQL Script xxvii
Whenever xxvii
Loops xxvii
Jump to error exit xxviii
Move large chunks of code into functions xxix
Use functions with parameters for similar SQL xxix
2 Kog Script Libarary 31
2.1 Lua builtin library functions 31
_G 31
_VERSION 31
assert 32
collectgarbage 32
dofile 33
error 33
getmetatable 34
ipairs 34
load 35
loadfile 36
next 36
pairs 37
pcall 38
print 38
rawequal 38
rawget 39
rawlen 39
rawset 39
require 40
selectfrom 40
select 41
setmetatable 41
shell 42
sql 42
tonumber 43
tostring 43
type 44
xpcall 44
bit32.band 44
bit32.lshift 44
bit32.bxor 44
bit32.extract 45
bit32.lrotate 45
bit32.arshift 45
bit32.rshift 45
bit32.rrotate 45
bit32.bnot 45
bit32.replace 45
bit32.bor 45
bit32.btest 46
debug.getinfo 46
debug.traceback 46
debug.getupvalue 46
debug.getuservalue 46
debug.getlocal 46
debug.debug 46
debug.upvaluejoin 46
debug.upvalueid 47
debug.gethook 47
debug.getregistry 47
debug.getmetatable 47
debug.setmetatable 47
debug.setlocal 47
debug.sethook 47
debug.setupvalue 47
debug.setuservalue 48
os.time 48
os.exit 48
os.getenv 48
os.difftime 48
os.setlocale 48
os.remove 48
os.rename 48
os.tmpname 49
os.execute 49
os.clock 49
os.date 49
math.modf 49
math.asin 49
math.tan 49
math.exp 49
math.atan 50
math.acos 50
math.max 50
math.min 50
math.deg 50
math.randomseed 50
math.pow 50
math.ceil 50
math.floor 51
math.abs 51
math.cosh 51
math.sin 51
math.frexp 51
math.random 51
math.sinh 51
math.tanh 51
math.ldexp 52
math.cos 52
math.log 52
math.atan2 52
math.fmod 52
math.sqrt 52
math.rad 52
io.flush 52
io.lines 53
io.popen 53
io.stderr 53
io.open 53
io.input 53
io.stdout 53
io.stdin 53
io.output 53
io.close 54
io.read 54
io.tmpfile 54
io.type 54
io.write 54
string.format 54
string.byte 54
string.gmatch 54
string.char 55
string.lower 55
string.upper 55
string.len 55
string.sub 55
string.dump 55
string.reverse 55
string.rep 55
string.match 56
string.gsub 56
string.find 56
table.insert 56
table.__tostring 56
table.concat 56
table.pack 56
table.sort 56
table.remove 57
table.unpack 57
coroutine.status 57
coroutine.resume 57
coroutine.yield 57
coroutine.running 57
coroutine.wrap 57
package.config 57
package.cpath 58
package.loaded 58
package.loadlib 58
package.path 58
package.preload 58
package.searchers 58
package.searchpath 58
2.2 Kog Script std library 59
string.format 59
2.3 std.base 59
std.assert 59
std.bind 60
std.collect 60
std.compose 60
std.concat 61
std.curry 61
std.die 62
std.eval 62
std.filter 62
std.fold 63
std.id 63
std.ileaves 63
std.inodes 64
std.leaves 64
std.map 64
std.memoize 65
std.metamethod 65
std.nodes 65
std.op 66
std.pack 66
std.pickle 66
std.prettytostring 67
std.render 67
std.ripairs 68
std.tostring 68
std.totable 69
std.warn 69
std.class 70
std.list 71
2.4 std.strbuf 71
__concat 71
__tostring 71
2.5 std.string 71
.. 72
[ ] 72
caps 72
chomp 72
escapePattern 72
escapeShell 73
find_as_list 73
finds 73
gsubs 74
ltrim 74
numbertosi 74
ordinalSuffix 75
pad 75
rep 75
rtrim 75
trim 75
wrap 76
2.6 std.Table 76
clone 76
clone_rename 77
empty 77
indices 77
insert 78
invert 78
merge 78
pack 79
remove 79
sort 79
values 80
3 Data Definition 81
3.1 Data Items 81
Data Item 81
Column 83
Connectors 84
Connector 84
Domains 85
Domain 86
External Scripts 86
External Script 87
Plugins 88
Plugin 89
Privileges 89
Privilege 90
Queues 92
Queue 92
Schemas 93
Schema 94
Script Environments 94
Script Environment 95
SecClasses 96
SecClass 96
Sessions 97
Session 98
Tables 99
Table 100
Users 101
User 102
Views 103
View 103
4 Macro Elements 105
wx2macro 105
actiontext 107
beforeserverversion 107
chartformat 109
dockable 109
kogscript 110
menu 112
name 113
objects 114
object 116
properties 117
report 118
requiredserverversion 120
requiredtableaccess 121
scalemarks 121
script 122
sql 125
title 127
update 127
<widget> common 128
<widget type=“button”> 132
<widget type=“chart”> 132
<widget type=“checkbox”> 133
<widget type=“combobox”> 134
<widget type=“groupbox”> 137
<widget type=“grid”> 139
<widget type=“hbox”> 139
<widget type=“image”> 140
<widget type=“label”> 140
<widget type=“lineedit”> 141
<widget type=“meter”> 143
<widget type=“piechart”> 145
<widget type=“radiobutton”> 146
<widget type=“server”> 147
<widget type=“slider”> 148
<widget type=“stretch”> 149
<widget type=“table”> 150
<widget type=“username”> 152
<widget type=“vbox”> 153
5 Using KogScript In Macros 156
6 Using Old Style SQL Scripts In Macros 157
Variables 157
Assignment 158
Connect statement 158
Describe Command 158
Disconnect Statement 158
Edit Command 158
Errorcode Command 158
Errorcodenot Command 159
Export Command 159
Goto Command 159
Help Command 159
If Statement 159
Include Command 160
Results Command 160
Returncode Command 160
Set Statement 160
Setvar Command 161
Shell Command 161
Show Command 162
Whenever Statement 162
Quit Command 162
Script variables 163
Example 1: 164
7 Putting It All Together 166
How Macros Are Used 166
Macro Directories 169
Writing effective macros 170
QuickViz - Creating macros the easy way 170
A 172
Index 172
Console Macro Guide vii
1Kog Scripting
In this Chapter we describe the Kognitio Scripting language (shortened to Kog Script). It is based on Lua 5.2, but has a number of extensions
1.1 Getting Started
Kog scripting is turned off by default in Console. It may be turned on in the Configuration dialog.
Then the SQL Script button will create a Kog script. Now a Kog script may be entered and run with similar debugging facilities to the old SQL scripts.
Pure Lua scripts (i.e. without any Kognitio extensions) may be created by changing the “Script:” combo box to “Lua”.
1.2 Naked SQL
The main way that Kog scripts differ from Lua is that they may contain SQL statements, without having to be enclosed in quotation marks. This ‘Naked’ SQL may also include Lua variables and expressions.
SQL Statements return a Lua table holding the statement status and resultset from select statements. A few Lua global variables are also set, mainly for compatibility with old style SQL scripts. Let us start with a simple one line Kog script containing naked SQL.
select * from sys.ipe_user;
This performs the select statement and sets some global variables. Note that all naked SQL must end with a ‘;’, this is different from Lua where terminating statements with ‘;’ is optional. Unlike Lua statements, SQL statements are case-insensitive. So the above script could have been written:
SELECT * FROM sys.ipe_user;
As well as select the following SQL keywords may also start SQL statements in Kog scripts: alter, commit, connect, create, defrag, delete, diagnose, disconnect, drop, explain, export, grant, insert, import, lock, merge, picture, reclaim, redistribute, rename, repack, revoke, rollback, set, truncate, update and with.
Global Variables
When a naked SQL statement is executed the following global variables are set:
variable / valuecliver / String, client version e.g.“7.09.01-s121122”
sysver / String, system version e.g. “07.09.0002”
SQLState / “OK”, or error string
WCSerror / “OK” or error string
CompileTime / Integer, milli-seconds
ExecuteTime / Integer, milli-seconds
FirstRowTime / Integer, milli-seconds
TotalTime / Integer, milli-seconds
NumRows / Integer, number of rows returned
NumColumns / Integer, number of columns returned
QueryNumber / Integer, number of queries performed in this script run.
Col1 / String, Contents of row=1, column=1
Col2 / String, Contents of row=1, column=2
Col3 / String, Contents of row=1, column=3
Col4 / String, Contents of row=1, column=4
Col5 / String, Contents of row=1, column=5
Col6 / String, Contents of row=1, column=6
Col7 / String, Contents of row=1, column=7
There are also some global variables that are used to control how scripts are run, and what to do when errors are encountered.
Variable / Contentserror_mode / String, made up of three parts:
<onerror> [ ; <transaction> ] [ ; <exitcode> ]
Where:
<onerror> = “continue”, “stop”, debug” or “exit”, default=“continue”
<transaction> = “rollback”, “commit” or “none”, default=”none”
<exitcode> =”success”, “failure” or “failure=n”, default=”success”
On Console the <onerror> value is set to the contents of “On server error” combo box.
Set this to change the action when an SQL error is encountered.
e.g error_mode=’exit;rollback;failure=9’
script_error_mode / String, contents of “On script fail” combo box. Set this to “continue”, “debug”, “stop” to change the action when a script fails.
fail_mode / String. Set this to “continue”, “debug”, “stop” to change the action when an error is encountered during testing.
NOTE: for Kognitio testing purposes, may change in future releases.
history_mode / String, contents of the “History mode:” combo box.
history_group / String, contents of the “History group” text box. Controls the section in the query history following statements are placed in.
sqldebug / Integer: Controls output to Console logs pane:
nil => no output
0 => no output
1 => “OK”, or “error” + error string, rows affected
2 => + times
3 => + resultset
SQL statements returning a table
Naked SQL Statements may also return a Lua table containing data about the query.
t = select * from sys.ipe_user;
This table contains several values:
Table Variable / ValuenumRows / Integer, Number of rows in the resultset.
numCols / Integer, Number of columns in the resultset.
status / Integer, 0=OK
colNames / Table
rows / Table
The colNames table contains an array of the column names, Lua uses tables indexed from 1 and so t.colnames[1] is the name of the first column.
The rows table contains an array of rows (which are tables), Lua uses tables indexed from 1 and so t.rows[1] is contents of the first row.
Each row is a Lua table which is indexed by the column names. So t.rows[1].id is the user id in the first row.
Here is a script to show all the output from a query:
t = select * from ipe_user;
print("table\n")
for i,v in pairs(t) do
print(" ", i, v, " \n");
end;
print("column names\n")
for i,v in pairs(t.colNames) do
print(" ", i, v, " \n");
end;
print("rows\n")
for i,row in pairs(t.rows) do
print(" row "..i, " \n")
for j, k in pairs(row) do
print(" ", j, "=", k, " \n");
end;
end;
print("globals\n")
for i,v in pairs(_G) do
print(" ", i, v, " \n");
end;
Using $ substitution within SQL statements
Kog scripts use ‘$’ to perform substitution with Lua variables and expressions within naked SQL.
$<variable>[ ( <some text> ) ] [ <space> | ' | $ | ;|, ]
Where: <space> ::= ', ' | '\t' | '\r' | '\n'
A variable or Lua function call may end with a space, a single or double quote or a $
More complex Lua expressions need to be enclosed in matching ( ) .
$( <expression> ) [ <any> ]
Example 1:
names = {'ipe_user', 'ipe_schema'}
for i,v in pairs(names) do
select * from sys.$v;
end;
Example 2:
function name() return 'ipe_user' end
t = select * from sys.$name();
Example 3:
1.3 KogScript command line tool
This is installed by the Kognitio Console installer on Windows, and is found in
C:\Program Files (x86)\Kognitio Ltd\wx2clients32\kogscript.exe (32 bit)
C:\Program Files\Kognitio Ltd\wx2clients64\kogscript.exe (64 bit)
The usage is:
kogscript.exe [options] [script [args]]
Available options are:
-e stat execute string stat
-i enter interactive mode after executing stat
-l name require library name
-v show version information
-E ignore environment variables
-s server connect to server
-s { str } connect using the connection string str
-u user connect as user
-p password use password for connection
-- stop handling options
- stop handling options and execute stdin
On windows kogscript includes line editing and line history.
As for the Console version a variable called sqldebug controls the amount of info which is output after each SQL statement.
Kogscript may be exited by either ctrl-z or using os.exit().
Example 1:
>"C:\Program Files (x86)\Kognitio Ltd\wx2clients32\kogscript.exe"
Kog Scripting - version: 7.02.01-s120808-michaela1 (32 bit)
Lua 5.2.1 Copyright (C) 1994-2012 Lua.org, PUC-Rio
> connect to latest user sys using albatros;
> t = select * from
> ipe_user;
> for i,v in pairs(t.rows) do print(v.name) end
SYS
GRP_DBG
GRP_DISKUSAGE
GRP_MONITOR
GRP_LOG
ODBC
MIKE
PUBLIC
> ^Z
Example 2:
The following example runs the script listusers.kog:
print("Finding Users:")
connect to latest user sys using albatros;
t = select * from ipe_user;
for i,v in pairs(t.rows) do print(v.name) end
Using kogscript like this:
>"C:\Program Files (x86)\Kognitio Ltd\wx2clients32\kogscript.exe" –i listusers.kog
Kog Scripting - version: 7.02.01-s120808-michaela1 (32 bit)