-- ###########################
-- 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
--
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
SB_LICENSE_PATH parameter
ARRAY_BLOCK_SIZE_BIG parameter
ARRAY_BLOCK_SIZE_SMALL parameter
PROD_SERVER parameter
MAX_PERF_USE_COMPRESSION parameter
PARETO_LIMIT parameter
PARTITION_LINE_COUNT 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
SB_LICENSE_PATH ./fo/foo SB's license key path n ./stormbase.license
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 %
PARTITION_LINE_COUNT 3 integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partition n 3000000000
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
SB_LICENSE_PATH ./fo/foo SB's license key path n ./stormbase.license
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 3 integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partition n 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_END
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
;