Det kan undertiden være nyttigt at skulle gemme filer i SQL Server.  Før SQL Server 2008 havde man den mulighed at gemme filer i en BLOB, dvs. man gemte filen binært i en kolonne af type VARBINARY.  Flere har talt imod denne løsning, fordi den – i hvert fald hvis man skal tro rygterne – kunne være ekstremt skadelig for performance.  Flere anbefalede i stedet en løsning, hvor filerne fysisk bliver gemt på disk på et fileshare, og man i stedet nøjes med at gemme filens placering i en kolonne på SQL Server.  Det er naturligvis en lidt skrøbelig løsning, fordi man altid skal huske at opdatere fileshare og database som ét.  Det kan gøres i en transaktion, men det er indiskutabelt en lidt besværlig løsning.

Til det formål introducerede SQL Server 2008 type FILESTREAM.  Med FILESTREAM gemmes filerne fysisk på disk et sted, som SQL Server administrerer, men al tilgang til filerne går gennem SQL Server med T-SQL.

FILESTREAM er som default ikke tilgængeligt på en nyinstalleret SQL Server, så der skal en lille smule arbejde til for at kunne benytte det.

Vha. ADO.NET kan man læse og skrive til en FILESTREAM kolonne.  Det er ikke svært, men det kræver lidt arbejde, hvor man skal have fat i mindre fashionable ting som SqlFileStream og GET_FILESTREAM_TRANSACTION_CONTEXT.  Desuden er der krav om, at både læsning og skrivning omkranses af en transaktion, som man selv er ansvarlig for at oprette.  Alt i alt kunne man godt ønske sig, at der var en lidt mere elegant måde at gøre det på.

Heldigvis findes der en sådan elegant løsning (ellers ville jeg ikke have noget at skrive om), og den findes i Entiry Framework.

Givet følgende tabel med en FILESTREAM kolonne,

   1:  CREATE TABLE Ad
   2:  (
   3:    AdId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY,
   4:    UserId UNIQUEIDENTIFIER NOT NULL,
   5:    Photo VARBINARY(MAX) FILESTREAM NULL,
   6:    Url NVARCHAR(512) NOT NULL,
   7:    ExpiryDate DATETIME2 NOT NULL
   8:  )

vil Entity Framework automatisk bl.a. generere en property Ad.Photo af typen byte[].  Bemærk i øvrigt, at en tabel, der benytter FIILESTREAM, skal have en ROWGUIDCOL kolonne.  Som i gamle dage er kolonnen med filen stadig af typen VARBINARY, men med et ekstra lille FILESTREAM bagefter, som sørger for al den filbaserede magi.

Med Entity Framework kan man nu gemme sine filer i SQL Server meget nemt ved blot at omsætte sin fil til et byte array:

   1:  using (var db = GetContext())
   2:  {
   3:      Stream fs = File.OpenRead(filePath);
   4:      using (var reader = new BinaryReader(fs))
   5:      {
   6:          var ad = new Ad()
   7:          {
   8:              // Set other properties here.
   9:              Photo = reader.ReadBytes((int) fs.Length)
  10:          };
  11:          db.Ad.AddObject(ad);
  12:          db.SaveChanges();
  13:      }
  14:  }

På tilsvarende vis kan filer læses meget simpelt.  Entity Framework sørger for alt det beskidte arbejde.

Mark Seemann skrev forleden et godt indlæg om de udfordringer, man møder, når man skal mappe DTO objekter til domain objekter og videre til viewmodel objekter.

Jeg er en af dem, der har forsøgt sig med at snyde og lade DTO objekter vandre op igennem forretningslaget, og det bliver hurtigt grimt. Bare det at DTO klasserne alle har default constructor gør, at vedligeholdelse af koden bliver en pine. Mark er inde på, at mapning mellem DTO objekter og domain objekter kan være svær, fordi ORM frameworks ikke understøtter mapping, når der ikke findes en default constructor og alle propeties ikke har en setter.

Jeg har haft nogenlunde succes med at bruge AutoMapper til det. Det viser sig, at AutoMapper er i stand til at mappe properties på source objektet til constructor parametre på destination objektet. Man skal bare sørge for, at parametre og properties hedder det samme (de behøver ikke have samme casing), hvilket i mine øjne i øvrigt er god ting at have i sin kodestandard alligevel (konsistent navngivning).

Lad os tage udgangspunkt i følgende klasser Track og DbTrack (tyvstjålet fra Mark’s indlæg):

   1:  public class Track
   2:  {
   3:      private readonly int id;
   4:      private string name;
   5:      private string artist;
   6:   
   7:      public Track(int id, string name, string artist)
   8:      {
   9:          if (name == null)
  10:              throw new ArgumentNullException("name");
  11:          if (artist == null)
  12:              throw new ArgumentNullException("artist");
  13:   
  14:          this.id = id;
  15:          this.name = name;
  16:          this.artist = artist;
  17:      }
  18:   
  19:      public int Id
  20:      {
  21:          get { return this.id; }
  22:      }
  23:   
  24:      public string Name
  25:      {
  26:          get { return this.name; }
  27:          set
  28:          {
  29:              if (value == null)
  30:                  throw new ArgumentNullException("value");
  31:   
  32:              this.name = value;
  33:          }
  34:      }
  35:   
  36:      public string Artist
  37:      {
  38:          get { return this.artist; }
  39:          set
  40:          {
  41:              if (value == null)
  42:                  throw new ArgumentNullException("value");
  43:   
  44:              this.artist = value;
  45:          }
  46:      }
  47:  }
  48:   
  49:  public class DbTrack
  50:  {
  51:      public int Id { get; set; }
  52:      public string Name { get; set; }
  53:      public string Artist { get; set; }
  54:  }

Track har ikke en default constructor og har setter properties for Name og Artist, men ikke for Id. AutoMapper kan – uden nogen egentlig konfiguration – benyttes til at mappe fra DbTrack til Track på følgende måde:

   1:  Mapper.CreateMap<DbTrack, Track>();
   2:   
   3:  var source = new DbTrack()
   4:  {
   5:      Id = 12,
   6:      Name = "Name12",
   7:      Artist = "PSB"
   8:  };
   9:   
  10:  var destination = Mapper.Map<Track>(source);

Kaldet til Mapper.Map i linje 10 vil først kalde constructoren på Track med de rigtige parametre bestemt ud fra parametrenes navne, og da Track klassen har setters på to properties, vil disse properties blive kaldt efterfølgende. Hvis Track klassen kun havde getters på alle properties, ville det naturligvis kun være constructoren, der blev kaldt. Hvis Track klassen har parametre i constructoren, som har navne, der ikke kan matches med properties på DbTrack klassen, vil AutoMapper fejle med en exception.

Jeg husker den glade sommer (husker dog ikke helt hvornår det var) hvor jeg lå i solen og prøvede at læse op til en SQL Server eksamen. Det lykkedes aldrig at komme forbi kapitlet om opbygningen af den fysiske filstruktur i databasen. Det hører jo med til en MS certificering, så den blev ikke til noget.

SQL Server er den database jeg har arbejdet mest med, men til mit seneste hobby webprojekt var det naturlige valg MySql. Pandasan var så flink at vise mig XAMPP som findes i en light udgave, hvilket var præcis det jeg skulle bruge til mit hygge project. Uden at skulle installere services kan man ha' MySql kørerende på sin lokale maskine på få minutter.

For et hobby website har MySql syntax en meget nyttig kommando LIMIT. Denne kommando alene har tidligere gjort at jeg har prøvet kræfter med MySql. Paging bliver et spørgsmål om man lige kan gange sidestørrelse med sidenummer og det bør være overkommelig matematik for en programør. MySql provideren til .Net findes på MySql's website og er dermed ikke en 3rd party komponent som det var for nogle år siden.

Det virker generelt som om der er sket en del med MySql siden jeg sidst så på det. Og man kan også spørge om Are Commercial Databases Worth It?. Star Wars sammenligninger giver altid letforståelige forklaringer. Jeg har ikke selv oplevet de store forbedringer i SQL Server for mine små projekter. Og t4rzsan har jo set lidt på Parametriserede queries vs. stored procedures, så den konto er også brugt selvom MySql har fået Stored Procedures for snart lang tid siden.

Betyder det så noget at Oracle har købt SUN og dermed også MySql? Jeg har i forbindelse med et kursus på ITU stødt på Oracle og det var ikke overbevist. Det virker unødigt komplekst for at sælge nogle konsulenttimer, hvilket jeg mener er at narre folk. Selvom der er andre firmaer som bygger deres forretning på denne model, er det jo ikke et argument for at gøre det selv.

Nok med brok, se at komme igang med MySql. XAMPP kan downloades som zip og køres næsten uden setup. Det er et værdigt alternativ til SQL Server (også Express).

Jeg har tidligere arbejdet i et firma hvor vi brugte mange soft-delete (eller som det hedder i salgsbrochuren - Fuldt revisionsspor), dvs. man sletter ikke i databasen, men markerer istedet rækken for slettet. I vores tilfælde blev slettetidspunktet markeret, så objektets tilstand kunne genskabes på forskellige valørdatoer. De første man lægger mærke til er naturligvis at databasen vokser hurtigt og at alle queries indeholder 'DeletionTime NOT NULL'. Det var praksis og påkrævet af revisorer.

Man kan mene mange ting om soft-delete og Frans Bouma mener at Soft-deletes are bad. Inden vi ser lidt nærmere på hans løsningsforslag, så lad mig give ham ret i at, hvis du ikke har revisorer på nakken, så bør du nok finde på en anden løsning end soft-deletes. Gammelt data er trods alt.. ja, gammelt. Jeg ved godt at vi lever i Google-tid og man bare kan søge igennem alt, men lader du Google indeksere dine business data?

Vi har allerede etableret et behov for at bevare data af juridiske grunde, men det er også værd at nævne at data forbliver slettet. Gammelt data bruges ikke til at "spole" objektets tilstand tilbage ved at "undelete" data, altså fjerne slettetidspunktet i databasen. Derimod oprettes en ny linie som en kopi af den gamle. Ellers ville vi miste det fulde revisionsspor. Vi er ikke i det Frans Bouma kalder tilfælde to (men beskriver først). Det er også noget rod.

En naturlig løsning er at arkivere gammelt data, som man arkiverer gamle emails (eller hvordan er det nu lige er man får håndteret dem?). Frans Bouma foreslår at bruge database triggers. En delete trigger kan kopiere det slettede data over i anden tabel. Et forslag som jeg ikke kan stå 100% bag. Jeg kan se ideen i at kopiere slettet data over
i en anden tabel. Det er alligevel oftest, at man kun skal bruge de aktuelle data og i de tilfælde hvor man skal se gammelt data vil det kunne klares med et view.

Det er database triggeren som giver mig problemer. Der er mange gode og dårlige grunde til at bruge triggers eller lade være, men min helt store anke i dette tilfælde er manglende triggers. Hvis man får slettet triggeren fra database så får man ingen fejlbeskeder. Uden at vide det kan man få slettet en masse data som burde være kopieret. Man opdager det først når man skal bruge de gamle data. En stored procedure ville kunne gøre det samme og man vil helt sikkert opdage hvis den manglede.

Jeg vil mene at soft-deletes har deres plads, specielt for enterprise data. Men derfor er det stadig tilladt at organisere sine data bedst muligt. Bare lov mig at du ikke bruger en delete trigger som er kritisk for dine data.

Et meget hyppigt anvendt argument for at bruge stored procedures frem for parametriserede queries på SQL Server er performance.  Argumentet er, at eksekveringsplanen for sprocs kan caches på serveren.  Det er korrekt, at sådan forholdt det sig på SQL Server 7 og måske også på 2000, men efter min bedste overbevisning er SQL Server nu i stand til på samme måde at lave caching for parametriserede queries.

Det er svært at finde dokumentation af dette (hvis du har links, så giv dem til mig!), så jeg besluttede at lave et lille forsøg: Jeg lavede en lille testtabel og prøvede at indsætte 100.000 rækker 50 gange med en sproc og med en parametriseret INSERT statement.

Konklusionen?

Jeg kunne ikke se nogen forskel.  Selvfølgelig svinger tiderne noget i forhold til, hvad serveren ellers laver, men man kan ikke se, at den ene metode er hurtigere end den anden.

Der kan selvfølgelig være mange andre argumenter for at sprocs - f.eks. security.  Det vil jeg ikke gå yderligere ind i her, for det plejer at kunne få folk helt op at køre.

Historien er en anden med dynamisk opbygget SQL.  Her kan SQL Server ikke cache.  Men dynamisk opbygget SQL er i forvejen bandlyst pånær i særlige tilfælde, da det øger risikoen for SQL injection.