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

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='https://ngx.rktmb.org/foo' 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='https://ngx.rktmb.org/foo'" parameter. npm run build --base-href='https://ngx.rktmb.org/foo did not set the base href in indext.html After looking for a while, I found https://github.com/angular/angular-cli/issues/13560 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='https://ngx.rktmb.org/foo&

Emacs Pulumi LSP

Install Pulumi Emacs Mode The source code is on https://github.com/pulumi/pulumi-lsp. At the very bottom of the page are the instructions on how to install. You Need to have make installed. $ make install emacs-client mkdir -p ./bin go build -ldflags "-X github.com/pulumi/pulumi-lsp/sdk/version.Version=v0.2.3-6-gec49054" -o ./bin -p 10 ./cmd/... go: downloading github.com/pulumi/pulumi/sdk/v3 v3.53.1 ... ... ... go install -ldflags "-X github.com/pulumi/pulumi-lsp/sdk/version.Version=v0.2.3-6-gec49054" ./cmd/... mkdir -p editors/emacs/bin cd editors/emacs && emacs -Q --batch --eval "(progn (setq package-user-dir \"$(pwd)/bin\" \ package-archives '((\"melpa\" . \"https://melpa.org/packages/\") \

emacs29 intelephense

Emacs 29 and PHP Intelephense I use to use Emacs and PHP Intelephense for PHP development. I recently upgraded to Emacs 29 and PHP Intelephense stopped working. I found a solution on Reddit Based on that, I rewrote my .emacs file to use eglot instead of lsp-mode, and this is the result. (use-package eglot :ensure t) (add-hook 'php-mode-hook 'eglot-ensure) (use-package php-mode :ensure t :mode ("\\.php\\'" . php-mode)) (add-to-list 'auto-mode-alist '("\\.php$" . php-mode)) (provide 'lang-php) (use-package company :ensure t :config (setq company-idle-delay 0.3) (global-company-mode 1) (global-set-key (kbd "M- ") 'company-complete)) (require 'eglot) (add-to-list 'eglot-server-programs '((php-mode :language-id "php") . ("intelephense" "--stdio" :initializationOptions (:licenseKey "98989898989898989898"