SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

RavenDB – NoSQL Datenbank - Tutorial

Ich habe in den letzten Tagen ein Wenig mit der NoSQL (übersetzt mit “not only sql”) Datenbank “RavenDB” herumgespielt. RavenDB ist eine Dokument-Datenbank. Das bedeutet in diesem Fall, dass die Daten nicht in Form von relationalen Tabellen, sondern in Form von JSON-Dokumenten gespeichert werden. In dieser Datenbank können Objekte “einfach so” ohne kompliziertes Mapping gespeichert werden.

Einige Vorteile bzw. Eigenarten von Dokument-basierten Datenbanken sind:

  • die DB muss kein festgelegtes Schema haben, da alles in Dokumenten gespeichert wird – die Dokumente an sich bilden das Schema
  • es muss kein Mapping von Klassen auf Datenbankobjekte vorgenommen werden
  • Performance-Vorteile beim schreibenden Zugriff, da die Konsistenz innerhalb der Datenbank nicht immer gewährleistet sein muss
  • große Datenbestände können auf mehrere kleine Server verteilt werden

Zu finden ist RavenDB unter http://ravendb.net/. Lizenziert wird die Datenbank “dual”, also frei für OpenSource-Projekte und kostenpflichtig für kommerzielle closed-source Software.

Einführung

Nun aber zur Datenbank an sich: Die Datenbank kann grundsätzlich in zwei verschiedenen Modi betrieben werden – embedded in der eigenen Anwendung oder als Server. Unterschied sind die einzubindenden Referenzen und die Einrichtung der Verbindung zur Datenbank. Wichtig zu wissen ist, dass die Embedded-Variante nur mit Anwendungen, die für das .NET Framework 4 kompiliert werden, funktioniert. Die Client-Server Variante funktioniert auch mit .NET 3.5. Die entpackte RavenDB hat diese Verzeichnisstruktur:

image

Um nun den Server zu starten genügt das Starten der Raven.Server.Exe im Verzeichnis “Server”. Der Server ist dann unter http://localhost:8080 zu erreichen und wartet mit einer kleinen Administrationsoberfläche unter der man sich die gespeicherten Dokumente anschauen kann, Statistiken einsehen kann und Map/Reduce Indizes anlegen kann – aber dazu später mehr.

image

RavenDB – Hello World Skiverleih – ein kleines Tutorial

Nun erst einmal zu einem kleinen “Hello-World” mit RavenDB. Nehmen wir an, wir betreiben einen Skiverleih (diesmal keine Autovermietung :-)) und wollen unsere Ski in der Datenbank ablegen und dazu noch Bewertungen der Ski unserer Kunden…

Also zunächst ein neues Visual Studio Projekt geöffnet, ich nehme hier eine Konsolenanwendung und den RavenDB-Client aus dem Verzeichnis Client eingebunden.

image

Die Ski werden durch die Klasse Ski repräsentiert.

public class Ski
{
    public string Id { get; set; }
    public decimal VerleihpreisTag { get; set; }
    public decimal Anschaffungspreis { get; set; }
    public List<Bewertung> Bewertungen { get; set; }
    public int LaengeInCm { get; set; }
    public bool Gewachst { get; set; }

    public Ski()
    {
        Bewertungen = new List<Bewertung>();
    }

    public double Durchschnittswertung
    {
        get
        {
            if (Bewertungen.Count > 0)
                return Bewertungen.Average(x => x.Wertung);
            else
                return 0;
        }
    }

}

Für die Datenbank und das “Wiederfinden” bestimmter Ski ist es notwendig, dass die Klasse ein Property mit dem Namen “Id” besitzt. Für die Erstellung von IDs in RavenDB gibt es drei Möglichkeiten:

  • explizites Festlegen – also bspw. durch mySki.Id = “1”
  • automatische Generierung – wird der Klasse kein Id-Property “spendiert”, dann generiert RavenDB beim Einfügen automatisch eine ID (GUID), wenn es also nicht wichtig ist konkrete Ski anhand ihrer ID wiederzufinden, kann auf die Id verzichtet werden
  • teilautomatische Generierung – die Id kann bei allen Instanzen der Ski-Klasse auf bspw. “ski/” gesetzt werden. Das veranlasst RavenDB dazu die Ids aufsteigend zu generieren, also “ski/1” für den ersten Ski, “ski/2” für den zweiten, etc.

Im Beispiel werde ich die Ids manuell vergeben.

Nun gibt es noch die Klasse Bewertung, die eine Wertung und einen Kommentar des Skifahrers enthält.

public class Bewertung
{
    public double Wertung { get; set; }
    public string Kommentar { get; set; }
}

Innerhalb der Ski-Klasse werden alle Bewertungen zu einem Ski als List<Bewertung> gespeichert und ein Property Durchschnittswertung errechnet den Durchschnitt aller Bewertungen des Ski.

Um nun Daten in RavenDB zu speichern, muss zuerst ein DocumentStore angelegt, instanziert und initialisiert werden:

//Initialisierung
var ds = new DocumentStore() { Url = "http://localhost:8080" };
ds.Initialize();

Hier genügt es die URL, unter der die Datenbank läuft anzugeben. Möchte man den Embedded Client verwenden, kann hier auch das Datadirectory angegeben werden, unter dem die Daten gespeichert werden sollen.

Nun erstelle ich ein paar Ski mit Bewertungen:

//ein Ski mit zwei Bewertungen
var mySki = new Ski();
mySki.Id = "Ski/1";
mySki.VerleihpreisTag = 11.0m;
mySki.Anschaffungspreis = 399.99m;
mySki.LaengeInCm = 170;
mySki.Gewachst = true;            
mySki.Bewertungen.Add(new Bewertung() { Wertung = 5, Kommentar = "Super Ski!" });
mySki.Bewertungen.Add(new Bewertung() { Wertung = 2, Kommentar = "Genial am Hang!" });

//noch ein Ski mit einer Bewertung ungewachst
var mySki2 = new Ski();
mySki2.Id = "Ski/2";
mySki2.VerleihpreisTag = 9.0m;
mySki2.Anschaffungspreis = 299.99m;
mySki2.LaengeInCm = 120;
mySki2.Gewachst = false;
mySki2.Bewertungen.Add(new Bewertung() { Wertung = 1, Kommentar = "Doof!" });

//noch ein Ski mit einer Bewertung ungewachst
var mySki3 = new Ski();
mySki3.Id = "Ski/3";
mySki3.VerleihpreisTag = 9.0m;
mySki3.Anschaffungspreis = 299.99m;
mySki3.LaengeInCm = 120;
mySki3.Gewachst = false;
mySki3.Bewertungen.Add(new Bewertung() { Wertung = 2, Kommentar = "Blöd!" });

Wie angekündigt habe ich die IDs manuell vergeben. Würde ich nun einen weiteren Ski mit der Id “Ski/” anlegen wäre seine Id automatisch “Ski/4”.

Speichern in der Datenbank

Zum Speichern der Ski in der Datenbank genügt folgender Code:

//Ski speichern
using (var session = ds.OpenSession())
{
    session.Store(mySki);
    session.Store(mySki2);
    session.Store(mySki3);
    session.SaveChanges();                
}

Hier wird eine Session mit der Datenbank geöffnet, die Ski gespeichert und SaveChanges() aufgerufen. SaveChanges() entspricht in etwa einem COMMIT in einer SQL-Datenbank.

Die gespeicherten Ski können nun auch direkt in der Datenbank angezeigt werden. Also http://localhost:8080 und Klick auf “Documents” ergibt folgendes Bild:

image

Die drei Ski sind also gespeichert – soweit so gut :-) Schauen wir uns einmal den Ski mit der ID “Ski/1” genauer an, um den Aufbau innerhalb der Datenbank zu sehen:

image

Der Ski wird also in einem Dokument inkl. aller Bewertungen gespeichert. Unter Document Metadata sind noch die von RavenDB vergebenen Metadaten enthalten. Interessant hier ist der “Raven-Entitiy-Name”, der als Plural der Klasse benannt ist.

image

Ändern in der Datenbank

Um nun an einzelnen Ski, wieder im Beispiel “Ski/1” heranzukommen, um mit diesem weiter arbeiten zu können bzw. um die Daten zu verändern ist folgender Code notwendig:

//einen Ski lesen und ändern
using (var session = ds.OpenSession())
{
    //lesen
    var myDbSki = session.Load<Ski>("Ski/1");
    
    //ändern
    myDbSki.VerleihpreisTag = 10.0m;

    //speichern
    session.Store(myDbSki);
    session.SaveChanges();
}

Also wieder eine Session öffnen, den Ski per Load<Ski>("<Id_des_Ski>”) laden, verändern und per Store() und SaveChanges() wieder in der Datenbank speichern.

Löschen eines Ski

Soll nun ein Ski wieder aus der Datenbank gelöscht werden, muss dieser zunächst gelesen werden, um eine Verknüpfung zu einer existierenden Instanz herzustellen.

//einen Ski löschen
using (var session = ds.OpenSession())
{
    session.Delete<Ski>(session.Load<Ski>("Ski/2"));
    session.SaveChanges();
}

Soweit erst einmal zu den Grundfunktionen von RavenDB. Im nächsten Post werde ich die Indexierung via Map/Reduce und die Abfrage mehrerer Instanzen vorstellen.

SQL Server Version auslesen – Teil 2

Im letzten Beitrag SQL Server Version auslesen habe ich beschrieben, wie die Version des SQL Server ausgelesen werden kann. In den Kommentaren wurde gefragt, wie man nun vom Ergebnis von

EXEC xp_msver

…die so aussehen:

image

…auf einen einzelnen Wert, zum Beispiel die “ProductVersion” kommt.

Hier kann man genau so vorgehen, wie bei jeder anderen Prozedur aus der die zurückgegebenen Daten weiterverarbeitet werden sollen. Dazu benötigt man eine Tabelle, die exakt den selben Aufbau, wie das Ergebnis der Prozedur hat. In diese Tabelle können dann die Ergebnisse per INSERT INTO eingefügt werden. Danach kann mit den Werten aus der Tabelle weitergearbeitet werden und so z.B. hier die Produktversion ausgelesen werden.

Ergebnis wäre dann folgendes SQL-Skript:

-- temp Table
DECLARE @temp TABLE 
(
  [Index] int,
  Name VARCHAR(100),
  Internal_Value INT,
  Character_Value VARCHAR(1000)
)  

-- Ergebnis aus xp_msver speichern
INSERT INTO @temp
EXEC xp_msver

-- abfragen
select Character_Value from @temp
where Name = 'ProductVersion'
 

Und als Wert erhalte ich nur:

image

…und kann den Wert weiter verwenden.

Standard Verzeichnisse im SQL Server

Der SQL Server besitzt in seiner Konfiguration Standard-Verzeichnisse für die Daten-Dateien, Log-Dateien und auch für Backups. Werden diese in einem Skript benötigt, können diese folgendermaßen ausgelesen werden:

-- SQL Data Root
DECLARE @SQLDataRoot nvarchar(512) 
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\Setup'
,N'SQLDataRoot',@SQLDataRoot OUTPUT

SELECT
@SQLDataRoot SQLDataRoot -- Standard-Datenverzeichnis DECLARE @DefaultData nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData',@DefaultData OUTPUT
select
@DefaultData DefaultData -- Standard-Logfile-Verzeichnis DECLARE @DefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog'
,@DefaultLog OUTPUT
select
@DefaultLog DefaultLog -- Standard Backup-Verzeichnis DECLARE @DefaultBackup nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'
,@DefaultBackup OUTPUT
select
@DefaultBackup DefaultBackup

Sollte eines der Verzeichnisse nicht explizit gesetzt sein (sondern in der Standardeinstellung, wie bei Installation belassen), dann wird folgende Meldung zurückgegeben:

RegQueryValueEx() returned error 2, 'Das System kann die angegebene Datei nicht finden.'
Msg 22001, Level 1, State 1

Die Standardwerte für nicht explizit festgelegte Verzeichnisse sind:

  • DefaultData => SQLDataRoot + “\DATA”
  • DefaultLog => SQLDataRoot + “\LOG”
  • DefaultBackup => SQLDataRoot + “\Backup”

Nicht nur auslesen, auch Setzen kann man diese Werte per Skript (außer das SQLDataRoot):

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'C:\data' GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'C:\Log' GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'C:\Backup'

Wer hat wann ein Backup wiederhergestellt?

Ganz einfache Frage und die Antwort ist auch schnell gefunden:

SELECT restore_date, 
       rh.user_name, 
       destination_database_name, 
       *
FROM msdb..backupset bs
INNER JOIN msdb..restorehistory rh on bs.backup_set_id = rh.backup_set_id
--where r.destination_database_name = 'DatenbankName'
ORDER BY rh.restore_date DESC

SQL Server Version auslesen

Um schnell mal auszulesen, welche Version des SQL Server eigentlich installiert ist gibt es mehrere Möglichkeiten. Einerseits (und am schnellsten getippt) ist das Statement:

SELECT @@VERSION

image

Andererseits kann man die Abfrage auch über die Produkteigenschaften machen:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

…gibt als Ergebnis die einzelnen Werte zurück:

image

Die letzte (hier beschriebene Möglichkeit) ist eine interne Prozedur, die noch einiges mehr an Zusatzinformationen zum installierten SQL Server und auch zur Windows-Version etc zurückgibt:

EXEC xp_msver

…Ergebnis:

image

Hier geht es zu Teil 2 des Artikels: Teil 2

Oberflächen gestalten mit balsamiq mockups

Bei der Gestaltung von (Programm-)Oberflächen kommt es meist nicht darauf an, diese beim Entwurf schon pixelgenau und farbgetreu zu erstellen. Die Gestaltung per Visio ist zwar ganz nett, aber IMHO auch recht umständlich, wenn es darum geht “mal einen Überblick” zu bekommen. Für solche Fälle gibt es das Tool mockups von balsamiq.

image

Dieses Tool bietet knapp 40 Elemente für das Design einer Software/Website – dabei ist alles von einfachen Buttons über Eine Fülle von Icons bis hin zu TagClouds. Unter http://www.balsamiq.com/products/mockups gibt es auch ein Video und eine Demoversion, die direkt im Browser läuft. Das Tolle ist: OpenSource Entwickler erhalten die Desktop-Software kostenlos! http://www.balsamiq.com/products/mockups/desktop#free

SQL CLR Integration – Test

Eine kurze Erweiterung zum letzten Artikel: siehe [hier]

Möchte ich die erstellte Funktion testen und debuggen, dann kann ich das auch direkt aus dem Visual Studio heraus. Dazu gibt es im erstellten Projekt eine bereits vorhandene Test.sql, die dazu dienen soll eigene Funktionen etc. auszuführen, zu testen und zu debuggen.

image

Mit Rechtsklick auf die Test.sql und “Set as default debug script” wird dieses Skript immer beim Debuggen ausgeführt.

image

Nun kann ich im Skript meine Funktion ausführen und ein paar Tests durchführen. Beispiel:

IF dbo.fDNSCheck('MyDNSName') = 1
PRINT 'OK'
IF dbo.fDNSCheck('NotMyDNSName') = 0
PRINT 'OK'  

Dazu muss auf dem SQL Server allerdings der Remote Debugger installiert sein (msvsmon.exe). Weitere Informationen dazu gibt es [hier]Außerdem müssen einige Berechtigungen als Voraussetzung vorhanden sein (siehe ebenso der Link). Sind alle Voraussetzungen vorhanden, dann kann per F5 das Skript gestartet werden, es können Haltepunkte im .NET Code gesetzt werden etc.

SQL CLR Integration - .NET Code im SQL Server

Der SQL Server bietet die Möglichkeit CLR-Code direkt auszuführen und somit erweiterte Funktionen zu erfüllen. Ein kleines Beispiel: Ich möchte prüfen, ob mein SQL Server auf einen bestimmten DNS-Namen im Netzwerk hört. Dazu ist mir mit Bordmitteln keine Möglichkeit bekannt.

Zunächst einmal muss die CLR-Integration aktiviert werden.

sp_configure 'clr_enable', 1
RECONFIGURE

Ergebnis ist:

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jetzt können wir loslegen. Ich erstelle im Visual Studio ein neues Datenbankprojekt und nenne es DNSChecker:

image

Im darauf folgenden Dialog muss eine Verbindung zu einer Datenbank hergestellt werden (die Datenbank, in der die Assembly letztendlich liegen soll und aus der sie ausgeführt wird).

image

Nachdem die Verbindung eingetragen ist, habe ich über einen Rechtsklick auf das Projekt und “Add” die Möglichkeit verschiedene Objekte zu erstellen:

image

Die einzelnen Möglichkeiten sind:

  • User-Defined Function – Eine Funktion mit Eingabeparametern und einem Ausgabeparameter
  • Stored Procedure – Ähnlich einer T-SQL Prozedur
  • Aggregate – Eine Aggregatfunktion, wie SUM(), AVG() etc.
  • Trigger – Ein CLR Trigger
  • User-Defined Type – Ein eigener “Datentyp”

Für das Beispiel beschränke ich mich auf das Erstellen einer Funktion, die mir nach Eingabe eines DNS-Namens zurückgibt, ob mein Server auf diesen Namen hört, oder nicht. Also => User-Defined Function

Nach Erstellen der Funktion erhalte ich einen bereits funktionsfähigen Rumpf mit einer Art “Hello World”.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fDNSCheck()
{
// Put your code here
return new SqlString("Hello");
}
};

Würde ich die Funktion nun aufrufen würde ich ganz einfach “Hello” als Ergebnis erhalten. Ich möchte allerdings “wahr” oder “falsch” als Bit-Wert zurückgeben und benötige als Eingabeparameter noch einen string (den DNS-Namen)

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
// Put your code here
return new SqlBoolean(false);
}
};

 

 

Nun fehlt noch die eigentliche Überprüfung, ob der Server auf den Namen hört.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
bool result = false;
try
{
//Get all ips for DNS-Name
IPHostEntry ip = Dns.GetHostEntry(sDnsName);
IPAddress[] IpA = ip.AddressList;
//check if a local ip for this name exists
foreach (IPAddress ipDNS in IpA)
{
foreach (IPAddress ipLocal in Dns.GetHostEntry(Dns.GetHostName()).AddressList)
{
if (ipDNS.ToString() == ipLocal.ToString())
{
result = true;
}
}
}
}
catch (Exception ex)
{
result = false;
}
return new SqlBoolean(result);
}

Dazu iteriere ich über alle für den DNS-Namen gefundenen IP-Adressen und vergleiche mit allen auf dem Server gefundenen IP-Adressen. Wird eine Übereinstimmung gefunden wird “true” zurück gegeben. Nun ist die Funktion fertig und könnte arbeiten. Da die Funktion allerdings die Berechtigungsstufe “unsafe” benötigt (näheres siehe unter http://technet.microsoft.com/de-de/library/ms131071.aspx) muss die Datenbank in der diese laufen soll die Eigenschaft “TRUSTWORTHY ON” haben. Also…

ALTER DATABASE CLRTestDB SET TRUSTWORTHY ON

…und für die Assembly muss eingestellt werden, dass der Zugriff in der Stufe “unsafe” erfolgen soll (in den Projekteigenschaften).

image

Nun kann ich die Assembly per “Deploy” auf dem Server installieren. Führe ich die Funktion nun aus und mein Server hört z.B. auf den Namen “MyDNSName”, dann gibt die Funktion “1” für “wahr” zurück.

image

Dieses kleine Beispiel hat gezeigt, wie einfach es ist den SQL Server mit eigenen .NET Funktionen zu erweitern.

yUML – UML Diagramme tippen statt zeichnen

Habe soeben den Dienst “yUML” entdeckt => http://yuml.me/. Auf dieser Seite kann man mit einer einfachen Beschreibungssprache UML Klassendiagramme, Aktivitätsdiagramme und Use Case Diagramme zeichnen.

Aus dem Text:

[Fortbewegungsmittel|VMax;AnzahlPassagiere|Fortbewegen()]^[Auto],[Fortbewegungsmittel]^[Fahrrad],[Fortbewegungsmittel]^[Fuß]

…wird beispielsweise dieses Diagramm:

image

und aus:

[BlogLeser]-(Kommentieren)
[Admin]-(Beitrag schreiben)
[Admin]-(Login).jpg

wird:

image

Ziemlich cool!