Skip to main content

ocaml sqlite3 date

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 
  • 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 "=================================================================================================================="; ;; 


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


Popular posts from this blog

vmware libz libfontconfig libexpat

Archlinux - Kernel 4.11 - VMWare workstation 12.5.7 With this combination, when I launch "vmware", despite the fact I already "export VMWARE_USE_SHIPPED_LIBS=force", I get those lines:

Unable to load /usr/lib/vmware/lib/ version `ZLIB_1.2.9' not found (required by /usr/lib/ Unable to load from /usr/lib/vmware/lib/ cannot open shared object file: No such file or directory Unable to load dependencies for /usr/lib/vmware/lib/
In order to workaround, I decided to get the things to the maximum: Add all shipped libraries in the LD_LIBRARY_PATH.

So I created my custom launcher of "vmware" and this is the content:

LD_LIBRARY_PATH=$( find /usr/lib/vmware/lib/ -maxdepth 1 -mindepth 1 -type d | awk 'BEGIN{p=&quo…

vmware net_device trans_start

VMWare Workstation 12 and Kernel 4.7 When recompiling vmware kernel modules on a kernel 4.7, I get this error:

/tmp/modconfig-xrrZGZ/vmnet-only/netif.c:468:7: error: ‘struct net_device’ has no member named ‘trans_start’; did you mean ‘mem_start’?     dev->trans_start = jiffies;
This seems to be an already encountered problem: I choosed to replace the line, instead of deleting it.

- dev->trans_start = jiffies; + netif_trans_update(dev); I also noted that I had to re-tar the modified sources instead of leaving them untared, because the compilation process only takes the archives. 
On precedent editions of these files, I just left the modified folders "vmnet-only/" and "vmmon-only/" expanded without the need to re-tar them.

Jira workflow for new projects

Associated workflow creation I'm a Jira Cloud user and begining from some version 6, I noticed that when I create a project, it automatically creates a Workflow and Issue Scheme that is prepended by the project key and which is a copy of the default scheme.
I always had to make a cleanup after creating a project. Default workflow for new projects I also miss a feature that would allow me to make a custom workflow (and globally custom project setting) the default for new projects I create.
Solution: Create with shared configuration While searching, I noticed that with Jira Cloud which is version 7.1.0 at the time I write, there is a link at the bottom of the "Create project" wizard:
"Create with shared configuration" will allow me to select the project I want the new one to share configuration with.

The new created project will use the same configuration as the project I selectThere will be no creation of Workflow and Issue Scheme that I need to cleanup

This fea…