< * | *** >
Computed columns 
- Basic types using loop function fn_computed_col
- SB parameter
- SB C interface
- priority between computed column and real column
- computed column basics
- computed columns with analytic function
- computed columns with param
- optimized contexts (type A)
- optimized contexts (type B)
- using other columns
- using other columns (better code)
- SB predefined contexts C API
- using context basics
- using custom context
- using indexes on computed columns
-- ########################### -- 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_computed_columns.sql --Computed columns
Basic types using loop function fn_computed_col
No coding an no parameter is necessary in 2 cases (most common cases):
1/ text 1 to 1 relationship, get the department of an item (type A)
2/ coefficient 1 to 1 relationship, get price from qty using item price (type B)
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
desc table sales;
table_name column_name column_type col_type_name sales item_id text t_item_id sales customer_id text t_customer_id sales sales_date text t_date sales sales_qty number sys#type#sales#sales_qty sales line_id text sys#type#sales#line_id sales packaging_id text t_item_id
select item_id,dept,sales_price from v_items cb sort(1,'asc');
item_id dept sales_price artA dept #1 1.500 artB dept #2 3.200 box1 packaging 0
--type A loop items(item_id,dept) function fn_computed_col('get_dept','A');
LOOP_RESULT DONE
refresh computed_column;
reponse success
--no parameter defaults to item_id (first column of loop) select line_id,item_id,packaging_id,get_dept() from v_sales cb sort(1,'asc');
line_id item_id packaging_id get_dept() ID01 artA box1 dept #1 ID02 artB # dept #2 ID03 artB # dept #2 ID04 artB box1 dept #2 ID05 # # # ID06 artA box1 dept #1 ID07 artA box1 dept #1
--using reverse index select line_id,item_id,packaging_id,get_dept() from v_sales and get_dept()='dept #1' cb sort(1,'asc');
line_id item_id packaging_id get_dept() ID01 artA box1 dept #1 ID06 artA box1 dept #1 ID07 artA box1 dept #1
select line_id,item_id,packaging_id,get_dept(item_id) from v_sales cb sort(1,'asc');
line_id item_id packaging_id get_dept(item_id) ID01 artA box1 dept #1 ID02 artB # dept #2 ID03 artB # dept #2 ID04 artB box1 dept #2 ID05 # # # ID06 artA box1 dept #1 ID07 artA box1 dept #1
select line_id,item_id,packaging_id,get_dept(packaging_id) from v_sales cb sort(1,'asc');
line_id item_id packaging_id get_dept(packaging_id) ID01 artA box1 packaging ID02 artB # # ID03 artB # # ID04 artB box1 packaging ID05 # # # ID06 artA box1 packaging ID07 artA box1 packaging
select item_id,sales_price from v_items;
item_id sales_price artA 1.500 artB 3.200 box1 0
--type B loop items(item_id,sales_price) function fn_computed_col('mult_by_sales_price','B','default=2.5 toto=1');
LOOP_RESULT DONE
loop items(item_id,sales_price) function fn_computed_col('divide_by_sales_price','B','1/X');
LOOP_RESULT DONE
desc context;
context_name is_drop_yn get_deptA n mult_by_sales_priceB n divide_by_sales_priceB n
refresh computed_column;
reponse success
--no second parameter defaults to item_id (first column of loop) select line_id,item_id,packaging_id,sales_qty,mult_by_sales_price(sales_qty) from v_sales cb sort(1,'asc');
line_id item_id packaging_id sales_qty mult_by_sales_price(sales_qty) ID01 artA box1 5 7.500 ID02 artB # 6 19.200 ID03 artB # 4 12.800 ID04 artB box1 7 22.400 ID05 # # 8 20 ID06 artA box1 5 7.500 ID07 artA box1 5 7.500
select line_id,item_id,packaging_id,sales_qty,mult_by_sales_price(sales_qty,item_id) from v_sales cb sort(1,'asc');
line_id item_id packaging_id sales_qty mult_by_sales_price(sales_qty,item_id) ID01 artA box1 5 7.500 ID02 artB # 6 19.200 ID03 artB # 4 12.800 ID04 artB box1 7 22.400 ID05 # # 8 20 ID06 artA box1 5 7.500 ID07 artA box1 5 7.500
select line_id,item_id,packaging_id,sales_qty,mult_by_sales_price(sales_qty,packaging_id) from v_sales cb sort(1,'asc');
line_id item_id packaging_id sales_qty mult_by_sales_price(sales_qty,packaging_id) ID01 artA box1 5 0 ID02 artB # 6 15 ID03 artB # 4 10 ID04 artB box1 7 0 ID05 # # 8 20 ID06 artA box1 5 0 ID07 artA box1 5 0
--division instead of multiplication select line_id,item_id,packaging_id,sales_qty,divide_by_sales_price(sales_qty) from v_sales cb sort(1,'asc');
line_id item_id packaging_id sales_qty divide_by_sales_price(sales_qty) ID01 artA box1 5 3.333 ID02 artB # 6 1.875 ID03 artB # 4 1.250 ID04 artB box1 7 2.188 ID05 # # 8 0 ID06 artA box1 5 3.333 ID07 artA box1 5 3.333
SB parameter
set COMPUTED_COLUMNS='comp_col1,comp_col2,...'SB C interface
SB needs 2 functions per computed column
1/ <comp_col_name>_before
2/ <comp_col_name>
See input/output of these functions in common.h
./_SO_CODE/common.h
typedef void (*t_fn_computed_column)(U_INT **ivals, float **num_vals, int ivals_len, void *context);
typedef struct computed_col_need {
int needed_col_count;
char **needed_col_names;
} COMPUTED_COL_NEED;
typedef COMPUTED_COL_NEED* (*t_fn_get_needed_columns)(char *param_fn_get_needed_columns, char **available_columns,
int available_columns_len, char **params, int params_len);
typedef struct computed_col_prereq {
//mandatory destination col_type (lower case)
char *destination_col_type_name;
//optional context that will be passed to functions
char *needed_context_name;
//columns needed (needed_col/fn_get_needed_columns)
// - simple case (needed_col!=NULL) : needed_col is used if fn_get_needed_columns is NULL
// - complex case (fn_get_needed_columns!=NULL)
// needed_col is computed this way: needed_col=fn_get_needed_columns(param_fn_get_needed_columns,available_columns,available_columns_len)
COMPUTED_COL_NEED *needed_col;
char *param_fn_get_needed_columns;
t_fn_get_needed_columns fn_get_needed_columns;
//put your error message here, NULL means no error
char *error;
//y/n (default n), if computed column overloads a real column
char priority_over_real_column_yn;
// in simple cases this will trigger an optimized treatment in SB
// defaults to ?
// A means select_contex is an TYPE_A*
// B means select_contex is an TYPE_B*
char select_context_type; // SB_LIB_VERSION >= 9
} COMPUTED_COL_PREREQ;
typedef COMPUTED_COL_PREREQ* (*t_fn_computed_column_before)(UTILITY *U);
//returns a "select_context"
typedef void* (*t_fn_computed_column_before_select)(UTILITY *U, char **params, int params_len, void *context);
typedef void (*t_fn_computed_column_after_select)(UTILITY *U, void *select_context);
typedef struct typeA {
U_INT *ival_needed_2_ival_dest;
U_INT len;
} TYPE_A;
typedef TYPE_A* (*t_fn_computed_column_before_select_typeA)(UTILITY *U, char **params, int params_len, void *context);
typedef void (*t_fn_computed_column_after_select_typeA)(UTILITY *U, TYPE_A *select_context);
typedef struct typeB {
float *ival_needed_2_coef;
U_INT len;
} TYPE_B;
typedef TYPE_B* (*t_fn_computed_column_before_select_typeB)(UTILITY *U, char **params, int params_len, void *context);
typedef void (*t_fn_computed_column_after_select_typeB)(UTILITY *U, TYPE_B *select_context);
COMPUTED_COL_PREREQ* sb_computed_col_prereq_factory();
//priority between computed column and real column
By default real column has priority over a computed column with same name
set COMPUTED_COLUMNS='customer_id';
reponse success
set CPU_COUNT=1;
reponse success
--real will be returned./_SO_CODE/Z_doc_comp_col_not_prio.c
#include "./common.h"
COMPUTED_COL_PREREQ* customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" );
return ret;
}
void customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
U_INT *pin_out = ivals[0];
U_INT idept = *pin_out;
*pin_out = idept == 10 ? NULL_IVAL : 10;
}
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
}
set SO_FILE_NAME='Z_doc_comp_col_not_prio.so';
reponse success
refresh dirty view;
reponse success
select sales#customer_id, customer_id from v_sales;
sales#customer_id customer_id C1 C1 C2 C2 C1 C1 C2 C2 C1 C1 C1 C1 C2 C2
--computed will be returned returned select dept,ival(dept) from v_sales group by dept;
dept ival(dept) dept #1 10 dept #2 11
./_SO_CODE/Z_doc_comp_col_prio.c
#include "./common.h"
COMPUTED_COL_PREREQ* customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" );
ret->priority_over_real_column_yn = 'y';
return ret;
}
void customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
U_INT *pin_out = ivals[0];
U_INT idept = *pin_out;
*pin_out = idept == 10 ? NULL_IVAL : 10;
}
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
}
set SO_FILE_NAME='Z_doc_comp_col_prio.so';
reponse success
refresh dirty view;
reponse success
select sales#customer_id, customer_id from v_sales;
sales#customer_id customer_id C1 # C2 NO_DATA C1 NO_DATA C2 NO_DATA C1 NO_DATA C1 # C2 #
computed column basics
New_customer_id is a computed text column
Its destination col_type is t_customer_id
We are going to return always the same value
So we don't need other columns to compute new_customer_id in this basic case
Please note that needed_col_count can't be zero, because on the in/out value at first position
Hence needed_col_names=["dept"]
set COMPUTED_COLUMNS='new_customer_id';
reponse success
set CPU_COUNT=1;
reponse success
set SO_FILE_NAME='Z_doc_comp_col_all_null.so';
reponse success
refresh dirty view;
reponse success
select customer_id, ival(customer_id) from v_sales group by customer_id callback sort(1,'asc') ;
customer_id ival(customer_id) C1 11 C2 12
select item_id, ival(item_id) from v_sales group by item_id callback sort(1,'asc') ;
item_id ival(item_id) artA 11 artB 12
--new_customer_id will always be null./_SO_CODE/Z_doc_comp_col_all_null.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1; //only dept only (not really needed)
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
*pin_out = NULL_IVAL; //update to null value
}
set CPU_COUNT=1;
reponse success
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 3 n n n inventory 2 2 n n n sales_#partition#_00001 6 3 n n n sales_#partition#_00002 6 1 n n n
desc computed_column;
comp_col_name error new_customer_id no error get_dept no error mult_by_sales_price no error divide_by_sales_price no error
select new_customer_id from v_sales group by new_customer_id;
new_customer_id #
--#SB no_index select line_id,new_customer_id,new_customer_id from v_sales and new_customer_id=null cb sort(1,'asc');
line_id new_customer_id new_customer_id ID01 # # ID02 # # ID03 # # ID04 # # ID05 # # ID06 # # ID07 # #
--#SB use_index select line_id,new_customer_id,new_customer_id from v_sales and new_customer_id=null cb sort(1,'asc');
line_id new_customer_id new_customer_id ID01 # # ID02 # # ID03 # # ID04 # # ID06 # # ID07 # #
--new_customer_id will be equal to the first customer_id created (C1 with ival=11) ./_SO_CODE/Z_doc_comp_col_first_value.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1; //only dept only (not really needed)
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
*pin_out = 11; //update to ival 11
}
set SO_FILE_NAME='Z_doc_comp_col_first_value.so';
reponse success
refresh dirty view;
reponse success
select line_id,new_customer_id from v_sales;
line_id new_customer_id ID01 C1 ID02 C1 ID03 C1 ID04 C1 ID05 C1 ID06 C1 ID07 C1
computed columns with analytic function
./_SO_CODE/Z_doc_comp_col_with_pareto_col.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1; //only dept only (not really needed)
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
needed_col->needed_col_names[0] = sb_clone("line_id" ); //pareto column
ret->destination_col_type_name = sb_clone("sys#type#sales#line_id" ); //pareto column
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
*pin_out = 11; //update to ival 11
}
set SO_FILE_NAME='Z_doc_comp_col_with_pareto_col.so';
reponse success
refresh dirty view;
reponse success
--the analytic functions sees new_customer_id (always same ival) select customer_id, new_customer_id from v_sales;
customer_id new_customer_id C1 ID02 C2 ID02 C1 ID02 C2 ID02 C1 ID02 C1 ID02 C2 ID02
select p(new_customer_id) from v_sales function fn_custom( '(void* c, U_INT thread_pos, U_INT iline, int key){printf("## fn_custom ival: %d\n",key);float *f=c; *f+=1; return 0.;}', 'fn_key', fn_num_before,fn_num_after ) ; fn_custom ival: 11 fn_custom ival: 11 fn_custom ival: 11 fn_custom ival: 11 fn_custom ival: 11 fn_custom ival: 11 fn_custom ival: 11
num 7
computed columns with param
--new_customer_id will be equal to customer_id pass in parameter (C1/ival=10 in param is A, C2/ival=11 in param is B) ./_SO_CODE/Z_doc_comp_col_with_param.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1; //only dept only (not really needed)
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
return ret;
}
void* new_customer_id_before_select(UTILITY *U, char **params, int params_len, void *context) {
U_INT *select_context = malloc(sizeof(U_INT));
if (strcmp(params[0], "A" ) == 0) {
*select_context = 10;
} else if (strcmp(params[0], "B" ) == 0) {
*select_context = 11;
} else {
*select_context = NULL_IVAL;
}
return select_context;
}
void new_customer_id_after_select(UTILITY *U, void *select_context) {
free(select_context);
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
U_INT ival_context = *(U_INT*) context;
*pin_out = ival_context;
}
set SO_FILE_NAME='Z_doc_comp_col_with_param.so';
reponse success
refresh dirty view;
reponse success
select line_id, new_customer_id('A'), new_customer_id('B'), new_customer_id('C') from v_sales;
line_id new_customer_id('A') new_customer_id('B') new_customer_id('C') ID01 NO_DATA C1 # ID02 NO_DATA C1 # ID03 NO_DATA C1 # ID04 NO_DATA C1 # ID05 NO_DATA C1 # ID06 NO_DATA C1 # ID07 NO_DATA C1 #
select new_customer_id('A') as col1, new_customer_id('B') as col2, count(*) from v_sales group by new_customer_id('A'), new_customer_id('B');
col1 col2 count(*) NO_DATA C1 7
select count(*) from v_sales and new_customer_id('A')='C1';
count(*)
select new_customer_id('A'),list(new_customer_id('B')) from v_sales group by new_customer_id('A');
new_customer_id('A') list(new_customer_id('B')) NO_DATA C1
select new_customer_id('A'),count(*) from v_sales group by new_customer_id('A');
new_customer_id('A') count(*) NO_DATA 7
optimized contexts (type A)
select_context_type A
--new_customer_id will be equal to customer_id pass in parameter (C1/ival=10 in param is A, C2/ival=11 in param is B) ./_SO_CODE/Z_doc_comp_col_with_param_typeA.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 1;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("item_id" );
needed_col->needed_col_names[0] = sb_clone("line_id" ); // ptaudou
ret->select_context_type = 'A';
return ret;
}
TYPE_A* new_customer_id_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) {
TYPE_A *select_context = malloc(sizeof(TYPE_A));
select_context->len = U->fn_get_col_type_ival_count("t_item_id" );
select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#line_id" ); // ptaudou
select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT));
U_INT ival;
if (strcmp(params[0], "A" ) == 0) {
ival = 10;
} else if (strcmp(params[0], "B" ) == 0) {
ival = 11;
} else {
ival = NULL_IVAL;
}
for (int i = 0; i < select_context-> len; i++) {
if (i == 11) {
select_context->ival_needed_2_ival_dest[i] = ival;
} else {
select_context->ival_needed_2_ival_dest[i] = NULL_IVAL;
}
}
return select_context;
}
void new_customer_id_after_select_typeA(UTILITY *U, U_INT *select_context) {
free(select_context);
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
printf("## CALL new_customer_id pin_out %p, should not happen in type A\n" , pin_out);
exit(-1);
*pin_out = NULL_IVAL;
}
set SO_FILE_NAME='Z_doc_comp_col_with_param_typeA.so';
reponse success
refresh dirty view;
reponse success
set cache='n';
reponse success
select customer_id from v_sales;
customer_id C1 C2 C1 C2 C1 C1 C2
select customer_id, customer_id from v_sales where customer_id='C1';
customer_id customer_id C1 C1 C1 C1 C1 C1 C1 C1
select item_id, customer_id, new_customer_id('A') from v_sales;
item_id customer_id new_customer_id('A') artA C1 # artB C2 NO_DATA artB C1 # artB C2 # # C1 # artA C1 # artA C2 #
select new_customer_id('A'), new_customer_id('A') from v_sales;
new_customer_id('A') new_customer_id('A') # # NO_DATA NO_DATA # # # # # # # # # #
select line_id,uniquesoft(customer_id) from v_sales group by line_id cb sort(1,'asc');
line_id uniquesoft(customer_id) ID01 C1 ID02 C2 ID03 C1 ID04 C2 ID05 C1 ID06 C1 ID07 C2
select line_id,uniquesoft(new_customer_id('A')) from v_sales group by line_id cb sort(1,'asc');
line_id uniquesoft(new_customer_id('A')) ID02 NO_DATA
select item_id, unique(new_customer_id('A')), max(new_customer_id('A')),count(*) from v_sales group by item_id;
item_id unique(new_customer_id('A')) max(new_customer_id('A')) count(*) artA # # 3 artB # NO_DATA 3 # # # 1
--#SB use_index explain select new_customer_id('A'), new_customer_id('A') from v_sales and new_customer_id('A')='NO_DATA';
logs
--#SB no_index select new_customer_id('A'), new_customer_id('A') from v_sales and new_customer_id('A')='NO_DATA';
new_customer_id('A') new_customer_id('A') NO_DATA NO_DATA
select new_customer_id('A'), new_customer_id('B'), new_customer_id('C') from v_sales;
new_customer_id('A') new_customer_id('B') new_customer_id('C') # # # NO_DATA C1 # # # # # # # # # # # # # # # #
select line_id, new_customer_id('A'), new_customer_id('B'), new_customer_id('C') from v_sales;
line_id new_customer_id('A') new_customer_id('B') new_customer_id('C') ID01 # # # ID02 NO_DATA C1 # ID03 # # # ID04 # # # ID05 # # # ID06 # # # ID07 # # #
select new_customer_id('A') as col1, new_customer_id('B') as col2, count(*) from v_sales group by new_customer_id('A'), new_customer_id('B');
col1 col2 count(*) # # 6 NO_DATA C1 1
select count(*) from v_sales and new_customer_id('A')='C1';
count(*)
select new_customer_id('A'),list(new_customer_id('B')) from v_sales group by new_customer_id('A');
new_customer_id('A') list(new_customer_id('B')) # NO_DATA C1
select new_customer_id('A'),count(*) from v_sales group by new_customer_id('A');
new_customer_id('A') count(*) # 6 NO_DATA 1
optimized contexts (type B)
select_context_type B
set COMPUTED_COLUMNS='new_qty,new_qty2';
reponse success
refresh dirty view;
reponse success
--new_customer_id will be equal to customer_id pass in parameter (C1/ival=10 in param is A, C2/ival=11 in param is B) ./_SO_CODE/Z_doc_comp_col_typeB.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_qty_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("sys#type#sales#sales_qty" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 2;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("sales_qty" );
needed_col->needed_col_names[1] = sb_clone("item_id" );
ret->select_context_type = 'B';
return ret;
}
TYPE_B* new_qty_before_select_typeB(UTILITY *U, char **params, int params_len, void *context) {
TYPE_B *select_context = malloc(sizeof(TYPE_A));
select_context->len = U->fn_get_col_type_ival_count("t_item_id" );
select_context->ival_needed_2_coef = malloc(select_context->len * sizeof(float));
for (int i = 0; i < select_context-> len; i++) {
select_context->ival_needed_2_coef[i] = i >= 10 ? 2 : -2;
}
return select_context;
}
void new_qty(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
}
COMPUTED_COL_PREREQ* new_qty2_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("sys#type#sales#sales_qty" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 2;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("sales_qty" );
needed_col->needed_col_names[1] = sb_clone("line_id" );
ret->select_context_type = 'B';
return ret;
}
TYPE_B* new_qty2_before_select_typeB(UTILITY *U, char **params, int params_len, void *context) {
TYPE_B *select_context = malloc(sizeof(TYPE_A));
select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#line_id" );
select_context->ival_needed_2_coef = malloc(select_context->len * sizeof(float));
for (int i = 0; i < select_context-> len; i++) {
select_context->ival_needed_2_coef[i] = i >= 10 ? 2 : -2;
}
return select_context;
}
void new_qty2(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
}
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
}
set SO_FILE_NAME='Z_doc_comp_col_typeB.so';
reponse success
refresh dirty view;
reponse success
--using dimension ival (non pareto ival) select item_id, ival(item_id), sum(sales_qty), sum(new_qty) from v_sales group by item_id;
item_id ival(item_id) sum(sales_qty) sum(new_qty) artA 11 15 30 artB 12 17 34 # # 8 -16
--using dimension ival (non pareto ival) select line_id, ival(line_id), sum(sales_qty), sum(new_qty2) from v_sales group by line_id;
line_id ival(line_id) sum(sales_qty) sum(new_qty2) ID01 10 5 10 ID02 11 6 12 ID03 12 4 8 ID04 13 7 14 ID05 14 8 16 ID06 15 5 10 ID07 16 5 10
set COMPUTED_COLUMNS='new_customer_id';
reponse success
using other columns
New_customer_id will be equal to the first customer_id will have 2 values:
1/ current customer_id if item_id is artA (ival=10)
2/ null otherwise
./_SO_CODE/Z_doc_comp_col_two_values.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 3; //customer_id and item_id are needed, we keep department for the moment
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("dept" ); //still not really needed
needed_col->needed_col_names[1] = sb_clone("customer_id" );
needed_col->needed_col_names[2] = sb_clone("item_id" );
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
U_INT *pival_customer_id = ivals[1]; //customer_id is passed after dept
U_INT *pival_item_id = ivals[2]; //item_id is passed after customer_id
if (*pival_item_id == 10) {
*pin_out = *pival_customer_id; //update to customer_id
} else {
*pin_out = NULL_IVAL; //update to null value
}
}
set SO_FILE_NAME='Z_doc_comp_col_two_values.so';
reponse success
refresh dirty view;
reponse success
select line_id,customer_id,item_id from v_sales;
line_id customer_id item_id ID01 C1 artA ID02 C2 artB ID03 C1 artB ID04 C2 artB ID05 C1 # ID06 C1 artA ID07 C2 artA
select line_id,new_customer_id from v_sales;
line_id new_customer_id ID01 # ID02 # ID03 # ID04 # ID05 # ID06 # ID07 #
select line_id,new_customer_id,customer_id from v_sales and customer_id='C2';
line_id new_customer_id customer_id ID02 # C2 ID04 # C2 ID07 # C2
select line_id,new_customer_id from v_sales and new_customer_id='C2';
line_id new_customer_id
using other columns (better code)
Dept was here for clarity (in/out value), let's remove it, and use customer_id instead
And we won't need to update the new in/out in case 1/ because it is already good
./_SO_CODE/Z_doc_comp_col_two_values_better.c
#include "./common.h"
COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
ret->destination_col_type_name = sb_clone("t_customer_id" );
COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
ret->needed_col = needed_col;
needed_col->needed_col_count = 2;
needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
needed_col->needed_col_names[0] = sb_clone("customer_id" );
needed_col->needed_col_names[1] = sb_clone("item_id" );
return ret;
}
void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
//position 0 is "in/out"
U_INT *pin_out = ivals[0];
U_INT *pival_customer_id = ivals[0];
U_INT *pival_item_id = ivals[1];
if (*pival_item_id == 10) {
//already good
} else {
*pival_customer_id = NULL_IVAL; //update to null value
}
}
set SO_FILE_NAME='Z_doc_comp_col_two_values_better.so';
reponse success
refresh dirty view;
reponse success
--#SB log_verbose select line_id, new_customer_id from v_sales ;
line_id new_customer_id ID01 # ID02 # ID03 # ID04 # ID05 # ID06 # ID07 #
select line_id from v_sales and new_customer_id='C1' ;
line_id
select line_id from v_sales and new_customer_id='C2' ;
line_id
select line_id, maxstr(new_customer_id) from v_sales group by line_id ;
line_id maxstr(new_customer_id)
select item_id, list(new_customer_id) from v_sales group by item_id ;
item_id list(new_customer_id) artA artB #
select item_id, list(new_customer_id), list(customer_id) from v_sales group by item_id ;
item_id list(new_customer_id) list(customer_id) artA C1,C2 artB C1,C2 # C1
SB predefined contexts C API
A context is a C custom objects stored in memory be SB ant that can be used by other part of your custom C code
Some predefined contexts for most common operations are provided (see common.h)
./_SO_CODE/common.h
typedef struct idx1 {
U_INT len;
U_INT *ival1_to_ival2;
char *origin_col_type_name; //SB_LIB_VERSION 12
char *origin_col_name; //SB_LIB_VERSION 12
char *destination_col_type_name;
COMPUTED_COL_NEED *needed_col;
} IDX1;
typedef struct idx1_num {
U_INT len;
float *ival1_to_num;
char *origin_col_type_name;
char *origin_col_name;
char *destination_col_type_name;
char ratio_yn;
} IDX1_NUM;
typedef struct idx2 {
U_INT **ival1_to_ival2_to_ival3;
U_INT ival1_len;
char *destination_col_type_name;
COMPUTED_COL_NEED *needed_col;
} IDX2;
#define YYYYMMDD unsigned int
typedef struct line_period {
U_INT ival;
YYYYMMDD d1;
YYYYMMDD d2;
} SB_PERIOD;
typedef struct line_periods {
SB_PERIOD *lines;
int count;
} SB_PERIODS;
typedef struct idx2_period {
U_INT ival1_len;
U_INT ival2_len;
SB_PERIODS ***ival1_to_ival2_to_ival3_periods;
char *destination_col_type_name;
COMPUTED_COL_NEED *needed_col;
YYYYMMDD *ival_to_d;
int ival_to_d_len;
char *d_col_type_name;
} IDX2_PERIOD;
COMPUTED_COL_PREREQ* sb_idx1_before(UTILITY *U, char *col1, char *col2);
COMPUTED_COL_PREREQ* sb_idx2_before(UTILITY *U, char *col1, char *col2, char *col3);
COMPUTED_COL_PREREQ* sb_idx2_period_before(UTILITY *U, char *col1, char *col2, char *col3, char *col4);
void sb_idx1(U_INT **ivals, float **num_vals, int ivals_len, void *context);
void sb_idx2(U_INT **ivals, float **num_vals, int ivals_len, void *context);
void sb_idx2_period(U_INT **ivals, float **num_vals, int ivals_len, void *context);
//using context basics
Sometimes your computed column will need other information because the needed columns in not enough
Context should be used in that case
set COMPUTED_COLUMNS='last_fidelity_card,last_customer_info,valid_customer_info,best_customer_info';
reponse success
select item_id, customer_id, sales_date, line_id from v_sales callback sort(4,'asc');
item_id customer_id sales_date line_id artA C1 20191231 ID01 artB C2 20200102 ID02 artB C1 20191231 ID03 artB C2 20200102 ID04 # C1 20200102 ID05 artA C1 20191231 ID06 artA C2 20191231 ID07
select * from v_fidelity_cards;
customer_id card_label valid_from valid_until C1 SILVER 20191201 20191231 C1 GOLD 20201201 20201231
select * from v_item_customer_infos;
customer_id item_id info valid_from valid_until C1 artA FREQUENT BUYER of artA in 2019 20190101 20191231 C1 artB FREQUENT BUYER of artB in 2020 20200101 20201231 C2 artB FREQUENT BUYER of artB in 2020 20200101 20201231
--1 column join with fn_build_idx1 --last fidelity card per customer_id./_SO_CODE/Z_doc_comp_col2_last_fidelity_card.c
#include "./common.h"
COMPUTED_COL_PREREQ* last_fidelity_card_before(UTILITY *U) {
return sb_idx1_before(U, "customer_id" , "card_label" );
}
void last_fidelity_card(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
sb_idx1(ivals, num_vals, ivals_len, context);
}
void fn_free(void *c){
free(c);
}
set SO_FILE_NAME='Z_doc_comp_col2_last_fidelity_card.so';
reponse success
loop fidelity_cards(customer_id,card_label) function fn_build_idx1;
LOOP_RESULT DONE
desc context;
context_name is_drop_yn get_deptA n mult_by_sales_priceB n divide_by_sales_priceB n IDX1#customer_id->card_label n
refresh computed_column;
reponse success
desc computed_column;
comp_col_name error last_fidelity_card no error last_customer_info function not found: last_customer_info valid_customer_info function not found: valid_customer_info best_customer_info function not found: best_customer_info get_dept no error mult_by_sales_price no error divide_by_sales_price no error
select item_id, customer_id, sales_date, line_id, last_fidelity_card from v_sales callback sort(4,'asc');
item_id customer_id sales_date line_id last_fidelity_card artA C1 20191231 ID01 GOLD artB C2 20200102 ID02 # artB C1 20191231 ID03 GOLD artB C2 20200102 ID04 # # C1 20200102 ID05 GOLD artA C1 20191231 ID06 GOLD artA C2 20191231 ID07 #
select customer_id, list(last_fidelity_card) from v_sales group by customer_id callback sort(1,'asc');
customer_id list(last_fidelity_card) C1 GOLD C2
select sales.* from v_sales where last_fidelity_card like 'GO';
item_id customer_id sales_date sales_qty line_id packaging_id artA C1 20191231 5 ID01 box1 artB C1 20191231 4 ID03 # # C1 20200102 8 ID05 # artA C1 20191231 5 ID06 box1
select count(*), list(customer_id) from v_sales and last_fidelity_card='GOLD';
count(*) list(customer_id) 4 C1
--2 columns join with fn_build_idx2 --last customer_info per item_id/customer_id./_SO_CODE/Z_doc_comp_col2_last_customer_info.c
#include "./common.h"
COMPUTED_COL_PREREQ* last_customer_info_before(UTILITY *U) {
return sb_idx2_before(U, "customer_id" , "item_id" , "info" );
}
void last_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
sb_idx2(ivals, num_vals, ivals_len, context);
}
void fn_free(void *c){
free(c);
}
set SO_FILE_NAME='Z_doc_comp_col2_last_customer_info.so';
reponse success
loop item_customer_infos(customer_id,item_id,info) function fn_build_idx2;
LOOP_RESULT DONE
desc context;
context_name is_drop_yn get_deptA n mult_by_sales_priceB n divide_by_sales_priceB n IDX1#customer_id->card_label n IDX2#customer_id->item_id->info n
refresh computed_column;
reponse success
desc computed_column;
comp_col_name error last_fidelity_card function not found: last_fidelity_card last_customer_info no error valid_customer_info function not found: valid_customer_info best_customer_info function not found: best_customer_info get_dept no error mult_by_sales_price no error divide_by_sales_price no error
select item_id, customer_id, sales_date, line_id, last_customer_info from v_sales callback sort(4,'asc');
item_id customer_id sales_date line_id last_customer_info artA C1 20191231 ID01 FREQUENT BUYER of artA in 2019 artB C2 20200102 ID02 FREQUENT BUYER of artB in 2020 artB C1 20191231 ID03 FREQUENT BUYER of artB in 2020 artB C2 20200102 ID04 FREQUENT BUYER of artB in 2020 # C1 20200102 ID05 # artA C1 20191231 ID06 FREQUENT BUYER of artA in 2019 artA C2 20191231 ID07 #
--2 columns join + period check with fn_build_idx2_period --valid customer_info per item_id/customer_id, valid at the sales_date./_SO_CODE/Z_doc_comp_col2_valid_customer_info.c
#include "./common.h"
COMPUTED_COL_PREREQ* valid_customer_info_before(UTILITY *U) {
return sb_idx2_period_before(U, "customer_id" , "item_id" , "sales_date" , "info" );
}
void valid_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
sb_idx2_period(ivals, num_vals, ivals_len, context);
}
void fn_free(void *c){
free(c);
}
set SO_FILE_NAME='Z_doc_comp_col2_valid_customer_info.so';
reponse success
loop item_customer_infos(customer_id,item_id,info,valid_from,valid_until) function fn_build_idx2_period('sales_date');
LOOP_RESULT DONE
desc context;
context_name is_drop_yn get_deptA n mult_by_sales_priceB n divide_by_sales_priceB n IDX1#customer_id->card_label n IDX2#customer_id->item_id->info n IDX2_PERIOD#customer_id->item_id->sales_date->info n
refresh computed_column;
reponse success
desc computed_column;
comp_col_name error last_fidelity_card function not found: last_fidelity_card last_customer_info function not found: last_customer_info valid_customer_info no error best_customer_info function not found: best_customer_info get_dept no error mult_by_sales_price no error divide_by_sales_price no error
select item_id, customer_id, sales_date, line_id, valid_customer_info from v_sales callback sort(4,'asc');
item_id customer_id sales_date line_id valid_customer_info artA C1 20191231 ID01 FREQUENT BUYER of artA in 2019 artB C2 20200102 ID02 FREQUENT BUYER of artB in 2020 artB C1 20191231 ID03 # artB C2 20200102 ID04 FREQUENT BUYER of artB in 2020 # C1 20200102 ID05 # artA C1 20191231 ID06 FREQUENT BUYER of artA in 2019 artA C2 20191231 ID07 #
using custom context
Sometimes predefined context is not enough, or you needed several custom contexts
In such cas you should define your own context
--best_customer_info valid_customer_info or last_customer_info or last_fidelity_card./_SO_CODE/Z_doc_comp_col2_best_customer_info.c
#include "./common.h"
typedef struct my_context {
IDX1 *idx1;
IDX2 *idx2;
IDX2_PERIOD *idx2_p;
} MY_CONTEXT;
void fn_free(void *c){
free(c);
}
int fn_build_my_context_before(SB_VALS* read, SB_VALS* new) {
MY_CONTEXT *c = malloc(sizeof(MY_CONTEXT));
c->idx1 = read->U->fn_get_context("IDX1#customer_id->card_label" );
c->idx2 = read->U->fn_get_context("IDX2#customer_id->item_id->info" );
c->idx2_p = read->U->fn_get_context("IDX2_PERIOD#customer_id->item_id->sales_date->info" );
if (c->idx1 == NULL) {
read->U->fn_log("fn_build_my_context_before: context IDX1#customer_id->card_label not found" );
return KO;
}
if (c->idx2 == NULL) {
read->U->fn_log("fn_build_my_context_before: context IDX2#customer_id->item_id->info not found" );
return KO;
}
if (c->idx2_p == NULL) {
read->U->fn_log(
"fn_build_my_context_before: context IDX2_PERIOD#customer_id->item_id->sales_date->info not found" );
return KO;
}
read->context = c;
read->context_name = sb_clone("my_context" );
read->fn_free_context_name = sb_clone("fn_free" );
return OK;
}
int fn_build_my_context(SB_VALS* read, SB_VALS* new) {
return NOT_MODIFIED;
}
COMPUTED_COL_PREREQ* best_customer_info_before(UTILITY *U) {
COMPUTED_COL_PREREQ*ret = sb_computed_col_prereq_factory();
ret->needed_context_name = sb_clone("my_context" );
MY_CONTEXT* idx = U->fn_get_context(ret->needed_context_name);
if (idx == NULL) {
ret->error = sb_concat3("context " , ret->needed_context_name, " not found" );
return ret;
}
ret->destination_col_type_name = sb_clone(idx->idx2_p->destination_col_type_name);
ret->needed_col = sb_clone_need_col(idx->idx2_p->needed_col);
return ret;
}
void best_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
MY_CONTEXT *c = context;
U_INT customer_id = *ivals[0];
sb_idx2_period(ivals, num_vals, ivals_len, c->idx2_p);
if (*ivals[0] == NULL_IVAL) {
*ivals[0] = customer_id;
sb_idx2(ivals, num_vals, ivals_len, c->idx2);
if (*ivals[0] == NULL_IVAL) {
*ivals[0] = customer_id;
sb_idx1(ivals, num_vals, ivals_len, c->idx1);
}
}
}
set SO_FILE_NAME='Z_doc_comp_col2_best_customer_info.so';
reponse success
--create an empty table to trigger fn_build_my_context create table empty_table (txt text);
reponse success
loop empty_table(txt) function fn_build_my_context;
LOOP_RESULT DONE
desc context;
context_name is_drop_yn get_deptA n mult_by_sales_priceB n divide_by_sales_priceB n IDX1#customer_id->card_label n IDX2#customer_id->item_id->info n IDX2_PERIOD#customer_id->item_id->sales_date->info n my_context n
refresh computed_column;
reponse success
desc computed_column;
comp_col_name error last_fidelity_card function not found: last_fidelity_card last_customer_info function not found: last_customer_info valid_customer_info function not found: valid_customer_info best_customer_info no error get_dept no error mult_by_sales_price no error divide_by_sales_price no error
select item_id, customer_id, sales_date, line_id, best_customer_info from v_sales callback sort(4,'asc');
item_id customer_id sales_date line_id best_customer_info artA C1 20191231 ID01 FREQUENT BUYER of artA in 2019 artB C2 20200102 ID02 FREQUENT BUYER of artB in 2020 artB C1 20191231 ID03 FREQUENT BUYER of artB in 2020 artB C2 20200102 ID04 FREQUENT BUYER of artB in 2020 # C1 20200102 ID05 GOLD artA C1 20191231 ID06 FREQUENT BUYER of artA in 2019 artA C2 20191231 ID07 #
--all computed columns./_SO_CODE/Z_doc_comp_col2_all.c
#include "./common.h"
COMPUTED_COL_PREREQ* last_fidelity_card_before(UTILITY *U) {
return sb_idx1_before(U, "customer_id" , "card_label" );
}
void last_fidelity_card(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
sb_idx1(ivals, num_vals, ivals_len, context);
}
COMPUTED_COL_PREREQ* last_customer_info_before(UTILITY *U) {
return sb_idx2_before(U, "customer_id" , "item_id" , "info" );
}
void last_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
sb_idx2(ivals, num_vals, ivals_len, context);
}
COMPUTED_COL_PREREQ* valid_customer_info_before(UTILITY *U) {
return sb_idx2_period_before(U, "customer_id" , "item_id" , "sales_date" , "info" );
}
void valid_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
sb_idx2_period(ivals, num_vals, ivals_len, context);
}
typedef struct my_context {
IDX1 *idx1;
IDX2 *idx2;
IDX2_PERIOD *idx2_p;
} MY_CONTEXT;
void fn_free(void *c){
free(c);
}
int fn_build_my_context_before(SB_VALS* read, SB_VALS* new) {
MY_CONTEXT *c = malloc(sizeof(MY_CONTEXT));
c->idx1 = read->U->fn_get_context("IDX1#customer_id->card_label" );
c->idx2 = read->U->fn_get_context("IDX2#customer_id->item_id->info" );
c->idx2_p = read->U->fn_get_context("IDX2_PERIOD#customer_id->item_id->sales_date->info" );
if (c->idx1 == NULL) {
read->U->fn_log("fn_build_my_context_before: context IDX1#customer_id->card_label not found" );
return KO;
}
if (c->idx2 == NULL) {
read->U->fn_log("fn_build_my_context_before: context IDX2#customer_id->item_id->info not found" );
return KO;
}
if (c->idx2_p == NULL) {
read->U->fn_log(
"fn_build_my_context_before: context IDX2_PERIOD#customer_id->item_id->sales_date->info not found" );
return KO;
}
read->context = c;
read->context_name = sb_clone("my_context" );
read->fn_free_context_name = sb_clone("fn_free" );
return OK;
}
int fn_build_my_context(SB_VALS* read, SB_VALS* new) {
return NOT_MODIFIED;
}
COMPUTED_COL_PREREQ* best_customer_info_before(UTILITY *U) {
COMPUTED_COL_PREREQ*ret = sb_computed_col_prereq_factory();
ret->needed_context_name = sb_clone("my_context" );
MY_CONTEXT* idx = U->fn_get_context(ret->needed_context_name);
if (idx == NULL) {
ret->error = sb_concat3("context " , ret->needed_context_name, " not found" );
return ret;
}
ret->destination_col_type_name = sb_clone(idx->idx2_p->destination_col_type_name);
ret->needed_col = sb_clone_need_col(idx->idx2_p->needed_col);
return ret;
}
void best_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
MY_CONTEXT *c = context;
U_INT customer_id = *ivals[0];
sb_idx2_period(ivals, num_vals, ivals_len, c->idx2_p);
if (*ivals[0] == NULL_IVAL) {
*ivals[0] = customer_id;
sb_idx2(ivals, num_vals, ivals_len, c->idx2);
if (*ivals[0] == NULL_IVAL) {
*ivals[0] = customer_id;
sb_idx1(ivals, num_vals, ivals_len, c->idx1);
}
}
}
set SO_FILE_NAME='Z_doc_comp_col2_all.so';
reponse success
refresh computed_column;
reponse success
desc computed_column;
comp_col_name error last_fidelity_card no error last_customer_info no error valid_customer_info no error best_customer_info no error get_dept no error mult_by_sales_price no error divide_by_sales_price no error
select item_id, customer_id, sales_date, line_id, last_fidelity_card, last_customer_info, valid_customer_info, best_customer_info from v_sales callback sort(4,'asc');
item_id customer_id sales_date line_id last_fidelity_card last_customer_info valid_customer_info best_customer_info artA C1 20191231 ID01 GOLD FREQUENT BUYER of artA in 2019 FREQUENT BUYER of artA in 2019 FREQUENT BUYER of artA in 2019 artB C2 20200102 ID02 # FREQUENT BUYER of artB in 2020 FREQUENT BUYER of artB in 2020 FREQUENT BUYER of artB in 2020 artB C1 20191231 ID03 GOLD FREQUENT BUYER of artB in 2020 # FREQUENT BUYER of artB in 2020 artB C2 20200102 ID04 # FREQUENT BUYER of artB in 2020 FREQUENT BUYER of artB in 2020 FREQUENT BUYER of artB in 2020 # C1 20200102 ID05 GOLD # # GOLD artA C1 20191231 ID06 GOLD FREQUENT BUYER of artA in 2019 FREQUENT BUYER of artA in 2019 FREQUENT BUYER of artA in 2019 artA C2 20191231 ID07 # # # #
using indexes on computed columns
Index can be used if there is only one needed column and that this column has an index (is part of a dimension)
--index on last_fidelity_card can be used but not on last_customer_info --#SB use_index select line_id from v_sales and last_fidelity_card='GOLD' and last_customer_info='FREQUENT BUYER of artA in 2019' ;
line_id ID01 ID06
-- refresh computed_column; -- refresh cache; -- ########################### -- RUNNING shutdown.sql shutdown;