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

Comments

Popular posts from this blog

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: http://rglinuxtech.com/?p=1746http://ferenc.homelinux.com/?p=356 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…

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=ftp://packages-infra.mg.rktmb.org/pub/centos/7/base-reposync-7 " >> /etc/yum.repos.d/CentOS-Base.repo RUN echo "gpgcheck=0 " >>…