Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

F# SQLProvider+SQLite on the Raspberry Pi

DZone's Guide to

F# SQLProvider+SQLite on the Raspberry Pi

In this post, we’ll create an SQLite database and an F# project that reads from and writes to it on a Windows PC and a Raspberry Pi.

· IoT Zone
Free Resource

Address your IoT software testing needs – improve quality, security, safety, and compliance across the development lifecycle.

fspi3

Both the SQLite database engine and the Raspberry Pi are lean, mean little things, so they are a perfect match for each other. Using F# and SQLProvider under Mono, we get a high level and convenient way to tap into the low cost power of the Pi (though everything in this post should work on anything else running a Debian based Linux).

In this post, we’ll create an SQLite database and an F# project that reads from and writes to it, on a Windows PC. Once we’ve got that running, we’ll get it running on a Raspberry Pi as well (I’ll assume that you’ve got Raspbian up and running on a Pi).

Creating the database

Without further ado, let’s get started by setting up the F# project and creating the database.

  1. Fire up Visual Studio on a Windows PC, create an F# console application and call it “SQLProviderDemo”.
  2. Compile the empty application. This is just to make Visual Studio create the output directory SQLProviderDemo\SQLProviderDemo\bin\Debug.
  3. SQLProvider generates types from an existing database, so let’s create one. Start an SQLite manager (if you don’t have one, I recommend SQLite Studio which is FOSS) and create a database with the name “TopGeek.sqlite3” in the SQLProviderDemo\SQLProviderDemo\bin\Debug directory. This is one of the worst places imaginable to keep a database, but it will suffice for this demo.
  4. Open an SQL Editor in your SQLite manager and run this DDL script to create the “TuringAwardRecipient” table:
    CREATE TABLE TuringAwardRecipient (
        RecipientId TEXT PRIMARY KEY NOT NULL,
        Name        TEXT NOT NULL,
        YearAwarded INT  NOT NULL
    );

Setting up the F# Project for SQLite Access

  1. Add the EntityFramework NuGet package to the project. Although it’s included in System.Data.SQLite, install it separately to avoid the irritating “Couldn’t uninstall EntityFramework.6.0.0” NuGet error.
  2. Add the System.Data.SQLite and SQLProvider NuGet packages.
  3. This next step is rather annoying, but for now we’ll have to do it anyway: Exit Visual Studio and copy the x64 and x86 subdirectories under SQLProviderDemo\packages\System.Data.SQLite.Core.<version>\build\net46 as described on this SQLProvider documentation page.
  4. Start Visual Studio again and we’re all set to start coding.

The Code

First of all, we’ll use SQLProvider to generate our database types:

open FSharp.Data.Sql

let [] designTimeConnectionString = 
    "Data Source=" +
    __SOURCE_DIRECTORY__ + "/bin/Debug/TopGeek.sqlite3;Version=3;foreign keys=true"

let connectionString = "Data Source=TopGeek.sqlite3;Version=3;foreign keys=true"
    
type Sql = SqlDataProvider< 
                ConnectionString = designTimeConnectionString,
                DatabaseVendor = Common.DatabaseProviderTypes.SQLITE,
                SQLiteLibrary = Common.SQLiteLibrary.SystemDataSQLite,
                IndividualsAmount = 1000,
                UseOptionTypes = true >

Note that we’re going to use System.Data.SQLite under Mono instead of Mono.Data.SQLite. To enable this, I’ve added the SQLiteLibrary parameter to SQLProvider (thanks to Tuomas and the SQLProvider team for accepting the addition!). Used as above, it explicitly specifies that System.Data.SQLite should be used regardless of platform.

Thanks to SQLProvider, querying the database becomes a piece of cake:

let ctx = Sql.GetDataContext(connectionString)

let listRecipients (ctx: Sql.dataContext) = 
    query {
        for recipient in ctx.Main.TuringAwardRecipient do
        sortBy recipient.Name
        select (recipient.Name, recipient.YearAwarded)
    } 
    |> Seq.iter (fun (name, year) -> printfn "%-20s %d" name year)

This query isn’t too exciting when executed on an empty database, so we’ll use SQLProvider’s CRUD functions to add some rows of computer science legends to our table:

let addRecipients (ctx: Sql.dataContext) =
    let recipients = [
        ("JMC", "John McCarthy",      1971)
        ("EWD", "Edsger W. Dijkstra", 1972)
        ("EFC", "Edgar F. Codd",      1981)
    ]
    for (id, name, year) in recipients do
        let row = ctx.Main.TuringAwardRecipient.Create()
        row.RecipientId <- id
        row.Name        <- name
        row.YearAwarded <- year
    ctx.SubmitUpdates()

For good measure, we’ll clean up after our demo by deleting all rows:

let deleteAll (ctx: Sql.dataContext) = 
    for i in ctx.Main.TuringAwardRecipient do i.Delete()
    ctx.SubmitUpdates()

This makes us just about ready to take our code for a spin on the PC, before we move on to deploying it on the Raspberry Pi. All we need is a little main function, so here’s the code in its entirety including main:

open FSharp.Data.Sql
 
let [<Literal>] designTimeConnectionString = 
    "Data Source=" + 
    __SOURCE_DIRECTORY__ + "/bin/Debug/TopGeek.sqlite3;Version=3;foreign keys=true"
let connectionString = "Data Source=TopGeek.sqlite3;Version=3;foreign keys=true"
    
type Sql = SqlDataProvider< 
                ConnectionString = designTimeConnectionString,
                DatabaseVendor = Common.DatabaseProviderTypes.SQLITE,
                SQLiteLibrary = Common.SQLiteLibrary.SystemDataSQLite,
                IndividualsAmount = 1000,
                UseOptionTypes = true >
 
 
let addRecipients (ctx: Sql.dataContext) =
    let recipients = [
        ("JMC", "John McCarthy",      1971)
        ("EWD", "Edsger W. Dijkstra", 1972)
        ("EFC", "Edgar F. Codd",      1981)
    ]
    for (id, name, year) in recipients do
        let row = ctx.Main.TuringAwardRecipient.Create()
        row.RecipientId <- id
        row.Name        <- name
        row.YearAwarded <- year
    ctx.SubmitUpdates()
 
 
let deleteAll (ctx: Sql.dataContext) = 
    for i in ctx.Main.TuringAwardRecipient do i.Delete()
    ctx.SubmitUpdates()
 
 
let listRecipients (ctx: Sql.dataContext) = 
    query {
        for recipient in ctx.Main.TuringAwardRecipient do
        sortBy recipient.Name
        select (recipient.Name, recipient.YearAwarded)
    } 
    |> Seq.iter (fun (name, year) -> printfn "%-20s %d" name year)
 
 
[<EntryPoint>]
let main argv = 
    let ctx = Sql.GetDataContext(connectionString)
    printfn "Adding data..."
    addRecipients ctx
    printfn "\nTuring Award Recipients:"
    listRecipients ctx
    printfn "\nDeleting data..."
    deleteAll ctx
    0 // return an integer exit code

Running the code should produce this output:

Adding data...

Turing Award Recipients:
Edgar F. Codd        1981
Edsger W. Dijkstra   1972
John McCarthy        1971

Deleting data...

Running on the Raspberry Pi

Running the above code on the Pi should just be a question of typing sudo apt-get install mono-complete, copying the Visual Studio output directory, SQLProviderDemo\SQLProviderDemo\bin\Debug, to the Pi and running SQLProviderDemo.exe, right? Well, not so fast, because our ambitions are a little higher than that.

pi3j

A small and frugal container for your F# applications.

First of all, we want a more recent version of Mono than the more than one year old one (as of October 2016) that’s available in Raspbian’s repo. We also want something that’s more actively maintained than Mono.Data.SQLite, i.e. we want a System.Data.SQLite that runs under Mono. This is what we need to do:

  1. Add Mono’s repository
    The team behind Mono have graciously provided their own Debian/Raspbian compatible repo. To add it to our system, we open a terminal window on the Pi and follow the instructions under “Debian, Ubuntu, and derivatives” on the Mono documentation page.
  2. Install Mono
    In the terminal, type:
    sudo apt-get install mono-complete
    To determine the version of Mono, you can type “mono --version“. If the version is older than 4.6.1, the Mono repo probably wasn’t added correctly.
  3. Build System.Data.SQLite Interop under Linux
    Now, we need to follow the instructions in my previous blog post about building System.Data.SQLite Interop under Linux. There, you can also read the compelling reasons for taking the trouble of doing so.
  4. Copy SQLProviderDemo binaries to the Pi
    Create a directory on the Pi and copy all files in the SQLProviderDemo\SQLProviderDemo\bin\Debug output directory from the PC to the Pi (I’m using WinSCP for this).
  5. Copy System.Data.SQLite Interop
    In step 3, we built the library file libSQLite.Interop.so (see the previous blog post for file location). Copy it to the directory created in step 4.
  6. Run!
    All that’s left for us now, is to start the F# application with “mono SQLProviderDemo.exe” and enjoy the same output we got on the PC, albeit on a $35 computer consuming about 5W.

At first, the performance of SQLProviderDemo on the Pi might seem abysmally sluggish, but if we iterate the logic in the main function 10 times, we’ll see that the first iteration is notably slower than the subsequent nine ones. This leads suspicion towards the JIT compiler or module load times, which of course are of little concern once an application is up and running.

Given the Raspberry Pi’s popularity as a data logger and F#’s suitability for data analysis, the two of them look very appealing together.

Accelerate the delivery of high-quality software in the connected IoT era through an integrated analysis, testing, security, and analytics platform

Topics:
raspberry pi ,sqlite ,f# ,iot

Published at DZone with permission of Adam Granicz, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}