2016-01-17 Exporting comments from Orchard CMS to import them into Disqus - Part 1

2016-01-17 exporting-comments-from-orchard-cms-to-import-them-into-disqus-part-1

# Title: Exporting comments from Orchard CMS to import them into Disqus - Part 1
# Author: Mikael Lundin
# Link: http://blog.mikaellundin.name/2016/01/17/exporting-comments-from-orchard-cms-to-import-them-into-disqus-part-1.html

Comments.sql

SELECT Comment.Id, Route.DisplayAlias, Title.Title, Comment.Author, Comment.SiteName, Comment.Email, Comment.CommentDateUtc, Comment.CommentText, Post.CreatedUtc, Body.Text
FROM Orchard_Comments_CommentPartRecord Comment 
INNER JOIN 
  (SELECT DISTINCT ContentItemRecord_id, Title FROM Title_TitlePartRecord) Title
  ON Comment.CommentedOn=Title.ContentItemRecord_id
INNER JOIN 
  (SELECT DISTINCT ContentItemRecord_id, DisplayAlias FROM Orchard_Autoroute_AutoroutePartRecord) Route
  ON Comment.CommentedOn=Route.ContentItemRecord_id
INNER JOIN
  (SELECT * FROM Common_BodyPartRecord WHERE Id IN (SELECT max(Id) FROM Common_BodyPartRecord GROUP BY ContentItemRecord_id)) Body
  ON Comment.CommentedOn=Body.ContentItemRecord_id
INNER JOIN
  Common_CommonPartRecord Post ON Comment.CommentedOn=Post.Id

dataContract.fs

[<DataContract(Name = "Comment", Namespace = "")>]
type Comment = {
  [<field: DataMember(Name = "Id")>]
  Id : int;
  [<field: DataMember(Name = "Author")>]
  Author: string;
  [<field: DataMember(Name = "SiteName")>]
  SiteName: string;
  [<field: DataMember(Name = "Email")>]
  Email: string;
  [<field: DataMember(Name = "CommentDateUtc")>]
  CommentDateUtc : string;
  [<field: DataMember(Name = "Text")>]
  Text: string;
  [<field: DataMember(Name = "PostTitle")>]
  PostTitle : string;
  [<field: DataMember(Name = "PostLink")>]
  PostLink : string;
  [<field: DataMember(Name = "PostSlug")>]
  PostSlug : string;
  [<field: DataMember(Name = "PostPublishUtc")>]
  PostPublishUtc : string;
  [<field: DataMember(Name = "PostText")>]
  PostText : string;
  }

[<DataContract(Name = "Comments", Namespace = "")>]
type Comments = Comment[]

getData.fs

// read data from sdf file and return Comments
// getData: string -> Comments
let getData databaseFile : Comments =
  // open a connection to the filename 
  use connection = new SqlCeConnection(@"Data Source=" + databaseFile)
  connection.Open() |> ignore

  // the query that will fetch all data we need from the Orchard database
  let query = @"SELECT Comment.Id, Route.DisplayAlias, Title.Title, Comment.Author, Comment.SiteName, Comment.Email, Comment.CommentDateUtc, Comment.CommentText, Post.CreatedUtc, Body.Text
                FROM Orchard_Comments_CommentPartRecord Comment 
                INNER JOIN 
                  (SELECT DISTINCT ContentItemRecord_id, Title FROM Title_TitlePartRecord) Title
                  ON Comment.CommentedOn=Title.ContentItemRecord_id
                INNER JOIN 
                  (SELECT DISTINCT ContentItemRecord_id, DisplayAlias FROM Orchard_Autoroute_AutoroutePartRecord) Route
                  ON Comment.CommentedOn=Route.ContentItemRecord_id
                INNER JOIN
                  (SELECT * FROM Common_BodyPartRecord WHERE Id IN (SELECT max(Id) FROM Common_BodyPartRecord GROUP BY ContentItemRecord_id)) Body
                  ON Comment.CommentedOn=Body.ContentItemRecord_id
                INNER JOIN
                  Common_CommonPartRecord Post ON Comment.CommentedOn=Post.Id";

  // execute the command
  use command = new SqlCeCommand(query, connection)
  use reader = command.ExecuteReader()

  seq {
    while (reader.Read()) do
      // extract every row in reader to a Comment record
      yield readComment reader
  } |> Seq.toArray

Model.fs

type Comment = {
  Id : int;
  Author: string;
  SiteName: string;
  Email: string;
  CommentDateUtc : string;
  Text: string;
  PostTitle : string;
  PostLink : string;
  PostSlug : string;
  PostPublishUtc : string;
  PostText : string;
}

type Comments = Comment[]

readComment.fs

// get data from a column or return default value for that type
// get<'T>: string -> SqlCeDataReader -> T
let get<'T> (column : string) (dataReader : SqlCeDataReader) =
  match dataReader.[column] with
  | :? System.DBNull
  | null -> Unchecked.defaultof<'T>
  | x -> x :?> 'T

// format date as GMT string
// dateString: DateTime -> string
let dateString (date : System.DateTime) =
  date.ToString("yyyy-MM-dd HH:mm:ss")

// format string as CDATA block
// cdata: string -> string
let cdata = sprintf "<![CDATA[%s]]>"

// format date and slug as link url
// link: (DateTime, string) -> string
let link (date : System.DateTime, slug) = 
  sprintf "http://blog.mikaellundin.name/%s/%s.html" (date.ToString("yyyy/MM/dd", System.Globalization.CultureInfo.InvariantCulture)) slug

// turn a data reader row into a Comment record
// readComment: SqlCeDataReader -> Comment
let readComment (dataReader : SqlCeDataReader) =
  {
    Id = dataReader |> get "Id"
    Author = dataReader |> get "Author"
    SiteName = dataReader |> get "SiteName"
    Email = dataReader |> get "Email"
    CommentDateUtc = dataReader |> get "CommentDateUtc" |> dateString
    Text = dataReader |> get "CommentText" |> cdata
    PostTitle = dataReader |> get "Title"
    PostLink = (dataReader |> get "CreatedUtc", dataReader |> get "DisplayAlias") |> link
    PostSlug = dataReader |> get "DisplayAlias"
    PostPublishUtc = dataReader |> get "CreatedUtc" |> dateString
    PostText = dataReader |> get "Text" |> cdata
  }

serialize.fs

// serialize Comments to Xml
let xmlSerialize (outputXmlFile : string) (data : Comments) =
  // open writer
  use writer = System.Xml.XmlWriter.Create(outputXmlFile)
  // create serializer
  let serializer = new System.Runtime.Serialization.DataContractSerializer(typedefof<Comments>)
  // write to file
  serializer.WriteObject(writer, data)