Szablon do sql kursora

Pewne rzeczy nie można przeskoczyć - takie jak kursor w sql server. Często szukam przykładów z użyciem kursora, więc postanowiłem zamieścić szablon.

DECLARE @v_name  NVARCHAR(MAX)
   
DECLARE v_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
  select * from ....

OPEN v_cursor
WHILE 1=1
BEGIN 
  FETCH NEXT FROM v_cursor INTO @v_name
  IF @@fetch_status = 0   
  BEGIN
   Begin TRY
    .....
   End TRY
   Begin CATCH 
    PRINT Error_Message()
    BREAK
   End CATCH
  END
  ELSE BREAK 
End
CLOSE v_cursor
DEALLOCATE v_cursor

W SSMS przydałby się edytor snippetów. Może kiedyś będzie Resharper dla t-SQL'a :)

Liczenie w SQL Server za pomocą CTE i ROW_NUMBER

Czasami proste rzeczy są lepsze, ale żeby te proste rzeczy znaleźć to potrzeba czasu.
Aby policzyć od 1 do 10000 to będziemy mogli wykorzystać pseudorekurencja, która opisałem wcześniej na blogu.

with cte (row)
 as (
SELECT 1
UNION all
SELECT c.row+1 FROM cte c
where c.row < 10000

 )
 select * from cte
 OPTION (MAXRECURSION 10000)

Ale wykorzystanie funkcji ROW_NUMBER jest dużo lepszym sposobem:

SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY s1.[object_id]) as row
FROM sys.all_objects AS s1 
CROSS JOIN sys.all_objects AS s2​

Dekompozycja projektu w R

Robiłem prezentacje i zastanawiałem się jak można zapisać cały projekt. Szukałem po sieci jakiś dobrych praktyk do dekompozycji plików. Ja wykombinowałem podział całego projektu na kilka plików:

Co w tych plikach jest? Zaczynając od początku:
Install.r - instalacja pakietów (ten skrypt wykonujemy tylko raz, aby pobrać paczki)
Functions.r - wszystkie funkcje
Load.r - ładowanie pakietów oraz przygotowywanie danych do prezentacji
Presentation.rpres - warstwa prezentacji, równie dobrze możne być to plik w Rmd czy z użyciem shiny (powód istnienia tego pliku jest TYLKO wyświetlanie tych danych jakie chcemy przedstawić, nie ma logiki biznesowej).
custom.css - customizacja warstwy prezentacji, czyli overridowane i dodatkowe css'y

I na końcu w folderze figures mam wszystkie zdjęcia.
Prezentacja o najpopularniejszych kryzysach ekonomicznych nie jest skończona. Jeszcze dużo chciałoby się dodać. Zaletą rpres jest to, że za każdym razem możemy mieć aktualne dane na prezentacji.
Poniżej rezultat wygenerowanej prezentacji:

UnGroup w PS

Ungroup to alias, jakiego mi brakowało w PowerShellu. Bardzo często grupuje obiekty, ale nie ma bezpośredniej możliwości odgrupowania tej kolekcji. Poniższy kod przedstawia tą możliwość:

function ungroup-object 
{
    process {
        $_ | %{$_.Group | %{$_} }
    }
}

Odgrupowanie wygląda jak by brało udział w konkursie na najbardziej nieczytelny kod w PS:)

Stwórzmy alias do funkcji ungroup-object:

New-Alias -Name 'ungroup' -Value 'ungroup-object' -Description 'Ungroup object which where grouped'​

Przykładem takiego odgrupowania może być wyświetlenie wszystkich definicji aliasów, które maja więcej niż jeden alias:

Get-Alias | group definition | ? {$_.Count -gt 1} | ungroup | sort definition | select name, definition | Out-GridView

Okazało się że takich aliasów mam z 53. Poniżej zamieszczona jest część wyników:

Help dla oracla w PS

Co najbardziej mnie denerwuje w oraclu to ilość błędów jakie dostaje ze względu na infrastrukturę lub złożone ustawienia oracla. Natomiast te błędy są całkiem dobrze udokumentowane m.in dzięki portalowi ora-code. Napisałem skrypcik do pobierania informacji o błędach z takiej strony. Skrypt znajduje się w module PS do Oracle, przy którym ostatnio rozwijam.
Skrypt jest podany poniżej:

function Get-OracleHelp
{
    param(
    [ValidateNotNullOrEmpty()]
    [Alias('code')]
    [string]$errorCode
    )

    [ref]$errorNumber=0
    $isNumber = [int]::TryParse($errorCode, $errorNumber)
    if($isNumber)
    {
        $errorCode = 'ORA-{0:00000}' -f $errorNumber.Value
    }

    $errorCode = $errorCode.ToUpper()

    $url = "http://{0}.ora-code.com/" -f $errorCode


    $request = Invoke-WebRequest -Uri $url  -UseDefaultCredentials

    $table = $request.ParsedHtml.body.getElementsByTagName('table')

    $trs = $table | %{$_.getElementsByTagName('tr')} | ? {$_.getAttributeNode('valign').value -eq 'top' }

    $desc=''
    $cause=''
    $action=''

    $trs | %{
        [string]$text = $_.innerText

         if( $text.StartsWith($errorCode, 'CurrentCultureIgnoreCase'))
         {
            $desc = $text.Remove(0, $errorCode.Length+1)
         }
 
         if( $text.StartsWith('Cause', 'CurrentCultureIgnoreCase'))
         {
            $cause = $text.Remove(0, 'Cause'.Length+1)
         }
         
         if( $text.StartsWith('Action', 'CurrentCultureIgnoreCase'))
         {
            $action = $text.Remove(0, 'Action'.Length+1)
         }
    }
    
    return  new-object PSObject `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Code' -Value $errorCode `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Description' -Value $desc `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Cause' -Value $cause `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Action' -Value $action `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Url' -Value $url 
}

Wywołanie wygląda następująco:

Get-OracleHelp -errorCode ORA-01243

I dostajemy taki opis błędu:

Oprócz całego kodu błedu to można wpisać tylko numer błędu:

Get-OracleHelp -code 12154

Dynamiczne parametry funkcji w PS ( dynamicParam )

ValidateSet jest fantastycznym atrybutem do parametrów funkcji. Umożliwia podpowiadanie argumentów oraz nie dopuszcza zmienianie wartości. Ma jednak jedną wadę. dozwolone wartości musza być stałymi przedefiniowanymi. Jeżeli chcielibyśmy zwalidować dane wejściowe, które ilość oraz wartości są zmienne to nie mamy takiej możliwości. Z pomocą przychodzi nam dynamiczny parametr. Całkowicie zmienia składnie funkcji. Przykład wywołania dynamicznego parametru jest poniżej. Przypuśćmy, że mamy hashtable z dozwolonymi nazwami oraz ich wartościami

$dparamColl = @{
param1 = 'test';
param2 = 'abc';
param3 = 'xyz'
}

function function_name
{
    param(
     $param
   )

    dynamicParam {

       $attributes = new-object System.Management.Automation.ParameterAttribute
       $attributes.ParameterSetName = "__AllParameterSets" 
       $attributes.Mandatory = $true
       $attributeCollection =      new-object -Type System.Collections.ObjectModel.Collection[System.Attribute]
       $attributeCollection.Add($attributes)
       $_Values  = $dparamColl.Keys
       $ValidateSet =      new-object System.Management.Automation.ValidateSetAttribute($_Values)
       $attributeCollection.Add($ValidateSet)
       $dynParam1 = new-object -Type System.Management.Automation.RuntimeDefinedParameter( "dparam", [string], $attributeCollection)
       $paramDictionary = new-object -Type System.Management.Automation.RuntimeDefinedParameterDictionary
       $paramDictionary.Add("dparam", $dynParam1)
       return $paramDictionary
    }

    begin{}
    process{
      return $dparam
    }
    end{}
}

Można stworzyć osobną funkcję, która będzie zwracała RuntimeDefinedParameterDictionary z dynamicznymi parametrami:

function Get-DynamicParam
{
    param(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [array]$paramSet, 
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$paramName)

        $attributes = new-object System.Management.Automation.ParameterAttribute
        $attributes.ParameterSetName = "__AllParameterSets" 
        $attributes.Mandatory = $true
        $attributeCollection =      new-object -Type System.Collections.ObjectModel.Collection[System.Attribute]
        $attributeCollection.Add($attributes)
        $_Values  = $paramSet
        $ValidateSet =      new-object System.Management.Automation.ValidateSetAttribute($_Values)
        $attributeCollection.Add($ValidateSet)
        $dynParam1 = new-object -Type System.Management.Automation.RuntimeDefinedParameter( $paramName, [string], $attributeCollection)
        $paramDictionary = new-object -Type System.Management.Automation.RuntimeDefinedParameterDictionary
        $paramDictionary.Add($paramName, $dynParam1)
        return $paramDictionary
}

Dynamiczny parametr pierwszy raz zastosowałem jak pisałem moduł do Oracla. W jednej zmiennej przetrzymuje zapytania sql, a funkcja z parametrem dynamicznym wywołuje to zapytanie sql:

$sqlHelpQueryCollection = @{
DatabaseName = @'
select ora_database_name from dual
'@;

Instance = @'
select * from v$instance
'@;
#and many more
}


function Invoke-OracleSqlHelpQuery
{
    [CmdletBinding()]
    param(
    $conn
    )
    dynamicParam {
        return Get-DynamicParam -paramSet $sqlHelpQueryCollection.Keys -paramName 'sqlHelpQuery'
    }

    begin{}
    process{
    $sql = $sqlHelpQueryCollection[$sqlHelpQuery]
    return Get-OracleDataTable -conn $conn -sql $sql
    }
    end{}
}

Efekt dynamicznego parametru jest przedstawiony poniżej:

Cały kod modułu jest na githubie.

PowerShell moduł do Oracle

Zacząłem pisać ps moduł do bazy danych w Oraclu. Kod źródłowy jest na githubie.

Na samym początku należy zainicjalizować moduł.

Load-OracleAssemblyes  'path_to_oracle_dataacces_dir\Oracle.DataAccess.dll'

Będziemy mogli przeglądać hasła oraz przeglądać połączenia w TNS:

$secPass, $pass = Get-Password

Get-TnsOracleConnectionString | Out-GridView

Można pobrać połączenie z app.configu oraz sprawdzić wersję servera:

$connectStr = Get-ConfigConnectionString 'path_to_app_dir\App.config' DatabaseNr1

Get-OracleServerVersion $connectStr

A wywoływanie zapytania SQL jest bardzo proste. Poniżej jest przykład zapisania do csv wszystkich obiektów w bazie danych, które stworzyliśmy dla schematu 'my':

$conn = new-OracleConnection $connectStr
$conn.Open()
$dbObj =Get-OracleDataTable -conn $conn -sql "select * from all_objects where owner like 'my' order by last_ddl_time desc"
$conn.Close()

$dbObj | Export-Csv -Path 'LastChangedObjects.csv'

Oczywiście można wywołać polecenie z pliku, gdzie znajduję się zapytanie sql:

Get-OracleDataTable -file file.sql -conn $conn

Gdy będziemy przeglądać rekordy w bardzo dużej tabeli to będziemy mogli uzyskać wyjątek z brakiem pamięci:

Błąd powyższy odnosi się do braku pamięci:
#out-lineoutput : Exception of type 'System.OutOfMemoryException' was thrown.
# + CategoryInfo : NotSpecified: (:) [out-lineoutput], OutOfMemoryException
# + FullyQualifiedErrorId : System.OutOfMemoryException,Microsoft.PowerShell.Commands.OutLineOutputCommand

Wtedy zamiast ładować wszystko do adaptera to można odczytywać z DataReader'a:

$reader = Get-OracleDataReader  -conn $conn -sql "select * from all_objects"

while($reader.Reader())
{
 # operacja na reader
}

Dodatkową funkcjonalnością jest porównywanie tabel dla różnych baz danych. Poniżej jest przykład sprawdzenia czy na 2 bazach są takie same tabele z takim samym właścicielem (owner) i z taką samą ilością rekordów:

$connStr1 = 'Data Source=...'
$connStr2 = 'Data Source=...'

$conn1 = New-OracleConnection  $connStr1
$conn2 = New-OracleConnection  $connStr2

$allTables1  = Get-OracleSystemTables -conn $conn1 -SystemTable ALL_TABLES
$allTables2  = Get-OracleSystemTables -conn $conn2 -SystemTable ALL_TABLES

Compare-Object 
($allTables1 | select TABLE_NAME,OWNER,NUM_ROWS)  
($allTables2 | select TABLE_NAME,OWNER,NUM_ROWS)  -IncludeEqual 

Pseudorekurencja w Oracle

Spróbowałem moich sił w oracle w wywołaniach rekurencyjnych. Skorzystałem z klauzury WITH. Tak na prawdę nie są to wywołanie rekurencyjne. Poniżej przedstawiam przykład wyświetlania wartości silni:

with 
Factorial (n,fact) as (
select 
0 as n,
1 as fact
from dual

union all

select 
n +1, (n+1)*fact
from Factorial
where n < 20
)
select * from Factorial 
;

A tutaj przykład wyliczenia liczby ruchów z postaci jawnej dla problemu wieży Hanoi:

with 
hanoi  (n,counts) as (
select 
1 as n,
1 as counts
from dual

union all

select 
n +1, POWER(2,n+1) -1
from hanoi 
where n < 20
)
select * from hanoi  
;

Wśród standardowych przykładów rekurencji zawsze musi wystąpić ciąg Fibonacciego. Zapytanie wyświetlające liczbę oraz wartość fibonacciego dla tej liczb, wygląda następująco:

with 
fibonacci (n,fib, fibadd) as (
select 
0 as n,
0 as fib,
1 as fibadd
from dual

union all

select 
n +1, fibadd, (fib + fibadd)
from fibonacci
where n < 100
)

select n,fib from fibonacci 
;

Jak pewnie zauważyłeś, to nie tworzysz modelu rekurencyjnego z warunkami stopu, ale model iteracyjny z warunkami początkowymi.

Produktywne makra Visual Studio

Chcę Ci przedstawić 2 makra, które pomagają mi w pisaniu kodu. Pierwsze makro dodaje nawiasy okrągłe do zaznaczonego tekstu w edytorze. Super się sprawdza makro dla kodu w PowerShell czy dla FSharp. Poniższy kod wystarczy zapisać w makrze VS:

Public Module BracketIt
    Public Sub AddBrackets()
        Dim s As Object = DTE.ActiveWindow.Selection()
        If s.Text.StartsWith("(") And s.Text.EndsWith(")") Then
            s.Text = s.Text.Substring(1, s.Text.Length - 2)
        Else
            s.Text = "(" + s.Text + ")"
        End If
    End Sub
End Module

Skrótem klawiszowym do wywołania tego makra mam ustawione na:
Shift+CTRL+A, Shift+CTRL+B,
Równie dobrze można było dodać nawiasy ostrokątne do edycji plików xml.
Drugim makrem jest asercja sprawdzająca czy wartość jest nullem:

Public Module AddAssert
    Public Sub IsNotNull()
        Dim s As Object = DTE.ActiveWindow.Selection()
        Dim assertStr As String = "Assert.IsNotNull"

        If s.Text.Length > 0 Then
            s.Text = assertStr + "(" + s.Text + ")"
        Else
            s.Text = assertStr
        End If
    End Sub
End Module

A skrótem klawiszowym jest:
Shift+CTRL+A, Shift+CTRL+A
Te 2 makra u mnie się sprawdzają. Jeśli masz swój pomysł na makro to podziel się nim.

Count lines of code

CLOC to tool do wyliczenia liczby plików różnych formatów oraz wylicza ile jest tych linijek kodu oraz komentarzy w tych plikach.Jest to tool, bardzo prosty w działaniu. Zacząłem pisać wrapper na PowerShella.
Pierwsza funkcja już jest:

function Get-Loc
{
    param($location="."
)
    $commandLine = "$location --csv"
    $clocResult = & $clocPath $location --csv
    $indexOfLineCsvToParse = [array]::IndexOf($clocResult, "")
    if($indexOfLineCsvToParse -lt 0)
    {
        throw "cant get empty line in cloc result"
    }
    $csvToparse = $clocResult[$indexOfLineCsvToParse..($clocResult.Length)]
    $csvFeed = ConvertFrom-Csv $csvToparse -Delimiter ',' | select  files,language,blank,comment,code  

    return  $csvFeed | %{
        return New-Object PSObject  -prop @{
                                    Files = [int]($_.files);
                                    Language = $_.language;
                                    Blank = [int]($_.blank);
                                    Comment = [int]($_.comment);
                                    Code = [int]($_.code);
                               }
                    } 
}

Aby wyświetlić statystykę projektów to wystarczy wywołać:

 Get-Loc "C:\ProjectDir"

Skrypt jest dostępny w github'a.