-- ########################### -- 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_parameters.sql --Parameters
desc parameter
desc <|parameter> <|verbose> --ACCEPT_DIFF_START --show non default parameter names desc callback where(2,'parameter');
object_name object_type 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
--show non default parameters information desc parameter;
param_name current_value comment is_default default_value TCP_PORT_TRANSAC 3319 integer, SB listening port for transaction mode n -1 LOG_VERBOSE y y/n, y: increase verbosity in stormbase.log n n CPU_COUNT 10 integer, number of threads that SB will create for most operations (select, refresh, insert) n 20 SLEEP_AFTER_SQL n y/n, y: a pause of 0.1 sec is added after each sql execution n y ARRAY_BLOCK_SIZE_BIG 2 integer, array size in B, this parameter should not be modified in there is already data n 10000 ARRAY_BLOCK_SIZE_SMALL 2 integer, small array size in B, this parameter should not be modified in there is already data n 100 PROD_SERVER n y/n, n: reduces SB internal memory for non prod server n y MAX_PERF_USE_COMPRESSION y y/n, y: SB will compress (put several ival in one integer) columns in memory n n PARETO_LIMIT 80 % float<=1, during max_perf ival are split between rare and often value, the x% more frequent ivals are the often ival n 100 % CACHE n integer, SB listening port for transaction mode n y MAX_PERF_CHANGE_COUNT n y/n, y: count becomes countdistinct n y ALLOW_WHERE_ERROR y y/n, y: where clause on bad column is ignored in select and does not return an error n n ALLOW_GROUP_BY_ERROR y y/n, y: group by clause on bad column is replaced by * in select resultset and does not return an error n n INIT_FILE_PATH init.sb string, path of script executed on startup and after "refresh dirty view" n ./init.sb
--show all parameters information desc parameter verbose;
param_name current_value comment is_default default_value TCP_PORT 2219 integer, SB listening port y 2219 TCP_PORT_TRANSAC 3319 integer, SB listening port for transaction mode n -1 LOG_VERBOSE y y/n, y: increase verbosity in stormbase.log n n GENERATE_REPLAY n y/n, y: sql are written in stormbase_replay.log y n CPU_COUNT 10 integer, number of threads that SB will create for most operations (select, refresh, insert) n 20 SLEEP_AFTER_SQL n y/n, y: a pause of 0.1 sec is added after each sql execution n y ACTIVITY_SECURITY n y/n, y: a dynamic pause (from 0 to 10 sec depending on #active sessions) is added before select execution y n SESSION_SECURITY y y/n, y: no more connections is accepted if #active sessions >= 30 y y CSV_FILES_DIR ../STORAGE/CSV string, CSV file location used in "insert from file" when file path does not start with / y ../STORAGE/CSV INTERNAL_FILES_DIR ../STORAGE/INT string, SB internal storage location y ../STORAGE/INT TMP_FILES_DIR ../STORAGE/TMP string, SB temporary storage location y ../STORAGE/TMP MAX_PERF_FILES_DIR ../STORAGE/MAX_PERF string, SB "memory image" storage location y ../STORAGE/MAX_PERF TRANSAC_FILES_DIR ../STORAGE/TRANSAC string, SB internal storage location for transaction mode y ../STORAGE/TRANSAC CELL_BLOCK_INCREMENT_SIZE_GB 0.10 GB float, CELL size, this parameter should not be modified if there is already data y 0.10 GB ARRAY_BLOCK_SIZE_BIG 2 integer, array size in B, this parameter should not be modified in there is already data n 10000 ARRAY_BLOCK_SIZE_SMALL 2 integer, small array size in B, this parameter should not be modified in there is already data n 100 MEMORY_MAX_GB 20 integer, SB allows defragmentation if "CELL memory" exceed this number y 20 UNFRAG_BLOCK_PER_BLOCK n y/n, y : SB will defrag block per block hence defrag will be longer but it won't consume more memory y n SPARSE_TEXT_KEEP 10000000 integer, number of values than can be displayed (last inserted) for sparse_text columns y 10000000 PROD_SERVER n y/n, n: reduces SB internal memory for non prod server n y IN_MEMORY_BIG_TABLES ,*, string, list (comma separated) of in memory big tables or * y ,*, NOT_IN_MEMORY_BIG_TABLES ,-1, string, list (comma separated) of "not in memory" big tables y ,-1, NOT_IN_MEMORY_COLUMNS ,-1, string, list (comma separated) of "not in memory columns" big tables y ,-1, MAX_PERF_INDEX_FOR_NOT_IN_MEMORY_BIG_TABLES n y/n, y: compute indexes for non in memory tables y n MAX_PERF_INDEX_ONLY n y/n, y: indexes are in memory but data is on disk y n NOT_INDEXED_DIMENSIONS ,-1, string, list (comma separated) of dimensions on which indexes won't be created y ,-1, MAX_PERF_FILTER_COL_TYPE -1 string, col_type used to reduce amount of data in memory (for sandbox environments) y -1 MAX_PERF_FILTER_FN -1 string, Lua boolean function used to reduce amount of data in memory (for sandbox environments) y -1 MAX_PERF_USE_COMPRESSION y y/n, y: SB will compress (put several ival in one integer) columns in memory n n PARETO_LIMIT 80 % float<=1, during max_perf ival are split between rare and often value, the x% more frequent ivals are the often ival n 100 % MAX_PERF_USE_IVAL_OFTEN_LIMIT 60000 integer, pareto ival are not computed during max_perf if the ival sparsity exceed this number y 60000 PIN_MEMORY n y/n, y: memory is pin hence virtual memory becomes resident y n MAX_PERF_COMPUTE_BIG_TABLES_JOIN y y/n, n: joined columns in big tables are not stored in memory y y MAX_COUNT_DISTINCT_THREAD_SIZE_GB 30 integer, SB will return an error if a countdistinct requires too much memory, the limit is per thread (see CPU_COUNT) y 30 QUERY_TABLE_ALLOWED n y/n, y: select from table allowed (non in memory volatile views will be created on top of each table) y n SKIP_REFRESH_FOR_NOT_IN_MEMORY_BIG_TABLES n y/n, y: (NOT_)IN_MEMORY_BIG_TABLES parameter applies also to view refresh (not only to MAX_PERF) y n HEADER y y/n, y: CSV files have a header line y y FLOAT_PRECISION 3 integer, number of digits kept when a number is inserted y 3 ALLOW_ORPHAN y y/n, y: SB will not refresh a view if its big tables has an orphan value (not found in dimension) y y 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 FILE_SEPARATOR , one character, CSV file separator y , INSERT_FORCE_VALUE.col_type_name forced_value string: forced value during insert of this col_type in a big table, unset means "not active" y forced_value SHOW_LINE 100000 integer, progression is log every x lines during inserts y 100000 PARTITION_LINE_COUNT 3000000000 integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partition y 3000000000 FILE_LOAD_WITHOUT_BUFFER y y/n, y: insert from file does not use buffer, hence it is faster but the full file is loaded in memory y y 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 SKIP_LINE_KO n y/n, y: bad lines in csv are skipped, n: bad lines generate error in insert y n CACHE n integer, SB listening port for transaction mode n y MAX_PERF_USE_WHERE_INDEX y y/n, y: index are created on dimensions for each view and put in memory y y USE_INDEX_LIMIT 2 integer (>0 and <100), if a where clause targets less than x% of the lines then index scan is done y 2 USE_INDEX_WITH_SORT_LIMIT 10 integer, if a where clause targets less than #lines/this_parameter of the lines then index scan is done in sequence context y 10 REPLACE_COUNTDISTINCT_BY_COUNTSEQUENCE n y/n, countdistinct are replaced by countsequence for sequence columns y n MAX_PERF_CHANGE_COUNT n y/n, y: count becomes countdistinct n y MAX_GBY_BOOSTER_LEN 200000 integer, a select with group by will be computed with optimum performance if the combined sparsity of the group by columns doesn't exceed this number, otherwise it will be slower y 200000 SEQUENCE_COLUMNS ,-1, string, list (comma separated) of sequence columns y ,-1, SEQUENCE_COLUMN_COMPANIONS ,-1, string, list (comma separated) of sequence columns companions y ,-1, ALLOW_WHERE_ERROR y y/n, y: where clause on bad column is ignored in select and does not return an error n n ALLOW_GROUP_BY_ERROR y y/n, y: group by clause on bad column is replaced by * in select resultset and does not return an error n n ALLOW_EXP_ERROR n y/n, y: sum/count/etc.. on bad column is replaced by 0 in select resultset and does not return an error y n ACTIVATE_NEW_DEV n y/n, used to test a new dev on demand y n INIT_FILE_PATH init.sb string, path of script executed on startup and after "refresh dirty view" n ./init.sb COMPUTED_COLUMNS ,-1, string, list (comma separated) of computed columns y ,-1, SO_FILE_NAME stormbase.so string, file under _SO_LINUX that contains the custom C code y stormbase.so DEBUG n y/n, y: triggers debug mode (dev only) y n CELL_BLOCK_INCREMENT_SIZE_B 1000 B float, CELL size, this parameter should not be modified if there is already data y 1000 B
--ACCEPT_DIFF_ENDset
set param_name='param_value' set param_name=default set LOG_VERBOSE='n';
reponse success
desc parameter verbose callback where(1,'LOG_VERBOSE');
param_name current_value comment is_default default_value LOG_VERBOSE n y/n, y: increase verbosity in stormbase.log y n
set LOG_VERBOSE='y';
reponse success
desc parameter verbose callback where(1,'LOG_VERBOSE');
param_name current_value comment is_default default_value LOG_VERBOSE y y/n, y: increase verbosity in stormbase.log n n
set LOG_VERBOSE=default;
reponse success
desc parameter verbose callback where(1,'LOG_VERBOSE');
param_name current_value comment is_default default_value LOG_VERBOSE n y/n, y: increase verbosity in stormbase.log y n
--error management continue_on_error(202,203,204);
reponse success
set LOG_VERBOSE='bad_value';
reponse error 203 (continue): invalid parameter value
set BAD_PARAMETER='value';
reponse error 202 (continue): invalid parameter name
set TCP_PORT_TRANSAC='2324';
reponse error 204 (continue): parameter can only be set in stormbase.conf
stop_on_error;
reponse success
-- ########################### -- RUNNING shutdown.sql shutdown;