==================
WER goes Datenbank
==================


Ausgangssituation
------------------

Bisher existierten in Ihrer Firma Powershell-Skripte, die eine Ausgabe verschiedenster Informationen
der WER-Reports über die Konsole möglich machen. Diese Informationen beziehen sich aber nur auf den jeweiligen Einzel-PC. Eine Aggregation von Informationen über alle Computer ist nicht möglich. Der Leiter der IT-Abteilung beschließt daher, ihr Skript des letzten Jahres als Ausgangspunkt für eine Erweiterung zu nehmen. Die Daten sollen nun in einer Mysql-Datenbank gespeichert werden.



Zur Information erhalten Sie vom IT-Leiter noch das folgende PS-Skript

.. only:: html

	.. sidebar:: Skript

		:download:`Skript <figure/werreports_10.ps1>`

		:download:`Bemerkungen <figure/bemerkungen.docx>`

.. admonition:: Aufgabe

	Analysieren Sie das dargestellte Skript **werreports_10.ps1**. Notieren Sie sich Bemerkungen.

.. image:: figure/notiz.png
	:width: 500px

.. only:: html

		.. sidebar:: Tafelbilder

			:download:`Gebraucht <figure/klasse_11/used_functions.jpg>`

			:download:`getReportData_Ablauf <figure/klasse_11/functionen_ablauf_und_daten_alt.jpg>`

			:download:`ArrayList_Benutzer <figure/klasse_11/BenuterArrayList_Verwendung_by_ref.jpg>`




Refaktoring
-----------

Die oben dargestellten Überlegungen führen zu Veränderungen des Quellcodes sowie zu einem ER-Modell, welche die Datenhaltung widerspiegeln könnte.


.. admonition:: Aufgabe

	Erstellen Sie ein ER-Modell bzw. Klassendiagramm.

.. image:: figure/notiz.png
	:width: 500px



.. only:: html

	.. sidebar:: Klassendiagramm / ERM

		:download:`KD <figure/klasse_11/kd1.png>`

		:download:`SQL <figure/klasse_11/kd1.sql>`

		:download:`PS1 <figure/klasse_11/reduz2_final.ps1>`

		:download:`Vergleich <figure/klasse_11/Vergleich_der_beiden_Skripte.docx>`





Umsetzung MySQL-Datenbank
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Nachdem die Daten nun in einer objektorientierten Struktur vorliegen, müsen diese nun anschließend in Datenbanktabellen gespeichert werden. Das zuvor erstellte SQL-Skript erzeugt eine leere Datenbankstruktur.

.. admonition:: Aufgabe

	Diskutieren Sie nochmals die Vor- und Nachteile des oben dargestellten Datenmodells

.. image:: figure/klasse_11/datamodell_1.png


Klassen
~~~~~~~

Da die Daten in Annäherung an das Datenmodell gehalten werden sollen, bietet es sich an, die Zsammenhänge mit Hilfe von Klassen zu modellieren.

Die many-Beziehung in der Klasse User zu den Reports wird mit Hilfe einer ArrayList abgebildet.
Die Klasse Computer erhält einen Konstruktor, der beim Erzeugen eines Objektes gleich die Werte für den Computer holt.
Dabei wird die bisher vorhandene Methode getOtherData/SetComputer wiederverwendet. Auch die Klasse Report erhält über einen Konstruktor die erforderlichen Daten. Die Klasse User erzeugt sich im Konstruktor einen neuen Computer und weist ihn der Referenz ps zu.

.. code-block:: sh

   class User
   {
     [string]$Name

     $Reports = [System.Collections.ArrayList]::New()
     [Computer]$pc

     User([string]$uname)
     {
        $this.Name = $uname
        $this.pc = [Computer]::new()
     }
   }

   class Report
	 {
     [string]$ReportID
     [int]$ReportType
     [string]$EventType
     [string]$EventTime
     [string]$BucketID
     [string]$Appname

     Report($repid, $repType, $evType, $evTime, $buckId, $appnam)
     {
       $this.ReportID = $repid
       $this.ReportType = $repType
       $this.EventType = $evTime
       $this.EventTime = $evTime
       $this.BucketID = $buckId
       $this.Appname = $appnam
     }
	 }


   class Computer
   {
     [string]$mac
     [string]$OpSys
     [string]$Name

     Computer()
     {
        $macadresse = get-wmiobject -class "Win32_NetworkAdapterConfiguration" | Where {$_.IpEnabled -Match "True"} | Select MacAddress
        $this.mac = $macadresse[0].MacAddress
        $this.OpSys = (Get-WmiObject Win32_OperatingSystem).Name
        $this.Name = (Get-WmiObject Win32_OperatingSystem).CSName
     }
   }


Anpassungen bestehender Funktionen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Die Funktionen GetReportInnerData sowie GetWERPath müssen nicht geändert werden.


**GetUsers**

Die Funktion muss nun User-Objekte liefern. Sie erwartet eine ArrayList, welche sie mit USer-Objekten füllt.

.. code-block:: sh

	function GetUsers($Benutzer)
	{
		<#

		.Synopsis

				Erstellt aus dem Ordnernamen unterhalb von C:\Users eigen-
				ständige User-Objekte und weist Sie einer ArrayList zu.

		#>

		$tempUser = Get-ChildItem "C:\Users" | Select-Object Name

		foreach($username in $tempUser)
		{
				[User]$u = [User]::New($username.Name)
			 	$Benutzer.Add($u)
		}
	}


**GetReportData**

Neben der verwendeten ArrayList für das Halten der User-Objekte wird die Zuweisung der Reports durch die Verwendung der Report-Klasse ersetzt. Am Ende hat man eine komplettes Objektmodell

.. code-block:: sh

	#Wegen Problemen mit Rückgabe von ArrayListen wird eine eigene erzeugt und der aufgerufenen Funktion übergeben.
	function GetReportData($Benutzer)
	{

		GetUsers $Benutzer

		foreach($_user in $Benutzer)
		{
			$paths = GetWERPath $_user.Name;
			foreach($_path in $paths) # $_users hier stehen alle benuter vom Rechner und werden nach $_user geschrieben in der Schleife
			{
				if($_path -ne $null)
				{
					$_reportid = GetReportInnerData $_path "ReportIdentifier";
					$_reporttype = GetReportInnerData $_path "ReportType";
					$_eventtype = GetReportInnerData $_path "EventType"; # z.B AppCrash
					$_eventtime = GetReportInnerData $_path "EventTime";
					$_bucketid = GetReportInnerData $_path "Response.BucketId";
					$_appname = GetReportInnerData $_path "AppName";

					[Report]$rep = [Report]::new($_reportid, $_reporttype, $_eventtype, $_eventtime, $_bucketid, $_appname)
					$_user.Reports += $rep
				}
			}
		}
		return $Benutzer
	}


Erweiterungen um datenbankspezifische Funktionen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Aus Vereinfachungsgründen werden die Datenbank-Funktionen mit in das Skript geschrieben. Es wäre besser, diese in eine eigene Datei auszulagern und diese in das Hauptskript mit einzubinden.

Zunächst wird die dll referenziert und Objekte wie Connection und Command erzeugt.
Die Funktionen open() und close() dienen zum Kapseln der Open() und Vlose()-Methode des Connection-Objektes.

Die Funktion saveData erhält die ArrayList aller Benutzerobjekte. Sie öffnet einmalig die Connection und geht dann in einer Schleife alle Userobjkete durch. Mit Hilfe von setUser wird dann zunächst eine USer in die Datenbank angelegt (siehe später mehr) und anschließend die dazugehörigen Report-Objekte gespeichert.

Die **setUser**-Funktion operiert mit dem replace-Befehl von mysql. Dieser stellt sicher, dass bereits bestehende User-Datensätze nicht neu hinzugefügt werden, sondern lediglich Änderungen umgesetzt werden (replace = delete und insert).

**setReports** erhält ein User-Objekt übergeben und geht mit Hilfe einer Schleife die Reports-Liste des User durch durch.
Sie erzeugt jeweils ein Insert-Statement. Das sie Zugriff auf die User-Eigenschaften hat, kann sie die MAC-Adresse des Computers sowie den Usernamen mit in das Statement aufnehmen.

Die **setComputer**-Funktion funktioniert analog zur setUser-Funktion.


.. code-block:: sh

	#mysql_model.ps1
	#Datenbankverbindung öffnen und Datenübertragung vorbereiten
	#Bibliothek zur Datenbankeinbindung einbinden
	[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.9\Assemblies\v4.0\MySql.Data.dll");

	#Skriptweite Variablen deklarieren
	#Connectionvariable
	$connstring = "Server=localhost;Uid=watson;Pwd='watson';Database=watson_11FI3"
	$con = New-Object Mysql.Data.MysqlClient.MysqlConnection;

	#Commandobjekt
	$command = New-Object MySql.Data.MySqlClient.MySqlCommand;

	function open()
	{
		#Funktion zum Öffnen der Datenbankverbindung
		try
		{
			$script:con.ConnectionString = $connstring;
			$con.Open();
			Write-Debug "Datenbankverbindung geöffnet"
		}
		catch
		{
			Write-Debug "Achtung Fehler: $_.ExceptionMessage"
			Write-Debug "Daten müssen später übertragen werden"
		}
	}


	function close()
	{
		#Funktion zum Schliessen der Datenbankverbindung
		try
		{
			$con.Close();
			Write-Debug "Datenbankverbindung geschlossen"
		}
		catch
		{
			Write-Debug "Achtung Fehler: $_.ExceptionMessage"
			Write-Debug "Daten müssen später übertragen werden"
		}
	}

	function saveData($Users)
	{
		open

		foreach($u in $Users)
		{
				write-debug $u.Name
				setUser $u
				setReports $u
		}

		close
	}


	function setUser($user)
	{
		$name = $user.Name
		[string]$sql = "replace into User(Anmeldename) values ('$name');"
		$command.CommandText = $sql
		$command.Connection = $con;
		$command.ExecuteNonQuery()
	}


  function setReports($u)
  {

    $m = $u.pc.mac
    $uname  = $u.Name

    foreach($rep in $u.Reports)
    {
        $repid = $rep.ReportID
        $repType = $rep.ReportType
        $evTime = $rep.EventTime
        $evType = $rep.EventType
        $buckID = $rep.BucketID
        $app = $rep.Appname

        [string]$sql = "insert into report(ReportID, Appname, EventTime, BucketID, ReportType, User, Computer) "
        $sql += "values('$repid', '$app', '$evTime', '$buckID', '$repType', '$uname', '$m');"

        $command.CommandText = $sql
        $command.Connection = $con;

        $command.ExecuteNonQuery()
    }
  }


	function setComputer([Computer]$c)
	{
		$m = $c.mac
		$sys = $c.OpSys
		$n = $c.Name

		[string]$sql = "replace into computer(MAC, OSName, HostName) values ('$m', '$sys','$n');"
		#[string]$sql = "Replace into computer values ($c.'mac', $c.'OpSys',$c.'Name');"

		$command.CommandText = $sql
		$command.Connection = $con;

		open
				$command.ExecuteNonQuery()
		close
	}


Ablaufsteuerung
~~~~~~~~~~~~~~~

Aufgrund der objektorientierten Struktur der Daten ergibt sich eine logische Abfolge des Einfügens der Daten.

Die **User**-Objekte enthalten nicht nur die Daten des ausgelesenen Benutzers, sondern referenzieren den jeweiligen Computer sowie alle Report-Objekte, die der jeweilige User auf dem Rechner hatte. Aufgrund der Foreign-Key-Beziehungen zwischen den Tabellen muss sichergestellt werden, dass neue User und Computer zunächst als Erstes in die DB geschrieben werden.
Um sich aufwendiges Abfragen zu sparen (ist der DS schon vorhanden ?), wird beim User und Computer ein **replace**-Staement verwendet.

Danach können  die Report-Datensätze mit den Informationen geschrieben werden.
Dieser Vorgang muss für jedes User-Objekt in der Users-Liste durchgeführt werden. Da davon auszugehen ist, dass das Skript immer nur auf einem Computer läuft, müssen die Computerdaten nur einmal geschrieben werden.


Folgende Abbildung veranschaulicht den Prozess des Einfügens.

.. image:: figure/klasse_11/Activity_InsertReport.png
	:width: 500px



.. only:: html

		.. sidebar:: Komplettes Skript

			:download:`Skript_final <figure/klasse_11/reduz2_final.ps1>`


Zum Ende des Skriptes erfolgt deshalb der Aufruf der Hauptfunktionen.
Zunächst holt man sich aller User mit zugehörigen Reports. Anschließend speichert/ersetzt man einmalig den Datensatz in der Tabelle Computer. Zuletzt speichert saveData() alle User sowie die dazugehörigen Reports.


.. code-block:: sh

	$Benutzer = [System.Collections.ArrayList]::New()
	GetReportData $Benutzer

	# untenstehende Routine könnte auch in der setUSer-Methode für jeden User ausgeführt werden
	# da das Skript auf nur auf einem Rechner läuft, kann man sich die wiederholten
	# Statements aber sparen
	setComputer ($Benutzer[0]).pc

	# und jetzt der ganze Rest
	saveData $Benutzer