-- ###########################
-- RUNNING stop_on_error.sql
stop_on_error;


reponse



success

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_full.sql
--
--

Create / insert / refresh

--

create col_type

create col_type <end_user|col_type_name> as <number|text>

--In stormbase there are only 2 primary data types: text and number.
--On top of this primary types you define your col_type (the functional data type).
--Why ? in SB you can join 2 columns if and only if they have the same col_type.
create col_type t_site_id as text;


reponse



success

reponse
success

create col_type t_dept_id as text;


reponse



success

reponse
success

create col_type t_item_id as text;


reponse



success

reponse
success

create col_type t_customer_id as text;


reponse



success

reponse
success

create col_type t_date as text;


reponse



success

reponse
success

create col_type t_customer_info as text;


reponse



success

reponse
success

--the end_user col_type is used for permissions (explained later)
create col_type end_user as text;


reponse



success

reponse
success

--Assuming you an integer value in a column, will you define it as text or number?

  --The answer is simple. Are you going to sum this column? If yes then it is a number column, otherwise it is a text column.

--So if your integer column contains for example a status code, use text. And if it contains quantities, use number.
--

create table

create table <|merge|big> table_name ( column_name1 <number|text|col_type_name>, ...)

-- In Stormbase, a table has a type: raw (no specific type), merge or big.
-- In traditional BI, big tables would be your fact tables (at the center of your star schema).
-- First column of a merge table becomes is primary key of the table.
-- Merge tables have an "insert or update" logic, based on the primary key.
-- In traditional BI, merge tables would be the dimension tables of your star schema.
--items and customers are merge tables, last insert wins and updates using the primary key
--they will be used as dimensions in views

  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

reponse
success

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


reponse



success

reponse
success

--these tables are raw tables (first column is a primary key)
create table item_tags( item_id t_item_id, tag text);


reponse



success

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

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

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

reponse
success

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


reponse



success

reponse
success

--

create view

  create view view_name as select * from table_name1, table_name2, ... where table_name1.column_nameA = table_name2.column_nameB ...

--In SB you query view not tables, so we create "one table views"
create view v_items as select * from items;


reponse



success

reponse
success

create view v_item_tags as select * from item_tags;


reponse



success

reponse
success

create view v_fidelity_cards as select * from fidelity_cards;


reponse



success

reponse
success

create view v_item_customer_infos as select * from item_customer_infos;


reponse



success

reponse
success

--most important, this view will link fact with dimensions

  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

reponse
success

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


reponse



success

reponse
success

--

insert values

insert into table_name values('col1_value','col2_value',...)

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

--

refresh dirty view

Refresh the views after insert

Dirty means that data has been inserted in the first table of the view since last refresh

refresh dirty view

refresh dirty view;


reponse



success

reponse
success

--

view data model & parameters

Desc (describe) command

set CACHE='n';


reponse



success

reponse
success

desc;


object_name object_type



t_site_id col_type
t_dept_id col_type
t_item_id col_type
t_customer_id col_type
t_date col_type
t_customer_info col_type
end_user col_type
items table
customers table
item_tags table
fidelity_cards table
item_customer_infos table
sales table
inventory table
v_items view
v_item_tags view
v_fidelity_cards view
v_item_customer_infos view
v_sales view
v_inventory view
TCP_PORT_TRANSAC parameter
LOG_VERBOSE parameter
CPU_COUNT parameter
SLEEP_AFTER_SQL parameter
ARRAY_BLOCK_SIZE_BIG parameter
ARRAY_BLOCK_SIZE_SMALL parameter
PROD_SERVER parameter
MAX_PERF_USE_COMPRESSION parameter
PARETO_LIMIT parameter
CACHE parameter
MAX_PERF_CHANGE_COUNT parameter
ALLOW_WHERE_ERROR parameter
ALLOW_GROUP_BY_ERROR parameter
INIT_FILE_PATH parameter

object_name	object_type
t_site_id	col_type
t_dept_id	col_type
t_item_id	col_type
t_customer_id	col_type
t_date	col_type
t_customer_info	col_type
end_user	col_type
items	table
customers	table
item_tags	table
fidelity_cards	table
item_customer_infos	table
sales	table
inventory	table
v_items	view
v_item_tags	view
v_fidelity_cards	view
v_item_customer_infos	view
v_sales	view
v_inventory	view
TCP_PORT_TRANSAC	parameter
LOG_VERBOSE	parameter
CPU_COUNT	parameter
SLEEP_AFTER_SQL	parameter
ARRAY_BLOCK_SIZE_BIG	parameter
ARRAY_BLOCK_SIZE_SMALL	parameter
PROD_SERVER	parameter
MAX_PERF_USE_COMPRESSION	parameter
PARETO_LIMIT	parameter
CACHE	parameter
MAX_PERF_CHANGE_COUNT	parameter
ALLOW_WHERE_ERROR	parameter
ALLOW_GROUP_BY_ERROR	parameter
INIT_FILE_PATH	parameter

desc col_type;


col_type_name data_type ival_count



t_site_id text 0
t_dept_id text 3
t_item_id text 5
t_customer_id text 3
t_date text 7
t_customer_info text 4
end_user text 0

col_type_name	data_type	ival_count
t_site_id	text	0
t_dept_id	text	3
t_item_id	text	5
t_customer_id	text	3
t_date	text	7
t_customer_info	text	4
end_user	text	0

desc col_type verbose;


col_type_name data_type ival_count



t_site_id text 0
t_dept_id text 3
t_item_id text 5
t_customer_id text 3
t_date text 7
t_customer_info text 4
end_user text 0
sys#type#items#art_label text 3
sys#type#items#avg_week_sales number 1
sys#type#items#sales_price number 3
sys#type#customers#customer_name text 2
sys#type#item_tags#tag text 2
sys#type#sales#sales_qty number 5
sys#type#sales#line_id text 7
sys#type#inventory#inv_qty number 2

col_type_name	data_type	ival_count
t_site_id	text	0
t_dept_id	text	3
t_item_id	text	5
t_customer_id	text	3
t_date	text	7
t_customer_info	text	4
end_user	text	0
sys#type#items#art_label	text	3
sys#type#items#avg_week_sales	number	1
sys#type#items#sales_price	number	3
sys#type#customers#customer_name	text	2
sys#type#item_tags#tag	text	2
sys#type#sales#sales_qty	number	5
sys#type#sales#line_id	text	7
sys#type#inventory#inv_qty	number	2

desc table;


table_name column_count line_count has_delete has_update parent_view_hidden



items 5 3 n n n
customers 2 2 n n n
item_tags 2 2 n n n
fidelity_cards 4 2 n n n
item_customer_infos 5 3 n n n
sales 6 7 n n n
inventory 2 2 n n n

table_name	column_count	line_count	has_delete	has_update	parent_view_hidden
items	5	3	n	n	n
customers	2	2	n	n	n
item_tags	2	2	n	n	n
fidelity_cards	4	2	n	n	n
item_customer_infos	5	3	n	n	n
sales	6	7	n	n	n
inventory	2	2	n	n	n

desc table items;


table_name column_name column_type col_type_name



items item_id text t_item_id
items art_label text sys#type#items#art_label
items dept text t_dept_id
items avg_week_sales number sys#type#items#avg_week_sales
items sales_price number sys#type#items#sales_price

table_name	column_name	column_type	col_type_name
items	item_id	text	t_item_id
items	art_label	text	sys#type#items#art_label
items	dept	text	t_dept_id
items	avg_week_sales	number	sys#type#items#avg_week_sales
items	sales_price	number	sys#type#items#sales_price

desc table item_tags;


table_name column_name column_type col_type_name



item_tags item_id text t_item_id
item_tags tag text sys#type#item_tags#tag

table_name	column_name	column_type	col_type_name
item_tags	item_id	text	t_item_id
item_tags	tag	text	sys#type#item_tags#tag

desc table item_tags verbose;


table_name column_name column_type col_type_name



item_tags item_id text t_item_id
item_tags tag text sys#type#item_tags#tag

table_name	column_name	column_type	col_type_name
item_tags	item_id	text	t_item_id
item_tags	tag	text	sys#type#item_tags#tag

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 7 n n n 10 2 15 17 0 0
v_inventory 2 2 n n n 10 0 12 12 0 0

desc view v_items;


view_name table_name column_names



v_items items item_id,art_label,dept,avg_week_sales,sales_price

desc view v_item_tags;


view_name table_name column_names



v_item_tags item_tags item_id,tag

desc view v_sales;


view_name table_name column_names



v_sales sales item_id,customer_id,sales_date,sales_qty,line_id,packaging_id
v_sales items item_id,art_label,dept,avg_week_sales,sales_price
v_sales customers customer_id,customer_name

desc view v_sales verbose;


view_name table_name column_name column_type col_type_name



v_sales sales item_id text t_item_id
v_sales sales customer_id text t_customer_id
v_sales sales sales_date text t_date
v_sales sales sales_qty number sys#type#sales#sales_qty
v_sales sales line_id text sys#type#sales#line_id
v_sales sales packaging_id text t_item_id
v_sales items item_id text t_item_id
v_sales items art_label text sys#type#items#art_label
v_sales items dept text t_dept_id
v_sales items avg_week_sales number sys#type#items#avg_week_sales
v_sales items sales_price number sys#type#items#sales_price
v_sales customers customer_id text t_customer_id
v_sales customers customer_name text sys#type#customers#customer_name

--

upsert (merge table)

Insert into a merge table will upsert (update if PK already exits, insert otherwise)

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

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


reponse



success

refresh dirty view;


reponse



success

select * from v_items;


item_id art_label dept avg_week_sales sales_price



artB the article B dept #2 10 3.200
box1 a box packaging 10 0
artA ### the article A ### dept #1 10 12.123

--

save

Save data on disk (persistence)

save

save;


reponse



success

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


reponse



success

desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 8 n n n

bounce;
--insert is lost
desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 7 n n n

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


reponse



success

--save data on disk
save;


reponse



success

bounce;
--insert is not lost
desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 8 n n n

insert into sales values('artA','C2','20191231',5,'ID07_','box1');


reponse



success

--table is updated
desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 9 n n n

--but not the view
select count(*) from v_sales;


count(*)



7

--because view is dirty
desc view callback where(1,'v_sales');


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_sales 3 7 y n n 10 2 15 17 0 0

save;


reponse



success

bounce;
--view is still dirty if we bounce SB
desc view callback where(1,'v_sales')
;


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_sales 3 7 y n n 10 2 15 17 0 0

refresh dirty view;


reponse



success

--view is no longer dirty
desc view callback where(1,'v_sales')
;


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_sales 3 9 n n n 10 2 17 19 0 0

--and count is correct
select count(*) from v_sales
;


count(*)



9

insert from file

insert into table_name select * from file(<'path/to/file'|'file_name under CSV_FILES_DIR/table_name'>)

desc parameter verbose callback where(1,'FILE_SEPARATOR');


param_name current_value comment is_default default_value



FILE_SEPARATOR , one character, CSV file separator y ,

desc parameter verbose callback where(1,'HEADER');


param_name current_value comment is_default default_value



HEADER y y/n, y: CSV files have a header line y y

--path/to/file (contains at least one /)
system '(echo ''item_id,customer_id,sales_date,sales_qty,line_id''>/tmp/foo.csv)';


reponse

system '(echo ''item_id_01,customer_id_01,sales_date_01,1,line_id_01''>>/tmp/foo.csv)';


reponse

insert into sales select * from file('/tmp/foo.csv');


reponse



success

--using CSV_FILES_DIR
desc parameter verbose callback where(1,'CSV_FILES_DIR');


param_name current_value comment is_default default_value



CSV_FILES_DIR ../STORAGE/CSV string, CSV file location used in "insert from file" when file path does not start with / y ../STORAGE/CSV

system 'mkdir -p ../STORAGE/CSV/sales';


reponse

system '(echo ''item_id,customer_id,sales_date,sales_qty,line_id''>../STORAGE/CSV/sales/foo.csv)';


reponse

system '(echo ''item_id_02,customer_id_03,sales_date_04,1,line_id_05''>>../STORAGE/CSV/sales/foo.csv)';


reponse

insert into sales select * from file('foo.csv');


reponse



success

--the column order does not matter
--in HEADER:y mode, SB is flexible regarding the columns provided in the csv file
--header is not case sensitive
desc parameter verbose callback where(1,'NEW_COLUMN_NULL_STRING');


param_name current_value comment is_default default_value



NEW_COLUMN_NULL_STRING NO_DATA string, default value when a column is added to a table or when a column is not provided in a CSV file y NO_DATA

system '(echo ''sales_DATE,item_id,sales_qty_new,line_id''>/tmp/foo.csv)';


reponse

system '(echo ''sales_date_03,item_id_03,3,line_id_03''>>/tmp/foo.csv)';


reponse

system '(echo ''sales_date_03,item_id_04,4,line_id_04''>>/tmp/foo.csv)';


reponse

insert into sales select * from file('/tmp/foo.csv');


reponse



success

--same with a merge table
system '(echo ''___art_label,dept,item_id''>/tmp/foo.csv)';


reponse

system '(echo ''art_label_01,dept_01,item_id_01''>>/tmp/foo.csv)';


reponse

system '(echo ''art_label_02,dept_02,artA''>>/tmp/foo.csv)';


reponse

insert into items select * from file('/tmp/foo.csv');


reponse



success

--SB is not flexible if HEADER:n
set HEADER='n';


reponse



success

continue_on_error(63);


reponse



success

insert into items select * from file('/tmp/foo.csv');


reponse



error 63 (continue): end of line found where non expected

stop_on_error;


reponse



success

set HEADER='y';


reponse



success

--view new lines
refresh dirty view;


reponse



success

select sales#item_id,sales#customer_id,sales_date,sales_qty,line_id from v_sales callback sort(5,'asc');


sales#item_id sales#customer_id sales_date sales_qty line_id



artA C1 20191231 5 ID01
artB C2 20200102 6 ID02
artB C1 20191231 4 ID03
artB C2 20200102 7 ID04
artC C1 20200102 8 ID05
artA C1 20191231 5 ID06
artA C2 20191231 5 ID07
artA C2 20191231 5 ID07
artA C2 20191231 5 ID07_
item_id_01 customer_id_01 sales_date_01 1 line_id_01
item_id_03 NO_DATA sales_date_03 0 line_id_03
item_id_04 NO_DATA sales_date_03 0 line_id_04
item_id_02 customer_id_03 sales_date_04 1 line_id_05

select * from v_items callback sort(1,'asc');


item_id art_label dept avg_week_sales sales_price



artA NO_DATA dept_02 0 0
artB the article B dept #2 10 3.200
box1 a box packaging 10 0
item_id_01 NO_DATA dept_01 0 0

--parameter ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE
desc parameter verbose callback where(1,'ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE');


param_name current_value comment is_default default_value



ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE n y/n, y: ,"abc,def", in a csv file with , separator is interpreted as one value: abc,def y n

system '(echo ''art_label,dept,item_id''>/tmp/foo.csv)';


reponse

system '(echo ''"art_label_03,art_label_03",dept_03,"item_id_03,item_id_03"''>>/tmp/foo.csv)';


reponse

continue_on_error(63);


reponse



success

insert into items select * from file('/tmp/foo.csv');


reponse



error 63 (continue): end of line found where non expected

stop_on_error;


reponse



success

set escape_separator_inside_double_quote='y';


reponse



success

insert into items select * from file('/tmp/foo.csv');


reponse



success

--buffer load for huge files, in this mode SB is not flexible even if HEADER:y
set file_load_without_buffer='n';


reponse



success

system '(echo ''item_id,art_label,dept,avg_week_sales,sales_price''>/tmp/foo.csv)';


reponse

system '(echo ''"item_id_04,item_id_04","art_label_04,art_label_04",dept_04,99,12.123''>>/tmp/foo.csv)';


reponse

insert into items select * from file('/tmp/foo.csv');


reponse



success

--view new lines
refresh dirty view;


reponse



success

select sales#item_id,sales#customer_id,sales_date,sales_qty,line_id from v_sales;


sales#item_id sales#customer_id sales_date sales_qty line_id



artA C1 20191231 5 ID01
artB C2 20200102 6 ID02
artB C1 20191231 4 ID03
artA C1 20191231 5 ID06
artA C2 20191231 5 ID07
artA C2 20191231 5 ID07
artA C2 20191231 5 ID07_
artB C2 20200102 7 ID04
artC C1 20200102 8 ID05
item_id_01 customer_id_01 sales_date_01 1 line_id_01
item_id_02 customer_id_03 sales_date_04 1 line_id_05
item_id_03 NO_DATA sales_date_03 0 line_id_03
item_id_04 NO_DATA sales_date_03 0 line_id_04

select * from v_items;


item_id art_label dept avg_week_sales sales_price



artB the article B dept #2 10 3.200
box1 a box packaging 10 0
item_id_01 NO_DATA dept_01 0 0
artA NO_DATA dept_02 0 0
item_id_03,item_id_03 art_label_03,art_label_03 dept_03 0 0
item_id_04,item_id_04 art_label_04,art_label_04 dept_04 99 12.123

bounce;
--

delete

--before situation
select customer_id, count(*) from v_sales group by customer_id;


customer_id count(*)



C1 4
C2 3

desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 9 n n n

--run delete
delete sales where customer_id='C1';


reponse



success

desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 9 y n n

--apply delete
refresh dirty view;


reponse



success

--delete has been applied
select customer_id, count(*) from v_sales group by customer_id;


customer_id count(*)



C2 5

desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 5 n n n

--nothing to delete case (won't trigger table/view rebuild)
delete sales where customer_id='C1';


reponse



success

desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 5 n n n

bounce;
--run select
delete sales where customer_id='C1';


reponse



success

bounce;
--apply select
refresh dirty view;


reponse



success

--delete is lost
select customer_id, count(*) from v_sales group by customer_id;


customer_id count(*)



C1 4
C2 5

delete sales where customer_id='C1';


reponse



success

refresh dirty table;


reponse



success

--delete has not been applied
desc view callback where(1,'v_sales');


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_sales 3 9 y n n 10 2 17 19 0 0

continue_on_error(207);


reponse



success

--#SB no_cache
select customer_id, count(*) from v_sales group by customer_id;


customer_id count(*)



C1 4
C2 5

--but table is updated
desc table callback like(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 5 n n n

save;


reponse



success

bounce;
refresh dirty view;


reponse



success

--delete has been applied
select customer_id, count(*) from v_sales group by customer_id;


customer_id count(*)



C2 5

--

refresh_online

Unlike refresh, refresh_online won't generate downtime

refresh_online dirty view

insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 7 n n n

refresh_online dirty view;


reponse



success

--a new view is created on top of new data
desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 5 y n n 10 0 15 15 1 1
v_sales_#refresh_online#_00001 3 2 n n n 15 0 12 12 0 0

--select against v_sales will also be executed against the refresh_online view
select count(*) from v_sales;


count(*)



7

--
--let's do it again
insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

refresh_online dirty view;


reponse



success

--now 2 refresh_online views, the first one is now obsolete (check column mp_r_online_count)
desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 5 y n n 10 0 15 15 2 2
v_sales_#refresh_online#_00001 3 2 n n n 15 0 12 12 0 0
v_sales_#refresh_online#_00002 3 3 n n n 15 0 13 13 0 0

select count(*) from v_sales;


count(*)



8

--
--the refresh will recompute the view, and the refresh_online view becomes obsolete
refresh dirty view;


reponse



success

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 8 n n n 10 2 16 18 0 0
v_sales_#refresh_online#_00001 3 2 n n n 15 0 12 12 0 0
v_sales_#refresh_online#_00002 3 3 n n n 15 0 13 13 0 0

select count(*) from v_sales;


count(*)



8

--
--
--a background thread will clean (drop) the obsolete refresh_online view
--the clean can also be triggered by a refresh_force
refresh_force dirty view;


reponse



success

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 8 n n n 10 2 16 18 0 0

select count(*) from v_sales;


count(*)



8

--

partitions

Size of table is limited to 4G lines, hence need for partition

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 8 n n n

insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 9 n n n

set partition_line_count=1;


reponse



success

desc parameter callback where(1,'PARTITION_LINE_COUNT');


param_name current_value comment is_default default_value



PARTITION_LINE_COUNT 1 integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partition n 3000000000

--next insert will trigger a new partition
insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 9 n n n
sales_#partition#_00001 6 1 n n n

refresh dirty view;


reponse



success

--and corresponding view
desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 9 n n n 10 2 17 19 0 0
v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0

--select against v_sales will also be executed against partition
--ACCEPT_DIFF_START
--#SB log_verbose explain
select count(*) from v_sales;


logs

--#SB explain
select count(*) from v_sales
callback like(1,'partition');


logs

--ACCEPT_DIFF_END
--
bounce;
refresh dirty view;


reponse



success

save;


reponse



success

--

refresh_online & partitions

A partition can have its refresh_online views attached

select count(*) from v_sales;


count(*)



5

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 5 n n n 10 0 15 15 0 0

--
insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 5 y n n 10 0 15 15 0 0

--I have a refresh_online view on top of the table
refresh_online dirty view;


reponse



success

select count(*) from v_sales;


count(*)



6

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 5 y n n 10 0 15 15 1 1
v_sales_#refresh_online#_00001 3 1 n n n 15 0 11 11 0 0

--
insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

set PARTITION_LINE_COUNT=1;


reponse



success

insert into sales values('artA','C1','20191231',5,'ID11','box1');


reponse



success

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 7 n n n
sales_#partition#_00001 6 1 n n n

refresh_online dirty view;


reponse



success

--I have a refresh_online view on top of the partition
desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 5 y n n 10 0 15 15 2 2
v_sales_#partition#_00001 3 0 y y y 10 0 0 0 1 1
v_sales_#partition#_00001_#refresh_online#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#refresh_online#_00001 3 1 n n n 15 0 11 11 0 0
v_sales_#refresh_online#_00002 3 2 n n n 15 0 12 12 0 0

select count(*) from v_sales;


count(*)



8

--ACCEPT_DIFF_START
--#SB no_cache explain
select count(*) from v_sales callback like(1,'running select');


logs

--ACCEPT_DIFF_END
--
refresh dirty view;


reponse



success

select count(*) from v_sales;


count(*)



8

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 7 n n n 10 2 15 17 0 0
v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#partition#_00001_#refresh_online#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#refresh_online#_00001 3 1 n n n 15 0 11 11 0 0
v_sales_#refresh_online#_00002 3 2 n n n 15 0 12 12 0 0

--
refresh_force dirty view;


reponse



success

select count(*) from v_sales;


count(*)



8

desc view callback like(1,'sales') sort(1,'asc');


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_sales 3 7 n n n 10 2 15 17 0 0
v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0

--

refresh advanced

Applies deletes + updates + data model changes on tables and views)

refresh dirty table

Recompute view but not max_perf

refresh view view_name

Recompute permissions if changes and drop cache

refresh permission

Recompute computed columns if changes and create a new version of stormbase.so under _SO_LINUX

refresh computed_column

Drop cache

refresh cache

Does all above and recomputes max_perf and re execute init file

refresh dirty view

--under construction
--

deletes & refresh_online

Some big tables have a "delete before insert" logic along with refresh_online needs

In previous version of Stormbase such tables were defined as big_online, now all big tables support this functionality

Big_online keyword is kept for backward compatibility

--I run a refresh_force to clean the old child view (not needed in client/server mode)
--delete sales where regex(item_id,'.*');
refresh_force dirty view;


reponse



success

-- set partition_line_count=100;
select list(line_id) from v_sales;


list(line_id)



ID02,ID04,ID07,ID07_,ID11

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 7 n n n
sales_#partition#_00001 6 1 n n n

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 7 n n n 10 2 15 17 0 0
v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0

--partial delete on big table not allowed by refresh_online
insert into sales values('artA','C1','20191231',5,'ID12','box1')('artA','C1','20191231',5,'ID13','box1');


reponse



success

delete sales where line_id='ID12';


reponse



success

continue_on_error(141);


reponse



success

refresh_online dirty view;


reponse



error 141 (continue): "refresh_online dirty views" is not allowed

stop_on_error;


reponse



success

refresh_force dirty view;


reponse



success

select list(line_id) from v_sales;


list(line_id)



ID02,ID04,ID07,ID07_,ID11,ID13

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 7 n n n 10 2 15 17 0 0
v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#partition#_00002 3 1 n n n 10 0 11 11 0 0

--full delete on big table is allowed by refresh_online
delete sales where regex(item_id,'.*');


reponse



success

insert into sales values('artA','C1','20191231',5,'ID14','box1');


reponse



success

--refresh_online is accepted
refresh_online dirty view;


reponse



success

select list(line_id) from v_sales;


list(line_id)



ID14

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 0 n n y
sales_#partition#_00001 6 0 n n y
sales_#partition#_00002 6 1 n n y

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 7 y n n 10 2 15 17 1 0
v_sales_#partition#_00001 3 1 y n n 10 0 11 11 1 0
v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00002 3 1 y n n 10 0 11 11 1 1
v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0

--redo
delete sales where regex(item_id,'.*');


reponse



success

insert into sales values('artA','C1','20191231',5,'ID15','box1')('artA','C1','20191231',5,'ID16','box1');


reponse



success

refresh_online dirty view;


reponse



success

select list(line_id) from v_sales;


list(line_id)



ID15,ID16

--the main view is flagged as with hide_parent_view
--so the select will not see this view
desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 0 n n y
sales_#partition#_00001 6 0 n n y
sales_#partition#_00002 6 2 n n y

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 7 y n n 10 2 15 17 2 0
v_sales_#partition#_00001 3 1 y n n 10 0 11 11 2 0
v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00001_#refresh_online#_00002 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00002 3 1 y n n 10 0 11 11 2 2
v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#partition#_00002_#refresh_online#_00002 3 2 n n n 10 0 12 12 0 0
v_sales_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#refresh_online#_00002 3 0 n n n 10 0 10 10 0 0

--after bounce, data is still here
save;


reponse



success

bounce;
desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 0 n n y
sales_#partition#_00001 6 0 n n y
sales_#partition#_00002 6 2 n n y

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 7 y n n 10 2 15 17 2 0
v_sales_#partition#_00001 3 1 y n n 10 0 11 11 2 0
v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00001_#refresh_online#_00002 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00002 3 1 y n n 10 0 11 11 2 2
v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#partition#_00002_#refresh_online#_00002 3 2 n n n 10 0 12 12 0 0
v_sales_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#refresh_online#_00002 3 0 n n n 10 0 10 10 0 0

select list(line_id) from v_sales;


list(line_id)



ID15,ID16

--after clear_max_perf the non dirty views are rebuilt
save;


reponse



success

system './clear_max_perf.sh sure';


reponse



sure mode, no confirmation prompt
clearing directory ../STORAGE/MAX_PERF...
done

bounce;
desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 0 n n y
sales_#partition#_00001 6 0 n n y
sales_#partition#_00002 6 2 n n y

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 7 y y y 10 0 0 0 2 0
v_sales_#partition#_00001 3 1 y y y 10 0 0 0 2 0
v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00001_#refresh_online#_00002 3 0 n n n 10 0 10 10 0 0
v_sales_#partition#_00002 3 1 y y y 10 0 0 0 2 2
v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n n 10 0 11 11 0 0
v_sales_#partition#_00002_#refresh_online#_00002 3 2 n n n 10 0 12 12 0 0
v_sales_#refresh_online#_00001 3 0 n n n 10 0 10 10 0 0
v_sales_#refresh_online#_00002 3 0 n n n 10 0 10 10 0 0

select list(line_id) from v_sales;


list(line_id)



ID15,ID16

--refresh dirty view cleans everything
refresh_force dirty view;


reponse



success

desc table callback like(1,'sales') sort(1,'asc');


table_name column_count line_count has_delete has_update parent_view_hidden



sales 6 0 n n n
sales_#partition#_00001 6 0 n n n
sales_#partition#_00002 6 2 n n n

desc view callback like(1,'v_sales') sort(1,'asc');


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_sales 3 0 n n n 10 0 10 10 2 0
v_sales_#partition#_00001 3 0 n n n 10 0 10 10 2 0
v_sales_#partition#_00001_#refresh_online#_00001 3 0 y n n 10 0 10 10 0 0
v_sales_#partition#_00001_#refresh_online#_00002 3 0 y n n 10 0 10 10 0 0
v_sales_#partition#_00002 3 2 n n n 10 0 12 12 0 0
v_sales_#refresh_online#_00001 3 0 y n n 10 0 10 10 0 0
v_sales_#refresh_online#_00002 3 0 y n n 10 0 10 10 0 0

select list(line_id) from v_sales;


list(line_id)



ID15,ID16

-- ###########################
-- RUNNING save.sql
save;


reponse



success

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

view_name	table_name	column_names
v_sales	sales	item_id,customer_id,sales_date,sales_qty,line_id,packaging_id
v_sales	items	item_id,art_label,dept,avg_week_sales,sales_price
v_sales	customers	customer_id,customer_name

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

sales#item_id	sales#customer_id	sales_date	sales_qty	line_id
artA	C1	20191231	5	ID01
artB	C2	20200102	6	ID02
artB	C1	20191231	4	ID03
artB	C2	20200102	7	ID04
artC	C1	20200102	8	ID05
artA	C1	20191231	5	ID06
artA	C2	20191231	5	ID07
artA	C2	20191231	5	ID07
artA	C2	20191231	5	ID07_
item_id_01	customer_id_01	sales_date_01	1	line_id_01
item_id_03	NO_DATA	sales_date_03	0	line_id_03
item_id_04	NO_DATA	sales_date_03	0	line_id_04
item_id_02	customer_id_03	sales_date_04	1	line_id_05

item_id	art_label	dept	avg_week_sales	sales_price
artA	NO_DATA	dept_02	0	0
artB	the article B	dept #2	10	3.200
box1	a box	packaging	10	0
item_id_01	NO_DATA	dept_01	0	0

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_sales	3	5	y	n	n	10	0	15	15	1	1
v_sales_#refresh_online#_00001	3	2	n	n	n	15	0	12	12	0	0

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_often_count	mp_count	r_online_count	mp_r_online_count
v_sales	3	5	y	n	n	10	15	15	2	2
v_sales_#refresh_online#_00001	3	2	n	n	n	15	12	12	0	0
v_sales_#refresh_online#_00002	3	3	n	n	n	15	13	13	0	0

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_sales	3	9	n	n	n	10	2	17	19	0	0
v_sales_#partition#_00001	3	1	n	n	n	10	0	11	11	0	0

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_often_count	mp_count	r_online_count	mp_r_online_count
v_sales	3	5	y	n	n	10	15	15	2	2
v_sales_#partition#_00001	3	0	y	y	y	10	0	0	1	1
v_sales_#partition#_00001_#refresh_online#_00001	3	1	n	n	n	10	11	11	0	0
v_sales_#refresh_online#_00001	3	1	n	n	n	15	11	11	0	0
v_sales_#refresh_online#_00002	3	2	n	n	n	15	12	12	0	0

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_rare_count	mp_often_count	mp_count
v_sales	3	7	n	n	n	10	2	15	17
v_sales_#partition#_00001	3	1	n	n	n	10	0	11	11
v_sales_#partition#_00002	3	1	n	n	n	10	0	11	11

table_name	column_count	line_count	has_delete	has_update	parent_view_hidden
sales	6	0	n	n	y
sales_#partition#_00001	6	0	n	n	y
sales_#partition#_00002	6	1	n	n	y

< * | *** >