Coding out of procrastination

$baseUrl = "http://localhost:8080"

$outputFile = "salesexport.csv"

$fields = "Manufacturer,ProductName,RetailPrice,ProductCode"

$fieldsArray = $fields.Split(',')

$startAt = 0;

$pagingSize = 100

# Build your Index in RavenDB - best to do your hard work there.

# script  supports flat indexes so we use fields in query to ensure it pulls

# from Lucene rather than a Lucene to DocID lookup - and again much faster

[System.Reflection.Assembly]::Load("System.Web.Extensions, Version=3.5.0.0," `

+ "Culture=neutral, PublicKeyToken=31bf3856ad364e35")

$serializer = New-Object System.Web.Script.Serialization.JavaScriptSerializer

$serializer.MaxJsonLength = [System.Int32]::MaxValue # probably too laxed

$webClient = New-Object System.Net.WebClient

#clear the file and write the columns

#using ascii since Excel presumes CSV and has problem open .csv directly when BOM 

#present. if you need unicode use utf8, save file as .txt and use wizard

[String]::Join(",",($fieldsArray | %{ ('"' + $_ + '"')})) | Out-File -Encoding ascii $outputFile

do {

$indexUrl = "$baseUrl/indexes/Shop/Brochure?start=$startAt&pageSize=$pagingSize&" `

    + [string]::Join("&",($fieldsArray | % { "fetch=$_" } ))

$response = $webClient.DownloadString($indexUrl)

$jsonDic = $serializer.DeserializeObject($response)

$results = $jsonDic["Results"]

$script:totalResults =$results.Length

foreach($result in $results)

{

# ravendb includes the __document_id in the ouput as the last key so 

# we select only fields we specify by using a select -first

[string]::Join(",", (% { $result.Keys } | select -first $fieldsArray.Length |`

% { ('"' + $result[$_].Replace(",","\,") + '"') } )) `

| Out-File -Encoding ascii $outputFile -Append

# if you want custom mapping just use $result[column-name] to build your own

}

$startAt = $startAt + $pagingSize

} while ( $script:totalResults -ne 0 )

# requires curl on PATH - if you installed mysysgit with it added to all PATHS then

# you should be okay, otherwise download from http://curl.haxx.se 

# presume integrated security edit connection otherwise

$sqlserver = ".\SQLEXPRESS" 

$db = "DATAVENDOR_CAR"

$baseUrl = "http://localhost:8080"

# first value selected must be the doc id - ideally create dedicated view instead

$sqlQuery =  "SELECT CarId, Manufacturer, Model," `

  + "Title, BodyStyle, Doors, CO2, Trim, DriveTrain, Transmission," `

  + "FuelType, EngineSize, ManufacturerRetailPrice, SourceUpdated FROM CarView"

$connection_string = ("Data Source=$sqlserver; Initial Catalog=$db;Integrated Security=SSPI")

$conn = New-Object System.Data.SqlClient.SqlConnection($connection_string)

# newtonsoft.json created weird errors 

[System.Reflection.Assembly]::Load("System.Web.Extensions, Version=3.5.0.0, Culture=neutral,

 PublicKeyToken=31bf3856ad364e35")

$serializer = New-Object System.Web.Script.Serialization.JavaScriptSerializer

$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText = $sqlQuery

$reader = $cmd.ExecuteReader()

$derivativeIds = New-Object 'System.Collections.Generic.List[string]'

while ($reader.Read()) {

    $docid = $reader.GetValue(0)

    $url = ([System.Uri]("$baseUrl/docs/" + $docid)).AbsoluteUri.ToString();

    # build a list of SET operations - either update or create property

    # only affects properties existly set in the operations, does not overwrite 

    # whole document. 

    $listOfSetOperations = New-Object 'System.Collections.Generic.List

[System.Collections.Generic.Dictionary[string,object]]'

    for ($i = 1; $i -lt $reader.FieldCount; $i = $i +1)    { 

        $dic = New-Object 'System.Collections.Generic.Dictionary[string,object]'

        $dic.Add("Type","set")

        $dic.Add("Name",$reader.GetName($i))

        $dic.Add("Value",$reader.GetValue($i))

        $listOfSetOperations.Add($dic)

     }

    $jsonPayLoad = $serializer.Serialize($listOfSetOperations).Replace("`"","'")

    $response = curl -w '%{HTTP_CODE}' -X PATCH $url "-d $jsonPayLoad"

    # doc does not exist so lets insert it. I go this route rather than a single update-or-insert PUT 

    # because if the doc already exists on the ravendb it will have additional properties associated with

    # that have been added/modified since (i.e. new properties dont exist in this sql source db)

    if($response -eq '404')  {

        $dictionary = New-Object 'System.Collections.Generic.Dictionary[string,object]'

        for ($i = 0; $i -lt $reader.FieldCount; $i = $i +1)    {

            $dictionary.Add($reader.GetName($i),$reader.GetValue($i))

         }

        $dictionarySerialized = $serializer.Serialize($dictionary).Replace("`"","'") 

        curl -X PUT $url "-d $dictionarySerialized"

    }

}