flat select
select
<*|table1.*|col1|table1#col1>, ... from
...
desc
view
;
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_items | 1 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_item_tags | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_fidelity_cards | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_item_customer_infos | 1 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_inventory | 2 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
select
* from
v_items;
item_id | art_label | dept | avg_week_sales | sales_price |
artA | the article A | dept #1 | 10 | 1.500 |
artB | the article B | dept #2 | 10 | 3.200 |
box1 | a box | packaging | 10 | 0 |
select
* from
v_item_tags;
item_id | tag |
artA | tag #1 |
artA | tag #2 |
select
* from
v_sales;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artB | C2 | 20200102 | 6 | ID02 | # | artB | the article B | dept #2 | 10 | 3.200 | C2 | customer #2 |
artB | C1 | 20191231 | 4 | ID03 | # | artB | the article B | dept #2 | 10 | 3.200 | C1 | customer #1 |
artB | C2 | 20200102 | 7 | ID04 | box1 | artB | the article B | dept #2 | 10 | 3.200 | C2 | customer #2 |
# | C1 | 20200102 | 8 | ID05 | # | # | # | # | 0 | 0 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
select
sales.* from
v_sales;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id |
artA | C1 | 20191231 | 5 | ID01 | box1 |
artB | C2 | 20200102 | 6 | ID02 | # |
artB | C1 | 20191231 | 4 | ID03 | # |
artB | C2 | 20200102 | 7 | ID04 | box1 |
# | C1 | 20200102 | 8 | ID05 | # |
artA | C1 | 20191231 | 5 | ID06 | box1 |
artA | C2 | 20191231 | 5 | ID07 | box1 |
group by select
select
col1, col2, ... from
... group by
col1, col2, ...
group by
clause can be ommited
select
item_id, count(*) from
v_items group by
item_id;
item_id | count(*) |
artA | 1 |
artB | 1 |
box1 | 1 |
select
item_id, count(*) from
v_item_tags group by
item_id;
select
customer_id, dept, avg_week_sales, count(*) from
v_sales group by
customer_id, dept, avg_week_sales;
customer_id | dept | avg_week_sales | count(*) |
C1 | dept #1 | 10 | 2 |
C2 | dept #2 | 10 | 2 |
C1 | dept #2 | 10 | 1 |
C1 | # | # | 1 |
C2 | dept #1 | 10 | 1 |
select
customer_id, dept, count(*) from
v_sales;
customer_id | dept | count(*) |
C1 | dept #1 | 2 |
C2 | dept #2 | 2 |
C1 | dept #2 | 1 |
C1 | # | 1 |
C2 | dept #1 | 1 |
callback basics
Ths callback allows to run functions on the resultset
select
.. from
.. <callback
|cb
> callback_function1 callback_function2 ...
cb is a shortcut for callback
most common callbacks are sort and limit
more info are provided in "callback Lua functions" section
select
customer_id, count(*) from
v_sales group by
customer_id
callback
sort(2,'desc'
);
customer_id | count(*) |
C1 | 4 |
C2 | 3 |
select
customer_id, count(*) from
v_sales group by
customer_id
callback
sort(2,'asc'
) limit(1);
select
customer_id, count(*) from
v_sales group by
customer_id
cb
sort(2,'asc'
) limit(1);
select
customer_id, count(*)as
nb_sales from
v_sales group by
customer_id
cb
sort('nb_sales'
,'asc'
) limit(1);
common aggregation operators
<sum|count|countdistinct|min|max|avg>
select
customer_id, sum(sales_qty), sum(avg_week_sales) from
v_sales group by
customer_id;
customer_id | sum(sales_qty) | sum(avg_week_sales) |
C1 | 22 | 30 |
C2 | 18 | 30 |
select
customer_id, count(item_id), count(sales_qty), count(avg_week_sales) from
v_sales group by
customer_id;
customer_id | count(item_id) | count(sales_qty) | count(avg_week_sales) |
C1 | 3 | 4 | 3 |
C2 | 3 | 3 | 3 |
select
customer_id, countdistinct(item_id), countdistinct(sales_qty), countdistinct(avg_week_sales) from
v_sales group by
customer_id;
customer_id | countdistinct(item_id) | countdistinct(sales_qty) | countdistinct(avg_week_sales) |
C1 | 2 | 3 | 1 |
C2 | 2 | 3 | 1 |
select
customer_id, min(item_id), min(sales_qty), min(avg_week_sales) from
v_sales group by
customer_id;
customer_id | min(item_id) | min(sales_qty) | min(avg_week_sales) |
C1 | artA | 4 | 10 |
C2 | artA | 5 | 10 |
select
customer_id, max(item_id), max(sales_qty), max(avg_week_sales) from
v_sales group by
customer_id;
customer_id | max(item_id) | max(sales_qty) | max(avg_week_sales) |
C1 | artB | 8 | 10 |
C2 | artB | 7 | 10 |
select
customer_id, avg(sales_qty), avg(avg_week_sales) from
v_sales group by
customer_id;
customer_id | avg(sales_qty) | avg(avg_week_sales) |
C1 | 5.500 | 10 |
C2 | 6 | 10 |
other aggregation operators
<unique|countsequence|minstr|maxstr|list|ival|ivallist|ivalbin|p>
Note: uniquesoft can be used instead of unique for backward compatibility, behavior is the same
select
customer_id, unique(line_id), unique(item_id), unique(customer_id) from
v_sales group by
customer_id;
customer_id | unique(line_id) | unique(item_id) | unique(customer_id) |
C1 | # | artA | C1 |
C2 | ID07 | # | C2 |
select
customer_id, countsequence(item_id) from
v_sales group by
customer_id;
customer_id | countsequence(item_id) |
C1 | 3 |
C2 | 2 |
select
customer_id, minstr(item_id) from
v_sales group by
customer_id;
customer_id | minstr(item_id) |
C1 | artA |
C2 | artA |
select
customer_id, maxstr(item_id) from
v_sales group by
customer_id;
customer_id | maxstr(item_id) |
C1 | artB |
C2 | artB |
select
customer_id, list(item_id) from
v_sales group by
customer_id;
customer_id | list(item_id) |
C1 | artA,artB |
C2 | artA,artB |
select
customer_id, ival(item_id), ival(customer_id) from
v_sales group by
customer_id;
customer_id | ival(item_id) | ival(customer_id) |
C1 | 11 | 11 |
C2 | # | 12 |
select
customer_id, ivallist(item_id), ivallist(customer_id) from
v_sales group by
customer_id;
customer_id | ivallist(item_id) | ivallist(customer_id) |
C1 | 11/12 | 11 |
C2 | 11/12 | 12 |
select
customer_id, ivalbin(item_id), ivalbin(customer_id) from
v_sales group by
customer_id;
customer_id | ivalbin(item_id) | ivalbin(customer_id) |
C1 | 6144 | 2048 |
C2 | 6144 | 4096 |
select
customer_id, p(item_id), p(customer_id) from
v_sales group by
customer_id;
orphans management and dimension priority
Column item_id in view v_sales can come from table sales or table items
For artA and artB we don't care
For artC we do care because artC is a orhan record (*)
By default SB will look for item_id in non empty (**) dimension tables (***), then in the fact table
But you can tell SB which one you want using table#col syntax
(*) exists in fact table but not in dimension table
(**) an empty dimension is skipped
(***) dimension1 is the second table of from clause of the view create statement etc...,
--> !!! the order of dimensions in the from clause of the view create statement is important
select
item_id,sales#item_id,items#item_id from
v_sales;
item_id | sales#item_id | items#item_id |
artA | artA | artA |
artB | artB | artB |
artB | artB | artB |
artB | artB | artB |
# | artC | # |
artA | artA | artA |
artA | artA | artA |
select
count(*) from
v_sales and
item_id='artC'
;
select
count(*) from
v_sales and
items#item_id='artC'
;
select
count(*) from
v_sales and
sales#item_id='artC'
;
escape quote
''
insert
into
item_tags values
('artD'
,'A'
'__B'
);
refresh
dirty
view
;
select
* from
v_item_tags where
item_id='artD'
;
select
* from
v_item_tags where
tag='A'
'__B'
;
Stormbase & Lua
Lua is a progamming langage.
Stormbase is not developped in Lua, it is developped in native C.
But in some cases (callbacks and
where
clause, see here under) Stormbase calls Lua code.
Standard Lua code (provided in Stormbase installer) is located in _FUNCTIONS/stormbase.lua.
Custom Lua code can also be added in _FUNCTIONS/custom.lua.
utility Lua functions
desc
function
callback
where
(2,'utility'
);
function_name | function_type | signature | comment |
ratio | utility | ratio( value1, value2) | returns value1/value2 or 0 in limit cases (division by zero, nil value) |
round | utility | round( v , n_digits ) | returns math.floor(v+0.5), with n_digits precision, n_digits defaults to 0 |
decode | utility | decode( x , y , a , b ) | returns a in x equals y , b otherwise |
least | utility | least( x , y ) | checks that x and y are numbers and returns least |
greatest | utility | greatest( x , y ) | checks that x and y are numbers and returns greatest |
ceil | utility | ceil( x ) | checks that x is number and calls math.ceil |
floor | utility | floor( x ) | checks that x is number and calls math.floor |
check | utility | check( cond ) | boolean function |
instr | utility | instr( str1 , str2 ) | calls string.find(str1, str2), returns -1 if not found |
length | utility | length( tbl ) | return numbers of key of a key/value table |
merge | utility | merge( tbl1, tbl2 ) | sums the values of 2 key/value tables |
stringify | utility | stringify( tbl ) | stringify a key/value table |
join | utility | join(idx_values, arr_fields, _sep, _type, fn) | joins an array _sep, _type and fn are optional |
concat | utility | concat( a , b, c, d, e, f ) | returns a .. nvl(b,"") .. nvl(c,"") .. nvl(d,"") .. nvl(e,"") .. nvl(f,"") |
nvl | utility | nvl( a , b ) | returns b if a==nil |
callback Lua functions
desc
function
callback
where
(2,'callback'
);
function_name | function_type | signature | comment |
add_number | callback | add_number( name, str_fn ) | adds a number column to resultset |
add_text | callback | add_text( name, str_fn ) | adds a text column to resultset |
select_from | callback | select_from( 'group(<column_pos/column_name>) <sum/count/avg/min/max>(<column_pos/column_name>)' ) | does a select ... from (the_resultset) |
sub_select | callback | sub_select( 'group(<column_pos/column_name>) <sum/count/avg/min/max>(<column_pos/column_name>)' ) | adds a sub select column |
where | callback | where( <column_pos/column_name> , 'value' ) | does an "equal where clause" on the resultset |
like | callback | like( <column_pos/column_name> , 'value1!value2' ) | does a "like where clause" on the resultset |
grep | callback | grep( 'value1!value2' ) | does a "like where clause" on the resultset |
limit | callback | limit( rownum ) | limits the resultset to rownum lines |
sort | callback | sort( <column_pos1/column_name1> , <'asc'/'desc'> , <column_pos2/column_name2> , ... ) | sorts the resultset |
export | callback | export( path/to/file , header_yn ) |
open_join | callback | open_join( 'with1,with2..' , 'join_col1,join_col2..' , 'new_with_name') | do an open join on withs |
rename | callback | rename( 'col_name' , 'new_col_name' ) | rename a column |
keep_columns | callback | keep_columns( 'col1,col2...' ) | keeps column list |
select
item_id,customer_id,sum(sales_qty) from
v_sales group by
item_id,customer_id callback
sort(3,'asc'
);
item_id | customer_id | sum(sales_qty) |
artB | C1 | 4 |
artA | C2 | 5 |
# | C1 | 8 |
artA | C1 | 10 |
artB | C2 | 13 |
select
item_id,sum(sales_qty) from
v_sales group by
item_id callback
sort(2,'asc'
);
item_id | sum(sales_qty) |
# | 8 |
artA | 15 |
artB | 17 |
select
customer_id,sum(sales_qty) from
v_sales group by
customer_id callback
sort(2,'asc'
);
customer_id | sum(sales_qty) |
C2 | 18 |
C1 | 22 |
select
item_id,customer_id,sales_qty from
v_sales
callback
select_from('group(1) group(2) sum(3)'
) sort(3,'asc'
);
group_item_id | group_customer_id | sum_sales_qty |
artB | C1 | 4 |
artA | C2 | 5 |
# | C1 | 8 |
artA | C1 | 10 |
artB | C2 | 13 |
select
item_id,customer_id,sales_qty from
v_sales
callback
select_from('group(1) sum(3)'
) sort(2,'asc'
);
group_item_id | sum_sales_qty |
# | 8 |
artA | 15 |
artB | 17 |
select
item_id,customer_id,sales_qty from
v_sales
callback
select_from('group(2) sum(3)'
) sort(2,'asc'
);
group_customer_id | sum_sales_qty |
C2 | 18 |
C1 | 22 |
select
item_id,customer_id,sales_qty from
v_sales
callback
sub_select('group(1) group(2) sum(3)'
);
item_id | customer_id | sales_qty | group_item_id | group_customer_id | sum_sales_qty |
artA | C1 | 5 | artA | C1 | 10 |
artB | C2 | 6 | artB | C2 | 13 |
artB | C1 | 4 | artB | C1 | 4 |
artB | C2 | 7 | artB | C2 | 13 |
# | C1 | 8 | # | C1 | 8 |
artA | C1 | 5 | artA | C1 | 10 |
artA | C2 | 5 | artA | C2 | 5 |
select
item_id,customer_id,sales_qty from
v_sales
callback
sub_select('group(2) sum(3)'
);
item_id | customer_id | sales_qty | group_customer_id | sum_sales_qty |
artA | C1 | 5 | C1 | 22 |
artB | C2 | 6 | C2 | 18 |
artB | C1 | 4 | C1 | 22 |
artB | C2 | 7 | C2 | 18 |
# | C1 | 8 | C1 | 22 |
artA | C1 | 5 | C1 | 22 |
artA | C2 | 5 | C2 | 18 |
select
sales_date, dept, sum(sales_qty) from
v_sales;
sales_date | dept | sum(sales_qty) |
20191231 | dept #1 | 15 |
20200102 | dept #2 | 13 |
20191231 | dept #2 | 4 |
20200102 | # | 8 |
select
dept, sum(sales_qty) from
v_sales;
dept | sum(sales_qty) |
dept #1 | 15 |
dept #2 | 17 |
# | 8 |
select
sum(sales_qty) from
v_sales;
with
a as
(select
sales_date, dept, sum(sales_qty) as
sales_qty from
v_sales),
b as
(select
dept, sum(sales_qty) as
dept_sales_qty from
v_sales),
c as
(select
sum(sales_qty) as
tot_sales_qty from
v_sales),
select
from
no_view, * function
fn_nothing
cb
open_join('a,b'
,'dept'
,'a_b'
)
open_join('a_b,c'
)
add_number('dept_share'
,'return round(line.sales_qty/line.dept_sales_qty*100,3)'
)
add_number('total_share'
,'return line.sales_qty/line.tot_sales_qty*100'
)
keep_columns('sales_date,dept,sales_qty,dept_sales_qty,dept_share,total_share'
)
;
sales_date | dept | sales_qty | dept_sales_qty | dept_share | total_share |
20200102 | # | 8 | 8 | 100 | 20 |
20200102 | dept #2 | 13 | 17 | 76.471 | 32.500 |
20191231 | dept #2 | 4 | 17 | 23.529 | 10 |
20191231 | dept #1 | 15 | 15 | 100 | 37.500 |
data model modification #1
...
create
big table
sales( item_id t_item_id, customer_id t_customer_id, sales_date text
, sales_qty number
, line_id text
, packaging_id t_item_id, sales_qty2 number
);
refresh
dirty
view
;
select
sales.* from
v_sales;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
# | C1 | 20200102 | 8 | ID05 | # | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
insert
into
sales values
('artA'
,'C2'
,'20191231'
,5,'ID09'
,''
,55);
refresh
dirty
view
;
select
sales.* from
v_sales;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
# | C1 | 20200102 | 8 | ID05 | # | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 |
data model modification #2
Since v1.17.08 SB is a lot more flexible
create
col_type
t_a as
text
;
create
col_type
t_c as
text
;
create
merge table
dim_a (a t_a, aa text
);
create
big table
foo (a t_a, b number
, c text
);
create
view
v_foo as
select
* from
foo, dim_a where
foo.a=dim_a.a;
refresh
dirty
view
;
insert
into
foo values
('a1'
,1,'c1'
);
insert
into
foo values
('a2'
,2,'c2'
);
insert
into
dim_a values
('a1'
,'aa1'
)('a2'
,'aa2'
)('a3'
,'aa3'
);
refresh
dirty
view
;
select
* from
v_foo;
a | b | c | a | aa |
a1 | 1 | c1 | a1 | aa1 |
a2 | 2 | c2 | a2 | aa2 |
continue_on_error(121);
create
big table
foo (a t_a, b number
, c t_c);
reponse |
error 121 (continue): changing column type during table alter is not allowed |
create
big table
foo (c text
, a t_a, b number
, d text
);
desc
table
cb
grep('foo'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
foo | 3 | 2 | n | n | n |
__NEW_foo__ | 4 | 0 | n | n | n |
desc
view
cb
grep('foo'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_foo | 2 | 2 | y | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
continue_on_error(43);
refresh
dirty
view
;
reponse |
error 43 (continue): joined columns must have same column type |
desc
view
cb
grep('foo'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_foo | 2 | 2 | y | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
continue_on_error(1);
create
view
v_foo as
select
* from
foo, dim_a where
foo.a=dim_a.a;
refresh
dirty
view
;
desc
table
cb
grep('foo'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
foo | 4 | 2 | n | n | n |
desc
view
cb
grep('foo'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_foo | 2 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
select
* from
v_foo;
c | a | b | d | a | aa |
c1 | a1 | 1 | NO_DATA | a1 | aa1 |
c2 | a2 | 2 | NO_DATA | a2 | aa2 |
insert
into
foo values
('c3'
,'a3'
,3,'d3'
);
insert
into
foo values
('c4'
,'a4'
,4,'d4'
);
refresh
dirty
view
;
select
* from
v_foo;
c | a | b | d | a | aa |
c1 | a1 | 1 | NO_DATA | a1 | aa1 |
c2 | a2 | 2 | NO_DATA | a2 | aa2 |
c3 | a3 | 3 | d3 | a3 | aa3 |
c4 | # | 4 | d4 | # | # |
create
big table
foo (c text
, d text
, b number
);
create
view
v_foo as
select
* from
foo;
refresh
dirty
view
;
select
* from
v_foo;
c | d | b |
c1 | NO_DATA | 1 |
c2 | NO_DATA | 2 |
c3 | d3 | 3 |
c4 | d4 | 4 |
where clause basics
select
.. from
...
<where
|and
> where_clause1
and
where_clause2
and
where_clause3
..
Where clause syntax
column_name='value'
column_name in ('value1'
, 'value2'
, ...)
column_name nin ('value1'
, 'value2'
, ...)
column_name like 'value'
select
* from
v_sales and
dept='dept #1'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
select
* from
v_sales and
dept='dept #1'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
select
* from
v_sales and
dept in ('dept #1'
);
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
select
* from
v_sales and
dept nin ('dept #2'
);
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
# | C1 | 20200102 | 8 | ID05 | # | 0 | # | # | # | 0 | 0 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
select
* from
v_sales and
dept like 'DePt #1'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
where clause / Lua boolean function
any Lua boolean function
can be used in a where
clause using one and
only one column
desc
function
callback
where
(2,'boolean'
);
function_name | function_type | signature | comment |
regex | boolean | regex( str , pattern ) | returns true is if string.match( str , pattern ) (upperbase comparaison) more info http://www.lua.org/manual/5.2/manual.html#6.4.1 |
is_not | boolean | is_not( boolean_value ) | returns not boolean_value |
regex_or | boolean | function regex_or( str , pattern, pattern2, ...) | returns regex( str , pattern ) or regex( str , pattern2 ) ... |
btw | boolean | btw( x , y , z ) | returns true if y < x and x <=z then (number comparaison) |
btwe | boolean | btwe( x , y , z ) | returns true if y <= x and x <= z then (number comparaison) |
btwe_s | boolean | btwe_s( x , y , z ) | returns true if y <= x and x <= z then (string comparaison) |
nbtwe | boolean | nbtwe( x , y , z ) | not between or equal, returns false if y <= x and x <= z |
btwe_or | boolean | function btwe_or( x , y , z , y2 , z2 ... ) | returns btwe( x , y , z ) or btwe( x , y2 , z2 ) |
nbtwe_and | boolean | function nbtwe_and( x , y , z , y2 , z2 ... ) | returns nbtwe( x , y , z ) and nbtwe( x , y2 , z2 ) |
gt | boolean | gt( x , y ) | greater than, returns true is x > y (number comparaison) |
gte | boolean | gte_( x , y ) | greater than or equal, returns true is x >= y (number comparaison) |
lt | boolean | lt( x , y ) | lower than, returns true is x < y (number comparaison) |
lte | boolean | lte( x , y ) | lower than or equal, returns true is x <= y (number comparaison) |
gt_s | boolean | gt_s( x , y ) | greater than, returns true is x > y (string comparaison) |
gte_s | boolean | gte_s( x , y ) | greater than or equal, returns true is x >= y (string comparaison) |
lt_s | boolean | lt_s( x , y ) | lower than, returns true is x < y (string comparaison) |
lte_s | boolean | lte_s( x , y ) | lower than or equal, returns true is x <= y (string comparaison) |
e | boolean | e( x , y ) | equal, returns true is x==y |
ne | boolean | ne( x , y ) | not equal, returns true is x!=y |
isin | boolean | isin( x , { val1, val2, .. } ) | is in |
isnin | boolean | isnin( x , { val1, val2, .. } ) | is not in |
mod | boolean | mod( x , y , z ) | (x % y) == z |
(not)exists basics
select
.. from
view1
and
view2.col=
'something'
Exists
and
view2!.col=
'something'
Not exists
The exists join between view1 and view2 is implicit (the first col_type in common will be used for join)
Use case: when view2 is based on a non merge table, which why it can't be included in view1
select
sales.* from
v_sales
where
v_item_tags.tag='tag #1'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 |
select
sales.* from
v_sales
where
v_item_tags!.tag='tag #1'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
# | C1 | 20200102 | 8 | ID05 | # | 0 |
col_type_checks basics
A col_type_check is a "select col, count(col) from .. where ... group by col"
It is used to filter other part of the sql
The filter will be applied on the first column that has same col_type as the col_type_check
If you alias your group by column in the with clause, the filter with apply in priority to a column with this name (if it exists)
with
with_name1 ( col_type_check1 ),
with_name2 ( col_type_check2 ),
...
select
.. from
view1, with_name1, with_name2! ..
select
item_id, dept from
v_items cb
sort(1,'asc'
);
item_id | dept |
artA | dept #1 |
artB | dept #2 |
box1 | packaging |
with
a as
(select
item_id, count(item_id) from
v_items where
dept='dept #1'
group by
item_id)
select
sales.* from
v_sales, a
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 |
with
a as
(select
item_id, count(item_id) from
v_items where
dept='dept #1'
group by
item_id)
select
sales.* from
v_sales, a!
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
# | C1 | 20200102 | 8 | ID05 | # | 0 |
with
a as
(select
item_id as
columm_that_does_not_exists, count(item_id) from
v_items where
dept='dept #1'
group by
item_id)
select
sales.* from
v_sales, a
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 |
with
a as
(select
item_id as
packaging_id, count(item_id) from
v_items where
dept='dept #1'
group by
item_id)
select
sales.* from
v_sales, a
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
with
a as
(select
item_id as
packaging_id, count(item_id) from
v_items where
dept='packaging'
group by
item_id)
select
sales.* from
v_sales, a
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
col_type_checks advanced
Col_type_checks can be combined to do intersection etc...
It is used to filter other part of the sql
with
with_name1 ( col_type_check1 ),
with_name2 ( select
col, count(col) from
view2, with_name1 where
... group by
col ),
...
select
.. from
view3, with_name2
select
item_id, dept, customer_id, sales_qty from
v_sales;
item_id | dept | customer_id | sales_qty |
artA | dept #1 | C1 | 5 |
artB | dept #2 | C2 | 6 |
artB | dept #2 | C1 | 4 |
artB | dept #2 | C2 | 7 |
# | # | C1 | 8 |
artA | dept #1 | C1 | 5 |
artA | dept #1 | C2 | 5 |
artA | dept #1 | C2 | 5 |
select
* from
v_item_tags;
item_id | tag |
artA | tag #1 |
artA | tag #2 |
artD | A'__B |
with
group_of_customers as
(select
customer_id, count(customer_id) from
v_sales and
dept='dept #1'
group by
customer_id)
select
dept,count(*),list(customer_id) from
v_sales, group_of_customers and
dept nin ('dept #1'
,null
) group by
dept
;
dept | count(*) | list(customer_id) |
dept #2 | 3 | C1,C2 |
select
customer_id, count(customer_id) from
v_sales and
dept='dept #1'
group by
customer_id
;
customer_id | count(customer_id) |
C1 | 2 |
C2 | 2 |
with
group_of_customers as
(select
customer_id, count(customer_id) from
v_sales and
dept='dept #1'
group by
customer_id)
select
sales.* from
v_sales, group_of_customers and
dept nin ('dept #1'
,null
)
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
with
items_with_tag1 as
(select
item_id, count(item_id) from
v_item_tags and
tag='tag #1'
group by
item_id),
group_of_customers as
(select
customer_id, count(customer_id) from
v_sales, items_with_tag1 and
dept='dept #1'
group by
customer_id)
select
dept,count(*),list(customer_id) from
v_sales, group_of_customers and
dept nin ('dept #1'
,null
) group by
dept
;
dept | count(*) | list(customer_id) |
dept #2 | 3 | C1,C2 |
permissions
end_user
col_type
create
table
article_permission ( user_name end_user
, art_id t_item_id);
insert
into
article_permission values
('rd'
,'artA'
);
insert
into
article_permission values
('pt'
,'artA'
);
insert
into
article_permission values
('pt'
,'artB'
);
create
view
v_article_permission as
select
* from
article_permission;
create
table
customer_permission ( user_name end_user
, customer_id t_customer_id);
insert
into
customer_permission values
('rd'
,'C1'
);
insert
into
customer_permission values
('pt'
,'*'
);
create
view
v_customer_permission as
select
* from
customer_permission;
refresh
dirty
view
;
select
* from
v_sales where
end_user
='pt'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 | artB | the article B | dept #2 | 10 | 3.200 | C2 | customer #2 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 | artB | the article B | dept #2 | 10 | 3.200 | C1 | customer #1 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 | artB | the article B | dept #2 | 10 | 3.200 | C2 | customer #2 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 | artA | the article A | dept #1 | 10 | 1.500 | C2 | customer #2 |
select
* from
v_sales where
end_user
='rd'
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 | item_id | art_label | dept | avg_week_sales | sales_price | customer_id | customer_name |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 | artA | the article A | dept #1 | 10 | 1.500 | C1 | customer #1 |
dynamic sql (advanced)
set
log_verbose='n'
;
select
count(*) from
v_sales
cb
add_text('foo'
,'return "hello world"'
)
;
select
count(*) from
v_sales
cb
add_text('foo'
,'return fn()'
)
`#LOAD#
function
fn()
return "hello world"
end
`
;
select
count(*) from
v_sales
and
item_id='artA'
;
select
count(*) from
v_sales
`#DYN_SQL#fn_where_clause()`
`#LOAD#
function
fn_where_clause()
return "and
item_id='artA'
"
end
`
;
system 'rm -f stormbase_replay*.sql'
;
set
generate_replay='y'
;
select
count(*) from
v_sales
`#DYN_SQL#fn_where_clause()`
cb
add_text('foo'
,'return fn()'
)
`#LOAD#
function
fn_where_clause()
return "and
item_id='artA'
"
end
function
fn()
return "hello world"
end
`
;
system 'cat stormbase_replay1.sql'
;
reponse |
select count(*) from v_sales |
`#DYN_SQL#fn_where_clause()` |
cb add_text('foo','return fn()') |
`#LOAD# |
function fn_where_clause() |
return "and item_id='artA'" |
end |
function fn() |
return "hello world" |
end |
` |
; |
system 'cat stormbase_replay1.sql'; |
|
system 'cat stormbase_replay2.sql'
;
reponse |
select count(*) from v_sales |
and item_id='artA' |
cb add_text('foo','return fn()') |
; |
system 'cat stormbase_replay1.sql'; |
system 'cat stormbase_replay2.sql'; |
|
system 'rm -f stormbase_replay*.sql'
;
select
count(*) from
v_sales
`#DYN_SQL#fn_where_clause()`
cb
add_text('foo'
,'return fn()'
)
`#LOAD#
function
fn_where_clause()
return "and
item_id='artA'
"
end
`
`#LOAD_KEEP#
function
fn()
return "hello world"
end
`
;
system 'cat stormbase_replay2.sql'
;
reponse |
select count(*) from v_sales |
and item_id='artA' |
cb add_text('foo','return fn()') |
`#LOAD_KEEP# |
function fn() |
return "hello world" |
end |
` |
; |
system 'cat stormbase_replay2.sql'; |
|
system 'rm -f stormbase_replay*.sql'
;
select
count(*) from
v_sales
`#DYN_SQL#fn_where_clause()`
`#LOAD#
function
fn_where_clause()
return "
end
`
;
system 'cat stormbase_replay2.sql'
;
reponse |
select count(*) from v_sales |
-- and item_id='artA' |
; |
system 'cat stormbase_replay2.sql'; |
|
with
a as
(select
sales.* from
v_sales),
select
from
no_view,* function
fn_nothing
cb
union('a'
)
;
item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
artA | C1 | 20191231 | 5 | ID01 | box1 | 0 |
artB | C2 | 20200102 | 6 | ID02 | # | 0 |
artB | C1 | 20191231 | 4 | ID03 | # | 0 |
artB | C2 | 20200102 | 7 | ID04 | box1 | 0 |
# | C1 | 20200102 | 8 | ID05 | # | 0 |
artA | C1 | 20191231 | 5 | ID06 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID07 | box1 | 0 |
artA | C2 | 20191231 | 5 | ID09 | # | 55 |
with
a as
(select
max(sales_qty) as
max_sales_qty from
v_sales cb
add_text('foo'
,'return fn(max_sales_qty)'
)),
b as
(select
sales.* from
v_sales and
e(sales_qty,tonumber(`v_max_sales_qty`))),
select
from
no_view,* function
fn_nothing
cb
open_join('a,b'
)
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function
fn(n)
v_max_sales_qty=n
return "done"
end
`
;
max_sales_qty | foo | item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
8 | done | # | C1 | 20200102 | 8 | ID05 | # | 0 |
with
a as
(select
max(sales_qty) as
max_sales_qty from
v_sales cb
add_text('foo'
,'v_max_sales_qty=max_sales_qty return "foo"'
)),
b as
(`DYN_SQL#fn_sql()`),
select
from
no_view,* function
fn_nothing
cb
open_join('a,b'
)
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function
fn_sql()
return "select
sales.* from
v_sales and
e(sales_qty,"..v_max_sales_qty..")"
end
`
;
max_sales_qty | foo | item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
8 | foo | # | C1 | 20200102 | 8 | ID05 | # | 0 |
continue_on_error(151);
with
a as
(select
max(sales_qty) as
max_sales_qty from
v_sales cb
add_text('foo'
,'v_max_sales_qty=max_sales_qty return "foo"'
)),
b as
(`DYN_SQL#fn_sql()`),
select
from
no_view,* function
fn_nothing
cb
open_join('a,b'
)
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function
fn_sql()
return "select
sales.* from
v_sales \n
end
`
;
reponse |
error 151 (continue): select on secondary view failed |
stop_on_error;
system 'rm -f stormbase.debug'
;
with
a as
(select
max(sales_qty) as
max_sales_qty from
v_sales cb
add_text('foo'
,'v_max_sales_qty=max_sales_qty return "foo"'
)),
b as
(`DYN_SQL#fn_sql()`),
select
from
no_view,* function
fn_nothing
cb
open_join('a,b'
)
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function
fn_sql()
debug("lua variable v_max_sales_qty value is "..v_max_sales_qty)
debug("lua variable v_max_sales_qty type is "..type(v_max_sales_qty))
return "select
sales.* from
v_sales and
e(sales_qty,"..v_max_sales_qty..")"
end
`
;
max_sales_qty | foo | item_id | customer_id | sales_date | sales_qty | line_id | packaging_id | sales_qty2 |
8 | foo | # | C1 | 20200102 | 8 | ID05 | # | 0 |
system 'cat stormbase.debug'
;
reponse |
lua variable v_max_sales_qty value is 8 |
lua variable v_max_sales_qty type is number |
|
continue_on_error(222);
system 'rm -f stormbase.debug'
;
with
a as
(select
max(sales_qty) as
max_sales_qty from
v_sales cb
add_text('foo'
,'v_max_sales_qty=max_sales_qty return "foo"'
)),
b as
(`DYN_SQL#fn_sql()`),
select
from
no_view,* function
fn_nothing
cb
open_join('a,b'
)
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function
fn_sql()
local foo=nil..""
return "select
sales.* from
v_sales and
e(sales_qty,"..v_max_sales_qty..")"
end
`
;
reponse |
error 222 (continue): error in dynamic sql (see file stormbase.debug for more info) |
stop_on_error;
system 'cat stormbase.debug'
;
reponse |
<<< |
-- error: |
[string "..."]:5: attempt to concatenate a nil value |
-- |
-- lua call: |
return fn_sql() |
-- |
-- lua load: |
1. |
2. v_max_sales_qty="no_set" |
3. function fn_sql() |
4. -- this will fail |
5. local foo=nil.."" |
6. return "select sales.* from v_sales and e(sales_qty,"..v_max_sales_qty..")" |
7. end |
8. |
-- |
>>> |
|
set
cache
='y'
;
with
a as
(`DYN_SQL#fn_sql()`),
select
from
no_view,* function
fn_nothing
cb
union('a'
)
`#LOAD#
function
fn_sql()
return "select
unique(item_id) from
v_sales and
item_id='artA'
"
end
`
;
with
a as
(`DYN_SQL#fn_sql()`),
select
from
no_view,* function
fn_nothing
cb
union('a'
)
`#LOAD#
function
fn_sql()
return "select
unique(item_id) from
v_sales and
item_id='artB'
"
end
`
;
save
;
shutdown
;