-- ########################### -- 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_commands.sql --Commands
Commands are non SQL instructions
defragmentation
The CELL_* files in internal storage needs defragmentation, defrag is done in offline mode during the save process
save;
reponse success
--ACCEPT_DIFF_START --defragmentation did not occur because of the memory_max_gb parameter system 'grep CELL_STORE ../STORAGE/INT/_DESC';
reponse CELL_STORE:8:7560
system 'ls -l ../STORAGE/INT/CELL*';
reponse -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0000.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0001.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0002.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0003.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0004.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0005.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0006.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0007.cell
--force fragmentation set memory_max_gb=0;
reponse success
save;
reponse success
--less CELL are needed but the files are still here system 'grep CELL_STORE ../STORAGE/INT/_DESC';
reponse CELL_STORE:4:3608
system 'ls -l ../STORAGE/INT/CELL*';
reponse -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0000.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0001.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0002.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0003.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0004.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0005.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0006.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0007.cell
--next start/bounce will remove them bounce; system 'ls -ltr ../STORAGE/INT/CELL*';
reponse -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0000.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0001.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0002.cell -rw-r--r-- 1 philippe staff 1000 16 jan 09:59 ../STORAGE/INT/CELL_BLOCK_0003.cell
--ACCEPT_DIFF_END select sales.* from v_sales;
item_id customer_id sales_date sales_qty line_id packaging_id artA C1 20191231 5 ID01 box1 artB C2 20200102 6 ID02 # artB C1 20191231 4 ID03 # artB C2 20200102 7 ID04 box1 # C1 20200102 8 ID05 # artA C1 20191231 5 ID06 box1 artA C2 20191231 5 ID07 box1
--continue_on_error/stop_on_error
By default a script execution will stop on first error
Sometimes this is not what you want (common case is multiple execution of a create statement)
To continue on all errors
continue_on_errorTo stop on all errors
stop_on_errorTo continue on some errors
continue_on_error(err_code1, err_code2, ...) create col_type foo as text;
reponse success
continue_on_error(11,1);
reponse success
create col_type foo as text;
reponse error 1 (continue): object exists already
c__reate col_type foo as text;
reponse error 11 (continue): non valid SQL statement
--basics shells
start.sh stop.sh bounce.sh show_log.shexport.sh
Exports data_model and/or data to a directory
./export.sh <export_dir> <data_model y/n> <data y/n> <nb_thread> <dry_run y/n>
import.sh
Import data_model and/or data from an export directory
./import.sh <export_dir> <data_model y/n> <data y/n> <dry_run y/n>
--sql.sh
From 02_sql
./sql.sh the_user/the_password@sb_host:sb_port [--file:<file.sql> --i]
i means interactive (the file is executed but you must press enter after each execution)From 01_database (connection to local SB)
./sql.sh [--file:<file.sql> --i]
Note about logging: sql.sh will create a log file (sql.log), this file is bit different from stdout, it is used to generate this documentation !! --y/n (sql.sh)
n tells sql.sh to "read but not run" y tells sql.sh to restart normally --display (sql.sh)
something I want to see in sql.log without being executed it can be on several lines but cannot contain a semi column --bounce
same as bounce.sh without killing the process bounce; --shutdown
same as stop.sh without killing the process shutdown;