Migration.fs

migration.fs

#I __SOURCE_DIRECTORY__
#r @"..\packages\SQLite.Net-PCL\lib\portable-win8+net45+wp8+wpa81+MonoAndroid1+MonoTouch1\SQLite.Net.dll"
#r @"..\packages\SQLite.Net-PCL\lib\net40\SQLite.Net.Platform.Generic.dll"
#r @"..\packages\System.Data.SQLite.Core\lib\net46\System.Data.SQLite.dll"


open System
open System.IO
open System.Data.SQLite
open SQLite.Net
open SQLite.Net.Platform.Generic
open SQLite.Net.Interop
open System.Collections
open System.Text.RegularExpressions
open SQLite.Net.Attributes

let connect() =
    new SQLiteConnection(
        SQLitePlatformGeneric(),
        __SOURCE_DIRECTORY__ + "\x.db",
        SQLiteOpenFlags.Create ||| SQLiteOpenFlags.ReadWrite,
        storeDateTimeAsTicks = false)

type [<Table "notes"; CLIMutable>] NoteEntity = {
    [<Column "note_id"; PrimaryKey; AutoIncrement>] NoteId : int64
    [<Column "ni_no"; NotNull>]                     NiNo: string
    [<Column "user_id"; NotNull>]                   UserId : string
    [<Column "outcome_areas">]                      OutcomeAreas : string
    [<Column "content">]                            Content : string 
    [<Column "engagement_type">]                    EngagementType : string
    [<Column "engagement_category">]                EngagementCategory : string
    [<Column "activity_name">]                      ActivityName : string
    [<Column "activity_provider">]                  ActivityProvider : string
    [<Column "engagement_duration">]                EngagementDuration : int64
    [<Column "support_duration">]                   SupportDuration: int64
    [<Column "date"; NotNull>]                      Date : DateTime
}

type [<Table "old_notes"; CLIMutable>] Old_NoteEntity = {
    [<Column "note_id"; PrimaryKey; AutoIncrement>] NoteId : int64
    [<Column "ni_no"; NotNull>]                     NiNo: string
    [<Column "user_id"; NotNull>]                   UserId : string
    [<Column "outcome_areas">]                      OutcomeAreas : string
    [<Column "content">]                            Content : string 
    [<Column "engagement_type">]                    EngagementType : string
    [<Column "engagement_category">]                EngagementCategory : string
    [<Column "activity_name">]                      ActivityName : string
    [<Column "activity_provider">]                  ActivityProvider : string
    [<Column "activity_duration">]                  ActivityDuration : string
    [<Column "support_duration">]                   SupportDuration: string
    [<Column "date"; NotNull>]                      Date : DateTime
}

[<CLIMutable>]
type TimeSpans = {
    [<Column "note_id">] NoteId : int64
    [<Column "support_hours">] SupportHours : string
    [<Column "engagement_duration">] EngagementDuration: string
}

let parseHumanizedTimeSpan (str: string) =
    let rgx = new Regex("((?'h'[0-9]+)\shour\(s\))?\s?((?'m'[0-9]+)\sminute\(s\))?")
    let groups =
        (rgx.Match (str.Trim())).Groups
    let (h, m) = 
        groups.["h"].Value,  groups.["m"].Value

    (TimeSpan.FromHours(if h <> "" then float h else 0.)
     + TimeSpan.FromMinutes(if m <> "" then float m else 0.)).TotalMinutes

let getDurations() =
    using (connect ()) 
    <|  fun conn -> conn.DeferredQuery<TimeSpans>("select note_id, support_duration as support_hours, activity_duration as engagement_duration from old_notes") :> seq<_> |> Seq.toList

let parseDurations durations =
    durations
    |> List.map (fun d -> (d.NoteId, (parseHumanizedTimeSpan d.SupportHours, parseHumanizedTimeSpan d.EngagementDuration)))
    |> Map.ofList
    
let getAllNotes() =
    using (connect ()) 
    <|  fun conn -> conn.DeferredQuery<NoteEntity>("select * from old_notes") :> seq<_> |> Seq.toList

let createTableAndSeed() =
    using (connect ())
    <| fun conn -> conn.CreateTable(typeof<NoteEntity>) |> ignore
    
    let query = """INSERT INTO notes (note_id,
                               ni_no,
                               user_id, 
                               outcome_areas, 
                               content, 
                               engagement_type, 
                               engagement_category,
                               activity_name,
                               activity_provider,
                               date)
                  SELECT note_id,
                         ni_no, 
                         user_id, 
                         outcome_areas, 
                         content, 
                         engagement_type, 
                         engagement_category,
                         activity_name,
                         activity_provider,
                         date
                  FROM old_notes"""
    
    using (connect ())
    <| fun conn -> conn.Execute(query) |> ignore

let update() =
    let parsed = getDurations() |> parseDurations
    let notes = getAllNotes()

    using (connect ()) 
    <|  fun conn ->
        notes
        |> List.map(fun n -> 
            let (supportHours, engagementDuration) =  parsed.[n.NoteId]
            { n with SupportDuration = int64 supportHours; EngagementDuration = int64 engagementDuration })
        |> conn.UpdateAll
        |> ignore

createTableAndSeed()
update()