// Code from Hansen and Rischel: Functional Programming using F# 16/12 2012 // Chapter 11: Sequences Revised 12/2 2016 // Code from the Section 11.8: Creating the tables in the product register. // It is assumed that a suitable database ProductRegister with Part and PartList tables is created #if INTERACTIVE #r "FSharp.Data.TypeProviders.dll" #r "System.Data.dll" #r "System.Data.Linq.dll" #endif open System open Microsoft.FSharp.Data.TypeProviders open System.Data.Linq.SqlClient open System.Linq type schema = SqlDataConnection<"Data Source=IMM-NBMRH\\SQLEXPRESS; Initial Catalog=ProductRegister; Integrated Security=True">;; let db = schema.GetDataContext();; let partTable = db.Part;; let partsListTable = db.PartsList;; // Projection let q1 = query {for part in db.Part do select (part.PartName, part.IsBasic) };; // Selection let q2 = query {for part in db.Part do where (not part.IsBasic) select (part.PartId, part.PartName, part.IsBasic)};; // Join let q3 = query {for pl in db.PartsList do join part in db.Part on (pl.PartsListId = part.PartId) select(part.PartName, pl.PartId, pl.Quantity) };; type partListElement = {PartName:string; PartId:int; Quantity:int} let q4 = query {let qa = query {for pl in db.PartsList do join part in db.Part on (pl.PartsListId = part.PartId) select {PartName = part.PartName; PartId = pl.PartId; Quantity = pl.Quantity} } for pl in qa do join part in db.Part on (pl.PartId = part.PartId) select(pl.PartName, part.PartName, pl.Quantity) };; // Aggregate operations let nextId() = query {for part in db.Part do count };; let getDesc id = query {for part in db.Part do where (part.PartId=id) select (part.PartName,part.IsBasic) exactlyOne };; let containsPartId id = query {for part in db.Part do select part.PartId contains id };; // Example: Parts Break Down let getPartsList id = query {for pl in db.PartsList do where (pl.PartsListId = id) select (pl.PartId,pl.Quantity) };; let rec add pl (id,q) = match pl with | [] -> [(id,q)] | (id1,q1)::pl1 when id=id1 -> (id,q+q1)::pl1 | idq::pl1 -> idq :: add pl1 (id,q);; let mergePartsList pl1 pl2 = List.fold add pl1 pl2;; let mult k pl = List.map (fun (id,q) -> (id,k*q)) pl;; let rec partBreakDown id = match getDesc id with | (_,true) -> [(id,1)] | _ -> partsListBreakDown(Seq.toList(getPartsList id)) and partsListBreakDown = function | (id,q)::pl -> let pl1 = mult q (partBreakDown id) let pl2 = partsListBreakDown pl mergePartsList pl1 pl2 | [] -> [];; // Updating a database let addBasic s = let id = nextId() let part = new schema.ServiceTypes.Part(PartId = id, PartName = s, IsBasic = true) db.Part.InsertOnSubmit(part) db.DataContext.SubmitChanges() Some id;; let isWellFormed pl = List.forall (fun (id,k) -> containsPartId id && k>0) pl;; let addComposite s pl = if isWellFormed pl then let id = nextId() let part = new schema.ServiceTypes.Part(PartId=id, PartName=s, IsBasic = false) let partslist = List.map (fun (pid,k) -> new schema.ServiceTypes.PartsList(PartsListId=id, PartId=pid, Quantity=k)) pl db.Part.InsertOnSubmit(part) db.PartsList.InsertAllOnSubmit(partslist) db.DataContext.SubmitChanges() Some id else None;; // The two tables from Figure 11.1 are created as follows let id0 = Option.get (addBasic "Part0");; let id1 = Option.get (addBasic "Part1");; let id2 = Option.get (addComposite "Part2" [(id0,5);(id1,4)]);; let id3 = Option.get (addComposite "Part3" [(id1,3);(id2,4)]);;