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

npm run build base-href

Using NPM to specify base-href When building an Angular application, people usually use "ng" and pass arguments to that invocation. Typically, when wanting to hard code "base-href" in "index.html", one will issue: ng build --base-href='' I used to build my angular apps through Bamboo or Jenkins and they have a "npm" plugin. I got the habit to build the application with "npm run build" before deploying it. But the development team once asked me to set the "--base-href=''" parameter. npm run build --base-href=' did not set the base href in indext.html After looking for a while, I found where it says: You need to use −− to pass arguments to npm scripts. This did the job! The command to issue is then: npm run build -- --base-href='

dockerfile multiline to file

Outputing a multiline string from Dockerfile I motsly use a Dockerfile by sourcing from a base ditribution: CentOS or Debian. But I also have a local mirror and would like to use it for packages installation. Espacially on CentOS it is about many lines to write to the /etc/yum.repos.d/CentOS-Base.repo file. Easiest way: one RUN per line The first method that comes in mind is to issue one RUN per line to write. Here you are: RUN echo "[base] " > /etc/yum.repos.d/CentOS-Base.repo RUN echo "name=CentOS-$releasever - Base " >> /etc/yum.repos.d/CentOS-Base.repo RUN echo "baseurl= " >> /etc/yum.repos.d/CentOS-Base.repo RUN echo "gpgcheck=0 &quo

Gnome VNC Grey Checkboxes

When setting up VNC server in Gnome, I get a grey screen (or a black one) and the checkboxes to "Accept clipboard from viewers", "Send clipboard to viewers", "Send primary selection to viewers". I'm setting up a VNC server on a Debian 10 machine that has Gnome environment. A zillion articles can be found on Internet, they have the same global path: Install Gnome and GDM Install TigerVNC Setup the VNC Server: password + startup Run the server Connect from the client One of them is this TeknoTut how-to I took inspiration from. The tutorial is good, but I needed to complete it with extra steps in order to make it work: If the current user has already a running Gnome session, I just get the "grey screen with the checkboxes". It is good to know that: The "grey screen" is due to the "xsetroot" command that can be seen in some howtos The checkboxes are due to the "vncconfig -iconic &" command