Home About Eric Topics SourceGear

2014-09-08 12:00:00

An F# newbie using SQLite

Like I said in a tweet on Friday, I'm guessing everybody's first 10,000 lines of F# are crap. That's a lot of bad code I need to write, so I figure maybe I better get started.

This blog entry is a journal of my first attempts at using F# to do some SQLite stuff. I'm using SQLitePCL.raw, which is a Portable Class Library wrapper (written in C#) allowing .NET developers to call the native SQLite library.

My program has five "stanzas":

I've got three implementations of this program to show you -- one in C# and two in F#.

C#

Here's the C# version I started with:

using System;
using System.IO;

using SQLitePCL;

public class foo
{
    // helper functions to check SQLite result codes and throw

    private static bool is_error(int rc)
    {
        return (
                (rc != raw.SQLITE_OK)
                && (rc != raw.SQLITE_ROW)
                && (rc != raw.SQLITE_DONE)
           );
    }

    private static void check(int rc)
    {
        if (is_error(rc))
        {
            throw new Exception(string.Format("{0}", rc));
        }
    }

    private static void check(sqlite3 conn, int rc)
    {
        if (is_error(rc))
        {
            throw new Exception(raw.sqlite3_errmsg(conn));
        }
    }

    private static int checkthru(sqlite3 conn, int rc)
    {
        if (is_error(rc))
        {
            throw new Exception(raw.sqlite3_errmsg(conn));
        }
        else
        {
            return rc;
        }
    }

    // MAIN program

    public static void Main()
    {
        sqlite3 conn = null;

        // ONE: open the db and create the table

        check(raw.sqlite3_open(":memory:", out conn));

        check(conn, raw.sqlite3_exec(conn, "CREATE TABLE foo (a int, b int)"));

        // TWO: insert 16 rows

        for (int i=1; i<=16; i++)
        {
            string sql = string.Format("INSERT INTO foo (a) VALUES ({0})", i);
            check(conn, raw.sqlite3_exec(conn, sql));
        }

        // THREE: set b = a squared and find b for a=7

        check(conn, raw.sqlite3_exec(conn, "UPDATE foo SET b = a * a"));

        sqlite3_stmt stmt = null;
        check(conn, raw.sqlite3_prepare_v2(conn, "SELECT b FROM foo WHERE a=?", out stmt));

        check(conn, raw.sqlite3_bind_int(stmt, 1, 7));
        check(conn, raw.sqlite3_step(stmt));
        int vsq = raw.sqlite3_column_int(stmt, 0);
        check(conn, raw.sqlite3_finalize(stmt));
        stmt = null;

        // FOUR: fetch sum(a) for all rows where b < 120

        check(conn, raw.sqlite3_prepare_v2(conn, "SELECT a FROM foo WHERE b<120", out stmt));

        int vsum = 0;

        while (raw.SQLITE_ROW == (checkthru(conn, raw.sqlite3_step(stmt))))
        {
            vsum += raw.sqlite3_column_int(stmt, 0);
        }
        
        check(conn, raw.sqlite3_finalize(stmt));
        stmt = null;

        // FIVE: close and print the results

        check(raw.sqlite3_close(conn));
        conn = null;

        Console.WriteLine("val: {0}", vsq);
        Console.WriteLine("sum: {0}", vsum);
    }
}

Notes:

F#, first attempt

OK, now here's a painfully direct translation of this code to F#:

open SQLitePCL

// helper functions to check SQLite result codes and throw

let is_error rc = 
    (rc <> raw.SQLITE_OK) 
    && (rc <> raw.SQLITE_ROW) 
    && (rc <> raw.SQLITE_DONE)

let check1 rc = 
    if (is_error rc) 
    then failwith (sprintf "%d" rc) 
    else ()

let check2 conn rc = 
    if (is_error rc) 
    then failwith (raw.sqlite3_errmsg(conn)) 
    else ()

let checkthru conn rc = 
    if (is_error rc) 
    then failwith (raw.sqlite3_errmsg(conn)) 
    else rc

// MAIN program

// ONE: open the db and create the table

let (rc,conn) = raw.sqlite3_open(":memory:") 
check1 rc

check2 conn (raw.sqlite3_exec (conn, "CREATE TABLE foo (a int, b int)"))

// TWO: insert 16 rows

for i = 1 to 16 do 
    let sql = (sprintf "INSERT INTO foo (a) VALUES (%d)" i)
    check2 conn (raw.sqlite3_exec (conn, sql ))

// THREE: set b = a squared and find b for a=7

check2 conn (raw.sqlite3_exec (conn, "UPDATE foo SET b = a * a"))

let rc2,stmt = raw.sqlite3_prepare_v2(conn, "SELECT b FROM foo WHERE a=?")
check2 conn rc2

check2 conn (raw.sqlite3_bind_int(stmt, 1, 7))
check2 conn (raw.sqlite3_step(stmt))
let vsq = raw.sqlite3_column_int(stmt, 0)
check2 conn (raw.sqlite3_finalize(stmt))

// FOUR: fetch sum(a) for all rows where b < 120

let rc3,stmt2 = raw.sqlite3_prepare_v2(conn, "SELECT a FROM foo WHERE b<120")
check2 conn rc3

let mutable vsum = 0

while raw.SQLITE_ROW = ( checkthru conn (raw.sqlite3_step(stmt2)) ) do 
    vsum <- vsum + (raw.sqlite3_column_int(stmt2, 0))

check2 conn (raw.sqlite3_finalize(stmt2))

// FIVE: close and print the results

check1 (raw.sqlite3_close(conn))

printfn "val: %d" vsq
printfn "sum: %d" vsum

Notes:

F#, somewhat less csharpy

Here's an F# version I called fsharp_less_bad.fs. It's still not very good, but I've made an attempt to do some things in a more F#-ish way.

open SQLitePCL

// helper functions to check SQLite result codes and throw

let is_error rc = 
    match rc with
    | raw.SQLITE_OK -> false
    | raw.SQLITE_ROW -> false
    | raw.SQLITE_DONE -> false
    | _ -> true

let check1 rc = 
    if (is_error rc) 
    then failwith (sprintf "%d" rc) 
    else ()

let check2 conn rc = 
    if (is_error rc) 
    then failwith (raw.sqlite3_errmsg(conn)) 
    else rc

let checkpair1 pair =
    let rc,result = pair
    check1 rc |> ignore
    result

let checkpair2 conn pair =
    let rc,result = pair
    check2 conn rc |> ignore
    result

// helper functions to wrap method calls in F# functions

let sqlite3_open name = checkpair1 (raw.sqlite3_open(name))
let sqlite3_exec conn sql = check2 conn (raw.sqlite3_exec (conn, sql)) |> ignore
let sqlite3_prepare_v2 conn sql = checkpair2 conn (raw.sqlite3_prepare_v2(conn, sql))
let sqlite3_bind_int conn stmt ndx v = check2 conn (raw.sqlite3_bind_int(stmt, ndx, v)) |> ignore
let sqlite3_step conn stmt = check2 conn (raw.sqlite3_step(stmt)) |> ignore
let sqlite3_finalize conn stmt = check2 conn (raw.sqlite3_finalize(stmt)) |> ignore
let sqlite3_close conn = check1 (raw.sqlite3_close(conn))
let sqlite3_column_int stmt ndx = raw.sqlite3_column_int(stmt, ndx)

// MAIN program

// ONE: open the db and create the table

let conn = sqlite3_open(":memory:")

// use partial application to create an exec function that already 
// has the conn parameter baked in

let exec = sqlite3_exec conn

exec "CREATE TABLE foo (a int, b int)"

// TWO: insert 16 rows

let ins x = 
    exec (sprintf "INSERT INTO foo (a) VALUES (%d)" x)

[1 .. 16] |> List.iter ins

// THREE: set b = a squared and find b for a=7

exec "UPDATE foo SET b = a * a"

let stmt = sqlite3_prepare_v2 conn "SELECT b FROM foo WHERE a=?"
sqlite3_bind_int conn stmt 1 7
sqlite3_step conn stmt
let vsq = sqlite3_column_int stmt 0
sqlite3_finalize conn stmt

// FOUR: fetch sum(a) for all rows where b < 120

let stmt2 = sqlite3_prepare_v2 conn "SELECT a FROM foo WHERE b<120"

let vsum = List.sum [ while 
    raw.SQLITE_ROW = ( check2 conn (raw.sqlite3_step(stmt2)) ) do 
        yield sqlite3_column_int stmt2 0 
    ]

sqlite3_finalize conn stmt2

// FIVE: close and print the results

sqlite3_close conn

printfn "val: %d" vsq
printfn "sum: %d" vsum

Notes:

Other notes

I did all this using the command-line F# tools and Mono on a Mac. I've got a command called fsharpc on my system. I'm not sure how it got there, but it probably happened when I installed Xamarin.

Since I'm not using msbuild or NuGet, I just harvested SQLitePCL.raw.dll from the SQLitePCL.raw NuGet package. The net45 version is compatible with Mono, and on a Mac, it will simply P/Invoke from the SQLite library that comes preinstalled with MacOS X.

So the bash commands to setup my environment for this blog entry looked something like this:

mkdir fs_sqlite
cd fs_sqlite
mkdir unzipped
cd unzipped
unzip ~/Downloads/sqlitepcl.raw_needy.0.5.0.nupkg 
cd ..
cp unzipped/lib/net45/SQLitePCL.raw.dll .

Here are the build commands I used:

fs_sqlite eric$ gmcs -r:SQLitePCL.raw.dll -sdk:4.5 csharp.cs

fs_sqlite eric$ fsharpc -r:SQLitePCL.raw.dll fsharp_dreadful.fs
F# Compiler for F# 3.1 (Open Source Edition)
Freely distributed under the Apache 2.0 Open Source License

fs_sqlite eric$ fsharpc -r:SQLitePCL.raw.dll fsharp_less_bad.fs
F# Compiler for F# 3.1 (Open Source Edition)
Freely distributed under the Apache 2.0 Open Source License

fs_sqlite eric$ ls -l *.exe
-rwxr-xr-x  1 eric  staff   4608 Sep  8 15:30 csharp.exe
-rwxr-xr-x  1 eric  staff   8192 Sep  8 15:30 fsharp_dreadful.exe
-rwxr-xr-x  1 eric  staff  11776 Sep  8 15:31 fsharp_less_bad.exe

fs_sqlite eric$ mono csharp.exe
val: 49
sum: 55

fs_sqlite eric$ mono fsharp_dreadful.exe 
val: 49
sum: 55

fs_sqlite eric$ mono fsharp_less_bad.exe 
val: 49
sum: 55

BTW, I noticed that compiling F# (fsharpc) is a LOT slower than compiling C# (gmcs).

Note that the command-line flag to reference (-r:) an assembly is the same for F# as it is for C#.

Note that fsharp_dreadful.exe is bigger than csharp.exe, and the "less_bad" exe is even bigger. I suspect that generalizing these observations would be extrapolating from too little data.

C# fans may notice that I [attempted to] put more effort into the F# code. This was intentional. Making the C# version beautiful was not the point of this blog entry.

So far, my favorite site for learning F# has been http://fsharpforfunandprofit.com/

 

Copyright 2001-2023 Eric Sink. All Rights Reserved

If you are in need of contract software development services, I would appreciate your consideration of SourceGear.