-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathmedmysql.c
1891 lines (1620 loc) · 64.9 KB
/
medmysql.c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#include <stdbool.h>
#include <mysql.h>
#ifdef MARIADB
#include <mariadb/errmsg.h>
#include <mariadb/mysqld_error.h>
#else
#include <mysql/errmsg.h>
#include <mysql/mysqld_error.h>
#endif
#include <assert.h>
#include "medmysql.h"
#include "config.h"
#include "records.h"
#define _TEST_SIMULATE_SQL_ERRORS 0
#define MED_CACHE_DURATION 30
#define MED_SQL_BUF_LEN(fixed_string_len, escaped_string_len) \
(fixed_string_len + 7 + 2 + (escaped_string_len) * 2 + 1) // _latin1'STRING'\0
#define MED_CALLID_QUERY "select a.callid from acc a" \
" where a.method = 'INVITE' " \
" group by a.callid limit 0,200000"
#define MED_FETCH_QUERY "(select distinct sip_code, sip_reason, method, callid, time, time_hires, " \
"src_leg, dst_leg, branch_id, id " \
"from acc where method = 'INVITE' and callid = '%s' order by time_hires asc) " \
"union all " \
"(select distinct sip_code, sip_reason, method, callid, time, time_hires, " \
"src_leg, dst_leg, branch_id, id " \
"from acc where method = 'BYE' and callid in ('%s', '%s"PBXSUFFIX"') " \
"order by length(callid) asc, time_hires asc) " \
"union all " \
"(select distinct sip_code, sip_reason, method, callid, time, time_hires, " \
"src_leg, dst_leg, branch_id, id " \
"from acc where method = 'BYE' and callid in ('%s', '%s"XFERSUFFIX"') " \
"order by length(callid) asc, time_hires asc) " \
"union all " \
"(select distinct sip_code, sip_reason, method, callid, time, time_hires, " \
"src_leg, dst_leg, branch_id, id " \
"from acc where method = 'BYE' and callid in ('%s', '%s"PBXSUFFIX""XFERSUFFIX"') " \
"order by length(callid) asc, time_hires asc)"
#define MED_LOAD_PEER_QUERY "select h.ip, h.host, g.peering_contract_id, h.id " \
"from provisioning.voip_peer_hosts h, provisioning.voip_peer_groups g " \
"where g.id = h.group_id"
#define MED_LOAD_CDR_TAG_IDS_QUERY "select id, type from accounting.cdr_tag"
typedef struct _medmysql_handler {
const char *name;
MYSQL *m;
int is_transaction;
GQueue transaction_statements;
} medmysql_handler;
typedef struct _statement_str {
char *str;
unsigned long len;
} statement_str;
typedef struct _cdr_tag_record {
unsigned long long cdr_id;
char *sql_record;
} cdr_tag_record;
typedef struct {
const char *begin;
const char *end;
unsigned long cdr_id;
} single_cdr;
typedef struct _medmysql_batch_definition {
const char *sql_init_string,
*sql_finish_string;
unsigned int min_string_tail_room; // defaults to 1024 if not set
int (*single_flush_func)(struct medmysql_str *);
int (*full_flush_func)(struct medmysql_batches *);
medmysql_handler **handler_ptr;
} medmysql_batch_definition;
static medmysql_handler *cdr_handler;
static medmysql_handler *int_cdr_handler;
static medmysql_handler *med_handler;
static medmysql_handler *prov_handler;
static medmysql_handler *stats_handler;
static unsigned long medmysql_cdr_auto_increment_value;
static unsigned long medmysql_tag_provider_customer;
static unsigned long medmysql_tag_provider_reseller;
static unsigned long medmysql_tag_provider_carrier;
static unsigned long medmysql_tag_direction_source;
static unsigned long medmysql_tag_direction_destination;
static int medmysql_flush_cdr(struct medmysql_batches *);
static int medmysql_flush_int_cdr(struct medmysql_batches *);
static int medmysql_flush_all_med(struct medmysql_batches *);
static int medmysql_flush_med_str(struct medmysql_str *);
static int medmysql_flush_medlist(struct medmysql_str *);
static int medmysql_flush_call_stat_info(void);
static void medmysql_handler_close(medmysql_handler **h);
static int medmysql_handler_transaction(medmysql_handler *h);
static const medmysql_batch_definition medmysql_trash_def = {
.sql_init_string = "insert into acc_trash (method, from_tag, to_tag, callid, sip_code, " \
"sip_reason, time, time_hires, src_leg, dst_leg, dst_user, dst_ouser, " \
"dst_domain, src_user, src_domain, branch_id) select method, from_tag, to_tag, " \
"callid, sip_code, sip_reason, time, time_hires, src_leg, dst_leg, " \
"dst_user, dst_ouser, dst_domain, src_user, src_domain, branch_id from acc " \
"where callid in (",
.sql_finish_string = ")",
.single_flush_func = medmysql_flush_medlist,
.handler_ptr = &med_handler,
};
static const medmysql_batch_definition medmysql_backup_def = {
.sql_init_string = "insert into acc_backup (method, from_tag, to_tag, callid, sip_code, " \
"sip_reason, time, time_hires, src_leg, dst_leg, dst_user, dst_ouser, " \
"dst_domain, src_user, src_domain, branch_id) select method, from_tag, to_tag, " \
"callid, sip_code, sip_reason, time, time_hires, src_leg, dst_leg, " \
"dst_user, dst_ouser, dst_domain, src_user, src_domain, branch_id from acc " \
"where callid in (",
.sql_finish_string = ")",
.single_flush_func = medmysql_flush_medlist,
.handler_ptr = &med_handler,
};
static const medmysql_batch_definition medmysql_delete_def = {
.sql_init_string = "delete from acc where callid in (",
.sql_finish_string = ")",
.full_flush_func = medmysql_flush_all_med,
.handler_ptr = &med_handler,
};
static const medmysql_batch_definition medmysql_cdr_def = {
.sql_init_string = "insert into cdr (id, update_time, " \
"source_user_id, source_provider_id, source_external_subscriber_id, "\
"source_external_contract_id, source_account_id, source_user, source_domain, " \
"source_cli, source_clir, source_ip, "\
"destination_user_id, destination_provider_id, destination_external_subscriber_id, "\
"destination_external_contract_id, destination_account_id, destination_user, destination_domain, " \
"destination_user_in, destination_domain_in, destination_user_dialed, " \
"peer_auth_user, peer_auth_realm, call_type, call_status, call_code, init_time, start_time, "\
"duration, call_id, " \
"source_carrier_cost, source_reseller_cost, source_customer_cost, " \
"destination_carrier_cost, destination_reseller_cost, destination_customer_cost, " \
"split, " \
"source_gpp0, source_gpp1, source_gpp2, source_gpp3, source_gpp4, " \
"source_gpp5, source_gpp6, source_gpp7, source_gpp8, source_gpp9, " \
"destination_gpp0, destination_gpp1, destination_gpp2, destination_gpp3, destination_gpp4, " \
"destination_gpp5, destination_gpp6, destination_gpp7, destination_gpp8, destination_gpp9, " \
"source_lnp_prefix, destination_lnp_prefix, " \
"source_user_out, destination_user_out, " \
"source_lnp_type, destination_lnp_type" \
") values ",
.full_flush_func = medmysql_flush_cdr,
.min_string_tail_room = 6000,
.handler_ptr = &cdr_handler,
};
static const medmysql_batch_definition medmysql_int_cdr_def = {
.sql_init_string = "insert into int_cdr (id, update_time, " \
"source_user_id, source_provider_id, source_external_subscriber_id, "\
"source_external_contract_id, source_account_id, source_user, source_domain, " \
"source_cli, source_clir, source_ip, "\
"destination_user_id, destination_provider_id, destination_external_subscriber_id, "\
"destination_external_contract_id, destination_account_id, destination_user, destination_domain, " \
"destination_user_in, destination_domain_in, destination_user_dialed, " \
"peer_auth_user, peer_auth_realm, call_type, call_status, call_code, init_time, start_time, "\
"duration, call_id, " \
"source_carrier_cost, source_reseller_cost, source_customer_cost, " \
"destination_carrier_cost, destination_reseller_cost, destination_customer_cost, " \
"split, " \
"source_gpp0, source_gpp1, source_gpp2, source_gpp3, source_gpp4, " \
"source_gpp5, source_gpp6, source_gpp7, source_gpp8, source_gpp9, " \
"destination_gpp0, destination_gpp1, destination_gpp2, destination_gpp3, destination_gpp4, " \
"destination_gpp5, destination_gpp6, destination_gpp7, destination_gpp8, destination_gpp9, " \
"source_lnp_prefix, destination_lnp_prefix, " \
"source_user_out, destination_user_out, " \
"source_lnp_type, destination_lnp_type, acc_ref" \
") values ",
.sql_finish_string = " on duplicate key update "
"update_time = values(update_time), source_user_id = values(source_user_id), " \
"source_provider_id = values(source_provider_id), source_external_subscriber_id " \
"= values(source_external_subscriber_id), source_external_contract_id = " \
"values(source_external_contract_id), source_account_id = " \
"values(source_account_id), source_user = values(source_user), source_domain = " \
"values(source_domain), source_cli = values(source_cli), source_clir = " \
"values(source_clir), source_ip = values(source_ip), destination_user_id = " \
"values(destination_user_id), destination_provider_id = " \
"values(destination_provider_id), destination_external_subscriber_id = " \
"values(destination_external_subscriber_id), destination_external_contract_id = " \
"values(destination_external_contract_id), destination_account_id = " \
"values(destination_account_id), destination_user = values(destination_user), " \
"destination_domain = values(destination_domain), destination_user_in = " \
"values(destination_user_in), destination_domain_in = " \
"values(destination_domain_in), destination_user_dialed = " \
"values(destination_user_dialed), peer_auth_user = values(peer_auth_user), " \
"peer_auth_realm = values(peer_auth_realm), call_type = values(call_type), " \
"call_status = values(call_status), call_code = values(call_code), init_time = " \
"values(init_time), start_time = values(start_time), duration = " \
"values(duration), call_id = values(call_id), source_carrier_cost = " \
"values(source_carrier_cost), source_reseller_cost = " \
"values(source_reseller_cost), source_customer_cost = " \
"values(source_customer_cost), destination_carrier_cost = " \
"values(destination_carrier_cost), destination_reseller_cost = " \
"values(destination_reseller_cost), destination_customer_cost = " \
"values(destination_customer_cost), split = values(split), source_gpp0 = " \
"values(source_gpp0), source_gpp1 = values(source_gpp1), source_gpp2 = " \
"values(source_gpp2), source_gpp3 = values(source_gpp3), source_gpp4 = " \
"values(source_gpp4), source_gpp5 = values(source_gpp5), source_gpp6 = " \
"values(source_gpp6), source_gpp7 = values(source_gpp7), source_gpp8 = " \
"values(source_gpp8), source_gpp9 = values(source_gpp9), destination_gpp0 = " \
"values(destination_gpp0), destination_gpp1 = values(destination_gpp1), " \
"destination_gpp2 = values(destination_gpp2), destination_gpp3 = " \
"values(destination_gpp3), destination_gpp4 = values(destination_gpp4), " \
"destination_gpp5 = values(destination_gpp5), destination_gpp6 = " \
"values(destination_gpp6), destination_gpp7 = values(destination_gpp7), " \
"destination_gpp8 = values(destination_gpp8), destination_gpp9 = " \
"values(destination_gpp9), source_lnp_prefix = values(source_lnp_prefix), " \
"destination_lnp_prefix = values(destination_lnp_prefix), source_user_out = " \
"values(source_user_out), destination_user_out = values(destination_user_out), " \
"source_lnp_type = values(source_lnp_type), destination_lnp_type = " \
"values(destination_lnp_type), export_status = 'unexported'",
.full_flush_func = medmysql_flush_int_cdr,
.min_string_tail_room = 9000,
.handler_ptr = &int_cdr_handler,
};
static const medmysql_batch_definition medmysql_del_int_cdr_def = {
.sql_init_string = "delete from int_cdr where call_id in (",
.sql_finish_string = ")",
.single_flush_func = medmysql_flush_med_str,
.handler_ptr = &int_cdr_handler,
};
static const medmysql_batch_definition medmysql_tag_def = {
.sql_init_string = "insert into cdr_tag_data (cdr_id, provider_id, direction_id, tag_id, " \
"val, cdr_start_time) values ",
.single_flush_func = medmysql_flush_med_str,
.handler_ptr = &cdr_handler,
};
static const medmysql_batch_definition medmysql_int_tag_def = {
.sql_init_string = "insert into int_cdr_tag_data (cdr_id, provider_id, direction_id, tag_id, " \
"val, cdr_start_time) values ",
.sql_finish_string = " on duplicate key update "
"val = values(val)",
.single_flush_func = medmysql_flush_med_str,
.handler_ptr = &int_cdr_handler,
};
static const medmysql_batch_definition medmysql_mos_def = {
.sql_init_string = "insert into cdr_mos_data (" \
"cdr_id, mos_average, mos_average_packetloss, mos_average_jitter, " \
"mos_average_roundtrip, cdr_start_time" \
") values ",
.single_flush_func = medmysql_flush_med_str,
.handler_ptr = &cdr_handler,
};
static const medmysql_batch_definition medmysql_group_def = {
.sql_init_string = "insert into cdr_group (" \
"cdr_id, call_id, cdr_start_time" \
") values ",
.single_flush_func = medmysql_flush_med_str,
.handler_ptr = &cdr_handler,
};
static const medmysql_batch_definition medmysql_int_group_def = {
.sql_init_string = "insert ignore into int_cdr_group (" \
"cdr_id, call_id, cdr_start_time" \
") values ",
.single_flush_func = medmysql_flush_med_str,
.handler_ptr = &int_cdr_handler,
};
static void statement_free(void *stm_p) {
statement_str *stm = stm_p;
free(stm->str);
free(stm);
}
static void __g_queue_clear_full(GQueue *q, GDestroyNotify free_func) {
void *p;
while ((p = g_queue_pop_head(q)))
free_func(p);
}
static unsigned int medmysql_real_query_errno(MYSQL *m, const char *s, unsigned long len) {
#if _TEST_SIMULATE_SQL_ERRORS
if (rand() % 10 == 0) {
L_INFO("Simulating SQL error - statement '%.*s'",
(int) len, s);
return CR_SERVER_LOST;
}
#endif
int ret = mysql_real_query(m, s, len);
if (!ret)
return 0;
return mysql_errno(m);
}
static int medmysql_query_wrapper(medmysql_handler *mysql, const char *stmt_str, unsigned long length) {
int i;
unsigned int err;
for (i = 0; i < 10; i++) {
err = medmysql_real_query_errno(mysql->m, stmt_str, length);
if (!err)
break;
if (err == CR_SERVER_GONE_ERROR || err == CR_SERVER_LOST || err == CR_CONN_HOST_ERROR
|| err == CR_CONNECTION_ERROR)
{
L_WARNING("Lost connection to SQL server during query, retrying...");
sleep(10);
continue;
}
break;
}
return !!err;
}
static int medmysql_query_wrapper_tx(medmysql_handler *mysql, const char *stmt_str, unsigned long length) {
int i;
unsigned int err;
if (!mysql->is_transaction) {
L_CRITICAL("SQL mode is not in transaction");
return -1;
}
for (i = 0; i < 10; i++) {
err = medmysql_real_query_errno(mysql->m, stmt_str, length);
if (!err)
break;
if (err == CR_SERVER_GONE_ERROR || err == CR_SERVER_LOST || err == CR_CONN_HOST_ERROR
|| err == CR_CONNECTION_ERROR || err == ER_LOCK_WAIT_TIMEOUT
|| err == ER_LOCK_DEADLOCK)
{
// rollback, cancel transaction, restart transaction, replay all statements,
// and then try again
L_WARNING("Got error %u from SQL server during transaction, retrying...",
err);
err = medmysql_real_query_errno(mysql->m, "rollback", 8);
if (err) {
L_CRITICAL("Got error %u from SQL during rollback",
mysql_errno(mysql->m));
return -1;
}
mysql->is_transaction = 0;
sleep(10);
if (medmysql_handler_transaction(mysql))
return -1;
// steal the statement queue and recursively replay them into a new empty queue
GQueue replay = mysql->transaction_statements;
g_queue_init(&mysql->transaction_statements);
statement_str *stm;
while ((stm = g_queue_pop_head(&replay))) {
if (medmysql_query_wrapper_tx(mysql, stm->str, stm->len)) {
__g_queue_clear_full(&mysql->transaction_statements, statement_free);
statement_free(stm);
return -1;
}
statement_free(stm);
}
continue;
}
break;
}
if (!err) {
// append statement to queue for possible replaying
statement_str *stm = malloc(sizeof(*stm));
if (!stm) {
L_CRITICAL("Out of memory (malloc statement_str)");
return -1;
}
stm->str = malloc(length);
if (!stm->str) {
L_CRITICAL("Out of memory (malloc statement_str body)");
free(stm);
return -1;
}
memcpy(stm->str, stmt_str, length);
stm->len = length;
g_queue_push_tail(&mysql->transaction_statements, stm);
}
return !!err;
}
static medmysql_handler *medmysql_handler_init(const char *name, const char *host, const char *user,
const char *pass, const char *db, unsigned int port)
{
medmysql_handler *ret;
bool recon = 1;
ret = malloc(sizeof(*ret));
if (!ret) {
L_CRITICAL("Out of memory (malloc in medmysql_handler_init)");
return NULL;
}
memset(ret, 0, sizeof(*ret));
ret->name = name;
g_queue_init(&ret->transaction_statements);
ret->m = mysql_init(NULL);
if (!ret->m) {
L_CRITICAL("Out of memory (mysql_init)");
goto err;
}
if(!mysql_real_connect(ret->m,
host, user, pass,
db, port, NULL, 0))
{
L_CRITICAL("Error connecting to %s db: %s", name, mysql_error(ret->m));
goto err;
}
if(mysql_options(ret->m, MYSQL_OPT_RECONNECT, &recon) != 0)
{
L_CRITICAL("Error setting reconnect-option for %s db: %s", name, mysql_error(ret->m));
goto err;
}
if(mysql_autocommit(ret->m, 1) != 0)
{
L_CRITICAL("Error setting autocommit=1 for %s db: %s", name,
mysql_error(ret->m));
goto err;
}
return ret;
err:
medmysql_handler_close(&ret);
return NULL;
}
/**********************************************************************/
static unsigned long medmysql_get_num_col(medmysql_handler *handler, const char *query) {
if (medmysql_query_wrapper(handler, query, strlen(query))) {
L_CRITICAL("Error getting DB value (query '%s'): %s",
query, mysql_error(handler->m));
return 0;
}
MYSQL_RES *res = mysql_store_result(handler->m);
if (!res) {
L_CRITICAL("No result set returned from SQL (query '%s'): %s",
query, mysql_error(handler->m));
return 0;
}
MYSQL_ROW row = mysql_fetch_row(res);
if (!row || !row[0]) {
L_CRITICAL("No row returned from SQL (query '%s'): %s",
query, mysql_error(handler->m));
mysql_free_result(res);
return 0;
}
unsigned long num = strtoul(row[0], NULL, 10);
if (!num) {
L_CRITICAL("Returned number from DB (query '%s') is '%s'",
query, row[0]);
mysql_free_result(res);
return 0;
}
mysql_free_result(res);
return num;
}
/**********************************************************************/
int medmysql_init()
{
cdr_handler = medmysql_handler_init("CDR",
config_cdr_host, config_cdr_user, config_cdr_pass,
config_cdr_db, config_cdr_port);
if (!cdr_handler)
goto err;
int_cdr_handler = medmysql_handler_init("INT-CDR",
config_intermediate_cdr_host, config_cdr_user, config_cdr_pass,
config_cdr_db, config_intermediate_cdr_port);
if (!int_cdr_handler)
goto err;
med_handler = medmysql_handler_init("ACC",
config_med_host, config_med_user, config_med_pass,
config_med_db, config_med_port);
if (!med_handler)
goto err;
prov_handler = medmysql_handler_init("provisioning",
config_prov_host, config_prov_user, config_prov_pass,
config_prov_db, config_prov_port);
if (!prov_handler)
goto err;
stats_handler = medmysql_handler_init("STATS",
config_stats_host, config_stats_user, config_stats_pass,
config_stats_db, config_stats_port);
if (!stats_handler)
goto err;
return 0;
err:
medmysql_cleanup();
return -1;
}
/**********************************************************************/
static void medmysql_handler_close(medmysql_handler **h) {
if (!*h)
return;
if ((*h)->m)
mysql_close((*h)->m);
if ((*h)->transaction_statements.length)
L_WARNING("Closing DB handle with still %u statements in queue",
(*h)->transaction_statements.length);
__g_queue_clear_full(&(*h)->transaction_statements, statement_free);
free(*h);
*h = NULL;
}
void medmysql_cleanup()
{
medmysql_handler_close(&cdr_handler);
medmysql_handler_close(&int_cdr_handler);
medmysql_handler_close(&med_handler);
medmysql_handler_close(&prov_handler);
medmysql_handler_close(&stats_handler);
}
static void medmysql_buf_escape(MYSQL *m, size_t *buflen, const char *str, const size_t str_len,
char *orig_dest,
size_t sql_buffer_size)
{
char *dest = orig_dest;
// verify buffer space requirements: string itself * 2, quotes '', optional "_latin1", null
if (MED_SQL_BUF_LEN(*buflen, str_len) > sql_buffer_size)
abort();
if (!g_utf8_validate(str, str_len, NULL)) {
// force the string as latin1
memcpy(dest, "_latin1'", 8);
dest += 8;
}
else
*dest++ = '\'';
size_t esc_len = mysql_real_escape_string(m, dest, str, str_len);
dest += esc_len;
*dest++ = '\'';
*dest = '\0';
*buflen += dest - orig_dest;
}
static inline void medmysql_buf_escape_c(MYSQL *m, size_t *buflen, const char *str, char *orig_dest,
size_t sql_buffer_size)
{
medmysql_buf_escape(m, buflen, str, strlen(str), orig_dest, sql_buffer_size);
}
static inline void medmysql_buf_escape_gstring(MYSQL *m, const char *str, GString *s)
{
size_t str_len = strlen(str);
size_t orig_size = s->len;
size_t req_size = MED_SQL_BUF_LEN(orig_size, str_len);
g_string_set_size(s, req_size);
char *dst = s->str + orig_size;
medmysql_buf_escape(m, &orig_size, str, str_len, dst, s->len);
g_string_set_size(s, orig_size);
}
#define BUFPRINT(x...) g_string_append_printf(sql_buffer, x)
#define BUFESCAPE(x) medmysql_buf_escape_gstring(med_handler->m, x, sql_buffer)
/**********************************************************************/
int medmysql_insert_records(GQueue *records, const char *table)
{
int ret = 0, entries = 0;
if (!records->length)
return 0;
GString *sql_buffer = g_string_sized_new(records->length * 512);
BUFPRINT("INSERT INTO kamailio.acc_%s " \
"(sip_code,sip_reason,method,callid,time,time_hires,src_leg,dst_leg,branch_id) VALUES ",
table);
for (GList *l = records->head; l; l = l->next) {
med_entry_t *e = l->data;
// this is only used for inserting redis entries into mysql
if (!e->redis)
continue;
BUFPRINT("(");
BUFESCAPE(e->sip_code);
BUFPRINT(",");
BUFESCAPE(e->sip_reason);
BUFPRINT(",");
BUFESCAPE(e->sip_method);
BUFPRINT(",");
BUFESCAPE(e->callid);
BUFPRINT(",");
BUFESCAPE(e->timestamp);
BUFPRINT(",'%f',", e->unix_timestamp);
BUFESCAPE(e->src_leg);
BUFPRINT(",");
BUFESCAPE(e->dst_leg);
BUFPRINT(",");
BUFESCAPE(e->branch_id);
BUFPRINT("),");
entries++;
}
if (!entries)
goto out;
g_string_set_size(sql_buffer, sql_buffer->len - 1);
L_DEBUG("Issuing record insert query: %s\n", sql_buffer->str);
ret = medmysql_query_wrapper(med_handler, sql_buffer->str, sql_buffer->len);
if (ret != 0)
{
L_ERROR("Error executing query '%s': %s",
sql_buffer->str, mysql_error(med_handler->m));
}
out:
g_string_free(sql_buffer, TRUE);
return ret;
}
/**********************************************************************/
gboolean medmysql_fetch_callids(GQueue *output)
{
MYSQL_RES *res;
MYSQL_ROW row;
/* char query[1024] = ""; */
/* g_strlcpy(query, MED_CALLID_QUERY, sizeof(query)); */
/*L_DEBUG("q='%s'", query);*/
if(medmysql_query_wrapper(med_handler, MED_CALLID_QUERY, strlen(MED_CALLID_QUERY)) != 0)
{
L_CRITICAL("Error getting acc callids: %s",
mysql_error(med_handler->m));
return FALSE;
}
res = mysql_store_result(med_handler->m);
while((row = mysql_fetch_row(res)) != NULL)
{
if(row[0] == NULL)
{
g_queue_push_tail(output, g_strdup("0"));
} else {
g_queue_push_tail(output, g_strdup(row[0]));
}
/*L_DEBUG("callid[%"PRIu64"]='%s'", i, c->value);*/
if (check_shutdown()) {
mysql_free_result(res);
g_queue_clear_full(output, free);
return FALSE;
}
}
mysql_free_result(res);
return TRUE;
}
/**********************************************************************/
int medmysql_fetch_records(char *callid,
GQueue *entries, int warn_empty,
records_filter_func filter, void *filter_data)
{
MYSQL_RES *res;
MYSQL_ROW row;
size_t callid_len = strlen(callid);
int ret = 0;
unsigned long long count = 0;
char esc_callid[callid_len*2+1];
mysql_real_escape_string(med_handler->m, esc_callid, callid, callid_len);
g_autoptr(char) query = g_strdup_printf(MED_FETCH_QUERY,
esc_callid,
esc_callid, esc_callid,
esc_callid, esc_callid,
esc_callid, esc_callid);
assert(query != NULL);
/*L_DEBUG("q='%s'", query);*/
if(medmysql_query_wrapper(med_handler, query, strlen(query)) != 0)
{
L_CRITICAL("Error getting acc records for callid '%s': %s",
callid, mysql_error(med_handler->m));
return -1;
}
res = mysql_store_result(med_handler->m);
count = mysql_num_rows(res);
if(count == 0)
{
if (warn_empty)
L_CRITICAL("No records found for callid '%s'!",
callid);
ret = -1;
goto out;
}
ret = 0;
while((row = mysql_fetch_row(res)) != NULL)
{
med_entry_t *e = g_slice_alloc0(sizeof(*e));
g_strlcpy(e->sip_code, row[0], sizeof(e->sip_code));
g_strlcpy(e->sip_reason, row[1], sizeof(e->sip_reason));
g_strlcpy(e->sip_method, row[2], sizeof(e->sip_method));
e->callid = g_strdup(row[3]);
g_strlcpy(e->timestamp, row[4], sizeof(e->timestamp));
e->unix_timestamp = atof(row[5]);
e->src_leg = g_strdup(row[6] ? : "");
e->dst_leg = g_strdup(row[7] ? : "");
g_strlcpy(e->branch_id, row[8] ? : "", sizeof(e->branch_id));
e->acc_ref = g_strdup(row[9]);
e->valid = 1;
cdr_parse_entry(e);
if (!filter || filter(e, filter_data))
{
g_queue_push_tail(entries, e);
if (records_handle_refer(entries, e, callid))
ret = 1;
}
else
med_entry_free(e);
if (check_shutdown())
return -1;
}
out:
mysql_free_result(res);
return ret;
}
/**********************************************************************/
static int medmysql_batch_prepare(struct medmysql_str *str)
{
const medmysql_batch_definition *def = str->def;
struct medmysql_batches *batches = str->batches;
unsigned int tail_room = def->min_string_tail_room;
if (!tail_room)
tail_room = 1024;
if (str->len > (PACKET_SIZE - tail_room)) {
if (def->single_flush_func) {
if (def->single_flush_func(str))
return -1;
}
if (def->full_flush_func) {
if (def->full_flush_func(batches))
return -1;
}
}
if (str->len == 0)
str->len = sprintf(str->str, "%s", def->sql_init_string);
return 0;
}
/**********************************************************************/
int medmysql_trash_entries(const char *callid, struct medmysql_batches *batches)
{
char esc_callid[strlen(callid)*2+1];
mysql_real_escape_string(med_handler->m, esc_callid, callid, strlen(callid));
if (medmysql_batch_prepare(&batches->acc_trash))
return -1;
batches->acc_trash.len += sprintf(batches->acc_trash.str + batches->acc_trash.len, "'%s',", esc_callid);
return medmysql_delete_entries(esc_callid, batches);
}
/**********************************************************************/
int medmysql_backup_entries(const char *callid, struct medmysql_batches *batches)
{
char esc_callid[strlen(callid)*2+1];
mysql_real_escape_string(med_handler->m, esc_callid, callid, strlen(callid));
if (medmysql_batch_prepare(&batches->acc_backup))
return -1;
batches->acc_backup.len += sprintf(batches->acc_backup.str + batches->acc_backup.len, "'%s',", esc_callid);
return medmysql_delete_entries(esc_callid, batches);
}
/**********************************************************************/
static int medmysql_flush_all_med(struct medmysql_batches *batches) {
if (medmysql_flush_medlist(&batches->acc_backup))
return -1;
if (medmysql_flush_medlist(&batches->acc_trash))
return -1;
if (medmysql_flush_medlist(&batches->to_delete))
return -1;
return 0;
}
int medmysql_delete_entries(const char *callid, struct medmysql_batches *batches)
{
if (medmysql_batch_prepare(&batches->to_delete))
return -1;
batches->to_delete.len += sprintf(batches->to_delete.str + batches->to_delete.len, "'%s',", callid);
return 0;
}
#define CDRPRINT(x) batch->cdrs.len += sprintf(batch->cdrs.str + batch->cdrs.len, x)
#define CDRESCAPE(x) medmysql_buf_escape(med_handler->m, &batch->cdrs.len, x->str, x->len, batch->cdrs.str + batch->cdrs.len, PACKET_SIZE)
#define CDRESCAPE_C(x) medmysql_buf_escape_c(med_handler->m, &batch->cdrs.len, x, batch->cdrs.str + batch->cdrs.len, PACKET_SIZE)
/**********************************************************************/
static int medmysql_tag_record(GQueue *q, unsigned long cdr_id, unsigned long provider_id,
unsigned long direction_id, const GString *value, double start_time, unsigned long tag_id)
{
char esc_value[value->len*2+1];
mysql_real_escape_string(med_handler->m, esc_value, value->str, value->len);
cdr_tag_record *record = malloc(sizeof(*record));
record->cdr_id = cdr_id;
if (asprintf(&record->sql_record, "%lu, %lu, %lu, '%s', %f",
provider_id, direction_id, tag_id, esc_value, start_time) <= 0)
{
free(record);
return -1;
}
g_queue_push_tail(q, record);
return 0;
}
static int medmysql_mos_record(GQueue *q, unsigned long cdr_id, double avg_score, int avg_packetloss,
int avg_jitter, int avg_rtt, double start_time)
{
cdr_tag_record *record = malloc(sizeof(*record));
record->cdr_id = cdr_id;
if (asprintf(&record->sql_record, "%.1f, %.1f, %.1f, %.1f, %.3f",
avg_score, (double) avg_packetloss, (double) avg_jitter,
(double) avg_rtt, start_time) <= 0)
{
free(record);
return -1;
}
g_queue_push_tail(q, record);
return 0;
}
static int medmysql_group_record(MYSQL *m, GQueue *q, unsigned long cdr_id, const GString *group, double start_time)
{
char esc_group[group->len*2+1];
mysql_real_escape_string(m, esc_group, group->str, group->len);
cdr_tag_record *record = malloc(sizeof(*record));
record->cdr_id = cdr_id;
if (asprintf(&record->sql_record, "'%s', %.3f", esc_group, start_time) <= 0)
{
free(record);
return -1;
}
g_queue_push_tail(q, record);
return 0;
}
static int medmysql_tag_cdr(struct medmysql_cdr_batch *batch, unsigned long provider_id,
unsigned long direction_id, const char *tag_name, const GString *tag_value,
const cdr_entry_t *e)
{
gpointer tag_id;
if (!tag_value->len)
return 0;
if ((tag_id = g_hash_table_lookup(med_cdr_tag_table, tag_name)) == NULL) {
L_WARNING("Call-Id '%s' has no cdr tag type '%s', '%s'",
e->call_id->str, tag_name, tag_value->str);
return -1;
}
if (medmysql_tag_record(&batch->tags.q, batch->num_cdrs, provider_id,
direction_id, tag_value, e->start_time,
GPOINTER_TO_UINT(tag_id)))
return -1;
return 0;
}
int medmysql_insert_cdrs(cdr_entry_t *entries, uint64_t count, struct medmysql_batches *batches)
{
uint64_t i;
int gpp;
int ret = 1; // default to intermediate
for(i = 0; i < count; ++i)
{
cdr_entry_t *e = &(entries[i]);
struct medmysql_cdr_batch *batch = &batches->cdr_batch;
if (e->intermediate)
batch = &batches->int_cdr_batch;
else
ret = 0; // if at least one record is not intermediate, we delete
if (medmysql_batch_prepare(&batch->cdrs))
return -1;
char str_source_clir[4] = "";
char str_split[4] = "";
char str_init_time[32] = "";
char str_start_time[32] = "";
char str_duration[32] = "";
char str_source_carrier_cost[32] = "";
char str_source_reseller_cost[32] = "";
char str_source_customer_cost[32] = "";
char str_dest_carrier_cost[32] = "";
char str_dest_reseller_cost[32] = "";
char str_dest_customer_cost[32] = "";
char str_source_accid[32] = "";
char str_dest_accid[32] = "";
snprintf(str_source_clir, sizeof(str_source_clir), "%u", e->source_clir);
snprintf(str_split, sizeof(str_split), "%u", e->split);
snprintf(str_init_time, sizeof(str_init_time), "%f", e->init_time);
snprintf(str_start_time, sizeof(str_start_time), "%f", e->start_time);
snprintf(str_duration, sizeof(str_duration), "%f", e->duration);
snprintf(str_source_carrier_cost, sizeof(str_source_carrier_cost), "%u", e->source_carrier_cost);
snprintf(str_source_reseller_cost, sizeof(str_source_reseller_cost), "%u", e->source_reseller_cost);
snprintf(str_source_customer_cost, sizeof(str_source_customer_cost), "%u", e->source_customer_cost);
snprintf(str_dest_carrier_cost, sizeof(str_dest_carrier_cost), "%u", e->destination_carrier_cost);
snprintf(str_dest_reseller_cost, sizeof(str_dest_reseller_cost), "%u", e->destination_reseller_cost);
snprintf(str_dest_customer_cost, sizeof(str_dest_customer_cost), "%u", e->destination_customer_cost);
snprintf(str_source_accid, sizeof(str_source_accid), "%llu", (long long unsigned int) e->source_account_id);
snprintf(str_dest_accid, sizeof(str_dest_accid), "%llu", (long long unsigned int) e->destination_account_id);
const char *begin_ptr = batch->cdrs.str + batch->cdrs.len;
CDRPRINT("(NULL, now(), ");
CDRESCAPE(e->source_user_id);
CDRPRINT(",");
CDRESCAPE(e->source_provider_id);
CDRPRINT(",");
CDRESCAPE(e->source_ext_subscriber_id);
CDRPRINT(",");
CDRESCAPE(e->source_ext_contract_id);
CDRPRINT(",");
CDRESCAPE_C(str_source_accid);
CDRPRINT(",");
CDRESCAPE(e->source_user);
CDRPRINT(",");
CDRESCAPE(e->source_domain);
CDRPRINT(",");
CDRESCAPE(e->source_cli);
CDRPRINT(",");
CDRESCAPE_C(str_source_clir);
CDRPRINT(",");
CDRESCAPE(e->source_ip);
CDRPRINT(",");
CDRESCAPE(e->destination_user_id);
CDRPRINT(",");
CDRESCAPE(e->destination_provider_id);
CDRPRINT(",");
CDRESCAPE(e->destination_ext_subscriber_id);
CDRPRINT(",");
CDRESCAPE(e->destination_ext_contract_id);
CDRPRINT(",");
CDRESCAPE_C(str_dest_accid);
CDRPRINT(",");
CDRESCAPE(e->destination_user);