Use case

I have a SQLite3 database, with Date, Integers and Float fields. I use it to make a poor-man accountig of my ppp0 interface traffic. Data I want to play with are in the format:

$ sqlite3 accouting-copy "SELECT * FROM ifconfig"
1|2011-08-12 09:37:47|0.0       |0.0
2|2011-08-12 09:43:01|18629153.0|8124895.0
3|2011-08-12 09:43:03|18636044.0|8125679.0
4|2011-08-12 09:44:32|18694283.0|8159197.0
5|2011-08-12 09:50:01|19203494.0|8270963.0
6|2011-08-12 09:55:01|19265098.0|8311962.0
Where:
  • Field #1: integer, incremental
  • Field #2: date, SQLite "DATETIME('NOW')"
  • Field #3 and #4: Float, "RX" and "TX" counters parsed from "ifconfig ppp0". Sometimes, these counters reset: If the modem hangs and I must perform a "ifdown ppp0; ifup ppp0". That is why I need computation.

I want to account the traffic:

  • Between to Dates: To know how much I had for a given month
  • From a given Date to Now(): To approximately know my remaining traffic

I am not going to care about to computing logic in this article, but focus on data extraction.

Preparing the toplevel

In order to use these examples, those modules are needed:

#use "topfind";;
#require "calendar";;
#require "sqlite3";;

Auxilliary functions and variables

Some variables:

let db = Sqlite3.db_open "/home/mihamina/accouting-copy";;
let the_query = "SELECT * FROM ifconfig";;

I need a couple of auxilliary functions.

To convert a "String Option" (Some "foo") to a String:

let so_to_strig the_so =
match the_so with
| Some s -> s;
| _ -> "";;

To get a Calendar Date from a an SQLite Date String:

let date_from_sql  = CalendarLib.Printer.Precise_Fcalendar.from_fstring "%F %T" ;;

To print a Date:

let date_to_string = CalendarLib.Printer.Precise_Fcalendar.to_string ;;

To add tso days to a Date (For testing or demonstration prupose):

let add_2_days a_date = CalendarLib.Fcalendar.Precise.add a_date (CalendarLib.Fcalendar.Precise.Period.make 0 0 2 0 0 0.);;

The callback just to print the table content:

let the_print_callback row headers =
(Array.iter (fun s -> Printf.printf "  %-12s"              s)  headers);
(print_endline "");
(Array.iter (fun s -> Printf.printf "  %-12s" (so_to_strig s)) row);
(print_endline "");;
Thanks and Acknowledgments

The callback to play with the data, which is the most important for the work:

let the_data_callback row headers=
(* row.(0) row.(1)  row.(2)  row.(3)  *)
(* id      date     rx       tx       *)
(* int     Date     float    float    *)
(* "headers" is no used at the moment *)
let the_id = int_of_string   (so_to_strig (row.(0)))
and the_rx = float_of_string (so_to_strig (row.(2)))
and the_tx = float_of_string (so_to_strig (row.(3)))
and the_date = CalendarLib.Printer.Precise_Fcalendar.from_fstring "%F %T" (so_to_strig (row.(1)))
in
print_string "the_id: "  ; print_int   the_id                    ; print_string "\t\t the_id doubled: "        ; print_int    (the_id * 2)                          ; print_string "\n";
print_string "the_tx: "  ; print_float the_tx                    ; print_string "\t\t the_tx doubled: "        ; print_float  (the_tx *. 2.)                        ; print_string "\n";
print_string "the_rx: "  ; print_float the_rx                    ; print_string "\t\t the_rx doubled: "        ; print_float  (the_rx *. 2.)                        ; print_string "\n";
print_string "the_date: "; print_string (date_to_string the_date); print_string "\t\t twodays after the_date: "; print_string (date_to_string (add_2_days the_date)); print_string "\n";
print_endline "==================================================================================================================";
;;

Launching

To print all the table content:

let result = Sqlite3.exec db ~cb:the_print_callback the_query;;

To make sample operations with the data:

  • double the ID and print it
  • double the RX and TX and print them
  • add 2 days to the Date and print it
let result = Sqlite3.exec db ~cb:the_data_callback the_query;;

Sample Source code

The source code is on my Google Code Repository