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.0Where:
- 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 " "; print_string "the_tx: " ; print_float the_tx ; print_string "\t\t the_tx doubled: " ; print_float (the_tx *. 2.) ; print_string " "; print_string "the_rx: " ; print_float the_rx ; print_string "\t\t the_rx doubled: " ; print_float (the_rx *. 2.) ; print_string " "; 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 " "; 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
