<  *  | *** >

Others 

  • reduce memory and disk usage
  • need to force a value during an insert
  • function sb_export
  • set_text/set_sparse_text
  • show table statistics (work in progress)
  • allow errors in select
  • sb_parallel
  • fn_compute_info / fn_smart_delete (beta)
  • set_dirty
  • backup
  • dates
  • SB tags
  • index vs no_index read
  • countsequence
  • hide data from memory
  • countdistinct & high sparsity
  • number format/precision

-- ###########################
-- RUNNING stop_on_error.sql
stop_on_error;
reponse
success

-- ###########################
-- RUNNING clear.sh

/*
sure mode, no confirmation prompt
clearing directory INTERNAL_FILES_DIR (../STORAGE/INT) ...
done
clearing directory MAX_PERF_FILES_DIR (../STORAGE/MAX_PERF) ...
done
clearing directory TRANSAC_FILES_DIR (../STORAGE/TRANSAC) ...
done
*/

-- ###########################
-- RUNNING doc_data_init.sql
create col_type t_site_id as text;
reponse
success

create col_type t_dept_id as text;
reponse
success

create col_type t_item_id as text;
reponse
success

create col_type t_customer_id as text;
reponse
success

create col_type t_date as text;
reponse
success

create col_type t_customer_info as text;
reponse
success

create col_type end_user as text;
reponse
success

create merge table items( item_id t_item_id, art_label text, dept t_dept_id, avg_week_sales number, sales_price number);
reponse
success

create merge table customers( customer_id t_customer_id, customer_name text);
reponse
success

create table item_tags( item_id t_item_id, tag text);
reponse
success

create table fidelity_cards( customer_id t_customer_id, card_label t_customer_info, valid_from t_date, valid_until t_date);
reponse
success

create table item_customer_infos( customer_id t_customer_id, item_id t_item_id, info t_customer_info, valid_from t_date, valid_until t_date);
reponse
success

create big table sales( item_id t_item_id, customer_id t_customer_id, sales_date t_date, sales_qty number, line_id text, packaging_id t_item_id);
reponse
success

create big table inventory( item_id t_item_id, inv_qty number);
reponse
success

create view v_items as select * from items;
reponse
success

create view v_item_tags as select * from item_tags;
reponse
success

create view v_fidelity_cards as select * from fidelity_cards;
reponse
success

create view v_item_customer_infos as select * from item_customer_infos;
reponse
success

create view v_sales as select * from sales, items, customers where items.item_id=sales.item_id and customers.customer_id=sales.customer_id;
reponse
success

create view v_inventory as select * from inventory, items where items.item_id=inventory.item_id;
reponse
success

insert into items values('artA','the article A','dept #1',10,1.5);
reponse
success

insert into items values('artB','the article B','dept #2',10,3.2);
reponse
success

insert into items values('box1','a box','packaging',10,0);
reponse
success

insert into customers values('C1','customer #1')('C2','customer #2');
reponse
success

insert into item_tags values('artA','tag #1');
reponse
success

insert into item_tags values('artA','tag #2');
reponse
success

insert into fidelity_cards values('C1','SILVER','20191201','20191231');
reponse
success

insert into fidelity_cards values('C1','GOLD','20201201','20201231');
reponse
success

insert into item_customer_infos values('C1','artA','FREQUENT BUYER of artA in 2019','20190101','20191231');
reponse
success

insert into item_customer_infos values('C1','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');
reponse
success

insert into item_customer_infos values('C2','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');
reponse
success

insert into sales values('artA','C1','20191231',5,'ID01','box1');
reponse
success

insert into sales values('artB','C2','20200102',6,'ID02','');
reponse
success

insert into sales values('artB','C1','20191231',4,'ID03','');
reponse
success

insert into sales values('artB','C2','20200102',7,'ID04','box1');
reponse
success

insert into sales values('artC','C1','20200102',8,'ID05','');
reponse
success

insert into sales values('artA','C1','20191231',5,'ID06','box1');
reponse
success

insert into sales values('artA','C2','20191231',5,'ID07','box1');
reponse
success

insert into inventory values('artA',32);
reponse
success

insert into inventory values('artC',12);
reponse
success

refresh dirty view;
reponse
success

-- ###########################
-- RUNNING doc_others.sql
--

Others

reduce memory and disk usage

Use QUERY_TABLE_ALLOWED and SKIP_REFRESH_FOR_NOT_IN_MEMORY_BIG_TABLES
Use these parameters when you need to limit memory and disk usage, and when response time does not matter
desc parameter verbose cb where(1,'QUERY_TABLE_ALLOWED');
param_namecurrent_valuecommentis_defaultdefault_value
QUERY_TABLE_ALLOWEDny/n, y: select from table allowed (non in memory volatile views will be created on top of each table)yn

continue_on_error(49);
reponse
success

select * from sales;
reponse
error 49 (continue): <text> is not a view

stop_on_error;
reponse
success

--the volatile views used to query table are created at startup, hence SB must be saved/bounced to take into account QUERY_TABLE_ALLOWED
system '(echo "QUERY_TABLE_ALLOWED:y">>stormbase.conf)';
reponse

save;
reponse
success

bounce;
desc parameter verbose cb where(1,'QUERY_TABLE_ALLOWED');
param_namecurrent_valuecommentis_defaultdefault_value
QUERY_TABLE_ALLOWEDyy/n, y: select from table allowed (non in memory volatile views will be created on top of each table)nn

--volatile views are created
desc view cb like(1,'volatile');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
volatile#items13nyy1000000
volatile#customers12nyy1000000
volatile#item_tags12nyy1000000
volatile#fidelity_cards12nyy1000000
volatile#item_customer_infos13nyy1000000
volatile#sales13nyy1000000
volatile#inventory12nyy1000000
volatile#sales_#partition#_0000113nyy1000000
volatile#sales_#partition#_0000211nyy1000000

select * from sales;
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC1201912315ID01box1
artBC2202001026ID02#
artBC1201912314ID03#
artBC2202001027ID04box1
artCC1202001028ID05#
artAC1201912315ID06box1
artAC2201912315ID07box1

select sales.* from v_sales;
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC1201912315ID01box1
artBC2202001026ID02#
artBC1201912314ID03#
artBC2202001027ID04box1
#C1202001028ID05#
artAC1201912315ID06box1
artAC2201912315ID07box1

set skip_refresh_for_not_in_memory_big_tables='y';
reponse
success

set in_memory_big_tables='-1';
reponse
success

refresh_force dirty view;
reponse
success

select sales.* from v_sales;
item_idcustomer_idsales_datesales_qtyline_idpackaging_id

select * from sales;
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC1201912315ID01box1
artBC2202001026ID02#
artBC1201912314ID03#
artBC2202001027ID04box1
artCC1202001028ID05#
artAC1201912315ID06box1
artAC2201912315ID07box1

save;
reponse
success

--volatile views are not saved, they are created at startup (or during table creation)
system 'ls -ltr ../STORAGE/INT/*|grep volatile|wc -l';
reponse
0

system 'cat ../STORAGE/INT/_DESC|grep volatile|wc -l';
reponse
0

system '(echo "QUERY_TABLE_ALLOWED:n">>stormbase.conf)';
reponse

save;
reponse
success

bounce;

need to force a value during an insert

use INSERT_FORCE_VALUE parameter
set 'INSERT_FORCE_VALUE.t_item_id'='-1';
reponse
success

desc parameter verbose cb like(1,'INSERT_FORCE_VALUE.T_ITEM_ID');
param_namecurrent_valuecommentis_defaultdefault_value
INSERT_FORCE_VALUE.T_ITEM_ID-1string: forced value during insert of this col_type in a big table, unset means "not active"y-1

insert into sales values('artZ','C2','20191231',5,'ID_TEST','box1');
reponse
success

refresh dirty view;
reponse
success

select sales#item_id from v_sales and line_id='ID_TEST';
sales#item_id
-1

set 'INSERT_FORCE_VALUE.t_item_id'='unset';
reponse
success

desc parameter verbose cb like(1,'INSERT_FORCE_VALUE.T_ITEM_ID');
param_namecurrent_valuecommentis_defaultdefault_value
INSERT_FORCE_VALUE.T_ITEM_IDunsetstring: forced value during insert of this col_type in a big table, unset means "not active"n-1

insert into sales values('artZ','C2','20191231',5,'ID_TEST','box1');
reponse
success

refresh dirty view;
reponse
success

select sales#item_id from v_sales and line_id='ID_TEST';
sales#item_id
-1
artZ

function sb_export

loop table_name(columns) function sb_export('path/to/directory','file_tag',thread_count) ...
loop sales(*) function sb_export('./foo','ABC',4);
LOOP_RESULT
DONE

--ACCEPT_DIFF_START
system 'for f in `ls ./foo/*.csv`; do echo "### $f ###"; cat $f; done';
reponse
### ./foo/ABC_001.csv ###
item_id,customer_id,sales_date,sales_qty,line_id,packaging_id
artB,C2,20200102,6.000,ID02,
artB,C2,20200102,7.000,ID04,box1
artC,C1,20200102,8.000,ID05,
artA,C2,20191231,5.000,ID07,box1
-1,C2,20191231,5.000,ID_TEST,-1
### ./foo/ABC_002.csv ###
item_id,customer_id,sales_date,sales_qty,line_id,packaging_id
artB,C1,20191231,4.000,ID03,
artA,C1,20191231,5.000,ID06,box1
### ./foo/ABC_003.csv ###
item_id,customer_id,sales_date,sales_qty,line_id,packaging_id
artA,C1,20191231,5.000,ID01,box1
artZ,C2,20191231,5.000,ID_TEST,box1
### ./foo/ABC_004.csv ###
item_id,customer_id,sales_date,sales_qty,line_id,packaging_id
### ./foo/aa_001.csv ###
col
a1
a2
a3
### ./foo/bb_001.csv ###
col
a1
a2
a3

--ACCEPT_DIFF_END

set_text/set_sparse_text

continue_on_error(20);
reponse
success

drop view v_foo;
reponse
error 20 (continue): "<object_name>" is not an object or "<type_name>" is not correct

drop table foo;
reponse
error 20 (continue): "<object_name>" is not an object or "<type_name>" is not correct

refresh dirty view;
reponse
success

stop_on_error;
reponse
success

create table foo(col number,str text);
reponse
success

create view v_foo as select * from foo;
reponse
success

refresh dirty view;
reponse
success

insert into foo values(111.11,'A')(222.22,'B');
reponse
success

refresh dirty view;
reponse
success

select * from v_foo;
colstr
111.110A
222.220B

desc table foo;
table_namecolumn_namecolumn_typecol_type_name
foocolnumbersys#type#foo#col
foostrtextsys#type#foo#str

set_text sys#type#foo#col;
reponse
success

desc table foo;
table_namecolumn_namecolumn_typecol_type_name
foocoltextsys#type#foo#col
foostrtextsys#type#foo#str

select * from v_foo;
colstr
111.110A
222.220B

insert into foo values('True','C');
reponse
success

refresh dirty view;
reponse
success

select * from v_foo;
colstr
111.110A
222.220B
TrueC

-- create table foo(a text, b number);
-- create view v_foo as select * from foo;
--ival behaves "normaly"
insert into foo values('aa3','bb3');
reponse
success

insert into foo values('aa3','bb3');
reponse
success

insert into foo values('aa3','bb3');
reponse
success

refresh dirty view;
reponse
success

select col,ival(col),count(*) from v_foo group by col;
colival(col)count(*)
111.110101
222.220111
True121
aa3133

set_sparse_text sys#type#foo#col;
reponse
success

select col,ival(col),count(*) from v_foo group by col;
colival(col)count(*)
111.110101
222.220111
True121
aa3133

save;
reponse
success

--ACCEPT_DIFF_START
system 'ls -l ../STORAGE/INT/*.cell_array';
reponse
ls: ../STORAGE/INT/*.cell_array: No such file or directory

--bounce will finalize the migration
bounce;
system 'ls -l ../STORAGE/INT/*.cell_array';
reponse
-rw-r--r-- 1 philippe staff 117 16 jan 10:00 ../STORAGE/INT/0113_sys#type#foo#col.ival_to_val.cell_array

--ACCEPT_DIFF_END
system 'cat ../STORAGE/INT/*.cell_array';
reponse
0;0.000
1;0.000
2;0.000
3;0.000
4;0.000
5;0.000
6;0.000
7;0.000
8;0.000
9;0.000
10;111.110
11;222.220
12;True
13;aa3

--
select col,ival(col),count(*) from v_foo group by col;
colival(col)count(*)
111.110101
222.220111
True121
aa3133

--each insert gets a new ival
insert into foo values('aa3','bb3')('aa3','bb3');
reponse
success

insert into foo values('aa4','bb4')('aa4','bb4');
reponse
success

refresh dirty view;
reponse
success

select col,ival(col),count(*) from v_foo group by col;
colival(col)count(*)
111.110101
222.220111
True121
aa3133
aa3141
aa3151
aa4161
aa4171

save;
reponse
success

--ACCEPT_DIFF_START
system 'ls -l ../STORAGE/INT/*.cell_array';
reponse
-rw-r--r-- 1 philippe staff 145 16 jan 10:00 ../STORAGE/INT/0113_sys#type#foo#col.ival_to_val.cell_array

system 'cat ../STORAGE/INT/*.cell_array';
reponse
0;0.000
1;0.000
2;0.000
3;0.000
4;0.000
5;0.000
6;0.000
7;0.000
8;0.000
9;0.000
10;111.110
11;222.220
12;True
13;aa3
14;aa3
15;aa3
16;aa4
17;aa4

--ACCEPT_DIFF_END

show table statistics (work in progress)

loop sales(sales_date,sales_qty) function fn_compute_info('my_info');
column_nameabs_avgabs_minabs_max
sales_datenannannan
sales_qty5.564.008.00

select sum(sales_qty),count(sales_qty),avg(sales_qty) from v_sales;
sum(sales_qty)count(sales_qty)avg(sales_qty)
1535

allow errors in select

Sometimes it can't be easier to ignore errors, it facilitates integration with reporting tool
Parameters: ALLOW_WHERE_ERROR, ALLOW_GROUP_BY_ERROR, ALLOW_EXP_ERROR
continue_on_error(53,120,150);
reponse
success

select sales.* from v_sales and bad_column='1' and item_id='artA';
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC2201912315ID07box1

select * from v_items and v_fidelity_cards.card_label='bad_value';
item_idart_labeldeptavg_week_salessales_price
artAthe article Adept #1101.500
artBthe article Bdept #2103.200
box1a boxpackaging100

with a as (select card_label,count(card_label) from v_fidelity_cards group by card_label) select * from v_items,a;
item_idart_labeldeptavg_week_salessales_price
artAthe article Adept #1101.500
artBthe article Bdept #2103.200
box1a boxpackaging100

stop_on_error;
reponse
success

set allow_where_error='y';
reponse
success

select sales.* from v_sales and bad_column='1' and item_id='artA';
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC2201912315ID07box1

select sales.* from v_sales and bad_column in ('1','2','3') and item_id='artA';
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC2201912315ID07box1

select sales.* from v_sales and gt(bad_column,1) and item_id='artA';
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC2201912315ID07box1

select sales.* from v_sales and bad_column in ('1','2','3') and item_id='artA' and bad_column in ('1','2','3') and item_id='artA' and gt(bad_column,1) and item_id='artA';
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC2201912315ID07box1

select * from v_items and bad_column='bad_value';
item_idart_labeldeptavg_week_salessales_price
artAthe article Adept #1101.500
artBthe article Bdept #2103.200
box1a boxpackaging100

select * from v_items and v_fidelity_cards.card_label='bad_value';
item_idart_labeldeptavg_week_salessales_price
artAthe article Adept #1101.500
artBthe article Bdept #2103.200
box1a boxpackaging100

with a as (select card_label,count(card_label) from v_fidelity_cards group by card_label) select * from v_items,a;
item_idart_labeldeptavg_week_salessales_price
artAthe article Adept #1101.500
artBthe article Bdept #2103.200
box1a boxpackaging100

continue_on_error(53);
reponse
success

select bad_column,count(*) from v_sales group by bad_column;
bad_columncount(*)
*3

stop_on_error;
reponse
success

set allow_group_by_error='y';
reponse
success

select count(*) from v_sales;
count(*)
3

select bad_column,count(*) from v_sales group by bad_column;
bad_columncount(*)
*3

select item_id,bad_column,item_id,count(*) from v_sales group by item_id,bad_column,item_id;
item_idbad_columnitem_idcount(*)
artA*artA1
#*#2

continue_on_error(53);
reponse
success

select sum(sales_qty),sum(bad_column) from v_sales;
reponse
error 53 (continue): <column_name> not part of <view_name> definition

stop_on_error;
reponse
success

set allow_exp_error='y';
reponse
success

select sum(sales_qty),sum(bad_column) from v_sales;
sum(sales_qty)sum(bad_column)
150

sb_parallel

When you use a custom C in multi thread context, you might need each thread to treat a given subset of the data
This is possible in SB, and this is called "sb_parallel mode"
If a where clause use sb_parallel lua function then the "sb_parallel mode" is triggered
For instance here under we want each thread to treat data of a single item_id, hence the sb_parallel(item_id) where clause
In some cases "sb_parallel mode" is replaced by a "mono thread mode"
This happens when pareto in used or when the index of the sb_parallel column cannot be used
--only one sb_parallel clause is authorized
continue_on_error(206);
reponse
success

select sales.* from v_sales
and sb_parallel(item_id)
and sb_parallel(sales_date);
reponse
error 206 (continue): only one sb_parallel clause is allowed

stop_on_error;
reponse
success

set pareto_limit='1';
reponse
success

refresh_force dirty view;
reponse
success

select sales.* from v_sales
and sb_parallel(item_id);
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC1201912315ID01box1
artBC2202001026ID02#
artBC1201912314ID03#
artAC1201912315ID06box1
artBC2202001027ID04box1
artAC2201912315ID07box1

--without the sb_parallel there is no logic between item_id and thread_pos
select p(item_id) from v_sales function fn_custom(
'(void* c, U_INT thread_pos, U_INT iline, int key1){printf("## item_id ival: %d, thread_pos: %u\n",key1,thread_pos);return 0.;}',
'fn_key',
'fn_hello_world_before','fn_hello_world_after'
)
;
item_id ival: 11, thread_pos: 0
item_id ival: 12, thread_pos: 0
item_id ival: 12, thread_pos: 0
item_id ival: 12, thread_pos: 0
item_id ival: 11, thread_pos: 0
item_id ival: 11, thread_pos: 0
txt
hello world

--with the sb_parallel one item_id receives one thread_pos
select p(item_id) from v_sales function fn_custom(
'(void* c, U_INT thread_pos, U_INT iline, int key1){printf("## item_id ival: %d, thread_pos: %u\n",key1,thread_pos);return 0.;}',
'fn_key',
'fn_hello_world_before','fn_hello_world_after'
)
and sb_parallel(item_id)
;
item_id ival: 11, thread_pos: 0
item_id ival: 12, thread_pos: 0
item_id ival: 12, thread_pos: 0
item_id ival: 11, thread_pos: 0
item_id ival: 12, thread_pos: 0
item_id ival: 11, thread_pos: 0
txt
hello world

fn_compute_info / fn_smart_delete (beta)

loop sales(sales_date,sales_qty) function fn_compute_info('my_info');
column_nameabs_avgabs_minabs_max
sales_datenannannan
sales_qty5.564.008.00

select sales_qty from v_sales callback sort(1,'desc');
sales_qty
8
7
6
5
5
5
5
5
4

with
a as (select avg(sales_qty) as v from v_sales function fn_store),
del_count as (select count(sales_qty) as v from v_sales function fn_store and gte(math.abs(sales_qty),`a.v*1.1`)),
kept_count as (select count(sales_qty) as v from v_sales function fn_store and lt(math.abs(sales_qty),`a.v*1.1`)),
select del_count.v, kept_count.v, a.v*1.1 from no_view, * function fn_merge
;
del_count.vkept_count.va.v*1.1
276.111

loop sales(sales_qty) function fn_smart_delete('my_info','1.1') update(item_id);
LOOP_RESULT
DONE

refresh dirty view;
reponse
success

select sales_qty from v_sales callback sort(1,'desc');
sales_qty
6
5
5
5
5
5
4

--

set_dirty

refresh a particular view
set_dirty <the_view>
desc view callback like(1,'v_sales');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33nnn100131300
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000233nnn100131300

set_dirty v_sales;
reponse
success

desc view callback like(1,'v_sales');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33ynn100131300
v_sales_#partition#_0000131ynn100111100
v_sales_#partition#_0000233ynn100131300

refresh dirty view;
reponse
success

desc view callback like(1,'v_sales');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33nnn100131300
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000233nnn100131300

--but in partition context you must specify the partitions
save;
reponse
success

set PARTITION_LINE_COUNT=1;
reponse
success

insert into sales values('artA','C1','20191231',5,'_','box1');
reponse
success

insert into sales values('artA','C1','20191231',5,'_','box1');
reponse
success

insert into sales values('artA','C1','20191231',5,'_','box1');
reponse
success

refresh dirty view;
reponse
success

desc view callback like(1,'v_sales') sort(1,'asc');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33nnn100131300
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000233nnn100131300
v_sales_#partition#_0000331nnn100111100
v_sales_#partition#_0000431nnn100111100
v_sales_#partition#_0000531nnn100111100

set_dirty v_sales;
reponse
success

set_dirty v_sales_#partition#_00002;
reponse
success

desc view callback like(1,'v_sales') sort(1,'asc');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33ynn100131300
v_sales_#partition#_0000131ynn100111100
v_sales_#partition#_0000233ynn100131300
v_sales_#partition#_0000331ynn100111100
v_sales_#partition#_0000431ynn100111100
v_sales_#partition#_0000531ynn100111100

refresh dirty view;
reponse
success

desc view callback like(1,'v_sales') sort(1,'asc');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33nnn100131300
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000233nnn100131300
v_sales_#partition#_0000331nnn100111100
v_sales_#partition#_0000431nnn100111100
v_sales_#partition#_0000531nnn100111100

bounce;
--

backup

backup 'the_directory'
--ACCEPT_DIFF_START
system 'ls -l ../STORAGE/INT| wc -l';
reponse
357

save;
reponse
success

save;
reponse
success

system 'ls -l ../STORAGE/INT| wc -l';
reponse
359

system 'rm -Rf ./toto';
reponse

system 'mkdir ./toto';
reponse

backup './toto';
reponse
BACKUP OK

system 'ls -l ./toto| wc -l';
reponse
322

system 'ls -l ../STORAGE/INT| wc -l';
reponse
359

--ACCEPT_DIFF_END
bounce;
--

dates

--ACCEPT_DIFF_START
select now(), sysdate(), to_char(now(),'yyyymmdd') from no_view function fn_merge;
now()sysdate()to_char(now(),'yyyymmdd')
1737017984173701798420250116

--ACCEPT_DIFF_END
select
to_date('20200101','yyyymmdd') as d,
to_char(line.d,'%a'),
to_char(line.d,'%j'),
from no_view function fn_merge;
dto_char(line.d,'%a')to_char(line.d,'%j')
1577833216Wed001

create table my_data (date_yyyymmdd text);
reponse
success

create view v_my_data as select * from my_data;
reponse
success

insert into my_data values('20191230')('20191231')('20200101')('20200102')('20200103')('20200104')('20200105');
reponse
success

insert into my_data values('20200107')('20200108')('20200109')('20200110')('20200111')('20200112');
reponse
success

refresh dirty view;
reponse
success

select * from v_my_data;
date_yyyymmdd
20191230
20191231
20200101
20200102
20200103
20200104
20200105
20200107
20200108
20200109
20200110
20200111
20200112

set log_verbose='y';
reponse
success

--working with week numbers (TODO)
--#SB no_cache log_verbose
with
a as(select date_yyyymmdd,unique(date_yyyymmdd) as v from v_my_data function fn_store group by date_yyyymmdd)
select col_type.val as yyyymmdd,
to_date(a.v,'yyyymmdd') as d,
-1 as prio_lag,
196 as keep_count,
--20000103 is a Monday
to_date('20000103','yyyymmdd')+line.prio_lag*24*3600 as prio_day,
to_char(line.prio_day,'%a') as prio_dow, to_char(line.d,'%a') as d_dow,
math.floor((now()-line.d)/24/3600) as delta_today,
(line.d-line.prio_day)/24/3600 as delta,
math.floor(line.delta/7) as delta_week,
line.delta%7 as delta_dow,
from no_view, a function fn_merge
callback
sub_select('group(delta_week) min(delta_dow)')
add_text('keep_yn','if line.delta_today>line.keep_count then return "n" end if math.floor(line.min_delta_dow-line.delta_dow)==0 then return "y" else return "n" end')
select_from('group(yyyymmdd) group(keep_yn) group(d_dow)')
sort(1,'asc')
;
group_yyyymmddgroup_keep_yngroup_d_dow
20191230nMon
20191231nTue
20200101nWed
20200102nThu
20200103nFri
20200104nSat
20200105nSun
20200107nTue
20200108nWed
20200109nThu
20200110nFri
20200111nSat
20200112nSun

--build a calendar table (TODO)
select
`fn_calendar('20200101','20200230','yyyymmdd',{"%Y%m%d","%A","%B","%Y"})` as `"date"..sep().."week_day"..sep().."month_name"..sep().."full_year"`,
from no_view function fn_merge
export to tmp_file('calendar.csv')
;
--

SB tags

select
SB use_index|no_index|log_verbose|no_cache|explain
..
from ...
Several SB tags can be used
--no_cache: obvious
--the explain tag tells SB to return the logs instead of the values
--the idea is that the logs "explain" what SB does
--note that without no_cache SB will "do nothing" (read result from cache)
select
--#SB no_cache explain
* from v_sales
;
logs

--handy to understand error
continue_on_error(53);
reponse
success

--#SB explain
select bad_column from v_sales;
logs
reponse
y
success

stop_on_error;
reponse
success

--log_verbose: obvious
--explain is always used with log_verbose
select
--#SB no_cache explain log_verbose
* from v_sales
callback limit(10)
;
logs

--no_index: obvious
select
--#SB explain no_cache log_verbose no_index
* from v_sales and dept like 'dept #1'
callback like(1,'where_index|function')
;
logs

--use_index: obvious
select
--#SB explain no_cache log_verbose use_index
* from v_sales and dept like 'dept #1'
callback like(1,'where_index|function')
;
logs

index vs no_index read

index read versus no_index (aka sequential read or full scan) read
set PARETO_LIMIT='1';
reponse
success

set CPU_COUNT='1';
reponse
success

refresh_force dirty view;
reponse
success

--a no_index (full scan) reads all lines sequentialy and filter
select --#SB log_verbose no_index no_cache
customer_id, line_id, sales#item_id from v_sales and item_id in ('artA','artB')
;
customer_idline_idsales#item_id
C1ID01artA
C2ID02artB
C1ID03artB
C1ID06artA
C2ID07artA

--an index read does that (lines corresponding to artA, then artB)
select --#SB log_verbose use_index no_cache
customer_id, line_id, sales#item_id from v_sales and item_id in ('artA','artB')
;
customer_idline_idsales#item_id
C1ID01artA
C2ID02artB
C1ID03artB
C1ID06artA
C2ID07artA

--SB's logic to do index or sequential read is this:
--X=total #lines
--Y=#lines targeted by index
--SB will use the index if Y < X / USE_INDEX_LIMIT
set USE_INDEX_LIMIT=1;
reponse
success

select --#SB log_verbose no_cache explain
customer_id, line_id, sales#item_id from v_sales and item_id in ('artA','artB')
callback like(1,'where_index')
;
logs

set USE_INDEX_LIMIT=10;
reponse
success

select --#SB log_verbose no_cache explain
customer_id, line_id, sales#item_id from v_sales and item_id in ('artA','artB')
callback like(1,'where_index')
;
logs

--if SB needs to sort the lines before (countsequence context, explained after) then USE_INDEX_WITH_SORT_LIMIT is used
--SB will use the index if Y < X / USE_INDEX_WITH_SORT_LIMIT
set USE_INDEX_LIMIT=1;
reponse
success

set USE_INDEX_WITH_SORT_LIMIT=1;
reponse
success

select --#SB log_verbose no_cache explain
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'where_index')
;
logs

set USE_INDEX_WITH_SORT_LIMIT=10;
reponse
success

select --#SB log_verbose no_cache explain
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'where_index')
;
logs

countsequence

countsequence(the_column) counts the number of value changes during reading
countsequence can be an alternative to countdistinct
!!! countsequence will return incorrect result if PARETO_LIMIT!=1 !!!
select --#SB no_cache no_index
customer_id, line_id, sales#item_id from v_sales and item_id in ('artA','artB')
;
customer_idline_idsales#item_id
C1ID01artA
C2ID02artB
C1ID03artB
C1ID06artA
C2ID07artA

select --#SB no_cache no_index
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
;
countsequence(customer_id)
4

--same result (of course) with index
select --#SB no_cache use_index
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
;
countsequence(customer_id)
4

--but when an index read is done, SB must sort the lines to get correct result
select
--#SB log_verbose use_index no_cache explain
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'sequence')
;
logs

hide data from memory

Parameters: MAX_PERF_FILTER & MAX_PERF_FILTER_FN
save;
reponse
success

select customer_id,count(*) from v_sales group by customer_id;
customer_idcount(*)
C13
C24

desc view callback like(1,'sales');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33nnn100131300
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000233nnn100131300

set MAX_PERF_FILTER_COL_TYPE='t_customer_id';
reponse
success

set MAX_PERF_FILTER_FN='e(x,''C1'')';
reponse
success

refresh_force dirty view;
reponse
success

desc view callback like(1,'sales');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales33nnn100121200
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000233nnn100101000

select customer_id,count(*) from v_sales group by customer_id;
customer_idcount(*)
C13

select sales.* from v_sales;
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC1201912315ID01box1
artBC1201912314ID03#
artAC1201912315ID06box1

set MAX_PERF_FILTER_COL_TYPE='-1';
reponse
success

refresh_force dirty view;
reponse
success

select customer_id,count(*) from v_sales group by customer_id;
customer_idcount(*)
C13
C24

countdistinct & high sparsity

Parameters: SEQUENCE_COLUMNS & SEQUENCE_COLUMN_COMPANIONS & USE_INDEX_WITH_SORT_LIMIT & REPLACE_COUNTDISTINCT_BY_COUNTSEQUENCE
SEQUENCE_COLUMNS will tell SB to sort the lines according to a given column
set SEQUENCE_COLUMNS='customer_id';
reponse
success

--the view must be refresh, otherwise sql will fail
refresh_force dirty view;
reponse
success

--we can see that lines have a new order, that can be seen with no_index tag
select --#SB no_cache no_index
customer_id, line_id, sales#item_id from v_sales and item_id in ('artA','artB')
;
customer_idline_idsales#item_id
C1ID01artA
C1ID03artB
C2ID02artB
C1ID06artA
C2ID07artA

--and countsequence(customer_id) has changed
select
--#SB no_cache
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
;
countsequence(customer_id)
4

--index read will see lines in this order
select
--#SB no_cache use_index
item_id, customer_id from v_sales and item_id in ('artA','artB')
;
item_idcustomer_id
artAC1
artBC1
artBC2
artAC1
artAC2

--which is why index read will need a resort operation to get correct countsequence result
select
--#SB no_cache explain use_index log_verbose
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'sort')
;
logs

--but this sort operation can be long
--some dimensions are functionaly liked to a column
--common case is transaction_id and dates, you expect a given transaction (with several lines) to have a unique date
--in SB's jargon date dimension is a companion of transaction_id
--in such cases (you know something about your data), you can tell SB with param SEQUENCE_COLUMN_COMPANIONS
--here we will tell SB that items is a companion of customer_id
--this is incorrect, I know, but you will understand what happens without sorting
set SEQUENCE_COLUMN_COMPANIONS='customer_id=items';
reponse
success

--no more sorting
select
--#SB no_cache explain use_index log_verbose
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'sort')
;
logs

--but result is incorrect
select
--#SB no_cache use_index
countsequence(customer_id) from v_sales and item_id in ('artA','artB')
;
countsequence(customer_id)
4

-- countdistinct is greedy against high sparsity columns
-- countsequence is not
-- in some context both are equivalent
-- so SB will replace countdistinct(the_col) by countsequence(the_col) if:
-- SEQUENCE_COLUMNS is set to the_col
-- and REPLACE_COUNTDISTINCT_BY_COUNTSEQUENCE=y
insert into sales values('artA','CX','20191231',5,'ID11','box1');
reponse
success

insert into sales values('artA','CY','20191231',5,'ID11','box1');
reponse
success

refresh_online dirty view;
reponse
success

select
--#SB log_verbose no_cache explain
countdistinct(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'sequence');
logs

set REPLACE_COUNTDISTINCT_BY_COUNTSEQUENCE='y';
reponse
success

--no_index context
select
--#SB log_verbose no_cache no_index explain
countdistinct(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'sequence');
logs

select
--#SB log_verbose no_cache no_index
countdistinct(customer_id) from v_sales and item_id in ('artA','artB')
;
countdistinct(customer_id)
2

--use_index context
select
--#SB log_verbose no_cache use_index explain
countdistinct(customer_id) from v_sales and item_id in ('artA','artB')
callback like(1,'sequence');
logs

select
--#SB log_verbose no_cache use_index
countdistinct(customer_id) from v_sales and item_id in ('artA','artB')
;
countdistinct(customer_id)
2

--remember that USE_INDEX_WITH_SORT_LIMIT can be used (see before)
set SEQUENCE_COLUMNS='-1';
reponse
success

refresh_force dirty view;
reponse
success

number format/precision

continue_on_error(20);
reponse
success

drop view v_foo;
reponse
success

drop table foo;
reponse
success

refresh dirty view;
reponse
success

stop_on_error;
reponse
success

create table foo(col number,str text);
reponse
success

create view v_foo as select * from foo;
reponse
success

set float_precision=1;
reponse
success

system '(printf "col,str\n1234,1234\n0.01234,0.01234\n12.43,12.43">./foo.csv)';
reponse

insert into foo select * from file('./foo.csv');
reponse
success

set float_precision=4;
reponse
success

system '(printf "col,str\n1234,1234\n0.01234,0.01234\n12.43,12.43">./foo.csv)';
reponse

insert into foo select * from file('./foo.csv');
reponse
success

refresh dirty view;
reponse
success

--number inserted with float_precision 1 are truncated in SB
select * from v_foo;
colstr
12341234
00.01234
12.400012.43
12341234
0.01230.01234
12.430012.43

--float_precision param also affects the display
set float_precision=0;
reponse
success

select * from v_foo;
colstr
12341234
00.01234
1212.43
12341234
00.01234
1212.43

--complex notation is allowed, float_precision affects only display
system '(printf "col,str\n4.04e-02,4.04e-02">./foo.csv)';
reponse

insert into foo select * from file('./foo.csv');
reponse
success

refresh dirty view;
reponse
success

select * from v_foo;
colstr
12341234
00.01234
1212.43
12341234
00.01234
1212.43
04.04e-02

set float_precision=4;
reponse
success

select * from v_foo;
colstr
12341234
00.01234
12.400012.43
12341234
0.01230.01234
12.430012.43
04.04e-02

set float_precision=2;
reponse
success

-- ###########################
-- RUNNING shutdown.sql
shutdown;