<  *  | *** >

Create / insert / refresh 

  • create col_type
  • create table
  • create view
  • insert values
  • refresh dirty view
  • view data model & parameters
  • upsert (merge table)
  • save
  • insert from file
  • delete
  • refresh_online
  • partitions
  • refresh_online & partitions
  • refresh advanced
  • deletes & refresh_online

-- ###########################
-- 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_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

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

--the end_user col_type is used for permissions (explained later)
create col_type end_user as text;
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

create merge table customers( customer_id t_customer_id, customer_name text);
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

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

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

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

--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

create view v_inventory as select * from inventory, items where items.item_id=inventory.item_id;
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

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

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

--

view data model & parameters

Desc (describe) command
desc <|col_type|table|view|context|computed_columns|parameter>
desc col_type <|verbose>
desc table the_table <|verbose>
desc view the_view <|verbose>
set CACHE='n';
reponse
success

desc;
object_nameobject_type
t_site_idcol_type
t_dept_idcol_type
t_item_idcol_type
t_customer_idcol_type
t_datecol_type
t_customer_infocol_type
end_usercol_type
itemstable
customerstable
item_tagstable
fidelity_cardstable
item_customer_infostable
salestable
inventorytable
v_itemsview
v_item_tagsview
v_fidelity_cardsview
v_item_customer_infosview
v_salesview
v_sales_#partition#_00001view
v_sales_#partition#_00002view
sales_#partition#_00001table
v_inventoryview
v_sales_#partition#_00003view
sales_#partition#_00002table
sales_#partition#_00003table
TCP_PORT_TRANSACparameter
LOG_VERBOSEparameter
CPU_COUNTparameter
SLEEP_AFTER_SQLparameter
ARRAY_BLOCK_SIZE_BIGparameter
ARRAY_BLOCK_SIZE_SMALLparameter
PROD_SERVERparameter
MAX_PERF_USE_COMPRESSIONparameter
PARETO_LIMITparameter
PARTITION_LINE_COUNTparameter
CACHEparameter
MAX_PERF_CHANGE_COUNTparameter
INIT_FILE_PATHparameter

desc col_type;
col_type_namedata_typeival_count
t_site_idtext0
t_dept_idtext3
t_item_idtext5
t_customer_idtext3
t_datetext7
t_customer_infotext4
end_usertext0

desc col_type verbose;
col_type_namedata_typeival_count
t_site_idtext0
t_dept_idtext3
t_item_idtext5
t_customer_idtext3
t_datetext7
t_customer_infotext4
end_usertext0
sys#type#items#art_labeltext3
sys#type#items#avg_week_salesnumber1
sys#type#items#sales_pricenumber3
sys#type#customers#customer_nametext2
sys#type#item_tags#tagtext2
sys#type#sales#sales_qtynumber5
sys#type#sales#line_idtext7
sys#type#inventory#inv_qtynumber2

desc table;
table_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
items53nnn
customers22nnn
item_tags22nnn
fidelity_cards42nnn
item_customer_infos53nnn
sales62nnn
inventory22nnn
sales_#partition#_0000162nnn
sales_#partition#_0000262nnn
sales_#partition#_0000361nnn

desc table items;
table_namecolumn_namecolumn_typecol_type_name
itemsitem_idtextt_item_id
itemsart_labeltextsys#type#items#art_label
itemsdepttextt_dept_id
itemsavg_week_salesnumbersys#type#items#avg_week_sales
itemssales_pricenumbersys#type#items#sales_price

desc table item_tags;
table_namecolumn_namecolumn_typecol_type_name
item_tagsitem_idtextt_item_id
item_tagstagtextsys#type#item_tags#tag

desc table item_tags verbose;
table_namecolumn_namecolumn_typecol_type_name
item_tagsitem_idtextt_item_id
item_tagstagtextsys#type#item_tags#tag

desc view;
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_items13nnn100131300
v_item_tags12nnn100121200
v_fidelity_cards12nnn100121200
v_item_customer_infos13nnn100131300
v_sales32nnn100121200
v_sales_#partition#_0000132nnn100121200
v_sales_#partition#_0000232nnn100121200
v_inventory22nnn100121200
v_sales_#partition#_0000331nnn100111100

desc view v_items;
view_nametable_namecolumn_names
v_itemsitemsitem_id,art_label,dept,avg_week_sales,sales_price

desc view v_item_tags;
view_nametable_namecolumn_names
v_item_tagsitem_tagsitem_id,tag

desc view v_sales;
view_nametable_namecolumn_names
v_salessalesitem_id,customer_id,sales_date,sales_qty,line_id,packaging_id
v_salesitemsitem_id,art_label,dept,avg_week_sales,sales_price
v_salescustomerscustomer_id,customer_name

desc view v_sales verbose;
view_nametable_namecolumn_namecolumn_typecol_type_name
v_salessalesitem_idtextt_item_id
v_salessalescustomer_idtextt_customer_id
v_salessalessales_datetextt_date
v_salessalessales_qtynumbersys#type#sales#sales_qty
v_salessalesline_idtextsys#type#sales#line_id
v_salessalespackaging_idtextt_item_id
v_salesitemsitem_idtextt_item_id
v_salesitemsart_labeltextsys#type#items#art_label
v_salesitemsdepttextt_dept_id
v_salesitemsavg_week_salesnumbersys#type#items#avg_week_sales
v_salesitemssales_pricenumbersys#type#items#sales_price
v_salescustomerscustomer_idtextt_customer_id
v_salescustomerscustomer_nametextsys#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_idart_labeldeptavg_week_salessales_price
artAthe article Adept #1101.500
artBthe article Bdept #2103.200
box1a boxpackaging100

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_idart_labeldeptavg_week_salessales_price
artBthe article Bdept #2103.200
box1a boxpackaging100
artA### the article A ###dept #11012.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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales62nnn

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

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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales62nnn

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

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

--but not the view
select count(*) from v_sales;
reponse
view and table out of sync, refresh dirty view is needed

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

save;
reponse
success

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

refresh dirty view;
reponse
success

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

--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_namecurrent_valuecommentis_defaultdefault_value
FILE_SEPARATOR,one character, CSV file separatory,

desc parameter verbose callback where(1,'HEADER');
param_namecurrent_valuecommentis_defaultdefault_value
HEADERyy/n, y: CSV files have a header lineyy

--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_namecurrent_valuecommentis_defaultdefault_value
CSV_FILES_DIR../STORAGE/CSVstring, 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_namecurrent_valuecommentis_defaultdefault_value
NEW_COLUMN_NULL_STRINGNO_DATAstring, default value when a column is added to a table or when a column is not provided in a CSV fileyNO_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_idsales#customer_idsales_datesales_qtyline_id
artAC1201912315ID01
artBC2202001026ID02
artBC1201912314ID03
artBC2202001027ID04
artCC1202001028ID05
artAC1201912315ID06
artAC2201912315ID07
artAC2201912315ID07
artAC2201912315ID07_
item_id_01customer_id_01sales_date_011line_id_01
item_id_03NO_DATAsales_date_030line_id_03
item_id_04NO_DATAsales_date_030line_id_04
item_id_02customer_id_03sales_date_041line_id_05

select * from v_items callback sort(1,'asc');
item_idart_labeldeptavg_week_salessales_price
artANO_DATAdept_0200
artBthe article Bdept #2103.200
box1a boxpackaging100
item_id_01NO_DATAdept_0100

--parameter ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE
desc parameter verbose callback where(1,'ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE');
param_namecurrent_valuecommentis_defaultdefault_value
ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTEny/n, y: ,"abc,def", in a csv file with , separator is interpreted as one value: abc,defyn

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_idsales#customer_idsales_datesales_qtyline_id
artAC1201912315ID01
artBC2202001026ID02
artBC1201912314ID03
artBC2202001027ID04
artCC1202001028ID05
artAC1201912315ID06
artAC2201912315ID07
artAC2201912315ID07
artAC2201912315ID07_
item_id_01customer_id_01sales_date_011line_id_01
item_id_02customer_id_03sales_date_041line_id_05
item_id_03NO_DATAsales_date_030line_id_03
item_id_04NO_DATAsales_date_030line_id_04

select * from v_items;
item_idart_labeldeptavg_week_salessales_price
artBthe article Bdept #2103.200
box1a boxpackaging100
item_id_01NO_DATAdept_0100
artANO_DATAdept_0200
item_id_03,item_id_03art_label_03,art_label_03dept_0300
item_id_04,item_id_04art_label_04,art_label_04dept_049912.123

bounce;
--

delete

--before situation
select customer_id, count(*) from v_sales group by customer_id;
reponse
view and table out of sync, refresh dirty view is needed

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

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

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

--apply delete
refresh dirty view;
reponse
success

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

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

--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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn

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_idcount(*)
C14
C25

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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales32ynn100121200

continue_on_error(207);
reponse
success

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

--but table is updated
desc table callback like(1,'sales');
table_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000461nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn

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_idcount(*)
C25

--

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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn
sales_#partition#_0000462nnn
sales_#partition#_0000561nnn

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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000431ynn100111111
v_sales_#partition#_00004_#refresh_online#_0000131nnn110111100
v_sales_#partition#_0000530yyy1000011
v_sales_#partition#_00005_#refresh_online#_0000131nnn100111100

--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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000431ynn100111122
v_sales_#partition#_00004_#refresh_online#_0000131nnn110111100
v_sales_#partition#_00004_#refresh_online#_0000231nnn110111100
v_sales_#partition#_0000530yyy1000022
v_sales_#partition#_00005_#refresh_online#_0000131nnn100111100
v_sales_#partition#_00005_#refresh_online#_0000232nnn100121200

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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_00004_#refresh_online#_0000131nnn110111100
v_sales_#partition#_00004_#refresh_online#_0000231nnn110111100
v_sales_#partition#_0000532nnn100121200
v_sales_#partition#_00005_#refresh_online#_0000131nnn100111100
v_sales_#partition#_00005_#refresh_online#_0000232nnn100121200

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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_0000532nnn100121200

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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn
sales_#partition#_0000462nnn
sales_#partition#_0000562nnn

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

desc table callback like(1,'sales') sort(1,'asc');
table_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn
sales_#partition#_0000462nnn
sales_#partition#_0000562nnn
sales_#partition#_0000661nnn

set partition_line_count=1;
reponse
success

desc parameter callback where(1,'PARTITION_LINE_COUNT');
param_namecurrent_valuecommentis_defaultdefault_value
PARTITION_LINE_COUNT1integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partitionn3000000000

--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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn
sales_#partition#_0000462nnn
sales_#partition#_0000562nnn
sales_#partition#_0000661nnn
sales_#partition#_0000761nnn

refresh dirty view;
reponse
success

--and corresponding view
desc view callback like(1,'sales') sort(1,'asc');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_0000532nnn100121200
v_sales_#partition#_0000631nnn100111100
v_sales_#partition#_0000731nnn100111100

--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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000431nnn100111100

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

desc view callback like(1,'sales') sort(1,'asc');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000431ynn100111100

--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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000431ynn100111111
v_sales_#partition#_00004_#refresh_online#_0000131nnn110111100

--
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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn
sales_#partition#_0000462nnn
sales_#partition#_0000561nnn
sales_#partition#_0000661nnn

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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000431ynn100111122
v_sales_#partition#_00004_#refresh_online#_0000131nnn110111100
v_sales_#partition#_00004_#refresh_online#_0000231nnn110111100
v_sales_#partition#_0000530yyy1000011
v_sales_#partition#_00005_#refresh_online#_0000131nnn100111100
v_sales_#partition#_0000630yyy1000011
v_sales_#partition#_00006_#refresh_online#_0000131nnn100111100

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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_00004_#refresh_online#_0000131nnn110111100
v_sales_#partition#_00004_#refresh_online#_0000231nnn110111100
v_sales_#partition#_0000531nnn100111100
v_sales_#partition#_00005_#refresh_online#_0000131nnn100111100
v_sales_#partition#_0000631nnn100111100
v_sales_#partition#_00006_#refresh_online#_0000131nnn100111100

--
refresh_force dirty view;
reponse
success

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

desc view callback like(1,'sales') sort(1,'asc');
view_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_0000531nnn100111100
v_sales_#partition#_0000631nnn100111100

--

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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales61nnn
sales_#partition#_0000161nnn
sales_#partition#_0000260nnn
sales_#partition#_0000362nnn
sales_#partition#_0000462nnn
sales_#partition#_0000561nnn
sales_#partition#_0000661nnn

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_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_0000531nnn100111100
v_sales_#partition#_0000631nnn100111100

--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_nametable_countrel_countis_dirtyis_mp_dirtyis_partialfirst_ilinemp_rare_countmp_often_countmp_countr_online_countmp_r_online_count
v_sales31nnn100111100
v_sales_#partition#_0000131nnn100111100
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332nnn100121200
v_sales_#partition#_0000432nnn100121200
v_sales_#partition#_0000531nnn100111100
v_sales_#partition#_0000631nnn100111100
v_sales_#partition#_0000731nnn100111100

--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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales60nny
sales_#partition#_0000160nny
sales_#partition#_0000260nnn
sales_#partition#_0000360nny
sales_#partition#_0000460nny
sales_#partition#_0000560nny
sales_#partition#_0000660nny
sales_#partition#_0000761nny

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_sales31ynn100111110
v_sales_#partition#_0000131ynn100111110
v_sales_#partition#_00001_#refresh_online#_0000130nnn100101000
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332ynn100121210
v_sales_#partition#_00003_#refresh_online#_0000130nnn100101000
v_sales_#partition#_0000432ynn100121210
v_sales_#partition#_00004_#refresh_online#_0000130nnn100101000
v_sales_#partition#_0000531ynn100111110
v_sales_#partition#_00005_#refresh_online#_0000130nnn100101000
v_sales_#partition#_0000631ynn100111110
v_sales_#partition#_00006_#refresh_online#_0000130nnn100101000
v_sales_#partition#_0000731ynn100111111
v_sales_#partition#_00007_#refresh_online#_0000131nnn100111100
v_sales_#refresh_online#_0000130nnn100101000

--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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales60nny
sales_#partition#_0000160nny
sales_#partition#_0000260nnn
sales_#partition#_0000360nny
sales_#partition#_0000460nny
sales_#partition#_0000560nny
sales_#partition#_0000660nny
sales_#partition#_0000762nny

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_sales31ynn100111120
v_sales_#partition#_0000131ynn100111120
v_sales_#partition#_00001_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00001_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332ynn100121220
v_sales_#partition#_00003_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00003_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000432ynn100121220
v_sales_#partition#_00004_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00004_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000531ynn100111120
v_sales_#partition#_00005_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00005_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000631ynn100111120
v_sales_#partition#_00006_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00006_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000731ynn100111122
v_sales_#partition#_00007_#refresh_online#_0000131nnn100111100
v_sales_#partition#_00007_#refresh_online#_0000232nnn100121200
v_sales_#refresh_online#_0000130nnn100101000
v_sales_#refresh_online#_0000230nnn100101000

--after bounce, data is still here
save;
reponse
success

bounce;
desc table callback like(1,'sales') sort(1,'asc');
table_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales60nny
sales_#partition#_0000160nny
sales_#partition#_0000260nnn
sales_#partition#_0000360nny
sales_#partition#_0000460nny
sales_#partition#_0000560nny
sales_#partition#_0000660nny
sales_#partition#_0000762nny

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_sales31ynn100111120
v_sales_#partition#_0000131ynn100111120
v_sales_#partition#_00001_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00001_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332ynn100121220
v_sales_#partition#_00003_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00003_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000432ynn100121220
v_sales_#partition#_00004_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00004_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000531ynn100111120
v_sales_#partition#_00005_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00005_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000631ynn100111120
v_sales_#partition#_00006_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00006_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000731ynn100111122
v_sales_#partition#_00007_#refresh_online#_0000131nnn100111100
v_sales_#partition#_00007_#refresh_online#_0000232nnn100121200
v_sales_#refresh_online#_0000130nnn100101000
v_sales_#refresh_online#_0000230nnn100101000

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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales60nny
sales_#partition#_0000160nny
sales_#partition#_0000260nnn
sales_#partition#_0000360nny
sales_#partition#_0000460nny
sales_#partition#_0000560nny
sales_#partition#_0000660nny
sales_#partition#_0000762nny

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_sales31yyy1000020
v_sales_#partition#_0000131yyy1000020
v_sales_#partition#_00001_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00001_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000332yyy1000020
v_sales_#partition#_00003_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00003_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000432yyy1000020
v_sales_#partition#_00004_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00004_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000531yyy1000020
v_sales_#partition#_00005_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00005_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000631yyy1000020
v_sales_#partition#_00006_#refresh_online#_0000130nnn100101000
v_sales_#partition#_00006_#refresh_online#_0000230nnn100101000
v_sales_#partition#_0000731yyy1000022
v_sales_#partition#_00007_#refresh_online#_0000131nnn100111100
v_sales_#partition#_00007_#refresh_online#_0000232nnn100121200
v_sales_#refresh_online#_0000130nnn100101000
v_sales_#refresh_online#_0000230nnn100101000

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_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
sales60nnn
sales_#partition#_0000160nnn
sales_#partition#_0000260nnn
sales_#partition#_0000360nnn
sales_#partition#_0000460nnn
sales_#partition#_0000560nnn
sales_#partition#_0000660nnn
sales_#partition#_0000762nnn

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_sales30nnn100101020
v_sales_#partition#_0000130nnn100101020
v_sales_#partition#_00001_#refresh_online#_0000130ynn100101000
v_sales_#partition#_00001_#refresh_online#_0000230ynn100101000
v_sales_#partition#_0000230nnn100101000
v_sales_#partition#_0000330nnn100101020
v_sales_#partition#_00003_#refresh_online#_0000130ynn100101000
v_sales_#partition#_00003_#refresh_online#_0000230ynn100101000
v_sales_#partition#_0000430nnn100101020
v_sales_#partition#_00004_#refresh_online#_0000130ynn100101000
v_sales_#partition#_00004_#refresh_online#_0000230ynn100101000
v_sales_#partition#_0000530nnn100101020
v_sales_#partition#_00005_#refresh_online#_0000130ynn100101000
v_sales_#partition#_00005_#refresh_online#_0000230ynn100101000
v_sales_#partition#_0000630nnn100101020
v_sales_#partition#_00006_#refresh_online#_0000130ynn100101000
v_sales_#partition#_00006_#refresh_online#_0000230ynn100101000
v_sales_#partition#_0000732nnn100121200
v_sales_#refresh_online#_0000130ynn100101000
v_sales_#refresh_online#_0000230ynn100101000

select list(line_id) from v_sales;
list(line_id)
ID15,ID16

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

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